mysql read rnd next_The handler_read_* 状态变量详解

最新推荐文章于 2023-02-11 14:16:50 发布
最新推荐文章于 2023-02-11 14:16:50 发布
阅读量208 收藏
点赞数
文章标签： mysql read rnd next
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_30158793/article/details/113433852
版权

Because I do a lot of Performance Tuning gigs I get often in

contact with these status variables. In the beginning I had a

problem to understand them and now I have a problem to memorize the

relation of the name and the meaning. Therefore I wrote this little

summary:

Prepare the example

To show you the effect I have worked out a little example:

CREATE TABLE test (

id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

, data VARCHAR(32)

, ts TIMESTAMP

, INDEX (data)

);

INSERT INTO test

VALUES (NULL, 'abc', NOW()), (NULL, 'abc', NOW()), (NULL, 'abd', NOW()), (NULL, 'acd', NOW()), (NULL, 'def', NOW())

, (NULL, 'pqr', NOW()), (NULL, 'stu', NOW()), (NULL, 'vwx', NOW()), (NULL, 'yza', NOW()), (NULL, 'def', NOW())

;

SELECT * FROM test;

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

| id | data | ts |

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

| 1 | abc | 2008-01-18 16:28:40 |

| 2 | abc | 2008-01-18 16:28:40 |

| 3 | abd | 2008-01-18 16:28:40 |

| 4 | acd | 2008-01-18 16:28:40 |

| 5 | def | 2008-01-18 16:28:40 |

| 6 | pqr | 2008-01-18 16:28:40 |

| 7 | stu | 2008-01-18 16:28:40 |

| 8 | vwx | 2008-01-18 16:28:40 |

| 9 | yza | 2008-01-18 16:28:40 |

| 10 | def | 2008-01-18 16:28:40 |

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

To see the effect of a query do the following steps:

FLUSH STATUS;

Execute the query

SHOW SESSION STATUS LIKE 'handler_read%';

Do an EXPLAIN of the query

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.

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

| Table | | In|ex |

| | | | |

| | | | |

| | | | |

| | | | |

| | | v |

| | | |

| | | |

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

SELECT data FROM test;

10 rows in set

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

| Variable_name | Value |

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

| Handler_read_first | 1 |

| Handler_read_key | 0 |

| Handler_read_next | 10 |

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

EXPLAIN SELECT data FROM test;

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test | index | NULL | data | 35 | NULL | 10 | Using index |

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

So what we can basically say is, that we had 1 full index scan

and it did 10+1 index fetches.

Let us do some more examples

SELECT data FROM test WHERE data BETWEEN 'A' AND 'O';

6 rows in set

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

| Variable_name | Value |

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

| Handler_read_first | 0 |

| Handler_read_key | 1 |

| Handler_read_next | 6 |

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test | range | data | data | 35 | NULL | 5 | Using where; Using index |

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

Here it seems the query is not starting with Handler_read_first

though it could theoretically. Instead of we get a

Handler_read_key. What we can also see is the "wrong" estimation of

the optimizer in the execution plan.

Whit this example the query really could start from the

beginning...

SELECT data FROM test WHERE data < 'O';

6 rows in set

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

| Variable_name | Value |

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

| Handler_read_first | 0 |

| Handler_read_key | 1 |

| Handler_read_next | 6 |

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test | range | data | data | 35 | NULL | 5 | Using where; Using index |

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

But it does not!

The same for this query:

SELECT data FROM test WHERE data LIKE 'a%';

4 rows in set

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

| Variable_name | Value |

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

| Handler_read_first | 0 |

| Handler_read_key | 1 |

| Handler_read_next | 4 |

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test | range | data | data | 35 | NULL | 4 | Using where; Using index |

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

And this query does something completly differnt:

SELECT data FROM test WHERE data IN ('abc', 'abd', 'acd');

4 rows in set

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

| Variable_name | Value |

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

| Handler_read_first | 0 |

| Handler_read_key | 3 |

| Handler_read_next | 4 |

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test | range | data | data | 35 | NULL | 4 | Using where; Using index |

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

I was not able to get any Handler_read_first count other than by

a real full index scan. So I would say that a Handler_read_first is

