SQL SERVER 索引

一、索引（Index）

在 SQL Server 中，索引（Index） 是一种数据库对象，它基于表或视图的一列或多列创建，其核心目的是极大地提高数据检索操作的速度，就像书籍的目录可以帮助你快速找到特定内容一样。

索引（无论是聚集还是非聚集）底层是 B+树。所有键值都按指定顺序（升序或降序）排列在叶子节点中。

索引健支持的数据类型

1、索引健的数据类型：

索引健的数据类型是由其定义所基于的“索引列”（Index Key Columns）的数据类型直接决定的。
举例：
ID列 是int 类型，那么 ID列的索引类型就是int类型
Name列 是nvarchar(n)类型，那么Name列的索引就是nvarchar(n)类型

2、索引健支持的数据类型

数据类型	说明
整数类型	bit,tinyint ,smallint,int,bigint ✅ 强烈推荐：尤其是 int和 bigint，常用于自增主键，性能极佳
精确数值类型	decimal(p,s), numeric(p,s) ✅ 支持: 注意 p（精度）不要过大
日期和时间类型	date, time, datetime2(n), datetimeoffset(n), smalldatetime, datetime ✅ 推荐
固定长度字符类型	char(n), nchar(n) ✅ 支持，适合存储代码、缩写等短字符串
可变长度字符类型	varchar(n), nvarchar(n)✅ 常用：适合名称、地址等。关键限制：索引键的最大总大小为 900 字节
唯一标识符	uniqueidentifier ✅ 支持，用于全局唯一 id (guid)。虽然性能不如 int，但功能上完全支持。

3、索引不推荐的数据类型

1、大对象类型： text, ​ntext, ​image
2、大值类型： varchar(max), ​nvarchar(max), ​varbinary(max)
3、极少不同值的列： bit（男/女）、状态标志列

二、索引分类

1、聚集索引

表中数据行的物理存储顺序与索引的顺序是一致的，这种索引我们将它成为聚集索引。表数据本身就被组织在聚集索引的 B+ 树的叶节点上。

2、非聚集索引

表中数据行的物理存储顺序与索引顺序不一致的时候，像这种快速查找数据的索引我们将它成为非聚集索引。

特性	聚集索引	非聚集索引
数量限制	每表仅有一个	每表可有多个
数据存储	叶子节点直接存储完整的行数据	叶子节点存储索引列值 + 主键值
物理顺序	数据按索引顺序物理排序	索引逻辑连续，但对应的数据物理上无序
查询效率	主键查询极快，​无需回表	通过索引找到主键后，常需回表查询完整数据
范围查询	极高效，可顺序读取连续数据块	效率较低，需多次随机I/O回表
键值选择	通常使用主键	基于非主键的查询列

对于主键查询或覆盖索引的查询，数据库只需遍历一次聚集索引的B+树，在叶子节点就能拿到全部数据，过程非常直接。相比之下，非聚集索引的叶子节点只存有主键值，查到所需数据的主键后，还需要拿着这些主键回到聚集索引里再查一次，这个过程称为“回表”，多了一次B+树的遍历，自然更慢

三、索引创建和使用方式

1、聚集索引

-- 语法
CREATE CLUSTERED INDEX index_name ON table_name (column_name [ASC | DESC]);
-- 案例
-- 对Users表的userid字段创建名称为IX_Users_UserID的聚集索引
CREATE CLUSTERED INDEX IX_Users_UserID on users (userid asc)

2、非聚集索引

-- 对Users表的Name字段创建名字为 IX_Users_Name的非聚集索引
-- 可以省略 NONCLUSTERED 关键字，默认情况下CREATE INDEX 创建的是非聚集索引。
CREATE NONCLUSTERED INDEX  IX_Users_Name ON Users (Name) 

3、联合索引（复合索引）

-- 针对Users表的 Age+City字段创建一个联合索引
CREATE INDEX IX_Users_age_city ON user (age, city);

这个索引适用于如下查询：

SELECT * FROM users WHERE age = 25 AND city = '永州';
SELECT * FROM users WHERE age = 25; -- 只用到了 age，也能走索引（最左前缀原则）
-- 注意：下面这句不能有效使用该索引（除非有 INCLUDE 或优化器特殊处理）
SELECT * FROM users WHERE city = '永州'; -- 跳过了 age，无法使用联合索引

对于创建的多列联合索引（如 (A, B, C)），只有当查询条件从索引的最左边列开始，并连续使用前面的列时，才能有效利用该索引

