35 張圖帶你 MySQL 調優

一般傳統互聯網公司很少接觸到 SQL 優化問題，其原因是數據量小，大部分廠商的數據庫性能能夠滿足日常的業務需求，所以不需要進行 SQL 優化，但是隨着應用程序的不斷變大，數據量的激增，數據庫自身的性能跟不上了，此時就需要從 SQL 自身角度來進行優化，這也是我們這篇文章所討論的。

SQL 優化步驟

當面對一個需要優化的 SQL 時，我們有哪幾種排查思路呢？

通過 show status 命令瞭解 SQL 執行次數

首先，我們可以使用 show status 命令查看服務器狀態信息。show status 命令會顯示每個服務器變量 variable_name 和 value，狀態變量是隻讀的。如果使用 SQL 命令，可以使用 like 或者 where 條件來限制結果。like 可以對變量名做標準模式匹配。

圖我沒有截全，下面還有很多變量，讀者可以自己嘗試一下。也可以在操作系統上使用 mysqladmin extended-status 命令來獲取這些消息。

但是我執行 mysqladmin extended-status 後，出現這個錯誤。

應該是我沒有輸入密碼的原因，使用 mysqladmin -P3306 -uroot -p -h127.0.0.1 -r -i 1 extended-status 後，問題解決。

這裏需要注意一下 show status 命令中可以添加統計結果的級別，這個級別有兩個

• session 級：默認當前鏈接的統計結果

• global 級：自數據庫上次啓動到現在的統計結果

如果不指定統計結果級別的話，默認使用 session 級別。

對於 show status 查詢出來的統計結果，有兩類參數需要注意下，一類是以 Com_ 爲開頭的參數，一類是以 Innodb_ 爲開頭的參數。

下面是 Com_ 爲開頭的參數，參數很多，我同樣沒有截全。

Com_xxx 表示的是每個 xxx 語句執行的次數，我們通常關心的是 select 、insert 、update、delete 語句的執行次數，即

• Com_select：執行 select 操作的次數，一次查詢會使結果 + 1。

• Com_insert：執行 INSERT 操作的次數，對於批量插入的 INSERT 操作，只累加一次。

• Com_update：執行 UPDATE 操作的次數。

• Com_delete：執行 DELETE 操作的次數。

以 Innodb_ 爲開頭的參數主要有

• Innodb_rows_read：執行 select 查詢返回的行數。

• Innodb_rows_inserted：執行 INSERT 操作插入的行數。

• Innodb_rows_updated：執行 UPDATE 操作更新的行數。

• Innodb_rows_deleted：執行 DELETE 操作刪除的行數。

通過上面這些參數執行結果的統計，我們能夠大致瞭解到當前數據庫是以更新（包括插入、刪除）爲主還是查詢爲主。

除此之外，還有一些其他參數用於瞭解數據庫的基本情況。

• Connections：查詢 MySQL 數據庫的連接次數，這個次數是不管連接是否成功都算上。

• Uptime：服務器的工作時間。

• Slow_queries：滿查詢次數。

• Threads_connected：查看當前打開的連接的數量。

下面這個博客彙總了幾乎所有 show status 的參數，可以當作參考手冊。

https://blog.csdn.net/ayay_870621/article/details/88633092

定位執行效率較低的 SQL

定位執行效率比較慢的 SQL 語句，一般有兩種方式

• 可以通過慢查詢日誌來定位哪些執行效率較低的 SQL 語句。

MySQL 中提供了一個慢查詢的日誌記錄功能，可以把查詢 SQL 語句時間大於多少秒的語句寫入慢查詢日誌，日常維護中可以通過慢查詢日誌的記錄信息快速準確地判斷問題所在。用 --log-slow-queries 選項啓動時，mysqld 會寫一個包含所有執行時間超過 long_query_time 秒的 SQL 語句的日誌文件，通過查看這個日誌文件定位效率較低的 SQL 。

比如我們可以在 my.cnf 中添加如下代碼，然後退出重啓 MySQL。

