SqlServer中Index Seek的匹配规则

最新推荐文章于 2023-01-14 17:08:28 发布

qq_18932003

最新推荐文章于 2023-01-14 17:08:28 发布

阅读量212

点赞数

分类专栏： MSSQL数据库

本文链接：https://blog.csdn.net/qq_18932003/article/details/114334088

版权

MSSQL数据库专栏收录该内容

153 篇文章 6 订阅

订阅专栏

SqlServer中Index Seek的匹配规则（一）
https://www.cnblogs.com/OpenCoder/p/5804794.html

   select * from
   (select billno,seq,srcbillno,srcbillseq,pkid ,materielid,factorypkid
   FROM DJDL_MES_JGONE.dbo.PM_manubill_main M1( NOLOCK )
WHERE factorypkid=1 AND createdate BETWEEN '2020-09-20' AND '2020-09-26') PMM
   INNER JOIN
   (select id,createdate,States,mainpkid ,createmanname //再加入createmanname一个字段就会卡死,查询变得很慢,不会命中索引
   -- ,pkid,gid,zapdeltag,seq,factorymaterielpkid,planqty,editmanname,editdate
   FROM DJDL_MES_JGONE.dbo.pm_manubill_flowcard M2(NOLOCK)) PMF ON pmm.pkid = pmf.mainpkid

优化
select
*
from
(select billno,seq,srcbillno,srcbillseq,pkid ,materielid,factorypkid from DJDL_MES_JGONE.dbo.PM_manubill_main ( NOLOCK )
where factorypkid =1
) pmm
INNER JOIN
( select id,createdate,States,mainpkid
--,createmanname ,gid,zapdeltag,seq,factorymaterielpkid,planqty,editmanname,editdate 再加入一个字段就会卡死,查询变得很慢
FROM DJDL_MES_JGONE.dbo.pm_manubill_flowcard
( NOLOCK ) WHERE createdate BETWEEN '2020-09-20' AND '2020-09-26') PMF ON pmm.pkid = pmf.mainpkid

把createmanname加到索引包含列确实快好多