MySQL多表关联查询速度慢 批量执行耗时过长求优化方案

我有一个包含多张表的数据库，需要从不同表中查询数据生成报表，使用的SQL语句如下：

SELECT finance, DR, DATE_Z_1, DATE_Z_2, P_CEL, DS1, VISIT_POL, VISIT_HOM FROM zap 
JOIN pacient ON zap.id = pacient.zap_id 
JOIN z_sl ON zap.id = z_sl.zap_id 
JOIN sl ON z_sl.id = sl.z_sl_id 
JOIN pers ON pacient.ID_PAC = pers.ID_PAC 
JOIN reestr ON zap.reestr_id = reestr.id 
WHERE (z_sl.IDSP IN (29,30)) AND (IDDOKT = "123") 
AND (sl.OTKAZ_I_TYPE IS NULL) AND (z_sl._SANK = 0) 
GROUP BY zap.id, pers.ID_PAC

该语句单次执行耗时约10秒，我需要执行200次此类查询，每次替换IDDOKT参数的标识符，总耗时将超过30分钟，无法满足需求，现求助排查问题所在及优化方案。

我已为所有关联比较字段创建索引，同时为IDDOKT列创建了索引。例如sl表的索引信息如下：

Name: z_sl_id
Field: `z_sl_id`
Index Type: NORMAL
Index method: BTREE

Name: IDDOKT_index
Field: `IDDOKT`(11)
Index Type: NORMAL
Index method: BTREE

执行计划（EXPLAIN结果） #

• 添加pers.ID_PAC索引前：
  
• 添加pers.ID_PAC索引后：
  

问题排查与优化方案 #

1. 优化索引设计 #

• 针对sl表的过滤条件，把单字段索引升级为复合索引：创建(IDDOKT, z_sl_id, OTKAZ_I_TYPE)的复合索引。这样可以同时匹配IDDOKT过滤、z_sl_id关联以及OTKAZ_I_TYPE IS NULL的条件，避免回表查询，大幅提升过滤效率。
• 为z_sl表创建复合索引(IDSP, _SANK, zap_id)，覆盖IDSP IN (29,30)、_SANK = 0的过滤条件和关联zap表的zap_id字段，直接从索引中获取所需数据，减少全表扫描的开销。

2. 替换循环查询为批量查询 #

不要重复执行200次单参数查询，改成批量查询：

• 收集所有需要查询的IDDOKT值，用IN条件一次性查询，比如IDDOKT IN ('123','456','789',...)，一次获取所有结果后在应用层按IDDOKT分组生成报表，把200次查询压缩为1次，总耗时会大幅降低。
• 如果IDDOKT数量过多（比如超过1000个），可以分批次查询（比如每次查50个），总次数降到4次，也能显著减少总耗时。

3. 验证GROUP BY的必要性 #

检查SELECT的字段是否在zap.id和pers.ID_PAC分组下是唯一的：

• 如果其他字段不会出现重复，直接去掉GROUP BY，避免不必要的排序和分组计算。
• 如果需要去重，用DISTINCT替代GROUP BY，部分场景下优化器对DISTINCT的处理效率更高。

4. 优化执行计划 #

• 查看EXPLAIN结果中的type列，确保关联表的访问类型是ref或eq_ref（最优级别），避免出现ALL（全表扫描）或range（范围扫描）。如果存在全表扫描，检查对应表的索引是否覆盖过滤和关联条件。
• 更新数据库统计信息：执行ANALYZE TABLE zap, pacient, z_sl, sl, pers, reestr;，让优化器基于最新的数据分布生成更合理的执行计划。

5. 数据量与表结构优化 #

• 如果部分表数据量极大，考虑对历史数据进行归档，减少单次查询需要扫描的数据量。
• 检查JOIN的表是否有冗余：比如reestr表，如果SELECT字段里没有用到该表的字段，且过滤条件也不依赖它，可以考虑去掉这个JOIN。

内容的提问来源于stack exchange，提问作者Tlenov