發(fā)現(xiàn)的一些問(wèn)題

• 問(wèn)題1

在過(guò)去的半年時(shí)間里，研發(fā)團(tuán)隊(duì)內(nèi)部嘗試抓了一波兒慢查詢(xún)SQL跟進(jìn)處理率。發(fā)現(xiàn)有些同學(xué)對(duì)于慢查詢(xún)處理的思路就是看看有沒(méi)有用到索引，沒(méi)有用到就試圖加一個(gè)，實(shí)在不行就甩鍋給這種情況是歷史設(shè)計(jì)問(wèn)題或者自行判定為用戶(hù)特殊操作下觸發(fā)的小概率事件，隨即便申請(qǐng)豁免掉...   其實(shí)問(wèn)題沒(méi)有根本上解決。

• 問(wèn)題2

還有就是網(wǎng)絡(luò)上經(jīng)常可以看到一些類(lèi)似這樣的文章：

“慢SQL性能優(yōu)化大全”

“慢SQL性能優(yōu)化看這篇就夠了”...  

其實(shí)內(nèi)容大同小異，要么建議加索引，要么建議重寫(xiě)SQL....

怎么說(shuō)呢？知識(shí)點(diǎn)是對(duì)的，但不全面，這個(gè)很容易誤導(dǎo)新同學(xué)，哈哈哈。

本文初衷

在業(yè)務(wù)項(xiàng)目發(fā)展過(guò)程中，我們常常會(huì)面對(duì)要處理 MySQL 慢查詢(xún)問(wèn)題，那我們應(yīng)該如何分析解決問(wèn)題呢？

部分同學(xué)在處理MySQL慢查詢(xún)時(shí)候主要思路是加索引來(lái)解決，確實(shí)加索引是一個(gè)很好的解決問(wèn)題的手段，但不是全部。既然慢查詢(xún)作為問(wèn)題，那就需要明確問(wèn)題發(fā)生原因，和解決問(wèn)題路徑分析， 授人以魚(yú)不如授人以漁，讓我們一起來(lái)解鎖 ?? 下MySQL處理慢查詢(xún)的正確姿勢(shì)。

本文計(jì)劃主要讓大家搞明白查詢(xún)SQL為什么會(huì)變慢，廢話不多說(shuō)，直接開(kāi)干~

寫(xiě)在前面

在業(yè)務(wù)項(xiàng)目發(fā)展過(guò)程中，我們常常會(huì)面對(duì)要處理 MySQL 慢查詢(xún)問(wèn)題，那我們應(yīng)該如何分析解決問(wèn)題呢？

部分同學(xué)在處理MySQL慢查詢(xún)時(shí)候主要思路是加索引來(lái)解決，確實(shí)加索引是一個(gè)很好的解決問(wèn)題的手段，但不是全部。既然慢查詢(xún)是問(wèn)題，那就需要明確問(wèn)題發(fā)生原因，和解決問(wèn)題路徑分析。我們一起來(lái)get下MySQL慢查詢(xún)的正確姿勢(shì)。

一、查詢(xún)SQL執(zhí)行到底經(jīng)歷了什么？

首先需要明確：一個(gè)查詢(xún)SQL的執(zhí)行到底經(jīng)歷了什么？

圖片

數(shù)據(jù)庫(kù)執(zhí)行SQL的大致流程如下：

• 建立與MySQL服務(wù)器連接（基礎(chǔ)）
• 客戶(hù)端發(fā)送查詢(xún)SQL到數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)驗(yàn)證是否有執(zhí)行的權(quán)限
• MySQL服務(wù)器先檢查查詢(xún)緩存，如果命中了緩存，則立即返回存儲(chǔ)在緩存中的結(jié)果，否則繼續(xù)流轉(zhuǎn)；
• MySQL服務(wù)器語(yǔ)法解析器，進(jìn)行詞法與語(yǔ)法分析，預(yù)處理
• 流轉(zhuǎn)至查詢(xún)優(yōu)化器生成執(zhí)行計(jì)劃
• 根據(jù)生成的執(zhí)行計(jì)劃，調(diào)用存儲(chǔ)引擎暴露的API來(lái)執(zhí)行查詢(xún)
• 將查詢(xún)執(zhí)行結(jié)果返回給客戶(hù)端
• 關(guān)閉MySQL連接

具體執(zhí)行過(guò)程可能會(huì)因MySQL服務(wù)器具體配置和執(zhí)行場(chǎng)景有一些差異。

