2021-08-06

索引失效问题排查

1. 页

1.1 页的介绍

当你创建一个数据库时，你指定了你的数据库被定位的文件，SQL SERVER 把各个文件看做是长长的字节字符串，它在逻辑上而不是物理上将文件分割成8K大小的块，这些8K大小的块成为页。因此文件的第一个8K大小的字节块就是页#0，下一个8K是页#1，如此等等，页是最小的IO单元，每次IO，SQL SERVER 只是读写一个页，如果多个连续的页需要读或者写，SQL SERVER 选择在一个单独的IO中来处理它们。页不仅仅是IO的最小单位，它还是所有关系的最小单位，也就是说，如果一个页包含了表A的行，那么它将只包含表A的行，反之如果它包含了非聚集索引B的一个条目，它将只会包含非聚集索引B的条目，除了数据之外，每个页都包含了一些头信息及偏移指针，用以帮助SQL SERVER来定位到页中的各个行数据或者条目

1.2 页的结构

数据页由3个部分组成。页头（标头），数据区（数据行和可用空间）及行偏移数组

页头（标头）: 每页的开头是 96 字节的标头，用于存储有关页的系统信息。此信息包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元 ID
数据区: 储存数据的区域，包含数据行和可用空间，紧接着标头按顺序放置
行偏移数组：页的末尾是行偏移表，对于页中的每一行，每个行偏移表都包含一个条目。每个行偏移量条目记录对应行的第一个字节与页首的距离。因此，行偏移量的功能有助于 SQL Server 快速在页面上定位行。行偏移表中的条目的顺序与页中行的顺序相反

1.3 页的种类

Data page 堆表和聚集索引的叶子节点数据
Index page 聚集索引的非叶子节点和非聚集索引的所有索引记录
……

2. 区

SQL SERVER在页上做了另一个逻辑分组，它将8个连续的页分成一个逻辑单元，称为区。
通常，区和页一样，是所有关系的单位，如果区中的一个页从属于表A或者索引B，那么区中的所有8个页都会从属于一个相同的对象。对于很小的表或者索引来说，情况会有所不同，它们不能充满整个区，在这种情况下，超过一个表或者索引会被定位到一个相同的区中，但对于大部分对象来说，区仍是所有关系的单位。
因此，SQL SERVER并不认为表扫描是读取表的所有行，它认为表扫描是读取表的所有页或者区，它知道它将很可能并发的处理8K，64K的IO请求来读取整个表，这将使得表扫描变得不至于像每行读取那样吓人。
读取页和区不仅意味着做一个表扫描比我们期望的具有更少的工作，它还意味着，为了从非聚集索引受益，一个查询应该比我们期望的更具有选择性

理解举例：假设有一张10W条记录的学生表,该表有主键索引id，非聚簇索引age

假设每页能够存100条数据，则10W条数据被分成了1000页

如果我们现在需要查询年龄大于18岁的学生，已知年龄大于18岁的学生总共有2000个，占总学生的2%，可以这样理解平均每50个学生中就有一个大于18岁的，我们每次查询就需要从50行中选择一行，那么平均每个页变会包含2个请求行，则平均每个页都包含了至少1个请求行，换句话说，几乎每个页都需要被读取来满足这个查询，那么用表扫描来读取它们是最好的选择，一次读取一个区，平均每次都装在16（2*8）个请求行进入内存

id name age

1 张三 18

2 李四 14

3 王五 13

…… …… ……

id	name	age
1	张三	18
2	李四	14
3	王五	13
……	……	……

3. 索引

3.1 索引介绍

索引是使 SQL SERVER 在最小的时间内查询或者修改所要请求的数据的一种数据库对象，它目的在于使用最小的系统资源达到最大的性能优化。除此之外，好的索引设计可以保证数据库达到最大的并发数，这样一个用户的查询就几乎不会影响另一个用户的查询请求。最后，索引提供了保证数据一致性的有效方式
因为索引本身也是有开销的，它们占用一定的存储空间并必须与数据库表数据保持一致，换句话说有多少数据行，就有多少索引行

3.2 聚集索引

聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。
只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆（堆是一个没有聚集索引的表。表中的数据不按任何字段排序，与之对应的就是聚簇索引表）的无序结构中。
数据结构

3.3 非聚集索引

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

3.4 联合索引

两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。SQL SERVER 从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。
数据结构

3.5 带有包含列的索引

那些在非聚集索引中，但不是索引键的一部分的列称为包含列。这些列不属于索引的一部分，因此不会影响索引中条目的顺序。同样我们会看到，包含列比索引列导致更少的开销。

4. 索引碎片

4.1 前置说明

每一个页包含了一个指向上一个和下一个页的指针。页的逻辑顺序和物理顺序或许是不一致的。指向下一个页的指针后续会指向位于同一个区的页，或者指向几百个页之前的一个页（也可能是之后）。
页的物理顺序和逻辑顺序越一致，扫描整个索引所需要的IO数就越少。当两个顺序一致的时候，SQL SERVER每次IO可读取一整个区或者更多。同样的，页的物理顺序和逻辑顺序越相关，那么使用的预读数便越多（预读：在使用之前，先将页存入内存）。不管是扫描整个索引，还是只扫描索引的一部分，这都是确定无疑的。
索引碎片的分类：
- 一个页内空闲容量的百分比称为页的内部碎片；
- 逻辑顺序和物理顺序的偏差称为外部碎片；

4.2 内部碎片简述

覆盖率 = 数据字节数 / 一页字节数

内部碎片率 = 1 - 覆盖率

内部碎片通常是以覆盖率的形式表现出来，并且其体现的是字节数上的覆盖率而不是条目上的覆盖率；
一个100%充满的是没有内部碎片的，但通常来说一个页是否被充满还与条目的大小相关，当一个覆盖率是96%的索引页而剩余的4%的页空间不够添加一个新的条目时，此时这个页是被完全充满的；

4.3 外部碎片简述

外部碎片率 = 索引叶子节点中的无序页数 / 叶子节点总页数

无序页：当前页物理上分配的紧邻的页不是偏移指针所指向的页，那么当前页便称之为无序页；

4.4 碎片产生原因

对于非聚簇索引来说无序的insert是外部碎片产生的主要因素，insert和delete是内部碎片产生的主要因素

4.5 碎片对性能的影响

在官方文档的说明中，索引碎片率超过30%就会极大的影响性能，必须考虑重建索引

内部碎片的影响：
- 内部碎片会增加IO操作。当执行的查询扫描部分或全部的表/索引时，如果那个表/索引上面有内部碎片，它会增加额外的页读取；
- 内部碎片降低缓存效率。当索引有碎片时，在缓存里就会占用更多的空间。这会降低缓存命中率，依次增加物理IO，同样增加逻辑读；
- 增加数据库文件。需要更多的空间来存储额外页，并降低备份和还原性能；
外部碎片的影响：
- 读取单条记录时，外部碎片不会影响性能，因为是直接到页里拿记录；
- 在有序的索引扫描里，外部碎片会成为性能下降因素。性能下降的原因是磁盘的磁头需要物理磁盘上来回跳；

5. 生产环境复盘

业务说明：IPBOX板会定时（一小时一次）上报设备的耗电数据到数据采集服务器，由数据采集服务器将其保存到Redshift数据表中，然后通过定时任务每天将IPBOX前一天内的所有小时数据进行清洗和汇总，并保存到RDS中，由AP服务器进行查询并展示给用户
相关数据表说明
- Redshift 电力数据采集表（power_data_collection）
- RDS 电力数据归档表（power_month_archive_data）
  
  电力数据归档表中有一共有三个索引
  - id（主键索引）
  - term_mac (非聚簇索引)
  - month_dt (非聚簇索引)
  本番电力数据归档表数据统计：
  - 总量：1432710
  - 4月份：104697
电力数据归档表中每个IPBOX每个月只会生成一行记录，该行记录保存了IPBOX当月内每天的消费电力数据总和，一般来说该记录会在每个月初由归档定时器生成，并且写入了当月1号的电力数据，之后定时器每天会去更新当月内其他日期的电力数据到该行记录；还有一些当月内新增的设备，这些设备的归档记录会在自身上报电力数据后的第二天由定时器生成。
问题回顾

