Posted on

SQL Server调优系列基础篇（常用运算符总结——三种物理连接方式剖析）

  1. SQL Server调优系列基础篇
  2. SQL Server调优系列基础篇（常用运算符总结——三种物理连接方式剖析）
  3. SQL Server调优系列基础篇（联合运算符总结）
  4. SQL Server调优系列基础篇（并行运算总结一）
  5. SQL Server调优系列基础篇（并行运算总结篇二）
  6. SQL Server调优系列基础篇（索引运算总结）
  7. SQL Server调优系列基础篇（子查询运算总结）
  8. SQL Server调优系列进阶篇（查询优化器的运行方式）
  9. SQL Server调优系列进阶篇（查询语句运行几个指标值监测）
  10. SQL Server调优系列进阶篇（深入剖析统计信息）
  11. SQL Server调优系列进阶篇（如何索引调优）
  12. SQL Server调优系列进阶篇（如何维护数据库索引）

前言

上一篇我们介绍了如何查看查询计划，本篇将介绍在我们查看的查询计划时的分析技巧，以及几种我们常用的运算符优化技巧，同样侧重基础知识的掌握。

通过本篇可以了解我们平常所写的T-SQL语句，在SQL Server数据库系统中是如何分解执行的，数据结果如何通过各个运算符组织形成的。

技术准备

基于SQL Server2008R2版本，利用微软的一个更简洁的案例库（Northwind）进行解析。

一、数据连接

数据连接是我们在写T-SQL语句的时候最常用的，通过两个表之间关联获取想要的数据。

SQL Server默认支持三种物理连接运算符：嵌套循环连接、合并连接以及哈希连接。三种连接各有用途，各有特点，不同的场景会数据库会为我们选择最优的连接方式。

a、嵌套循环连接（nested loops join)

嵌套循环连接是最简单也是最基础的连接方式。两张表通过关键字进行关联，然后通过双层循环依次进行两张表的行进行关联，然后通过关键字进行筛选。

可以参照下图进行理解分析

其实嵌套扫描是很简单的获取数据的方式，简单点就是两层循环过滤出结果值。

我们可以通过如下代码加深理解

for each row R1 in the outer table
   for each row R2 int the inner table
       if R1 join with R2
       return (R1,R2)

举个列子 Continue reading “SQL Server调优系列基础篇（常用运算符总结——三种物理连接方式剖析）”

Posted on

SQL Server 数据库的一些基本概念

1.区:

区是SQL Server 中管理空间的基本单位。

一个区是八个物理上连续的页（即 64 KB）, 所有页都存储在区中, 这意味着 SQL Server 数据库中每 MB 有 16 个区。 一旦一个区段已满, 下一条数据Sql server将分配一个区段空间, 防止每次添加都要分配空间.

2.页（Page）:

页是SQL Server 中数据存储的基本单位, 它是区段的分配单元, 一页8K, 它下面就是数据行了, 但每页的行数不定, 这取决于数据行的大小.

数据库中的数据文件（.mdf 或 .ndf）分配的磁盘空间可以从逻辑上划分成页（从 0 到 n 连续编号）。磁盘 I/O 操作在页级执行。也就是说，SQL Server 每次读取或写入数据的最少数据单位是数据页。

每页的开头是 96 字节的标头，用于存储有关页的系统信息。此信息包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元 ID。

在 SQL Server 中，页的大小为 8 KB。这意味着 SQL Server 数据库中每 MB 有 128 页。依次类推。根据数据库的文件大小，我们可以算出数据库有多少数据页。

3.页的类型: 数据页, 索引页, Blob页等等.

4.行: 因为行存于页中,因此, 一行的大小通常最多8K(8060字符, 一页的上限), 一行最大列数为1024列(字段).
如果是varchar(max), text, image时, 可以跨越多页, 一行最大2GB, 此时, 原始的行用来存放指针及其它列.

5.全文目录: 虽然和sql server在一起, 但实际上该目录是独立存放在磁盘上的.

6.索引: 是与表或视图关联的磁盘上的结构，可以加快从表或视图中检索行的速度。

它包含由表或视图中的一列或多列生成的键。这些键存储在一个结构（B 树）中，使 SQL Server 可以快速有效地查找与键值关联的行。

7.索引的类型：聚集, 非聚集.

另外还有唯一索引, 唯一索引确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一的。

聚集索引和非聚集索引都可以是唯一索引。

8.聚集索引: 根据数据行的键值在表或视图中排序和存储这些数据行。

每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。

只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。

如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。

即聚集表是有聚集索引的表，堆是没有聚集索引的表。

索引视图与聚集表具有相同的存储结构。

