MySQL 5.6的72个新特性

一，安全提高

1.提供保存加密认证信息的方法，使用.mylogin.cnf文件。使用mysql_config_editor可以创建此文件。这个文件可以进行连接数据库的访问授权。mysql_config_editor会进行加密而不是明文存储。客户端只会在内存中进行解密。这样密码以非明文方式存储，不会在命令行或者环境变量中暴露。更多信息，访问 Section 4.6.6, “mysql_config_editor — MySQL Configuration Utility”.

2.使用sha256_password，支持更为强大的用户密码加密方式。这个插件是内置的。更多信息访问 Section 6.3.6.2, “The SHA-256 Authentication Plugin

3.mysql.user表现在增加password_expired列，默认值是’N'，使用新的ALTER USER命令可以设置为’Y'。当密码过期后，使用此账号的后续连接都会报错，只到用户使用SET PASSWORD命令创建一个新密码。更多信息访问Section 13.7.1.1, “ALTER USERSyntax”

4.现在提供密码安全策略

 使用明文指定密码时，密码会被当前的密码策略检查，如果太弱会被拒绝（返回ER_NOT_VALID_PASSWORD 错误）。会影响 CREATE USER, GRANT, 和 SET PASSWORD命令。密码作为参数被password()，old_password()引用时也会被检查。

 密码的强状程度可以被新函数VALIDATE_PASSWORD_STRENGTH() 检查。此函数把密码做为参数，返回0(弱)-100（强）。

 以上都是validate_password插件提供，更多信息见 Section 6.1.2.6, “The Password Validation Plugin”.

 mysql_upgrade如果发现使用4.1以前的哈希密码会警告。这样的账号必须升级到更安全的哈希密码。见Section 6.1.2.4, “Password Hashing in MySQL”

5.登录记录被更改，所以密码不会再被明文记载在general log，bin log,slow log。见Section 6.1.2.3, “Passwords and Logging”

6.start slave语句被修改，可以指定参数。密码可以存放在master.info 文件。见Section 13.4.2.5, “START SLAVE Syntax”◦

二，默认值更改

7. 从5.6.6开始，默认值与以前不同，动机是提供更好的性能，避免手工更改。见 Section 5.1.2.1, “Changes to Server Defaults”.

三，Innodb加强

8. 可以在Innodb 表上建全文索引，使用 MATCH() … AGAINST语句。这个特性包括一个新的近似搜索符号 @，和几种新的配置项以及INFORMATION_SCHEMA表。见Section 14.2.4.12.3, “FULLTEXT Indexes”◦

9. 几种ALTER TABLE操作不再拷备表，不会阻塞insert,update,delete或者全部写入操作。这就是所谓的online DDL.见Section 14.2.2.6, “Online DDL for InnoDB Tables”

10.单独表空间下，对于.ibd files你有更多的自主性。 file-per-table模式创建表时可以指定MySQL数据目录以外的目录。比如，把压力大的表放到SSD设备上，或者把一个大表放到一个大HDD上。你可以把一个表从一个实例导出，然后导入另一个实例，不会引起因为缓存数据，进行中的事务以及内部因素如 space ID以及LSN引起的数据不一致。见：Section 14.2.5.2.33, “Tablespace Management”

11.你可以设置Innodb的未压缩表的page size 为8KB或者4KB，或者默认的16KB。使用参数innodb_page_size来配置。创建实例的时候指定此参数。同一实例Innodb tablespaces共享此页面大小。更小的页面大小对某种混合压力负载可以避免冗余或者低效的IO，尤其是针对块大小小的SSD 设备。

blackjack, roulette, slots, slots, blackjack, roulette, casino, blackjack, blackjack, blackjack, slots

12.改进的 adaptive flushing 算法使得多种workloads下I/O操作更有效率和一致性。新算法和默认配置期待可以提高多数用户的性能和并发。见Section 14.2.5.2.8, “Improvements to Buffer Pool Flushing”

