响应式网站开发,ui设计做兼职的网站,一个人看的浏览器,潍坊网站建设哪里好假设存在表t#xff0c;这个表有两个字段id和c#xff0c;并且我在里面插入了10万行记录。
-- 创建表t
CREATE TABLE t (id int(11) NOT NULL,c int(11) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB;-- 通过存储过程向t写入10w行数据
delimiter ;;
create procedure idat…假设存在表t这个表有两个字段id和c并且我在里面插入了10万行记录。
-- 创建表t
CREATE TABLE t (id int(11) NOT NULL,c int(11) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB;-- 通过存储过程向t写入10w行数据
delimiter ;;
create procedure idata()
begindeclare i int;set i1;while(i100000)doinsert into t values(i,i);set ii1;end while;
end;;
delimiter ;-- 插入数据
call idata();
查询长时间不返回 查询长时间不返回的原因被其他线程堵住了。
在表t执行下面的SQL语句
select * from t where id 1;
查询结果长时间不返回。 一般碰到这种情况的话 大概率是表t被锁住了。 接下来分析原因的时候 一般都是首先执行一下show processlist命令 看看当前语句处于什么状态。
然后我们再针对每种状态 去分析它们产生的原因、 如何复现 以及如何处理。
场景一等MDL锁
如图所示 就是使用show processlist命令查看Waiting for table metadata lock的示意图。 出现这个状态表示的是 现在有一个线程正在表t上请求或者持有MDL写锁 把select语句堵住了。
在MySQL 5.7版本下复现这个场景 如图所示 我给出了简单的复现步骤。 session A 通过lock table命令持有表t的MDL写锁 而session B的查询需要获取MDL读锁。 所以 session B进入等待状态。
这类问题的处理方式 就是找到谁持有MDL写锁 然后把它kill掉。 问如何找到是谁持有MDL写锁呢
方式一如果session A在加表锁后接着执行了别的语句那么便可通过执行show processlist命令找到造成阻塞的process id。
方式二如果如果session A在加表锁后没有执行任何命令即Command列是“Sleep”。此时需要通过查询sys.schema_table_lock_waits这张表找出造成阻塞的process id。MySQL启动时需要设置performance_schemaonMySQL5.5默认是关闭此功能的若要开启需手动在配置文件里添加从MySQL5.6开始默认打开此功能
在上述场景下另启一个session C然后执行
select blocking_pid from sys.schema_table_lock_waits;
执行结果 最后在找到造成阻塞的process id后把这个连接用kill命令断开即可。
场景二等flush
MySQL里面对表做flush操作的用法 一般有以下两个
-- flush指定table
flush tables t with read lock;--flush全部table
flush tables with read lock;
这两个flush语句 如果指定表t的话 代表的是只关闭表t 如果没有指定具体的表名 则表示关闭MySQL里所有打开的表。
正常情况下这两个语句执行起来都很快 除非它们也被别的线程堵住了。
所以 出现Waiting for table flush状态的可能情况是 有一个flush tables命令被别的语句堵住了 然后它又堵住了我们的select语句。
注flush table的作用关闭表并清空查询缓存。 现在 我们一起来复现一下这种情况 复现步骤如图所示 在session A中 我故意每行都调用一次sleep(1) 这样这个语句默认要执行10万秒 在这期间表t一直是被session A“打开”着。 然后 session B的flush tables t命令再要去关闭表t 就需要等session A的查询结束。 这样 session C要再次查询的话 就会被flush 命令堵住了。
这个复现步骤的show processlist结果 看到这个show processlist的结果 肯定就知道应该怎么做了。
场景三等行锁
经过了表级锁的考验 我们的select 语句终于来到引擎里了。
select * from t where id1 lock in share mode;
由于访问id1这个记录时要加读锁 如果这时候已经有一个事务在这行记录上持有一个写锁 我们的select语句就会被堵住。
复现步骤和现场如下 显然 session A启动了事务 占有写锁 还不提交 是导致session B被堵住的原因。 问等行锁的场景下怎么查出是谁占着这个写锁呢
答如果是MySQL5.7及以上版本可以通过sys.innodb_lock_waits 表查到。
查询方法
select * from t sys.innodb_lock_waits where locked_tabletest.t\G
执行结果 可以看到 这个信息很全 4号线程是造成堵塞的罪魁祸首。 而干掉这个罪魁祸首的方式 就是KILL 4。
查询慢 经过了重重封“锁” 我们再来看看一些查询慢的例子。
先来看一条你一定知道原因的SQL语句
select * from t where c 50000 limit 1;
由于字段c上没有索引 这个语句只能走id主键顺序扫描 因此需要扫描5万行。
接下来 我们再看一个只扫描一行 但是执行很慢的语句。
select * from t where id 1
慢查询结果 虽然扫描行数是1 但执行时间却长达800毫秒。是不是有点奇怪呢 这些时间都花在哪里了
如果我把这个slow log的截图再往下拉一点 你可以看到下一个语句 select * from t where id1 lock in share mode 执行时扫描行数也是1行 执行时间是0.2毫秒。 看上去是不是更奇怪了 按理说lock in share mode还要加锁 时间应该更长才对啊。
场景复现 session A先用start transaction with consistent snapshot该命令启动事务时就创建了一致性视图命令启动了一个事务 之后session B才开始执行update 语句。session B更新完100万次 生成了100万个回滚日志(undo log)。
session A在执行select * from t where id1时需要判断当前记录的可见性由于session B执行了100万次所以session A需要判断100万次这也就是为什么select * from t where id1语句执行的比较慢。
session A在执行select * from t where id1 lock in share mode时直接读最新记录即可无需判断记录的可见性所以执行的比较快。
上述两个语句的执行输出结果 假设有如下表结构
CREATE TABLE t(id int(11) NOT NULL,c int(11) DEFAULT NULL,d int(11) DEFAULT NULL,PRIMARY KEY (id),KEY c (c)
) ENGINEInnoDB;insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
小结思考题 思考如果执行如下语句语句序列是怎么加锁的锁又是什么时候释放的
begin;
select * from t where c 5 for update;
commit; 这个语句会命中d5的这一行 对应的主键id5 因此在select 语句执行完成后 id5这一行会加一个写锁 而且由于两阶段锁协议 这个写锁会在执行commit语句的时候释放。
