MySQL系列——索引介绍

索引

• 索引（Index）是帮助 MySQL 高效获取数据的数据结构，所以说索引的本质是：数据结构
• 常见的索引模型其实有很多，哈希表、有序数组，各种搜索树都可以实现索引结构
• 索引的占用空间也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上

优势

• 索引大大减少了服务器需要扫描的数据量（提高数据检索效率）
• 索引可以帮助服务器避免排序和临时表（降低数据排序的成本，降低 CPU 的消耗）
• 索引可以将随机 I/O 变为顺序 I/O（降低数据库 IO 成本）

劣势

• 索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。 因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

索引分类

详见官网表格：dev.mysql.com/doc/refman/…

InnoDB 存储引擎索引特性

MyISAM存储引擎索引特性

MEMORY存储引擎索引特性

NDB存储引擎索引特性

从逻辑角度

• 主键索引：主键索引是一种特殊的唯一索引，不允许有空值
• 普通索引或者单列索引：每个索引只包含单个列，一个表可以有多个单列索引
• 多列索引（复合索引、联合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。
• 唯一索引或者非唯一索引
• Full-Text 全文索引：它查找的是文本中的关键词，而不是直接比较索引中的值
• 空间索引：空间索引是对空间数据类型的字段建立的索引

数据结构角度

• Hash 索引：主要就是通过 Hash 算法，将数据库字段数据转换成定长的 Hash 值，与这条数据的行指针一并存入 Hash 表的对应位置；如果发生 Hash 碰撞，则在对应 Hash 键下以链表形式存储。查询时，就再次对待查关键字再次执行相同的 Hash 算法，得到 Hash 值，到对应 Hash 表对应位置取出数据即可，Memory 引擎又是支持非唯一哈希索引的，如果发生 Hash 碰撞，会以链表的方式存放多个记录在同一哈希条目中。使用 Hash 索引的数据库并不多， 目前有 Memory 引擎和 NDB 引擎支持 Hash 索引。

  缺点是，只支持等值比较查询，像 = 、 in() 这种，不支持范围查找，比如 where id > 10 这种，也不能排序。

• B+ 树索引，详见MySQL 索引结构

从物理存储角度

• 聚集索引（clustered index）
• 非聚集索引（non-clustered index），也叫辅助索引（secondary index）

注意：

聚集索引和非聚集索引都是 B+ 树结构

索引策略

前缀索引

前缀索引其实就是对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引占用空间更小，所以查询更快，insert操作也会更快

• 可以为CHAR、VARCHAR、TEXT、VARBINARY创建索引前缀
• blob，text 或者很长的 varchar 列，必须使用前缀索引。如果将过于长的字段设置为索引，索引占用空间太大。
• 通常，前缀长度限制单位为字节数，但是在CREATE TABLE、ALTER TABLE 和 CREATE INDEX中索引长度的限制是根据字符串类型判断的。
  • 非二进制字符串类型（CHAR、VARCHAR、TEXT）使用的是字符数
  • 二进制字符串类型（BINARY、VARBINARY、BLOB）使用的是字节数

ALTER TABLE table_name ADD KEY(column_name(prefix_length));
ALTER TABLE table_name ADD index index_name(column_name(prefix_length));

创建前缀索引的关键就是要选择合适长度的前缀，即 prefix_length。前缀太短，选择性太低，前缀太长，索引占用空间太大。

比如上图中，两个不同的索引同样执行下面的语句

select id,name,email from user where email='abcdefg@sql.com' 

执行效果会有很大的差别，普通索引 idx_email 找到满足条件的记录后，再返回主键索引取出数据即可，而前缀索引会多次查到 abcde，然后返回主键索引取出数据进行对比，会扫描多次数据行。

如果前缀索引取前 6个字节构建的话 idx_pre_email(6)，就只需要扫描一行。

所以使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列进行比较。

前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有缺点：MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做『覆盖索引』。

函数索引

MySQL 8.0.13 及更高版本支持

也就是将表达式的值作为索引的内容，而不是列值或列值前缀

CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1))));
CREATE INDEX idx1 ON t1 ((col1 + col2));
CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);