聚集索引的叶节点就是实际的数据页。

聚集索引的平均大小大约为表大小的5%左右。

9.非聚集索引：

每个表最多可以有249个非聚集索引.

10.索引的维护：每当修改了表数据后，都会自动维护表或视图的索引。

11.索引和约束: 对表列定义了 PRIMARY KEY 约束和 UNIQUE 约束时，会自动创建索引。

下面摘自MSDN:

页和区

SQL Server 中数据存储的基本单位是页。为数据库中的数据文件（.mdf 或 .ndf）分配的磁盘空间可以从逻辑上划分成页（从 0 到 n 连续编号）。磁盘 I/O 操作在页级执行。也就是说，SQL Server 读取或写入所有数据页。

区是八个物理上连续的页的集合，用来有效地管理页。所有页都存储在区中。

页

在 SQL Server 中，页的大小为 8 KB。这意味着 SQL Server 数据库中每 MB 有 128 页。每页的开头是 96 字节的标头，用于存储有关页的系统信息。此信息包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元 ID。

下表说明了 SQL Server 数据库的数据文件中所使用的页类型。

页类型 内容
Data 当 text in row 设置为 ON 时，包含除 text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据之外的所有数据的数据行。
Index 索引条目。
Text/Image 大型对象数据类型：

  • text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据。

数据行超过 8 KB 时为可变长度数据类型列：

  • varchar、nvarchar、varbinary 和 sql_variant
Global Allocation Map、Shared Global Allocation Map 有关区是否分配的信息。
Page Free Space 有关页分配和页的可用空间的信息。
Index Allocation Map 有关每个分配单元中表或索引所使用的区的信息。
Bulk Changed Map 有关每个分配单元中自最后一条 BACKUP LOG 语句之后的大容量操作所修改的区的信息。
Differential Changed Map 有关每个分配单元中自最后一条 BACKUP DATABASE 语句之后更改的区的信息。
注意
日志文件不包含页，而是包含一系列日志记录。

在数据页上，数据行紧接着标头按顺序放置。页的末尾是行偏移表，对于页中的每一行，每个行偏移表都包含一个条目。每个条目记录对应行的第一个字节与页首的距离。行偏移表中的条目的顺序与页中行的顺序相反。

具有行偏移的 SQL Server 数据页

大型行支持

行不能跨页，但是行的部分可以移出行所在的页，因此行实际可能非常大。页的单个行中的最大数据量和开销是 8,060 字节 (8 KB)。但是，这不包括用 Text/Image 页类型存储的数据。包含 varchar、nvarchar、varbinary 或 sql_variant 列的表不受此限制的约束。当表中的所有固定列和可变列的行的总大小超过限制的 8,060 字节时，SQL Server 将从最大长度的列开始动态将一个或多个可变长度列移动到 ROW_OVERFLOW_DATA 分配单元中的页。每当插入或更新操作将行的总大小增大到超过限制的 8,060 字节时，将会执行此操作。将列移动到 ROW_OVERFLOW_DATA 分配单元中的页后，将在 IN_ROW_DATA 分配单元中的原始页上维护 24 字节的指针。如果后续操作减小了行的大小，SQL Server 会动态将列移回到原始数据页。有关详细信息，请参阅行溢出数据超过 8 KB。

区

区是管理空间的基本单位。一个区是八个物理上连续的页（即 64 KB）。这意味着 SQL Server 数据库中每 MB 有 16 个区。

为了使空间分配更有效，SQL Server 不会将所有区分配给包含少量数据的表。SQL Server 有两种类型的区：

  • 统一区，由单个对象所有。区中的所有 8 页只能由所属对象使用。
  • 混合区，最多可由八个对象共享。区中八页的每页可由不同的对象所有。

通常从混合区向新表或索引分配页。当表或索引增长到 8 页时，将变成使用统一区进行后续分配。如果对现有表创建索引，并且该表包含的行足以在索引中生成 8 页，则对该索引的所有分配都使用统一区进行。

混合区和统一区

行内数据

小到中等大小的大值类型（varchar(max)、nvarchar(max)、varbinary(max) 和 xml）和大型对象 (LOB) 数据类型（text、ntext 和 image）都可以存储在数据行中。该行为可以通过在 sp_tableoption 系统存储过程中使用以下两个选项来控制：用于大值类型的 large value types out of row 选项，以及用于大型对象类型的 text in row 选项。这两个选项最适用于这样的表：其中上述任意一种数据类型的数据值通常在一个单元中读/写，并且引用表的大多数语句都将引用此类数据。在行内存储的数据不一定有用，这取决于使用情况或工作负荷特征。