✅ 可用索引：WHERE A = 1、WHERE A = 1 AND B = 2、WHERE A = 1 AND B > 2 AND C = 3
❌ 不可用索引：WHERE B = 2、WHERE C = 3、WHERE A = 1 AND C = 3（跳过 B）
❌ 不可用索引：WHERE A = 1 OR B=2 ⚠️ 特别注意：联合索引对 OR 无效

3、唯一索引

-- 创建唯一索引可以是聚集或者非聚集
 
-- 创建唯一聚集索引：
-- 对Users表的UserId字段创建名称为IX_Users_UserId的唯一聚集索引
CREATE UNIQUE CLUSTERED INDEX  IX_Users_UserId ON Users (UserId) 
 
-- 创建唯一非聚集索引：
-- 对Users表的Name字段创建名称为IX_Users_Name的唯一非聚集索引
CREATE UNIQUE NONCLUSTERED INDEX  IX_Users_Name ON Users (Name) 
 
-- 创建唯一非聚集索引
-- 可以省略 NONCLUSTERED 关键字，默认情况下CREATE INDEX 创建的是非聚集索引
CREATE UNIQUE INDEX IX_Users_Name ON Users (Name) 

案例1：带条件的唯一索引

--对Users表，在DeleteTime字段为空的数据中，为Name字段添加唯一索引
CREATE UNIQUE INDEX IX_Users_Name ON Users (Name) 
WHERE DeleteTime is null

举例：有以下Users表

ID	Name	DeleteTime
1	张三	2022-02-10
2	李四	NULL

此时想插入Name为李四的数据，会报错：无法插入。
但可以插入Name为张三的数据，因为张三这一行的DeleteTime列数据不为Null

案例2：唯一联合索引

--对Users表，为Name+Email字段添加唯一联合索引
CREATE UNIQUE INDEX  Index_Users_Name  ON Users (Name,Email)  

ID	Name	Email	DeleteTime
1	张三	abc@qq.com	NULL

此时想插入一条Name为 张三，Email为 abc@qq.com的数据是不行的
但可以插入一条Name为 张三，Email为除abc@qq.com以外的任意值的数据
也可以插入一条Email为abc@qq.com，Name为除张三以外的任意值的数据

其实就是将Name和Email看成一个字段，两个字段共同组成一个唯一

案例3：带条件的唯一联合索引

-- 针对Users表，在DeleteTime字段为空的数据中，为Name+Email字段添加唯一联合索引
-- 同时排除为DeleteTime不为空的数据添加唯一联合索引
-- 意思就是只为DeleteTime为空的数据添加唯一联合索引
CREATE UNIQUE INDEX  Index_Users_Name  ON Users (Name,Email)  
WHERE DeleteTime  is null

4、覆盖索引

-- 在 Users 表的 Status 列上创建一个非聚集索引，名称为：DEX IX_Users_Status，
-- 并且将 UserName、Email、CreatedDate 这三个列作为包含列（Included Columns） 
-- 存储在索引的叶子节点中，以便查询时无需回表（Key Lookup），
-- 直接从索引中获取所有所需数据。
CREATE NONCLUSTERED INDEX IX_Users_Status
ON Users (Status) 
INCLUDE (UserName, Email, CreatedDate);
 
-- ON Users (Status) 作用：指定索引建立在 Users 表的 Status 列上
 
-- INCLUDE (UserName, Email, CreatedDate) 作用如下：
-- 将 UserName、Email、CreatedDate 这三个列作为非键列（Included Columns）
-- 添加到索引的叶子节点中。
-- 这些列不参与索引的排序和查找逻辑，但会物理存储在索引页中
-- 目的是让查询可以直接从索引中获取这些列的值，避免回表查询（Key Lookup）

覆盖索引的优势： 假设你经常执行以下查询：

SELECT UserName, Email, CreatedDate
FROM Users
WHERE Status = 'Active';

如果没有 INCLUDE，执行过程是：

• 使用索引找到 Status = 'Active' 的行（通过 Status 索引）。
• 然后回表去主数据页查找 UserName、Email、CreatedDate 的值。
• 回表操作是I/O 密集型，性能较差。

而使用 INCLUDE 后，执行过程是：

• 索引叶子节点已经包含了 UserName、Email、CreatedDate
• 查询可以直接从索引中获取所有数据，无需回表。
• 这种索引称为 覆盖索引

四、SQL语句优化

1、Like优化

LIKE 查询是否使用索引取决于通配符位置

✅ --可用索引：LIKE '张%'  以固定字符串开头，支持前缀匹配
 select * from Users where Name like '张%'

✅ --可用索引：LIKE '张_'   以固定字符串开头，支持前缀匹配
 select * from Users where Name like '张_'

