mysql loose_关于mysql的loose index scan的几点疑问

关于MySQL的loose index scan有几点疑问，欢迎看到这篇文章的人一起探讨。

测试表结构：

CREATE TABLE `test` (

`id` int(11) NOT NULL default '0',

`v1` int(10) unsigned NOT NULL default '0',

`v2` int(10) unsigned NOT NULL default '0',

`v3` int(10) unsigned NOT NULL default '0',

PRIMARY KEY (`id`),

KEY `v1_v2_v3` (`v1`,`v2`,`v3`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

select * from test;

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

| id | v1 | v2 | v3 |

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

| 1 | 1 | 0 | 1 |

| 2 | 3 | 1 | 2 |

| 10 | 4 | 10 | 10 |

| 0 | 4 | 100 | 0 |

| 3 | 4 | 100 | 3 |

| 5 | 5 | 9 | 5 |

| 8 | 7 | 3 | 8 |

| 7 | 7 | 4 | 7 |

| 30 | 8 | 15 | 30 |

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

select version();

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

| version() |

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

| 5.0.51b-log |

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

由此我们可以大致画出索引的结构：

下面说下我纠结的实验过程：

mysql> explain select max(v3) from test where v1>3 group by v1,v2;

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

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

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

| 1 | SIMPLE | test | range | v1_v2_v3 | v1_v2_v3 | 8 | NULL | 1 | Using where; Using index for group-by |

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

1 row in set (0.00 sec)

一般来说，MySQL的索引扫描需要定义一个起点和终点，即使需要的数据只是这段索引中的几个，MySQL仍然需要扫描这段索引中的每一个条目，将它们返回给Sever层，Server层根据where条件将存储引擎返回的数据再进行一遍过滤。

但是根据官方文档9.2.1.16 GROUP BY Optimization的描述，这段sql会用到松散扫描索引，那么我有一点疑问：MySQL的loose index scan的工作原理究竟是怎样的呢？我有个猜想：

如上图，MySQL根据索引的前2列(v1,v2)来分组，此时先不读取v3列的值。MySQL扫描时发现(v1,v2)的分组值发生变化时，取上一个节点的v3值(因为是B-Tree，v3的值在相同的(v1,v2)中肯定是有序的，并且是从小到大)。这样的话MySQL就少扫描了一个v3的值。当(v1,v2)重复的越多，MySQL少扫描的v3列的次数越多。

如果MySQL全部读取的话，存储引擎需要将全部的数据返回给Server层，Server层还需要自己判断max(v3)，莫不如在扫描索引的时候顺便读取max(v3)了。

当我马上就要说服自己的时候，突然发现Explain结果的rows值为1。难道说MySQL估算只扫描一行就能算出结果？这时，我通过如下命令来看MySQL是如何扫描索引的：

mysql> flush status;

Query OK, 0 rows affected (0.00 sec)

mysql> select max(v3) from test where v1>3 group by v1,v2;

+---------+

| max(v3) |

+---------+

| 10 |

| 3 |

| 5 |

| 8 |

| 7 |

| 30 |

+---------+

6 rows in set (0.00 sec)

mysql> show session status like 'Handler_%';

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

| Variable_name | Value |

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

| Handler_commit | 0 |

| Handler_delete | 0 |

| Handler_discover | 0 |

| Handler_prepare | 0 |

| Handler_read_first | 0 |

| Handler_read_key | 15 |

| Handler_read_next | 0 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 0 |

| Handler_rollback | 0 |

| Handler_savepoint | 0 |

| Handler_savepoint_rollback | 0 |

| Handler_update | 0 |

| Handler_write | 14 |

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

15 rows in set (0.00 sec)

结果令我很惊奇，我们重点观察这两行数据：

| Handler_read_key | 15 |

| Handler_read_next | 0 |

Handler_read_next为0(Handler_read_next的意思是按照索引叶子节点顺序读取下一个节点的次数。6.1.7 Server Status Variables)。说明MySQL根本没有按照我上面的意思顺序扫描v1>3的叶子节点，到此只有三种解释了：

我猜想的MySQL松散扫描的方法不正确。

show session status like 'Handler_%'存在bug，没有计算出正确的值。

explain方法的rows列的估算方法存在bug，没有正确的估算出扫描的行数。

罢了，我换了个MySQL版本：

mysql> select version();

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

| version() |

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

| 5.7.14 |

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

1 row in set (0.00 sec)

执行同样的查询：

mysql> flush status;

Query OK, 0 rows affected (0.00 sec)

mysql> explain select max(v3) from test where v1>3 group by v1,v2\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test

partitions: NULL

type: range

possible_keys: v1_v2_v3

key: v1_v2_v3

key_len: 4

ref: NULL

rows: 7

filtered: 100.00

Extra: Using where; Using index

1 row in set, 1 warning (0.00 sec)

mysql> select max(v3) from test where v1>3 group by v1,v2;

+---------+

| max(v3) |

+---------+

| 10 |

| 3 |

| 5 |

| 8 |

| 7 |

| 30 |

+---------+

6 rows in set (0.00 sec)

mysql> show session status like 'Handler_%';

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

| Variable_name | Value |

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

| Handler_commit | 1 |

| Handler_delete | 0 |

| Handler_discover | 0 |

| Handler_external_lock | 2 |

| Handler_mrr_init | 0 |

| Handler_prepare | 0 |

| Handler_read_first | 0 |

| Handler_read_key | 1 |

| Handler_read_last | 0 |

| Handler_read_next | 7 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 0 |

| Handler_rollback | 0 |

| Handler_savepoint | 0 |

| Handler_savepoint_rollback | 0 |

| Handler_update | 0 |

| Handler_write | 0 |

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

18 rows in set (0.00 sec)

我惊喜的发现，换了版本之后，explain输出的rows值正常了，为7。show session status like 'Handler_%';输出的值看起来也正常了：

| Handler_read_key | 1 |

| Handler_read_last | 0 |

| Handler_read_next | 7 |

正当我以为是MySQL5.7修复了统计的bug时，我突然发现explain的Extra是这样的：

Extra: Using where; Using index

等等，这是什么鬼，这说明MySQL在运行这条Sql时根本没有使用松散扫描索引，怪不得统计输出结果是正常的。

参考资料：