SQL效能優化的47個小技巧，你瞭解多少？

語言: CN / TW / HK
時間 2023-02-21 08:15:04
主題:

大家好，我是哪吒。

1、先了解MySQL的執行過程

瞭解了MySQL的執行過程，我們才知道如何進行sql優化。

  1. 客戶端傳送一條查詢語句到伺服器；
  2. 伺服器先查詢快取，如果命中快取，則立即返回儲存在快取中的資料；
  3. 未命中快取後，MySQL通過關鍵字將SQL語句進行解析，並生成一顆對應的解析樹，MySQL解析器將使用MySQL語法進行驗證和解析。例如，驗證是否使用了錯誤的關鍵字，或者關鍵字的使用是否正確；
  4. 預處理是根據一些MySQL規則檢查解析樹是否合理，比如檢查表和列是否存在，還會解析名字和別名，然後前處理器會驗證許可權；
  5. 根據執行計劃查詢執行引擎，呼叫API介面呼叫儲存引擎來查詢資料；
  6. 將結果返回客戶端，並進行快取；

2、資料庫常見規範

  1. 所有資料庫物件名稱必須使用小寫字母並用下劃線分割；
  2. 所有資料庫物件名稱禁止使用mysql保留關鍵字；
  3. 資料庫物件的命名要能做到見名識意，並且最後不要超過32個字元；
  4. 臨時庫表必須以tmp_為字首並以日期為字尾，備份表必須以bak_為字首並以日期(時間戳)為字尾；
  5. 所有儲存相同資料的列名和列型別必須一致；

3、所有表必須使用Innodb儲存引擎

沒有特殊要求（即Innodb無法滿足的功能如：列儲存，儲存空間資料等）的情況下，所有表必須使用Innodb儲存引擎（mysql5.5之前預設使用Myisam，5.6以後預設的為Innodb）。

Innodb 支援事務，支援行級鎖，更好的恢復性，高併發下效能更好。

4、每個Innodb表必須有個主鍵

Innodb是一種索引組織表：資料的儲存的邏輯順序和索引的順序是相同的。每個表都可以有多個索引，但是表的儲存順序只能有一種。

Innodb是按照主鍵索引的順序來組織表的

  1. 不要使用更新頻繁的列作為主鍵，不適用多列主鍵；
  2. 不要使用UUID、MD5、HASH、字串列作為主鍵（無法保證資料的順序增長）；
  3. 主鍵建議使用自增ID值；

5、資料庫和表的字符集統一使用UTF8

相容性更好，統一字符集可以避免由於字符集轉換產生的亂碼，不同的字符集進行比較前需要進行轉換會造成索引失效，如果資料庫中有儲存emoji表情的需要，字符集需要採用utf8mb4字符集。

6、查詢SQL儘量不要使用select *，而是具體欄位

select *的弊端：

  1. 增加很多不必要的消耗，比如CPU、IO、記憶體、網路頻寬；
  2. 增加了使用覆蓋索引的可能性；
  3. 增加了回表的可能性；
  4. 當表結構發生變化時，前端也需要更改；
  5. 查詢效率低；

7、避免在where子句中使用 or 來連線條件

  1. 使用or可能會使索引失效，從而全表掃描；
  2. 對於or沒有索引的salary這種情況，假設它走了id的索引，但是走到salary查詢條件時，它還得全表掃描；
  3. 也就是說整個過程需要三步：全表掃描+索引掃描+合併。如果它一開始就走全表掃描，直接一遍掃描就搞定；
  4. 雖然mysql是有優化器的，處於效率與成本考慮，遇到or條件，索引還是可能失效的；

8、儘量使用數值替代字串型別

  1. 因為引擎在處理查詢和連線時會逐個比較字串中每一個字元；
  2. 而對於數字型而言只需要比較一次就夠了；
  3. 字元會降低查詢和連線的效能，並會增加儲存開銷；

