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

• 問題1

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

• 問題2

還有就是網(wǎng)絡(luò)上經(jīng)?？梢钥吹揭恍╊愃七@樣的文章：

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

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

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

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

本文初衷

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

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

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

寫在前面

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

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

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

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

圖片

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

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

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

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

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

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

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

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

1）查詢了過多不需要的數(shù)據(jù)

2）掃描了額外的記錄

2.1 查詢了過多不需要的數(shù)據(jù)

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

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

例如我們要查詢用戶關(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)化器無法完成索引覆蓋掃描這類優(yōu)化，還會(huì)為服務(wù)器帶來額外的I/O、內(nèi)存和CPU的消耗。

2.2 掃描了額外的記錄

此種情況大部分屬于索引應(yīng)用不當(dāng)造成的（包括：該建的索引沒有建，或者未應(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` 的情況下，查詢執(zhí)行計(jì)劃如下：

EXPLAIN SELECT * FROM test_table WHERE age = 10;

圖片

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

圖片

三、如何定位問題呢？

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

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

圖片

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

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

圖片

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

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

1）select_type

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

2）type

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

3）possible_keys 和 key

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

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

4）rows

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

5）Extra

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

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

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

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

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

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

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

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

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

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

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

4.1.2 范式與反范式

• 范式化

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

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

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

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

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

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

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

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

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

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

• 反范式化

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

4.1.3 小結(jié)

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

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

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

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

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

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

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

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

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

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

舉例如下情況：

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

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

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

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

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

4.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é)點(diǎn)都是順序存儲(chǔ)的，所以可以利用索引進(jìn)行查找(找某些值)，也可以對查詢結(jié)果進(jìn)行ORDER BY。

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

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

4.2.2 聚簇索引

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

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

4.3 查詢緩存

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

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

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

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

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

超過 query_cache_size （設(shè)置查詢緩存空間大小）的查詢結(jié)果無法被緩存；

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

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

2）緩存易失效

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

4.3.2 小結(jié)

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

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

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

4.4.1 復(fù)雜查詢拆分

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

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

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

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

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

將關(guān)聯(lián)查詢進(jìn)行分解，對每一個(gè)表進(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_id IN  （123, 456, 789）;

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

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

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

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

圖片

廢話不多說，直接開干~

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

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

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

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

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

主要有以下簡單的原則值得你去考慮使用：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

MySQL 查詢優(yōu)化器并不是對所有查詢都適用的，我們可以通過改寫查詢 SQL 來讓數(shù)據(jù)庫更高效的完成工作。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

7）盡量避免使用 *

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

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

5.4 小結(jié)

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

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

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

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

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

寫在最后

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

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

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