索引优化策略

目录

• 索引基础概念
• EF Core 中的索引配置
• 索引类型详解
• 性能分析与优化
• .NET 8/9/10 新特性
• 最佳实践与陷阱

---

索引基础概念

什么是索引?

索引(Index) 是数据库中用于快速定位数据的数据结构,类似于书籍的目录。

没有索引: 
┌─────────────────────────────────┐
│ 扫描全表: 逐行检查 100 万条记录  │
│ SELECT * FROM Products          │
│ WHERE Name = 'Laptop'           │
│ → 需要检查 1,000,000 行         │
└─────────────────────────────────┘
     ↓ 耗时: 2000ms

有索引:
┌─────────────────────────────────┐
│ B-Tree 索引: 二分查找            │
│ SELECT * FROM Products          │
│ WHERE Name = 'Laptop'           │
│ → 只需检查 ~20 行(树深度)       │
└─────────────────────────────────┘
     ↓ 耗时: 5ms (快 400 倍!)

索引工作原理

B-Tree 索引结构

根节点
├── A-F
│   ├── Apple
│   ├── Banana
│   └── Cherry
├── G-M
│   ├── Grape
│   ├── Lemon
│   └── Mango
└── N-Z
    ├── Orange
    ├── Peach
    └── Strawberry

搜索 "Lemon":
1. 从根节点开始
2. 进入 G-M 分支
3. 找到 Lemon
4. 获取数据行指针
5. 读取完整数据

时间复杂度: O(log n)

索引的性能影响

操作	无索引	有索引	提升
WHERE 查询	全表扫描	索引查找	100-1000x
JOIN 连接	嵌套循环	索引查找	50-500x
ORDER BY	文件排序	有序索引	10-100x
GROUP BY	全表扫描+哈希	索引聚合	20-200x
INSERT	快速	需更新索引	-5% (开销)
UPDATE	快速	可能更新索引	-5% (开销)

权衡: 索引加速查询,但略微降低写入性能。

---

EF Core 中的索引配置

基本索引配置

方法 1: Fluent API(推荐)

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 单列索引
    modelBuilder.Entity<Product>()
        .HasIndex(p => p.Name);
    
    // 唯一索引
    modelBuilder.Entity<Product>()
        .HasIndex(p => p.Sku)
        .IsUnique();
    
    // 降序索引(.NET 8+)
    modelBuilder.Entity<Product>()
        .HasIndex(p => p.CreatedAt)
        .IsDescending();
    
    // 过滤索引(部分索引)
    modelBuilder.Entity<Product>()
        .HasIndex(p => p.Price)
        .HasFilter("[Price] > 0");
    
    // 指定索引名称
    modelBuilder.Entity<Product>()
        .HasIndex(p => p.CategoryId)
        .HasDatabaseName("IX_Products_CategoryId");
}

方法 2: 数据注解

public class Product
{
    public int Id { get; set; }
    
    [Index(nameof(Name))]
    [Index(nameof(Sku), IsUnique = true)]
    [Index(nameof(Price), nameof(CategoryId))]  // 复合索引
    public string Name { get; set; }
    
    public string Sku { get; set; }
    public decimal Price { get; set; }
    public int CategoryId { get; set; }
}

对比:

• ✅ Fluent API: 功能完整,灵活控制
• ⚠️ 数据注解: 简单但功能有限

复合索引

// 场景: 经常按分类和价格范围查询
modelBuilder.Entity<Product>()
    .HasIndex(p => new { p.CategoryId, p.Price });

// SQL:
// CREATE INDEX IX_Products_CategoryId_Price 
// ON Products (CategoryId, Price);

// 查询优化:
var products = await context.Products
    .Where(p => p.CategoryId == 1 && p.Price >= 100 && p.Price <= 500)
    .ToListAsync();  // ✅ 使用复合索引

// ⚠️ 注意: 列顺序很重要!
// 必须先查 CategoryId,再查 Price
// 单独查 Price 无法使用该索引

包含列索引(Covering Index)

// 覆盖索引: 包含查询所需的所有列
modelBuilder.Entity<Product>()
    .HasIndex(p => p.CategoryId)
    .IncludeProperties(p => new { p.Name, p.Price });

