MySQL两表多条件内连接查询优化问题咨询

核心问题分析 #

原查询用OR连接多条件，且字段为TEXT类型——TEXT在多数数据库中无法高效利用普通索引，OR条件又会迫使数据库放弃索引转而全表扫描，这是10万+数据量下查询缓慢的根本原因。

优化步骤 #

1. 优先调整数据类型并创建索引

TEXT类型不适合等值匹配场景，建议改成合适长度的VARCHAR（比如VARCHAR(255)，根据实际数据长度调整），再为相关字段创建单独索引：

-- 为sample1创建索引
CREATE INDEX idx_sample1_key1 ON sample1(key1);
CREATE INDEX idx_sample1_key2 ON sample1(key2);

-- 为sample2创建索引
CREATE INDEX idx_sample2_tey1 ON sample2(tey1);
CREATE INDEX idx_sample2_tey2 ON sample2(tey2);
CREATE INDEX idx_sample2_tey3 ON sample2(tey3);

2. 拆分查询逻辑，避免OR导致的全表扫描

把原查询拆成互斥的两部分，用UNION ALL合并（比UNION快，无需去重）：

• 第一部分：只匹配sample1.key1 = sample2.tey1的记录
• 第二部分：只匹配不满足第一条件，但满足sample1.key2等于tey2/tey3的记录

具体SQL：

-- 第一部分：满足条件1的连接
SELECT s1.*, s2.*
FROM sample1 s1
INNER JOIN sample2 s2 ON s1.key1 = s2.tey1

UNION ALL

-- 第二部分：不满足条件1，但满足条件2的连接
SELECT s1.*, s2.*
FROM sample1 s1
INNER JOIN sample2 s2 
    ON (s1.key2 = s2.tey2 OR s1.key2 = s2.tey3)
WHERE NOT EXISTS (
    -- 排除已在第一部分匹配到的记录
    SELECT 1 FROM sample2 s2_check 
    WHERE s2_check.tey1 = s1.key1
)

3. 若无法修改数据类型，用哈希字段优化

如果业务限制不能将TEXT改为VARCHAR，可新增哈希值字段，利用哈希索引加速匹配：

第一步：新增哈希字段并赋值

-- 给sample1新增哈希字段
ALTER TABLE sample1 ADD COLUMN key1_hash VARCHAR(32), ADD COLUMN key2_hash VARCHAR(32);
UPDATE sample1 SET key1_hash = MD5(key1), key2_hash = MD5(key2);

-- 给sample2新增哈希字段
ALTER TABLE sample2 ADD COLUMN tey1_hash VARCHAR(32), ADD COLUMN tey2_hash VARCHAR(32), ADD COLUMN tey3_hash VARCHAR(32);
UPDATE sample2 SET tey1_hash = MD5(tey1), tey2_hash = MD5(tey2), tey3_hash = MD5(tey3);

第二步：为哈希字段创建索引

CREATE INDEX idx_sample1_key1_hash ON sample1(key1_hash);
CREATE INDEX idx_sample1_key2_hash ON sample1(key2_hash);

CREATE INDEX idx_sample2_tey1_hash ON sample2(tey1_hash);
CREATE INDEX idx_sample2_tey2_hash ON sample2(tey2_hash);
CREATE INDEX idx_sample2_tey3_hash ON sample2(tey3_hash);

第三步：修改查询语句

SELECT s1.*, s2.*
FROM sample1 s1
INNER JOIN sample2 s2 ON s1.key1_hash = s2.tey1_hash AND s1.key1 = s2.tey1

UNION ALL

SELECT s1.*, s2.*
FROM sample1 s1
INNER JOIN sample2 s2 
    ON (s1.key2_hash = s2.tey2_hash AND s1.key2 = s2.tey2) 
    OR (s1.key2_hash = s2.tey3_hash AND s1.key2 = s2.tey3)
WHERE NOT EXISTS (
    SELECT 1 FROM sample2 s2_check 
    WHERE s2_check.tey1_hash = s1.key1_hash AND s2_check.tey1 = s1.key1
)

注：保留原字段的等值判断是为了避免哈希冲突导致的错误匹配。

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