資料庫SQL調優的幾種方式彙總

2022-10-28 14:04:44

char vs varchar

1、如果文字欄位始終是固定長度的（例如，US 郵編，其始終具有“XXXXX-XXXX”形式的規範表示），那麼推薦使用char。varchar 型別的長度是可變的，而 char 型別是一個定長的欄位，以 char(10) 為例，不管真實的儲存內容多大或者是佔了多少空間，都會消耗掉 10 個字元的空間，通俗來講，當定義為 char(10) 時，即使插入的內容是 'abc' 3 個字元，它依然會佔用 10 個位元組，其中包含了 7 個空位元組。

2、CHAR 在快速、隨機存取時效率很高。使用 VARCHAR，如果你想讀取下一個字串，不得不先讀取到當前字串的末尾，查詢效率比較低。

3、char 長度最大為 255 個字元，varchar 長度最大為 65535 個字元

為什麼varchar(255)而不是varchar(258),varchar(257)呢？

使用255是因為它是8位元數位可以計算的最大字元數。它最大限度地利用了8位元計數，而不需要另一個整位元組來計算255以上的字元。

開啟慢查詢紀錄檔來定位查詢慢的語句

啟動慢查詢紀錄檔，看哪些語句慢。預設是禁用慢查詢紀錄檔的，--slow_query_log[={0|1}] 不指定或者為1，將啟用紀錄檔。如果引數為0，此選項將禁用紀錄檔。--slow_query_log_file=file_name 來改變慢查詢紀錄檔名稱。

先找到慢查詢的原因：

1、開啟資料庫慢查詢紀錄檔，定義超時時間，比如超過2S就是慢查詢

2、定位執行效率低的慢查詢

show processlist

3、也可以通過explain來分析執行計劃，explain得到的資訊要主要關注：type欄位，

possible_keys欄位，key欄位，key_len欄位，rows，extra欄位等

一般情況下慢查詢的原因有以下這些：

沒用索引或者索引失效

用了索引但是走了全表掃描

慢查詢如何優化

索引+sql語句+架構優化+資料庫結構優化

索引：避免索引失效

sql語句：優化insert語句：多條插入寫一條，資料有序的插入；分頁優化：通過子查詢優化，比如查詢2w起後面10條資料，先通過子查詢拿到20000的id,然後通過主鍵索引，通過B+樹定位到拿到的id對應的行資料，然後再向後取10條資料；

架構優化：資料庫讀寫分離，主庫寫，從庫讀；

資料庫結構優化：將欄位比較多的表分解成多個表，將欄位使用頻率高的和欄位使用頻率低的分開，對於一些要進行聯合查詢的表，可以考慮建立中間表

合理使用關鍵字

比如MySQL還有其他更危險的關鍵字，應該謹慎使用。其中包括INSERT DELAYED，它告訴MySQL立即插入資料並不重要（例如，在紀錄檔記錄情況下）。問題是，在高負載情況下，插入可能會無限期延遲，導致插入佇列停滯。

優化查詢快取

查詢快取相關的伺服器變數：

query_cache_min_res_unit：查詢快取中記憶體塊的最小分配單位，預設4k，較小值會減少浪費，但會導致更頻繁的記憶體分配操作，較大值會帶來浪費，會導致碎片過多，記憶體不足
query_cache_limit：單個查詢結果能快取的最大值，單位位元組，預設為1M，對於查詢結果過大而無法快取的語句，建議使用SQL_NO_CACHE
query_cache_size：查詢快取總共可用的記憶體空間；單位位元組，必須是1024的整數倍，最小值40KB，低於此值有警報
query_cache_wlock_invalidate：如果某表被其它的對談鎖定，是否仍然可以從查詢快取中返回結果，預設值為OFF，表示可以在表被其它對談鎖定的場景中繼續從快取返回資料；ON則表示不允許
query_cache_type：是否開啟快取功能，取值為ON, OFF, DEMAND

查詢快取相關的狀態變數：
show gloable status like 'Qcache%' ;

innodb_buffer_pool_size：這是任何使用innodb的安裝都要考慮的#1設定。緩衝池是快取資料和索引的地方：使其儘可能大將確保大多數讀取操作使用記憶體而不是磁碟。典型值為5-6GB（8GB RAM）、20-25GB（32GB RAM）、100-120GB（128GB RAM）。
innodb_log_file_size:這是重做紀錄檔的大小。重做紀錄檔用於確保寫入速度和永續性，以及在崩潰恢復期間。在MySQL 5.1之前，很難進行調整，因為您既需要大的重做紀錄檔來獲得良好的效能，也需要小的重做紀錄檔來實現快速的崩潰恢復。幸運的是，自MySQL 5.5以來，崩潰恢復效能有了很大提高，因此您現在可以擁有良好的寫效能和快速的崩潰恢復。在MySQL 5.5之前，重做紀錄檔的總大小限制為4GB（預設為有2個紀錄檔檔案）。這在MySQL 5.6中得到了提升。

