Mariadb 10.1

Optimistic mode of in-order parallel replication<br>有序并行复制的乐观模式<br>尝试并行应用大多数的事务性DML，并处理（回滚、重试）任何发生的冲突。

Enhanced semisync replication; Wait for at least one slave to acknowledge transaction before committing<br>增强的半同步复制。<br>直到至少一个从属服务器确认接收到该事务，该事务才变得可见。这意味着，如果主服务器完全丢失，其他连接看到的任何事务都将复制到某个地方，从而避免了潜在的幻像。<br>Mariadb10.3.3之前以插件形式实现。

Triggers can now be run on the slave for row-based events.<br>在基于行的复制下，可以在从库上运行主库上不存在的触发器。

Dump Thread Enhancements from Google. Makes multiple slave setups faster by allowing concurrent reading of binary log.<br>读取binlog事件时，dump thread不会彼此阻塞。<br>dump thread不会被正在Write Binlog事件的用户会话阻止。<br>用户会话不受正在读取binlog事件的dump thread阻止。<br>dump thread和user session都可以获得更好的吞吐量，并且改进非常明显，尤其是在有许多从库服务器的情况下。

Commits in certain instances in parallel replication complete immediately, avoiding losing throughput when many transactions need conflicting locks<br>如果服务器检测到提交事务T1中的一个持有另一个事务T2正在等待的 InnoDB行锁，则提交将立即完成，而不会进一步延迟。当许多事务需要冲突的锁时，这有助于避免丢失吞吐量。

Doesn’t make stupid choices when several multi-part keys and potential range accesses are present.<br>优化多字段索引的相关排序问题。例如：select * from tb_page where page_id=0 and page_name&gt;='Sg' order by page_name limit 10;<br>index(page_id,page_name)

Always uses “range” and (not full “index” scan) when it switches to an index to satisfy ORDER BY … LIMIT.<br>优化多字段索引的相关排序问题。例如：select * from t where col1&lt;=1412199999 order by col1 desc,col2 desc,col3 desc limit 10;<br>index(col1,col2,col3)

Tries hard to be smart and use cost/number of records estimates from other parts of the optimizer.<br>优化单表多个索引，优化器选择最合适的索引，而不是目前的最先创建的索引。<br>例如：select * from tb_test1 where fd1=1 order by fd_pk limit 1000;<br>tb_test1.ix_fd_fdpk(fd1,fd_pk),tb_test1.ix_fd1_fd2(fd1,fd2)。<br>select * from tb_test2 where fd1=1 order by fd_pk limit 1000;<br>tb_test2.ix_fd1_fd2(fd1,fd2),tb_test2.ix_fd_fdpk(fd1,fd_pk)。

Takes full advantage of InnoDB’s Extended Keys feature when checking if filesort() can be skipped<br>利用InnoDB的存储特点来优化索引排序。<br>例如：SELECT pk2 FROM tb_bug1 USE INDEX(key1) WHERE pk1 = 123 AND col1 = 'video' ORDER BY pk2 DESC LIMIT 10;<br>CREATE TABLE tb_bug1 (<br> pk1 int(11) NOT NULL,<br> pk2 varchar(64) NOT NULL,<br> col1 varchar(16) DEFAULT NULL,<br> PRIMARY KEY (pk1,pk2),<br> KEY key1 (pk1,col1)<br>) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The ORDER BY optimizer takes multiple-equalities into account<br>排序优化需要考虑等式传播。<br>例如：select * from a join b on a.id=b.id order by a.id desc limit 10;<br> select * from a join b on a.id=b.id order by b.id desc limit 10;<br>

Make switching of index due to small limit cost-based: We have made the decision in make_join_select() of whether to switch to a new index in order to support "ORDER BY ... LIMIT N" cost-based.<br>order by limit小批量数据时，基于成本考虑索引的切换。<br>例如：select * from t1 where a LIKE '0.5555%' AND b &gt;= now()-interval 100 day ORDER BY b DESC LIMIT 100;<br>index(a),index(b),a的范围筛选值更少，order by b导致使用了b的索引，而不是基于成本考虑的a的索引。

MAX_STATEMENT_TIME can be used to automatically abort long running queries. <br>终止超过一定时间（MAX_STATEMENT_TIME，单位为秒，默认0，即无限制）的sql语句（所有sql，不包括存储过程）。<br>

UNION ALL works without usage of a temporary table .<br>没有order by 子句的union all 可以不发送到临时表，行可以直接发送到客户端。

Consistent support for IF EXISTS, IF NOT EXISTS, and OR REPLACE clauses.<br>语法支持：database\function\role\server\user\view\event\index\trigger

ANALYZE statement provides output that looks like EXPLAIN output, but also includes data from the query execution (how many rows were actually read, etc).<br>新的解析支持，ANALYZE explainable_statement,调用查询优化器，查询实际影响行数（真实执行查询）。

EXPLAIN FORMAT=JSON is a re-implementation of similar feature in MySQL 5.6<br>ANALYZE FORMAT=JSON produces detailed information about the statement execution<br>以json形式输出相关解析。

Using Compound Statements Outside of Stored Programs<br>语法支持：可以在存储过程之外使用BEGIN,IF, CASE, LOOP, WHILE, REPEAT&nbsp;

The number of rows affected by a slow UPDATE or DELETE is now recorded in the slow query log - see also mysql.slow_log Table.<br>update\delete的慢SQL语句实际影响行数也可以记录在mysql.slow_log了,之前rows为0。

Allow up to 64K pages in InnoDB (old limit was 16K)<br>InnoDB内部page的容量限制由16K改为64K。支持多个blob等大数据类型的字段，减少分page存储。

The Facebook/Kakao defragmentation patch (see Defragmenting InnoDB Tablespaces) which uses OPTIMIZE TABLE to defragment InnoDB tablespaces).<br>对InnoDB表空间进行碎片整理，没有创建新表，也不需要将数据从旧表复制到新表。取而代之的是，此功能加载n页面（由innodb-defragment-n-pages确定）并尝试移动记录，以便页面将充满记录，然后在操作后释放完全为空的页面。

New server variable enforce_storage_engine<br>新的系统参数enforce_storage_engine用于强制对新表使用特定的存储引擎。用于避免使用其他引擎不必要地创建表。

Default size of query_alloc_block_size changed from 8192 to 16384 and query_prealloc_size from 8192 to 24576 to avoid the need for simple queries with one join to call my_malloc<br>优化query_alloc_block_size、query_prealloc_size参数的默认值，避免简单查询调用my_malloc

SET STATEMENT - set variables for the duration of the query (MDEV-5231). This is a backport of Per-query Variable Statement feature of Percona Server 5.6 (which, in turn, is based in MySQL GSoC 2009 project by Joseph Lukas), with many bugs fixed.<br>对单个查询设置系统参数的值，执行完查询后将还原之前的值。语法：SET STATEMENT &lt;variable=value&gt; FOR &lt;statement&gt;;

数据加密

InnoDB页面压缩<br>

密码验证插件<br>