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

2020-02-27 193浏览

1.程序猿都该知道的 MySQL秘籍http://imysql.comimysql_wx 2016.5.14
2.关于我 • • Oracle MySQL ACE MySQL • • From 2006 • MySQL • MySQL • •http://imysql.com10 MySQL DBA imysql_wx MySQL
3.Agenda • MySQL • InnoDB or MyISAM • InnoDB • • • DBA 5.7 MySQL
4.先从一个成见开始
5.还死守MyISAM？out了 MyISAM • InnoDB • • MyISAM MySQL TA • MyISAM InnoDB • MyISAM • MyISAM • count(*) • InnoDB insert • MyISAM MYD/MYI order by merge count(*) update count(*)
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=> 2=> • innodb_max_dirty_pages_pct 25%~50% • 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/RAF4d7zhttp://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 • file i/o 64-bit 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