通常我們設置最長的查詢時間是 2 秒，表示查詢時間超過 2 秒就記錄了，通常情況下 2 秒就夠了，然而對於很多 WEB 應用來說，2 秒時間還是比較長的。

也可以通過命令來開啓：

我們先查詢 MySQL 慢查詢日誌是否開啓

啓用慢查詢日誌

然後再次查詢慢查詢是否開啓

如圖所示，我們已經開啓了慢查詢日誌。

慢查詢日誌會在查詢結束以後才記錄，所以在應用反應執行效率出現問題的時候慢查詢日誌並不能定位問題，此時應該使用 show processlist 命令查看當前 MySQL 正在進行的線程。包括線程的狀態、是否鎖表等，可以實時的查看 SQL 執行情況。同樣，使用 mysqladmin processlist 語句也能得到此信息。

下面就來解釋一下各個字段對應的概念

• Id ：Id 就是一個標示，在我們使用 kill 命令殺死進程的時候很有用，比如 kill 進程號。

• User：顯示當前的用戶，如果不是 root，這個命令就只顯示你權限範圍內的 SQL 語句。

• Host：顯示 IP ，用於追蹤問題

• Db：顯示這個進程目前連接的是哪個數據庫，爲 null 是還沒有 select 數據庫。

• Command：顯示當前連接鎖執行的命令，一般有三種：查詢 query，休眠 sleep，連接 connect。

• Time：這個狀態持續的時間，單位是秒

• State：顯示當前 SQL 語句的狀態，非常重要，下面會具體解釋。

• Info：顯示這個 SQL 語句。

State 列非常重要，關於這個列的內容比較多，讀者可以參考一下這篇文章

https://blog.csdn.net/weixin_34357436/article/details/91768402

這裏面涉及線程的狀態、是否鎖表等選項，可以實時的查看 SQL 的執行情況，同時對一些鎖表進行優化。

通過 EXPLAIN 命令分析 SQL 的執行計劃

通過以上步驟查詢到效率低的 SQL 語句後，可以通過 EXPLAIN 或者 DESC 命令獲取 MySQL 如何執行 SELECT 語句的信息，包括在 SELECT 語句執行過程中表如何連接和連接的順序。

比如我們使用下面這條 SQL 語句來分析一下執行計劃

explain select * from test1;

上表中涉及內容如下

• select_type：表示常見的 SELECT 類型，常見的有 SIMPLE，SIMPLE 表示的是簡單的 SQL 語句，不包括 UNION 或者子查詢操作，比如下面這段就是 SIMPLE 類型。

PRIMARY ，查詢中最外層的 SELECT（如兩表做 UNION 或者存在子查詢的外層的表操作爲 PRIMARY，內層的操作爲 UNION），比如下面這段子查詢。

UNION，在 UNION 操作中，查詢中處於內層的 SELECT（內層的 SELECT 語句與外層的 SELECT 語句沒有依賴關係時）。

SUBQUERY：子查詢中首個 SELECT（如果有多個子查詢存在），如我們上面的查詢語句，子查詢第一個是 sr（sys_role）表，所以它的 select_type 是 SUBQUERY。

• table ，這個選項表示輸出結果集的表。

