MariaDB含Left Join与Order By的慢查询优化求助（Copying to tmp table）

问题核心分析 #

当前查询耗时约30秒，瓶颈集中在Copying to tmp table阶段，执行计划显示：

• 从vtiger_assets表出发扫描955594行，关联vtiger_crmentity和vtiger_crmentity_user_field后进行全局排序（Using temporary; Using filesort）
• 单独对vtiger_crmentity按modifiedtime排序取前100条效率极高，但加入LEFT JOIN后优化器未利用该索引，导致需处理大量数据后再排序

具体优化措施 #

1. 创建关键联合索引（核心优化） #

在vtiger_crmentity表上创建覆盖联合索引，引导优化器直接按排序条件获取数据，避免全局排序：

CREATE INDEX idx_crmentity_deleted_modified_crmid ON vtiger_crmentity (deleted, modifiedtime DESC, crmid);

该索引的作用：

• 快速筛选deleted=0的行
• 直接按modifiedtime DESC排序，无需额外排序操作
• 包含crmid字段，可直接关联vtiger_assets和vtiger_crmentity_user_field，避免回表查询

优化后，优化器会优先从vtiger_crmentity取前35条符合条件的记录，再关联其他两张表，处理数据量从百万级降至35条，彻底解决临时表和文件排序的瓶颈。

2. 修复字段类型不一致问题 #

vtiger_assets.assetsid为int(11)，而vtiger_crmentity_user_field.recordid为int(19)，类型差异会导致关联时的隐式转换，降低索引效率。执行以下语句统一类型：

ALTER TABLE vtiger_assets MODIFY COLUMN assetsid int(19) NOT NULL;

注意：执行前需备份数据，且确认CRM系统兼容该字段类型变更。

3. 验证并调整优化器行为 #

创建索引后，执行EXPLAIN查看执行计划，确认优化器是否选择新创建的索引，且扫描行数降至35左右。若优化器仍未选择，可更新表统计信息：

ANALYZE TABLE vtiger_crmentity, vtiger_assets, vtiger_crmentity_user_field;

补充说明 #

• 增大缓冲区（join_buffer_size、tmp_table_size）无法解决核心问题，瓶颈在于需处理的数据集过大，而非内存不足
• 由于查询无法修改，所有优化需通过调整索引和表结构实现

内容的提问来源于stack exchange，提问作者Юрий Глущенко