Shulou(Shulou.com)05/31 Report--

This article mainly introduces "Innodb Handler_read_* parameter analysis in MySQL". In daily operation, I believe many people have doubts about Innodb Handler_read_* parameter analysis in MySQL. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "Innodb Handler_read_* parameter analysis in MySQL". Next, please follow the editor to study!

1. The essence of Handler_read_* value

The internal representation is as follows:

{"Handler_read_first", (char*) offsetof (STATUS_VAR, ha_read_first_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_read_key", (char*) offsetof (STATUS_VAR, ha_read_key_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_read_last", (char*) offsetof (STATUS_VAR Ha_read_last_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_read_next", (char*) offsetof (STATUS_VAR, ha_read_next_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_read_prev", (char*) offsetof (STATUS_VAR, ha_read_prev_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL} {"Handler_read_rnd", (char*) offsetof (STATUS_VAR, ha_read_rnd_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}, {"Handler_read_rnd_next", (char*) offsetof (STATUS_VAR, ha_read_rnd_next_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL}

In fact, these variables are defined by the MySQL layer, because MySQL can contain multiple storage engines. So how to increase these values needs to be implemented in the interface of the engine layer, that is, each engine has its own implementation and is summarized at the MySQL layer, so these values are not specific to a particular engine. For example, if there are Innodb and MyISAM engines, then these values are the sum of the two engines. This article will take Innodb as the main learning object to explain.

Second, the interpretation of each value 1. Handler_read_key

Internal representation: ha_read_key_count

Innodb change interface: ha_innobase::index_read

Document explanation: 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.

Source function interpretation: Positions an index cursor to the index specified in the handle. Fetches the row if any.

The author explains: this function is used to locate the location of the value when accessing the index, because you must know the starting position of the read index before you can access it down.

2 、 Handler_read_next

Internal representation: ha_read_next_count

Innodb change interface: ha_innobase::index_next_same ha_innobase::index_next

Document explanation: 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.

Source code function explanation:

Index_next-Reads the next row from a cursor, which must have previously been positioned using index_read.

Index_next_same-Reads the next row matching to the key value given as the parameter.

The author explains that the difference between index_next_same and index_next for accessing the ha_innobase::general_fetch function encapsulated by the next piece of data in the index lies in the way it is accessed. For example, the scope range query and the index full scan also use index_next, while the ref access method uses index_next_same.

3 、 Handler_read_first

Internal representation: ha_read_first_count

Innodb change interface: ha_innobase::index_first

Document explanation: 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 col1

Is indexed

Source function interpretation: Positions a cursor on the first record in an index and reads the corresponding row to buf.

The author explains that the first piece of data in the location index is actually an encapsulated ha_innobase::index_read function (such as full table scan / full index scan call).

4 、 Handler_read_rnd_next

Internal representation: ha_read_rnd_next_count

Innodb change interface: ha_innobase::rnd_next

Document explanation: 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.

Source function interpretation: Reads the next row in a table scan (also used to read the FIRST row in a table scan).

The author explains that a full table scan accesses the next piece of data, which is actually an encapsulated ha_innobase::general_fetch, and ha_innobase::index_first is called before the access.

5 、 Handler_read_rnd

Internal representation: ha_read_rnd_count

Innodb change interface: ha_innobase::rnd_pos

Memory change interface: ha_heap::rnd_pos

Document explanation: 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.

The author explained: during my testing, I found that this status value is only used when sorting temporary tables, and it is from the Memory engine, which can only be understood according to the documentation.

6. Other

The last two are briefly described.

Handler_read_prev

The Innodb API accesses the last data of the index for ha_innobase::index_prev. In fact, it is also an encapsulated ha_innobase::general_fetch function, which is used for ORDER BY DESC index scanning to avoid sorting, and the internal status value ha_read_prev_count is increased.

Handler_read_last

The Innodb API serves as a location for ha_innobase::index_last to access the last piece of data in the index. In fact, it is also an encapsulated ha_innobase::index_read function, which is used for ORDER BY DESC index scanning to avoid sorting, and the internal status value ha_read_last_count is increased.

3. Common query test 1, test case mysql > show create table Z1 +- -- + | Table | Create Table | +-+- - -+ | Z1 | CREATE TABLE `z1` (`a` int (11) DEFAULT NULL `name` varchar (20) DEFAULT NULL KEY `a` (`a`) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-+- -- + 1 row in set (0.00 sec) mysql > show create table Z10 +- -- + | Table | Create Table | +-+- - -+ | Z10 | CREATE TABLE `z10` (`a` int (11) DEFAULT NULL `name` varchar (20) DEFAULT NULL KEY `a_ idx` (`a`) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-+- -+ 1 row in set (0.00 sec) mysql > select count (*) from Z1 +-+ | count (*) | +-+ | 56415 | +-+ 1 row in set (5.27 sec) mysql > select count (*) from z10 + | count (*) | +-+ | 10 | +-+ 1 row in set (0.00 sec) 2, full table scan mysql > desc select * from Z1 +-+ | id | select_type | | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-+ | 1 | SIMPLE | Z1 | NULL | ALL | NULL | 56650 | 100.00 | NULL | +-+ -+-+ 1 row in set 1 warning (0.00 sec) mysql > pager cat > > / dev/nullPAGER set to 'cat > > / dev/null'mysql > flush status Query OK, 0 rows affected (0.10 sec) mysql > select * from Z1 to 56415 rows in set (4.05 sec) mysql > pager;Default pager wasn't set, using stdout.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 | 56416 | +-- +-+ 7 rows in set (0.01sec) |

Handler_read_first increased once for initial positioning, Handler_read_key increased once, and Handler_read_rnd_next increased the number of scanning lines. As we said earlier, + 1 is required because ha_innobase::index_first is also an encapsulated ha_innobase::index_read.

3. Full index scan mysql > desc select a from Z1 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -- + | 1 | SIMPLE | Z1 | NULL | index | NULL | a | 5 | NULL | 56650 | 100.00 | Using index | + -+-+ 1 row in set 1 warning (0.00 sec) mysql > flush status Query OK, 0 rows affected (0.12 sec) mysql > pager cat > > / dev/nullPAGER set to 'cat > > / dev/null'mysql > select a from Z1 rows in set 56415 rows in set (4.57 sec) mysql > pagerDefault pager wasn't set, using stdout.mysql > show status like' Handler_read%' +-- +-+ | Variable_name | Value | +-- +-+ | Handler_read_first | 1 | | Handler_read_key | 1 | Handler_read_last | 0 | Handler_read_next | 56415 | Handler_read_prev | 0 | Handler_read_rnd | 0 | Handler_read_rnd_next | 0 | 0 | +-- +-+ 7 rows in set (0.01sec)

Handler_read_first is added once for initial positioning, Handler_read_key is added once, and Handler_read_next increases the number of scan lines for continuous access to the following lines. As we said earlier, + 1 is required because ha_innobase::index_first is also an encapsulated ha_innobase::index_read.

4. Index ref access

Here, because it is a test, the index is all equal to 10 plus force index.

Mysql > desc select * from Z1 force index (a) where axiom 10 +-+ | id | select_ Type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -- + | 1 | SIMPLE | Z1 | NULL | ref | a | a | 5 | const | 28325 | 100.00 | NULL | + -+ 1 row in set 1 warning (0.01 sec) mysql > flush status Query OK, 0 rows affected (0.13 sec) mysql > pager cat > > / dev/nullPAGER set to 'cat > > / dev/null'mysql > select * from z1 force index (a) where axiom 10 rows in set (32.39 sec) mysql > pagerDefault pager wasn't set, using stdout.mysql > show status like' Handler_read%' +-- +-+ | Variable_name | Value | +-- +-+ | Handler_read_first | 0 | Handler_read_key | 1 | Handler_read_last | 0 | Handler_read_next | 56414 | Handler_read_prev | 0 | | Handler_read_rnd | 0 | Handler_read_rnd_next | 0 | 0 | +-- +-+ 7 rows in set (0.06 sec)

Handler_read_key is added once, which is used for initial positioning, and Handler_read_next increases the number of scan lines for subsequent data access.

5. Index range visits mysql > desc select * from Z1 force index (a) where a > 9 and a pager cat > > / dev/nullPAGER set to 'cat > > / dev/null'mysql > select * from Z1 force index (a) where a > 9 and a show status like' Handler_read%';7 rows in set (0.03 sec) mysql > pagerDefault pager wasn't set, using stdout.mysql > show status like 'Handler_read%' +-- +-+ | Variable_name | Value | +-- +-+ | Handler_read_first | 0 | Handler_read_key | 1 | Handler_read_last | 0 | Handler_read_next | 56414 | Handler_read_prev | 0 | | Handler_read_rnd | 0 | Handler_read_rnd_next | 0 | 0 | +-- +-+ 7 rows in set (0.02 sec)

Handler_read_key is added once, which is used for initial positioning, and Handler_read_next increases the number of scan lines for subsequent data access.

6. Mysql > desc select * from Z1 STRAIGHT_JOIN Z10 force index (a_idx) on z1.a=z10.a with index access for driven table +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | Z1 | NULL | ALL | a | NULL | 56650 | 100.00 | Using where | | 1 | SIMPLE | Z10 | NULL | ref | a_idx | a_idx | | 5 | test.z1.a | 10 | 100.00 | NULL | + -+ 2 rows in set 1 warning (0.01 sec) mysql > flush status Query OK, 0 rows affected (0.47 sec) mysql > pager cat > > / dev/nullPAGER set to 'cat > > / dev/null'mysql > select * from z1 STRAIGHT_JOIN z10 force index (a_idx) on z1.atransferz10.a * rows in set (1 min 21.21 sec) mysql > pagerDefault pager wasn't set, using stdout.mysql > show status like' Handler_read%' +-- +-+ | Variable_name | Value | +-- +-+ | Handler_read_first | 1 | | Handler_read_key | 56416 | | Handler_read_last | 0 | Handler_read_next | 112828 | Handler_read_prev | 0 | Handler_read_rnd | 0 | Handler_read_rnd_next | 56416 | +-+-+ 7 rows in set (0.00 sec)

Handler_read_first adds once as the start of driving table Z1 full table scan positioning, then Handler_read_rnd_next scans all records, each scan increases Handler_read_key once through index a_idx positioning in Z10 table, and then index a_idx for data lookup Handler_read_next increases the number of rows scanned.

6. Avoid sorting forward and reverse mysql > flush status;Query OK, 0 rows affected (0.05 sec) mysql > pager cat > > / dev/nullPAGER set to 'cat > > / dev/null'mysql > select * from z1 force index (a) order by a * * rows in set (27.39 sec) mysql > pagerDefault pager wasn't set, using stdout.mysql > show status like' Handler_read%' +-- +-+ | Variable_name | Value | +-- +-+ | Handler_read_first | 1 | | Handler_read_key | 1 | Handler_read_last | 0 | Handler_read_next | 56415 | Handler_read_prev | 0 | Handler_read_rnd | 0 | Handler_read_rnd_next | 0 | +-- +-+ 7 rows in set (0.01sec) mysql > flush status Query OK, 0 rows affected (0.10 sec) mysql > desc select * from Z1 force index (a) order by a desc +-+ | id | select_ Type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | Z1 | NULL | index | NULL | a | 5 | NULL | 56650 | 100.00 | NULL | +- -+ 1 row in set 1 warning (0.00 sec) mysql > pager cat > > / dev/nullPAGER set to 'cat > > / dev/null'mysql > select * from Z1 force index (a) order by a desc 56415 rows in set (24.94 sec) mysql > pagerDefault pager wasn't set, using stdout.mysql > show status like 'Handler_read%' +-- +-+ | Variable_name | Value | +-- +-+ | Handler_read_first | 0 | Handler_read_key | 1 | Handler_read_last | 1 | Handler_read_next | 0 | | Handler_read_prev | 56415 | | Handler_read_rnd | 0 | Handler_read_rnd_next | 0 | 0 | +-- +-+ 7 rows in set (sec) |

Without much explanation, you can see the use of Handler_read_last and Handler_read_prev.

At this point, the study of "Innodb Handler_read_* parameter analysis in MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.