除非 text in row 选项设置为 ON 或特定的行内限制，否则 text、ntext 或 image 字符串都将是在数据行外存储的大型字符或二进制字符串（最多 2 GB）。数据行只包括一个 16 字节的文本指针，该指针指向一个内部指针构成的树的根节点。这些指针映射存储字符串片段的页。有关 text、ntext 或 image 字符串存储的详细信息，请参阅使用 Text 和 Image 数据。

可以为包含 LOB 数据类型列的表设置 text in row 选项。还可以指定 text in row 选项限制，范围从 24 到 7,000 字节。

同样，除非 large value types out of row 选项设置为 ON，否则会尽可能将 varchar(max)、nvarchar(max)、varbinary(max) 和 xml 列存储在数据行内。如果如此设置，则可以的话 SQL Server 数据库引擎将尝试容纳此特定值，否则会将其推到行外。如果 large value types out of row 设置为 ON，则上述值将存储在行外而只有 16 字节的文本指针存储在记录中。

注意
当 large value types out of row 设置为 OFF 时，用于大型值数据类型的最大行内存储量设置为 8,000 字节。与 text in row 选项不同，您不能指定表中列的行内限制。

将表配置为直接在数据行中存储大型值类型或大型对象数据类型时，如果存在以下情况之一，实际的列值都将存储在行内：

  • 字符串的长度小于为 text、ntext 和 image 列指定的限制值。
  • 数据行中有足够的可用空间容纳字符串。

当大型值类型或大型对象数据类型列值存储在数据行中时，数据库引擎不必访问单独的页或页集来读/写字符或二进制字符串。这便使读/写行内字符串的速度与读/写大小受限制的 varchar、nvarchar 或 varbinary 字符串的速度大致一样。同样，当值存储在行外时，数据库引擎将引发读/写附加页。

对于大型对象数据类型，如果存储字符串所需的空间比 text in row 选项限制或行中的可用空间大，则本应存储在指针树根节点中的指针集将存储在行中。如果存在以下情况之一，指针将存储在行中：

  • 存储指针所需的空间量比 text in row 选项限制指定的空间量小。
  • 数据行中有足够的可用空间容纳指针。

当指针从根节点移至行本身时，数据库引擎不需要使用根节点。这样便可以在读/写字符串时不必访问页。从而可以提高性能。

如果使用根节点，它们将存储为 LOB 页中的一个字符串片段，并且最多可以包含 5 个内部指针。数据库引擎需要行具有 72 字节的空间来存储行内字符串的五个指针。如果 text in row 选项为 ON 或 large value types out of row 选项为 OFF 时行中没有足够的空间来容纳指针，数据库引擎可能必须分配一个 8K 的页来容纳它们。如果值的数据长度超过 40,200 字节，则需要 5 个以上的行内指针，此时只有 24 字节存储在主行中，而其他数据页被分配在 LOB 存储空间中。

当大型字符串存储在行中时，它们将与可变长度字符串的存储方式相似。数据库引擎将对列按大小以降序排序，并将值推到行外，直到剩余的列容纳在数据页 (8K) 中。

行溢出数据超过 8 KB

一个表中的每一行最多可以包含 8,060 字节。在 SQL Server 2008 中，对于包含 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型列的表，可以放宽此限制。其中每列的长度仍必须在 8,000 字节的限制内，但是它们的总宽可以超过 8,060 字节的限制。创建和修改 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型的列以及更新或插入数据时，此限制适用于上述列。

当合并每行超过 8060 字节的 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型的列时，请注意下列事项：

  • 超过 8,060 字节的行大小限制可能会影响性能，因为 SQL Server 仍保持每页 8 KB 的限制。当合并 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型的列超过此限制时，SQL Server 数据库引擎 将把最大宽度的记录列移动到 ROW_OVERFLOW_DATA 分配单元的另一页上，而在原始页上保留一个 24 字节指针。如果更新操作使记录变长，大型记录将被动态移动到另一页。如果更新操作使记录变短，记录可能会移回 IN_ROW_DATA 分配单元中的原始页。此外，执行查询和其他选择操作（例如，对包含行溢出数据的大型记录进行排序或合并）将延长处理时间，因为这些记录将同步处理，而不是 异步处理。因此，当要设计的表中包含多个 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型的列时，请考虑可能溢出的行的百分比，以及可能查询这些溢出数据的频率。如果可能需要经常查询行溢出数据中的许多行，请考虑对表格进行规范化处理，以使某些列移动到另一个表中。然后可以在异步 JOIN 操作中执行查询。
  • 对于 varchar、nvarchar、varbinary、sql_variant 或 CLR 用户定义类型的列，单个列的长度仍然必须在 8000 字节的限制之内。只有它们的合并长度可以超过表的 8060 字节的行限制。
  • 其他数据类型列的和（包括 char 和 nchar 数据）必须在 8,060 字节的行限制之内。大型对象数据也不受 8,060 字节行限制的制约。
  • 聚集索引的索引键不能包含在 ROW_OVERFLOW_DATA 分配单元中具有现有数据的 varchar 列。如果对 varchar 列创建了聚集索引，并且在 IN_ROW_DATA 分配单元中存在现有数据，则对该列执行的将数据推送到行外的后续插入或更新操作将会失败。有关分配单元的详细信息，请参阅表组织和索引组织。
  • 可以包括包含行溢出数据的列，作为非聚集索引的键列或非键列。
  • 对 于使用稀疏列的表，记录大小限制为 8,018 字节。转换后的数据加上现有记录数据超过 8,018 字节时，会返回 MSSQLSERVER ERROR 576。列在稀疏和非稀疏类型之间转换时，数据库引擎会保留当前记录数据的副本。这样，记录所需的存储会临时加倍。
  • 若要获得有关可能包含行溢出数据的表或索引的信息，请使用 sys.dm_db_index_physical_stats 动态管理函数。
