SQLServer SQL语句优化简介

在日常的工作中，或多或少会遇到一些较为复杂的SQL，当我们发现这些语句执行的慢时，总是会想办法对其修改，让它变得更快一些。本篇就来简单介绍下SQLSERVER下SQL的语句调优方法。

本文结构：

  • 语句的执行顺序
  • 优化思路
  • 使用索引
  • 写出简练的语句

1. 语句的执行顺序

要对复杂的SQL进行优化，语句的执行顺序可谓是重中之重，我们只有明确了执行顺序，才能在后续简化SQL的过程中有的放矢的进行。否则，如果方向错了，再努力也是徒劳。

SQL不同于编程语言的顺序执行，它是由固定的执行顺序的。SQL的每个执行步骤都会产生一个虚拟表，该虚拟表作为下一步的输入，这些虚拟表对于调用者不可见，只有最后一步select动作返回的虚拟表才会返回给调用者。

SQL的语法如下：

    (8) SELECT (9) DISTINCT  (11) <Top Num> <select list>
    (1) FROM [left_table]
    (3) <join_type> JOIN <right_table>
    (2)        ON <join_condition>
    (4) WHERE <where_condition>
    (5) GROUP BY <group_by_list>
    (6) WITH <CUBE | RollUP>
    (7) HAVING <having_condition>
    (10) ORDER BY <order_by_list>

标准SQL的解析顺序：

  • （1）FROM子句：组装来自不同数据源的数据
  • （2）WHERE子句：基于指定条件对记录进行筛选
  • （3）GROUP BY子句：将记录分组
  • （4）对聚合函数进行计算
  • （5）使用HAVING子句筛选分组
  • （6）计算所有的表达式
  • （7）使用ORDER BY对结果进行排序

执行顺序：

  • （1） FROM：对于from子句中的前两个表进行笛卡尔积，生成虚拟表VT1；
  • （2） ON：对VT1表筛选，符合join_condition的写入VT2；
  • （3）OUTER(join)：如果指定了OUTER JOIN，保留表中未找到的行将作为外部行插入到VT2中，生成VT3；如果from包含两个以上表则对上一个联结生成的结果表和下一个表重复执行此步骤直到结束；
  • （4）WHERE：对VT3进行筛选，只有where_condition为真的记录会被写入到VT4；
  • （5）GROUP BY：对VT4进行分组，生成VT5；
  • （6）CUBE|ROLLUP：根据聚合函数生成VT6；
  • （7）HAVING：对VT6根据having_condition条件进行筛选，生成VT7；
  • （8）SELECT：选择select列，生成VT8；
  • （9）DISTINCT：将重复的行从VT8中移除，生成VT9；
  • （10）ORDER BY：将VT9的行根据order by子句排序生成一个游标VC10；
  • （11）TOP：从VC10的开始处选择特定的行生成VT11，返回给调用者；

2. 优化思路

（1）循环改批量

对于循环改批量操作，这个对于程序员来说应该是很常见的场景：

  • 应用程序使用循环对数据库的操作，要记住每一次数据库的操作都需要建立连接、关闭连接，这是耗时的操作。因此整体性能也会差很多，如对数据库进行插入操作时，使用循环单条操作和批量，性能真可谓是天壤之别，这个可以参考本站的其他两篇文章C# SqlBulkCopy介绍， Bulk Insert介绍；
  • 在数据库中，游标跟for循环是一个道理，因此要尽可能的避免使用游标；
  • 如果确实无法避免需要循环处理，那么可以在操作前开启事务，等整个循环处理完之后，再提交；

（2）降低语句复杂性

一般来说，绝大部分的慢查询都是因为SQL语句写法的问题，当然因为业务的需要，的确会导致一些很复杂的SQL语句：

  • 程序代码逻辑本身就很复杂，需要关联很多表，还需要聚合和排序，嵌套着子查询和函数运算；
  • 业务需要创建了很多视图，视图又嵌套视图，然后外层还需要关联其他表等；

对上上述两种情况，优化的办法都是降低复杂性，把尽量拆分将其放入临时表或表变量中，如共有10张表的关联，可以先关联5张表写入临时表，再用临时表关联另外5张表，这样关联的复杂性就降低了。

对于复杂视图，也是如此，在视图与外层关联前，放入临时表，再跟外层关联；

对于子查询，可以先将子查询的结果放入临时表；

（3）避免重复读取

有时候会碰到对于同一份基础数据，需要多各种不同的运算，如不同维度的报表分析，因此出现每一次分析都从原始表中读取数据，然后运算。对于这种情况，其实可以将基础数据读取之后写入临时表，后续的查询分析就可以通过临时表来完成，这样大大减少了查询的数据量。

需要注意的时，对于创建大数据量的临时表，一定要试用先create再insert的方式，而不能直接使用select into,因为对于大数据量来说，select into的性能很差。

3. 使用索引

