作者：Peter Zaitsev
mysql> show create table tbl \G
*************************** 1. row ***************************
Table: tbl
Create Table: CREATE TABLE `tbl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`g` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2340933 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
并且以不同方式执行相同的 GROUP BY 语句：
mysql> select k, count(*) c from tbl group by k order by k limit 5;
+---+---+
| k | c |
+---+---+
| 2 | 3 |
| 4 | 1 |
| 5 | 2 |
| 8 | 1 |
| 9 | 1 |
+---+---+
5 rows in set (0.00 sec)
mysql> explain select k, count(*) c from tbl group by k order by k limit 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
partitions: NULL
type: index
possible_keys: k
key: k
key_len: 4
ref: NULL
rows: 5
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
如果您有少量组，并且没有覆盖索引，索引顺序扫描可能会导致大量 IO。所以这可能不是最优化的计划。
mysql> explain select SQL_BIG_RESULT g, count(*) c from tbl group by g limit 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 998490
filtered: 100.00
Extra: Using filesort
1 row in set, 1 warning (0.00 sec)
mysql> select SQL_BIG_RESULT g, count(*) c from tbl group by g limit 5;
+---+---+
| g | c |
+---+---+
| 0 | 1 |
| 1 | 2 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
+---+---+
5 rows in set (0.88 sec)
一般来说，MySQL 只有在我们拥有大量组时才更喜欢使用这个计划，因为在这种情况下，排序比拥有临时表更有效（我们将在下面讨论）。
mysql> explain select g, sum(g) s from tbl group by g limit 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 998490
filtered: 100.00
Extra: Using temporary
1 row in set, 1 warning (0.00 sec)
mysql> select g, sum(g) s from tbl group by g order by null limit 5;
+---+------+
| g | s |
+---+------+
| 0 | 0 |
| 1 | 2 |
| 4 | 4 |
| 5 | 5 |
| 6 | 12 |
+---+------+
5 rows in set (7.75 sec)
mysql> explain select g, sum(g) s from tbl group by g limit 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 998490
filtered: 100.00
Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)
如果要强制 MySQL 使用为 GROUP BY 执行临时表的计划，可以使用 SQL_SMALL_RESULT 提示。
mysql> explain select k,max(id) from tbl group by k \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
partitions: NULL
type: range
possible_keys: k
key: k
key_len: 4
ref: NULL
rows: 2
filtered: 100.00
Extra: Using index for group-by
1 row in set, 1 warning (0.00 sec)
mysql> select k,max(id) from tbl group by k;
+---+---------+
| k | max(id) |
+---+---------+
| 0 | 2340920 |
| 1 | 2340916 |
| 2 | 2340932 |
| 3 | 2340928 |
| 4 | 2340924 |
+---+---------+
5 rows in set (0.00 sec)
mysql> explain select max(k) from tbl \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Select tables optimized away
1 row in set, 1 warning (0.00 sec)
过滤和分组
mysql> explain select g, sum(g) s from tbl where k>4 group by g order by NULL limit 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
partitions: NULL
type: range
possible_keys: k
key: k
key_len: 4
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition; Using temporary
1 row in set, 1 warning (0.00 sec)
mysql> alter table tbl add key(g);
Query OK, 0 rows affected (4.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select g, sum(g) s from tbl where k>1 group by g limit 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
partitions: NULL
type: index
possible_keys: k,g
key: g
key_len: 4
ref: NULL
rows: 16
filtered: 50.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> explain select g, sum(g) s from tbl where k>4 group by g limit 5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
partitions: NULL
type: range
possible_keys: k,g
key: k
key_len: 4
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)
近期社区动态
