覆盖索引 covering index

2018-01-30 2492

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

云数据库 RDS MySQL Serverless，价值2615元额度，1个月

简介： 覆盖索引定义应用场景测试

简介

覆盖索引是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