mysql group by最佳化

標籤：style   blog   ar   color   os   使用   sp   on   檔案   

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: ALLpossible_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: refpossible_keys: PRIMARY,idx_fk_film_id          key: PRIMARY      key_len: 2          ref: sakila.actor.actor_id         rows: 1        Extra: Using index2 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: ALLpossible_keys: PRIMARY          key: NULL      key_len: NULL          ref: NULL         rows: 200        Extra: NULL*************************** 2. row ***************************           id: 1  select_type: PRIMARY        table: <derived2>         type: refpossible_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: indexpossible_keys: PRIMARY,idx_fk_film_id          key: PRIMARY      key_len: 4          ref: NULL         rows: 5462        Extra: Using index3 rows in set (0.00 sec)mysql> 

 

如果是有過濾條件的子查詢，查詢過濾條件盡量加到子查詢條件中，而不要加到外面

 

mysql group by最佳化