超详细的MySQL handler相关状态参数解释

概述

MySQL“自古以来”都有一个神秘的超详参数HANDLER命令，而此命令非SQL标准语法，细的相关可以降低优化器对于SQL语句的状态解析与优化开销，从而提升查询性能。解释

一、超详参数Handler参数列表

mysql> show global status like Handle%; 

参数介绍如下：

二、细的相关实际优化中比较看重的状态几个参数

1. Handler_read_first和Handler_read_rnd_next

前者表示全索引扫描的次数，当前者值较大，解释说明可能是超详参数一个全索引扫描，此外走全表也可能导致这个值比较大;后者表示在进行数据文件扫描时，细的云南idc服务商相关从数据文件里取数据的状态次数。当后者值较大，解释说明扫描的超详参数行非常多，可能没有合理的细的相关使用索引

2. Handler_read_key

这个表示走索引的次数，如果这个值比较大，状态说明索引使用良好

三、实验演示

1. 准备数据

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( ) )

2. limit 2观察Handler_read_first、Handler_read_rnd_next、Handler_read_key

FLUSH STATUS; select * from test limit 2; SHOW SESSION STATUS LIKE handler_read%; explain select * from test limit 2;

可以看到全表扫描其实也是走了key(Handler_read_key=1),可能是因为索引组织表的原因。源码库因为limit 2 所以rnd_next为2.这个Stop Key在执行计划中是看不出来的。

3. 索引消除排序(升序)，只走索引

FLUSH STATUS; select data from test order by data limit 4; SHOW SESSION STATUS LIKE handler_read%; explain select data from test order by data limit 4;

使用索引消除排序，因为是升序，所以read first为1,由于limit 4,所以read_next为3，因为只从索引拿，不从数据文件里取数据所以rnd_next为0，索引通过这个可以看出Stop Key.

4. 索引消除排序(倒序)

FLUSH STATUS; select data from test order by data desc limit 3; SHOW SESSION STATUS LIKE handler_read%; explain select data from test order by data desc limit 3;

使用索引消除排序，因为是倒序，所以read_last为1,read_prev为2.因为往回读了两个key.

5. 没有使用索引

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; FLUSH STATUS; select * from test order by file_sort limit 4; SHOW SESSION STATUS LIKE handler_read%; explain select * from test order by file_sort limit 4;

Handler_read_rnd为4 说明没有使用索引，rnd_next为11说明扫描了所有的数据,read key总是read_rnd+1。云服务器提供商