SQL慢查询优化之联表查询

柔光的暖阳◎ 2022-05-29 06:54 287阅读 0赞

## 一、前提基础 ##

###  ###

### 1、关联查询： ###

MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。

###  ###

### 2、驱动表定义：    ###

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

2）未指定联接条件时，行数少的表为驱动表。（**划重点！！！**）。

对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序（**划重点！！！**）。

### 3、Explain结果详情： ###

请参考我的另一篇文章：[Explain分析][Explain]

Explain的结果中，第一行出现的表就是驱动表（**划重点！！！**）

![Image 1][]

在上面的Explain结果中，id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

**建议：**如果你搞不清楚该让谁做驱动表、谁 join 谁，请让 MySQL 运行时自行判断  
既然“未指定联接条件时，行数少的表为\[驱动表\]”了，  
而且你也对自己写出的复杂的 Nested Loop Join 不太有把握，  
就别指定谁 left/right join 谁了，  
请交给 MySQL优化器 运行时决定吧。  
如果对自己特别有信心，可以参考：[像火丁一样优化][Link 1]。  
  
优化的目标是尽可能减少JOIN中Nested Loop的循环次数，以此保证：

永远用小结果集驱动大结果集（**圈重点！！！**）。

## 二、实例分析 ##

### **1、SQL语句如下：** ###

SELECT
        shipD.ID,
        shipD.SHIPMENT_NO,
        shipD.OUTBOUND_ID,
        shipD.OUTBOUND_NO,
        orderM.OUTBOUND_TYPE,
        orderM.OUTBOUNDSUB_TYPE,
        shipD.GOODS_VOLUMN,
        shipD.PACKING_UNIT,
        shipD.PACKING_QTY,
        shipD.PICKTASK_ID,
        shipD.TASK_TYPE,
        ...
        orderM.dest_warehouse_no,
        orderM.dest_distribute_no,
        orderM.dest_org_no
    FROM
        OB_INTERNAL_SHIP_M shipM
    JOIN OB_INTERNAL_SHIP_D shipD ON shipD.shipment_no = shipM.shipment_no
    AND shipM.ORG_NO = shipD.ORG_NO
    AND shipM.warehouse_no = shipD.WAREHOUSE_NO
    JOIN OB_INTERNAL_ORDER_M orderM ON shipD.outbound_no = orderM.outbound_no
    WHERE
        shipM.BILL_NO = 'CD117090620'
    AND shipM.HAND_NO = '43017090603370789'
    AND shipD.ORG_NO = '4'
    AND shipD.DISTRIBUTE_NO = '4'
    AND shipD.WAREHOUSE_NO = '30'

**Explain结果：**

![Image 1][]

**表中现有索引：**

ob_internal_ship_m：  KEY `idx_update_time` (`UPDATE_TIME`),  KEY ‘idx_SHIPMENT_NO’ (‘SHIPMENT_NO’)
    ob_internal_ship_d：   KEY `idx_update_time` (`UPDATE_TIME`),  KEY `idx_unique_key` (`UNIQUE_KEY`)   KEY ‘idx_SHIPMENT_NO’ (‘SHIPMENT_NO’),  KEY ‘idx_BOX_NO’(‘BOX_NO’)
    ob_internal_order_m：  UNIQUE KEY `OB_INTERNAL_ORDER_m_index` (`OUTBOUND_NO`),  KEY `idx_update_time` (`UPDATE_TIME`),  KEY’idx_outbound_no’(‘OUTBOUND_NO’), KEY’idx_WAVE_NO’(‘WAVE_NO’), KEY’idx_OPRATE_STATUS’(‘OPRATE_STATUS’)

**解决思路：**  
联查时on后面的条件不满足时可能会出现全表查询，where查询条件也没有shipment\_no,确定shipment\_no是否满足查询，若不满足，考虑将现有where条件中的bill\_no, hand\_no加上索引。  
**最终方案：**

