explain select actor.first_name, actor.last_name, count(*) from (film_actor inner join actor on film_actor.actor_id = actor.actor_id) group by actor.actor_id\G; 与这个相比， 我的这种写法的分析结果其实更好一点，区别就在于最后group by 的actor_id是用的actor的， 结果如下， 请问为何会这样。
+----+-------------+------------+-------+---------------+---------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-----------------------+------+-------------+
| 1 | SIMPLE | actor | index | PRIMARY | PRIMARY | 2 | NULL | 15 | |
| 1 | SIMPLE | film_actor | ref | PRIMARY | PRIMARY | 2 | sakila.actor.actor_id | 13 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+-----------------------+------+-------------+
个回答
-
- zhangxianggis
- 2015-12-01 19:23:33
同问
难道是主键的原因？
在actor表中actor_id是主键
而在film_actor表中('actor_id','film_id')组合作为主键
在两个执行计划的对比中看到
如果group by actor.actor_id
在查actor表时 用到了primary key
而 GROUP BY film_actor.actor_id 时 没有用到索引
