问题

1. Can’t create thread to handle new connection(errno= 11)

一般是内存溢出了

2. mysql57不支持零值的datetime

解决方法：

# 1. 查询sql_mode
SHOW VARIABLES LIKE 'sql_mode'
Result : ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# 2. 移除这些sql_mode: NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO
set GLOBAL SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'

3. mysql日志打印时间与系统时间差8h

使用docker安装mysql5.7的时候，有两个小问题：

1. 执行sql: select now()时，发现时间比当前时间晚8小时。 select now()使用的是容器的系统时间，需要修改容器的时区。

   • docker容器使用的是UTC时间(UTC没有时区的概念)。所以在执行docker run命令的时候，使用-e参数设置时区: -e TZ="Asia/Shanghai
   • 设置时区之后，在容器中执行date -R命令可以验证时区是否设置成功。

2. mysql启动日志时间比当前时间晚8小时。

   • mysql日志输出时间默认使用的是UTC(UTC没有时区的概念)，而不是系统时间，需要在mysqld.cnf文件中添加以下配置：

     1 2	# vim /etc/mysql/mysql.conf.d/mysqld.cnf log_timestamps=SYSTEM

   • 验证： show engine innodb status;

3. 设置root用户的密码: -e MYSQL_ROOT_PASSWORD=molotest123

4. 使用binlog恢复数据

介绍： binlog文件记录了数据库的所有增删改稍作日志，并包含这些操作的执行时间。 可以简单的理解为数据库操作的流水账。 所以，在某个时刻使用mysqldump对所有数据库进行备份之后，一旦执行了一些错误的数据库操作，就可以先备份binlog文件，然后清除数据库，之后使用sql文件还原那个时刻的数据，最后使用binlog文件恢复那个时刻之后，执行错误数据库操作之前的数据。

问题：为什么要先备份binlog文件，再使用sql文件恢复数据？ 因为先使用sql文件恢复数据，就会在binlog文件中添加大量的流水记录，不利于寻找执行错误数据库操作的那个点。

1. 执行备份操作 命令：mysqldump --single-transaction --master-data=2 -uadmin -proot -F test > /opt/test.sql 这里的test是数据库名，而/opt/test.sql是sql文件保存的位置。 由于这里使用了-F参数，所以生成的/opt/test.sql文件中会有一行：-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000013', MASTER_LOG_POS=151; 会有一行与这行类似的，找到它就行了，一般在sql文件的前几行中。

2. 备份binlog文件 从1中可以知道，/opt/test.sql备份的点在binlog.000013，position=151上。 所以，我们执行如下的命令：

1
2
3

cd /data1/mysql   (根据你的mysql配置来)
# 这里假设我执行错误的数据库操作在418这个点上。
mysqlbinlog --start-position=151 --stop-position=418 ./binlog.000013 > /opt/151-418.sql

3. 恢复数据库数据

1
2
3
4

# 恢复在位置binlog.000013，position=151之前的数据
mysql -uadmin -proot test  < /opt/test.sql
# 恢复在位置binlog.000013，position=151之后，在执行错误数据库操纵之前的数据。
mysql -uadmin -proot < /opt/151-418.sql

问题，如果数据库所在的主机挂了，并且磁盘也没有办法读数据了，还能用上面的操作恢复数据吗？ 不能，因为上面的操作，必须保证binlog文件是完整的。如果磁盘无法读取数据，那么binlog文件肯定是有问题的。这个时候是无法恢复数据的。

5. 各种字符集

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

# 查询支持字符集
show character set;
select * from information_schema.character_sets;

# 查询当前使用的字符集
show variables like 'char%';
1. 服务器级别的字符集可以在配置文件中永久修改
2. 数据库级别的字符集默认是继承服务器的字符集
3. 表的字符集会继承数据库级别的字符集
4. 客户端，网络，返回结果集的字符集需要单独设置
   * character_set_client表明client发送给server用的是什么编码方式
   * character_set_connection表明传输的编码方式
   * character_set_results表明服务器返回出来的编码方式
   * 发送SQL到服务器的流程:
       `client编码->connection编码->server`
   * 从服务器返回结果的流程:
       `server->connection编码->results编码`

6. 误删数据后除了跑路，还能怎么办？

6.1 误删行

可以使用https://mariadb.com/kb/en/flashback/工具通过闪回把数据恢复回来。

Flashback 恢复数据的原理，是修改 binlog 的内容，拿回原库重放。而能够使用 这个方案的前提是，需要确保binlog_format=row和binlog_row_image=FULL。

具体恢复数据时，对单个事务做如下处理：

