mysql 5.7特性
简介:mysql 5.7内存和线程性能方面的优化
一 细节优化
参数:
1 innodb_buffer_pool
改进 innodb_buffer_pool可以动态扩大,这样在机器内存扩容后,不必重启服务就可以动态扩大了
调整
set global innodb_buffer_pool_size= GX3072 MX2048
放大 不会影响性能
缩小 可能会耗费时间,因为要将占用的空间释放掉
show global status like 'innodb_buffer_pool_resize_status'; //查看调整结果
Resizing buffer pool failed, finished resizing at 180129 10:18:23. 失败
Completed resizing buffer pool at 180129 10:23:50.成功
2 innodb_purge_threads
改进 purge_thread的线程数由默认为1调整到默认为4 加快标记为废弃undo页的回收速度
3 innodb_page_cleaners
改进 page_cleaners的线程数由默认为1调整到默认为4 加快buffer_pool里脏页的刷新速度
4 max_execution_time
单位是毫秒，SQL 语句的超时中断，自我保护的一种方案.只针对 select，也可以在 sql 里指定,此参数可以很好的限制慢查询
此参数默认为0,表示无限制,建议根据业务场景进行规定
5 default_password_lifetime
提供了账户信息过期的信息,建议关闭,默认也是关闭,如果需要安全认证,可选择性开启,但是一定要注意账号过期会被锁定
6 undo相关参数
1 innodb_undo_tablespaces 设置undo表空间 一般至少2个 默认为0 至少设置2个 命名为 undo001..00n
2 innodb_max_undo_log_size 设置单个undo文件的大小 一般是1G
3 innodb_undo_logs 设置回滚段的个数 默认是128个
undo的分离有效的防止了ibdata无限增大无法收缩的情况,但只支持初始化设置
7 super_read_only
改进 新版本可以对超级用户进行限制,这样哪怕超级用户在从库也无法执行DML和DDL语句了
8 更为严格的sql model
ONLY_FULL_GROUP_BY
STRICT_TRANS_TABLES
NO_ZERO_IN_DATE //未来将移除
NO_ZERO_DATE //未来将移除
ERROR_FOR_DIVISION_BY_ZERO//未来将移除
NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION
各位可根据自己的适用场景进行分析
9 临时表
innodb_temp_data_file_path 临时表的文件路径,按照12M自动扩张
1 InnoDB临时表的元数据保存在一个新的系统表中 从ibdata（共享表空间文件）中分离.并且可以重启重置大小，避免出现像ibdata难以释放的问题
2 在实例关闭之后，将会被删除。在实例启动时则会被创建。
3 通过information_schema.INNODB_TEMP_TABLE_INFO 可以查看临时表信息
4 临时表空间中存储这非压缩的InnoDB临时表，如果是压缩的InnoDB临时表，则需要单独存储在各自的表空间文件中，文件存放在 tmpdir（/tmp）目录下,默认是不会指定压缩格式的
5 某些特定的ONLINE DDL也会存放在这里(一般不建议直接DDL,需要采用pt工具,因为虽然不会打满tmp下,但是文件会不断增大,只有重启才能缩小)
总结两点
1.临时表只在当前会话中可见,不会记录日志
2.临时表的生命周期是当前连接（MySQL宕机或重启，则当前连接结束）
10 系统表默认引擎
系统表的默认引擎不再为memory,而统一设置成为innodb
11 redo~重启恢复
MySQL5.7.5之前,在recovery的过程中,需要扫描所有的ibd文件,获取元信息5.7.5之后，新加了一种redo log类型,即MLOG_FILE_NAME,记录从上一次checkpoint以来，发生过变更的文件，这样在recovery的过程中，只需要打开这些文件就可以了。
12 引入JSON列数据类型及相关函数
增加了json列类型以及json_开头的函数，如json_type(),json_object(),json_merge()等
eg: insert into json_test(jos) values(json_object("key1",1,"key2",2))
和mongodb对比的优势
1.可以混合存储结构化数据和非结构化数据，同时拥有关系型数据库和非关系型数据库的优点
2.能够提供完整的事务支持
3.本质是将JSON编码成BLOB，然后交由存储引擎层进行处理,这样看来,myisam也同样支持json
13 新增generate column
virtual generated column
只将generated column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上
stored generated column
会将generated column持久化到磁盘上，而不是每次读取的时候计算所得需要更多的磁盘空间,在默认不指定的情况下,只会建立virtual
eg:Create Table: CREATE TABLE `triangle` (
`sidea` double DEFAULT NULL,
`sideb` double DEFAULT NULL,
`area` double GENERATED ALWAYS AS (((`sidea` * `sideb`) / 2)) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
优势 如果需要在计算结果加索引,这里是可以实现的,但是代码层实现就比较困难了
14 修改复制过滤规则
在线设置 复制的过滤规则 不再需要重启MySQL，只需要停止SQL thread，修改完成以后，启动SQL thread,5.6必须重启从库服务(不建议在主库设置过滤规则)
15 在线DDL的改进
支持在线重命名索引和修改varchar的大小,采用in_place方式,不需要再利用tmp表
16 消除无用库
已经消除了test库,增强了数据库的安全性
17 触发器的改进
每个表上对于某一触发事件(insert\update\delete)和触发时机（before\after）的组合只能有一个触发器，新版本取消了这一限制
18 原理性优化
1 变量的初始化操作（THD、VIO）下发给工作线程，以减少连接接收线程的工作量，提高连接的处理速度。这个优化对那些频繁建立短连接的应用，将会非常有用。
2 避免为只读事务分配事务ID ，不为只读事务分配回滚段，减少锁竞争等多种方式，优化了只读事务的开销，提高了数据库的整体性能->qps
3 innoDB分区表。1 已支持HANDLER语法 2 分区表查询已支持ICP特性
4 DYNAMIC替换 COMPACT为InnoDB表的隐式默认行格式
二 架构优化
1 多源复制的支持
2 半同步复制的优化
3 并行复制的表级别支持
4 新型GTID在线转化的支持
5 传统复制关于binlog锁的优化
6 出现新型高可用架构MGR