机遇屋-执行sqlserver数据库索引优化测试

执行sqlserver数据库索引优化测试

作者：ych

## 一百万数据测试
### 创建数据库表
#### 有索引表
```
USE Test
CREATE TABLE Base_HtmlCode1
(
Id int IDENTITY(1,1) PRIMARY KEY,
PId int  DEFAULT 0,
TemplateName  varchar(50),
Code nvarchar(max),
EidtDate datetime,
CreateDate datetime,
Status int
)
EXEC sp_addextendedproperty
'MS_Description', N'主键id',
'SCHEMA', N'dbo',
'TABLE', N'Base_HtmlCode1',
'COLUMN', N'Id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'父键id',
'SCHEMA', N'dbo',
'TABLE', N'Base_HtmlCode1',
'COLUMN', N'PId'
GO

EXEC sp_addextendedproperty
'MS_Description', N'模板名称',
'SCHEMA', N'dbo',
'TABLE', N'Base_HtmlCode1',
'COLUMN', N'TemplateName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'代码',
'SCHEMA', N'dbo',
'TABLE', N'Base_HtmlCode1',
'COLUMN', N'Code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'dbo',
'TABLE', N'Base_HtmlCode1',
'COLUMN', N'CreateDate'
GO

EXEC sp_addextendedproperty
'MS_Description', N'最后编辑时间',
'SCHEMA', N'dbo',
'TABLE', N'Base_HtmlCode1',
'COLUMN', N'EidtDate'
GO

EXEC sp_addextendedproperty
'MS_Description', N'状态：1.启用 2.删除',
'SCHEMA', N'dbo',
'TABLE', N'Base_HtmlCode1',
'COLUMN', N'Status'
GO

CREATE NONCLUSTERED INDEX [Index_Base_HtmlCode1_Name]
ON [dbo].[Base_HtmlCode1] (
  [TemplateName] ASC
)
GO

EXEC sp_addextendedproperty
'MS_Description', N'名称索引',
'SCHEMA', N'dbo',
'TABLE', N'Base_HtmlCode1',
'INDEX', N'Index_Base_HtmlCode1_Name'
GO

CREATE NONCLUSTERED INDEX [Index_Base_HtmlCode1_Code]
ON [dbo].[Base_HtmlCode1] (
  [TemplateName] ASC
)
GO

EXEC sp_addextendedproperty
'MS_Description', N'Code索引',
'SCHEMA', N'dbo',
'TABLE', N'Base_HtmlCode1',
'INDEX', N'Index_Base_HtmlCode1_Code'
GO

```
#### 无索引表
```
USE Test
CREATE TABLE Base_HtmlCode2
(
Id int IDENTITY(1,1) PRIMARY KEY,
PId int  DEFAULT 0,
TemplateName  varchar(50),
Code nvarchar(max),
EidtDate datetime,
CreateDate datetime,
Status int
)
EXEC sp_addextendedproperty
'MS_Description', N'主键id',
'SCHEMA', N'dbo',
'TABLE', N'Base_HtmlCode2',
'COLUMN', N'Id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'父键id',
'SCHEMA', N'dbo',
'TABLE', N'Base_HtmlCode2',
'COLUMN', N'PId'
GO

EXEC sp_addextendedproperty
'MS_Description', N'模板名称',
'SCHEMA', N'dbo',
'TABLE', N'Base_HtmlCode2',
'COLUMN', N'TemplateName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'代码',
'SCHEMA', N'dbo',
'TABLE', N'Base_HtmlCode2',
'COLUMN', N'Code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'dbo',
'TABLE', N'Base_HtmlCode2',
'COLUMN', N'CreateDate'
GO

EXEC sp_addextendedproperty
'MS_Description', N'最后编辑时间',
'SCHEMA', N'dbo',
'TABLE', N'Base_HtmlCode2',
'COLUMN', N'EidtDate'
GO

EXEC sp_addextendedproperty
'MS_Description', N'状态：1.启用 2.删除',
'SCHEMA', N'dbo',
'TABLE', N'Base_HtmlCode2',
'COLUMN', N'Status'
GO

```
### 数据插入