1. 对于 insert 语句，对应的 binlog event 类型是Write_rows event，把它改成Delete_rows event即可；
2. 同理，对于 delete 语句，也是将Delete_rows event改为Write_rows event；
3. 而如果是Update_rows的话，binlog 里面记录了数据行修改前和修改后的值， 对调这两行的位置即可。

6.2 误删库 / 表

这种情况下，要想恢复数据，就需要使用全量备份，加增量日志的方式了。 这个方案要求线上有定期的全量备份，并且实时备份 binlog。

在这两个条件都具备的情况下，假如有人中午 12 点误删了一个库，恢复数据的流程如下：

1. 取最近一次全量备份，假设这个库是一天一备，上次备份是当天 0 点；
2. 用备份恢复出一个临时库；
3. 从日志备份里面，取出凌晨 0 点之后的日志；
4. 把这些日志，除了误删除数据的语句外，全部应用到临时库。
   • 如果原实例没有使用 GTID 模式，只能在应用到包含 12 点的 binlog 文件的时候， 先用--stop-position参数执行到误操作之前的日志，然后再用--start-position 从误操作之后的日志继续执行；
   • 如果实例使用了 GTID 模式，就方便多了。假设误操作命令的 GTID 是 gtid1， 那么只需要执行set gtid_next=gtid1;begin;commit;先把这个 GTID 加到 临时实例的 GTID 集合，之后按顺序执行 binlog 的时候，就会自动跳过误操作的语句。

加速数据恢复的办法：

1. 如果这个临时库上有多个数据库，你可以在使用 mysqlbinlog 命令时，加上一个 --database参数，用来指定误删表所在的库。这样，就避免了在恢复数据时还要 应用其他库日志的情况。 注意： mysqlbinlog工具并不能指定只解析一个表的日志，而且解析过程是单线程。
2. 在用备份恢复出临时实例之后，将这个临时实例设置成线上备库的从库，这样：
   • 在 start slave 之前，先通过执行 change replication filter replicate_do_table = (tbl_name)命令， 就可以让临时库只同步误操作的表；
   • 这样做也可以用上并行复制技术，来加速整个数据恢复过程。

把数据恢复功能做成自动化工具，并且经常拿出来演练的重要性：

1. 虽然“发生这种事，大家都不想的”，但是万一出现了误删事件，能够快速恢复数据， 将损失降到最小，也应该不用跑路了。
2. 而如果临时再手忙脚乱地手动操作，最后又误操作了，对业务造成了二次伤害， 那就说不过去了。

7. mysql-driver 934工单

连接池的配置:

max_idle: 20  # 最大空闲连接数
max_open: 100 # 最大在线连接数
max_lifetime: 180000 # 连接最大生命周期(单位：毫秒)

在没有考虑连接池的前提下，一般来说invalid connection错误出现的原因是： mysql server已经关闭了该连接，但是gorm客户端从连接池中拿到了这个已经关闭的连接 进行CRUD操作导致。

进一步可以细分为下面3个原因：

1. mysql server配置参数wait_timeout小于client max_lifetime值;
2. mysql server主动断开connction;
3. client到mysql server网络不稳定，发生抖动现象，导致连接断开。

测试方案 将mysql server wait_timeout设置的比client的max_lifetime小。

这时，会报大量closing bad idle connection: EOF错误 将go-sql-driver升级到v1.5.0之后，发现不再报这个错误。

工单934解决方案 当mysql server主动关闭连接，或者因为mysql server wait_timeout连接等待超时，mysql server会在server端主动杀掉该连接，而client是不会直接的意识到该连接已经失效了。

client不知道自己已经接收到一个RST包(也就是server关闭动作)，直到业务程序从连接池拿到 一个自认为空闲可用的连接再次读数据时，则会报invalid connection错误(unexpected EOF)

为了解决这个问题，当业务程序从连接池拿一个空闲的连接时，应先进行健康检查，看看这个连接 是否是有效连接，有效则返回给用户，无效则进行一次重试。

何时对连接进行健康检查呢? 在go1.10版本引入了一个sql/driver接口：driver.SessionResetter，它的方法列表只有一个， 为ResetSession，该方法会在client把使用完的连接connection放入连接池时触发，而工单934 的解决方案就是利用这个方法处理connection健康检查。

前提： 使用一个reset标识位作为connection的一个参数值(初始值为false)

mysqlConn.writePacket方法的逻辑:

1. 如何创建一个新连接

// database/sql/sql.go
Open(driverName, dataSourceName string)
  OpenDB(c driver.Connector)
    go db.connectionOpener(ctx) // 它暂时不会创建，类似懒加载，它要等到真正的CRUD时，才会通过channel方法来创建一个connection
    go db.connectionResetter(ctx)