Posted on

SQL Server调优系列基础篇

  1. SQL Server调优系列基础篇
  2. SQL Server调优系列基础篇（常用运算符总结——三种物理连接方式剖析）
  3. SQL Server调优系列基础篇（联合运算符总结）
  4. SQL Server调优系列基础篇（并行运算总结一）
  5. SQL Server调优系列基础篇（并行运算总结篇二）
  6. SQL Server调优系列基础篇（索引运算总结）
  7. SQL Server调优系列基础篇（子查询运算总结）
  8. SQL Server调优系列进阶篇（查询优化器的运行方式）
  9. SQL Server调优系列进阶篇（查询语句运行几个指标值监测）
  10. SQL Server调优系列进阶篇（深入剖析统计信息）
  11. SQL Server调优系列进阶篇（如何索引调优）
  12. SQL Server调优系列进阶篇（如何维护数据库索引）

前言

关于SQL Server调优系列是一个庞大的内容体系，非一言两语能够分析清楚，本篇先就在SQL 调优中所最常用的查询计划进行解析，力图做好基础的掌握，夯实基本功！而后再谈谈整体的语句调优。

通过本篇了解如何阅读和理解查询计划、并且列举一系列最常用的查询执行运算符。

技术准备

基于SQL Server2008R2版本，利用微软的一个更简洁的案例库（Northwind）进行解析。

一、区别不同的运算符

在所有T-SQL语句在执行的时候，都会将语句分解为一些基本的结构单元，这些结构单元统称为：运算符。每一个运算符都实现一个单独的基本操作，比 如：表扫描、索引查找、索引扫描、过滤等。每个运算符可以循环迭代，也可以延续子运算符，这样就可以组成查询树，即：查询计划。

每个T-SQL语句都会通过多种运算符进行组合形成不同的查询计划，并且这些查询计划对于结果的筛选都是有效的，但在执行的时候，SQL Server的查询优化器会自动为我们找到一个最优的。

每一个运算符都会有源数据的传入和结果数据的输出，源数据的输入可以来源于其它的运算符或者直接从数据源表中读取，经过本身的运算进行结果的输出。所以每一个运算符是独立的。互不关心的。

如下例子

SELECT COUNT(*) FROM Orders

此语句会生成两个简单的运算符

Continue reading “SQL Server调优系列基础篇”

Posted on

常用的SQL优化技巧

常见的字段类型选择

•字符类型建议采用varchar/nvarchar数据类型
•金额货币建议采用money数据类型
•科学计数建议采用numeric数据类型
•自增长标识建议采用bigint数据类型   (数据量一大，用int类型就装不下，那以后改造就麻烦了)
•时间类型建议采用为datetime数据类型
•禁止使用text、ntext、image老的数据类型
•禁止使用xml数据类型、varchar(max)、nvarchar(max)

约束与索引

每张表必须有主键

•每张表必须有主键，用于强制实体完整性
•单表只能有一个主键（不允许为空及重复数据）
•尽量使用单字段主键

不允许使用外键

•外键增加了表结构变更及数据迁移的复杂性
•外键对插入，更新的性能有影响，需要检查主外键约束
•数据完整性由程序控制

索引设计准则

•应该对 WHERE 子句中经常使用的列创建索引
•应该对经常用于连接表的列创建索引
•应该对 ORDER BY 子句中经常使用的列创建索引
•不应该对小型的表（仅使用几个页的表）创建索引，这是因为完全表扫描操作可能比使用索引执行的查询快
•单表索引数不超过6个
•不要给选择性低的字段建单列索引
•充分利用唯一约束
•索引包含的字段不超过5个（包括include列）

