35 張圖帶你 MySQL 調優

程式設計師cxuan發表於2021-06-09
MySql

這是 MySQL 基礎系列的第四篇文章，之前的三篇文章見如下連結

138 張圖帶你 MySQL 入門

47 張圖帶你 MySQL 進階！！！

炸裂！MySQL 82 張圖帶你飛

一般傳統網際網路公司很少接觸到 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。

log-slow-queries = /tmp/mysql-slow.log
long_query_time = 2

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

也可以通過命令來開啟：

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

show variables like "%slow%";

啟用慢查詢日誌

set global slow_query_log='ON';

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

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

慢查詢日誌會在查詢結束以後才記錄，所以在應用反應執行效率出現問題的時候慢查詢日誌並不能定位問題，此時應該使用 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`)
)

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

  • 全文索引：主要用來查詢文字中的關鍵字，而不是直接與索引中的值相比較，目前只有 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)
);

    當然也可以直接建立全域性索引

    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 表起作用。

相關文章