sql優化查詢

㈢ SQL執行與優化

SQL優化

執行計劃,表關聯查詢順序,優化策略與思路

下面再向前走一些，容我根據自己的認識說一下查詢執行的流程是怎樣的：

1.連接

1.1客戶端發起一條Query請求，監聽客戶端的『連接管理模塊』接收請求

1.2將請求轉發到『連接進/線程模塊』

1.3調用『用戶模塊』來進行授權檢查

1.4通過檢查後，『連接進/線程模塊』從『線程連接池』中取出空閑的被緩存的連接線程和客戶端請求對接，如果失敗則創建一個新的連接請求

2.處理

2.1先查詢緩存，檢查Query語句是否完全匹配，接著再檢查是否具有許可權，都成功則直接取數據返回

2.2上一步有失敗則轉交給『命令解析器』，經過詞法分析，語法分析後生成解析樹

2.3接下來是預處理階段，處理解析器無法解決的語義，檢查許可權等，生成新的解析樹

2.4再轉交給對應的模塊處理

2.5如果是SELECT查詢還會經由『查詢優化器』做大量的優化，生成執行計劃

2.6模塊收到請求後，通過『訪問控制模塊』檢查所連接的用戶是否有訪問目標表和目標欄位的許可權

2.7有則調用『表管理模塊』，先是查看table cache中是否存在，有則直接對應的表和獲取鎖，否則重新打開表文件

2.8根據表的meta數據，獲取表的存儲引擎類型等信息，通過介面調用對應的存儲引擎處理

2.9上述過程中產生數據變化的時候，若打開日誌功能，則會記錄到相應二進制日誌文件中

3.結果

3.1Query請求完成後，將結果集返回給『連接進/線程模塊』

3.2返回的也可以是相應的狀態標識，如成功或失敗等

3.3『連接進/線程模塊』進行後續的清理工作，並繼續等待請求或斷開與客戶端的連接

接下來再走一步，讓我們看看一條SQL語句的前世今生。

首先看一下示例語句

示例語句

執行順序

SQL解析

1. FROM

當涉及多個表的時候，左邊表的輸出會作為右邊表的輸入，之後會生成一個虛擬表VT1。

(1-J1)笛卡爾積

計算兩個相關聯表的笛卡爾積(CROSS JOIN) ，生成虛擬表VT1-J1。

兩次全表掃描

哈希索引,查找復雜度都是 O(1) 。

2. WHERE

對VT1過程中生成的臨時表進行過濾，滿足WHERE子句的列被插入到VT2表中。

注意：

此時因為分組，不能使用聚合運算；也不能使用SELECT中創建的別名；

與ON的區別：

如果有外部列，ON針對過濾的是關聯表，主表（保留表）會返回所有的列；

如果沒有添加外部列，兩者的效果是一樣的；

應用：

對主表的過濾應該放在WHERE；

對於關聯表，先條件查詢後連接則用ON，先連接後條件查詢則用WHERE；

hash join 哈希連接 驅動表和被驅動表都只會訪問0次或1次

應用場景:一個大表一個小表/表上沒有索引/返回結果集比較大

3. GROUP BY

這個子句會把VT2中生成的表按照GROUP BY中的列進行分組。生成VT3表。

注意：

其後處理過程的語句，如SELECT,HAVING，所用到的列必須包含在GROUP BY中，對於沒有出現的，得用聚合函數；

原因：

GROUP BY改變了對表的引用，將其轉換為新的引用方式，能夠對其進行下一級邏輯操作的列會減少；

原作者的理解是：

根據分組欄位，將具有相同分組欄位的記錄歸並成一條記錄，因為每一個分組只能返回一條記錄，除非是被過濾掉了，而不在分組欄位裡面的欄位可能會有多個值，多個值是無法放進一條記錄的，所以必須通過聚合函數將這些具有多值的列轉換成單值；

GROUP BY 重新聚合查詢

4. HAVING

這個子句對VT3表中的不同的組進行過濾，只作用於分組後的數據，滿足HAVING條件的子句被加入到VT4表中。

7.LIMIT

LIMIT子句從上一步得到的VT6虛擬表中選出從指定位置開始的指定行數據。

注意：

offset和rows的正負帶來的影響；