🟨 --视情况而定：LIKE '_晶'（单字符模糊）在某些情况下仍可使用索引，但效率较低
 select * from Users where Name like '_晶'

❌ --不可用索引：LIKE '%晶' 或 LIKE '%张%'   以 % 开头，无法确定起始位置
 select * from Users where Name like LIKE '%晶'

2、OR 优化

如果where条件中有or 则要求or的所有字段都必须有索引，否则不能用到索引

✅-- 如果Name和Age字段都创建了单列索引 以下查询才能用道索引
❌-- 如果只有Name或者只有Age字段创建了索引，则不能用到索引
SELECT * FROM Users WHERE Name = 'A' OR Age= 16

用IN替代OR

-- 不推荐（难读且不易优化）
WHERE Name = 'A' OR Name = 'B' OR Name = 'C'

✅ -- 推荐（清晰且易用索引）
WHERE Name IN ('A', 'B', 'C')

3、IN 优化

IN 后面是表达式或函数 → 索引失效！

❌ -- 因为对字段用了函数 YEAR()，无法使用 CreateDate 上的索引
SELECT * FROM T_UserInfo WHERE YEAR(CreateDate) IN (2020, 2021, 2022)

✅ --应改为范围查询：
WHERE CreateDate >= '2020-01-01' AND CreateDate < '2023-01-01'

4、NOT IN 优化

NOT IN适用于小数据集和已知列表：
当你要排除的值是固定的、数量较少的列表时（如 NOT IN ('A', 'B', 'C')），not in 非常高效且方便

NULL 值陷阱 这是 NOT IN 最致命的缺点
如果 not in 后面的子查询结果集中包含任何 null 值，那么整个 not in 表达式将返回 unknown，导致主查询不返回任何结果。

原因: 在 sql 的三值逻辑中，value not in (1, 2, null) 等价于 value != 1 and value != 2 and value != null。而任何值与 null 比较（!= null, = null）的结果都是 unknown，而不是 true或 false。因此，整个表达式的最终结果是 unknown，不会被 where 子句选中。

-- 假设 SubTable 包含 (1, 2, NULL)
SELECT * FROM MainTable WHERE ID NOT IN (SELECT FK_ID FROM SubTable);
-- 即使 MainTable 中有 ID=3 的行，此查询也返回空集！

✅-- 解决方法: 必须在子查询中显式过滤掉 NULL 值
SELECT * FROM MainTable WHERE ID 
NOT IN (SELECT FK_ID FROM SubTable WHERE FK_ID IS NOT NULL)

对于主查询表 (MainTable)是大型表的情况，NOT IN 可能不如其他替代方案（如 NOT EXISTS)
用 NOT EXISTS 替代 NOT IN (推荐)
优点:
1、性能通常更好，语义清晰，不受 null 值影响。not exists 关注的是“是否存在匹配行”，null 不会影响其布尔判断。
2、虽然 not in 可以利用索引，但其效率通常不如 exists 或 in。not exists 通常可以在找到第一个匹配项后就停止搜索，而 not in 有时需要检查所有值

✅-- 将 not in 改成 not exists
SELECT * 
FROM MainTable m
WHERE NOT EXISTS (
    SELECT 1 
    FROM SubTable s 
    WHERE s.FK_ID = m.ID -- 注意这里是关联条件
);

5、IS NULL 值优化

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描（聚集索引扫描或表扫描）

❌ -- 不能用到索引。
select id from t where age is null

✅ --优化方案：此例可以在age上设置默认值0，确保表中age列没有null值，然后这样查询
select id from t where age = 0

6、<> 或 != 操作符 优化

尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

当你使用!=或<>时，你是在寻找“不等于某个值”的所有行。这个集合通常是表中绝大部分的数据。对于数据库来说，扫描整个索引或直接扫描整个表可能比逐个查找并排除那个特定值更高效

7、使用局部变量查询 优化

在 WHERE 子句中使用局部变量 时可能导致 全表扫描 的原因
问题核心：局部变量值在编译时未知

DECLARE @Age INT = 25

SELECT * FROM Users WHERE Age = @Age

在这个查询中：

• @Age 是一个局部变量。
• 它的值在 编译时 是未知的（变量赋值发生在运行时之前，但优化器在编译时看不到具体值）。
• 所以优化器无法像对待常量那样，利用统计信息来评估使用索引是否更优。
• 结果往往是：选择全表扫描，因为这是一种“安全”的默认策略

✅ -- 优化方案1：强制查询使用索引
DECLARE @Age INT = 25