多列索引可以同时包含非函数列和函数列。

函数索引支持ASC和DESC选项。

在索引定义中，需要将表达式放入括号之中，以便与列值索引或者前缀索引进行区分。例如，以下索引表达式使用了括号：

INDEX ((col1 + col2), (col3 – col4))

函数索引中的表达式不能使用列的前缀。可以使用 SUBSTRING() 和 CAST() 函数作为一个替代方案。

限制：

（1）主键不支持函数索引，因为主键以实际列进行存储，而函数索引是作为虚拟列存在的

（2）在有主键的情况下，唯一索引支持函数索引；但在无主键的情况下，被提升为主键的唯一索引不支持

（3）外键不支持函数索引

（4）空间索引和全文索引不支持函数索引

（5）函数索引不能直接使用列前缀，可以通过SUBSTRING()和CAST()来替代

（6）在删除列之前，要先删除相关的函数索引

覆盖索引

覆盖索引（Covering Index），也就是平时所说的不需要回表操作

• 就是 select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL 可以利用索引返回 select 列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
• 索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据，当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含（覆盖）满足查询结果的数据就叫做覆盖索引。

判断标准：使用explain，extra 列为using index

覆盖索引的利弊

好处：

• 避免Innodb表进行索引的二次查询（回表）
• Innodb是以聚集索引的顺序来存储的，对于Innodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果 是用二级索引查询数据，在查找到相应的键值后，还需通过主键进行二次查询才能获取我们真实所需要的数据。
• 在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了工0操作，提升了查询 效率。
• 可以把随机IO变成顺序IO加快查询效率
• 由于覆盖索引是按键值的II页序存储的，对于10密集型的范围查找来说，对比随机从磁盘读取每一行的数据10要少 的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的工0转变成索引查找的顺序工0。
• 由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

弊端：

• 索引字段的维护 总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或者称为业务数据架构师的工作。

联合索引

联合索引(concatenated index)：由多个列构成的索引，如CREATE INDEX idx_age_classid_name ON student(age,classId,name);则称idx_age_classid_name索引为联合索引。

在多个列上建立独立的单列索引大部分情况下并不能提高 MySQL 的查询性能。如SELECT * FROM student WHERE age=30 and classId=4;对于这个查询来说，两个单列索引都不不好的选择

版本说明：
MySQL 5.0 版本之前，MySQL 会对这个查询使用全表扫描，除非改写成两个查询 UNION 的方式
MySQL 5.0 及更高版本引入“索引合并”的策略（详见下一小节）

索引合并

索引合并能够同时使用两个单列索引进行扫描，并将结果合并。这种算法有三个变种：

• OR 条件的联合（union）
• AND 条件的相交（intersection）
• 组合前两种情况的联合及相交

索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建得很糟糕

1. 当出现服务器对多个索引做相交操作时（多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引
2. 当出现服务器对多个索引做联合操作时（多个OR条件），通常需要耗费大量的 CPU 和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候
3. 如果在 explain 中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。

最左前缀原则

在MySQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配

MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段的第1个字段时，联合索引不会被使用

因为B+树是按照从左至右的顺序建立搜索树的，如下图，比如当(20,4,zhangsan)这样的数据来检索时，B+ 树会优先比较 age，如果 20相同再依次比较 classid和 name，最后得到检索的数据；但当（4,张三）这样没有age的数据，B+树就不知道下一步该检索哪个节点，造成索引失效

注意
只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 N 个字符

索引下推（Index Condition Pushdown）

MySQL 5.6及更高版本支持

比如SELECT * FROM student WHERE name like 'zhangs%' and age=19 and sex='M';

因为最左前缀原则，age和sex的访问方法是all。只有name是有效索引

无ICP：

• 步骤一：查询出id为1和id为3的2条记录，其过滤条件只有name like 'zhangs%'
• 步骤二：带着过滤条件为age=19 and sex='M'进行回表查询2次。

有ICP：

• 步骤一：查询出id为1的唯一记录，其过滤条件为name like 'zhangs%' and age=19 and sex='M';
• 步骤二：带着唯一记录回表查询1次。