9、使用varchar代替char

  1. varchar變長欄位按資料內容實際長度儲存，儲存空間小，可以節省儲存空間；
  2. char按宣告大小儲存，不足補空格；
  3. 其次對於查詢來說，在一個相對較小的欄位內搜尋，效率更高；

10、財務、銀行相關的金額欄位必須使用decimal型別

  • 非精準浮點：float,double

  • 精準浮點：decimal

  • Decimal型別為精準浮點數，在計算時不會丟失精度；

  • 佔用空間由定義的寬度決定，每4個位元組可以儲存9位數字，並且小數點要佔用一個位元組；
  • 可用於儲存比bigint更大的整型資料；

11、避免使用ENUM型別

  • 修改ENUM值需要使用ALTER語句；
  • ENUM型別的ORDER BY操作效率低，需要額外操作；
  • 禁止使用數值作為ENUM的列舉值；

12、去重distinct過濾欄位要少

  1. 帶distinct的語句佔用cpu時間高於不帶distinct的語句
  2. 當查詢很多欄位時，如果使用distinct，資料庫引擎就會對資料進行比較，過濾掉重複資料
  3. 然而這個比較、過濾的過程會佔用系統資源，如cpu時間

13、where中使用預設值代替null

  1. 並不是說使用了is null或者 is not null就會不走索引了，這個跟mysql版本以及查詢成本都有關；
  2. 如果mysql優化器發現，走索引比不走索引成本還要高，就會放棄索引，這些條件 !=，<>，is null，is not null經常被認為讓索引失效；
  3. 其實是因為一般情況下，查詢的成本高，優化器自動放棄索引的；
  4. 如果把null值，換成預設值，很多時候讓走索引成為可能，同時，表達意思也相對清晰一點；

14、避免在where子句中使用!=或<>操作符

  1. 使用!=和<>很可能會讓索引失效
  2. 應儘量避免在where子句中使用!=或<>操作符，否則引擎將放棄使用索引而進行全表掃描
  3. 實現業務優先，實在沒辦法，就只能使用，並不是不能使用

15、inner join 、left join、right join，優先使用inner join

三種連線如果結果相同，優先使用inner join，如果使用left join左邊表儘量小。

  • inner join 內連線，只保留兩張表中完全匹配的結果集；
  • left join會返回左表所有的行，即使在右表中沒有匹配的記錄；
  • right join會返回右表所有的行，即使在左表中沒有匹配的記錄；

為什麼？

  • 如果inner join是等值連線，返回的行數比較少，所以效能相對會好一點；
  • 使用了左連線，左邊表資料結果儘量小，條件儘量放到左邊處理，意味著返回的行數可能比較少；
  • 這是mysql優化原則，就是小表驅動大表，小的資料集驅動大的資料集，從而讓效能更優；

16、提高group by語句的效率

1、反例

先分組，再過濾

sql select job, avg（salary） from employee group by job having job ='develop' or job = 'test';

2、正例

先過濾，後分組

sql select job，avg（salary） from employee where job ='develop' or job = 'test' group by job;

3、理由

可以在執行到該語句前，把不需要的記錄過濾掉

17、清空表時優先使用truncate

truncate table在功能上與不帶 where子句的 delete語句相同：二者均刪除表中的全部行。但 truncate table比 delete速度快，且使用的系統和事務日誌資源少。

delete語句每次刪除一行，並在事務日誌中為所刪除的每行記錄一項。 truncate table通過釋放儲存表資料所用的資料頁來刪除資料，並且只在事務日誌中記錄頁的釋放。

truncate table刪除表中的所有行，但表結構及其列、約束、索引等保持不變。新行標識所用的計數值重置為該列的種子。如果想保留標識計數值，請改用 DELETE。如果要刪除表定義及其資料，請使用 drop table語句。

對於由 foreign key約束引用的表，不能使用 truncate table，而應使用不帶 where子句的 DELETE 語句。由於 truncate table不記錄在日誌中，所以它不能啟用觸發器。

truncate table不能用於參與了索引檢視的表。

