各种资料关于loose index scan的解释很拗口,其实等同于oracle的index skip scan。
对于复合索引(sex, id),前导列sex的值只有两个:M和F,
(`F',98)
(`F',100)
(`F',102)
(`F',104)
(`M',101)
(`M',103)
(`M',105)
则索引可以逻辑的分成两个子索引
当执行select * from t where id = N时,等价于
select * from t where id = N and sex =‘M’
union
select * from t where id = N and sex = 'F'
进而有效利用到索引进行查找,如果换成mysql,则只能执行全表扫描;
不考虑字段选择性的前提下,即便将sql写为select * from t where id = N and sex in(‘M’,'F'),pre-5.6在索引扫描时也不会使用到id列过滤,而是在读取出行记录后再进行id=N的筛选。
但是5.0之后group by在特定条件下可能使用到loose index scan,以下是来自http://explainextended.com/2010/05/08/max-and-min-on-a-composite-index/
的案例。
CREATE TABLE log_table (
id INT NOT NULL PRIMARY KEY,
log_machine VARCHAR(20) NOT NULL,
log_time DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE INDEX ix_log_machine_time ON log_table (log_machine, log_time);
1
SELECT MAX(log_time) FROM log_table;
SELECT MAX(log_time) FROM log_table WHERE log_machine IN ('Machine 1');
这两条sql都只需一次index seek便可返回,源于索引的有序排序,优化器意识到min/max位于最左/右块,从而避免范围扫描;
extra显示Select tables optimized away ;
2
SELECT MAX(log_time) FROM log_table WHERE log_machine IN (‘Machine 1’,’Machine 2’,’Machine 3’,’Machine 4’);
执行计划type 为range(extra显示using where; using index),即执行索引范围扫描,先读取所有满足log_machine约束的记录,然后对其遍历找出max value;
改进
SELECT MAX(log_time) FROM log_table WHERE log_machine IN (‘Machine 1’,’Machine 2’,’Machine 3’,’Machine 4’) group by log_machine order by 1 desc limit 1;
这满足group by选择loose index scan的要求,执行计划的extra显示using index for group-by,执行效果等值于
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;
http://blog.itpub.net/15480802/viewspace-757562/
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,对于复合索引,只有当引导列使用"="时才有机会在索引扫描时使用到后面的索引列。
mysql> explain SELECT name FROM people WHERE age=18 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 | 8 | NULL | 3 | Using where |
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
对比一下查询效率
mysql> SELECT sql_no_cache name FROM people WHERE age=19 AND zip IN (12345,12346, 12347);
+----------------------------------+
| name |
+----------------------------------+
| 888ba838661aff00bbbce114a2a22423 |
+----------------------------------+
1 row in set (0.06 sec)
mysql> SELECT SQL_NO_CACHE name FROM people WHERE age BETWEEN 18 AND 22 AND zip IN (12345,12346, 12347);
+----------------------------------+
| name |
+----------------------------------+
| ed4481336eb9adca222fd404fa15658e |
| 888ba838661aff00bbbce114a2a22423 |
+----------------------------------+
2 rows in set (1 min 56.09 sec)
对于第二条sql,可以使用union改写,
mysql> SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347)
-> UNION ALL
-> SELECT name FROM people WHERE age=19 AND zip IN (12345,12346, 12347)
-> UNION ALL
-> SELECT name FROM people WHERE age=20 AND zip IN (12345,12346, 12347)
-> UNION ALL
-> SELECT name FROM people WHERE age=21 AND zip IN (12345,12346, 12347)
-> UNION ALL
-> SELECT name FROM people WHERE age=22 AND zip IN (12345,12346, 12347);
而mysql5.6引入了index condition pushdown,从优化器层面解决了此类问题。
http://www.percona.com/blog/2006/08/10/using-union-to-implement-loose-index-scan-to-mysql/
http://blog.itpub.net/15480802/viewspace-1117157/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-1427989/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-1427989/