查一条记录sql也很慢的原因

落日映苍穹つ 2022-03-26 05:20 191阅读 0赞

\#数据准备  
 CREATE TABLE \`t\` (  
\`id\` int(11) NOT NULL,  
\`c\` int(11) DEFAULT NULL,  
PRIMARY KEY (\`id\`)  
) ENGINE=InnoDB;

delimiter ;;  
create procedure idata()  
begin  
declare i int;  
set i=1;  
while(i<=100000)do  
insert into t values(i,i);  
set i=i+1;  
end while;  
end;;  
delimiter ;

call idata();

1等MDL 锁  
\#session1中对t上写锁  
lock table t write;  
\#session2中查询t，所以互斥，不能执行如下sql  
select \* from t where id=1;

show processlist;  
\#下面语句查询block的进程id，如果能查询到，可以使用kill + 进程号 杀死阻塞进程，但是我并没有查到查到pid  
select  blocking\_pid from sys.schema\_table\_lock\_waits;  
\#第二种方式是将processlist中的所有id kill掉  
如：kill 33

2等flush  
模拟如下：  
session1  
select sleep(1) from  t;  
session2  
flush tables t;  
session3  
select \* from t where id=1;

show processlist;  
kill 34;  
kill 36;  
kill 37;

3等行锁  
session1  
begin;  
update t set c=c+1 where id=1;  
session2  
select \* from t where id=1 lock in share mode;  
session A 启动了事务，占有写锁，还不提交，是导致 session B 被堵住的原因。  
  这个问题并不难分析，但问题是怎么查出是谁占着这个写锁。如果你用的是 MySQL 5.7 版本，可以通过 sys.innodb\_lock\_waits 表查到。  
  查询方法是：  
mysql> select \* from t sys.innodb\_lock\_waits where locked\_table=\`'test'.'t'\`\\G

慢查询：  
\#因为没有索引所以会查询5000行所以较慢  
select \* from t where c=50000 limit 1;

\# session A 先用 start transaction with consistent snapshot 命令启动了一个事务，之后 session B 才开始执行 update 语句。  
sessionA  
start transaction with consistent snapshot;  
session B  
update t set c=c+1 where id = 1;\#执行100万次  
SessionA  
sql1:select \* from t where id=1;  
sql2:select \* from t where id=1 lock in share mode;  
上面语句，sql1明显会慢与sql2  
原因是： 带 lock in share mode 的 SQL 语句，是当前读，因此会直接读到 1000001 这个结果，所以速度很快；而 select \* from t where id=1 这个语句，是一致性读，因此需要从 1000001 开始，依次执行 undo log，执行了 100 万次以后，才将 1 这个结果返回。