本文同时发表在https://github.com/zhangyachen/zhangyachen.github.io/issues/102
关于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，没有正确的估算出扫描的行数。
在bugs.mysql.com上我找到了有人存在跟我一样的疑惑：Manual does not provide enough details on how loose index scan really works
罢了，我换了个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时根本没有使用松散扫描索引，怪不得统计输出结果是正常的。
我在*上关于loose index scan的提问：How MySQL implements the loose index scan
参考资料：