• type，這個選項表示表的連接類型，這個選項很有深入研究的價值，因爲很多 SQL 的調優都是圍繞 type 來講的，但是這篇文章我們主要圍繞優化方式來展開的，type 這個字段我們暫時作爲了解，這篇文章不過多深入。

  type 這個字段會牽扯到連接的性能，它的不同類型的性能由好到差分別是

  system ：表中僅有一條數據時，該表的查詢就像查詢常量表一樣。

  const ：當表中只有一條記錄匹配時，比如使用了表主鍵（primary key）或者表唯一索引（unique index）進行查詢。

  eq-ref ：表示多表連接時使用表主鍵或者表唯一索引，比如

  select A.text, B.text where A.ID = B.ID

  這個查詢語句，對於 A 表中的每一個 ID 行，B 表中都只能有唯一的 B.Id 來進行匹配時。

  ref ：這個類型不如上面的 eq-ref 快，因爲它表示的是因爲對於表 A 中掃描的每一行，表 C 中有幾個可能的行，C.ID 不是唯一的。

  ref_or_null ：與 ref 類似，只不過這個選項包含對 NULL 的查詢。

  index_merge ：查詢語句使用了兩個以上的索引，比如經常在有 and 和 or 關鍵字出現的場景，但是在由於讀取索引過多導致其性能有可能還不如 range（後面說）。

  unique_subquery ：這個選項經常用在 in 關鍵字後面，子查詢帶有 where 關鍵字的子查詢中，用 sql 來表示就是這樣

  value IN (SELECT primary_key FROM single_table WHERE some_expr)

  range ：索引範圍查詢，常見於使用 =，<>，>，>=，<，<=，IS NULL，<=>，BETWEEN，IN() 或者 like 等運算符的查詢中。

  index ：索引全表掃描，把索引從頭到尾掃一遍。

  all ：這個我們接觸的最多了，就是全表查詢，select * from xxx ，性能最差。

上面就是 type 內容的大致解釋，關於 type 我們經常會在 SQL 調優的環節使用 explain 分析其類型，然後改進查詢方式，越靠近 system 其查詢效率越高，越靠近 all 其查詢效率越低。

• possible_keys ：表示查詢時，可能使用的索引。

• key ：表示實際使用的索引。

• key_len ：索引字段的長度。

• rows ：掃描行的數量。

• filtered ：通過查詢條件查詢出來的 SQL 數量佔用總行數的比例。

• extra ：執行情況的描述。

通過上面的分析，我們可以大致確定 SQL 效率低的原因，一種非常有效的提升 SQL 查詢效率的方式就是使用索引，接下來我會講解一下如何使用索引提高查詢效率。

索引

索引是數據庫優化中最常用也是最重要的手段，通過使用不同的索引可以解決大多數 SQL 性能問題，也是面試經常會問到的優化方式，圍繞着索引，面試官能讓你造出火箭來，所以總結一點就是索引非常非常重！要！不只是使用，你還要懂其原！理！

索引介紹

索引的目的就是用於快速查找某一列的數據，對相關數據列使用索引能夠大大提高查詢操作的性能。不使用索引，MySQL 必須從第一條記錄開始讀完整個表，直到找出相關的行，表越大查詢數據所花費的時間就越多。如果表中查詢的列有索引，MySQL 能夠快速到達一個位置去搜索數據文件，而不必查看所有數據，那麼將會節省很大一部分時間。

索引分類

先來了解一下索引都有哪些分類。

• 全局索引(FULLTEXT)：全局索引，目前只有 MyISAM 引擎支持全局索引，它的出現是爲了解決針對文本的模糊查詢效率較低的問題，並且只限於 CHAR、VARCHAR 和 TEXT 列。

• 哈希索引(HASH)：哈希索引是 MySQL 中用到的唯一 key-value 鍵值對的數據結構，很適合作爲索引。HASH 索引具有一次定位的好處，不需要像樹那樣逐個節點查找，但是這種查找適合應用於查找單個鍵的情況，對於範圍查找，HASH 索引的性能就會很低。默認情況下，MEMORY 存儲引擎使用 HASH 索引，但也支持 BTREE 索引。

• B-Tree 索引：B 就是 Balance 的意思，BTree 是一種平衡樹，它有很多變種，最常見的就是 B+ Tree，它被 MySQL 廣泛使用。

• R-Tree 索引：R-Tree 在 MySQL 很少使用，僅支持 geometry 數據類型，支持該類型的存儲引擎只有 MyISAM、BDb、InnoDb、NDb、Archive 幾種，相對於 B-Tree 來說，R-Tree 的優勢在於範圍查找。

從邏輯上來對 MySQL 進行分類，主要分爲下面這幾種

