数据库调优

参考

sql官方文档

常规优化方式

• 查询条件优化：
  • 先锁定最小的条件
  • 左边只能是字段，不能进行计算、函数之类　
  • 表连接时小表连大表
  • 模糊查询：
    • 用CharIndex、Patindex 代替 Like
    • 用 like %%的模糊查询时不走索引，对不频繁更新的数据可以使用列存储索引，测试字段长度20内千万级的数据，建立列存储索引后用like %%也是2秒内能查询出来。如果是varchar(8000)超长的，就要10秒甚至更长时间，建议用全文索引。
• 增加索引，表连接时尤其要注意
• 全文索引
• 分区表：一般把日期字段按照年分区

视图优化

视图里面其实就是查询sql语句，参考上面说到的方式优化基本能搞定，如果是复杂的查询语句，就要增加查询条件，不增加查询条件会把数据全部查询出来

• 函数代替视图参数
  • 视图中是没有参数的，但是可以通过在查询条件中使用函数，具体参考：SQLserver视图中定义参数
• 动态锁定最近日期：因为好多业务场景都是使用最近的数据的，例如锁定最近60天的：日期字段>=GETDATE()-60，锁定字段必须有索引

影响性能因素

• 数据库结构设计
• T-SQL语句
• 数据量大
• 事务和隔离级别
• 硬件资源
• IO阻塞
  • 批量删除表数据：大量删除时会记录到日志中，也会造成IO阻塞

优化和注意事项

• 了解业务
• 优先考虑第三范式设计，参考设计范式
• 表关联尽可能少
• 坚持最小原则
• 在适当的地方使用约束
• 用户数据和日志文件隔离存放

T-SQL语句优化

使用字段名，尽量不适用*

• 第一是避免浪费，不使用的字段不要显示出来
• 第二是从*变成字段名的编译过程也需要消耗性能，尤其是高并发的场景

条件

• 从左边开始，先写最小条件锁定最少数据
• 索引
  • 尽量使用索引字段
  • 索引字段放到左边
  • 不能计算也不要使用函数，否则索引失效
• in 与 not in 注意缺项与优化代替
  • 使用not in时要加 ISNULL 避免子有 NULL 结果时匹配不了，如：not in(select ISNULL(列明,''))
  • in和not in的替代品exists：因为in与not in查询时每行数据都会与子查询的全部结果行数据循环一次，造成性能非常低，使用exists和not exists代替
  • 使用表连接代替in和not in

表连接与查询中的子查询

• 表关联JOIN：效率要高于子查询，因为子查询走的是笛卡尔积，但是连接条件要建立在索引字段字段上
• 查询中的子查询：性能在代码测试后不补充？？？

以数据量小表关联数据量大表

SQL语句尽量简单

执行计划

参考

sql官方执行计划文档

执行计划图标和运算符

MSSQLSERVER执行计划详解 - 张龙豪 - 博客园

SQL Server执行计划的理解 - 馨馨妙 - 博客园

点击开启【包括实际的执行计划】

执行计划关键字和图标理解

•  表扫描：
  Parameter Table Scan 运算符扫描在当前查询中用作参数的表。 该运算符一般用于存储过程内的 INSERT 查询。 Parameter Table Scan 既是一个逻辑运算符，也是一个物理运算符。
  就是扫描查询列整个表全部数据，最耗时性能最低的。
• 嵌套循环：
  Nested Loops 运算符执行内部联接、左外部联接、左半部联接和左反半部联接逻辑运算。 嵌套循环联接通常使用索引，针对外部表的每一行在内部表中执行搜索。 查询处理器根据预计的开销来决定是否对外部输入进行排序，以改进内部输入索引上的搜索定位。 将基于所执行的逻辑操作返回所有满足 Argument 列中的（可选）谓词的行。 如果 OPTIMIZED 特性设置为“True”，则表示使用了优化的嵌套循环（或批处理排序） 。 Nested Loops 是一个物理运算符。 有关详细信息，请参阅了解嵌套循环联接。
• RID查询 ：
  RID Lookup 是使用提供的行标识符 (RID) 在堆上进行的书签查找。 Argument 列包含用于查找表中的行的书签标签和从中查找行的表的名称。 RID Lookup 通常带有 NESTED LOOP JOIN。 RID Lookup 是一个物理运算符。 有关书签查找的详细信息，请参阅 MSDN SQL Server 博客中的Bookmark Lookup（书签查找）。
