優化復雜的sql

1. 復雜慢sql語句如何優化

很簡單啊，優先索引，第二結構，第三演算法。
索引最簡單，如果是SQL server客戶端或者toad可以提示有哪些需要進行優化的地方。
結構就是針對要查詢的值，盡量集中到一個表，減少串表，函數查詢，左鏈的表欄位查詢。
演算法就是OR還是IN？串表時IN還是EXISTS ？oracle in 的限制。條件執行順序等。
然後還有其他注意的，例如只查固定欄位就不要 select * 只要注意以上步驟，千萬級數據串10個秒也能1秒內顯示出來。
有條件的話，當然是用歸檔數據進行查詢，這樣就不會佔用業務數據IO了，最後一步就是「雲計算」（解析有一百種，沒有統一概念，我的意識其實就是歸檔過程中根據分組維度計算好，並根據日期放進相關的表，減少表粒度，只進行簡單的select查詢）

2. 優化SQL有什麼方法

在資料庫應用系統中編寫可執行的SQL語句可以有多種方式實現，但哪一條是最佳方案卻難以確定。為了解決這一問題，有必要對SQL實施優化。簡單地說，SQL語句的優化就是將性能低下的SQL語句轉換成達到同樣目的的性能更好的SQL語句。

優化SQL語句的原因

資料庫系統的生命周期可以分成: 設計、開發和成品三個階段。在設計階段進行優化的成本最低，收益最大。在成品階段進行優化的成本最高，收益最小。如果將一個資料庫系統比喻成一座樓房，在樓房建好後進行矯正往往成本很高而收效很小（甚至可能根本無法矯正），而在樓房設計、生產階段控制好每塊磚瓦的質量就能達到花費小而見效高的目的。

為了獲得最大效益，人們常需要對資料庫進行優化。資料庫的優化通常可以通過對網路、硬體、操作系統、資料庫參數和應用程序的優化來進行。根據統計，對網路、硬體、操作系統、資料庫參數進行優化所獲得的性能提升全部加起來只佔資料庫應用系統性能提升的40%左右，其餘60%的系統性能提升全部來自對應用程序的優化。許多優化專家甚至認為對應用程序的優化可以得到80%的系統性能提升。因此可以肯定，通過優化應用程序來對資料庫系統進行優化能獲得更大的收益。

對應用程序的優化通常可分為兩個方面: 源代碼的優化和SQL語句的優化。由於涉及到對程序邏輯的改變，源代碼的優化在時間成本和風險上代價很高(尤其是對正在使用中的系統進行優化) 。另一方面，源代碼的優化對資料庫系統性能的提升收效有限，因為應用程序對資料庫的操作最終要表現為SQL語句對資料庫的操作。

對SQL語句進行優化有以下一些直接原因：

1. SQL語句是對資料庫(數據) 進行操作的惟一途徑，應用程序的執行最終要歸結為SQL語句的執行，SQL語句的效率對資料庫系統的性能起到了決定性的作用。

2. SQL語句消耗了70%～90%的資料庫資源。

3. SQL語句獨立於程序設計邏輯，對SQL語句進行優化不會影響程序邏輯，相對於對程序源代碼的優化，對SQL語句的優化在時間成本和風險上的代價都很低。

4. SQL語句可以有不同的寫法，不同的寫法在性能上的差異可能很大。

5. SQL語句易學，難精通。SQL語句的性能往往同實際運行系統的資料庫結構、記錄數量等有關，不存在普遍適用的規律來提升性能。

傳統的優化方法

SQL程序人員在傳統上採用手工重寫來對SQL語句進行優化。這主要依靠DBA或資深程序員對SQL語句執行計劃的分析，依靠經驗，嘗試重寫SQL語句，然後對結果和性能進行比較以試圖找到性能較佳的SQL語句。這種做法存在著以下不足：

1. 無法找出SQL語句的所有可能寫法。很可能花費了大量的時間也無法找到性能較佳的SQL語句。即便找到了某個性能較佳的SQL語句也無法知道是否存在性能更好的寫法。

2. 非常依賴於人的經驗，經驗的多寡往往決定了優化後SQL語句的性能。