func (db *DB) connectionResetter(ctx context.Context) {
	for {
		select {
		case <-ctx.Done():
			...
		case dc := <-db.resetterCh():
			// 当连接被放入连接池后，就会通过channel resetterCh投递，
			// 进行resetSession调用
			dc.resetSession(ctx)
		}
	}
}
func (dc *driverConn) resetSession(ctx context.Context) {
	defer dc.Unlock() // In case of panic.
	if dc.closed { // Check if the database has been closed
		return
	}
	// 这里dc.ci就是go-sql-driver库中实例的connection，然后执行该go-sql-driver库
	// 实现driver.SessionResetter接口
	dc.lastErr = dc.ci.(driver.SessionResetter).ResetSession(ctx)
}

// go-sql-driver/mysql/connection.go
func (mc *mysqlConn) ResetSession(ctx context.Context) error {
	if mc.closed.IsSet() {
		return driver.ErrBadConn
	}
	mc.reset = true
	return nil
}

2. 如何从连接池中获取一个连接

exec(ctx context.Context, query string, args []interface{}, strategy connReuseStrategy)
query(ctx context.Context, query string, args []interface{}, strategy connReuseStrategy)
        dc, err := db.conn(ctx, strategy)
		db.putConn(ret.conn, ret.err, false)

// 当mysql client connection使用完后，且当前连接池中的idle连接数小于`max_idle`值，则
// 表示该连接可以放入到连接池中
// 当dc连接投递到channel resetterCh，会触发前面Open启动的connectionResetter消费者等待操作，
// 最后通过resetSession将connection连接上的reset标志位置为true。
func (db *DB) putConn(dc *driverConn, err error, reseetSession bool) {
	...
	select {
	default:
		// If the resetterCh is blocking then mark the connection
		// as bad and continue on.
		dc.lastErr = driver.ErrBadConn
		dc.Unlock()
	cas db.resetterCh <- dc:
	}
}

3. 如何给mysql server发送sql的: 在database/sql/driver.go的所有接口，最终都会通过实现类go-sql-driver的对应方法，比如 Query、QueryContext、Exec、ExecContext等等所有实现类调用，而这些方法接口和处理的参数 最终都会通过connection的writePacket方法发送到网络上。在把包发到网络上之前，会做两个 拦截操作： 1> 包大小校验，如果超过限制，直接报发送包超过限制错误； 2> 通过该连接发往mysql server之前，还要对该connection进行探活检测，校验该连接是否有效； a. 任何一个连接最初都是来自连接创建，那么连接池中的连接，肯定是经过putConn操作后 放入的，那么连接池中的所有连接的reset标志位最初肯定都是true b. 主动创建的connection reset标志位默认是false的

// 所有mysql client通过connection发往mysql server的网络包，都会经过底层的writePacket方法处理后发送到网络上。
// 注意：connection探活是针对连接池中长时间空闲的连接进行一次探活，同时另一个角度，client和server在发送网络包时，如果期间执行connection探活，有可能会读取到有效数据，并覆盖client和server之间发送的数据，导致数据失效不能正常解析，影响业务


// go-sql-driver/mysql/packets.go
func (mc *mysqlConn) writePacket(data []byte) error

8. count(*)这么慢，我该怎么办

1. count(*)的实现方式

• MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高。
  • 如果加了where条件的话，MyISAM表也是不能很快返回的。
• InnoDB引擎执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
  • 所以，当记录数越来越多的时候，计算一个表的总行数会越来越慢。

2. 解决方案：

• 用缓存系统保存计数 你可以用一个 Redis 服务来保存这个表的总行数。 这个表每被插入一行 Redis 计数就加 1，每被删除一行 Redis 计数就减 1。 这种方式下，读和更新操作都很快，但你再想一下这种方式存在什么问题吗？

没错，缓存系统可能会丢失更新。

Redis 的数据不能永久地留在内存里，所以你会找一个地方把这个值定期地持久化存储起来。 但即使这样，仍然可能丢失更新。试想如果刚刚在数据表中插入了一行，Redis 中保存的值也加了 1， 然后 Redis 异常重启了，重启后你要从存储 redis 数据的地方把这个值读回来， 而刚刚加 1 的这个计数操作却丢失了。

当然了，这还是有解的。比如，Redis 异常重启以后，到数据库里面单独执行一次count(*) 获取真实的行数，再把这个值写回到 Redis 里就可以了。异常重启毕竟不是经常出现的情况， 这一次全表扫描的成本，还是可以接受的。