1）如未開(kāi)啟應(yīng)用查詢(xún)緩存，則直接忽略查詢(xún)緩存的檢查；

2）執(zhí)行過(guò)程中，如同時(shí)對(duì)于被掃描的行可能加鎖，同時(shí)也可能會(huì)被其他sql阻塞

二、查詢(xún)SQL為什么會(huì)慢？

我們可以把查詢(xún)SQL執(zhí)行看做是一個(gè)任務(wù)的話，那它是由一些列子任務(wù)組成的，每個(gè)子任務(wù)都存在一定的時(shí)間消耗。通常情況下，導(dǎo)致慢查詢(xún)最根本的問(wèn)題就是需要訪問(wèn)的數(shù)據(jù)太多，導(dǎo)致查詢(xún)不可避免的需要篩選大量的數(shù)據(jù)。

面對(duì)慢查詢(xún)，我們需要注意以下兩點(diǎn)：

1）查詢(xún)了過(guò)多不需要的數(shù)據(jù)

2）掃描了額外的記錄

2.1 查詢(xún)了過(guò)多不需要的數(shù)據(jù)

MySQL并不是只返回需要的數(shù)據(jù)，實(shí)際上會(huì)返回全部結(jié)果集再進(jìn)行計(jì)算。

尤其是多表關(guān)聯(lián)查詢(xún) select * 的情況，我們是不是真的需要全部的列呢？如果不是，那我們直接指定對(duì)應(yīng)字段就好了。

例如我們要查詢(xún)用戶(hù)關(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';

這將返回三個(gè)表的全部數(shù)據(jù)列，可以調(diào)整為僅取需要的列：

SELECT goods.title, goods.description
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';

取出全部列，會(huì)讓優(yōu)化器無(wú)法完成索引覆蓋掃描這類(lèi)優(yōu)化，還會(huì)為服務(wù)器帶來(lái)額外的I/O、內(nèi)存和CPU的消耗。

2.2 掃描了額外的記錄

此種情況大部分屬于索引應(yīng)用不當(dāng)造成的（包括：該建的索引沒(méi)有建，或者未應(yīng)用到最佳索引）。

示例表結(jié)構(gòu)如下：

CREATE TABLE `test_table` (
  `name` varchar(32) DEFAULT NULL,
  `desc` varchar(32) DEFAULT NULL,
  `age` int(16) DEFAULT NULL,
  `id` bigint(11) DEFAULT NULL,
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

存在索引 `idx_age` 的情況下，查詢(xún)執(zhí)行計(jì)劃如下：

EXPLAIN SELECT * FROM test_table WHERE age = 10;

圖片

預(yù)估訪問(wèn)1行數(shù)據(jù)即可命中數(shù)據(jù)，如刪除有效索引 `idx_age` 后則會(huì)變成全表掃描（ALL），預(yù)估需要掃描121524條記錄才能完成這個(gè)查詢(xún)，如下圖所示：

圖片

三、如何定位問(wèn)題呢？

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

如果將MySQL慢查詢(xún)作為一個(gè)問(wèn)題來(lái)拆解分析的話，以上內(nèi)容算是問(wèn)題分析，那接下來(lái)開(kāi)始問(wèn)題定位和問(wèn)題解決。

圖片

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

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

圖片

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

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

1）select_type

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

2）type

顯示查詢(xún)使用類(lèi)型，從好到差依次為：system > const > eq_ref > ref > range > index > all

3）possible_keys 和 key

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

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

4）rows

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

5）Extra

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

四、幾種實(shí)用解決方案

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

推進(jìn)幾種較為實(shí)用的解決方案給大家。

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

4.1.1 選擇索引的數(shù)據(jù)類(lèi)型

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

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

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

(2)簡(jiǎn)單的數(shù)據(jù)類(lèi)型更好：整型數(shù)據(jù)比起字符，處理開(kāi)銷(xiāo)更小，因?yàn)樽址谋容^更復(fù)雜。在MySQL中，應(yīng)該用內(nèi)置的日期和時(shí)間數(shù)據(jù)類(lèi)型，而不是用字符串來(lái)存儲(chǔ)時(shí)間；以及用整型數(shù)據(jù)類(lèi)型存儲(chǔ)IP地址。

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

4.1.2 范式與反范式

• 范式化

范式化模型要求滿(mǎn)足下面三大范式：

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

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

比如用戶(hù)這個(gè)模型，它的主鍵是用戶(hù)ID，那么用戶(hù)模型其它字段都應(yīng)該依賴(lài)于用戶(hù)ID。；

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

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

訂單表(訂單編號(hào)，訂購(gòu)日期，顧客編號(hào)，顧客姓名，……)；

初看該表沒(méi)有問(wèn)題，滿(mǎn)足第二范式，每列都和主鍵列”訂單編號(hào)”相關(guān)。

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

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

• 反范式化

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

4.1.3 小結(jié)

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

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

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

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

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

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

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

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

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

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

舉例如下情況：

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

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

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

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

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

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

• 匹配全值(Match the full value)：對(duì)索引中的所有列都指定具體的值。例如，上圖中索引可以幫助你查找出生于1960-01-01的Cuba Allen。
• 匹配最左前綴(Match a leftmost prefix)：你可以利用索引查找last name為Allen的人，僅僅使用索引中的第1列。
• 匹配列前綴(Match a column prefix)：例如，你可以利用索引查找last name以J開(kāi)始的人，這僅僅使用索引中的第1列。
• 匹配值的范圍查詢(xún)(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開(kāi)始的人。
• 僅對(duì)索引進(jìn)行查詢(xún)(Index-only queries)：如果查詢(xún)的列都位于索引中，則不需要讀取元組的值。

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

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

• 查詢(xún)必須從索引的最左邊的列開(kāi)始。關(guān)于這點(diǎn)已經(jīng)提了很多遍了。例如你不能利用索引查找在某一天出生的人。
• 不能跳過(guò)某一索引列。例如，你不能利用索引查找last name為Smith且出生于某一天的人。
• 存儲(chǔ)引擎不能使用索引中范圍條件右邊的列。例如，如果你的查詢(xún)語(yǔ)句為WHERE lastname="Smith" AND firstname LIKE 'J%' AND dob='1976-12-23'，則該查詢(xún)只會(huì)使用索引中的前兩列，因?yàn)長(zhǎng)IKE是范圍查詢(xún)。

4.2.2 聚簇索引

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

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

4.3 查詢(xún)緩存

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

但是查詢(xún)緩存不是銀彈，它也會(huì)存在一些問(wèn)題。

4.3.1 查詢(xún)緩存注意事項(xiàng)

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

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

超過(guò) query_cache_size （設(shè)置查詢(xún)緩存空間大?。┑牟樵?xún)結(jié)果無(wú)法被緩存；

