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

2020-02-23 290浏览

  • 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_file_path 1G • 5.6 • innodb_log_file_size 5.5 • innodb_flush_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_files_limit & innodb_open_files • 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 • • file i/o • 32bit purge bug
  • 29. ibdata1 • ibdata1 • 5.6 • purge • 64-bit file 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 filter GTIDs Group Replication
  • 45. MySQL 5.7 • • MySQL Router • Generated Columns • JSON • sys schema trigger • • GIS