--这条语句还能优化吗 大概100W的数据量

select id,account,email,mobile from users where (datediff(yyyy,birthday,getdate())>=20) and ((CreditCoin>1999 and CreditCoin<=3999) or (CreditCoin>999 and CreditCoin<=1999) or (CreditCoin>399 and CreditCoin<=999) or (CreditCoin>99 and CreditCoin<=399) or (CreditCoin>29 and CreditCoin<=99) or isnull(CreditCoin,0) <= 29 or (CreditCoin>=4000))

select id,account,email,mobile 
from users 
where birthday <= dateADD(yyyy,-20,getdate())

------解决方案--------------------
把 OR 用 union all 替换，虽然语句长点，但是效率会提高，尤其是百万级以上数据。 OR 要全表扫描。

------解决方案--------------------
birthday 建立索引

------解决方案--------------------
探讨


birthday 建立索引


------解决方案--------------------
探讨

后面那一长串OR是会员的信誉等级，等级在某个区间，如下：

C# code


case "F": values += " or isnull(CreditCoin,0) <= 29"; break;
                case "E": values += " or (CreditCoin>29 and CreditCoin<=99)"; break;
       ……


------解决方案--------------------

SQL code

(datediff(yyyy,birthday,getdate())>=20)--这种不使用索引
birthday<=dateadd(yy,-20,getdate())--这种使用索引