转载

MySQL 内核月报

MySQL · 答疑释惑· 并发Replace into导致的死锁分析

测试版本： MySQL5.6.23

测试表：

create table t1 (a int auto_increment primary key, b int, c int, unique key (b));并发执行SQL： replace into t1(b,c) values (2,3)  //使用脚本，超过3个会话

背景

Replace into操作可以算是比较常用的操作类型之一，当我们不确定即将插入的记录是否存在唯一性冲突时，可以通过Replace into的方式让MySQL自动处理：当存在冲突时，会把旧记录替换成新的记录。

我们先来理一下一条简单的replace into操作（如上例所示）的主要流程包括哪些。

Step 1. 正常的插入逻辑

首先插入聚集索引记录，在上例中a列为自增列，由于未显式指定自增值，每次Insert前都会生成一个不冲突的新值。

随后插入二级索引b，由于其是唯一索引，在检查duplicate key时，为其加上类型为LOCK_X的记录锁。

Tips：对于普通的INSERT操作，当需要检查duplicate key时，加LOCK_S锁，而对于Replace into 或者 INSERT..ON DUPLICATE操作，则加LOCK_X记录锁。

当UK记录已经存在时，返回错误DB_DUPLICATE_KEY。

Step 2. 处理错误

由于检测到duplicate key，因此第一步插入的聚集索引记录需要被回滚掉（row_undo_ins）。

Step 3. 转换操作

从InnoDB层失败返回到Server层后，收到duplicate key错误，首先检索唯一键冲突的索引，并对冲突的索引记录（及聚集索引记录）加锁。

随后确认转换模式以解决冲突：

如果发生uk冲突的索引是最后一个唯一索引、没有外键引用、且不存在delete trigger时，使用UPDATE ROW的方式来解决冲突；
否则，使用DELETE ROW + INSERT ROW的方式解决冲突。

Step 4. 更新记录

对于聚集索引，由于PK列发生变化，采用delete + insert 聚集索引记录的方式更新。

对于二级uk索引，同样采用标记删除 + 插入的方式。

我们知道，在尝试插入一条记录时，如果插入位置的下一条记录上存在记录锁，那么在插入时，当前session需要对其加插入意向锁，具体类型为LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION。这也是导致死锁的关键点之一。

是否能保证自增列的有序性?

默认情况下，参数innodb_autoinc_lock_mode的值为1，因此只在分配自增列时互斥（如果我们将其设为0的话，就会产生AUTO_INC类型的表级锁）。当分配完自增列值后，我们并不知道并发的replace into的顺序。

死锁分析

回到死锁线程分析，从死锁日志我们大致可以推断出如下序列（本例中死锁的heap no为5）：

Session 1 执行到Step4, 准备更新二级Uk索引，因此持有uk上heap no 为5的X 行锁和PK上的X行锁；
Session 2 检查到uk冲突，需要加X行锁；
Session 1 在标记删除记录后，尝试插入新的uk记录，发现预插入点的下一条记录(heap no =5) 上有锁请求，因此尝试加插入意向X锁，产生锁升级，死锁路径：Session1 => Session 2 => Session1。

到这里其实问题已经很明显了，我们考虑如下场景：假设当前表内数据为：

root@sb1 08:57:41>select * from t1; +---------+------+------+ | a | b | c | +---------+------+------+ | 2100612 | 2 | 3 | +---------+------+------+ 1 row in set (0.00 sec)

由于不能保证自增列被更新的有序性，我们假定有三个并发的会话，并假定表上只有一条记录。

session 1获得自增列值为2100619， session 2 获得的自增列值为2100614， session 3获得的自增列值为2100616。

Session 1: replace into t1 values (2100619, 2, 3); // uk索引上记录(2, 2100612)被标记删除，同时插入新记录(2, 2100619)

Purge线程启动，(2, 2100612)被物理删除，Page上只剩下唯一的物理记录(2, 2100619)。

Session 2: replace into t1 values (2100614, 2, 3);

这里我们使用gdb的non-stop模式，使其断在row_update_for_mysql函数(insert尝试失败后，会转换成update)，此时session2持有(2, 2100619) 的X锁。

Tips：我们可以通过如下命令使用gdb的non-stop模式： 1. 以gdb启动mysqld 2. 设置：      set target-async 1      set pagination off      set non-stop on 3. 设置函数断点，然后run

Session 3: replace into t1 values (2100616, 2, 3); // 检测到uk有冲突键，需要获取记录(2, 2100619) 的X锁，等待session 2。

Session 2:

a)标记删除记录(2, 2100619)，同时插入新记录(2, 2100614)；
b) (2, 2100614) 比(2, 2100619) 要小，因此定位到该记录之前，也就是系统记录infimum；
c)infimum记录的下一条记录(2, 2100619)上有锁等待，需要升级成插入意向X锁，导致死锁发生。

如果Purge线程一直停止，会发生什么呢？

我们随便建一个表，然后执行FLUSH TABLE tbname FOR EXPORT来让purge线程停止。

假设当前表上数据为：

root@sb1 10:26:05>select * from t1; +---------+------+------+ | a | b | c | +---------+------+------+ | 2100710 | 2 | 3 | +---------+------+------+ 1 row in set (0.00 sec)

Session 1：replace into t1 values (2100720, 2, 3);

此时Page上存在记录(infimum), (2, 2100710), (2, 2100720), (supremum)。

Session 2：replace into t1 values (2100715, 2, 3);

同上例，使用gdb断到函数row_update_for_mysql。由于没有启动purge线程，因此老的被标记删除的记录还存在于page内，在扫描二级索引重复键时，也会依次给这些老记录加锁，因此session 2会持有 (2, 2100710)和 (2, 2100720)的X锁。