3. 非常耗時間。重寫-->校驗正確性-->比較性能，這一循環過程需要大量的時間。

根據傳統的SQL優化工具的功能，人們一般將優化工具分為以下三代產品：

第一代的SQL優化工具是執行計劃分析工具。這類工具對輸入的SQL語句從資料庫提取執行計劃，並解釋執行計劃中關鍵字的含義。

第二代的SQL優化工具只能提供增加索引的建議，它通過對輸入的SQL語句的執行計劃的分析來產生是否要增加索引的建議。這類工具存在著致命的缺點——只分析了一條SQL語句就得出增加某個索引的結論，根本不理會(實際上也無法評估到)增加的索引對整體資料庫系統性能的影響。

第三代工具是利用人工智慧實現自動SQL優化。

人工智慧自動SQL優化

隨著人工智慧技術的發展和在資料庫優化領域應用的深入，在20世紀90年代末優化技術取得了突破性的進展，出現了人工智慧自動SQL優化。人工智慧自動SQL優化的本質就是藉助人工智慧技術，自動對SQL語句進行重寫，找到性能最好的等效SQL語句。LECCO SQL Expert就採用了這種人工智慧技術，其SQL Expert支持Oracle、Sybase、MS SQL Server和IBM DB2資料庫平台。其突出特點是自動優化SQL語句。除此以外，還可以以人工智慧知識庫「反饋式搜索引擎」來重寫SQL語句，並找出所有等效的SQL語句及可能的執行計劃，通過測試運行為應用程序和資料庫自動找到性能最好的SQL語句，提供微秒級的計時; 能夠優化Web應用程序和有大量用戶的在線事務處理中運行時間很短的SQL語句; 能通過比較源SQL和待選SQL的不同之處，為開發人員提供「邊做邊學式訓練」，迅速提高開發人員的SQL編程技能等等。

該工具針對資料庫應用的開發和維護階段提供了數個特別的模塊：SQL語法優化器、PL/SQL集成化開發調試環境(IDE)、掃描器、資料庫監視器等。其核心模塊之一「SQL 語法優化器」的工作原理大致如下：輸入一條源SQL語句，「人工智慧反饋式搜索引擎」對輸入的SQL語句結合檢測到的資料庫結構和索引進行重寫，產生N條等效的SQL語句輸出，產生的N條等效SQL語句再送入「人工智慧反饋式搜索引擎」進行重寫，直至無法產生新的輸出或搜索限額滿，接下來對輸出的SQL語句進行過濾，選出具有不同執行計劃的SQL語句(不同的執行計劃意味著不同的執行效率)，最後，對得到的SQL語句進行批量測試，找出性能最好的SQL語句（參見下圖）。

圖 人工智慧自動SQL優化示意圖

LECCO SQL Expert不僅能夠找到最佳的SQL語句，它所提供的「邊做邊學式訓練」還能夠教會開發人員和資料庫管理員如何寫出性能最好的SQL語句。LECCO SQL Expert的SQL語句自動優化功能使SQL的優化變得極其簡單，只要能夠寫出SQL語句，它就能幫開發人員找到最好性能的寫法。

小 結

SQL語句是資料庫應用中一個非常關鍵的部分，它執行性能的高低直接影響著應用程序的運行效率。正因為如此，人們在SQL語句的優化上投入了很大的精力，出現了許多SQL語句優化工具。隨著人工智慧等相關技術的日益成熟, 肯定還會有更多更好的工具出現，這將會給開發人員提供更多的幫助。

3. 如何優化SQL語句

