MySql 優化 group by 語句

默認情況下，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,所以查詢會較第二個查詢耗時。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！