Innodb的覆盖索引实践

站长

2024年03月19日 20:48 · 阅读数 4

前言

MySQL的索引分为6种级别，级别如下

system：系统表，少量数据，往往不需要进行磁盘IO
const：常量连接
eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
ref：非主键非唯一索引等值扫描
range：范围扫描
index：索引树扫描
ALL：全表扫描(full table scan)

6种级别中，type扫描方式由快到慢，system > const > eq_ref > ref > range > index > ALL

5.6X开始，现在的MySQL默认引擎是INNODB,innodb是索引组织结构，首先从主键去扫描，按照表的主键构造一颗B+树，非主键索引指向主键管理叶子节点中存放的就是整张表的行记录数据 主键索引又称为集簇索引，非主键索引称为辅助索引、二级索引。

主键索引的点查询【where 索引=XXX】隶属于const, 非主键索引的点查询则属于ref, 非主键索引一般情况总是要回表，那么二级索引有没有可能更快，有可能，那就是覆盖索引。

Innodb的覆盖索引实践

实验环境

单表数据集	数据结构	查询SQL
100万	主键、索引	sum\group by

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.34    |
+-----------+
1 row in set (0.01 sec)


mysql> select count(*) from tt2;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.37 sec)


其中c0和c1是同样的数据，
CREATE TABLE `tt2` (
  `c0` int(11) NOT NULL DEFAULT '0',
  `c1` int(11) NOT NULL DEFAULT '0',
  `c2` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `c3` double NOT NULL
)

主键查询



mysql> alter table  tt2 add primary key(c0);


      0.0169409732334316 |
|        0.24051994131878 |
+-------------------------+
1000000 rows in set, 1 warning (2.77 sec)

mysql> explain  select sql_no_cache sum(c3) from tt2     group by c0;
+----+-------------+-------+------------+-------+------------------------------------------------+---------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys                                  | key     | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------------+-------+------------------------------------------------+---------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | tt2   | NULL       | index | PRIMARY | PRIMARY | 4       | NULL | 997545 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+------------------------------------------------+---------+---------+------+--------+----------+-------+
1 row in set, 2 warnings (0.01 sec)

非主键索引

创建普通索引

mysql> create index c1_idx1 on tt2(c1);
Query OK, 0 rows affected (1.95 sec)
Records: 0  Duplicates: 0  Warnings: 0

Innodb的覆盖索引实践

正确的覆盖索引

创建覆盖索引

mysql> create index  c3_c1_idx on   tt2(c1,c3);
Query OK, 0 rows affected (3.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

Innodb的覆盖索引实践

错误的覆盖索引

同样创建覆盖索引，不过c1和c3的位置相反
mysql> create index  c1_c3_idx on   tt2(c3,c1);
Query OK, 0 rows affected (4.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Innodb的覆盖索引实践

错误的覆盖索引导致了filesort和temporary现象，filesort和temporary的技术原理如下。为什么会发生filesort和temporary，主要是innodb是索引组织的有序排序结构，不遵从它的玩法，内存管理上需要耗费额外多的性能。

using filesort

filesort主要用于查询数据结果集的排序操作，首先MySQL会使用sort_buffer_size大小的内存进行排序，如果结果集超过了sort_buffer_size大小，会把这一个排序后的chunk转移到file上，最后使用多路归并排序完成所有数据的排序操作。

MySQL filesort有两种使用模式:

模式1: sort的item保存了所需要的所有字段，排序完成后，没有必要再回表扫描。

模式2: sort的item仅包括，待排序完成后，根据rowid查询所需要的columns。

很明显，模式1能够极大的减少回表的随机IO。

using temporary

MySQL使用临时表保存临时的结构，以用于后续的处理，MySQL首先创建heap引擎的临时表，如果临时的数据过多，超过max_heap_table_size的大小，会自动把临时表转换成MyISAM引擎的表来使用。

从上面的解释上来看，filesort和temporary的使用场景的区别并不是很明显，不过，有以下的原则:

filesort只能应用在单个表上，如果有多个表的数据需要排序，那么MySQL会先使用using temporary保存临时数据，然后再在临时表上使用filesort进行排序，最后输出结果。

性能记录

主键查询(单位秒)	非主键索引	正确覆盖索引	错误覆盖索引
2.71	6.41	2.21	9.44

总结

主键索引未必是最快的，未必比非主键索引快，根据各种场景而定。
组合索引【覆盖索引】注意先后排序，否则使用了出现临时空间表、文件排序
MySQL的索引6种级别，我们做到ref就不错了，大部分的情况是index
主键索引是innodb的核心性能的标配，可以选配UUID或者递增ID，熟悉MySQL的人都会选配递增ID，因为只有递增ID才能发挥innodb的性能。递增ID能够使innodb有序存放数据，但是大部分业务场景，我们可能无法使用递增ID做索引，基于安全的需要也不能暴露主键ID。最后二级索引是常态，进一步的优化就是覆盖索引了。