SQL Server 2019两亿行大表慢查询原因排查与数据库选型咨询

问题背景 #

• 业务表table1共存储2亿行用户活动历史数据，包含40个字段，覆盖20万用户，每日固定新增500万行数据
• 表结构示例：

userid  name    ranking energy     time       ID  ...
--------------------------------------------------- ...  
222     tom      15      f       1657642231    997  
333     sara     11      f       1657642225    996
333     sara     2       a       1657642224    995
111     jhon     4       h       1657642223    994
111     jhon     16      f       1657642222    993
333     sara     13      g       1657642221    992
222     tom      12      j       1657642220    991

• 现有索引配置：
  • 自增ID字段为聚集主键
  • userid、time字段分别建有独立的非唯一、非聚集索引，无其他索引
• 异常表现：
  • 执行select * from table1 where userid =2323 ORDER BY time耗时长达10分钟
  • 增加单日时间范围过滤的两类查询耗时超过1小时：

    select * from table1 where time > 1657642220  and userid =2323 ORDER BY time
    select * from table1 where userid =2323 and time  between 1657642220 and 1657728620  ORDER BY time
    

  • 从table1复制400万行数据到结构完全一致的table2后，执行相同查询仅需3秒

核心问题解答 #

1. table1查询速度极慢的根本原因 #

根本原因是索引设计完全不匹配查询模式，叠加大规模数据下的索引碎片、统计信息失准放大了性能问题：

• 最核心的缺陷是索引建错了逻辑。所有高频查询的过滤逻辑都是先定位指定userid，再按time做范围过滤、排序，这种模式最适配(userid, time)顺序的联合索引。现有两个独立非聚集索引完全无法高效支撑这类查询：
  • 走userid独立索引时，只能定位到该用户所有记录对应的聚集主键ID，每取一条记录就要回聚集索引查找剩余38个字段，这是随机IO操作，单用户如果有数千条活动记录，回表开销会被放大数个数量级；同时索引中不包含time字段，捞完所有记录后还要额外做文件排序，10分钟的耗时基本都消耗在这两步。
  • 增加时间过滤条件后查询反而更慢，是因为查询优化器被过时的统计信息误导，错误选择了执行计划：要么走time索引扫描单日全量500万行数据再过滤userid，要么走userid索引拉取该用户全量历史数据再过滤时间，两个选择的IO开销都远高于合理值，直接导致耗时涨到1小时以上。
• 400万行的table2查询快，是因为小数据量下全表扫描、回表、排序的总开销极低，哪怕索引不合理，SQL Server也能快速返回结果；等数据量涨到2亿，这些开销会指数级上升，性能差距会被彻底拉开。
• 日常运维缺失进一步放大了问题：表每天新增500万行数据，长期不做索引维护的话，独立索引碎片率会超过90%，统计信息和实际数据分布严重偏差，会进一步提高优化器选错执行计划的概率。

2. 是否需要更换为NoSQL等SQL Server以外的数据库？ #

完全不需要。
这个性能问题和SQL Server本身的能力没有关系，纯粹是索引设计和基础运维不到位导致的，换任何数据库只要索引逻辑错误，2亿行数据规模下一样会出现慢查询。为了这类问题更换数据库，属于典型的方案选型错位，不仅解决不了根本问题，还会额外增加数据迁移、业务适配、运维体系重建的大量成本。

3. 哪种数据库更适合承载该表的业务场景？ #

当前阶段继续使用现有SQL Server 2019即可，完成以下优化后完全可以支撑业务需求，这类单用户时序查询的耗时可以降到毫秒级：

• 立刻删除userid、time两个独立的非聚集索引，建立(userid, time)顺序的联合非聚集索引；如果查询确实每次都要返回全量40个字段，可以将高频返回的字段加入索引的INCLUDE子句建成覆盖索引，彻底消除回表和排序的开销。
• 配置常规数据库运维任务：每周针对大表做索引碎片重建/重组，每日更新表的统计信息，避免因数据快速新增导致的索引失效、优化器执行计划选择错误。
• 若后续单表数据量涨到10亿级以上，可以基于time字段做分区表，查询时间范围数据时直接扫描对应分区，进一步缩小数据扫描范围。
  如果后续业务发展到需要存储百亿级以上时序数据、有大量跨用户的时序聚合分析需求，再考虑选型InfluxDB、TimescaleDB这类专门的时序数据库即可，当前2亿行的数据规模完全没有更换数据库的必要。

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