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

前端时间，应用人员上报一个性能问题：在生产环境中，每天凌晨时段数据库运行很慢，一些EVENT运行失败，导致一部分应用功能异常。

成都创新互联是一家专业提供宁洱企业网站建设,专注与成都网站制作、成都网站建设、H5响应式网站、小程序制作等业务。10年已为宁洱众多企业、政府机构等服务。创新互联专业网络公司优惠进行中。

根据应用人员提供的时间段，对数据库进行排查。

先对主机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。

分享标题：性能分析：hash索引导致delete慢
新闻来源：http://scjbc.cn/article/gihgcd.html