MySQL Handler變數解析
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
實驗資料初始化:
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_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.
Handler_read_rnd為4 說明沒有使用索引
rnd_next為11說明掃描了所有的資料
但是read first和read key的資料,不能解釋.不知道為什麼會是這個資料
read key總是read_rnd+1
參考:
FLUSH STATUS;
Execute the query
SHOW SESSION STATUS LIKE 'handler_read%';
Do an EXPLAIN of the query
實驗資料初始化:
-
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())
-
;
-
-
SELECT * FROM test;
-
+----+------+---------------------+
-
| id | data | ts |
-
+----+------+---------------------+
-
| 1 | abc | 2008-01-18 16:28:40 |
-
| 2 | abc | 2008-01-18 16:28:40 |
-
| 3 | abd | 2008-01-18 16:28:40 |
-
| 4 | acd | 2008-01-18 16:28:40 |
-
| 5 | def | 2008-01-18 16:28:40 |
-
| 6 | pqr | 2008-01-18 16:28:40 |
-
| 7 | stu | 2008-01-18 16:28:40 |
-
| 8 | vwx | 2008-01-18 16:28:40 |
-
| 9 | yza | 2008-01-18 16:28:40 |
-
| 10 | def | 2008-01-18 16:28:40 |
- +----+------+---------------------+
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.
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.
-
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;
Handler_read_rnd為4 說明沒有使用索引
rnd_next為11說明掃描了所有的資料
但是read first和read key的資料,不能解釋.不知道為什麼會是這個資料
read key總是read_rnd+1
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29254281/viewspace-1159014/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Handler解析
- Handler訊息機制完全解析Handler解析
- Handler全家桶之 —— Handler 原始碼解析原始碼
- MYSQL: Handler_read_%引數說明MySql
- Handler機制解析
- Handler原始碼解析原始碼
- MySQL:Innodb Handler_read_*引數解釋MySql
- MySQL handler相關狀態引數解釋MySql
- Handler系列原始碼解析原始碼
- MySQL 之變數MySql變數
- Android Handler 原始碼解析Android原始碼
- MySQL變數的使用MySql變數
- mysql 變數說明MySql變數
- Android 8.1 Handler 原始碼解析Android原始碼
- 【勝通 】mysql的引數變數MySql變數
- mysql handler語句之一MySql
- MySQL之Handler_read_*MySql
- MYSQL使用記錄之:handlerMySql
- MySQL常用內建變數MySql變數
- (10)mysql 中的變數MySql變數
- MySQL 環境變數配置MySql變數
- MySQL 變數和條件MySql變數
- Mysql變數宣告的方式MySql變數
- MySQL中的@變數名MySql變數
- C# 變數初始化解析C#變數
- C/C++ 靜態變數解析C++變數
- MariaDB/MySQL中的變數MySql變數
- MySQL 持久化系統變數MySql持久化變數
- 《MySQL 入門教程》第 17 篇 MySQL 變數MySql變數
- Android原始碼解析Handler系列第(四)篇 --- 打破Handler那些困惑事兒Android原始碼
- MySQL中變數的定義和變數的賦值使用MySql變數賦值
- 不重啟mysql情況修改引數變數MySql變數
- Android的Handler訊息機制 解析Android
- 手把手帶你解析Handler原始碼原始碼
- Android IntentService完全解析 當Service遇到HandlerAndroidIntent
- 【Web前端培訓】預解析(變數提升)Web前端變數
- MySQL innodb_buffer_pool_size 變數MySql變數
- mysql中自定義變數有哪些MySql變數