MySQL大表查询未使用PRIMARY主键索引的原因及JPA下的查询优化改写咨询

问题详情 #

原始查询语句 #

EXPLAIN SELECT b.* FROM bills b WHERE
(b.id IN (SELECT u.id FROM bills u WHERE u.updated BETWEEN '2023-12-19 14:17:48' AND '2023-12-19 14:27:40' AND u.organisation_id = 'ABC123')
OR b.id IN (SELECT c.id FROM bills c WHERE c.created BETWEEN '2023-12-19 14:17:48' AND '2023-12-19 14:27:40' AND c.organisation_id = 'ABC123'))

EXPLAIN执行计划结果 #

核心疑问 #

1. 为什么主查询（第一行）没有使用仅基于id的PRIMARY索引？即使添加FORCE INDEX (PRIMARY)也无效。
2. 如何在JPA环境下将查询改写为单个更高效的版本（无法使用UNION）？

补充编辑 #

编辑1：我目前正在把查询改写为更复杂的版本，类似下面的结构：

SELECT b.* FROM bills b WHERE
(b.updated BETWEEN '2023-12-19 14:17:48' AND '2023-12-19 14:27:40'
OR c.created BETWEEN '2023-12-19 14:17:48' AND '2023-12-19 14:27:40')
AND c.organisation_id = 'ABC123';

（注：这里应该是笔误，c应为b）

编辑2：补充一下，表上已有(updated)和(organisation_id,created)索引。之前的查询因为在不同列的不等式上使用OR，MySQL无法同时利用两个索引，只能使用其中一个，然后对另一条件执行全表扫描或全量查找。

解答 #

一、为什么PRIMARY索引没被用上？ #

这主要是MySQL查询优化器的成本估算和执行计划选择逻辑导致的，核心原因可能有两个：

1. 统计信息过时导致成本估算偏差
   从EXPLAIN结果看，两个子查询总共最多返回5条id（子查询u返回4条，c返回1条）。理论上用PRIMARY索引匹配这5个id应该远快于全表扫描1040万行，但优化器却选择了全表扫描——大概率是表或索引的统计信息过时，让优化器误判全表扫描的成本更低。
   你可以先执行ANALYZE TABLE bills;更新统计信息，之后再看EXPLAIN结果是否有变化。
   另外，FORCE INDEX失效也和成本估算有关：如果优化器判断即使强制用PRIMARY索引，执行成本依然高于全表扫描，就会忽略强制指令。

2. IN子查询的执行逻辑限制
   部分MySQL版本对IN (子查询)的处理是先执行子查询得到id列表，再在外层查询中逐行匹配。但优化器可能没有将这个逻辑转换为更高效的JOIN形式，而是选择了全表扫描来匹配id，导致主键索引的快速定位能力无法被利用。

二、JPA兼容的高效查询改写方案 #

既然JPA无法直接用UNION，我给你几个纯JPQL/可兼容JPA的改写思路：

方案1：用EXISTS替代IN，引导优化器利用索引

把IN子查询替换为EXISTS关联查询，这样优化器更容易将外层的id和子查询关联，从而利用主键索引或子查询的复合索引：

SELECT b FROM bills b
WHERE EXISTS (
    SELECT 1 FROM bills u 
    WHERE u.id = b.id 
      AND u.updated BETWEEN :startTime AND :endTime 
      AND u.organisationId = :orgId
) OR EXISTS (
    SELECT 1 FROM bills c 
    WHERE c.id = b.id 
      AND c.created BETWEEN :startTime AND :endTime 
      AND c.organisationId = :orgId
)

这个写法在JPQL中完全支持，而且优化器可以通过子查询的索引快速过滤，外层查询也更可能选择PRIMARY索引来匹配id。

方案2：利用复合索引改写为单条件OR查询

结合你已有的索引，先把organisation_id作为前置条件，再用OR连接时间范围，这样可以利用复合索引的前缀匹配特性：

SELECT b FROM bills b
WHERE b.organisationId = :orgId
  AND (
      b.updated BETWEEN :startTime AND :endTime
      OR 
      b.created BETWEEN :startTime AND :endTime
  )

建议你新增一个复合索引(organisation_id, updated, created)，或者分别创建(organisation_id, updated)和(organisation_id, created)两个复合索引——MySQL的Index Merge特性可以将这两个索引的结果合并，避免全表扫描。

方案3：用JOIN结合临时结果集（兼容JPA原生SQL）

如果允许使用JPA原生SQL，也可以用UNION ALL生成过滤后的id集合，再和主表JOIN（比全表扫描快得多）：

SELECT b.* FROM bills b
JOIN (
    SELECT u.id FROM bills u 
    WHERE u.updated BETWEEN ?1 AND ?2 
      AND u.organisation_id = ?3
    UNION ALL
    SELECT c.id FROM bills c 
    WHERE c.created BETWEEN ?1 AND ?2 
      AND c.organisation_id = ?3
) AS filtered_ids ON b.id = filtered_ids.id

UNION ALL不会去重，比UNION性能更好，而且原生SQL在JPA中是完全支持的。

三、额外优化建议 #

1. **避免SELECT * **：如果不需要所有列，只查询需要的字段，这样可以创建覆盖索引，避免回表操作，大幅提升速度。
2. 升级MySQL版本：如果用的是5.7及以下版本，升级到8.0+可以获得更好的子查询优化、索引合并优化能力。
3. 定期更新统计信息：对于数据量变化大的表，定期执行ANALYZE TABLE可以让优化器做出更准确的执行计划选择。

备注：内容来源于stack exchange，提问作者mjaggard