同時(shí)大小寫(xiě)敏感，只有字符串相等情況下查詢(xún)SQL才使用相同緩存。

-- 不會(huì)使用同一個(gè)緩存
select name from users where id = 1;
SELECT name FROM users WHERE id = 1;

2）緩存易失效

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

4.3.2 小結(jié)

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

4.4 重構(gòu)查詢(xún)方式

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

4.4.1 復(fù)雜查詢(xún)拆分

將一個(gè)復(fù)雜查詢(xún)拆分多個(gè)簡(jiǎn)單查詢(xún)，考慮是否需要將一個(gè)復(fù)雜查詢(xún)拆分為多個(gè)簡(jiǎn)單查詢(xún)。

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

對(duì)于大查詢(xún)我們要“分而治之”，將大查詢(xún)切分成多個(gè)小查詢(xún)。不過(guò)在一次查詢(xún)能夠勝任的情況下還拆成多個(gè)獨(dú)立查詢(xún)就不明智了。

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

4.4.2 分解關(guān)聯(lián)查詢(xún)

將關(guān)聯(lián)查詢(xún)進(jìn)行分解，對(duì)每一個(gè)表進(jìn)行一次單表查詢(xú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';

以上查詢(xún)可以分解成下面的查詢(xún)來(lái)代替：

SELECT * FROM users WHERE users.name = 'zhangsan';
SELECT * FROM orders WHERE orders.user_id = 103;
SELECT * FROM goods WHERE goods.good_id IN  （123, 456, 789）;

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

• 將查詢(xún)分解后，執(zhí)行單個(gè)查詢(xún)可減少鎖的競(jìng)爭(zhēng)；
• 應(yīng)用層做關(guān)聯(lián)，更容易對(duì)數(shù)據(jù)庫(kù)進(jìn)行拆分，更易于做到高性能和可擴(kuò)展；
• 減少冗余記錄的查詢(xún)（在應(yīng)用層做關(guān)聯(lián)，表示對(duì)某條記錄應(yīng)用只需要查詢(xún)一次，而在數(shù)據(jù)庫(kù)中做關(guān)聯(lián)查詢(xún)，則可能需要重復(fù)訪問(wèn)一部分?jǐn)?shù)據(jù)。）

