腳本之家服務器常用軟件

快捷導航

數(shù)據(jù)庫SQL調優(yōu)的幾種方式匯總

更新時間：2022年10月26日 11:48:33 作者：帶你去吃小豆花

在項目中,SQL的調優(yōu)對項目的性能來講至關重要,所有掌握常見的SQL調優(yōu)方式是必不可少的,下面這篇文章主要給大家介紹了關于數(shù)據(jù)庫SQL調優(yōu)的幾種方式,需要的朋友可以參考下

char vs varchar

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

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

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

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

使用255是因為它是8位數(shù)字可以計算的最大字符數(shù)。它最大限度地利用了8位計數(shù)，而不需要另一個整字節(jié)來計算255以上的字符。

開啟慢查詢日志來定位查詢慢的語句

啟動慢查詢日志，看哪些語句慢。默認是禁用慢查詢日志的，--slow_query_log[={0|1}] 不指定或者為1，將啟用日志。如果參數(shù)為0，此選項將禁用日志。--slow_query_log_file=file_name 來改變慢查詢日志名稱。

先找到慢查詢的原因：

1、打開數(shù)據(jù)庫慢查詢日志，定義超時時間，比如超過2S就是慢查詢

2、定位執(zhí)行效率低的慢查詢

show processlist

3、也可以通過explain來分析執(zhí)行計劃，explain得到的信息要主要關注：type字段，

possible_keys字段，key字段，key_len字段，rows，extra字段等

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

沒用索引或者索引失效

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

慢查詢如何優(yōu)化

索引+sql語句+架構優(yōu)化+數(shù)據(jù)庫結構優(yōu)化

索引：避免索引失效

sql語句：優(yōu)化insert語句：多條插入寫一條，數(shù)據(jù)有序的插入；分頁優(yōu)化：通過子查詢優(yōu)化，比如查詢2w起后面10條數(shù)據(jù)，先通過子查詢拿到20000的id,然后通過主鍵索引，通過B+樹定位到拿到的id對應的行數(shù)據(jù)，然后再向后取10條數(shù)據(jù)；

架構優(yōu)化：數(shù)據(jù)庫讀寫分離，主庫寫，從庫讀；

數(shù)據(jù)庫結構優(yōu)化：將字段比較多的表分解成多個表，將字段使用頻率高的和字段使用頻率低的分開，對于一些要進行聯(lián)合查詢的表，可以考慮建立中間表

合理使用關鍵字

比如MySQL還有其他更危險的關鍵字，應該謹慎使用。其中包括INSERT DELAYED，它告訴MySQL立即插入數(shù)據(jù)并不重要（例如，在日志記錄情況下）。問題是，在高負載情況下，插入可能會無限期延遲，導致插入隊列停滯。

優(yōu)化查詢緩存

查詢緩存相關的服務器變量：

query_cache_min_res_unit：查詢緩存中內存塊的最小分配單位，默認4k，較小值會減少浪費，但會導致更頻繁的內存分配操作，較大值會帶來浪費，會導致碎片過多，內存不足
query_cache_limit：單個查詢結果能緩存的最大值，單位字節(jié)，默認為1M，對于查詢結果過大而無法緩存的語句，建議使用SQL_NO_CACHE
query_cache_size：查詢緩存總共可用的內存空間；單位字節(jié)，必須是1024的整數(shù)倍，最小值40KB，低于此值有警報
query_cache_wlock_invalidate：如果某表被其它的會話鎖定，是否仍然可以從查詢緩存中返回結果，默認值為OFF，表示可以在表被其它會話鎖定的場景中繼續(xù)從緩存返回數(shù)據(jù)；ON則表示不允許
query_cache_type：是否開啟緩存功能，取值為ON, OFF, DEMAND

查詢緩存相關的狀態(tài)變量：
show gloable status like 'Qcache%' ;

innodb_buffer_pool_size：這是任何使用innodb的安裝都要考慮的#1設置。緩沖池是緩存數(shù)據(jù)和索引的地方：使其盡可能大將確保大多數(shù)讀取操作使用內存而不是磁盤。典型值為5-6GB（8GB RAM）、20-25GB（32GB RAM）、100-120GB（128GB RAM）。
innodb_log_file_size:這是重做日志的大小。重做日志用于確保寫入速度和持久性，以及在崩潰恢復期間。在MySQL 5.1之前，很難進行調整，因為您既需要大的重做日志來獲得良好的性能，也需要小的重做日志來實現(xiàn)快速的崩潰恢復。幸運的是，自MySQL 5.5以來，崩潰恢復性能有了很大提高，因此您現(xiàn)在可以擁有良好的寫性能和快速的崩潰恢復。在MySQL 5.5之前，重做日志的總大小限制為4GB（默認為有2個日志文件）。這在MySQL 5.6中得到了提升。

