MYSQL 索引

索引是存储引擎用于快速查找记录的一种结构。如何理解索引，最简单的方式去看看一本书的 “索引” 部分，可以快度找到特定主题的页码。 MYSQL 中类似，首先在索引中查到对应的值，更具索引找到对应的数据行。本文主要介绍 InnoDB 中的 B-Tree 索引。

索引类型

• 索引有很多类型，对于 MYSQL 来说索引是存储引擎层决定的，不是服务层决定的，没有统一的标准。
• 不同存储引擎索引工作方式不一样；并非所有的引擎支持所有的索引；即使多个引擎支持同一类型的索引，但是底层的实现也可能不同。

B-Tree 索引

• 如果没有特定指定说明索引类型，通常索引指的就是 B-Tree 索引。
• B-Tree 索引意味着索引底层是通过B-Tree（B+Tree）实现的，索引的值是按顺序存储的，并且每个叶子节点到根的距离相同。其抽象图如下:
  
• B-Tree 索引能够加快数据的查询效率，因为引擎不需要全表查询数据，取而代之从 B-Tree 的根结点开始查找，通过比较节点的值和查询值可以找到适合指针进入下层叶子节点。
• 叶子结点比较特殊，他们的指针指向的是被索引的数据，而不是其他叶子结点的指针。
• B-Tree 索引树的深度和表的大小有关。

举个例子

• 假如如下数据表:
  

• 索引包含了 last_name， first_name, dob, 索引的组织存储结构如下:
  

• 通过 B-Tree 索引可以加快查询，B-Tree 索引适用于全键值，键值范围和键前缀查找。

全值匹配

全值匹配是指索引匹配所有的列，比如查询姓名为 Allen Cuba，生日为 1960-01-01 的人。

匹配最左前缀

即只使用索引的第一列，比如查找 last_name 是 Allen 的人。

匹配列前缀

可以匹配某一个列的开头部分，比如查询 last_name 是以 A 开头的人，这里也只是使用了某一列。

匹配范围值

例如查找last_name在 Allen 和 Basinger 之间的人。

精确匹配某一个列和范围匹配另外一列

例如查找 last_name 是 Allen, first_name 是 K 开头的人。第一列精确匹配，第二列范围匹配。

只访问索引的查询

指的是查询只访问索引，无需访问数据行。

B-Tree 索引的限制

• 如果不是从索引的最左列开始，则无法使用索引。比如这里如果只查询 first_name 或 生日，索引不生效。
• 不能跳过索引列。如果只查询 last_name 和 生日，first_name被跳过，这里是只能使用索引的第一列，生日的索引不生效。
• 如果某个索引列存在范围查找，则右边的索引列不生效。比如 where last_name = ‘Allen’
  and first_name like ‘J’ and dob = ‘1960-01-01’, 这里只能使用前2列的索引， dob列的索引无法生效。

这里充分说明了索引的顺序十分重要；这些限制都和顺序有关。在优化索引，可能需要相同的列，不同的顺序的索引。

索引的优点

• 索引大大减少了服务器需要扫描的数据量。
• 索引可以将服务器随机 IO 变成 顺序 IO。
• 索引可以帮助服务避免排序和临时表。 B-Tree 索引的数据存储是有序的，所以可以用来做 order by 和 group by 操作。