Handler_read_*的总结

Handler

首先说说什么是handler。

handler是一个类，里面按不同的功能模块定义了若干接口（具体可参考sql/handler.h）。其中，

DML操作相关的接口有：

write_row()

update_row()

delete_row()

delete_all_rows()

start_bulk_insert()

end_bulk_insert()

索引扫描相关的接口有：

index_read_map()

index_init()

index_end()

index_read_idx_map()

index_next()

index_prev()

index_first()

index_last()

index_next_same()

index_read_last_map()

read_range_first()

read_range_next()

其它相关接口可参考sql/handler.h，sql/handler.cc文件。

如此设计，有两点显而易见的好处：

1. Server层与存储引擎层解耦。MySQL Server层在与存储引擎层交互时，无需关心存储引擎层的实现细节，直接调用handler对象的相关方法即可。

2. 降低了新引擎的引入门槛。如MyRocks。

测试数据

复制代码
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE t1 (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
k int(10) unsigned NOT NULL DEFAULT ‘0’,
c varchar(20) NOT NULL DEFAULT ‘’,
pad varchar(20) NOT NULL DEFAULT ‘’,
PRIMARY KEY (id),
KEY k (k)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> select count() from t1;
±---------+
| count() |
±---------+
| 100 |
±---------+
1 row in set (0.00 sec)

mysql> select * from t1 limit 6;
±—±–±-------±---------+
| id | k | c | pad |
±—±–±-------±---------+
| 1 | 1 | test_c | test_pad |
| 2 | 1 | test_c | test_pad |
| 3 | 1 | test_c | test_pad |
| 4 | 4 | test_c | test_pad |
| 5 | 5 | test_c | test_pad |
| 6 | 6 | test_c | test_pad |
±—±–±-------±---------+
6 rows in set (0.00 sec)
复制代码

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.

简而言之，即基于索引来定位记录，该值越大，代表基于索引的查询越多。

看看下面这个Demo。

复制代码
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id=1;
±—±–±-------±---------+
| id | k | c | pad |
±—±–±-------±---------+
| 1 | 1 | test_c | test_pad |
±—±–±-------±---------+
1 row in set (0.00 sec)

mysql> show status like ‘%Handler_read%’;
±----------------------±------+
| Variable_name | Value |
±----------------------±------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
±----------------------±------+
7 rows in set (0.00 sec)
复制代码
测试中有两点发现：

1. 无论是基于主键，还是二级索引进行等值查询，Handler_read_key都会加1。

2. 对于二级索引，如果返回了N条记录，Handler_read_next会相应加N。

Handler_read_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).

读取索引的第一个值，该值越大，代表涉及索引全扫描的查询越多。

但是，这并不意味着查询利用到了索引，还需要结合其它的Handler_read_xxx来分析。

看看下面这个Demo

复制代码
mysql> flush status;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t1 where c=‘0’;
Empty set (0.10 sec)

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 | 101 |
±----------------------±------+
7 rows in set (0.01 sec)
复制代码
基于c来查询，c不是索引，故走的是全表扫描（通过Handler_read_rnd_next的值和表的总行数也可判断出来），但Handler_read_first和 Handler_read_key同样也增加了。

下面再看看另外一个Demo

复制代码
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2 where c=‘0’;
Empty set (0.00 sec)

mysql> show status like ‘%Handler_read%’;
±----------------------±------+
| Variable_name | Value |
±----------------------±------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 101 |
±----------------------±------+
7 rows in set (0.00 sec)
复制代码
t2和t1基本一样，只不过t2是MyISAM表，此时只增加了Handler_read_rnd_next。

之所以会这样，是因为t1是Innodb表，而Innodb是索引组织表，全表扫描实际上是基于主键来做的，所以Handler_read_first和Handler_read_key都会相应加1。

而t2是MyISAM表，MyISAM是堆表。

所以，单凭Handler_read_first很难评估查询的优劣。

Handler_read_last

首先看看官档的解释

The number of requests to read the last key in an index. With ORDER BY, the server issues a first-key request followed by several next-key requests, whereas with ORDER BY DESC, the server issues a last-key request followed by several previous-key requests.

和Handler_read_first相反，是读取索引的最后一个值。

该值增加基本上可以判定查询中使用了基于索引的order by desc子句。

看看下面两个Demo

1. 基于主键的正向排序

复制代码
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 order by id limit 10;
…
10 rows in set (0.00 sec)

mysql> show status like ‘%Handler_read%’;
±----------------------±------+
| Variable_name | Value |
±----------------------±------+
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 9 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
±----------------------±------+
7 rows in set (0.00 sec)
复制代码
可以看到，增加的还是Handler_read_first和Handler_read_nex t。

2. 基于主键的反向排序

复制代码
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 order by id desc limit 10;
…
10 rows in set (0.00 sec)

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 | 9 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
±----------------------±------+
7 rows in set (0.00 sec)
复制代码
此时增加的是Handler_read_last和Handler_read_ prev。

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.

根据索引的顺序来读取下一行的值，常用于基于索引的范围扫描和order by limit子句中。

看看下面两个Demo

1. 基于索引的范围查询

复制代码
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where k < 2;
±—±–±-------±---------+
| id | k | c | pad |
±—±–±-------±---------+
| 1 | 1 | test_c | test_pad |
| 2 | 1 | test_c | test_pad |
| 3 | 1 | test_c | test_pad |
±—±–±-------±---------+
3 rows in set (0.00 sec)

mysql> show status like ‘%Handler_read%’;
±----------------------±------+
| Variable_name | Value |
±----------------------±------+
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 3 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
±----------------------±------+
7 rows in set (0.00 sec)
复制代码

2. 基于索引的order by子句

复制代码
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 force index(k) order by k limit 10;
…
10 rows in set (0.00 sec)

mysql> show status like ‘%Handler_read%’;
±----------------------±------+
| Variable_name | Value |
±----------------------±------+
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 9 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
±----------------------±------+
7 rows in set (0.01 sec)
复制代码
USB Microphone https://www.soft-voice.com/
Wooden Speakers https://www.zeshuiplatform.com/
亚马逊测评 www.yisuping.cn
深圳网站建设www.sz886.com