MySQL内核月报 2014.10_appeared in the statement, but this is not allowed

为了生成执行计划，优化器会使用一个基于估算查询执行过程中各种操作开销的成本模型。MySQL优化器有一组在编译时默认指定的“成本常量”（例如CPU算几个因子、IO算几个因子），用于决策执行计划的生成。MySQL·　5.7重构·Optimizer Cost Model

从5.7.5开始，优化器在执行计划的生成过程中有了额外的成本估算项可用。这些估算项存在在mysql系统库的server_cost和engine_cost表中，并且任何时候都可以通过修改表中的值来配置这些估算项。这些表存在的目的是，可以通过简单的调整这些表中的成本估算项来影响执行计划的生成，来达到调整执行计划的目的。

两张表的结构和内容如下：

root@localhost : test 12:38:41> select * from mysql.server_cost; 
+------------------------------+------------+---------------------+---------+
| cost_name                    | cost_value | last_update         | comment |
+------------------------------+------------+---------------------+---------+
| disk_temptable_create_cost   |       NULL | 2014-10-15 11:34:11 | NULL    |
| disk_temptable_row_cost      |       NULL | 2014-10-15 11:34:11 | NULL    |
| key_compare_cost             |       NULL | 2014-10-15 11:34:11 | NULL    |
| memory_temptable_create_cost |       NULL | 2014-10-15 11:34:11 | NULL    |
| memory_temptable_row_cost    |       NULL | 2014-10-15 11:34:11 | NULL    |
| row_evaluate_cost            |       NULL | 2014-10-15 11:34:11 | NULL    | 
+------------------------------+------------+---------------------+---------+

root@localhost : test 12:38:51> select * from mysql.engine_cost;
+-------------+-------------+--------------------+------------+---------------------+---------+
| engine_name | device_type | cost_name          | cost_value | last_update         | comment |
+-------------+-------------+--------------------+------------+---------------------+---------+
| default     |           0 | io_block_read_cost |       NULL | 2014-10-15 11:34:11 | NULL    |
+-------------+-------------+--------------------+------------+---------------------+---------+

成本模型的工作方式

可配置的优化器成本模型按如下方式工作：

MySQL Server在启动时读取成本模型表，并且在运行时使用内存中存贮的值。表中任何非NULL的成本估算项的值都会覆盖在代码中写死的默认成本常数，优先参与优化器成本计算。任何NULL值的成本估算项优化器都会认为用户没有指定特定的值，而使用代码中默认的成本常数。

在MySQL运行时，Server可能会重新读取成本表，可以通过动态载入存储引擎或者执行FLUSH OPTIMIZER_COSTS语句来触发。

成本估算表可以让管理员通过简单的方式去调整成本估算项，也可以通过把估算项设置为NULL来恢复原来的内置默认值。优化器使用的是内存中缓存的开销值，所以修改了表中的值后记得用FLUSH OPTIMIZER_COSTS命令让修改生效。

内存中缓存的成本项对当前正在执行的Session是不起效果的，一个Session内执行的Query其成本项的值是不会变动的。即使Server触发了重新读取成本表，任何估算项的变更也只影响后来链接上来的Session。

成本开销表是不参与复制的，只影响修改的本地实例，不会通过复制把开销表的变更复制到备库。

成本模型数据库

优化器成本模型库由mysql系统库下的两张表组成，包含了Query执行过程中一些操作项的成本估算值：

server_cost: Server层一些操作的成本估算项的值

engine_cost: 特定引擎的一些操作的成本估算项的值

server_cost表包含这些字段:

cost_name

成本模型中的成本估算项的名称（不区分大小写）。如果Server无法识别名称，在读取的时候会打一个报错在error log中。

cost_value

成本估算项的值。如果值是非NULL的，那么Server就使用这个值作为成本，否则就用编译时内置的值，DBA可以通过UPDATE这个列来修改响应的成本项。如果Server读到无效的值（例如负数），会在errorlog中打一条Warning。

要覆盖内置的默认值就需要设置一个非NULL值，如果要恢复默认值，就把值重新改为NULL，然后执行FLUSH OPTIMIZER_COSTS 告诉Server重新读取成本表。

