MySQL中InnodbHandler_read_*参数分析
这篇文章主要介绍“MySQL中Innodb Handler_read_*参数分析”,在日常操作中,相信很多人在MySQL中Innodb Handler_read_*参数分析问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”MySQL中Innodb Handler_read_*参数分析”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!
创新互联-专业网站定制、快速模板网站建设、高性价比长沙县网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式长沙县网站制作公司更省心,省钱,快速模板网站建设找我们,业务覆盖长沙县地区。费用合理售后完善,十载实体公司更值得信赖。
一、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_*参数分析”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注创新互联网站,小编会继续努力为大家带来更多实用的文章!
新闻标题:MySQL中InnodbHandler_read_*参数分析
URL链接:http://myzitong.com/article/jgsocg.html