mysql group by优化
mysql> explain select actor.first_name,actor.last_name,count(*) from sakila.film_actor inner join sakila.actor using(actor_id) group by film_actor.actor_id\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 200 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: PRIMARY,idx_fk_film_id key: PRIMARY key_len: 2 ref: sakila.actor.actor_id rows: 1 Extra: Using index 2 rows in set (0.00 sec) mysql>
从explain看,上面的写法使用了临时表和文件排序
改写后
mysql> explain select actor.first_name,actor.last_name,c.cnt from sakila.actor inner join (select actor_id,count(*) as cnt from sakila.film_actor group by actor_id) as c using(actor_id)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: actor type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 200 Extra: NULL *************************** 2. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: ref possible_keys: <auto_key0> key: <auto_key0> key_len: 2 ref: sakila.actor.actor_id rows: 27 Extra: NULL *************************** 3. row *************************** id: 2 select_type: DERIVED table: film_actor type: index possible_keys: PRIMARY,idx_fk_film_id key: PRIMARY key_len: 4 ref: NULL rows: 5462 Extra: Using index 3 rows in set (0.00 sec) mysql>
如果是有过滤条件的子查询,查询过滤条件尽量加到子查询条件中,而不要加到外面
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。