技术文章摘抄

✅MySQL的驱动表是什么？MySQL怎么选的？

典型回答

驱动表是表连接中的基础表，也就是通过驱动表的数据结果集作为循环基础数据，然后一条一条的通过这个结果集的数据作为过滤条件到被驱动表中查询数据，然后再做合并。那么，也就意味着：驱动表在SQL语句执行的过程中先读取。而被驱动表在SQL语句执行的过程中后读取。

当我们知道MySQL的join的原理之后，其实就可以很容易的知道，驱动表的选择会决定着一条SQL的执行效率。所以，一条SQL中，该使用哪张表作为驱动表，其实是优化器决定的。

MySQL的优化器选择驱动表的原则是：更好的访问性能和筛选性能，所以，通常情况下， 会做以下几个方面的考量：

  • 表大小：这个很容易理解，小表作为驱动表可以更快地被扫描和匹配。所以优化器倾向于选择较小的表作为驱动表。

当然，如果两张表都没有索引，那么都需要全表扫描，那么在nested loop join下就是笛卡尔积， 那么小表驱动大表的提升就微乎其微。

但是如果我们考虑到有索引的情况、hash join的情况等的话，小表驱动大表还是优选的，所以总体来说，数据库还是会倾向于小表驱动大表。

  • 索引：在MySQL中，索引能大大的影响SQL的查询效率，所以选择可以利用索引进行加速访问的表作为驱动表可以提升效率。
  • where条件：如果查询中包含过滤条件，优化器会选择能够使用过滤条件进行筛选的表作为驱动表，以减少后续的匹配操作。
  • 连接类型：根据连接类型，INNER JOIN、LEFT JOIN、RIGHT JOIN等，优化器可能会做一些选择。比如left join会选择左表作为驱动表，主要是因为LEFT JOIN要返回左表中的所有记录，而右表中的匹配记录是可选的。通过以左表作为驱动表，可以确保返回左表中的所有记录。
    • left join： 左表是驱动表，右表是被驱动表
    • right join： 右表是驱动表，左表是被驱动表
    • inner join： 表数据量较小的表会由mysql自动选择作为驱动表

扩展知识

如何判断哪张表是驱动表

可以使用explain查看一下SQL的执行计划。在输出的执行计划中，排在第一行的表是驱动表，排在第二行的表是被驱动表。

+----+-------------+---------------+--------+------------------+---------+---------+--------------------------------+------+-------------+
| id | select_type | table         | type   | possible_keys    | key     | key_len | ref                            | rows | Extra       |
+----+-------------+---------------+--------+------------------+---------+---------+--------------------------------+------+-------------+
|  1 | SIMPLE      | orders        | ALL    | NULL             | NULL    | NULL    | NULL                           | 100  | Using where |
|  1 | SIMPLE      | order_details | ref    | order_id         | order_id| 4       | db_name.orders.order_id       | 2    | NULL        |
+----+-------------+---------------+--------+------------------+---------+---------+--------------------------------+------+-------------+

如上图，orders表为驱动表，order_details表为非驱动表。

上面的执行计划中，有一个id字段，这个是执行计划中每个操作的唯一标识符。对于一条查询语句，每个操作都有一个唯一的id。但是在多表join的时候，一次explain中的多条记录的id是相同的。而排在前面的表会作为驱动表先执行。

left join 一定是左表作为驱动表吗？

当然不一定，因为这是MySQL优化器决定的。当右表的数据量远远小于左表时，通过以右表作为驱动表可以更快地完成匹配操作。当右表上存在适合的索引或过滤条件，也可以通过右表作为驱动表来利用这些优化。

指定驱动表

在MySQL中，可以使用"STRAIGHTJOIN"关键字来强制指定JOIN语句中的表连接顺序。使用"STRAIGHTJOIN"可以告诉优化器使用查询中表的顺序，而不再重新优化连接顺序。

SELECT *
FROM table1 STRAIGHT_JOIN table2 ON table1.id = table2.id

但是，需要注意，STRAIGHT_JOIN只适用于内连接。

原文: https://www.yuque.com/hollis666/xkm7k3/vs83kfhxbz19mkcg