由于电力归档定时器是每天凌晨4点执行，从2022年4月3号之后，本番RDS在每天凌晨4点的时候会出现10分钟左右的CPU使用率飙高，之后会慢慢恢复到正常水平

监控本番数据库，可以看出导致cpu飙升的主要由下面这句sql导致的

1	update power_month_archive_data set day%d = ?, update_dt = ? where term_mac = ? and month_dt = ?

结合业务来说，4月份电力归档定时器每天会去执行10w多次更新sql，如果单条sql出现性能下滑，那么乘以10W就会出现很明显的效率下降，这点从定时执行时长中能体现出来，之前执行时长一直是1分钟左右，从4月3号之后，执行时长平均是10分钟

问题排查

对于sqlserver来说，更新操作本质是先找到原始记录，然后去比对更新，所以我们一开始猜想的是会不会出现索引失效的问题，于是我们在IT环境执行了和生产环境相同格式的sql，监控它的执行计划，结果发现只走了term_mac索引，没有走month_dt索引（由于重复率过高，导致sqlserver底层优化不走索引），但这显然不是导致sql执行效率断崖式下降的最主要的原因

问题猜想：翻阅资料，发现在索引效率下降的时候也会导致更新效率变低，结合之前索引碎片的原理，我们怀疑有可能是索引碎片率过高导致的，于是我们查询了本番power_month_archive_data表的索引碎片比例

内部碎片率查询sql

SELECT 
  IX.name AS 'Name', 
  PS.index_level AS 'Level', 
  PS.page_count AS 'Pages', 
  PS.avg_page_space_used_in_percent AS 'Page Fullness (%)' 
FROM 
  sys.dm_db_index_physical_stats(
    DB_ID(), 
    OBJECT_ID('power_month_archive_data'), 
    DEFAULT, 
    DEFAULT, 
    'DETAILED'
  ) PS 
  JOIN sys.indexes IX ON IX.OBJECT_ID = PS.OBJECT_ID 
  AND IX.index_id = PS.index_id

外部碎片率查询sql

SELECT 
  IX.name AS 'Name', 
  PS.index_level AS 'Level', 
  PS.page_count AS 'Pages', 
  PS.avg_fragmentation_in_percent AS 'External Fragmentation (%)', 
  PS.fragment_count AS 'Fragments', 
  PS.avg_fragment_size_in_pages AS 'Avg Fragment Size' 
FROM 
  sys.dm_db_index_physical_stats(
    DB_ID(), 
    OBJECT_ID('power_month_archive_data'), 
    DEFAULT, 
    DEFAULT, 
    'LIMITED'
  ) PS 
  JOIN sys.indexes IX ON IX.OBJECT_ID = PS.OBJECT_ID 
  AND IX.index_id = PS.index_id

结果发现本番环境term_mac索引的内部碎片率和外部碎片率达到了30% 和 99%

猜想证明：

为了证明索引碎片率高会影响更新的效率，我们在IT环境准备了两个power_month_archive_data表，这两个表的数据量完全相同，区别是一个表的索引碎片率和本番接近，一个表的索引碎片率较低，然后模拟本番环境去执行电力归档定时器，结果发现
- 索引碎片率低的表: 定时器更新时间为1分钟，RDS的CPU使用波动率较低
- 索引碎片率高的表: 定时器更新时间为10分钟，RDS的CPU使用波动率较高
到这一步基本上已经确定，是索引碎片率过高导致sql执行效率下降，但是也仅能说明索引碎片率变高是sql执行效率下降的导火索，并不能说明索引碎片率变高是导致sql执行效率下降的根本原因，也有可能是间接原因；因为在本番环境更新记录的时候，本身是走了term_mac索引，按照业务每个板子最多24条记录，所以索引碎片率影响也不可能这么大；但是我们发现在执行以下sql时，当索引碎片率不同的时候，其执行计划也有所差异，如下
1
update power_month_archive_data set day%d = ?, update_dt = ? where term_mac = ? and month_dt = ?

