mysql嵌套groupby_MySQL数据库SQL优化之GROUPBY语句
和优化嵌套查询
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)
数据库优化sql语句
若是使⽤链接 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 不须要再内存中建⽴临时表来完成这个逻辑上须要两个步骤的查询⼯做。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。