MySQL索引建立和优化策略

索引执行查看以及失效情况

示例表建表语句：

explain 查看执行计划

一条简单查询的执行计划：

• possible_key 字段表示可能用到到索引
• key 字段表示实际用的索引
• key_len 表示索引的长度
• rows 表示扫描的数据行数
• type 表示数据扫描类型
  • 常见扫描类型执行效率从低到高的顺序为：
    ALL(全表扫描)、index(全索引扫描)、range(索引范围扫描)、ref(非唯一索引扫描)、eq_ref（唯一索引扫描）、const(结果只有一条的主键或唯一索引扫描)。

PS：全表扫描和全索引扫描是需要尽量避免的。

索引失效的例子

like 语句

结合普通索引的B+tree 结构解释原因

当MySQL优化器根据name like ‘%Parto’ 这个条件到索引 index_name 的B+tree数上进行查询评估时，发现当前节点的左右子节点上的值都有可能符合‘%Parto’这个条件，于是优化器判定当前索引需要扫描整个索引并且还要回表查询，不如直接全表扫描。

• 一些其他类似索引失效的情况：
  • 1、在索引列上做了计算、函数、类型转换操作。
  • 2、like 匹配使用了前缀匹配符 ‘%abc’。
  • 3、字符串不加引号导致类型转换。

这些索引失效的原因时查询过程需要扫描整个索引并回表，代价高于直接全表扫描。

• 可能发送索引失效的情况：
  • 1、索引列上用了 != , > , >= , < , <= , or , in 等。
  • 2、索引列上用了 is null , is not null

如果MySQL查询优化器预估走索引的代价比全表扫描的代价还要大，则不走相应的索引直接全表扫描。如果走索引比全表扫描代价小，则使用索引。

可以通过 force index 的方式强制查询引擎走特定的索引 index_name ,MySQL查询优化器的预估并不是精确的，在对索引的效率有把握的情况下可以强制走特定的索引来提高查询效率。

高效索引的建立

建立前缀索引

建立前缀索引时常用的方法之一，通常字符类型的字段只需要对它前面几位字符建立索引就能满足性能要求了，过长的索引会占用更多空间，也影响查找效率。

建立覆盖索引

所谓覆盖索引是指SQL中查询的所有字段在索引 B+tree 的叶子节点上都能找到的那些索引。
使用覆盖索引查询时无需回表查询。

联合索引的最左匹配准则：
这个准则指明了联合索引中某个字段如果想要被用到联合索引的过滤当中，除了这个字段不会使联合索引失效以外，还一个必要前提是这个字段的前一个字段也被用到了联合索引的过滤中。
因此建立联合索引是的字段顺序对索引效率是有很大影响的，越靠前的字段被用于索引过滤的概率越高。开发工作中建立联合索引时需要把区分度大的字段排在靠前的位置，这样区分度大的字段越有可能被更多的SQL用到。(如性别这种区分度小的字段就不适合靠前，uuid则合适)
失效示例：

• sales字段前面的 name 字段使得联合索引失效了 ，无法满足最左匹配准则。
  有效示例：
  

索引下推机制

依据联合索引的最左匹配准则，当联合索引中某个字段使得索引失效时，这个字段就不能被联合索引用于索引过滤了。
MySQL 从5.6 版本开始针对这种情况引入了索引下推机制。

select id,name,sales,age from workers where name = 'Parto' and sales > 10;

这条语句中的name字段被用在了联合索引的过滤中,而 sales 字段由于是一个范围很广的范围查询，被优化器判定位使得索引失效了。执行计划中的 Using index condition 表面SQL 查询用到了索引下推。
在索引下推机制开启时，查询引擎在根据 name = ‘Parto’ 的条件找到联合索引 index_name_sales的叶子节点后，会在叶子节点上根据 sales > 10 的条件进一步过滤数据。
而在索引下推机制关闭时，sales > 10 的条件实在 MySQL Server 层根据查询引擎返回的结果再过滤的。
显然索引下推机制能减少二级索引的回表次数，也能减少查询引擎和MySQL Server 层之间的传递数据量。

join、order by 和 group by 的使用注意

join

• 参与 join 的字段上一般需要加上索引

order by

• 在实际开发中并不是提倡一定要在 order by 字段上加索引，而是提倡尽量结合现有的索引字段进行 order by 操作，尽量避免低效的文件排序操作，以提升SQL查询效率。

group by

• 如果 group by 操作能够依赖索引完成则不需要创建临时表，执行效率高。
  

在实际场景中，复杂的语句很容易使得 order by 和 group by 无法使用相关的索引，所以我们要有意识地寻求 order by 和 group by 依赖相关索引完成。以避免 order by 中低效的文件排序或在 group by 中创建临时表.
如果难度太大或建立索引代价太高的话，也不必强求使用索引。

索引的缺点

• 索引会带来数据写入延迟。
• 索引会引入额外的空间损耗。
• 索引能提升查询效率的场景是有限的，大数据量时需要其它的如读写分离、分库分表等方案。

在整表数据量不大且查询走了索引仍然很慢的情况，可能的原因有：

• SQL 查询是事务中依赖MVCC(多版本并发控制)的快照读，需要多次版本回退。
• 或者SQL查询操作需要等待上一次更新操作释放表的写锁。

总结

---

笔记依据：https://time.geekbang.org/dailylesson/detail/100056830?tid=143 内容编写。