當偏移量很大時效率是很低的，可以這么做：

採用子查詢的方式優化，在子查詢里先從索引獲取到最大id，然後倒序排，再取N行結果集

採用INNER JOIN優化，JOIN子句里也優先從索引獲取ID列表，然後直接關聯查詢獲得最終結果

當前未用到索引,

三次full scan , table1 AS a / table2 AS b / GROUP BY

盡量少做重復的工作

控制同一語句的多次執/減少多次的數據轉換/

杜絕不必要的子查詢和連接表，子查詢在執行計劃一般解釋成外連接，多餘的連接表帶來額外的開銷

關於臨時表和表變數的選擇

臨時表產生使用SELECT INTO和CREATE TABLE + INSERT INTO的選擇，一般情況下，SELECT INTO會比CREATE TABLE + INSERT INTO的方法快很多，但是SELECT INTO會鎖定TEMPDB的系統表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用戶並發環境下，容易阻塞其他進程，所以建議，在並發系統中，盡量使用CREATE TABLE + INSERT INTO，而大數據量的單個語句使用中，使用SELECT INTO。

子查詢的用法

相關子查詢可以用IN、NOT IN、EXISTS、NOT EXISTS引入

NOT IN、NOT EXISTS的相關子查詢可以改用LEFT JOIN代替寫法

如果保證子查詢沒有重復 ，IN、EXISTS的相關子查詢可以用INNER JOIN 代替

IN``的相關子查詢用EXISTS代替

不要用 COUNT (*)的子查詢判斷是否存在記錄，最好用 LEFT` `JOIN 或者EXISTS，比如有人寫這樣的語句：

建立索引後，並不是每個查詢都會使用索引，在使用索引的情況下，索引的使用效率也會有很大的差別。只要我們在查詢語句中沒有強制指定索引，

不要對索引欄位進行運算，而要想辦法做變換

不要對索引欄位進行格式轉換

不要對索引欄位使用函數

不要對索引欄位進行多欄位連接

join關聯查詢的計算是很復雜的，特別是數據量比較大的情況下，實際情況還是拆解較快的

Join拆解的核心就是利用In關鍵字

要麼用空間換時間，要麼用時間換空間

多表連接的連接條件對索引的選擇有著重要的意義，所以我們在寫連接條件條件的時候需要特別注意。

A、多表連接的時候，連接條件必須寫全，寧可重復，不要缺漏。

B、連接條件盡量使用聚集索引

C、注意ON、WHERE和HAVING部分條件的區別

ON是最先執行， WHERE次之，HAVING最後，因為ON是先把不符合條件的記錄過濾後才進行統計，它就可以減少中間運算要處理的數據，按理說應該速度是最快的，WHERE也應該比 HAVING快點的，因為它過濾數據後才進行SUM，在兩個表聯接時才用ON的，所以在一個表的時候，就剩下WHERE跟HAVING比較了

考慮聯接優先順序：

(1)INNER JOIN

(2)LEFT JOIN (註：RIGHT JOIN 用 LEFT JOIN 替代)

(3)CROSS JOIN

索引並不適用於所有情況：a.少量數據；b.頻繁進行改動的欄位，不適合做索引；c.很少使用的欄位，不需要加索引

索引會提高數據查詢效率，但是會降低「增、刪、改」的效率。當不使用索引的時候，我們進行數據的增刪改，只需要操作源表即可，但是當我們添加索引後，不僅需要修改源表，也需要再次修改索引，很麻煩。

先執行順序, 是否走索引, 有無類型轉換

18000 字的SQL優化大全

步步深入：MySQL架構總覽->查詢執行流程->SQL解析順序

MySQL索引總結（4）——btree與hash區別

㈣ SQL優化（二）

SQL優化一： sql優化（一）

上片文章已經詳細介紹了explain各個欄位的含義，以及什麼情況應該建立索引，什麼情況不需要建立索引以及sql語句性能的判斷依據，接下來我介紹下如何合理的建立索引。

sql語句：select id，author_id from article where category_id = 1 and comments>1 order by views desc limit 1;