last_update

这一行的最后修改时间。

comment

成本项的描述注释。DBA可以利用这个这个列来记录为什么修改了这个成本项的值，用于备查。

server_cost表的主键是server_cost，所以不能创建名称相同的成本项。

Server可以识别server_cost表中如下的cost_name:

disk_temptable_create_cost, disk_temptable_row_cost

内部创建磁盘临时表的成本开销。增加这些成本项的值可以让优化器更偏向于生成不使用磁盘临时表的执行计划。

key_compare_cost

比较记录键值的成本开销。增加这个值可以让执行计划中比较键值的操作成本变的更加昂贵。例如，一个执行计划执行了filesort，那么它的代价会比利用索引避免排序的代价要大得多。

memory_temptable_create_cost, memory_temptable_row_cost

内部创建内存临时表的成本开销。增加这些值可以使得建立内部临时表成本增加，因而优化器会偏向于不使用临时表。

row_evaluate_cost

扫描记录行的成本开销。增加这个会导致执行计划中扫描很多行数据的操作变得更加昂贵，因而执行计划会偏向扫描更少的函数。例如，一个全表扫描会比范围扫描要昂贵的多。

engine_cost 表包含这些列:

engine_name

要应用这个成本项的存储引擎的名称（不区分大小写）。如果这些值是default，那么对所有没在表里指定的存储引擎都会生效。如果Server无法认出引擎名称，会在errorlog输出一条Warning。

device_type

这个成本项适用的设备类型。这个列可以为不同的存储设备指定不同的成本开销，例如SAS盘和SSD盘是不一样的。不过目前，这个信息还没启用，只有0可以设置。

cost_value，last_update，comment

这三列的含义跟server_cost表中的字段含义一样.

engine_cost表的主键是 (cost_name, engine_name, device_type)，所以不允许为一个引擎的同一类存储设备创建相同的成本项。

目前Server只识别engine_cost表中的一个cost_name：

io_block_read_cost

这个成本项表示从磁盘读取一个数据的成本。增加这个值会导致执行计划中读取磁盘块会有更高的成本，因此优化器会偏向于读取更少的磁盘块。例如，一个全表扫描会比一个范围扫描读取更少的磁盘块，因此优化器会偏向范围扫描。

MySQL·　系统限制·text字段数

背景

　　当用户从oracle迁移到MySQL时，可能由于原表字段太多建表不成功，这里讨论一个问题：一个InnoDB表最多能建多少个text字段。

　　我们后续的讨论基于创建表的语句形如：create table t(f1 text, f2 text, ..., fN text)engine=innodb;

默认配置

　　在默认配置下，上面的建表语句，N取值范围为[1, 1017]。 为什么是1017这个“奇怪”的数字。实际上单表的最大列数目是1024-1，但是由于InnoDB会增加三个系统内部字段（主键ID、事务ID、回滚指针），因此需要减3。而用于记录系统字典表也受1023的限制，又需要再增加三个该表的系统字段，因此每个表的最大字段数是1023-3*2。

插入异常

　　上述描述说明的是表能够创建成功的最大字段数。但是这样的表是“插入不安全”的。我们知道text的长度上限是64k。而往上表中插入一行，每个字段长度为7，就会报错：Row size too large (> 8126).

　　一个page是16k，空page扣掉页信息占用空间是16252，需要除以2，原因是每个page至少要包含两个记录。

　　也就是说，虽然可以创建一个包含1017个text字段的表，但是很容易碰到插入失败。

如何保证插入安全

　　上面的表结构，在保证插入安全的情况下，N的最大值是多少？text在存储的时候，当超过768字节的时候，剩余部分会保存在另外的页面（off-page），因此每个字段占用的最大空间为768+20+2=788. 20字节存储最短剩余部分的位置（SPACEID+PAGEID+OFFSET）。2字节存储本地实际长度。

　　因此N最大值为lower(8126/790)=10。

　　如果我们想在创建的表的时候，保证创建的表中的text字段都能安全的达到64k上限（而不是等插入的时候才发现），那么需要将默认为OFF的innodb_strict_mode设置为ON，这样在建表时会先做判断。

