Mysql中遇到的索引问题总结

最近几天碰到了Mysql查询数据慢，造成接口请求超时情况，排查原因后发现为建立索引无法使用问题，特总结如下

第一步

查看Mysql中的索引，使用命令 show index from table_name，首先需要了解使用查询出来后所代表的意思

命令详解：

命令行	对应的意思
Table	表的名称
Non_unique	如果索引不能包括重复词，则为0。如果可以，则为1
Key_name	索引的名称
Seq_in_index	索引中的列序列号，从1开始
Column_name	列名称
Collation	列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）
Cardinality	索引中唯一值的数目的估计值
Sub_part	如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed	指示关键字如何被压缩。如果没有被压缩，则为NULL
Null	如果列含有NULL，则含有YES。如果没有，则该列含有NO
Index_type	用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）
Comment	多种评注

第二步

根据业务需求，列出可能会被搜索的条件，进行设置主键索引或者复合索引。

第三步

同样也是最重要的一步
使用 Explain 命令并附带你所需要的运行的Sql进行查询，检验是否所有索引都有被引用到，此处需要掌握 Explain 后所代表的意思

命令详解：

命令行	对应的意思
id	选择标识符
select_type	表示查询的类型
table	输出结果集的表
partitions	匹配的分区
type	表示表的连接类型
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
ref	列与索引的比较
rows	扫描出的行数(估算的行数)
filtered	按表条件过滤的行百分比
Extra	执行情况的描述和说明

第四步

根据测试结果可以对你所建立的索引进行再次优化，需要了解索引建立的规则

1.最左前缀匹配原则，非常重要的原则，也就是在你建立复合索引的时候需要将能够使你快速缩短查询范围的字段放在第一个位置。

2.尽量选择区分度高的列作为索引,比如性别平时会使用0,1代表男、女，这样可以更快速的查询

3.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

4.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

5.为经常需要排序、分组和联合操作的字段建立索引，如经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

6.前导模糊查询不能命中索引：EXPLAIN SELECT * FROM user WHERE name LIKE ‘%s%’，尤其是在java后台中，可优化为非前导模糊查询。

7.建立索引的列，不允许为null，如果有空置，建议设为0.

注意：查询条件的顺序与索引的顺序无关，如果你使用到了某个索引，那他会按照你所建立的索引进行排序