但实际上，将计数保存在缓存系统中的方式，还不只是丢失更新的问题。 即使 Redis 正常工作，这个值还是逻辑上不精确的。

情况1：
|时刻|会话A         |会话B            |
+----+--------------+-----------------+
|T1  |              |                 |
+----+--------------+-----------------+
|T2  |插入一行数据R;|                 |
+----+--------------+-----------------+
|T3  |              |读Redis计数;     |
|    |              |查询最近100条记录|
+----+--------------+-----------------+
|T4  |Redis计数加1  |                 |
+----+--------------+-----------------+

情况2：
|时刻|会话A         |会话B            |
+----+--------------+-----------------+
|T1  |              |                 |
+----+--------------+-----------------+
|T2  |Redis计数加1  |                 |
+----+--------------+-----------------+
|T3  |              |读Redis计数;     |
|    |              |查询最近100条记录|
+----+--------------+-----------------+
|T4  |插入一行数据R;|                 |
+----+--------------+-----------------+

• 在数据库保存计数 把这个计数直接放到数据里单独的一张计数表C中

|时刻|会话A         |会话B             |
+----+--------------+------------------+
|T1  |              |                  |
+----+--------------+------------------+
|T2  |begin;        |                  |
|    |表C中计数值加1|                  |
+----+--------------+------------------+
|T3  |              |begin;            |
|    |              |读表C计数值       |
|    |              |查询最近100条记录;|
|    |              |commit;           |
+----+--------------+------------------+
|T4  |插入一行数据R;|                  |
|    |commit;       |                  |
+----+--------------+------------------+

3. 不同count()的语义 count() 是一个聚合函数，对于返回的结果集，一行行地判断， 如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

所以，count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数； 而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

但是count(*)是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不是 null，按行累加。

InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。 所以，普通索引树比主键索引树小很多。对于count(*)这样的操作，遍历哪个索引树得到的 结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。

所以结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用count(*)。

9. 更新语句没有生效的问题

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
mysql> insert into t(id, c) values(1,1),(2,2),(3,3),(4,4);
mysql> set global transaction isolation level REPEATABLE READ;

场景一：
+----------------------------+-------------------+
|session A                   |session B          |
+----------------------------+-------------------+
|begin;                      |                   |
|select * from t;            |                   |
+----------------------------+-------------------+
|                            |update t set c=c+1;|
+----------------------------+-------------------+
|update t set c=0 where id=c;|                   |
|select * from t;            |                   |
+----------------------------+-------------------+

场景二:
+----------------------------+-------------------+
|session A                   |session B'         |
+----------------------------+-------------------+
|                            |begin;             |
|                            |select * from t;   |
+----------------------------+-------------------+
|begin;                      |                   |
|select * from t;            |                   |
+----------------------------+-------------------+
|                            |update t set c=c+1;|
|                            |commit;            |
+----------------------------+-------------------+
|update t set c=0 where id=c;|                   |
|select * from t;            |                   |
+----------------------------+-------------------+

场景二属于3-a，场景一属于2
而update语句会使用当前读，所以才会出现上面更新没反应的现象

10. 为什么我只查一行的语句，也执行这么慢？

• 正在疯狂刷缓存：使用show processlist命令来探测
• 被锁住了：找到谁持有写锁，然后把它kill掉。
  • 将performance_schema的值设置为on
  • 之后在sys库下，就可以查询相应的表获得对应的数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

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. 等MDL锁

+-------------------+----------------------------+
|session A          |session B                   |
+-------------------+----------------------------+
|lock table t write;|                            |
+-------------------+----------------------------+
|                   | select * from t where id=1;|
+-------------------+----------------------------+

mysql> show processlist;
+----+-----------------+------------------+------+---------+--------+---------------------------------+------------------------------+
| Id | User            | Host             | db   | Command | Time   | State                           | Info                         |
+----+-----------------+------------------+------+---------+--------+---------------------------------+------------------------------+
|  5 | event_scheduler | localhost        | NULL | Daemon  | 262838 | Waiting on empty queue          | NULL                         |
| 26 | root            | localhost        | test | Query   |      0 | init                            | show processlist             |
| 27 | root            | 172.17.0.1:38616 | test | Query   |      7 | Waiting for table metadata lock | select * from t where id = 1 |
+----+-----------------+------------------+------+---------+--------+---------------------------------+------------------------------+
3 rows in set (0.00 sec)

mysql> select blocking_pid from sys.schema_table_lock_waits;
+--------------+
| blocking_pid |
+--------------+
|           26 |
+--------------+
1 row in set (0.00 sec)

mysql> kill 26;

