一條慢查詢日誌
mysql的索引是一把雙刃劍， 如果使用得當， 會給系統帶來極大的性能提升；相反的如果使用不當， 則可能會帶來災難性的後果。 最可怕的是前期很難發現， 隨著資料量的增加以及業務高峰期的臨近， 問題才會突然暴露出來。
本周我所負責的一個系統就出來了類似的問題， 慶倖的是處理及時， 沒有造成災難性的後果。 今天抽時間把事情的經過記錄下來， 在未來的時間裡鞭策自己：對新成員必須進行sql語句基礎規範的學習， 並對每個人每次上線的sql語句必須進行code review。
這個系統是一個新業務，
從慢查詢日誌中可以看到， 最消耗性能的語句是“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
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語句導致的性能問題，
第三步：分析執行計畫， 以及索引命中情況
查看執行計畫：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， 這個索引根本就沒用。
也行還會發現其他很多的問題。
到這裡突然發現自己應該負很大的責任：對新同事的培養，
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 in\like，會導致索引失效。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勢在必行。
如"xxx%"。12、在字串列上創建索引，儘量使用首碼索引。首碼基數根據具體業務，在匹配度和存儲量（索引的存儲量）之前做一個平衡。
13、不要使用 not in\like，會導致索引失效。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勢在必行。