MySQL笔记索引优化

巧妙的使用 Explain

看一条 SQL 语句的性能，可以使用 explain 关键字查看语句性能，这里说一下其中的 type 字段的部分含义，

• all，即全表扫描，说明这个 SQL 语句没有使用到索引，有可能是表本身没有创建索引，也可能是因为 SQL 语句导致没有使用索引

• range，说明使用的是有范围的索引扫描，性能优于 index

• index，这里说明使用了索引，这种情况下，如果 extra 列中的值为 Using index，这种情况是索引覆盖，索引覆盖的意思是，我们想要查询的数据，索引中已经都存在啦，这种情况下就不需要再回表取数据了

• ref，说明条件列使用了索引，但是不是主键和 unique，所以这里即使使用了索引，索引值不唯一，有重复的情况

• eq_ref，相对于 ref 来说就是使用的是唯一索引，对于每个索引键值，只有唯一的一条匹配记录

• const/system，单表中最多只有一条匹配行，查询起来非常迅速，所以这个匹配行中的其他列中的值可以被优化器在当前查询中当做常量来处理。例如根据主键或者唯一索引进行的查询

• index_merge，说明使用了 MySQL 的索引合并的优化方法，当使用合并索引的时候，就需要检查一下我们所创建的索引是否为多个单列索引

如何有效的优化索引

使用索引的时候，索引必须作为独立的列出现

作为独立的列的意思是，索引不能作为表达式的一部分，也不能作为函数的参数出现，否则索引会失效，原因是 MySQL 无法自动解析表达式以及参数，所以也就无法使用索引了，索引失效还有以下几种情况：

• 条件中使用 is null 或者 is not null 会导致索引失效，原因是索引中不会存储 null

• 使用 %like，因为 MySQL 是左匹配，使用模糊查询时如果以%开头会导致全表查询

• 使用多列索引的时候，如果索引顺序不是建立索引的顺序，或者跳过第一个索引直接使用后面的索引，也会导致索引失败，原因依然是 MySQL 是左匹配~

• 条件中包含or时，只有所有列都是单独索引时才会使用索引

如何创建有效的索引

• 如果需要索引很长的字符串，此时需要考虑前缀索引

• 计算该列完整列的选择性，使得前缀选择性接近于完整列的选择性

• 前缀索引即选择所需字符串的一部分前缀作为索引，这时候，需要引入一个概念叫做索引选择性，索引选择性是指不重复的索引值与数据表的记录总数的比值，可以看出索引选择性越高则查询效率越高，当索引选择性为1时，效率是最高的，但是在这种场景下，很明显索引选择性为1的话我们会付出比较高的代价，索引会很大，这时候我们就需要选择字符串的一部分前缀作为索引，通常情况下一列的前缀作为索引选择性也是很高的

• 如何选择前缀

• 使用多列索引

• 尽量不要为多列上创建单列索引，因为这样的情况下最多只能使用一星索引，这样的话，不如去创建一个全覆盖索引，在多列上创建单列索引大部分情况下并不能提高 MySQL 的查询性能，MySQL 5.0 中引入了合并索引，在一定程度上可以表内多个单列索引来定位指定的结果，但是 5.0 以前的版本，如果 where 中的多个条件是基于多个单列索引，那么 MySQL 是无法使用这些索引的，这种情况下，还不如使用 union

• 选择合适的索引列顺序

• 经验是将选择性最高的列放到索引最前列，可以在查询的时候过滤出更少的结果集

• 但这样并不总是最好的，如果考虑到 group by 或者 order by 等情况，再比如考虑到一些特别场景下的 guest 账号等数据情况，上面的经验法则可能就不是最适用的

• 覆盖索引

• 所谓覆盖索引就是指索引中包含了查询中的所有字段，这种情况下就不需要再进行回表查询了

• MySQL 中只能使用 B-Tree 索引做覆盖索引，因为哈希索引等都不存储索引的列的值，覆盖索引对于 MyISAM 和 InnoDB 都非常有效，可以减少系统调用和数据拷贝等时间

• Tips：减少 select * 操作

• 使用索引扫描来做排序

• 索引列顺序和 ORDER BY 顺序一致

• 所有列的排序方向一致

• 如果关联多表，那么只有当 ORDER BY 子句引用的字段全部为第一张表时，才能使用索引做排序，限制依然是需要满足索引的最左前缀要求

• MySQL 生成有序的结果有两种方法：通过排序操作，或者按照索引顺序扫描；使用排序操作需要占用大量的 CPU 和内存资源，而使用 index 性能是很好的，所以，当我们查询有序结果时，尽量使用索引顺序扫描来生成有序结果集

• 怎样保证使用索引顺序扫描：

• 压缩索引

• 上一篇将索引结构的文章提到了，MyISAM 中使用了前缀压缩技术，会减少索引的大小，可以在内存中存储更多的索引，这部分优化默认也是只针对字符串的，但是可以自定义对整数做压缩

• 这个优化在一定情况下性能比较好，但是对于某些情况可能会导致更慢，因为前缀压缩决定了每个关键字都必须依赖于前面的值，所以无法使用二分查找等，只能顺序扫描，所以如果查找的是逆序那么性能可能不佳

• 减少重复、冗余以及未使用的索引

• MySQL 的唯一限制和主键限制都是通过索引实现的，所以不需要在同一列上增加主键、唯一限制再创建索引，这样是重复索引

• 再举个例子，如果已经创建了索引（A，B），那么再创建索引（A）的话，就属于重复索引，因为 MySQL 索引是最左前缀，所以索引（A，B）本身就可以使用索引（A），但是创建索引（B）的话不属于重复索引

• 尽量减少新增索引，而应该扩展已有的索引，因为新增索引可能会导致 INSERT、UPDATE、DELETE 等操作更慢

• 可以考虑删除没有使用到的索引，定位未使用的索引，有两个办法，在 Percona Server 或者 MariaDB 中打开 userstates 服务器变量，然后等服务器运行一段时间后，通过查询 INFORMATION_SCHEMA.INDEX_STATISTICS 就可以查询到每个索引的使用频率

• 索引和锁

• 之前讲索引结构的时候说过，InnoDB 支持行锁和表锁，默认使用行锁，而 MyISAM 使用的是表锁，所以使用索引可以让查询锁定更少的行，这样也会提升查询的性能，如果查询中锁定了1000行，但实际只是用了100行，那么在 5.1 之前都需要提交事务之后才能释放这些锁，5.1 之后可以在服务器端过滤掉行之后就释放锁，不过依然会导致一些锁冲突

• 减少索引和数据碎片

• 首先我们需要了解一下为什么会产生碎片，比如 InnoDB 删除数据时，这一段空间就会被留空，如果一段时间内大量删除数据，就会导致留空的空间比实际的存储空间还要大，这时候如果进行新的插入操作时，MySQL 会尝试重新使用这部分空间，但是依然无法彻底占用，这样就会产生碎片

• 产生碎片带来的后果当然是，降低查询性能，因为这种情况会导致随机磁盘访问

• 可以通过 OPTIMIZE TABLE 或者重新导入数据表来整理数据

总结

数据库的索引这部分要讲的话实在是太多了~绝大部分情况都需要结合实际情况，如果我们可以更多的了解数据库索引本身的一些原理，那么对于优化会有一些帮助~巧妙地使用 explain 分析自己所写的 SQL 语句，可以更好的进行优化。