Session 3：replace into t1 values (2100718, 2, 3); // 被session2阻塞，等待(2,2100710)的X锁

Session 2：在标记删除二级索引记录，并进行插入时，选择的插入位置为 (2, 2100710), (2,2100720)之间，插入点的下一条记录(2,2100720)上没有其他线程锁等待，当前session锁升级成功；

完成插入后，page上的记录分布为(infimum), (2, 2100710), (2, 2100715), (2, 2100720), (supremum)。

Session 3：完成插入，最终page内的记录为(infimum), (2, 2100710), (2, 2100715), (2, 2100718), (2, 2100720), (supremum)。其中只有用户记录(2, 2100718)未被标记删除。

MySQL · 性能优化· 5.7.6 InnoDB page flush 优化

在上期的月报中，我们已经详细介绍了Oracle MySQL以及社区分支最新的对InnoDB page flush的优化。在最近release的5.7.6版本中又有了进一步的改进。主要包括以下几点修改

修改一、更精确的loop时间

Page cleaner每做srv_flushing_avg_loops次flush后，会去计算刷脏和Redo LSN增长的速度。由于每次Page cleaner的工作量是自适应的，一次flush操作的时间可能超过1秒。

在新版本中，统一采用当前时间和上次更新速率的时间差来确认是否需要重新计算速率。因此参数innodb_flushing_avg_loops的行为实际上等同于每这么多秒后重计算速率。

修改二、根据buffer pool实例的脏页分布来决定刷脏

从5.7版本开始支持配置多个page cleaner线程以实现并行刷脏。在5.7.6之前的版本，Page cleaner协调线程根据当前的负载情况，会计算出预计需要flush的总page数和目标LSN，然后在多个bp instance间做个均分。

但是考虑一种场景：如果bp实例间的负载不平衡，某个实例在目标LSN之前的脏页很多，而有些实例很少，那么本应该多做刷脏动作的bp就可能产生堆积。我们之前在 webscalesql google公开讨论组有过类似的讨论，感兴趣的可以看看。

回到正题上来，在5.7.6版本中，计算目标page数的方法大概如下：

根据当前脏页占比和Redo LSN增长状态，计算利用IO Capacity的百分比（pct_total）

计算目标LSN:

target_lsn = oldest_lsn + lsn_avg_rate * buf_flush_lsn_scan_factor

其中oldest_lsn表示当前buffer pool中最老page的LSN，lsn_avg_rate表示每秒LSN推进的平均速率，buf_flush_lsn_scan_factor目前是hardcode的，值为3。

统计每个buffer pool 小于target_lsn的page数pages_for_lsn

初步估定每个bp instance 的n_pages_requested= pages_for_lsn /buf_flush_lsn_scan_factor。每个bp的pages_for_lsn被累加到sum_pages_for_lsn

同时根据io capacity估算总的需要flush的Page数量：

sum_pages_for_lsn /= buf_flush_lsn_scan_factor; n_pages = (PCT_IO(pct_total) + avg_page_rate + sum_pages_for_lsn) / 3;

n_pages若超过innodb_io_capacity_max，则设置为innodb_io_capacity_max

轮询每个Buffer pool 实例：

如果当前有足够的Redo 空间：n_pages_requested  = n_pages / srv_buf_pool_instances 否则：n_pages_requested = n_pages_requested  * n_pages / sum_pages_for_lsn

也就是说，在Redo 空间足够时，依然采用均衡的刷脏逻辑。

在早期版本中，会根据两个条件来判断每个bp刷脏的进度：目标LSN及page数。而到了5.7.6版本里，大多数情况下只根据更加准确的请求刷page数来进行判定（系统空闲时进行100% io capactiy的page flush、崩溃恢复时、以及实例shutdown时的刷脏除外）

虽然计算公式比较清晰，但有些factor的定值依然让人很困惑，也许是官方测试的比较理想的配置。不过最好还是设置成可配置的，由有经验的用户根据自己具体的负载情况来进行定制。

修改三、用户线程在检查Redo 空间时不参与刷脏

在之前版本中，当未做checkpoint的日志量过多时，用户线程会进行batch flush操作，将每个buffer pool instance的LSN推进到某个指定值。如果某个bp instance已经有别的线程在flush，则跳过尝试下一个instance，同时认为这次的flush操作是失败的，会返回重试。

当用户线程参与到刷脏时，通常会认为这是个性能拐点，TPS会出现急剧下降，大量线程陷入condtion wait 和并发flush。因此在5.7.6里，当用户线程需要推进LSN时，不再主动发起刷脏，这些工作会留给page cleaner线程来作。用户线程只去轮询每个bp instance，直到所有的bp instance 的LSN超过其目标LSN，每次轮询默认sleep重试时间为10000微妙

事实上, Percona Server早在5.6版本里已经使用相同的策略了。

修改四、为page cleaner线程设置更高的优先级

在Linux平台下，对于page cleaner的协调线程和worker线程，其CPU优先级被设置为-20，即最高优先级，通过函数set_priority设置。目前还不支持参数配置。

修改五、防止checkpoint LSN被覆盖

在之前的版本中，尽管每次在写Redo时都会去检查日志文件是否容留了足够百分比的可用空间，但实际上并没有考虑即将写入的Redo log长度。如果我们操作一些极大的记录并产生很长的Redo log记录，这可能导致检查点LSN被覆盖掉，如果这时候crash就会无法安全的做崩溃恢复。

