程序猿都该知道的MySQL秘籍 - 叶金荣

1. MySQL http://imysql.com imysql_wx 2016.5.14

2. • • Oracle MySQL ACE MySQL • • From 2006 • MySQL • http://imysql.com MySQL • • 10 MySQL DBA imysql_wx MySQL

3. Agenda • MySQL • InnoDB or MyISAM • InnoDB • • • DBA 5.7 MySQL

4.

5. MyISAM MyISAM • InnoDB • • MyISAM MySQL TA • MyISAM InnoDB • MyISAM • MyISAM • count(*) • InnoDB insert • MyISAM MYD/MYI order by merge count(*) update count(*) out

6. InnoDB • 95% • InnoDB • InnoDB change buffer merge • InnoDB count(*) WHERE • • InnoDB • MyISAM insert buffer order by count(*) InnoDB update merge COUNT(*) —- count(*) redis —-

7. InnoDB MyISAM • InnoDB TPS • • crash recovery MyISAM InnoDB • buffer • MyISAM [MySQL FAQ] — MyISAM InnoDB

8. so InnoDB

9. InnoDB InnoDB • • B+ ORACLE • • ROW_ID • • InnoDB 4 RR IOT

10. InnoDB

11. InnoDB • InnoDB I/O • autocommit=1 • • VARCHAR/ off-page • TEXT storage • SELECT * I/O

12.

13. • k1 c1, c2, c3 • SQL • WHERE c1 = ? AND c2 IN (?, ?) AND c3 = ? √ • WHERE c1 = ? AND c2 =? ORDER BY c3 √ • WHERE c3 = ? AND c1 = ? AND c2 IN (?, ?) √ • WHERE c1 = ? AND c2 IN (?, ?) ORDER BY c3 x • 5.6 (c1, c3) 5.6 ICP

14. JOIN • inner join

15. JOIN • straight join

16. JOIN • INNER JOIN • LEFT JOIN & STRAIGHT_JOIN • RIGHT JOIN • JOIN JOIN

17.

18.

19. • io scheduler • deadline • noop • cfq

20.

21. • fs • xfs • ext4 • zfs ext3

22.

23. MySQL • innodb_buffer_pool_size 50% ~ 70% • innodb_data_ﬁle_path 1G • 5.6 • innodb_log_ﬁle_size 5.5 • innodb_ﬂush_log_at_trx_commit 0=> • innodb_max_dirty_pages_pct 25%~50% 2=> • innodb_io_capacity SSD=>20000 undo 1G =>1000 5.5 512M 1=> SSD=>10000 PCIe

24. MySQL • key_buffer_size • sync_binlog 1=> 32M 0=> binlog event N=> • long_query_time 0.5 • open_ﬁles_limit & innodb_open_ﬁles • max_connections • thread_handling = “pool-of-thread” • query_cache_size & query_cache_type 65535 80% N binlog

25.

26. • QUERY CACHE QC • • QC QC Waiting for query cache lock • query_cache_size =0 & query_cache_type = 0 • http://t.cn/RAF4d7z http://t.cn/RAF4d7Z

27. ibdata1 • ibdata1 • Data dictionary • Double write buffer • Insert buffer • Rollback segments • UNDO space • Foreign key constraint system tables

28. ibdata1 • ibdata1 undo log • undo log • • ﬁle i/o • 32bit purge bug

29. ibdata1 • ibdata1 • 5.6 • purge • 64-bit ﬁle i/o innodb_purge_threads • • • undo autocommit = 1 autocommit=0

30. • • iphone CHAR(11) NOT NULL DEFAULT ’’ • WHERE iphone = 13900000000 •

31. • • • WHERE iphone = ’13900000000’ iphone BIGINT UNSIGNED

32. • too many connections • • max_user_connections • • extra-port

33. DBA MySQL

34. • • mysqldump • ( ) • +keepalived • MHA • • • => xtrabackup

35. • 10 • 100 • 1000 • DDL 1 SQL N SQL • proxy • • pt-query-digest + anemometer SQL

36. tokudb • • • • / /

37. 5.7

38. 5.6 • • mysqldump • • • fast_shutdown=0 xtrabackup • redo undo log • • • mysql_upgrade 5.5 5.7 P_S I_S mysql

39. 5.7 • • innodb monitor table • • innodb_status_output innodb_status_output_locks old-password • • skip-innodb InnoDB

40. MySQL 5.7 • • error log • root@localhost • • • • 5.7.10 SSL/TLS 360 5.7.10 0

41. MySQL 5.7 “ • • • • ” STRICT_TRANS_TABLES NO_ZERO_IN_DATE ERROR_FOR_DIVISION_BY_ZERO CHAR(20) “ ” 30

42. MySQL 5.7 • • Online DDL • • InnoDB / • • InnoDB buffer pool • • VARCHAR

43. MySQL 5.7 • • InnoDB Fusion-io Non-Volatile Memory (NVM) • InnoDB • Optimizer Hints • • • InnoDB InnoDB

44. MySQL 5.7 • • • • • • replication ﬁlter GTIDs Group Replication

45. MySQL 5.7 • • MySQL Router • Generated Columns • JSON • sys schema trigger • • GIS