Mysql之Handler_read%
纯属自己理解,如有误导概不负责O(∩_∩)O
加索引:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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)
删除索引之后:
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,肯定是越小越好
因为年轻,所有没有失败。