• 哈希匹配：
  Hash Match 运算符通过计算其生成输入中每行的哈希值生成哈希表。 HASH:() 谓词以及一个用于创建哈希值的列的列表会出现在 Argument 列中。 然后，该谓词为每个探测行（如果适用）计算哈希值（使用相同的哈希函数）并在哈希表内查找匹配项。 如果存在残留谓词（由 Argument 列中的 RESIDUAL:() 标识），则还须满足此残留谓词，只有这样行才能被视为是匹配项。 行为取决于所执行的逻辑操作：
  - 对于联接，使用第一个（顶端）输入生成哈希表，使用第二个（底端）输入探测哈希表。 按联接类型规定的模式输出匹配项（或不匹配项）。 如果多个联接使用相同的联接列，这些操作将分组为一个哈希组。
  - 对于非重复或聚合运算符，使用输入生成哈希表（删除重复项并计算聚合表达式）。 生成哈希表时，扫描该表并输出所有项。
  - 对于 union 运算符，使用第一个输入生成哈希表（删除重复项）。 使用第二个输入（它必须没有重复项）探测哈希表，返回所有没有匹配项的行，然后扫描该哈希表并返回所有项。Hash Match 是一个物理运算符。 有关详细信息，请参阅理解哈希联接。
  没有索引时一般都是使用哈希连接
• 合并连接：
  Merge Join 运算符执行内部联接、左外部联接、左半部联接、左反半部联接、右外部联接、右半部联接、右反半部联接和联合逻辑运算。
  在 Argument 列中，如果操作执行一对多联接，则 Merge Join 运算符将包含 MERGE:() 谓词；如果操作执行多对多联接，则该运算符将包含 MANY-TO-MANY MERGE:() 谓词。 Argument 列还包含一个用于执行操作的列的列表，该列表以逗号分隔。 Merge Join 运算符要求在各自的列上对两个输入进行排序，这可以通过在查询计划中插入显式排序操作来实现。 如果不需要显式排序（例如，如果数据库内有合适的 B 树索引或可以对多个操作（如合并联接和对汇总分组）使用排序顺序），则合并联接尤其有效。 Merge Join 是一个物理运算符。 有关详细信息，请参阅理解合并联接。 

执行计划查看顺序

从右到左，从下到上

查询存储

参考

官方查询存储文档

概念

SQL Server 查询存储功能让你可以探查查询计划选项和性能。 它可帮助你快速找到查询计划更改所造成的性能差异，从而简化了性能疑难解答。 查询存储将自动捕获查询、计划和运行时统计信息的历史记录，并保留它们以供查阅。 它按时间窗口将数据分割开来，使你可以查看数据库使用模式并了解服务器上何时发生了查询计划更改。 可以使用 ALTER DATABASE SET 选项来配置查询存储。

索引

堆（没有聚集索引的表）

堆是不含聚集索引的表。 可在存储为堆的表上创建一个或多个非聚集索引。 数据存储于堆中并且无需指定顺序。 通常，数据最初以行插入表时的顺序存储，但 数据库引擎 可能会在堆中四处移动数据，以便高效地存储行；因此，无法预测数据顺序。 若要确保从堆返回的行的顺序，您必须使用 ORDER BY 子句。 若要指定用于存储行的永久逻辑顺序，请对表创建聚集索引，以便表不是堆。

聚集索引和非聚集索引

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

行索引优化

• 索引列不能为空
• sql语句时索引字段不要计算，计算会取消索引
• with指定索引（复杂的表多索引时可以强制指定索引）
• 尽量使用合并索引，减少使用单列索引
• 首列索引最重要，把使用频率最高的索引放到首列索引中

列存储索引

列存储索引是存储和查询大型数据仓库事实数据表的标准。 此索引使用基于列的数据存储和查询处理，与面向行的传统存储相比，最多可实现 10 倍的数据仓库查询性能提升。 此外，与处理非压缩数据相比，处理压缩数据可将性能提升 10 倍。 

自 SQL Server 2016 (13.x) SP1 起，列存储索引支持操作分析，即能够对事务工作负载运行高性能实时分析。 

事务

参考

事务