分析：首先我們根據where後面的條件建立符合索引，然後根據order by後面的欄位建立索引，因此建立索引idx_article_ccv，即以（category_id，comments，views）數據列建立復合索引，但由於comments是一個范圍，按照BTree索引的原理，先排序category_id,如果遇到相同的category_id則再排序comments，如果遇到相同的comments則再排序views，又因為comments欄位在復合索引里處於中間位置，而comments>1是一個條件（是一個范圍值），在復合索引的一個范圍值的數據列後面的索引全部失效，mysql無法利用索引再對後面的views部分進行檢索，也就是說views無法按照索引排序，所以explain下此sql語句，type為range，extra使用的是Using filesort，這是比較糟糕的。所以我們放棄comments這個范圍欄位，建立索引idx_article_cv，即以（category_id，views）數據列建立復合索引，explain 此sql，type變成了ref，extra的using filesort也變成了using index，這就變得好多了。

索引：idx_article_cv，即以（category_id，views）數據列建立復合索引

前段時間做了一個銷售精細化項目，是公司crm項目的一個大模塊，大致就是為銷售人員制定指標，實現銷售目標從區域到團到業務員到客戶，實時跟蹤業務員所負責客戶的下單量的情況。這就存在許多關聯關系，區域-團，團-業務員，業務員-客戶，這使得sql常常需要關聯多張表。

sql語句：SELECT

tu.fuserid,

tu.faccount,

tu.fphone,

tu.fcertificationtype,

tu.fcertificatename,

tu.fkeyarea,

tu.fkeyareatext,

DATE_FORMAT(tcr.fupdatetime,'%Y-%m-%d %H:%i:%s') as fupdatetime,

tag.forggroupid,

tag.forggroupname,

tug.forguserid,

tug.fusername,

tug.fuserphone,

tag.fcitycode

FROM t_finedt_user AS tu

LEFT JOIN t_finedt_customer_relation AS tcr

ON tu.fuserid = tcr.fuserid

LEFT JOIN t_finedt_usergroup AS tug

ON tcr.forguserid = tug.forguserid

and tcr.forggroupid = tug.forggroupid

LEFT JOIN t_finedt_areagroup AS tag

ON tug.forggroupid = tag.forggroupid

where tu.fkeyarea=? and tu.fuserid=? and tug.forggroupid = ?

分析：上面的sql是左連接，左邊的表一定是全表查詢，所以要建立右邊表對應關聯欄位的索引，在表t_finedt_user上建立tu_fuserid_fkeyarea索引，即以(fuserid，fkeyarea)欄位建立索引，在表t_finedt_customer_relation 上建立tcr_forguserid_forggroupid索引，即以（forguserid，forggroupid）欄位建立索引，在表t_finedt_usergroup 上建立tug_forguserid_forggroupid索引，即以（forguserid,forggroupid)欄位建立索引，在表t_finedt_areagroup上建立tag_forggroupid索引，即以（forggroupid)欄位建立索引。建立索引後，sql查詢速度明顯快了很多

索引：tcr_forguserid_forggroupid，tu_fuserid_fkeyarea，tug_forguserid_forggroupid，tag_forggroupid

1、盡可能減少join語句中的NestedLoop的循環次數，永遠用小結果集驅動大結果集

2、優先優化NestedLoop的內層循環

3、保證join語句總被驅動表上的join欄位已經被索引

4、當無法保證被驅動表join條件欄位被索引，且內存資源充足的前提下，不要太吝嗇joinBuffer的設置

1、全值匹配我最愛

2、最佳左前綴原則——如果索引了多列，要遵守最左前綴原則，指的是查詢從索引的最左前列開始並且不跳過索引中的列

3、並在索引列上做任何操作（計算、函數、自動or手動類型轉換），這些會導致索引失效而轉向全表掃描

4、存儲引擎不能使用索引中范圍條件右邊的列，范圍之後的索引全失效

5、盡量使用覆蓋索引（之訪問索引的查詢（索引列和查詢的列一致）），減少select *

6、mysql在使用不等於（！=、>、<）的時候無法使用索引會導致全表掃描。

7、is null、is not null也無法使用索引。

8、like以通配符開頭（"%abc.."），mysql索引失效也會變成全表掃描的操作。

9、字元串不加單引號也會引起索引失效

10、少用or，用它來連接時會索引失效。

1、對於單值索引，盡量選擇針對當前query過濾性更好的索引

