0227浅谈MySQL之 Handler_read_*参数
转自博客http://www.path8.net/tn/archives/5613
1.监控语法：
在MySQL里，使用SHOW STATUS查询服务器状态，语法一般来说如下：
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr]
执行命令后会看到很多内容，其中有一部分是Handler_read_*，它们显示了数据库处理SELECT查询语句的状态，对于调试SQL语句有很大意义。
mysql> show session status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 1 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
7 rows in set (0.00 sec)
2.测试流程及用例
每次执行SQL时按照如下过程执行：
FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler_read%';
EXPLAIN SELECT ...;
测试用例：
mysql> show create table artist\G;
*************************** 1. row ***************************
Table: artist
Create Table: CREATE TABLE `artist` (
`artist_id` int(10) unsigned NOT NULL,
`type` enum('Band','Person','Unknown','Combination') NOT NULL,
`name` varchar(255) NOT NULL,
`name_reverse` varchar(255) DEFAULT NULL,
`gender` enum('Male','Female') DEFAULT NULL,
`founded` year(4) DEFAULT NULL,
`country_id` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`artist_id`),
KEY `name` (`name`),
KEY `idx_founded` (`founded`),
KEY `type` (`type`),
KEY `idx_name_reverse` (`name_reverse`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
3.参数测试
3.1 Handler_read_first
Handler_read_first原意：The number of times the first entry in an index was read. 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语句，这个选项是不会变的。如果这个选项的数值很大，既是好事也是坏事。说它好是因为毕竟查询是在索引里完成的，而不是数据文件里，说它坏是因为大数据量时，即使是索引文件，做一次完整的扫描也是很费时间的。
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> select name from artist;
......
577983 rows in set (1.50 sec)
mysql> show session status like 'Handler_read%';
ERROR 1317 (70100): Query execution was interrupted
mysql> show session status like 'Handler_read%';
+-----------------------+--------+
| Variable_name | Value |
+-----------------------+--------+
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 577983 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+--------+
7 rows in set (0.00 sec)
mysql> explain select name from artist\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: artist
type: index
possible_keys: NULL
key: name
key_len: 257
ref: NULL
rows: 585801
Extra: Using index
1 row in set (0.00 sec)
使用了只读索引。
3.2 Handler_read_key
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.
此选项数值如果很高，那么恭喜你，你的系统高效的使用了索引，一切运转良好。
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from artist where name='Enya';
+-----------+--------+------+--------------+--------+---------+------------+
| artist_id | type | name | name_reverse | gender | founded | country_id |
+-----------+--------+------+--------------+--------+---------+------------+
| 88 | Person | Enya | aynE | Female | 1961 | 103 |
+-----------+--------+------+--------------+--------+---------+------------+
1 row in set (0.00 sec)
mysql> show session status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 1 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
7 rows in set (0.00 sec)
mysql> explain select * from artist where name='Enya'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: artist
type: ref
possible_keys: name
key: name
key_len: 257
ref: const
rows: 1
Extra: Using index condition
1 row in set (0.00 sec)
使用了索引下推技术。
3.3 Handler_read_last
Handler_read_last的原意：The number of requests to read the last key in an index. With ORDER BY, the server will issue a first-key request followed by several next-key requests, whereas with ORDER BY DESC, the server will issue a last-key request followed by several previous-key requests. This variable was added in MySQL 5.6.1.
3.4 Handler_read_next
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.
此选项表明在进行索引扫描时，按照索引从数据文件里取数据的次数。
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> select name from artist order by name;
......
577983 rows in set (1.55 sec)
mysql> show session status like 'Handler_read%';
+-----------------------+--------+
| Variable_name | Value |
+-----------------------+--------+
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 577983 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+--------+
7 rows in set (0.00 sec)
mysql> explain select name from artist order by name\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: artist
type: index
possible_keys: NULL
key: name
key_len: 257
ref: NULL
rows: 585801
Extra: Using index
1 row in set (0.00 sec)
3.4 Handler_read_prev
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。注意Handler_read_next是ORDER BY ... ASC的方式取数据。
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> select name from artist order by name desc;
......
577983 rows in set (1.55 sec)
mysql> show session status like 'Handler_read%';
+-----------------------+--------+
| Variable_name | Value |
+-----------------------+--------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 1 |
| Handler_read_next | 0 |
| Handler_read_prev | 577983 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+--------+
7 rows in set (0.00 sec)
mysql> explain select name from artist order by name desc\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: artist
type: index
possible_keys: NULL
key: name
key_len: 257
ref: NULL
rows: 585801
Extra: Using index
1 row in set (0.00 sec)
3.5 Handler_read_rnd
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 do not use keys properly.
简单的说，就是查询直接操作了数据文件，很多时候表现为没有使用索引或者文件排序。
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from artist order by country_id asc;
577983 rows in set (1.54 sec)
mysql> show session status like 'Handler_read%';
ERROR 1317 (70100): Query execution was interrupted
mysql> show session status like 'Handler_read%';
+-----------------------+--------+
| Variable_name | Value |
+-----------------------+--------+
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 577984 |
+-----------------------+--------+
7 rows in set (0.00 sec)
mysql> explain select country_id from artist order by country_id asc\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: artist
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 585801
Extra: Using filesort
1 row in set (0.00 sec)
3.6 Handler_read_rnd_next
Handler_read_rnd_next表示“在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。”
这个说明跟你的SQL语句有很大的关系，你可以通过explain工具或者是慢查询日志找出对应的慢SQL，并对执行慢的SQL语句进行调试，直到找到最优的执行计划，这样Handler_read_rnd_next的值就会往下降了。
很多时候，为了完成一个查询任务，我们往往可以写出几种查询语句，这时，你不妨挨个按照上面的方式执行，根据结果中的Handler_read_*数值，你就能相对容易的判断各种查询方式的优劣。
还有一种监控的方法就是profile
mysql> show variables like 'profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set (0.01 sec)
mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show profiles;
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------+
| 1 | 2.94708450 | select country_id from artist order by country_id asc |
+----------+------------+-------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show profile;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000201 |
| checking permissions | 0.000023 |
| Opening tables | 0.000062 |
| init | 0.000069 |
| System lock | 0.000035 |
| optimizing | 0.000011 |
| statistics | 0.000030 |
| preparing | 0.000019 |
| Sorting result | 0.000014 |
| executing | 0.000010 |
| Sending data | 0.000044 |
| Creating sort index | 1.601273 |
| end | 0.000070 |
| query end | 0.000026 |
| closing tables | 0.000029 |
| freeing items | 1.344915 |
| logging slow query | 0.000207 |
| cleaning up | 0.000048 |
+----------------------+----------+
18 rows in set, 1 warning (0.00 sec)
