MSSQL的条件索引和include索引
Tags： include索引MSSQLSQL Server优化条件索引索引
简介
SQL Server中创建索引时，有2个比较特殊的关键字： where 、include。
在 SQL Server 中，条件索引（带where条件）和包含（include）索引都是用于优化查询性能的索引策略。
条件索引（Filtered Indexes）
条件索引是一种在索引中仅包含满足特定条件的行的索引。它们允许你定义一个过滤条件，只有满足该条件的行才会包含在索引中。这种索引非常适用于那些查询只需要处理表中的一小部分数据的情况，可以减少索引的大小，提高查询性能。
where顾名思义，就和sql语句中的where是一个意思，起到过滤作用。
加上where子句，创建的索引就是条件索引，索引中只包含满足条件的数据，这样会使得索引的体积更小，不管是索引扫描，还是查找，需要访问的页数也就更少。
举例：
1 | create index idx_A on A(name) where biz_date >='2017-01-01' |
这个索引中，只包含biz_date大于等于2017-01-01的数据。
示例： 假设有一个包含了大量历史数据的订单表（Order），但只有最近一年的数据被频繁查询。可以使用条件索引来只包含最近一年的数据：
1 2 3 | CREATE NONCLUSTERED INDEX idx_Order_RecentYear ON Order (OrderDate) WHERE OrderDate >= DATEADD(YEAR, -1, GETDATE()); |
这样，只有满足条件 OrderDate >= DATEADD(YEAR, -1, GETDATE()) 的行才会被包含在索引中，从而减小索引的大小，提高查询效率。
包含索引（Include Indexes）
包含索引是一种将非键列（非索引列）包含在索引中的技术。通常，在一个索引中，只有键列会被包含在索引的数据结构中，而非键列则需要通过回表操作（查找主索引或堆）来获取。但是，通过使用包含索引，可以将非键列的数据直接包含在索引中，避免了回表操作，从而提高查询性能。
include是包含的意思，加上include子句，可以指定多个列，也就是说在这个索引中会包含include中指定的所有列。
举例：
1 | create index idx_A on A(biz_date) include(name,id); |
这个索引中，include包含了name，id 两个字段。
示例： 假设有一个包含了大量商品信息的商品表（Product），其中除了商品ID（ProductID）作为主键外，还有商品名称（ProductName）、商品价格（Price）等列。为了提高按商品名称进行查询的性能，可以创建一个包含索引：
1 2 3 | CREATE NONCLUSTERED INDEX idx_Product_Name ON Product (ProductName) INCLUDE (Price); |
这样，除了将商品名称列作为索引键列外，还将商品价格列作为包含列，可以直接从索引中获取商品价格信息，而无需进行回表操作。
复合索引和include索引的区别
那么这种include索引和直接指定 biz_date，name，id的复合索引，有什么区别呢？
区别在于 include中的列是不排序的，一般可以把select中的列，放到include中，好处是索引的开销相对小了。
如果是在where中的列，就不适合放到include子句中，因为这样不利于快速查找过滤数据。
包含索引（Include Indexes）和普通的复合索引在索引设计和使用方式上存在一些区别。
- 包含索引（Include Indexes）：
- 包含索引允许将非键列（非索引列）直接包含在索引的数据结构中。
- 非键列的值存储在叶子节点中，这样可以避免回表操作（访问主索引或堆）来获取非键列的数据。
- 包含索引可以显著减少查询的IO操作，提高查询性能，尤其是对于那些需要返回非键列数据的查询。
- 适用于查询中需要访问的列较多，或者需要返回的列较大的情况。
- 复合索引（Composite Indexes）：
- 复合索引是由多个列组成的索引，通过在多个列上创建单个索引来提高查询性能。
- 复合索引的键列（索引列）决定了索引的排序顺序，同时也是用于查询条件的筛选。
- 复合索引可以覆盖多个查询条件，可以减少索引的数量，节省存储空间。
- 适用于涉及多个列的查询条件，并且这些列的组合较为频繁出现。
主要区别：
- 包含索引关注的是将非键列直接包含在索引中，避免回表操作，减少IO操作。
- 复合索引关注的是在多个列上创建索引，提高查询的效率，减少索引的数量。
- 包含索引适用于需要返回非键列数据的查询，而复合索引适用于多个列的查询条件。
需要注意的是，包含索引和复合索引可以结合使用，以进一步提高查询性能。例如，在复合索引中包含一些非键列，以减少回表操作和IO开销。具体使用哪种索引策略，取决于数据库表的结构、查询需求和性能优化目标。
总的来说 复合索引 和 Include索引 各有利弊吧，前者会让索引页的行数据更大，导致索引页更多，也就会占用更多的存储空间，更多的逻辑读，索引维护开销也更大，而后者只会将 Include 列 保存在叶子节点，不参与索引计算，相对来说占用的索引页空间更小。
在查询方面，复合索引能达到的索引覆盖场景远大于单列索引，而且在过滤，排序场景下也能发挥奇效，所以还是根据你的读写比例做一个取舍吧。
示例
1. 这些索引解决了什么问题
说区别之前，一定要知道它们大概解决了什么问题？这里我就从 索引覆盖 角度来展开吧，为了方便讲述，先上一个测试 sql：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | IF(OBJECT_ID('t') IS NOT NULL) DROP TABLE t; CREATE TABLE t(a INT IDENTITY, b CHAR(6), c CHAR(10) DEFAULT 'aaaaaaaaaa') SET NOCOUNT ON DECLARE @num INT SET @num =10000 WHILE (@num <90000) BEGIN INSERT INTO t(b) VALUES ('b'+CAST(@num AS CHAR(5))) SET @num=@num+1 END CREATE CLUSTERED INDEX idx_a ON t(a) CREATE INDEX idx_b ON t(b) SELECT * FROM t; |
代码非常简单，在 t 表中创建三个列，插入 8w 条数据，然后创建两个索引，接下来做一个查询获取 b,c 列。
1 2 3 4 5 | SET STATISTICS IO ON SET STATISTICS TIME ON SELECT b,c FROM t WHERE b IN ('b10000','b20000','b30000','b40000','b50000','b70000','b80000','b90000') SET STATISTICS IO OFF SET STATISTICS TIME OFF |
输出如下：
1 2 3 4 5 6 7 8 9 | 表“t”。扫描计数 8，逻辑读取次数 30，物理读取次数 0，页面服务器读取次数 0，预读读取次数 0，页面服务器预读读取次数 0，LOb 逻辑读取次数 0，LOB 逻辑读取次数 0，LOB 页面服务器读取次数 0，LOB 预读读取次数 0，LOB 页面服务器预读读取次数 0。 SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 134 毫秒。 SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。 Completion time: 2023-01-06T08:47:45.2364473+08:00 |