五、高性能查詢(xún)難題優(yōu)化總結(jié)

如果將MySQL慢查詢(xún)作為一個(gè)問(wèn)題來(lái)拆解分析的話，之前內(nèi)容算是問(wèn)題分析、問(wèn)題定位和解決，那現(xiàn)在來(lái)收下尾，聊聊MySQL慢查詢(xún)問(wèn)題解決經(jīng)驗(yàn)總結(jié)。

圖片

廢話不多說(shuō)，直接開(kāi)干~

我們來(lái)總結(jié)一下，應(yīng)該如何處理高性能查詢(xún)難題？

假如把高性能查詢(xún)比作一個(gè)“難題”，它其實(shí)是包括多個(gè)子難題在內(nèi)，共同作用的結(jié)果。

今天我們來(lái)歸納總結(jié)下，主要包括以下幾類(lèi)：

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

良好的schema設(shè)計(jì)原則是普遍適用的，但是MySQL有他自己的實(shí)現(xiàn)細(xì)節(jié)要注意，概況來(lái)講，盡可能保持任何東西小而簡(jiǎn)單總是好的。

主要有以下簡(jiǎn)單的原則值得你去考慮使用：

• 盡量避免過(guò)度設(shè)計(jì)
• 使用小而簡(jiǎn)單的合適數(shù)據(jù)類(lèi)型，盡可能避免使用null
• 盡量使用相同的數(shù)據(jù)類(lèi)型存儲(chǔ)相似或者相關(guān)的值
• 注意可變長(zhǎng)字符串，其在臨時(shí)表和排序時(shí)可能按最大長(zhǎng)度分配內(nèi)存
• 盡量使用整形定義標(biāo)識(shí)符

5.2 索引設(shè)計(jì)優(yōu)化

常見(jiàn)的B-Tree索引，按照順序存儲(chǔ)數(shù)據(jù)，所以MySQL可以用來(lái)做ORDER BY 和 GROUP BY操作。因?yàn)閿?shù)據(jù)是有序的，所以便于將相關(guān)的列值都存儲(chǔ)在一起。由于索引中存儲(chǔ)了實(shí)際的列值，所以一些查詢(xún)只通過(guò)索引就能夠完成查詢(xún)（如：聚簇索引）。

根據(jù)索引的特性，總結(jié)索引的優(yōu)點(diǎn)有如下幾點(diǎn)：

• 減少服務(wù)器需要掃描的數(shù)據(jù)量；
• 幫助服務(wù)器避免排序和臨時(shí)表；
• 將隨機(jī)I/O變?yōu)轫樞騃/O。

編寫(xiě)查詢(xún)語(yǔ)句時(shí)候應(yīng)該注意盡可能選擇合適的索引，以避免單行查找，盡可能使用索引覆蓋。

根據(jù)執(zhí)行計(jì)劃依次掃描相關(guān)表中的行，不在數(shù)據(jù)緩沖區(qū)的走IO存儲(chǔ)引擎掃描表的性能消耗參考下面的list，消耗從大到?。?/p>

全表掃描>全索引掃描>部分索引掃描>索引查找>唯一索引/主鍵查找>常量/null

5.3 應(yīng)用查詢(xún)優(yōu)化

應(yīng)用查詢(xún)優(yōu)化是建立在良好的數(shù)據(jù)結(jié)構(gòu)和合理的索引設(shè)計(jì)之上的。

它主要包括以下幾種情況：

5.3.1 重構(gòu)查詢(xún)方式

優(yōu)化慢查詢(xún)時(shí)，目標(biāo)應(yīng)該是找到一個(gè)更優(yōu)的方案來(lái)達(dá)到我們獲取結(jié)果數(shù)據(jù)的目的。其中可以存在多樣的權(quán)衡方案：

1）從數(shù)據(jù)庫(kù)中查詢(xún)計(jì)算直接獲取到結(jié)果數(shù)據(jù)；

2）拆分多條子查詢(xún)來(lái)逐步得到結(jié)果數(shù)據(jù)；

3）從數(shù)據(jù)庫(kù)獲取到基礎(chǔ)數(shù)據(jù)，然后應(yīng)用代碼邏輯加工后獲得結(jié)果數(shù)據(jù)。

