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

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

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

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

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

作为独立的列的意思是,索引不能作为表达式的一部分,也不能作为函数的参数出现,否则索引会失效,原因是 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 语句,可以更好的进行优化。