mysql索引覆蓋掃描優化

orclwujian發表於2016-12-28
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後面的欄位在同一個索引中才會走索引覆蓋掃描

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29989552/viewspace-2131546/，如需轉載，請註明出處，否則將追究法律責任。

相關文章