MySQL学习笔记之索引

索引是存储引擎用于快速找到记录的一种数据结构。

索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。在数据量较小且负载较低时，不恰当的索引对性能的影响可能还不明显，但当数据量逐渐增大时，性能则会急剧下降。

在 MySQL 中，存储引擎用类似的方法使用索引，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。

索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。

索引的类型

在MySQL中，索引是在存储引擎层而不是服务器层实现的。

B-Tree 索引

当人们谈论索引的时候，如果没有特别指明类型，那多半说的是B-Tree索引，它使用B-Tree数据结构来存储数据。

我们使用术语“B-Tree”，是因为MySQL在CREATE TABLE和其他语句中也适用该关键字。不过，底层的存储引擎也可能使用不同的存储结构，例如，NDB 集群存储引擎内部实际上使用了T-Tree 结构存储这中索引，即使其名字是BTREE；InnoDB 则使用的是B+Tree。

存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如，MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

B-Tree 通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。下图展示了B-Tree索引的抽象表示，大致反映了InnoDB索引是如何工作的。

B-Tree 索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根结点开始进行搜索。根结点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页（不同存储引擎的“指针”类型不同）。上图中近绘制了一个节点和其对应的叶子节点，其实在根节点和叶子节点之间可能有很多节点层。树的深度和表的大小直接相关。

B-Tree对索引列是顺序存组织存储的，所以很适合查找范围数据。

可以使用B-Tree索引的查询类型。B-Tree 索引适用于全健值、健值范围或健前缀查找。其中健前缀查找只适用于最左前缀查找。B-Tree 索引对如下类型的查询最有效：

• 全值匹配
• 匹配最左前缀
• 匹配列前缀
• 匹配范围值
• 精确匹配某一列并范围匹配另一列
• 只访问索引的查询

B-Tree 索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。
• 不能跳过索引中的列。
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

所以，索引列的顺序是很重要的：上面的限制都和索引列的顺序有关。

哈希索引

哈希索引基于哈希索引实现，只有精确匹配所有所有列的查询才有效。 对于每一行数据，存储引擎都会对所有的索引列精算一个哈希码，哈希码是一个较小的值，并且不同健值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

哈希索引的限制：

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
• 哈希索引只支持等值比较查询。不支持任务范围查询。
• 访问哈希索引的数据非常快，除非有很多哈希冲突（不同索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
• 如果哈希冲突很多的话，一些索引维护代价也会很高。

因为这些限制哈希索引只适用于某些特定的场合。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的、内部的，用户无法控制或者配置。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。

索引的优点

索引可以让服务器快速地定位到表的指定位置。但这并不是索引唯一作用，根据创建索引的数据结构不同，索引也有一些其他的附加作用。

最常见的B-Tree 索引，按照顺序存储数据，所以MySQL 可以用来做ORDER BY 和 GROUP BY 操作。因为数据是有序的，索引B-Tree也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。总结下来索引有如下三个优点：

1. 索引大大减少了服务器需要扫描的数据量。
2. 索引可以帮助服务器避免排序和临时表。
3. 索引可以将所及I/O变为顺序I/O。

评价一个索引是否适合某个查询的“三星系统”：索引将相关的记录放到一起则获得“一星”；如果索引中的数据顺序和查找中的排列顺序一致则获得“二星”；如果索引中的列包含了查询中需要的全部列则获得“三星”。

高性能的索引策略

正确的创建和使用索引是实现高性能查询的基础。

独立的列

如果查询中的列不是独立的，则MySQL就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

例如，下面这个查询无法使用actor_id列的索引：

mysql>select * from sakila.action_id + 1 = 5;

凭肉眼很容易看出where中的表达式其实等价于actor_id = 4，但是MySQL无法自动解析这个方程式。这完全是用户行为。我们应该养成简化where条件的习惯，始终保持将索引列单独放在比较符号的一侧。

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是前面提到过的模拟哈希索引。但有时候这样做还不够，还可以做些什么呢？

通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值（也称为基数）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

一般情况下某个列的前缀选择性也是足够高的，足以满足查询性能。对于BLOG、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些的完整长度。

诀窍在于选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。

选择合适的索引顺序

我们遇到的最容易引起困惑的问题就是索引列的顺序。正确的索引顺序依赖于使用该索引的查询，并且同时需要考虑如何更地满足排序和分组的需要。

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照生序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCK等子句的查询需求。

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件查找。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。 请注意，这句话十分重要，这句话的意思翻译一下就是说，聚簇索引是一种物理存储结构而不是建表时候指定的一种索引类型。

InnoDB会以聚簇索引的形式来存储实际的数据，它是其他二级索引的基础。

InnoDB建立聚簇索引的方式：

1. InnoDB对主键建立聚簇索引。
2. 如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。
3. 如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。

InnoDB的聚簇索引实际上是在同一个结构种保存了B-Tree索引和数据行。

聚簇索引的优点：

• 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据。这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘IO。
• 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点：

• 聚簇索引最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
• 插入速度严重依赖于插入顺序。
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该也分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引（非聚簇索引）可能比想象的要更大（和使用地址的方式相比较），因为在二级索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。

最后一点可能让人有些疑惑，为什么二级索引需要两次索引查找？答案在于二级索引中保存的“行指针”的实质。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。

这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。对于InnoDB，自适应哈希索引能够减少这样的重复工作。

覆盖索引

如果一个索引包含（或者说是覆盖）所有需要查询的字段值，我们就称之为“覆盖索引”。

覆盖索引是非常有用的工具，能够极大地提高性能。考虑一下如果查询需要扫描索引而无需回表，会带来多少好处。

总结

总的来说，编写查询语句时应该尽可能选择合适的索引以避免单行查找、尽可能地使用数据原生顺序从而避免额外的排序操作，并尽可能使用索引覆盖查询。

我对于索引的认知是从上次在一个有1000多万行记录的表中根据一个没有索引的字段进行条件查询开始的，天知道那是怎样的一种痛苦...

有一些经验再来学这些基础的知识，感觉十分不一样。

欢迎关注我的公众号：荒古传说

本文作者： 荒古
本文链接： https://haxianhe.com/2019/08/...
版权声明： 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 3.0 许可协议。转载请注明出处！