上篇回顧

繼上篇：MySQL慢查詢（上）：你知道為啥會慢么？

在上一篇內(nèi)容中，我們一起探索了這些內(nèi)容：

1）SQL執(zhí)行過程

2）查詢SQL為什么會慢

通過梳理 MySQL中的 SQL執(zhí)行過程我們發(fā)現(xiàn)，任何流程的執(zhí)行都存在其執(zhí)行環(huán)境和規(guī)則，主要導(dǎo)致慢查詢最根本的問題就是需要訪問的數(shù)據(jù)太多，導(dǎo)致查詢不可避免的需要篩選大量的數(shù)據(jù)。

如果將MySQL慢查詢作為一個問題來拆解分析的話，上一篇算是問題分析，那今天來跟大家聊聊問題定位和問題解決。

問題處理流程

本文主要內(nèi)容包括：

1、如何定位問題呢？

2、幾種實用解決方案

廢話不多說，直接開干~

1、如何定位問題呢？

發(fā)現(xiàn)了慢查詢之后，關(guān)于如何定位問題發(fā)生原因，最常用的方法就是利用EXPLAIN關(guān)鍵字模擬查詢優(yōu)化器執(zhí)行查詢SQL，從而知道MySQL是如何處理你的查詢SQL，通過執(zhí)行計劃來分析性能瓶頸。

通常我們使用EXPLAIN，會得到如下下的執(zhí)行計劃信息：

關(guān)于各字段含義，大家可以通過檢索自行了解，在此就不再過多贅述。

關(guān)于定位分析問題，關(guān)鍵看如下幾點：

1）select_type

表示查詢類型，用于區(qū)別普通查詢、聯(lián)合查詢、子查詢等復(fù)雜查詢。

2）type

顯示查詢使用類型，從好到差依次為：system > const > eq_ref > ref > range > index > all

3）possible_keys 和 key

分別指可能應(yīng)用的索引和實際應(yīng)用的索引。

注意：查詢中若使用了覆蓋索引（select 后要查詢的字段剛好和創(chuàng)建的索引字段完全相同），則該索引僅出現(xiàn)在key列表中。

4）rows

大致估算出找到所需記錄所需要讀取的行數(shù)（從效率上來講，數(shù)值越小越好）

5）Extra

重要的額外信息。包含MySQL解決查詢的詳細(xì)信息，也是關(guān)鍵參考項之一。

2、幾種實用解決方案

我們通過EXPLAIN關(guān)鍵字模擬查詢優(yōu)化器執(zhí)行查詢SQL，發(fā)現(xiàn)了慢查詢問題原因，那看看如何才能有效解決呢？

推薦幾種較為實用的解決方案給大家。

2.1 優(yōu)化數(shù)據(jù)結(jié)構(gòu)

2.1.1 選擇索引的數(shù)據(jù)類型

MySQL支持很多數(shù)據(jù)類型，選擇合適的數(shù)據(jù)類型存儲數(shù)據(jù)對性能有很大的影響。

通常來說，可以遵循以下一些指導(dǎo)原則：

(1)越小的數(shù)據(jù)類型通常更好：越小的數(shù)據(jù)類型通常在磁盤、內(nèi)存和CPU緩存中都需要更少的空間，處理起來更快。

(2)簡單的數(shù)據(jù)類型更好：整型數(shù)據(jù)比起字符，處理開銷更小，因為字符串得比較更復(fù)雜。在MySQL中，應(yīng)該用內(nèi)置的日期和時間數(shù)據(jù)類型，而不是用字符串來存儲時間；以及用整型數(shù)據(jù)類型存儲IP地址。

(3)盡量避免NULL：應(yīng)該指定列為NOT NULL，除非你想存儲NULL。在MySQL中，含有空值的列很難進(jìn)行查詢優(yōu)化，因為它們使得索引、索引的統(tǒng)計信息以及比較運算更加復(fù)雜。你應(yīng)該用0、一個特殊的值或者一個空串代替空值。

