MySQL添加索引导致表死锁问题

出现该问题描述:由于SQL慢,希望通过索引优化,发现很多单字段索引、且组合索引缺少一些关键字段,便决定删除单字段索引,补全组合索引。修改完索引后提交,发现执行时间几个小时都没有执行完成。
造成结果:测试环境数据库表死锁,一些数据库执行语句查询超时。
MySQL有三种锁的级别:页级、表级、行级。
表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般

问题排查

1、使用show processlist 和 show full processlist

show processlist:查询当前数据库连接、连接状态等相关信息(当前100条)
show full processlist:查询当前数据库连接、连接状态等相关信息(全部连接)

在这里插入图片描述
①.id列,用户登录mysql时,系统分配的"connection_id",可以使用函数connection_id()查看
②.user列,显示当前用户。如果不是root,这个命令就只显示用户权限范围的sql语句
③.host列,显示这个语句是从哪个ip的哪个端口上发的,可以用来跟踪出现问题语句的用户
④.db列,显示这个进程目前连接的是哪个数据库
⑤.command列,显示当前连接的执行的命令,一般取值为休眠(sleep),查询(query),连接(connect),死锁(locked)等
⑥.time列,显示这个状态持续的时间,单位是秒
⑦.state列,显示使用当前连接的sql语句的状态,很重要的列。state描述的是语句执行中的某一个状态。一个sql语句,以查询为例,可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
⑧.info列,显示这个sql语句,是判断问题语句的一个重要依据

2、找到对应的死锁(locked)的连接

我们只需通过command状态为(locked),并通过info判断是什么造成的死锁。造成死锁的时长,然后获取其ID。
例如上面ID为274589
我们该线程杀死即可

kill 274589

3、使用show processlist 和 show full processlist或重新查看

看连接Id是否存在,不存在则标识关闭成功。在验证数据库的查询性能,完美解决了

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

有趣的灵魂_不世俗的心

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值