MySQL Handler变量解析

时间：2022-06-03 18:47:39

http://blog.itpub.net/29254281/viewspace-1159014/

To see the effect of a query do the following steps:

FLUSH STATUS;
Execute the query
SHOW SESSION STATUS LIKE 'handler_read%';
Do an EXPLAIN of the query

实验数据初始化:

  1. CREATE TABLE test (
  2. id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
  3. , data VARCHAR(32)
  4. , ts TIMESTAMP
  5. , INDEX (data)
  6. );
  7. INSERT INTO test
  8. VALUES (NULL, 'abc', NOW()), (NULL, 'abc', NOW()), (NULL, 'abd', NOW())
  9. , (NULL, 'acd', NOW()), (NULL, 'def', NOW()), (NULL, 'pqr', NOW())
  10. , (NULL, 'stu', NOW()), (NULL, 'vwx', NOW()), (NULL, 'yza', NOW())
  11. , (NULL, 'def', NOW())
  12. ;
  13. SELECT * FROM test;
  14. +----+------+---------------------+
  15. | id | data | ts                  |
  16. +----+------+---------------------+
  17. | 1  | abc  | 2008-01-18 16:28:40 |
  18. | 2  | abc  | 2008-01-18 16:28:40 |
  19. | 3  | abd  | 2008-01-18 16:28:40 |
  20. | 4  | acd  | 2008-01-18 16:28:40 |
  21. | 5  | def  | 2008-01-18 16:28:40 |
  22. | 6  | pqr  | 2008-01-18 16:28:40 |
  23. | 7  | stu  | 2008-01-18 16:28:40 |
  24. | 8  | vwx  | 2008-01-18 16:28:40 |
  25. | 9  | yza  | 2008-01-18 16:28:40 |
  26. | 10 | def  | 2008-01-18 16:28:40 |
  27. +----+------+---------------------+

Handler_read_first
全索引扫描的次数
The number of times the first entry was read from an index. 
If this value is high, it suggests that the server is doing a lot of full index scans.

Handler_read_key
走索引的次数
The number of requests to read a row based on a key. 
If this value is high, it is a good indication that your tables are properly indexed for your queries.

HANDLER_READ_NEXT
The number of requests to read the next row in key order. 
This value is incremented if you are querying an index column 
with a range constraint or if you are doing an index scan.


Handler_read_prev
The number of requests to read the previous row in key order. 
This read method is mainly used to optimize ORDER BY ... DESC.

Handler_read_rnd
文件排序或者没有使用索引
The number of requests to read a row based on a fixed position. 
This value is high if you are doing a lot of queries that require sorting of the result. 
You probably have a lot of queries that require MySQL to scan entire tables or you have joins that don't use keys properly.

Handler_read_rnd_next
此选项表明在进行数据文件扫描时，从数据文件里取数据的次数。
The number of requests to read the next row in the data file. 
This value is high if you are doing a lot of table scans. 
Generally this suggests that your tables are not properly indexed 
or that your queries are not written to take advantage of the indexes you have.

MySQL Handler变量解析

可以看到全表扫描其实也是走了key,可能是因为索引组织表的原因。因为limit 2 所以rnd_next为2.这个Stop Key在执行计划中是看不出来的。

MySQL Handler变量解析

使用索引消除排序，因为是升序，所以read first为1,由于limit 4,所以read_next为3.通过这个也可以看出Stop Key.

MySQL Handler变量解析

也是使用索引消除排序，因为是倒序，所以read_last为1,read_prev为2.因为往回读了两个key.

  1. ALTER TABLE test ADD COLUMN file_sort text;
  2. UPDATE test SET file_sort = 'abcdefghijklmnopqrstuvwxyz' WHERE id = 1;
  3. UPDATE test SET file_sort = 'bcdefghijklmnopqrstuvwxyza' WHERE id = 2;
  4. UPDATE test SET file_sort = 'cdefghijklmnopqrstuvwxyzab' WHERE id = 3;
  5. UPDATE test SET file_sort = 'defghijklmnopqrstuvwxyzabc' WHERE id = 4;
  6. UPDATE test SET file_sort = 'efghijklmnopqrstuvwxyzabcd' WHERE id = 5;
  7. UPDATE test SET file_sort = 'fghijklmnopqrstuvwxyzabcde' WHERE id = 6;
  8. UPDATE test SET file_sort = 'ghijklmnopqrstuvwxyzabcdef' WHERE id = 7;
  9. UPDATE test SET file_sort = 'hijklmnopqrstuvwxyzabcdefg' WHERE id = 8;
  10. UPDATE test SET file_sort = 'ijklmnopqrstuvwxyzabcdefgh' WHERE id = 9;
  11. UPDATE test SET file_sort = 'jklmnopqrstuvwxyzabcdefghi' WHERE id = 10;

MySQL Handler变量解析

Handler_read_rnd为4 说明没有使用索引
rnd_next为11说明扫描了所有的数据
但是read first和read key的数据，不能解释.不知道为什么会是这个数据
read key总是read_rnd+1

参考:
http://www.fromdual.com/mysql-handler-read-status-variables
http://hi.baidu.com/thinkinginlamp/item/8d038333c6b0674a3075a1d3

 

相关文章