复合索引设计 & 优化器选择逻辑 - mysql

问题背景：

一个select语句中，where条件语句为 where isdeleted=0 and order_id is not null; 这个select语句执行了11s。

表中已有索引：

• • 索引1：（order_id）
  • 索引2：（order_id, starttime, isdeleted）

分析：

　　explain analyze select ... 观察执行器执行逻辑发现命中索引1，而并未命中索引2；

　　这是因为 is not null 是范围条件，此时索引2 后续列都无法再用于过滤，实际能用的只有第一列 order_id，因此作用等同于索引1，而索引2 体积比 索引1 更大，扫描时 I/O 成本更高，所以会命中索引1；

　　1. 为什么范围条件导致后续索引列无法用于过滤？

　　因为索引的结构是 B+Tree，键值有序。比如索引 (order_id, starttime, isdeleted)，数据首先按 order_id 排序，order_id 相同的情况下再按 starttime 排序，最后按 isdeleted 排序。而当order_id 使用范围条件时，会导致后续列无序，故此时后续列无法用于过滤。

　　IN在执行时，实际表现为多个等值查询的 OR 组合，但如果IN列表过大，需要另外考虑（小于 eq_range_index_dive_limit，默认 200）；

 

　　2. 一旦第一列order_id使用了范围，索引中后续的列无法再用于过滤，只能用于覆盖索引（如果查询的列都在索引中）。

　　覆盖索引：查询所需的所有列都在索引中，无需回表。

　　对于 DELETE 或 SELECT *，覆盖索引无意义，因为SELECT *包含所有列，而DELETE需要删除行，不仅要定位行，还要修改数据页和索引，即使索引包含所有列，也要回表。

 

优化：

方案一：新建索引 (isdeleted, order_id)；

Q. (isdeleted, order_id) & (order_id, isdeleted) 为什么是前者？

　　等值条件（=、IN）需放在 范围条件（包括 !=、>、<、BETWEEN、LIKE 'abc%'、IS NOT NULL 等）之前。

　　效果：先通过等值过滤掉 isdeleted=0 的大量数据（如果删除标记占比高），再在剩余小范围内处理 order_id 非 NULL。

 

方案二：给 order_id 加上一个状态列，如 order_status，标记为 valid 或 invalid，然后直接过滤 order_status = 'valid'，添加索引（isdeleted, order_status）

 

Ps：两个方案对比：

1）方案二从索引效率来说，两个都是精确查找，查找范围要优于方案一，执行器 io 和 cpu压力更低；

2）如果不想新增列，方案一什么时候合适？
a. isdeleted可以过滤大部分列（在这个例子中，即isdeleted=0的记录很少）；同理，或者 order_status基本都非null，方案二等同几乎为全表扫描时；

b. 数据量不多（<10w行），性能差异微乎其微；

 

PPs：然而，意外情况，我发现增加 复合索引(isdeleted, order_id)后，执行器还是命中 单索引order_id，使用force index强制指定后，单索引反而表现比复合索引更好，这是为什么呢？

观察表数据分布，表共8w+行数据，isdeleted=0 符合记录有8w，order_id is not null 符合记录 7w；因此这两个条件几乎过滤不掉任何数据，几乎等同于全表扫描（也可 explain format=json select ...，查看filtered字段，99.84%，过滤效果极差）。而加载索引需要的开销，单索引小于复合索引，因此执行器选择单索引。

面对这种情况，如果是delete或者select *，可以尝试放弃索引，直接按照等值条件分批操作；

而在这个案例中，我的select和where只涉及5个字段，而全表共有20+字段，所以我可以考虑从减少回表入手；也就是新建索引（isdeleted, order_id, zidaun1, ziduan2, ziduan3），即覆盖索引。修改后再测试，发现执行器这次选择了新索引，观察执行时间效率确有提升。

 

PPPs: 上述都是基于mysql的优化，而转为使用doris是一个很好的架构优化方案。

两者的设计定位简单来说，MySQL是为高并发的实时业务请求（OLTP）而生，而Doris是为海量数据分析（OLAP）诞生的。具体而言（个别点）：

a. MySQL采用行式存储，doris使用列式存储，分析的数据基数就小很多；

b. mysql是主从架构，受限于单台服务器性能；doris是并行处理架构，能将任务拆分并分发至多个节点后汇总结果，同理doris的扩展能力也要更好；