OR查询下主键与二级索引的使用及三大数据库行为一致性问询

核心逻辑前提 #

对于WHERE uid=121 OR eid='name'这类OR条件查询，数据库能否同时利用主键索引（针对uid=121）和eid上的二级索引，本质取决于数据库优化器是否支持**索引合并（Index Merge）**或者类似的执行策略，将两个索引的结果集合并后再回表获取全量数据。

PostgreSQL #

PostgreSQL的优化器在处理这类OR条件时，支持使用BitmapOr索引合并策略：

• 优化器会分别扫描主键索引（匹配uid=121，因为主键是(uid,id)，前缀匹配uid可以用到主键索引）和eid的二级索引，生成两个bitmap结构记录符合条件的行位置
• 对两个bitmap做OR运算合并，再根据合并后的位置回表读取*对应的全量数据
• 注意：如果其中一个条件的过滤性极差（比如返回大部分数据），优化器可能会选择全表扫描而非索引合并，避免额外的索引扫描和合并开销

Google Cloud Spanner #

Spanner的优化器对OR条件的处理有明确的限制：

• Spanner不支持跨多个索引的OR查询优化，也就是说无法同时利用主键索引和eid二级索引来处理这个OR条件
• 优化器会在两种执行计划中选择：要么全表扫描，要么选择其中一个过滤性更好的索引（比如如果eid='name'返回的行数更少，就用eid二级索引；如果uid=121返回更少，就用主键索引），不会合并两个索引的结果

MySQL #

MySQL的优化器支持Index Merge - OR策略，但有前提条件：

• 当OR条件中的每个子条件都能单独匹配到一个合适的索引时（这里uid=121匹配主键索引，eid='name'匹配二级索引），优化器会选择分别扫描两个索引，获取对应的主键ID，然后去重合并这些ID，再回表读取全量数据
• 注意：如果查询中包含SELECT *，回表操作是不可避免的；另外如果某个子条件无法命中索引（比如这里如果uid不是主键前缀就不行，但这里主键是(uid,id)，所以uid=121可以命中），或者优化器评估索引合并的开销高于全表扫描，就会放弃索引合并

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