13.你可以通过NoSQL API开发应用访问InnnoDB表。它使用流行的memcached守护进程来响应对于key-value对的Add,Set和GET请求。这样避免了解析和构建query execution plan的成本。你可以使用NoSQL API或者SQL来访问同一份数据。比如：你可以通过NoSQL API快速访问和查询表数据，使用SQL来进行复杂查询以及兼容已有的应用。更多见Section 14.2.10, “InnoDB Integration with memcached” 。

14.Innodb的优化器统计会以更确定的间隔收集，同时服务器重启后还能保持，使得plan stability更稳定。你还可以控制InnoDB索引的样本数量，使得优化器的统计更准确，以提主查询执行计划。更多见 Section 14.2.5.2.9, “Persistent Optimizer Statistics for InnoDB Tables”

15.优化了只读事务，提高了特定查询和报告生成应用的性能和并发。这个优化是自动的，或者你可以指定START TRANSACTION READ ONLY 参数。更多见 Section 14.2.5.2.2, “Optimizations for Read-Only Transactions”

16.可以把Innodb 的undo log移出system tablespace到一个或者多个独立的tablespaces。undo log的I/O模式使得将这些表空间移到SSD设备成为一个好选择，同时将系统表空间放在普通磁盘上。更多见：Section 14.2.5.2.3, “SeparateTablespaces for InnoDB Undo Logs”

17.Innodb redo log大小从最大4GB提高到512GB，通过参数 innodb_log_file_size 配置。

18.–innodb-read-only 选项可以让MySQL运行在只读模式。你可以通过DVD,CD等只读媒体访问Innodb 表，还可以共享数据目录来创建多个数据仓库。更多见： Section 14.2.6.1, “Support for Read-Only Media”

19.多个关于Innodb的新INFORMATION_SCHEMA表，提供了关于buffer pool，表的元数据，索引，数据字典中的外键，以及更细的性能粒度信息，补充了Performance Schema表的信息。

20.打开多个表时，Innodb限制了保持表信息的内存。

21.Innodb强化了几种内部性能，包括拆分kernel mutex来减少竞争，将刷新操作移出主线程，允许使用多个清理线程，以及减少了大内存系统中的buffer_pool竞争。

22.Innodb使用了一种新的，更快的算法来检测deadlocks. 所有死锁信息都可以被记录到错误日志。

23.为了避免大buffer_pool的实例重启服务时过长的warmup 时间，你可以在重启后立即加载缓存页面。MySQL可以在关闭时导出完全数据文件，检阅此文件找出重启时需要加载的pages。你可以在任何手工导入导出buffer_pool，比如在性能测试时或者在执行复杂的OLAP查询后。

四，分区

24.最大分区数量提高到8192，这包括所有的分区和子分区。

25.使用ALTER TABLE … EXCHANGE PARTITION 可以在未分区表和分区表和子分区表之间交换分区。这可以用来导出导入分区。更多见：Section 17.3.3, “Exchanging Partitions and Subpartitions with Tables”.

26.可以在分区表中显示查询一个或者多个分区，或者更改数据。比如，表t有int 列c，有4个分区p0-03，查询SELECT * FROM t PARTITION (p0, p1) WHERE c < 5 只返回po,p1符合条件结果。

以下语句支持显式分区查询

• SELECT
• DELETE
• INSERT
• REPLACE
• UPDATE
• LOAD DATA
• LOAD XML

译：更多见：Section 17.5, “Partition Selection”.

27.减少分区锁提高了有很多分区的表的多数DML和DDL操作，减少的是未被语句影响的分区上的锁。这样的语句包括 SELECT, SELECT … PARTITION, UPDATE, REPLACE, INSERT, 以及很多其它语句。更多，包括哪些语句性能提高见Section 17.6.4, “Partitioning and Locking”.

五，Performance Schema

