[MYSQL]关于join的慢sql优化

结论：

● 多表关联查询时，保证被关联的字段需要有索引**（最关键）**

● 尽量避免三个表的 join。

● 需要 join 的字段，数据类型必须绝对一致；

● 注意排序字段，可能会导致索引失效，进行全表扫描

● 只使用单一表进行orderby，不要使用两张表的字段排序

排查过程

sql复现

SELECT
*
FROM
  `api_console_acq_business_task` task
  LEFT JOIN api_console_acq_business business on task.business_id = business.business_id
  LEFT JOIN api_console_acq_task_bill bill on task.`local_task_id` = bill.`task_id`
order by
  id desc
limit
  10, 10

索引情况如下

task：有business_id索引,有local_task_id索引

business： 无business_id索引

bill：有task_id索引

执行计划如下

执行时间：120s

主要都是用了全表查询，效率极低

异常原因

多表关联查询时，关联的字段无索引

在 SQL 语句中，task是主表，而 business是关联表。

● 你 JOIN 了 business表，但该表只有 80 条数据，查询它本身应该不慢。

● 但如果 B.business_id没有索引，数据库在 JOIN 时可能会进行 全表扫描（Full Table Scan） ，每次都要遍历整张表去匹配 business_id，导致查询变慢。

● 索引建立后，数据库可以直接通过索引快速查找 business_id，避免全表扫描，提高查询效率。

orderby影响

该sql去除排序后，执行速度恢复正常；由于业务需要，该表需要按照id倒叙

1. 索引未被利用，导致全表扫描（ALL）

   ○ 如果 id 没有索引或者查询优化器选择不使用索引，MySQL 可能需要 扫描整个表，然后进行排序，速度自然会慢。

2. 涉及 JOIN，多表数据排序开销大

   ○ LEFT JOIN 可能导致 大量数据被合并，即使 task 表有索引，最终的 结果集仍可能较大，MySQL 可能会创建临时表并进行 filesort。

3. Using filesort 导致磁盘 IO 变高

   ○ ORDER BY 在某些情况下会触发 外部排序（filesort），特别是在 未使用合适索引 或 数据量较大 的情况下。

解决如下

多表关联查询时，保证被关联的字段需要有索引

给business表新增一个idx_businessId索引内容

修改后，执行计划如下

执行时间：70ms

join扩充

原来MySQL内部采用了一种叫做 nested loop join （嵌套循环连接） 的算法。Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过 该结果集中的数据作为过滤条件到下一个表中查询数据 ，然后合并结果。如果还有第三个参与 Join，则再通过前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复，基本上MySQL采用的是最容易理解的算法来实现join

由上述内容克制

● 每次循环都能快速匹配到 business 和 bill 的记录，所以索引要 覆盖 JOIN 条件