// SQL Server:
// CREATE INDEX IX_Products_CategoryId 
// ON Products (CategoryId)
// INCLUDE (Name, Price);

// 查询:
var products = await context.Products
    .Where(p => p.CategoryId == 1)
    .Select(p => new { p.Name, p.Price })
    .ToListAsync();

// ✅ 只需读取索引,无需回表查询!
// 性能提升: 5-10x

---

索引类型详解

1. 聚集索引(Clustered Index)

// 主键自动创建聚集索引
modelBuilder.Entity<Product>()
    .HasKey(p => p.Id);  // 默认聚集索引

// 显式指定聚集索引
modelBuilder.Entity<Product>()
    .HasIndex(p => p.Sku)
    .IsClustered();  // SKU 作为聚集索引

// 特点:
// - 每个表只能有一个聚集索引
// - 数据物理存储按聚集索引排序
// - 适合: 主键、自增列、频繁范围查询

适用场景:

• ✅ 主键(默认)
• ✅ 自增 ID
• ✅ 时间戳(日志表)
• ❌ 频繁更新的列

2. 非聚集索引(Non-Clustered Index)

// 默认创建的都是非聚集索引
modelBuilder.Entity<Product>()
    .HasIndex(p => p.Name);  // 非聚集索引

// 可以有多个非聚集索引
modelBuilder.Entity<Product>()
    .HasIndex(p => p.CategoryId);

modelBuilder.Entity<Product>()
    .HasIndex(p => p.Price);

// 特点:
// - 一个表可有多个(建议 < 10 个)
// - 单独的索引结构,不改变数据存储
// - 适合: WHERE、JOIN、ORDER BY

3. 唯一索引(Unique Index)

modelBuilder.Entity<User>()
    .HasIndex(u => u.Email)
    .IsUnique();

modelBuilder.Entity<Product>()
    .HasIndex(p => p.Sku)
    .IsUnique();

// SQL:
// CREATE UNIQUE INDEX IX_Users_Email 
// ON Users (Email);

// 效果:
// 1. 加速查询
// 2. 强制唯一约束
// 3. 插入重复值会抛出异常

// 处理异常:
try
{
    context.Users.Add(new User { Email = "test@example.com" });
    await context.SaveChangesAsync();
}
catch (DbUpdateException ex) when (ex.InnerException?.Message.Contains("unique") == true)
{
    Console.WriteLine("邮箱已存在!");
}

4. 过滤索引(Filtered Index)

// 只对部分数据建立索引
modelBuilder.Entity<Order>()
    .HasIndex(o => o.Status)
    .HasFilter("[Status] = 'Pending'");

// SQL:
// CREATE INDEX IX_Orders_Status 
// ON Orders (Status)
// WHERE Status = 'Pending';

// 适用场景:
// - 只查询活跃数据
// - 软删除标记
// - 特定状态订单

// 查询优化:
var pendingOrders = await context.Orders
    .Where(o => o.Status == OrderStatus.Pending)
    .ToListAsync();  // ✅ 使用过滤索引,极快!

优势:

• ✅ 索引更小
• ✅ 维护更快
• ✅ 查询更高效

5. 全文索引(Full-Text Index)

// SQL Server 全文索引(需手动创建)
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Article>()
        .HasIndex(a => a.Title);
    
    // 在迁移中创建全文索引
    migrationBuilder.Sql(@"
        CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
        
        CREATE FULLTEXT INDEX ON Articles(Content)
        KEY INDEX PK_Articles
        WITH CHANGE_TRACKING AUTO;
    ");
}

// 全文搜索
var articles = await context.Articles
    .FromSqlRaw(@"
        SELECT * FROM Articles 
        WHERE CONTAINS(Content, @searchTerm)", 
        new SqlParameter("@searchTerm", "\"ASP.NET\" OR \"EF Core\""))
    .ToListAsync();

6. 降序索引(Descending Index)

// .NET 7+ 支持
modelBuilder.Entity<Product>()
    .HasIndex(p => p.CreatedAt)
    .IsDescending(true);  // 降序

// SQL:
// CREATE INDEX IX_Products_CreatedAt 
// ON Products (CreatedAt DESC);