2、在選擇組合索引的時候，當前query中過濾性最好的欄位在索引欄位順序中，位置越靠前越好

3、在選擇組合索引的時候，盡量選擇盡可能包含當前query中的where字句中更多欄位的索引

4、盡可能通過分析統計信息和調整query的寫法來達到選擇合適索引的目的。

全值匹配我最愛，最左前綴要遵守

帶頭大哥不能死，中間兄弟不能斷

索引列上少計算，范圍之後全失效

like百分寫最右，覆蓋索引不寫里

不等空值還有or，索引失效要少用

var引號不可丟，sql高級也不難

㈤ sql優化的N種方法

1.SQL語句中IN包含的值不應過多:
例如：select id from t where num in(1,2,3) 對於連續的數值，能用between就不要用in了； 實測速度差距不是很大.

2.SELECT語句務必指明欄位名稱:
禁止用 * 來查詢 ,禁止用 * 來查詢 ,禁止用 * 來查詢 , 查找哪個欄位,就寫具體的欄位.

select * from user_test WHERE address=15988;
select address from user_test WHERE address=15988;

3.只查詢一條數據的時候，使用limit 1
【這個很有用】

4.避免在where子句中對欄位進行null值判斷:
【實測：null值的判斷依然走了索引】
explain select uid from user_test WHERE phone is null;

5.避免在where子句中對欄位進行表達式操作:

6.對於聯合索引來說，要遵守最左前綴法則:
例如組合索引(id,name,sex) 使用的時候,可以id 或者id,name . 禁止直接name,或者sex.會導致聯合索引失敗

注意: id, name,sex 這三個欄位填寫順序不會有影響, mysql會自動優化成最左匹配的順序.

前三條sql都能命中索引,中間兩條由於不符合最左匹配原則,索引失效.

最後一條sql 由於有最左索引id 所以索引部分成功,部分失效. id欄位索引使用成功.

7.盡量使用inner join，避免left join：
如果連接方式是inner join，在沒有其他過濾條件的情況下MySQL會自動選擇小表作為驅動表，但是left join在驅動表的選擇上遵循的是左邊驅動右邊的原則，即left join左邊的表名為驅動表。
【實測：不是很准確，具體用explain測試】

8.注意范圍查詢語句:
對於聯合索引來說，如果存在范圍查詢，比如between、>、<等條件時，會造成後面的索引欄位失效。

解決辦法: 業務允許的情況下,使用 >= 或者<= 這樣不影響索引的使用.

explain select * from user_test where uid=10 and name='張三' and phone='13527748096';
explain select * from user_test where uid between( 1 and 10) and name ='張三' and phone='13527748096';

9.不建議使用%前綴模糊查詢:
例如 : LIKE「%name」或者LIKE「%name%」，這種查詢會導致索引失效而進行全表掃描。但是可以使用LIKE 「name%」。

explain select * from user_test where uid=10 and uid like "%1" ;
explain select * from user_test where uid=10 and uid like "1%" ;

10.在 where 子句中使用 or 來連接條件，如果or連接的條件有一方沒有索引,將導致引擎放棄使用索引而進行全表掃描
解決辦法: 將or連接的雙方都建立索引,就可以使用.

explain select * from user_test where uid=10 or name='張三';

11.應盡量避免在where子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。(此處存在疑點,我本人測試的時候,發現索引還是能使用到)

12.字元串類型的欄位 查詢的時候如果不加引號'' ,會導致自動進行隱式轉換,然後索引失效

㈥ 怎樣提高SQL查詢效率

1. SQL優化的原則是：將一次操作需要讀取的BLOCK數減到最低,即在最短的時間達到最大的數據吞吐量。
調整不良SQL通常可以從以下幾點切入：
? 檢查不良的SQL，考慮其寫法是否還有可優化內容
? 檢查子查詢 考慮SQL子查詢是否可以用簡單連接的方式進行重新書寫
? 檢查優化索引的使用
? 考慮資料庫的優化器

2. 避免出現SELECT * FROM table 語句，要明確查出的欄位。

3. 在一個SQL語句中，如果一個where條件過濾的資料庫記錄越多，定位越准確，則該where條件越應該前移。