　　但是，在设置为严格模式后，上述建表语句的最大N却并非10.

ROW_FORMAT

　　在off-page存储时，本地占用790个字节，是基于默认的ROW_FORMAT，即为COMPACT，此时插入安全的N上限为10。

　　而在InnoDB新格式Barracuda支持下，Dynamic格式的off-page存储时，在local保存的上限不再是768，而是20个字节。这样每个字段在数据页里面占用的最大值是40byte，再需要一个额外的字节存储实际的本地长度，因此每个text最大占用41字节。

　　实际上很容易测试在严格模式下，建表的最大N为196. 以下为N=197时计算过程：

　　每行记录预留header 5个字节。

　　每个bit保存是否允许null，需要 upper(197/8)=25个字节。

　　三个系统保留字段 6+6+7=19.

　　因此总占用空间 5+25+19+41*197=8126！

　　也就是说，当N=197时，刚好长度为8126，而代码中实现是 if(rec_max_size >= page_rec_max) reutrn(error).

　　就这么不巧！

作为补充

　　有经验的读者可以联想到，如果我们的表中自己定义一个int型主键呢？此时系统不需要额外增加主键，因此整个表结构比之前少2字节。

　　也就是说，建表语句修改为: create table t(id int primary key, f1 text, f2 text, ..., fN text)engine=innodb;

　　则此时的N上限能达到197。

MySQL·　捉虫动态·binlog重放失败

背景

在 MySQL 日常维护中，要回滚或者恢复数据，我们经常会用 binlog 来在数据库上重放，执行类似下面的语句：

mysqlbinlog mysql-bin.000001 | mysql -hxxxx -Pxx -u

最近遇到了这样一个问题，在重放 binlog 时，mysqld 报这样的错

ERROR 1064 (42000) at line 25: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER ;

分析

上面的错是说语法不对，难道是 binlog 写错了，为了方便查看，先把 mysqlbinlog 解析结果保存到一个文件

mysqlbinlog mysql-bin.000001 > abc.sql

然后打开 abc.sql 文件，会看到这样的语句

"CREATE TABLE t_binlog_sbr(a int)^@"

最后面的奇怪的 "^@" 这是啥呢，我们用二进制方式打开文件后，发现这个其实是1个字节，值是 00，被显示成 "^@"了。

为啥后面会多 1 个 0 呢，后来发现是用户在用 MySQL C API 时用错了，具体是这个函数 mysql_real_query，基原型是

int mysql_real_query(MYSQL *mysql, const char *stmt_str, unsigned long length)

详细说明参考这里，length 参数表示 stmt_str 的长度，所以正常的调用应该是这样的：

mysq_real_query(mysql, sql, strlen(sql))

可是用户在使用时多加了个1，变成这样

mysq_real_query(mysql, sql, strlen(sql) + 1)

最终导致记录的 binlog 后面多了个 '\0'。 这个问题只在 statement 格式有，row 格式没有。

解决方法

有同学会问，+1 可以，+2、 +3 呢，这个是不可以的，>=2 的都是不行的，语句发过来后，mysqld 在 parse_sql 阶段直接报错返回了，后面就不会执行了。

1) 修改代码

mysq_real_query(mysql, sql, strlen(sql) + 1) 这种用法是不对的，但是 MySQL 却允许，虽然这么用是不对的，但是为了兼容性，最好还是允许这种使用方式，但是在写binlog的时候做个判断，长度是不是写错了，错了的话纠正过来，在 THD::binlog_query 里面改。

2) 5.6 版本加参数

如果是用 5.6 版本的 mysql client 的话，在重放时出错提示信息不一样，是类似下面这样的，更加友好，这个错误是 mysql client 报的，不是mysqld报的：

ERROR at line 24: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected....

5.6 版本的 mysql client 多了一个参数 --binary-mode，允许语句里有 '\0'，所以如果是用5.6的话，就可以不用修改代码，重放binlog时这样做就可以了：

mysqlbinlog mysql-bin.000001 | mysql --binary-mode -hxxxx -Pxx -u

MySQL·　捉虫动态·从库OOM

bug背景

