Shulou(Shulou.com)05/31 Report--

This article shows you how to optimize mysql index coverage scanning, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Override scanning means that the results are scanned directly in the index and returned to the client, and there is no need to scan the results on the table according to the index. This scanning method is efficient. Override scan when Using index appears in the extra column

There is a sentence to optimize in the current production environment.

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

Execution takes 20 seconds. Take a look at the execution plan.

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)

Full table scan and Using filesort temporary file sorting are used; both create_day and remarks_format fields are indexed, but not indexed

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)

When you only do grouping statistics for create_day, you can see the index override scan Using index, which takes only 5 seconds to execute.

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)

When you only do grouping statistics for remarks_format, you can see the index override scan Using index that is also moving, and it only takes 4 seconds to execute.

It seems that only one combined index can be added.

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

After adding the index, take a look at the execution plan.

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)

At this point, the execution plan takes the index coverage scan Using index of the create_day_remarks_format index, but it still takes 20 seconds to execute. This may be related to the statistics, and the actual implementation plan is different from that of explain.

ANALYZE collects statistics

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)

It only takes more than 5 seconds to execute again and the result will be returned.

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

5.580s

Conclusion: the fields after select will not be scanned until they are in the same index.

The above is how to optimize mysql index coverage scanning. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.