18、操作delete或者update語句，加個limit或者迴圈分批次刪除

（1）降低寫錯SQL的代價

清空表資料可不是小事情，一個手抖全沒了，刪庫跑路？如果加limit，刪錯也只是丟失部分資料，可以通過binlog日誌快速恢復的。

（2）SQL效率很可能更高

SQL中加了limit 1，如果第一條就命中目標return， 沒有limit的話，還會繼續執行掃描表。

（3）避免長事務

delete執行時,如果age加了索引，MySQL會將所有相關的行加寫鎖和間隙鎖，所有執行相關行會被鎖住，如果刪除數量大，會直接影響相關業務無法使用。

（4）資料量大的話，容易把CPU打滿

如果你刪除資料量很大時，不加 limit限制一下記錄數，容易把cpu打滿，導致越刪越慢。

（5）鎖表

一次性刪除太多資料，可能造成鎖表，會有lock wait timeout exceed的錯誤，所以建議分批操作。

19、UNION操作符

UNION在進行錶鏈接後會篩選掉重複的記錄，所以在錶鏈接後會對所產生的結果集進行排序運算，刪除重複的記錄再返回結果。 實際大部分應用中是不會產生重複的記錄，最常見的是過程表與歷史表UNION。如： sql select username,tel from user union select departmentname from department 這個SQL在執行時先取出兩個表的結果，再用排序空間進行排序刪除重複的記錄，最後返回結果集，如果表資料量大的話可能會導致用磁碟進行排序。 推薦方案：採用UNION ALL操作符替代UNION，因為UNION ALL操作只是簡單的將兩個結果合併後就返回。

20、SQL語句中IN包含的欄位不宜過多

MySQL的IN中的常量全部儲存在一個數組中，這個陣列是排序的。如果值過多，產生的消耗也是比較大的。如果是連續的數字，可以使用between代替，或者使用連線查詢替換。

21、批量插入效能提升

（1）多條提交

```sql INSERT INTO user (id,username) VALUES(1,'哪吒程式設計');

INSERT INTO user (id,username) VALUES(2,'妲己'); ```

（2）批量提交

sql INSERT INTO user (id,username) VALUES(1,'哪吒程式設計'),(2,'妲己');

預設新增SQL有事務控制，導致每條都需要事務開啟和事務提交，而批量處理是一次事務開啟和提交，效率提升明顯，達到一定量級，效果顯著，平時看不出來。

22、表連線不宜太多，索引不宜太多，一般5個以內

（1）表連線不宜太多，一般5個以內

  1. 關聯的表個數越多，編譯的時間和開銷也就越大
  2. 每次關聯記憶體中都生成一個臨時表
  3. 應該把連線表拆開成較小的幾個執行，可讀性更高
  4. 如果一定需要連線很多表才能得到資料，那麼意味著這是個糟糕的設計了
  5. 阿里規範中，建議多表聯查三張表以下

（2）索引不宜太多，一般5個以內

  1. 索引並不是越多越好，雖其提高了查詢的效率，但卻會降低插入和更新的效率；
  2. 索引可以理解為一個就是一張表，其可以儲存資料，其資料就要佔空間；
  3. 索引表的資料是排序的，排序也是要花時間的；
  4. insert或update時有可能會重建索引，如果資料量巨大，重建將進行記錄的重新排序，所以建索引需要慎重考慮，視具體情況來定；
  5. 一個表的索引數最好不要超過5個，若太多需要考慮一些索引是否有存在的必要；

23、禁止給表中的每一列都建立單獨的索引

真有這麼幹的，我也是醉了。

24、如何選擇索引列的順序

建立索引的目的是：希望通過索引進行資料查詢，減少隨機IO，增加查詢效能 ，索引能過濾出越少的資料，則從磁碟中讀入的資料也就越少。

區分度最高的放在聯合索引的最左側（區分度=列中不同值的數量/列的總行數）。

