为什么我只查一行的语句，也执行这么慢？读后总结

待我称王封你为后i 2024-04-17 05:39 57阅读 0赞

sql执行慢的常见原因：mysql压力大，CPU占用率高，IO利用率高。这不属于今天讨论的范围。

造表与造数据的语句  
mysql> 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.  查询长时间不返回  
    select \* from t where id = 1；  
    遇到长时间不返回，大概率是表被锁住了。  
    分析手段：通过show processlist命令查看语句状态  
    1.1等待MDL锁  
    查看show processlist 的state字段显示waiting for table metadata lock 等待MDL锁。  
    这表示现在有另一个线程正在表t上持有MDL写锁（一般就是DDL操作），把select操作堵住了。  
    这类问题的处理方式谁持有MDL写锁，然后把它kill掉。但是在show processlist命令输出中很难找到。我们可以在performance\_schema和sys系统库查找，不过需要设置performance\_schema=on这个参数开启时，会有10%左右性能损失。  
    select blocking\_pid from sys.schema\_table\_lock\_waits;  
    1.2等flush  
    select \* from information\_schema.processlist where id =1;  
    查询该线程的状态是waiting for table flush。  
    这表示有一个线程正在对表t做flush操作，阻塞了select操作。  
    flush tables t with read lock;//关闭表t  
    flush tables with read lock;//关闭所有打开的表  
    flush tables 语句执行都很快，除非flush语句被其他语句阻塞了  
    这样的话，我们一般先用show processlist看一下整体状态，追踪一下阻塞源头。  
    1.3等待行锁  
    select \* from t where id=1 lock in share mode;  
    如果在执行这条查询语句时，有事务在这行记录上持有一个写锁。select将被阻塞  
    查询是谁占着写锁，如果你用的是mysql5.7的版本可以查询sys.innodb\_lock\_waits表  
    select \* from t sys.innodb\_lock\_waits where locked\_table=`'test'.'t'`\\G;  
    kill query 4是无效的，占有行锁的update语句已经执行完成了，但是由于事务没有提交导致行锁没有释放，kill 4 直接断开这个连接，会自动回滚这个连接里面执行的线程。从而释放了行锁。

2：查询慢  
扫描行数很多的情况：  
select \* from t where c=50000 limit 1；  
由于c上没有索引，进行主键索引全表扫描。返回不会很快。  
查询某个查询是否慢，我们可以去查看慢查询日志。  
set long\_query\_time=0,将慢查询日志的时间阈值设置为0.  
坏查询不一定是慢查询，有的时候由于数据量还没起来的时候，查询不会很慢，但是随着数据量的上升，导致查询时间的线性上升。  
扫描行数只有一行的情况：  
select \* from t where id=1;//800ms  
select \* from t where id=1 lock in share mode;//0.2ms  
在查询id为1的记录前，大量的更新id=1记录，这样就会产生大量的undo log  
sql1需要从最新视图遍历undo log计算出之前的视图。很耗时。