聚集索引遇到的性能问题(查询分析器试验)

2006-03-25 08:04:10 167 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

ARM双核锁步DCLS Lockstep技术研究（FPGA实现）基于《Applying Dual-Core Lockstep in Embedded Processors to Mitigate Radiation-induced Soft Errors》文章总结 RISC-V双核锁步DCLS Lockstep技术参考之前文章： RISC-V双核锁步DCLS Lockstep技术总结_远古架构师alanwu的博客-CSDN博客https://blog.csdn.net/heyuming20062007/article/details/123329537ARM+RIS 阅读详情

背景:做的是userId+sdate的聚集索引，userId在前
select * from test where datediff(day,sdate,'2006-1-1')=0 and userId='01'

后由于发现性能很差，便在查询分析器中做实验

当实现
select * from test where datediff(day,sdate,'2006-1-1')=0 and userId='01'
select * from test where datediff(day,sdate,'2006-1-2')=0 and userId='01'
select * from test where datediff(day,sdate,'2006-1-3')=0 and userId='01'

也即变化sdate，保持userId不变时，性能很好，

而当
select * from test where datediff(day,sdate,'2006-1-1')=0 and userId='01'
select * from test where datediff(day,sdate,'2006-1-1')=0 and userId='02'
select * from test where datediff(day,sdate,'2006-1-1')=0 and userId='03'
性能却慢了一倍多的时间

如果在where中把sdate和userId的位置对调也是这样

由于我在实际中的例子是按照
select * from test where datediff(day,sdate,'2006-1-1')=0 and userId='01'
select * from test where datediff(day,sdate,'2006-1-1')=0 and userId='02'
select * from test where datediff(day,sdate,'2006-1-1')=0 and userId='03'

来的，所以只能上来问下大家，谢谢

...全文