儘量把欄位長度小的列放在聯合索引的最左側（因為欄位長度越小，一頁能儲存的資料量越大，IO效能也就越好）。

使用最頻繁的列放到聯合索引的左側（這樣可以比較少的建立一些索引）。

25、對於頻繁的查詢優先考慮使用覆蓋索引

覆蓋索引：就是包含了所有查詢欄位(where,select,ordery by,group by包含的欄位)的索引。

覆蓋索引的好處：

（1）避免Innodb表進行索引的二次查詢

Innodb是以聚集索引的順序來儲存的，對於Innodb來說，二級索引在葉子節點中所儲存的是行的主鍵資訊，如果是用二級索引查詢資料的話，在查詢到相應的鍵值後，還要通過主鍵進行二次查詢才能獲取我們真實所需要的資料。

而在覆蓋索引中，二級索引的鍵值中可以獲取所有的資料，避免了對主鍵的二次查詢 ，減少了IO操作，提升了查詢效率。

（2）可以把隨機IO變成順序IO加快查詢效率

由於覆蓋索引是按鍵值的順序儲存的，對於IO密集型的範圍查詢來說，對比隨機從磁碟讀取每一行的資料IO要少的多，因此利用覆蓋索引在訪問時也可以把磁碟的隨機讀取的IO轉變成索引查詢的順序IO。

26、建議使用預編譯語句進行資料庫操作

預編譯語句可以重複使用這些計劃，減少SQL編譯所需要的時間，還可以解決動態SQL所帶來的SQL注入的問題。

只傳引數，比傳遞SQL語句更高效。

相同語句可以一次解析，多次使用，提高處理效率。

27、避免產生大事務操作

大批量修改資料，一定是在一個事務中進行的，這就會造成表中大批量資料進行鎖定，從而導致大量的阻塞，阻塞會對MySQL的效能產生非常大的影響。

特別是長時間的阻塞會佔滿所有資料庫的可用連線，這會使生產環境中的其他應用無法連線到資料庫，因此一定要注意大批量寫操作要進行分批。

28、避免在索引列上使用內建函式

使用索引列上內建函式，索引失效。

29、組合索引

排序時應按照組合索引中各列的順序進行排序，即使索引中只有一個列是要排序的，否則排序效能會比較差。

sql create index IDX_USERNAME_TEL on user(deptid,position,createtime); select username,tel from user where deptid= 1 and position = 'java開發' order by deptid,position,createtime desc;

實際上只是查詢出符合 deptid= 1 and position = 'java開發'條件的記錄並按createtime降序排序，但寫成order by createtime desc效能較差。

30、複合索引最左特性

（1）建立複合索引

sql ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)

（2）滿足複合索引的最左特性，哪怕只是部分，複合索引生效

sql SELECT * FROM employee WHERE NAME='哪吒程式設計' （3）沒有出現左邊的欄位，則不滿足最左特性，索引失效

sql SELECT * FROM employee WHERE salary=5000 （4）複合索引全使用，按左側順序出現 name,salary，索引生效

sql SELECT * FROM employee WHERE NAME='哪吒程式設計' AND salary=5000

（5）雖然違背了最左特性，但MySQL執行SQL時會進行優化，底層進行顛倒優化

sql SELECT * FROM employee WHERE salary=5000 AND NAME='哪吒程式設計'

（6）理由

複合索引也稱為聯合索引，當我們建立一個聯合索引的時候，如(k1,k2,k3)，相當於建立了（k1）、(k1,k2)和(k1,k2,k3)三個索引，這就是最左匹配原則。

聯合索引不滿足最左原則，索引一般會失效。

31、必要時可以使用force index來強制查詢走某個索引

有的時候MySQL優化器採取它認為合適的索引來檢索SQL語句，但是可能它所採用的索引並不是我們想要的。這時就可以採用forceindex來強制優化器使用我們制定的索引。

32、優化like語句

