) Handler_read _ * of MySQL _*

In MySQL, we generally use show status to query the server status. The syntax is generally as follows:

Show [Global | session] status [like 'pattern' | where expr]

After executing the command, you will see a lot of content, some of which are handler_read _ *, which shows the status of the database to process SELECT query statements, which is of great significance for debugging SQL statements, unfortunately, many people do not understand their actual meanings. This article briefly introduces:

To make the introduction easier, first create a test table:

Create Table if not exists 'foo '(

'Id' int (10) unsigned not null auto_increment,

'Col1' varchar (10) Not null,

'Col2' text not null,

Primary Key ('id '),

Key 'col1' ('col1 ')

);

Insert into 'foo' ('id', 'col1', 'col2') Values

(1, 'A', 'A '),

(2, 'B', 'B '),

(3, 'C', 'C '),

(4, 'D', 'D '),

(5, 'E', 'E '),

(6, 'F', 'F '),

(7, 'G', 'G '),

(8, 'h', 'H '),

(9, 'I', 'I ');

In the following test, each SQL statement is executed as follows:

Flush status;

Select ...;

Show session status like 'handler _ read % ';

Explain Select ...;

Handler_read_first

The number of times the first entry was read from an index. 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 col1 is indexed.

This option indicates that SQL is performing a full index scan. Note that it is full, not partial. Therefore, if the where statement exists, this option will not change. If the value of this option is large, it is both a good thing and a bad thing. It is good because, after all, the query is completed in the index, not in the data file. It is bad because when there is a large amount of data, the index file is simple, it is time-consuming to perform a complete scan.

Flush status;

Select col1 From Foo;

Mysql> show session status like 'handler _ read % ';

+ ----------------------- + ------- +

| Variable_name | value |

+ ----------------------- + ------- +

| Handler_read_first | 1 |

| Handler_read_key | 0 |

| Handler_read_next | 9 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 0 |

+ ----------------------- + ------- +

6 rows in SET (0.00 Sec)

Mysql> explain select col1 From Foo \ G

Type: Index

Extra: Using Index

Handler_read_key

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.

If the value of this option is very high, congratulations! Your system uses the index efficiently and everything works well.

Flush status;

Select * From Foo where col1 = 'E ';

Mysql> show session status like 'handler _ read % ';

+ ----------------------- + ------- +

| Variable_name | value |

+ ----------------------- + ------- +

| Handler_read_first | 0 |

| Handler_read_key | 1 |

| Handler_read_next | 1 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 0 |

+ ----------------------- + ------- +

Mysql> explain select * From Foo where col1 = 'E' \ G

Type: ref

Extra: Using where

Handler_read_next

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.

This option indicates the number of times the index retrieves data from the data file during index scanning.

Flush status;

Select col1 From Foo order by col1 ASC;

Mysql> show session status like 'handler _ read % ';

+ ----------------------- + ------- +

| Variable_name | value |

+ ----------------------- + ------- +

| Handler_read_first | 1 |

| Handler_read_key | 0 |

| Handler_read_next | 9 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 0 |

+ ----------------------- + ------- +

Mysql> explain select * From Foo where col1 = 'E' \ G

Type: Index

Extra: Using Index

Handler_read_prev

The number of requests to read the previous row in key order. This read method is mainly used to optimize order by... DESC.

This option indicates the number of times the data is retrieved from the data file in reverse order of the index during index scanning, which is generally order by... DESC.

Flush status;

Select col1 From Foo order by col1 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 | 9 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 0 |

+ ----------------------- + ------- +

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.

Simply put, data files are directly operated through queries, which often result in no index or file sorting.

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.

This option indicates the number of times data is retrieved from the data file during data file scanning.

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

Note: The handler_read _ * values may vary depending on different platforms and versions of MySQL, the key is that you need to realize that handler_read _ * can help you understand the MySQL query process. Many times, to complete a query task, we can write several query statements, depending on the handler_read _ * value in the result, you can easily determine the advantages and disadvantages of various query methods.

When it comes to determining the merits and demerits of the query method, I will mention the show profile syntax by the way. This function is provided in the new MySQL version:

Mysql> set profiling = on;

Mysql> use MySQL;

Mysql> select * from user;

Mysql> show profile;

+ -------------------- + ---------- +

| Status | duration |

+ -------------------- + ---------- +

| Startling | 0.000078 |

| Opening tables | 0.000022 |

| System lock | 0.000010 |

| Table lock | 0.000014 |

| Init | 1, 0.000054 |

| Optimizing | 0.000008 |

| Statistics | 0.000015 |

| Preparing | 0.000014 |

| Executing | 0.000007 |

| Sendingdata | 0.000139 |

| End| 0.000007 |

| Query end | 0.000007 |

| Freeing items | 0.000044 |

| Logging slow query | 0.000004 |

| Cleaningup | 0.000005 |

+ -------------------- + ---------- +

15 rows in SET (0.00 Sec)

Mysql> show profiles;

+ ---------- + ------------ + -------------------- +

| Query_id | duration | query |

+ ---------- + ------------ + -------------------- +

| 1 | 0.00017725 | select database (). |

| 1 | 0.00042675 | select * from user |

+ ---------- + ------------ + -------------------- +

2 rows in SET (0.00 Sec)