MySQL 笔记-索引优化

巧妙的使用 Explain

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

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

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

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

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

如何有效的优化索引

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

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

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

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

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

如何创建有效的索引

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

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

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

如何选择前缀

使用多列索引

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

选择合适的索引列顺序

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

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

覆盖索引

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

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

Tips：减少操作

使用索引扫描来做排序

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

所有列的排序方向一致

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

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

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

压缩索引

上一篇将索引结构的文章提到了，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 语句，可以更好的进行优化。