| SELECT MAX(log_time) FROM log_table WHERE log_machine IN (‘Machine 1') Union SELECT MAX(log_time) FROM log_table WHERE log_machine IN (‘Machine 2') ….. |
即对每个log_machine执行loose index scan，rows从原来的82636下降为16(该表总共1,000,000条记录)。
Group by何时使用loose index scan?
适用条件：
1 针对单表操作
2 Group by使用索引的最左前缀列
3 只支持聚集函数min()/max()
4 Where条件出现的列必须为=constant操作 ， 没出现在group by中的索引列必须使用constant
5 不支持前缀索引，即部分列索引 ，如index(c1(10))
执行计划的extra应该显示using index for group-by
假定表t1有个索引idx(c1,c2,c3)
| SELECT c1, c2 FROM t1 GROUP BY c1, c2; SELECT DISTINCT c1, c2 FROM t1; SELECT c1, MIN(c2) FROM t1 GROUP BY c1; SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2; SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2; SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2; SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2 SELECT c1, c3 FROM t1 GROUP BY c1, c2;--无法使用松散索引 |
而SELECT c1, c3 FROM t1 where c3= const GROUP BY c1, c2;则可以
紧凑索引扫描tight index scan
Group by在无法使用loose index scan，还可以选择tight，若两者都不可选，则只能借助临时表；
扫描索引时，须读取所有满足条件的索引键，要么是全索引扫描，要么是范围索引扫描；
Group by的索引列不连续；或者不是从最左前缀开始，但是where条件里出现最左列；
| SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3; SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3; |
5.6的改进
事实上，5.6的index condition push down可以弥补loose index scan缺失带来的性能损失。
KEY(age,zip)
| mysql> explain SELECT name FROM people WHERE age BETWEEN 18 AND 20 AND zip IN (12345,12346, 12347); +----+-------------+--------+-------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | people | range | age | age | 4 | NULL | 90556 | Using where | +----+-------------+--------+-------+---------------+------+---------+------+-------+-------------+ 1 row in set (0.01 sec) |
根据key_len=4可以推测出sql只用到索引的第一列，即先通过索引查出满足age (18,20)的行记录，然后从server层筛选出满足zip约束的行；
pre-5.6，对于复合索引，只有当引导列使用"="时才有机会在索引扫描时使用到后面的索引列。
