浅谈MySQL之 Handler_read_*参数

zwd

　　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)