MySQL如何实现松散索引扫描

Question

最近，我面临一个问题，mysql是如何实现松散索引扫描的？

例如：

测试表结构是：

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 |
+----+----+-----+----+


Now let's see two sql:
first one:

mysql> explain select v1,v2 from test group by v1,v2;
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | range | NULL          | v1_v2_v3 | 8       | NULL |    3 | Using index for group-by |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

I know that Using index for group-by means MySQL use the loose index scan to query the sql.But why the explain output column rows is 3?I wonder how MySQL only scan three rows and get the query result.

second one:

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> explain select max(v2) 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 | 4       | NULL |    4 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)


the only difference between the above two sql is in the select list,one is max(v3),another one is max(v2).But why the max(v3) uses the loose index scan,the max(v2) don't use the loose index scan? I don't unnderstand the GROUP BY Optimization says:


  The only aggregate functions used in the select list (if any) are MIN() and MAX(), and all of them refer to the same column. The column must be in the index and must immediately follow the columns in the GROUP BY.


why the column must immediately follow the columns in the GROUP BY?

I am searching for a long time on net. But no use. Please help or try to give some ideas how to achieve this. 
Thanks!

Answer 1

这太长了，不能发表评论。

本质上，当问到“为什么优化器会以某种方式运行”时，答案是因为设计人员是这样实现的。如果你想知道“为什么”，你必须问他们。。。对于一个通用论坛来说，这不是一个合适的问题。

不过，我想指出几点。如果您认为max(v2)是一个bug，那么您可以在bugs.mysql.com上报告它。我不认为这是一个bug，有两个原因：

1. 文档显式地说明了优化是如何工作的，并且这个查询没有记录下来来使用索引("v2“不跟随group by中的键)。
2. 即使以不同的方式记录了它，在group by键上使用聚合函数，我应该说，是荒谬的。它是有效的SQL，但它只是冗长和不必要的。这样的构造在数据库实现者的优先级列表中是很低的。

最后，MySQL并不真正使用统计数据(非常好吗？)创建查询计划时。但是，在大多数数据库中，对9行(适合于单个数据页)的查询计划进行验证通常会导致查询计划进行全表扫描和“效率低下”的算法。例如，对于大量行，像冒泡排序这样的算法效率很低，但它可以是(非常)小行数上最有效的排序算法。

Answer 2

是否有任何理由在查询中使用max (v2)？即使不使用max ()函数，结果也是一样的。如果您将查询更改为“从测试中选择v2，其中v1> 3组按v1，v2 "，则将通过松散索引扫描方法完成。

下面是列必须立即跟随组中列的原因。

v1   v2   v3
1     1    1 
1     1    2
1     1    10
1     2    1
1     2    2 
1     2    8  

在这种情况下，从t1组中选择v1、v2来执行松散索引扫描。此操作如下图所示。

v1   v2   v3
1     1    1  
1     1    2
1     1    10  ------------------> 10 return
1     2    1
1     2    2 
1     2    8   ------------------>  8 return 

但是，如果通过v3从t1组执行select max ( v1 )，则不可能进行松散索引扫描。因为您必须访问所有键才能找到最大值(=10)。

v1   v2   v3
1     1    1   ------------------> (x) 
1     1    2   ------------------> (x)
1     1    10  ------------------> 10 return
1     2    1   ------------------> (x)
1     2    2   ------------------> (x)
1     2    8   ------------------> (x)

请注意，您可以使用以下命令查看使用松散索引扫描(或紧密索引扫描)访问多少记录。

flush status;

select max(v3) from t1 group by v1,v2;  -- perform loose index scan

show session status like 'Handler_read_key%';


flush status;

select max(v3) from t1 group by v1; -- perform tight index scan

show session status like 'Handler_read_key%';