生产环境MySQL索引时效的排查过程生产环境MySQL索引时效的排查过

早上收到开发同学求助，有个SQL查询耗时特别长，看了执行计划发现没有走索引，但是不知道原因在哪里，我们一起来分析一下。

mysql>explain SELECT * FROM artisan_income WHERE parent_id IN (222645481,222583953,222181775,222180931,222081126,221678753,221616102,221591783,221219312,221195482,221118672,220763129,220654289,220633930,220323633,220227641,219825564,219720338,219321345, 219291958 ) \G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: artisan_incomepartitions: type: ALLpossible_keys: idx_parent_idkey: key_len: ref: rows: 20711352filtered: 100Extra: Using where

确实是全表扫描，带着疑问我们把生产环境数据同步到测试库，方便测试，然后在测试环境进行查询。

mysql>explain SELECT * FROM artisan_income WHERE parent_id IN (222645481,222583953,222181775,222180931,222081126,221678753,221616102,221591783,221219312,221195482,221118672,220763129,220654289,220633930,220323633,220227641,219825564,219720338,219321345, 219291958 ) \G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: artisan_incomepartitions: type: rangepossible_keys: idx_parent_idkey: idx_parent_idkey_len: 5ref: rows: 1870780filtered: 100Extra: Using index condition

【生产环境MySQL索引时效的排查过程】发现在测试环境就用到了parent_id字段的索引，生产库和测试库同样都是5.7的版本，数据也几乎一样，但是执行计划不一样，第一时间想到了统计信息的问题，于是进行analyze table

analyze table artisan_income;

然后查看执行计划：

mysql>explain SELECT * FROM artisan_income WHERE parent_id IN (222645481,222583953,222181775,222180931,222081126,221678753,221616102,221591783,221219312,221195482,221118672,220763129,220654289,220633930,220323633,220227641,219825564,219720338,219321345, 219291958 ) \G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: artisan_incomepartitions: type: rangepossible_keys: idx_parent_idkey: idx_parent_idkey_len: 5ref: rows: 1901880filtered: 100Extra: Using index condition

发现执行计划已经恢复正常。

mysql>SELECT * FROM artisan_income WHERE parent_id IN (222645481,222583953,222181775,222180931,222081126,221678753,221616102,221591783,221219312,221195482,221118672,220763129,220654289,220633930,220323633,220227641,219825564,219720338,219321345, 219291958 ) \G返回行数：[0]，耗时：2 ms.

到此这篇关于生产环境MySQL索引时效的排查过程的文章就介绍到这了,更多相关MySQL索引排查内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！