MySQL -- KILL + 客户端

迷南。 2022-12-13 04:56 220阅读 0赞

## KILL ##

1.  `KILL QUERY THREAD_ID` **终止这个线程中正在执行的语句**
2.  `KILL [ CONNECTION ] THREAD_ID` 断开这个线程的连接，如果该线程有语句在执行，先停止正在执行的语句

## 锁等待 ##

### 表初始化 ###

CREATE TABLE `t` (
      `id` INT(11) NOT NULL,
      `c` INT(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;
    
    INSERT INTO t VALUES (1,1);

### 操作次序 ###

<table> 
 <thead> 
  <tr> 
   <th>session A</th> 
   <th>session B</th> 
   <th>session C</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>BEGIN;</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>UPDATE t SET c=c+1 WHERE id=1;</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>UPDATE t SET c=c+2 WHERE id=1;(Blocked)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td>SHOW PROCESSLIST;</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td>KILL QUERY 24;</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>ERROR 1317 (70100): Query execution was interrupted</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

### KILL QUERY ###

1.  对表进行增删改查操作时，会在表上加MDL读锁
2.  KILL QUERY并不是马上停止，而是告诉线程这条语句已经不需要再继续执行，可以开始执行停止的逻辑
    
    1.  类似于Linux的kill -N pid命令，并不是让进程直接停止
    2.  而是给进程发一个信号，然后让进程处理这个信号，进入终止逻辑
3.  MySQL的具体动作
    
    1.  把session B的线程状态改成THD:KILL\_QUERY(将变量killed赋值为THD:KILL\_QUERY)
    2.  给session B的执行线程发一个信号
        
        1.  session B原本处于锁等待状态
        2.  如果只是修改线程状态，线程B是不知道这个状态的变化的，还会继续等待
        3.  发信号的目的：让session B退出等待，来处理THD:KILL\_QUERY状态
4.  隐含逻辑
    
    1.  一个语句在执行过程中会有多处埋点，在这些埋点的地方会判断线程状态。 **如果发现线程状态为THD:KILL\_QUERY，才开始进入语句终止的逻辑**
    2.  如果处于等待状态，必须是一个可以被唤醒的等待，否则根本不会执行到埋点处
    3.  语句从开始进入终止逻辑，到完全完成终止逻辑，是有个过程的

## 并发线程数 ##

SET GLOBAL innodb_thread_concurrency=2;

### 操作序列 ###

<table> 
 <thead> 
  <tr> 
   <th>session A</th> 
   <th>session B</th> 
   <th>session C</th> 
   <th>session D</th> 
   <th>session E</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SELECT SLEEP(100) FROM t;</td> 
   <td>SELECT SLEEP(100) FROM t;</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td>SELECT * FROM t;(Blocked)</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>SHOW PROCESSLIST;(1st)</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>KILL QUERY 28;(无效)</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>ERROR 2013 (HY000): Lost connection to MySQL server during query</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>SHOW PROCESSLIST;(2nd)</td> 
  </tr> 
 </tbody> 
</table>

### KILL QUERY / CONNECTION ###

1.  session C执行的时候被堵住了，session D执行KILL QUERY没啥效果
    
    1.  等行锁时，使用的是pthread\_cond\_timedwait函数，这个等待状态是可以被唤醒的
    2.  本例中，28号线程的等待逻辑为:每10ms判断下能否进入InnoDB执行，如果不行，调用nanosleep函数进入SLEEP状态
    3.  虽然28号线程的状态被设置成了THD:KILL\_QUERY，但在等待进入InnoDB的循环过程中,**并没有去判断线程的状态，因此根本不会进入终止逻辑阶段**
2.  session E执行KILL CONNECTION，断开session C的连接，Command列变成了Killed
    
    1.  表示客户端虽然断开了连接，但实际上服务端上这条语句还是在执行中
    2.  把28号线程的状态设置为THD:KILL\_CONNECTION，然后关闭12号线程的网络连接。session C会收到断开连接的提示
3.  SHOW PROCESSLIST
    
    1.  如果一个线程的状态为THD:KILL\_CONNECTION，Command列会显示为Killed
    2.  28号线程只有满足进入InnoDB的条件后，session C的查询语句将继续执行：
        
        1.  才有可能判断到线程状态是否已经变成了KILL\_QUERY或者KILL\_CONNECTION
        2.  再进入终止逻辑阶段

### KILL无效的情况 ###

1.  线程没有执行到判断线程状态的逻辑
    
    1.  innodb\_thread\_concurrency过小
    2.  例如IO压力过大，读写IO的函数一直没有返回，导致不能及时判断线程的状态
2.  终止逻辑耗时较长，SHOW PROCESSLIST显示为Command=Killed
    
    1.  超大事务执行期间被KILL，**回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长**
    2.  大查询回滚
        
        1.  查询过程中生成了较大的临时文件，恰好此时文件系统压力较大
        2.  删除临时文件需要等待IO资源，导致耗时较长
    3.  DDL执行到最后阶段被KILL。**需要删除中间过程的临时文件，可能受IO资源影响，耗时比较久**

### CTRL + C ###

1.  在客户端的操作只能影响到客户端的线程
2.  客户端与服务端只能通过网络交互，因此是不可能直接操作服务端线程的
3.  MySQL是停等协议，在这个线程执行的语句还没有返回的时候，再往该连接发命令是没有用的。**实际上，执行CTRL + C，MySQL是另起一个连接，然后发送一个KILL QUERY命令**

### mysql -A ###

$ mysql -uroot -Dtest
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

1.  MySQL客户端默认会提供一个本地库名和表名补全的功能，在客户端连接成功后，会多执行以下操作
    
    1.  SHOW DATABASES; -> USE test; -> SHOW TABLES;
    2.  目的：用于构建一个本地的哈希表
2.  当表很多的时候，会表现得慢，但这是客户端慢，而非连接慢或者服务端慢

### mysql –quick ###

1.  MySQL客户端发送请求后，接收服务端返回结果的两种方式
    
    1.  使用本地缓存，在本地开辟一片内存，先把结果存起来，对应API为mysql\_store\_result。**MySQL客户端默认行为，即不加参数–quick**
    2.  MySQL客户端默认行为，即不加参数–quick。**如果客户端本地处理得慢，会导致服务端发送结果被阻塞，导致服务端变慢**
2.  使用–quick的效果
    
    1.  \-A参数，跳过表名自动补全功能
    2.  mysql\_store\_result需要申请本地内存来缓存结果。**如果查询结果太大，可能会影响客户端本地机器的性能**
    3.  不会把执行命令记录到本地的命令历史文件
3.  –quick的目的是为了让客户端更快，但有可能会降低服务端性能

### 参考资料 ###

《MySQL实战45讲》