在新的逻辑里，在检测到当前写入的Redo 可能造成覆盖上次的checkpoint点时，就会进入sleep，等待page cleaner线程刷脏，然后再做一次Redo log checkpoint。如此循环直到checkpoint的LSN推进到安全的位置。

参考： worklog：wl#7868 ，及补丁

MySQL · 捉虫动态· pid file丢失问题分析

现象

mysql5.5,通过命令show variables like '%pid_file%'; 可以查到pid文件位置，例如/home/mysql/xx.pid。但发现在此目录下找不到此pid文件。

背景知识

mysql pid文件记录的是当前mysqld进程的pid。

通过mysqld_safe启动mysqld时，mysqld_safe会检查PID文件，未指定PID文件时，pid文件默认名为$DATADIR/`hostname`.pid

pid文件不存在，不做处理
文件存在，且pid已占用则报错"A mysqld process already exists"；文件存在，但pid未占用，则删除pid文件。

mysqld启动后会通过create_pid_file函数新建pid文件，通过getpid()获取当前进程pid并将pid写入pid文件。

因此，通过mysqld_safe启动时，pid文件的作用是为了防止同一个数据库被启动多次（数据文件是同一份，但端口不同的情况）。

另一个事实是mysqld在正常关闭时或通过SIGQUIT,SIGKILL,SIGTERM信号来kill mysqld时，会调用clean_up函数将pid文件删除。而mysqld异常crash时，pid文件是保留的。

另外mysqld_safe有一个功能是当mysqld异常crash时，后台会自动重启mysqld。mysqld关闭后，mysqld_safe会检查pid文件是否存在。如果存在则认为mysqld是异常crash, 需要自动重启；如果不存在则认为是正常关闭的，不需要自动重启,mysqld_safe程序也退出。

原因分析

查看error log发现数据库在相近的时间内启动了两次

141128 23:16:15 mysqld_safe Starting mysqld daemon with databases from  …..  141128 23:16:23 mysqld_safe Starting mysqld daemon with databases from

前面说到mysqld_safe启动mysqld时,会根据pid文件来判断避免重复启动mysqld.然而，由于两次启动时间较近，导致第一次mysqld启动生成pid文件之前，第二个mysqld就已开始启动了，从而绕过了这个判断。第一次mysqld启动会成功，而第二次mysqld启动会因为文件锁而导致启动失败。

InnoDB: Unable to lock ./ibdata1, error: 11

第二次启动的mysqld关闭时会将第一次启动时产生的pid文件删除，从而导致pid文件丢失。

通过mysqld_safe启动mysqld来重现pid文件丢失有一定的概率性，必须是同时启动mysqld_safe。如果是直接通过mysqld启动，同时指定相同的参数启动两次，那么就很容易重现了。

修复

参考5.6 官方的修复方法，在上述场景下删除pid文件时，需判断是否是自己新建的pid文件，同时文件中的pid是否和自身pid一致，否则不能删除。参考补丁

MySQL · 答疑释惑· using filesort VS using temporary

背景

MySQL 执行查询语句，对于order by谓词，可能会使用filesort或者temporary。比如explain一条语句的时候，会看到Extra字段中可能会出现，using filesort和using temporary。下面我们就来探讨下两个的区别和适用场景。

解释

1. using filesort

filesort主要用于查询数据结果集的排序操作，首先MySQL会使用sort_buffer_size大小的内存进行排序，如果结果集超过了sort_buffer_size大小，会把这一个排序后的chunk转移到file上，最后使用多路归并排序完成所有数据的排序操作。

MySQL filesort有两种使用模式:

模式1: sort的item保存了所需要的所有字段，排序完成后，没有必要再回表扫描。

模式2: sort的item仅包括<sort_key, rowid>，待排序完成后，根据rowid查询所需要的columns。

很明显，模式1能够极大的减少回表的随机IO。

2. using temporary

MySQL使用临时表保存临时的结构，以用于后续的处理，MySQL首先创建heap引擎的临时表，如果临时的数据过多，超过max_heap_table_size的大小，会自动把临时表转换成MyISAM引擎的表来使用。

从上面的解释上来看，filesort和temporary的使用场景的区别并不是很明显，不过，有以下的原则:

filesort只能应用在单个表上，如果有多个表的数据需要排序，那么MySQL会先使用using temporary保存临时数据，然后再在临时表上使用filesort进行排序，最后输出结果。

适用场景

我们看一下下面的三个case:

create table t1(         id int, col1 int, col2 varchar(10),     key(id, col1));

create table t2(     id int, col1 int, col2 varchar(10),     key(col1));

case 1:

mysql> explain select * from t1 force index(id), t2 where t1.id=1 and t1.col1 = t2.col2 order by t1.col1 ;  +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+  | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |  +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+  |  1 | SIMPLE      | t1    | ref  | id            | id   | 5       | const |    1 | Using where |  |  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL  |    1 | Using where |  +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+

case1: order by字段能够使用index的有序性，所以没有使用filesort，也没有使用temporary。

case 2:

mysql> explain select * from t1 force index(id), t2 where t1.id=1 and t1.col1 = t2.col2 order by t1.col2;  +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+  | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                       |  +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+  |  1 | SIMPLE      | t1    | ref  | id            | id   | 5       | const |    1 | Using where; Using filesort |  |  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL  |    1 | Using where                 |  +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+

case2: order by谓词，是在第一个表t1上完成，所以只需要在t1表上使用filesort，然后排序后的结果集join t2表。

case 3:

