一條慢查詢?nèi)罩?/b>

mysql的索引是一把雙刃劍真朗，如果使用得當(dāng)此疹，會(huì)給系統(tǒng)帶來極大的性能提升；相反的如果使用不當(dāng)，則可能會(huì)帶來災(zāi)難性的后果蝗碎。最可怕的是前期很難發(fā)現(xiàn)湖笨，隨著數(shù)據(jù)量的增加以及業(yè)務(wù)高峰期的臨近，問題才會(huì)突然暴露出來蹦骑。

本周我所負(fù)責(zé)的一個(gè)系統(tǒng)就出來了類似的問題慈省，慶幸的是處理及時(shí)，沒有造成災(zāi)難性的后果眠菇。今天抽時(shí)間把事情的經(jīng)過記錄下來辫呻，在未來的時(shí)間里鞭策自己：對(duì)新成員必須進(jìn)行sql語句基礎(chǔ)規(guī)范的學(xué)習(xí)，并對(duì)每個(gè)人每次上線的sql語句必須進(jìn)行code review琼锋。

這個(gè)系統(tǒng)是一個(gè)新業(yè)務(wù)放闺，并且也已經(jīng)上線正常運(yùn)行了一段時(shí)間。隨著618大促的臨近業(yè)務(wù)量增加缕坎，問題才暴露出來怖侦。本周三晚上加班，突然收到DBA信息說我們mysql所在的服務(wù)器cpu利用率在近20分鐘內(nèi)急劇攀升谜叹，快到90%匾寝，讓我們排查是不是我們的應(yīng)用導(dǎo)致的（該mysql服務(wù)器上有多個(gè)應(yīng)用對(duì)應(yīng)的多個(gè)數(shù)據(jù)庫）。事發(fā)突然荷腊，立即向DBA索要慢查詢?nèi)罩具M(jìn)行分析艳悔，日志如下（已屏蔽業(yè)務(wù)信息）：

從慢查詢?nèi)罩局锌梢钥吹剑钕男阅艿恼Z句是“SELECT xxx_pc_act_profile”女仰，該語句在26分鐘內(nèi)（Time range: 2017-05-31 20:20:02 to 20:46:04）猜年，執(zhí)行7618次，平均每次大約113ms疾忍，已經(jīng)到了無法容忍的地步乔外。并且不幸的是，這張表確實(shí)是屬于我們系統(tǒng)一罩。

解決問題步驟

第一步：停服務(wù)

由于該mysql服務(wù)器中還有其他應(yīng)用數(shù)據(jù)庫杨幼，為了防止影響其他業(yè)務(wù)，第一步就是立即決定停掉我們這個(gè)子系統(tǒng)服務(wù)（權(quán)衡影響范圍）聂渊。再次觀察msyql服務(wù)器情況差购，cpu使用率恢復(fù)正常，進(jìn)一步說明確實(shí)是由于該業(yè)務(wù)引起汉嗽。

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

分析這個(gè)出問題的語句欲逃，是一個(gè)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語句導(dǎo)致的性能問題暖夭，可以99%的確定是索引設(shè)置不當(dāng)引起的。

第三步：分析執(zhí)行計(jì)劃撵孤，以及索引命中情況

查看執(zhí)行計(jì)劃：explain select xxx from xxx_pc_act_profile where xxx;

發(fā)現(xiàn)查詢命中索引'idx_status'迈着，看起來像是在一個(gè)狀態(tài)字段上建了索引。進(jìn)一步確認(rèn)邪码，證實(shí)status字段是一個(gè)狀態(tài)字段（0-正常裕菠，1-下線）。

至此定位到問題原因：錯(cuò)誤的在“低基數(shù)列創(chuàng)建索引”闭专。

第四步：查看表索引的創(chuàng)建明細(xì)

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活動(dòng)畫像表'

看到這里驚呆了奴潘，這都創(chuàng)建了些什么索引。初步列舉問題：

1影钉、索引創(chuàng)建太多（普通索引是B-TREE画髓，需要單獨(dú)的存儲(chǔ)空間）。

