MySQL5.6中新增特性、不推荐使用的功能以及废弃的功能

柔情只为你懂 2022-08-10 08:43 141阅读 0赞

虽然已经使用MySQL5.6版本有一段时间了，但由于没有和之前的版本作过详细比较，所以对于哪些重要的或者不太重要的特性是在新版本中引入的，还有哪些特性已经或者将要从旧版本中移除的并没有一个十分全面的了解。最近有一个将某数据库实例由5.5升级为5.6的需求，所以对于MySQL5.6和5.5版本之间的差异做了一个简单梳理。

**新增**  
   **   Security improvements.**

可使用mysql\_config\_editor将登陆认证信息以加密方式保存在.mylogin.cnf文件中，客户端可从该文件读取以避免以明文方式使用登录信息。

通过内置sha256\_password认证插件支持更强的账户密码加密。

mysql.user表新增password\_expired用以标识密码是否过期。可通过alter user语句设置密码过期。

通过 validate\_password 插件实现密码安全度检查。

对于使用MySQL4.1之前版本的Hash方法加密的账户密码mysql\_upgrade 会给出警告。

在Unix平台上mysql\_install\_db支持--random-passwords，可为初始root用户生成强度更高的密码并设置密码过期、移除匿名用户。

各类日志（慢查询、二进制日志、通用日志）包括mysql客户端命令历史记录中不再会出现明文密码内容。

START SLVAE语法改进可在该语句中指定连接MASTER的参数，可避免将密码之类的信息保存在master.info文件

