MySQL与MariaDB核心特性比较详细版v1.0（覆盖mysql 8.0/mariadb 10.3，包括优化、功能及维护）

注：本文严禁任何形式的转载，原文使用word编写，为了大家阅读方便，提供pdf版下载。

MySQL与MariaDB主要特性比较详细版v1.0（不含HA）.pdf

链接：https://pan.baidu.com/s/1qAcrxg8eRumRi3FTJtXZxw
提取码：giei

MySQL与MariaDB主要特性核心正式版v1.0.pdf

链接：https://pan.baidu.com/s/1yAKU7GIV4LDQRCvnx7oEnQ
提取码：1d6t 

简介

如果你希望编写的SQL是非常通用的，不使用任何具体平台相关的特性，那么使用MySQL社区版、Percona Server或MariaDB并无本质性差别。所以，通常使用某个分支是有着特定考虑的，它们可能和高可用、SQL语法、亦或性能相关。本文讨论的就是这些差别。

因为percona server是直接fork Mysql社区版的，所以绝大多数服务器本身的特性是一样的（除了线程池、备份、审计、InnoDB增强、及其在PMM中的一些监控工具，这些特性企业版MySQL也都有相应的提供），所以除非特别说明，在本文中针对mysql说明的特性基本上可以无缝适用于percona server，并不专门分开讨论它们。由于实际专业用户几乎不会使用mysql社区版（但是在这里，笔者还是要友情提醒下，mysql社区版的并发性和扩展性是较差的，如果生产系统用的是mysql社区版，应尽快切换为percona server或mariadb，要查看percona server对mysql社区版的增强，可以参考https://learn.percona.com/download-percona-server-8-0-manual），所以大部分测试以percona server为例，但是为了沟通更方便，通常为mysql。

有些读者可能希望现在就知道笔者推荐使用哪个版本及其理由，就当前来说，mysql 5.7和mariadb 10.x之间，笔者建议使用mariadb 10.x。mysql 8.0和mariadb 10.x之间，还没有足够的依据让笔者做出选择，这几个版本，本文都会详细讨论它们的差别。有些读者会说阿里推荐使用percona server，应该来说阿里哪个版本都有在用，对于不同的时间、不同的用户他们会推荐不同的版本，例如阿里云上你可以同时找到MySQL版（https://www.aliyun.com/product/rds/mysql?spm=5176.12145306.1240834.s22.627e5022kzBGRY）和MariaDB版本（https://www.aliyun.com/product/rds/mariadb?spm=5176.7920929.selected.28.6f7741d6nFpaZk）。在此笔者还想补充一句，几年前，对于B端系统，阿里云也开始推荐PostgreSQL了，这确实是更适合的方案，但是这个推荐是在阿里云上线postgresql之后（2015年,阿里云宣布正式推出RDS for PostgreSQL服务），14年笔者在公司论坛的帖子也是这么说的。所以存在即合理，只是应该根据实际情况选择尽可能最满足要求的那个。

限于篇幅，本文只介绍笔者认为比较重要的那些特性，对于可能有差别但是个人认为并不会有很多成本差异的特性或并不太合适在数据库中实现的特性，文本并不做讨论；对于一些对开发或性能并没有那么重要的特性例如MySQL Shell、密钥管理、默认身份认证等亦如是；第三，文本也不会讨论MySQL和MariaDB在实现底层细节上的差异，例如对于子查询中的order by，mysql会照实进行排序，但是mariadb会择机优化去掉排序，它确实对于分析性能有帮助，但不是本文核心。在版本上，笔者主要参考MySQL5.7/8.0和MariaDB 10.3为主，必要的时候也会提及之前的版本。

MySQL分支简介

分支发展史

我们先来看一下MySQL的分支发展史，如下图所示：

 

从上可知，整体而言，mysql和mariadb版本的一般比较如下：

l  10.3-10.4和8.0对应

l  10.1-3和5.7对应

l  10.0-1和5.6对应

mysql 5.7以及之前的大多数特性合并到了mariadb，但是mysql 8.0没有合并（因为MyISAM存储引擎废弃，提升了性能，提高了崩溃安全性，但mariadb并没有兼容（笔者没有细细分析，不排除是因为开源协议的限制，导致mariadb无法使用mysql 8.0的内部数据字典，而只能使用5.7），这导致了文件级别的不兼容性）。

mariadb差不多从mysql 5.1版本的时候衍生出，但是mariadb第一个历史性版本应该算是5.3，该版本引入了很多mysql 5.5不支持的特性，例如子查询半连接优化、ICP、内嵌视图合并、哈希连接等，但是该版本的功能层面特性新增并不多。

目前mysql 8.0和10.4的生产用户应该来说比较少，因为老司机一般根据mysql的套路会选择a.b.20+版本，mariadb则a.b.10+的版本，这两个小版本之后，相对就比较稳定了。所以这两个版本估计广泛被采用还要一段时间，而且因为mysql 8.0之后系统表的存储引擎从MyISAM换成了专有格式，后面MySQL和MariaDB的不一致性会越来越多，用户从这个版本开始分支的选择相比之前版本应该会更加谨慎。

    至于percona server，该分支本身并不提供对mysql功能的完善或加强，更多的是完善可靠性、扩展性、维护性，有些类似于CDH之于apache hadoop生态的关系。

主流维护版本

首先来看mysql和mariadb当前的各自活跃版本。mariadb目前主流的版本为5.5到10.4，mariadb 10.4在6月份刚刚发布GA版本，相比之前MariaDB 10.1-10.3这几个大版本来说，这个版本并没有包含特别多很有价值的新特性，唯一本身很有价值的特性时即时删除列，但是该特性在实际中应该较少被用到，否则就说明设计存在问题（读者有兴趣可以参考https://mariadb.com/kb/en/library/changes-improvements-in-mariadb-104/）。mariadb正常大约一到两个月发布一次。

mysql的主流版本为MySQL 5.6-8.0，大约3个月左右发布一次，5.5已经半年多没有发版了,https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-62.html。

通常来说，MariaDB 的发布频率比 MySQL 更频繁，从上可知，mariadb的发布频率大约比mysql快一倍。一般来说，太高的发布频率既有利也有弊。从好的方面来说，用户可以更及时地收到功能和错误修复。从不好的方面来说，为了让 MariaDB 保持最新的状态，很可能引入更多的bug。但是mariadb有一定的特殊性，因为mariadb的创始人也是Mysql创始人，oracle收购sun之后，他另立门户的目的就是再搞一个高度兼容mysql二进制和SQL接口、可直接替换mysql的分支。所以，它是否符合其他开源产品一样更活跃是为了更快的支持社区发展呢？目前来说确实大多数特性都快mysql一步，且即使mysql先支持的或后支持但有意不采用mariadb当前语法的，maraidb几乎都做了兼容，例如虚拟列特性。否，mariadb也不见的是无私的，它的创始人读者可能都了解，是mysql的创始人。不过至少到现在为止，还看不出mariadb有postgresql和mongodb的套路。

安装方式差别

从5.7版本开始（5.7仍然兼容老的模式），mysql和mariadb在mysql数据库初始化方式上发生了变化，mysql 5.6以及之前的版本采用scripts/mysql_install_db进行数据库实例初始化，从5.7开始，该方式被标记为已过期，推荐采用mysqld --initialize进行初始化。