2.1.2 范式與反范式

范式化

范式化模型要求滿足下面三大范式：

1）數(shù)據(jù)庫表中每個字段只包含最小的信息屬性，不能再進(jìn)行細(xì)化分解；

2）（在滿足1的基礎(chǔ)上）模型含有主鍵，非主鍵字段依賴主鍵；

比如用戶這個模型，它的主鍵是用戶ID，那么用戶模型其它字段都應(yīng)該依賴于用戶ID

如商品ID和用戶沒有直接關(guān)系，則這個屬性不應(yīng)該放到用戶模型而應(yīng)該放到“用戶-商品”中間表。

3）（在滿足2的基礎(chǔ)上）模型非主鍵字段不能相互依賴。

訂單表(訂單編號，訂購日期，顧客編號，顧客姓名，……)

初看該表沒有問題，滿足第二范式，每列都和主鍵列”訂單編號”相關(guān)。

再細(xì)看你會發(fā)現(xiàn)“顧客姓名”和“顧客編號”相關(guān)，“顧客編號”和“訂單編號”又相關(guān)，最后經(jīng)過傳遞依賴，“顧客姓名”也和“訂單編號”相關(guān)。

為了滿足第三范式，應(yīng)去掉“顧客姓名”列，放入客戶表中。

反范式化

