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> 

 

如果是有过滤条件的子查询，查询过滤条件尽量加到子查询条件中，而不要加到外面