mysql> explain select * from t1 force index(id), t2 where t1.id=1 and t1.col1 = t2.col2 order by t2.col1 ;  +----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------+  | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                                        |  +----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------+  |  1 | SIMPLE      | t1    | ref  | id            | id   | 5       | const |    1 | Using where; Using temporary; Using filesort |  |  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL  |    1 | Using where; Using join buffer               |  +----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------+

case 3: order by的字段在t2表上，所以需要把t1，t2表join的结果保存到temporary表上，然后对临时表进行filesort，最后输出结果。

特别优化

MySQL对order by + limit的filesort做了特别优化，使用Priority queue来保存结果，即一个堆的结构，只保留top n的数据满足limit条件。

另外

filesort和temporary都会在tmp目录下创建文件，temporary创建的是MYI，MYD文件。但filesort的文件, 因为MySQL使用了create->open->unlink->使用->close的方式，隐藏了文件，以便进程异常结束的时候，临时文件能够自动回收掉，所以在评估tmp目录空间的时候，需要特别注意。

MySQL · 优化限制· MySQL index_condition_pushdown

背景

MySQL 5.6 开始支持index_condition_pushdown特性，即server层把可以在index进行filter的谓词传递给引擎层完成过滤，然后结果返回到server。

工作方式

下面看一下InnoDB的处理方式:

通过设置set global optimizer_switch= "index_condition_pushdown=ON"来启用这个特性。

例如:

CREATE TABLE `t1` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `col1` int(11) DEFAULT NULL,   `col2` int(11) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `t1_cc` (`col1`,`col2`) )  ENGINE=InnoDB;

mysql> explain select * from t1 where col1>= 1 and col1 <= 4 and col2=11;  +----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+  | id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                 |  +----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+  |  1 | SIMPLE      | t1    | range | t1_cc         | t1_cc | 10      | NULL |    2 | Using index condition |  +----+-------------+-------+-------+---------------+-------+---------+------+------+-----------------------+

1. 评估

在执行计划评估阶段，通过push_index_cond函数把index filter谓词传递给引擎handler。

2. 执行

InnoDB通过row_search_for_mysql获取每行记录的时候，使用innobase_index_cond函数来check index filter谓词条件是否成立。通过这种方式来完成index上的filter，整个过程并不复杂。

收益和限制

下面来看一下index_condition_pushdown的收益和限制:

收益: index_condition_pushdown所带来的收益可以从三个方面来看:

1. 数据copy

减少了InnoDB层返回给server层的数据量，减少了数据copy。

2. 随机读取

对于二级索引的扫描和过滤，减少了回primary key上进行随机读取的次数

3. 记录锁

记录锁是在InnoDB层完成的，比如如果是select for update语句，就会发现index_condition_pushdown会大大减少记录锁的个数。

限制: 目前index_condition_pushdown还有诸多的限制:

1. 索引类型

如果索引类型是primary key，就不会采用，因为index_condition_pushdown最大的好处是减少回表的随机IO，所以如果使用的index是PK，那么收益就大大减少，不过MySQL官方也在从新评估是否采用，见WL#6061。

2. 性能衰减

如果在primary key上面使用，或者index filter谓词并不能有效过滤记录的时候，会发现sysbench的测试性能相比较关闭ICP的方式略低。可以参考 http://s.petrunia.net/blog/?p=101的讨论。

3. SQL类型

1. 不支持多表update和delete语句，因为select和update会共用handler，而一个是一致性读，一个是当前读，同样的filter都apply的话，update会找不到记录。

2. 如果JOIN是CONST 或者 SYSTEM，不能使用。因为CONST和SYSTEM做了特别优化，只执行一次，做了缓存，而应用filter的话，会产生数据一致性问题。

索引设计的原则

除了MySQL提供的这些新特性以外，DBA或者开发在设计index的时候，应该遵循的一些原则:

1. 查询谓词都能够通过index进行扫描

2. 排序谓词都能够利用index的有序性

3. index包含了查询所需要的所有字段

这就是传说中的Three-star index。

可以参考《Wiley,.Relational.Database.Index.Design.and.the.Optimizers》

MySQL的index_condition_pushdown，前进了一大步，不过相比较Oracle的index扫描方式，还有空间。比如oracle的index扫描支持的index skip scan方式。

MySQL · 捉虫动态·DROP DATABASE外键约束的GTID BUG

背景

MySQL的DDL没有被设计成事务操作，因此DDL操作是无法回滚的（像PgSQL把DDL也设计成事务操作，DDL就可以在执行成功后被回滚操作取消）。这就会导致如果某个DDL语句内部被拆分为多个原子的DDL调用，那么这个DDL语句就不具备中途执行失败后回滚整个DDL语句的能力，也就是说，即使语句逻辑内的某个原子DDL调用失败了，也无法回滚已经完成的那些原子DDL调用。

问题描述

DROP DATABASE 就是一个例子，对于MySQL而言，DROP DATABASE 并非是一个原子DDL操作，因为它是一个个删除DB下的每张表，而 DROP TABLE 操作本身是会做预检查的，无法删除就会取消删表操作返回失败，所以 DROP TABLE 才能认为是原子的DDL调用。这就会引起一个问题，如果一个DB中的某张表DROP失败了，实际上 DROP DATABASE 作为一个整体是执行失败的，但是DB中已经有一些表被删除了，因此Binlog中会记录成多个 DROP TABLE 操作，而不是一个 DROP DATABASE 语句。如果被删除的表的表名都不长，还是会记录成一个删除多张表的 DROP TABLE 语句（DROP TABLE tbl1, tbl2, ...），但是如果表名总长度太长，MySQL会拆分为多个 DROP TABLE 语句来记录。没有GTID的时候这似乎也不是什么大问题，但是引入GTID之后就有一个问题：每个语句只分配一个GTID。如果一个 DROP DATABASE 语句被拆分为多个 DROP TABLE 语句，Binlog中就会出现多个 DROP TABLE 事件共用一个GTID的情况！

