mysql索引——從一次慢查詢優化說起

一條慢查詢日誌

mysql的索引是一把雙刃劍，如果使用得當，會給系統帶來極大的性能提升；相反的如果使用不當，則可能會帶來災難性的後果。最可怕的是前期很難發現，隨著數據量的增加以及業務高峰期的臨近，問題才會突然暴露出來。

本周我所負責的一個系統就出來了類似的問題，慶幸的是處理及時，沒有造成災難性的後果。今天抽時間把事情的經過記錄下來，在未來的時間裡鞭策自己：對新成員必須進行sql語句基礎規範的學習，並對每個人每次上線的sql語句必須進行code review。

這個系統是一個新業務，並且也已經上線正常運行了一段時間。隨著618大促的臨近業務量增加，問題才暴露出來。本周三晚上加班，突然收到DBA信息說我們mysql所在的伺服器cpu利用率在近20分鐘內急劇攀升，快到90%，讓我們排查是不是我們的應用導致的（該mysql伺服器上有多個應用對應的多個資料庫）。事發突然，立即向DBA索要慢查詢日誌進行分析，日誌如下（已屏蔽業務信息）：

從慢查詢日誌中可以看到，最消耗性能的語句是「SELECT xxx_pc_act_profile」，該語句在26分鐘內（Time range: 2017-05-31 20:20:02 to 20:46:04），執行7618次，平均每次大約113ms，已經到了無法容忍的地步。並且不幸的是，這張表確實是屬於我們系統。

解決問題步驟

第一步：停服務

由於該mysql伺服器中還有其他應用資料庫，為了防止影響其他業務，第一步就是立即決定停掉我們這個子系統服務（權衡影響範圍）。再次觀察msyql伺服器情況，cpu使用率恢復正常，進一步說明確實是由於該業務引起。

第二步：初步確定索引問題

分析這個出問題的語句，是一個select語句：

SELECT

xxx,xxx,xxx,xxx

FROM xxx_pc_act_profile

where

and start_time <= "2017-05-31 20:30:00"

and end_time >= "2017-05-31 20:30:00"

and valid_flag = 1

and status = 1

and brandIds = "94924"

order by weight desc desc

可以看到這裡的where語句里有很多查詢條件，還有order by語句，由於select語句導致的性能問題，可以99%的確定是索引設置不當引起的。

第三步：分析執行計劃，以及索引命中情況

查看執行計劃：explain select xxx from xxx_pc_act_profile where xxx;

發現查詢命中索引"idx_status"，看起來像是在一個狀態欄位上建了索引。進一步確認，證實status欄位是一個狀態欄位（0-正常，1-下線）。

至此定位到問題原因：錯誤的在「低基數列創建索引」。

第四步：查看錶索引的創建明細

CREATE TABLE `xxx_pc_act_profile` (

--省略欄位

PRIMARY KEY (`id`),

KEY `idx_url` (`url`),

KEY `idx_third_cate` (`third_cate`),

KEY `idx_start_time` (`start_time`),

KEY `idx_end_time` (`end_time`),

KEY `idx_status` (`status`),

KEY `idx_valid_flag` (`valid_flag`),

KEY `idx_pre_cate_level` (`pre_cate_level`),

KEY `idx_confirm_flag` (`confirm_flag`),

KEY `idx_last_publish_date` (`last_publish_date`),

KEY `idx_valid_query` (`start_time`,`end_time`,`status`,`valid_flag`)

) ENGINE=InnoDB COMMENT="xxx活動畫像表"

看到這裡驚呆了，這都創建了些什麼索引。初步列舉問題：

1、索引創建太多（普通索引是B-TREE，需要單獨的存儲空間）。

2、對低基數列創建索引，如：status、valid_flag 等。

3、對字元串類型創建索引，如:third_cate等。

4、對無用欄位創建索引：url，這個索引根本就沒用。

也行還會發現其他很多的問題。

到這裡突然發現自己應該負很大的責任：對新同事的培養，平時都只是停留在java coding上，尤其是現在大部分業務都有redis緩存擋在上一層，對sql的基礎規範沒有組織學習，上線前的code review也沒有覆蓋到sql。

msyql創建索引的基本原則

通過這個反面教材，快速的總結了在創建索時的注意事項（基本原則）：

1、不要在低基數列創建索引。浪費索引存儲空間，並且不會提高查詢效率。

2、盡量不要在經常被修改的欄位上建索引，會增加插入的成本，以及提高死鎖發生的概率。例如本示例不會在weight欄位加索引

