mysql 5.7优化不求人_知数堂 叶金荣——MySQL 5-7优化不求人

1. PHP 2017·北京 全球开发者大会 高可用的 PHP 2017.6.10-11 北京亚洲大酒店

2. MySQL 5.7优化不求人 知数堂联合创始人 叶金荣 2017 PHP 全球开发者大会

3. • 知数堂联合创始人 • Oracle MySQL ACE • MySQL布道师 • 微信：4700963 • 公众号： 老叶茶馆 • 专注培养靠谱的互联 网攻城狮

4. MySQL 5.7搜索趋势 • 2016.11月开始，5.7占绝 对优势 • 2016.12月，5.7.17版本发 布，支持group replication • 事实上，2013.4.23发布5.7， 至今已过去4年多 2017 PHP 全球开发者大会

5. 2017 PHP 全球开发者大会

6. 官方号称5.7比5.6性能提升3倍以上 2017 PHP 全球开发者大会

7. 2017 PHP 全球开发者大会

8. 2017 PHP 全球开发者大会

9. InnoDB引擎增强 性能提升 1 只读事务性能提升；临时表性能提升；page clean效率提升；索引更新效率提升 功能提升 2 buffer pool online resize；varchar in-place enlarge；设备原子写特性检测； 其他增强 3 monitor增强；undo log truncate；原生支持表分区；支持通用表空间； 2017 PHP 全球开发者大会

10. 是时候放弃MyISAM了 2017 PHP 全球开发者大会

11. 2017 PHP 全球开发者大会

12. 2017 PHP 全球开发者大会

13. InnoDB buffer pool功能增强 Online buffer pool resize • 由小改大几乎无影响； • 由大改小需要释放部分内存，不过影响也不大，秒级完成。 Buffer pool dump增强 • 设置innodb_buffer_pool_dump_pct，只导出最热那部分数据； • 系统负载高的时候，会根据innodb_io_capacity设置自动限制buffer pool dump的速度。 2017 PHP 全球开发者大会

14. VARCHAR in-place enlarge mysql> ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255); • 255字节长度是个门槛； • 不跨越255长度门槛即可在线调整； • 这使得增加VARCHAR列长度毫无压力； • 也就没必要再为VARCHAR列预留太大长度了； • 不过，不支持VARCHAR列长度in-place缩减。 2017 PHP 全球开发者大会

15. 其他InnoDB增强 临时表增强 1 临时表使用独立表空间、不记 录redo、没有change buffer、锁 更少 UNDO log 清除 2 执行innodb_undo_log_truncate=1选 项，当undo log超过 innodb_max_undo_log_size时自动 truncate page clean 效率提升 其他增强 3 支持多个page cleaner线程，提 高dirty page flush效率 4 索引批量更新效率提升3倍； InnoDB原生支持分区，效率更 高；自动检测设备是否支持原 子写 2017 PHP 全球开发者大会

16. MySQL复制增强 多源复制 1 支持一从多主，由多个主服务 器汇聚到一个从服务器，可用 于数据分析或异地容灾、集中 备份 并行复制 2 基于LOGICAL_CLOCK时序组提交 的并行复制，同时进入PREPARE状 态的事务都可以在SLAVE并行应用 组复制 3 类似Gelera Cluster(PXC)架构， 支持多点并行写入，同时提供读负 载均衡，只支持InnoDB引擎，所有 表必须都有主键，RBR 半同步增强 4 设置master事务提交点AFTER_SYNC 或 AFTER_COMMIT，提高复制可靠性； 接收、发送信号线程分离(串行变并行 )，提高复制效率 2017 PHP 全球开发者大会

17. 2017 PHP 全球开发者大会

18. sys schema增强 schema table相关统计视图 2017 PHP 全球开发者大会

19. sys schema增强 table DML统计 2017 PHP 全球开发者大会

20. sys schema增强 table index usage统计 2017 PHP 全球开发者大会

21. sys schema增强 redundant indexes 2017 PHP 全球开发者大会

22. sys schema增强 unused indexes 2017 PHP 全球开发者大会

23. sys schema增强 row lock wait 2017 PHP 全球开发者大会

24. sys schema增强 metadata lock wait 2017 PHP 全球开发者大会

25. sys schema增强 I/O写最多的文件 2017 PHP 全球开发者大会

26. sys schema增强 热门SQL TOP 10 2017 PHP 全球开发者大会

27. sys schema增强 查看实例消耗内存 2017 PHP 全球开发者大会

28. sys schema增强 查看内部对象内存消耗 2017 PHP 全球开发者大会

29. sys schema增强 查看线程内存消耗 2017 PHP 全球开发者大会

30. Optimizer增强 • UNION ALL不再产生临时表(除非需要排序) • IN子查询效率提升 • 全文检索效率提升 • 排序效率提升 2017 PHP 全球开发者大会

31. EXPLAIN增强 • 查看当前活跃SESSION的SQL执行计划 – EXPLAIN FOR CONNECTION 3306 • JSON格式的输出结果中，还能看到执行计划代价信息 • 总是启用PARTITIONS/EXTENDED选项 2017 PHP 全球开发者大会

32. JSON & Generated Columns 2017 PHP 全球开发者大会

33. JSON & Generated Columns 2017 PHP 全球开发者大会

34. JSON & Generated Columns 2017 PHP 全球开发者大会

35. JSON & Generated Columns 2017 PHP 全球开发者大会

36. JSON & Generated Columns 2017 PHP 全球开发者大会

37. 设定SELECT SQL超时 • SELECT /*+ MAX_EXECUTION_TIME(1) */ * FROM t • 也可修改 max_statement_time 选项 • 这个特性非常实用，有效避免某个垃圾SQL引发雪崩 • 参考：MariaDB/Percona版本选项 innodb_kill_idle_transaction 2017 PHP 全球开发者大会

38. 其他新特性 单表支持多个触发器 • 可以设定 trigger_order，调整不同触发器的优先级 • 兼容 pt-osc 等工具 默认启用严格SQL_MODE • STRICT_ALL_TABLES、STRICT_TRANS_TABLES • 规避一些容易混淆的操作，比如超长内容自动被截断、除零、用’0000-00-00’表示日期、写 入不同类型的数据、不能在GRANT中同时创建用户等 2017 PHP 全球开发者大会

39. MySQL 8.0新特性 • Optimizer增强，如JOIN优化(可能支持hash join)、HINT增强 • 新增支持不可见索引、倒序索引 • CTE(公用表表达式，Common Table Expression)功能及windowing统计函数 • 直方图、UUID优化、减少handler API调用执行计划缓存(可能) • 所有metadata全部存储到InnoDB中 • 增加user role特性，权限管理更方便 • InnoDB表自增列最大值持久化，再也不用担心实例重启后最大自增Id丢失了 2017 PHP 全球开发者大会

40. MySQL 8.0新特性 • InnoDB memcached支持mget指令 • innodb_deadlock_detect在线动态调整，在高负载环境下，建议关闭死锁检测 • InnoDB加密除了支持data page外，增加支持redo log和undo log，提高保密性 • SELECT ... FOR SHARE/UPDATE增加NOWAIT 和 SKIP LOCKED选项 • JSON功能增强，JSON列更新、数据排序效率提升 • 默认使用utf8mb4字符集 • SET PERSIST语法支持修改options后持久化 2017 PHP 全球开发者大会

41. PHP 2017·北京 全球开发者大会 2017 PHP 全球开发者大会