5.3.2 讓SQL盡量符合查詢(xún)優(yōu)化器的執(zhí)行要求

MySQL 查詢(xún)優(yōu)化器并不是對(duì)所有查詢(xún)都適用的，我們可以通過(guò)改寫(xiě)查詢(xún) SQL 來(lái)讓數(shù)據(jù)庫(kù)更高效的完成工作。

常見(jiàn)查詢(xún)應(yīng)用優(yōu)化建議匯總?cè)缦拢?/p>

1）對(duì)于任何查詢(xún)，應(yīng)盡量避免全表掃描

    首先應(yīng)考慮在 where 及 order by 涉及的列上建立并應(yīng)用索引；

2）盡量避免在 where 子句中進(jìn)行操作

    使用 or 來(lái)連接條件、對(duì)字段進(jìn)行 null 值判斷、匹配查詢(xún) '%abc%'、!= 或 <> 操作符，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；

    對(duì)字段進(jìn)行表達(dá)式、函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；

3）盡量應(yīng)用索引

    使用索引字段作為條件時(shí)，如果是復(fù)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引，否則該索引將不會(huì)被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致；

4）索引字段要注意慎重選取

    索引盡量避開(kāi)區(qū)分度不大的字段，如：sex、male、female

    這種五五開(kāi)的索引列有大量數(shù)據(jù)重復(fù)時(shí)，那么即使在 sex 上建了索引也對(duì)查詢(xún)效率起不了作用。

5）一個(gè)表的索引數(shù)最好不要超過(guò) 6 個(gè)

    索引并不是越多越好，索引固然可以提高相應(yīng)的 select 的效率，但同時(shí)也降低了 insert 及 update 的效率， 因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。

6）盡量使用數(shù)字型字段

    若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會(huì)降低查詢(xún)和連接的性能，并會(huì)增加存儲(chǔ)開(kāi)銷(xiāo)。 這是因?yàn)橐嬖谔幚聿樵?xún)和連接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符，而對(duì)于數(shù)字型而言只需要比較一次就夠了。

7）盡量避免使用 *

    select * from table ，用具體的字段列表代替 *，不要返回用不到的任何字段，尤其是多表關(guān)聯(lián)查詢(xún)的情況。

MySQL v5.6版本以后，消除了很多MySQL原本的限制，讓更多的查詢(xún)能夠以盡可能高的效率完成。

5.4 小結(jié)

根據(jù)梳理 MySQL中的 SQL執(zhí)行過(guò)程我們發(fā)現(xiàn)，任何流程的執(zhí)行都存在其執(zhí)行環(huán)境和規(guī)則，其實(shí)產(chǎn)生慢SQL的本質(zhì)是：我們沒(méi)有按照數(shù)據(jù)庫(kù)的要求方式來(lái)執(zhí)行SQL。

主要導(dǎo)致慢查詢(xún)最根本的問(wèn)題就是需要訪問(wèn)的數(shù)據(jù)太多，導(dǎo)致查詢(xún)不可避免的需要篩選大量的數(shù)據(jù)。

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

• 優(yōu)化數(shù)據(jù)結(jié)構(gòu)
• 應(yīng)用索引策略
• 查詢(xún)緩存
• 重構(gòu)查詢(xún)方式

良好的表結(jié)構(gòu)設(shè)計(jì)是高性能查詢(xún)的基石，恰當(dāng)?shù)乃饕O(shè)計(jì)是高性能查詢(xún)的助推器，同時(shí)合理的查詢(xún)應(yīng)用也是必不可少的。數(shù)據(jù)結(jié)構(gòu)優(yōu)化、索引設(shè)計(jì)優(yōu)化及應(yīng)用查詢(xún)優(yōu)化猶如三叉戟一般，齊頭并進(jìn)，在高性能查詢(xún)應(yīng)用中缺一不可。

寫(xiě)在最后

全文總結(jié)一下，其實(shí)就是我們要學(xué)會(huì)用數(shù)據(jù)庫(kù)的要求方式來(lái)執(zhí)行SQL。

即要寫(xiě)好應(yīng)用查詢(xún)SQL，必須要結(jié)合良好的數(shù)據(jù)結(jié)構(gòu)和合理的索引設(shè)計(jì)才可以。

其實(shí)MySQL查詢(xún)優(yōu)化中的每一項(xiàng)拆開(kāi)講都可以是很大的章節(jié)，在此主要是將解決問(wèn)題的思路分享給大家，希望能對(duì)大家今后的工作中能有所幫助。