索引碎片率低的表，只走了term_mac索引

索引碎片率高的表，走了term_mac索引和month_dt索引

由于碎片率变高，导致更新数据时，既走了term_mac索引，又走了month_dt索引，而month_dt索引由于表中数据重复率过高，导致其扫描行数过多，影响了sql性能，于是我们将索引碎片率较高的表中的month_dt删掉，使其执行更新sql时永远不走时间索引，结果发现本来要15分钟才能执行完成的定时任务，1分钟以内就执行完毕，到此我们可以得出结论

结论：

由于本番环境power_month_archive_data表中的索引碎片率高，导致在根据term_mac和month_dt更新数据时同时走了两个索引，而month_dt索引接近于全表扫描，所以导致数据库性能消耗过大
本番问题总结
1. 为什么term_mac索引碎片率会骤增？
  
  因为定时器每个月初会新增一批与IPBOX等量的归档记录数，同时也会删除一批等量的归档记录数，大量无序的新增和删除操作导致索引页不断分裂，重组，造成term_mac的索引碎片率变高
2. 为什么主键索引和month_dt索引的碎片率处于正常范围？
  
  因为主键索引和month_dt索引都是有序增加的，新增的索引数据也会有序的添加到之前的索引页后面，理论上不会产生太多的索引碎片
3. 如何避免索引的碎片率增加？
  - 合理的创建索引，并且尽量按照索引排序进行新增数据

6. 索引的重组与重建

针对于索引的碎片率程度，推荐选择不同的处理方式

碎片率	采用方法
>30%	索引重建
>5% 且 <=30%	索引重组

1. 如果索引碎片率超过30％，推荐重建索引，重建索引分为以下三种方式

删除索引并重建（不推荐）
- 在删除索引期间，索引不可用
- 删除并重建索引会阻塞表上所有的其他请求，也可能被其他请求所阻塞
- 对于删除聚集索引，则会导致对应的非聚集索引重建两次（删除时重建，建立时再重建，因为非聚集索引中有指向聚集索引的指针）
- 用于定义主键或者唯一性约束的索引不能使用DROP INDEX语句删除。而且，唯一性约束和主键都可能被外键约束引用。在主键卸载之前，所有引用该主键的外键必须首先被删除。尽管可以这么做，但这是一种冒险而且费时的碎片整理方法
使用DROP_EXISTING语句重建索引
- 与删除重建方法类似，这种技术也导致并面临来自其他访问该表(或该表的索引)的查询的阻塞问题
- 与删除重建不同，具有DROP_EXISTING子句的CREATE INDEX语句可以用于重新创建使用约束的索引。如果该约束是一个主键或与外键相关的唯一性约束，在CREATE语句中不能包含UNIQUE
使用ALTER INDEX REBUILD重新生成索引（推荐）

通过动态重建索引而不需要卸载并重建索引，是优于前两种方法的，ALTER INDEX REBUILD完全是一个原子操作，如果它在结束前停止，所有到那时为止进行的碎片整理操作都将丢失，可以通过ONLINE关键字减少锁，但会造成重建时间加长，并且也会存在阻塞问题

2. 如果索引碎片率在10％到30％之间，推荐重组索引

使用ALTER INDEX REORGANIZE重新组织索引

这种方式不会重建索引，也不会生成新的页，仅仅是整理叶级数据，不涉及非叶级，当遇到加锁的页时跳过，所以不会造成阻塞。但同时，整理效果会差于前三种

3. 执行方法

由运维人员定期查询数据库每个表的碎片率，然后手动进行修复
编写修复索引脚本，使用定时任务定期执行

本文标题:索引失效问题排查

文章作者:王霄

发布时间:2021-08-06, 00:00:00

最后更新:2023-02-25, 09:53:27

原始链接:http://example.com/2021/08/06/%E9%A1%B9%E7%9B%AE%E6%80%BB%E7%BB%93/%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88%E9%97%AE%E9%A2%98%E6%8E%92%E6%9F%A5/

许可协议: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。