mariadb则仍然沿用5.6以及之前版本的方式，采用mysql_install_db进行安装，新的mysqld --initialize方式暂不被支持。

MySQL与MariaDB关键特性比较

注：本来笔者的初衷是以计划以10.3为主要版本进行比较的，但是实际编写过程中发现这个比较并不一定很恰当，很多特性在10.2甚至10.1就已经支持，这部分的比例还不小（相对于MySQL 8.0与之前的版本泾渭分明而言）。所以在最后将其标题的10.3去掉，并尽可能的描述了讨论到的特性是哪个版本开始支持的。

mariadb官方比较完整的罗列了oracle mysql和mariadb兼容性、不兼容性以及系统参数上的差异，读者可以参见https://mariadb.com/kb/en/library/compatibility-differences/，但是其更多的是参考手册的性质，并没有对特性做重要性划分。

存储引擎支持

MariaDB 比 MySQL 支持更多的存储引擎类型。但话说回来，数据库可以支持多少个存储引擎并不重要，重要的是哪个数据库可以支持适合你需求的存储引擎。例如关心的是InnoDB及其增强、ColumnStore（mariadb从10.1开始支持，但是稳定比较晚）、Connect、MyRocks。

MariaDB支持的存储引擎包括：XtraDB, InnoDB, MariaDB ColumnStore, Aria, Archive, Blackhole, Cassandra Storage Engine, Connect, CSV, FederatedX, Memory storage engine, Merge, Mroonga, MyISAM, MyRocks, QQGraph, Sequence Storage Engine, SphinxSE, Spider, TokuDB。

注：MariaDB 10.2开始从Percona XtraDB切换回InnoDB，其解释是从Mysql 5.7开始，innodb基本上都包含了xtradb所做的改进，所以没有必要使用xtradb引擎。

MySQL支持的存储引擎包括：InnoDB, MyISAM, Memory, CSV, Archive, Blackhole, Merge, Federated, Example。

percona server新增了存储引擎MyRocks:基于RocksDB（一个通过更好的压缩实现闪存模式下更高性能的数据库），对NVME SSD做了性能优化，Percona鼓励TokuDB用户探索MyRocks存储引擎，它可以为大多数工作负载提供类似的优势，并且可以更好地优化对现代硬件的支持。

 

GTID

mysql 5.6和mariadb 10.0引入了GTID（全局事务ID）特性，它的目的在于使用Gtid的Mysql能够在整个复制环境中能够自动的切换，而不像以前需要指定文件和位置。但是mysql和mariadb的实现并不兼容。mysql的gtid是服务器UUID+序列号组成，mariadb的GTID则是Domain ID+服务器ID+序列号组成。mariadb为了尽可能兼容mysql，实现了对mysql GTID的部分兼容，使得从mysql同步到mariadb的GTID能够被识别，反之不然。就GTID而言，影响比较大的是第三方的同步中间件例如otter/canal，对应用而言影响比较小。

线程池

mysql社区版并不包含线程池特性，如果应用有大量的数据库短连接（例如成百上千连接不停的经常断开、重连），线程池对于保持mysql数据库稳定是有价值的，这也是早期不少用户不选择mysql社区版的主要原因。如果都是长连接或者连接并不是很多，则线程池的价值并没有其所述的那么大。mariadb和percona server都包含了线程池特性。

注：percona server的线程池是基于mariadb的线程池。

Oracle兼容性

在10.3以及之后的版本中，maraidb在原来的基础上，有意增加了对多种数据库语法的兼容，包括SQL_MODE=ORACLE、MSSQL，当设置SQL_MODE为ORACLE时，相当于设置了如下选项：

SET SQL_MODE='PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT';

设置为Oracle模式后，相关存储过程和函数的定义，游标、循环、变量赋值、异常、SQL类型、Begin块这些基本特性就很大程度上兼容了Oracle PL/SQL的常规语法，对于特殊特性例如BUCK COLLECT INTO这些涉及到底层引擎的优化实现则基本上不在范围，完整的支持特性可以参考https://mariadb.com/kb/en/library/sql_modeoracle-from-mariadb-103/。

注：该特性虽然并非没有价值，但是并不建议使用这个模式，为了达到较好的可靠性和性能效果，纯粹为了一些语法糖并不值得让去更换发行版，更何况是分支。

匿名块

从mariadb 10.1.1开始引入了对匿名块的支持，如下所示：

DELIMITER $$

BEGIN NOT ATOMIC

IF 1=1 THEN

     SELECT * FROM assets;

ELSE

     SELECT * FROM t;

END IF;

END$$

item_name        dynamic_cols                    

---------------  ---------------------------------

MariaDB T-shirt 

Thinkpad Laptop 

当SQL_MODE=ORACLE(mariadb 10.3+)的时候，mariadb还支持oracle风格的匿名块。这一特性的价值在于原来在给mysql做升级脚本时，不得不先定义一个存储过程，调用、然后删除，类似如下：

DROP PROCEDURE IF EXISTS sp_db_mysql;

DELIMITER $$

         CREATE PROCEDURE sp_db_mysql()

                   BEGIN

                            declare v_rowcount int;

                            declare database_name VARCHAR(100);

                            select database() into database_name;

                            select count(1) into v_rowcount from information_schema.columns where table_schema= database_name and table_name=’table_name’ and column_name='dict_name';

                            if v_rowcount = 1 then

                                     ALTER TABLE hs_tabase.sys_show_component MODIFY COLUMN dict_name varchar(2000) DEFAULT ' ';

                            end if;

                   END$$

DELIMITER ;

 

call sp_db_mysql();

DROP PROCEDURE IF EXISTS sp_db_mysql;

现在则几乎完全可以和oracle一样处理了，也就是不需要NOT ATOMIC子句，无论维护还是开发、测试都更加的简便。

序列

MariaDB从10.3版本开始增加了对序列的支持。该特性可以解决多个字段无法共用一个序列的问题（注：该特性不要求设置sql_mode=oracle）。

CREATE SEQUENCE s START WITH 100 INCREMENT BY 10; -- 不要求SQL_MODE=ORACLE

SELECT PREVIOUS VALUE FOR s; -- sequence_name.currval oracle模式可以使用oracle序列的语法糖

SELECT NEXT VALUE FOR s; -- sequence_name.nextval oracle模式可以使用oracle序列的语法糖

因为mariadb从10.2版本开始已经实现了持久化ID，所以不需要借助该特性解决mysql自增列清空后又从0开始的问题，见持久性自增ID一节。序列还可以直接用于表的值，如下：

CREATE SEQUENCE s;

CREATE TABLE t(id INT);

INSERT INTO t VALUES(NEXT VALUE FOR s );

INSERT INTO t SELECT NEXT VALUE FOR s FROM t;

 

Oracle风格的动态SQL

mariadb 10.2开始增加了对oracle风格动态sql EXECUTE IMMEDIATE的支持。原来执行动态sql需要prepare，execute，deallocate三步，如下所示：

prepare stmt from "select 1";

execute stmt;

deallocate prepare stmt;

 

使用oracle风格的动态SQL将更加简洁，如下：

EXECUTE IMMEDIATE 'SELECT 1'

 

EXECUTE IMMEDIATE一样支持参数化方式执行，如下：

EXECUTE IMMEDIATE CONCAT('SELECT COUNT(*) FROM ', 't1', ' WHERE a=?') USING 5+5;

