-
C#中关于数据库索引优化——慢查询解决
第十部分：性能优化与内存管理
实例98：数据库索引优化——慢查询解决
实例介绍
之前做电商订单系统时，用户反馈“我的订单”页面加载时间超过5秒，通过慢查询日志分析，发现查询订单的SQL语句执行时间超过4秒，扫描了100万条数据。通过添加复合索引后，SQL语句执行时间降到100ms以内，页面加载时间降到500ms以内。这节就带你从vb.net教程C#教程python教程SQL教程access 2010教程
零学习数据库索引优化，包括慢查询识别、索引创建、索引失效场景、执行计划分析，覆盖数据库性能优化的核心场景。
需求分析
数据库索引优化要解决“慢查询识别、索引创建、索引失效、执行计划分析”的问题，具体需求如下：
1.慢查询识别：掌握慢查询日志的配置和分析方法；
2.索引创建：学习如何创建主键索引、唯一索引、普通索引、复合索引；
3.索引失效：识别常见的索引失效场景，如LIKE '%xxx%'、函数包装字段；
4.执行计划分析：掌握执行计划的分析方法，识别性能瓶颈；
5.性能提升：通过索引优化提升数据库查询速度，减少系统响应时间；
代码实现
前置条件：SQL Server 2022；Entity Framework Core 8；Visual Studio 2022；
场景1：慢查询识别——SQL Server慢查询日志配置
配置SQL Server慢查询日志，识别执行时间超过阈值的SQL语句。
步骤1：配置慢查询日志（SQL Server Management Studio）
1.打开SQL Server Management Studio，连接到数据库；
2.右键点击数据库实例 → “属性” → “数据库设置” → “慢查询日志”；
3.勾选“将慢查询记录到文件”，设置“慢查询阈值”为2秒；
4.点击“确定”，重启SQL Server服务；
步骤2：分析慢查询日志
1.打开慢查询日志文件（默认路径：C:Program FilesMicrosoft SQL ServerMSSQL16.MSSQLSERVERMSSQLLog）；
2.查找执行时间超过2秒的SQL语句，例如：
sql
SELECT * FROM Orders WHERE CustomerId = 12345 AND Status = 2;
3.分析SQL语句的执行计划，识别性能瓶颈；
场景2：索引创建——复合索引提升多条件查询速度
为多条件查询创建复合索引，提升查询速度。
步骤1：错误代码（NoIndexExample.cs）
csharp
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
namespace DatabaseIndexOptimization;
public class OrderDbContext : DbContext
{
public DbSet<Order> Orders { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("Server=.;Database=OrderDb;Integrated Security=True;TrustServerCertificate=True;");
}
}
public class OrderQueryService
{
private readonly OrderDbContext _dbContext;
public OrderQueryService(OrderDbContext dbContext)
{
_dbContext = dbContext;
}
// 错误：没有为CustomerId和Status创建索引，导致全表扫描
public List<Order> GetCustomerOrders(int customerId, OrderStatus status)
{
// 生成的SQL语句：SELECT * FROM Orders WHERE CustomerId = @p0 AND Status = @p1;
// 执行时间超过4秒，扫描100万条数据
return _dbContext.Orders
.Where(o => o.CustomerId == customerId && o.Status == status)
.ToList();
}
}
public class Order
{
public int OrderId { get; set; }
public int CustomerId { get; set; }
public string CustomerName { get; set; } = string.Empty;
public decimal TotalAmount { get; set; }
public DateTime OrderDate { get; set; }
public OrderStatus Status { get; set; }
}
public enum OrderStatus
{
Pending,
Processing,
Shipped,
Delivered,
Cancelled
}
步骤2：修复代码（创建复合索引）
csharp
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.Collections.Generic;
using System.Linq;
namespace DatabaseIndexOptimization;
// 配置Order实体的索引
public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
public void Configure(EntityTypeBuilder<Order> builder)
{
// 主键索引（自动创建）
builder.HasKey(o => o.OrderId);
// 为CustomerId和Status创建复合索引，提升多条件查询速度
builder.HasIndex(o => new { o.CustomerId, o.Status })
.HasName("IX_Orders_CustomerId_Status"); // 指定索引名称
// 为OrderDate创建普通索引，提升排序和范围查询速度
builder.HasIndex(o => o.OrderDate)
.IsDescending() // 降序索引，适合按时间倒序查询
.HasName("IX_Orders_OrderDate");
}
}
public class OrderDbContext : DbContext
{
public DbSet<Order> Orders { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("Server=.;Database=OrderDb;Integrated Security=True;TrustServerCertificate=True;");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// 应用Order实体的配置
modelBuilder.ApplyConfiguration(new OrderConfiguration());
}
}
public class OrderQueryService
{
private readonly OrderDbContext _dbContext;
public OrderQueryService(OrderDbContext dbContext)
{
_dbContext = dbContext;
}
// 正确：使用复合索引，查询时间降到100ms以内，扫描10条数据
public List<Order> GetCustomerOrders(int customerId, OrderStatus status)
{
return _dbContext.Orders
.Where(o => o.CustomerId == customerId && o.Status == status)
.ToList();
}
}
场景3：索引失效——避免常见的索引失效场景
识别并避免常见的索引失效场景，确保索引生效。
步骤1：索引失效代码（IndexInvalidationExample.cs）
csharp
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
namespace DatabaseIndexOptimization;
public class OrderQueryService
{
private readonly OrderDbContext _dbContext;
public OrderQueryService(OrderDbContext dbContext)
{
_dbContext = dbContext;
}
// 错误：使用函数包装字段，导致索引失效
public List<Order> GetOrdersByMonth(int year, int month)
{
// 生成的SQL语句：SELECT * FROM Orders WHERE YEAR(OrderDate) = @p0 AND MONTH(OrderDate) = @p1;
// 使用YEAR和MONTH函数包装OrderDate字段，导致IX_Orders_OrderDate索引失效
return _dbContext.Orders
.Where(o => o.OrderDate.Year == year && o.OrderDate.Month == month)
.ToList();
}
// 错误：使用LIKE '%xxx%'，导致索引失效
public List<Order> GetOrdersByCustomerName(string customerName)
{
// 生成的SQL语句：SELECT * FROM Orders WHERE CustomerName LIKE @p0;
// 使用LIKE '%xxx%'，导致CustomerName索引失效（如果有索引的话）
return _dbContext.Orders
.Where(o => o.CustomerName.Contains(customerName))
.ToList();
}
// 错误：类型转换，导致索引失效
public List<Order> GetOrdersByCustomerId(string customerId)
{
// 生成的SQL语句：SELECT * FROM Orders WHERE CustomerId = CAST(@p0 AS int);
// 将字符串转换为int，导致CustomerId索引失效
return _dbContext.Orders
.Where(o => o.CustomerId == int.Parse(customerId))
.ToList();
}
}
步骤2：修复代码（避免索引失效）
csharp
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
namespace DatabaseIndexOptimization;
public class OrderQueryService
{
private readonly OrderDbContext _dbContext;
public OrderQueryService(OrderDbContext dbContext)
{
_dbContext = dbContext;
}
// 正确：避免使用函数包装字段，使用范围查询
public List<Order> GetOrdersByMonth(int year, int month)
{
var startDate = new DateTime(year, month, 1);
var endDate = startDate.AddMonths(1);
// 生成的SQL语句：SELECT * FROM Orders WHERE OrderDate >= @p0 AND OrderDate < @p1;
// 使用范围查询，IX_Orders_OrderDate索引生效
return _dbContext.Orders
.Where(o => o.OrderDate >= startDate && o.OrderDate < endDate)
.ToList();
}
// 正确：使用全文索引或前缀匹配，避免LIKE '%xxx%'
public List<Order> GetOrdersByCustomerName(string customerName)
{
// 方法1：使用前缀匹配（如果业务允许）
// 生成的SQL语句：SELECT * FROM Orders WHERE CustomerName LIKE @p0;
// @p0 = 'Customer_%'，CustomerName索引生效
return _dbContext.Orders
.Where(o => o.CustomerName.StartsWith(customerName))
.ToList();
// 方法2：使用全文索引（适合模糊查询）
// 需要先创建全文索引：CREATE FULLTEXT INDEX ON Orders(CustomerName) KEY INDEX PK_Orders_OrderId;
// return _dbContext.Orders
// .Where(o => EF.Functions.FreeText(o.CustomerName, customerName))
// .ToList();
}
// 正确：提前转换类型，避免在查询中转换
public List<Order> GetOrdersByCustomerId(string customerId)
{
if (!int.TryParse(customerId, out var id))
{
throw new ArgumentException("Invalid customer ID", nameof(customerId));
}
// 生成的SQL语句：SELECT * FROM Orders WHERE CustomerId = @p0;
// 提前转换类型，CustomerId索引生效
return _dbContext.Orders
.Where(o => o.CustomerId == id)
.ToList();
}
}
场景4：执行计划分析——使用SQL Server执行计划识别性能瓶颈
使用SQL Server执行计划分析查询性能，识别索引是否生效。
步骤1：查看执行计划（SQL Server Management Studio）
1.打开SQL Server Management Studio，执行SQL语句：
sql
SELECT * FROM Orders WHERE CustomerId = 12345 AND Status = 2;
2.
3.点击“显示估计的执行计划”（Ctrl+L）；
4.查看执行计划图标：
1.聚集索引扫描：全表扫描，索引未生效；
2.索引查找：索引生效，快速定位数据；
3.键查找：需要回表查询，可能需要创建覆盖索引；
步骤2：创建覆盖索引（避免回表查询）
sql
-- 创建覆盖索引，包含查询需要的所有字段，避免回表查询
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_Status_Covering
ON Orders (CustomerId, Status)
INCLUDE (OrderId, CustomerName, TotalAmount, OrderDate);
逐行讲解
场景2：复合索引核心代码
1.HasIndex(o => new { o.CustomerId, o.Status })：为CustomerId和Status创建复合索引；
2.复合索引顺序：复合索引的顺序很重要，通常将过滤条件中选择性高的字段放在前面；
3.索引名称：使用HasName指定索引名称，便于管理和维护；
4.降序索引：使用IsDescending()创建降序索引，适合按时间倒序查询；
场景3：索引失效修复核心代码
1.范围查询替代函数：使用OrderDate >= startDate && OrderDate < endDate替代YEAR(OrderDate) == year，避免函数包装字段；
2.StartsWith替代Contains：使用StartsWith生成LIKE 'xxx%'，索引生效；Contains生成LIKE '%xxx%'，索引失效；
3.提前类型转换：在查询前转换类型，避免在SQL语句中使用CAST或CONVERT；
场景4：覆盖索引核心代码
1.INCLUDE子句：包含查询需要的所有字段，避免回表查询（键查找）；
2.覆盖索引作用：覆盖索引可以将查询时间从毫秒级降到微秒级，因为不需要从聚集索引中获取额外数据；
基础知识拓展
- 索引类型与适用场景
| 索引类型 | 描述 | 适用场景 |
|---|---|---|
| 主键索引 | 唯一标识表中的记录，自动创建，聚集索引 | 主键查询、唯一约束 |
| 唯一索引 | 确保字段值唯一，可以是聚集或非聚集索引 | 唯一字段查询，如邮箱、手机号 |
| 普通索引 | 加速单字段查询，非聚集索引 | 单字段过滤、排序 |
| 复合索引 | 为多个字段创建索引，加速多条件查询 | 多条件过滤、排序 |
| 覆盖索引 | 包含查询需要的所有字段，避免回表查询 | 复杂查询、报表查询 |
| 全文索引 | 加速文本模糊查询 | 文本搜索、模糊匹配 |
-
索引失效常见场景
函数包装字段：YEAR(OrderDate) = 2024、UPPER(CustomerName) = 'CUSTOMER_123'；
LIKE '%xxx%'：模糊查询使用通配符开头，索引失效；
类型转换：CustomerId = CAST('123' AS int)，索引失效；
OR条件：CustomerId = 123 OR Status = 2，如果没有复合索引，索引可能失效；
NULL值判断：CustomerName IS NULL，如果索引不包含NULL值，索引失效； - 执行计划核心图标
| 图标 | 描述 | 性能影响 |
|---|---|---|
| 聚集索引扫描 | 全表扫描，遍历所有记录 | 性能最差，适合小表 |
| 索引查找 | 使用索引快速定位记录 | 性能好，适合大表查询 |
| 键查找 | 从聚集索引中获取额外数据（回表查询） | 性能一般，可通过覆盖索引优化 |
| 哈希匹配 | 连接两个数据集，适合大数据量连接 | 性能一般，可通过索引优化 |
| 嵌套循环 | 连接两个数据集，适合小数据量连接 | 性能好 |
-
索引维护与优化
索引重建：当索引碎片超过30%时，重建索引：
sql
ALTER INDEX IX_Orders_CustomerId_Status ON Orders REBUILD;
索引重组：当索引碎片在5%到30%之间时，重组索引：
sql
ALTER INDEX IX_Orders_CustomerId_Status ON Orders REORGANIZE;
索引统计信息更新：更新索引统计信息，帮助查询优化器生成更好的执行计划：
sql
UPDATE STATISTICS Orders;
-
数据库性能监控工具
SQL Server Profiler：监控数据库查询，识别慢查询；
Extended Events：轻量级性能监控工具，适合生产环境；
Query Store：跟踪查询性能变化，识别性能退化的查询；
Azure Monitor：云数据库性能监控工具；
总结
数据库索引优化的核心是创建合适的索引、避免索引失效、减少全表扫描和回表查询。关键要点：
1.慢查询识别：配置慢查询日志，识别执行时间超过阈值的SQL语句；
2.索引创建：根据查询场景创建主键索引、唯一索引、复合索引、覆盖索引；
3.索引失效避免：避免函数包装字段、LIKE '%xxx%'、类型转换等场景；
4.执行计划分析：使用执行计划识别性能瓶颈，优化查询；
5.索引维护：定期重建或重组索引，更新统计信息；
比如这个数据库索引优化，创建复合索引后，查询时间从4秒降到100ms以内；使用覆盖索引后，查询时间降到10ms以内；避免索引失效后，查询性能提升100倍。掌握数据库索引优化，你就能构建高性能的数据库应用！
本站原创，转载请注明出处：https://www.xin3721.com/ArticlecSharp/c49496.html