max_connections:如果經常遇到“連接數(shù)過多”錯誤，則最大連接數(shù)太低。由于應用程序無法正確關閉與數(shù)據(jù)庫的連接，因此經常需要比默認的151個連接多得多的連接。

查詢緩存的優(yōu)化路線

在早期版本mysql均支持緩存，但是隨著redis等內存型高性能的緩存技術興起，mysql已經拋棄自己的緩存功能，mysql8.0以后不再支持緩存功能。

適當使用索引

每個索引都需要與表中的行數(shù)成比例的空間，因此太多的索引最終會占用更多內存。由于每次寫入都需要更新相應的索引，因此寫入操作的性能也會受到影響。通過分析代碼，可以發(fā)現(xiàn)一個平衡點。這因系統(tǒng)和實施而異。

查詢（SELECT、GROUP BY、ORDER BY、JOIN）的列如果用了索引會更快
索引通常表示為自平衡的 B 樹，可以保持數(shù)據(jù)有序，并允許在對數(shù)時間內進行搜索，順序訪問，插入，刪除操作
設置索引，會將數(shù)據(jù)存在內存中，占用了更多內存空間
寫入操作會變慢，因為索引需要被更新
加載大量數(shù)據(jù)時，禁用索引再加載數(shù)據(jù)，然后重建索引，這樣也許會更快

索引何時會失效？

索引 (Index) 是幫助 MySQL 高效獲取數(shù)據(jù)的數(shù)據(jù)結構。我們可以簡單理解為：快速查找排好序的一種數(shù)據(jù)結構。

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

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

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

2、合理創(chuàng)建索引

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

4、避免使用select *

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

然后進行回表操作，在主鍵索引中查詢id對應的行數(shù)據(jù)

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

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

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

All: 全表掃描

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

Range: 根據(jù)索引實現(xiàn)的范圍掃描

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

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

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

NULL：類似于覆蓋查詢

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

復雜類型的查詢三種：

簡單子查詢：
用于from中的子查詢
聯(lián)合查詢，union

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

Select_type:

簡單查詢?yōu)閟imple

復雜查詢：

Subquery:簡單子查詢

Derived:用于from中的子查詢

Union:用于union第一個之后的select語句

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

Key: 使用到的索引

Key_len: 索引中使用的字節(jié)數(shù)，比如索引有70個字節(jié)數(shù)，只是用了20個

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

rows: MYSQL估計為了查找目標行而需要讀取的行數(shù)：

Extra:額外信息

Using index:會使用覆蓋索引，以避免訪問表

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

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

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

例如：

MariaDB [testdb]> explain select sname,age from stu union select tname,age from teacher\G;

*************************** 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   聯(lián)合前兩個表   匿名臨時表
  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

分割數(shù)據(jù)表

將熱點數(shù)據(jù)拆分到單獨的數(shù)據(jù)表中，可以有助于緩存

例如，在博客上，可能會在許多地方顯示條目標題（例如，最近發(fā)布的文章列表）. 經常訪問的數(shù)據(jù)保存在一個表中，而不經常訪問的數(shù)據(jù)保存在另一個表中。由于數(shù)據(jù)現(xiàn)在已分區(qū)，因此不經常訪問的數(shù)據(jù)占用的內存更少。

非規(guī)范化的方式

非規(guī)范化: 非規(guī)范化是一種用于先前規(guī)范化數(shù)據(jù)庫以提高性能的策略。在計算中，非規(guī)范化是指通過添加數(shù)據(jù)的冗余副本或對數(shù)據(jù)進行分組，以犧牲某些寫入性能為代價，嘗試提高數(shù)據(jù)庫的讀取性能的過程。

這通常是由需要執(zhí)行大量讀取操作的關系數(shù)據(jù)庫軟件的性能或可擴展性引起的。一般情況下，只需要在性能需要的地方進行非規(guī)范化。

參考：