MariaDB10.0亮眼特性详解-创新互联
很多同学一定听说过MariaDB。作为MySQL的重要分支之一，它继续秉承完全开源的姿态（MySQL也有不少好用功能是收费的哟），被很多大型互联网企业广泛使用（如Google、Twitter）。同时，红帽7（包括CentOS）也将默认数据库由MySQL更改为MariaDB。在这种情况下，一定要了解一下这个逆生长的MariaDB。
本文，着重介绍MariaDB 10.0 GA版中的非常吸引人的若干特性，方便大家和MySQL进行比较。
1. Feedback插件--不推荐：
a) 开启方式：my.cnf配置文件的[mysqld]模块，加入feedback=on
b) 作用：会发送使用数据给开发人员，帮助其优化代码。
2. InnoDB和XtraDB转换：
a) MDB默认使用XtraDB。
b) 开启方式：
1） 停止mysql服务。
2） 在my.cnf配置文件的[mysqld]模块，加入ignore_builtin_innodb、
plugin_load=innodb=ha_innodb.so
3. MYSQL客户端报告：
a) 控制参数：global.progress_report_time 值范围要大于5.单位秒。5以下的值被忽略。
b) 关闭的方法：
1） 启动服务时加入--disable-progress-reports
2） 设置global.progress_report_time 值等于0.
c) MariaDB附带的mytop脚本一样也支持此功能
4. SHOW EXPLAIN FOR query_id：Explain命令的变种。
可以在得知QID的情况下查询执行计划。
5. LIMIT ROWS EXAMINED：LIMIT命令的进化版本。
语法：LIMIT [m] ROWS EXAMINED n （m可选）
作用：普通的LIMIT语句在查询到指定数量的分页结果后还会继续执行。如果是大表的话这样的分页操作会消耗过多的资源。
举例：LIMIT 100 ROWS EXAMINED 10000 将从一个含有10000条记录的分页中再次筛选出前100条记录。
6. INSTALL SONAME：安装指定插件、引擎。INSTALL PLUGIN命令变种。
语法：INSTALL SONAME engine_name.
举例：安装BLACKHOLE引擎。
INSTALL SONAME ‘ha_blackhole’;
INSTALL PLUGIN Blackhole SONAME ‘ha_blackhole’;
卸载某插件使用UNINSTALL替换INSTALL
7. 生成HTML/XML文件：
举例：mysql --html/--xml isfdb < isfdb-001.sql > isfdb-001.html/ isfdb-001.xml
会将isfdb-001.sql中的查询语句返回的结果生成相应的html或者xml文件。
8. MYISAM引擎转换为ARIA引擎：
ARIA引擎与MYISAM引擎的主要区别是ARIA引擎自带crash safe功能，在灾难性断电或其他意想不到的表错误导致的表损坏时，可以恢复数据。
使用ALTER TABLE命令转换引擎。
转换引擎操作步骤和MYSQL一致：
a) 首先创建新表
b) 导入数据
c) Rename操作
因此对于千万级的大表来说这一过程将相当漫长。建议不要在生产数据库上进行此操作。
9. 控制MariaDB查询优化器策略：
a) 查看当前查询优化器中的优化策略状态：
SELECT @@optimizer_switch\G
开启或关闭某个优化策略：
如：SET [GLOBAL] optimizer_switch="mrr=on";
或者在my.cnf配置文件中的[mysqld]模块中添加：
[mysqld]
optimizer_switch = "mrr=on, mrr_cost_based=on,mrr_sort_keys=on"
b) 在INNODB和XTRADB上打开优化器extended keys策略：
1） 打开方法如上文所示，参数名为：extended_keys=on
2） 作用：由于优化器是基于成本的（CBO），因此执行计划中会出现有很多索引但是优化器无法使用的查询。打开优化器extended keys策略将会使优化器在出现这一情况时，尽量使用索引来返回，而不是扫描全表。
10. 配置Aria引擎两步死锁监测：
a) 原理：
当Aria引擎无法在表上创建锁时，它首先会依据deadlock_search_depth_short的值为可能出现的死锁创建一个深度搜索等待图（WFG）。当搜索结束后如果还无法创建锁，那么Aria引擎并不急着判断出死锁，而是会等待deadlock_timeout_short定义的微秒后，再搜索一遍。如果还是无法创建锁，那么Aria引擎会使用deadlock_search_depth_long参数的值，创建一个深度搜索等待图。当搜索结束后，如果还没有定义死锁，那么Aria引擎将等待deadlock_timeout_long定义的微秒后，返回超时错误。
b) 查看当前设置：SHOW VARIABLES LIKE ‘deadlock_%’\G
c) 修改参数值，如：
d) 或写入配置文件my.cnf的[mysqld]模块中。
e) 注意：上述timeout参数中的单位是微秒。
11. 配置MYISAM引擎键缓存段（key_cache_segments）：
作用：增强MYISAM引擎表性能。高并发环境中提高MYISAM引擎性能。
a) 原理：
当MYISAM引擎的线程使用键缓存时（key cache），线程将首先获得一把锁。在高并发场景中，大量的线程同时申请锁。对大表或者热点表来说，完整的键缓存会成为性能瓶颈。分割后的键缓存可以减少锁竞争，获得锁的线程只需要锁定相应的键缓存段就可以了，无需锁定整个键缓存。
b) 查看当前设置：SHOW VARIABLES LIKE ‘key_cache_segments’\G
c) 修改参数值：
d) 或写入配置文件my.cnf的[mysqld]模块中。
12. 配置线程池(重中之重啊，核心功能啊)：
MariaDB的线程池提供了更强大的将线程集中使用的功能。并替换了mysql之前的one tread per client connection方法。该方法在典型的web压力场景（数量多但是返回结果小的场景）中对线程的复用效果并不理想。
a) 修改参数值：
在my.cnf中的[mysqld]模块中添加thread_handling = pool-of-threads。
b) 重启服务
c) 作用：
生产场景中出现大量的短查询或者CPUload过高时，线程池是处理这一问题的最好方案。但是在短时间出现的大量长查询时，就不适用了。通常这种情况，linux下可以使用thread_pool_idle_timeout参数来缓解压力。
d) 其他重要的参数：
1） thread_pool_stall_limit：单位毫秒。
用于出现大量长连接查询，该参数控制线程被stall的时间。默认值500。如果查询已经被stall，MDB将创建一个新线程，线程池中的大线程数由thread_pool_max_threads参数控制。默认值500。当线程池中的线程数达到大值后，将不会在创建新线程，即使线程已经被stall。
这一情况可以使用参数extra_port来解决。该参数将打开一个额外的端口，来保持查询进行连接。该参数的值必须和默认端口不同。
2） thread_pool_idle_timeout：单位秒。
定义线程在撤销之前的等待时间。默认值60.
如果发现在某线程被撤销后会定期的创建新的线程，那么应当增加该参数的值。
3） thread_pool_size：LINUX平台定义线程池大小。
13. 配置Aria引擎pagecache：
Aria引擎的PAGE是row format型。它的pagecache由三个参数控制：
a) aria_pagecache_buffer_size：单位byte。默认值128M-512M。
该参数不能动态修改。
b) aria_pagecache_age_threshold：定义在pagecache中的数据块留存长度。
c) aria_pagecache_division_limit：单位百分比。定义pagecache中温数据所占的百分比。
14. 使用子查询缓存（subquery cache）优化查询：MariaDB独占功能！
a) subquery cache极大提升子查询性能。
b) 默认开启.
c) 两个状态值变量：
1） subquery_cache_hit：子查询缓存命中次数
2） subquery_cache_miss：未命中次数
15. 优化半连接子查询（semijoin query）：
半连接子查询中常见的是带where条件的IN子句。这种子句在MYSQL中是很难被优化的。而MDB提供了一个可以开启的优化器参数，指导优化器对这种类型的查询进行优化。
a) 参数：exists_to_in=on。默认不开启。
b) 对EXISTS 型的查询也可以起到优化作用。
c) 修改参数值：
1） SET optimizer_switch='exists_to_in=on';
2） 或者修改配置文件：
16. 创建全文索引(full-text index)--不推荐：
a) full-text index是一个特殊类型的索引,用来搜索基于text类型的列.
b) 只可以为char, varchar, text类型创建全文索引.
c) 全文索引允许我们使用"MATCH() ... AGAINST"语法来查询数据.
语法的MATCH部分包含一个以逗号分隔的待查询columns列表.
语法的AGAINST部分包含需要搜索的字符串也包含一个可选的修饰符来表明所执行查询的类型.
查询类型有:
IN NATURAL LANGUAGE MODE, IN BOOLEAN MODE, WITH QUERY EXPANSION
默认类型为: IN NATURAL LANGUAGE MODE.
17. 开启用户统计：
a) 修改配置文件：
b) 或SET GLOBAL USERSTAT = 1;
c) 开启后可以查看统计数据。如：
SHOW INDEX_STATISTICS;
18. 使用全局事务ID（global transaction IDs）：
更直观的全局事务。比5.6逗比般的GTID更容易理解。
a) 全局事务ID（GTID）是在10.0.2版本中新添加的特性，它将使复制机能更稳定、更灵活。这里需要注意的是：MariaDB和MYSQL 5.6中提到的GTID不能通用，而且差别较大。
b) GTID信息的存放位置在mysql.gtid_slave_pos表，在更新数据的同一事务中更新此表的最新位置信息。
c) GTID的组成：0-1-12345
1） 第一位是domain ID。这是由MariaDB特有的多源复制场景决定的。是32位的无符号整型。单源场景下，该值为0.domain id 的值在my.cnf中设定，如gtid-domain-id = 1。
2） 第二位是server_id。是32位的无符号整型。
3） 第三位是Commit_id。是64位的无符号整型。事务在master提交阶段指定的提交id号，这是一个递增的值，每次提交都不一样，而在group commit中所有被group的事务所指定的commit id都是相同的。
d) 默认开启，查看GTID的值使用show binlog events命令，结果如下所示：
GTID_LIST列中的值就是GTID。
其实值为[]。
查看从库的当前GTID 值，使用命令 SELECT @@global.gtid_slave_pos，主库上返回空值。
查看master的当前gtid值，使用命令 select @@global.gtid_current_pos;
e) 从库采用gtid的复制，语法为：CHANGE MASTER TO master_use_gtid = { slave_pos | current_pos | no }
f) 在一般的复制场景中，从库使用的是slave_pos的值。如A是B的主库，当A挂掉后B充当主库，A重新上线后想要做B的从库时，使用的是current_pos值。因为A之前是主库，没有做过从库，所以不存在slave_pos值。注：没有做过从库的主库没有slave_pos
如果之前的从库没有开启binlog，那么current_pos和slave_pos的值是相等的。
g) 可以使用set global gtid_slave_pos= ‘XXXX’修改slave_pos的值。
查询slave_pos和current_pos：
select @@global.gtid_slave_pos / gtid_current_pos
h) 实测主库在不停写入时崩溃，当主库重新上线后，从库可以根据slave_pos追上主库。
i) 理解current_pos和slave_pos概念：
Current_pos：值由主库操作决定。当机器开启binlog，在执行事务时就会记录该值。使用select @@gtid_current_pos中展示的是所有的pos值。
Slave_pos： 从库上的该值与current_pos一致。使用select @@gtid_current_pos中展示的是本机复制到的GTID。
19. 多源复制：
a) 多源复制中应当注意的要点：
1） 各源my.cnf中应当增加relay-log参数，如：relay_log = db01-relay-binlog。格式中建议增加主机名来区分。
2） 不同源的domain-id一定要不同。否则会出现复制失败。配置domain-id在my.cnf文件中增加gtid-domain-id=n 参数。重启服务。
3） change master 语法中可以增加源主机名来增加区分，如：
Change master [“connect_name”] to
master_host = ‘xxx.xxx.xxx.xxx’,
master_port=3306,
master_user=’replication’,
master_password=’replication’,
master_use_gtid=current_pos;
注：使用源主机名来做change master后，只能只用start all slaves命令来启动复制。其余命令也类似。
4） 启动所有复制：start all slaves;
停止所有复制：stop all slaves;
查看所有连接复制状态：show all slaves status;
查看某连接复制状态：show slave [“connect_name”] status;
清空某连接复制状态：reset slave [“connect_name”];
注意：这里的清空只是将relay_log从1开始重做。不能将复制信息从slave status的展示中清除。
清空某连接复制状态并从slave status展示中删除：
reset slave [“connect_name”] ALL
b) 多源复制的引用场景：
1） 将各主库上的数据整合到一个slave上，方便查询。
2） 将各主库的数据整合到一个slave上，方便备份。
20. 增加基于行复制的binlog注释：
在配置文件中增加binlog_ annotate_row_events参数。会在mysqlbinlog命令查看binlog时，在基于行复制的部分展示SQL语句。
21. 配置binlog事件总和检查器（binlog event checksum）：
a) 作用：用于在文件系统出现故障前快速发现。
b) 参数设置：
也可以通过在my.cnf配置文件中添加参数BINLOG_CHECKSUM开启。
22. 在复制过程中跳过指定的binlog事件：
a) 参数设置：开启。
b) 作用：
在开启功能的这一阶段中处理的所有事务都不会被从库复制。类似的功能还有将binlog功能关闭：set @@sql_log_bin = 0。但是这样的参数设置会停止所有事务记录binlog。Skip_replication参数则不会。
Perry.Zhang
02.18.2016
分享题目：MariaDB10.0亮眼特性详解-创新互联
文章分享：http://cdiso.cn/article/dcjjso.html
