1. 首页
  2. IT资讯

MySQL:Innodb Handler_read_*参数解释


本文为学习笔记,有误请指出。
本文使用源码版本:Percona 5.7.14


一、Handler_read_*值的实质

内部表示如下:

    {"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},  

实际上这些变量都是MySQL层定义出来的,因为MySQL可以包含多个存储引擎。因此这些值如何增加需要在引擎层的接口中自行实现,也就是说各个引擎都有自己的实现,在MySQL层进行汇总,因此这些值不是某个引擎特有的,打个比方如果有Innodb和MyISAM引擎,那么这些值是两个引擎的总和。本文将以Innodb为主要学习对象进行解释。

二、各个值的解释

1、Handler_read_key
  • 内部表示:ha_read_key_count

  • Innodb更改接口:ha_innobase::index_read

  • 文档解释: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.

  • 源码函数解释:Positions an index cursor to the index specified in the handle. Fetches the row if any.

  • 作者解释:这个函数是访问索引的时候定位到值所在的位置用到的函数,因为必须要知道读取索引的开始位置才能向下访问。

2、Handler_read_next
  • 内部表示:ha_read_next_count

  • Innodb更改接口:ha_innobase::index_next_same ha_innobase::index_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.

  • 源码函数解释:
    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.

  • 作者解释:访问索引的下一条数据封装的 ha_innobase::general_fetch 函数,index_next_same和index_next不同在于访问的方式不一样,比如范围range查询需要用到和索引全扫描也会用到index_next,而ref访问方式会使用index_next_same

3、Handler_read_first
  • 内部表示:ha_read_first_count

  • Innodb更改接口:ha_innobase::index_first

  • 文档解释: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

  • 源码函数解释:Positions a cursor on the first record in an index and reads the corresponding row to buf.

  • 作者解释:定位索引的第一条数据,实际上也是封装的 ha_innobase::index_read  函数(如全表扫描/全索引扫描调用)

4、Handler_read_rnd_next
  • 内部表示:ha_read_rnd_next_count

  • Innodb更改接口:ha_innobase::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.

  • 源码函数解释:Reads the next row in a table scan (also used to read the FIRST row in a table scan).

  • 作者解释:全表扫描访问下一条数据,实际上也是封装的 ha_innobase::general_fetch ,在访问之前会调用ha_innobase::index_first

5、Handler_read_rnd
  • 内部表示:ha_read_rnd_count

  • Innodb更改接口:ha_innobase::rnd_pos

  • Memory更改接口:ha_heap::rnd_pos

  • 文档解释: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.

  • 作者解释:这个状态值在我测试期间只发现对临时表做排序的时候会用到,而且是Memory引擎的,具体只能按照文档理解了。

6、其他

最后2个简单说一下

  • Handler_read_prev
    Innodb接口为 ha_innobase::index_prev 访问索引的上一条数据,实际上也是封装的 ha_innobase::general_fetch 函数,用于ORDER BY DESC 索引扫描避免排序,内部状态值ha_read_prev_count增加。

  • Handler_read_last
    Innodb接口为ha_innobase::index_last 访问索引的最后一条数据作为定位,实际上也是封装的 ha_innobase::index_read 函数,用于ORDER BY DESC 索引扫描避免排序,内部状态值ha_read_last_count增加。

三、常用查询测试

1、测试用例
  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、全表扫描
  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          | NULL | NULL    | NULL | 56650 |   100.00 | NULL  |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+1 row in set, 1 warning (0.00 sec)  mysql> pager cat >>/dev/null  PAGER set to 'cat >>/dev/null'mysql> flush status;  Query OK, 0 rows affected (0.10 sec)  mysql> select * from z1;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.01 sec)  

Handler_read_first增加1次用于初次定位,Handler_read_key增加1次,Handler_read_rnd_next增加扫描行数。我们前面说过因为ha_innobase::index_first也是封装的 ha_innobase::index_read 因此都需要+1。

