如何优化高延迟SQL关联查询？求除缩减数据集外的方案

问题概述 #

原始查询语句：

SELECT ctm.* 
FROM compound_transaction_map ctm 
JOIN bustransaction_map btm ON ctm.transId = btm.transId 
WHERE btm.docId = ?

• 表规模：compound_transaction_map含776,387条数据，bustransaction_map含3,252,772条数据
• 现有索引：两张表的transId均有索引，bustransaction_map的docId为唯一索引
• 执行计划异常：btm表通过docId索引快速定位（const类型），但ctm表始终执行全表扫描（ALL类型），即使强制指定transId索引也无效

具体优化方案 #

1. 修复字符集不匹配问题 #

这是导致ctm表无法使用transId索引的核心原因：

• compound_transaction_map的transId为latin1字符集的varchar(100)
• bustransaction_map的transId为utf8字符集的varchar(50)
  字符集不一致会使MySQL无法直接利用索引进行JOIN匹配，只能全表扫描。解决方式：
• 统一两张表的字符集：将bustransaction_map的transId字段字符集改为latin1，或把compound_transaction_map整体改为utf8（操作前需备份数据，确保兼容性）
• 临时应急方案（不推荐长期使用）：JOIN时显式转换字符集，但会导致btm的transId索引失效：

SELECT ctm.* 
FROM compound_transaction_map ctm 
JOIN bustransaction_map btm ON ctm.transId = CONVERT(btm.transId USING latin1)
WHERE btm.docId = ?

2. 清理冗余索引 #

compound_transaction_map中，UNQ_compoundId_transId唯一索引已将transId作为前缀字段，单独的transId普通索引属于冗余索引，可删除以减少索引维护成本：

DROP INDEX transId ON compound_transaction_map;

3. 对齐数据类型长度 #

ctm.transId为varchar(100)，btm.transId为varchar(50)，长度差异可能引发隐性转换，建议统一两者的长度（例如都设为100），消除潜在的索引匹配障碍。

4. 更新表统计信息 #

MySQL优化器依赖最新的表统计信息生成最优执行计划，若统计信息过时，可能导致错误的索引选择。执行以下命令更新统计信息：

ANALYZE TABLE compound_transaction_map;
ANALYZE TABLE bustransaction_map;

5. 验证索引生效 #

修复字符集和数据类型后，重新执行EXPLAIN查询，确认ctm表的type字段变为ref或range，而非ALL，说明索引已正常使用。

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