无ORDER BY的查询性能优异，添加后却极慢，如何优化？

我执行了以下SQL查询：

SELECT n.id, n.member_id, n.content, n.created_at
FROM notes n
JOIN note_metadata nm ON n.id = nm.note_id
    AND nm.meta_key_id = 4 -- Nature
    AND nm.meta_value = 'Cancellation'
-- ORDER BY n.id DESC
LIMIT 10;

不加ORDER BY时，查询性能极佳；但添加ORDER BY n.id DESC后，查询速度骤降。

以下是notes表与note_metadata表的建表语句：

notes表 #

CREATE TABLE `notes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `member_id` int(10) unsigned NOT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `type_id` int(10) unsigned NOT NULL,
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_public` int(10) unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `is_archived` tinyint(3) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `notes_type_id_foreign` (`type_id`),
  KEY `member_id` (`member_id`),
  KEY `created_at` (`created_at`),
  CONSTRAINT `_notes_type_id_foreign` FOREIGN KEY (`type_id`) REFERENCES `note_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21374344 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

note_metadata表 #

CREATE TABLE `note_metadata` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `note_id` bigint(20) unsigned NOT NULL,
  `meta_key_id` int(10) unsigned NOT NULL,
  `meta_value` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `note_metadata_meta_key_id_foreign` (`meta_key_id`),
  KEY `_note_metadata_note_id_foreign` (`note_id`),
  CONSTRAINT `_note_metadata_note_id_foreign` FOREIGN KEY (`note_id`) REFERENCES `notes` (`id`),
  CONSTRAINT `note_metadata_meta_key_id_foreign` FOREIGN KEY (`meta_key_id`) REFERENCES `note_meta_keys` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4098655 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

请问如何让带ORDER BY的查询和无ORDER BY的查询一样快？是否需要添加新索引？

优化方案 #

1. 添加针对性复合索引（核心解决办法）

在note_metadata表上创建匹配查询过滤条件+排序需求的复合索引：

CREATE INDEX idx_meta_key_value_note_id ON note_metadata (meta_key_id, meta_value(255), note_id DESC);

说明：

• meta_key_id前置，快速匹配nm.meta_key_id = 4的过滤条件
• meta_value(255)因字段为text类型，需指定长度创建索引，匹配nm.meta_value = 'Cancellation'的过滤
• note_id DESC直接按排序需求索引化，查询时可直接从索引中获取有序的note_id，彻底避免额外排序操作

2. 调整查询逻辑（辅助优化）

如果索引生效后仍有顾虑，可以换一种查询写法：先从note_metadata中筛选出有序的目标note_id，再关联notes表取数据，减少不必要的数据处理：

SELECT n.id, n.member_id, n.content, n.created_at
FROM (
    SELECT note_id
    FROM note_metadata
    WHERE meta_key_id = 4
      AND meta_value = 'Cancellation'
    ORDER BY note_id DESC
    LIMIT 10
) AS nm
JOIN notes n ON n.id = nm.note_id;

这个子查询会直接利用上面创建的复合索引，快速拿到前10个符合条件的note_id，再关联主表，性能可与无ORDER BY的查询持平。

3. 验证优化效果

创建索引后执行EXPLAIN查看执行计划，确认：

• note_metadata表的访问类型为ref或range
• Extra列出现Using index（说明使用覆盖索引，无需回表）
• Extra列无Using filesort（排序操作已被索引替代）

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