3、全索引扫描
  mysql> desc select a from z1;  +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-------------+| 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 | 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/null  PAGER set to 'cat >>/dev/null'mysql> select a from z1;56415 rows in set (4.57 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     | 56415 |  | Handler_read_prev     | 0     |  | Handler_read_rnd      | 0     |  | Handler_read_rnd_next | 0     |  +-----------------------+-------+  7 rows in set (0.01 sec)  

Handler_read_first增加1次用于初次定位,Handler_read_key增加1次,Handler_read_next增加扫描行数用于连续访问接下来的行。我们前面说过因为ha_innobase::index_first也是封装的 ha_innobase::index_read 因此都需要+1。

4、索引ref访问

我这里因为是测试索引全是等于10的加上了force index

  mysql>  desc select  * from z1 force index(a) where a=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/null  PAGER set to 'cat >>/dev/null'mysql> select  * from z1 force index(a) where a=10;56414 rows in set (32.39 sec)  mysql> pager  Default 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     |  +-----------------------+-------+  7 rows in set (0.06 sec)  

Handler_read_key增加1次这是用于初次定位,Handler_read_next增加扫描行数次数用于接下来的数据访问。

5、索引range访问
  mysql> desc select  * from z1 force index(a) where a>9 and a<12;  +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                 |  +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+  |  1 | SIMPLE      | z1    | NULL       | range | a             | a    | 5       | NULL | 28325 |   100.00 | Using index condition |+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)  mysql>  pager cat >>/dev/null  PAGER set to 'cat >>/dev/null'mysql> select  * from z1 force index(a) where a>9 and a<12;56414 rows in set (47.54 sec)  mysql> show status like 'Handler_read%';7 rows in set (0.03 sec)  mysql>  pager  Default 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     |  +-----------------------+-------+  7 rows in set (0.02 sec)  

Handler_read_key增加1次这是用于初次定位,Handler_read_next增加扫描行数次数用于接下来的数据访问。

6、被驱动表带索引访问
  mysql> desc select * from z1 STRAIGHT_JOIN z10 force index(a_idx) on z1.a=z10.a;  +----+-------------+-------+------------+------+---------------+-------+---------+-----------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref       | rows  | filtered | Extra       |  +----+-------------+-------+------------+------+---------------+-------+---------+-----------+-------+----------+-------------+  |  1 | SIMPLE      | z1    | NULL       | ALL  | a             | NULL  | NULL    | 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/null  PAGER set to 'cat >> /dev/null'  mysql>  select * from z1 STRAIGHT_JOIN z10 force index(a_idx) on z1.a=z10.a;  112828 rows in set (1 min 21.21 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      | 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 增加一次作为驱动表z1全表扫描定位的开始,接下来Handler_read_rnd_next扫描全部记录,每次扫描一次在z10表通过索引a_idx定位一次Handler_read_key增加1次,然后接下来进行索引a_idx进行数据查找Handler_read_next增加为扫描的行数。

6、索引避免排序正向和反向
  mysql>  flush status;  Query OK, 0 rows affected (0.05 sec)  mysql> pager cat >> /dev/null  PAGER set to 'cat >> /dev/null'mysql> select * from z1 force index(a) order by a;56415 rows in set (27.39 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     | 56415 |  | Handler_read_prev     | 0     |  | Handler_read_rnd      | 0     |  | Handler_read_rnd_next | 0     |  +-----------------------+-------+  7 rows in set (0.01 sec)  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/null  PAGER set to 'cat >> /dev/null'  mysql>  select * from z1 force index(a) order by a desc;  56415 rows in set (24.94 sec)  mysql> pager  Default 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     |+-----------------------+-------+7 rows in set (0.01 sec)  

不用过多解释,可以看到Handler_read_last 和Handler_read_prev的用途。

四、总结

  • Handler_read_rnd_next 通常代表着全表扫描。

  • Handler_read_first 通常代表着全表或者全索引扫描。

  • Handler_read_next 通常代表着合理的使用了索引或者全索引扫描。

  • Handler_read_key 不管全表全索引或者正确使用的索引实际上都会增加,只是一次索引定位而已。

  • Innodb中全表扫描也是主键的全索引扫描。

  • 顺序访问的一条记录实际上都是调用 ha_innobase::general_fetch 函数,另外一个功能innodb_thread_concurrency参数的功能就在里面实现,下次在说。

作者微信:

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-2158728/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183518.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code