对于程序员来说，基于数据库的优化可能了解较少，但不管怎样一定要了解索引的使用。索引是写出高效SQL的根本，如果你了解索引，那么是不可能写出高效的SQL的。

大家一定要有这样的意识，那就是语句的where查询条件的最终目标就是要让其命中索引，避免全表扫描。

关于数据库索引的一些知识，大家可以自行搜索下，这方面应该是面试的重点考察知识了。

（1）建立索引

首先要知道什么样的列适合建立索引，哪些列不合适。

  • 选择性高的且不常变更的列才适合建立索引。

选择性可以使用下面的语句来判定，其值越接近1，说明选择性越高，建立索引的收益就越大，如对主键列的计算结果就为1：

    SELECT cast(count(distinct col1) as float)/count(col1)  FROM table

因为索引的建立和维护也是需要空间的，对于频繁变动的列，会增加这方面的开销，因此最好是在少变更的列上建立索引。如相对于updatetime来说，inserttime列更适合建立索引，因为一般来说inserttime一旦写入就不会变更了，而updatetime可能面临多次修改而更新的情况。

  • 一张表的索引个数不超过8个
  • 复合索引建立的顺序需要注意
  • 查看执行计划会显示缺少索引的列

（2）让语句走索引

  • 在语句中的查询条件中不能出现函数或计算，如price列本来有索引，但是如果出现where price/2 > 100这种情况是不会走索引的，可以改为where price > 100*2；
  • 不能出现类型的隐式转换，如int型，datetime型都当作nvarchar来计算这样在查询计划中可以看到CONVERT_IMPLICIT标志，同时如果是nvarchar/varchar字段时，其长度也应该与数据库长度一致；
  • 如果使用EF，对于bool类型，不要出现col.HasValue && col.Value这种查询，因为这样转换成的语句是 where (col is not null && col ='X'),这样出现不必要的判断；
  • 对于索引的模糊匹配，like 'xx%'会走索引，而like '%xx'不会；
  • 对于组合索引，查询条件必须按顺序匹配才行，如组合索引index(a, b), 那么where a=x会走索引，而where b=x不会；
  • 在索引列避免出现计算、函数、空值、类型转换、IS NULL/IS NOT NULL、not、<>、!=;

不过这里需要说明的，数据库会根据执行计划和统计分析的结果来评估是否需要走索引，如果出现全局扫描查询比走索引快时，这是数据库不会使用索引，这种情况通常在数据库的数据集很小的情况下出现。

4. 写出简练的语句

（1）只返回需要的数据

一般来说返回给客户端的数据都需要经过存储、网络传输才能到达客户端，如果是客户端不需要的数据给返回了，那自然是白白浪费网络带宽，影响执行速度。因此我们需要注意的：

  • 不要写select *， 指选择需要的数据；
  • 对于有多表关联的left join语句，在select列时将表的别名前缀带上，这样减少解析查询的时间
  • where条件尽可能的保证能够筛选大部分数据
  • 返回给前段的数据库是否真需要那么多，页面是否可以添加默认条件等？

笔者现在接手的项目中就有很多页面没有默认条件，用户一开始本能性的查询全部数据，导致SQL执行缓慢，且页面传输的数据也很大，最后直接的结果就是页面加载慢的难以忍受，用户一直抱怨。因此我也是建议在某些页面强制添加查询条件或者先给设置默认条件等方法来减少结果集。

（2）减少不必要的操作

有时候我们会发现常常因为取某一两个字段关联了一大堆表，这个时候就需要衡量这些字段是否真的需要返回显示，是否真的需要在这么早的做关联？是否可以考虑现将基础信息的结果集取出之后再做分步查询，或者干脆在页面做页面异步。

还有一些可以注意的点：

  • 减少不必要表的关联，没关联一个表，都要多一分开销
  • 减少不必要的判断，在试用EF进行判空查询时，SQL经常会被转换为 X is not null and X = 'bb'等，这样是比较耗性能的，一个好的做法是在数据库中给字段设置默认值
  • 确定是否需要去重，只有在必要的情况下使用distinct，union操作，有些情况下明知道两个集合不会重复的话，使用union all而不是union，前者更快
  • 数据集是否需要真的需要排序，如确有需要可否通过索引字段来提高性能

（3）筛选应该尽早进行

  • 筛选条件放在where子句和having子句是有差别的，从文首的语句执行顺序可以看到where子句在前面执行，因此尽可能的将筛选条件放在where子句中。另外再有一些子查询的情况下，尽可能的在内层查询中也添加上where条件
  • 复杂情况下可以试用临时表来降低语句复杂性，但要尽可能的使用有条件的多表关联来降低临时表的数据量
  • 如果查询条件出现隐式转换，或者在索引列使用函数或计算，都会导致筛选无法尽早的进行

标签: SQLSERVER, 索引, SQL优化

添加新评论