Index diving instance analysis for MySQL query performance optimization

Let’s start with a strange thing:

Let me create some data to reproduce the problem and create a user table:

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT 0 COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Through a batch of user ages, query the user information of this age, and check the SQL execution plan:

explain select * from user 
where age in (1,2,3,4,5,6,7,8,9);

where there are 9 in the condition Parameters, focus on the estimated number of scanned lines in the execution plan, which is 279 lines.

There is no problem here, the estimate is very accurate, it is actually 279 lines.

However, the problem arises. When we add another parameter to the where condition, it becomes 10 parameters. The estimated number of scanned rows should have increased. The result But it has been greatly reduced.

explain select * from user
where age in (1,2,3,4,5,6,7,8,9,10);

It was suddenly reduced to 30 lines, but what is the actual number of lines?

The actual number is 310 lines, and the estimated number of scanned lines is 30 lines. It’s really a grandma’s mistake.

What's going on with MySQL? Can it still be estimated?

If you can’t predict, find someone else!

Everyone must have been confused, until I went to the official website and saw a word, Index dive.

Related to this word, there is also a configuration parameter eq_range_index_dive_limit.

MySQL5.7.3For previous versions, this value defaulted to 10, and for later versions, this value defaulted to 200.

You can use the command to check the size of this value:

show variables like '%eq_range_index_dive_limit%';

Of course, we can also manually modify this value Size:

set eq_range_index_dive_limit=200;

The function of this eq_range_index_dive_limit configuration is:

When the number of parameters in the where statement in condition is less than this value, MySQL will use Index dive method estimates the number of scanned lines, which is very accurate.

When the number of parameters in the in condition of the where statement is greater than or equal to this value, MySQL uses another method Index statistics (Index statistics) The estimated number of scanned rows has a large error .

Why does MySQL do this?

We all use Index dive(Index dive) to estimate the number of scanned lines, isn't it good?

In fact, this is based on cost considerations. Index DivingThe estimated cost is relatively high and is suitable for small data volumes. Index statisticsThe estimation cost is low and suitable for large data volumes.

Under normal circumstances, the in condition of our where statement does not have too many parameters, so it is suitable to use index diving to estimate the number of scanned rows.

It is recommended that students who are still using the previous version of MySQL5.7.3 manually modify the index diving configuration parameters to appropriate values.

If the in condition in your project has up to 500 parameters, change the configuration parameter to 501.

In this way, MySQL estimates the number of scanned rows more accurately and can select a more appropriate index.

The above is the detailed content of Index diving instance analysis for MySQL query performance optimization. For more information, please follow other related articles on the PHP Chinese website!