作為開發也要了解的 mysql 優化思路

作為開發人員，資料庫知識掌握的可能不是很深入，但是一些基本的技能還是要有時間學習一下的。作為一個資料庫菜鳥，厚著臉皮來總結一下 mysql 的基本的不能再基本的優化方法。

為了更好的說明，我假想出來了一個業務場景，可能在實際業務中並不存在這樣的場景，只為舉例說明問題：

表結構說明

• 使用者賬號表（account），主要儲存使用者賬號、密碼、註冊時間等資訊，1萬條資料
• 使用者基本資訊表（userinfo），主要儲存使用者個人資訊，包括年齡、性別等，關聯 account 表，關聯欄位 account_id，1萬條資料
• 訂單表（orderinfo）,主要儲存使用者訂單資訊，關聯account 表，關聯欄位 account_id，10萬條資料

如果需要表結構和資料初始化的指令碼，可以在本公眾號回覆關鍵字 「mysql」，這裡就不佔篇幅了。

業務需求說明

統計出年齡大於 30 歲，性別為女（0）的使用者所下訂單的總數量。 當然用其他方式可以實現，但這裡不考慮非資料庫處理的其他方式。

下面是 sql 查詢語句，三個表做 join 查詢，並通過三個條件做篩選。做查詢之前，這三個表都沒有做其他處理，只是主鍵 INT 型別設定了自增。 執行下面的語句，在我本地的時間是 35s 左右，這已經不能忍受了。

SELECT
    count(*)
FROM
    account a
LEFT JOIN userinfo u ON a.id = u.account_id
LEFT JOIN orderinfo o on a.id =o.account_id
WHERE
    u.age >= 30 and u.sex=0  and o.id is NOT NULL;
    
    ## 查詢時間30多秒  

使用 explain 命令分析

碰到這種執行時間非常慢的慢查詢語句時，就要有請神器 explain 命令了，這是 mysql 提供的查詢語句優化分析工具。

使用方法非常簡單，就是在查詢語句前加上 explain 命令，比如分析上面的語句就是這樣的：

EXPLAIN 
SELECT
                count(*)
FROM
    account a
LEFT JOIN userinfo u ON a.id = u.account_id
LEFT join orderinfo o on a.id =o.account_id
WHERE
     u.age >= 30 and u.sex=0 and o.id is NOT NULL; 

命令執行後是下面這樣的結果：

下面分別解釋一下各個欄位的含義：

id

每個 SELECT 都會自動分配一個唯一的識別符號。如果在語句中沒子查詢或關聯查詢，只有唯一的select，每行都將顯示1。否則，內層的select語句一般會順序編號，對應於其在原始語句中的位置。本例中因為只有一個 select ,所以 id 都是1。

下面兩條語句會編號為1、2，可以執行試一下：

#  包含子查詢的
EXPLAIN 
SELECT * FROM account
WHERE id IN 
    (       SELECT          max(account_id)         FROM            orderinfo   );
    
    #  帶有union的聯合查詢
    EXPLAIN SELECT *
FROM account
WHERE id = 100
UNION ALL
SELECT *
FROM account
WHERE id = 101;

select_type

查詢的型別。有如下幾種型別：

table

查詢的是哪個表，顯示錶名或者別名

partitions

查詢的分割槽，如果資料庫沒有做過分割槽操作，此欄位為 null

type

表示查詢語句的掃描型別，有如下幾種：

效能從高到低為：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

possible_keys

表示查詢時, 能夠使用到的索引。但是, 即使有些索引在 possible_keys 中出現, 但是並不表示此索引會真正地被使用到。在查詢時具體使用了哪些索引, 由 key 欄位決定

key

當前查詢真正使用的索引

ref

表示使用了哪個列或 const 與 key(查詢所用到的索引) 一起從表中做選擇

rows

可以 sql 的優化過程就是為了減小 rows 欄位的數量，rows 表示要掃描的行數，行數越多，當然查詢的時間就越長。

extra

該列顯示MySQL在查詢過程中的一些詳細資訊，MySQL查詢優化器執行查詢的過程中對查詢計劃的重要補充資訊。

Using filesort：當 Extra 中有 Using filesort 時, 表示 MySQL 需額外的排序操作, 不能通過索引順序達到排序效果. 一般有 Using filesort, 都建議優化去掉, 因為這樣的查詢 CPU 資源消耗大.

Using index："覆蓋索引掃描", 表示查詢在索引樹中就可查詢所需資料, 不用掃描表資料檔案, 往往說明效能不錯；