官方最近发布的版本(5.7.5)修复了这样一个bug，主备复制场景下，如果主库和备库对应的表结构中有数据类型不一致，并且主库的 binlog 是 row 格式的，这时候如果主库对不一致的表做了一个大事务更新，备库在应用 relay-log 的时候报OOM(Out of Memory)。bug地址在这里，主备数据类型不一致主要发生在这2种情况下：

1. 主备库版本不一致，不同版本之间的数据类型可能存在不一致。用户在报这个bug时，就是在5.5到5.6的复制场景下，用到了时间类型，时间类型在5.6.4版本时发生了变化
2. 人为直接的连上从库 alter 表

bug分析

为啥数据类型不一致会导致 OOM 呢？OOM 表示程序在持续申请内存，把内存给用爆了。从库的slave thread在应用Rows_log_event时，如果发现主库的表和从库的表不兼容，就会创建一个临时中间表，做数据转化：

Rows_log_event::do_apply_event()
    table_def::compatible_with()
         table_def::create_conversion_table()
              create_conversion_table()

在此过程中用的临时表结构和field字段都是从thd的mem_root分配的，而每个Rows_log_event应用时分配内存空间再do_apply_event后就不会再用了，但是 Rows_log_event::do_apply_event() 结束后并没有free_root释放，而是在事务所有event做完后释放的。类似下面这种包含大量更新语句的事务，每一个更新对应一个Rows_log_event，备库在应用时，在事务执行中间所有申请的内存都会保持，如果语句非常多的话，就导致OOM了。

begin;
insert into t1 values (xxx); /* 1 */
insert into t1 values (xxx); /* 2 */
insert into t1 values (xxx); /* 3 */
....
insert into t1 values (xxx); /* 1000000 */
end;

bug 修复

像 Query_log_event::do_apply_event() 在结束会调用free_root，来释放thd->mem_root空间，而Rows_log_event::do_apply_event()却不能这样干，因为在下面的场景下，用户的线程会调用 Rows_log_event::do_apply_event()

mysqlbinlog mysql-bin.00000x | mysql -hxxxx -Pxx -u

如果在中间释放用户线程的thd->mem_root的话，会有问题。

因此官方的修复方法是在Log_event类构造函数初始化一个属于log_event的 mem_root

Log_event::Log_event()
    init_sql_alloc(PSI_INSTRUMENT_ME, &m_event_mem_root, 4096, 0);

在析构函数里释放

virtual ~Log_event()
    free_root(&m_event_mem_root, MYF(MY_KEEP_PREALLOC));

然后把Rows_log_event::do_apply_event()本来从thd->mem_root申请的内存改为从自身的 m_event_mem_root 申请，这样每个event应用完，被delete时其转化过程中申请的内存也一并被释放，避免了OOM的产生。

MySQL·　捉虫动态·崩溃恢复失败

现象

　　5.6版本，在创建InnoDB表过程中，若发生crash，会导致服务无法启动。

背景

　　每个InnoDB表A创建成功后有两个文件A.frm和A.ibd。建表流程如下：

　　1、创建A.frm

　　2、创建A.ibd

　　3、初始化A.ibd

　　4、将表A加入InnoDB字典

　　若crash发生在步骤2之后，则只保留一个完整的A.frm和一个空文件A.idb。

崩溃恢复

　　在上述的crash发生后，下一次启动则需要做崩溃恢复。崩溃恢复的一个逻辑是需要遍历数据目录下的所有.ibd文件，验证文件与字典的一致性。

　　对于长度为0的.ibd 文件，报错并跳过，继续检测下一个表。

　　以上是5.5和5.6共有的逻辑。但5.6的一个新特性破坏了这个规则。

远程目录

　　5.6支持create table的时候指定其他目录。语法是create table 里新增参数DATA DIRECTORY.这样一个表就可能存在多个表空间。每个表空间对应一个数据结构(fsp_open_info).

　　这意味着在崩溃恢复过程中，需要验证哪一个表空间是可用的(fil_validate_single_table_tablespace)，

　　验证的方法是尝试读取该表空间的第一个page，若可用则将对应的fsp_open_info::success设置为TRUE。

　　而在读取本地默认表空间的第一个页时，若碰到读取失败，直接exit(1)，导致程序直接退出。“若文件小于4个page就报错”的逻辑，是在这个exit之后。