4. 查詢時盡可能使用索引覆蓋。即對SELECT的欄位建立復合索引，這樣查詢時只進行索引掃描，不讀取數據塊。

5. 在判斷有無符合條件的記錄時建議不要用SELECT COUNT （*）和select top 1 語句。

6. 使用內層限定原則，在拼寫SQL語句時，將查詢條件分解、分類，並盡量在SQL語句的最里層進行限定，以減少數據的處理量。

7. 應絕對避免在order by子句中使用表達式。

8. 如果需要從關聯表讀數據，關聯的表一般不要超過7個。

9. 小心使用 IN 和 OR，需要注意In集合中的數據量。建議集合中的數據不超過200個。

10. <> 用 < 、 > 代替，>用>=代替，<用<=代替，這樣可以有效的利用索引。

11. 在查詢時盡量減少對多餘數據的讀取包括多餘的列與多餘的行。

12. 對於復合索引要注意，例如在建立復合索引時列的順序是F1，F2，F3，則在where或order by子句中這些欄位出現的順序要與建立索引時的欄位順序一致，且必須包含第一列。只能是F1或F1，F2或F1，F2，F3。否則不會用到該索引。

13. 多表關聯查詢時，寫法必須遵循以下原則，這樣做有利於建立索引，提高查詢效率。格式如下select sum（table1.je） from table1 table1, table2 table2, table3 table3 where (table1的等值條件（=）) and (table1的非等值條件) and (table2與table1的關聯條件) and (table2的等值條件) and (table2的非等值條件) and (table3與table2的關聯條件) and (table3的等值條件) and (table3的非等值條件)。
注:關於多表查詢時from 後面表的出現順序對效率的影響還有待研究。

14. 子查詢問題。對於能用連接方式或者視圖方式實現的功能，不要用子查詢。例如：select name from customer where customer_id in ( select customer_id from order where money>1000)。應該用如下語句代替：select name from customer inner join order on customer.customer_id=order.customer_id where order.money>100。

15. 在WHERE 子句中，避免對列的四則運算，特別是where 條件的左邊，嚴禁使用運算與函數對列進行處理。比如有些地方 substring 可以用like代替。

16. 如果在語句中有not in（in）操作，應考慮用not exists（exists）來重寫,最好的辦法是使用外連接實現。

17. 對一個業務過程的處理，應該使事物的開始與結束之間的時間間隔越短越好，原則上做到資料庫的讀操作在前面完成，資料庫寫操作在後面完成，避免交叉。

18. 請小心不要對過多的列使用列函數和order by,group by等，謹慎使用disti軟體開發t。

19. 用union all 代替 union，資料庫執行union操作，首先先分別執行union兩端的查詢，將其放在臨時表中，然後在對其進行排序，過濾重復的記錄。
當已知的業務邏輯決定query A和query B中不會有重復記錄時，應該用union all代替union，以提高查詢效率。

㈦ Sql優化-多like模糊查詢及根據時間排序

2020-04-21

記錄一次sql優化記錄：

環境：用的mysql版本  select Version();

優化過程：

用的是兩張表聯查，四個條件like查詢 ，根據時間排序降序

其中A，B表沒有大欄位，A表20萬多數據，B表50萬多條數據。語句如下：

EXPLAIN

SELECT A.bondId,A.sname,A.cname,A.secuCode,A. ISSUER,A.guarantor,B.underwriter AS infoSource

  FROM   A

  LEFT JOIN  B ON B.bondId = A.bondId

 WHERE B.agentType = 1

 AND B.underwriter = '有限公司'

 AND A.startDate <= '2020-04-21 18:02:10'

 AND A.endDate >= '2020-04-21 18:02:10'

 AND (

 A.cname LIKE '%%'  OR A.sname LIKE '%%'  OR A.secuCode LIKE '%%'

 OR A. ISSUER LIKE '%%'OR A.guarantor LIKE '%%')

 AND A.isValid = 1

 ORDER BY A.startDate DESC

 LIMIT 0, 20

這是2個表都沒有加索引的情況，從explain來看結果非常糟糕，都是全表掃描，並且產生臨時表同時有文件排序，效率肯定非常低。

首先嘗試在B表上建立一個聯合索引

可以考慮從關聯欄位及where條件欄位考慮（bondId, underwriter, agentType）