// 优化降序查询:
var latestProducts = await context.Products
    .OrderByDescending(p => p.CreatedAt)
    .Take(10)
    .ToListAsync();  // ✅ 直接使用索引顺序

---

性能分析与优化

索引选择原则

应该建立索引的列

✅ 高频查询列:

// WHERE 子句
.HasIndex(p => p.CategoryId)
.HasIndex(p => p.Status)

// JOIN 外键
.HasIndex(o => o.CustomerId)

// ORDER BY
.HasIndex(p => p.CreatedAt)

// GROUP BY
.HasIndex(o => o.OrderDate)

✅ 高选择性列(唯一值多):

// ✅ 好: Email、手机号、SKU
.HasIndex(u => u.Email).IsUnique()

// ❌ 差: 性别、布尔值
// .HasIndex(u => u.Gender)  // 只有 2 个值,不适合

✅ 复合索引前列:

// 经常组合查询
.HasIndex(p => new { p.CategoryId, p.Price })
// CategoryId 在前(等值查询),Price 在后(范围查询)

不应该建立索引的列

❌ 低频查询列:

// 很少查询的字段
// .HasIndex(p => p.Description)  // 不需要

❌ 低选择性列:

// 只有少量不同值
// .HasIndex(o => o.IsActive)  // 只有 true/false

❌ 频繁更新列:

// 每次更新都要重建索引
// .HasIndex(p => p.ViewCount)  // 每次浏览都更新,开销大

❌ 大文本列:

// .HasIndex(p => p.Content)  // NVARCHAR(MAX) 不适合
// 改用全文索引或前缀索引

索引性能测试

public class IndexPerformanceTest
{
    private readonly AppDbContext _context;
    private readonly ILogger _logger;
    
    public async Task RunTestsAsync()
    {
        // 准备 100 万条测试数据
        await SeedDataAsync(1_000_000);
        
        // 测试 1: 无索引查询
        var sw1 = Stopwatch.StartNew();
        var result1 = await _context.Products
            .Where(p => p.Name == "Test Product")
            .ToListAsync();
        sw1.Stop();
        _logger.LogInformation($"无索引: {sw1.ElapsedMilliseconds}ms");
        // 输出: ~2000ms (全表扫描)
        
        // 添加索引
        _context.Database.ExecuteSqlRaw(
            "CREATE INDEX IX_Products_Name ON Products (Name)");
        
        // 测试 2: 有索引查询
        var sw2 = Stopwatch.StartNew();
        var result2 = await _context.Products
            .Where(p => p.Name == "Test Product")
            .ToListAsync();
        sw2.Stop();
        _logger.LogInformation($"有索引: {sw2.ElapsedMilliseconds}ms");
        // 输出: ~5ms (索引查找)
        
        _logger.LogInformation($"性能提升: {(double)sw1.ElapsedMilliseconds / sw2.ElapsedMilliseconds:F0}x");
        // 输出: 性能提升: 400x
    }
}

执行计划分析

// 查看 SQL 执行计划
var products = await _context.Products
    .FromSqlRaw("SET STATISTICS PROFILE ON; SELECT * FROM Products WHERE Name = @p0", "Laptop")
    .ToListAsync();

// SQL Server Management Studio 输出:
// 
// |--Index Seek(OBJECT:[IX_Products_Name])
//     成本: 0.003 (0%)
//     实际行数: 5
// 
// vs
// 
// |--Table Scan(OBJECT:[Products])
//     成本: 1.234 (100%)
//     实际行数: 5
// 
// 索引查找成本低 400 倍!

缺失索引检测

-- SQL Server: 查询缺失索引
SELECT 
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    OBJECT_NAME(mid.Object_id) AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

-- PostgreSQL:
SELECT * FROM pg_stat_user_tables WHERE seq_scan > idx_scan;
-- seq_scan >> idx_scan 表示缺少索引

索引碎片整理

-- 检查索引碎片
SELECT 
    OBJECT_NAME(ps.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ps
INNER JOIN sys.indexes i ON ps.OBJECT_ID = i.OBJECT_ID AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent > 30;

-- 重组索引(< 30% 碎片)
ALTER INDEX IX_Products_Name ON Products REORGANIZE;

-- 重建索引(> 30% 碎片)
ALTER INDEX IX_Products_Name ON Products REBUILD;

---

.NET 8/9/10 新特性

.NET 8: 改进的索引建议

// .NET 8 增强了诊断信息
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString)
           .EnableDetailedErrors()
           .LogTo(Console.WriteLine, LogLevel.Warning);
});

