SQL慢查询优化之联表查询
一、前提基础
1、关联查询：
MySQL 表关联的算法是Nest Loop Join, 是由主表返回的数据集作为循环的基础, 逐条查询该数据集中对应的记录并传递至下一个表进行过滤条件下的数据获取, 最后将所有结果进行整合.
2、驱动表定义：
1）制定了联接条件时，满足查询条件记录行数少的为驱动表；
2）未指定联接条件时，行数少的表为驱动表。（划重点！！！ ）。
可以直接对驱动表进行排序处理；而针对非驱动表（字段的排序处理），则需要特别提醒大家注意的是，在处理循环查询的结果时（即合并后的临时表），同样需要按照一定的顺序进行排列。
3、Explain结果详情：
如需了解更多信息，请您参考我另一篇详细的文章：Explain分析
Explain的结果中，第一行出现的表就是驱动表（划重点！！！ ）
在上面的Explain结果中，在所有组中如果id相同，则被视为一组；由上至下依次处理这些组；对于整个结果集中的各组而言，在所有组中如果id值较大，则该组的优先级较高，并排在前面。
建议：如果你不确定该如何指派驱动表以及关联操作，请将这一决定交由MySQL自行处理。当未设置连接条件时，默认由行数较少的表担任驱动角色，并根据数据量大小自动选择合适的联结方式；你对自己的复杂嵌套式联结（Nested Loop Join）操作尚不自信，则无需指定左右联结方式；将交由MySQL自动处理更为稳妥。如果对你特别有信心（例如你已经掌握熟练的操作技巧），则可参考以下方法进行优化：像火丁一样优化
优化的目标是尽可能减少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结果：
表中现有索引：
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表中新增了两个索引键idx_bill_no（对应的字段为BILL_NO）和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 DESCExplain结果：
表中现有索引：
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语句中不使用order by子句，并去除sql语句中的distinct关键字，在业务层面实现数据去重。（例如，在需要排序的情况下，默认可以选择wave_m表中的update_time字段或主键id作为排序依据）
PS： 通过放弃distinct关键字可以避免使用临时表；放弃order by排序或其他字段如update_time、id等则有助于绕开filesort。
三、归纳总结
在大数据应用开发中，应避免过度依赖生产环境中运行的SQL语句的实际执行速度数据。建议关注那些在执行效率上表现不佳的SQL语句，并不要仅仅关注某个时刻运行的时间长短。为了诊断性能问题，在使用DBMS自带的EXPLAIN工具时，请特别注意进行优化处理：当遇到以下常见问题时，请采取相应的改进措施以提升系统性能。
- 类型字段取值为ALL或使用索引字段；
- 可能的关键字超出预期数量（待选）索引；
- 键字段为空值（未被索引选择）；
- 行数过高或接近全表记录量；
- 在Extra部分遇到临时存储开销较大的情况时，请注意使用filesort时的情况需谨慎处理。
说明：
type取值为ALL或index。其中使用index进行的查询与全表扫描效果相同。其主要优势在于无需对数据进行排序操作，
然而这种机制所带来的开销依然巨大。
当查看Extra列时若发现结果中包含'Using index'字样，则表示系统正在使用覆盖性索引。
该系统仅对索引中的数据进行 scan，
而非对整个表进行逐一排查。
利用临时表来存储中间计算结果，在GROUP BY和ORDER BY运算中较为常见。通常发现这一点意味着查询可能需要优化。即使无法避免使用临时表的情况，请尽量避免使用硬盘上的临时表。
采用文件排序法（filesort），MySQL提供了两种方法来生成有序的结果：可以通过排序操作或借助索引实现。需要注意的是,'filesort'虽然被称为文件排序法，并不一定意味着实际采用了文件来进行排序（即使可能也是在内存里完成）。通常情况下，默认在内存中进行处理。因此，在这种情况下，默认查询可能不够高效。如果默认采用的是默认配置，则默认会将数据加载到内存中进行处理。其常见于如'orderBy'和'groupBy'等语句的情况下。这样的过程通常是CPU密集型的。为了提升性能表现，则建议适当选择空间索引以加快数据查找速度。用索指引是为了让结果按特定顺序排列而不是用来存储额外信息
**
**
日常SQL优化的大体思路：
使用Explain，确定是否出现如上需要优化的情况；
深入分析该SQL语句所承载的业务逻辑（例如：为何采用此结构？修改可能带来的影响有哪些？在业务层面实施此改动的可能性如何？）；当前系统条件是否能够支撑这一优化目标？；若上述方案无法达到预期优化效果，请考虑对现有表结构或SQL语句进行调整（例如：是否需要新增索引？join操作是否应予以去除？）。
在实践过程中，在线查询（SQL）优化是一项复杂的系统工程，在这个过程中需要展现出耐心和细致的工作态度，在这个过程中还需要不断学习和积累经验才能不断提升技能水平
参考资料：