2. 等flush

+-----------------------+---------------+---------------------------+
|session A              |session B      |                           |
+-----------------------+---------------+---------------------------+
|select sleep(1) from t;|               |                           |
+-----------------------+---------------+---------------------------+
|                       |flush tables t;|                           |
+-----------------------+---------------+---------------------------+
|                       |               |select * from t where id=1;|
+-----------------------+---------------+---------------------------+

 show processlist;
+----+-----------------+------------------+------+---------+--------+-------------------------+----------------------------+
| Id | User            | Host             | db   | Command | Time   | State                   | Info                       |
+----+-----------------+------------------+------+---------+--------+-------------------------+----------------------------+
|  5 | event_scheduler | localhost        | NULL | Daemon  | 263925 | Waiting on empty queue  | NULL                       |
| 30 | root            | 172.17.0.1:38838 | test | Query   |     43 | Waiting for table flush | flush tables t             |
| 31 | root            | localhost        | test | Query   |     45 | User sleep              | select sleep(1) from t     |
| 37 | root            | 172.17.0.1:38972 | test | Query   |     39 | Waiting for table flush | select * from t where id=1 |
| 38 | root            | 172.17.0.1:38986 | NULL | Query   |      0 | init                    | show processlist           |
+----+-----------------+------------------+------+---------+--------+-------------------------+----------------------------+

在session A中，我故意每行都调用一次sleep(1)，这样这个语句默认要执行10万秒，在这期间表t一直是被 session A“打开”着。然后，session B的flush tables t命令再要去关闭表t，就需要等session A的查询 结束。这样，session C要再次查询的话，就会被flush命令堵住了。

3. 等行锁

+------------------------------+----------------------------------------------+
|session A                     |session B                                     |
+------------------------------+----------------------------------------------+
|begin;                        |                                              |
|update t set c=c+1 where id=1;|                                              |
+------------------------------+----------------------------------------------+
|                              |select * from t where id=1 lock in share mode;|
+------------------------------+----------------------------------------------+

mysql> show processlist;
+----+-----------------+------------------+------+---------+--------+------------------------+-----------------------------------------------+
| Id | User            | Host             | db   | Command | Time   | State                  | Info                                          |
+----+-----------------+------------------+------+---------+--------+------------------------+-----------------------------------------------+
| 30 | root            | 172.17.0.1:38838 | test | Query   |     10 | statistics             | select * from t where id=1 lock in share mode |
| 31 | root            | localhost        | test | Sleep   |     16 |                        | NULL                                          |
| 37 | root            | 172.17.0.1:38972 | test | Query   |      0 | init                   | show processlist                              |
+----+-----------------+------------------+------+---------+--------+------------------------+-----------------------------------------------+

显然，session A开启了事务，占有写锁，还没提交，而session B此时是当前读，所以被堵住了。 对于行锁，就查innodb_lock_waits表来确定谁占着这个写锁。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

mysql> select * from sys.innodb_lock_waits where locked_table='`test`.`t`'\G
 select * from sys.innodb_lock_waits where locked_table='`test`.`t`'\G
*************************** 1. row ***************************
                wait_started: 2021-04-26 02:54:22
                    wait_age: 00:00:03
               wait_age_secs: 3
                locked_table: `test`.`t`
         locked_table_schema: test
           locked_table_name: t
      locked_table_partition: NULL
   locked_table_subpartition: NULL
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 421484077610056
         waiting_trx_started: 2021-04-26 02:54:22
             waiting_trx_age: 00:00:03
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 44
               waiting_query: select * from t where id=1 lock in share mode
             waiting_lock_id: 140009100899400:7:5:2:140009021722304
           waiting_lock_mode: S,REC_NOT_GAP
             blocking_trx_id: 403839
                blocking_pid: 31

可以看到，31号线程是造成阻塞的罪魁祸首。而干掉这个罪魁祸首的方式，就是kill query 31或kill 31。

不过，这里不应该显示"kill query 31"。这个命令表示停止31号线程当前正在执行的语句，而这个方法其实 是没有用的。因为占有行锁的是update语句，这个语句已经是之前执行完成的了，现在执行kill query，无法 让这个事务去掉id=1上的行锁。

实际上，kill 31才有效，也就是说直接断开这个连接。这里隐含的一个逻辑就是，连接被断开的时候，会 自动回滚这个连接里面正在执行的线程，也就释放了id=1上的行锁。

11. 为什么还有kill不掉的语句？

在 MySQL 中有两个 kill 命令：一个是 kill query + 线程 id，表示终止这个线程中 正在执行的语句；一个是 kill connection + 线程 id，这里 connection 可缺省， 表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的。