不要给选择性低的字段创建单列索引

•SQL SERVER对索引字段的选择性有要求，如果选择性太低SQL SERVER会放弃使用•
•不适合创建索引的字段：性别、0/1、TRUE/FALSE
•适合创建索引的字段：ORDERID、UID等

充分利用唯一索引

唯一索引给SQL Server提供了确保某一列绝对没有重复值的信息，当查询分析器通过唯一索引查找到一条记录则会立刻退出，不会继续查找索引

•表索引数不超过6个, 表索引数不超过6个（这个规则只是携程DBA经过试验之后制定的。。。）
•索引加快了查询速度，但是却会影响写入性能
•一个表的索引应该结合这个表相关的所有SQL综合创建，尽量合并
•组合索引的原则是，过滤性越好的字段越靠前
•索引过多不仅会增加编译时间，也会影响数据库选择最佳执行计划

SQL查询

•禁止在数据库做复杂运算
•禁止使用SELECT *
•禁止在索引列上使用函数或计算
•禁止使用游标
•禁止使用触发器
•禁止在查询里指定索引
•变量/参数/关联字段类型必须与字段类型一致
•参数化查询
•限制JOIN个数
•限制SQL语句长度及IN子句个数
•尽量避免大事务操作
•关闭影响的行计数信息返回
•除非必要SELECT语句都必须加上NOLOCK
•使用UNION ALL替换UNION
•查询大量数据使用分页或TOP
•递归查询层级限制
•NOT EXISTS替代NOT IN
•临时表与表变量
•使用本地变量选择中庸执行计划
•尽量避免使用OR运算符
•增加事务异常处理机制
•输出列使用二段式命名格式

禁止在数据库做复杂运算

•XML解析
•字符串相似性比较
•字符串搜索（Charindex）
•复杂运算在程序端完成

禁止使用SELECT *

•减少内存消耗和网络带宽
•给查询优化器有机会从索引读取所需要的列
•表结构变化时容易引起查询出错

•禁止在索引列上使用函数或计算

禁止在索引列上使用函数或计算

假设在字段Col1上建有一个索引，则下列场景将可以使用到索引：

[Col1]=3.14

[Col1]>100

[Col1] BETWEEN 0 AND 99

[Col1] LIKE ‘abc%’

[Col1] IN(2,3,5,7)

像上面这样的查询，将无法用到O_OrderProcess表上的PrintTime索引

LIKE查询的索引问题

•[Col1] like “abc%”  –index seek  这个就用到了索引查询
•[Col1] like “%abc%”  –index scan  而这个就并未用到索引查询
•[Col1] like “%abc”  –index scan 这个也并未用到索引查询
我想从上而三个例子中，大家应该明白，最好不要在LIKE条件前面用模糊匹配，否则就用不到索引查询。

禁止使用游标

•关系数据库适合集合操作，也就是对由WHERE子句和选择列确定的结果集作集合操作，游标是提供的一个非集合操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能。
•游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非常大的。
（再加上游标真心比较复杂，挺不好用的，尽量少用吧）

禁止使用触发器

•触发器对应用不透明（应用层面都不知道会什么时候触发触发器，发生也也不知道，感觉莫名其秒）

禁止在查询里指定索引

With(index=XXX)（ 在查询里我们指定索引一般都用With(index=XXX) ）

•随着数据的变化查询语句指定的索引性能可能并不最佳
•索引对应用应是透明的，如指定的索引被删除将会导致查询报错，不利于排障
•新建的索引无法被应用立即使用，必须通过发布代码才能生效

变量/参数/关联字段类型必须与字段类型一致（这是我之前不太关注的）

避免类型转换额外消耗的CPU，引起的大表scan尤为严重

看了上面这两个图，我想我不用解释说明，大家都应该已经清楚了吧。

如果数据库字段类型为VARCHAR，在应用里面最好类型指定为AnsiString并明确指定其长度

如果数据库字段类型为CHAR，在应用里面最好类型指定为AnsiStringFixedLength并明确指定其长度

如果数据库字段类型为NVARCHAR，在应用里面最好类型指定为String并明确指定其长度

参数化查询

以下方式可以对查询SQL进行参数化：

•sp_executesql
•Prepared Queries
•Stored procedures
用图来说明一下，哈哈。

限制JOIN个数

•单个SQL语句的表JOIN个数不能超过5个
•过多的JOIN个数会导致查询分析器走错执行计划
•过多JOIN在编译执行计划时消耗很大

