如何优化耗时20秒的MySQL DISTINCT查询？排查慢查询原因

问题场景 #

某metadata表共6902215条数据，执行以下查询耗时约20秒：

SELECT DISTINCT metadata.dimension_value FROM metadata WHERE metadata.name = 'content' AND metadata.dimension_name = 'lang'

表结构如下：

CREATE TABLE `metadata` (
  `metadata_id` int NOT NULL AUTO_INCREMENT,
  `resource_id` int NOT NULL,
  `dimension_name` varchar(50) NOT NULL DEFAULT '',
  `dimension_value` varchar(50) NOT NULL DEFAULT '',
  `name` varchar(50) NOT NULL DEFAULT '',
  `value` longtext,
  `content` longtext,
  PRIMARY KEY (`metadata_id`),
  UNIQUE KEY `INDEX_UNIQUE_METADATA` (`resource_id`,`name`,`dimension_name`,`dimension_value`),
  KEY `INDEX_FK_RESOURCE` (`resource_id`),
  KEY `INDEX_NAME` (`name`),
  KEY `INDEX_METAKEY` (`resource_id`,`dimension_name`,`dimension_value`,`name`),
  KEY `INDEX_DIMENSION_NAME` (`dimension_name`),
  KEY `INDEX_DIMENSION_VALUE` (`dimension_value`),
  KEY `INDEX_VALUE` (`value`(255)),
  FULLTEXT KEY `INDEX_CONTENT` (`content`),
  CONSTRAINT `FK_RESOURCE` FOREIGN KEY (`resource_id`) REFERENCES `resource` (`resource_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 

查询执行计划：

id: 1
select_type: SIMPLE
table: metadata
partitions: null
type: ref
possible_keys: INDEX_UNIQUE_METADATA,INDEX_NAME,INDEX_METAKEY,INDEX_DIMENSION_NAME,INDEX_DIMENSION_VALUE
key: INDEX_NAME
key_len: 202
ref: const
rows: 887774
filtered: 50.00
Extra: Using where; Using temporary

问题原因分析 #

1. 单独索引无法满足多条件高效过滤：虽然name和dimension_name各自有单独索引，但MySQL在多条件过滤时只能选择一个索引（此处选了INDEX_NAME）。通过INDEX_NAME过滤出约88万行数据后，需要回表读取每行的dimension_name字段进行二次过滤，这一步产生大量磁盘IO开销。
2. DISTINCT操作额外开销：由于需要去重，MySQL不得不创建临时表存储筛选出的dimension_value，再进行去重排序，进一步消耗CPU和内存资源，这也是执行计划中Using temporary的原因。
3. 现有复合索引不匹配查询模式：已有的复合索引（如INDEX_UNIQUE_METADATA、INDEX_METAKEY）均以resource_id开头，而当前查询未使用该字段作为过滤条件，因此这些索引无法被高效利用。

优化方案 #

核心优化：创建覆盖索引 #

针对查询的过滤条件和返回字段，创建联合覆盖索引，让MySQL无需回表即可完成所有操作：

CREATE INDEX idx_name_dimname_dimval ON metadata(name, dimension_name, dimension_value);

优化原理：

• 该索引的字段顺序完全匹配查询的WHERE条件（先name，再dimension_name），可以快速定位到符合条件的记录。
• 索引中包含了要返回的dimension_value字段，属于覆盖索引，MySQL直接遍历索引就能获取所需数据，无需访问表的主数据块。
• 由于索引本身是有序的，DISTINCT操作可以直接在索引上完成，无需创建临时表，消除Using temporary的开销。

验证优化效果 #

创建索引后重新执行查询，执行计划应出现以下变化：

• key字段显示为新创建的idx_name_dimname_dimval
• Extra字段变为Using index（表示使用覆盖索引，无需回表）
• rows和filtered数值会大幅降低，查询耗时将显著减少。

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