建一個聯合索引，試試。

ALTER TABLE B ADD INDEX bua_index(bondId, underwriter, agentType)

再explain看：

可以看到B表用到了我們剛剛建的聯合索引，並且額外信息是Using index ，type是ref級別的，效果比較理想，再來看A表。

Where條件中有多個like，這種情況下一般索引都是不可用的，所以必須用覆蓋索引解決，

由於又根據startDate排序，所以嘗試根據如下欄位建立聯合索引，同時查詢的欄位就是索引中的欄位（startDate, endDate,cname, sname, secuCode, issuer, guarantor）

ALTER TABLE A ADD INDEX index_scssig(startDate, endDate,cname, sname, secuCode, issuer, guarantor)

再次explain看看效果：

這樣乍看上去A表也用到了剛剛建的聯合索引，並且type是range級別雖然比ref差點，按理說應該也還可以，但是我執行sql語句，效率還是非常差，查詢耗時達到8s，並且偶爾還不止這個時間

究其原因，雖然使用了索引，但是extra裡面是Using index condition&Using where

回表操作了，我在想如果將extra優化成Using index效率肯定沒問題

故再進一步優化，還是從索引入手

在聯合索引上添加2個欄位isValid, bondId 再試試

ALTER TABLE A ADD INDEX index_scssig(isvalid,startDate, endDate,cname, sname, secuCode, issuer, guarantor,bondId)

再次explain:

這個結果就是我想要的，然後執行sql看看效率：

已經提升了很多了，但是我試了別的查詢條件偶爾時間會到3，4s,懷疑和自己的機器有關

在這這種多個like的or查詢mysql本身並不擅長，無奈坑爹的需要需要這樣，可能效率並不是非常的高，優化成這樣可以接受了。

最近對以前項目的慢查詢進行sql調優，感覺性能的下降往往還是sql語句及索引的建立的問題，explain是很有幫助，正確優化還是能極大提升效率的。

㈧ 【DB2】SQL優化

於我來說，我喜歡技術，不偏執於某一類開發語言，願意花時間精力去解決問題。

1.去除在謂詞列上編寫的任何標量函數

優化前：(耗時3.1s)

優化後：(耗時0.922s)

總結：

DB2可以選擇使用START_DATE上的列索引，但是在列上使用了函數後，DB2就無法使用列索引了，從而導致查詢效率變低。

2.去除在謂詞列上編寫的任何數學運算

優化前：(耗時10.265)

優化後：(耗時3.39s)

總結：

DB2查詢時候，會優先選擇列CONTRACT_AMT上的索引，如果直接對列CONTRACT_AMT應用數學運算，DB2就無法使用索引了。一定要做到：列本身(不加數學運算)放在操作符的一邊，而所有的計算都放在另外一邊。

3.SQL語句中指定查詢列

優化前：(耗時13.15s)

優化後：(耗時2.922s)

總結：

如果Select包含不需要的列，優化工具會選擇Indexonly=』N』,這會強制DB2必須進入數據頁來得到所請求的特定列，這就要求更多的I/O操作，梁歪，這些多餘的列可能是某些排序的部分，這樣一來就需要和傳遞一個更大的排序文件，相應的會使排序成本更高。

4.盡可能不使用distinct

優化前：(耗時0.687s)

優化後：(耗時0.437s)

總結：

在測試distinct與group by性能的過程中，在列CST_ID上添加索引後，發現group by 確實比distinct快一些，但是在數據分布比較離散的情況下使用group by ，比較集中的情況下使用distinct.表數據量較少的情況隨便使用哪個都一樣， 不管選擇誰，都要建立索引

5.Exists、in、not in 、not exists的使用場景選擇

5.1 in跟exists的區別：

例如：表A(小表)，表B(大表)

優化前：(耗時1.93s)

優化後：(耗時1.125s)

相反的，

優化前：(耗時1.9s)

優化後：(耗時1.0s)

總結：

in是把外表和內表作hash連接，而exists是對外表作loop循環，每次loop循環再對內表進行查詢，一直以來認為exists比in效率高的說法是不準確的。 如果查詢的兩個表大小相當，那麼用in和exists差別不大；如果兩個表中一個較小一個較大，則子查詢表大的用exists，子查詢表小的用in；

