《MySQL实战45讲》学习小结(应用篇)

丁奇老师《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条:

  1. 了解sql执行原理,了解导致sql慢的原因和常见的应对方法,了解每种方法的代价

  2. 不要踩坑,比如:条件字段函数操作、隐式类型转换、隐式字符编码转换(第18讲)。这个有切肤之痛。有个sql,要查某一天的订单,小伙伴写了 date(...)=xxx,没做好code review,就这样上线了,第二天早上一起量,整个系统就挂了。。。

  3. 做好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讲》的学习笔记,只是一个提纲。

这门课程的内容和组织方式,每一讲的思考题、大家的留言、老师的点评,都非常棒。

强烈推荐!

极客时间 - MySQL实战45讲

黄鹤

2019-12-11

 

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

黄鹤

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值