四、MySQL索引

一、索引是什么

索引是存储引擎用于快速查找表中记录的一种数据结构，这也是索引的基本功能。

可以将索引类比于书籍的目录：如果没有目录，需要查找书中的内容时，就需要从书的第一页开始顺序查找书的每一页，直到查找命中；在有目录的情况下，就可以根据目录，快速定位到要查找的内容位于书中的哪一页，极快提高查询速度。

对于存储引擎也是一样的：在没有索引的情况下，就只能逐行遍历表中数据进行查找；在有索引的情况下，就可以根据索引快速查找到相应的索引记录，然后再根据索引记录中的数据快速定位到数据库表中的行记录，完成数据的快速查找。

二、索引的优缺点

2.1、优点

• 减少需要扫描的数据量，提高查询效率
• 将随机IO变为顺序IO
• 帮助服务器避免排序和创建临时表

2.2、缺点

• 增删改操作时，需要进行维护索引的操作，降低增删改效率
• 索引需要占用存储空间

三、索引的底层数据结构

在MySQL中，索引是在存储引擎层实现的，而不是在服务器层实现的。不同存储引擎的索引的工作原理并不一定都相同。总体来说MySQL支持以下两种索引：B+树索引、Hash索引。

3.1、B+树索引

通常情况下，讨论数据库的索引时，默认都是指的B+树索引。与B树不同的是，B+树的所有数据都只会存储在叶子节点上，并且每个叶子节点都包含有指向下一个叶子节点的指针，这样可以使B+树更加高效的支持范围查找。

例：假设有如下一张表，创建的索引中包含了last_name, first_name, dob三列。

 索引将会以这三列作为一个整体，下图展示了索引中数据存储的结构

索引对多个值进行排序时，首先根据last_name进行排序，last_name相同的，再根据first_name进行排序，first_name也相同时，再根据出生日志排序。也就是说，索引排序是根据创建索引时索引列的顺序来进行排序的，因此，创建索引时索引列的顺序尤其重要。

以上索引可以满足以下类型的查询需要：

1、全值匹配：查找索引中的三列与某个值完全匹配的数据，如查找姓名为Allen Cuba，出生于1960-01-01的人

2、匹配最左前缀：如查找所有last_name为Allen的人（只使用到索引的第一列）

3、匹配列前缀：如查找所有last_name以A开头的人（只使用到索引的第一列）

4、匹配范围值：如查找所有last_name在Allen和Barrymore范围内的人（只使用到索引的第一列）

5、精确匹配某一列，并对其他列进行范围查找：如查找所有last_name为Allen，first_name以K开头的人（第一列精确查找，第二列范围查找）

6、只查询索引列数据的查询：如查询last_name为Allen的所有人的last_name，first_name和dob，这些数据再索引中已经存在，不再需要访问数据库中的行

B+索引的限制：

1、如果不是按照索引的最左列开始查询，则无法使用索引：如上述索引中，无法使用索引查询first_name为Cuba的人

2、不能跳过索引中的列：无法查询last_name为Allen，dob为某一天的人。如果不指定first_name，就只能使用索引的第一列

3、如果查询中有范围查询，则其右边的列都无法使用索引优化查询：如WHERE last_name = "Allen" AND first_name LIKE "J%" AND dob = "1976-12-23"。由于LIKE是一个范围查询条件，这个查询只能使用索引的前两列。

3.2、Hash索引

Hash索引是基于Hash表实现的，只支持精确查找，不支持范围查找。

对于表中每一行的数据，存储引擎将会计算其索引列的Hash值，并将其记录在索引中，同时，索引中也会记录指向每个数据行的地址，便于根据索引查找到对应的数据。

Hash索引的限制：

• 不支持范围查找，也不支持排序
• 不支持按照部分索引列进行查找，因为Hash索引是根据所有的索引列计算Hash值的，如在列（A，B）两列上面创建Hash索引，若只查询数据列A，则无法使用索引
• 索引中只记录了Hash值和行指针，不包括行数据，所以Hash索引不能避免读取行（但是内存中计算很快，此影响可以忽略不计）

四、索引适合的场景

• 在where条件中经常使用的列上创建索引
• 在经常需要排序（order by）的列上创建索引
• 在经常需要连接（join）的列上创建索引

五、高性能的索引策略

索引对于性能非常关键，当表中数据量越大，索引对性能的影响就越明显。合适的索引能够极大提升查询效率，但是不合适的索引甚至会降低性能，因此，一定要在合适的场景创建合适的索引。

此外，索引创建之后，不当的使用的方式也无法提高查询性能，只有正确的使用索引，才能将索引的优势发挥出来。

下面介绍常见的高性能的索引策略。

1、独立的列

查询时应该将索引列单独放在where条件的一侧，而不要让索引列参与计算，否则无法利用索引。

如下，如果id列已经创建了索引，看下面的查询语句，很明显是要查询id = 4的列，但是MySQL却并不会进行这样的转换，所以就无法使用索引。

select id from table_name where id + 1 = 5;

因此，为了高效利用索引，应该将查询语句写为如下的方式：

select id from table_name where id = 5 - 1;

同样的，另外一个常见的错误是

