SQL Server查询优化 (转载非原创)

2022-08-09 11:05:48 浏览数 (1)

从上至下优化

看过一篇文章，印象深刻，里面将数据库查询优化分为四个大的方向

  • 使用钞能力——给DB服务器加物理配置，内存啊，CPU啊，硬盘啊，全上顶配
  • 替换存储系统——根据实际的业务情况选择不同的存储数据库，比如用ES做全文检索
  • 优化存储结构——比如采用分库分表，CQRS（命令查询职责分离），分布式缓存，历史数据归档，数据序列化等
  • 查询语句的优化——增加数据库索引命中率，定期清理数据库索引碎片等 从上到下成本依次递减，性价比依次升高，今天咱们聊聊Sql Server中基于索引的“查询语句的优化”

索引数据结构

谈到索引，咱们避免不了会想到索引的存储数据结构，目前大多数RDBS（关系型数据库系统）采用B 树来存储索引数据，如果还不是特别清楚啥是B 树的话，这里有传送门点击这里。 这里简单概括一下B 树的几个特点：

  • 每个节点可以存储多个元素
  • 所有的非叶子节点只存储关键字信息
  • 所有具体数据都存在叶子结点中
  • 所有的叶子结点中包含了全部元素的信息
  • 所有叶子节点之间都有一个链指针

索引分类

聚集索引
  • 聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。 索引定义中包含聚集索引列。 每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。
  • 只有当表包含聚集索引时，表中的数据行才按排序顺序存储。 如果表具有聚集索引，则该表称为聚集表。 如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。

可以简单理解为数据表中的数据按照既定的顺序进行存储，而这个用来排序的字段就是聚集索引。也可以理解为一个个由Key-Value组成的元素分布在一棵B 树上，Key对应的就是索引，Value对应的就是具体的数据行。

非聚集索引
  • 非聚集索引具有独立于数据行的结构。 非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针
  • 从非聚集索引中的索引行指向数据行的指针称为行定位器。 行定位器的结构取决于数据页是存储在堆中还是聚集表中。 对于堆，行定位器是指向行的指针。 对于聚集表，行定位器是聚集索引键。

大白话就是非聚集索引中存储的Key-Value，其中Key跟聚集索引一样是索引列，Value根据表是否存在聚集索引来进行区分，如果存在则Value为指向聚集索引键(也就是聚集索引的Key)的指针，不存在，则Value为指向表中数据行的指针。

查询优化

索引命中规则之最左匹配原则

众所周知，我们通常会在高频的where条件所用的字段上建立相关索引，那么我们建立索引以后我们的where查询条件是否命中索引呢？

代码语言：javascript复制
CREATE NONCLUSTERED INDEX IDEMO ON DEMOTABLE (A ASC,B ASC,C ASC,D ASC);

如上，在表DEMOTABLE中用A,B,C,D四个字段创建了非聚集索引，首先列A必须出现在查询条件中即（A组合），剩下的依次可以为，A,B组合，A,B,C组合，A,B,C,D组合，类似下面这样：

代码语言：javascript复制
SELECT E,F,G FROM DEMOTABLE WHERE A=1
SELECT E,F,G FROM DEMOTABLE WHERE A=1 AND B=2
SELECT E,F,G FROM DEMOTABLE WHERE A=1 AND B=2 AND C=3
SELECT E,F,G FROM DEMOTABLE WHERE A=1 AND B=2 AND C=3 AND D=4
//不会命中索引
SELECT E,F,G FROM DEMOTABLE WHERE B=2 AND C=3 AND D=4
//部分命中索引，只有条件A=1会命中
SELECT E,F,G FROM DEMOTABLE WHERE A=1 AND C=3 AND D=4
索引之覆盖索引

何为覆盖索引？

代码语言：javascript复制
CREATE NONCLUSTERED INDEX IDEMO ON DEMOTABLE (A ASC,B ASC,C ASC,D ASC) INCLUDE(E,F,G);

上面所建的非聚集索引以上一个创建语句后面多了一个INCLUDE语句，这样做可以减少索引命中以后查询相关列时的回表操作，何谓回表？之前我们讲过在非聚集索引的叶子节点上存放了对应聚集索引的指针，查询在命中非聚集索引的以后要查询非索引列时会根据这个指针去聚集索引上查找相关列，这个动作就是回表；如果我们的非聚集索引上INCLUDE了要查询的列，就可以减少相关查询的回表操作，从而提高查询性能。像下面这条语句就可以完美的规避回表查询。

代码语言：javascript复制
SELECT E,F,G FROM DEMOTABLE WHERE A=1 AND B=2 AND C=3 AND D=4
索引碎片

索引在建立过程中随着数据量的增加，索引碎片也会越来越多，从而导致即使在索引命中的情况下查询性能可能也不是特别理想，那这些碎片是怎么产生的呢？

  • 外部碎片

新的索引在插入的时候与旧的索引在物理存储位置上不连续，这就产生了外部碎片。

  • 内部碎片

新的索引在插入的时候导致因为索引所占空间大小的变化导致同一页上本可以存储3个索引，现在只能存下2个索引，存储2个索引以后剩下的空间就是内部碎片。

如何处理索引碎片呢？
  • 索引碎片已经很多的情况下 这种情况我们可以采用索引重新生成或索引重新组织，当然一般来说线上环境都有专门的DBA负责这些事宜，我们只需要知道有这些处理方式就好。
  • 在创建索引的时候 创建索引时我们可以根据实际的业务场景和索引字段所存信息的大小来适当的添加填充因子（0-100），也可以一定程度上减少索引碎片的产生。如果你还不清楚填充因子的话，可以看看这个。

转载来源: https://www.cnblogs.com/clue-yang/p/16526295.html

sqlserver

0 人点赞