800万条数据SQL查询耗时7秒索引失效优化方案求助

现有索引失效原因 #

• 多字段OR条件跨4个手机号列，单个单列非聚集索引无法覆盖所有匹配场景，走单索引后仍需回表查询其他字段及校验剩余OR条件，查询成本高于全表扫描，因此优化器选择不走索引
• SELECT * 需要返回所有字段，非覆盖索引需要回表查询聚集索引获取剩余字段，进一步拉高索引访问成本
• 若record_status='1'的数据占比过高，单列record_status索引筛选性不足，也无法带来性能收益

优化方案 #

1. 索引层面优化 #

优先为每个手机号字段建立record_status为前导列的联合覆盖索引，如果record_status='1'为常规有效数据筛选条件，可改为筛选索引进一步降低索引体积：

-- 若只需查询指定字段，替换INCLUDE内为实际需要的字段，不要填*
CREATE NONCLUSTERED INDEX IX_cust_info_handphone ON customer_info (record_status, CUST_HANDPHONE) INCLUDE (id, CUST_PREFERRED_NO1, CUST_PREFERRED_NO2, CUST_PREFERRED_NO3 /* 其他需要返回的字段 */);
CREATE NONCLUSTERED INDEX IX_cust_info_prefer1 ON customer_info (record_status, CUST_PREFERRED_NO1) INCLUDE (id, CUST_HANDPHONE, CUST_PREFERRED_NO2, CUST_PREFERRED_NO3 /* 其他需要返回的字段 */);
CREATE NONCLUSTERED INDEX IX_cust_info_prefer2 ON customer_info (record_status, CUST_PREFERRED_NO2) INCLUDE (id, CUST_HANDPHONE, CUST_PREFERRED_NO1, CUST_PREFERRED_NO3 /* 其他需要返回的字段 */);
CREATE NONCLUSTERED INDEX IX_cust_info_prefer3 ON customer_info (record_status, CUST_PREFERRED_NO3) INCLUDE (id, CUST_HANDPHONE, CUST_PREFERRED_NO1, CUST_PREFERRED_NO2 /* 其他需要返回的字段 */);

-- 若record_status='1'占比低于30%，优先用筛选索引，索引体积更小性能更高
CREATE NONCLUSTERED INDEX IX_cust_info_handphone_valid ON customer_info (CUST_HANDPHONE) INCLUDE (/* 需要返回的字段 */) WHERE record_status = '1';
-- 剩余三个手机号字段同上建立对应筛选索引

2. SQL语句改写优化 #

将多字段OR条件拆分为UNION/UNION ALL的多个独立查询分支，每个分支可单独匹配对应索引，避免优化器无法处理OR条件选择全表扫描：

DECLARE @number varchar(30) = '123456789';
DECLARE @match_num1 varchar(32) = @number;
DECLARE @match_num2 varchar(32) = CONCAT('00', @number);

-- 若业务上同一个客户不会出现多个手机号同时匹配，可将UNION改为UNION ALL性能提升更明显
SELECT * FROM customer_info WHERE record_status = '1' AND CUST_HANDPHONE IN (@match_num1, @match_num2)
UNION
SELECT * FROM customer_info WHERE record_status = '1' AND CUST_PREFERRED_NO1 IN (@match_num1, @match_num2)
UNION
SELECT * FROM customer_info WHERE record_status = '1' AND CUST_PREFERRED_NO2 IN (@match_num1, @match_num2)
UNION
SELECT * FROM customer_info WHERE record_status = '1' AND CUST_PREFERRED_NO3 IN (@match_num1, @match_num2);

额外优化建议 #

• 避免使用SELECT *，仅返回业务需要的字段，可大幅降低索引体积和IO消耗，修改后同步调整INCLUDE内的字段列表即可
• 检查手机号字段的varchar长度是否合理，避免过长的字段类型拉高索引存储成本
• 若业务允许，可定期归档record_status!='1'的无效数据，降低主表数据量

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