#### 有索引表语句
```
-- 创建一个表变量，用于生成测试数据
DECLARE @TestData TABLE
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    PId INT,
    TemplateName VARCHAR(50),
    Code NVARCHAR(MAX),
    EidtDate DATETIME,
    CreateDate DATETIME,
    Status INT
)

-- 设置插入数据的数量
DECLARE @NumRows INT = 1000000

-- 开始循环插入数据
DECLARE @Counter INT = 1
WHILE @Counter <= @NumRows
BEGIN
    -- 生成随机数据，或根据需求自定义数据
    DECLARE @PId INT = @Counter
    DECLARE @TemplateName VARCHAR(50) = 'Template ' + CAST(@Counter AS VARCHAR(10))
    DECLARE @Code NVARCHAR(MAX) = 'Code ' + CAST(@Counter AS VARCHAR(10))
    DECLARE @EidtDate DATETIME = GETDATE()
    DECLARE @CreateDate DATETIME = GETDATE()
    DECLARE @Status INT = 1

-- 插入数据到表变量
    INSERT INTO @TestData (PId, TemplateName, Code, EidtDate, CreateDate, Status)
    VALUES (@PId, @TemplateName, @Code, @EidtDate, @CreateDate, @Status)

SET @Counter = @Counter + 1
END

-- 将数据从表变量插入到目标表
INSERT INTO Base_HtmlCode1 (PId, TemplateName, Code, EidtDate, CreateDate, Status)
SELECT PId, TemplateName, Code, EidtDate, CreateDate, Status
FROM @TestData

```
执行时间：
>时间: 38.817s

#### 无索引表语句
```
-- 创建一个表变量，用于生成测试数据
DECLARE @TestData TABLE
(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    PId INT,
    TemplateName VARCHAR(50),
    Code NVARCHAR(MAX),
    EidtDate DATETIME,
    CreateDate DATETIME,
    Status INT
)

-- 设置插入数据的数量
DECLARE @NumRows INT = 1000000

-- 插入数据到表变量
    INSERT INTO @TestData (PId, TemplateName, Code, EidtDate, CreateDate, Status)
    VALUES (@PId, @TemplateName, @Code, @EidtDate, @CreateDate, @Status)

SET @Counter = @Counter + 1
END

-- 将数据从表变量插入到目标表
INSERT INTO Base_HtmlCode2 (PId, TemplateName, Code, EidtDate, CreateDate, Status)
SELECT PId, TemplateName, Code, EidtDate, CreateDate, Status
FROM @TestData

```
执行时间：
>时间: 21.248s

### 查询测速
#### 全匹配查询
```
-- 0.229s
select * from Base_HtmlCode1 where Code='Code 100010';
-- 0.308s
select * from Base_HtmlCode2 where Code='Code 100010';
```
#### in查询
```
-- 0.478s
select * from Base_HtmlCode1 where Code in('Code 100012','Code 100014','Code 100016','Code 100019','Code 100017','Code 100018','Code 100011','Code 100077');
-- 0.496s
select * from Base_HtmlCode2 where Code in('Code 100012','Code 100014','Code 100016','Code 100019','Code 100017','Code 100018','Code 100011','Code 100077');
```
#### like查询
```
-- 0.407s
select * from Base_HtmlCode1 where Code LIKE '%Code 100010%';
-- 0.413s
select * from Base_HtmlCode2 where Code LIKE '%Code 100010%';
-- 1.597s
select * from Base_HtmlCode1 where Code LIKE '%Code 1000%' OR Code LIKE '%Code 110%' OR Code LIKE '%Code 120%' OR Code LIKE '%Code 130%';
-- 1.628s
select * from Base_HtmlCode2 where Code LIKE '%Code 1000%' OR Code LIKE '%Code 110%' OR Code LIKE '%Code 120%' OR Code LIKE '%Code 130%';
```

## 一百万数据差距不大，测试一千万

### 数据插入

-- 设置插入数据的数量
DECLARE @NumRows INT = 10000000

-- 开始循环插入数据
DECLARE @Counter INT = 1000001
WHILE @Counter <= @NumRows
BEGIN
    -- 生成随机数据，或根据需求自定义数据
    DECLARE @PId INT = @Counter
    DECLARE @TemplateName VARCHAR(50) = 'Template ' + CAST(@Counter AS VARCHAR(10))
    DECLARE @Code NVARCHAR(MAX) = 'Code ' + CAST(@Counter AS VARCHAR(10))
    DECLARE @EidtDate DATETIME = GETDATE()
    DECLARE @CreateDate DATETIME = GETDATE()
    DECLARE @Status INT = 1

