有时候只查询一行语句，执行也慢

1 查询长时间不返回

• 假如有一个SQL

  mysql> select * from t where id=1;
  1

  • 查询结果长时间不返回

    

  • 一般是表t被锁住了，这时候执行show processlist

1.1 等MDL锁

• 使用 show processlist 命令查看 Waiting for table metadata lock 的示意图。

  

  • 出现这个状态表示，现在有一个线程正在表t上请求或持有MDL写锁，把select堵住了
  • 查询sys.schema_table_lock_waits这张表，找到阻塞的process id，用kill命令将连接断开
  • 

1.2 等flush

• 执行SQL

  select * from information_schema.processlist where id=1;
  1

  • 查出来这个线程的状态是 Waiting for table flush，你可以设想一下这是什么原因。

  • 

  • 这个状态表示的是，现在又一个线程正要堆表t做flush操作，MySQL里面堆表做flush操作的用法，一般有以下两个:

    
    flush tables t with read lock;
    
    flush tables with read lock;
    
    //flush tables 是将buffer pool的数据刷回到磁盘中
    1
    2
    3
    4
    5
    6

  • 这两个flush语句，如果指定表t的话，代表的是只关闭表t；如果没有指定具体的表明，则表示关闭MySQL里所有打开的表。

    • 正常情况下这语句执行很快，除非也是被堵住了。
    • 所以，出现 Waiting for table flush 状态的可能情况是：有一个 flush tables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句。

• 复现步骤

  

  1. 在session A中，每行都调用一次sleep(1)，这样这个语句默认要执行10万秒，在这期间表t一直被session A打开。

  2. show processlist

  3. 将sessionA 线程kill 13

     KILL [CONNECTION | QUERY] processlist_id
      kill query 13 //连接有SQL执行会继续执行
      kill 13 //不管有没有，都会将线程杀掉。
     1
     2
     3

1.3 等行锁

• SQL

   select * from t where id=1 lock in share mode; 
   
    select k from t where id=1 lock in share mode; 
    select k from t where id=1 for update; 
    select 语句如果加锁，是当前读；
    分别加了读锁（S 锁，共享锁）和写锁（X 锁，排他锁）。
  1
  2
  3
  4
  5
  6

  • 由于id = 1 这个记录加了读锁，如果这时候已经有一个事务在这行记录上持有一个读锁，我们的select 语句就会被锁住。

  • 

  • 查询语句被锁住：

  • 因此是session A启动事务，占有写锁后，还不进行提交，导致了session B被锁住。

  • 查询占用

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

  • 然后将query kill掉，需要直接进行kill id ，直接将连接断开后，才会事务回滚释放锁。

2 查询慢

•  select * from t where c=50000 limit 1;
  1

• 由于字段c上没有索引，所以这个语句直走id主键索引，进行全表扫描。

  • 扫描行数多，执行慢。坏查询不一定是慢查询

• diselect * from t where id=1;
  select * from t where id=1 lock in share mode;
  1
  2

  • 第一条语句慢查询：

  • 第二条语句慢查询：

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-b9UYTdjJ-1656643464196)(https://cdn.jsdelivr.net/gh/Cltlient/PiGoCDN/img/20220113171449.png)]

  • 执行结果：

    

• 复现步骤：

  • session A 先用 start transaction with consistent snapshot 命令启动了一个事务，之后 session B 才开始执行 update 语句。、
  • session B执行万100万次update 后，id = 1 这一行状态如下：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-P5g5ZU38-1656643464197)(https://cdn.jsdelivr.net/gh/Cltlient/PiGoCDN/img/20220113171702.png)]
  • session B更新完100万次，生成了100万个回滚日志(undo log)。
  • 带lock in share mode的SQL语句，是当前读，因此会直接读到100001这个结果，所以速度很快；
  • select * from where id =1 这个语句，是一致性读，因此需要从100001开始，一次执行undolog，执行100万次，将1这个记过返回。

3 小结：

• 差一行：可能会出现的被锁住和执行慢的例子，主要是表锁、行锁和一致性读的概念。

• 问题：

  • 举例加锁读的时候，用的是这个语句，select * from t where id=1 lock in share mode。由于 id 上有索引，所以可以直接定位到 id=1 这一行，因此读锁也是只加在了这一行上。

  • 但如果是下面的 SQL 语句，

    
    begin;
    select * from t where c=5 for update;
    commit;
    1
    2
    3
    4

  • 这个语句怎么加锁的呢？加的锁又是什么时候释放？