MYSQL 5.6新特性介绍

官方参考：
http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html

一、大的方面变动：

1、新增字典表，InnoDB buffer pool 相关(评：可深入了解)
Several new InnoDB-related INFORMATION_SCHEMA tables provide information about the InnoDB buffer pool, metadata about tables, indexes, and foreign keys from the InnoDB data dictionary, and low-level information about performance metrics that complements the information from the Performance Schema tables.

2、执行计划优化  (评：可持续关注)
Optimizer statistics for InnoDB tables can now persist across server restarts, for improved plan stability. You can also control the amount of sampling done for InnoDB indexes, to make the optimizer statistics more accurate and improve the query execution plan.

3、多表打开优化
InnoDB now limits the memory used to hold table information when many tables are opened

4、内部优化
InnoDB has several internal performance enhancements, including reducing contention by splitting the kernel mutex, moving flushing operations from the main thread to a separate thread, enabling multiple purge threads, and reducing contention for the buffer pool on large-memory systems.

5、死锁写入log
Information about all InnoDB deadlocks can be written to the MySQL server error log, to help diagnose application issues.

二、分区表方面(注：越来越接近ORACLE分区表了)

1、分区交换 (评：想用的功能)
It is now possible to exchange a partition of a partitioned table or a subpartition of a subpartitioned table with a nonpartitioned table

2、在SELECT等dml中指定查询的分区
Explicit selection of one or more partitions or subpartitions is now supported for queries, as well as for many data modification statements, that act on partitioned tables. For example, assume a table t with some integer column c has 4 partitions named p0, p1, p2, and p3. Then the query SELECT * FROM t PARTITION (p0, p1) WHERE c < 5 returns only those rows from partitions p0 and p1 for which c is less than 5.

三、性能方面

(评：看能得到哪些数据，用以监控分析)

Performance Schema.  The Performance Schema includes several new features:
Instrumentation for table input and output. Instrumented operations include row-level accesses to persistent base tables or temporary tables. Operations that affect rows are fetch, insert, update, and delete.
关注：
Event filtering by table, based on schema and/or table names.
关注：
Event filtering by thread. More information is collected for threads.
关注：
Summary tables for table and index I/O, and for table locks.
关注：
Instrumentation for statements and stages within statements.

Configuration of instruments and consumers at server startup, which previously was possible only at runtime.

四、复制方面

Replication and logging.  These replication enhancements were added:

MySQL row-based replication now supports row image control. By logging only those columns required for uniquely identifying and executing changes on each row (as opposed to all columns) for each row change, it is possible to save disk space, network resources, and memory usage. You can determine whether full or minimal rows are logged by setting the binlog_row_image server system variable to one of the values minimal (log required columns only), full (log all columns), or noblob (log all columns except for unneeded BLOB or TEXT columns). See System variables used with the binary log, for more information.
（评：关注怎么设置，减少log量，减少同步压力）

复制校验
中文解释：http://www.zhaokunyao.com/archives/2387
1、校验
Binary logs written and read by the MySQL Server are now crash-safe, because only complete events (or transactions) are logged or read back. By default, the server logs the length of the event as well as the event itself and uses this information to verify that the event was written correctly. You can also cause the server to write checksums for the events using CRC32 checksums by setting the binlog_checksum system variable. To cause the server to read checksums from the binary log, use the master_verify_checksum system variable. The --slave-sql-verify-checksum system variable causes the slave SQL thread to read checksums from the relay log.

2、不是很了解
MySQL now supports logging of master connection information and of slave relay log information to tables as well as files. Use of these tables can be controlled independently, by the --master-info-repository and --relay-log-info-repository server options. Setting --master-info-repository to TABLE causes connection information to be logged in the slave_master_info table; setting --relay-log-info-repository to TABLE causes relay log information to be logged to the slave_relay_log_info table. Both of these tables are created automatically, in the mysql system database.

3、备份
mysqlbinlog now has the capability to back up a binary log in its original binary format. When invoked with the --read-from-remote-server and --raw options, mysqlbinlog connects to a server, requests the log files, and writes output files in the same format as the originals. See Section 4.6.7.3, “Using mysqlbinlog to Back Up Binary Log Files”.

4、延时复制(评:用以数据恢复，历史数据情况)
MySQL now supports delayed replication such that a slave server deliberately lags behind the master by at least a specified amount of time. The default delay is 0 seconds. Use the new MASTER_DELAY option for CHANGE MASTER TO to set the delay.

5\并行执行SLAVE同步
MySQL Replication now supports parallel execution of transactions with multi-threading on the slave.

6、binlog位置参数
The log_bin_basename system variable has been added. This variable contains the complete filename and path to the binary log file

五、优化器

1、explain(评：可以在select以外语句执行)
The EXPLAIN statement now provides execution plan information for DELETE, INSERT, REPLACE, and UPDATE statements. Previously, EXPLAIN provided information only for SELECT statements.

2、优化器增加可选
mysql> SELECT @@optimizer_switch\G

engine_condition_pushdown={on|off}
index_merge={on|off}
index_merge_intersection={on|off}
index_merge_sort_union={on|off}
index_merge_union={on|off}
engine_condition_pushdown={on|off}
index_condition_pushdown={on|off}
mrr={on|off}
mrr_cost_based={on|off}

3、Pushdown Optimization
参考：
http://hidba.org/?p=320


4\ trace SQL （评：更加细致，可以了解下）
参考：http://forge.mysql.com/wiki/MySQL_Internals_Optimizer_tracing

The optimizer now has a tracing capability, primarily for use by developers. The interface is provided by a set of optimizer_trace_xxx system variables and the INFORMATION_SCHEMA.OPTIMIZER_TRACE table