H2数据库带WHERE+ORDER查询大数据时性能延迟问题求助

问题根源 #

带WHERE cid=1时，MySQL无法匹配到合适的索引，只能全表扫描后再排序取数；而不带WHERE时，直接利用了name字段的索引快速获取前10条数据。核心问题是缺少能同时满足cid过滤和name排序需求的索引。

具体解决方案 #

• 创建复合索引（最优方案）
  针对你的查询逻辑，创建(cid, name, date_of_birth)的复合索引，MySQL可以：

  1. 通过cid快速过滤出所有cid=1的行
  2. 这些行在索引中已按name排序，直接取前10条即可
  3. 索引包含date_of_birth，计算age时无需回表查询原数据，进一步提升性能
     执行命令：

  CREATE INDEX idx_cid_name_dob ON campaigns_people(cid, name, date_of_birth);
  

  如果不想包含date_of_birth，至少要创建(cid, name)的复合索引，也能解决全表扫描问题，仅计算age时需要回表。

• 验证索引效果
  重新执行EXPLAIN ANALYZE你的查询，查看输出中key列是否显示刚创建的索引，rows列应接近10而非全表15万+数据，耗时会大幅降低。

• 排查现有索引
  检查表上是否已有单独的cid或name索引：

  • 单独的cid索引仅能过滤数据，但过滤后仍需对大量数据排序，成本极高
  • 单独的name索引无法过滤cid，因此带WHERE时无法被利用
    若这些索引对其他查询无作用，可考虑删除，避免MySQL选错索引。

• 临时强制使用索引（可选）
  若创建索引后MySQL仍未自动选用，可在查询中强制指定索引：

  explain analyze select name, (TIMESTAMPDIFF('YEAR', date_of_birth, CURDATE())) as age 
  from campaigns_people force index(idx_cid_name_dob)
  where cid=1 order by name asc limit 10
  

  但这只是临时手段，优先保证索引创建正确让MySQL自动选择。

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