复合索引优化查询问题求助：为何复合索引未生效？

一、重新设计复合索引结构 #

1. 遵循最左前缀+范围后置原则 #

你的查询里，c、k、p是固定等值匹配，cli是IN（等价多值等值），c_date是范围过滤（<=），最后还要按id排序。按照MySQL索引的使用规则，等值条件要放在复合索引的最前面，范围条件跟在后面，最后加上排序字段避免额外排序。

建议创建如下复合索引：

CREATE OR REPLACE INDEX idx_b_c_k_p_cli_cdate_id ON bill_lading (c, k, p, cli, c_date, id);

这样优化器能通过前四个等值字段快速缩小数据范围，再用c_date过滤，最后直接利用索引的id有序性完成排序，无需额外的filesort操作。

2. 构建覆盖索引减少回表开销 #

你的查询需要返回id、cav、cave，同时WHERE子句还用到了status、c_check_id、number_r这些字段。如果把这些字段加入索引（或用INCLUDE子句），就能让查询直接从索引获取所有数据，避免回表查询原表，大幅提升性能：

MySQL 8.0.14+版本（支持INCLUDE）：

CREATE OR REPLACE INDEX idx_b_covering ON bill_lading (c, k, p, cli, c_date) 
INCLUDE (id, cav, cave, status, c_check_id, number_r);

低版本MySQL（直接把字段加入索引）：

CREATE OR REPLACE INDEX idx_b_covering ON bill_lading (c, k, p, cli, c_date, id, cav, cave, status, c_check_id, number_r);

二、拆分OR条件消除索引选择障碍 #

OR是优化器放弃复合索引的常见原因——如果OR两边的条件无法被同一个索引覆盖，优化器会倾向于选择全表扫描或单字段索引。你可以把原查询拆成两个独立子查询，用UNION ALL合并：

SELECT id, cav, cave
FROM b 
WHERE 
  c = '0' 
  AND k = '1' 
  AND p = '0' 
  AND cli IN ( '1', '2', '3', '4', '5', '11' ) 
  AND c_date <= '2021-02-15 23:59:59' 
  AND cav <> '0' 
  AND ( c_check_id <> '0' OR number_r = '1' ) 
  AND status = '3'
UNION ALL
SELECT id, cav, cave
FROM b 
WHERE 
  c = '0' 
  AND k = '1' 
  AND p = '0' 
  AND cli IN ( '1', '2', '3', '4', '5', '11' ) 
  AND c_date <= '2021-02-15 23:59:59' 
  AND cav = '0' 
  AND status IN ( '1', '2' )
ORDER BY id ASC 
LIMIT 200 OFFSET 400;

拆分后每个子查询的条件更单一，优化器更容易匹配到我们创建的复合索引。

三、临时强制使用复合索引（测试用） #

如果优化器仍固执选择c_date单索引，可以用FORCE INDEX强制指定复合索引，测试性能差异：

SELECT id, cav, cave
FROM b FORCE INDEX(idx)
WHERE 
  c = '0' 
  AND k = '1' 
  AND p = '0' 
  AND cli IN ( '1', '2', '3', '4', '5', '11' ) 
  AND c_date <= '2021-02-15 23:59:59' 
  AND ((
    cav <> '0' 
    AND ( c_check_id <> '0' OR number_r = '1' ) 
    AND status = '3' 
    ) 
  OR (
    cav = '0' 
  AND status IN ( '1', '2' )))
ORDER BY id ASC 
LIMIT 200 OFFSET 400;

注意这只是临时测试方案，长期来看还是要通过优化索引和查询语句让优化器自动选择最优路径。

四、检查字段选择性调整索引顺序 #

如果c、k这类字段的选择性极低（比如c几乎全是'0'），把它们放在索引前面意义不大。可以用以下语句查看字段的区分度：

SELECT 
  COUNT(DISTINCT c)/COUNT(*) as c_selectivity,
  COUNT(DISTINCT k)/COUNT(*) as k_selectivity,
  COUNT(DISTINCT p)/COUNT(*) as p_selectivity,
  COUNT(DISTINCT cli)/COUNT(*) as cli_selectivity
FROM bill_lading;

选择性越高（值越接近1），字段过滤数据的能力越强，应该优先放在复合索引的前面。

五、优化OFFSET分页减少扫描开销 #

OFFSET 400意味着数据库要先扫描600行数据，再丢弃前400行返回结果，数据量大时这部分开销很可观。如果业务允许，改成基于id的连续分页：

SELECT id, cav, cave
FROM b 
WHERE 
  id > [上一页最后一条记录的id]
  AND c = '0' 
  AND k = '1' 
  AND p = '0' 
  AND cli IN ( '1', '2', '3', '4', '5', '11' ) 
  AND c_date <= '2021-02-15 23:59:59' 
  AND ((
    cav <> '0' 
    AND ( c_check_id <> '0' OR number_r = '1' ) 
    AND status = '3' 
    ) 
  OR (
    cav = '0' 
  AND status IN ( '1', '2' )))
ORDER BY id ASC 
LIMIT 200;

这种方式直接利用id索引定位起始位置，避免无意义的扫描。

内容的提问来源于stack exchange，提问作者Mộc Uyển Thanh