反范式化模型即不滿足范式化的模型。主要是為了性能和效率的考慮適當(dāng)?shù)倪`反范式化設(shè)計要求，允許存在少量的數(shù)據(jù)冗余，即以空間換時間。

小結(jié)

可見一個良好而實用的數(shù)據(jù)模型往往是依賴于具體的需求場景的，在設(shè)計數(shù)據(jù)模型之前，仔細(xì)分析需求場景，不僅能提高效率，也能有效規(guī)避后期可能遇到的一些意外麻煩。

范式化設(shè)計和反范式化設(shè)計的優(yōu)劣對比如下：

1、范式化可以盡量的減少數(shù)據(jù)冗余

2、范式化的更新操作比反范式化更快

3、范式化的表通常比反范式化的表要小

4、反范式化減少表的關(guān)聯(lián)

5、反范式化相比范式化可以更好地對索引進(jìn)行優(yōu)化，例如使用覆蓋索引。

關(guān)于數(shù)據(jù)庫范式與反范式設(shè)計，詳情可參考我之前的一篇文章：數(shù)據(jù)庫范式與反范式設(shè)計，是一門藝術(shù)

2.2 應(yīng)用索引策略

索引（MySQL中也被稱為“鍵Key”），是存儲引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)。索引對于良好的性能非常關(guān)鍵，尤其當(dāng)表中的數(shù)據(jù)量越來越大時，索引對性能的影響愈發(fā)重要（不恰當(dāng)?shù)乃饕龑S數(shù)據(jù)量增大時，性能急劇下降）。

舉例如下情況：

假設(shè)數(shù)據(jù)庫中一個表有10^6條記錄，DBMS的頁面大小為4K（約可存儲100條記錄）。

如果沒有索引，查詢將對整個表進(jìn)行掃描，最壞的情況下，如果所有數(shù)據(jù)頁都不在內(nèi)存，需要讀取10^4個頁面，如果這10^4個頁面在磁盤上隨機分布，需要進(jìn)行10^4次I/O，假設(shè)磁盤每次I/O時間為10ms(忽略數(shù)據(jù)傳輸時間)，則總共需要100s(但實際上要好很多很多)。

如果對之建立B-Tree索引，則只需要進(jìn)行l(wèi)og100(10^6)=3次頁面讀取，最壞情況下耗時30ms。這就是索引帶來的效果。

了解了索引的優(yōu)點之后，其實正確的創(chuàng)建和使用索引是實現(xiàn)高性能查詢的基礎(chǔ)。

可以利用B-Tree索引進(jìn)行全關(guān)鍵字、關(guān)鍵字范圍和關(guān)鍵字前綴查詢，當(dāng)然，如果想使用索引，必須保證按索引的最左邊前綴(leftmost prefix of the index)來進(jìn)行查詢。

2.2.1 最左邊前綴主要規(guī)則

匹配全值(Match the full value)：對索引中的所有列都指定具體的值。例如，上圖中索引可以幫助你查找出生于1960-01-01的Cuba Allen。

匹配最左前綴(Match a leftmost prefix)：你可以利用索引查找last name為Allen的人，僅僅使用索引中的第1列。

匹配列前綴(Match a column prefix)：例如，你可以利用索引查找last name以J開始的人，這僅僅使用索引中的第1列。

匹配值的范圍查詢(Match a range of values)：可以利用索引查找last name在Allen和Barrymore之間的人，僅僅使用索引中第1列。

匹配部分精確而其它部分進(jìn)行范圍匹配(Match one part exactly and match a range on another part)：可以利用索引查找last name為Allen，而first name以字母K開始的人。

僅對索引進(jìn)行查詢(Index-only queries)：如果查詢的列都位于索引中，則不需要讀取元組的值。

由于B-樹中的節(jié)點都是順序存儲的，所以可以利用索引進(jìn)行查找(找某些值)，也可以對查詢結(jié)果進(jìn)行ORDER BY。

當(dāng)然，使用B-tree索引有以下一些限制：

查詢必須從索引的最左邊的列開始。關(guān)于這點已經(jīng)提了很多遍了。例如你不能利用索引查找在某一天出生的人。

不能跳過某一索引列。例如，你不能利用索引查找last name為Smith且出生于某一天的人。

存儲引擎不能使用索引中范圍條件右邊的列。例如，如果你的查詢語句為WHERE lastname="Smith" AND firstname LIKE 'J%' AND dob='1976-12-23'，則該查詢只會使用索引中的前兩列，因為LIKE是范圍查詢。

2.2.2 聚簇索引

聚簇索引保證關(guān)鍵字的值相近的元組存儲的物理位置也相同（所以字符串類型不宜建立聚簇索引，特別是隨機字符串，會使得系統(tǒng)進(jìn)行大量的移動操作），且一個表只能有一個聚簇索引。因為由存儲引擎實現(xiàn)索引，所以，并不是所有的引擎都支持聚簇索引。目前，只有solidDB和InnoDB支持。

InnoDB對主鍵建立聚簇索引。如果你不指定主鍵，InnoDB會用一個具有唯一且非空值的索引來代替。如果不存在這樣的索引，InnoDB會定義一個隱藏的主鍵，然后對其建立聚簇索引。

2.3 查詢緩存

MySQL查詢緩存會保存查詢返回的完整結(jié)果。當(dāng)查詢命中緩存，MySQL會立刻返回結(jié)果，而跳過了后續(xù)解析、優(yōu)化以及執(zhí)行階段，會有效提升查詢性能。

但是查詢緩存不是銀彈，它也會存在一些問題。

2.3.1 查詢緩存注意事項

1）緩存情況嚴(yán)格

存在一些不確定函數(shù)情況無法使用查詢緩存，如：NOW()、CURRENT_DATE() 等類似的函數(shù)；

超過 query_cache_size （設(shè)置查詢緩存空間大?。┑牟樵兘Y(jié)果無法被緩存；

同時大小寫敏感，只有字符串相等情況下查詢SQL才使用相同緩存。

-- 不會使用同一個緩存

select name from users where id = 1;

SELECT name FROM users WHERE id = 1;

2）緩存易失效

假如緩存過查詢結(jié)果，但是由于查詢緩存設(shè)置內(nèi)存不足，新緩存加入時MySQL會將某些緩存逐出，導(dǎo)致后續(xù)查詢未命中。同時數(shù)據(jù)結(jié)構(gòu)及數(shù)據(jù)修改，內(nèi)存不足，緩存碎片都會導(dǎo)致緩存失效。

2.3.2 小結(jié)

查詢緩存對應(yīng)用程序完全透明，應(yīng)用程序無需關(guān)心MySQL是通過查詢緩存返回的還是實際執(zhí)行返回的結(jié)果。但隨著目前服務(wù)器性能越來越強，查詢緩存被發(fā)現(xiàn)是一個影響服務(wù)器擴展性的因素，它很可能成為整個服務(wù)器的資源競爭點，大家采用生產(chǎn)環(huán)境開啟應(yīng)用時候一定要慎重考量。

2.4 重構(gòu)查詢方式

優(yōu)化慢查詢時候，我們可以轉(zhuǎn)換下思路，我們的目標(biāo)是找到一個更優(yōu)的方法獲取時間需要的結(jié)果，而不是一定從MySQL獲取一模一樣的結(jié)果集。重構(gòu)查詢的技巧很有必要。

2.4.1 復(fù)雜查詢拆分

將一個復(fù)雜查詢拆分多個簡單查詢，考慮是否需要將一個復(fù)雜查詢拆分為多個簡單查詢。

實際開發(fā)過程中，大家往往會強調(diào)數(shù)據(jù)庫層完成盡可能多的工作，這樣做的初衷是認(rèn)為網(wǎng)絡(luò)通信、查詢解析和優(yōu)化是一件代價很高的事情，其實MySQL從設(shè)計上讓連接和斷開都很輕量級，同時在返回一個小查詢結(jié)果方面很高效。況且目前網(wǎng)絡(luò)速度也比之前快很多，無論是帶寬還是延遲。

對于大查詢我們要“分而治之”，將大查詢切分成多個小查詢。不過在一次查詢能夠勝任的情況下還拆成多個獨立查詢就不明智了。

例如：做數(shù)據(jù)庫做10次查詢，每次返回一行記錄。

2.4.2 分解關(guān)聯(lián)查詢

將關(guān)聯(lián)查詢進(jìn)行分解，對每一個表進(jìn)行一次單表查詢，然后將結(jié)果在應(yīng)用程序中進(jìn)行關(guān)聯(lián)。

例如：

SELECT * FROM users LEFT JOIN orders ON orders.user_id = users.user_id

LEFT JOIN goods ON goods.good_id = orders.good_id

WHERE users.name = 'zhangsan';

以上查詢可以分解成下面的查詢來代替：

SELECT * FROM users WHERE users.name ='zhangsan';

SELECT * FROM orders WHERE orders.user_id =103;

SELECT * FROM goods WHERE goods.good_idIN（123,456,789）;

為什么要這樣做呢？看起來好像沒有什么好處，而且返回數(shù)據(jù)結(jié)果也是一致的。實際上利用分解查詢的方式來重構(gòu)查詢有很大的優(yōu)勢，主要表現(xiàn)為：

1）將查詢分解后，執(zhí)行單個查詢可減少鎖的競爭；

2）應(yīng)用層做關(guān)聯(lián)，更容易對數(shù)據(jù)庫進(jìn)行拆分，更易于做到高性能和可擴展；

3）減少冗余記錄的查詢（在應(yīng)用層做關(guān)聯(lián)，表示對某條記錄應(yīng)用只需要查詢一次，而在數(shù)據(jù)庫中做關(guān)聯(lián)查詢，則可能需要重復(fù)訪問一部分?jǐn)?shù)據(jù)。）

小結(jié)

MySQL慢查詢問題細(xì)數(shù)起來，林林總總太多了，但行之有效的無外乎這幾種：

1、優(yōu)化數(shù)據(jù)結(jié)構(gòu)

2、應(yīng)用索引策略

3、查詢緩存

實踐出真知，如果大家有任何其他好的解決方法可以留言與我交流，希望對你的工作有所幫助，謝謝！

- END -

作者：架構(gòu)精進(jìn)之路，專注軟件架構(gòu)研究，技術(shù)學(xué)習(xí)與個人成長，關(guān)注并私信我回復(fù)“01”，送你一份程序員成長進(jìn)階大禮包。

Thanks for reading!