-- 插入数据到表变量
    INSERT INTO @TestData (PId, TemplateName, Code, EidtDate, CreateDate, Status)
    VALUES (@PId, @TemplateName, @Code, @EidtDate, @CreateDate, @Status)

SET @Counter = @Counter + 1
END

```
执行时间：
> 时间: 372.776s

-- 设置插入数据的数量
DECLARE @NumRows INT = 10000000

-- 插入数据到表变量
    INSERT INTO @TestData (PId, TemplateName, Code, EidtDate, CreateDate, Status)
    VALUES (@PId, @TemplateName, @Code, @EidtDate, @CreateDate, @Status)

SET @Counter = @Counter + 1
END

```
执行时间：
>时间: 216.92s

### 查询测速
#### 全匹配查询
```
-- 2.258s
select * from Base_HtmlCode1 where Code='Code 100010';
-- 0.992s
select * from Base_HtmlCode2 where Code='Code 100010';
```
#### in查询
```
-- 4.419s
select * from Base_HtmlCode1 where Code in('Code 100012','Code 100014','Code 100016','Code 100019','Code 100017','Code 100018','Code 100011','Code 100077');
-- 4.427s
select * from Base_HtmlCode2 where Code in('Code 100012','Code 100014','Code 100016','Code 100019','Code 100017','Code 100018','Code 100011','Code 100077');
```
#### like查询
```
-- 3.874s
select * from Base_HtmlCode1 where Code LIKE '%Code 100010%';
-- 3.931s
select * from Base_HtmlCode2 where Code LIKE '%Code 100010%';
-- 16.459s
select * from Base_HtmlCode1 where Code LIKE '%Code 1000%' OR Code LIKE '%Code 110%' OR Code LIKE '%Code 120%' OR Code LIKE '%Code 130%';
-- 17.474s
select * from Base_HtmlCode2 where Code LIKE '%Code 1000%' OR Code LIKE '%Code 110%' OR Code LIKE '%Code 120%' OR Code LIKE '%Code 130%';
```
## 字段使用大文本并没有啥优化换成小字符串
### 查询测速
#### 全匹配查询
```
-- 0.002s
select * from Base_HtmlCode1 where TemplateName='Template 100010';
-- 0.820s
select * from Base_HtmlCode2 where TemplateName='Template 100010';
```
#### in查询
```
-- 0.013s
select * from Base_HtmlCode1 where TemplateName in('Template 100012','Template 100014','Template 100016','Template 100019','Template 100017','Template 100018','Template 100011','Template 100077');
-- 4.235s
select * from Base_HtmlCode2 where TemplateName in('Template 100012','Template 100014','Template 100016','Template 100019','Template 100017','Template 100018','Template 100011','Template 100077');
```
#### like查询
```
-- 4.655s
select * from Base_HtmlCode1 where TemplateName LIKE '%Template 100010%';
-- 4.955s
select * from Base_HtmlCode2 where TemplateName LIKE '%Template 100010%';
-- 23.642s
select * from Base_HtmlCode1 where TemplateName LIKE '%Template 1000%' OR TemplateName LIKE '%Template 110%' OR TemplateName LIKE '%Template 120%' OR TemplateName LIKE '%Template 130%';
-- 27.085s
select * from Base_HtmlCode2 where TemplateName LIKE '%Template 1000%' OR TemplateName LIKE '%Template 110%' OR TemplateName LIKE '%Template 120%' OR TemplateName LIKE '%Template 130%';
```

#### backup
backup.sql
```
GO
DECLARE
@backupTime VARCHAR(20)
DECLARE
@fileName VARCHAR(1000)
SELECT
 @backupTime=(CONVERT(VARCHAR(8), GETDATE(), 112) +REPLACE(CONVERT(VARCHAR(5), GETDATE(), 114), ':', ''))
SELECT
 @fileName='E:\database\dbBackup\DB_'+@backupTime+'.bak'
backup database AAOIT to disk=@fileName
```
bat
```
sqlcmd -S . -i C:\Database\dbBackup\backup.sql
```

### 创建索引
#### 添加索引
```
create index idx_table_cxp on table(code,xh,price)
```

