MySQL查询优化器--非SPJ优化（一）--GROUPBY优化

今天是个特别的日子，特意为朋友们送出非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)

从查询执行计划看，对表进行了索引扫描，并且 Extra 列的值为“ Using index ”，表明带有 MIN 聚集函数操作的排序利用了索引。