**Changes to server defaults.**  
          提供更好的性能并减少管理员对设置的手工修改  
  
     **InnoDB enhancements.**  
          开始支持全文索引。  
          Online DDL（即一些ALTER TABLE操作无需拷贝表、不阻塞增删改）。  
          CREATE TABLE语句支持DATA DIRECTORY='directory' 可将file-per-table 下每个表对应的.ibd文件存放在数据目录以外的其他目录，提升了灵活性继而可提升性能，比如将.ibd放在单独的SSD盘。  
          支持transportable tablespaces、允许单表空间(每个表的.ibd文件)动态导入导出。 FLUSH TABLE的FOR EXPORT从句可将buffer中未保存的变化刷到.ibd文件。之后可将.ibd文件拷贝和元数据文件拷贝至其他服务器实例(如SSD、HDD）。ALTER TABLE的DISCARD TABLESPACE和IMPORT TABLESPACE从句可将数据载入不同的MySQL实例。  
          可通过innodb\_page\_size设置InnoDB页大小（默认16KB可设置为8KB、4KB）以适应特定的负载和硬件来降低冗余、提升系能。  
          Buffer Pool刷新算法得以改进以增强性能和并发性，可通过一系列参数进行调整。  
          与memcached整合可使用NoSQL-style API来访问数据避免解析和构建查询计划的开销（适用于简单查询、更新等）。  
          优化用的统计信息更加精确且可在服务重启后保持并可控制采样率。  
          优化只读事务对于ad-hoc queries和report-generating 应用提升了性能和并发。  
          可将InnoDB undo log 从系统表空间移至一个或多个独立表空间（如移动到SSD盘）。  
          可通过 innodb\_checksum\_algorithm=crc32指定使用更快的checksum算法。  
          redo log文件总大小可达到512G（之前为4G），通过innodb\_log\_file\_size指定单个文件大小，innodb\_log\_files\_in\_group指定文件数量、innodb\_log\_group\_home\_dir指定存放路径。  
          可将MySQL中InnoDB表配置为只读模式（通过--innodb-read-only），这样可在只读媒介如DVD、CD上访问InnoDB表或者搭建多实例共享数据目录的数据仓库。  
          可通过innodb\_compression\_level指定InnoDB compressed表的压缩级别并通过innodb\_log\_compressed\_pages指定是否将buffer pool中compressed pages由于更新操作引起的re-compressed pages存储在redo log中。  
          可通过innodb\_compression\_failure\_threshold\_pct, innodb\_compression\_pad\_pct\_max动态调整InnoDB压缩表中的填充块（用以避免DML时的重新压缩，但数量过多时会增加压缩失败的几率）的比例，以降低压缩失败率而无需通过新的参数重建表或者以不同的页大小重建整个实例。  
          INFORMATION\_SCHEMA中新增InnoDB相关系统表，可查询InnoDB buffer pool、表元数据、索引、外键、底层性能相关的数据等。  
          新的InnoDB可以使用LRU算法释放打开的表所占用的内存以缓解有大量表的服务器上的内存负载。（可通过 table\_definition\_cache 调整打开的InnoDB表可使用的内存量）  
          内部性能优化，如通过拆分kernel mutex减少争用、将flush操作由主线程移至独立线程、使用多个刷新线程、较少大内存系统上的buffer pool的争用。  
          更快的死锁检测算法，InnoDB相关的死锁信息会全部写入错误日志。  
          可在服务器重启后立即重载页至InnoDB buffer pool以避免重启后过长的“热身”时间，尤其是InnoDB buffer pool较大的实例。可在服务器关闭时dump出buffer pool中的数据下次重启后载入、也可以在运行过程中导入导出。  
          5.6.16后innochecksum可支持2G以上的文件  
          可通过innodb\_status\_output和innodb\_status\_output\_locks,分别动态开闭InnoDB Monitor和InnoDB Lock Monitor（分别会在error log中打印出InnoDB相关信息，后一个参数的开启要以前一个参数的开启为前提，以后后一个参数的结果是前一个结果的子集）。  
          5.6.17后可以使用online DDL（ALGORITHM=INPLACE）通过如下操作OPTIMIZE TABLE、ALTER TABLE ... FORCE、ALTER TABLE ... ENGINE=INNODB 来重建表，以减少重建时间并允许并发的DML。

**Partitioning.**  
          分区最大量可达8192（包含子分区的量）  
          可使用ALTER TABLE ... EXCHANGE PARTITION与非分区表交换分区表的一个分区或子分区（前提是这两个表除了分区之外表结构一致、且非分区的表不为临时表且无外键引用或被引用的外键非分区表中不包含要交换的分区边界以外的值）  
          可以限定在一个或者多个分区或子分区内查询。支持的语句包括SELECT、DELETE、INSERT、REPLACE、UPDATE、LOAD DATA、LOAD XML。  
          Partition lock pruning通过消除分区上的锁，极大提升了多分区表上DML、DDL操作的性能。  
  
  
     **Performance Schema.**  
               更加丰富的信息。表输入输出，表事件过滤，线程事件过滤，表、索引I/O及表锁摘要信息，语句及语句的阶段信息等。相关的两个参数为 --gtid-mode和--enforce-gtid-consistency。若使用GTIDs在开始一个新的slave或者指向一个新的master时就不必指定日志文件和文件中的位置，过程可大大简化。另外，因基于GTID的复制时完全基于事务的，因而很容易检查主从一致性，只要master上提交的事务在slave上也提交过就可以保证一致。

**Replication and logging.**

开始支持使用GTIDs( global transaction identifiers)的基于事务的复制，使识别和追踪在master上提交并在slave上应用的每个事务成为可能。  
           
          可通过binlog\_row\_image控制 row-based复制中的row image所包含的列，使其可以包含所有列或者不包含BLOB和TEXT列或者仅包含必要的用于唯一标识并执行每一行上的变化的个别列，以便于节省磁盘开销、网络开销和内存使用。  
           
          MySQL Server 对二进制日志的读写是 crash-safe的，因只记录和读取完整的事务/事件。默认，会记录事件本身和事件的长度，然后利用这些信息验证事件是否被正确书写。也可以通过binlog\_checksum 变量使server使用CRC32算法为事件写校验和，并通过 master\_verify\_checksum使server从日志文件中读取校验和， --slave-sql-verify-checksum 可以使slave的SQL thread从中继日志中读取校验和。  
  
          支持选择将master的连接信息以及slave的中继日志信息保存在文件或者表中，相关参数为--master-info-repository和--relay-log-info-repository。但为了保证复制的crash-safe，当选择将master的连接信息以及slave的中继日志信息保存在表中时相关的表必须是事务性引擎如InnoDB（5.6.6之后的版本会自动创建为InnoDB表但之前的版本会创建MyISAM表，因此在复制开始之前一定要修改为InnoDB引擎）  
  
          mysqlbinlog工具可以从指定的主机以原始的二进制格式备份二进制日志文件。工具相关的命令行参数为 --read-from-remote-server和--raw  
  
          支持延迟复制，也即，将slave延后于master一段特定的时间。改特性可以用于测试延迟带来的影响或者挽救 DBA在master上不小心犯的错误（通过CHANGE MASTER TO中的MASTER\_DELAY指定）。  
  
          对于有多块网络接口的salve机器可指定只使用其中某一个（通过CHANGE MASTER TO 语句中的 MASTER\_BIND指定）  
  
          新增log\_bin\_basename参数用于指定二进制日志文件完整的路径和文件名  
  
          支持slave上多个线程并行执行master上传递下来的事务。通过 slave\_parallel\_workers指定slave上并发执行事务的worker数量。这种并发是基于数据库的，也即数据库内的操作是有先后顺序的，主从上需以一致的顺序执行，但不同数据库之间则不需要这种顺序，也即一个worker可不必等待其他worker在更新完某个库之后再更新某库。  
  
                
     **Optimizer enhancements.**  
          优化了SELECT ... FROM single\_table ... ORDER BY non\_index\_column \[DESC\] LIMIT \[M,\]N;查询性能  
          实现了Disk-Sweep Multi-Range Read.有效减少了在大表且没有缓存在缓存中的情况下在辅助索引上范围扫描造成的随机磁盘访问。采用的办法是先扫描找出相关行关联的key，然后依据主键将key排序，最终依据排好序的key回表取出数据。  
          实现了ICP(Index Comndition Pushdowndown)。若没有ICP，存数引擎层根据索引定位表中的行并返回给Server层然后在这里用WHERE从句中的条件对结果进行过滤。若使用ICP，如果WHERE条件中的部分列可由索引中的字段进行评估那么server层会将WHERE条件中的这一部分下推到存储引擎层。引擎层进行评估后只取出满足要求的行。这样可有效减少引擎访问表的次数和server访问引擎的次数。  
          EXPLAIN支持 DELETE, INSERT, REPLACE和UPDATE，以前只支持SELECT，且可以以JSON格式输出。  
          处理FROM从句中的子查询（派生表）更加高效。首先FROM后的子查询的物化过程直到查询执行时需要这一部分的内容时才进行，另外查询可能会在派生表上加索引以加快查询。  
          使用semi-join和物化策略优化子查询。  
          使用BKA(Batched Key Access)join算法提升join性能。支持inner join、outer join、semi-join、nested outer joins和nested semi-joins。      
          新增tracing功能主要供开发者使用。通过一些列 optimizer\_trace\_xxx 变量和 INFORMATION\_SCHEMA.OPTIMIZER\_TRACE来提供  
  
     **Condition handling.**  
          可通过GET DIAGNOSTICS语句获取各种诊断信息  
  
     **Data types.**  
          对于TIME、DATETIME、TIMESTAMP类型的值支持小数的秒，最多可支持毫秒级别（也即6位的精度）。  
          之前的版本中每个表至多只有一个TIMESTAMP列可以初始化为当天日期时间(DEFAULT CURRENT\_TIMESTAMP)或者更新至当天日期时间( ON UPDATE CURRENT\_TIMESTAMP)。新版本的中取消了此限制.另外，之前只有TIMESTAMP列可以使用DEFAULT CURRENT\_TIMESTAMP和 ON UPDATE CURRENT\_TIMESTAMP，现在datetime列也可以。  
          之前版本中TIMESTAMP列定义中什么都不指定的话自动的默认为DEFAULT CURRENT\_TIMESTAMP ON UPDATE CURRENT\_TIMESTAMP。现在可以通过 explicit\_defaults\_for\_timestamp 变量关闭这一行为。  
  
     **Host cache.**  
          提供了更多有关导致连接错误的原因的信息以及对host cache访问的改进，其中包含客户端IP和主机名可用以避免DNS查询。具体实现如下：  
          增加了一系列 Connection\_errors\_xxx状态变量，提供有关连接错误的信息，这些信息是全局的表示所有连接的各类错误信息的聚合，不是针对于某个特定IP地址。  
          performance\_schema库新增host\_cache表且有更多的关于错误信息的计数字段来track各类错误（非针对特定IP的错误），这样一来便可通过查询该表得知有多少主机被缓存、哪些主机发生了哪类错误、连接错误量有多接近max\_connect\_errors限制。  
          可通过 host\_cache\_size调整host cache大小  
  
  
     **OpenGIS.**  
          原生实现了OPENGIS规范定义的测量两个几何值之间关系的一系列函数。  
  
**降级**  
     ERROR\_FOR\_DIVISION\_BY\_ZERO, NO\_ZERO\_DATE和NO\_ZERO\_IN\_DATE 几个sql mode 不赞成使用，MySQL5.7中这些值不会有任何影响，效果会被包含在strict SQL mode中((STRICT\_ALL\_TABLES 或 STRICT\_TRANS\_TABLES)。MySQL升级时要注意这些问题。确保新版本中的SQL MODE不会影响应用。  
  
     GROUP BY中的隐式排序不赞成使用，这属于有MySQL的一个扩展，可能在将来版本中出于性能考虑被移除。所以最好使用ORDER BY语句明确指明排序。  
  
     不推荐使用4.1之前版本的密码和mysql\_old\_password认证插件，不安全。为防止使用旧版本密码哈希可将secure\_auth 开启。对于DBA 建议将使用mysql\_old\_password 认证插件的账户转为使用 mysql\_native\_password认证插件。  
  
     不建议使用OLD\_PASSWORD()和old\_passwords=1来生成4.1版本之前的密码。  
  
     不建议使用 --skip-innodb或者--innodb=OFF, --disable-innodb；date\_format, datetime\_format 和 time\_format； have\_profiling, profiling 和 profiling\_history\_size ； innodb\_use\_sys\_malloc 和innodb\_additional\_mem\_pool\_size； timed\_mutexes 变量。这些都可能在将来的版本中移除。  
  
     使用 --lc-messages-dir 和 --lc-messages 代替--language（用于指明错误信息使用的语言）。  
  
     不建议使用 ALTER IGNORE TABLE。  
  
     不建议使用 msql2mysql, mysql\_convert\_table\_format, mysql\_find\_rows, mysql\_fix\_extensions, mysql\_setpermission, mysql\_waitpid, mysql\_zap, mysqlaccess 和 mysqlbug这些工具。  
  
     不建议使用mysqlhotcopy工具。  
  
  
**移除**  
     --log选项，使用 --general\_log 和 --general\_log\_file=file\_name 代替。  
     --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\_oopen\_cache变量代替。  
     移除--init-rpl-role、--rpl-recovery-rank options选项, 和rpl\_recovery\_rank、Rpl\_status状态变量。  
     取消engine\_condition\_pushdown 变量，改为optimizer\_switch变量中的一个flag  engine\_condition\_pushdown。  
     取消have\_csv, have\_innodb, have\_ndbcluster和have\_partitioning变量。使用SHOW PLUGIN或者查询INFORMATION——SCHEMA中的PLUGINS表。  
     sql\_big\_tables变量替换为big\_tables.  
     sql\_low\_priority\_updates变量替换为low\_priority\_updates.  
     sql\_max\_join\_size变量替换为max\_join\_size.  
     max\_log\_data\_size变量替换为max\_allowed\_packet。  
     FLUSH MASTER和FLUSH SLAVE语句替换为 RESET MASTER和 RESET SLAVE.  
     SLAVE START和SLAVE STOP替换为START SLAVE和STOP SLAVE。  
     取消SHOW AUTHORS和SHOW CONTRIBUTORS.  
     取消SET 命令中的OPTION和ONE\_SHOT修饰符。  
     不允许加在存储过程、存储函数中将参数或者本地变量设置为DEFAULT( SET var\_name = DEFAULT ).但可以将系统变量设置为DEFAULT。  
     移除 SHOW ENGINE INNODB MUTEX。