限制IN子句中条件个数

•在 IN 子句中包括数量非常多的值（数以千计）可能会消耗资源并返回错误 8623 或 8632，要求IN子句中条件个数限制在100个以内

尽量避免大事务操作

•只在数据需要更新时开始事务，减少资源锁持有时间
•增加事务异常捕获预处理机制
•禁止使用数据库上的分布式事务
用图来说明一下
也就是说我们不应该在1000行数据都更新完成之后再commit tran,你想想你在更新这一千行数据的时候是不是独占资源导致其它事务无法处理。

关闭影响的行计数信息返回

在SQL语句中显示设置Set Nocount On，取消影响的行计数信息返回，减少网络流量

除非必要，尽量让所有的select语句都必须加上NOLOCK

指定允许脏读。不发布共享锁来阻止其他事务修改当前事务读取的数据，其他事务设  置的排他锁不会阻碍当前事务读取锁定数据。允许脏读可能产生较多的并发操作，但其代价是读取以后会被其他事务回滚的数据修改。这可能会使您的事务出错，向 用户显示从未提交过的数据，或者导致用户两次看到记录（或根本看不到记录）

使用UNION ALL替换UNION

UNION会对SQL结果集去重排序，增加CPU、内存等消耗

查询大量数据使用分页或TOP

合理限制记录返回数，避免IO、网络带宽出现瓶颈

递归查询层次限制

使用 MAXRECURSION 来防止不合理的递归 CTE 进入无限循环

临时表与表变量

使用本地变量选择中庸执行计划

在存储过程或查询中，访问了一张数据分布很不平均的表格，这样往往会让存储过程或查询使用了次优甚至于较差的执行计划上，造成High CPU及大量IO Read等问题，使用本地变量防止走错执行计划。

采用本地变量的方式，SQL在编译的时候是不知道这个本地变量的值，这时候SQL会根据表格里数据的一般分布，“猜测”一个返回值。不管用户在调用存储过程或语句的时候代入的变量值是多少，生成的计划都是一样的。这样的计划一般会比较中庸一些，不一定是最优的计划，但一般也不会是最差的计划

如果查询中本地变量使用了不等式运算符，查询分析器使用了一个简单的 30% 的算式来预估
Estimated Rows =(Total Rows * 30)/100
如果查询中本地变量使用了等式运算符，则查询分析器使用：精确度 * 表记录总数来预估
Estimated Rows = Density * Total Rows

尽量避免使用OR运算符

对于OR运算符，通常会使用全表扫描，考虑分解成多个查询用UNION/UNION ALL来实现，这里要确认查询能走到索引并返回较少的结果集

增加事务异常处理机制

应用程序做好意外处理，及时做Rollback。
设置连接属性 “set xact_abort on”

输出列使用二段式命名格式

二段式命名格式：表名.字段名

有JOIN关系的TSQL，字段必须指明字段是属于哪个表的，否则未来表结构变更后，有可能发生Ambiguous column name的程序兼容错误

架构设计

•读写分离
•schema解耦
•数据生命周期

读写分离

•设计之初就考虑读写分离，哪怕读写同一个库，有利于快速扩容
•按照读特征把读分为实时读和可延迟读分别对应到写库和读库
•读写分离应该考虑在读不可用情况下自动切换到写端

Schema解耦

禁止跨库JOIN

数据生命周期

根据数据的使用频繁度，对大表定期分库归档

主库/归档库物理分离

日志类型的表应分区或分表

对于大的表格要进行分区，分区操作将表和索引分在多个分区，通过分区切换能够快速实现新旧分区替换，加快数据清理速度，大幅减少IO资源消耗

频繁写入的表，需要分区或分表

自增长与Latch Lock

闩锁是sql Server自己内部申请和控制，用户没有办法来干预，用来保证内存里面数据结构的一致性，锁级别是页级锁

Posted on

T-SQL中的格式转换，CAST和CONVERT

在 SQL Server 2012 中将表达式由一种数据类型转换为另一种数据类型。

语法

CAST ( expression AS data_type [ ( length ) ] )
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

参数

expression

任何有效的表达式。

data_type

目标数据类型。这包括 xml、bigint 和 sql_variant。不能使用别名数据类型。

length

指定目标数据类型长度的可选整数。默认值为 30。

style

指定 CONVERT 函数如何转换 expression 的整数表达式。如果样式为 NULL，则返回 NULL。该范围是由 data_type 确定的。有关详细信息，请参阅“备注”部分。

返回类型

返回转换为 data_type 的 expression。

备注

Date 和 Time 样式

如果 expression 为 date 或 time 数据类型，则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。SQL Server 使用科威特算法来支持阿拉伯样式的日期格式。

