原来分析为啥慢的原因分析错误，不是因为索引大的原因，而是错误的使用聚合索引，造成查询频繁的访问数据，进而磁盘I/O很大。
这里错误的使用聚合索引，就是因为在聚合索引中，错误的把Time字段放到前面了。
“索引本身就很大了，大的要遍历这个索引就要频繁的磁盘I/O” 这句话是错误的，对指出这句话错误的色盲 、文盲 、李颖 深表感谢。 以下是修改过的原因分析。
最近连续三、四天，CSDN的登录一直不正常，白天人多的时候，经常会登录超时。为这个问题苦恼了好几天，不知道头发都掉了几根。^&^.
昨天和今天，通过做实验和咨询CSDN几个SQL Server版的大斑竹，才搞清楚是由于当初，想当然的以为聚集索引的效率要比非聚集索引的效率高，错误的使用了聚合索引，而修改了登录日志表中的索引造成的。
CSDN 存在这如下结构的登录日志数据表（安全起见，我修改了表名、字段名）
CREATE TABLE [dbo].[Table1] (
[CSDNUserID] [int] NOT NULL ,
[Time] [smalldatetime] NOT NULL ,
[IP] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]
而登陆的时候存在一个判断三分钟内登录次数不能超过5次的存储过程。其核心SQL如下:
if ((select count( * ) as num from Table1 where abs(datediff(minute,Time,getdate()))<3 and CSDNUserID = @DBUserID) >= 5 )
最近这几天新建的索引是由CSDNUserID和Time组成的聚集索引。（Time靠前的聚合索引）由于最近定期清理登录日志不是很及时，这个表非常大，达到600万以上的数量级。这个索引查询就会非常非常的巨大。上面的SQL语句，在执行的时候，磁盘I/0 ，CPU 的占用就会巨大。进而造成这几天CSDN登录频繁超时。
如果把聚合索引修改顺序，把UserID 靠前，结果就不一样了，两个的速度差别是近千次的差别。
如果修改为不用聚集索引，只在CSDNUserID上建立非聚集索引。这时候的查询速度跟正确的使用聚合索引的时候速度差很小，比起错误的使用聚合索引时候速度差，仍然是近千倍。
当然，如果能定期的清理日志表，速度要比现在要快很多，如果这个日志表足够的小，使用聚集索引反而要非聚集索引要快很多。
如果只是在这里判断三分钟内登录次数不超过5次，这里的判断语句，如果使用正确的聚集索引的。速度是会提高的，当时提高的等次不是特别的大。
另外，在处理这个问题的时候，向邹建学到很多有用的东西，SQL查询分析器一些以前没有注意到的功能在这次查原因中用到，非常方便。
比如：
1、查询分析器中可以方便的看到一个表、存储过程依赖于那些表、存储过程。
2、查询分析器可以分析一个SQL 语句的计划执行情况，从这里可以方便的进行SQL的性能优化。
打印 | 张贴于 2004-03-14 16:25:00 | Tag:数据库开发管理心得
留言反馈
我是菜鸟，我想到一个办法不知道有没有用？ 3分钟登陆5次可以改成： 30秒内不能重复登录吗？ 如果可以的话，创建一个SESSION会话，设置TIMEOUT=30秒。记录IP！
不知道行不行？
里面记录其登陆时间 谢谢
稍晚时候，对你们提出的几套方案进行性能测试，
然后对下一套用户系统：其实早已开发完毕，就是用在DearBook上的那套，进行必要的调整，然后部署上去。
以及问题的根本原因。
1、你要搞明白慢的原因。我认为是INSERT LOG到表的时候，因为你做了聚集索引，要进行物理移动，SQL语句:if ((select count( * ) as num from Table1 where abs(datediff(minute,Time,getdate()))<3 and CSDNUserID = @DBUserID) >= 5 )没有优化好只是次要。
2、方案无数，李颖讲的，还有其它人讲的。。。
针对慢的原因/兼顾缓存方案维护困难 提出方案:
1、建一张3分钟LOG表，这表最大记录数是N（N=后面的N）分钟内登录次数，因为数据少，不存在做查询优化的问题了。维护此表办法：设任务每隔N分钟删除超过3分钟的记录，N由你去控制了。
2、建一历史LOG表，不做任何索引，做INSER操作速度非常快。为什么可以不做索引，CSDN网管没有必要每天会去看LOG吧？你要统计分析的时候把它倒到与CSDN前台无关的数据库，任你煎炒后（做什么索引都好啦）再做任何的SELECT分析统计速度也是非常快。
这样不是解决问题了吗？这样做很麻烦吗？
说明：用户登录时同时INSERT两个表，并SELECT了3分钟表。别怕因为是两次INSERT会影响性能，因为你没有做聚集索引，兄弟！
1， 关键的问题是由于smalldatetime 精确到分钟，这样的话就有大量的重复，需要再根据UserID来建索引，这就是你用Time慢的原因。你可以改成DateTime试试
2，由于你的更改特别频繁，用索引消耗的资源太大，还不如不用索引，而是使用一个临时表保存最近的登录信息（UserID，Time），隔一段时间（5分钟？这样你测试了）清空多于三分钟的信息。
以上的测试是测试的如下代码，也就是让聚合索引起作用的代码。
declare @Time DateTime
select @Time = DateAdd(minute,-3,getdate())
select count(*) as num from lt_logonbak
where logontime > @Time
and UserID = 4807
如果对Time和UserID分别都作非聚合索引。
比Time 靠前的聚合索引要快。
比UserID 靠前的聚合索引慢。
你也不能只看查询速度啊，还有频繁的插入log会导致索引更新，可能更慢呢。
放心，没在CSDN上用，因为目前正在本地测试，并没有放到CSDN上。
where logontime > @Time
三分钟以前的记录应该很多啊。
select @Time = DateAdd(minute,-3,getdate())
select count(*) as num from lt_logonbak
where logontime < @Time
and UserID = 4807
这样的语句，这个聚集索引起作用了。但是在索引中，把Time和UserID的顺序对调，Time 靠前的情况所花费的CPU 磁盘I/O 仍然是UserID 在前面的近百倍。
看齐无论如何Time都不应该放到最前面。
Time > getdate() - 3min and Time < getdate()
呵呵，Log时间会大于当前时间吗？？有黑客吗？ :)))
那么，
语句： abs(datediff(minute,Time,getdate()))<3
这个判断必须执行函数，我想可能还是很难利用索引的吧？
改为
Time > getdate() - 3min
and Time < getdate() // 这部分应该可以省略
应该就可以直接利用索引了
Time>@T0 and Userid = @DBUserID
因为三分钟内的记录不多，用row scan好了。
我觉得主键应该是登录时间靠前是对的。慢的原因很可能是查询执行的时候没能利用到你的主键。
abs(datediff(minute,Time,getdate()))<3 and CSDNUserID = @DBUserID) >= 5 )
不知道SQL server的查询分析有多智能，对于datediff这样的查询会不会优化。如果先计算三分钟以前的时间点T0, 再用Time>t0，应该会利用上索引。
UserId+Time索引：查询时会优先利用索引上的UserID，但是索引更新频率太高，而且是大面积调整。只对Time索引会不会好些？Time本身就是顺序的，所以索引调整的机会不大。
楼上几个也别激动，学习都是需要过程的。美国战斗机还会因为软件原因坠毁呢。
有空我测试一下你的方案的效率。自己也学习学习。
但是我设想的方案，主要优点在于第2点
使用单独的表保持最近的登陆信息，且由程序自动清除
这样的话，对于你的大表不再有查询的必要，因此也就可以去掉为优化查询速度服务的索引，这样可以大大提高INSERT和UPDATE速度，因为数据量实在太大了
而且，使用单独的表，对于大表中的数据量不再有限制，也就对不再要求一定要通过“手工清理LOG数量”来保证系统性能，这也是系统稳定性的一个方面
如果因为用户没有定期清除日志，系统性能略降，这是正常的可以接受的，如果性能狂降甚至崩溃，这就是设计者的责任了
总的来说，这是一个原则性的问题，对于一个小需求，尽量限制在局部范围内解决，包括系统逻辑结构、数据结构、程序代码等等，尽量不要因为这个小范围的需求对系统全局产生大范围的影响和限制
这样的话，可以尽量保持系统各模块相对独立，耦合度低，也是为了将来各模块尽量能够独立地维护、演化的考虑
与定期清理日志表同时日志表增加索引的差别应该不是很大，
我没做实验，这只是我的猜想。
毕竟日志你是需要定期清理的，你不可能让他一直保留下去。
那还不如把清理作的勤快点，同时有索引。
我个人觉得，在600百万日志级别的数据量下，你的方案的优势不是特别突出。如果我保证日志在100万以下，更没有这个必要了。
现在最苦恼的是，这个日志表很大，进行清理的时候，就会造成登录超时。也许清理这个日志表的时候，应该把限制的查询停掉。
数据越聚越多。效率越来越差。
1、每次登陆时，插入记录到LOG表，同时插入到缓存表
2、同时删除缓存表中超过3分钟的纪录
3、对缓存表进行select count统计
这样的话，优点是
1、select count统计对象的数据量保持在最近3分钟，相比查询LOG表中的全部数据来说，性能更高
2、对于LOG不再有查找需求，因此可以去掉其中的索引，对于LOG表，99%的操作是INSERT，而且数据量可能会极大，因此尽可能的去掉不必要的索引之后，INSERT时维护索引的开销会降低很多
要根据你的具体应用，决定你的聚合索引先后顺序。
这句话是错误的，对指出这句话错误的色盲 、文盲 、李颖 深表感谢。
索引本身应该是用平衡查找树实现的
查找次数应该是数据量的对数，就算是在文件中保存的索引，也应该非常快才对
这是对的，索引是很快的，但是错误的使用聚合索引，会造成访问数据的几何倍的增长。
((select count( * ) as num from Table1 where CSDNUserID = @DBUserID and time between dateadd(minute,-3,getdate()) and dateadd(minute,3,getdate())) >= 5 )
单独建立一个表来保存“最近多少分钟内”的登陆数据，不是可以大大减少查询数据量么？
这确实是一个方法，以前没有想过这个问题这么突出，对这个问题也就没有刻意的这么做。这个方法的缺点也很明确，就是冗余，当然有时候为了性能，冗余是必须的。对这个方法需要测试，讨论。比如：为了这个判断快，让登录作两个插入操作，这样的方案比起登陆日志表勤快的清理起来，值得么？？
我刚刚想了一下，不这样做的原因如下：
登录次数要限制的同一个ID，三分钟之内，他不论从任何机子任何IP进行登录都是要遵守这个限制的。要实现这样的限制，在ASP中在缓存中实现的这个的成本比较大，需要自己开发Com组件，心理没把握，如果保存到一个Application 列表中，对这个列表的定期清理，这个列表所占的内存定期清理，这些问题会很多，所以最简单的做法就是在数据库中实现。只要最好这个历史表的定期清理，这个做法是最简单的做法。
.net 下也存在这个问题，好在.net是托管代码，自己定义的缓存更安全，可靠些。我在考虑.net下实现缓存记录的可行性,不过这个对内存的占用有多大？？是需要测试的，尤其是你的在线用户很多的时候。
索引本身应该是用平衡查找树实现的
查找次数应该是数据量的对数，就算是在文件中保存的索引，也应该非常快才对
教学理论上是这样的。但我实践得到的数据跟这个不一样，我专门作了实验，用聚合索引和不用聚合索引，两个的磁盘I/O数，CPU占用时间，两个的差别是近1000倍。
以上只是我对这个事实的分析。也许我的原因分析不对。但是事实毕竟是事实，你无法否定，不要迷信与课本的观点，任何观点都是有成立条件的。
1、索引本身应该是用平衡查找树实现的
查找次数应该是数据量的对数，就算是在文件中保存的索引，也应该非常快才对
对于查找操作，速度慢的原因应该是没有用到合适的索引才对
2、对于LOG这种表，99%的操作是insert动作
做索引本身就会大大降低操作速度
对于楼主的需求，判断最近多少分钟内登陆过多少次
这个可以用内存中的缓存数据来进行判断，根本不用去LOG表中查找
这样一来，因为去掉了对LOG表的查找动作，索引存在的必要性就完全没有了
用缓存实现，只需要缓存最近几分钟的登陆信息，就可以了
如果在LOG表中查找实现，则需要在几年的历史数据中查找
3、退一步说，不用内存缓存，针对“判断最近多少分钟内登陆过多少次”这样的需求
单独建立一个表来保存“最近多少分钟内”的登陆数据，不是可以大大减少查询数据量么？
不知道这位老大会不会用SQL……
拜托，CSDN的老大，你学过数据库原理吗？你知道索引是什么吗？不要天天装成老大来教训人好不好？好好的回去学习学习数据库再出来说话，你这么说话简直就是丢我们这些在CSDN混的人的脸啊。
我不觉得这个是瓶颈，每次登录的时候无论成功与否都用INSERT或UPDATE [Table1]吧？如果是，瓶颈就在这里。对这种SELECT和UPDATE操作是相同次数量的，是不应该做聚集索引会快！
不将登录次数状态放到缓存
改为
将登录次数状态放到缓存
if ((select count( * ) as num from Table1 where abs(datediff(minute,Time,getdate()))<3 and CSDNUserID = @DBUserID) >= 5 )
1、两个AND条件位置对调，原因不说应该知道吧。。。WHERE 的第二个条件是在第一个条件结果中去执行的。。。
2、是否可以把ABS去了呢？不一定要那么准确3分钟吧？可以减少运算。
if ((select count( * ) as num from Table1 where CSDNUserID = @DBUserID) >= 5 AND datediff(minute,Time,getdate())<3)
可能十万级的数据量没有太大变化，如果上到千万级，那可是明显的变化啊。。。。。
另外是否可以不将登录次数状态放到缓存，避免平凡的数据读写呢？？？？
权衡一下维护吧。。。考虑考虑，以免我也登录不上CSDN，我昨天登录了好几次都失败：）
是太大的表不能做聚集索引？
对聚集索引我的看法是：
1、权衡（INSERT、UPDATE）和（SELECT）这两类操作的次数，如果SELECT>（INSERT、UPDATE）可以考虑做聚集，小于的话不建议。如果做了聚集索引（INSERT 、UPDATE）将明显变慢，因为SQLSERVER要重新排序，相当与做大量的SELECT操作。
2、即使做聚集索引，索引哪些子段？也是很讲究，比如
SELECT ProductName FROM Products WHERE ProductName LIKE 'pnameStr%' ORDER BY Rank ,CreatedDate DESC
这条语句应索引ProductName还是Rank，感觉是ProductName，因为SQL执行是先WHERE后ORDER，而且是LIKE操作，但如果不是LIKE操作，而是有几个=条件操作呢？
我现在还不知道？望高人指点。。。PRODUCTS有1千万数据量
abs(datediff(minute,Time,getdate()))<3根本用不到索引难道连这都不知？