聊聊 MySQL 索引

为什么使用索引

不同于redis 和 memorycache 等内存数据库，MySQL 数据库中数据表的数据记录是以存储引擎规定的特定数据格式存储在物理磁盘上的。所以执行 sql 语句最耗时的阶段就是磁盘的 IO 阶段。

有效减少磁盘 IO 阶段的查询时间可使查询效率大幅提升，那么我们如何减少 IO 阶段的查询时间呢？

假设有一张数据表表中的数据条数为N，在不使用索引的情况下查询数据表中的一条记录理论上时间复杂度是O(N)，即顺序遍历。那么使用索引是否可以大幅度提升效率？

MySQL中主要的是BTREE索引:

B-Tree 是一种平衡搜索树，它具备以下特性：

  • 平衡性，含有 n 个结点的 B-Tree 的高度为 O(logN) 。它的严格高度可能比红黑树（不严格的平衡二叉树）的高度要小许多，这是因为它的分支因子，也就是表示高度的对数的底数可以非常大。
  • 排序性，结点的排列方式类似于二叉搜索树，可以有序的遍历输出结点。

使用索引查询时每层树高都会进行一次I/O，所以索引大大提高了磁盘IO效率O(logN)，如何做到建立有效高效的索引呢？以下是几条建议：

索引建议

  1. 在 InnoDB 存储引擎中，主键尽量避免使用很长的字段

每个叶子节点中不仅记录了索引值也记录了索引值对应数据的指向。不同的引擎指向方式有所区别。

InnoDB 存储引擎和 MyISAM 存储引擎的区别是，MyISAM 存储引擎的数据文件和索引文件是分开的，某个数据表的数据记录是单独存放在文件中的，这时索引树中的节点存放的是数据表的记录的物理地址。InnoDB存储引擎中某个数据表的数据记录就直接保存在索引树的叶子节点中，普通叶子节点的数据存放的就是主键。如果主键越长二级索引的叶子节点只能存储越少的主键，这样 B-Tree 的树高会增加，这会使整个二级索引树更大，从而增加 I/O。

  1. 在保障索引区分度的情况下，被索引的字段尽量不要太长

在 MySQL 的存储引擎中 B-Tree 索引的每个节点都是一个磁盘页面有被称为 page ，可以通过以下命令来检索：

1
show variables like 'innodb_page_size'

在 B-Tree 索引的磁盘读写过程中，每读取一个节点就会进行一次磁盘 I/O，所以B-Tree的树高就是通过B-Tree索引进行检索的磁盘I/O次数，因而当被索引的字段较短时一次磁盘 I/O 就可以获得更多的索引建，从而减小 I/O 次数。

但是也不能盲目缩短索引字段的长度，还要考虑索引的区分度即索引的选择性，索引的区分度=不重复的索引值/总记录数，这个数据越接近于1表示索引列的重复记录越少，索引越有价值。通过以下语句来查看，以 name 字段为例：

1
2
3
4
// 建立索引
create index index_name on students (name)
// 查询区分度
select count(distinct(name)) / cont(*) as selectivity from students;

如果使用更短的索引（使用name最左边第一个字符作为索引字段）：

1
2
3
4
// 建立索引
create index index_name on students (name(1))
// 查询区分度
select count(distinct(left(name,1)) / cont(*) as selectivity from students;

在实际应用中可以通过逐步缩短索引字段长度计算索引区分度的方式来找到既可以有较好的索引区分度同时字段也较短的索引字段。

  1. 查询中可以利用索引覆盖，避免不必要的回表

索引覆盖简单说就是 query 中所有字段都可在索引 B-Tree 中找到，使用查询语句最好不需要回表，字段不多则可以利用联合索引来实现。

建立联合索引时的字段顺序对索引效率具有很大影响越靠前的字段用于索引过滤的概率越高，实际应用中应把索引区分度高的字段排在联合索引前面，这样区分度大的字段越有可能被更多的 sql 使用到。

  1. 不要建立太多索引

索引不是万能的，建立索引也是有代价的数据表中任何一条数据的写操作都可能影响到数据的索引树，随着索引数目的增加索引表的更新操作会浪费更多的磁盘I/O，如果在索引区分度较低的索引字段进行索引，对于查询效率不但没有提升反而降低插入删除更新的操作效率。

  1. 尽量使用索引字段进行 order by ， group by

为了避免 order by 文件排序和 group by 创建临时表的过程，可以尽量使用具有索引的字段来进行操作。如果创建成本过高则可以放弃。

索引失效

在我们实际应用中经常发现有些查询语句很慢，加索引也无济于事，那可能是索引失效：

  1. 隐式类型转换

如果查询语句中查询字段类型不匹配时，MySQL 会进行隐式类型转换，如果该字段是索引字段则会导致索引失效

1
 select * from students where phone=12233448897
  1. 表达式计算

被索引字段使用了表达式计算语句会导致索引失效。

1
 select * from students where age-2=18
  1. 函数使用

被索引字段使用了函数

1
select * from students where left(phone,3)='133'

以上三种索引失效是因为，索引字段的使用依赖于B-Tree树的节点遍历，节点遍历又依赖于叶子节点的有序性。被索引字段进行处理后，这个字段新的排列顺序和原来在索引树叶子节点上的排列顺序有了差异，索引策略无法得知是否可以使用索引则不进行索引查询。

  1. Like 关键字 后使用左模糊匹配和左右模糊匹配
  1. 被使用的索引字段不是联合索引的最左字段，联合索引不使用

4，5 两个都是因为违反了B-Tree索引的最左匹配原则