// 当检测到慢查询时,会建议添加索引:
// warn: The query uses a column 'Products.Name' that has no index. 
//       Consider adding an index to improve performance.

.NET 9: 自动索引优化(预览)

// .NET 9 实验性功能: 基于查询模式的自动索引建议
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString)
           .EnableQueryAnalysis();  // .NET 9 新增
});

// 运行时收集查询统计
// 定期生成索引建议报告

.NET 10: 智能索引管理(路线图)

预计特性:

• 基于 AI 的索引优化建议
• 自动创建和删除索引
• 索引使用情况实时监控
• 自适应索引策略

---

最佳实践与陷阱

索引设计最佳实践

1. 限制索引数量

// ✅ 推荐: 每个表 3-5 个索引
modelBuilder.Entity<Product>()
{
    .HasIndex(p => p.Sku).IsUnique()           // 唯一索引
    .HasIndex(p => p.CategoryId)                // 外键索引
    .HasIndex(p => p.CreatedAt).IsDescending()  // 排序索引
    .HasIndex(p => new { p.CategoryId, p.Price }) // 复合索引
};

// ❌ 避免: 过多索引(> 10 个)
// 每个索引都会:
// - 占用存储空间
// - 降低 INSERT/UPDATE 性能
// - 增加维护成本

2. 正确的列顺序

// ✅ 正确: 等值查询在前,范围查询在后
modelBuilder.Entity<Product>()
    .HasIndex(p => new { p.CategoryId, p.Price });
// WHERE CategoryId = 1 AND Price > 100

// ❌ 错误: 顺序颠倒
modelBuilder.Entity<Product>()
    .HasIndex(p => new { p.Price, p.CategoryId });
// 无法有效利用索引

规则:

1. 等值查询列(=)在前
2. 范围查询列(>, <, BETWEEN)在后
3. 高选择性列优先

3. 使用包含列

// ✅ 优化: 覆盖常用查询
modelBuilder.Entity<Product>()
    .HasIndex(p => p.CategoryId)
    .IncludeProperties(p => new { p.Name, p.Price });

// 查询只需读取索引,无需回表
var products = await context.Products
    .Where(p => p.CategoryId == 1)
    .Select(p => new { p.Name, p.Price })
    .ToListAsync();

4. 定期维护

-- 每周执行
-- 1. 重建碎片化索引
ALTER INDEX ALL ON Products REBUILD 
WITH (FILLFACTOR = 80, ONLINE = ON);

-- 2. 更新统计信息
UPDATE STATISTICS Products WITH FULLSCAN;

-- 3. 删除未使用的索引
DROP INDEX IX_Products_Obsolete ON Products;

常见陷阱

陷阱 1: 过度索引

// ❌ 错误: 为每个列都创建索引
modelBuilder.Entity<Product>()
{
    .HasIndex(p => p.Name)
    .HasIndex(p => p.Description)  // 💥 不必要
    .HasIndex(p => p.Price)
    .HasIndex(p => p.Weight)       // 💥 不必要
    .HasIndex(p => p.Color)        // 💥 不必要
};

// 后果:
// - INSERT 性能下降 50%+
// - 存储空间增加 2-3x
// - 索引维护成本高

// ✅ 正确: 只为高频查询列创建索引

陷阱 2: 忽略复合索引顺序

// 查询模式
var products = await context.Products
    .Where(p => p.CategoryId == 1)
    .OrderByDescending(p => p.CreatedAt)
    .ToListAsync();

// ❌ 错误: 顺序不匹配
.HasIndex(p => new { p.CreatedAt, p.CategoryId });

// ✅ 正确: 与查询模式匹配
.HasIndex(p => new { p.CategoryId, p.CreatedAt });

陷阱 3: 忘记外键索引

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }  // 外键
    
    public Customer Customer { get; set; }
}

// ❌ 错误: 忘记为外键创建索引
// JOIN 查询会很慢