一、問題的提出
在應用系統開發初期，由於開發資料庫數據比較少，對於查詢SQL語句，復雜視圖的的編寫等體會不出SQL語句各種寫法的性能優劣，但是如果將應用系統提交實際應用後，隨著資料庫中數據的增加，系統的響應速度就成為目前系統需要解決的最主要的問題之一。系統優化中一個很重要的方面就是SQL語句的優化。對於海量數據，劣質SQL語句和優質SQL語句之間的速度差別可以達到上百倍，可見對於一個系統不是簡單地能實現其功能就可，而是要寫出高質量的SQL語句，提高系統的可用性。
在多數情況下，Oracle使用索引來更快地遍歷表，優化器主要根據定義的索引來提高性能。但是，如果在SQL語句的where子句中寫的SQL代碼不合理，就會造成優化器刪去索引而使用全表掃描，一般就這種SQL語句就是所謂的劣質SQL語句。在編寫SQL語句時我們應清楚優化器根據何種原則來刪除索引，這有助於寫出高性能的SQL語句。
二、SQL語句編寫注意問題
下面就某些SQL語句的where子句編寫中需要注意的問題作詳細介紹。在這些where子句中，即使某些列存在索引，但是由於編寫了劣質的SQL，系統在運行該SQL語句時也不能使用該索引，而同樣使用全表掃描，這就造成了響應速度的極大降低。
1.
IS
NULL
與
IS
NOT
NULL
不能用null作索引，任何包含null值的列都將不會被包含在索引中。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會從索引中排除。也就是說如果某列存在空值，即使對該列建索引也不會提高性能。
任何在where子句中使用is
null或is
not
null的語句優化器是不允許使用索引的。
2.
聯接列
對於有聯接的列，即使最後的聯接值為一個靜態值，優化器是不會使用索引的。我們一起來看一個例子，假定有一個職工表(employee)，對於一個職工的姓和名分成兩列存放(FIRST_NAME和LAST_NAME)，現在要查詢一個叫比爾.柯林頓(Bill
Cliton)的職工。
下面是一個採用聯接查詢的SQL語句，
select
*
from
employss
where
first_name||''||last_name
='Beill
Cliton';
上面這條語句完全可以查詢出是否有Bill
Cliton這個員工，但是這里需要注意，系統優化器對基於last_name創建的索引沒有使用。
當採用下面這種SQL語句的編寫，Oracle系統就可以採用基於last_name創建的索引。
***
where
first_name
='Beill'
and
last_name
='Cliton';
.
帶通配符(%)的like語句
同樣以上面的例子來看這種情況。目前的需求是這樣的，要求在職工表中查詢名字中包含cliton的人。可以採用如下的查詢SQL語句:
select
*
from
employee
where
last_name
like
'%cliton%';
這里由於通配符(%)在搜尋詞首出現，所以Oracle系統不使用last_name的索引。在很多情況下可能無法避免這種情況，但是一定要心中有底，通配符如此使用會降低查詢速度。然而當通配符出現在字元串其他位置時，優化器就能利用索引。在下面的查詢中索引得到了使用:
select
*
from
employee
where
last_name
like
'c%';
4.
Order
by語句
ORDER
BY語句決定了Oracle如何將返回的查詢結果排序。Order
by語句對要排序的列沒有什麼特別的限制，也可以將函數加入列中(象聯接或者附加等)。任何在Order
by語句的非索引項或者有計算表達式都將降低查詢速度。
仔細檢查order
by語句以找出非索引項或者表達式，它們會降低性能。解決這個問題的辦法就是重寫order
by語句以使用索引，也可以為所使用的列建立另外一個索引，同時應絕對避免在order
by子句中使用表達式。
5.
NOT
我們在查詢時經常在where子句使用一些邏輯表達式，如大於、小於、等於以及不等於等等，也可以使用and(與)、or(或)以及not(非)。NOT可用來對任何邏輯運算符號取反。下面是一個NOT子句的例子:
...
where
not
(status
='VALID')
如果要使用NOT，則應在取反的短語前面加上括弧，並在短語前面加上NOT運算符。NOT運算符包含在另外一個邏輯運算符中，這就是不等於(<>)運算符。換句話說，即使不在查詢where子句中顯式地加入NOT詞，NOT仍在運算符中，見下例:
...
where
status
<>'INVALID';
對這個查詢，可以改寫為不使用NOT:
select
*
from
employee
where
salary<3000
or
salary>3000;
雖然這兩種查詢的結果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許Oracle對salary列使用索引，而第一種查詢則不能使用索引。
雖然這兩種查詢的結果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許Oracle對salary列使用索引，而第一種查詢則不能使用索引。

4. 如何進行SQL性能優化

這里分享下mysql優化的幾種方法。

1、首先在打開的軟體中，需要分別為每一個表創建 InnoDB FILE的文件。

5. 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區別