如何通过索引加速非规范化表？性能优化及死锁问题求助

我来帮你梳理下针对这个场景的优化思路，结合你的描述，咱们从几个核心方向入手：

1. 索引策略：从“堆索引”到“精准覆盖” #

你提到现在索引扫描占80%的成本，说明现有索引没做到覆盖查询——说白了就是SQL Server查完索引还得回表拿数据，这就拖慢了速度。

• 先把动态SQL里所有可能用到的过滤、排序、关联字段列出来，然后创建覆盖索引：用INCLUDE子句把查询需要返回的列也加进去，这样数据库直接从索引里就能拿到所有需要的数据，不用再碰主表，能大幅降低扫描成本。
• 全文搜索慢的话，先检查全文目录的维护情况：看看填充因子是不是合理，有没有定期做索引重建。如果是多字段全文搜索，试试把高频搜索的字段单独建全文索引，别在大文本字段上做无差别检索。
• 别贪多建索引！每个索引都会增加写操作的锁开销，反而可能加重死锁。定期跑sys.dm_db_index_usage_stats看看哪些索引从来没被用过，果断删掉，减负才是关键。

2. 动态SQL：解决参数嗅探与执行计划重用问题 #

动态SQL拼接最容易踩的坑就是参数嗅探——不同参数对应的结果集大小差很多时，复用的执行计划根本不适用，自然慢。

• 试试在存储过程末尾加OPTION (RECOMPILE)，让每次执行都生成适配当前参数的执行计划，适合参数差异大的场景；
• 别直接拼字符串执行，改用sp_executesql，它能重用执行计划，还能防SQL注入；
• 把动态SQL拆成几个逻辑分支，比如有没有搜索参数分成不同的执行路径，减少不必要的条件拼接，让执行计划更精准。
• 还有个细节：别在列上做函数操作（比如WHERE YEAR(CreateTime) = 2024），这种操作会直接让索引失效，逼着数据库做全扫描。

3. 死锁问题：从“堵”到“疏” #

LCK_M_X（排他锁）和LCK_M_S（共享锁）的死锁，大多是因为读写顺序乱了或者锁持有时间太长。

• 先抓死锁图！用SQL Server的扩展事件或者sys.dm_tran_locks工具，搞清楚到底是哪几条语句、哪个资源在抢锁——找不到根源的优化都是瞎忙活。
• 缩短事务长度：把事务里的非核心操作（比如日志记录、前端参数校验）移到事务外面，别让锁一直占着。
• 试试调整隔离级别：如果业务允许，开启数据库的ALLOW_SNAPSHOT_ISOLATION，把事务隔离级别改成READ COMMITTED SNAPSHOT，用行版本控制代替共享锁，读写互不干扰，能大幅减少死锁。
• 统一访问顺序：所有事务访问表的顺序要一致，比如先访问Companies表，再访问你的非规范化表，避免循环等待导致死锁。

4. 非规范化表的瘦身与优化 #

36万条记录其实不算超大，但35列的宽表会让索引体积暴涨，扫描起来自然慢。

• 拆分宽表：把不常用的列拆到附属表，只在需要的时候关联查询，主表的索引变小了，扫描速度自然提上来。
• 更新统计信息：过时的统计信息会让数据库生成错误的执行计划，定期跑UPDATE STATISTICS [你的表名] WITH FULLSCAN，让数据库知道数据的真实分布。
• 如果是读多写少的场景，试试把表改成内存优化表，内存里的操作速度比磁盘快N倍，而且锁机制更轻量，死锁概率也低。

5. 查询细节的小优化 #

• 内连接Companies表时，确保连接字段是Companies的主键或者唯一索引，这样连接操作的成本会低很多；
• 全文搜索用CONTAINS代替FREETEXT，前者的精准度和性能都更好，尤其是指定关键词的时候；
• 别返回多余的列！只拿业务需要的字段，减少数据传输开销，也更容易做覆盖索引。

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