性能分析: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索引造成。
优化方案:
把delete改为没有where条件的全表delete或truncate(该表数据是缓存数据)。
把HASH索引改为BTREE索引。
注:由于btree索引占用的内存空间很大(经测试,btree索引占用空间是hash索引的6倍以上),数据库主机当时内存紧张,所以优先使用方案1。
网站名称:性能分析:hash索引导致delete慢
文章位置:http://pwwzsj.com/article/gihgcd.html