3、刪除冗餘索引，沒有用到的索引必須全部刪除，避免不必要的空間浪費。本示例中url 索引是無用的。

4、不要創建太多的索引，因為在插入數據時，索引也需要插入。索引太多會導致插入性能下降。本示例優化後 只剩兩個索引。

5、不要在非null列創建索引，如果值為null時，建議替換成1或-1等常量。本示例start_time、end_time兩個欄位優化為非空。

6、如果查詢是多條件，不要為每個條件欄位創建索引，而是創建複合索引，因為mysql只用使用1個索引。

7、創建複合索引，注意左匹配原則，盡量考慮重用性。比如創建複合索引index(a、b、c),相當於同時創建了index(a) index(a、b) index(a、b、c)。

8、創建複合索引，需要注意把區分度最大的放到最前面（如果與第6點衝突，需要自己根據業務平衡下）。

通過查找資料，還有其他幾點：

9、主鍵最好使用自增型，保證數據連續性（mysql innodb 主鍵默認採用b+tree，索引和數據放在同一個btree中），不要使用uuid、hash、md5等

10、少使用外鍵，會導致兩張表數據變更時相互影響。盡量通過業務實現。

11、不要使用前匹配的like查詢，會導致索引失效。可以使用後匹配like，如"xxx%"。

12、在字元串列上創建索引，盡量使用前綴索引。前綴基數根據具體業務，在匹配度和存儲量（索引的存儲量）之前做一個平衡。

13、不要使用 not inlike，會導致索引失效。not in可以用not exists替換。in和or所在列最好有索引

（ps：普通java開發，非dba總結，不全的地方，還望有DBA大神補充下）。

本次事故示例 按照上面的原則對索引進行優化：最終去掉了以前的所有索引，根據具體業務，只新建了兩個複合索引（其他查詢都可以重用複合索引中的部分）。

（ps：實際修復步驟：新建一張欄位信息相同的表，並創建新的索引，再把老表中的數據同步到新表）

至此 該問題解決，期間丟失部分業務數據，但慶幸的是該系統是一個外圍系統，損失還在可控範圍內。

新問題 Duplicate PRIMARY

在解決上述索引問題的過程中，我始終覺得這張表的主鍵創建方式會導致問題。根據上述主鍵的創建原則：「主鍵最好使用自增型」，但上述表的主鍵不滿足該規則：

PRIMARY KEY (`id`),

在問題修復後，我們持續的對日誌進行不定期的檢查，果然又有新的發現，日誌中偶爾會報錯：

Duplicate entry "xxx" for key "PRIMARY"

問題很明顯，就是數據在插入時，發現改主鍵id 『xxx』已經存在，報主鍵重複寫入衝突錯誤。立即提取代碼分析問題，這裡的主鍵id是另外一張表X的主鍵，根據業務查詢X表，對滿足條件的記錄進行加工後插入該新表。其代碼邏輯如下：

------開啟spring 事務 省略代碼 -------

xxxPcActProfile oldInfo = xxxDao.getById(newInfo.getId);//先查詢該主鍵id對應的記錄是否存在

if (oldInfo == null){

xxxDao.insert(newInfo);//如果不存在，就插入

}else{

xxxDao.update(newInfo);//如果已存在，就修改

}

------提交spring 事務 省略代碼 -------

初步看該代碼沒有問題啊，在一個事務裡面，怎麼會出現插入時「主鍵衝突」呢。

其實不要被事務所欺騙，我們來分析下在高並發情況下，兩個相同id的插入請求：

事務1 事務2

1、 判斷id：123是否存在 判斷id：123是否存在

2、 判斷結果：不存在，進行插入 判斷結果：不存在，進行插入

3、 插入id為123的記錄 等待

4、 插入完成 插入失敗，id：123已存在

5、 關閉事務 關閉事務

由於mysql的插入是不可分割的指令是原子性的，必須得等待其中一條插入完成後，另一條才能插入。這就導致了上述「主鍵衝突」異常的發生。

該問題導致的後果：在發現已經存在後，應該執行修改，但衝突後直接拋出異常，修改操作沒有被執行，導致修改數據丟失。該問題在高並發的情況下，還會經常出現。

最終的解決辦法：採用 insert duplicate update語句，問題得到解決，語法如下：

INSERT INTO table (xx,xx,xx) VALUES (xx,xx,xx) ON DUPLICATE KEY UPDATE ....

至此問題得以解決。但仍心有餘悸，加強sql編寫規範學習，並對所有sql進行code review勢在必行。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！