分析改进

　　其实在这个场景下，多出来的A.frm和A.ibd并不会导致系统严重问题。由于表A还没有记录入系统字典，实际上只需要将这两个文件直接删掉即可。

　　因此5.6的这个新增要求过于苛刻。改进方法是将文件大小的判断提前，若发现小于4个page，则直接报错跳过这个表。

MySQL·　功能改进·InnoDB Warmup特性

提要

相对于纳秒级的内存访问延时，普通的机械盘达到了毫秒级的随机访问延时，对于OLTP应用来说，物理IO绝对是目前数据库管理系统的最大性能杀手，所以增加内存的大小，提高IO的命中率无疑可以作为一种降低时延的常用优化手段。

针对使用InnoDB引擎的MySQL实例来说，增加buffer pool的大小，尽可能的提高buffer pool的命中率，减少物理IO的概率，能极大的提升系统的吞吐量。

但是，随着内存越来越大，面临着一个很严重的问题：当内存突然失效，或者实例异常crash后，面对相同的请求压力，或者突然的大压力，系统由于内存未命中会耗尽IO资源，并导致request响应变慢，形成雪崩效应。

Warmup特性

MySQL 5.6 Innodb提供了warmup的功能，并增加了三个控制参数：

innodb_buffer_pool_dump_at_shutdown

innodb_buffer_pool_filename

innodb_buffer_pool_load_at_startup

工作原理

InnoDB启动一个后台线程，等待一个条件变量：

1. 当系统shutdown的时候，如果innodb_buffer_pool_dump_at_shutdown=on，系统会notify condition，从buffer pool的LRU链表中，读取spaceid+page_no到innodb_buffer_pool_file文件中，然后正常关闭。

2. 当系统startup的时候，如果innodb_buffer_pool_load_at_startup=on，并且存在innodb_buffer_pool_file，会读取元信息，进行异步IO读取数据加载到buffer pool中。

3. 为了防止系统运行过久，innodb_buffer_pool_file过时，无法反映当前热点数据的情况，InnoDB又提供了一个innodb_buffer_pool_dump_now参数，set后会即时进行一次dump，覆盖掉老的文件。

那么问题来了

1. Warmup是否影响startup的速度：

不影响.启动的时候，读取innodb_buffer_pool_file, 排序后，进行异步IO，不影响startup的速度。但现实的情况是：如果你是在业务高峰期出现crash，其实对于系统来说，先warmup后，再开放提供服务，更合适。

2. 异常crash的时候，使用过时的元数据：

如果异常crash，那么就存在过时的innodb_buffer_pool_file，如果想避免这种情况，系统可以每隔一段时间，进行一次dump。

3. dump是否导致系统抖动

dump的过程，会持有mutex，扫描LRU链表，读取元数据，如果在系统业务高峰期，可能会产生抖动。

改进

MySQL 5.7 又增强了warmup功能的使用：

1. 新增参数innodb_buffer_pool_dump_pct

当前InnoDB的buffer pool可能设置的比较大，可以通过设置dump的比例，控制dump的速度和load时的量。

2. innodb_io_capacity

控制load过程中，防止过量使用IO资源，如果单机多实例的情况下，同时启动实例，会使IO过载。

MySQL·　文件结构·告别frm文件

提要

长久以来，server层和InnoDB层都保存了表的元数据，server使用frm文件保存了column，data_types等信息，而InnoDB使用data dictionary来保存meta data。

由于server层使用文件系统，而InnoDB使用事务引擎，所以经常会存在两者不一致的情况，比如：

create table的过程中实例crash

alter table过程中实例crash

具体可以参见： http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html 包括相关的解决方法。

改进

MySQL 后续的版本准备做相关的改进：

1. 使用native InnoDB-based Data Dictionary保存meta data，不再需要frm文件

2. 使用InnoDB引擎保存MySQL的系统表，比如privileges和timezones相关的表

使用new Data Dictionary，去掉frm文件，目前还在MySQL lab版本中。

