mysql快速讲义

  • 34 浏览
文字内容
1. Mysql快速讲义 峰云就她了了 xiaorui.cc
2. about me Name: 峰云就她了 Blog: xiaorui.cc Github: github.com/rfyiamcool Desc: 喜欢研究高性能服务端、数据库相关
3. LIST 01 Mysql Basic 02 Mysql Ops 03 Mysql Optimize 04 Mysql集群
4. 01 基本选择
5. 版本 Mysql >= 5.7 Percona Mariadb more … # recommend
6. 秒杀压测 测试: update goods set stock =stock -1 where id = 1 and stock >3 并发连接 mysql 5.7 percona5.7 mysql (关闭死锁) percona (关闭死锁) 256 2000 4000 6000 4200 512 500 4300 2500 4350 1024 30 5000 1500 5200
7. 引擎的选择 Myisam # 淘汰的玩意 Innodb # 优秀全能⼿ Tokudb # ⾼压缩率 Myrocks # 基于lsm的rocksdb
8. 表结构设计 innodb⼀定要有主键 主键最好整型, 并单调递增 将text/blob⼤对象独⽴存放 要注意字符集/ 校验集的⼀致性, 避免类型隐式转换 尽量不要使⽤外键
9. 表使⽤规范 使⽤Not null, default ‘’ 少⽤limit m, n取范围 多⽤limit, 减少检索及输出 避免 select * 避免使⽤ 存储过程
10. 表使⽤规范 表的索引不要太多 表的字段不要太多 单表⾏数在1000w
11. 索引基本规则 idx(a, b, c) HIT where a = x and b = x idx(a, b, c) HIT where a > x idx(a, b, c) Not HIT where b > x idx(a, b, c) Not HIT where a > x and b = x idx(a, b, c) Not HIT where a = x and c = x
12. 索引基本规则 idx(sex) 没必要建⽴索引 显⽰ 和 隐式 join 性能与结果⼀致 from a,b vs from a inner join b on
13. 索引⾼级规则 idx(a, b, c) HIT where a = x order by b idx(a, b, c) HIT where a > x order by a idx(a, b, c) HIT where a = x and b > x order by a idx(a, b, c) Not HIT where a > x order by b idx(a, b, c) HIT where a = x group by a, b idx(a, b, c) Not HIT where a = x group by b
14. 联表索引 驱动表 笛卡尔积 nested loop join for each row in t1 matching range { for each row in t2 matching refer key { for each row in t3 { if row satisfies join conditions, send to client } } }
15. 联表查询 简单说, ⼩表驱动⼤表 右表的条件列⼀定要加上索引 显⽰ 和 隐式 join 性能与结果⼀致 from a, b vs from a inner join b on Inner⾃动选择驱动表, left join 选择左⾯表.
16. 关键 多⽤Explain 多看慢查询 优化器会随着数据量变⼤发⽣变化
17. explain using index; High using where; using where; using index; using filesort; using temporary; Low
18. 02 排查问题
19. 事务锁 通过三表信息能快速发现哪些事务在阻塞其他事务 INFORMATION_SCHEMA.INNODB_TRX INNODB_LOCKS INNODB_LOCK_WAITS
20. 慢查询 开启定义慢查询条件及开关 slow_query_log # 开关 slow_query_log_file # ⽇志路径 long_query_time # 超时时间
21. 管理连接 show processlist; # 正在进⾏行行的sql语句句 . Sleep Sending data Waiting for tables more… kill id; 杀掉某任务
22. 03 Ops Mysql
23. 修改表结构 阻塞 alter table xxxx 非阻塞 pecona/ pt-online-schema-change 触发器⽅案 github/ gh-ost 模拟主从协议
24. percona toolkit pt-heartbeat pt-table-checksum pt-table-sync pt-archiver pt-kill
25. 热备份 Mysqldump ⾮非⼀一致性备份 Mysqlpump 基于表并发 Percona XtraBackup
26. binlog2sql 1. binlog ⽇日志格式为 row 2. 找到相关的sql pos点 3. 导出回溯sql语句句 4. 导⼊入重写修改的数据 后悔药
27. 04 Optimize Mysql
28. innodb innodb_buffer_pool_size innodb_log_file_size innodb_log_buffer_size innodb_flush_log_at_trx_commit innodb_file_per_table innodb_buffer_pool_instances more
29. innodb autocommit innodb_deadlock_detect innodb_lock_wait_timeout FB:'>FB: 可以这么调优lock_deadlock … Mysql:'>Mysql: ⽅方案不不靠谱 FB:'>FB: 我写完了了, 已提pr了了 Mysql:'>Mysql: 已经merge了了
30. sql 连接池复⽤用 批量量更更新减少rtt消耗 避免⼤大数据返回 适当的压缩数据 多⽤用乐观锁 more …
31. cmd iostat -x 1 io perf dstat 最重要 !!! free -m mpstat -p ALL top cpu
32. 再提⾼ 硬件 升级 SSD 升级 ⼤大内存 system ⽂文件系统xfs/zfs 软中断 分布式 分库分表 中间件 磁盘算法
33. 05 mysql cluster
34. Master/Slave Slave Master Slave Slave 级联复制
35. HA Master Slave mha ha Slave Master Slave convert new Master !!!
36. 可靠 ? slave replication semi replication semi replication + 主从切换的集群环境下: 不能保证消息的一致性
37. how ? 阿⾥里里⾃自研 腾讯⾃自研 京东⾃自研 各⼤大云⼚厂商⾃自研 more 通过paxos, raft来保证消息的一致性.
38. HA keepalived multi master repl heartbeat drdb mysql galera mha # recommend mysql group replication # recommend
39. MGR client Master client Master Master
40. 中间件 Mycat Kingshard DBProxy Cobar More # recommend
41. Q&A