不知道你在使用 MySQL 的时候，有没有遇到过这样的现象：使用了 kill 命令，却没能 断开这个连接。再执行 show processlist 命令，看到这条语句的 Command 列显示的是 Killed。

你一定会奇怪，显示为 Killed 是什么意思，不是应该直接在 show processlist 的结果里看不到这个线程了吗？

1. 收到 kill 以后，线程做什么？ 但是，这里你要停下来想一下：session B 是直接终止掉线程，什么都不管就直接退出吗？ 显然，这是不行的。session B 虽然处于 blocked 状态，但还是拿着一个 MDL 读锁的。 如果线程被 kill 的时候，就直接终止，那之后这个 MDL 读锁就没机会被释放了。

实现上，当用户执行kill query thread_id_B时，MySQL 里处理 kill 命令的线程做了两件事：

• 把 session B 的运行状态改成THD::KILL_QUERY (将变量 killed 赋值为THD::KILL_QUERY)；
• 给 session B 的执行线程发一个信号。 为什么要发信号呢: 让 session B 退出等待，来处理这个THD::KILL_QUERY状态。

上面的分析中，隐含了这么三层意思：

• 一个语句执行过程中有多处“埋点”，在这些“埋点”的地方判断线程状态， 如果发现线程状态是THD::KILL_QUERY，才开始进入语句终止逻辑；
• 如果处于等待状态，必须是一个可以被唤醒的等待，否则根本不会执行到“埋点”处；
• 语句从开始进入终止逻辑，到终止逻辑完全完成，是有一个过程的。

2. 线程没有执行到判断线程状态的逻辑，导致kill不掉连接 因innodb_thread_concurrency不够导致的kill不掉的例子：

# 将InnoDB的并发线程上限数设置为2
mysql> set global innodb_thread_concurrency=2;
# 执行下面的序列
+-----------------+-----------------+----------------+-------------+--------+
|session A        |session B        |session C       |session D    |sessionE|
+-----------------+-----------------+----------------+-------------+--------+
|select sleep(100)|select sleep(100)|                |             |        |
|from t;          |from t;          |                |             |        |
+-----------------+-----------------+----------------+-------------+--------+
|                 |                 |select * from t;|             |        |
|                 |                 |(blocked)       |             |        |
+-----------------+-----------------+----------------+-------------+--------+
|                 |                 |                |kill query C;|        |
+-----------------+-----------------+----------------+-------------+--------+
|                 |                 |ERROR 2013      |             |kill C; |
|                 |                 |(HY000):Lost    |             |        |
|                 |                 |connection to   |             |        |
|                 |                 |MySQL server    |             |        |
|                 |                 |during query    |             |        |
+-----------------+-----------------+----------------+-------------+--------+

可以看到：

• sesssion C 执行的时候被堵住了；
• 但是 session D 执行的 kill query C 命令却没什么效果，
• 直到 session E 执行了 kill connection 命令，才断开了 session C 的连接， 提示“Lost connection to MySQL server during query”，
• 但是这时候，如果在 session E 中执行 show processlist，你就能看到下面这个图。

1
2
3
4
5
6
7
8
9

mysql> show processlist;
+--+----+---------------+----+-------+----+------------+------------------------+
|Id|User|Host           |db  |Command|Time|State       |Info                    |
+--+----+---------------+----+-------+----+------------+------------------------+
| 4|root|localhost:50934|test|Query  |  30|User sleep  |select sleep(100) from t|
| 5|root|localhost:50956|test|Query  |  26|User sleep  |select sleep(100) from t|
|12|root|localhost:53288|test|Killed |  24|Sending data|select * from t         |
|13|root|localhost:53316|test|Query  |   0|starting    |show processlist        |
+--+----+---------------+----+-------+----+------------+------------------------+

这时候，id=12 这个线程的 Commnad 列显示的是 Killed。也就是说，客户端虽然断开了连接， 但实际上服务端上这条语句还在执行过程中。

为什么在执行 kill query 命令时，并没有第一时间退出呢？

在实现上，等行锁时，使用的是pthread_cond_timedwait函数，这个等待状态可以被唤醒。 但是，在这个例子里，12 号线程的等待逻辑是这样的：每 10 毫秒判断一下是否可以进入 InnoDB 执行，如果不行，就调用 nanosleep 函数进入 sleep 状态。

也就是说，虽然 12 号线程的状态已经被设置成了KILL_QUERY，但是在这个等待进入 InnoDB 的循环过程中，并没有去判断线程的状态，因此根本不会进入终止逻辑阶段。

