mysq联表查询优化：小表驱动大表

参考资料
了解MySQL联表查询中的驱动表，优化查询，以小表驱动大表
MySql小表驱动大表
MySQL高级知识（十六）——小表驱动大表

背景

有的时候我们在操作数据库时会将两个或多个数据表关联起来通过一些条件筛选数据，在关联表时我们要遵循一些原则，这样会使我们编写的SQL 语句在效率上快很多。

为什么要用小表驱动大表

1. 驱动表的定义
   当进行多表连接查询时， [驱动表] 的定义为：

• 指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]
• 未指定联接条件时，行数少的表为[驱动表]（Important!）

忠告：如果你搞不清楚该让谁做驱动表、谁 join 谁，请让 MySQL 运行时自行判断

既然“未指定联接条件时，行数少的表为[驱动表]”了，而且你也对自己写出的复杂的 Nested Loop Join 不太有把握（如下面的实例所示），就别指定谁 left/right join 谁了，请交给 MySQL优化器 运行时决定吧。

3. MySQL关联查询的概念
   MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。

• 例: user表10000条数据，class表20条数据

select * from user u left join class c u.userid=c.userid

这样则需要用user表循环10000次才能查询出来，而如果用class表驱动user表则只需要循环20次就能查询出来

• 例：

select * from class c left join user u c.userid=u.userid

小结果集驱动大结果集
de.cel 在2012年总结说，不管是你，还是 MySQL，优化的目标是尽可能减少JOIN中Nested Loop的循环次数。
以此保证：永远用小结果集驱动大结果集（Important）！

4. 举例说明
   类似循环嵌套

for(int i=5;.......)
{
     for(int j=1000;......)
     {}
}

如果小的循环在外层，对于数据库连接来说就只连接5次，进行5000次操作，如果1000在外，则需要进行1000次数据库连接，从而浪费资源，增加消耗。这就是为什么要小表驱动大表。

驱动联表查询

排序: 根据驱动表的字段排序

对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序（既废内存，又废cpu）！

explain

SELECT mb.id……

FROM mb LEFT JOIN mbei ON mb.id=mbei.mb_id INNER JOINu ON mb.uid=u.uid  

WHERE 1=1  

ORDER BY mb.id DESC

limit 0,10

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

查询: 小表驱动大表

当我们知道哪个表是小表时可以用小表驱动大表，如果不知道就直接去除所有的join，让MySQL自己决定

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

id select_type table   type    possible_keys      key          key_len  ref                rows    Extra
1  SIMPLE        mbei ALL      mb_id  (NULL)      (NULL)      (NULL)                         13388 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