技术分享 | MySQL 覆盖索引优化案例一则

SELECT c1,
       SUM(CASE WHEN c2=0 THEN 1 ELSE 0 END) as folders,
       SUM(CASE WHEN c2=1 THEN 1 ELSE 0 END) as files,
       SUM(c3)
FROM  test
GROUP BY c1;

复制

为了更好地说明，创建一张测试表，主键字段是id，除了c1、c2、c3字段外，还有其他字段，有很多索引，但和c1、c2、c3相关的，只是idx_test_01，c1作为前导列的复合索引，且c2和c3不在索引中，

CREATE TABLE test (
  id bigint(20) not null,
  c1 varchar(64) collate utf8_bin not null,
  c2 tinyint(4) not null,
  c3 bigint(20) default null,
  ...
  primary key(id),
  key idx_test_01(c1, ...)
  key ...
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

复制

显而易见，如上SQL执行时，能用到的索引就只有idx_test_01，Extra是NULL，

+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | index | idx_test_01   | idx_test_01 | 206     | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

复制

create index idx_test_02(c1, c2, c3) on test;

复制

此时执行SQL，Extra显示Using index，说明用到了覆盖索引的特性，

+----+-------------+-------+------------+-------+-------------------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys           | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+-------------------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | index | idx_test_01,idx_test_02 | idx_test_02 | 204     | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+-------------------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

复制