Shulou(Shulou.com)06/01 Report--

Today, I would like to share with you the relevant knowledge of what is the limitation of the MySQL index in the production environment. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article. Let's take a look.

Question:

Mysql > explain SELECT * FROM artisan_income WHERE parent_id IN (222645481, 222583953, 222181775, 222180931, 222081126,221678753,221616102,221591783,221219312221195482 221118672, 220763129, 220654289, 220633930, 220323633, 220227641, 219825564, 219720338, 219321345 219291958)\ ALLpossible_keys * 1. Row * * id: 1 select_type: SIMPLE table: artisan_income partitions: type: ALLpossible_keys: idx_parent_id key: key _ len: ref: rows: 20711352 filtered: 100 Extra: Using where

It is indeed a full table scan, with doubt, we synchronize the production environment data to the test library to facilitate testing, and then query in the test environment.

Mysql > explain SELECT * FROM artisan_income WHERE parent_id IN (222645481, 222583953, 222181775, 222180931, 222081126,221678753,221616102,221591783,221219312221195482 221118672, 220763129, 220654289, 220633930, 220323633, 220227641, 219825564, 219720338, 219321345 219291958)\ rangepossible_keys * 1. Row * * id: 1 select_type: SIMPLE table: artisan_income partitions: type: rangepossible_keys: idx_parent_id key: idx_parent_ Id key_len: 5 ref: rows: 1870780 filtered: 100 Extra: Using index condition

It is found that the index of the parent_id field is used in the test environment, the production library and the test library are both version 5.7, and the data is almost the same, but the execution plan is different, so I immediately thought of the problem of statistical information, so analyze table was carried out.

Analyze table artisan_income

Then view the execution plan:

Mysql > explain SELECT * FROM artisan_income WHERE parent_id IN (222645481, 222583953, 222181775, 222180931, 222081126,221678753,221616102,221591783,221219312221195482 221118672, 220763129, 220654289, 220633930, 220323633, 220227641, 219825564, 219720338, 219321345 219291958)\ rangepossible_keys * 1. Row * * id: 1 select_type: SIMPLE table: artisan_income partitions: type: rangepossible_keys: idx_parent_id key: idx_parent_ Id key_len: 5 ref: rows: 1901880 filtered: 100 Extra: Using index condition

It is found that the implementation plan has returned to normal.

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 number of rows returned: [0] Time: 2 ms. These are all the contents of this article entitled "what is the troubleshooting process of MySQL Index validity in production Environment?" Thank you for your reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to 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.