而当 session E 执行 kill connection 命令时，是这么做的，

• 把 12 号线程状态设置为KILL_CONNECTION；
• 关掉 12 号线程的网络连接。因为有这个操作，所以你会看到， 这时候 session C 收到了断开连接的提示。

那为什么执行 show processlist 的时候，会看到 Command 列显示为 killed 呢？ 其实，这就是因为在执行 show processlist 的时候，有一个特别的逻辑： 如果一个线程的状态是KILL_CONNECTION，就把Command列显示成Killed。

所以其实，即使是客户端退出了，这个线程的状态仍然是在等待中。 那这个线程什么时候会退出呢？

只有等到满足进入 InnoDB 的条件后，session C 的查询语句继续执行，然后才有可能
判断到线程状态已经变成了 KILL_QUERY 或者 KILL_CONNECTION，再进入终止逻辑阶段。

3. 终止逻辑耗时较长，导致kill不掉连接 这时候，从 show processlist 结果上看也是 Command=Killed，需要等到终止逻辑完成，语句才算真正完成。这类情况，比较常见的场景有以下几种：

• 超大事务执行期间被 kill。这时候，回滚操作需要对事务执行期间 生成的所有新数据版本做回收操作，耗时很长。
• 大查询回滚。如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大， 删除临时文件可能需要等待 IO 资源，导致耗时较长。
• DDL 命令执行到最后阶段，如果被 kill，需要删除中间过程的临时文件， 也可能受 IO 资源影响耗时较久。

12. 为什么临时表可以重名？

+---------------------------------------------+-------------------------+
|session A                                    |session B                |
+---------------------------------------------+-------------------------+
|create temporary table t(c int)engine=myisam;|                         |
+---------------------------------------------+-------------------------+
|                                             |show create table t;     |
|                                             |(Table 't' doesn't exist)|
+---------------------------------------------+-------------------------+
|create table t(id int primary                |                         |
|key)engine=innodb;                           |                         |
|show create table t;                         |                         |
|//create temporary table t(c                 |                         |
|int)engine=myisam;                           |                         |
|show tables;                                 |                         |
|//只显示普通表                               |                         |
+---------------------------------------------+-------------------------+
|                                             |insert into t values(1); |
|                                             |select * from t;         |
|                                             |//返回1                  |
+---------------------------------------------+-------------------------+
|select * from t;                             |                         |
|//Empty set                                  |                         |
+---------------------------------------------+-------------------------+

1. 临时表的特性：

• 建表语句是create temporary table …。
• 一个临时表只能被创建它的 session 访问，对其他线程不可见。所以，图中 session A 创建的临时表 t，对于 session B 就是不可见的。
• 临时表可以与普通表同名。
• session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。
• show tables 命令不显示临时表。

2. 为什么临时表可以重名： 我们在执行:

1

create temporary table temp_t(id int primary key)engine=innodb;

这个语句的时候，MySQL 要给这个 InnoDB 表创建一个 frm 文件保存表结构定义，还要有地方保存表数据。

这个 frm 文件放在临时文件目录下，文件名的后缀是.frm，前缀是#sql{进程 id}_{线程 id}_ 序列号。你可以使用 select @@tmpdir 命令，来显示实例的临时文件目录。

而关于表中数据的存放方式，在不同的 MySQL 版本中有着不同的处理方式：

• 在 5.6 以及之前的版本里，MySQL 会在临时文件目录下创建一个相同前缀、以.ibd 为后缀的文件，用来存放数据文件；

• 而从 5.7 版本开始，MySQL 引入了一个临时文件表空间，专门用来存放临时文件的数据。因此，我们就不需要再创建 ibd 文件了。

+-----------------------------+-----------------------------+
|session A                    |session B                    |
+-----------------------------+-----------------------------+
|create temporary table t1 ...|                             |
|// #sql4d2_4_0.frm           |                             |
|create temporary table t2 ...|                             |
|// #sql4d2_4_1.frm           |                             |
+-----------------------------+-----------------------------+
|                             |create temporary table t1 ...|
|                             |// #sql4d2_5_0.frm           |
+-----------------------------+-----------------------------+

这个进程的进程号是 1234(16进制是4d2)，session A 的线程 id 是 4，session B 的线程 id 是 5。所以你看到了，session A 和 session B 创建的临时表，在磁盘上的文件不会重名。

MySQL 维护数据表，除了物理上要有文件外，内存里面也有一套机制区别不同的表，每个表都对应一个table_def_key。

• 一个普通表的table_def_key的值是由“库名 + 表名”得到的，所以如果你要在同一个库下创建两个同名的普通表，创建第二个表的过程中就会发现table_def_key已经存在了。

