MySQL索引

1 索引概述

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。

索引的作用就相当于目录的作用。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

当然，使用索引也是需要代价的，比如创建和维护索引就需要耗费很多时间。下面总结了使用索引的优缺点：

• 优点
  • 加快检索速度
    • 大大减少了服务器需要扫描的数据行数。
    • 帮助服务器避免进行排序和分组，也就不需要创建临时表(B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，因为不需要排序和分组，也就不需要创建临时表)。
    • 将随机 I/O 变为顺序 I/O(B+Tree 索引是有序的，也就将相邻的数据都存储在一起)。
  • 创建唯一索引，可以保证数据库表中每一行的唯一性
• 缺点
  • 创建和维护索引需要耗费时间
  • 索引需要使用物理文件存储，会耗费一定的物理空间

若索引太多，应用程序的性能会受到影响。而索引太少，对查询性能又会产生影响。因此在实际生产活动中，要根据业务情况，合理创建索引。

2 InnoDB中的索引

因为B+树具有高扇出性，因此高度通常在2~4层，也就是说查找某一键值对应的行记录时只需要2到4次IO即可。B+树索引可以分为聚集索引、辅助索引和联合索引，下面分别进行介绍。

2.1 聚集索引

聚集索引即按照表的逐渐构造一棵B+树，同时叶子节点中存放的为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

上图展示了聚集索引的存储结构，需要注意的是：聚集索引的存储并不是物理上连续的，而是通过指针，实现逻辑上的连续。这里说到的连续有两个：

1. 不同页，通过双向链表链接，页按照主键顺序排列；
2. 页中的行数据，也是按照主键顺序排列，每一行数据也是通过链表连接到一起的。

下面总结了使用聚集索引的优缺点：

• 优点
  • 对于主键的排序查找和范围查找速度非常快
  • 叶子节点中的数据就是全数据，一次查找即可获得所有列的数据
• 缺点
  • 依赖有序数据：如果索引列是字符串或者UUID之类的，比较难比较，插入和查找的速度会慢一点
  • 更新代价大：索引列数据被修改的话，那么对应的索引也要修改，而且聚集索引叶子节点存放了整行数据，修改代价会比较大，所以一般禁止修改主键。

2.2 辅助索引

辅助索引，或者称之为二级索引，常见的二级索引包括唯一索引、普通索引、前缀索引、全文索引等。和聚集索引不同，二级索引的叶子节点包含的不是行数据而是主键值，如下图所示：

辅助索引的存在并不影响数据在聚集索引中的组织，每张表中可以有多个辅助索引。如果希望通过辅助索引查询非索引列数据，需要二次检索：

1. 先在辅助索引中，检索，得到对应行主键；
2. 根据步骤1得到的主键，到聚集索引中检索行数据。

这里引申出一个“覆盖索引”的概念。 覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。

辅助索引的优点是：更新代价比聚集索引小，因为叶子节点中只存放了主键。它的缺点是：

1. 和聚集索引一样，它也依赖于有序的数据。
2. 如果索引列没有覆盖查询数据，需要二次查询。

2.3 联合索引

联合索引就是：把多个列放到一起，建立索引，其本质上也是一个二级索引。比如将c2列和c3列建立联合索引，此时B+树建立规则为：

1. 先把各个记录和页按照c2列进行排序；
2. 如果两条记录c2列值相等，再按照c3列进行排序。

为c2、c3列建立的联合索引示意图如下：

此时索引的目录项包含三个值：主键、c1、c2

2.4 其他概念

2.4.1 回表

从前文我们知道，二级索引的数据页的目录项中只包含：主键和索引字段，那么如果我们需要查询非索引字段怎么办呢？此时就需要进行回表操作，到聚簇索引中获取目标字段。

所谓回表就是：首先通过二级索引获取目标数据的主键，然后拿着合法主键再到聚簇索引中获取目标字段。

2.4.2 覆盖索引

