mysql handler_Mysql之Handler_read%

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

加索引：

mysql>flush status;

Query OK,0 rows affected (0.00sec)

mysql>flush tables;

Query OK,0 rows affected (0.02sec)

mysql> explain select * from userinfo where username='test1'\G*************************** 1. row ***************************id:1select_type: SIMPLEtable: userinfo

type: ref

possible_keys: usernamekey: username

key_len:768ref: const

rows:1Extra: Usingwhere

1 row in set (0.00sec)

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.00sec)

mysql>flush tables;

Query OK,0 rows affected (0.00sec)

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 ishigh, it suggests that the serveris doing a lot of full index scans; forexample, 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 isa good indication that

your tables are properly indexedfor your queries.

肯定是越大越好

• Handler_read_last[647]

The number of requests to read the last keyin 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 rowin key order. This value is incremented ifyou are querying

an index column with a range constraint orif 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 rowin key order. This read method ismainly used to

optimize ORDER BY ... DESC.

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

• Handler_read_rnd[647]

The number of requests to read a row based on afixed position. This value is high ifyou 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 thatdo not use keys properly.

应该和上一个类似。

• Handler_read_rnd_next[647]

The number of requests to read the next rowin the data file. This value is high ifyou are doing a lot of

table scans. Generallythissuggests that your tables are not properly indexed or that your queries are

not written to take advantage of the indexes you have.

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