十七、Mysql之SQL優化查詢

Hro發表於2018-12-17

一、說到SQL優化,很多人就知道利用索引,但是當利用索引之後,查詢速度還是慢怎麼解決?
  1、利用Explain檢視sql執行是否使用了索引,
  2、sql優化如下
    1)、like語句查詢時,全表查詢,索引不會生效;
    2)、where子句中使用 != 或 <>操作符,索引不會生效;
      如: SQL:SELECT id FROM A WHERE ID != 1
      優化成:SELECT id FROM A WHERE ID>1 OR ID<1
    3)、where子句中使用 IS NULL 或 IS NOT NULL,索引不會生效;
      如: SQL:SELECT id FROM A WHERE num IS NULL
      優化成num上設定預設值0,確保表中num沒有null值,
      然後SQL為:SELECT id FROM A WHERE num=0
    4)、使用union all(查詢所有,有重複) 或 nuin(查詢時,去除重複資料,效能慢)替換or;
      SQL:SELECT id FROM A WHERE num =10 or num = 20
      優化成:SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num=20
    5)、in和not in替換;
      方案一:between替換in   
        如SQL:SELECT id FROM A WHERE num in(1,2,3)
        優化成:SELECT id FROM A WHERE num between 1 and 3   
      方案二:exist替換in(in 是在記憶體中遍歷比較,exist 需要查詢資料庫,所以當B的資料量比較大時,exists效率優於in.)   
        如SQL:SELECT id FROM A WHERE num in(select num from b )
        優化成:SELECT num FROM A WHERE num exists(select 1 from B where B.num = A.num)
    6)、不要在where子句中的“=”左邊進行函式、算數運算或其他表示式運算,否則系統將可能無法正確使用索引;
      如SQL:SELECT id FROM A WHERE num/2 = 100
      優化成:SELECT id FROM A WHERE num = 1002
    7)、用具體的欄位列表替換"
";
    8)、使用“臨時表”暫存中間結果,避免多次掃描主表;
    9)、limit分頁優化,當偏移量特別時,limit效率會非常低;
      如:SELECT id FROM A LIMIT 90000,10
      優化後:select id from A order by id limit 90000,10
    10)、批量插入優化 ;
      INSERT into person(name,age) values('test',1),('test',2),('test',3)
    11)、Inner join 和 left join、right join、子查詢。
      查詢效能Inner join>left join/right join>子查詢

相關文章