列默认值支持表达式和函数

从mariadb 10.2、mysql 8.0开始，支持将表达式和函数作为列的默认值，这个特性还是很有价值的，尤其是对于日期相关的类型来说，有时候因为某些原因需要使用date之外的类型，如果不支持表达式或函数，就比较麻烦了。如下所示：

CREATE TABLE tx (a varchar(32) DEFAULT (DATE_FORMAT(now(),"%Y-%m-%d %T")), b int DEFAULT 1);

insert into tx (b) values (2);

select * from tx;

参见：https://mariadb.com/kb/en/library/create-table/#default-column-option

10.3开始支持序列作为默认值一部分，如下所示：

ALTER TABLE Observation MODIFY Id int(11) NOT NULL DEFAULT NEXT VALUE FOR Seq1_1;

CHECK约束

在mariadb 10.2以及mysql 8.0之前，check约束仅仅是语法上不报错，并未真正生效，从这两个版本开始，check约束真正生效。如下所示：

create table t1 (a int check(a>0) ,b int check (b> 0), constraint abc check (a>b));

insert into t1 VALUES(1,-1);

在mariadb下执行会报错误” [Err] 4025 - CONSTRAINT `t1.b` failed for `hs_tabase`.`t1`”，mysql则报” [Err] 3819 - Check constraint 't1_chk_2' is violated.(mysql)”。

持久性自增ID

mysql> create table t1(id int auto_increment primary key);

Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into t1 values(null),(null),(null);

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

 

mysql> select * from t1;

+----+

| id |

+----+

| 1 |

| 2 |

| 3 |

+----+

rows in set (0.00 sec)

 

mysql> delete from t1 where id=3;

Query OK, 1 row affected (0.36 sec)

 

mysql> insert into t1 values(null);

Query OK, 1 row affected (0.35 sec)

 

mysql> select * from t1;

+----+

| id |

+----+

| 1 |

| 2 |

| 4 |

+----+

rows in set (0.01 sec)

mysql> delete from t1 where id=4;

# service mysqld restart

mysql> insert into t1 values(null);

Query OK, 1 row affected (0.00 sec)

 此时，在mysql 5.7以及之前的版本中，插入的值是3。在mysql 8.0版本中，插入的值是5。

mysql> select * from t1;

+----+

| id |

+----+

| 1 |

| 2 |

| 3 |

+----+

rows in set (0.00 sec)

mariadb在10.2.4静悄悄的实现了该特性，因此该值也是5。https://jira.mariadb.org/browse/MDEV-6076

DDL WAIT/NOWAIT子句

默认情况下，DML语句是阻塞的，也就是当DML操作的记录正在被其它会话更新时，当前会话会一直等待，直到超过lock_wait_timeout和innodb_lock_wait_timeout时间。有时候全局控制并不足够灵活，在mariadb 10.3开始，Mariadb引入了wait N/nowait子句，允许用户更精确地控制具体语句等待的时间，默认和原来一样，等待模式。

A会话

SET autocommit=off;

SELECT * FROM t FOR UPDATE;

B会话

SET autocommit=off;

SELECT * FROM t FOR UPDATE nowait;

默认情况下，也就是不带NOWAIT子句的时候，B会话会一直等待。

C会话

SET autocommit=off;

SELECT * FROM t FOR UPDATE nowait;

错误代码： 1205

Lock wait timeout exceeded; try restarting transaction

除了NOWAIT，也支持等待N秒。如下：

SELECT * FROM t FOR UPDATE wait 3;

到目前为止，mysql并没有支持该特性。

完整的语法可以参考https://mariadb.com/kb/en/library/wait-and-nowait/

持久化更改参数SET PERSIST

mysql 8.0为了修改系统参数引入了一个额外的选项，用于控制被修改系统参数的生效时间，只要是全局可修改且非只读的变量都可以使用PERSIST_ONLY/PERSIST选项控制修改范围，后者是前者和GLOBAL的组合。类似于oracle的alter system set var=value scope=memory|both|spfile。

SET PERSIST操作在performance_schema.persisted_variables表中读取和设置持久变量列表。

SET PERSIST_ONLY max_connections=100;

SELECT * FROM `persisted_variables`;

持久化变量在datadir中存储在mysqld-auto.cnf中，其使用JSON格式存储。它包含的信息不仅仅是持久值，还包括诸如谁做出改变以及何时做出改变等信息。示例文件是：

shell$ cat mysqld-auto.cnf

{

    "Version": 1,

    "mysql_server": {

        "sort_buffer_size": {

            "Value": "32768",

            "Metadata": {

                "Timestamp": 1534230053297668,

                "User": "root",

                "Host": "localhost"

            }

        },

        "join_buffer_size": {

            "Value": "131072",

            "Metadata": {

                "Timestamp": 1534230072956789,

                "User": "root",

                "Host": "localhost"

            }

        },

        "mysql_server_static_options": {

            "slave_parallel_type": {

                "Value": "LOGICAL_CLOCK",

                "Metadata": {

                    "Timestamp": 1534230099583642,

                    "User": "root",

                    "Host": "localhost"

                }

            }

        }

    }

}

截止mariadb 10.4，该特性尚未被支持，但是已经包含在支持计划中（https://jira.mariadb.org/browse/MDEV-16228）。

动态列

MariaDB早在5.3就引入动态列特性，主要是为了支持一定程度上的NoSQL，只不过那会儿没有使用JSON类型实现。该特性使得用户能够不更改表结构的情况下动态增加或删除列，相比JSON类型而言，它更加的严格，而且尤其是在产品化系统中，这样我们就可以只升级脚本、不强求客户升级表结构，同时保证不会出现“找不到列“的运行时错误。如下所示：

mariadb> create table assets (

item_name varchar(32) primary key, -- A common attribute for all items

dynamic_cols blob -- Dynamic columns will be stored here

);

Query OK, 0 rows affected

 

mariadb> INSERT INTO assets VALUES

('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL'));

 

Query OK, 1 row affected

 

mariadb> INSERT INTO assets VALUES

