最近发现一些SQL,很正常的查询或者根据主键ID查询,也加入了慢SQL预警,一条 SQL 语句,正常执行的时候特别快,有时候不知道怎么回事,它会变的很慢。为什么?有的同学会说:MySQL负载过大,再此不讨论此种情况。
刷脏页:
当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为“干净页”。
刷脏页的四个场景:
合理地设置 innodb_io_capacity 的值,并且平时要多关注脏页比例,不要让它经常接近 75%。
等MDL锁
用show processlist发现当前语句正在:Waiting for table metadata lock,表示当前表t上有MDL写锁,因为查询时需要MDL读锁,因此进入等待状态。
在performance_schema=ON的状态下,执行select * from sys.schema_table_lock_waits; 找到本行sql后,找到对应的blocking_pid字段,kill 这个pid即可。kill pid;
等flush
如果当前语句状态是:Waiting for table flush,表示当前有现成对表做flush操作【FTWRL,数据迁移时为了保证数据的一致性】
flush tables t with read lock; flush tables with read lock; 出现这种情况的可能是:FTWRL语句被别的语句堵住了。比如一个查询语句执行时间特别长,FTWRL语句关闭表时被堵住,之后的查询全部被堵住。
等行锁
select * from t where id = 1 lock in share mode表示添加共享锁,读取最新的数据,需要等待行锁释放。如果执行select * from t where id = 1 不等待行锁。
可以执行下面语句来查询是谁占有这个行锁:select * from sys.innodb_lock_waits where locked_table=X';
知识点:共享锁 (lock in share mode)、排他锁 (for update)。
RR隔离级别下,为保证binlog记录顺序,非索引更新会锁住全表记录,且事务结束前不会对不符合条件记录有逐步释放的过程。请使用唯一索引或者主键索引更新数据。
kill掉对应进程后,断开连接,自动回滚,释放行锁。
查询慢
select * from t where id = 1; // 执行速度特别慢
select * from t where id = 1 lock in share mode;// 执行速度很快
带 lock in share mode 的 SQL 语句,是当前读,因此会直接读到 1000001 这个结果,所以速度很快;
而 select * from t where id=1 这个语句,是一致性读,因此需要从 1000001 开始,依次执行 undo log,执行了 100 万次以后,才将 1 这个结果返回。
修复慢SQL,是一个需要有全局意识处理的问题,不能头疼医头,脚痛医脚。坏查询不一定慢SQL,不要仅仅修复一个慢SQL,可以引入结构性优化问题。
每周一句:不断建立自己的正向循环系统,保持热爱,不念过去,不惧未来。