35 張圖帶你 MySQL 調(diào)優(yōu)
一般傳統(tǒng)互聯(lián)網(wǎng)公司很少接觸到 SQL 優(yōu)化問題，其原因是數(shù)據(jù)量小，大部分廠商的數(shù)據(jù)庫性能能夠滿足日常的業(yè)務(wù)需求，所以不需要進(jìn)行 SQL 優(yōu)化，但是隨著應(yīng)用程序的不斷變大，數(shù)據(jù)量的激增，數(shù)據(jù)庫自身的性能跟不上了，此時就需要從 SQL 自身角度來進(jìn)行優(yōu)化，這也是我們這篇文章所討論的。
SQL 優(yōu)化步驟
當(dāng)面對一個需要優(yōu)化的 SQL 時，我們有哪幾種排查思路呢？
通過 show status 命令了解 SQL 執(zhí)行次數(shù)
首先，我們可以使用 show status 命令查看服務(wù)器狀態(tài)信息。show status 命令會顯示每個服務(wù)器變量 variable_name 和 value，狀態(tài)變量是只讀的。如果使用 SQL 命令，可以使用 like 或者 where 條件來限制結(jié)果。like 可以對變量名做標(biāo)準(zhǔn)模式匹配。
圖我沒有截全，下面還有很多變量，讀者可以自己嘗試一下。也可以在操作系統(tǒng)上使用 mysqladmin extended-status 命令來獲取這些消息。
但是我執(zhí)行 mysqladmin extended-status 后，出現(xiàn)這個錯誤。
應(yīng)該是我沒有輸入密碼的原因，使用 mysqladmin -P3306 -uroot -p -h127.0.0.1 -r -i 1 extended-status 后，問題解決。
這里需要注意一下 show status 命令中可以添加統(tǒng)計結(jié)果的級別，這個級別有兩個
session 級：默認(rèn)當(dāng)前鏈接的統(tǒng)計結(jié)果
global 級：自數(shù)據(jù)庫上次啟動到現(xiàn)在的統(tǒng)計結(jié)果
如果不指定統(tǒng)計結(jié)果級別的話，默認(rèn)使用 session 級別。
對于 show status 查詢出來的統(tǒng)計結(jié)果，有兩類參數(shù)需要注意下，一類是以 Com_ 為開頭的參數(shù)，一類是以 Innodb_ 為開頭的參數(shù)。
下面是 Com_ 為開頭的參數(shù)，參數(shù)很多，我同樣沒有截全。
Com_xxx 表示的是每個 xxx 語句執(zhí)行的次數(shù)，我們通常關(guān)心的是 select 、insert 、update、delete 語句的執(zhí)行次數(shù)，即
Com_select：執(zhí)行 select 操作的次數(shù)，一次查詢會使結(jié)果 + 1。
Com_insert：執(zhí)行 INSERT 操作的次數(shù)，對于批量插入的 INSERT 操作，只累加一次。
Com_update：執(zhí)行 UPDATE 操作的次數(shù)。
Com_delete：執(zhí)行 DELETE 操作的次數(shù)。
以 Innodb_ 為開頭的參數(shù)主要有
Innodb_rows_read：執(zhí)行 select 查詢返回的行數(shù)。
Innodb_rows_inserted：執(zhí)行 INSERT 操作插入的行數(shù)。
Innodb_rows_updated：執(zhí)行 UPDATE 操作更新的行數(shù)。
Innodb_rows_deleted：執(zhí)行 DELETE 操作刪除的行數(shù)。
通過上面這些參數(shù)執(zhí)行結(jié)果的統(tǒng)計，我們能夠大致了解到當(dāng)前數(shù)據(jù)庫是以更新（包括插入、刪除）為主還是查詢?yōu)橹鳌?/p>
除此之外，還有一些其他參數(shù)用于了解數(shù)據(jù)庫的基本情況。
Connections：查詢 MySQL 數(shù)據(jù)庫的連接次數(shù)，這個次數(shù)是不管連接是否成功都算上。
Uptime：服務(wù)器的工作時間。
Slow_queries：滿查詢次數(shù)。
Threads_connected：查看當(dāng)前打開的連接的數(shù)量。
下面這個博客匯總了幾乎所有 show status 的參數(shù)，可以當(dāng)作參考手冊。
https://blog.csdn.net/ayay_870621/article/details/88633092
定位執(zhí)行效率較低的 SQL
定位執(zhí)行效率比較慢的 SQL 語句，一般有兩種方式
可以通過慢查詢?nèi)罩?/strong>來定位哪些執(zhí)行效率較低的 SQL 語句。
MySQL 中提供了一個慢查詢的日志記錄功能，可以把查詢 SQL 語句時間大于多少秒的語句寫入慢查詢?nèi)罩?，日常維護(hù)中可以通過慢查詢?nèi)罩镜挠涗浶畔⒖焖贉?zhǔn)確地判斷問題所在。用 --log-slow-queries 選項啟動時，mysqld 會寫一個包含所有執(zhí)行時間超過 long_query_time 秒的 SQL 語句的日志文件，通過查看這個日志文件定位效率較低的 SQL 。
比如我們可以在 my.cnf 中添加如下代碼，然后退出重啟 MySQL。
log-slow-queries = /tmp/mysql-slow.log
long_query_time = 2
通常我們設(shè)置最長的查詢時間是 2 秒，表示查詢時間超過 2 秒就記錄了，通常情況下 2 秒就夠了，然而對于很多 WEB 應(yīng)用來說，2 秒時間還是比較長的。
也可以通過命令來開啟：
我們先查詢 MySQL 慢查詢?nèi)罩臼欠耖_啟
show variables like "%slow%";
啟用慢查詢?nèi)罩?/p>
set global slow_query_log='ON';
然后再次查詢慢查詢是否開啟
如圖所示，我們已經(jīng)開啟了慢查詢?nèi)罩尽?/p>
慢查詢?nèi)罩緯诓樵兘Y(jié)束以后才記錄，所以在應(yīng)用反應(yīng)執(zhí)行效率出現(xiàn)問題的時候慢查詢?nèi)罩静⒉荒芏ㄎ粏栴}，此時應(yīng)該使用 show processlist 命令查看當(dāng)前 MySQL 正在進(jìn)行的線程。包括線程的狀態(tài)、是否鎖表等，可以實時的查看 SQL 執(zhí)行情況。同樣，使用mysqladmin processlist語句也能得到此信息。
下面就來解釋一下各個字段對應(yīng)的概念
Id ：Id 就是一個標(biāo)示，在我們使用 kill 命令殺死進(jìn)程的時候很有用，比如 kill 進(jìn)程號。
User：顯示當(dāng)前的用戶，如果不是 root，這個命令就只顯示你權(quán)限范圍內(nèi)的 SQL 語句。
Host：顯示 IP ，用于追蹤問題
Db：顯示這個進(jìn)程目前連接的是哪個數(shù)據(jù)庫，為 null 是還沒有 select 數(shù)據(jù)庫。
Command：顯示當(dāng)前連接鎖執(zhí)行的命令，一般有三種：查詢 query，休眠 sleep，連接 connect。
Time：這個狀態(tài)持續(xù)的時間，單位是秒
State：顯示當(dāng)前 SQL 語句的狀態(tài)，非常重要，下面會具體解釋。
Info：顯示這個 SQL 語句。
State 列非常重要，關(guān)于這個列的內(nèi)容比較多，讀者可以參考一下這篇文章
https://blog.csdn.net/weixin_34357436/article/details/91768402
這里面涉及線程的狀態(tài)、是否鎖表等選項，可以實時的查看 SQL 的執(zhí)行情況，同時對一些鎖表進(jìn)行優(yōu)化。
通過 EXPLAIN 命令分析 SQL 的執(zhí)行計劃
通過以上步驟查詢到效率低的 SQL 語句后，可以通過 EXPLAIN 或者 DESC 命令獲取 MySQL 如何執(zhí)行 SELECT 語句的信息，包括在 SELECT 語句執(zhí)行過程中表如何連接和連接的順序。
比如我們使用下面這條 SQL 語句來分析一下執(zhí)行計劃
explain select * from test1;
上表中涉及內(nèi)容如下
select_type：表示常見的 SELECT 類型，常見的有 SIMPLE，SIMPLE 表示的是簡單的 SQL 語句，不包括 UNION 或者子查詢操作，比如下面這段就是 SIMPLE 類型。
PRIMARY ，查詢中最外層的 SELECT（如兩表做 UNION 或者存在子查詢的外層的表操作為 PRIMARY，內(nèi)層的操作為 UNION），比如下面這段子查詢。
UNION，在 UNION 操作中，查詢中處于內(nèi)層的 SELECT（內(nèi)層的 SELECT 語句與外層的 SELECT 語句沒有依賴關(guān)系時）。
SUBQUERY：子查詢中首個SELECT（如果有多個子查詢存在），如我們上面的查詢語句，子查詢第一個是 sr（sys_role）表，所以它的 select_type 是 SUBQUERY。
table ，這個選項表示輸出結(jié)果集的表。
type，這個選項表示表的連接類型，這個選項很有深入研究的價值，因為很多 SQL 的調(diào)優(yōu)都是圍繞 type 來講的，但是這篇文章我們主要圍繞優(yōu)化方式來展開的，type 這個字段我們暫時作為了解，這篇文章不過多深入。
type 這個字段會牽扯到連接的性能，它的不同類型的性能由好到差分別是
system ：表中僅有一條數(shù)據(jù)時，該表的查詢就像查詢常量表一樣。
const ：當(dāng)表中只有一條記錄匹配時，比如使用了表主鍵（primary key）或者表唯一索引（unique index）進(jìn)行查詢。
eq-ref ：表示多表連接時使用表主鍵或者表唯一索引，比如
select A.text, B.text where A.ID = B.ID這個查詢語句，對于 A 表中的每一個 ID 行，B 表中都只能有唯一的 B.Id 來進(jìn)行匹配時。
ref ：這個類型不如上面的 eq-ref 快，因為它表示的是因為對于表 A 中掃描的每一行，表 C 中有幾個可能的行，C.ID 不是唯一的。
ref_or_null ：與 ref 類似，只不過這個選項包含對 NULL 的查詢。
index_merge ：查詢語句使用了兩個以上的索引，比如經(jīng)常在有 and 和 or 關(guān)鍵字出現(xiàn)的場景，但是在由于讀取索引過多導(dǎo)致其性能有可能還不如 range（后面說）。
unique_subquery ：這個選項經(jīng)常用在 in 關(guān)鍵字后面，子查詢帶有 where 關(guān)鍵字的子查詢中，用 sql 來表示就是這樣
value IN (SELECT primary_key FROM single_table WHERE some_expr)range ：索引范圍查詢，常見于使用 =，<>，>，>=，<，<=，IS NULL，<=>，BETWEEN，IN() 或者 like 等運算符的查詢中。
index ：索引全表掃描，把索引從頭到尾掃一遍。
all ：這個我們接觸的最多了，就是全表查詢，select * from xxx ，性能最差。
上面就是 type 內(nèi)容的大致解釋，關(guān)于 type 我們經(jīng)常會在 SQL 調(diào)優(yōu)的環(huán)節(jié)使用 explain 分析其類型，然后改進(jìn)查詢方式，越靠近 system 其查詢效率越高，越靠近 all 其查詢效率越低。
possible_keys ：表示查詢時，可能使用的索引。
key ：表示實際使用的索引。
key_len ：索引字段的長度。
rows ：掃描行的數(shù)量。
filtered ：通過查詢條件查詢出來的 SQL 數(shù)量占用總行數(shù)的比例。
extra ：執(zhí)行情況的描述。
通過上面的分析，我們可以大致確定 SQL 效率低的原因，一種非常有效的提升 SQL 查詢效率的方式就是使用索引，接下來我會講解一下如何使用索引提高查詢效率。
索引
索引是數(shù)據(jù)庫優(yōu)化中最常用也是最重要的手段，通過使用不同的索引可以解決大多數(shù) SQL 性能問題，也是面試經(jīng)常會問到的優(yōu)化方式，圍繞著索引，面試官能讓你造出火箭來，所以總結(jié)一點就是索引非常非常重！要！不只是使用，你還要懂其原！理！
索引介紹
索引的目的就是用于快速查找某一列的數(shù)據(jù)，對相關(guān)數(shù)據(jù)列使用索引能夠大大提高查詢操作的性能。不使用索引，MySQL 必須從第一條記錄開始讀完整個表，直到找出相關(guān)的行，表越大查詢數(shù)據(jù)所花費的時間就越多。如果表中查詢的列有索引，MySQL 能夠快速到達(dá)一個位置去搜索數(shù)據(jù)文件，而不必查看所有數(shù)據(jù)，那么將會節(jié)省很大一部分時間。
索引分類
先來了解一下索引都有哪些分類。
全局索引(FULLTEXT)：全局索引，目前只有 MyISAM 引擎支持全局索引，它的出現(xiàn)是為了解決針對文本的模糊查詢效率較低的問題，并且只限于 CHAR、VARCHAR 和 TEXT 列。哈希索引(HASH)：哈希索引是 MySQL 中用到的唯一 key-value 鍵值對的數(shù)據(jù)結(jié)構(gòu)，很適合作為索引。HASH 索引具有一次定位的好處，不需要像樹那樣逐個節(jié)點查找，但是這種查找適合應(yīng)用于查找單個鍵的情況，對于范圍查找，HASH 索引的性能就會很低。默認(rèn)情況下，MEMORY 存儲引擎使用 HASH 索引，但也支持 BTREE 索引。B-Tree 索引：B 就是 Balance 的意思，BTree 是一種平衡樹，它有很多變種，最常見的就是 B+ Tree，它被 MySQL 廣泛使用。R-Tree 索引：R-Tree 在 MySQL 很少使用，僅支持 geometry 數(shù)據(jù)類型，支持該類型的存儲引擎只有MyISAM、BDb、InnoDb、NDb、Archive幾種，相對于 B-Tree 來說，R-Tree 的優(yōu)勢在于范圍查找。
從邏輯上來對 MySQL 進(jìn)行分類，主要分為下面這幾種
普通索引：普通索引是最基礎(chǔ)的索引類型，它沒有任何限制 。創(chuàng)建方式如下
create index normal_index on cxuan003(id);
刪除方式
drop index normal_index on cxuan003;
唯一索引：唯一索引列的值必須唯一，允許有空值，如果是組合索引，則列值的組合必須唯一，創(chuàng)建方式如下
create unique index normal_index on cxuan003(id);
主鍵索引：是一種特殊的索引，一個表只能有一個主鍵，不允許有空值。一般是在建表的時候同時創(chuàng)建主鍵索引。
CREATE TABLEtable(
idint(11) NOT NULL AUTO_INCREMENT ,
titlechar(255) NOT NULL ,
PRIMARY KEY (id)
)
組合索引：指多個字段上創(chuàng)建的索引，只有在查詢條件中使用了創(chuàng)建索引時的第一個字段，索引才會被使用。使用組合索引時遵循最左前綴原則，下面我們就會創(chuàng)建組合索引。
全文索引：主要用來查找文本中的關(guān)鍵字，而不是直接與索引中的值相比較，目前只有 char、varchar，text 列上可以創(chuàng)建全文索引，創(chuàng)建表的適合添加全文索引
CREATE TABLEtable(
idint(11) NOT NULL AUTO_INCREMENT ,
titlechar(255) CHARACTER NOT NULL ,
contenttext CHARACTER NULL ,
timeint(10) NULL DEFAULT NULL ,
PRIMARY KEY (id),
FULLTEXT (content)
);當(dāng)然也可以直接創(chuàng)建全局索引
CREATE FULLTEXT INDEX index_content ON article(content)
索引使用
索引可以在創(chuàng)建表的時候進(jìn)行創(chuàng)建，也可以單獨創(chuàng)建，下面我們采用單獨創(chuàng)建的方式，我們在 cxuan004 上創(chuàng)建前綴索引
我們使用 explain 進(jìn)行分析，可以看到 cxuan004 使用索引的情況
如果不想使用索引，可以刪除索引，索引的刪除語法是
索引使用細(xì)則
我們在 cxuan005 上根據(jù) id 和 hash 創(chuàng)建一個復(fù)合索引，如下所示
create index id_hash_index on cxuan005(id,hash);
然后根據(jù) id 進(jìn)行執(zhí)行計劃的分析
explain select * from cxuan005 where id = '333';
可以發(fā)現(xiàn)，即使 where 條件中使用的不是復(fù)合索引（Id 、hash），索引仍然能夠使用，這就是索引的前綴特性。但是如果只按照 hash 進(jìn)行查詢的話，索引就不會用到。
explain select * from cxuan005 where hash='8fd1f12575f6b39ee7c6d704eb54b353';
如果 where 條件使用了 like 查詢，并且 % 不在第一個字符，索引才可能被使用。
對于復(fù)合索引來說，只能使用 id 進(jìn)行 like 查詢，因為 hash 列不管怎么查詢都不會走索引。
explain select * from cxuan005 where id like '%1';
可以看到，如果第一個字符是 % ，則沒有使用索引。
explain select * from cxuan005 where id like '1%';
如果使用了 % 號，就會觸發(fā)索引。
如果列名是索引的話，那么對列名進(jìn)行 NULL 查詢，將會觸發(fā)索引。
explain select * from cxuan005 where id is null;
還有一些情況是存在索引但是 MySQL 并不會使用的情況。
最簡單的，如果使用索引后比不使用索引的效率還差，那么 MySQL 就不會使用索引。
如果 SQL 中使用了 OR 條件，OR 前的條件列有索引，而后面的列沒有索引的話，那么涉及到的索引都不會使用，比如 cxuan005 表中，只有 id 和 hash 字段有索引，而 info 字段沒有索引，那么我們使用 or 進(jìn)行查詢。
explain select * from cxuan005 where id = 111 and info = 'cxuan';
我們從 explain 的執(zhí)行結(jié)果可以看到，雖然 possible_keys 選項上仍然有 id_hash_index 索引，但是從 key、key_len 可以得知，這條 SQL 語句并未使用索引。
在帶有復(fù)合索引的列上查詢不是第一列的數(shù)據(jù)，也不會使用索引。
explain select * from cxuan005 where hash = '8fd1f12575f6b39ee7c6d704eb54b353';
如果 where 條件的列參與了計算，那么也不會使用索引
explain select * from cxuan005 where id + '111' = '666';
索引列使用函數(shù)，一樣也不會使用索引
explain select * from cxuan005 where concat(id,'111') = '666';
索引列使用了 like ，并且
%位于第一個字符，則不會使用索引。在 order by 操作中，排序的列同時也在 where 語句中，將不會使用索引。
當(dāng)數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)換時，比如 varchar 不加單引號可能轉(zhuǎn)換為 int 類型時，會使索引無效，觸發(fā)全表掃描。比如下面這兩個例子能夠顯而易見的說明這一點
在索引列上使用 IS NOT NULL 操作
在索引字段上使用 <>，!=。不等于操作符是永遠(yuǎn)不會用到索引的，因此對它的處理只會產(chǎn)生全表掃描。
關(guān)于設(shè)置索引但是索引沒有生效的場景還有很多，這個需要小伙伴們工作中不斷總結(jié)和完善，不過我上面總結(jié)的這些索引失效的情景，能夠覆蓋大多數(shù)索引失效的場景了。
查看索引的使用情況
在 MySQL 索引的使用過程中，有一個 Handler_read_key 值，這個值表示了某一行被索引值讀的次數(shù)。Handler_read_key 的值比較低的話，則表明增加索引得到的性能改善不是很理想，可能索引使用的頻率不高。
還有一個值是 Handler_read_rnd_next，這個值高則意味著查詢運行效率不高，應(yīng)該建立索引來進(jìn)行搶救。這個值的含義是在數(shù)據(jù)文件中讀下一行的請求數(shù)。如果正在進(jìn)行大量的表掃描，Handler_read_rnd_next 的值比較高，就說明表索引不正確或?qū)懭氲牟樵儧]有利用索引。
MySQL 分析表、檢查表和優(yōu)化表
對于大多數(shù)開發(fā)者來說，他們更傾向于解決簡單 SQL的優(yōu)化，而復(fù)雜 SQL 的優(yōu)化交給了公司的 DBA 來做。
下面就從普通程序員的角度和你聊幾個簡單的優(yōu)化方式。
MySQL 分析表
分析表用于分析和存儲表的關(guān)鍵字分布，分析的結(jié)果可以使得系統(tǒng)得到準(zhǔn)確的統(tǒng)計信息，使得 SQL 生成正確的執(zhí)行計劃。如果用于感覺實際執(zhí)行計劃與預(yù)期不符，可以執(zhí)行分析表來解決問題，分析表語法如下
analyze table cxuan005;
分析結(jié)果涉及到的字段屬性如下
Table：表示表的名稱；
Op：表示執(zhí)行的操作，analyze 表示進(jìn)行分析操作，check 表示進(jìn)行檢查查找，optimize 表示進(jìn)行優(yōu)化操作；
Msg_type：表示信息類型，其顯示的值通常是狀態(tài)、警告、錯誤和信息這四者之一；
Msg_text：顯示信息。
對表的定期分析可以改善性能，應(yīng)該成為日常工作的一部分。因為通過更新表的索引信息對表進(jìn)行分析，可改善數(shù)據(jù)庫性能。
MySQL 檢查表
數(shù)據(jù)庫經(jīng)?？赡苡龅藉e誤，比如數(shù)據(jù)寫入磁盤時發(fā)生錯誤，或是索引沒有同步更新，或是數(shù)據(jù)庫未關(guān)閉 MySQL 就停止了。遇到這些情況，數(shù)據(jù)就可能發(fā)生錯誤： Incorrect key file for table: ' '. Try to repair it. 此時，我們可以使用 Check Table 語句來檢查表及其對應(yīng)的索引。
check table cxuan005;
檢查表的主要目的就是檢查一個或者多個表是否有錯誤。Check Table 對 MyISAM 和 InnoDB 表有作用。Check Table 也可以檢查視圖的錯誤。
MySQL 優(yōu)化表
MySQL 優(yōu)化表適用于刪除了大量的表數(shù)據(jù)，或者對包含 VARCHAR、BLOB 或則 TEXT 命令進(jìn)行大量修改的情況。MySQL 優(yōu)化表可以將大量的空間碎片進(jìn)行合并，消除由于刪除或者更新造成的空間浪費情況。它的命令如下
optimize table cxuan005;
我的存儲引擎是 InnoDB 引擎，但是從圖可以知道，InnoDB 不支持使用 optimize 優(yōu)化，建議使用 recreate + analyze 進(jìn)行優(yōu)化。optimize 命令只對 MyISAM 、BDB 表起作用。
完
顛覆認(rèn)知——Redis會遇到的15個「坑」，你踩過幾個？
漫畫 | 沒有一個程序員能拒絕這樣的女朋友