簡稱：子大Exists,子小in

5.2 not in 與 not exists區別：

如果查詢語句使用了not in，那麼對內外表都進行全表掃描，沒有用到索引；而not exists的子查詢依然能用到表上的索引。所以無論哪個表大，用not exists都比not in 要快。

6.盡可能使用union all來代替union

優化前：(耗時15.344s)

優化後：(耗時2.719s)

總結：

在union中，DB2最後會自動執行一個排序來消除重復值，這樣是很耗費資源的，所以在不需要去重復的情況下，盡可能使用UNION ALL 代替union

N.模板

優化前：(耗時3.1s)

優化後：(耗時0.922s)

總結：

㈨ 資料庫牛人是如何進行SQL優化的

SQL 查詢優化減少了查詢所需的資源並提高了整體系統性能，在本文中，我們將討論 SQL 查詢優化、它是如何完成的、最佳實踐及其重要性。

SQL 查詢優化是編寫高效的 SQL 查詢，並在執行時間和資料庫表示方面 提高查詢性能 的迭代過程，查詢優化是幾個關系資料庫管理系統 (RDBMS) 的一項重要功能。

查詢是對來自資料庫的數據或信息的問題或請求，需要編寫一組資料庫可以理解的預定義代碼，結構化查詢語言 (SQL) 和其他查詢語言旨在檢索或管理關系資料庫中的數據。

資料庫中的查詢可以用許多不同的結構編寫，並且可以通過不同的演算法執行，寫得不好的查詢會消耗更多的系統資源，執行時間長，並可能導致服務損失，一個完美的查詢可以減少執行時間並帶來最佳的 SQL 性能。

SQL查詢優化的主要目的是：

確保查詢處於最佳路徑和形式非常重要，SQL 查詢過程需要最好的執行計劃和計算資源，因為它們是 CPU 密集型操作，SQL 查詢優化通過三個基本步驟完成：

解析確保查詢在語法和語義上都是正確的，如果查詢語法正確，則將其轉換為表達式並傳遞到下一步。

優化在查詢性能中扮演著重要的角色，並且可能很困難，任何考慮優化的查詢執行計劃都必須返回與之前相同的結果，但優化後的性能應該會有所提高。

SQL 查詢優化包括以下基本任務：

最後，查詢執行涉及將查詢優化步驟生成的計劃轉化為操作，如果沒有發生錯誤，此步驟將返回結果給用戶。

一旦用戶確定某個查詢需要改進以優化 SQL 性能，他們就可以選擇任何優化方法——優化 SQL 查詢性能的方法有很多種，下面介紹了一些最佳實踐。

提高查詢性能的一種簡單方法是將 SELECT * 替換為實際的列名，當開發人員在表中使用 SELECT * 語句時，它會讀取每一列的可用數據。

使用 SELECT 欄位名 FROM 而不是 SELECT * FROM 時，可以縮小查詢期間從表中提取的數據的范圍，這有助於提高查詢速度。

循環中的 SQL 查詢運行不止一次，這會顯著降低運行速度，這些查詢會不必要地消耗內存、CPU 能力和帶寬，這會影響性能，尤其是當 SQL 伺服器不在本地計算機上時，刪除循環內的查詢可提高整體查詢性能。

使用SQL 伺服器索引可以減少運行時間並更快地檢索數據，可以使用聚集和非聚集 SQL 索引來優化 SQL 查詢，非聚集索引單獨存儲，需要更多的磁碟空間，因此，了解何時使用索引很重要。

該OLAP功能「擴展了SQL解析函數的語法。」 SQL 中的 OLAP 功能更快且易於使用，熟悉這些語法的 SQL 開發人員和 DBA 可以很容易地適應和使用它們。

OLAP 函數可以創建所有標准計算度量，例如排名、移動聚合、份額、期初至今、前期和未來期、平行期等。

查詢優化器使用統計信息來確定如何最好地連接表、何時應該使用索引以及如何訪問這些索引等，無論是手動還是自動，SQL 伺服器統計信息都應該保持最新。

過時的 SQL Server 統計信息會影響表、索引或列統計信息，並導致查詢計劃性能不佳。

