mysql索引基础

mysql索引

什么是索引？

索引（在Mysql中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。

为什么要使用索引？

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

索引应该是对查询性能优化最有效的手段了，索引能够轻易的将查询性能提高几个数量级，”最优“的索引有时比一个"好的"索引性能要好两个数量级。

索引基础

要理解索引是如何工作的，最简单的方法就是去看看一本书的"索引部分"。如果想在一本书中找到某个特定主题，一般会先看书的“索引”，找到对应的页码。

在Mysql中，存储引擎用类似的方法使用索引，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行（ps：根据索引树找到符合的索引记录，索引记录包含索引值与主键值，通过主键值定位到数据行）。

假如运行如下查询

mysql> SELECT first_name FROM sakila.actor WHERE actor_id=5;

如果在actor_id列上建有索引，则mysql将使用该索引查找actor_id等于5的索引记录（ps：包含索引值与主键），然后返回通过主键定位到具体的数据行（ps：通过主键定位数据行的步骤称之为回表）。

索引可以包含一个或多个列的值（ps：即单列索引和多列索引）。如果索引包含多个列，那么列的顺序也十分重要，因为Mysql只能高效的使用索引的最左前缀列，创建一个包含两个列的索引和创建两个只包含一列的索引是大不相同的。

可以把没有索引的表理解为Java中的List，在没有索引的情况下，我们要查找指定的数据，只能遍历这个list，但是随着数据量的逐渐增大，遍历list产生的开销也随之增大。因此我们需要一个无需遍历整个list（ps：无需扫描整张表）就可以找到指定数据的方案，这个方案就是索引。（ps：遍历list可以理解为mysql的全表扫描）

索引类型

索引有很多种类型，可以为不同的场景提供更好的性能，不同存储引擎索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。

B-tree索引

当人们谈论索引的时候，如果没有特殊说明，那么多半说的是B-tree索引，它使用B-tree的数据结构来存储数据，大多数mysql引擎都支持这种索引，InnoDB使用B树的变种B+树的结构存储数据。

B-tree通常意味着所有的索引值都是按顺序存储的（ps：这里的顺序存储指的并不是数据行的顺序存储，而是指索引树中包含索引值及主键值的索引记录），并且每一个叶子节点到根节点的距离相同（ps：叶子节点存放索引值及主键值，非叶子节点仅保存索引值范围）。

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

B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于最左前缀查找

• 全值匹配

  全值匹配指的是查找列和索引中的所有列进行等值匹配。

• 最左前缀匹配

  按照索引的定义顺序仅匹配最左侧的一列，即只使用索引的第一列。

• 列前缀匹配

  仅匹配某一列值的开头部分。

• 范围匹配

  （ps：范围匹配指查询条件并非是一个具体的数值，而是一个数值区间）

• 精确匹配+范围匹配

  一般适用于多列组合索引，即第一列采用全值匹配，第二列采用范围匹配

• 覆盖索引

  B-tree通常可以支持“只访问索引的查询”，即查询只需要访问索引而无须访问数据行（ps：无须回表的查询）

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的Order By操作（按顺序查找），一般来说，如果B-Tree可以按照某种方式查到值，那么也可以按照这种方式用于排序。所以，如果order by子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求（ps：因为索引树中的索引记录本身就是就是有序的）

B-Tree索引的限制

• 如果不上按照索引的最左列开始查找，则无法使用索引（ps：即不能跳过索引的第一列直接使用第二列）
• 如果查询条件中存在某列的范围查询，则右边所有的列都无法使用索引。

哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。（ps：因为哈希索引树不保存索引值，所以无法应用覆盖索引这一特性）

在Mysql中，只有Memory引擎显示支持哈希索引，这也是Memory引擎的默认索引类型，Memory引擎同时也支持B-Tree索引。值得一提的是，Memory引擎是支持费唯一哈希索引的，如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。（ps：类似于HashMap的思想）

Hash索引的优势在于索引本身只存储对应的哈希值，所以索引的结构十分紧凑

Hash索引的限制

• 哈希索引只包含哈希值与行指针，不存储字段值，所以不能使用覆盖索引这一特性，不过在内存中访问数据行的速度非常快，所以大部分情况下这一点对性能的影响并不明显
• 哈希索引数据并不是按照索引值顺序存储的，所以无法适用于排序
• 哈希索引不支持部分索引列匹配查找，因为哈希码是通过索引列的全部内容计算的。
• 哈希索引仅支持等值比较查询包括=、In()、<=>
• 如果哈希冲突很多的话，一些索引纬度操作的代价也会很高，（ps：极端情况下，哈希索引可能会退化为链表，此时索引已完全失去意义）

聚簇索引

聚簇索引（ps：也可以称之为主键索引）并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，InnoDB的聚簇索引在同一个结构中保存了B-Tree的索引值和数据行。（ps：即InnoDB的聚簇索引的实质就是一个基于主键的B-Tree索引，不同的是此索引树的叶子节点包含的是具体的数据行）

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。术语“聚簇”表示数据行和相邻的键值（主键值）紧凑的存储在一起，因为无法同时把数据行存放在两个不同的地方，因此一个表只能有一个聚簇索引

一些数据库服务器允许选择哪个索引作为聚簇索引，但是Mysql目前内建的存储引擎都不支持此功能，InnoDB通过主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

聚簇索引的优点

• 将索引值与数据行保存在同一个B-tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快
• 使用覆盖索引扫描的查询可以直接使用该索引对应的主键值（ps：指的是非聚簇索引叶子节点保存有聚簇索引的主键值）

聚簇索引的缺点

• 更新聚簇索引列的代价很高，因为需要移动B-tree索引树中数据行的位置。且数据行在移动时可能会面临页分裂的问题。
• 聚簇索引会导致二级索引（也叫非聚簇索引、辅助索引、普通索引）的增大。因为二级索引的叶子节点包含聚簇索引的主键值
• 二级索引访问需要先查找二级索引树、再查找聚簇索引树（ps：二级索引查找聚簇索引树的步骤即回表的步骤，对于Innobd的自适应哈希索引可以减少这样重复的工作）。

索引的优点

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

最常见的B-Tree索引，按照顺序存储数据，所以mysql可以用来做orderBy和GroupBy操作。而且因为索引中存储了实际的列值，所以某些查询也可以应用覆盖索引这一特性。据此特性，总结下来索引有如下三个优点：

• 索引大大减少了服务器需要扫描的数据量。
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机IO变为顺序IO