性能分析：hash索引導(dǎo)致delete慢

前端時(shí)間，應(yīng)用人員上報(bào)一個(gè)性能問題：在生產(chǎn)環(huán)境中，每天凌晨時(shí)段數(shù)據(jù)庫運(yùn)行很慢，一些EVENT運(yùn)行失敗，導(dǎo)致一部分應(yīng)用功能異常。

根據(jù)應(yīng)用人員提供的時(shí)間段，對(duì)數(shù)據(jù)庫進(jìn)行排查。

先對(duì)主機(jī)CPU、IO、數(shù)據(jù)庫連接等監(jiān)控歷史數(shù)據(jù)進(jìn)行分析，確認(rèn)故障時(shí)間線，縮小時(shí)間范圍。

從上圖看到0:30左右，數(shù)據(jù)庫活動(dòng)連接由0增到200，1:09活動(dòng)連接數(shù)增到400+，數(shù)據(jù)庫連接異常增高，需要進(jìn)一步分析數(shù)據(jù)庫此時(shí)間在執(zhí)行什么操作。

對(duì)抓取到的歷史數(shù)據(jù)（主機(jī)部署了shell監(jiān)控腳本）進(jìn)行分析：在0:30，數(shù)據(jù)庫正在對(duì)表_1030做delete操作，其他線程在等待表鎖。

綜合以上，梳理出故障時(shí)間線：

監(jiān)控?cái)?shù)據(jù)顯示，0:30表_1030進(jìn)行delete操作，該操作在1:15分左右才執(zhí)行完成，該操作運(yùn)行了40+分鐘左右，此期間表_1030的select操作被阻塞，導(dǎo)致數(shù)據(jù)庫連接從0升高到200，最大達(dá)到400，應(yīng)用異常：

造成阻塞的SQL為：

DELETE FROM _1030 WHERE _1030.F05 <=  NAME_CONST('_current_date',_latin1'2016-01-17 00:30:00' COLLATE 'latin1_swedish_ci')

結(jié)合以上，有2個(gè)疑問：

該delete語句為什么會(huì)產(chǎn)生表鎖？

該delete語句為什么這么慢？能否優(yōu)化？

(root@172.30.3.113) [(none)]> show create table S11._1030 \G

*************************** 1. row ***************************

       Table: _1030

Create Table: CREATE TABLE `_1030` (

  `F01` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `F02` char(45) NOT NULL,

  `F03` datetime NOT NULL,

  `F04` int(10) unsigned DEFAULT NULL,

  `F05` datetime NOT NULL,

  `F06` varchar(40) NOT NULL,

  `F07` varchar(40) DEFAULT NULL,

  PRIMARY KEY (`F01`),

  UNIQUE KEY `F02` (`F02`) USING HASH,

  KEY `F06` (`F06`) USING HASH,

  KEY `F07` (`F07`) USING HASH

) ENGINE=MEMORY DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

通過查看發(fā)現(xiàn)該表是heap表，heap表數(shù)據(jù)都在內(nèi)存里，heap性能應(yīng)該是很快的，該delete語句為什么這么慢？

在測(cè)試環(huán)境進(jìn)行測(cè)試，DELETE _1030 50w的數(shù)據(jù)量需要58s,慢的不合常理。刪除該表的索引后，delete 1s內(nèi)完成。這里基本確認(rèn)索引維護(hù)代價(jià)太大導(dǎo)致。

添加btree索引，再次測(cè)試，delete 1s內(nèi)完成。確認(rèn)是hash索引造成。

優(yōu)化方案：

1. 把delete改為沒有where條件的全表delete或truncate（該表數(shù)據(jù)是緩存數(shù)據(jù)）。

2. 把HASH索引改為BTREE索引。

注：由于btree索引占用的內(nèi)存空間很大（經(jīng)測(cè)試，btree索引占用空間是hash索引的6倍以上），數(shù)據(jù)庫主機(jī)當(dāng)時(shí)內(nèi)存緊張，所以優(yōu)先使用方案1。