MySql小表驱动大表

转载：https://blog.youkuaiyun.com/dc2222333/article/details/78234649

https://blog.youkuaiyun.com/codejas/article/details/78632883

一、为什么要用小表驱动大表

1、驱动表的定义

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

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

2）未指定联接条件时，行数少的表为[驱动表]（Important!）

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

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

如果您对自己特别有信心

2、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）！

二、优化联表查询
优化第一步之：根据驱动表的字段排序

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

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第一张表就是驱动表，数据量比其它两张表都要小！

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

三、总结

1、不要过于相信你的运气！

2、不要相信你的开发环境里SQL的执行速度！

3、请拿起 explain 武器，如果你看到以下现象，请优化：

1）出现了Using temporary

2）rows过多，或者几乎是全表的记录数

3）key 是 (NULL)

4）possible_keys 出现过多（待选）索引
--------------------- 
作者：程序人生dc 
来源：优快云 
原文：https://blog.youkuaiyun.com/dc2222333/article/details/78234649 
版权声明：本文为博主原创文章，转载请附上博文链接！

 

 

 

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

一、优化原则

小表驱动大表，即小的数据集驱动大得数据集。在知道什么是小表驱动达大表之前，我们先来了解两个查询关键字，IN 与 EXISTS。我们通过两段查询语句先来了解一下它们的作用。我建立了两张表，一张员工表，一张部门表，员工表中有部门id 这个属性，将这两张表关联起来。

我们先使用IN 来查询数据：

SELECT * 
FROM t_emp 
WHERE dept_id IN (SELECT dept_id FROM t_dept) 
LIMIT 5;
1
2
3
4
查询结果：由于有很多的员工信息，在这里我就只查询5 条数据。

+-------------+----------+------------+--------------+---------+
| emp_id      | emp_name | emp_gender | emp_email    | dept_id |
+-------------+----------+------------+--------------+---------+
| 00000000177 | 41d80    | m          | 41d80@zc.com |       1 |
| 00000000178 | a74b8    | m          | a74b8@zc.com |       1 |
| 00000000179 | 661ca    | m          | 661ca@zc.com |       1 |
| 00000000180 | 9413d    | m          | 9413d@zc.com |       1 |
| 00000000181 | 7d577    | m          | 7d577@zc.com |       1 |
+-------------+----------+------------+--------------+---------+
1
2
3
4
5
6
7
8
9
接下里使用EXISTS 来查询数据：

 SELECT * 
 FROM t_emp 
 WHERE EXISTS 
     (SELECT 1 
     FROM t_dept 
     WHERE t_dept.dept_id = t_emp.dept_id) 
 LIMIT 5;
1
2
3
4
5
6
7
查询结果：与上面的结果一样。

+-------------+----------+------------+--------------+---------+
| emp_id      | emp_name | emp_gender | emp_email    | dept_id |
+-------------+----------+------------+--------------+---------+
| 00000000177 | 41d80    | m          | 41d80@zc.com |       1 |
| 00000000178 | a74b8    | m          | a74b8@zc.com |       1 |
| 00000000179 | 661ca    | m          | 661ca@zc.com |       1 |
| 00000000180 | 9413d    | m          | 9413d@zc.com |       1 |
| 00000000181 | 7d577    | m          | 7d577@zc.com |       1 |
+-------------+----------+------------+--------------+---------+
1
2
3
4
5
6
7
8
9
既然IN 和 EXISTS 都可以用来查询数据，那它们两个有什么区别呢？

SELECT * 
FROM t_emp 
WHERE dept_id IN 
    (SELECT dept_id 
    FROM t_dept);

// 这条SQL 语句相当于：
for SELECT dept_id FROM t_dept
    for SELECT * FROM t_emp WHERE t_emp.dept_id = t_dept.dept_id
1
2
3
4
5
6
7
8
9
这里虽然我们编写的SQL 语句是主查询员工信息，子查询部门id ，但是MySql 的执行顺序会先执行子查询，再执行主查询，然后获得我们要查询的数据。

 SELECT * 
 FROM t_emp 
 WHERE EXISTS 
     (SELECT 1 
     FROM t_dept 
     WHERE t_dept.dept_id = t_emp.dept_id);

// 这条SQL 语句相当于：     
for SELECT * FROM t_emp 
    for SELECT * FROM t_dept  WHERE t_dept.dept_id = t_emp.dept_id          
1
2
3
4
5
6
7
8
9
10
我们可以将EXISTS 语法理解为：将主查询的数据放在子查询中做条件验证，根据结果TRUE 和 FALSE 来决定主查询中的数据是否需要保留。EXISTS 子查询只返回TRUE 或 FALSE ，因此子查询中的SELECT * 可以是SELECT 1 或者其他，MySql 的官方说在实际执行时会忽略SELECT 清单，因此是没有 什么区别的。EXISTS 子查询其实在执行时，MySql 已经对它做了一些优化并不是对每条数据进行对比。

二、总结

在实际操作过程中我们要对两张表的dept_id 都设置索引。在一开始我们就讲了一个优化原则即：小表驱动大表，在我们使用IN 进行关联查询时，通过上面IN 操作的执行顺序，我们是先查询部门表再根据部门表查出来的id 信息查询员工信息。我们都知道员工表肯定会有很多的员工信息，但是部门表一般只会有很少的数据信息，我们事先通过查询部门表信息查询员工信息，以小表(t_dept)的查询结果，去驱动大表(t_emp)，这种查询方式是效率很高的，也是值得提倡的。

但是我们使用EXISTS 查询时，首先查询员工表，然后根据部门表的查询条件返回的TRUE 或者 FALSE ，再决定员工表中的信息是否需要保留。这不就是用大的数据表(t_emp) 去驱动小的数据表小的数据表(t_dept)了吗？虽然这种方式也可以查出我们想要的数据，但是这种查询方式是不值得提倡的。

当t_emp 表中数据多于 t_dept 表中的数据时，这时我们使用IN 优于 EXISTS。当t_dept 表中数据多于 t_emp 表中的数据时(我们这里只是假设)，这时我们使用EXISTS 优于 IN。因此是使用IN 还是使用EXISTS 就需要根据我们的需求决定了。但是如果两张表中的数据量差不多时那么是使用IN 还是使用 EXISTS 差别不大。
--------------------- 
作者：留兰香丶 
来源：优快云 
原文：https://blog.youkuaiyun.com/codejas/article/details/78632883 
版权声明：本文为博主原创文章，转载请附上博文链接！