注意：时间差不明显是因为数据比较少，数据多的话更好验证 
 

一、使用where字句代替having字句 
 1、select语句汇总，where字句过滤行 
 2、having字句是分组后才过滤 
 3、行被分组需要时间，降低分组的行数可以提高效率，所以使用where 字句代替havingz字句
 
验证
 
select JOB,max(sal)  from emp
group by JOB 
having JOB<>'CLERK';


select JOB,max(sal)  from emp
where JOB<>'CLERK'
group by JOB ;
 
 

二、使用<=代替< 
 empno<7901与empno<=7900的区别 
 1、使用empno<7901会定位到7901，然后再去找比7901小的数据 
 2、使用empno<=7900d自己定位到7900 
 3、在查询数据量大，并且在循环语句中使用这2个比较操作符时，区别就很明显
 
验证
 
select * from emp where empno<7901;
select * from emp where empno<=7900;
 
 
--------------------- 
 作者：单身贵族男 
 原文：https://blog.csdn.net/zhou920786312/article/details/72857428

where字句有关的优化 
 注意：时间差不明显是因为数据比较少，数据多的话更好验证 
 使用where字句代替having字句 
 1select语句汇总，where字句过滤行 
 2having字句是分组后才过滤 
 3行被分组需要时间，降低分组的行数可以提高效率，所以使用where 字句代替havingz字句
 
验证
 
select JOB,max(sal)  from emp
group by JOB 
having JOB<>'CLERK';


select JOB,max(sal)  from emp
where JOB<>'CLERK'
group by JOB ;
 
 
使用<=代替< 
 empno<7901与empno<=7900的区别 
 1使用empno<7901会定位到7901，然后再去找比7901小的数据 
 2使用empno<=7900d自己定位到7900 
 3在查询数据量大，并且在循环语句中使用这2个比较操作符时，区别就很明显
 
验证
 
select * from emp where empno<7901;
select * from emp where empno<=7900;
 

SELECT Mate_PBusinessDocNo 采购单号,Mate_PTotalQuantity	总计数量,Mate_PTotalTon	总计吨数,Mate_PTotalAmount,
	SUM(Mate_PDWholeQuantity) 细表数量, SUM(Mate_PDWholeTon) 细表吨数, SUM(Mate_PDAmount) 细表金额
FROM dbo.Mate_Purchasing p -- 采购单
LEFT JOIN dbo.Mate_PurchasingDetail pd ON p.Mate_PCode = pd.Mate_PCode-- 采购明细
GROUP BY Mate_PBusinessDocNo,Mate_PTotalQuantity,Mate_PTotalTon,Mate_PTotalAmount
HAVING Mate_PTotalQuantity <> SUM(Mate_PDWholeQuantity) OR Mate_PTotalTon <> SUM(Mate_PDWholeTon) OR Mate_PTotalAmount <> SUM(Mate_PDAmount)
		OR COUNT(Mate_PDWholeQuantity) = 0 --不存在细表数据的单据

 http://bbs.csdn.net/topics/390137775/
 
 EXPLAIN EXTENDED
 
 
 
 How to create fast database queries
 
 
 
 
 Passing parameters in MySQL: IN list vs. temporary table
 
 
 with 4 comments
 
 
 
  
 When you need to pass a list of parameters into a MySQL, there are at least two method to do this:
 
  