而在MySQL 5.7的版本中，以下的系统表已不再使用MyISAM，而使用InnoDB引擎保存数据：

 - help_category
 - help_keyword
 - help_relation
 - help_topic
 - time_zone
 - time_zone_leap_second
 - time_zone_name
 - time_zone_transition,
 - time_zone_transition_type

变化

1. 由于使用具有ACID特性的InnoDB引擎，由crash导致的元数据不一致情况将不再出现。

2. 对于information_schema中元数据的查询，系统将暴露一个基表上的view出来，供查询。

3. 由于使用InnoDB引擎来保存数据，--skip-innodb的参数将不再有意义，5.7版本中做删除处理。

问题

1. 系统表切换到InnoDB引擎上，对于版本升级或者降级需要对脚本特殊处理。

2. 去掉frm文件，系统如何平滑的进行升级？

3. 第三方引擎怎么办？oracle后续的打算是如何？这是一个很大的问题。

由于去掉frm文件使用native InnoDB-based Data Dictionary的特性还在lab环境中酝酿，期待中。

无论如何，提升failure recovery的水平，对于使用者来说，终归是好事情！

MariaDB· 新鲜特性·ANALYZE statement 语法

MariaDB 10.1版本中新增加了一个 ANALYZE statement 命令。这个命令跟 EXPLAIN statement 命令类似，但不同的是， ANALYZE statement 命令调用优化器生成执行计划并且会真实的去执行语句，再用 EXPLAIN 的输出来替代结果集，并且 EXPLAIN 结果是实际语句执行中统计出来的。

这个语句可以让你检查优化器估算的执行计划代价和实际执行差多少。

命令的输出

MariaDB> analyze select * from tbl1 where key1 between 10 and 200 and col1 like 'foo%'\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: tbl1
        type: range
possible_keys: key1
         key: key1
     key_len: 5
         ref: NULL
        rows: 181
      r_rows: 181
    filtered: 100.00
  r_filtered: 10.50
       Extra: Using index condition; Using where

我们可以看到 ANALYZE 命令多了r_rows和r_filterd两行，我们来比较一下 EXPLAIN 计算的 rows/filtered 和 ANALYZE 计算的 r_rows/r_filtered 两列的区别。

r_rows 是基于实际观察的 rows 列，它表示实际从表中读取了多少行数据。

r_filtered 是基于实际观察的 filtered 列，它表示经过应用WHERE条件之后还有百分之多少的数据剩余。

输出结果解析

让我们来看一个更复杂的SQL。

analyze select * 
from orders, customer 
where 
  customer.c_custkey=orders.o_custkey and 
  customer.c_acctbal < 0 and 
  orders.o_totalprice > 200*1000

+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
| id | select_type | table    | type | possible_keys | key         | key_len | ref                | rows   | r_rows | filtered | r_filtered | Extra       |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
|  1 | SIMPLE      | customer | ALL  | PRIMARY,...   | NULL        | NULL    | NULL               | 149095 | 150000 |    18.08 |       9.13 | Using where |
|  1 | SIMPLE      | orders   | ref  | i_o_custkey   | i_o_custkey | 5       | customer.c_custkey |      7 |     10 |   100.00 |      30.03 | Using where |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+

从上面的结果，我们可以获得如下信息：

对于 customer 表，customer.rows=149095, customer.r_rows=150000. 从这两个值来看，优化器对 customer 表的访问估算还是很准确的。

customer.filtered=18.08, customer.r_filtered=9.13. 优化器有点高估了`customer` 表所匹配的记录的条数。（一般来说，当你有个全表扫描，并且 r_filtered 少于15%的时候，你得考虑为表增加相应的索引了）

orders.filtered=100, orders.r_filtered=30.03. 优化器无法预估经过条件（orders.o_totalprice > 200*1000）检查后还剩多少比例的记录。因此，优化器显示了100%。事实上，这个值是30%，通常来说30%的过滤性并不值得去建一个索引。但是对于多表Join，采集和使用列统计信息也许对查询有帮助，也可能帮助优化器选择更好的执行计划。（因为在关联中，关联条件和普通过滤条件组合以后，可能过滤性会非常好，并且有助于优化器判断哪张表做驱动表比较好）

然后我们再把前面的例子稍微修改一下

