性能分析：hash索引導致delete慢

前端時間，應用人員上報一個性能問題：在生產環境中，每天凌晨時段數據庫運行很慢，一些EVENT運行失敗，導致一部分應用功能異常。

根據應用人員提供的時間段，對數據庫進行排查。

先對主機CPU、IO、數據庫連接等監控歷史數據進行分析，確認故障時間線，縮小時間范圍。

從上圖看到0:30左右，數據庫活動連接由0增到200，1:09活動連接數增到400+，數據庫連接異常增高，需要進一步分析數據庫此時間在執行什么操作。

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

綜合以上，梳理出故障時間線：

監控數據顯示，0:30表_1030進行delete操作，該操作在1:15分左右才執行完成，該操作運行了40+分鐘左右，此期間表_1030的select操作被阻塞，導致數據庫連接從0升高到200，最大達到400，應用異常：

造成阻塞的SQL為：

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

結合以上，有2個疑問：

該delete語句為什么會產生表鎖？

該delete語句為什么這么慢？能否優化？

(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)

通過查看發現該表是heap表，heap表數據都在內存里，heap性能應該是很快的，該delete語句為什么這么慢？

在測試環境進行測試，DELETE _1030 50w的數據量需要58s,慢的不合常理。刪除該表的索引后，delete 1s內完成。這里基本確認索引維護代價太大導致。

添加btree索引，再次測試，delete 1s內完成。確認是hash索引造成。

優化方案：

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

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

注：由于btree索引占用的內存空間很大（經測試，btree索引占用空間是hash索引的6倍以上），數據庫主機當時內存緊張，所以優先使用方案1。