Issue a dynamical query which would fill an IN list with constant values
Fill a temporary table with these values then use it in a JOIN
 
  
 The second method is considered better for many reasons (you can easily reuse the values, it's more injection-proof etc.)
 
  
 Leaving all these issues aside, let's compare these methods performance-wise.
 
  
 We will create a table of 50,000 records, pass a list of 500 parameters (using both methods) and see which is faster:
 
  
 
 Table creation details
 
 
  
 We have created two stored procedures.
 
  
 The first procedure, prc_temporary, fills the temporary value using a dynamic query then issues the query using a JOIN in a loop.
 
  
 The second procedure, prc_range, just uses the IN list in a dynamic query, also in a loop.
 
  
 Let's run both procedures:
 
  
 
   
 
    
 
     view source
     print
     ?
    
 
   
 
   
 
    
 
     1.
     CALL prc_list(1000)
    
 
   
 
  
 
  
 
   
 _cnt
 1001
 1 row fetched in 0.0001s (4.1218s)
 
  
 
  
 
   
 
    
 
     view source
     print
     ?
    
 
   
 
   
 
    
 
     1.
     CALL prc_temporary(1000)
    
 
   
 
  
 
  
 
   
 _cnt
 1001
 1 row fetched in 0.0001s (1.9406s)
 
  
 
  
 However, if we change the procedures and leave but 20 parameters:
 
  
 Changing procedures here
 
 
  
 , both procedures have almost the same performance:
 
  
 
   
 
    
 
     view source
     print
     ?
    
 
   
 
   
 
    
 
     1.
     CALL prc_list(1000)
    
 
   
 
  
 
  
 
   
 _cnt
 1001
 1 row fetched in 0.0001s (0.6315s)
 
  
 
  
 
   
 
    
 
     view source
     print
     ?
    
 
   
 
   
 
    
 
     1.
     CALL prc_temporary(1000)
    
 
   
 
  
 
  
 
   
 _cnt
 1001
 1 row fetched in 0.0001s (0.6408s)
 
  
 
  
 We see that for a large list of parameters, passing them in a temporary table is much faster that as a constant list, while for small lists performance is almost the same.
 
  
 Using a temporary table is the best way to pass large arrays of parameters in MySQL.
 
  
 
   
 
    
 Share this:
 
    
 
     
 Facebook
 Twitter
 
 
    
 
   
 
  
 
 
 
 
 
  
 
   
 Written by Quassnoi
 
   
 August 18th, 2009 at 11:00 pm
 
  
 
  
 
   
 Posted in MySQL
 
  
 
 
 
 
 
  

   « 
   Hierarchical queries in MySQL: VARCHAR keys
  
 
  

   MySQL: difference between sets »
  
 
 
 
  
 
4 Responses to 'Passing parameters in MySQL: IN list vs. temporary table'
 
 
 Subscribe to comments with RSS
 
 
 
   
 
   
 
    
 You could provide an SQL example
 
   
 
   
 
     
    
 Alon
 
    
 24 Apr 13 at 15:51
 
   
 
   
 
 
   
 
   
 
    
 @Alon: click on the links (“Table creation details”) and (“Changing procedures here”)
 
   
 
   
 
     
    
 Quassnoi
 
    
 24 Apr 13 at 16:03
 
   
 
   
 
 
   
 
   
 
    
 Isn’t mysql internally creating a temp table even with IN (LIST) syntax? If you perform an EXPLAIN on any query with the IN list statement, you will see the “using temporary” message. Based on that I would think there has to be more to this performance difference. Perhaps the temp table that Mysql is using also has a unique index on the values, which would avoid redundant / duplicate joins and save on query time but lose on time to create the unique temp table values. It would be interesting to see the results of your test if you also added unique indexing on your temp table. I would think the methods would be on par at that point.
 
   
 
   
 
     
    
 Mike
 
    
 30 Jun 13 at 12:03
 
   
 
   
 
 
   
 
   
 
    
 Not sure where is a bottleneck in your procedure,
 but IN (with,constants, here) may not be a culprit.
 Maybe indexes?
 
    
 I just run a query with some 2100 constants (yes, 2 zeros – all integers) on a temp table 11.000 rows and it took some 0.4 sec to complete.
 
    
 I must add, that query had 7 tables involved 2 of which were LEFT JOINed and it was quite heavy with filters: multiple BETWEENs,”=”, even some SELECT GROUP_CONCAT() subqueries.
 On top of above, query run BOOLEAN FULLTEXT search on 2 tables, which is not speediest of them all.
 
    
 0.4 sec is not that bad.