#### 删除索引
```
drop index idx_table_cxp on table
```

#### 索引介绍
```
聚簇索引(一般作为主键，这个会影响排序)
create clustered index index_name on table_name (cloumn_name);

非聚簇索引(默认，索引的叶子节点上存储索引键的数据指针，不改变表物理排序)
create nonclustered index index_name on table_name (cloumn_name);

唯一索引(确保索引列中的所有值都是唯一的，帮助保证数据的完整性)
create unique index index_name on table_name(cloumn_name);
```
#### 查询已存在索引
```
exec sp_helpindex table
```

### 案例
#### 使用索引优化查询
假设有一张TestTable表
```
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type IN ('U'))
DROP TABLE [dbo].[TestTable]
GO
CREATE TABLE [dbo].[TestTable] (
[Id] varchar(50) COLLATE Chinese_PRC_CI_AS  NOT NULL,
[Name] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
[FilePath] varchar(200) COLLATE Chinese_PRC_CI_AS DEFAULT '' NOT NULL,
[ImgPath] varchar(200) COLLATE Chinese_PRC_CI_AS DEFAULT '' NULL
)
GO
ALTER TABLE [dbo].[TestTable] SET (LOCK_ESCALATION = TABLE)
GO
-- Primary Key structure for table TestTable
ALTER TABLE [dbo].[TestTable] ADD CONSTRAINT [PK__TestTabl__3214EC071881A0DE] PRIMARY KEY CLUSTERED ([Id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO
```
#### 1. 添加索引
```
CREATE NONCLUSTERED INDEX IX_TestTable_Name
ON TestTable (Name);
```
解释：
>CREATE NONCLUSTERED INDEX IX_TestTable_Name： 创建一个非聚集索引，并指定索引的名称为 IX_TestTable_Name。 索引名称应该具有描述性，方便识别。
ON TestTable (Name)： 指定要在 TestTable 表的 Name 列上创建索引。

##### 为什么使用非聚集索引？
TestTable 表已经有一个聚集索引（在 Id 列上）。 每个表只能有一个聚集索引，因为聚集索引决定了数据在磁盘上的物理存储顺序。
非聚集索引是独立的结构，指向数据行，可以有多个。 在 Name 列上创建非聚集索引可以提高 WHERE Name = ... 查询的性能。

这个时候把Like改为直接查询，这种索引对like查询是无效的。
```
/// <summary>
        /// 数据检索
        /// </summary>
        public DataTable GetListByKey(string keyWords, ref JqGridParam jqpara, string queryJson)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT * FROM TestTable WHERE 1 = 1 ");
            var queryParam = queryJson.ToJObject();
            DateTime startTime = DateTime.Now;
            DateTime endTime = DateTime.Now;
            if (!string.IsNullOrEmpty(keyWords))
            {
                strSql.Append(@" AND Name =@Name ");
            }
            List<DbParameter> parameter = new List<DbParameter>();
            parameter.Add(DbFactory.CreateDbParameter(GetDbHelper(), "@Name", keyWords));

return Repository().FindTablePageBySql(strSql.ToString(), parameter.ToArray(), ref jqpara);
        }
```
你会发现查询的飞快。
#### 2. 移除索引
```
DROP INDEX IX_TestTable_Name ON TestTable;
```
解释：
>DROP INDEX IX_TestTable_Name ON TestTable： 删除名为 IX_TestTable_Name 的索引。 ON TestTable 指定索引所在的表。

添加索引的注意事项：
>查询模式： 索引主要用于加速 WHERE 子句中的查询。 如果你的查询经常根据 Name 列进行过滤或排序，那么添加索引会有助于提高查询性能。
写操作开销： 索引会增加 INSERT、UPDATE 和 DELETE 操作的开销，因为每次修改数据时，都需要更新索引。 因此，不要过度索引。
存储空间： 索引会占用额外的存储空间。
索引维护： 需要定期维护索引，以确保其性能。 可以使用 ALTER INDEX REBUILD 语句来重建索引。
移除索引的注意事项：
不再使用的索引： 如果某个索引不再被任何查询使用，那么应该删除它，以减少存储空间和维护开销。
性能影响： 删除索引可能会导致某些查询的性能下降。 在删除索引之前，请确保它不会对关键查询产生负面影响。

评论区

先去登录