SQL事务 - laizhixue的博客_CSDN博客

事务四个特性

• 原子性：事务必须是一个自动工作的单元，要么全部执行，要么全部不执行。例如银行转账、电商中的出库与订单支付
• 一致性：事务结束的时候，所有的内部数据都是正确的。
• 隔离性：并发多个事务时，各个事务不干涉内部数据，处理的都是另外一个事务处理之前或之后的数据。
• 持久性：事务提交之后，数据是永久性的，不可再回滚。

3类常见的事务：

• 自动提交事务：是SQL Server默认的一种事务模式，每条Sql语句都被看成一个事务进行处理，你应该没有见过，一条Update 修改2个字段的语句，只修该了1个字段而另外一个字段没有修改。
• 显式事务：T-sql标明，由Begin Transaction开启事务开始，由Commit Transaction 提交事务、Rollback Transaction 回滚事务结束。
• 隐式事务：使用Set IMPLICIT_TRANSACTIONS ON 将将隐式事务模式打开，不用Begin Transaction开启事务，当一个事务结束，这个模式会自动启用下一个事务，只用Commit Transaction 提交事务、Rollback Transaction 回滚事务即可。

测试方式

• 建立一个会话，启动事务，然后更新一条数据，不提交事务，然后再建立一个会话去查询这条记录，发现会一直卡主，要提交事务或者关闭事务的会话才能查询

锁 lock

参考

SQL Server中的锁类型及用法 - 霖薇暮 - 博客园

数据库大并发操作要考虑死锁和锁的性能问题

为了解决事务造成的几个错误

• 更新丢失：多个用户同时对一个数据资源进行更新，必定会产生被覆盖的数据，造成数据读写异常。
• 不可重复读：如果一个用户在一个事务中多次读取一条数据，而另外一个用户则同时更新啦这条数据，造成第一个用户多次读取数据不一致。
• 脏读：第一个事务读取第二个事务正在更新的数据表，如果第二个事务还没有更新完成，那么第一个事务读取的数据将是一半为更新过的，一半还没更新过的数据，这样的数据毫无意义。
• 幻读：第一个事务读取一个结果集后，第二个事务，对这个结果集经行增删操作，然而第一个事务中再次对这个结果集进行查询时，数据发现丢失或新增。

锁的种类

• 共享锁(Shared lock)

• 更新锁(Update lock)

  为解决死锁，引入更新锁

• 排他锁（独占锁，Exclusive Lock）

• 意向锁(Intent Locks)
• 计划锁(Schema Locks)

建议

• 为了不影响性能，一般情况尽量不使用锁，例如为了避免同时多用户更新同一条数据时，可以增加一个字段记录每条数据最后更新的时间的时间戳，用户更新提交前判断当前用户在取出数据更新这段时候内是否有更新过
• 减低锁数量，减少锁的持有时间
• 缩短事务运行时间（范围、持续时间）
• 更新（修改）操作尽可能靠近事务的结尾
• 避免多次更新相同的数据，不要再同一个事务中，多次更新数据
• 监控锁升级
• 同一个事务中，不要混用DML和DDL

内存中的联机事务处理（OLTP）

参考

OLTP_百度百科

内存中的OLTP

内存优化表

OLTP

On-Line Transaction Processing联机事务处理过程(OLTP)，也称为面向交易的处理过程，其基本特征是前台接收的用户数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果，是对用户操作快速响应的方式之一。

内存中的OLTP是什么

内存中 OLTP 是 SQL Server 和 SQL 数据库 中用于优化事务处理、数据引入、数据加载和瞬态数据方案性能的核心技术。

对于合适的工作负荷，In-Memory OLTP 可提供显著的性能增益。 客户 BWIN 充分利用内存中 OLTP，只通过一台运行 SQL Server 2016 (13.x) 的计算机，便成功实现每秒 120 万次的请求。 另一个客户 Quorum 也充分利用 SQL 数据库中的内存中 OLTP，成功将其工作负荷翻倍，同时其资源使用率减少 70%。 虽然在某些情况下，客户可实现高达 30 倍的性能增益，但是增益的多少取决于工作负荷。

分区表和分表

注意事项

• 一般使用分区表就能解决数据量过大的性能问题
• 分区表不会影响系统设计，分表需要修改系统设计
• 先分区表，最后超过数量再分表