MySql小表驱动大表

怼烎@ 2022-06-05 11:08 163阅读 0赞

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

#### 一、优化原则 ####

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

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

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

查询结果：由于有很多的员工信息，在这里我就只查询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 | +-------------+----------+------------+--------------+---------+

接下里使用`EXISTS` 来查询数据：

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

查询结果：与上面的结果一样。

既然`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

这里虽然我们编写的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

我们可以将`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` 差别不大。