title全文索引 class普通索引
------------------------------
select top 1 * from (
select row_number() over(order by id desc) as nid,id,title from [News] where CONTAINS([title],'"Mike*"') and class='系统管理' ) y
耗时3000ms ( 经测 top n 当n越大 耗时越小 )select top 10000 * from (
select row_number() over(order by id desc) as nid,id,title from [News] where CONTAINS([title],'"Mike*"') and class='系统管理' ) y
耗时20msselect row_number() over(order by id desc) as nid,id,title from [News] where CONTAINS([title],'"Mike*"') and class='系统管理'
耗时20ms
求解为何语句一中n越小反而耗时越长
------------------------------
select top 1 * from (
select row_number() over(order by id desc) as nid,id,title from [News] where CONTAINS([title],'"Mike*"') and class='系统管理' ) y
耗时3000ms ( 经测 top n 当n越大 耗时越小 )select top 10000 * from (
select row_number() over(order by id desc) as nid,id,title from [News] where CONTAINS([title],'"Mike*"') and class='系统管理' ) y
耗时20msselect row_number() over(order by id desc) as nid,id,title from [News] where CONTAINS([title],'"Mike*"') and class='系统管理'
耗时20ms
求解为何语句一中n越小反而耗时越长
--------------------
select top 10000 row_number() over(order by id desc) as nid,id,title from [News] where CONTAINS([title],'"Mike*"') and class='系统管理' - 20msselect top 1 row_number() over(order by id desc) as nid,id,title from [News] where CONTAINS([title],'"Mike*"') and class='系统管理' - 3000ms那为何上面2种情况截然相反呢
select top 10000 * from news order by id desc前者做全表扫描,会慢。这个分析不要单看返回值的时间,set showplan_all on
select row_number() over(order by id desc) as nid,id,title from [News] where CONTAINS([title],'"Mike*"') and class='系统管理' ) y 计划: SELECT... 嵌套循环 <- 聚集索引扫描(5%)
|<- 远程扫描(95%)
select top 10000 * from (
select row_number() over(order by id desc) as nid,id,title from [News] where CONTAINS([title],'"Mike*"') and class='系统管理' ) y 计划: SELECT...嵌套循环 <- 排序 <- 嵌套循环 <- 远程扫描(17%)
|<-键查找(22%) |<-索引查找(55%)
|<- 远程扫描(95%)
嵌套循环 <- 排序 <- 嵌套循环 <- 远程扫描(17%)
|<-键查找(22%) |<-索引查找(55%)
select top 1 * from (
select row_number() over(order by id desc) as nid,id,title from [News] where CONTAINS([title],'"Mike*"') and class='系统管理' ) y -- 2000ms
select top 1 * from (
select row_number() over(order by id desc) as nid,id,title,class from [News] where CONTAINS([title],'"Mike*"') ) y where class='系统管理' - 22 ms
SQL 试图优化第一个查询,而采用了一个错误的执行计划。 引起这种情况的最常见原因是“统计信息过期”,更新统计(update statistics tb)通常可以修复此类问题。也可以采用索引提示,强制SQL使用某个索引;如果以上方法还无法修复,楼主可能需要检查一下原有索引设置是否恰当。楼主,可以结贴了