[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 条件