今天是个特别的日子,特意为朋友们送出非SPJ的优化。
14.1.9 非SPJ优化
MySQL的查询优化器支持对非SPJ格式的查询语句进行优化,主要包括GROUPBY、ORDERBY、DISTINCT操作的优化。
1. GROUPBY优化
MySQL对于GROUPBY的处理,通常采用的方式是扫描整个表、创建一个临时表用以执行分组操作。所以查询执行计划中出现“Using temporary”字样就表示MySQL采用了常规的处理方式。
MySQL不支持分组转换技术。对于GROUPBY的优化,则尽量利用利用索引。利用索引的条件是:分组子句中的列对象源自同一个btree索引(不支持利用Hash索引进行优化)的全部或前缀部分的部分有序的键(分组使用的索引列与索引建立的顺序不匹配则不能使用索引)。主要的方式有“Loose Index Scan”和“Tight Index Scan”。
Loose Index Scan:直接用索引完成分组操作中对分组列的检索,不必考虑索引的全部键满足WHERE子句,只要有部分匹配WHERE中的列对象即可(loose,利用索引中部分列为“松散”)。
Tight Index Scan:索引中的全部键与WHERE子句中的列对象匹配(tight,利用索引中的全部列为“严密”)。
不管是“Loose Index Scan”还是“Tight Index Scan”,都是以WHERE中的列对象为依据,用索引匹配,若全部匹配上,则表示是“Tight Index Scan”;若部分匹配上,则表示是“Loose Index Scan”;若没有可匹配的,则表示不能利用索引进行分组操作的优化。
这两种优化方式有一些限制条件,可参考MySQL的官方文档。
下面我们通过具体示例来分析一下GROUPBY优化。
我们先创建表,命令如下:
CREATE TABLE t_god (a INT, b INT, c INT, d INT, e INT);
CREATE INDEX t_god_idx_1 ON t_god (a,b,c);
CREATE INDEX t_god_idx_2 ON t_god (d);
示例1 索引列上执行GROUPBY,支持GROUPBY优化(没有使用“Using filesort”类似的操作进行排序),查询执行计划如下:
mysql> EXPLAIN EXTENDED SELECT a FROM t_god GROUP BY a;
+----+-------------+-------+-------+-------------+-------------+
| id | select_type | table | type | key | Extra |
+----+-------------+-------+-------+-------------+-------------+
| 1 | SIMPLE | t_god | index | t_god_idx_1 | Using index |
+----+-------------+-------+-------+-------------+-------------+
1 row in set, 1 warning (0.00 sec)
从查询执行计划看,对表进行了索引扫描,并且Extra列的值为“Using index”,表明分组操作利用了索引。
示例2 索引列上执行ORDERBY,MySQL支持ORDERBY优化,查询执行计划如下:
test=# EXPLAIN SELECT a FROM t_god ORDER BY a;
+----+-------------+-------+-------+-------------+-------------+
| id | select_type | table | type | key | Extra |
+----+-------------+-------+-------+-------------+-------------+
| 1 | SIMPLE | t_god | index | t_god_idx_1 | Using index |
+----+-------------+-------+-------+-------------+-------------+
1 row in set, 1 warning (0.00 sec)
从查询执行计划看,对表进行了索引扫描,并且Extra列的值为“Using index”,表明排序操作利用了索引。
示例3 索引列上执行ORDERBY、GROUPBY,MySQL支持ORDERBY优化也支持GROUPBY优化。查询执行计划如下:
mysql> EXPLAIN SELECT a FROM t_god GROUP BY a ORDER BY a;
+----+-------------+-------+-------+-------------+-------------+
| id | select_type | table | type | key | Extra |
+----+-------------+-------+-------+-------------+-------------+
| 1 | SIMPLE | t_god | index | t_god_idx_1 | Using index |
+----+-------------+-------+-------+-------------+-------------+
1 row in set (0.00 sec)
从查询执行计划看,对表进行了索引扫描,并且Extra列的值为“Using index”,表明排序和分组操作利用了索引。
示例4 带有聚集操作,索引列上执行GROUPBY,MySQL支持GROUPBY优化。查询执行计划如下:
mysql> EXPLAIN SELECT a, MIN(b) FROM t_god WHERE c>2 GROUP BY a;
+----+-------------+-------+-------+-------------+--------------------------+
| id | select_type | table | type | key | Extra |
+----+-------------+-------+-------+-------------+--------------------------+
| 1 | SIMPLE | t_god | index | t_god_idx_1 | Using where; Using index |
+----+-------------+-------+-------+-------------+--------------------------+
1 row in set (0.05 sec)