• 普通索引：普通索引是最基礎的索引類型，它沒有任何限制 。創建方式如下

  create index normal_index on cxuan003(id);

  刪除方式

  drop index normal_index on cxuan003;

• 唯一索引：唯一索引列的值必須唯一，允許有空值，如果是組合索引，則列值的組合必須唯一，創建方式如下

  create unique index normal_index on cxuan003(id);

• 主鍵索引：是一種特殊的索引，一個表只能有一個主鍵，不允許有空值。一般是在建表的時候同時創建主鍵索引。

  CREATE TABLE 
  table
   (
         
  id
   int(11) NOT NULL AUTO_INCREMENT ,
         
  title
   char(255) NOT NULL ,
         PRIMARY KEY (
  id
  )
  )
      
  table
  id
  title
  id

• 組合索引：指多個字段上創建的索引，只有在查詢條件中使用了創建索引時的第一個字段，索引纔會被使用。使用組合索引時遵循最左前綴原則，下面我們就會創建組合索引。

• 全文索引：主要用來查找文本中的關鍵字，而不是直接與索引中的值相比較，目前只有 char、varchar，text 列上可以創建全文索引，創建表的適合添加全文索引

  CREATE TABLE 
  table
   (
    
  id
   int(11) NOT NULL AUTO_INCREMENT ,
    
  title
   char(255) CHARACTER NOT NULL ,
    
  content
   text CHARACTER NULL ,
    
  time
   int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (
  id
  ),
    FULLTEXT (content)
  );
      
  table
  id
  title
  content
  time
  id

  當然也可以直接創建全局索引

  CREATE FULLTEXT INDEX index_content ON article(content)

索引使用

索引可以在創建表的時候進行創建，也可以單獨創建，下面我們採用單獨創建的方式，我們在 cxuan004 上創建前綴索引

我們使用 explain 進行分析，可以看到 cxuan004 使用索引的情況

如果不想使用索引，可以刪除索引，索引的刪除語法是

索引使用細則

我們在 cxuan005 上根據 id 和 hash 創建一個複合索引，如下所示

create index id_hash_index on cxuan005(id,hash);

然後根據 id 進行執行計劃的分析

explain select * from cxuan005 where id = '333';

可以發現，即使 where 條件中使用的不是複合索引（Id 、hash），索引仍然能夠使用，這就是索引的前綴特性。但是如果只按照 hash 進行查詢的話，索引就不會用到。

explain select * from cxuan005 where hash='8fd1f12575f6b39ee7c6d704eb54b353';

如果 where 條件使用了 like 查詢，並且 % 不在第一個字符，索引纔可能被使用。

對於複合索引來說，只能使用 id 進行 like 查詢，因爲 hash 列不管怎麼查詢都不會走索引。

explain select * from cxuan005 where id like '%1';

可以看到，如果第一個字符是 % ，則沒有使用索引。

explain select * from cxuan005 where id like '1%';

如果使用了 % 號，就會觸發索引。

如果列名是索引的話，那麼對列名進行 NULL 查詢，將會觸發索引。

explain select * from cxuan005 where id is null;

還有一些情況是存在索引但是 MySQL 並不會使用的情況。

• 最簡單的，如果使用索引後比不使用索引的效率還差，那麼 MySQL 就不會使用索引。

• 如果 SQL 中使用了 OR 條件，OR 前的條件列有索引，而後面的列沒有索引的話，那麼涉及到的索引都不會使用，比如 cxuan005 表中，只有 id 和 hash 字段有索引，而 info 字段沒有索引，那麼我們使用 or 進行查詢。

  explain select * from cxuan005 where id = 111 and info = 'cxuan';

• 我們從 explain 的執行結果可以看到，雖然 possible_keys 選項上仍然有 id_hash_index 索引，但是從 key、key_len 可以得知，這條 SQL 語句並未使用索引。

• 在帶有複合索引的列上查詢不是第一列的數據，也不會使用索引。

  explain select * from cxuan005 where hash = '8fd1f12575f6b39ee7c6d704eb54b353';

