但看图片上的执行计划,却是先Nested Loops,而且iesk_stocktransfer还是全表扫描,整个查询才用了"IESK_STOCKTRANSITEM_Name2"一个索引.按我的理解,应该是先执行c.resName='c' 以减少返回的数据量,再进行Nested Loops
先执行c.resName='c' -- 可以使用 IESK_STOCKTRANSITEM_Name 索引
再执行Nested Loops,应该可以使用iesk_stocktransfer_billNo索引
这种走索引方式才是最优的?---------下面是SQL语句----------
create index IESK_STOCKTRANSITEM_Name on IESK_STOCKTRANSITEM(resname);
create index iesk_stocktransfer_billNo on iesk_stocktransfer(billNo,resourceID);
create index IESK_STOCKTRANSITEM_Name2 on IESK_STOCKTRANSITEM(billId ,resid);select c.resid,c.resCode,c.resName ,m.billNo,m.resourceID,m.resourceName from
(select c.resid,c.resCode,c.resName, c.billId from IESK_STOCKTRANSITEM c where c.resName='a') c,
iesk_stocktransfer m where m.billNo = c.billId and m.resourceID = c.resid ;
SELECT C.RESID,
C.RESCODE,
C.RESNAME,
M.BILLNO,
M.RESOURCEID,
M.RESOURCENAME
FROM IESK_STOCKTRANSITEMC,
IESK_STOCKTRANSFER M
WHERE M.BILLNO = C.BILLID
AND M.RESOURCEID = C.RESID
AND C.RESNAME = 'a';
另:C.RESNAME 的值分布如何?
值分布很零散的,19万条数据,同一个值的记录不超200,并且resName='a'的记录是没有的。
下面的是mssql的执行计划,两个服务的数据是一样的,索引也一样的。
先执行c.resName='c' -- 可以使用 IESK_STOCKTRANSITEM_Name 索引
再执行Nested Loops,应该可以使用iesk_stocktransfer_billNo索引这个是原来的查询,mssql中与上图的计划一样的,IO也一样。
select c.resid,c.resCode,c.resName ,m.billNo,m.resourceID,m.resourceName from iesk_stocktransfer m ,IESK_STOCKTRANSITEM c where m.billNo = c.billId and m.resourceID = c.resid and c.resName='a';
C.RESID,
C.RESCODE,
C.RESNAME,
M.BILLNO,
M.RESOURCEID,
M.RESOURCENAME
FROM (SELECT C.RESID, C.RESCODE, C.RESNAME, C.BILLID
FROM IESK_STOCKTRANSITEM C
WHERE C.RESNAME = 'a') C,
IESK_STOCKTRANSFER M
WHERE M.BILLNO = C.BILLID
AND M.RESOURCEID = C.RESID;
修改一下SELECT /* ordered use_nl(c m)*/
C.RESID,
C.RESCODE,
C.RESNAME,
M.BILLNO,
M.RESOURCEID,
M.RESOURCENAME
FROM (SELECT C.RESID, C.RESCODE, C.RESNAME, C.BILLID
FROM IESK_STOCKTRANSITEM C
WHERE C.RESNAME = 'a') C,
IESK_STOCKTRANSFER M
WHERE M.BILLNO = C.BILLID
AND M.RESOURCEID = C.RESID;