SQL 查詢優化可以輕松提高系統性能，從而節省成本，優化 SQL 查詢可以提高運營效率並加快性能，從而提高系統上線進度。

SQL 查詢優化很重要，原因有很多，包括：

組織可以通過更快的響應時間獲得可靠的數據訪問和高水平的性能，優化 SQL 查詢不僅可以提高整體系統性能，還可以提高組織的聲譽，最終，SQL 查詢優化的最佳實踐幫助用戶獲得准確、快速的資料庫結果。

㈩ SQL優化萬能公式：5 大步驟 + 10 個案例

在應用開發的早期，數據量少，開發人員開發功能時更重視功能上的實現，隨著生產數據的增長，很多SQL語句開始暴露出性能問題，對生產的影響也越來越大，有時可能這些有問題的SQL就是整個系統性能的瓶頸。

1、通過慢查日誌等定位那些執行效率較低的SQL語句

2、explain 分析SQL的執行計劃

type由上至下，效率越來越高

Extra

3、show profile 分析

了解SQL執行的線程的狀態及消耗的時間。默認是關閉的，開啟語句「set profiling = 1;」

4、trace

trace分析優化器如何選擇執行計劃，通過trace文件能夠進一步了解為什麼優惠券選擇A執行計劃而不選擇B執行計劃。

5、確定問題並採用相應的措施

案例1、最左匹配

索引

SQL語句

查詢匹配從左往右匹配，要使用order_no走索引，必須查詢條件攜帶shop_id或者索引( shop_id , order_no )調換前後順序

案例2、隱式轉換

索引

SQL語句

隱式轉換相當於在索引上做運算，會讓索引失效。mobile是字元類型，使用了數字，應該使用字元串匹配，否則MySQL會用到隱式替換，導致索引失效。

案例3、大分頁

索引

SQL語句

對於大分頁的場景，可以優先讓產品優化需求，如果沒有優化的，有如下兩種優化方式， 一種是把上一次的最後一條數據，也即上面的c傳過來，然後做「c < xxx」處理，但是這種一般需要改介面協議，並不一定可行。另一種是採用延遲關聯的方式進行處理，減少SQL回表，但是要記得索引需要完全覆蓋才有效果，SQL改動如下

案例4、in + order by

索引

SQL語句

in查詢在MySQL底層是通過n*m的方式去搜索，類似union，但是效率比union高。in查詢在進行cost代價計算時（代價 = 元組數 * IO平均值），是通過將in包含的數值，一條條去查詢獲取元組數的，因此這個計算過程會比較的慢，所以MySQL設置了個臨界值(eq_range_index_pe_limit)，5.6之後超過這個臨界值後該列的cost就不參與計算了。因此會導致執行計劃選擇不準確。默認是200，即in條件超過了200個數據，會導致in的代價計算存在問題，可能會導致Mysql選擇的索引不準確。

處理方式，可以( order_status , created_at )互換前後順序，並且調整SQL為延遲關聯。

案例5、范圍查詢阻斷，後續欄位不能走索引

索引

SQL語句

范圍查詢還有「IN、between」

案例6、不等於、不包含不能用到索引的快速搜索。（可以用到ICP）

在索引上，避免使用NOT、!=、>、!、NOT EXISTS、NOT IN、NOT LIKE等

案例7、優化器選擇不使用索引的情況

如果要求訪問的數據量很小，則優化器還是會選擇輔助索引，但是當訪問的數據占整個表中數據的蠻大一部分時（一般是20%左右），優化器會選擇通過聚集索引來查找數據。

查詢出所有未支付的訂單，一般這種訂單是很少的，即使建了索引，也沒法使用索引。

案例8、復雜查詢

如果是統計某些數據，可能改用數倉進行解決；如果是業務上就有那麼復雜的查詢，可能就不建議繼續走SQL了，而是採用其他的方式進行解決，比如使用ES等進行解決。

案例9、asc和desc混用

desc 和asc混用時會導致索引失效

案例10、大數據

對於推送業務的數據存儲，可能數據量會很大，如果在方案的選擇上，最終選擇存儲在MySQL上，並且做7天等有效期的保存。那麼需要注意，頻繁的清理數據，會照成數據碎片，需要聯系DBA進行數據碎片處理。