专栏首页洁癖是一只狗Mysql中sql执行如此慢

Mysql中sql执行如此慢

我们经常发现,往往执行一条简单的查询语句,但是很长时间都没有返回,今天我们看看是什么原因导致的

第一类:查询长时间不能返回

执行下面语句

select  *  from  t where id =1;

等待MDL锁

我们按照下面操作,看看会发生什么呢

我们发现sql语句很长时间都不见返回响应,我们先看一下他的状态,发现果然是被锁住了.

此类问题我们直接可以找到谁持有MDL的写锁,直接kill.

可以用查询sys.schema_table_lock_waits这张表,我们就可以直接找到阻塞的process id ,把这个连接用kill命令断开即可(mysql启动的时候设置performation_schema=on)

等待flush

下面我们说另外一种查询被阻塞的情况,当一个线程正好对表进行flush操作,本身这个线程执行的很快,但是如果这个线程flush线程被其他线程阻塞,最终会导致阻塞表t的查询,如下图所示

sessionA中,我们故意调用一次sleep(1),默认执行10万秒,这个时候t表是打开的,使用flush去关闭表t,就必须等待sessionA结束,同时也会阻塞sessionC

等待行锁

首先,我们看看下面sql语句

mysql> select * from t where id=1 lock in share mode; 

要执行上面语句的时候,这个记录就会要加读锁,如果这个时候已经有一个事物在这行记录上持有一个写锁,我们select 语句就会被阻塞。

这个问题并并不难分析,问题是如何查出谁占着这个写锁,如果你用的mysql5.7,可以使用下面语句

mysql> select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G

可以看到4号线程就是阻塞的罪魁祸首,因此只要干掉他就可以了,

不过,这里不应该显示kill query 4,这个命令是指把正在执行的语句停止,但是我们的update语句已经执行完成了,这样是无法去掉id=1的行锁.

实际上,kill 4才有效,也就是直接断开这个连接,这里连接被断开的时候,会自动回滚这个连接里面正在执行的线程,也就是释放id=1上的行锁.

第二类:查询慢

我们执行下面语句

select *  from t where c=50000 limit 1;

有字段c没有索引,这个语句只能全表扫描,因此要扫描5万行,再看看慢日志的记录.

发现扫描了50000行,消耗时间13.5毫秒,看起来很快,但是目前数据的数据只有10万行数据,如果数据量到千万级别,这个sql就会消耗很多时间。

我们在看看另外sql,如下图

select *  from t where id=1
select *  from t where id=1 lock in share mode

按照上面操作我们再看看对应的慢查询日志

我们发现lock in share mode加锁操作居然时间比没有加锁的查询块了,超出了我们的预期,我们再看看每个sql查询结果

此时我们就知道原因了,是因为session A先用start transaction with consistent snapshot启动了一个事物,然后sessionB才进行更新语句,然后在执行完100万次update语句后,此时的id是处于下图的状态

发现session B生产100万回滚日志(undo log),此时lock in share mode的sql语句,是当前读,因此会直接读到100001,速度很快,但是select * from t where id=1,是一致性读,因此从1000001开始,依次执行undo log,执行100万次,才会把1返回.

本文分享自微信公众号 - 洁癖是一只狗(rookie-dog),作者:洁癖汪

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-11-09

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Lock同步锁优化

    上一次我们介绍了Synchronized的优化,除此之外在JDK1.5之后,也提供了另外一种锁Lock,今天我们就看看这个有什么优势

    小土豆Yuki
  • 为什么会有Lock

    在并发编程的领域中,有两个核心问题,一个是互斥,即同一时刻只有一个线程访问共享资,一个是同步,即线程之间如何通讯,协作,这两大问题,管程都能够实现,在java ...

    小土豆Yuki
  • 如何解决可见性,有序性,原子性

    上一次我们说到了可见性,原子性,有序性,今天我们看看如何解决这个问题,今天我们先看看可见性和有序性,因此我们先要知道java内存模型

    小土豆Yuki
  • MYSQL基本操作

    create database if not exist db1;#如果不存在db1则创建

    嘘、小点声
  • 数据库优化

      1. 对sql语句进行优化, 首先对where和order by涉及的列加索引。

    用户7798898
  • TensorFlow 队列与多线程的应用

    深度学习的模型训练过程往往需要大量的数据,而将这些数据一次性的读入和预处理需要大量的时间开销,所以通常采用队列与多线程的思想解决这个问题,而且TensorFlo...

    chaibubble
  • X.Org Server软件包存在提权漏洞,影响主流Linux发行版

    印度安全研究员Narendra Shinde在X.Org Server软件包中发现了一个非常关键的提权漏洞(CVE-2018-14665),主流Linux发行版...

    FB客服
  • HotSpot垃圾收集器1 Serial垃圾收集器2 ParNew垃圾收集器3 Parallel Scavenge垃圾收集器老年代垃圾收集器1 Serial Old垃圾收集器2 Parallel Ol

    JavaEdge
  • Java多线程之synchronized关键字详解

    多线程的同步机制对资源进行加锁,使得在同一个时间,只有一个线程可以进行操作,同步用以解决多个线程同时访问时可能出现的问题。

    魏晓蕾
  • 深入理解JVM(五)——HotSpot垃圾收集器详解

    HotSpot虚拟机提供了多种垃圾收集器,每种收集器都有各自的特点,没有最好的垃圾收集器,只有最适合的垃圾收集器。我们可以根据自己实际的应用需求选择最适合的垃...

    大闲人柴毛毛

扫码关注云+社区

领取腾讯云代金券