MySQL索引

一、索引

定义：

索引是一个排序的列表，包含索引字段的值和其对应的行记录数据所在的物理地址

作用：

加快表的查询速度（主要作用），还可以对字段排序

副作用：

会额外占用磁盘空间；更新包含索引的表会花费更多的时间

工作方式：

没有索引的情况下，要查询某行记录数据时，需要先扫描全表，再定位某行记录数据的位置
有了索引后，会先通过索引查询到行记录数据所在的物理地址，即可直接访问相应的行记录数据，就像通过书目录的页码快速查找书内容一样。

创建索引的依据：

表的记录行数较多时（一般超过三五百行时），且读操作多的情况下应该要创建索引
建议在表的主键字段、外键字段、多表连接使用的公共字段、唯一性较好的字段、不经常更新的字段、where条件字段、分组(group by)字段、排序(order by)字段、短小的字段上创建索引
不建议在唯一性较差的字段、更新太频繁的字段、大文本字段上创建索引

二、索引类型：

（1）普通索引：

create index 索引名 on 表名(字段(长度));
alter table 表名 add index 索引名(字段);
create table 表名 (.... , index 索引名(字段));

（2）唯一索引：

create unique index 索引名 on 表名(字段);
alter table 表名 add unique 索引名(字段);
create table 表名 (.... , unique 索引名(字段));

（3）主键索引：

alter table 表名 add primary key(字段);
create table 表名 (.... , primary key(字段));

（4）多列组合索引：

create index 索引名 on 表名(字段1, 字段2, ....);
alter table 表名 add index 索引名(字段1, 字段2, ....);
select 字段列表 from 表名 where 字段1=XX and 字段2=XX .... ; #查询语句使用 and 做逻辑运算符时，字段顺序要与创建多列索引的字段顺序一致（要满足最左原则）

（5）全文索引：

create fulltext index 索引名 on 表名(字段);
alter table 表名 add fulltext 索引名(字段);
create table 表名 (.... , fulltext 索引名(字段));
全文索引可用作于模糊查询 select 字段列表 from 表名 where match(字段) against('单词');

全文索引只能在类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引，只支持InnoDB和MyISAM引擎。
全文索引只支持英文全文索引，不支持中文全文索引，需要使用ngram全文解析器，用来支持中文全文索引

vim /etc/my.cnf
[mysqld]
ngram_token_size=2 #指定查询的单词的最小字数

create fulltext index 索引名 on 表名(字段) WITH PARSER ngram;;
alter table 表名 add fulltext 索引名(字段) WITH PARSER ngram;;
create table 表名 (.... , fulltext 索引名(字段) WITH PARSER ngram);

select 字段列表 from 表名 where match(字段) against('单词'); #默认使用自然语言模式
select 字段列表 from 表名 where match(字段) against('+单词1 -单词2' IN BOOLEAN MODE); #使用BOOLEAN模式，必须包含"单词1"，且不能包含"单词2"
select 字段列表 from 表名 where match(字段) against('+单词1 +单词2' IN BOOLEAN MODE); #使用BOOLEAN模式，必须同时包含"单词1"和"单词2"
select 字段列表 from 表名 where match(字段) against('单词1 单词2' IN BOOLEAN MODE); #使用BOOLEAN模式，要么包含"单词1"，要么包含"单词2"

（6）删除索引：

drop index 索引名 on 表名; #这两种方法都可以用来删除普通索引、唯一索引、全文索引、组合索引
alter table 表名 drop index 索引名;

alter table 表名 drop primary key; #删除主键索引

（7）查看索引：

show create table 表名;

show index from 表名;
show keys from 表名;

三、遇到 select 查询语句执行速度慢该怎么办？

1）升级 CPU 内存硬盘硬件性能
2）对 MySQL 配置进行优化
3）对查询语句的结构进行优化，比如将嵌套子查询优化成表连接查询；或连接表时，可以先用where条件对表进行过滤，然后做表连接
4）进行索引优化：先使用 explain 分析 select 语句（看 key rows type 字段），判断这个查询语句是否正确的使用了索引
再根据查询语句中的 where 条件字段建立相应的单列索引或者多列组合索引（多列组合索引要满足最左原则）