MySql 优化 group by 语句

最新推荐文章于 2024-05-23 14:27:44 发布

weixin_34227447

最新推荐文章于 2024-05-23 14:27:44 发布

阅读量176

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/3387320/blog/3014061

版权

2019独角兽企业重金招聘Python工程师标准>>>

默认情况下，Mysql 对所有 group by 的字段进行排序，如果查询包括 group by ，用户想要避免排序结果的消耗。可以指定 order by null 禁止排序。

mysql> EXPLAIN select * from sys_log group by title;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                           |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
|  1 | SIMPLE      | sys_log | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 23733 |   100.00 | Using temporary; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+

mysql> EXPLAIN select * from sys_log group by title order by null;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra           |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
|  1 | SIMPLE      | sys_log | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 23733 |   100.00 | Using temporary |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-----------------+

从上面的例子可以看出，第一个查询的 Extra 多了一个 filesort,所以查询会较第二个查询耗时。

转载于:https://my.oschina.net/u/3387320/blog/3014061