• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

MySQL优化案例分享

MySQL SEian.G 3年前 (2021-09-26) 1305次浏览 已收录 0个评论
文章目录[隐藏]

临近十一,国庆放假的同时,往往会伴随着国庆期间业务要上相关的活动,那么今天就分享一个今年五一前夕(4月30日)上新活动中遇到的一个性能问题;

问题背景

五一前夕(4月30日)中午频繁的收到业务慢查询的告警

排查过程

查看mysql实例监控,发现CPU在不断增长,系统的负载也在不断的增加

MySQL优化案例分享

查看慢查询日志,发现有一个update操作的慢查询,执行时间在50s多,而且锁的时间48s多,经过和业务人员沟通,由于五一活动游戏开赛,所以出现业务量的突增,那么接下来我们来分析下慢SQL的问题,下面是慢查询日志中的记录:

# Time: 2021-04-30T06:32:37.832446Z
# User@Host: srv_xxx_rwl[srv_xxx_rwl] @  [10.30.xx.xx]  Id: 1602496
# Schema: test_tab  Last_errno: 0  Killed: 0
# Query_time: 54.673171  Lock_time: 49.894811  Rows_sent: 0  Rows_examined: 4  Rows_affected: 2
# Bytes_sent: 52
SET timestamp=1619764357;
UPDATE dic_match_signup_userinfo_6 SET state=0,mtime=NOW() WHERE msid=2332976 AND userid=724493906 AND state=1;
# Time: 2021-04-30T06:32:37.953099Z
# User@Host: srv_xxx_rwl[srv_xxx_rwl] @  [10.30.xx.xx]  Id: 299508
# Schema: test_tab  Last_errno: 0  Killed: 0
# Query_time: 54.078065  Lock_time: 48.940644  Rows_sent: 0  Rows_examined: 2  Rows_affected: 1
# Bytes_sent: 52
SET timestamp=1619764357;
UPDATE dic_match_signup_userinfo_6 SET state=0,mtime=NOW() WHERE msid=2332976 AND userid=749435007 AND state=1;
# Time: 2021-04-30T06:32:38.378148Z

从慢查询日志中可以看到,锁等待时间比较长,那么我们来看下锁相关的信息,可以发现出现了死锁的情况,如下所示:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-04-30 13:58:47 0x7faff13ff700
*** (1) TRANSACTION:
TRANSACTION 13826305171, ACTIVE 53 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 9186 lock struct(s), heap size 1220816, 13 row lock(s)
MySQL thread id 1152912, OS thread handle 140399163991808, query id 8222848458 10.30.xx.xx srv_xxx_rwl Searching rows for update
UPDATE dic_match_signup_userinfo_7 SET state=0,mtime=NOW() WHERE msid=2332976 AND userid=749435007 AND state=1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1376 page no 17410 n bits 320 index PRIMARY of table `test_tab`.`dic_match_signup_userinfo_7` trx id 13826305171 lock_mo
de X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 13826304999, ACTIVE 53 sec fetching rows
mysql tables in use 3, locked 3
9180 lock struct(s), heap size 1220816, 5 row lock(s)
MySQL thread id 299537, OS thread handle 140393643505408, query id 8222848 122 10.30.xx.xx srv_xxxx_rwl Searching rows for update
UPDATE dic_match_signup_userinfo_7 SET state=0,mtime=NOW() WHERE msid=2332976 AND userid=761512237 AND state=1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1376 page no 17410 n bits 320 index PRIMARY of table `test_tab`.`dic_match_signup_userinfo_7` trx id 13826304999 lock_mo
de X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1376 page no 21758 n bits 584 index idx_state of table `test_tab`.`dic_match_signup_userinfo_7` trx id 13826304999 lock_
mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

从上面死锁信息可以看到,产生是两个update操作导致的,按道理来说,update操作不应该会很慢,那么接下来,我们看一下sql语句的执行计划:

mysql>explain UPDATE dic_match_signup_userinfo_7 SET state=0,mtime=NOW() WHERE msid=2332976 AND userid=749435007 AND state=1\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: dic_match_signup_userinfo_7
   partitions: NULL
         type: index_merge
possible_keys: idx_msid_userid,idx_state
          key: idx_msid_userid,idx_state
      key_len: 8,4
          ref: NULL
         rows: 3269
     filtered: 100.00
        Extra: Using intersect(idx_msid_userid,idx_state); Using where; Using temporary
1 row in set (0.00 sec)

从执行计划中可以看到,MySQL执行计划选择了单独的2个二级索引,然后通过Using intersect算法进行index merge操作。从字面意义来上intersect就是 交集的意思。虽然性能上没多少影响,但比较好奇,在理解当中MySQL知识体系中是没有交集语法。using intersect:表示使用and的各个索引的条件时,该信息表示是从处理结果获取交集

通过查看官方文档了解到:

Using intersect方式是索引合并访问方法。一般有几种算法,在EXPLAIN输出的额外字段中显示:

Using intersect(…)

Using union(…)

Using sort_union(…)

索引合并交集算法对所有使用的索引执行同步扫描,并生成从合并索引扫描中接收到的行序列的交集。其中Using intersect 就是一种。

那么接下来看下表结构和索引结构:

mysql >show create table dic_match_signup_userinfo_7\G
*************************** 1. row ***************************
Table: dic_match_start_timer_trigger
Create Table: CREATE TABLE `dic_match_start_timer_trigger` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`msid` int(11) unsigned NOT NULL COMMENT '开赛ID',
`state` int(11) unsigned NOT NULL DEFAULT '1' COMMENT '0-失效,1-生效, 2-已完成',
.......
`btime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '生效时间',
PRIMARY KEY (`id`),
KEY `idx_msid_state` (`msid`,`state`),
KEY `idx_state` (`state`)
) ENGINE=InnoDB AUTO_INCREMENT=2375237 DEFAULT CHARSET=utf8 COMMENT='xxxxx'
1 row in set (0.00 sec)

从表结构中可以看到,两个二级索引idx_msid_state和idx_state,在结合SQL语句,我们来调整一下索引结构;

解决思路

添加组合索引KEY `idx_idx_msid_userid_state` (`msid`,`userid`,`state`),

添加完成后,我们在来看下执行计划

mysql>explain UPDATE dic_match_signup_userinfo_7 SET state=0,mtime=NOW() WHERE msid=2332976 AND userid=749435007 AND state=1\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: dic_match_signup_userinfo_7
   partitions: NULL
         type: range
possible_keys: idx_msid_userid,idx_state,idx_idx_msid_userid_state
          key: idx_idx_msid_userid_state
      key_len: 12
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using temporary
1 row in set (0.00 sec)

接着观察一下系统的负载是否有降下来,发现添加完索引之后,CPU和实例负载就降下来了;

MySQL优化案例分享

MySQL优化案例分享

那么两个update操作怎么就产生了死锁的情况呢?今天就先到这里了,后面我们会继续进行分享;

MySQL优化案例分享


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:MySQL优化案例分享
喜欢 (3)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址