2平委、對(duì)低基數(shù)列創(chuàng)建索引奈虾，如：status、valid_flag 等廉赔。

3肉微、對(duì)字符串類型創(chuàng)建索引，如:third_cate等蜡塌。

4碉纳、對(duì)無用字段創(chuàng)建索引：url，這個(gè)索引根本就沒用馏艾。

也行還會(huì)發(fā)現(xiàn)其他很多的問題劳曹。

到這里突然發(fā)現(xiàn)自己應(yīng)該負(fù)很大的責(zé)任：對(duì)新同事的培養(yǎng)，平時(shí)都只是停留在java coding上琅摩，尤其是現(xiàn)在大部分業(yè)務(wù)都有redis緩存擋在上一層厚者，對(duì)sql的基礎(chǔ)規(guī)范沒有組織學(xué)習(xí)，上線前的code review也沒有覆蓋到sql迫吐。

msyql創(chuàng)建索引的基本原則

通過這個(gè)反面教材库菲，快速的總結(jié)了在創(chuàng)建索時(shí)的注意事項(xiàng)（基本原則）：

1、不要在低基數(shù)列創(chuàng)建索引志膀。浪費(fèi)索引存儲(chǔ)空間熙宇，并且不會(huì)提高查詢效率。

2溉浙、盡量不要在經(jīng)常被修改的字段上建索引烫止，會(huì)增加插入的成本，以及提高死鎖發(fā)生的概率戳稽。例如本示例不會(huì)在weight字段加索引

3馆蠕、刪除冗余索引期升，沒有用到的索引必須全部刪除，避免不必要的空間浪費(fèi)互躬。本示例中url 索引是無用的播赁。

4、不要?jiǎng)?chuàng)建太多的索引吼渡，因?yàn)樵诓迦霐?shù)據(jù)時(shí)容为，索引也需要插入。索引太多會(huì)導(dǎo)致插入性能下降寺酪。本示例優(yōu)化后只剩兩個(gè)索引坎背。

5、不要在非null列創(chuàng)建索引寄雀，如果值為null時(shí)得滤，建議替換成1或-1等常量。本示例start_time盒犹、end_time兩個(gè)字段優(yōu)化為非空耿戚。

6、如果查詢是多條件阿趁，不要為每個(gè)條件字段創(chuàng)建索引膜蛔，而是創(chuàng)建復(fù)合索引，因?yàn)閙ysql只用使用1個(gè)索引脖阵。

7皂股、創(chuàng)建復(fù)合索引，注意左匹配原則命黔，盡量考慮重用性呜呐。比如創(chuàng)建復(fù)合索引index(a、b悍募、c),相當(dāng)于同時(shí)創(chuàng)建了index(a) index(a蘑辑、b) index(a、b坠宴、c)洋魂。

8、創(chuàng)建復(fù)合索引喜鼓，需要注意把區(qū)分度最大的放到最前面（如果與第6點(diǎn)沖突副砍，需要自己根據(jù)業(yè)務(wù)平衡下）。

通過查找資料庄岖，還有其他幾點(diǎn)：

9豁翎、主鍵最好使用自增型，保證數(shù)據(jù)連續(xù)性（mysql innodb 主鍵默認(rèn)采用b+tree隅忿，索引和數(shù)據(jù)放在同一個(gè)btree中）心剥，不要使用uuid邦尊、hash、md5等

10优烧、少使用外鍵蝉揍，會(huì)導(dǎo)致兩張表數(shù)據(jù)變更時(shí)相互影響。盡量通過業(yè)務(wù)實(shí)現(xiàn)匙隔。

11、不要使用前匹配的like查詢熏版，會(huì)導(dǎo)致索引失效纷责。可以使用后匹配like撼短，如"xxx%"再膳。

12、在字符串列上創(chuàng)建索引曲横，盡量使用前綴索引喂柒。前綴基數(shù)根據(jù)具體業(yè)務(wù)，在匹配度和存儲(chǔ)量（索引的存儲(chǔ)量）之前做一個(gè)平衡禾嫉。

13灾杰、不要使用 not in\like，會(huì)導(dǎo)致索引失效熙参。not in可以用not exists替換艳吠。in和or所在列最好有索引

