丁奇老师《MySQL实战45讲》的学习小结
第三篇：合理使用MySQL
题目很大，写得出来的很少 -_-!
把自己的理解梳理出一个提纲，作为一个速查手册吧。
第一部分：sql优化
1. 了解一个sql如何被执行
最重要的是搞清楚MySQL是如何执行一个sql命令的。
explain命令
最简单，也是最常用的：explain命令。
看看执行次序，看看是否用了索引、哪个索引、要扫描多少行记录（估算）。
OPTIMIZER_TRACE（第16讲）
可以打开optimizer_trace，只对本线程有效。
执行sql命令后，查询information_schema.OPTIMIZER_TRACE，可看到更多信息，例如一个排序语句是否使用了临时文件。
执行sql命令前后分别查询 performance_schema.session_status 表的 Innodb_rows_read 值，可以计算这个sql命令扫描了多少行记录。
慢SQL日志（第19讲）
查看慢日志sql，可查看sql的扫描行数 Rows_examined。
扫描行数可用于印证sql的执行过程，是一个比较有用的信息。
select @@slow_query_log; — 是否开启慢日志
select @@slow_query_log_file; — 慢日志文件路径
select @@long_query_time; — 阈值，单位为秒，超过这个时间视为慢sql，才记录日志
2. 跟sql执行过程相关的一些概念
索引
主键索引为聚簇索引。非主键索引需回表，除非包含了sql用到的所有字段，即覆盖索引。
一般情况下，建议使用自增主键，这样非主键索引占用的空间小，存储效率和查询效率高。
建议尽量使用普通索引，少用唯一索引（第9讲）
索引的数据结构（第4讲，第5讲）
索引的基数（cardinality）（第10讲）
前缀索引、倒序存储、hash字段（第11讲）
函数索引（第37讲，5.7版本以上）
change buffer（第9讲）
如果一个业务的更新模式是写入之后马上会做查询，建议关闭 change buffer。
join（34讲，35讲）
MySQL优化器根据两个表的字段、行数（根据sql中每个表各自的条件筛选），统计数据量。以小表为驱动表，大表为被驱动表。
如果大表在join字段上有索引，适用 Index Nested-Loop Join（NLJ）算法，对小表中的每一行，从大表中查找匹配的记录，形成结果集。
如果大表在join字段上没有索引，适用 Block Nested-Loop Join 算法，先把小表读入 join_buffer，然后扫描大表，对于大表的每一行，跟join_buffer中的数据进行匹配，形成结果集。如果小表的数据不能一次性全部读入 join_buffer，需要分批读入，则需要多次扫描大表。
sort_buffer（16讲，17讲）
mysql提供了两种排序方法：全字段排序 和 rowid排序。
全字段排序：将结果集及排序所需的所有字段载入 sort_buffer，进行排序。如果数据量超出buffer size，需要用到临时文件做归并排序。
rowid排序：只把满足条件的排序字段和主键字段载入 sort_buffer，排序后，根据主键，逐行回表获取数据，组成结果集。如果sort_buffer装不下，也要用临时文件辅助排序。但rowid排序需载入内容的数量小，能载入更多行，需要文件辅助排序的概率小。
MySQL优先选择全字段排序。
内部临时表（第37讲）
执行union语句、group by语句时，MySQL会自动创建内部临时表，存储中间数据，并进行排序、计数、sum、去重等操作。
内存临时表默认大小为16M，如果超出这个大小，就会转成磁盘临时表，默认使用InnoDB引擎。
group by 语句如果能用上索引，就可以免去排序这个过程。
外部临时表（第35讲，36讲）
create temporary table，只对当前连接有效。read-only，也可以使用。
多用于优化join，减少对大表的扫描次数，应用 NLJ 或 BKA 算法。
如果binlog格式为statement/mixed，记录临时表相关操作。如果格式为row，不会记录。
3. sql优化
sql优化，没啥固定的招式。自己的体会，比较重要的是3条：
-
了解sql执行原理，了解导致sql慢的原因和常见的应对方法，了解每种方法的代价
-
不要踩坑，比如：条件字段函数操作、隐式类型转换、隐式字符编码转换（第18讲）。这个有切肤之痛。有个sql，要查某一天的订单，小伙伴写了 date(…)=xxx，没做好code review，就这样上线了，第二天早上一起量，整个系统就挂了。。。
-
做好code review，做好慢sql监控，多用explain等方式检查执行计划，不要偷懒
第二部分：数据库配置和运维
作为数据库的使用者，我希望运维来管理和处理哪些问题？
如果我负责数据库的运维，会怎样设置优先级，哪些能独立完成，哪些需要其他岗位的配合？
对于数据库来说，正常情况下 “数据可靠性” > “系统可用性” > 优先，运维要做的是在数据可靠的基础上，尽量提高可用性。
为了达成这个目的，监控必不可少。做好应对问题的预案、定期演练，很重要。
1. 数据可靠性
日志的“双1”设置（第23讲）
innodb_flush_log_at_trx_commit = 1，每次事务的 redo log 都直接持久化。
sync_binlog = 1，每次事务的 binlog 都直接持久化。
关联的还有2个参数：
binlog_group_commit_sync_delay
binlog_group_commit_sync_no_delay_count
这两个参数跟数据可靠性无关。在磁盘IO成为瓶颈的情况下，可结合syn_binlog来提升可用性。
binlog格式（第24讲）
三种格式：statement, row, mixed。
row格式，能更好地保障主库和从库之间（特别注意主备切换过程）的数据一致性。
代价是磁盘空间
参考设置：
binlog_format = row
binlog_row_image = FULL
GTID（第27讲）
用于解决“一主多从”结构下，主备切换时，新的主库和从库之间的数据同步。
备份
这个无需多说
版本
尽量升到高版本。
5.6版本引入GTID、并行复制，5.7版本引入SQL动态重写。
semi-sync（第28讲）
主库在结束一个事务之前，等待至少一个从库收到binlog的response。
可避免主库在完成事务，但尚未向从库发送binlog时损毁，导致的数据丢失。
对可用性的影响较大，不建议打开。
2. 可用性：正常使用
磁盘IO（第12讲）
innodb_io_capacity，建议设为磁盘的IOPS，可用fio工具来测试
redo log文件，4个1G的文件（内存128G、innodb_io_capacity为20000的情况）
缓存相关
join_buffer, sort_buffer：根据应用场景，调整其的大小
MRR可优化join，但从教程中信息来看，MySQL似乎并不推荐，谨慎使用。（第35讲）
change buffer：根据应用场景，判断是否需要启动。
事务隔离级别（第20讲）
把binlog格式设为row，应对可以保证主从一致。
InnoDB的缺省事务隔离级别为RR。
RR的隔离性比RC好，解决了不可重复度问题和幻读问题。因为增加了gap lock，锁的数量大大增加，死锁概率升高，死锁检测也要消耗资源。一致性读也要消耗资源。
在业务允许的情况下，调整为RC，对性能有帮助。
读写分离，负载均衡（28讲）
一主多从，读写分离，这已是常规配备。
两个问题：1）如何做负载均衡，2）过期读
这里只说第一个问题，如何负载均衡。综合来看，proxy转发是比较好的选项。
在此基础上，我倾向再拿一个从库出来，让某些查询固定通过这个从库来操作，例如：数据维护操作、某些运行时间长及时性要求低的统计查询。
限制并发线程数（第29讲）
innodb_thread_concurrency，缺省为0，不限制。建议设置为 64~128 之间的值（或CPU核数的2倍）。
分库
划分业务领域，拆分为多个库，分开存储。
这是个系统工程，需要业务系统开发、数据库运维合作。
3. 可用性：故障恢复
服务器故障的情况下，主要的可用性指标，是多久可以恢复可用。
全量备份 + 增量备份
全量备份越频繁，恢复时间越短，存储空间代价也越大。
主备双M配置
这个现在是常见手段，一旦主库故障，备库可以马上顶上来。
从库延迟备份
为了应对误删数据问题，可考虑采用从库延迟备份策略。
检测数据库是否出问题（29讲）
有了这些备份，还需要做好监控，尽早判断服务器是否故障。
推荐用update 系统表 + performance_schema 这种方式（打开performace_schema有10%性能损失，19讲）。
4. 监控
监控，必须单独拿出来写一下。
-
常规要监控的：
-
长事务
-
慢sql
-
主从延迟
-
锁等待
-
执行次数
-
update系统表 + performance_schema
做好监控和报警，才能心中不慌。
以上几条，都是按“数据可靠性”>“系统可用性”来考虑的。
实际中也会遇到服务器故障、短时间压力峰值等情况，需要优先保证系统的可用，可在一段时间内，容忍少量数据错误。这种情况下，需要调整策略。
以后如果有了实战经验，再总结。
在课程中，有几讲实战性特别强，遇到相关问题，可以先看下：
– 避免长事务，
第3讲
，思考题
– 降低锁冲突，
第7讲
，内容及思考题
– 字符串字段索引，
第11讲
，思考题及网友留言
– 业务高峰，MySQL压力太大的应对，
第22讲
，内容及网友留言
– 有损设置及其应对，
第23讲
，思考题及网友留言
– 误删数据后的恢复，
第31讲
，内容及网友留言，各种血泪
系统运维要做好，首选是
预防
问题，其次是
监控
，争取在用户感知之前发现问题解决问题，最后没办法了才是问题的暴露和应对。
平时下功夫，才能少出问题，遇到问题才能快速应对：
-
sql review（包括建索引）
-
监控：长事务、慢sql、主从延迟……
-
设立和推行规范：删数据、删字段、加字段、删表
-
问题处理工具化，定期演练
再推而广之，系统的设计、开发，都要把工作做在前面，多问几个为什么，把原理搞清楚，把逻辑搞清楚。不断学习，不断总结，才能不断提高。
本文内容为丁奇老师《MySQL实战45讲》的学习笔记，只是一个提纲。
这门课程的内容和组织方式，每一讲的思考题、大家的留言、老师的点评，都非常棒。
强烈推荐！
黄鹤
2019-12-11