索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率

限制
当需要访问整个表行时，ICP 用于 range、ref、eq_ref 和 ref_or_null 访问方法
ICP 可用于 InnoDB 和 MyISAM 表，包括分区 InnoDB 和 MyISAM 表
对于 InnoDB 表，ICP 仅用于二级索引。 ICP 的目标是减少全行读取次数，从而减少 I/O 操作。对于 InnoDB 聚集索引，完整的记录已经读入 InnoDB 缓冲区。在这种情况下使用 ICP 不会减少 I/O
在虚拟生成列上创建的二级索引不支持 ICP。 InnoDB 支持虚拟生成列的二级索引
引用子查询的条件不能下推
引用存储功能的条件不能被按下。存储引擎不能调用存储的函数
触发条件不能下推
条件不能下推到包含对系统变量的引用的派生表（MySQL 8.0.30及更高版本）

使用索引扫描来做排序

MySQL 有两种方式可以生成有序的结果，通过排序操作或者按照索引顺序扫描，如果 explain 的 type 列的值为 index，则说明 MySQL 使用了索引扫描来做排序（不要和 extra 列的 Using index 搞混了，那个是使用了覆盖索引查询）。

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录，但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就回表查询一次对应的整行，这基本上都是随机 I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在 I/O 密集型的工作负载时。

MySQL 可以使用同一个索引既满足排序，又用于查找行，因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的。

只有当索引的列顺序和 order by 子句的顺序完全一致，并且所有列的排序方向（倒序或升序，创建索引时可以指定 ASC 或 DESC）都一样时，MySQL 才能使用索引来对结果做排序，如果查询需要关联多张表，则只有当 order by 子句引用的字段全部为第一个表时，才能使用索引做排序，order by 子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，否则 MySQL 都需要执行排序操作，而无法使用索引排序。

压缩（前缀压缩）索引

MyISAM 使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。默认只压缩字符串，但通过参数设置也可以对整数做压缩。

MyISAM 压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。

例如，索引块中的第一个值是perform，第二个值是performance，那么第二个值的前缀压缩后存储的是类似7,ance这样的形式。MyISAM 对行指针也采用类似的前缀压缩方式。

压缩块使用更少的空间，代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值，所以 MyISAM 查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度还不错，但是如果是倒序扫描——例如 ORDER BY DESC——就不是很好了。所有在块中查找某一行的操作平均都需要扫描半个索引块。

测试表明，对于 CPU 密集型应用，因为扫描需要随机查找，压缩索引使得 MyISAM 在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在 CPU 内存资源与磁盘之间做权衡。压缩索引可能只需要十分之一大小的磁盘空间，如果是 I/O 密集型应用，对某些查询带来的好处会比成本多很多。

可以在 CREATE TABLE 语句中指定 PACK_KEYS 参数来控制索引压缩的方式。

重复索引和冗余索引

MySQL 允许在相同列上创建多个索引，无论是有意的还是无意的。有意的用途没想明白~

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。

冗余索引和重复索引有一些不同。如果创建了索引(A,B)，再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。因此索引(A,B)也可以当做索引(A)来使用（这种冗余只是对 B-Tree 索引来说的）。但是如果再创建索引(B,A)，则不是冗余索引，索引(B)也不是，因为B不是索引(A,B)的最左前缀。另外，其他不同类型的索引（例如哈希索引或者全文索引）也不会是 B-Tree 索引的冗余索引，而无论覆盖的索引列是什么。

未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不使用的索引，这样的索引完全是累赘，建议考虑删除，有两个工具可以帮助定位未使用的索引：

1. 在 percona server 或者 mariadb 中先打开 userstat=ON 服务器变量，默认是关闭的，然后让服务器运行一段时间，再通过查询information_schema.index_statistics 就能查到每个索引的使用频率。
2. 使用 percona toolkit 中的 pt-index-usage 工具，该工具可以读取查询日志，并对日志中的每个查询进行explain 操作，然后打印出关于索引和查询的报告，这个工具不仅可以找出哪些索引是未使用的，还可以了解查询的执行计划。