Mysql之Handler

Mysql之Handler_read%

纯属自己理解，如有误导概不负责O(∩_∩)O

加索引：

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.02 sec)

mysql> explain select * from userinfo where username='test1'G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: userinfo
         type: ref
possible_keys: username
          key: username
      key_len: 768
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 4     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

View Code

删除索引之后：

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from userinfo where username='te
*************************** 1. row *********************
           id: 1
  select_type: SIMPLE
        table: userinfo
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 2     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

官方文档对这几个参数的解释：

Handler_read_first[647]
　　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 col1is
indexed.

但是我不知道它在值是越大越好还是越小越好？因为即使是从索引中执行一次full索引扫描，也应该快不到哪去。

• Handler_read_key[647]
　　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.

肯定是越大越好

• Handler_read_last[647]
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 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.

• Handler_read_next[647]
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.

貌似也是越小越好，至少官方文档的例子是这样说的：The Handler_read_next[647]value
decreases from 5 to 1, indicating more efficient use of the index

• Handler_read_prev[647]
The number of requests to read the previous row in key order. This read method is mainly used to
optimize ORDER BY ... DESC.

主要用于排序，排序肯定消耗资源

• Handler_read_rnd[647]
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.

应该和上一个类似。

• Handler_read_rnd_next[647]
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.

这个涉及到table scans，肯定是越小越好