叶金荣 mysql优化_知数堂 叶金荣 - MySQL 5-7让优化更轻松

1. MySQL 5.7让优化更轻松 叶金荣

2. 关于我 • 知数堂联合创始人 • Oracle MySQL ACE • 公众号 老叶茶馆(imysql_wx) • 个人网站 http://imysql.com

3. MySQL 5.7搜索趋势

4. MySQL roadmap

5. MySQL 5.7 官方号称5.7比5.6性能提升3倍以上

6. MySQL 5.7 图片来源《Whats New in MySQL 5.7》by Geir Høydalsvik & Simon Mudd

7. MySQL 5.7

8. MySQL 5.7

9. MySQL 5.7 是时候全面放弃MyISAM了

10. MySQL 5.7

11. MySQL 5.7

12. MySQL 5.7 InnoDB增强

13. InnoDB增强 1、Online buffer pool resize - 由小改大几乎无影响； - 由大改小需要释放部分内存，有一定代价。

14. InnoDB增强 resize innodb buffer pool from 64G to 16G

15. InnoDB增强 2、VARCHAR in-place enlarge - 255字节长度是个门槛； - 不跨越255长度门槛即可在线调大； - 没必要再为VARCHAR列预留更大长度； - 缩短长度不能in-place。

16. InnoDB增强 - 成功 #enlarge c1 length from 30 to 50 mysql> ALTER TABLE t1 ALGORITHM=INPLACE, email VARCHAR(50); Query OK, 0 rows affected (0.68 sec) modify - 失败 #enlarge c1 length from 30 to 300 mysql> alter table t1 ALGORITHM=INPLACE, modify char_col varchar(300) not null default ''; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

17. InnoDB增强 3、临时表空间增强 - 使用独立表空间，不写redo、无change buffer； 4、支持在线清除undo log - 避免undo log积累过多；

18. MySQL 5.7 复制增强

19. 复制增强 1、multi source replication/多源复制 - 支持多主一从，从多个master汇聚到一个slave； - 特别适合数据集中分析、集中备份、异地容灾。 2、multi-threaded replication/并行复制 - 基于LOGICAL_CLOCK时序组提交的并行复制，同时 进入PREPARE状态的事务都可以在SLAVE并行应用； - 可解决除大事务外的大部分复制延迟问题。

20. 复制增强 multi-threaded replication

21. 复制增强 multi source replication

22. 复制增强 multi-threaded replication

23. 复制增强 multi-threaded replication 图片来源《Multi-Threaded Replication in MySQL 5.6 and MySQL 5.7》by Stéphane Combaudon

24. 复制增强 multi-threaded replication 图片源自《Multi-Threaded Replication in MySQL 5.6 and MySQL 5.7》by Stéphane Combaudon

25. 复制增强 multi-threaded replication 图片源自《Whats New in MySQL 5.7》by Geir Høydalsvik & Simon Mudd

27. 复制增强 3、lossless semi-sync replication/无损半同步复 制 - 设置master事务提交点AFTER_SYNC 或 AFTER_COMMIT，提高复制可靠性； - 接收、发送信号线程分离(串行变并行)，提高复制 效率。 4、group replication/组复制 - 类似Gelera Cluster(PXC)架构，支持多点并行写 入，同时提供读负载均衡； - 只支持InnoDB引擎，表必须有主键，且基于RBR。

28. 复制增强 semi-sync replication

29. 复制增强 lossless semi-sync replication

30. 复制增强 增强semi-sync replication(ack异步且独立线程处理)

31. 复制增强 group replication

32. MySQL 5.7 p_s、sys增强

33. p_s、sys增强

34. p_s、sys增强 sys schema中，schema、table相关统计视图

35. p_s、sys增强 table对象DML统计

36. p_s、sys增强 table对象DML统计

37. p_s、sys增强 index对象usage统计

38. p_s、sys增强 redundant indexes

39. p_s、sys增强 unused indexes

40. p_s、sys增强 row lock waits

41. p_s、sys增强 mdl lock waits

42. p_s、sys增强 I/O写最多的文件

43. p_s、sys增强 热门SQL TOP10

44. p_s、sys增强 查看实例内存消耗

45. p_s、sys增强 查看内部对象内存消耗

46. p_s、sys增强 查看线程内存消耗

47. MySQL 5.7 其他增强

48. 其他增强 1、EXPLAIN增强 - 查看当前活跃SESSION的SQL执行计划 EXPLAIN FOR CONNECTION 3306 - JSON格式输出结果中，可见执行计划代价信息 - 总是启用PARTITIONS/EXTENDED选项

49. 其他增强 2、 JSON & Generated Columns

50. 其他增强 2、 JSON & Generated Columns

51. 其他增强 2、 JSON & Generated Columns

52. 其他增强 2、 JSON & Generated Columns

53. 其他增强 2、 JSON & Generated Columns

54. 其他增强 3、SELECT中设置超时阈值 - SQL Hint - SELECT /*+ MAX_EXECUTION_TIME(10000) */ … - 也可设定 max_execution_time 选项 - 可有效避免某个垃圾SQL导致雪崩 - 参考 - MariaDB/Percona选项innodb_kill_idle_transaction

55. MySQL 8.0新特性 1、支持invisible index - 无需重建索引，快速完成 - in-place设置visible/invisible - force index也不可用 - 索引统计信息仍旧更新 - 主键不能invisible

56. MySQL 8.0新特性 2、descending index - 有了倒序索引，倒序扫描效率更高了 - 也支持虚拟列(VIRTUAL、STORED) - 只支持B+TREE，不支持HASH、FULLTEXT、 SPATIAL索引 - 只支持InnoDB

57. MySQL 8.0新特性 - SELECT增加NOWAIT、SKIP LOCKED选项 - InnoDB memcached支持mget指令 - InnoDB表自增列最大值持久化 - 直方图(Histogram)

58. THANK YOU