• 如果 where 條件的列參與了計算，那麼也不會使用索引

  explain select * from cxuan005 where id + '111' = '666';

• 索引列使用函數，一樣也不會使用索引

  explain select * from cxuan005 where concat(id,'111') = '666';

• 索引列使用了 like ，並且 % 位於第一個字符，則不會使用索引。

• 在 order by 操作中，排序的列同時也在 where 語句中，將不會使用索引。

• 當數據類型出現隱式轉換時，比如 varchar 不加單引號可能轉換爲 int 類型時，會使索引無效，觸發全表掃描。比如下面這兩個例子能夠顯而易見的說明這一點

• 在索引列上使用 IS NOT NULL 操作

• 在索引字段上使用 <>，!=。不等於操作符是永遠不會用到索引的，因此對它的處理只會產生全表掃描。

關於設置索引但是索引沒有生效的場景還有很多，這個需要小夥伴們工作中不斷總結和完善，不過我上面總結的這些索引失效的情景，能夠覆蓋大多數索引失效的場景了。

查看索引的使用情況

在 MySQL 索引的使用過程中，有一個 Handler_read_key 值，這個值表示了某一行被索引值讀的次數。Handler_read_key 的值比較低的話，則表明增加索引得到的性能改善不是很理想，可能索引使用的頻率不高。

還有一個值是 Handler_read_rnd_next，這個值高則意味着查詢運行效率不高，應該建立索引來進行搶救。這個值的含義是在數據文件中讀下一行的請求數。如果正在進行大量的表掃描，Handler_read_rnd_next 的值比較高，就說明表索引不正確或寫入的查詢沒有利用索引。

MySQL 分析表、檢查表和優化表

對於大多數開發者來說，他們更傾向於解決簡單 SQL 的優化，而複雜 SQL 的優化交給了公司的 DBA 來做。

下面就從普通程序員的角度和你聊幾個簡單的優化方式。

MySQL 分析表

分析表用於分析和存儲表的關鍵字分佈，分析的結果可以使得系統得到準確的統計信息，使得 SQL 生成正確的執行計劃。如果用於感覺實際執行計劃與預期不符，可以執行分析表來解決問題，分析表語法如下

analyze table cxuan005;

分析結果涉及到的字段屬性如下

Table：表示表的名稱；

Op：表示執行的操作，analyze 表示進行分析操作，check 表示進行檢查查找，optimize 表示進行優化操作；

Msg_type：表示信息類型，其顯示的值通常是狀態、警告、錯誤和信息這四者之一；

Msg_text：顯示信息。

對錶的定期分析可以改善性能，應該成爲日常工作的一部分。因爲通過更新表的索引信息對錶進行分析，可改善數據庫性能。

MySQL 檢查表

數據庫經常可能遇到錯誤，比如數據寫入磁盤時發生錯誤，或是索引沒有同步更新，或是數據庫未關閉 MySQL 就停止了。遇到這些情況，數據就可能發生錯誤： Incorrect key file for table: ' '. Try to repair it. 此時，我們可以使用 Check Table 語句來檢查表及其對應的索引。

check table cxuan005;

檢查表的主要目的就是檢查一個或者多個表是否有錯誤。Check Table 對 MyISAM 和 InnoDB 表有作用。Check Table 也可以檢查視圖的錯誤。

MySQL 優化表

MySQL 優化表適用於刪除了大量的表數據，或者對包含 VARCHAR、BLOB 或則 TEXT 命令進行大量修改的情況。MySQL 優化表可以將大量的空間碎片進行合併，消除由於刪除或者更新造成的空間浪費情況。它的命令如下

optimize table cxuan005;

我的存儲引擎是 InnoDB 引擎，但是從圖可以知道，InnoDB 不支持使用 optimize 優化，建議使用 recreate + analyze 進行優化。optimize 命令只對 MyISAM 、BDB 表起作用。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/CF0WODa-kCypJiQaA9XkDQ