28.记录表的输入与输出，操作包括行级访问表和临时表，如insert,upate,delete.

29.表的事件过滤，以库或者表名为基础。

30.线程的事件过滤，更多关于线程的信息被搜集

31.表和索引I/O以及表锁的统计表。

32.记录命令以及命令的阶段。

33.服务启动的的时候配置参数，以前只能运行时设设置。

六，复制和日志

34.支持以全局统一事务ID（GTID)为基础的复制。当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务。

35.使用–gtid-mode and –enforce-gtid-consistency 参数启动复制可以开启GTIDs。更多信息见Section 16.1.4.5, “Global Transaction ID Options and Variables”.

36.如果使用了GTIDs，启动一个新的复制从库或切换到一个新的主库，就不必依赖log文件或者pos位。

37.GTID复制是全部以事务为基础，使得检查主从一致性变得非常简单。如果所有主库上提交的事务也同样提交到从库上，一致性就得到了保证。

38.更多见Section 16.1.3, “Replication with Global Transaction Identifiers”.

39.行复制现在支持行映像控制。可以只记录唯一定位列和变化列（非全部列），这样可以节省磁盘空间，网络流量和内存使用。你可以使用参数binlog_row_image ，设置为minimal(记录必要列)，full(全部列)，noblob（不包括blob或者text的所有列）来控制最小或者最大记录。更多见System variables used with the binary log,

40.binlog的读写现在是崩溃安全的，因为只有完整的事件（或者事务）才会被记录和读取。默认会记录事件的大小以及事件本身，使用大小来验证事件被正确记录。你也可以使用参数binlog_checksum 设置使用crc32记录事件的校验值。使用参数 master_verify_checksum可以让服务读取校验值。–slave-sql-verify-checksum 参数使从库读relay日志的时候读取校验值 。

41.MySQL支持在表中保存主库连接信息了。使用参数–master-info-repository 和 –relay-log-info-repository 来设置。设置 –master-info-repository 为表，会记录连接信息到slave_master_info 表。设置–relay-log-info-repository 为表，会记录relay log信息到slave_relay_log_info表。这几个表都是自动建立在mysql系统库。

重要：为了保证复制安全，lave_master_info 和 slave_relay_log_info表必须使用事务引擘比如innodb，默认是使用myisam引擘,意味着在开始复制前，你必须把这些表改为事务引擘，以保证安全。使用语句ALTER TABLE … ENGINE=…完成。如果复制运行时，请不要更改

42.mysqlbinlog 现在支持以原始格式备份binlog，使用选项–read-from-remote-server and –raw。mysqlbinlog连接服务器，请求日志文件，以原始格式写入输出文件。参见：Section 4.6.8.3, “Using mysqlbinlog to Back Up Binary Log Files”.

43.MySQL现在支持延迟复制，默认是0秒。使用CHANGE MASTER TO参数 MASTER_DELAY 来设置延迟。

44.延迟复制用来防护主库的用户误操作（DBA可以回滚一个延迟复制从库到误操作前）或者测试当系统有延迟时系统行为。见Section 16.3.9, “Delayed Replication”.

45.如果复制从库有多个网络接口，可以只使用CHANGE MASTER TO 命令的参数MASTER_BIND 来只使用其中一个。

46.增加系统参数log_bin_basename ，指定完整的路径和文件名，log_bin 只控制是否开启binlog.

同样适用relay_log_basename 。

47.现在支持多线程复制。如果开启，sql线程作为协调者协调多个工作线程，数量取决于 slave_parallel_workers 。现在多线程复制以单库为基础，特定库的更新的相对顺序和主库一样。不过，没有必要协调不同库之间的事务。事务可以被分布到每个库，意味着一个复制从库的工作线程可以顺序执行事务而不必等待其它库的更新完成。

