首页 > 数据库 > 其他综合 >

千万级别数据表,单列索引和多列索引性能对比

2011-10-14

由于Contact表存在多个(单列)索引,造成Delete ,update ,insert操作时需要花费大量的时间删除索引和重建索引。通过把多个(单列)索引合并成一个(多列)索引后,测试得出Delete ,update ,insert操作时...

由于Contact表存在多个(单列)索引,造成Delete update insert操作时需要花费大量的时间删除索引和重建索引。

通过把多个(单列)索引合并成一个(多列)索引后,测试得出Delete update insert操作时需要花费的时间大大缩短。

由于多个(单列)索引合并成一个(多列)索引,可能会对之前单列索引字段的查询性能有影响,做了对比测试。结果如下

测试

Sql语句

查询结果行数

多个(单列)索引运行时间

单个(多列)索引运行时间

结论

删除一天数据

Declare @minCreatedDate Datetime Set @minCreatedDate= Convert(DateTime,'2010-4-25 00:00:00',120)

DELETE from u_ch_Contact where CreatedDate between @minCreatedDate and dateadd(day,1,@minCreatedDate);

40822

SQL Server Execution Times:

CPU time = 17031 ms, elapsed time = 633199 ms.

001034

SQL Server Execution Times:

CPU time = 10405 ms, elapsed time = 39571 ms.

000039

单个(多列)索引明显比多个(单列)索引Del数据时花费的时间要短很多。

CustomerID(单列)索引和(多列)索引下查询性能对比

SELECT * FROM u_ch_contact WHERE CustomerId='F9F268C1-A234-4716-9FC8-00022B2DE8E4'

42

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1345 ms.

000001

SQL Server Execution Times:

CPU time = 31049 ms, elapsed time = 21414 ms.

000021

(单列)索引比(多列)索引在查询数据花费时间要短很多,主要表现在(单列)索引用的是索引查找,(多列)索引用的是索引扫描

CreatedBy(单列)索引和(多列)索引下查询性能对比

SELECT * FROM u_ch_contact WHERE CreatedBy='B8056067-5DBA-41A0-B6CB-01CDFBAC517E'

19099

SQL Server Execution Times:

CPU time = 422 ms, elapsed time = 37038 ms.

000037

SQL Server Execution Times:

CPU time = 35842 ms, elapsed time = 19708 ms.

000019

奇怪!(多列)索引比(单列)索引花费时间要短,但(单列)索引第二次运行Sql语句时间缩短为(CPU time = 218 ms, elapsed time = 733 ms.),而(多列)索引第二次运行Sql语句时间缩短接近一半(CPU time = 35734 ms, elapsed time = 10806 ms.)。同样表现为(单列)索引用的是索引查找,(多列)索引用的是索引扫描

ContactTime(单列)索引和(多列)索引下查询性能对比

SELECT * FROM u_ch_contact WHERE ContactTime BETWEEN '2010-03-01' AND '2010-03-30'

886469

SQL Server Execution Times:

CPU time = 8047 ms, elapsed time = 120984 ms.

000201

SQL Server Execution Times:

CPU time = 7579 ms, elapsed time = 143798 ms.

000224

(单列)索引比(多列)索引在查询数据花费时间要短些,但差距不是大很多。查看执行计划发现(单列)索引和(多列)索引用的都是索引扫描

ContactTelNo(单列)索引和(多列)索引下查询性能对比

SELECT * FROM u_ch_contact WHERE ContactTelNo='15121007351'

36

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 972 ms.

000001

SQL Server Execution Times:

CPU time = 47282 ms, elapsed time = 17972 ms.

000041

(单列)索引比(多列)索引在查询数据花费时间要短很多,主要表现在(单列)索引用的是索引查找,(多列)索引用的是索引扫描

CreateDate(单列)索引和(多列)索引下查询性能对比

SELECT * FROM dbo.u_ch_Contact WHERE CreatedDate BETWEEN '2010-03-01' AND '2010-03-30'

886461

SQL Server Execution Times:

CPU time = 7078 ms, elapsed time = 125751 ms.

000205

SQL Server Execution Times:

CPU time = 7750 ms, elapsed time = 129782 ms.

000210

(单列)索引比(多列)索引在查询数据花费时间要短些,但非常细微的差距。查看执行计划发现(单列)索引和(多列)索引用的都是索引扫描

EscalatedTo,Escalated2列)索引和(多列)索引下查询性能对比

SELECT * FROM dbo.u_ch_Contact WHERE EscalatedTo='BDD4DE94-A75E-4F00-9FD8-06917B856CC1' AND Escalated=0

229

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 311 ms.

000000

SQL Server Execution Times:

CPU time = 35204 ms, elapsed time = 11806 ms.

000011

(单列)索引比(多列)索引在查询数据花费时间要短很多,主要表现在(单列)索引用的是索引查找,(多列)索引用的是索引扫描

EscalatedTo,status2列)索引和(多列)索引下查询性能对比

SELECT * FROM dbo.u_ch_Contact WHERE EscalatedTo='BDD4DE94-A75E-4F00-9FD8-06917B856CC1' AND status=3

6004

SQL Server Execution Times:

CPU time = 328 ms, elapsed time = 7449 ms.

(00:00:07)

SQL Server Execution Times:

CPU time = 34811 ms, elapsed time = 13253 ms.

0000:13

(单列)索引比(多列)索引在查询数据花费时间要短很多,主要表现在(单列)索引用的是索引查找,(多列)索引用的是索引扫描

通过以上对比发现:

查询结果行数不大的情况下: (单列)索引用索引查找明显比(多列)索引用的索引扫描有效率。

查询结果行数大的情况下:都是用索引扫描,相差的数据就不是特别明显。

摘自:踏雪无痕的blog

相关文章
最新文章
热点推荐