MySQL Handler變數解析

壹頁書發表於2014-05-12
MySql變數
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. );

  8. INSERT INTO test
  9. VALUES (NULL, 'abc', NOW()), (NULL, 'abc', NOW()), (NULL, 'abd', NOW())
  10.      , (NULL, 'acd', NOW()), (NULL, 'def', NOW()), (NULL, 'pqr', NOW())
  11.      , (NULL, 'stu', NOW()), (NULL, 'vwx', NOW()), (NULL, 'yza', NOW())
  12.      , (NULL, 'def', NOW())
  13. ;

  15. SELECT * FROM test;
  16. +----+------+---------------------+
  17. | id | data | ts                  |
  18. +----+------+---------------------+
  19. | 1  | abc  | 2008-01-18 16:28:40 |
  20. | 2  | abc  | 2008-01-18 16:28:40 |
  21. | 3  | abd  | 2008-01-18 16:28:40 |
  22. | 4  | acd  | 2008-01-18 16:28:40 |
  23. | 5  | def  | 2008-01-18 16:28:40 |
  24. | 6  | pqr  | 2008-01-18 16:28:40 |
  25. | 7  | stu  | 2008-01-18 16:28:40 |
  26. | 8  | vwx  | 2008-01-18 16:28:40 |
  27. | 9  | yza  | 2008-01-18 16:28:40 |
  28. | 10 | def  | 2008-01-18 16:28:40 |
  29. +----+------+---------------------+

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.


可以看到全表掃描其實也是走了key,可能是因為索引組織表的原因。因為limit 2 所以rnd_next為2.這個Stop Key在執行計劃中是看不出來的。


使用索引消除排序，因為是升序，所以read first為1,由於limit 4,所以read_next為3.通過這個也可以看出Stop Key.


也是使用索引消除排序，因為是倒序，所以read_last為1,read_prev為2.因為往回讀了兩個key.


  1. ALTER TABLE test ADD COLUMN file_sort text;

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

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


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1159014/，如需轉載，請註明出處，否則將追究法律責任。

相關文章