sql怎樣優化

3. 項目中優化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，以提高查詢效率。

4. 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進行數據碎片處理。

5. 怎樣進行sql資料庫的優化

1、資料庫空間是個概述，在sqlserver里，使用語句 exec sp_spaceused 'TableName' 這個語句來查。

6. 資料庫牛人是如何進行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 查詢優化的最佳實踐幫助用戶獲得准確、快速的資料庫結果。

7. 列舉sql優化有哪些方式

sql優化的方式有：

1、選擇最有效率的表名順序(只在基於規則的優化器中有效)：

ORACLE 的解析器按照從右到左的順序處理FROM子句中的表名，FROM子句中寫在最後的表(基礎表 driving table)將被最先處理，在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表。如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表。

2、WHERE子句中的連接順序：

ORACLE採用自下而上的順序解析WHERE子句,根據這個原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾。

3、SELECT子句中避免使用 『 * 『：

ORACLE在解析的過程中, 會將'*' 依次轉換成所有的列名, 這個工作是通過查詢數據字典完成的, 這意味著將耗費更多的時間 。

4、 減少訪問資料庫的次數：

ORACLE在內部執行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變數 , 讀數據塊等。

5、 在SQL*Plus , SQL*Forms和Pro*C中重新設置ARRAYSIZE參數, 可以增加每次資料庫訪問的檢索數據量 ,建議值為200 。

6、 使用DECODE函數來減少處理時間：

使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表。

7、整合簡單,無關聯的資料庫訪問：

如果你有幾個簡單的資料庫查詢語句,你可以把它們整合到一個查詢中(即使它們之間沒有關系)。

8. 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高級也不難

9. 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.字元串類型的欄位 查詢的時候如果不加引號'' ,會導致自動進行隱式轉換,然後索引失效