select * from table_name where to_days(current_date) - to_days(date_clo) <= 10

应该将查询语句写为如下方式，才能有效利用索引

select * from table_name where to_days(date_clo) >= to_days(current_date) - 10

2、索引的选择性和前缀索引

索引的选择性是指，不重复的索引值的个数（也称为基数）与表中行记录的总数（T）的比值，选择性的取值范围为1/T ~ 1。对于唯一索引，不重复的索引值个数等于行记录的总数，因此它的索引选择性为1。索引的选择性越高，则索引的效率就越高，因为选择性高的索引在查找时能够过滤掉更多的行记录。

MySQL中，对于BLOB，TEXT或者长度很长的VARCHAR类型的列，必须使用前缀索引，因为MYSQL不允许索引这些列的完整长度（太耗费存储空间）。所谓的前缀索引，就是取这些列一定长度的前缀来作为索引。

创建前缀索引时，关键就在于如何决定前缀的长度。需要从两方面考虑：

• 前缀的长度不能太长，以便于解约空间。
• 前缀的长度也不能太短，要使前缀索引的选择性也要尽可能足够高。（前缀的长度越长，两行重复的概率就越低，前缀索引的选择性就越大）

前缀索引也有相应的不足：MySQL无法基于前缀索引做order by和group by操作。

3、多列索引

对索引的理解不够透彻时，容易进入一个误区：即为每个列创建单独的索引，或者按照不恰当的顺序创建了多列索引。索引顺序放在下一节单独讨论，这里主要讨论为每列创建单独索引的情况。如下表，为每一列都创建了单独的索引。

 通常情况下，为每列创建单独索引并不能有效提高查询效率。假设有以下查询语句

select * from t where c1 = 1 or c2 =2;

在老版本的MySQL中，这个查询语句将无法使用索引，而不得不进行全表扫描。在5.0及以后的新版本中， MySQL引入了一种叫“索引合并”的策略，在查询时能够分别使用两个单列索引进行查询，并将查询结果进行合并。

索引合并策略是MySQL底层为了优化查询而实现的，但是，在更多时候，如果一条查询语句导致MySQL使用了索引合并策略，则说明表上的索引创建得可能并不是很恰当。

4、选择合适的索引列顺序

合适的索引列顺序与使用该索引的查询语句是强相关的，并且还需要同时考虑满足排序和分组的需求。

索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列、第三列。。。所以，索引列的顺序至关重要。对于如何选取索引列的顺序，有一个经验法则：将选择性最高的列放在索引的最前列（在不需要考虑排序和分组的情况下）。

通常情况下这一法则是有用的，但它并非在所有情况都有效，要根据查询语句来实际判断。

 5、聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。聚簇表示数据行和相应的键值是存放在一起的。当表有聚簇索引索引时，它的数据行实际是存放在B+树的叶子节点中的。由于无法同时将数据行存放在两个地方，所以一个表只能有一个聚簇索引。（聚簇索引的作用？？？）

并非所有的存储引擎都支持聚簇索引，这里主要讨论InnoDB实现的聚簇索引，原理与其他存储引擎是类似的。下图显示了，聚簇索引中记录的存储方式。叶子节点中包含了行的全部数据，但非叶子节点中只包含了索引列。图中被索引的列就是主键列。

 当期存储引擎不支持手动指定聚簇索引的索引列。如果没有显式定义主键列，InnoDB会选择一个唯一的非空的索引来代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引的索引列。

InnoDB将同一页（同一个叶结点）中的记录聚集在一起，但是具有相邻键值的两行记录，是有可能存放在两页中的，而这两页在磁盘中也有可能相距甚远。

聚簇索引的优点：

• 将相关数据保存在一起。例如，实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要很少的磁盘IO就能将一个用户所有的电子邮件读取出来，若没有聚簇索引，有可能每一封邮件都需要进行一次磁盘IO。
• 数据访问更快。数据和索引放在一起，从聚簇索引中获取数据比从非聚簇索引中获取更快。
• 使用覆盖索引扫描的查询可以直接使用叶结点中主键值。

聚簇索引的缺点：

• 聚簇索引最大限度提高了IO密集型应用的性能，但如果数据全都放在内存中，则访问的顺序就没那么重要了，聚簇索引的优势也就没有了
• 插入速度严重依赖于插入顺序。按照主键的顺序插入时加载数据到InnoDB表中速度最快的方式。如果不是按照主键顺序插入的数据，在加载完成之后，最好使用optimize table命令重新组织一下表
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，InnoDB会将该页分裂成两个页面来容纳该行。这就是一次页分裂操作，页分裂会导致表占用更多的磁盘空间
• 聚簇索引可能会导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
• 二级索引（非聚簇索引）可能会更大，因为二级索引的叶子节点包含了引用行的主键列
• 二级索引访问需要两次索引查找，而不是一次。因为二级索引中保存的“行指针”的实质是，二级索引叶子结点保存的不是指向行的物理位置的指针，而是行的主键值。（？？？）

6、覆盖索引

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

既然覆盖索引中包含了所有需要查询的字段的值，则查询时就不必再去表中查询，能够极大提高查询性能。