MySQL之Handler_read_*(二)

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.000426