• 而对于临时表，table_def_key在“库名 + 表名”基础上，又加入了server_id+thread_id。

在实现上，每个线程都维护了自己的临时表链表。这样每次session内操作表的时候，先遍历 链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；在 session结束的时候，对链表里的每个临时表，执行“DROP TEMPORARY TABLE + 表名”操作。

这时候你会发现，binlog中也记录了DROP TEMPORARY TABLE这条命令。你一定会觉得奇怪，临时表只在线程内自己可以访问，为什么需要写到 binlog 里面？

这，就需要说到主备复制了。

3. 临时表和主备复制 假设，在主库上执行下面这个语句序列：

1
2
3
4

create table t_normal(id int primary key, c int)engine=innodb;/*Q1*/
create temporary table temp_t like t_normal;/*Q2*/
insert into temp_t values(1,1);/*Q3*/
insert into t_normal select * from temp_t;/*Q4*/

如果关于临时表的操作都不记录，那么在备库就只有create table t_normal表和insert into t_normal select * from temp_t这两个语句的 binlog 日志，备库在执行到insert into t_normal的时候，就会报错表 temp_t 不存在。

你可能会说，如果把 binlog 设置为 row 格式就好了吧？因为 binlog 是 row 格式时，在记录insert into t_normal的 binlog 时，记录的是这个操作的数据，即：write_row event里面记录的逻辑是“插入一行数据（1,1)”。

确实是这样。如果当前的binlog_format=row，那么跟临时表有关的语句，就不会记录到 binlog 里。也就是说，只在binlog_format=statment/mixed的时候，binlog 中才会记录临时表的操作。

这种情况下，创建临时表的语句会传到备库执行，因此备库的同步线程就会创建这个临时表。主库在线程退出的时候，会自动删除临时表，但是备库同步线程是持续在运行的。所以，这时候我们就需要在主库上再写一个 DROP TEMPORARY TABLE 传给备库执行。

4. 主库上不同的线程创建同名的临时表是没关系的，但是传到备库执行是怎么处理的呢？

+--+----------------+----------------+-----------------+
|  |M上session A    |M上session B    |S上的应用日志线程|
+--+----------------+----------------+-----------------+
|T1|create temporary|                |                 |
|  |table t1 ...;   |                |                 |
+--+----------------+----------------+-----------------+
|T2|                |                |create temporary |
|  |                |                |table t1 ...;    |
+--+----------------+----------------+-----------------+
|T3|                |create temporary|                 |
|  |                |table t1 ...;   |                 |
+--+----------------+----------------------------------+
|T4|                |                |create temporary |
|  |                |                |table t1 ...;    |
+--+----------------+----------------------------------+

主库M上的两个session创建了同名的临时表t1，这两个create temporary table t1语句都会被传到备库S上。

但是，备库的应用日志线程是公用的，也就是说要在应用线程里面先后执行这个create语句两次。（即使开了多线程复制，也可能被分配到从库的同一个worker中执行）。那么，这会不会导致同步线程报错？

显然是不会的，否则临时表就是一个bug了。也就是说，备库线程在执行的时候，要把这两个t1表当做两个不同的临时表来处理。这，又是怎么实现的呢？

MySQL在记录binlog的时候，会把主库执行这个语句的线程id写到binlog中。这样，在备库的应用线程就能够知道执行每个语句的主库线程id，并利用这个线程id来构造临时表的table_def_key:

• session A的临时表t1，在备库的table_def_key就是: 库名 + t1 + M的serverid + sessionA的thread_id；

• session B的临时表t2，在备库的table_def_key就是：库名 + t1 + M的serverid + sessionB的thread_id；

由于table_def_key不同，所以这两个表在备库的应用线程里面是不会冲突的。

5. 问题

1
2
3
4
5
6
7

mysql> create temporary table temp_t(id int primary key)engine=innodb;
mysql> alter table temp_t rename to temp_t2;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warning: 0

mysql> rename table temp_t2 to temp_t3;
ERROR 1017 (HY000): Can't find file: './test/temp_t2.frm' (errno: 2 - No such file or directory)

可以看到，我们可以使用alter table语法修改临时表的表名，而不能使用rename语法。你知道这是什么原因吗？

在实现上，执行rename table语句的时候，要求按照“库名/表名.frm”的规则去磁盘找文件，但是
临时表在磁盘上的frm文件是放在tmpdir目录下的，并且文件名的规则是“#sql{进程id}_{线程id}_序列号.frm”，因此会报"找不到文件名"的错误。

上一篇 « LSM-Tree 下一篇 » 全文索引