MySQL执行SHOW STATUS查询服务器状态状态之Handler_read_* 详解
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL执行SHOW STATUS查询服务器状态状态之Handler_read_* 详解，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含11162字，纯文字阅读大概需要16分钟。
内容图文
GLOBAL | SESSION] STATUS [LIKE ‘pattern’ | WHERE expr]
执行命令后会看到很多内容，其中有一部分是Handler_read_*，它们显示了数据库处理SELECT查询语句的状态，对于调试SQL语句有很大意义，可惜实际很多人并不理解它们的实际意义，本文简单介绍一下：
为了让介绍更易懂，先建立一个测试用的表：
CREATE TABLE `foo` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `col1` varchar(10) NOT NULL, `col2` text NOT NULL, PRIMARY KEY (`id`), KEY `col1` (`col1`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=gbk
mysql> 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 | 1 | | Handler_read_last | 0 | | Handler_read_next | 9 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 7 rows in set (0.03 sec)
mysql> EXPLAIN SELECT col1 FROM foo\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: foo type: index possible_keys: NULL key: col1 key_len: 22 ref: NULL rows: 9 Extra: Using index 1 row in set (0.01 sec)
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;
mysql> SELECT * FROM foo WHERE col1="e"; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 5 | e | e | +----+------+------+ 1 row in set (0.02 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.02 sec)
mysql> explain SELECT * FROM foo WHERE col1="e"; +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ | 1 | SIMPLE | foo | ref | col1 | col1 | 22 | const | 1 | Using index condition | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+ 1 row in set (0.01 sec)
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 col1 FROM foo ORDER BY col1 ASC; +------+ | col1 | +------+ | a | | b | | c | | d | | e | | f | | g | | h | | i | +------+ 9 rows in set (0.01 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 | 9 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 7 rows in set (0.01 sec)
mysql> explain SELECT col1 FROM foo ORDER BY col1 ASC\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: foo type: index possible_keys: NULL key: col1 key_len: 22 ref: NULL rows: 9 Extra: Using index 1 row in set (0.00 sec) ERROR: No query specified
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。
mysql> FLUSH STATUS; Query OK, 0 rows affected (0.00 sec) mysql> SELECT col1 FROM foo ORDER BY col1 DESC; +------+ | col1 | +------+ | i | | h | | g | | f | | e | | d | | c | | b | | a | +------+ 9 rows in set (0.01 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 | 9 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | +-----------------------+-------+ 7 rows in set (0.03 sec)
mysql> explain SELECT col1 FROM foo ORDER BY col1 DESC; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | foo | index | NULL | col1 | 22 | NULL | 9 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.01 sec)
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.
简单的说，就是查询直接操作了数据文件，很多时候表现为没有使用索引或者文件排序。
mysql> FLUSH STATUS; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM foo ORDER BY col2 DESC; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 9 | i | i | | 8 | h | h | | 7 | g | g | | 6 | f | f | | 5 | e | e | | 4 | d | d | | 3 | c | c | | 2 | b | b | | 1 | a | a | +----+------+------+ 9 rows in set (0.02 sec)
mysql> SHOW SESSION STATUS LIKE "%handler_read%"; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 1 | | Handler_read_key | 10 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 9 | | Handler_read_rnd_next | 10 | +-----------------------+-------+ 7 rows in set (0.02 sec)
mysql> explain SELECT * FROM foo ORDER BY col2 DESC; +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | foo | ALL | NULL | NULL | NULL | NULL | 9 | Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.01 sec)
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.
此选项表明在进行数据文件扫描时，从数据文件里取数据的次数。
mysql> FLUSH STATUS; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM foo; +----+------+------+ | id | col1 | col2 | +----+------+------+ | 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 | +----+------+------+ 9 rows in set (0.01 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 | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 10 | +-----------------------+-------+ 7 rows in set (0.02 sec)
mysql> EXPLAIN SELECT * FROM foo\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: foo
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 9
Extra: NULL
1 row in set (0.01 sec)
后记：不同平台，不同版本的MySQL，在运行上面例子的时候，Handler_read_*的数值可能会有所不同，这并不要紧，关键是你要意识到 Handler_read_*可以协助你理解MySQL处理查询的过程，很多时候，为了完成一个查询任务，我们往往可以写出几种查询语句，这时，你不妨挨 个按照上面的方式执行，根据结果中的Handler_read_*数值，你就能相对容易的判断各种查询方式的优劣。
说到判断查询方式优劣这个问题，就再顺便提提show profile语法，在新版MySQL里提供了这个功能：
mysql> set profiling=on; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show profile; +----------------+----------+ | Status | Duration | +----------------+----------+ | starting | 0.000840 | | query end | 0.000116 | | closing tables | 0.000097 | | freeing items | 0.000102 | | cleaning up | 0.000205 | +----------------+----------+ 5 rows in set, 1 warning (0.01 sec) mysql> show profiles; +----------+------------+-------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------+ | 1 | 0.00135925 | show warnings | | 2 | 0.00127000 | show warnings | | 3 | 0.18649600 | SELECT * FROM foo | +----------+------------+-------------------+ 3 rows in set, 1 warning (0.00 sec)
http://www.path8.net/tn/archives/5613
参考链接：http://www.shinguz.ch/MySQL/mysql_handler_read_status.html
from http://hi.baidu.com/thinkinginlamp/blog/item/31690cd7c4bc5cdaa144df9c.html
MySQL执行SHOW STATUS查询服务器状态状态之Handler_read_* 详解
标签：
本文系统来源：http://www.cnblogs.com/zengkefu/p/5685811.html
内容总结
以上是互联网集市为您收集整理的MySQL执行SHOW STATUS查询服务器状态状态之Handler_read_* 详解全部内容，希望文章能够帮你解决MySQL执行SHOW STATUS查询服务器状态状态之Handler_read_* 详解所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。