// ✅ 正确: 始终为外键创建索引
modelBuilder.Entity<Order>()
    .HasIndex(o => o.CustomerId);

陷阱 4: 索引 NULL 值

// ⚠️ 注意: NULL 值也会被索引
modelBuilder.Entity<Product>()
    .HasIndex(p => p.DeletedAt);  // 软删除时间

// 大部分记录 DeletedAt 为 NULL
// 索引中包含大量 NULL 值,浪费空间

// ✅ 解决: 使用过滤索引
modelBuilder.Entity<Product>()
    .HasIndex(p => p.DeletedAt)
    .HasFilter("[DeletedAt] IS NOT NULL");
// 只索引已删除的记录

监控与调优

1. 索引使用情况监控

-- SQL Server: 查看索引使用统计
SELECT 
    OBJECT_NAME(ius.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    ius.user_seeks,      -- 索引查找次数
    ius.user_scans,      -- 索引扫描次数
    ius.user_lookups,    -- 书签查找次数
    ius.user_updates     -- 索引更新次数
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i ON ius.index_id = i.index_id AND ius.OBJECT_ID = i.OBJECT_ID
WHERE OBJECT_NAME(ius.OBJECT_ID) = 'Products'
ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC;

-- 分析:
-- seeks >> scans: 索引健康
-- scans >> seeks: 可能需要优化索引
-- updates >> (seeks + scans): 索引可能不值得

2. 慢查询日志

builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString)
           .LogTo((eventId, logLevel) => 
           {
               if (eventId.Id == RelationalEventId.CommandExecuted.Id)
                   return logLevel >= LogLevel.Warning;
               return false;
           }, (eventId, message) => 
           {
               if (message.Contains("EXECUTION TIME"))
                   Console.WriteLine($"慢查询: {message}");
           });
});

3. 自动索引建议工具

// 使用第三方工具
// 1. SQL Server Management Studio (SSMS)
//    - 数据库引擎优化顾问
// 
// 2. Azure SQL Database
//    - 自动索引管理
// 
// 3. PostgreSQL
//    - pg_stat_statements 扩展
// 
// 4. MySQL
//    - Performance Schema

---

总结

核心要点

1. 索引作用: 加速查询 100-1000 倍
2. 索引代价: 略微降低写入性能(5-10%)
3. 索引类型: 单列、复合、唯一、过滤、覆盖
4. 设计原则: 高频查询、高选择性、正确顺序
5. 维护策略: 定期重建、监控使用、删除无用索引

索引决策表

查询模式	推荐索引	示例
WHERE col = value	单列索引	HasIndex(p => p.Sku)
WHERE col1 = x AND col2 > y	复合索引	HasIndex(p => new {p.CatId, p.Price})
ORDER BY col DESC	降序索引	HasIndex(p => p.CreatedAt).IsDescending()
WHERE unique_col	唯一索引	HasIndex(u => u.Email).IsUnique()
WHERE partial_condition	过滤索引	HasIndex(o => o.Status).HasFilter("Status='Pending'")
SELECT col1, col2 WHERE col3	覆盖索引	HasIndex(p => p.CatId).IncludeProperties(p => new {p.Name, p.Price})

性能对比

100 万条数据查询性能:

查询类型          | 无索引   | 有索引   | 提升
-----------------|----------|----------|------
等值查询(WHERE)   | 2000ms   | 5ms      | 400x
范围查询(BETWEEN) | 3000ms   | 10ms     | 300x
排序查询(ORDER BY)| 5000ms   | 50ms     | 100x
JOIN 连接         | 10000ms  | 20ms     | 500x

最佳实践清单

✅ 为外键创建索引
✅ 为 WHERE 子句常用列创建索引
✅ 为 ORDER BY 列创建索引
✅ 使用复合索引优化多列查询
✅ 使用覆盖索引减少回表
✅ 定期监控索引使用情况
✅ 定期重建碎片化索引
✅ 删除未使用的索引

❌ 避免过度索引(> 10 个/表)
❌ 避免为低选择性列创建索引
❌ 避免为频繁更新列创建索引
❌ 不要忘记复合索引的列顺序
❌ 不要忽略索引维护

下一步

• 📖 阅读 预编译查询
• 🔧 学习 批量操作优化
• 🚀 了解 查询拆分技术