Index--复合索引的思考1

9/1/2015来源:SQL技巧人气:2439

Index--复合索引的思考1

在创建复合索引时,除了考虑索引键的选取外,还需考虑索引键的先后顺序。下面借助一些场景来讲解。

场景1表dbo.UserLoginStats记录每个用户每天的登录统计,目前表中存放10亿数据,每天新增数据500W(每天每个用户很少几条条记录),目前系统有用户8000W,有查询:SELECT * FROM dbo.UserLoginStatsWHERE UserID=@userIDAND LoginDay=@loginDay

对于此查询,可以创建索引:

CREATE INDEX IX_UserID_LoginDay ON dbo.UserLoginStats(UserID,LoginDay)或CREATE INDEX IX_LoginDay_UserID ON dbo.UserLoginStats(LoginDay,UserID)

以上两种索引都可以帮助查询快速返回结果,并且消耗的IO相同,消耗的CPU时间也大致相同,因此对于该查询来说,两个索引没有区别,但我们该使用哪一个查询呢?

假设索引行每行占用20个字节,每个索引页存放400条记录,则10亿数据需要约2500W个索引页。对于索引IX_LoginDay_UserID(LoginDay,UserID):每天新增的500W新纪录存放在一起,需要约1.3万个索引页来存放,只需要100MB的内存来存放,在数据读取和写入时,更多的是顺序IO。

对于索引IX_UserID_LoginDay(UserID,LoginDay):每天新增的500W数据需要分散存放到索引的各个页面中,可能影响到数百万的索引页,需要1GB到5GB的内存,在数据读取和写入时,更多的是随机IO。

因此,在不考虑其他因素影响的条件下,针对该场景,索引IX_LoginDay_UserID(LoginDay,UserID)时最佳的。

误区:在创建复合索引时,很多人会将选择性较高的列放在前面,解释:可选择性是我们在挑选索引键时考虑的一个因素,通常会选择性较高的备选键来创建索引,但不意味该键就应该放在索引前面。

PS: 在笔者维护的系统中,曾出现过类似问题,在checkpoint时需要写入上万个不连续的数据页,导致很高的磁盘队列,同时还导致在日志备份还原时消耗大量的时间。

PS2:针对该问题,数据分区和历史数据定期数据归档也是很好的解决办法。

惯例上图引狼

图片来源:http://www.douban.com/photos/photo/353424799/