analyze select * 
from orders, customer 
where 
  customer.c_custkey=orders.o_custkey and 
  customer.c_acctbal < -0 and customer.c_comment like '%foo%'
  orders.o_totalprice > 200*1000
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
| id | select_type | table    | type | possible_keys | key         | key_len | ref                | rows   | r_rows | filtered | r_filtered | Extra       |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+
|  1 | SIMPLE      | customer | ALL  | PRIMARY,...   | NULL        | NULL    | NULL               | 149095 | 150000 |    18.08 |       0.00 | Using where |
|  1 | SIMPLE      | orders   | ref  | i_o_custkey   | i_o_custkey | 5       | customer.c_custkey |      7 |   NULL |   100.00 |       NULL | Using where |
+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+

这里我们可以看到 orders.r_rows=NULL，以及 orders.r_filtered=NULL。这意味着 orders 表连一次都没有被扫描到。

TokuDB·　主备复制·Read Free Replication

尽管MySQL 5.6和MariaDB 10.x在replication上已经做了不少优化，TokuDB 7.5也做了一个＂进一步＂的优化：Read Free Replication(RFR)，目的是提高备库(slave)重放速度，减少主备延迟。

RFR的原理比较简单：就是避免一些＂不必要＂的读来减少read IO。

Read IO

当在slave上执行：UPDATE/DELETE操作的时候，MySQL进行read-modify-write操作，可能会产生read IO操作，而INSERT的时候如果需要做＂唯一性＂检查，也可能会产生read IO。

什么条件下，TokuDB才可以read free呢？

1. 主库配置必须BINLOG_FORMAT=ROW
2. 主库上的操作不能违反唯一性约束（比如设置了＂unique_checks=OFF＂，否则主备同步会停止），这样到TokuDB备库的所有log event都默认不需要再做＂unique check＂
3. 备库只读

同时满足以上3个条件，TokuDB的备库认为从主库过来的INSERT/UPDATE/DELETE log event均是安全的，可以不做read，直接做write操作。

从TokuDB官方测试看效果还是不错的，主库~140TPS，备库~70TPS，当开启RFR的时候，备库可以飚到1400TPS，随后与主库基本持平。

进一步的优化还在继续，希望＂妈妈＂(DBA)再也不用担心我(read only slave)的性能了。

TokuDB· 引擎特性·压缩

TokuDB除了有着较好的写性能外，还有一个重要的特性：压缩，而且大部分情况下压缩效果很不错。

目前TokuDB有４种压缩算法：

tokudb_lzma:	压缩比高，CPU消耗也高
tokudb_zlib:	压缩比和CPU消耗持中(默认压缩算法)
tokudb_quicklz:	压缩比低，CPU消耗低
tokudb_uncompressed:无压缩

使用上也比较简单：

MariaDB:
CREATE TABLE `t_quicklz` (
  `x` int(11) DEFAULT NULL,
  `y` int(11) DEFAULT NULL
) ENGINE=TokuDB `compression`='tokudb_quicklz'

MySQL(Percona Server):
CREATE TABLE `t_quicklz` (
  `x` int(11) DEFAULT NULL,
  `y` int(11) DEFAULT NULL
) ENGINE=TokuDB DEFAULT CHARSET=latin1 ROW_FORMAT=TOKUDB_QUICKLZ

可能大家会有一个疑问：如果一个表创建的时候压缩算法是tokudb_quicklz，我可以通过ALERT TABLE改成其他算法吗？答案是：可以的！

TokuDB在底层实现上，用1byte来标记当前block的压缩算法，并持久化到磁盘，当压缩算法改变后，从磁盘读取数据然后解压缩的代码类似：

 switch (block_buffer[0] & 0xF) {
	case TOKU_NO_COMPRESSION:
		...
		break;

	case TOKU_ZLIB_METHOD:
		...
		break;

	case TOKU_QUICKLZ_METHOD:
		...
		break;
	case TOKU_LZMA_METHOD:
		...
		break;
｝

是不是很机智？

在使用TokuDB的过程中，一般不会改变压缩算法，除非默认的tokudb_zlib不给力，TokuDB是大block(4MB)，压缩效果较好，enjoy~