mysql5.5 5.6 5.7新特性

xingyu655

　　1,5.5默认存储引擎为innodb
　　2,5.5增加cpu多核处理能力：innodb_read_io_threads
　　innodb_write_io_threads
　　3,5.5改善磁盘IO
　　提高脏页刷新增也数量和合并插入数量：innodb_io_capacity
　　增加自适应刷新脏页功能：innodb_adaptive_flushing
　　(innodb刷新脏页的规则是在如下三种情况下才会把innodb_buffer_pool的脏页输入磁盘：
　　1，当超过innodb_max_dirty_pages_pct设定的值时，
　　2，重做日志ib_logfile文件写满时，
　　3，机器空闲的时候。)
　　4，5.5innodb的数据恢复时间加快
　　redo log 最大可以设置为4GB，降低了IO需求，刷脏页频率降低，极大提高了Innodb写的性能
　　5，5.5innodb支持多个bufferPool :innodb_buffer_pool_instances
　　6，5.5支持关闭自适应哈希索引功能
　　哈希索引可以提高查询性能，但是高并发情况下会造成RW-latch挣用，堵塞进程
　　可以：show engine innodb status\G; 监控SEMA-PHORES ,如果waits很多那么应该关闭该功能，从而提升写性能
　　innodb_adaptive_hash_index参数开启或关闭hash索引
　　7，5.5innodb可以宣誓使用内存分配程序
　　TCMalloc每个线程均有缓冲区，可以大幅度提高mysql性能和内存使用
　　mysql多线程小内存分配
　　8，5.5提高默认innodb线程并发数
　　innodb_thread_concurrency默认为0，表示不显示并发数
　　9，5.5实现了异步I/O
　　innodb_use_native_aio参数
　　10,5.5恢复组提交
　　组提交模式只支持sync_binlog=0，innodb_support_xa=0的情况下 目的是保证innodb存储引擎redo log事物日志与binlog日志顺序一致
　　11，5.5更新创建索引更快，增加删除索引不会锁表
　　创建和删除非聚集索引不用复制整张表内容了，聚集索引还需要，聚集索引是把primary key和row data 保存在一起，而secondary
　　index 是单独存放，有个指针指向primary key
　　12，5.5支持创建压缩数据页
　　barracuda文件格式且是独立表空间，才支持数据页压缩，另压缩会带来额外的cpu消耗
　　13，5.5可以动态关闭数据元统计功能
　　innodb_stats_on_metadata=OFF 5.6默认是关闭 提升mysql性能
　　14，5.5开启innodb严格检查模式，增加安全性
　　innodb_strict_mode=1 默认是off
　　sql_mode
　　15，5.5支持动态更改独立表空间
　　set global innodb_file_per_table=1 默认共享表空间
　　支持动态更改innodb锁超时时间
　　set global innodb_lock_wait_timeout= 10  默认50秒
　　16，5.5增加半同步复制功能
　　master上4个相关参数
　　rpl_semi_sync_master_enabled=ON 表示master开启半同步功能
　　rpl_semi_sync_master_timeout=10000 默认10000毫秒即10秒等待slave，即某次事物中等待超过10秒降级为异步复制
　　rpl_semi_sync_master_wait_no_slave, 默认ON，表示master每个事务提交后都等待slave接受确认信号，若为off，slave追赶上后不会开启半同步复制
　　rpl_semi_sync_master_trace_level=32，默认32,开启半同步复制的模式的调试级别
　　slave上2个参数
　　rpl_semi_sync_slave_enabled=ON 表示master开启半同步功能
　　rpl_semi_sync_slave_trace_level=32，默认32,开启半同步复制的模式的调试级别
　　半同步复制跟IO_therad有直接关系与sql_therad没关系，也就是说slave 从库接受到二进制日志后会给master主一个确认，并不会管relay-log中继日志否是执行完。
　　异步复制的性能（吞吐率）要好于半同步复制。
　　17，5.5复制功能加强，增加中继日志relay_log自我修复功能
　　relay_log_recovery=1
　　5.6同步复制新特性
　　mysql可以通过GTID自动找同步点，无需指定binglog和pos点（基于事务，myisam不支持）另无法跳过错误，gtid模式下不支持：sql_slave_skip_counter参数
　　开启参数：gtid-mode=ON
　　disable-gtid-unsafe-statements=ON
　　增加基于库的多线程复制，多个库复制可以使用多线程复制，一个库的表里多线程复制无效
　　开启参数：slave_parallel_workers 默认为0 不开启，最大可以开启1024个线程。
　　18，5.6版本后子查询被改为join方式
　　19，5.6新特性 快速预热buffer_pool
　　my.cnf中添加：
　　innodb_buffer_pool_dump_at_shutdown=1 关闭时把热数据备份到本地磁盘ib_buffer_pool文件中
　　innodb_buffer_pool_load_at_startup=1  启动时加载热数据到内存
　　20,5.6新加binlog-row-imaeg参数
　　当binlog_format=row时，binlog-row-imaeg=full ,binlog日志会记录影响的行所有记录
　　binlog-row-imaeg=minimal,binlog日志只记录影响的那一行的记录
　　21,5.6year(2)自动转换为year(4)参数
　　22,5.6在线ddl功能，即增加字段时，增、删、改、查均不受影响，不锁表（5.5锁表）
　　23,5.6一条sql可以使用两个索引,支持全文索引
　　24,5.6支持explain update
　　mysql5.7版本
　　1、安全性
　　mysql.user表的改变
　　初始化方式的改变
　　MySQL官方已经删除了test数据库
　　提供了更为简单SSL安全访问配置，并且默认连接就采用SSL的加密方式
　　可以为用户设置密码过期策略，一定时间以后，强制用户修改密码：
　　ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
　　可以”锁”住用户，用以暂时禁用某个用户：
　　ALTER USER  'jeffrey'@'localhost' ACCOUNT LOCK;
　　ALTER USER l 'jeffrey'@'localhost'  ACCOUNT UNLOCK;
　　2、灵活性
　　支持json
　　1.可以混合存储结构化数据和非结构化数据，同时拥有关系型数据库和非关系型数据库的优点
　　2.能够提供完整的事务支持
　　新增功能generated column

