如何为两列相等比较的SQL查询创建可用索引？

你的问题核心在于：常规的单列/复合索引是为列与常量匹配的场景设计的，而email_id = thread_id是同一行内两列的比较，这类条件没法直接利用普通索引。下面给你两种可行的解决方案：

方案1：使用生成列（Generated Column）+ 索引 #

适合MySQL 5.7及以上版本，步骤如下：

1. 给表添加一个存储式生成列，用来存储两列是否相等的结果：

ALTER TABLE email ADD COLUMN id_match BOOLEAN GENERATED ALWAYS AS (email_id = thread_id) STORED;

2. 给这个生成列创建索引：

CREATE INDEX idx_id_match ON email(id_match);

之后你可以直接用生成列作为查询条件，MySQL会自动使用索引：

SELECT * FROM email WHERE id_match = TRUE;

部分版本的MySQL也能自动优化原查询SELECT * FROM email WHERE email_id = thread_id使用这个索引，不过直接用生成列的条件更稳妥。

方案2：使用函数索引（MySQL 8.0.13+支持） #

如果你的MySQL版本是8.0.13或更高，可以直接针对比较表达式创建索引，无需额外添加列：

CREATE INDEX idx_id_eq_thread ON email((email_id = thread_id));

创建完成后，原查询SELECT * FROM email WHERE email_id = thread_id就能直接使用这个索引，用EXPLAIN分析时会看到该索引出现在possible_keys和key列中。

为什么之前的索引没用？ #

• 单列索引：只能加速email_id = 某个值或thread_id = 某个值这类条件，无法处理列之间的比较。
• 复合索引：(email_id, thread_id)的索引是按email_id排序后再按thread_id排序，适用于email_id = ? AND thread_id = ?这类多条件匹配场景，同样不适合列间比较的需求。

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