SELECT * FROM Users with(index(索引名)) WHERE Age = @Age

✅ -- 优化方案2：告诉优化器在生成执行计划时，不要尝试嗅探任何具体参数的值，而是将所有参数视为“未知”
DECLARE @Age INT = 25;
SELECT * FROM Users WHERE Age = @Age
OPTION (OPTIMIZE FOR UNKNOWN); --诉优化器在生成执行计划时,将所有参数视为“未知”

✅ -- 优化方案3：强制每次执行都重新编译查询。
DECLARE @Age INT = 25;
SELECT * FROM Users WHERE Age = @Age
OPTION (RECOMPILE);  --表示：强制每次执行都重新编译查询。

--缺点：CPU 开销高：每次执行都要经历解析、编译、优化的过程。如果这个查询执行非常频繁，CPU 可能成为瓶颈

✅ -- 优化方案4：定义存储过程，使用参数化查询  (推荐做法)
CREATE PROCEDURE GetUsersByAge @Age INT
AS
BEGIN
    SELECT * FROM Users WHERE Age = @Age
END

-- 这个存储过程中的 @Age 是 参数，不是局部变量。
-- SQL Server 在编译存储过程时，会**嗅探**第一次执行时的参数值（即：参数嗅探），
-- 并据此生成执行计划。所以 通常能正确使用索引。

8、 WHERE 子句优化

1、SQL server 应尽量避免在 where 子句中对字段进行表达式，函数，操作，这将导致引擎放弃使用索引而进行全表扫描

-- ❌ 反模式：对列使用函数，导致索引失效
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2023 AND MONTH(OrderDate) = 10

-- ❌ 反模式：对列进行数学运算
SELECT * FROM Products WHERE Price * 1.1 > 100

-- ❌ 反模式：对列使用字符串函数
SELECT * FROM Customers WHERE UPPER(LastName) = 'SMITH'

-- ❌ 反模式：查询条件是对 num 进行了运算：num / 2   导致全表扫描
SELECT id FROM t WHERE num/2 = 100

正确方式

-- ✅ 使用范围查询，让引擎能利用索引 避免在列上使用函数
SELECT * FROM Orders 
WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01';

-- ✅ 将表达式移到等号右边  将计算移到常量一侧
SELECT * FROM Products WHERE Price > 100 / 1.1; -- 即 Price > ~90.91

-- ✅ 在应用层处理大小写，或使用合适的排序规则
-- 如果LastName的排序规则是区分大小写的(CS), 则应用层传入正确的大小写
SELECT * FROM Customers WHERE LastName = 'Smith';

-- ✅ 变换不等式。相当于select id from t where num=200 查询将变为索引查找
SELECT id FROM t WHERE num = 100*2  

9、WHERE 与 HAVING 的优化

在 SQL Server 中，应优先使用 WHERE 子句而非 HAVING 来过滤数据，以提升查询性能。

这是因为 WHERE 在数据分组和聚合之前执行，能尽早减少参与后续操作（如排序、分组、聚合）的数据量；
而 HAVING 用于对 GROUP BY 分组后的结果进行过滤，必须等待聚合计算完成之后才能执行，因此开销更大

对于不涉及聚合函数的过滤条件，应始终放在 WHERE 中。
只有当过滤条件基于聚合结果（如 SUM()、COUNT()、AVG() 等）时，才必须使用 HAVING。

此外，在多表连接查询中，连接条件应放在 ON 子句中，因为它在连接过程中立即生效，有助于减少中间结果集的大小；
而将连接条件误置于 WHERE 中可能影响执行计划甚至导致逻辑错误（特别是在外连接中）。

综上所述，合理分配过滤条件的位置——非聚合条件用 WHERE，聚合条件用 HAVING，连接条件用 ON——不仅能保证语义正确，还能显著提高查询效率。

举例： 查询2020 年以后入职的员工中，平均工资超过 7000 元的部门在这里插入代码片

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(50),
    DepartmentID INT,
    Salary DECIMAL(10,2),
    HireDate DATE
);

SELECT 
    DepartmentID, 
    AVG(Salary) AS AvgSalary
FROM Employees
WHERE HireDate >= '2020-01-01'   -- ✅ 非聚合条件：先进行条件筛选
GROUP BY DepartmentID            -- ✅ 然后再对经过条件筛选出来的数据分组
HAVING AVG(Salary) > 7000;       -- ✅ 聚合结果过滤：最后用 HAVING 筛选出平均工资 > 7000 的部门

原文地址：https://blog.csdn.net/Fanbin168/article/details/154353273

免责声明：本站文章内容转载自网络资源，如侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！