不带世纪数位 (yy) (1) 带世纪数位 (yyyy) 标准 输入/输出 (3)
– 0 或 100（1、2） 默认 mon dd yyyy hh:miAM（或 PM）
1 101 美国 mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 英国/法国 dd/mm/yyyy
4 104 德语 dd.mm.yy
5 105 意大利语 dd-mm-yy
6 106 (1) – dd mon yy
7 107 (1) – Mon dd, yy
8 108 – hh:mi:ss
– 9 或 109（1、2） 默认格式 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM（或 PM）
10 110 美国 mm-dd-yy
11 111 日本 yy/mm/dd
12 112 ISO yymmdd

yyyymmdd
– 13 或 113（1、2） 欧洲默认格式 + 毫秒 dd mon yyyy hh:mi:ss:mmm(24h)
14 114 – hh:mi:ss:mmm(24h)
– 20 或 120 (2) ODBC 规范 yyyy-mm-dd hh:mi:ss(24h)
– 21 或 121 (2) ODBC 规范（带毫秒） yyyy-mm-dd hh:mi:ss.mmm(24h)
– 126 (4) ISO8601 yyyy-mm-ddThh:mi:ss.mmm（无空格）
– 127(6, 7) 带时区 Z 的 ISO8601。 yyyy-mm-ddThh:mi:ss.mmmZ

（无空格）
– 130 (1,2) 回历 (5) dd mon yyyy hh:mi:ss:mmmAM
– 131 (2) 回历 (5) dd/mm/yy hh:mi:ss:mmmAM

1 这些样式值返回不确定的结果。包括所有 (yy)（不带世纪数位）样式和一部分 (yyyy)（带世纪数位）样式。

2 默认值（style0 或 100、9 或 109、13 或 113、20 或 120 以及 21 或 121）始终返回世纪数位 (yyyy)。

3 转换为 datetime 时输入；转换为字符数据时输出。

4 为用于 XML 而设计。对于从 datetime 或 smalldatetime 到字符数据的转换，其输出格式如上一个表所述。

5 回历是有多种变体的日历系统。SQL Server 使用科威特算法。

重要提示
默认情况下，SQL Server 基于截止年份 2049 年来解释两位数的年份。换言之，就是将两位数的年份 49 解释为 2049，将两位数的年份 50 解释为 1950。许多客户端应用程序（如基于自动化对象的应用程序）都使用截止年份 2030 年。SQL Server 提供了 two digit year cutoff 配置选项以更改 SQL Server 使用的截止年份，从而进行一致的日期处理。建议您指定四位数年份。

6 仅支持从字符数据转换为 datetime 或 smalldatetime。仅表示日期或时间成分的字符数据转换为 datetime 或 smalldatetime 数据类型时，未指定的时间成分设置为 00:00:00.000，未指定的日期成分设置为 1900-01-01。

7 使用可选的时间区域指示符 (Z) 更便于将具有时区信息的 XML datetime 值映射到没有时区的 SQL Server datetime 值。Z 是时区 UTC-0 的指示符。其他时区则以 + 或 – 方向的 HH:MM 偏移量来指示。例如：2006-12-12T23:45:12-08:00。

从 smalldatetime 转换为字符数据时，包含秒或毫秒的样式将在这些位置上显示零。使用相应的 char 或 varchar 数据类型长度从 datetime 或 smalldatetime 值转换时，可截断不需要的日期部分。

从样式包含时间的字符数据转换为 datetimeoffset 时，将在结果末尾追加时区偏移量。

float 和 real 样式

如果 expression 为 float 或 real，则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。

值 输出
0（默认值） 最多包含 6 位。根据需要使用科学记数法。
1 始终为 8 位值。始终使用科学记数法。
2 始终为 16 位值。始终使用科学记数法。
126, 128, 129 为了保持向后兼容而包括在内，在以后的版本中可能不推荐使用。

money 和 smallmoney 样式

如果 expression 为 money 或 smallmoney，则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。

值 输出
0（默认值） 小数点左侧每三位数字之间不以逗号分隔，小数点右侧取两位数，例如 4235.98。
1 小数点左侧每三位数字之间以逗号分隔，小数点右侧取两位数，例如 3,510.92。
2 小数点左侧每三位数字之间不以逗号分隔，小数点右侧取四位数，例如 4235.9819。
126 转换为 char(n) 或 varchar(n) 时，等同于样式 2

xml 样式

如果 expression 为 xml，则 style 可以为下表中显示的值之一。其他值作为 0 进行处理。

值 输出
0（默认值） 使用默认的分析行为，即放弃无用的空格，且不允许使用内部 DTD 子集。

