覆盖索引 covering index-CSDN博客

简介

覆盖索引是InnoDB中索引的特例，索引中包含查询的所有必填字段;换句话说，索引本身包含执行查询所需的数据，而不必执行额外的读取。

请注意，在InnoDB中，数据是使用主键索引存储的。换句话说，数据通过主键索引物理地存储为B +树。因此，主键被包括在所有二级索引的叶节点中以便建立从二级索引到实际数据行的引用。

因此，任何涉及二级索引的查找都必须从根节点通过分支节点导航到正确的叶节点以获取主键值，然后对主键索引执行随机IO读取（再次从根节点通过分支节点到正确的叶节点）来获取数据行。

使用覆盖索引，我们避免了在主键索引（一个B +树较少遍历，避免昂贵的IO操作）上的这种额外的随机IO读取以获取数据行，因为查询所需的所有字段都包括在覆盖索引中。

什么时候使用覆盖索引

避免大表额外的IO读取。有时甚至可以避免额外的物理随机IO读取（最昂贵的IO操作）。
我们可以使用这个特定的索引进行查询，需要：

过滤符合一定条件的行（WHERE子句）
分组数据（GROUP BY子句）
按覆盖索引（ORDER BY子句）的顺序排序数据
投影数据（SELECT子句）

测试

创建测试表

CREATE TABLE big_table(

  id int primary key auto_increment,

  field01 int,

  field02 int,

  field03 varchar(50)
) engine=innodb;

循环插入数据1kw数据

insert into big_table( field01, field02, field03) SELECT FLOOR(RAND() * 10000),FLOOR(RAND() * 10000),MD5(RAND() * 1000);

测试SQL：

select sum(field02) from big_table  group by field01 limit 10;

不建立索引情况下耗时

mysql> show index from big_table;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| big_table |          0 | PRIMARY  |            1 | id          | A         |     8941508 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> explain select sum(field02) from big_table  group by field01 limit 10;
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                           |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------+
|  1 | SIMPLE      | big_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9294102 |   100.00 | Using temporary; Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select sum(field02) from big_table  group by field01 limit 10;
+--------------+
| sum(field02) |
+--------------+
|  47205571014 |
|        68956 |
|       100295 |
|        83829 |
|        71500 |
|        87253 |
|        66426 |
|        59674 |
|        62528 |
|        51620 |
+--------------+
10 rows in set (4.19 sec)

建立单个field01及field02索引

mysql> create index idx_f2 on big_table(field02);
Query OK, 0 rows affected (18.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_f1 on big_table(field01);
Query OK, 0 rows affected (13.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  show index from big_table;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| big_table |          0 | PRIMARY  |            1 | id          | A         |     8941508 |     NULL | NULL   |      | BTREE      |         |               |
| big_table |          1 | idx_f2   |            1 | field02     | A         |       10669 |     NULL | NULL   | YES  | BTREE      |         |               |
| big_table |          1 | idx_f1   |            1 | field01     | A         |       10206 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> explain  select sum(field02) from big_table  group by field01 limit 10;
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-------+
|  1 | SIMPLE      | big_table | NULL       | index | idx_f1        | idx_f1 | 5       | NULL | 9106 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select sum(field02) from big_table  group by field01 limit 10;
+--------------+
| sum(field02) |
+--------------+
|  47205571014 |
|        68956 |
|       100295 |
|        83829 |
|        71500 |
|        87253 |
|        66426 |
|        59674 |
|        62528 |
|        51620 |
+--------------+
10 rows in set (16.86 sec)

有没有发现建立索引的情况下反而比全表扫描慢很多，读者可以自己去思考。其实本质上这种类型的sql全表扫描是一种比较快速的方式

建立联合索引covering index

mysql> create index idx_f12 on big_table(field01,field02);
Query OK, 0 rows affected (24.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from big_table;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| big_table |          0 | PRIMARY  |            1 | id          | A         |     8941508 |     NULL | NULL   |      | BTREE      |         |               |
| big_table |          1 | idx_f12  |            1 | field01     | A         |       10019 |     NULL | NULL   | YES  | BTREE      |         |               |
| big_table |          1 | idx_f12  |            2 | field02     | A         |      328573 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> explain select sum(field02) from big_table  group by field01 limit 10;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | big_table | NULL       | index | idx_f12       | idx_f12 | 10      | NULL |   10 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select sum(field02) from big_table  group by field01 limit 10;
+--------------+
| sum(field02) |
+--------------+
|  47205571014 |
|        68956 |
|       100295 |
|        83829 |
|        71500 |
|        87253 |
|        66426 |
|        59674 |
|        62528 |
|        51620 |
+--------------+
10 rows in set (2.04 sec)

比不加索引的情况下快了一倍左右。

参考

link