举个例子：

CREATE DATABASE db1; USE db1; CREATE TABLE t1 (id INT, name VARCHAR(20), PRIMARY KEY (`id`)) ENGINE=InnoDB; CREATE TABLE t2 (id INT) ENGINE=InnoDB; CREATE TABLE t3 (id INT) ENGINE=InnoDB; CREATE TABLE t4 (id INT) ENGINE=InnoDB; INSERT INTO t1 VALUES(1, "test"), (2, "try"); INSERT INTO t2 VALUES(1);   # 创建很多表名很长的表 let $count = 50; while ($count) {   eval create table a_very_long_long_long_long_long_table_name_$count(id int) engine = InnoDB;   dec $count; }   CREATE DATABASE db2; USE db2; CREATE TABLE t3 (id INT, num INT, ext_id INT, CONSTRAINT t3_fk_1 FOREIGN KEY (ext_id) REFERENCES db1.t1(id)) ENGINE=InnoDB; INSERT INTO t3 VALUES (1, 2, 2);   DROP DATABASE IF EXISTS db1;

这里因为 db2.t3 表引用了 db1.t1 的字段作为外键约束，所以当 db1 做 DROP DATABASE 删除到 t1 表时就报错了，但此时很多表已经被删除了。我们看Binlog中记录的内容：

SET @@SESSION.GTID_NEXT= '340d95b8-a699-11e4-868d-a0d3c1f20ae4:61'/*!*/;  # at 12209  #150128 10:56:10 server id 1  end_log_pos 13259 CRC32 0xcf952733        Query   thread_id=6     exec_time=1     error_code=0  use `db1`/*!*/;  SET TIMESTAMP=1422413770/*!*/;  DROP TABLE IF EXISTS `a_very_long_long_long_long_long_table_name_33`,`a_very_long_long_long_long_long_table_name_15`,`a_very_long_long_long_long_long_table_name_43`,`a_very_long_long_long_long_long_table_name_13`,`a_very_long_long_long_long_long_table_name_10`,`a_very_long_long_long_long_long_table_name_28`,`a_very_long_long_long_long_long_table_name_23`,`a_very_long_long_long_long_long_table_name_32`,`a_very_long_long_long_long_long_table_name_50`,`a_very_long_long_long_long_long_table_name_17`,`a_very_long_long_long_long_long_table_name_19`,`a_very_long_long_long_long_long_table_name_30`,`a_very_long_long_long_long_long_table_name_48`,`a_very_long_long_long_long_long_table_name_49`,`a_very_long_long_long_long_long_table_name_3`,`a_very_long_long_long_long_long_table_name_29`,`a_very_long_long_long_long_long_table_name_9`,`a_very_long_long_long_long_long_table_name_47`,`a_very_long_long_long_long_long_table_name_12`,`a_very_long_long_long_long_long_table_name_42`  /*!*/;  # at 13259  #150128 10:56:10 server id 1  end_log_pos 14315 CRC32 0xd91d1210        Query   thread_id=6     exec_time=1     error_code=0  SET TIMESTAMP=1422413770/*!*/;  DROP TABLE IF EXISTS `a_very_long_long_long_long_long_table_name_36`,`a_very_long_long_long_long_long_table_name_1`,`a_very_long_long_long_long_long_table_name_38`,`a_very_long_long_long_long_long_table_name_24`,`a_very_long_long_long_long_long_table_name_16`,`a_very_long_long_long_long_long_table_name_34`,`a_very_long_long_long_long_long_table_name_37`,`a_very_long_long_long_long_long_table_name_6`,`a_very_long_long_long_long_long_table_name_5`,`a_very_long_long_long_long_long_table_name_40`,`t2`,`a_very_long_long_long_long_long_table_name_4`,`a_very_long_long_long_long_long_table_name_20`,`a_very_long_long_long_long_long_table_name_45`,`a_very_long_long_long_long_long_table_name_2`,`a_very_long_long_long_long_long_table_name_27`,`a_very_long_long_long_long_long_table_name_46`,`a_very_long_long_long_long_long_table_name_35`,`t3`,`a_very_long_long_long_long_long_table_name_26`,`a_very_long_long_long_long_long_table_name_8`,`a_very_long_long_long_long_long_table_name_22`  /*!*/;  # at 14315  #150128 10:56:10 server id 1  end_log_pos 14891 CRC32 0x06158e42        Query   thread_id=6     exec_time=1     error_code=0  SET TIMESTAMP=1422413770/*!*/;  DROP TABLE IF EXISTS `a_very_long_long_long_long_long_table_name_44`,`a_very_long_long_long_long_long_table_name_11`,`a_very_long_long_long_long_long_table_name_25`,`a_very_long_long_long_long_long_table_name_18`,`a_very_long_long_long_long_long_table_name_7`,`a_very_long_long_long_long_long_table_name_31`,`a_very_long_long_long_long_long_table_name_21`,`a_very_long_long_long_long_long_table_name_14`,`t4`,`a_very_long_long_long_long_long_table_name_39`,`a_very_long_long_long_long_long_table_name_41`

3个 DROP TABLE 语句都是同一个GTID：340d95b8-a699-11e4-868d-a0d3c1f20ae4:61

这就导致备库复制报错：