max_connections:如果經常遇到“連線數過多”錯誤，則最大連線數太低。由於應用程式無法正確關閉與資料庫的連線，因此經常需要比預設的151個連線多得多的連線。

查詢快取的優化路線

在早期版本mysql均支援快取，但是隨著redis等記憶體型高效能的快取技術興起，mysql已經拋棄自己的快取功能，mysql8.0以後不再支援快取功能。

適當使用索引

每個索引都需要與表中的行數成比例的空間，因此太多的索引最終會佔用更多記憶體。由於每次寫入都需要更新相應的索引，因此寫入操作的效能也會受到影響。通過分析程式碼，可以發現一個平衡點。這因系統和實施而異。

查詢（SELECT、GROUP BY、ORDER BY、JOIN）的列如果用了索引會更快
索引通常表示為自平衡的 B 樹，可以保持資料有序，並允許在對數時間內進行搜尋，順序存取，插入，刪除操作
設定索引，會將資料存在記憶體中，佔用了更多記憶體空間
寫入操作會變慢，因為索引需要被更新
載入大量資料時，禁用索引再載入資料，然後重建索引，這樣也許會更快

索引何時會失效？

索引 (Index) 是幫助 MySQL 高效獲取資料的資料結構。我們可以簡單理解為：快速查詢排好序的一種資料結構。

當索引不起作用時，會引起全表查詢，索引就會失效，引起慢查詢，有以下幾種情況：

模糊查詢，比如以%開頭的like查詢。
在索引列上操作or, not in , !=,<>,等操作
如果查詢條件有or,並且or的前後條件中有一個列沒有索引，則涉及的索引都不會用到

1、避免索引失效，減少%like這種索引失效語句

2、合理建立索引

3、分頁查詢優化，可以通過子查詢，關聯查詢優化，比如要查詢從10000行開始的10行資料，看似只返回了10條資料，但是資料庫引擎需要查詢10010資料，然後將前面的10000條資料丟棄，效能可想而知，針對這種情況，我們可以先定位到上次分頁的id,然後對id做條件索引查詢；或者將原有的sql拆成2步，首先查詢出一頁資料中的最小id,然後通過索引樹，定位到最小id索引樹節點位置，通過偏移量來讀取後面的10條資料

4、避免使用select *

select *的查詢過程：先在欄位的二級索引B+樹上，查出對應的主鍵id列表

然後進行回表操作，在主鍵索引中查詢id對應的行資料

5.通過explain來分析SQL執行計劃，看是否用到了索引

explain得到的欄位key（使用到的索引）,rows（MYSQL估計為了查詢目標行而需要讀取的行數：）,possible_keys（查詢可能會使用的索引）等主要關注type

Type:以怎樣的方式查詢表中的行的方式（效能逐漸變好）

All: 全表掃描

Index: 根據索引的次序進行全表掃描，若在extra出現using index表示使用覆蓋索引，而非全表掃描

Range: 根據索引實現的範圍掃描

Ref: 根據索引返回表中匹配某單個值的所有行

Eq_ref: 僅返回一個行，但需要和某個參考值作比較

Const,system: 根據具有唯一性的索引（比如主鍵）查詢時，返回的是一行

NULL：類似於覆蓋查詢

Id:當前查詢中，每個Select語句的編號

複雜型別的查詢三種：

簡單子查詢：
用於from中的子查詢
聯合查詢，union

注意：union查詢的分析結果中會出現一個額外的匿名臨時表

Select_type:

簡單查詢為simple

複雜查詢：

Subquery:簡單子查詢

Derived:用於from中的子查詢

Union:用於union第一個之後的select語句

possible_keys: 查詢可能會使用的索引

Key: 使用到的索引

Key_len: 索引中使用的位元組數，比如索引有70個位元組數，只是用了20個

ref: 在利用key所表示的索引完成查詢時，所用的列或某常數值

rows: MYSQL估計為了查詢目標行而需要讀取的行數：

Extra:額外資訊

Using index:會使用覆蓋索引，以避免存取表

Using where:伺服器將在儲存引擎檢索後，再進行一次過濾

Using temporary:對結果排序時會使用臨時表

Using filesort:對結果使用一個外部索引排序

例如：

MariaDB [testdb]> explain select sname,age from stu union select tname,age from teacherG;

*************************** 1. row ***************************

           id: 1
  select_type: PRIMARY
        table: stu
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
        Extra:

*************************** 2. row ***************************

           id: 2
  select_type: UNION
        table: teacher
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra:

*************************** 3. row ***************************

           id: NULL   聯合前兩個表   匿名臨時表
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
3 rows in set (0.00 sec)
ERROR: No query specified