模糊查詢，程式設計師最喜歡的就是使用like，但是like很可能讓你的索引失效。

  • 首先儘量避免模糊查詢，如果必須使用，不採用全模糊查詢，也應儘量採用右模糊查詢， 即like ‘…%’，是會使用索引的；
  • 左模糊like ‘%...’無法直接使用索引，但可以利用reverse + function index的形式，變化成 like ‘…%’；
  • 全模糊查詢是無法優化的，一定要使用的話建議使用搜索引擎。

33、統一SQL語句的寫法

對於以下兩句SQL語句， 程式設計師認為是相同的，資料庫查詢優化器認為是不同的。

sql select * from user; select * From USER;

這都是很常見的寫法，也很少有人會注意，就是表名大小寫不一樣而已。然而，查詢解析器認為這是兩個不同的SQL語句，要解析兩次，生成兩個不同的執行計劃，作為一名嚴謹的Java開發工程師，應該保證兩個一樣的SQL語句，不管在任何地方都是一樣的。

34、不要把SQL語句寫得太複雜

經常聽到有人吹牛逼，我寫了一個800行的SQL語句，邏輯感超強，我們還開會進行了SQL講解，大家都投來了崇拜的目光。。。

一般來說，巢狀子查詢、或者是3張表關聯查詢還是比較常見的，但是，如果超過3層巢狀的話，查詢優化器很容易給出錯誤的執行計劃，影響SQL效率。SQL執行計劃是可以被重用的，SQL越簡單，被重用的概率越大，生成執行計劃也是很耗時的。

35、將大的DELETE，UPDATE、INSERT 查詢變成多個小查詢

能寫一個幾十行、幾百行的SQL語句是不是顯得逼格很高？然而，為了達到更好的效能以及更好的資料控制，你可以將他們變成多個小查詢。

36、關於臨時表

  1. 避免頻繁建立和刪除臨時表，以減少系統表資源的消耗；
  2. 在新建臨時表時，如果一次性插入資料量很大，那麼可以使用 select into 代替 create table，避免造成大量 log；
  3. 如果資料量不大，為了緩和系統表的資源，應先create table，然後insert；
  4. 如果使用到了臨時表，在儲存過程的最後務必將所有的臨時表顯式刪除。先 truncate table ，然後 drop table ，這樣可以避免系統表的較長時間鎖定。

37、使用explain分析你SQL執行計劃

（1）type

  1. system：表僅有一行，基本用不到；
  2. const：表最多一行資料配合，主鍵查詢時觸發較多；
  3. eq_ref：對於每個來自於前面的表的行組合，從該表中讀取一行。這可能是最好的聯接型別，除了const型別；
  4. ref：對於每個來自於前面的表的行組合，所有有匹配索引值的行將從這張表中讀取；
  5. range：只檢索給定範圍的行，使用一個索引來選擇行。當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關鍵字列時，可以使用range；
  6. index：該聯接型別與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引檔案通常比資料檔案小；
  7. all：全表掃描；
  8. 效能排名：system > const > eq_ref > ref > range > index > all。
  9. 實際sql優化中，最後達到ref或range級別。

（2）Extra常用關鍵字

  • Using index：只從索引樹中獲取資訊，而不需要回表查詢；
  • Using where：WHERE子句用於限制哪一個行匹配下一個表或傳送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where並且表聯接型別為ALL或index，查詢可能會有一些錯誤。需要回表查詢。
  • Using temporary：mysql常建一個臨時表來容納結果，典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時；

38、讀寫分離與分庫分表

當資料量達到一定的數量之後，限制資料庫儲存效能的就不再是資料庫層面的優化就能夠解決的；這個時候往往採用的是讀寫分離與分庫分表同時也會結合快取一起使用，而這個時候資料庫層面的優化只是基礎。

讀寫分離適用於較小一些的資料量；分表適用於中等資料量；而分庫與分表一般是結合著用，這就適用於大資料量的儲存了，這也是現在大型網際網路公司解決資料儲存的方法之一。

39、使用合理的分頁方式以提高分頁的效率

