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

How to understand the relevant state parameters of MySQL handler, I believe that many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Overview

MySQL has a mysterious HANDLER command "since ancient times", which is not the standard syntax of SQL and can reduce the cost of parsing and optimizing SQL statements by the optimizer, thus improving query performance.

1. Handler parameter list

Mysql > show global status like 'Handle%'

The parameters are described as follows:

Second, several parameters that are more important in practical optimization.

1. Handler_read_first and Handler_read_rnd_next

The former indicates the number of full index scans, and the current value is larger, indicating that it may be a full index scan. In addition, walking through the whole table may also cause this value to be relatively large; the latter indicates the number of times the data is fetched from the data file when scanning the data file. When the latter value is large, it means that a large number of rows are scanned and the index may not be used properly.

2. Handler_read_key

This indicates the number of times to walk through the index. If this value is large, it means that the index is used well.

Third, experimental demonstration

1. Prepare data

CREATE TABLE test (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, DATA VARCHAR (32), ts TIMESTAMP, INDEX (DATA)) INSERT INTO test VALUES (NULL, 'abc', NOW ()), (NULL,' abc', NOW ()), (NULL, 'abd', NOW ()), (NULL,' acd', NOW ()), (NULL, 'def', NOW ()), (NULL,' pqr', NOW ()), (NULL, 'stu', NOW ()) (NULL, 'vwx', NOW ()), (NULL,' yza', NOW ()), (NULL, 'def', NOW ())

2. Limit 2 to observe Handler_read_first, Handler_read_rnd_next, Handler_read_key

FLUSH STATUS; select * from test limit 2; SHOW SESSION STATUS LIKE 'handler_read%'; explain select * from test limit 2

You can see that the full table scan actually goes key (Handler_read_key=1), probably because the index organizes the table. Because of limit 2, the rnd_next is 2. This Stop Key is not visible in the implementation plan.

3. The index eliminates sorting (ascending order) and only walks the index.

FLUSH STATUS; select data from test order by data limit 4; SHOW SESSION STATUS LIKE 'handler_read%'; explain select data from test order by data limit 4

Use the index to eliminate sorting, because it is an ascending order, so read first is 1, because limit 4, so read_next is 3, because only take from the index, not from the data file, so rnd_next is 0, the index can be seen through this Stop Key.

4. Index elimination sort (reverse order)

FLUSH STATUS; select data from test order by data desc limit 3; SHOW SESSION STATUS LIKE 'handler_read%'; explain select data from test order by data desc limit 3

Use the index to eliminate sorting, because it is in reverse order, so the read_last is 1 and the prev is 2. 5. Because I read back two key.

5. No index is used

ALTER TABLE test ADD COLUMN file_sort text; UPDATE test SET file_sort = 'abcdefghijklmnopqrstuvwxyz' WHERE id = 1; UPDATE test SET file_sort =' bcdefghijklmnopqrstuvwxyza' WHERE id = 2; UPDATE test SET file_sort = 'cdefghijklmnopqrstuvwxyzab' WHERE id = 3; UPDATE test SET file_sort =' defghijklmnopqrstuvwxyzabc' WHERE id = 4; UPDATE test SET file_sort = 'efghijklmnopqrstuvwxyzabcd' WHERE id = 5; UPDATE test SET file_sort =' fghijklmnopqrstuvwxyzabcde' WHERE id = 6; UPDATE test SET file_sort = 'ghijklmnopqrstuvwxyzabcdef' WHERE id = 7; UPDATE test SET file_sort =' hijklmnopqrstuvwxyzabcdefg' WHERE id = 8 UPDATE test SET file_sort = 'ijklmnopqrstuvwxyzabcdefgh' WHERE id = 9; UPDATE test SET file_sort =' jklmnopqrstuvwxyzabcdefghi' WHERE id = 10; FLUSH STATUS; select * from test order by file_sort limit 4; SHOW SESSION STATUS LIKE 'handler_read%'; explain select * from test order by file_sort limit 4

Handler_read_rnd for 4 means no index is used, rnd_next for 11 means all data is scanned, and read key is always read_rnd+1.

After reading the above, have you mastered how to understand the relevant state parameters of MySQL handler? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.