优化MySQL查询：JOIN与ORDER BY共存时查询超时问题求助

我的查询语句如下：

SELECT *
FROM conferences co
JOIN calls ca ON ca.conference_id = co.id       
JOIN conference_participants p ON co.id = p.conference_id AND p.caller_id = 40
WHERE ca.duration >= 60
ORDER BY ca.created_at desc
LIMIT 0, 100;

单独移除JOIN conference_participants p部分，或者单独移除ORDER BY部分时，查询速度都很快，但同时保留这两部分时，查询会变得极慢。

EXPLAIN执行计划结果： #

id: 1
select_type: SIMPLE
table: p
partitions: NULL
type: index
possible_keys: PRIMARY
key: PRIMARY
ref: NULL
rows: 54474
filtered: 10.00
Extra: Using where; Using index; Using temporary; Using filesort

id: 1
select_type: SIMPLE
table: ca
partitions: NULL
type: ref
possible_keys: calls_conference_id_index,duration_queue,duration_user_created,idx_duration_created_at
key: calls_conference_id_index
key_len: 9
ref: mediphone.p.conference_id
rows: 1
filtered: 50.00
Extra: Using index condition; Using where

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

简化后的表结构： #

CREATE TABLE `conference_participants` (
  `conference_id` bigint(20) NOT NULL,
  `caller_id` int(11) NOT NULL,
  PRIMARY KEY (`conference_id`,`caller_id`)
) ENGINE=InnoDB;

CREATE TABLE `calls` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `conference_id` bigint(20) unsigned DEFAULT NULL,
  `duration` int(10) unsigned NOT NULL DEFAULT '0',
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `calls_created_at_index` (`created_at`),
  KEY `calls_user_id_index` (`user_id`),
  KEY `created_user` (`created_at`,`user_id`),
  KEY `user_created` (`user_id`,`created_at`),
  KEY `calls_conference_id_index` (`conference_id`),
  KEY `idx_duration_created_at` (`duration`,`created_at`),
  KEY `duration_queue` (`duration`,`queue_name`),
  KEY `duration_user_created` (`duration`,`user_id`,`created_at`),
  CONSTRAINT `calls_conference_id_foreign` FOREIGN KEY (`conference_id`) REFERENCES `conferences` (`id`),
  CONSTRAINT `calls_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `conferences` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

分析与解决方案 #

问题根源 #

从执行计划可以看到，MySQL选择了先扫描conference_participants表（扫描54474行），再关联calls和conferences表。最后需要对所有符合条件的记录执行Using temporary和Using filesort——这两个操作在数据量较大时开销极大，直接导致查询变慢。

而单独移除JOIN或ORDER BY时，MySQL会选择更优的执行路径：比如移除JOIN后，会利用calls表的idx_duration_created_at索引直接筛选并排序，避免全量扫描；移除ORDER BY后，无需排序操作，即使扫描大量数据也能快速返回。

解决方案 #

1. 优先筛选小数据集（子查询改写）

先从calls表中筛选出符合duration >=60的前100条记录（按created_at倒序），再关联其他表。这样可以将排序操作限制在小数据集内，避免全量排序：

SELECT *
FROM (
    SELECT * 
    FROM calls 
    WHERE duration >= 60 
    ORDER BY created_at DESC 
    LIMIT 0, 100
) ca
JOIN conferences co ON ca.conference_id = co.id
JOIN conference_participants p ON co.id = p.conference_id AND p.caller_id = 40
ORDER BY ca.created_at DESC;

2. 强制指定表连接顺序

使用STRAIGHT_JOIN强制MySQL优先处理calls表，利用idx_duration_created_at索引完成筛选和排序，再关联其他表：

SELECT *
FROM calls ca
STRAIGHT_JOIN conferences co ON ca.conference_id = co.id
STRAIGHT_JOIN conference_participants p ON co.id = p.conference_id AND p.caller_id = 40
WHERE ca.duration >= 60
ORDER BY ca.created_at DESC
LIMIT 0, 100;

3. 优化conference_participants表索引

当前主键是(conference_id, caller_id)，查询caller_id=40时需要扫描整个主键索引。添加一个(caller_id, conference_id)的索引，可以快速定位到指定caller_id的所有会议记录：

ALTER TABLE conference_participants ADD INDEX idx_caller_conference (caller_id, conference_id);

这个索引能让MySQL在关联时更快找到符合条件的conference_id，减少扫描行数。

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