① 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進行數據碎片處理。
② 通過分析SQL語句的執行計劃優化SQL
如何干預執行計劃
-
-
使用hints提示
基於代價的優化器是很聰明的，在絕大多數情況下它會選擇正確的優化器，減輕了DBA的負擔。但有時它也聰明反被聰明誤，選擇了很差的執行計劃，使某個語句的執行變得奇慢無比。此時就需要DBA進行人為的干預，告訴優化器使用我們指定的存取路徑或連接類型生成執行計劃，從而使語句高效的運行。例如，如果我們認為對於一個特定的語句，執行全表掃描要比執行索引掃描更有效，則我們就可以指示優化器使用全表掃描。在Oracle中，是通過為語句添加hints(提示)來實現干預優化器優化的目的。
hints是oracle提供的一種機制，用來告訴優化器按照我們的告訴它的方式生成執行計劃。我們可以用hints來實現：
1.
使用的優化器的類型
2.基於代價的優化器的優化目標，是all_rows還是first_rows。
3.
表的訪問路徑，是全表掃描，還是索引掃描，還是直接利用rowid。
4.
表之間的連接類型
5.
表之間的連接順序
6.
語句的並行程度
除了」RULE」提示外，一旦使用的別的提示，語句就會自動的改為使用CBO優化器，此時如果你的數據字典中沒有統計數據，就會使用預設的統計數據。所以建議大家如果使用CBO或HINTS提示，則最好對表和索引進行定期的分析。
如何使用hints:
Hints只應用在它們所在sql語句塊(statement
block，由select、update、delete關鍵字標識)上，對其它SQL語句或語句的其它部分沒有影響。如：對於使用union操作的2個sql語句，如果只在一個sql語句上有hints，則該hints不會影響另一個sql語句。
我們可以使用注釋(comment)來為一個語句添加hints，一個語句塊只能有一個注釋，而且注釋只能放在SELECT,
UPDATE,
or
DELETE關鍵字的後面
使用hints的語法：
{DELETE|INSERT|SELECT|UPDATE}
/*+
hint
[text]
[hint[text]]...
*/
or
{DELETE|INSERT|SELECT|UPDATE}
--+
hint
[text]
[hint[text]]...
註解：
1.DELETE、INSERT、SELECT和UPDATE是標識一個語句塊開始的關鍵字，包含提示的注釋只能出現在這些關鍵字的後面，否則提示無效。
2.「+」號表示該注釋是一個hints，該加號必須立即跟在」/*」的後面，中間不能有空格。
3.hint是下面介紹的具體提示之一，如果包含多個提示，則每個提示之間需要用一個或多個空格隔開。
4.text
是其它說明hint的注釋性文本
如果你沒有正確的指定hints，Oracle將忽略該hints，並且不會給出任何錯誤。
③ 【基於ORACLE資料庫的SQL語句優化分析】 資料庫查詢語句的優化
【摘要】隨著資料庫應用范圍及規模的不斷擴大，資料庫的性能問題逐漸顯現，優化資料庫有助於維持系統的穩定性以及運行的高效性。本文主要依據筆者在實際工作中的精坦敏拍英，對SQL語句優化的目的、SQL語句優化技術及原則進行全面分析和闡述。
【關鍵詞】ORACLE資料庫；SQL語句；優化
1前言
隨著現代化信息技術的迅猛發展，互聯網應用的日益普及，資料庫技術的影響力越來越大。作為信息系統管理的核心，資料庫的主要操作就是查詢，資料庫的應用效率在很大程度上是由查詢速度決定的，特別是對於規模較大的資料庫而言，查詢速度十分關鍵。查詢速度在SQL語句中佔有很大比重，所以，通過對查詢語句進行優化有助於促進應用系統性能及效率的進一步提升。
2SQL語句優化分析
2.1SQL語句優化的目的
對於一個資料庫而言，在確保設計無誤的前提下，要想避免出現性能問題必須確保其擁有合理的SQL語句拿喚結構。最簡單的資料庫尋找數據路徑是對SQL語句進行調整，ORACLE資料庫性能提升的主要途徑就是對SQL語句進行適當的調整。從本質上講，SQL語句優化就是確保所使用的語句可以被優化器識別，對索引進行有效利用以便控製表掃描的I/O次數，有效防止出現表搜索。用高性能的SQL語句替代低性能的SQL語句，確定最佳的數據查找路徑，盡可能使CPU時間與I/O時間保持平衡是進行優化的主要目的。在對SQL語句進行優化的過程中，以系統需求為依據確定最有可能實現性能提升的語句並進行優化。
2.2SQL語句優化技術及原則
當數據量積累到一定程度之後，對於資料庫全表SQL語句進行一次掃描，若查詢策略較好，一般只用幾秒鍾，但如果SQL語句性能較低，就需要用幾分鍾甚至更多時間。從這點不難看出，SQL語句性能對於查詢速度具有極大的影響，所以，對於應用系統而言，不僅能滿足功能的實現，還要保證SQL語句的質量。
（1）採取適宜的索引。為達到優化查詢的目的，一項重要工作就是確定相適應的索引，並嚴格依照原則加以使用，與此同時，為有效控制I/O競爭，不可以在同一個磁碟中同時建立索引和用戶表空間。
語句1：SELECT CUS_NO, CUS_NAME FROM CUSTOMER WHERE CUS_NO NOT IN
(SELECT CUS_NO FROM SERVICE);
語句2： SELECT CUS_NO, CUS_NAME FROM CUSTOMER WHERE NOT EXISTS
(SELECT * FROM SERVICE WHERE SERVICE.CUS_NO=CUSTOMER.CUS_NO);
上述兩個語句可以達到一致的查詢結果，對二者進行對比，當執行語句1時，由於ORACLE未利用CUSTOMER 表上CUS_NO索引，所以就會掃描整表，在執行語句2的過讓羨程中，ORACLE所掃描的只是CUSTOMER 表子查詢中的聯合查詢，並且使用了CUS_NO索引，因此，在執行效率方面明顯優於前者。
（2）避免在SELECT子句中出現「*」。ORACLE在進行解析時，需要按照一定順序對「*」進行轉換，該項轉換工作的進行需要對資料庫的數據字典進行查詢，勢必需要花費較多的時間，這樣就會導致較低的效率，所以，要避免在SELECT子句中出現「*」。
（3）如果必要可以利用COMMIT提交事務。ORACLE能夠自動提交DDL語句，而諸如DML等類型的語句的提交則是通過手動方式或者回滾事務實現的。在編寫應用程序的過程中，在操作諸如insert、delete以及update 等較為復雜的語境的時候，利用COMMIT提交事務可以講會話中持有的鎖加以釋放，將存在於緩存中的未經修改的數據塊進行清除，進而將系統資源予以釋放，促進系統性能的進一步提升，因此，如果有必要，可以利用COMMIT對相關事務進行提交。
（4）聯合查詢連接順序的確定。如果查詢操作涉及到多個表，基礎表應當是交叉表，所謂交叉表具體是指被其他表引用的表。連接執行效果在很大程度上受到FROM語句中表的順序的影響，對於FROM中所包含的表，ORACLE解析器進行處理的順序是由右至左，SQL語句中所選擇的基礎表會因優化器的不同而有所區別，在使用CBO的情況下，優化器會對SQL語句中各個表的物理大小以及索引狀態進行檢查，在此基礎上確定一個花費最小的執行路徑；在使用RBO的情況下，如果全部的連接條件均有索引與之相對應，那麼，FROM子句中位置最後面的表就是基礎表。
（5）IN用EXISTS取代。在對數個基礎表查詢過程中，一般需要進行表的連接。因為利用IN的子查詢過程中，ORACLE的掃描對象是全表，因此，出於提高查詢效率目的的考慮，應當將IN用EXISTS取代。
（6）在索引列中不使用計算。當通過對函數進行引用在WHERE子句中進行計算的時候，假如索引列只是函數的一部分，優化器就會針對全表進行掃描，而不會使用索引，所以，在索引列中不能使用函數。
3結語
綜上所述，隨著現代化信息技術的迅猛發展，互聯網應用的日益普及，資料庫技術的影響力越來越大。在信息量迅速激增的形勢下，資料庫優化調整成為當前所面臨的一大關鍵性問題，特別是對規模較大的資料庫而言，及時進行優化的意義更加倍重大。對於資料庫的運行性能而言，最主要的影響因素主要體現在以下幾點：資料庫系統架構的設計是否合理，資源配置是否科學以及SQL語句編寫效率等。筆者從事的是電信企業的運營分析工作，每天都要從資料庫取各種數據，可以說是離不開資料庫，所以在實踐中，我覺得嚴格遵守SQL語句優化原則及方法，並在實踐中及時總結經驗教訓，可以實現對系統響應時間的有效控制，促進運行效率的提升。
參考文獻
[1] 許開宇,胡文驊. 如何提高ORACLE資料庫應用程序的性能[J]. 計算機應用與軟體. 2002(10)
[2] 鄭耀,吳建嵐. 基於Oracle資料庫的語句優化策略[J]. 信息與電腦(理論版). 2011(07)
[3] 高攀,施蔚然. 基於Oracle資料庫的SQL語句優化[J]. 電腦編程技巧與維護. 2010(22)
[4] 鍾小權,葉猛. Oracle資料庫的SQL語句優化[J]. 計算機與現代化. 2011(03)
作者簡介：
王勇軍，男，（1981.1-），吉林通化人，就職於中國聯合網路通信有限公司長春市分公司，通信工程師，本科，研究方向：SQL使用
（作者單位：中國聯合網路通信有限公司長春市分公司）
④ 高手詳解SQL性能優化十條經驗
查詢的模糊匹配
盡量避免在一個復雜查詢裡面使用 LIKE %parm % —— 紅色標識位置的百分號會導致相關列的索引無法使用 最好不要用
解決辦法:
其實只需要對該腳本略做改進 查詢速度便會提高近百倍 改進方法如下
a 修改前台程序——把查詢條件的供應商名稱一欄由原來的文本輸入改為下拉列表 用戶模糊輸入供拿禪旁應商名稱時 直接在前台就幫忙定位到具體的供應商 這樣在調用後台程序時 這列就可以直接用等於來關聯了
b 直接修改後台——根據輸入條件 先查出符合條件的供應商 並把相關記錄保存在一個臨時表裡頭 然後再用臨時表去做復雜關聯
索引問題
在做性能跟蹤分析過程中 經常發現有不少後台程序的性能問題是因為缺少合適索引造成的 有些表甚至一個索引都沒有 這種情況往往都是因為在設計表時 沒去定義索引 而開發初期 由於表記錄很少 索引創建與否 可能對性能沒啥影響 開發人員因此也未多加重視 然一旦程序發布到生產環境 隨著時間的推移 表記錄越來越多
這時缺少索引 對性能的影響便會越來越大了
這個問題需要資料庫設計人員和開發人員共消橡同關注
法則 不要在建立的索引的數據列上進行下列操作:
◆避免對索引欄位進行計算操作◆避免在索引欄位上使用not <> !=◆避免在索引列上使用IS NULL和IS NOT NULL ◆避免在索引列上出現數據類型轉換◆避免在索引欄位上使用函數 ◆避免建立索引的列中使用空值
復雜操作
部分UPDATE SELECT 語句 寫得很復雜（經常嵌套多級子查詢）——可以考慮適當拆成幾步 先生成一些臨時數據表 再進行關聯操作
update
同一個表的修改在一個過程里出現好幾十次 如
update table set col = where col = ;update table set col = where col =象這類腳本其實可以很簡單就整合在一個UPDATE語句來完成（前些時候在協助xxx項目做性能問題分析時就發現存在這種情況）
在可以使用UNION ALL的語句里 使用了UNION
UNION 因為會將各查詢子集的記錄做比較 故比起UNION ALL 通常速度都會慢上許多 一般來說 如果使用UNION ALL能滿足要求的話 務必使用UNION ALL 還有一種情況大家可能會忽略掉 就是雖然要求幾個子集的並集需要過濾掉重復記錄 但由於腳本的特殊性 不可能存在重復記錄 這時便應該使用UNION ALL 如xx模塊的某個查詢程序就曾經存在這種情況 見 由於語句的特殊性 在這個腳本中幾個子集的記錄絕對不可能重復 故可以改用UNION ALL）
在WHERE 語句中 盡量避免對索引欄位進行計算操作
這個常識相信絕大部分開發人員都應該知道 但仍有不少人這么使用 我想其中一個最主要的原因可能是為了編寫寫簡單而損害了性能 那就不可取了
月份在對XX系統做性能分析時發現 有大量的後台程序存在類似用法 如
where trunc(create_date)=trunc(:date )雖然已對create_date 欄位建了索引 但由於加了TRUNC 使得索引無法用上 此處正確的寫法應該是
where create_date>=trunc(:date ) and create_date或者是
where create_date beeen trunc(:date ) and trunc(:date )+ /( * * )注意 因beeen 的范圍是個閉區間（greater than or equal to low value and less than or equal to high value ） 故嚴格意義上應該再減去一個趨於 的小數 這里暫且設置成減去 秒（ /( * * )） 如果不要求這么精確的話 可以襲凳略掉這步
對Where 語句的法則
避免在WHERE子句中使用in not in or 或者having
可以使用 exist 和not exist代替 in和not in
可以使用表鏈接代替 exist Having可以用where代替 如果無法代替可以分兩步處理
例子
SELECT * FROM ORDERS WHERE CUSTOMER_NAME NOT IN (SELECT CUSTOMER_NAME FROM CUSTOMER) 優化 SELECT * FROM ORDERS WHERE CUSTOMER_NAME not exist (SELECT CUSTOMER_NAME FROM CUSTOMER)不要以字元格式聲明數字 要以數字格式聲明字元值 （日期同樣）否則會使索引無效 產生全表掃描 例子使用 SELECT emp ename emp job FROM emp WHERE emp empno = ;不要使用 SELECT emp ename emp job FROM emp WHERE emp empno =
對Select語句的法則
在應用程序 包和過程中限制使用select * from table這種方式 看下面例子
使用SELECT empno ename category FROM emp WHERE empno = 而不要使用SELECT * FROM emp WHERE empno =
排序
避免使用耗費資源的操作 帶有DISTINCT UNION MINUS INTERSECT ORDER BY的SQL語句會啟動SQL引擎 執行 耗費資源的排序(SORT)功能 DISTINCT需要一次排序操作 而其他的至少需要執行兩次排序
臨時表
lishixin/Article/program/SQL/201311/16379
⑤ 請簡述項目中優化sql語句執行效率的方法,從哪些方面,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性能優化
這里分享下mysql優化的幾種方法。
1、首先在打開的軟體中，需要分別為每一個表創建 InnoDB FILE的文件。
⑦ 一條sql執行過長的時間，你如何優化，從哪些方面
1、查看sql是否涉及多表的聯表或者子查詢，如果有，看是否能進行業務拆分，相關欄位冗餘或者合並成臨時表（業務和演算法的優化）
2、涉及鏈表的查詢，是否能進行分表查詢，單表查詢之後的結果進行欄位整合
3、如果以上兩種都不能操作，非要鏈表查詢，那麼考慮對相對應的查詢條件做索引。加快查詢速度
4、針對數量大的表進行歷史表分離（如交易流水表）
5、資料庫主從分離，讀寫分離，降低讀寫針對同一表同時的壓力，至於主從同步，mysql有自帶的binlog實現 主從同步
6、explain分析sql語句，查看執行計劃，分析索引是否用上，分析掃描行數等等
7、查看mysql執行日誌，看看是否有其他方面的問題
個人理解：從根本上來說，查詢慢是佔用mysql內存比較多，那麼可以從這方面去酌手考慮
⑧ sql優化的幾種方法 如何優化
sql優化的方法是：設計資料庫表結構時，物枝歲要對表做數量級和性能影響預測和評估，表的欄位盡量都設置default值; sql條件中允許出現庫函數和左模糊查詢;單個事務的sql語句數量要有上限要求，不能前台一個提交操作，後台要去插入幾十張表的數據等。
sql優化的幾種方法
1、設計資料庫表結構時，要對表做數量級和性能影響預測和評估，表的欄位盡量都設置default值，盡量避免default為null，主要防止在執行sql查詢時直接將查詢條件設置為null或者not null而導致資料庫放棄索引，直接全表掃描;
2、sql條件中允許出現庫函數和左模糊查詢，sql條件中庫函搭念數會導致資料庫執行時放棄索引，直接全表掃描，而左模糊也是，直接就全表掃描了;
3、原則上，sql條件中避免出現<>，in，not in，exists，not exists等操作符;
4、子查詢中的實際查詢結果要設置上限要求，且子查詢必須要有索引支持，否則子查詢也去掃描全表就悲劇了;
5、單個事務的sql語句數量要有上限要求，不能前台一個提交操作，後台要去插入幾十張表的數據，那如果是千萬級用戶數，基本上就光去插入數據了;
6、同上一條類似，單條sql語句的數據影響量也要有上限要求，不能一個update操作更新了上千條數據;
7、盡量減少多表關聯的sql，如果必須使用多表關聯，也盡量減少關聯的表數量，且多表關聯時，關聯欄位必須包含在查詢索引中。多表關聯sql中盡量不要使用視圖和代理表;
8、充分利用索引，嚴禁出現表掃描。同時，創建表時也注意索引的欄位順序。
sql語言具有什麼功能
1、sql數據定義功能：能夠定義資料庫的三級模式結構，即外模式、全局模式和內模式結構。在sql中，外模式有叫做視圖(View)，全局模式簡稱模式( Schema)，內模式由系統根據資料庫模式罩睜自動實現，一般無需用戶過問。
2、sql數據操縱功能：包括對基本表和視圖的數據插入、刪除和修改，特別是具有很強的數據查詢功能。
3、sql的數據控制功能：主要是對用戶的訪問許可權加以控制，以保證系統的安全性。