【第27天】SQL进阶-查询优化- performance
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
回城传送–》《32天SQL筑基》
文章目录
零、前言
今天是学习 SQL 打卡的第 27 天每天我会提供一篇文章供群成员阅读 不需要订阅付钱 。
希望大家先自己思考如果实在没有想法再看下面的解题思路自己再实现一遍。在小虚竹JAVA社区 中对应的 【打卡贴】打卡今天的任务就算完成了养成每天学习打卡的好习惯。
虚竹哥会组织大家一起学习同一篇文章所以有什么问题都可以在群里问群里的小伙伴可以迅速地帮到你一个人可以走得很快一群人可以走得很远有一起学习交流的战友是多么幸运的事情。
我的学习策略很简单题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍那意味着 SQL 已经筑基成功了。后面的进阶学习可以继续跟着我一起走向架构师之路。
今天的学习内容是SQL进阶-查询优化- performance_schema系列实战二锁问题排查表级锁
一、什么是表级锁
表级锁是MySQL中对当前操作的整张表加锁它实现简单资源消耗较少被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁共享锁与表独占写锁排他锁。
特点开销小加锁快不会出现死锁锁定粒度大发出锁冲突的概率最高并发度最低。
二、什么时候适合加表级锁
1、事务需要更新某张大表的大部分或全部数据。如果使用默认的行锁不仅事务执行效率低而且可能造成其它事务长时间锁等待和锁冲突这种情况下可以考虑使用表锁来提高事务执行速度
2、事务涉及多个表比较复杂可能会引起死锁导致大量事务回滚可以考虑表锁避免死锁。
3、表级锁更适合于以查询为主并发用户少只有少量按索引条件更新数据的应用如Web 应用
其中表锁又分为表读锁和表写锁命令分别是
表读锁
lock tables 表名 read;
表写锁
lock tables 表名 write;
解锁释放锁只能释放我们手动使用LOCK 加的锁, 不能释放死锁等其他情况的事物锁等
UNLOCK TABLES;
三、实战演练
通过一个示例演示如何找出谁持有表级锁。
3.1 数据准备如果已有数据可跳过此操作
使用sysbench准备初始化数据
创建测试数据库sysbenchdemo
create database sysbenchdemo;
准备测试数据
sysbench /usr/share/sysbench/oltp_insert.lua \
--mysql-host=localhost \
--mysql-port=3306 \
--mysql-socket=/tmp/mysql.sock \
--mysql-user=root \
--mysql-password=xiaoxuzhu \
--mysql-db=sysbenchdemo \
--db-driver=mysql \
--tables=8 \
--table-size=100000 \
--time=180 prepare
3.2 开启第一个会话执行显式加表级锁
登录mysql数据库
use sysbenchdemo;
查询以下加锁线程的process id以便后续排查过程好对应
select connection_id();
对sbtest1 表加表读锁
lock table sbtest1 read;
3.3 开启第二个会话对该表执行update更新
登录mysql数据库
use sysbenchdemo;
查询线程的process id以便后续排查过程好对应
select connection_id();
对该表执行update更新
update sbtest1 set pad='xxx' where id=1;
发现执行更新语句被阻塞。
3.4 开启第三个会话查询线程信息
show processlist;
可以发现update语句在等待MDL锁Waiting for table metadata lock
其中OWNER_THREAD_ID: # 持有锁的内部线程ID
查看process id为 1244212443 各自对应的内部线程ID是多少
select sys.ps_thread_id(12442);
process id=12442 的线程对应的内部线程ID正好为12606
select sys.ps_thread_id(12443);
process id=12443 的线程对应的内部线程ID正好为12607
3.5 分析
因为是等待MDL锁所以先看看performance_schema.metadata_locks表记录的顺序代表持有锁的时间顺序
select * from performance_schema.metadata_locks where OWNER_THREAD_ID!=sys.ps_thread_id(connection_id());
如下就是MDL相关的锁模式以及对应的SQL语句
| 锁模式 | 对应SQL |
|---|---|
| MDL_INTENTION_EXCLUSIVE | GLOBAL对象、SCHEMA对象操作会加此锁 |
| MDL_SHARED | FLUSH TABLES with READ LOCK |
| MDL_SHARED_HIGH_PRIO | 仅对 MyISAM 存储引擎有效 |
| MDL_SHARED_READ | SELECT查询 |
| MDL_SHARED_WRITE | DML语句 |
| MDL_SHARED_WRITE_LOW_PRIO | 仅对MyISAM存储引擎有效 |
| MDL_SHARED_UPGRADABLE | ALTER TABLE |
| MDL_SHARED_READ_ONLY | LOCK xxx READ |
| MDL_SHARED_NO_WRITE | FLUSH TABLES xxx,yyy,zzz READ |
| MDL_SHARED_NO_READ_WRITE | FLUSH TABLE xxx WRITE |
| MDL_EXCLUSIVE | ALTER TABLE xxx PARTITION BY … |
从图上可知,LOCK_TYPE 为SHARED_READ_ONLY的12606 内部线程手动加了表读锁
确认一下线程是否存在着一个没有提交的事务。
select * from information_schema.innodb_trx;
发现没有记录。
可以尝试着去查询一些表级别的锁信息通过会话3查询performance_schema.table_handles表
select * from performance_schema.table_handles where OWNER_THREAD_ID=12606;
证实了12606 内部线程手动加了表读锁但还是不知道该线程执行了什么SQL语句可以通过performance_schema.events_statements_current表查询。
select * from performance_schema.events_statements_current where thread_id=12606;
通过SQL_TEXT字段我们可以清晰地看到该线程正在执行的SQL语句是什么。
3.6 释放第一个会话的表读锁
UNLOCK TABLES;
第二个会话被阻塞的语句也顺利执行了。
在第三个会话中查看线程情况
show processlist;
从图上结果可知MDL锁被释放了。
四、总结
通过本文学习学会了什么是表级锁以及表级锁的适用场景通过实战演练排查表级锁问题,从理论到实战的介绍可以加深对表级锁的理解。
五、参考
应用示例荟萃 | performance_schema全方位介绍中
技术分享 | MySQL 的 MDL 锁解惑
SQL进阶-查询优化- performance_schema系列三事件记录SQL 小虚竹)
我是虚竹哥我们明天见~