怎么进行mysql索引覆盖扫描优化

覆盖扫描即直接在索引中扫描出结果返回给客户端，不需要根据索引再去表上扫描结果，这种扫描方式效率高。当extra列出现Using index时即为覆盖扫描

现生产环境有个语句要优化，

select create_day,remarks_format,count(*) from CDM.cdm_account_itemized GROUP BY create_day,remarks_format;

执行需要20秒，看下执行计划

mysql> explain select create_day,remarks_format,count(*) from CDM.cdm_account_itemized GROUP BY create_day,remarks_format;

| id | select_type | table| type | possible_keys | key | key_len | ref | rows| Extra |

| 1 | SIMPLE | cdm_account_itemized | ALL | NULL | NULL | NULL| NULL | 10123349 | Using temporary; Using filesort |

1 row in set (0.00 sec)

走了全表扫描并使用了Using filesort临时文件排序；create_day和remarks_format 字段都是有索引的，但并没有走索引

mysql> explain select create_day,count(*) from CDM.cdm_account_itemized GROUP BY create_day ;

| id | select_type | table| type | possible_keys | key| key_len | ref | rows| Extra |

| 1 | SIMPLE | cdm_account_itemized | index | biz_account_itemized_create_day | biz_account_itemized_create_day | 25 | NULL | 10123349 | Using index |

+----+-------------+----------------------+-------+---------------------------------+---------------------------------+---------+------+----------+-------------+

1 row in set (0.00 sec)

只针对create_day进行分组统计的时候可以看到走的索引覆盖扫描Using index，执行只要5秒

mysql> explain select remarks_format,count(*) from CDM.cdm_account_itemized GROUP BY remarks_format;

| id | select_type | table| type | possible_keys | key| key_len | ref | rows| Extra |

| 1 | SIMPLE | cdm_account_itemized | index | biz_account_itemized_create_day | biz_account_itemized_create_day | 25 | NULL | 10123349 | Using index |

1 row in set (0.00 sec)

只针对 remarks_format进行分组统计的时候可以看到也走的索引覆盖扫描Using index，执行只要4秒

看样子只能增加个组合索引了

mysql> alter table CDM.cdm_account_itemized add index create_day_remarks_format(create_day,remarks_format)

加完索引再看下执行计划

mysql> explain select create_day,remarks_format,count(*) from CDM.cdm_account_itemized GROUP BY create_day,remarks_format;

| id | select_type | table| type | possible_keys| key | key_len | ref | rows| Extra |

| 1 | SIMPLE | cdm_account_itemized | index | create_day_remarks_format | create_day_remarks_format | 793| NULL | 10123349 | Using index |

1 row in set (0.00 sec)

这个时候执行计划走的是create_day_remarks_format索引的索引覆盖扫描Using index，但是执行还是需要20秒。这可能和统计信息有关，实际的执行计划和explain出来的不一样

ANALYZE收集下统计信息

mysql> ANALYZE table CDM.cdm_account_itemized;

| Table| Op | Msg_type | Msg_text |

| CDM.cdm_account_itemized | analyze | status | OK |

1 row in set (1.64 sec)

再次执行只要5秒多就返回结果了

mysql> select create_day,remarks_format,count(*) from CDM.cdm_account_itemized GROUP BY create_day,remarks_format;

5.580s
结论：select后面的字段在同一个索引中才会走索引覆盖扫描