覆盖索引性能一定比非覆盖索引快吗？

覆盖索引是一种常用的SQL优化手段，可以减去回表的开销，以提高SQL效率。对于同一个SQL，覆盖索引性能一定比非覆盖索引快吗？下面我们验证下这个问题：

1. 创建测试数据

use modb;

#测试表
drop table if exists cover_table;
CREATE TABLE `cover_table` (
  `id` int(11) NOT NULL auto_increment,
  `business_no` varchar(32) DEFAULT NULL,
  `complete_time` datetime DEFAULT NULL,
  `handle_result` varchar(64) DEFAULT NULL,
  `handle_status` varchar(4) DEFAULT NULL,
  `task_type` varchar(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#插入10万行记录
insert into cover_table(business_no)
select ''
from information_schema.columns a
cross join information_schema.columns b
limit 100000;

#填充测试数据
update cover_table
set business_no = concat('4c01f2e1-ab69-11ed', left('0000000',6-char_length(id)), id),
complete_time = date_add('1970-01-01', interval id hour),
handle_result = 'success',
handle_status = '2',
task_type = 'T3';

#创建两个索引
alter table cover_table
add index ix_complete_time_business_no(complete_time, business_no),  -- 组合索引
add index ix_complete_time_business_no_coverindex(complete_time, business_no,handle_result,handle_status,task_type);  -- 覆盖索引

2. 分析测试SQL

#要测试的SQL语句
select business_no
from cover_table
where complete_time >= '1980-01-01' and complete_time < '1981-01-01'
and handle_result = 'success'
and handle_status = '2'
and task_type != 'T3'
and (business_no like '%801%' or business_no like '%901%');

这是一个简单的SQL，优化思路如下：

• complete_time 如果数据量不大，可以考虑走索引范围扫描。
• business_no 由于是like ‘%%’，无法走索引。
• 其余的3个字段选择性低，无法走索引。
• 如果想进一步提升性能，可考虑创建5个字段的覆盖索引。

3. 优化器使用覆盖索引(默认)时的性能

#执行计划如下：
mysql> explain select business_no
    -> from cover_table
    -> where complete_time >= '1980-01-01' and complete_time < '1981-01-01'
    -> and handle_result = 'success'
    -> and handle_status = '2'
    -> and task_type != 'T3'
    -> and (business_no like '%801%' or business_no like '%901%');
+----+-------------+-------------+------------+-------+----------------------------------------------------------------------+-----------------------------------------+---------+------+-------+----------+--------------------------+
| id | select_type | table       | partitions | type  | possible_keys                                                        | key                                     | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-------------+------------+-------+----------------------------------------------------------------------+-----------------------------------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | cover_table | NULL       | range | ix_complete_time_business_no,ix_complete_time_business_no_coverindex | ix_complete_time_business_no_coverindex | 6       | NULL | 16940 |     0.19 | Using where; Using index |
+----+-------------+-------------+------------+-------+----------------------------------------------------------------------+-----------------------------------------+---------+------+-------+----------+--------------------------+

#实际执行时长0.10秒
mysql> select business_no
    -> from cover_table
    -> where complete_time >= '1980-01-01' and complete_time < '1981-01-01'
    -> and handle_result = 'success'
    -> and handle_status = '2'
    -> and task_type != 'T3'
    -> and (business_no like '%801%' or business_no like '%901%');
Empty set (0.10 sec)

#使用explain analyze跟踪实际执行时长0.50秒
mysql> explain analyze select business_no
    -> from cover_table
    -> where complete_time >= '1980-01-01' and complete_time < '1981-01-01'
    -> and handle_result = 'success'
    -> and handle_status = '2'
    -> and task_type != 'T3'
    -> and (business_no like '%801%' or business_no like '%901%');

-> Filter: ((cover_table.complete_time >= TIMESTAMP'1980-01-01 00:00:00') and (cover_table.complete_time < TIMESTAMP'1981-01-01 00:00:00') and (cover_table.handle_result = 'success') and (cover_table.handle_status = '2') and (cover_table.task_type <> 'T3') and ((cover_table.business_no like '%801%') or (cover_table.business_no like '%901%')))  (cost=4065.03 rows=32) (actual time=487.718..487.718 rows=0 loops=1)
    -> Covering index range scan on cover_table using ix_complete_time_business_no_coverindex over ('1980-01-01 00:00:00' <= complete_time < '1981-01-01 00:00:00')  (cost=4065.03 rows=16940) (actual time=0.337..274.214 rows=8784 loops=1)
1 row in set (0.50 sec)

覆盖索引的执行逻辑如下：

1. 进行覆盖索引范围扫描，范围区间为：(‘1980-01-01 00:00:00’ <= complete_time < ‘1981-01-01 00:00:00’)，共计：8784条记录。
2. 一边扫描，一边对比where中的所有条件：(cover_table.complete_time >= TIMESTAMP’1980-01-01 00:00:00’) and (cover_table.complete_time < TIMESTAMP’1981-01-01 00:00:00’) and (cover_table.handle_result = ‘success’) and (cover_table.handle_status = ‘2’) and (cover_table.task_type <> ‘T3’) and ((cover_table.business_no like ‘%801%’) or (cover_table.business_no like ‘%901%’))。
3. 把最终符合条件的记录，返回到server层，共计：0条记录。

4. 优化器使用非覆盖索引时的性能

#执行计划如下：
mysql> explain
    -> select business_no
    -> from cover_table force index(ix_complete_time_business_no)
    -> where complete_time >= '1980-01-01' and complete_time < '1981-01-01'
    -> and handle_result = 'success'
    -> and handle_status = '2'
    -> and task_type != 'T3'
    -> and (business_no like '%801%' or business_no like '%901%');
+----+-------------+-------------+------------+-------+------------------------------+------------------------------+---------+------+-------+----------+------------------------------------+
| id | select_type | table       | partitions | type  | possible_keys                | key                          | key_len | ref  | rows  | filtered | Extra                              |
+----+-------------+-------------+------------+-------+------------------------------+------------------------------+---------+------+-------+----------+------------------------------------+
|  1 | SIMPLE      | cover_table | NULL       | range | ix_complete_time_business_no | ix_complete_time_business_no | 6       | NULL | 16674 |     0.19 | Using index condition; Using where |
+----+-------------+-------------+------------+-------+------------------------------+------------------------------+---------+------+-------+----------+------------------------------------+

#索引下推(ICP)，默认开启
SET optimizer_switch = 'index_condition_pushdown=on'; 

#实际执行时长0.05秒
mysql> select business_no
    -> from cover_table force index(ix_complete_time_business_no)
    -> where complete_time >= '1980-01-01' and complete_time < '1981-01-01'
    -> and handle_result = 'success'
    -> and handle_status = '2'
    -> and task_type != 'T3'
    -> and (business_no like '%801%' or business_no like '%901%');
Empty set (0.05 sec)

#使用explain analyze跟踪实际执行时长0.07秒
mysql> explain analyze select business_no
    -> from cover_table force index(ix_complete_time_business_no)
    -> where complete_time >= '1980-01-01' and complete_time < '1981-01-01'
    -> and handle_result = 'success'
    -> and handle_status = '2'
    -> and task_type != 'T3'
    -> and (business_no like '%801%' or business_no like '%901%');

-> Filter: ((cover_table.handle_result = 'success') and (cover_table.handle_status = '2') and (cover_table.task_type <> 'T3'))  (cost=7503.56 rows=31) (actual time=56.960..56.960 rows=0 loops=1)
    -> Index range scan on cover_table using ix_complete_time_business_no over ('1980-01-01 00:00:00' <= complete_time < '1981-01-01 00:00:00'), with index condition: ((cover_table.complete_time >= TIMESTAMP'1980-01-01 00:00:00') and (cover_table.complete_time < TIMESTAMP'1981-01-01 00:00:00') and ((cover_table.business_no like '%801%') or (cover_table.business_no like '%901%')))  (cost=7503.56 rows=16674) (actual time=0.852..52.981 rows=138 loops=1)
1 row in set (0.07 sec)

非覆盖索引的执行逻辑如下：

1. 进行索引范围扫描，范围区间为：(‘1980-01-01 00:00:00’ <= complete_time < ‘1981-01-01 00:00:00’)，同时使用索引下推，把((cover_table.business_no like ‘%801%’) or (cover_table.business_no like ‘%901%’))条件传给存储引擎，最终返回138条记录。
2. 回表，并过滤where中的剩余条件：((cover_table.handle_result = ‘success’) and (cover_table.handle_status = ‘2’) and (cover_table.task_type <> ‘T3’))。
3. 把最终符合条件的记录，返回到server层，共计：0条记录。

总结：

1. 覆盖索引不一定是最快的优化方法，且维护成本较大。
2. 如果某些条件组合，选择性较好，引导其走索引下推，性能更好。此时回表的成本可忽略不计。
3. 当SQL使用覆盖索引时，不支持索引下推优化方法。所以，不要滥用覆盖索引。