mysql中文group优化_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,所以查询会较第二个查询耗时。