Last_SQL_Errno: 1837               Last_SQL_Error: Error 'When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is '340d95b8-a699-11e4-868d-a0d3c1f20ae4:61'.' on query. Default database: 'db1'. Query: 'DROP TABLE IF EXISTS `a_very_long_long_long_long_long_table_name_36`,`a_very_long_long_long_long_long_table_name_1`,`a_very_long_long_long_long_long_table_name_38`,`a_very_long_long_long_long_long_table_name_24`,`a_very_long_long_long_long_long_table_name_16`,`a_very_long_long_long_long_long_table_name_34`,`a_very_long_long_long_long_long_table_name_37`,`a_very_long_long_long_long_long_table_name_6`,`a_very_long_long_long_long_long_table_name_5`,`a_very_long_long_long_long_long_table_name_40`,`t2`,`a_very_long_long_long_long_long_table_name_4`,`a_very_long_long_long_long_long_table_name_20`,`a_very_long_long_long_long_long_table_name_45`,`a_very_long_long_long_long_long_table_name_2`,`a_very_long_lon

解决方案

怎么解决这个问题呢？

1. 让MySQL支持DDL事务

2. 对DROP DATABASE操作进行预检查

第一种方案对MySQL改动太大了，完全不现实。因此我们采用了第二种方案，也间接实现了 DROP DATABASE 这个操作的原子性。 DROP DATABASE 之所以出现上面的状况，就是因为没有先检查表是否可以删除，而是走一步看一步，一个个删的时候才看能不能删除。我们对MySQL做了修正，对于DB中的每张表，在 DROP DATABASE 执行之前，都先预检查所有可能导致删除表失败的条件，如果一旦发现某张表会无法删除，就放弃整个 DROP DATABASE 操作，提示用户删除错误，让用户先自行解决问题后，再重新执行 DROP DATABASE。

例如上面例子中的情况，本来 DROP DATABASE 执行到有外键约束的表时会报错:

ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails

但此时其他表已经删除了，而我们修正以后，同样的操作会报一个Error和一个Warning，并且没有真的删任何表：

SHOW WARNINGS;  Level Code Message  Error 1217 Cannot delete or update a parent row: a foreign key constraint fails  Note 3000 There are at least one table referenced by foreign key, the first table is 't1'. Please drop table(s) that referenced by foreign key first!

这里提示了用户有表存在问题无法删除，让用户先处理掉之后，再来执行 DROP DATABASE。此时库下面所有的表都还在，一定要预检查通过才会真的删除。

MySQL · 答疑释惑· lower_case_table_names 使用问题

背景

在MySQL中，表是和操作系统中的文件对应的，而文件名在有的操作系统下是区分大小写的（比如linux），有的是不区分大小写（比如Windows），表名与文件名的大小写对应关系，MySQL 是通过 lower_case_table_names 这个变量来控制的。

这个变量的有效取值是0，1，2，按照官方文档的解释：

0表示，表在文件系统存储的时候，对应的文件名是按建表时指定的大小写存的，MySQL 内部对表名的比较也是区分大小写的；

1表示，表在文件系统存储的时候，对应的文件名都小写的，MySQL 内部对表名的比较是转成小写的，即不区分大小写；

2表示，表在文件系统存储的时候，对应的文件名是按建表时指定的大小写存的，但是 MySQL 内部对表名的比较是转成小写的，即不区分大小写。

0适用于区分大小写的系统，1都适用，2适用于不区分大小写的系统。

如果在开始使用MySQL选定了一个合适的值后，就不要改变，不然的话在之后使用中就会出现问题。

问题描述

这里给出一个在使用过程中改变 lower_case_table_names 导致 drop database 失败的案例。因为lower_case_table_names是个只读变量，只能在启动时指定参数设置值，或者 gdb 挂上去直接改内存。

首先在启动 mysqld 的时候，指定 lower_case_table_names = 0，我们执行这样的语句：

create database db1; use db1; create table t1(a int) engine = InnoDB;  create table t2(a int) engine = MyISAM;  create table T3(a int) engine = InnoDB;  create table T4(a int) engine = MyISAM;

查看对应数据库目录下的表文件：

$ls db1 db.opt  t1.frm  t1.ibd  t2.frm  t2.MYD  t2.MYI  T3.frm  T3.ibd  T4.frm  T4.MYD  T4.MYI

然后重启mysqld，指定 lower_case_table_names =1，执行删除db1

