mysql自身关联 慢吗_【转】[慢查优化]联表查询注意谁是驱动表 & 你搞不清楚谁join谁更好时请放手让mysql自行判定...

够复杂吧。Nested Loop Join 就是这样，

以驱动表的结果集作为循环的基础数据，然后将结果集中的数据作为过滤条件一条条地到下一个表中查询数据，最后合并结果；此时还有第三个表，则将前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此反复。

这条语句的执行计划如下：

id  select_type  table   type    possible_keys   key             key_len  ref                     rows  Extra

------  -----------  ------  ------  --------------  --------------  -------  -------------------  -------  --------------------------------------------

1  SIMPLE       mbindex   userid          userid          4        (NULL)               6060455Using index; Using temporary;Using filesort

1  SIMPLE       mbei    eq_ref  mb_id  mb_id  4        mb.id             1

1  SIMPLE       u       eq_ref  PRIMARY         PRIMARY         4        mb.uid        1  Using index

由于动用了“LEFT JOIN”，所以攻城狮已经指定了驱动表，虽然这张驱动表的结果集记录数达到百万级！

.

.

如何优化？

.

.

优化第一步：LEFT JOIN改为JOIN

干嘛要 left join 啊？直接 join！

explain

SELECT mb.id……

FROM mb JOINmbei ON mb.id=mbei.mb_idINNER JOINu ON mb.uid=u.uid

WHERE 1=1

ORDER BY mbei.apply_time DESC

limit 0,10

立竿见影，驱动表立刻变为小表 mbei 了， Using temporary 消失了，影响行数少多了：

id  select_type  table   type    possible_keys   key      key_len  ref                             rows  Extra

------  -----------  ------  ------  --------------  -------  -------  ----------------------------  ------  --------------

1  SIMPLE       mbeiALL     mb_id  (NULL)   (NULL)   (NULL)                         13383  Using filesort

1  SIMPLE       mb      eq_ref  PRIMARY,userid  PRIMARY  4        mbei.mb_id       1

1  SIMPLE       u       eq_ref  PRIMARY         PRIMARY  4        mb.uid                1  Using index

优化第一步之分支1：根据驱动表的字段排序，好吗？

left join不变。干嘛要根据非驱动表的字段排序呢？我们前面说过“对驱动表可以直接排序，对非驱动表(的字段排序)需要对循环查询的合并结果(临时表)进行排序！”的。

explain

SELECT mb.id……

FROM mb LEFTJOINmbei ON mb.id=mbei.mb_idINNER JOINu ON mb.uid=u.uid

WHERE 1=1

ORDER BY mb.id DESC

limit 0,10

也满足业务场景，做到了rows最小：

id  select_type  table   type    possible_keys   key             key_len  ref                    rows  Extra

------  -----------  ------  ------  --------------  --------------  -------  -------------------  ------  -----------

1  SIMPLE       mb      index   userid          PRIMARY         4        (NULL)                   10

1  SIMPLE       mbei    eq_ref  mb_id  mb_id  4        mb.id            1  Using index

1  SIMPLE       u       eq_ref  PRIMARY         PRIMARY         4        mb.uid       1  Using index

优化第二步：去除所有JOIN，让MySQL自行决定！

写这么多密密麻麻的 left join/inner join 很开心吗？

explain

SELECT mb.id……

FROM mb,mbei,u

WHERE

mb.id=mbei.mb_id

and mb.uid=u.user_id

order by mbei.apply_time desc

limit 0,10

立竿见影，驱动表一样是小表 mbei：