业务代码中有两处使用该sql语句，其中一个无法获取shipment\_no，所以采用添加索引的方式，将shipment\_m表新增KEY \`idx\_bill\_no\` (\`BILL\_NO\`), KEY \`idx\_hand\_no\` (\`HAND\_NO\`)。

### 2、SQL语句如下： ###

SELECT DISTINCT
        (waveM.wave_no)
    FROM
        OB_INTERNAL_WAVE_M waveM
    LEFT JOIN OB_INTERNAL_ORDER_M orderM ON waveM.wave_no = orderM.wave_no
    WHERE
        waveM.YN = N
    AND waveM.ORG_NO = 'S'
    AND waveM.DISTRIBUTE_NO = 'S'
    AND waveM.WAREHOUSE_NO = 'S'
    AND waveM.OUTBOUND_TYPE = 'S'
    AND waveM.WAVE_TYPE = 'S'
    AND waveM.WAVE_STATUS = N
    AND orderM.outboundsub_type IN ('S', 'S', 'S')
    ORDER BY
    waveM.CREATE_TIME DESC

**Explain结果：**

![Image 1][]

**表中现有索引：**

ob_internal_order_m：  UNIQUE KEY `OB_INTERNAL_ORDER_m_index` (`OUTBOUND_NO`),  KEY `idx_update_time` (`UPDATE_TIME`),  KEY’idx_outbound_no’(‘OUTBOUND_NO’), KEY’idx_WAVE_NO’(‘WAVE_NO’), KEY’idx_OPRATE_STATUS’(‘OPRATE_STATUS’)
    ob_internal_wave_m：   KEY `idx_update_time` (`UPDATE_TIME`),  KEY `idx_wave_no` (`WAVE_NO`),  KEY `idx_wave_type` (`WAVE_TYPE`),  KEY `idx_source` (`SOURCE`),  KEY ’WAVE_STATUS’(‘WAVE_STATUS’)

**解决思路：**  
确定业务逻辑是否需要对SQL排序，去掉distinct去重，在业务层做去重。  
**最终方案：**

业务中不需要排序，去掉order by，去掉sql中的distinct，在业务层做去重。（如业务中需要排序，考虑将wave\_m表中的update\_time或者主键id作为order by条件）

**PS：**去掉distinct可解决Using temporary临时表问题，去掉order by或使用update\_time或id可解决Using filesort问题。

## 三、归纳总结    ##

不要太过于相信生产环境的SQL的执行速度，多观察执行效率慢的SQL语句，不要只看执行的瞬时时间。使用Explain，如出现以下常见的情况，请 注意优化：

*  type出现ALL或者index的；
 *  possible\_keys出现过多（待选）索引；
 *  key为NULL的（没走索引）；
 *  rows过多，或者几乎是全表的记录数的；
 *  Extra中出现Using temporary的，（Using filesort需视情况而定）；

**说明：**  
type为ALL或index，all最坏情况全表查，index和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多。  
  
Using temporary，用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。

Using filesort，MySQL有两种方式可以生成有序的结果，通过排序操作或者使用索引，当Extra中出现了Using filesort 说明MySQL使用了后者，但注意虽然叫filesort但并不是说明就是用了文件来进行排序，只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快，所以一般这时也要考虑优化查询了。使用文件完成排序操作，这是可能是ordery by，group by语句的结果，这可能是一个CPU密集型的过程，可以通过选择合适的索引来改进性能，用索引来为查询结果排序。

**日常SQL优化的大体思路：**

使用Explain，确定是否出现如上需要优化的情况；

理清该SQL对应的业务逻辑（如：为什么这么写，改动对现有业务有无影响，在业务层改动是否可行等）；  
现有条件能否满足优化需求（如：已存在索引等）；  
以上方法无法满足现有优化需求时，最后在考虑是否对现有表结构或SQL语句进行改动（如：新增索引，join是否去掉等）。

总之，SQL优化是一个复杂的过程，需要慢工出细活，其中的道理需要我们慢慢积累，逐步进阶。

**参考资料：**

[https://www.cnblogs.com/zhengyun\_ustc/p/slowquery1.html][https_www.cnblogs.com_zhengyun_ustc_p_slowquery1.html]

[Explain]: http://blog.csdn.net/zhendong_Z/article/details/79363828
[Image 1]: 
[Link 1]: https://huoding.com/2013/06/04/261
[https_www.cnblogs.com_zhengyun_ustc_p_slowquery1.html]: https://www.cnblogs.com/zhengyun_ustc/p/slowquery1.html