Using temporary：查詢有使用臨時表, 一般出現於排序, 分組和多表 join 的情況, 查詢效率不高, 建議優化.

回過頭來看我們上面的例子，這三個表只有主鍵 id 有索引。

1、首先先掃描 userinfo 表 ，type 為 ALL ，為全表掃描， rows 欄位為 10000 行，掃描了 10000 行。

2、然後連線了 account 表，並使用索引 PRIMARY (也就是主鍵 id),通過 ref userinfo.account_id，進行了行選擇，所以這裡的 rows 為1，也就是沒有進行掃描，直接定位到了要查詢的行。

3、之後掃描 orderinfo 表，type 為 ALL ,還是全表掃描，rows 為 99900。

所以三次掃描執行下來，在我本地的機器上平均35s左右。

簡單優化

優化原則大體上是這樣的：

• sql 層面有優化空間的，先優化了再說。最常用的手段就是加索引。
• 如果 sql 語句無法優化了，看一下是不是能夠修改 sql 查詢語句的結構，比如有子查詢的語句，能不能用 union 查詢兩次或多次。
• 如果 sql 層面確實無法優化了，考慮用程式的方式，或者修改架構。但並不是說 sql 層面優化了，程式中就不需要優化了，兩者並不衝突，當然是效能越快越好了。

來看一下這個例子，join 了三個表，但這三個表都只有主鍵有索引。第一步優化：加索引。加索引有一下幾個原則：

1、較頻繁的作為查詢條件的欄位應該建立索引

2、唯一性太差的欄位不適合單獨建立索引，即使頻繁作為查詢條件，也就是區分度太低，比如性別，比如檢視性別的區分度可以用這個語句：

SELECT
    count(*),
    sex
FROM
    userinfo
GROUP BY
    sex;
    
    +----------+------+
| count(*) | sex  |
+----------+------+
| 5000     | 0    |
| 5000     | 1    |
+----------+------+

可以看到，一共有兩個性別，每個5000，即使加了索引，每次也需要掃描一半的資料。

3、更新非常頻繁的欄位不適合建立索引；

4、不會出現在 WHERE 子句中的欄位不該建立索引

先給 userinfo 表的 account_id 欄位加上索引，因為 join 連線條件是用的它。加索引的命令如下：

ALTER TABLE userinfo ADD INDEX index_account_id (`account_id`);

檢視索引：

SHOW INDEX FROM userinfo;

順便說以下刪除索引的命令：

ALTER TABLE userinfo DROP INDEX index_account_id;

再次執行 explain 命令，結果如下：

看到沒，查詢 userinfo 時使用了剛剛建立的索引，rows 馬上變成了1，再次執行，執行時間就下降到了0.5s以下。

然後再給 orderinfo 的 account_id 建立索引，再次 explain ,分析如下：

這次 orderinfo 表查詢的時候走了索引，但是 userinfo 表沒有，mysql 會自動選擇最優的索引。再次執行查詢，查詢時間降到了30ms左右。

如果查詢的條件較多，還可以考慮聯合索引，比如本例中可以考慮給account_id、age、sex 建立聯合索引，只是舉個例子，sex 欄位其實並不適合納入索引列。

ALTER TABLE userinfo ADD INDEX index_accountid_age_sex (`account_id`, `age`, `sex`);

但由於 mysql 的自動選擇最優索引的機制，即使加了聯合索引，也還是會優先使用 orderinfo 的索引，因為使用那個索引效率更高。但是如果 userinfo 的記錄更多，那結果就不一樣了。

注意點

1、除非列有要求要存空值 null，否則建議列設定為不允許為 null，因為 null 無法利用索引，而且會佔用額外的空間；

2、建議減少對大表的 join 查詢，如果是 myisam 引擎會產生表鎖，會導致其他寫操作被阻塞。innodb 引擎會產生行鎖，倒是影響不大；

最後

本篇主要是為了說明 mysql 的分析方法，就是用 explain 命令。發現問題是關鍵步驟，至於解決方法，每個場景的解決方法都會有不同，這就需要各位結合自身經驗，或者藉助搜尋引擎，或者請教更專業的人來想辦法了。

另外，關注公眾號回覆關鍵字 「mysql」，獲取本例中的表結構和初始資料。

微信公眾號，多謝關注：

還可以加入 Java 微信討論群（如果二維碼過期：請加微信:fengdezitai001 ,備註：cnblogs）：