mysql 嵌套group by_MySQL数据库SQL优化之GROUP BY 语句和优化嵌套查询

1.优化GROUP BY 语句

默认状况下，MySQL对全部GROUP BY col1，col2，...的字段进行排序。这与在查询中指定ORDER BY col1，col2，...相似。mysql

所以，若是显示包括一个包含相同列的order by 子句，则对MySQL的实际执行性能没什么影响。sql

若是查询包括group by 但用户想要避免排序结果的消耗，则能够指定order by null 禁止排序，以下面的例子：性能

mysql> explain select payment_date,sum(amount) from payment group by payment_date;

+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-----------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-----------------+

| 1 | SIMPLE | payment | NULL | ALL | NULL | NULL | NULL | NULL | 16125 | 100.00 | Using temporary，Using filesort |

+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-----------------+

1 row in set, 1 warning (0.04 sec)

mysql> explain select payment_date,sum(amount) from payment group by payment_date order by null;

+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-----------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-----------------+

| 1 | SIMPLE | payment | NULL | ALL | NULL | NULL | NULL | NULL | 16125 | 100.00 | Using temporary |

+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-----------------+

1 row in set, 1 warning (0.00 sec)

从上面的例子能够看出，第一个sql语句须要进行“Filesort”，而第二个SQL因为ORDER BY NULL 不须要进行“Filesort”，而上文提过Filesort 每每很是耗费时间。优化

可是，在个人MySQL 8.0中，两种方式查询没有区别。code

2.优化嵌套查询

MySQL 4.1开始支持SQL的子查询。这个技术可使用SELECT 语句来建立一个单例的查询结果，而后把这个结果做为开始过滤条件在用在另外一个查询中。排序

使用子查询能够一次性的完成不少逻辑上须要多个步骤才能完成的SQL操做，同时也能够避免事务或者表死锁。而且写起来也很容易。可是，有些状况下，子查询能够被更有效率的链接(JION)替代。索引

在下面的例子中，要从客户表customer 中找到不在支付表payment 中的全部客户信息：事务

mysql> explain select * from customer where customer_id not in (select customer_id from payment);

+----+--------------------+----------+------------+----------------+--------------------+--------------------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+--------------------+----------+------------+----------------+--------------------+--------------------+---------+------+------+----------+-------------+

| 1 | PRIMARY | customer | NULL | ALL | NULL | NULL | NULL | NULL | 599 | 100.00 | Using where |

| 2 | DEPENDENT SUBQUERY | payment | NULL | index_subquery | idx_fk_customer_id | idx_fk_customer_id | 2 | func | 26 | 100.00 | Using index |

+----+--------------------+----------+------------+----------------+--------------------+--------------------+---------+------+------+----------+-------------+

2 rows in set, 1 warning (0.10 sec)

若是使用链接 JOIN 来完成这个查询工做，速度将会快不少。尤为是当payment 表中对customer_id 建有索引，性能将会更好，j具体查询以下：内存

mysql> explain select * from customer a left join payment b on a.customer_id = b.customer_id where b.customer_id is null;

+----+-------------+-------+------------+------+--------------------+--------------------+---------+----------------------+------+----------+-------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+--------------------+--------------------+---------+----------------------+------+----------+-------------------------+

| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 599 | 100.00 | NULL |

| 1 | SIMPLE | b | NULL | ref | idx_fk_customer_id | idx_fk_customer_id | 2 | sakila.a.customer_id | 26 | 100.00 | Using where; Not exists |

+----+-------------+-------+------------+------+--------------------+--------------------+---------+----------------------+------+----------+-------------------------+

2 rows in set, 1 warning (0.00 sec)

从执行计划中能够看出查询关联的类型从 index_subquery 调整为了ref ，在MySQL 5.5如下版本(包括5.5)，子查询的效率仍是不如关联查询JOIN.it

链接JOIN 之因此更有效率一些，是由于MySQL 不须要再内存中建立临时表来完成这个逻辑上须要两个步骤的查询工做。