('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500));

 

Query OK, 1 row affected

 

mariadb> SELECT item_name, column_list(dynamic_cols) FROM assets;

 

+-----------------+---------------------------+

| item_name | column_list(dynamic_cols) |

+-----------------+---------------------------+

| MariaDB T-shirt | `size`,`color` |

| Thinkpad Laptop | `color`,`price` |

+-----------------+---------------------------+

2 rows in set

 

mariadb> SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets;

+-----------------+-------+

| item_name | color |

+-----------------+-------+

| MariaDB T-shirt | blue |

| Thinkpad Laptop | black |

+-----------------+-------+

2 rows in set

 

mariadb> SELECT item_name,

dynamic_cols AS color FROM assets;

+-----------------+-------------------------------+

| item_name | color |

+-----------------+-------------------------------+

| MariaDB T-shirt |

| Thinkpad Laptop |

+-----------------+-------------------------------+

2 rows in set

 

mariadb> SELECT item_name, COLUMN_JSON(dynamic_cols) FROM assets;

+-----------------+-------------------------------+

| item_name | COLUMN_JSON(dynamic_cols) |

+-----------------+-------------------------------+

| MariaDB T-shirt | {"size":"XL","color":"blue"} |

| Thinkpad Laptop | {"color":"black","price":500} |

+-----------------+-------------------------------+

2 rows in set

 

mariadb> SELECT item_name, IFNULL(COLUMN_GET(dynamic_cols, 'xx' as char),'1') AS color FROM assets;

+-----------------+-------+

| item_name       | color |

+-----------------+-------+

| MariaDB T-shirt | 1     |

| Thinkpad Laptop | 1     |

+-----------------+-------+

2 rows in set

动态列配套的相关函数如下：

COLUMN_CREATE

COLUMN_ADD

COLUMN_GET

COLUMN_DELETE

COLUMN_EXISTS

COLUMN_LIST

COLUMN_CHECK

COLUMN_JSON

更多使用说明可以参考https://mariadb.com/kb/en/library/dynamic-columns/。

JSON类型

在现在很多高负载的应用中，都不推荐将很大的文本例如文章的正文存储在关系型数据库中，相反建议存储在NoSQL如MongoDB或ES中，但是很多应用并没有那么高的负载，而是仅仅为了希望模式更加松散（Schemaless），因此关系型数据库对JSON类型的支持仍然是很有价值的。

从 5.7 版本开始，MySQL 支持由 RFC 7159 定义的原生 JSON 数据类型，可以高效地访问 JSON 文档中的数据。

MariaDB 没有提供这一增强功能，认为 JSON 数据类型不是 SQL 标准的一部分。但为了支持从 MySQL 复制数据，MariaDB 从10.2开始为 JSON 定义了一个别名，实际上就是一个 LONGTEXT 列，如下所示。MariaDB 声称两者之间没有显著的性能差异，但他们并没有提供基准测试数据来支持这个说法。

CREATE TABLE t (j JSON);

DESC t;

+-------+----------+------+-----+---------+-------+

| Field | Type     | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| j     | longtext | YES  |     | NULL    |       |

+-------+----------+------+-----+---------+-------+

CREATE TABLE t2 (

  j JSON

  CHECK (JSON_VALID(j))

);

 

INSERT INTO t2 VALUES ('invalid');

ERROR 4025 (23000): CONSTRAINT `j` failed for `test`.`t2`

 

INSERT INTO t2 VALUES ('{"id": 1, "name": "Monty"}');

Query OK, 1 row affected (0.13 sec)

就MySQL/MariaDB而言，除了提供更加松散的模型外，还能够用来变相实现存储过程的可变参数支持。可能是由于MySQL/MariaDB的主要开发者本身就认为不应该广泛使用存储过程的原因，可变参数这个特性社区提过多次申请，无论是mysql还是Mariadb都没有计划实现，这样当存储过程新增了一个参数后，当前的调用都需要修改，这样就为造成高昂的维护成本，而oracle就不存在这个问题。借助json类型的参数，就可以变通实现可变参数的特性。

在 JSON 相关函数上，mariadb和mysql都提供了一些用于更方便地访问、解析和检索 JSON 数据的支持函数，虽然它们的底层实现不同，但是大部分函数都相同。为节省篇幅，这里就不一一列举，读者可以参考下面的链接：

l  https://mariadb.com/kb/en/library/json-functions/

l  https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html

不可见列

不可见列特性是mariadb 10.3引入的，其行为是在默认的select *中不会包含不可见列，在insert table values()中插入时，也不需要为其赋值。该特性使得在编写了通用的查询后，升级能够无缝进行。如下所示：

CREATE TABLE t (x INT, y INT INVISIBLE, z INT INVISIBLE NOT NULL DEFAULT 4);

INSERT INTO t VALUES (1),(2);

INSERT INTO t (x,y) VALUES (3,33);

SELECT * FROM t;

+------+

| x    |

+------+

|    1 |

|    2 |

|    3 |

+------+

 

SELECT x,y,z FROM t;

+------+------+---+

| x    | y    | z |

+------+------+---+

|    1 | NULL | 4 |

|    2 | NULL | 4 |

|    3 |   33 | 4 |

+------+------+---+

 

DESC t;

+-------+---------+------+-----+---------+-----------+

| Field | Type    | Null | Key | Default | Extra     |

+-------+---------+------+-----+---------+-----------+

| x     | int(11) | YES  |     | NULL    |           |

| y     | int(11) | YES  |     | NULL    | INVISIBLE |

| z     | int(11) | NO   |     | 4       | INVISIBLE |

+-------+---------+------+-----+---------+-----------+

 

ALTER TABLE t MODIFY x INT INVISIBLE, MODIFY y INT, MODIFY z INT NOT NULL DEFAULT 4;

 

DESC t;

+-------+---------+------+-----+---------+-----------+

| Field | Type    | Null | Key | Default | Extra     |

+-------+---------+------+-----+---------+-----------+

| x     | int(11) | YES  |     | NULL    | INVISIBLE |

| y     | int(11) | YES  |     | NULL    |           |

| z     | int(11) | NO   |     | 4       |           |

+-------+---------+------+-----+---------+-----------+

创建视图的话也是一样的行为，如下：

CREATE VIEW v1 AS SELECT * FROM t;

DESC v1;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| x     | int(11) | YES  |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

 

CREATE VIEW v2 AS SELECT x,y,z FROM t;

 

DESC v2;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| x     | int(11) | YES  |     | NULL    |       |

| y     | int(11) | YES  |     | NULL    |       |

| z     | int(11) | NO   |     | 4       |       |

+-------+---------+------+-----+---------+-------+

 

mariadb> CREATE TABLE t (x INT, y INT INVISIBLE, z INT INVISIBLE NOT NULL DEFAULT 4);

 

INSERT INTO t VALUES (1),(2);

INSERT INTO t (x,y) VALUES (3,33);

 

SELECT * FROM t;

+---+

| x |

+---+

| 1 |

| 2 |

| 3 |

+---+

3 rows in set

截止到mysql 8.0，尚无支持计划（PS：oracle 12c也支持该特性）。

虚拟列

mariadb从10.0开始支持虚拟列特性，其功能上和oracle虚拟列类似。从mariadb 10.2开始，也兼容mysql的虚拟列定义（mysql的官方子句时PERSISTENT，mariadb为STORED）。

虚拟列索引

提到了虚拟列就不得不提索引对虚拟列的支持情况。在10.2.2以及之前版本，mariadb不支持在VIRTUAL列上创建索引，从MariaDB 10.2.3开始，同时支持VIRTUAL和PERSISTENT上创建索引。mysql 5.7则不然，除非索引列是主键的一部分，否则并不需要虚拟列必须为持久化。

不可见索引

mysql 8.0支持不可见索引，mariadb不支持将index设置为invisible，会导致优化器在选择执行计划时，自动忽略该索引，即便使用了FORCE INDEX。

当然，这个是由optimizer_switch变量中use_invisible_indexes选项决定的，默认为off。如果想看一个查询在索引调整前后执行计划的差别，可在会话级别调整use_invisible_indexes的值。

set session optimizer_switch="use_invisible_indexes=on";

 

DROP TABLE t1;

CREATE TABLE t1(id INT PRIMARY KEY,NAME VARCHAR(10),INDEX idx_name (NAME) invisible);

SELECT table_schema,table_name,index_name,column_name,is_visible FROM information_schema.statistics WHERE is_visible='no';

ALTER TABLE t1 ALTER INDEX idx_name visible;

SELECT table_schema,table_name,index_name,column_name,is_visible FROM information_schema.statistics WHERE is_visible='no';

ALTER TABLE t1 ALTER INDEX idx_name invisible;

截止目前，尚无看到MariaDB支持该特性的计划。

分析函数

使用分析函数，开发人员可以通过更清晰、简洁的 SQL 代码执行复杂分析。原来需要几十行甚至上百行代码完成的逻辑现在可以使用一条 SQL 语句表示复杂任务，编写和维护速度更快、效率更高。数据库中分析支持的处理优化可大幅提高查询性能。以前需要自联接或复杂过程处理的操作现在可以用原生 SQL 执行。以分组排序为例，如果有下列表：

mysql> select * from rank_over;
+------+-------+---------------------+
| id  | subid | curd                |
+------+-------+---------------------+
|    1 |    1 | 2018-09-24 00:47:12 |
|    2 |    1 | 2018-09-24 00:47:38 |
|    3 |    1 | 2018-09-24 00:47:42 |
|    4 |    2 | 2018-09-24 00:47:50 |
|    5 |    2 | 2018-09-24 00:47:54 |
|    6 |    3 | 2018-09-24 00:48:00 |
|    7 |    4 | 2018-09-24 00:48:06 |
|    8 |    3 | 2018-09-24 01:12:10 |
|    9 |    2 | 2018-09-24 01:12:11 |
+------+-------+---------------------+

现在要取出每个subid下curd最大的1条。

使用分析函数只需要很简单的SQL：

select t.id,t.subid,t.curd 
from(SELECT id,subid,curd,RANK() OVER(PARTITION BY subid ORDER BY curd DESC) RK
   FROM rank_over) t
where t.RK<2

如果没有分析函数，则要复杂得多，如下：

select t1.*  from 
(select (@rowNum1:=@rowNum1+1) as rowNo,id,subid,curd from rank_over a,(Select (@rowNum1 :=0)) b order by a.subid,a.curd desc) t1 left join
(select (@rowNum2:=@rowNum2+1) as rowNo,id,subid,curd from rank_over c,(Select (@rowNum2 :=1)) d order by c.subid,c.curd desc) t2 on t1.rowNo=t2.rowNO
where t1.subid<>t2.subid or t2.subid is null

它们的结果都是：

+-------+------+-------+---------------------+
| rowNo | id  | subid | curd                |
+-------+------+-------+---------------------+
|    1 |    3 |    1 | 2018-09-24 00:47:42 |
|    4 |    9 |    2 | 2018-09-24 01:12:11 |
|    7 |    8 |    3 | 2018-09-24 01:12:10 |
|    9 |    7 |    4 | 2018-09-24 00:48:06 |
+-------+------+-------+---------------------+
4 rows in set (0.00 sec)

分析函数不仅用于提高开发效率，而且数据库优化器通常会对分析函数的执行进行优化，典型的即是避免了对基表的二次扫描。下面梳理了mariadb和mysql分别支持的分析函数，可以发现Mariadb支持的分析函数要比mysql更加丰富。

mariadb 10.2.0引入	mysql 8.0引入，5.7不支持
CUME_DIST	CUME_DIST()
DENSE_RANK	DENSE_RANK()
FIRST_VALUE	FIRST_VALUE()
LAG	LAG()
LAST_VALUE	LAST_VALUE()
LEAD	LEAD()
MEDIAN
NTH_VALUE	NTH_VALUE()
NTILE	NTILE()
PERCENT_RANK	PERCENT_RANK()
PERCENTILE_CONT
PERCENTILE_DISC
RANK, ROW_NUMBER	RANK,ROW_NUMBER()
AVG
BIT_AND
BIT_OR
BIT_XOR
COUNT
MAX
MIN
STD
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE

在mysql中，分析函数的执行计划并不体现在常规的explain输出中，要查看关于分析函数执行计划相关的信息，需要使用 EXPLAIN FORMAT=JSON 模式，然后查看其中windowing的部分。

递归CTE实现层次查询

MariaDB从10.2.1开始支持WITH子句，10.2.2开始支持递归WITH。mysql则从8.0开始CTE。

CTE除了方便编写递归查询外，还有一个更重要的价值，mysql/mariadb的CTE本质上都是先创建临时表（mysql/mariadb的cte都支持merge，mariadb还支持pushdown，mysql不支持，但是大部分情况下都会先物化），所以一般来说更加符合实际的预期，而普通的内嵌视图有可能会被优化器选择与主查询合并，进而导致执行计划欠佳（注：在mysql 8.0中，可以使用MERGE/NO_MERGE优化器提示控制子查询是否合并到主查询）。

很多时候我们通常仅在SELECT中使用WITH子句，实际上，WITH可以出现在很多上下文中，下面列出了WITH可以所处的上下文。

WITH ... SELECT ...

WITH ... UPDATE ...

WITH ... DELETE ...

SELECT ... WHERE id IN (WITH ... SELECT ...) ...

SELECT * FROM (WITH ... SELECT ...) AS dt ...

INSERT ... WITH ... SELECT ...

REPLACE ... WITH ... SELECT ...

CREATE TABLE ... WITH ... SELECT ...

CREATE VIEW ... WITH ... SELECT ...

DECLARE CURSOR ... WITH ... SELECT ...

EXPLAIN ... WITH ... SELECT ...

WITH [RECURSIVE] table_reference as (SELECT ...) SELECT ...

 

WITH还可以相互嵌套，如下：

WITH

engineers AS ( SELECT * FROM employees WHERE dept IN('Development','Support') ),

eu_engineers AS ( SELECT * FROM engineers WHERE country IN('NL',...) )

SELECT ... FROM eu_engineers;

 

使用CTE递归查询，就可以和在oracle中一样快速造测试数据了。

CREATE TABLE my_big_table

WITH  RECURSIVE cte

AS (SELECT 1 AS n-- anchor member

        UNION ALL

        SELECT n + 1 -- recursive member

        FROM   cte

        WHERE  n < 500000 -- terminator

       )

SELECT n,CONCAT('last-name',n) last_name FROM cte;

 

SELECT COUNT(1) FROM my_big_table;

如果是mysql 8.0，需要设置cte_max_recursion_depth参数足够大，例如1000000，否则会报” Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.”

集合操作符

MariaDB从10.3开始在UNION基础上增加了对EXCEPT和INTERSECT的支持，分别用于两个结果集取差集和交集。例如：

CREATE TABLE seqs (i INT);

INSERT INTO seqs VALUES (1),(2),(3),(4),(5),(6);

SELECT i FROM seqs WHERE i <= 3 INTERSECT SELECT i FROM seqs WHERE i>=3;

+------+

| i    |

+------+

|    3 |

+------+

因为mysql不支持这两个操作，所以mysql中会报语法错误。

10.4开始还支持对集合操作符声明优先级，如下：

((SELECT a FROM t1) UNION (SELECT b FROM t2)) INTERSECT (SELECT c FROM t3);

分区

都知道，在单表数据量巨大时有效采用分区能够极大的提高SQL语句的性能（但是也需要注意的是，因为mysql本质上实现了oracle分区本地索引的概念，所以对非唯一索引搜索性能相比非分区而言会降低，https://zhuanlan.zhihu.com/p/28703566），同时降低维护复杂性。mysql从5.1开始就引入分区功能，在随后版本中功能增加并不多，但是在5.7.17开始采用了存储引擎自带的分区处理而不是作为插件来支持。

相比其它特性来说，mariadb对分区的支持远不如mysql，不支持hash分区，对组合分区的支持相比mysql也比较弱。下表总结了mysql和mariadb分别支持的分区类型。

mysql	mariadb	描述
HASH	不支持
KEY（hash的衍生版本，表达式由MySQL服务器自己决定）	不支持
组合分区 RANGE|LIST/HASH|KEY（mysql 5.7）	不支持
LIST	LIST	支持表达式作为分区键（相当于虚拟列）
RANGE	RANGE	支持表达式作为分区键（相当于虚拟列）
COLUMNS（mysql中合并称为COLUMNS）	RANGE COLUMNS	多列范围分区，相当于RANGE/RANGE组合分区
	LIST COLUMNS	相当于LIST/LIST组合分区

和oracle中一样，分区在很多偏向于OLTP的SQL语句中通常是会降低性能的，这里给出一个分区对性能影响测试的链接。http://www.nilinfobin.com/mysql/performance-of-inserts-on-partitions-mysql-5-6-vs-mysql-5-7/

系统版本表

系统版本表，也成为Temporal tables表，是SQL 2011中增加的规范。他可以说是真正意义上的闪回特性。例如：

CREATE TABLE t (

   x INT

) WITH SYSTEM VERSIONING;

insert into t values(1),(2),(3);

insert into t values(4),(5),(6);

 

select now();

2018-10-23 11:58:54

select * from t;

delete from t; 

select * from t;   --此时默认查不到记录了

SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP '2018-10-23 11:58:54'

 

历史快照查到了。和oracle一样，还支持历史版本查询，如下：

SELECT * FROM t FOR SYSTEM_TIME FROM '2016-01-01 00:00:00' TO '2017-01-01 00:00:00';

在Mariadb中，其内部是基于不可见列实现的。

闪回

既然讲到了系统版本表，就不得不提一下maraidb的闪回。mariadb 在10.2.4引入闪回特性，支持DML（INSERT, DELETE, UPDATE）操作的闪回，不支持DDL语句，使用闪回，必须设置binlog_row_image=FULL。

其原理和oracle有undo不一样，将INSERT重写为DELETE, DELETE重写为INSERT, UPDATE根据前后值进行交换，这也是必须设置binlog_row_image=FULL的原因。

mysqlbinlog默认情况下会生成重做SQL，通过使用新增的"--flashback"选项，可以生成自某个SCN或者时间点以来的反向SQL。看如下对比：

mysqlbinlog /var/lib/mysql/mysql-bin.000001 -vv -d test -T mytable --start-datetime="2013-03-27 14:54:00" > review.sql

mysqlbinlog /var/lib/mysql/mysql-bin.000001 -vv -d test -T mytable --start-datetime="2013-03-27 14:54:00" --flashback > flashback.sql

执行之后，就可以通过执行mysql < flashback.sql将所有变更操作还原了。

实际上受制于mysql的体系架构，它准确的说算不上真正的闪回，随便一个高级开发换点时间慢慢研究都可以做出来，它还不支持查询。

除此之外，它还需要访问到mysql的binlog，这也是个比较困难的事，因为运维体系可能不允许用户直接访问mysql服务器，如果是阿里云的RDS，就更是如此了。

对于时间点恢复这个事情，还有一种典型的做法是依赖于从库，通过延迟复制的方式实现，这种方式用于实现OLTP或者误操作是可以的，但是把它作为一个撤销操作的机制就比较强人所难了，需要人工干预的侵入性太强了。除非不得已，我们不会选择这种实现方式，太脆弱了。

 

关于系统版本表和闪回的示例和使用模式参见：https://www.cnblogs.com/zhjh256/p/9836047.html

 

PCRE正则表达式

mariadb从10.0开始采用PCRE正则表达式，包括普通的模式匹配、替换、子串截取等，mysql虽然从5.5就已经支持正则表达式，但是在mysql 8.0之前仅仅支持最简单的三个模式匹配，相比mariadb而言更弱。下面列出了Mysql和mariadb分别支持的正则函数和操作符。

Mysql	mariadb
NOT REGEXP	NOT REGEXP
REGEXP	REGEXP
REGEXP_INSTR()（8.0）	REGEXP_INSTR()
REGEXP_LIKE()（8.0）	无
REGEXP_REPLACE()（8.0）	REGEXP_REPLACE()
REGEXP_SUBSTR()（8.0）	REGEXP_SUBSTR()
RLIKE	RLIKE

注：虽然我们在应用中广泛的使用正则表达式，但是一般不推荐在数据库中使用正则表达式匹配和其它相关操作。

TEXT类型默认值

mariadb允许为TEXT类型的字段设置默认值，如下：

mysql> alter table cmp1 add column id4 text default '1';

1101 - BLOB, TEXT, GEOMETRY or JSON column 'id4' can't have a default value

 

mariadb> alter table t add column id4 text default '1';

Query OK, 0 rows affected

Records: 0  Duplicates: 0  Warnings: 0

性能相关

近年来，出现了很多关于 MySQL 和 MariaDB 引擎性能的基准测试，每个厂商都会说自己比其它性能要好。所以“MySQL 或 MariaDB 哪个更快”这个问题不会有一个最终的答案，它在很大程度上取决于具体的使用场景、查询、用户和连接数量等因素。

不过，如果你确实想知道，下面列出了一些基准测试结果。请注意，这些测试都是在一组特定的数据库 + 引擎+版本（例如 MySQL 5.7.4+InnoDB+10.1.3）组合上进行的，因此得出的结论只与特定的组合有关。

mysql 8.0官方的性能测试：https://www.mysql.com/why-mysql/benchmarks/

mariadb官方10.1和mysql 5.7的性能测试对比：https://mariadb.org/performance-evaluation-of-mariadb-10-1-and-mysql-5-7-4-labs-tplc/

percona server官方的percona server与mysql社区版性能测试对比：https://www.percona.com/blog/2013/10/08/a-closer-look-at-percona-server-5-6/

三方测试中，简单的基准测试mysql普遍优于mariadb（因为mariadb的优化器更加复杂）。

不过有一点可以确定的是，percona server无论性能还是稳定性肯定比对应的mysql社区版要表现更好。

也有测试反应在80/20、且数据较为稳定的场景下，maraidb在启用查询缓存时性能优于mysql，反之mysql更优：https://www.softizy.com/blog/mariadb-10-1-mysql-5-7-performances-ibm-power-8/

         所以哪个性能更好取决于你想测试哪种场景下的表现，仅仅因为A比B高20%而不考虑其他因素是有失偏颇的。

参数差异

mariadb和mysql在核心参数上的差异并不大，毕竟很大一部分核心是基于mysql的，但是在很多默认值以及xtradb相关的参数上它们并不相同。mariadb官网https://mariadb.com/kb/en/library/system-variable-differences-between-mariadb-and-mysql/详细列出了mysql和mariadb每个版本参数不一致的地方，这里不一一列举。这里仅列出几个相对比较重要的参数。

空闲事务超时时间

percona server 5.5引入了参数innodb_kill_idle_transaction用于控制空闲XtraDB事务的超时时间。因为该特性是xtradb特性，所以从10.2开始该参数不再起作用。从mariadb 10.3开始，重新引入了几个参数控制空闲事务的超时时间，分别是：

l  idle_transaction_timeout：控制所有事务的超时时间

l  idle_readonly_transaction_timeout：控制只读事务的超时时间

l  idle_write_transaction_timeout：控制写事务的超时时间

超时之后，会话会报ERROR 2006 (HY000): MySQL server has gone away，建议开发、测试环境开启以便提前发现bug。

全局innodb日志刷新控制

mysql参数innodb_flush_log_at_trx_commit控制 innodb日志合适刷新到磁盘，1：每次提交时写入文件并执行刷新；0：日志每秒刷新一次；2：日志每次提交时写入，但是刷新每秒执行一次，它和0的区别在于，0只要mysql宕机就可能丢失最多1秒的事务，2则是主机宕机可能丢失1秒事务。在标准的mysql中，该参数不支持会话级修改，percona server引入了参数innodb_use_global_flush_log_at_trx_commit控制是否启用会话级修改，当为1时，会话可以修改innodb_flush_log_at_trx_commit的值。这在一些包含交易但是量不大的系统中很重要，它使得对不同应用采用不同的ACID严格程度。因为从Mariadb 10.2开始，innodb从xtradb切换回了mysql innodb，因此XtraDB实现相关的参数不再被支持，所以该参数也无法再在mariadb中使用。

用户统计信息

用户统计信息会最早是mariadb 5.2（percona server也支持）引入的，由userstat参数控制，它会在INFORMATION_SCHEMA数据库中创建CLIENT_STATISTICS/INDEX_STATISTICS/TABLE_STATISTICS/USER_STATISTICS表，分别从表、索引、客户端、用户层面分析使用情况，该特性使得用户能更多的了解数据库的使用模式。该特性默认不启用，需要设置userstat = 1进行启用。如下所示：

mariadb> show variables like '%userstat%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| userstat      | ON    |

+---------------+-------+

1 row in set

 

mariadb> select * from information_schema.TABLE_STATISTICS;

+--------------+------------+-----------+--------------+------------------------+

| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |

+--------------+------------+-----------+--------------+------------------------+

| yidoo        | ebk_ebook  |      4202 |            0 |                      0 |

| mysql        | proc       |         6 |            0 |                      0 |

+--------------+------------+-----------+--------------+------------------------+

2 rows in set

 

mariadb> select * from information_schema.INDEX_STATISTICS;

+--------------+------------+------------+-----------+

| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |

+--------------+------------+------------+-----------+

| mysql        | proc       | PRIMARY    |         6 |

+--------------+------------+------------+-----------+

1 row in set

 

即时加字段INSTANT ADD COLUMN

mysql 8.0和mariadb 10.3均支持即时加字段，该特性的实现原理是对于新增的字段，它对于为大表新增字段特别有价值，原来为一张数千万的表新增字段可能需要10多分钟，现在瞬间就可以完成，如下所示：

-- mariadb 10.3

alter table cmp1 add column id2 text default '1';

受影响的行: 0

时间: 0.004s

 

update cmp1 set id2 = i;

受影响的行: 1048576

时间: 18.394s

 

alter table cmp1 drop column id2;

受影响的行: 0

时间: 2.348s

 

-- mariadb 10.2

CREATE TABLE `cmp1` (

`i` varchar(128) /*!100301 COMPRESSED*/ COLLATE gbk_bin DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_bin;

 

insert into cmp1 values('3isfuues');

insert into cmp1 select * from cmp1; -- 插入100万行

alter table cmp1 add column id2 text;

受影响的行: 0

时间: 2.911s

 

update cmp1 set id2 = i;

受影响的行: 1048576

时间: 51.435s

 

alter table cmp1 drop column id2;

受影响的行: 0

时间: 3.530s

 

查询：alter table cmp1 add column id2 text -- mysql 5.7

共 0 行受到影响

执行耗时 : 6.317 sec

传送时间 : 1.765 sec

总耗时 : 8.083 sec

alter table cmp1 drop column id2

共 0 行受到影响

执行耗时 : 4.764 sec

传送时间 : 1.764 sec

总耗时 : 6.529 sec

在这一点上，mariadb/mysql比oracle做得更好，oracle对于新增包含默认值的字段，仍然是采用即时更新的实现方式。

表连接方式

虽然mariadb从5.3版本开始在表连接之间增加了哈希连接，但是mariadb的哈希连接实现并没有从头开始实现，而是和嵌套循环连接使用了相同的前半部分，即所有的处理都是基于块循环，而不像oracle只要PGA足够就一次性将整个驱动表创建为哈希表，然后循环一遍关联表即可。这种实现方式的结果就是性能提升比较难以做到数量级提升。

create table big_table_a as select * from columns; -- 插入506240行

create table big_table_b as select * from columns; -- 插入506240行

create index idx_big_table_a on big_table_a(table_name,column_name);

create index idx_big_table_b on big_table_b(table_name,column_name);

mariadb> desc big_table_a;

+--------------------------+---------------------+------+-----+---------+-------+

| Field                    | Type                | Null | Key | Default | Extra |

+--------------------------+---------------------+------+-----+---------+-------+

| TABLE_CATALOG            | varchar(512)        | NO   |     |         |       |

| TABLE_SCHEMA             | varchar(64)         | NO   |     |         |       |

| TABLE_NAME               | varchar(64)         | NO   | MUL |         |       |

| COLUMN_NAME              | varchar(64)         | NO   |     |         |       |

| ORDINAL_POSITION         | bigint(21) unsigned | NO   |     | 0       |       |

| COLUMN_DEFAULT           | longtext            | YES  |     | NULL    |       |

| IS_NULLABLE              | varchar(3)          | NO   |     |         |       |

| DATA_TYPE                | varchar(64)         | NO   |     |         |       |

| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |

| CHARACTER_OCTET_LENGTH   | bigint(21) unsigned | YES  |     | NULL    |       |

| NUMERIC_PRECISION        | bigint(21) unsigned | YES  |     | NULL    |       |

| NUMERIC_SCALE            | bigint(21) unsigned | YES  |     | NULL    |       |

| DATETIME_PRECISION       | bigint(21) unsigned | YES  |     | NULL    |       |

| CHARACTER_SET_NAME       | varchar(32)         | YES  |     | NULL    |       |

| COLLATION_NAME           | varchar(32)         | YES  |     | NULL    |       |

| COLUMN_TYPE              | longtext            | NO   |     | NULL    |       |

| COLUMN_KEY               | varchar(3)          | NO   |     |         |       |

| EXTRA                    | varchar(30)         | NO   |     |         |       |

| PRIVILEGES               | varchar(80)         | NO   |     |         |       |

| COLUMN_COMMENT           | varchar(1024)       | NO   |     |         |       |

| IS_GENERATED             | varchar(6)          | NO   |     |         |       |

| GENERATION_EXPRESSION    | longtext            | YES  |     | NULL    |       |

+--------------------------+---------------------+------+-----+---------+-------+

22 rows in set

 

show variables like '%join%'; -- 控制连接相关算法是否可用以及工作区缓存大小

set join_cache_level=8;

set join_buffer_size = 1048576000;

set join_buffer_space_limit = 1048576000;

set query_cache_type = off; -- 验证每次重新执行，而不是从缓存获取，否则瞬间就查出来了

 

select count(1) from big_table_a a ,big_table_b b

where a.column_name = b.column_name

and a.table_name = b.table_name;

-- hj a,b均索引，平均10.5秒，索引字段数和表字段数相差很多，且表大部分字段都有值，但是索引覆盖扫描效果却一点都不明显

  684519403

- 685679789

    116w  Innodb_buffer_pool_read_requests

-- nl，a\b均索引，平均27秒

685682832

734794328

 4911w   Innodb_buffer_pool_read_requests

-- hj a全表,b索引，平均11秒

734931616

735957796

  102w   Innodb_buffer_pool_read_requests

慢日志差别

mariadb和percona server分别在mysql慢日志的基础上增加了额外的统计信息，包括精确的执行时间、innodb相关的统计信息等。

log_slow_verbosity

该参数为percona server/mariadb的扩展。用于控制慢日志统计信息记录的详细程度，mariadb 取值范围包括query_plan, innodb, explain。

percona server取值范围包括：

l  microtime: 记录查询统计信息到微妙精度。

l  query_plan: 记录查询执行计划。

l  innodb: 记录InnoDB的统计信息。

l  minimal: 相当于仅开启microtime。

l  standard: 相当于开启microtime,innodb。

l  full: 启用除了profiling和profiling_use_getrusage之外的选项。

l  profiling（一般不建议开启）: 启用所有连接的所有profiling。

l  profiling_use_getrusage: 启用profiling。

参见：

https://mariadb.com/kb/en/library/server-system-variables/#log_slow_verbosity

https://www.percona.com/doc/percona-server/LATEST/diagnostics/slow_extended.html

log_slow_filter

该参数用于控制记录慢日志的过滤条件，percona server的取值范围为：

l  full_scan: 查询通过全表扫描执行。

l  full_join: 查询关联时没有走索引。

l  tmp_table: 查询创建了内部临时表。

l  tmp_table_on_disk: 查询创建了磁盘临时表。

l  filesort: 查询执行了filesort。

l  filesort_on_disk: 查询执行了基于磁盘的filesort。

mariab除了上述取值范围外，还包括下列几个取值：

l  query_cache：语句直接通过查询缓存返回结果。

l  query_cache_miss：语句没有使用查询缓存返回。

l  admin：非普通增删改查语句如create, optimize, drop等。

慢日志对于服务器性能是有一定影响的，具体影响程度参见https://www.percona.com/blog/2009/02/10/impact-of-logging-on-mysql%E2%80%99s-performance/。

慢日志分析工具

mysqldumpslow

mysqldumpslow是mysql自带的用于从慢日志中分析汇总语句的工具。

pt-query-digest

它是percona toolkit包中包含的一个可以从慢日志、processlist以及tcpdump分析查询的工具。相比mysqldumpslow而言，它提供了更多的信息帮助时候分析性能。具体可参见https://www.percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html

优化器提示/跟踪

准确的说，mysql从5.7开始真正算引入了优化器提示，其用法和oracle的优化器提示完全相同，5.6以及之前版本的索引提示等虽然能够帮助优化，但是其价值远没有5.7大。mysql 8.0对此进行了进一步加强，引入了更多的优化器提示。如下所示：

mariadb支持的优化器提示（mysql均支持）	mysql 5.7开始新增的优化器提示
SQL_CACHE / SQL_NO_CACHE	BKA, NO_BKA
SQL_BUFFER_RESULT	BNL, NO_BNL
SQL_SMALL_RESULT / SQL_BIG_RESULT	INDEX_MERGE, NO_INDEX_MERGE
STRAIGHT_JOIN	JOIN_FIXED_ORDER
SQL_CALC_FOUND_ROWS 该提示用在分页查询上，带了该提示后，MariaDB会计算不带LIMIT的时候，有多少行记录。随后可通过FOUND_ROWS()获取行数	JOIN_ORDER
USE/FORCE/IGNORE INDEX	JOIN_PREFIX
	JOIN_SUFFIX
	MAX_EXECUTION_TIME
	MERGE, NO_MERGE
	MRR, NO_MRR
	NO_ICP
	NO_RANGE_OPTIMIZATION
	QB_NAME
	RESOURCE_GROUP
	SEMIJOIN, NO_SEMIJOIN
	SKIP_SCAN, NO_SKIP_SCAN
	SET_VAR
	SUBQUERY

从上可知，相比Mysql而言，Mariadb的提示几乎少得可怜。各优化器提示的含义参见https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html。

对于MySQL，用户可以用EXPLAIN检查优化器提示是如何影响执行计划的，如果要查看某个优化器提示是否被使用了，可以在执行EXPLAIN后执行 SHOW WARNINGS，EXPLAIN EXTENDED可以看到哪个提示被用了，如下：

 

 

 

Mariadb则没有该特性。

查看正在运行SQL的执行计划

mysql和mariadb都提供了查看正在执行的sql的执行计划。其中：

l  MariaDB可以通过执行SHOW EXPLAIN FOR <thread_id>查看。除了根据线程号、查询号终止外，还支持中止某个用户的所有连接。如下：

KILL [HARD | SOFT] [CONNECTION | QUERY [ID] ] [thread_id | USER user_name | query_id]

默认为杀连接。

l  MySQL可以通过EXPLAIN FOR CONNECTION <thread_id>查看，但是mysql没有提供用户级别的。如下所示：

KILL [CONNECTION | QUERY] processlist_id

 

性能视图

mysql从5.5版本引入PERFORMANCE_SCHEMA，并且在随后的版本中得到增强。MariaDB 10.2集成了MySQL 5.6 PERFORMANCE_SCHEMA，但是随后一直没有加强，截止mariadb 10.4仍然是最早mysql 5.6版本的P_S。

mysql从5.7.7开始在performance_schema的基础上新增了sys性能库，它是基于performance_schema的视图，还包括一些存储过程，在此之前， 通常需要自己编写自定义sql查询performance_schema，其友好性相对来说不是很好。在mysql 8.0中，该库仍然存在并且进行了加强，https://dev.mysql.com/doc/refman/8.0/en/sys-schema.html。mariadb标准安装包并不包含sys库，但是用户可以通过执行5.6版本的脚本https://github.com/mysql/mysql-sys自行安装。

analyze

explain只能查看SQL语句的解释计划，它并不总是和实际的执行计划相同，为此，mariadb10.1增加了analyze语句，包含了要查看实际的执行计划，它会先执行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 |

+----+-------------+----------+------+---------------+-------------+---------+--------------------+--------+--------+----------+------------+-------------+

其中r_开头的代表实际的结果，不带r_的为优化器基于统计信息估计的结果。注：顺带提一下，如果过滤后符合（filtered）条件的记录比例高于15%，一般来说就说明索引不合理。有时候会看到r_rows或r_filtered为NULL，这说明该对象没有被实际扫描或没有符合条件的记录。

截止目前位置，mysql尚不支持该特性，也无增加的计划。

进度报告

mariadb从5.3版本开始在INFORMATION_SCHEMA.PROCESSLIST 中增加了一个PROGRESS 列，其中包含一些很可能较慢的操作的进度信息，类似于oracle的v$session_longops视图。当前报告进度的操作包括：

l  ALTER TABLE

l  CREATE INDEX

l  DROP INDEX

l  LOAD DATA INFILE（LOAD DATA LOCAL INFILE暂不支持，因为文件在客户端，所以无法预知大小）

因为目前不支持查看SQL语句的进度，该特性目前帮助不是特别大。

参见：https://mariadb.com/kb/en/library/progress-reporting/