MySQL中Innodb Handler_read_ 参数剖析
发布时间：2021-12-22 06:48:35 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍MySQL中Innodb Handler_read_*参数分析，在日常操作中，相信很多人在MySQL中Innodb Handler_read_*参数分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答MySQL中Innodb Handler_read_*参数分析的疑惑有所帮
|
这篇文章主要介绍“MySQL中Innodb Handler_read_*参数分析”，在日常操作中，相信很多人在MySQL中Innodb Handler_read_*参数分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL中Innodb Handler_read_*参数分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！ 一、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的用途。 到此，关于“MySQL中Innodb Handler_read_*参数分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！ （编辑：拼字网 - 核心网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