MySQL（2）- 索引的使用和优化

一、前言

本文将介绍索引的使用和优化。
在上一节已经讲述了索引的数据结构。
如果还没看，建议先看上一节，对数据结构了解了，本文看起来应该就没什么压力了。

二、正文

1.索引的分类

（1）单列索引

一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了

• 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一 点。
• 唯一索引：索引列中的值必须是唯一的，但是允许为空值。
• 主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。

（2）组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

（3）全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 …" 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思。一般开发中，不贵用到全文索引，因为其占用很大的物理空间和降低了记录修改性，故较为少用。

2.使用场景

建立索引能优化查询，但对于数据的更新插入需要消耗一定的资源。

（1）使用场景

• 全值匹配
• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其它表关联的字段，外键关系建立索引
• 频繁更新的字段不适合创建索引
• 单键/组合索引选择问题
• 查询中排序的字段
• 查询中统计或者分组字段

（2）不使用场景

• 表记录太少不需要创建
• 经常更新的表不创建
• 数据重复或者均匀分布

3.优化

（1）导致索引失效

• 不在索引列上做任何操作（计算，函数，（自动or手动）类型转换），会导致索引失效
• 在组合索引中，使用范围条件的列，会导致后面的列索引失效
• 尽量使用覆盖索引（只访问索引的查询（索引列表和查询列一致）），避免回表查询
• 在使用不等于（!= 或 <>）的时候会导致索引失效
• is not null 无法使用索引（is null 在高版本可用）
• like查询以通配符%开头（解决like查询%开头可用覆盖索引）
• 字符串不加单引号会失效
• or 导致后面条件的列索引失效
• order by，group by 时 filesort（分组前必排序，会有临时表生成）

（2）建议

• 对于单键索引，尽量选择针对当前query过滤下更好的索引
• 在选择组合索引的时候，当query中过滤性越好的字段，顺序越靠前越好
• 尽可能通过分析统计信息和调整query的写法选择合适的索引

（3）优化口令

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能端；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写量；
不等空值还有or，索引失效要少用。

4.举栗子

（1）单表

sql：select * from table where a=1 and b>2 order by c desc limit 1

index：(a,b,c)

因为b>2是范围条件，查找b>2的条件后的数据对于c是乱序的，所以索引用到了ab，索引c失效。
如果要使c排序生效，应该这样建立索引（a，c）

（2）多表

sql：select * from article_class RIGHT JOIN article on article.class_id = article_class.id;

index：article(class_id)

多表连接查询时，要遵循小表驱动大表的原则，left join 是会先执行查询左边表，right join 会先执行查询右边表，查询完第一个表后，再关联第二个表查询，所以，第二个表的外键可以建立索引来命中。
注意： 当先执行第一个表时，如果有第2个表的排序条件时，会全文检索，生成临时表并使用文件排序（Extra：Using temporary; Using filesort）导致索引失效。

sql：select * from article_class RIGHT JOIN article on article.class_id = article_class.id where article_class.admin_id=20 order by article.create_time desc ;

index：article(class_id,create_time)，article_class（admin_id）

解决： 可以先查询出满足条件的表数据作为子表，再关联查询。

sql：select * from ( SELECT class_id FROM article_class WHERE article_class.admin_id=20) RIGHT JOIN article on article.class_id = article_class.id  order by article.create_time desc ;

5.explain

mysql查询sql可用explain分析，可以看到查询语句的顺序，表，使用的索引和索引的命中情况。