速度提升10倍 | 记一次苦逼的 SQL 查询优化

最近在维护公司项目时，需要加载某页面，总共加载也就 4000 多条数据，竟然需要 35 秒钟，要是数据增长到 40000 条，我估计好几分钟都搞不定。要我是用户的话估计受不了，于是趁有时间，就想把它优化一下。

先把查询贴上：

执行后有图有真相：

这么慢，没办法就去看看查询计划是怎么样：

这是该 sql 查询里面执行三个函数时生成查询计划的截图，一看就知道，执行时开销比较大，而且都是花费在聚集索引扫描上，把鼠标放到聚集索引扫描的方块上面，依次看到如下详细计划：

从这几张图里，可以看到查询 I/O 开销，运算符开销，估计行数，以及操作的对象和查询条件，这些都为优化查询提供了有利证据。第 1，3 张图 IO 开销比较大，第 2 张图估计行数比较大，再根据其它信息，首先想到的应该是去建立索引，不行的话再去改查询。

先看看数据库引擎优化顾问能给我们提供什么优化信息，有时候它能够帮我们提供有效的信息，比如创建统计，索引，分区什么的。

先打开 SQL Server Profiler 把刚刚执行的查询另存为跟踪 (.trc) 文件, 再打开数据库引擎优化顾问，做如下图操作

最后生成的建议报告如下：

在这里可以单击查看一些建议，分区，创建索引，根据提示创建了如下索引：

索引创建后，再次执行查询，原以为可提高效率，没想到我勒个去，还是要 30 几秒，几乎没什么改善，优化引擎顾问有时候也会失灵，在这里只是给大家演示有这种解决方案去解决问题，有时候还是靠谱的，只是这次不靠谱。没办法，只有打开函数仔细瞅瞅，再结合上面的查询计划详细图，删除先前创建的索引，然后创建了如下索引：

再次执行查询

好了，只需 3.5 秒，差不多提高 10 倍速度，看来这次是凑效了哈。

再来看看查询计划是否有改变，上张图来说明下问题：

从上图当中我们可以看到，索引扫描不见了，只有索引查找，聚集索引查找，键查找，而且运算符开销，I/O 开销都降低了很多。索引扫描 (Index Scan), 聚集索引扫描 (Clustered Index Scan) 跟表扫描 (Table Scan) 差不多，基本上是逐行去扫描表记录，速度很慢，而索引查找 (Index Seek)，聚集索引查找，键查找都相当的快。优化查询的目的就是尽量把那些带有 XXXX 扫描的去掉，换成 XXXX 查找。

这样够了吗？但是回头又想想，4000 多条数据得 3.5 秒钟，还是有点慢了，应该还能再快点，所以决定再去修改查询。看看查询，能优化的也只有那个三个函数了。

为了看函数执行效果先删除索引，看看查询中函数 f_GetAidNamebyConsult1 要干的事情，截取查询中与该函数有关的子查询：

得到下图的结果：

没想到就这么点数据竟然要 46 秒，看来这个函数真的是罪魁祸首。

该函数的具体代码就不贴出来了，而且该函数里面还欠套的另外一个函数，本身函数执行起来就慢，更何况还函数里子查询还包含函数。其实根据几相关联的表去查询几个字段，并且把一个字段的值合并到同一行，这样没必要用函数或存储过程，用子查询再加 sql for xml path 就行了，把该函数改成如下查询：

这样查询出来的结果在没有索引的情况下不到 1 秒钟就行了。再把主查询写了：

这样基本上就完事了，在没有建立索引的情况下需要 8 秒钟，比没索引用函数还是快了 27 秒。

把索引放进去，就只需 1.6 秒了，比建立索引用函数而不用子查询和 sql for xml path 快了 1.9 秒

查询里面还有个地方用了函数，估计再优化下还能提高执行效率，因为时间有限再加上篇幅有点长了，在这里就不多讲了。

总 结

• 增加索引或重建索引。通常在外键，连接字段，排序字段，过滤查询的字段建立索引，也可通过数据库引擎优化顾问提供的信息去建索引。有时候当你创建索引时，会发现查询还是按照索引扫描或聚集索引扫描的方式去执行，而没有去索引查找，这时很可能是你的查询字段和 where 条件字段没有全部包含在索引字段当中，解决这个问题的办法就是多建立索引，或者在创建索引时 Include 相应的字段，让索引字段覆盖你的查询字段和 where 条件字段。
• 调整查询语句，前提要先看懂别人的查询，搞清楚业务逻辑。
• 表分区，大数据量可以考虑。
• 提高服务器硬件配置。

转自微信公众号：DBGeeK