SQL查询优化疑问：索引已存在却出现临时表与文件排序

问题背景 #

用户尝试优化一条多表关联的SQL查询，查询语句如下：

EXPLAIN 
SELECT co.id, ca.duration, ca.type, 
ca.from, ca.to, ca.queue_name, ca.created_at
FROM conferences co
JOIN calls ca ON ca.sid = co.name
JOIN conference_participants p ON co.id = p.conference_id 
  AND p.caller_id IN ('group:40')
WHERE ca.duration >= 60
ORDER BY ca.created_at desc
LIMIT 0, 100;

关联表结构 #

conferences表

CREATE TABLE `conferences` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `organization_id` int(10) unsigned DEFAULT NULL,
  `sid` char(34) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name` char(34) COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner_user_id` bigint(20) unsigned DEFAULT NULL,
  `duration` int(10) unsigned DEFAULT NULL,
  `events` int(10) unsigned NOT NULL DEFAULT '1',
  `ending_participant_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `conferences_name_unique` (`name`),
  UNIQUE KEY `conferences_sid_unique` (`sid`),
  UNIQUE KEY `conferences_ending_participant_id_unique` (`ending_participant_id`),
  KEY `conferences_organization_id_index` (`organization_id`),
  KEY `conferences_owner_user_id_index` (`owner_user_id`),
  CONSTRAINT `conferences_ending_participant_id_foreign` FOREIGN KEY (`ending_participant_id`) REFERENCES `conference_participants` (`id`),
  CONSTRAINT `conferences_organization_id_foreign` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`),
  CONSTRAINT `conferences_owner_user_id_foreign` FOREIGN KEY (`owner_user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1851662 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

calls表

CREATE TABLE `calls` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `organization_id` int(10) unsigned NOT NULL,
  `conference_id` bigint(20) unsigned DEFAULT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `sid` char(34) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `original_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `queue_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `from` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `to` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `direction` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `priority` int(10) unsigned DEFAULT NULL,
  `age` int(10) unsigned DEFAULT NULL,
  `duration` int(10) unsigned NOT NULL DEFAULT '0',
  `user_hold_duration` int(10) unsigned NOT NULL DEFAULT '0',
  `total_hold_duration` int(10) unsigned NOT NULL DEFAULT '0',
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `reason` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `calls_sid_unique` (`sid`),
  KEY `calls_created_at_index` (`created_at`),
  KEY `calls_organization_id_index` (`organization_id`),
  KEY `calls_conference_id_index` (`conference_id`),
  KEY `calls_user_id_index` (`user_id`),
  KEY `calls_original_type_index` (`original_type`),
  KEY `calls_type_index` (`type`),
  KEY `calls_queue_name_index` (`queue_name`),
  KEY `calls_from_index` (`from`),
  KEY `calls_to_index` (`to`),
  KEY `calls_direction_index` (`direction`),
  KEY `calls_priority_index` (`priority`),
  KEY `calls_status_index` (`status`),
  KEY `calls_reason_index` (`reason`),
  KEY `type` (`type`),
  KEY `type_sid` (`type`,`sid`),
  KEY `sid_type` (`sid`,`type`),
  KEY `duration_queue` (`duration`,`queue_name`),
  KEY `queue_duration` (`queue_name`,`duration`),
  KEY `duration_user_created` (`duration`,`user_id`,`created_at`),
  KEY `user_duration_created` (`user_id`,`duration`,`created_at`),
  KEY `created_user_duration` (`created_at`,`user_id`,`duration`),
  KEY `created_duration_user` (`created_at`,`duration`,`user_id`),
  KEY `sid_user_duration_created` (`sid`,`user_id`,`duration`,`created_at`),
  KEY `created_user` (`created_at`,`user_id`),
  KEY `user_created` (`user_id`,`created_at`),
  CONSTRAINT `calls_conference_id_foreign` FOREIGN KEY (`conference_id`) REFERENCES `conferences` (`id`),
  CONSTRAINT `calls_organization_id_foreign` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`),
  CONSTRAINT `calls_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2306330 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

conference_participants表

CREATE TABLE `conference_participants` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `conference_id` bigint(20) unsigned NOT NULL,
  `call_leg_id` bigint(20) unsigned DEFAULT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `sid` char(34) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `task_sid` char(34) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `caller_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `has_joined_conference` tinyint(1) NOT NULL DEFAULT '1',
  `is_hidden` tinyint(1) NOT NULL DEFAULT '0',
  `is_muted` tinyint(1) NOT NULL DEFAULT '0',
  `is_on_hold` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `conference_participants_call_leg_id_unique` (`call_leg_id`),
  UNIQUE KEY `conference_participants_sid_unique` (`sid`),
  KEY `conference_participants_conference_id_index` (`conference_id`),
  KEY `conference_participants_user_id_index` (`user_id`),
  KEY `conference_participants_caller_id_index` (`caller_id`),
  KEY `conference_participants_has_joined_conference_index` (`has_joined_conference`),
  KEY `conference_participants_is_hidden_index` (`is_hidden`),
  KEY `conference_participants_is_muted_index` (`is_muted`),
  KEY `conference_participants_is_on_hold_index` (`is_on_hold`),
  KEY `conference_participants_task_sid_index` (`task_sid`),
  CONSTRAINT `conference_participants_call_leg_id_foreign` FOREIGN KEY (`call_leg_id`) REFERENCES `call_legs` (`id`),
  CONSTRAINT `conference_participants_conference_id_foreign` FOREIGN KEY (`conference_id`) REFERENCES `conferences` (`id`),
  CONSTRAINT `conference_participants_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3947768 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

EXPLAIN输出结果 #

id: 1
select_type: SIMPLE
table: p
partitions: NULL
type: ref
possible_keys: conference_participants_conference_id_index,conference_participants_caller_id_index
key: conference_participants_caller_id_index
key_len: 130
ref: const
rows: 52568
filtered: 100.00
Extra: Using temporary; Using filesort

id: 1
select_type: SIMPLE
table: co
partitions: NULL
type: eq_ref
possible_keys: PRIMARY,conferences_name_unique
key: PRIMARY
key_len: 8
ref: phone.p.conference_id
rows: 1
filtered: 100.00
Extra: NULL

id: 1
select_type: SIMPLE
table: ca
partitions: NULL
type: ref
possible_keys: calls_sid_unique,sid_type,duration_queue,duration_user_created,sid_user_duration_created
key: sid_user_duration_created
key_len: 137
ref: phone.co.name
rows: 1
filtered: 50.00
Extra: Using index condition

用户疑问：conference_participants表的caller_id字段已建索引，但EXPLAIN显示Using temporary; Using filesort而非Using index，想知道原因，同时排查查询缓慢的其他因素。

核心疑问解答 #

为什么没有Using index，反而出现Using temporary; Using filesort？ #

• Using index代表使用覆盖索引，即查询所需的所有字段都包含在索引中，无需回表读取原数据。
• 当前caller_id是单字段索引，但查询中需要从conference_participants表获取conference_id（用于关联conferences表），这个字段不在caller_id索引里，数据库需要通过索引找到符合条件的行后，再回表读取conference_id，因此不会触发Using index。
• Using temporary; Using filesort是因为查询需要最终按ca.created_at desc排序，当前关联顺序下，结果集无法直接利用现有索引排序，所以需要创建临时表存储中间结果，再进行文件排序来满足ORDER BY的要求。

查询运行缓慢的其他排查点 #

1. 索引效率优化 #

• conference_participants表：创建联合索引(caller_id, conference_id)，通过索引直接获取conference_id，避免回表，减少IO开销。
• calls表：当前使用的sid_user_duration_created索引包含关联、过滤、排序字段，但缺少查询需要的type、from、to、queue_name，建议调整为联合索引(sid, duration, created_at, type, from, to, queue_name)，实现覆盖索引，避免回表。
• 定期维护索引：执行ANALYZE TABLE更新统计信息，或OPTIMIZE TABLE整理索引碎片（注意锁表风险）。

2. 数据量与过滤效率 #

• 从EXPLAIN看，conference_participants表预计返回52568行，数据量较大导致后续关联和排序开销高。检查caller_id = 'group:40'的实际数据量，看是否能添加其他过滤条件提前缩小结果集。
• calls表的duration >=60过滤条件，当前通过索引条件推送处理，若能结合关联字段优化索引，过滤效率会更高。

3. 排序开销优化 #

• 当前排序是对关联后的全量结果集排序再取前100条，可尝试调整查询顺序，从calls表按created_at倒序取数据，再关联其他表，利用calls_created_at_index索引提前终止排序：

SELECT co.id, ca.duration, ca.type, ca.from, ca.to, ca.queue_name, ca.created_at
FROM calls ca
JOIN conferences co ON ca.sid = co.name
JOIN conference_participants p ON co.id = p.conference_id AND p.caller_id IN ('group:40')
WHERE ca.duration >=60
ORDER BY ca.created_at desc
LIMIT 0,100;

观察EXPLAIN是否优先使用calls_created_at_index，减少临时表和文件排序的开销。

4. 数据库统计信息 #

• 确保统计信息最新，过时的统计信息会导致优化器选择错误执行计划。执行ANALYZE TABLE conferences, calls, conference_participants;更新统计信息。

5. 硬件与配置检查 #

• 查看服务器内存、CPU、磁盘IO情况：排序时内存不足会导致磁盘文件排序开销剧增；磁盘IO性能差会拖慢回表和数据读取速度。
• 调整tmp_table_size和max_heap_table_size参数，避免临时表写入磁盘，减少IO开销。

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