48.既然不同库的事务在主从库的上顺序可能不同，简单的最近执行的事务不能保证以前的事务在从库都执行了。这对于多线程复制时日志和恢复有特殊意义。对于如何在多线程复制的时候解释binlog信息，请见Section 13.7.5.35,“SHOW SLAVE STATUS Syntax”

七，优化器提升

49.查询优化器对于以下查询（子查询）更有效率SELECT … FROM single_table … ORDER BY non_index_column[DESC] LIMIT [M,]N;

此类在大结果集中显示几行的查询在网站中很常见。如：SELECT col1, … FROM t1 … ORDER BY name LIMIT 10; SELECT col1, … FROM t1 … ORDER BY RAND() LIMIT 15;

排序缓存由 sort_buffer_size.指定。如果Ｎ行被排序的元素足够小可以被入排序缓存(M+N行如果M被指定)，可以避免使用合并文件，整个查询可以都放入内存中。见Section 8.2.1.3, “Optimizing LIMIT Queries”

50.优化器使用MRR。使用非聚集索引进行索引扫描时，如果表很大没有缓存，会导致大量的随机磁盘访问。使用MRR优化，优化器会先对扫描索引，然后收集每行的主键并对主键排序，此时就可以用主键顺序访问基表。这样就用顺序访问代替了随机磁盘访问。

51. 优化器使用ICP，没有ICP，引擘使用索引定位行，并返回给服务层，使用where丢弃不符合条件记录。使用ICP后，如果索引中只有部分能被 where条件利用，MySQL将where条件压到引擘层。引擘层使用索引项进行评估，只有满足条件的会被读取。ICP可以减少引擘层对基表的访问，同 时减少了服务层对引擘层的访问。更多见：Section 8.13.4, “Index Condition Pushdown Optimization”

52.EXPLAIN命令现在可以用在DELETE, INSERT, REPLACE,UPDATE语句上。以前，只能用在查询语句上。另外，现在支持json格式输出。见Section 13.8.2, “EXPLAIN Syntax”

53.优化器地于from子句的子查询更有效率。查询执行中会物化子查询以提高性能。另外，优化器还可能对派生表创建索引来加速行检索。更多见：Section 8.13.15.3, “Optimizing Subqueries in the FROMClause (Derived Tables)”

54.优化器使用semi-join和物化来优化子查询，见：Section 8.13.15.1, “Optimizing Subqueries with Semi-Join Transformations”, and Section 8.13.15.2, “Optimizing Subqueries with Subquery Materialization”

55. 对关联表查询或者使用join buffer的查询，新算法BKA被使用。BKA支持inner join,outer join,semi-join，包手nested outer joins 和 nested semi-joins。好处是提高了表扫描的性能。更多见：Section 8.13.11, “Block Nested-Loop and Batched Key Access Joins”

56.优化器有追踪功能，对于开发者很有用。optimizer_trace_xxx 系统参数和INFORMATION_SCHEMA.OPTIMIZER_TRACE 表提供接口，更多见 MySQL Internals: Tracing the Optimizer.

八，条件处理

57.MySQL现在支持 GET DIAGNOSTICS 命令，可以提供诊断信息，如前一条sql命令为什么有异常，更多见：Section 13.6.7.3, “GET DIAGNOSTICS Syntax”

58.另外，一些低效的条件处理被修正，使得更像标准的SQL

59.可以在某个块范围定义选择哪个handler，以前一个存储程序只能有一个全局的handler.

60.条件顺序被更准确的解决。

61.诊断区清理改变了。错误#55843导致条件处理在handler被激活以前被清理，使得条件信息无效。现在可以使用GET DIAGNOSTICS得到此信息。当handler存在时条件信息会被清理掉，如果它还没有在handler执行时被清理的话。

62. 以前当条件触发时handler立即被激活。现在只有当条件执行完成后再激活，这样可以选择更为合适的handler.这对于触发多个条件的语句和以往的 处理是不同的，如当某个更高优先级的条件稍后被触发，而在此范围内有handlers可以处理这些条件时。以前，只有第一个触发的会被选择，即使它的优先 级低。现在更高优先级的会被选择，即使不是第一个触发的。