sql select id,name from user limit 100000, 20

使用上述SQL語句做分頁的時候，隨著表資料量的增加，直接使用limit語句會越來越慢。

此時，可以通過取前一頁的最大ID，以此為起點，再進行limit操作，效率提升顯著。

sql select id,name from user where id> 100000 limit 20

40、儘量控制單表資料量的大小，建議控制在500萬以內。

500萬並不是MySQL資料庫的限制，過大會造成修改表結構，備份，恢復都會有很大的問題。

可以用歷史資料歸檔（應用於日誌資料），分庫分表（應用於業務資料）等手段來控制資料量大小。

41、謹慎使用Mysql分割槽表

  • 分割槽表在物理上表現為多個檔案，在邏輯上表現為一個表；
  • 謹慎選擇分割槽鍵，跨分割槽查詢效率可能更低；
  • 建議採用物理分表的方式管理大資料。

42、儘量做到冷熱資料分離，減小表的寬度

Mysql限制每個表最多儲存4096列，並且每一行資料的大小不能超過65535位元組。

減少磁碟IO,保證熱資料的記憶體快取命中率（表越寬，把表裝載進記憶體緩衝池時所佔用的記憶體也就越大,也會消耗更多的IO）；

更有效的利用快取，避免讀入無用的冷資料；

經常一起使用的列放到一個表中（避免更多的關聯操作）。

43、禁止在表中建立預留欄位

  1. 預留欄位的命名很難做到見名識義；
  2. 預留欄位無法確認儲存的資料型別，所以無法選擇合適的型別；
  3. 對預留欄位型別的修改，會對錶進行鎖定；

44、禁止在資料庫中儲存圖片，檔案等大的二進位制資料

通常檔案很大，會短時間內造成資料量快速增長，資料庫進行資料庫讀取時，通常會進行大量的隨機IO操作，檔案很大時，IO操作很耗時。

通常儲存於檔案伺服器，資料庫只儲存檔案地址資訊。

45、建議把BLOB或是TEXT列分離到單獨的擴充套件表中

Mysql記憶體臨時表不支援TEXT、BLOB這樣的大資料型別，如果查詢中包含這樣的資料，在排序等操作時，就不能使用記憶體臨時表，必須使用磁碟臨時表進行。而且對於這種資料，Mysql還是要進行二次查詢，會使sql效能變得很差，但是不是說一定不能使用這樣的資料型別。

如果一定要使用，建議把BLOB或是TEXT列分離到單獨的擴充套件表中，查詢時一定不要使用select * 而只需要取出必要的列，不需要TEXT列的資料時不要對該列進行查詢。

46、TEXT或BLOB型別只能使用字首索引

因為MySQL對索引欄位長度是有限制的，所以TEXT型別只能使用字首索引，並且TEXT列上是不能有預設值的。

47、一些其它優化方式

（1）當只需要一條資料的時候，使用limit 1

limit 1可以避免全表掃描，找到對應結果就不會再繼續掃描了。

（2）如果排序欄位沒有用到索引，就儘量少排序

（3）所有表和欄位都需要添加註釋

使用comment從句新增表和列的備註，從一開始就進行資料字典的維護。

（4）SQL書寫格式，關鍵字大小保持一致，使用縮排。

（5）修改或刪除重要資料前，要先備份。

（6）很多時候用 exists 代替 in 是一個好的選擇

（7）where後面的欄位，留意其資料型別的隱式轉換。

（8）儘量把所有列定義為NOT NULL

NOT NULL列更節省空間，NULL列需要一個額外位元組作為判斷是否為 NULL的標誌位。 NULL列需要注意空指標問題，NULL列在計算和比較的時候，需要注意空指標問題。

（9）偽刪除設計

（10）索引不適合建在有大量重複資料的欄位上，比如性別，排序欄位應建立索引

（11）儘量避免使用遊標

因為遊標的效率較差，如果遊標操作的資料超過1萬行，那麼就應該考慮改寫。

「」