所谓覆盖索引，就是我们索引字段刚好包含我们需要查询的字段。举个例子：比如我们在c2、c3列上建立联合索引，现执行如下查询语句：

select c1, c2 from demo where c1 = 1 and c2 = 2;

此时，我们直接在二级索引中就能获取我们需要的所有字段，无需执行回表操作，大大提高查询性能。

2.4.3 Cardinality

通过SHOW INDEX命令可以查看表的索引信息，其中有一个名为Cardinality的字段。Cardinality表示索引中不重复记录数量的一个预估值。在实际应用中，Cardinality/n_rows_in_table的值应当尽可能接近1，这样使用B+树索引才有意义。对于地区、类型、性别字段等，它们的取值范围通常很小，对这些字段添加索引没有太大意义，甚至影响系统性能。

不同存储引擎，对索引的实现不同，因此对于Cardinality值的计算和更新策略也是不同的。下面介绍InnoDB中Cardinality值的计算与更新策略。

1. 计算方法

   InnoDB内部通过采样的方法计算cardinality：

   • 取B+树中所有叶子节点数量，记为A；
   • 随机从B+树中取8个叶子节点，统计每个页中不同记录数，记为P1,…,P8；
   • 根据采样信息，计算cardinality=（P1+P2+…+P8）*A/8

2. 更新策略

   • 表中1/16的数据已经发生过变化

     这种策略表示，自上一次统计Cardinality信息后，表中1/16数据已经发生过变化，这时需要更新。

   • stat_modified_counter > 2000000000

     这种策略考虑的是如果表中某一行数据频繁地发生变动超过若干次，进行更新。

   • 当执行一些SQL语句，比如ANALYZE TABLE，SHOW INDEX时会触发引擎去重新计算cardinality，因此当表中数据量很大时，这些命令执行速度较慢。

3 索引的使用

3.1 注意事项

1. 选择合适的字段创建索引

   • 不为NULL的字段
   • 被作为条件频繁查询的字段
   • 频繁需要排序的字段
   • 被经常用于连接的字段

2. 被频繁更新的字段，建立索引时要慎重

3. 尽可能考虑建立联合索引，而不是单列索引

   因为索引是占用磁盘空间的，索引过多，无论是维护、存储的代价都比价大。另外使用联合索引可能形成覆盖索引，提升查询性能。

4. 注意避免索引的冗余

   比如建立（a，b）联合索引，就不需要再给a单独建立索引了。

5. 在字符串类型的字段上使用索引，要考虑使用前缀索引

3.2 常见索引失效场景

1. 使用左或者左右模糊匹配

   因为B+树索引按照索引值有序排列，只能根据前缀进行比较。需要注意的是，如果要查询的数据都在二级索引里面（覆盖索引），此时也会用到索引查询。只不过此时索引查询方式为全树扫描，而不是范围查询。因为和全表扫描相比，扫描整个二级索引树代价更低。举个例子：select * from s where name like "%xxx"，表s中只有id和name字段，id为主键，name字段构建了二级索引。

2. 对索引字段使用函数，比如LENGTH函数等

   不过MySQL8.0之后增加了函数索引。

3. 对索引进行表达式计算，比如where id + 1 = 10

   如果将其改为where id = 10 - 1就可以走索引。

4. 对索引进行隐式类型转换

   如果索引字段是字符串类型， 但是在条件查询中，输入的参数是整型的话，你会在执行计划的结果发现这条语句会走全表扫描。 比如phone类型为varchar，查询语句select * from t_user where phone = 1300000001;会进行全表扫描。

   但是如果索引字段是整型类型，查询条件中的输入参数即使字符串，是不会导致索引失效，还是可以走索引扫描。

   因为 MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。

5. 联合索引非最左匹配

   建立联合索引（a，b，c），如果查询条件只包含b或c或bc，那么不会走索引。因为建立联合索引时，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。

6. where子句中的or

   在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。 因为or的两个条件只要满足1个即可，只有1个有索引没意义。

打赏