一之Yu
2014-10-30 00:03
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 |
+----+-------------+------------+-------+---------------+---------+---------+-----------------------+------+-------------+
同问
难道是主键的原因?
在actor表中actor_id是主键
而在film_actor表中('actor_id','film_id')组合作为主键
在两个执行计划的对比中看到
如果group by actor.actor_id
在查actor表时 用到了primary key
而 GROUP BY film_actor.actor_id 时 没有用到索引
性能优化之MySQL优化
101781 学习 · 235 问题
相似问题