equivalent to Number of full index scans.

A full index scan is better than a full table scan but still not

good because they burn a lot of cpu cycles. But sometimes you

cannot avoid it...

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.

See also the examples in the previous chapter.

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

| Table | | Index |

| |

| | | |

| | | |

| | | |

| | | |

| | | |

| | | |

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

SELECT data FROM test where data = 'abc';

2 rows in set

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

| Variable_name | Value |

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

| Handler_read_first | 0 |

| Handler_read_key | 1 |

| Handler_read_next | 2 |

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test | ref | data | data | 35 | const | 2 | Using where; Using index |

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

What makes me wondering in this example (an also in the

previous) is, that based on the query there is IMHO no reson to

access the table (row)...

SELECT * FROM test where data = 'pqr';

1 row in set

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

| Variable_name | Value |

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

| Handler_read_first | 0 |

| Handler_read_key | 1 |

| Handler_read_next | 1 |

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test | ref | data | data | 35 | const | 1 | Using where |

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

In this example it makes clearly sense...!

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.

See also the examples in the previous chapters.

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

| Table | | 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.

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

| Table | | Index |

| | | |

| |

| |

| |

| | | |

| | | |

| | | |

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

SELECT data FROM test ORDER BY data DESC;

10 rows in set

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

| Variable_name | Value |

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

| Handler_read_first | 0 |

| Handler_read_key | 0 |

| Handler_read_next | 0 |

| Handler_read_prev | 10 |

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test | index | NULL | data | 35 | NULL | 10 | Using index |

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

There is no such status like Handler_read_last implemented like

it could be accoriding to the HANDLER functions [1].

SELECT * FROM test where data between 'A' and 'B' ORDER BY data DESC;

4 rows in set

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

| Variable_name | Value |

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

| Handler_read_first | 0 |

| Handler_read_key | 1 |

| Handler_read_next | 0 |

| Handler_read_prev | 4 |

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test | range | data | data | 35 | NULL | 4 | Using where |

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

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.

This status comes into account if the old file_sort mechanism is

used [2].

To make this work we have to modify slightly our table:

ALTER TABLE test ADD COLUMN file_sort text;

UPDATE test SET file_sort = 'abcdefghijklmnopqrstuvwxyz' WHERE id = 1;

UPDATE test SET file_sort = 'bcdefghijklmnopqrstuvwxyza' WHERE id = 2;

UPDATE test SET file_sort = 'cdefghijklmnopqrstuvwxyzab' WHERE id = 3;

UPDATE test SET file_sort = 'defghijklmnopqrstuvwxyzabc' WHERE id = 4;

UPDATE test SET file_sort = 'efghijklmnopqrstuvwxyzabcd' WHERE id = 5;

UPDATE test SET file_sort = 'fghijklmnopqrstuvwxyzabcde' WHERE id = 6;

UPDATE test SET file_sort = 'ghijklmnopqrstuvwxyzabcdef' WHERE id = 7;

UPDATE test SET file_sort = 'hijklmnopqrstuvwxyzabcdefg' WHERE id = 8;

UPDATE test SET file_sort = 'ijklmnopqrstuvwxyzabcdefgh' WHERE id = 9;

UPDATE test SET file_sort = 'jklmnopqrstuvwxyzabcdefghi' WHERE id = 10;

SELECT * FROM test ORDER BY file_sort asc;

10 rows in set

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

| Variable_name | Value |

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

| Handler_read_rnd | 10 |

| Handler_read_rnd_next | 11 |

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 10 | Using filesort |

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

This is really a performance killer and should be avoided

whenever possible!

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.

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

| Table| | | Index |

| | | | |

| | | | |

| | | | |

| | | | |

| v | | |

| | | |

| | | |

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

SELECT * FROM test;

10 rows in set

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

| Variable_name | Value |

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

| Handler_read_rnd_next | 11 |

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 10 | |

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

Obviously also filtering does not have a inpact on the work

which is performed:

SELECT * FROM test WHERE ts = '2008-01-18 17:33:39';

Empty set

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

| Variable_name | Value |

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

| Handler_read_rnd_next | 11 |

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

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

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

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

| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 10 | Using where |

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

(本文转自：http://www.shinguz.ch/MySQL/mysql_handler_read_status.html)

weixin_30158793
关注
【MySQL】mysql status 详细解释
九师兄
06-02 99
【代码】【MySQL】mysql status 详细解释。
MySQL参数调优详解
悦分享
09-09 2146
网上有很多的文章教怎么配置MySQL服务器，但考虑到服务器硬件配置的不同，具体应用的差别，那些文章的做法只能作为初步设置参考，我们需要根据自己的情况进行配置优化，好的做法是MySQL服务器稳定运行了一段时间后运行，根据服务器的”状态”进行优化。 可以列出MySQL服务器运行各种状态值，另外，查询MySQL服务器配置信息语句： 1、慢查询 置中打开了记录慢查询，执行时间超过2秒的即为慢查询，系统显示有4148个慢查询，你可以分析慢查询日志，找出有问题的SQL语句，慢查询时间不宜设置过长，否则意
mysql read rnd next_MySQL 优化
weixin_33438009的博客
02-01 948
参考文章:讲真，MySQL索引优化看这篇文章就够了.感谢作者提供的知识分享,如果涉及侵权,请联系本人删除,谢谢一、MySQL——索引基础“索引(在MySQL中也叫“键key”)是存储引擎快速找到记录的一种数据结构。”——《高性能MySQL》索引类型:主键索引 PRIMARY KE:特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。注意：一个表只能有一个主键。唯一索引 UNIQUE:...
mysql read rnd next_MYSQL: Handler_read_%参数说明
weixin_42107374的博客
01-19 395
环境：表t_feed_idx(user_id bigint, feed_id bigint, KEY (`user_id`,`feed_id`)) engine=innodb;表t_feed_info(feed_id bigint, PRIMARY KEY (`feed_id`), '其他列')engine=innodb;feed 总数 : 100000用户(ID:20000001) feed ...
mysql read rnd next_mysql Handler_read_rnd_next value 值超大（10G+）的处理
weixin_33428613的博客
01-19 572
mysql Handler_read_rnd_next value 值超大(10G+)的处理发现问题是在top检查服务器性能时，数据库占内存特别厉害，峰值到100%，很奇怪。phpmyadmin中发现状态值，红色 ，Handler_read_rnd_next value，特别大，就进mysql，show processlist;，一直刷新，发现 一张表修改特别频繁然后发现，即使匿名访问网站首页时也...
mysql的handler_read_next理解
weixin_34113237的博客
03-14 5366
研发提问：执行计划这么优秀，扫描了143行记录就得出结果，怎么还需要0.68s这么长的时间？ 废话不多说，直接上实验。清空status状态，再执行sql语句，查看handler状态： mysql> flush status; mysql> SELECT * FROM trade_quotation WHERE symbol = 'LTC' ...
mysql Handler_read_rnd_next value 值超大（10G+）的处理
sflsgfs的专栏
04-01 5990
mysql Handler_read_rnd_next value 值超大（10G+）的处理 发现问题是在top检查服务器性能时，数据库占内存特别厉害，峰值到100%，很奇怪。 phpmyadmin中发现状态值，红色 ，Handler_read_rnd_next value，特别大，就进mysql，show processlist;，一直刷新，发现 一张表修改特别频繁 然后发现，即使匿名访问
MySQL进阶详解教程，看过的都说好
qq_41694894的博客
02-11 1263
​ 索引是对数据库表中一个或多个列的值进行排序的结构。所有MySQL列类型都可以被索引，对相关列使用索引是提高SELECT操作性能的最佳途径。​ 存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。​ 存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型的。
【若泽大数据】MySQL参数详解
强悍de巨人
04-23 1675
系统变量提供的是各种与服务器配置和功能有关的信息。大部分的系统变量都可以在服务器启动时进行设置。在运行时，每一个系统变量都拥有一个全局值或会话值，或者同时拥有这两个值。许多系统变量都是动态的，也就是说，可以在服务器郑州运行时修改它们的值。这些类型的信息都会在每个变量的描述里（与变量名同处一行）指明。对于那些可以在服务器启动时设置的变量，看到的是“自动”一词，后面紧跟着“直接设置”或某个选项。“直接...
MySQL运行状态show status详解
浅者
11-17 6937
要查看MySQL运行状态，要优化MySQL运行效率都少不了要运行show global status或者SELECT VARIABLE_NAME, VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS查看各种状态，下面是参考官方文档及网上资料整理出来的中文详细解释，不管你是初学mysql还是你是mysql专业级的dba，这都是值得看的.
mysql handler read_mysql中的handler_read_%
weixin_29767975的博客
02-07 202
mysql>showstatuslike‘handler_read_%‘;+-----------------------+-------+|Variable_name|Value|+-----------------------+-------+|Handler_read_first|1||Handler_read_key|1||Handler_read_...
mysql read rnd next_16.9.6. 实施rnd_next()函数
weixin_31699893的博客
01-27 298
16.9.6. 实施rnd_next()函数完成表的初始化操作后，MySQL服务器将调用处理程序的1次，直至满足了服务器的搜索条件或到达文件结尾为止，在后一种情况下，处理程序将返回HA_ERR_END_OF_FILE。rnd_next()函数有一个名为*buf的单字节数组参数。对于*buf参数，必须按内部MySQL格式用表行的内容填充它。服务器采用了三种数据格式：固定长度行，可变长度行，以及具有B...
mysql read rnd next_MySQL rnd_next_编程学问网
weixin_39635432的博客
02-01 263
16.14.11.rnd_next目的从表中读取下一行，并将其返回服务器。概要virtual int rnd_next (buf);byte *buf ;描述这是rnd_next方法。对于表扫描的每一行调用它。耗尽记录时，应返回HA_ERR_END_OF_FILE。用行信息填充buff。表的字段结构是以服务器能理解的方式将数据保存到buf中的键。从下述文件调用：filesort.cc, reco...
mysql handler read_mysql的handler_read_next理解-阿里云开发者社区
weixin_33942142的博客
01-21 338
研发提问：执行计划这么优秀，扫描了143行记录就得出结果，怎么还需要0.68s这么长的时间？废话不多说，直接上实验。清空status状态，再执行sql语句，查看handler状态：mysql> flush status;mysql> SELECT * FROM trade_quotation WHERE symbol = 'LTC' ORDER BY id DESC LIMIT 30;...
mysql中的handler_read_%
陈羽
12-17 2362
mysql> show status like 'handler_read_%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 1 | | Handler_read_key | 1
MySQL中handler_read的用法
像豆芽一样优秀
08-23 1356
概述 MySQL有一个神秘的HANDLER命令，此命令非SQL标准语法，可以降低优化器对于SQL语句的解析与优化开销，从而提升查询性能。 步骤 # 1. 更新会话 FLUSH STATUS; # 2. 执行一段SQL语句 SELECT * FROM CAR WHERE CAR_BRANK LIKE "别克%" ORDER BY ID DESC LIMIT 10; # 3.1 查看当前会话 SHOW SESSION STATUS LIKE "handler_read%"; # 3.2 查看全局会话 SHOW
mysql优化之索引监控Handler_read
zy_crazy_code的博客
12-13 498
mysql> show status like 'Handler_read%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_last | 0 | | Handl.
read函数_技术分享 | InnoDB Handler_read_* 变量解释
weixin_39979159的博客
11-21 170
作者：高鹏文章末尾有他著作的《深入理解 MySQL 主从原理 32 讲》，深入透彻理解 MySQL 主从，GTID 相关技术知识。源码版本：percona 5.7.14 本文为学习记录，可能有误请谅解。本文建议PC端观看，效果更佳。一、Handler_read_* 值的实质内部表示如下：{"Handler_read_first", (char*) offsetof(STATU...
MYSQL调优（四）之索引优化
qq_40223516的博客
07-31 733
MYSQL调优（四）之索引优化
read_rnd_buffer_size
最新发布
04-10
b'read_rnd_buffer_size'是MySQL数据库的一个参数，它指定了在从磁盘读取行时，MySQL服务器预读取行的缓冲区大小。该参数的默认值通常足够适用于大多数情况，但在某些情况下，可以根据需要调整它以提高性能。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值