- Mysql> Show 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 | 21 |
- +-----------------------+-------+
- 7 rows in Set (0.01 sec)
As shown above, there are 7 counters for read in MySQL. Their numerical understanding of the condition of the system is important for system tuning. We should understand what they mean. This article is some of my own understanding.
First 7 counters, we should be divided into two parts:
1) Counters for index reads: The previous 5 are counters for index reads,
Handler_read_first: Refers to the first item of a read index (the number of times);
Handler_read_key: Refers to an item (the number of times) of a read index;
Handler_read_next: Refers to the next item of the index (the number of times);
Handler_read_last: Refers to the last first item of the index (the number of times);
Handler_read_prev: Refers to the previous item (the number of times) of a read index;
5 people should have four combinations:
1. Handler_read_first and handler_read_next combinations should be index overlay scans
2. Handler_read_key based on index values
3. Handler_read_key and handler_read_next combination should be index range scan
4. Handler_read_last and Handler_read_prev combination should be an index range scan (Orde by DESC)
2) counter to the data file: The next 2 are the counters that read the data files.
Handler_read_rnd:
The number of requests to read a row based on a fixed position. This value was high if you are doing alot of queries that require sorting of the result. You probably has a lot of queries so require MySQL toscan entire tables or you had joins that does not use the keys properly .
Handler_read_rnd_next
-
Top
handler_read_% in MySQL
