SQL Server索引 #

索引作用：
├── 加速查询
├── 强制唯一性
├── 加速排序
└── 加速连接

索引代价：
├── 占用存储空间
├── 降低INSERT/UPDATE/DELETE速度
└── 需要维护

SQL Server索引类型
├── 聚集索引（Clustered）
│   └── 表数据按索引顺序存储
├── 非聚集索引（Nonclustered）
│   └── 独立存储，包含指向数据的指针
├── 列存储索引（Columnstore）
│   └── 列式存储，适合分析查询
├── 全文索引（Full-text）
│   └── 全文搜索
├── 空间索引（Spatial）
│   └── 地理空间数据
├── XML索引
│   └── XML数据
└── 筛选索引（Filtered）
    └── 部分数据索引

聚集索引特点：
├── 每个表只能有一个聚集索引
├── 表数据按聚集索引键值排序存储
├── 聚集索引的叶节点就是数据页
├── 主键默认创建聚集索引
└── 适合范围查询和排序

-- 创建表时定义主键（自动创建聚集索引）
CREATE TABLE users (
    id INT PRIMARY KEY,
    name NVARCHAR(50),
    email VARCHAR(100)
);

-- 显式创建聚集索引
CREATE CLUSTERED INDEX ix_users_id ON users(id);

-- 创建聚集索引并指定选项
CREATE CLUSTERED INDEX ix_users_created ON users(created_at)
WITH (
    PAD_INDEX = ON,
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = OFF,
    DROP_EXISTING = ON,
    ONLINE = ON
);

聚集索引选择原则：
├── 选择唯一或高选择性的列
├── 选择经常用于范围查询的列
├── 选择经常用于ORDER BY的列
├── 选择经常用于GROUP BY的列
├── 选择窄列（占用空间小）
├── 选择静态列（很少更新）
└── 避免选择频繁更新的列

推荐：
├── 自增ID列
├── 日期时间列（如created_at）
└── 复合键（如(user_id, order_date)）

非聚集索引特点：
├── 每个表可以有多个非聚集索引
├── 独立于表数据存储
├── 包含索引键和行定位符
├── 叶节点指向数据行
└── 适合精确查找

-- 创建简单非聚集索引
CREATE INDEX ix_users_email ON users(email);

-- 创建复合索引
CREATE INDEX ix_users_name_age ON users(name, age);

-- 创建唯一索引
CREATE UNIQUE INDEX ix_users_email_unique ON users(email);

-- 创建降序索引
CREATE INDEX ix_users_salary_desc ON users(salary DESC);

-- 创建包含列索引
CREATE INDEX ix_users_department ON users(department_id)
INCLUDE (name, salary);

-- 包含列索引（覆盖索引）
CREATE INDEX ix_orders_user_date ON orders(user_id, order_date)
INCLUDE (amount, status);

-- 查询可以利用覆盖索引
SELECT user_id, order_date, amount, status
FROM orders
WHERE user_id = 1;

-- 筛选索引：只索引部分数据
CREATE INDEX ix_users_active ON users(department_id)
WHERE status = 1;

-- 筛选唯一索引
CREATE UNIQUE INDEX ix_users_email_active ON users(email)
WHERE status = 1;

列存储索引特点：
├── 列式存储
├── 高压缩率
├── 适合分析查询（OLAP）
├── 批量插入性能好
└── 单行更新性能差

-- 创建聚集列存储索引
CREATE CLUSTERED COLUMNSTORE INDEX ix_sales_columnstore
ON sales;

-- 创建非聚集列存储索引
CREATE NONCLUSTERED COLUMNSTORE INDEX ix_users_columnstore
ON users (age, salary, department_id);

-- 列存储索引适合的查询
SELECT 
    department_id,
    AVG(salary),
    COUNT(*)
FROM users
GROUP BY department_id;

-- 创建全文目录
CREATE FULLTEXT CATALOG ft_catalog AS DEFAULT;

-- 创建全文索引
CREATE FULLTEXT INDEX ON products(name, description)
KEY INDEX pk_products
ON ft_catalog
WITH STOPLIST = SYSTEM;

-- 使用全文搜索
SELECT * FROM products
WHERE CONTAINS(name, 'laptop');

SELECT * FROM products
WHERE FREETEXT(description, 'high performance laptop');

-- CONTAINS：精确匹配
SELECT * FROM products
WHERE CONTAINS(name, '"laptop"');

-- 使用AND/OR
SELECT * FROM products
WHERE CONTAINS(name, 'laptop AND computer');

-- 使用通配符
SELECT * FROM products
WHERE CONTAINS(name, '"lap*"');

-- FREETEXT：自然语言搜索
SELECT * FROM products
WHERE FREETEXT(name, 'good laptop');

-- 重建单个索引
ALTER INDEX ix_users_email ON users REBUILD;

-- 重建所有索引
ALTER INDEX ALL ON users REBUILD;

-- 重建并设置选项
ALTER INDEX ix_users_email ON users
REBUILD WITH (
    FILLFACTOR = 80,
    ONLINE = ON,
    SORT_IN_TEMPDB = ON
);

-- 重组索引（轻量级维护）
ALTER INDEX ix_users_email ON users REORGANIZE;

-- 重组所有索引
ALTER INDEX ALL ON users REORGANIZE;

-- 查看索引碎片
SELECT 
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10;

-- 碎片处理建议：
-- < 5%：不需要处理
-- 5% - 30%：重组
-- > 30%：重建

-- 查看表索引
EXEC sp_helpindex 'users';

-- 查看索引详细信息
SELECT 
    t.name AS table_name,
    i.name AS index_name,
    i.type_desc,
    i.is_unique,
    i.is_primary_key
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE t.name = 'users';

-- 删除索引
DROP INDEX ix_users_email ON users;

-- 安全删除
IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_users_email' AND object_id = OBJECT_ID('users'))
    DROP INDEX ix_users_email ON users;

索引设计原则：
├── 为WHERE子句列创建索引
├── 为JOIN列创建索引
├── 为ORDER BY列创建索引
├── 使用覆盖索引减少IO
├── 复合索引注意列顺序
├── 避免过度索引
├── 定期维护索引碎片
└── 更新统计信息

-- 查看缺失索引建议
SELECT 
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
ORDER BY migs.avg_user_impact DESC;