　　就是在CREATE |>　　CREATE TABLE triangle (
　　sidea DOUBLE,
　　sideb DOUBLE,
　　sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
　　);
　　INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);
　　mysql> SELECT * FROM triangle;
　　+——-+——-+——————–+
　　| sidea | sideb | sidec |
　　+——-+——-+——————–+
　　| 1 | 1 | 1.4142135623730951 |
　　| 3 | 4 | 5 |
　　| 6 | 8 | 10 |
　　+——-+——-+——————–+
　　新增sys schema系统库
　　通过sys schema快速的知道，哪些语句使用了临时表，哪个用户请求了最多的io，哪个线程占用了最多的内存，哪些索引是无用索引等
　　sys schema中包含了大量的视图，使用performance schema信息，通过视图的方式给出解决实际问题的答案。
　　2、innodb引擎增强
　　优化了DDL操作
　　MySQL 5.7支持重命名索引和修改varchar的大小，这两项操作在之前的版本中，都需要重建索引或表
　　优化了临时表 InnoDB临时表会存储在一个非压缩的、单独的表空间中，每次启动MySQL服务，都会自动创建该表空间，默认存储在DATADIR下，其路径由参数innodb_temp_data_file_path指定；
　　在线修改buffer pool的大小
　　支持online buffer pool resize
　　在线开启GTID
　　3、SQL_MODE
　　默认启用STRICT_TRANS_TABLES模式；
　　对ONLY_FULL_GROUP_BY模式实现了更复杂的特性支持，并且也被默认启用；
　　其他被默认启用的sql mode还有NO_ENGINE_SUBSTITUTION。
　　在5.6中对一个10字符长度的VARCHAR列写入15个字符，会自动截断并给出告警，而在5.7，则直接抛出错误了。
　　4、优化online操作，例如修改buffer pool、修改索引名（非主键）、修改REPLICATION FILTER、修改MASTER而无需关闭SLAVE线程等众多特性。
　　5、在5.7中，可以在INFORMATION_SCHEMA里面的表中查看MySQL的系统参数
　　6、支持一个表上有多个触发器，这样一来，原先已有触发器表也可以支持用pt-osc 了
　　7、支持对在线某个连接直接查看执行计划，比如EXPLAIN FOR CONNECTION 1024
　　8、新增log_syslog选项，可将MySQL日志打印到系统日志文件中
　　9、在MySQL 5.6以前，在客户端CTRL+C后会直接退出啊MySQL客户端，在5.7以后不会退出客户端而是终端当前的操作
　　10、
　　11、支持多源复制，可以把多个MASTER的数据归并到一个实例上，如果是同一个表的话，会存在主键和唯一索引冲突的风险，需要提前做好规划。
　　12、支持多线程复制。并行复制