（ps：普通java開發(fā)，非dba總結(jié)孽椰，不全的地方昭娩，還望有DBA大神補(bǔ)充下）。

本次事故示例按照上面的原則對(duì)索引進(jìn)行優(yōu)化：最終去掉了以前的所有索引黍匾，根據(jù)具體業(yè)務(wù)栏渺，只新建了兩個(gè)復(fù)合索引（其他查詢都可以重用復(fù)合索引中的部分）。

（ps：實(shí)際修復(fù)步驟：新建一張字段信息相同的表锐涯，并創(chuàng)建新的索引磕诊，再把老表中的數(shù)據(jù)同步到新表）

至此該問題解決，期間丟失部分業(yè)務(wù)數(shù)據(jù)纹腌，但慶幸的是該系統(tǒng)是一個(gè)外圍系統(tǒng)秀仲，損失還在可控范圍內(nèi)。

新問題Duplicate PRIMARY

在解決上述索引問題的過程中壶笼，我始終覺得這張表的主鍵創(chuàng)建方式會(huì)導(dǎo)致問題神僵。根據(jù)上述主鍵的創(chuàng)建原則：“主鍵最好使用自增型”，但上述表的主鍵不滿足該規(guī)則：

PRIMARY KEY (`id`),

在問題修復(fù)后覆劈，我們持續(xù)的對(duì)日志進(jìn)行不定期的檢查保礼，果然又有新的發(fā)現(xiàn)沛励，日志中偶爾會(huì)報(bào)錯(cuò)：

Duplicate entry 'xxx' for key 'PRIMARY'

問題很明顯，就是數(shù)據(jù)在插入時(shí)炮障，發(fā)現(xiàn)改主鍵id ‘xxx’已經(jīng)存在目派，報(bào)主鍵重復(fù)寫入沖突錯(cuò)誤。立即提取代碼分析問題胁赢，這里的主鍵id是另外一張表X的主鍵企蹭，根據(jù)業(yè)務(wù)查詢X表，對(duì)滿足條件的記錄進(jìn)行加工后插入該新表智末。其代碼邏輯如下：

------開啟spring 事務(wù)省略代碼 -------

xxxPcActProfile oldInfo = xxxDao.getById(newInfo.getId);//先查詢?cè)撝麈Iid對(duì)應(yīng)的記錄是否存在

if (oldInfo == null){

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

}else{

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

}

------提交spring 事務(wù)省略代碼 -------

初步看該代碼沒有問題啊系馆，在一個(gè)事務(wù)里面送漠，怎么會(huì)出現(xiàn)插入時(shí)“主鍵沖突”呢。

其實(shí)不要被事務(wù)所欺騙由蘑，我們來分析下在高并發(fā)情況下闽寡，兩個(gè)相同id的插入請(qǐng)求：

事務(wù)1 事務(wù)2

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

2尼酿、判斷結(jié)果：不存在爷狈，進(jìn)行插入判斷結(jié)果：不存在，進(jìn)行插入

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

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

5句惯、關(guān)閉事務(wù)關(guān)閉事務(wù)

由于mysql的插入是不可分割的指令是原子性的土辩，必須得等待其中一條插入完成后，另一條才能插入抢野。這就導(dǎo)致了上述“主鍵沖突”異常的發(fā)生拷淘。

該問題導(dǎo)致的后果：在發(fā)現(xiàn)已經(jīng)存在后，應(yīng)該執(zhí)行修改指孤，但沖突后直接拋出異常启涯，修改操作沒有被執(zhí)行，導(dǎo)致修改數(shù)據(jù)丟失恃轩。該問題在高并發(fā)的情況下结洼，還會(huì)經(jīng)常出現(xiàn)。

最終的解決辦法：采用 insert duplicate update語句叉跛，問題得到解決松忍，語法如下：

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

至此問題得以解決。但仍心有余悸筷厘，加強(qiáng)sql編寫規(guī)范學(xué)習(xí)鸣峭，并對(duì)所有sql進(jìn)行code review勢(shì)在必行宏所。

@來自科多大數(shù)據(jù)