注意
转换为 xml 数据类型时，SQL Server 的无用空格处理方式不同于 XML 1.0。有关详细信息，请参阅创建 XML 数据的实例。
1 保留无用空格。此样式设置将默认的 xml:space 处理方式设置为与指定了 xml:space=”preserve” 的行为相同。
2 启用有限的内部 DTD 子集处理。

如果启用，则服务器可使用内部 DTD 子集提供的以下信息来执行非验证分析操作。

  • 应用属性的默认值。
  • 解析并扩展内部实体引用。
  • 检查 DTD 内容模型以实现语法的正确性。

分析器将忽略外部 DTD 子集。此外，不评估 XML 声明来查看 standalone 属性是设置为 yes 还是 no，而是将 XML 实例当成一个独立文档进行分析。
3 保留无用空格，并启用有限的内部 DTD 子集处理。

二进制样式

如果 expression 为 binary(n)、varbinary(n)、char(n) 或 varchar(n)，则 style 可以为下表中显示的值之一。表中没有列出的样式值将返回错误。

值 输出
0（默认值） 将 ASCII 字符转换为二进制字节，或者将二进制字节转换为 ASCII 字符。每个字符或字节按照 1:1 进行转换。

如果 data_type 为二进制类型，则会在结果左侧添加字符 0x。
1, 2 如果 data_type 为二进制类型，则表达式必须为字符表达式。 expression 必须由数量为偶数的十六进制数字（0、1、2、3、4、5、6、7、8、9、A、B、C、D、E、F、a、b、c、d、e、f）组成。如果将 style 设置为 1，字符 0x 必须为表达式中的前两个字符。如果表达式中包含的字符数为奇数或者包含任何无效的字符，则会引发错误。

如果转换后的表达式长度大于 data_type 长度，则会在右侧截断结果。

如果固定长度 data_types 大于转换后的结果，则会在结果右侧添加零。

如果 data_type 为字符类型，则表达式必须为二进制表达式。每个二进制字符均转换为两个十六进制字符。如果转换后的表达式长度大于 data_type 长度，则会在右侧截断结果。

如果 data_type 为固定大小的字符类型，并且转换后的结果长度小于其 data_type 长度，则会在转换后的表达式右侧添加空格，以使十六进制数字的个数保持为偶数。

对于 style 1，将在转换后的结果左侧添加字符 0x。
Posted on

在.Net中检查Sql注入

SQL 注入是一种攻击方式，在这种攻击方式中，恶意代码被插入到字符串中，然后将该字符串传递到 SQL Server 的实例以进行分析和执行。任何构成 SQL 语句的过程都应进行注入漏洞检查，因为 SQL Server 将执行其接收到的所有语法有效的查询。一个有经验的、坚定的攻击者甚至可以操作参数化数据。

SQL 注入的主要形式包括直接将代码插入到与 SQL 命令串联在一起并使其得以执行的用户输入变量。一种间接的攻击会将恶意代码注入要在表中存储或作为元数据存储的字符串。在存储的字符串随后串连到一个动态 SQL 命令中时，将执行该恶意代码。

注入过程的工作方式是提前终止文本字符串，然后追加一个新的命令。由于插入的命令可能在执行前追加其他字符串，因此攻击者将用注释标记“–”来终止注入的字符串。执行时，此后的文本将被忽略。

Continue reading “在.Net中检查Sql注入”

Posted on

SQL注入

所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令，比如先前的很多影视网站泄露VIP会员密码大多就是通过WEB表单递交查询字符暴出的，这类表单特别容易受到SQL注入式攻击．

目录

  • sql注入
  • 注入大致方法
  • 常用sql注入语句

sql注入

什么时候最易受到sql注入攻击

当应用程序使用输入内容来构造动态sql语句以访问数据库时，会发生sql注入攻击。如果代码使用存储过程，而这些存储过程作为包含未筛选的用户输入的字符串来传递，也会发生sql注入。sql注入可能导致攻击者使用应用程序登陆在数据库中执行命令。相关的SQL注入可以通过测试工具pangolin进行。如果应用程序使用特权过高的帐户连接到数据库，这种问题会变得很严重。在某些表单中，用户输入的内容直接用来构造动态sql命令，或者作为存储过程的输入参数，这些表单特别容易受到sql注入的攻击。而许多网站程序在编写时，没有对用户输入的合法性进行判断或者程序中本身的变量处理不当，使应用程序存在安全隐患。这样，用户就可以提交一段数据库查询的代码，根据程序返回的结果，获得一些敏感的信息或者控制整个服务器，于是sql注入就发生了。

Continue reading “SQL注入”