更多请见Section 13.6.7.6, “Scope Rules for Handlers”

九，数据类型

63.TIME, DATETIME, 和 TIMESTAMP 支持更小时的时间粒度，精确到微秒（6位）。见Section 11.3.6, “Fractional Seconds in Time Values”

64.以前每个表最多只有一个TIMESTAMP 列可以用当前时间初始化和更新。这个限制没有了。所有TIMESTAMP列都可以设置这2个属性。另外，DATETIME 也支持这些属性了。更多见Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”

65.可以用参数explicit_defaults_for_timestamp关闭默认以前的TIMESTAMP 的默认值及自动初始化、更新属性。更多见Section 5.1.4, “Server System Variables”。

66.YEAR(2)类型被抛弃，现存的表中的year(2)列会和以前一样处理，但新建或者修改表结构时会转化为YEAR(4).更多见：Section 11.3.4, “YEAR(2)Limitations and Migrating to YEAR(4)”

十，主机缓存

67.现在提供更多关于客户端连接失败的信息，并提高了对于主机缓存的访问，主机缓存包括客户端IP和主机名，避免了DNS解析。

68.新的状态值 Connection_errors_xxx 提供了连接失败的信息，不适用于特写客户端IP。

69.主机缓存增加了对特定IP错误的计数，以及一个新的host_cache Performance Schema表。可以明确知道有多少主机被缓存，每个主机连接失败的错误类型，以及错误主机距离max_connect_errors的上限有多远

70.主机缓存大小由参数 host_cache_size 配置。

更多见： Section 8.11.5.2, “DNS Lookup Optimization and the Host Cache”, 和 Section 20.9.9.1, “The host_cache Table”

十一，OpenGIS

71.OpenGIS定义函数可以测试两个地理位置之间的关系。以前是MBR-based函数返回以矩形测量的结果。现在支持更为精准的形状。这些函数加入ST_前辍，比如Contains() 使用矩形，ST_Contains()使用对象形状。更多见Section 12.17.5.4.2, “Functions That Test Spatial Relationships Between Geometries”

十二，移除参数

72.移除参数

以下被参数移除，同时会显示新的参数。

移除–log，换为–general_log 指定是否开启，–general_log_file=file_name 指定文件名。

移除–log-slow-queries和 log_slow_queries，用–slow_query_log 开启慢查询日志，用–slow_query_log_file=file_name 指定文件名。

移除–one-thread ，使用–thread_handling=no-threads 替换。

移除–safe-mode

移除–skip-thread-priority

移除–table-cache，改为table_open_cache

移除–init-rpl-role 和 –rpl-recovery-rank选项和rpl_recovery_rank 系统参数，以及Rpl_status状态值。

移除engine_condition_pushdown，使用engine_condition_pushdown标识optimizer_switch 参数。

移除have_csv, have_innodb, have_ndbcluster, 和 have_partitioning，使用show engines替换。

移除sql_big_tables，使用big_tables。

移除sql_low_priority_updates ，使用low_priority_updates 。

移除 sql_max_join_size，使用max_join_size 。

移除max_long_data_size，使用max_long_data_size。

移除 FLUSH MASTER 和 FLUSH SLAVE命令，使用RESET MASTER 和 RESET SLAVE 替换。

移除SLAVE START 和 SLAVE STOP命令，使用START SLAVE 和 STOP SLAVE替换。

移除SHOW AUTHORS 和 SHOW CONTRIBUTORS命令。

移除set命令的OPTION and ONE_SHOT修改器。

不允许在存储过程中或者函数参数中或者存储程序本地变量中使用default来指定（如：SETvar_name= DEFAULT命令），但可以在指定系统变量时使用default。