sql优化
0
悬赏园豆：5
[已解决问题]
解决于 2010-11-12 11:15
我有一句sql语句
select top 12 * from Product where productID not in (select top 12 productID from Product where (productName like '%2612%' or productID in (select productID from Product_Fitting where suitType like '%2612%')) and isAdjust = 0 and [status]=1 ) and (productName like '%2612%' or productID in (select productID from Product_Fitting where suitType like '%2612%')) and isAdjust = 0 and [status]=1
里面因为有了一句
productID in (select productID from Product_Fitting where suitType like '%2612%')) and isAdjust = 0 and [status]=1 ) and (productName like '%2612%' or productID in (select productID from Product_Fitting where suitType like '%2612%')
就会很卡！请问各位高手谁帮我优化一下，我这个是做分页用的
问题补充：
第一句是：select top 12 * from Product where productID not in (select top 12 productID from Product where (productName like '%2612%' or productID in (select productID from Product_Fitting where suitType like '%2612%')) and isAdjust = 0 and [status]=1 ) and (productName like '%2612%' or productID in (select productID from Product_Fitting where suitType like '%2612%')) and isAdjust = 0 and [status]=1
第二句是:productID in (select productID from Product_Fitting where suitType like '%2612%')) and isAdjust = 0 and [status]=1 ) and (productName like '%2612%' or productID in (select productID from Product_Fitting where suitType like '%2612%')
最佳答案
0
第二句：
productID in (select productID from Product_Fitting where suitType like '%2612%')) and isAdjust = 0 and [status]=1 ) and (productName like '%2612%' or productID in (select productID from Product_Fitting where suitType like '%2612%')
1.应该可以写成（where后有重复）：
isAdjust = 0 and [status]=1 ) and (productName like '%2612%' or productID in (select productID from Product_Fitting where suitType like '%2612%')
2.like后的条件是否只需向右匹配就可以了，这样可以使用索引，具体可以探讨一下需求(productName like '2612%')
3. select * 是否需要显示所有的字段信息，如果不需要，且字段很多的话，尽量只检索有用的字段。
4.通过Left join 的形式，减少多余的where条件与表检索。
精简后的Sql：
select top 12 * from Product P Left Join Product_Fitting PF ON P.productID = PF.productID
where P.productName like '%2612%' AND PF.suitType = like '%2612%'
AND P.isAdjust =0 and P.[Status] = 1
收获园豆：5
其他回答(6)
0
like '%2612%' //like加上左右通配符就会引起全表扫描。
这是一个模糊查询啊！所以要加啊！
@°潶、緤°:如果你在sqlserver上执行这样的like，优化不了，可以建立全文索引，或者使用第三方索引组件。
0
采用翻转分页试下
select top 12 * from (select top 24 * from product where **** order by productid) order by productid desc
)
0
你看看这个查询的
select count(*) from Product_Fitting where suitType like '%2612%')
select count(*) from Product_Fitting
看看这两个查询的比值是多少，如果很大，例如 30% 那么就没什么优化的余地，如果很小，例如 3% 那么可以尝试这个方法：
create index idx_Product_Fitting_suitType on Product_Fitting(suitType);
将查询该写成：
select productID from Product_Fitting where suitType in
(
select suitType from Product_Fitting like '%2612%'
)
也许可以骗过优化器使用索引
0
select *
from (
select ROW_NUMBER() OVER (ORDER BY productID) AS FRowIndex,* from Product where productID not in
(select productID from Product_Fitting where suitType like '%2612%')
and productName like '%2612%'
and isAdjust = 0 and [status]=1
)tb where FRowIndex <13
0
1. 建全文索引
2. 尽量把in换成join
3. 表里尽量少用Null值，如果是Null在做操作的时候用Isnull函数换成非Null值，多写几种等价的SQL，测一下，选最佳的那个。
0
效率比较,尽量使用INNER JOIN,用ON或者where过滤掉不需要的行
1. Exists比In好.
2. 模糊查询 suitType like '%2612%' SQL优化器无法使用Index