mysql> drop database db1; ERROR 1010 (HY000): Error dropping database (can't rmdir './db1', errno: 39)

可以看到删库语句执行失败，我们再看下数据库目录下的表文件

$ls a T3.frm  T4.frm  T4.MYD  T4.MYI

可以看到，大写的 T3 和 T4 表没有被删掉，为什么呢？

问题分析

mysqld 在执行 drop database 操作的时候，是调用 mysql_rm_db 这个函数，在删除时先把db下的所有表都删掉，然后再把db删掉。为了找出对应db下的所有表，mysqld 是通过遍历数据库目录下的文件来做的，具体是用 find_db_tables_and_rm_known_files 这个函数，遍历数据库目录下的所有文件，然后构造出要 drop 的table列表，然而在构造删除列表过程中，会有这样一个判断:

if (lower_case_table_names)   table_list->table_name_length= my_casedn_str(files_charset_info,                                               table_list->table_name);

意思就是如果lower_case_table_names非0的话，就把 table_name 转成小写的，T3 和 T4 就被转成 t3 和 t4，这样生成的 table_list 中的对应的表是 t1,t2,t3,t4。之后拿着这样的 table_list 通过 mysql_rm_table_no_locks 一个个删表，这样就只把t1,t2 给删了，t3和t4不存在，并且删表时的逻辑是带有 if exists 的，所以也不会报错。

在list表都删除完后，调用rm_dir_w_symlink来删除db目录，此时db1目录下还有 T3 T4 对应的文件，这个函数会调用系统的 rmdir 函数，而当目录非空的时候，rmdir是执行失败的。

所以我们看到最终的错误提示 Error dropping database (can't rmdir './db1', errno: 39)

建议

上面的问题是改变 lower_case_table_names 导致 drop database 失败，其实还有许多其它的因为lower_case_table_names值改变导致的问题，比如主备库本来这个值本来是一致的，如果只改主库的值的话，就会导致备库复制中断，报找不到表的问题，或者本来是不区分大小写的，应用里的写的SQL语句有大写表名，也有小写表名，之后改成区分大小写，就会导致应用出错。

所以建议是:

不要轻易的改变lower_case_table_names的值，如果真要改的话，要先检查下已有的表是否有大小写的问题，保证目前的表名和要改的模式是一致的，比如从区分大小写改为不区分大小写，那就不应该有大写表存在，如果有的话，要先把大写表rename成小写的，如果本来有共存同名的大写表和小写表，就要想办法去掉一个。
应用不要依赖于 mysql 的表名转换机制，应用里的sql语句应该和表名一致，在不区分大小写的时候，应用里对同一个表的使用，不要既有大写表名，也有小写表名。

PgSQL · 特性分析· Logical Decoding探索

Logical Decoding是9.4里面的一个主要功能，是向最终实现逻辑复制迈出的一大步。简言之，它的功能是从PG的WAL日志中，读取数据库更新信息，然后“翻译”（Decode）成逻辑的形式，可发送到远程从库做数据同步。这个功能还可以用于，DBA在数据库宕机，并发生主从切换后，检查原主库有哪些更新宕机前未同步到从库，并手动同步来弥补丢失的（已提交）的更新。这里我们探索一下它的使用和实现原理。

使用

1）首先需要将 wal_level这个配置参数设置为logical，并保证max_replication_slots至少为1。

2）创建Logical Replication Slot。Logical Decoding利用了Logical Replication Slot来获取和Decode日志。关于Physical Replication Slot我们在上期中有详细介绍，而Logical Replication Slot与Physical Replication Slot的数据结构类似。创建一个Logical Replication Slot的命令如下：

SELECT * FROM pg_create_logical_replication_slot('my_rep_slot', 'test_decoding');  slot_name  | xlog_position -------------+--------------- my_rep_slot | 0/7FE68E8

select * from pg_replication_slots;   slot_name   |    plugin     | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn ---------------+---------------+-----------+--------+----------+--------+------+--------------+------------- my_rep_slot   | test_decoding | logical   |  13003 | postgres | f      |      |         9735 | 0/7FE6828

注意，创建Logical Replication Slot，需要指定一个输出插件（Output Plugin）。这个插件要提供一些回调函数，用于格式化输出日志。就是说，内核中的Logical Decoding先读取WAL日志，将其Decode成一种半成品式的格式（已包含所有有效信息，比如被更新的表名，更新类型，更新前后的数据记录即Tuple），然后交由输出插件最终呈现给用户。这里我们使用了系统自带的一个插件，即test_decoding。上面的输出中，创建命令返回的xlog_position的值是当前系统中，最后被写入磁盘的日志记录的LSN。此LSN之后的日志，都可以通过Logical Decoding进行解析了。

3）解析日志。

--先做一个执行插入操作的事务： begin; insert into test values(2); commit; --使用pg_logical_slot_peek_changes，Decode对应的WAL日志。 SELECT * FROM pg_logical_slot_peek_changes('my_rep_slot', NULL, NULL, 'include-timestamp', 'on', 'include-xids', 'on'); location  | xid  |                      data -----------+------+------------------------------------------------  0/7FE6C80 | 9736 | BEGIN 9736  0/7FE6C80 | 9736 | table public.test: INSERT: col[integer]:2  0/7FE6DE0 | 9736 | COMMIT 9736 (at 2015-03-13 15:03:49.803582+08)

pg_logical_slot_peek_changes返回数据中的第二行记录了我们所做的INSERT操作（只有在事务提交后，才能看到这些修改）。而我们通过pg_xlogdump可以看到原来的WAL日志记录为：

<PG installdir>/bin/pg_xlogdump -s  0/7FE6C80 -n 1 rmgr: Heap        len (rec/tot):     31/   219, tx:       9736, lsn: 0/07FE6C80, prev 0/07FE6C48, bkp: 1000, desc: insert: rel 1663/13003/16507; tid 0/3

也就是说，Logical Decoding把这条日志，反解析成一个“table public.test: INSERT: col[integer]:2”字符串。其实如果对输出插件稍作修改，可以直接解析成可执行的SQL语句：“INSERT INTO public.test (col) VALUES(2)“

那么这是如何做到的呢？下面我们看看其中原理。

原理

追踪一下pg_logical_slot_peek_changes的调用链，不难看到Decoding的整个过程。在pg_logical_slot_get_changes_guts中，从restart_lsn（即上次的最后读取后，剩下的事务中最先开始的事务对应的LSN）开始，先用XLogReadRecord函数（注意，会先从cache里面读取日志，如果cache里面没有，则会到磁盘中的日志段里面读取）获取一个日志记录，存入结构体XLogRecord，紧接着用LogicalDecodingProcessRecord做Decode。如此循环，直到读完日志或到达指定点。

LogicalDecodingProcessRecord是解析日志的关键。它在内存中维护一个哈希表（LogicalDecodingContext->reorder->by_txn），存放正在处理的事务信息。在处理每个日志记录时，如果遇到一个BEGIN操作，就在哈希表中插入相应事务。而只有在遇到COMMIT操作的时候，才会把整个事务的所有语句解析出来（调用ReorderBufferCommit）。这个过程中，它要为每个事务维护一个快照（Snapshot）。每次有事务做COMMIT都要更新一下这个快照。这样，等到事务COMMIT时，它的快照是最新的，可以用来访问系统表，得到如relation node id与relation名字之间的对应关系等信息，从而完成Decode。需要说明的是，LogicalDecodingProcessRecord在维护快照时做了优化：因为Decode过程只需要访问系统表，所以快照中只保留了那些更新了系统表的事务。

另外，Replication Slot的xmin信息会影响系统的Vacuum，使其保留仍然需要的数据版本。而SnapBuildProcessRunningXacts会不断更新Replication Slot中的xmin信息，避免使Vacuum停滞。

输出Decode后日志的过程，都在DecodeCommit调用的ReorderBufferCommit函数中。在ReorderBufferCommit中，调用了输出插件的apply_change等回调函数，会将日志信息打印成我们最终看到的字符串，这样就完成了Decode。

PgSQL · 特性分析· jsonb类型解析

PG 9.4版本里面，增强了对json数据的支持，受到了很大关注。9.4之前，PG已经原生支持json数据类型了，但只是用字符串的形式存储和处理。这样做天然有性能上的缺点：每次对json字符串里面的数据进行查询，一般需要全表扫描加字符串匹配，效率很低。当然也可以在存储json的字符串字段上创建GIN索引，但需要对查询中用到的json的key或value创建单独索引，造成要被动维护很多索引。所以，这种json类型，只适用于把PG单纯作为数据存储，只读入读出数据，不对数据进行限定key或value查询的场景。

PG 9.4中引入了jsonb类型。其特点是，将json数据中的key和value进行解析，转换为PG的基本数据类型，包括数字，字符串和布尔类型等；同时，增加了对应的GIN处理函数，可以将json中的所有key和value转换为GIN索引的key。这样，只用一个GIN索引，即可实现对所有key或value的条件查询。下面我们分析一下jsonb的使用方法和内核实现。

使用

创建含jsonb类型的表方法如下所示：

=> create table test_jsonb(col_jsonb jsonb); CREATE TABLE => insert into test_jsonb values('{"product": "PostgreSQL", "version": 9.4, "platform":["win", "linux", "unix"]}'::jsonb); INSERT 0 1

创建GIN索引的方法如下：

--创建jsonb_ops索引： => create index idx_jsonb on test_jsonb using gin (col_jsonb); CREATE INDEX --创建jsonb_ops_path索引： => create index idx_jsonb_path on test_jsonb using gin (col_jsonb jsonb_path_ops); CREATE INDEX

可以使用下面的查询得到含有<product, ProgreSQL>键值对的行：

=>select * from test_jsonb where col_jsonb @> '{"product" : "PostgreSQL"}';                                     col_jsonb --------------------------------------------------------------------------------- {"product": "PostgreSQL", "version": 9.4, "platform": ["win", "linux", "unix"]}

内核实现

先分析一下jsonb是如何从字符串，变成特殊的二进制形式存入磁盘的。追踪一下jsonb插入的过程，可以看到PG所调用的函数流程如下。

jsonb_in->jsonb_from_cstring->pg_parse_json->JsonbValueToJsonb

其中，pg_parse_json先把用户输入的字符串，通过编译器转换为一个树形结构（每个节点的类型为JsonbValue）。然后JsonbValueToJsonb在这个结构基础上，转换为存入磁盘的格式。从convertJsonbObject函数可以看出，转换为磁盘格式的策略为：从树形结构的根部开始遍历，递归进行广度优先遍历。对于同一父亲下面的子键值，将所有键名（字符串）长度写入buffer中预留的头部，随后将键名依次写入buffer中。最后再以相似的方式写入键所对应的所有值（值如果是json对象，则递归调用）。这样，读入buffer的头部，就可以遍历出所有键名的位置，得到键名。再从读第一个键值开始，读入对应的值或子键，最终得到整个树（见JsonbIteratorNext）。

采用这种存储方式，jsonb所占用的存储空间比原来支持的json类型要多一些。其实，jsonb的核心优势在于快速和灵活的索引。从前面创建index的语句可以看到，jsonb支持两种特有的GIN索引jsonb_ops和jsonb_path_ops。我们知道，GIN索引建立时，会先通过内建函数从表中每行数据的索引字段的值中，抽取键（key），一个字段值一般可抽取多个key。然后，将每个key与含有此key的所有行的ID组成键值对，再将它们插入b树索引供查询。那么这两种GIN索引有什么区别呢？

它们的区别在于，生成GIN key的方式不同。jsonb_ops调用gin_extract_jsonb函数生成key，这样每个字段的json数据中的所有键和值都被转成GIN的key；而jsonb_path_ops使用函数gin_extract_jsonb_path抽取：如果将一个jsonb类型的字段值看做一颗树，叶子节点为具体的值，中间节点为键，则抽取的每个键值实际上时每个从根节点到叶子节点的路径对应的hash值。

不难推测，jsonb_path_ops索引的key的数目和jsonb的叶子节点数有关，用叶子节点的路径做查询条件时会比较快（这也是这种索引唯一支持的查询方式）；而jsonb_ops索引的key的数目与jsonb包含的键和值（即树形结构的所有节点）的总数有关，可以用于路径查询之外的其他查询。