MySQL之Handler_read_*
在MySQL裡，我們一般使用SHOW STATUS查詢伺服器狀態，語法一般來說如下：
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr]
執行命令後會看到很多內容，其中有一部分是Handler_read_*，它們顯示了資料庫處理SELECT查詢語句的狀態，對於除錯SQL語句有很大意義，可惜實際很多人並不理解它們的實際意義，本文簡單介紹一下：
為了讓介紹更易懂，先建立一個測試用的表：
CREATE TABLE IF NOT EXISTS `foo` (
`id` int(10) unsigned NOT NULL auto_increment,
`col1` varchar(10) NOT NULL,
`col2` text NOT NULL,
PRIMARY KEY (`id`),
KEY `col1` (`col1`)
);
INSERT INTO `foo` (`id`, `col1`, `col2`) VALUES
(1, 'a', 'a'),
(2, 'b', 'b'),
(3, 'c', 'c'),
(4, 'd', 'd'),
(5, 'e', 'e'),
(6, 'f', 'f'),
(7, 'g', 'g'),
(8, 'h', 'h'),
(9, 'i', 'i');
在下面的測試裡，每次執行SQL時按照如下過程執行：
FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler_read%';
EXPLAIN SELECT ...;
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; for example, SELECT col1 FROM foo, assuming that col1 is indexed.
此選項表明SQL是在做一個全索引掃描，注意是全部，而不是部分，所以說如果存在WHERE語句，這個選項是不會變的。如果這個選項的數值很大，既是好事也是壞事。說它好是因為畢竟查詢是在索引裡完成的，而不是資料檔案裡，說它壞是因為大資料量時，簡便是索引檔案，做一次完整的掃描也是很費時的。
FLUSH STATUS;
SELECT col1 FROM foo;
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 0 |
| Handler_read_next | 9 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0.00 sec)
mysql> EXPLAIN SELECT col1 FROM foo\G
type: index
Extra: Using index
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.
此選項數值如果很高，那麼恭喜你，你的系統高效的使用了索引，一切運轉良好。
FLUSH STATUS;
SELECT * FROM foo WHERE col1 = 'e';
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_next | 1 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
mysql> EXPLAIN SELECT * FROM foo WHERE col1 = 'e'\G
type: ref
Extra: Using where
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.
此選項表明在進行索引掃描時，按照索引從資料檔案裡取資料的次數。
FLUSH STATUS;
SELECT col1 FROM foo ORDER BY col1 ASC;
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 0 |
| Handler_read_next | 9 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
mysql> EXPLAIN SELECT * FROM foo WHERE col1 = 'e'\G
type: index
Extra: Using index
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.
此選項表明在進行索引掃描時，按照索引倒序從資料檔案裡取資料的次數，一般就是ORDER BY ... DESC。
FLUSH STATUS;
SELECT col1 FROM foo ORDER BY col1 DESC;
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 9 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
mysql> EXPLAIN SELECT col1 FROM foo ORDER BY col1 DESC\G
type: index
Extra: Using index
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.
簡單的說，就是查詢直接操作了資料檔案，很多時候表現為沒有使用索引或者檔案排序。
FLUSH STATUS;
SELECT * FROM foo ORDER BY col2 DESC;
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 9 |
| Handler_read_rnd_next | 10 |
+-----------------------+-------+
mysql> EXPLAIN SELECT * FROM foo ORDER BY col2 DESC\G
type: ALL
Extra: Using filesort
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.
此選項表明在進行資料檔案掃描時，從資料檔案裡取資料的次數。
FLUSH STATUS;
SELECT * FROM foo;
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 10 |
+-----------------------+-------+
mysql> EXPLAIN SELECT * FROM foo ORDER BY col2 DESC\G
type: ALL
Extra: Using filesort
後記：不同平臺，不同版本的MySQL，在執行上面例子的時候，Handler_read_*的數值可能會有所不同，這並不要緊，關鍵是你要意識到Handler_read_*可以協助你理解MySQL處理查詢的過程，很多時候，為了完成一個查詢任務，我們往往可以寫出幾種查詢語句，這時，你不妨挨個按照上面的方式執行，根據結果中的Handler_read_*數值，你就能相對容易的判斷各種查詢方式的優劣。
說到判斷查詢方式優劣這個問題，就再順便提提show profile語法，在新版MySQL裡提供了這個功能：
mysql> set profiling=on;
mysql> use mysql;
mysql> select * from user;
mysql> show profile;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000078 |
| Opening tables | 0.000022 |
| System lock | 0.000010 |
| Table lock | 0.000014 |
| init | 0.000054 |
| optimizing | 0.000008 |
| statistics | 0.000015 |
| preparing | 0.000014 |
| executing | 0.000007 |
| Sending data | 0.000139 |
| end | 0.000007 |
| query end | 0.000007 |
| freeing items | 0.000044 |
| logging slow query | 0.000004 |
| cleaning up | 0.000005 |
+--------------------+----------+
15 rows in set (0.00 sec)
mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------+
| 1 | 0.00017725 | SELECT DATABASE(). |
| 2 | 0.00042675 | select * from user |
+----------+------------+--------------------+
2 rows in set (0.00 sec)
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr]
執行命令後會看到很多內容，其中有一部分是Handler_read_*，它們顯示了資料庫處理SELECT查詢語句的狀態，對於除錯SQL語句有很大意義，可惜實際很多人並不理解它們的實際意義，本文簡單介紹一下：
為了讓介紹更易懂，先建立一個測試用的表：
CREATE TABLE IF NOT EXISTS `foo` (
`id` int(10) unsigned NOT NULL auto_increment,
`col1` varchar(10) NOT NULL,
`col2` text NOT NULL,
PRIMARY KEY (`id`),
KEY `col1` (`col1`)
);
INSERT INTO `foo` (`id`, `col1`, `col2`) VALUES
(1, 'a', 'a'),
(2, 'b', 'b'),
(3, 'c', 'c'),
(4, 'd', 'd'),
(5, 'e', 'e'),
(6, 'f', 'f'),
(7, 'g', 'g'),
(8, 'h', 'h'),
(9, 'i', 'i');
在下面的測試裡，每次執行SQL時按照如下過程執行：
FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler_read%';
EXPLAIN SELECT ...;
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; for example, SELECT col1 FROM foo, assuming that col1 is indexed.
此選項表明SQL是在做一個全索引掃描，注意是全部，而不是部分，所以說如果存在WHERE語句，這個選項是不會變的。如果這個選項的數值很大，既是好事也是壞事。說它好是因為畢竟查詢是在索引裡完成的，而不是資料檔案裡，說它壞是因為大資料量時，簡便是索引檔案，做一次完整的掃描也是很費時的。
FLUSH STATUS;
SELECT col1 FROM foo;
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 0 |
| Handler_read_next | 9 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0.00 sec)
mysql> EXPLAIN SELECT col1 FROM foo\G
type: index
Extra: Using index
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.
此選項數值如果很高，那麼恭喜你，你的系統高效的使用了索引，一切運轉良好。
FLUSH STATUS;
SELECT * FROM foo WHERE col1 = 'e';
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_next | 1 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
mysql> EXPLAIN SELECT * FROM foo WHERE col1 = 'e'\G
type: ref
Extra: Using where
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.
此選項表明在進行索引掃描時，按照索引從資料檔案裡取資料的次數。
FLUSH STATUS;
SELECT col1 FROM foo ORDER BY col1 ASC;
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 0 |
| Handler_read_next | 9 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
mysql> EXPLAIN SELECT * FROM foo WHERE col1 = 'e'\G
type: index
Extra: Using index
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.
此選項表明在進行索引掃描時，按照索引倒序從資料檔案裡取資料的次數，一般就是ORDER BY ... DESC。
FLUSH STATUS;
SELECT col1 FROM foo ORDER BY col1 DESC;
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 9 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
mysql> EXPLAIN SELECT col1 FROM foo ORDER BY col1 DESC\G
type: index
Extra: Using index
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.
簡單的說，就是查詢直接操作了資料檔案，很多時候表現為沒有使用索引或者檔案排序。
FLUSH STATUS;
SELECT * FROM foo ORDER BY col2 DESC;
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 9 |
| Handler_read_rnd_next | 10 |
+-----------------------+-------+
mysql> EXPLAIN SELECT * FROM foo ORDER BY col2 DESC\G
type: ALL
Extra: Using filesort
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.
此選項表明在進行資料檔案掃描時，從資料檔案裡取資料的次數。
FLUSH STATUS;
SELECT * FROM foo;
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 10 |
+-----------------------+-------+
mysql> EXPLAIN SELECT * FROM foo ORDER BY col2 DESC\G
type: ALL
Extra: Using filesort
後記：不同平臺，不同版本的MySQL，在執行上面例子的時候，Handler_read_*的數值可能會有所不同，這並不要緊，關鍵是你要意識到Handler_read_*可以協助你理解MySQL處理查詢的過程，很多時候，為了完成一個查詢任務，我們往往可以寫出幾種查詢語句，這時，你不妨挨個按照上面的方式執行，根據結果中的Handler_read_*數值，你就能相對容易的判斷各種查詢方式的優劣。
說到判斷查詢方式優劣這個問題，就再順便提提show profile語法，在新版MySQL裡提供了這個功能：
mysql> set profiling=on;
mysql> use mysql;
mysql> select * from user;
mysql> show profile;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000078 |
| Opening tables | 0.000022 |
| System lock | 0.000010 |
| Table lock | 0.000014 |
| init | 0.000054 |
| optimizing | 0.000008 |
| statistics | 0.000015 |
| preparing | 0.000014 |
| executing | 0.000007 |
| Sending data | 0.000139 |
| end | 0.000007 |
| query end | 0.000007 |
| freeing items | 0.000044 |
| logging slow query | 0.000004 |
| cleaning up | 0.000005 |
+--------------------+----------+
15 rows in set (0.00 sec)
mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------+
| 1 | 0.00017725 | SELECT DATABASE(). |
| 2 | 0.00042675 | select * from user |
+----------+------------+--------------------+
2 rows in set (0.00 sec)
參考連結：
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21601207/viewspace-686490/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MYSQL: Handler_read_%引數說明MySql
- MySQL:Innodb Handler_read_*引數解釋MySql
- Handler_read_*的總結
- MySQL(六)：MySQL之MVCCMySqlMVC
- 重返MySQL之MySQL基礎MySql
- mysql之MHAMySql
- MySQL之索引MySql索引
- mysql之\cMySql
- MySQL之vacharMySql
- MySQL之order byMySql
- MySQL 之 ORDER BY FIELDMySql
- MySQL 之 USINGMySql
- mysql之 redo logMySql
- MySQL 之變數MySql變數
- MYSQL之 GroupCommitMySqlMIT
- MySQL之swap配置MySql
- MySQL之in與existsMySql
- MySQL之timestampMySql
- javaertogo之mysql操作JavaGoMySql
- MySQL預設資料庫之mysql庫MySql資料庫
- MySQL基礎之MySQL 5.7 新增配置MySql
- MySQL錯誤之mysql.slave_master_infoMySqlAST
- 【MySQL】MySQL 5.6 引數之 extra_portMySql
- 【Mysql】mysql公開課之-mysql5.7複製特性MySql
- MySQL Study之–Mysql啟動失敗“mysql.host”薦MySql
- 18 . Go之操作MysqlGoMySql
- MySQL之MGR白名單MySql
- MySQL8.0之XtraBackupMySql
- MySQL 之 show processlist 神器MySql
- MySQL之表聯結MySql
- mysql索引之字首索引MySql索引
- MySQL之臨時表MySql
- Mysql之案例分析（一）MySql
- 精通MySQL之鎖篇MySql
- MySQL學習之索引MySql索引
- MySql之EXPLAN詳解MySql
- TarsCpp 元件之 MySQL 操作元件MySql
- mysql安全之loginpathMySql