MySQL精讲 mysql实战34讲

文章目录

• mysql实战45讲

• 第一讲：一条查询语句是怎样执行的

• MySql总的架构

• 结构
• 过程

• 分别介绍各个部分

• 一：连接器

• 任务：
• 过程
• 连接管理

• 二：查询缓存

• 形式

• 三：分析器

• 编译原理知识

• 四：优化器

• 介绍

• 五：执行器

• 介绍

• 第二讲：一条更新语句是怎样执行的（日志系统）

• 恢复MYSQL到半个月内任意一秒
• 表创建语句 / update语句
• redo log
• bin log

• 为什么需要两份日志

• 执行update语句的内部流程（引擎和执行器配合）
• 两阶段提交
• 设置相关参数，保证数据不丢失
• 什么情况下：一天一备比一周一备份更有优势？

• 第三讲：事务隔离

• 隔离性 / 隔离级别

• 读未提交
• 读提交
• 可重复读
• 串行化

• 事务隔离中 级别的实现（通过视图）

• 读未提交
• 读提交
• 可重复读
• 串行化

• 事务隔离的实现

• 回滚操作
• 回滚日志
• 不建议使用长事务

• 事务的启动方式

• 显示启动事务语句
• set autocommit=0
• 解决
• 如何避免和处理长事务

• 第四讲：索引（上）

• 索引的常见模型

• 哈希表
• 有序数组
• 搜索树
• 总结

• InnoDB的索引模型

• InnoDB索引使用的是B+树索引模型

• 索引维护
• 自增主键

• 新增一个自增字段作为主键
• 业务字段直接做主键

• 问题

• 第五讲：第五讲：索引（下）

• 回表
• 避免回表——覆盖索引

• 是否需要联合索引

• 最左前缀原则

• 利用B+树的最左前缀原则性质
• 建立联合索引时，如何安排字段顺序

• 索引下推

• 使用最左前缀加速之后

• 问题

• 第六讲：锁——全局锁和表锁

• 全局锁

• 使用场景

• 表级锁（两种）
• 表锁
• 元数据锁

• 第七讲：锁——行锁（InnoDB）

• 两阶段锁
• 死锁和死锁检测
• 问题

• 第八讲：事务到底是隔离的还是不隔离的？

• 
  
• 快照

• 事务ID
• 快照概念
• 可重复读

• 更新逻辑

• 当前读
• 串联一致性读、可重复读、行锁
• 可重复读的能力是如何实现的
• 可重复读和读提交的区别

• 问题

• 第九讲：普通索引和唯一索引，该怎么选择

• 查询过程
• 更新过程

• change buffer
• change buffer在InnoDB的处理流程

• change buffer的使用场景
• 索引选择和实践

• 普通索引和唯一索引该如何选择

• change buffer和redo log
• 问题：

• 第十讲：MySQL为什么有时候会选错索引？

• 优化器的逻辑

• 判断标准
• 扫描行数是如何判断的

• 索引选择异常和处理
• 方法一
• 方法二
• 方法三
• 问题：

• 第十一讲：怎么给字符串字段加索引

• 前缀索引
• 前缀索引对覆盖索引对影响
• 其他方式
• 问题

• 第十二讲：为什么我的MySQL会抖一下

• 为什么SQL语句变慢了

• flush
• 为什么会抖一下
• 什么情况下会引发flush过程
• 分析性能影响

• InnoDB刷脏页的控制策略

• 考虑因素
• 刷脏页的一个策略

• 问题

• 第十三讲：为什么表数据删掉一半，表文件大小不变

• 表数据

• 参数innodb_file_per_table

• 数据删除流程

• 删除记录 / 删除数据页

• 重建表

• 重建表方法
• MySQL对此流程的优化

• online和inplace（有疑问没听懂）

• 概念
• online和inplace的区别

• 问题

• 第十四讲：count（*）这么慢，我该怎么办

• count(*)的实现方式
• 当有一个页面需要经常显示总数

• 用缓存系统保存计数

• 用Redis服务来保存
• 在逻辑上依然不准确

• 用数据库保存计数

• 不同的count用法
• 问题

• 第十五讲：答疑
• 
  
• 第十六讲：“orderby是怎么工作的”

• 全字段排序

• 排序过程
• 备注

• rowid排序

• 单行太大时
• 变化
• 排序过程
• 变化

• 全字段排序 vs rowid排序

• 内存
• orderby
• 进一步简化

• 问题

• 第十七讲：如何正确地显示随机消息

• 内存临时表

• order by rand()
• 执行流程与扫描行数
• MySQL是靠什么定义“一行数据”

• 磁盘临时表

• 不是所有临时表都是内存临时表

• 随机排序方法

• 随机排序方法1
• 随机排序方法2
• 随机排序方法3

• 问题

• 第十八讲：为什么这些SQL语句逻辑相同，性能却差异巨大

• 案例一：条件字段函数操作
• 案例二：隐式类型转换

• 数据类型转换规则是什么
• 为什么有数据类型转换就要走全索引扫描

• 案例三：隐式字符编码转换

• 字符集不同，用不上索引

• 问题

• 第十九讲：为什么我只查一行的语句，也执行这么慢

• 第一类：查询长时间不返回

• 等MDL锁
• 等flush
• 等行锁

• 第二类：查询慢
• 问题

• 第二十讲：幻读是什么，幻读有什么问题

• 幻读是什么
• 幻读有什么问题

• 语义上
• 数据一致性

• 如何解决幻读

• 解决办法
• 间隙锁
• next-key lock
• 带来的困扰

• 问题

• 第二十一讲：为什么我只改一行的语句，锁这么多

• 前提
• 加锁规则

• 原则
• 优化
• bug

• 案例一：等值查询间隙锁
• 案例二：非唯一索引等值锁
• 案例三：主键索引范围锁
• 案例四：非唯一索引范围锁
• 案例五：唯一索引范围锁bug
• 案例六：非唯一索引上存在“等值”的例子
• 案例七：limit语句加锁
• 案例八：一个死锁的例子
• 问题

• 第二十二讲：MySQL有哪些“饮鸩止渴”提高性能的方法

• 短连接风暴

• 先处理掉那些占着连接但是不工作的线程
• 减少连接过程的消耗

• 慢查询性能问题

• 索引没有设计好
• 语句没写好
• 选错了索引
• 总结

• QPS突增问题

• 总结

• 第二十三讲：MySQL是怎么保证数据不丢的？

• binlog的写入机制

• 写入逻辑
• binlog cache
• binlog cache的保存问题

• redo log的写入机制

• redolog的三种状态

• 三种状态
• 通过参数控制

• redolog的写入策略

• 补充说明

• 组提交机制

• 日志逻辑序列号（LSN）
• 保证不会执行重复redolog
• binglog也可以组提交
• 通过设置参数提升效果

• 总结

• WAL是如何提升性能的
• MySQL在IO上出现性能瓶颈，如何解决
• 补充

• 第二十四讲：MySQL是怎么保证主备一致的？

• MySQL主备的基本原理

• 概念
• 尽管备库没有被直接访问，但是要设置成只读
• 设置成只读了，还怎么和主库一致？
• 从主库到备库的内部流程

• binlog三种格式对比

• binlog有三种格式
• binlog为statement情况下
• binlog为row的情况下

• 为什么会有mixed格式的binlog
• 为什么现在普遍要求设为row

• delete
• insert
• update
• 总结
• 如果是mixed？
• 恢复方式

• 循环复制问题

• 结构
• 双M存在循环复制的问题

• 问题

• 第二十五讲：MySQL是怎么保证高可用的？

• 最终一致性
• 主备延迟

• 概念
• seconds_behind_master是如何计算的

• 主备延迟的来源

• 原因一
• 原因二
• 原因三
• 原因四

• 切换策略

• 可靠性优先策略
• 可用性优先策略

• 概念
• 例子
• 把binlog_format设为row

• 什么情况下使用可用性优先策略
• 可靠性优先策略异常切换时是什么样

• 第二十六讲：备库为什么会延迟好几个小时

• 备库的并行复制能力
• MySQL5.5版本的并行复制策略

• 按表分发策略
• 按行分发策略

• MySQL5.6版本的并行复制策略
• MariaDB的并行复制策略
• MySQL5.7版本的并行复制策略
• MySQL5.7.22版本的并行复制策略

• 第二十七讲：主库出问题了，从库怎么办？
• 
  
• 第二十八讲：读写分离有哪些坑
• 
  
• 第二十九讲：如何判断一个数据库是不是出问题了
• 
  
• 第三十讲：
• 第三十一讲：误删数据后除了跑路，还能怎么办
• 
  
• 第三十二讲：为什么还有kill不掉的语句
• 
  
• 第三十三讲：我查这么多数据，会不会把数据内存打爆
• 
  
• 第三十四讲：到底可不可以使用join？
• 
  
• 第三十五讲：join语句怎么优化
• 
  
• 第三十六讲：为什么临时表可以重名
• 
  
• 第三十七讲：什么时候会使用内部临时表
• 
  
• 第三十八讲：都说InnoDB好，那还要不要使用Memory引擎
• 
  
• 第三十九讲：自增主键为什么不是连续的
• 
  
• 第四十讲：Insert语句的锁为什么这么多
• 
  
• 第四十一讲：怎么最快的复制一张表
• 
  
• 第四十二讲：grant之后要跟着flushprivileges吗？
• 
  
• 第四十三讲：要不要使用分区表
• 
  
• 第四十四讲：
• 第四十五讲：自增ID用完怎么办
• 
  

mysql实战45讲

慢查询日志

第一讲：一条查询语句是怎样执行的

通过一条语句的执行过程，将MySql的内部架构串联一下

MySql总的架构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Aga6rjUw-1655861353126)(./FILES/mysqlshi-zhan-45jiang.md/MySql逻辑架构.png)]

结构

包括Server层 和 存储引擎层 的上下两层，
其中
1. Server层
对上：处理用户传来的数据（词法分析语法分析）& 用户权限验证
对下：生成给引擎的指令 & 操作引擎，返回数据 & 协调各个引擎，统揽全局

2. 存储引擎层
由多个存储引擎构成，负责具体数据的存储
多个存储引擎共用同一个上层（Server层）

过程

1. 建立 **连接**    
—— 连接器（确认权限，维持/管理这个连接）
2. 进入查询缓存查询（类似于快表） 
3. 没有命中，真正开始查询
Server层：
确认从客户端传来的命令没有问题
—— 分析器
对语句进行预处理，方便传给下一层
—— 优化器
引擎层：
一行一行执行代码

分别介绍各个部分

一：连接器

任务：

建立连接，获取权限，维持和管理连接

过程

1. 客户端和数据库三次握手
2. 用户名密码正确，则查询该用户所拥有的权限

连接管理

空闲连接：不在操作的，可立即使用的连接
长时间没有动静：连接会自动断开
长连接：每次查询都用同一个连接
短连接：每次查询时，都先建立一个；查询完就断开。

注意事项
    1. 尽量使用长连接
    2. 全部使用长连接后，容易积累内存导致内存占用太大被系统强行杀掉
        解决办法：
        a. 定期断开长连接
        b. 每次执行一个大操作后，附带执行一个操作reset一下资源（非先断开再重新连接）

二：查询缓存

形式

以key-value保存之前查询过的结果

往往利大于弊， 8.0版本后已删去此功能

三：分析器

编译原理知识

1. 词法分析
2. 语法分析

四：优化器

介绍

有多个方案可以得出同一个结果时，选择一个最优方案

五：执行器

介绍

确认权限
调用引擎的接口

第二讲：一条更新语句是怎样执行的（日志系统）

恢复MYSQL到半个月内任意一秒

表创建语句 / update语句

1. 基本的执行链路（上一讲）走一次
2. 日志模块（redo log和bin log两个日志模块）

redo log

内存、redo log、磁盘，是三样东西

类比：孔乙己中的账本与粉板
WAL技术：先写日志，再写磁盘
（先写粉板，不忙时再写账本）

有记录需要更新时

1. InnoDB引擎先先记录到redo log/内存中
2. 系统空闲时再写入磁盘中

特殊情况：当redo log写满时：先腾到磁盘中，再写redo log

1. 什么样叫满：

1. redo log的大小

2. 循环空间存储/擦除 （crash-safe）

1. 两个指针：write和check（头尾指针/追击）
2. write 写的位置
3. check 数据存储的位置（届时从此处开始擦除数据）

redo log的crash-safe技术保证数据库异常重启时之前提交的数据不会丢失。

bin log

redo log为InnoDB引擎独有
而server层独有的为bin log

为什么需要两份日志

1. 背景

1. redo log是InnoDB引擎独有，最初并不是InnoDB引擎

2. 不同点

1. Server层的binlog所有引擎都能用，redo log只有InnoDB能用
2. redolog是物理日志：记录方式
3. binlog是逻辑日志
4. redo log是循环写
5. binlog是追加写

执行update语句的内部流程（引擎和执行器配合）

执行器对数据的逻辑进行操作（取数据，加减，写入）
引擎对数据的物理进行操作（内存/磁盘/读取/返回）

1. 初始
2. prepare阶段

1. 写入redo log之后，写入binlog之前

3. commit阶段

1. 写入binlog之后

两阶段提交

1. 为什么需要两阶段提交

1. 两份日志的逻辑要一致
2. 数据恢复的过程：重放binlog
3. 反证法（假如有先后），以事务（0+1）为例

1. 先redolog再binlog，在redolog写完之后异常重启了

1. binlog中无数据，用binlog重放时会出现问题

2. 先binlog在redolog

1. 在binlog写完之后出问题，redolog没写
2. 由于redolog中无数据，所以该事务（+1）无效
3. 但binlog中数据已经变成值1了

会在需要数据恢复时/扩容（备用库）时出错
主从数据库不一致

设置相关参数，保证数据不丢失

redolog：innodb_flush_log_at_trx_commit=1
 binlog：sync_binlog=1

什么情况下：一天一备比一周一备份更有优势？

第三讲：事务隔离

事务：相当于操作系统中的多个步骤组成的一个原子操作。性质：ACID

隔离性 / 隔离级别

隔离的越严实，效率越低。故通常要找一个平衡点

读未提交

在commit之前就被别的事务看到

读提交

在commit之后才能被别的事务看到

可重复读

整个执行期间，数据都是不变的

串行化

同一行记录（相当于os中一个文件），有读写锁

事务隔离中 级别的实现（通过视图）

读未提交

无视图，就是当时记录的值

读提交

每个，SQL语句执行时创建

可重复读

在启动时创建，整个过程都用同一个

串行化

直接对记录加锁

默认：读提交
配置方式：transaction- isolation：READ-COMMITTED
查看：show variables

事务隔离的实现

回滚操作

数据在update时可以通过回滚操作，恢复成上一个值

回滚日志

系统自动判断何时删除

不建议使用长事务

导致回滚日志必须保留，大量占用存储空间

事务的启动方式

避免无意识的使用长事务

显示启动事务语句

begin/start transaction 搭配 commit / rollback

set autocommit=0

会关闭自动提交，事务会一直存在到你主动commit/rollback或者断开连接
有的框架会默认执行：set autocommit=0

解决

1. 通过set autocommit=1，用显式语句启动事务
2. 多一次交互的问题

1. commit work and chain
2. 在提交本事务时会自动启动下一事务

3. 查询长事务

1. 在information_schema库的innodb_trx中查询
2. 查询语句

如何避免和处理长事务

第四讲：索引（上）

索引：相当于书的目录。数据库表的目录，为了提高数据查询效率。

索引的常见模型

实现索引的方式有很多种

哈希表

1. key:value键值对
2. 数组：用哈希函数计算出一个位置
3. 冲突处理方法之一：拉链法
4. 适用于：等值查询

有序数组

1. 等值查询/范围查询的性能都优秀（二分法）
2. 查询效率高，但是更新数据时麻烦（因为是数组）
3. 只适用于静态存储引擎（不再修改）

搜索树

树根总在内存中

1. 二叉树

1. 左小于父，右大于父
2. 平衡二叉树（加快效率）
3. 大多数数据库不使用二叉树：因为索引要写在磁盘上。

1. 查询数据要来回访问内存和磁盘

2. N叉树

1. N取决于数据块大小，差不多是1200
2. 2^N=能存的数据量来看，一般最多访问3次内存

总结

数据库的适用场景，先看数据模型

InnoDB的索引模型

不同引擎的索引方式不同

InnoDB索引使用的是B+树索引模型

1. 数据都是存在B+树中
2. 索引分为主键索引和非主键索引

1. 主键索引：整行数据
2. 非主键索引：主键的值

3. 查询时的区别

1. 主键索引：直接查询ID（主键）这棵B+树
2. 非主键：需要多扫描一颗索引树
3. 所以，应尽量使用主键查询

索引维护

在插入新值时需要做必要的维护

1. B+树算法

1. 直接插入一条新的记录
2. 挪动数据，空出位置
3. 页分裂

1. 数据页满了，需要申请新页再挪动部分数据
2. 影响性能、影响空间利用率

4. 页合并

1. 页分裂的逆过程

自增主键

新增一个自增字段作为主键

1. 自动+1
2. 方便追加操作，不挪动
3. 有业务逻辑时，不能保证有序插入，

1. 数据成本高
2. 性能受影响
3. 存储空间（身份证号：32位，整型4字节，长整型8字节）

4. 自增主键往往更合理

业务字段直接做主键

1. 只有一个索引
2. 该索引是唯一索引
3. 可以避免每次都需要搜索两棵树

问题

InnoDB两个重建主键索引的做法，哪个不合适，为什么？

第五讲：第五讲：索引（下）

回表

没有用主键作为关键词来搜索，但还是回到主键索引树来搜索

避免回表——覆盖索引

通过改变查询语句？

是否需要联合索引

1. 有两个字段的索引表（通过身份证号来查姓名）
2. 假如该请求比较高频，那么有必要建立联合索引
3. 用冗余索引来实现覆盖索引，成本比较高需要专人维护

最左前缀原则

用身份证号查家庭住址

1. 既不能全表扫描
2. 又不能为一个不频繁的请求创建一个（身份证号，地址）索引

利用B+树的最左前缀原则性质

只要满足最左端的和所查询的相符，就可以加速检索

建立联合索引时，如何安排字段顺序

1. 索引的复用能力

1. 通过调整顺序，可以少维护一个索引

2. 具体案例

1. 通过一个高频索引来支持某个低频请求

3. 既有联合查询，又有a，b各自的查询

1. 同时维护两个索引
2. 这时需要考虑空间
3. （name,age）还是（age,name） + （age）

索引下推

不符合最左前缀时

使用最左前缀加速之后

1. 把一条条合适的数据先取出来
2. 再用索引中包含的其他字段来比较，去掉某一些不符合
3. 能够减少回表次数

问题

a,b是联合主键。是否需要创建(ca),(cb),©共三个索引

第六讲：锁——全局锁和表锁

初衷：处理并发事件，有冲突时制定访问规则
分类：全局锁、表级锁、行锁（根据加锁的范围）

全局锁

对整个数据实例加锁，整个数据库都是只读的状态。
数据更新语句，数据定义语句（表），提交语句都会阻塞

使用场景

1. 全库逻辑备份

1. 是通过视图

1. 既然通过视图，那只要在可重复读的级别下不行吗？
2. 有的引擎不支持

2. 为什么不通过命令set global readonly=true设置只读

1. 修改global变量影响面更大
2. 异常处理机制不一样
3. 出现异常时，锁会自动释放
4. 出现异常时，global会一直保持

表级锁（两种）

表锁

1. 语法 lock tables……read/write
2. 不仅限制读写（别的线程），也限制本线程的操作对象
3. lock tables命令控制并发的影响面还是比较大，用得少
4. 需要更细粒度的锁

元数据锁

1. 系统会自动加上
2. 增删改查（读锁）——不互斥
3. 表结构变更时（写锁）——互斥
4. 注意点：长事务

1. 语句结束后不会释放，整个事务提交后才释放
2. DDL变更的表有长事务在执行：暂停或者kill

5. 注意点：数据量不大的热点表

1. kill未必管用：新的请求马上就来了
2. 设置等待时间，时间到了先放弃锁，免得阻塞后面的业务语句

第七讲：锁——行锁（InnoDB）

针对于某一条行记录的锁
提高并发度，由各个引擎自己实现

两阶段锁

1. 在commit之后才会释放
2. 要把有可能造成冲突，影响并发度的锁往后放

1. 影响某一行的锁，时间最少
2. 不会等待很长时间

死锁和死锁检测

1. 资源+等待别人
2. 策略：设置超时时间

1. 如果是在线服务，不能时间不能太长
2. 不能设置时间太短，容易误伤
3. 这一种方法使用的少

3. 策略：发起主动死锁检测

1. 主动回滚某一个事务（退回前一个状态）
2. 让别的事务执行
3. 这也是默认状态

4. 主动死锁检测消耗CPU太大

1. 每个加进来的进程都要检测一遍
2. 假设有1000个并发线程更新同一行

5. 解决方法

1. 确保这个业务一定不会死锁——主动关闭

1. 临时使用，且有很大风险。

2. 控制并发度：同一行最多只有十个进程在执行。

1. 在客户端做，不太可行。在服务端做（MySQL源码）
2. 在设计上优化：把一行记录的东西分散到十行来记录

问题

删除一万行数据，选择哪种方法+理由？

第八讲：事务到底是隔离的还是不隔离的？

快照

事务ID

1. 每次更新数据时，会生成一个新的数据版本
2. 每个数据版本有对应对应的事务id

快照概念

1. 可重复读情况下，事务在启动时就会拍下“快照”
2. 并不是拷贝一份数据，而是拿到了这个数据的某个版本
3. 某一行数据可能有多个数据版本
4. 数据版本是通过undo log（回滚日志）和当前版本计算出来的

可重复读

1. 在事务启动时，能够看到所有已经提交的事务
2. 启动瞬间的活跃事务数组：启动但还没提交的所有事务
3. 数组中id最小值：低水位，最大值加1:高水位
4. 该视图数组和高水位：一致性视图
5. 秒级别创建快照
6. 可重复读实现就是判断是否处于不可见的区域

1. 处于，即向前退，一直到数据版本ID小于当前的ID

更新逻辑

当前读

1. 更新数据update指令都是先读再写
2. 别的指令先执行了（由1->2），然后本指令再update
3. 就是在2的基础上update
4. 当前读：读当前值
5. 加锁的select也是当前读

串联一致性读、可重复读、行锁

1. 事务A没有commit
2. 事务B读时是当前读，并且加了行锁，读的值不会改变
3. 实现了一致性读

可重复读的能力是如何实现的

1. 核心是一致性读

可重复读和读提交的区别

1. 可重复读，一开始创建的致性视图一直用
2. 每条语句执行时重新计算视图
3. 读提交的数组是在执行语句时创建的
4. 而可重复读是在commit时创建的

问题

第九讲：普通索引和唯一索引，该怎么选择

普通索引：只加快了对数据的访问
唯一索引：不允许有重复数据
两种索引对查询语句和更新语句的影响

查询过程

1. 在B+树上查找，按层从树根找到叶子
2. 普通索引：找到第一个记录后需要继续向下查找直至不满足的数据
3. 唯一索引：找到第一个满足的记录后就停止
4. 没有太大影响：因为在内存中是整个数据页全读取进来了

更新过程

change buffer

1. 数据在内存：直接更新
2. 数据不在内存：

1. 不读数据，而把操作放在buffer中，该buffer可以持久化（写入磁盘）
2. 这个操作称为merge
3. 在下次读取数据进入内存时会触发merge
4. 有线程定期merge
5. 数据库正常关闭也会执行merge
6. 能够减少读磁盘
7. 能够避免从磁盘读进内存，占内存

3. 什么条件下使用change buffer

1. 唯一索引需要判断是否存在重复
2. 所以必须要读入内存
3. 所以唯一索引不能使用

4. 补充

1. change buffer用的是buffer pool中的内存
2. 不能无限增大
3. 可以通过设置参数来设置允许占用pool的最高比例

change buffer在InnoDB的处理流程

1. 已经在内存（不使用change buffer）

1. 唯一索引：判断是否有冲突，然后插入
2. 普通索引：直接执行

2. 不在内存

1. 唯一索引：读入内存，判断没有冲突，插入
2. 普通索引：写进change buffer，结束

3. 总结

1. 减少的磁盘访问次数，极大提升了效率
2. 而唯一索引需要读入内存，使用不了change buffer，还占用内存

change buffer的使用场景

1. 能够实现加速，但并不是所有使用场景都加速

1. 对于写多读少的业务，change buffer将操作缓存下来
2. 记录的变更数越大，收益就越大

2. 而对于写入之后马上就查询的业务，

1. 不仅没有加速
2. 反而增加了change buffer的维护成本

索引选择和实践

普通索引和唯一索引该如何选择

1. 在查询上没有差别
2. 主要考虑在更新上的性能差别
3. 尽量选择普通索引，甚至把buffer pool中change buffer容量调大一点
4. 但是对于更新之后立即查询的业务
5. 应关闭change buffer

change buffer和redo log

不要混淆redo log和change buffer

1. redo log会把整个过程都记录下来
2. 包括更新内存 + change buffer中的操作
3. redo log节约了写磁盘的IO消耗
4. change buffer是读磁盘的IO消耗（减少了读的次数，在执行update语句当时不读数据，而是下次读数据时update数据）

问题：

突然断电是否会丢失change buffer

第十讲：MySQL为什么有时候会选错索引？

选错索引，导致更长的执行时间

优化器的逻辑

选择索引时优化器的工作，目的是为了一个最优的执行方案以最小的代价执行语句。

判断标准

1. 扫描行数，是否使用临时表，是否排序等

扫描行数是如何判断的

1. 通过统计信息（区分度）来估算
2. 统计信息也就是不同值的个数/基数
3. 基数是如何得到的

1. 针对N个数据页面
2. 使用采样统计（全部统计代价太高）
3. 得到的平均值 * 页面数
4. 当行数超过1/M，会在做一次索引统计
5. 可以通过设置参数来选择两种统计方式

4. 对于具体语句，还有判断这个语句本身要扫描多少行
5. 普通索引时，还要把“回表”的时间算上去
6. 可以修正统计信息：analyze table命令

索引选择异常和处理

原本可以执行的很快的SQL语句却比预期慢很多

方法一

1. 采用 force index强行选择一个索引
2. MySQL回根据词法分析，提供可用的索引作为候选项
3. 自己判断，然后强行选择
4. force index 采用矫正的作用
5. 使用的少

1. 不优美
2. 索引改名后语句也得改
3. 迁移到别的数据库时，语法可能不兼容
4. 不够敏捷

6. 数据库的问题最好在数据库内部解决

方法二

1. 修改语句，引导MySQL使用我们期望的索引
2. 基于具体语句来写

方法三

1. 新建一个更合适的索引
2. 或删掉一个误用的索引
3. 使用的少

这个是MySQL的BUG，需要修改MySQL代码才行

问题：

第十一讲：怎么给字符串字段加索引

以邮箱登录的的系统，如果email上没有索引，就只能全表扫描

前缀索引

1. 可以定义字符串的一部分作为索引
2. 如果不能指定前缀长度，则索引会包含整个字符串
3. 使用整个字符串作为索引

1. 从索引树上查找到‘[e-mail]’，取得它的主键ID2
2. 从主键上查主键为ID2的行，判断email的值是正确的
3. 加入结果集，再向下查
4. 发现不满足email=‘@126.com’了，循环结束
5. 只需要回主键扫描一行

4. 使用前6个字符作为索引

1. 占用的空间更小，但是可能会增加额外扫描次数
2. 可能会多次去主键树中验证
3. 只要长度定义得好，比如取前7个数据时，也只查一次
4. 确定前缀长度最重要是区分度，即重复越少越好
5. 通过语句计算

1. select count()
2. count() as L4;

6. 可能会损失区分度，要预先确定损失比例

前缀索引对覆盖索引对影响

1. 前缀索引不仅增加扫描行数影响性能
2. 并且使用前缀索引就使用不了覆盖索引了

1. 必须要再回主键树查一次

其他方式

1. 当前缀长度不够好的情况

1. 身份证号，同一地区前六位总是相同的
2. 再取前缀为前12位，效率就更低了

2. 解决方法

1. 使用倒序存储

1. 语法
2. 实践中还需要验证下

2. 使用hash字段

1. 在表上再创建一个int字段存校验码
2. 校验码由crc32()函数计算得出
3. 但是crc32()函数可能会得出两个相同值
4. 所以语句的where部分要判断身份证是否精确相同

3. 相同点

1. 都不支持范围查询（大于xx，小于xx），都是字符串

4. 不同点

1. 倒序不增加字段，但是前缀长度更长
2. cpu消耗

1. 倒序每次都要调用reverse()函数
2. hash每次都要调用crc32()函数
3. reverse()函数消耗cpu资源更小

3. 稳定性

1. crc32()也会冲突（相同值），但是概率很小，扫描行数接近1
2. 倒序毕竟还是前缀索引，会增加扫描行数

问题

针对这个特定数据表，如何设计索引

第十二讲：为什么我的MySQL会抖一下

一条SQL语句，正常时很快，但有时会特别慢
这种情况不仅随机，而且持续时间很短，很难复现

为什么SQL语句变慢了

flush

1. WAL中，掌柜总要找时间把粉板内容写进账本，即flush
2. 脏页：内存数据页和磁盘数据页内容不一致
3. 干净页：内存数据页和磁盘数据页内容一致

为什么会抖一下

1. 运行的很快，就是在写内存和redo log
2. “抖一下”，可能就是在刷脏页（flush）

什么情况下会引发flush过程

1. “粉板”满了，redo log写满了（环形）
2. 系统内存不够用了，要淘汰了脏页时需要flush
3. 系统空闲时
4. MySQL正常关闭时

分析性能影响

1. 后两种情况下不需要太关注性能
2. 第一种情况，InnoDB要尽量避免

1. 会导致更新堵塞，不再update

3. 第二种情况，是常态

1. InnoDB用buffer pool管理内存
2. 缓冲池中的内存页有三种状态

1. 还没使用（很少）
2. 干净页（可以正常淘汰，不影响性能）
3. 脏页（需要flush才能用）

4. 脏页影响性能的情况

1. 要淘汰的脏页太多
2. redo log写满了

InnoDB刷脏页的控制策略

避免上面两种两种情况影响性能，故要控制脏页比例

考虑因素

1. 主机的IO能力（全力刷脏页能力）

1. innodb_io_capacity参数
2. 可以设置成磁盘的IOPS

2. 磁盘不能全用来刷脏页，按照“全力”的百分比来刷脏页
3. 刷脏页速度太慢，会导致

1. 内存脏页太多
2. redo log写满了

4. 所以，考虑因素有

1. 脏页比例
2. redo log写盘速度

5. 计算速度

1. 脏页比例上限：参数innodb_max_dirty_pages_pct
2. checkpoint计算出另一个值
3. 两个值可通过一个复杂公式算出
4. 取大者所为刷脏页速度

6. 平时 就需要多关注脏页比例

刷脏页的一个策略

1. 在刷脏页时，刚好本页旁边的一页也是脏页
2. 顺带flush自己的“邻居”
3. innodb_flush_neighbors
4. 参数取0/1可设置是否使用“连坐”机制
5. 在机械硬盘时代，能够提升性能
6. SSD时代，只刷自己就能够更快
7. 该参数在MySQL默认值是0

问题

redo log容量设置得太小会发生什么情况

第十三讲：为什么表数据删掉一半，表文件大小不变

数据库占用空间太大，把表删了之后文件大小不变？

表数据

1. 表结构只能用空间很小，不讨论表结构

参数innodb_file_per_table

1. 控制表数据存在共享表空间还是单独的文件
2. 参数设置为off

1. 在共享表空间
2. 和数据字典放在一起

3. 参数设置为on

1. 在一个以.ibd为后缀的文件中
2. 是默认值和推荐做法
3. 更容易管理
4. drop table可以直接删掉文件

4. 删除表的具体某些行时

1. 非drop table删整张表的操作
2. 表中数据被删除了，表空间却没被回收

数据删除流程

删除记录 / 删除数据页

1. InnoDB的数据都是用B+树结构组织的
2. 删除某个记录时，只会把这个位置标记为删除，文件大小不会缩小
3. delete命令只是把空间标记为“可复用”，没有回收空间

1. 而留下了一个“空洞”，没有数据却占用了空间
2. 类似os中的固定分区大小

4. 插入数据时，当某一页数据不够用，只能插入新的一页

1. 也会在那一页中留下空洞，浪费空间

重建表

能够收缩空间，去掉空洞。

重建表方法

1. 类似于复制数组，将数据一行一行读取并插入到一个新表
2. 命令：alter table A engine=InnoDB
3. 其中插入数据过程比较花时间
4. 在此过程中如果有新数据插入会导致数据丢失

MySQL对此流程的优化

引入Online DDL

1. 将原来的数据存储到临时文件中
2. 将对原表的操作记录到一个日志文件中 (row log)
3. 将操作应用到临时文件上
4. 用临时文件替换原数据

alter语句执行时，加了锁

1. 一开始时写锁，然后在真正拷贝数据之前就退化为读锁
2. 为什么要读锁，而不干脆解锁？
3. 防止其他线程同时做DDL
4. 比较浪费时间，想要比较安全的操作的话，可以使用GitHub开源的gh-ost

online和inplace（有疑问没听懂）

概念

1. 没有优化之前的重建表

1. 表A的临时数据导出是一个临时表，在server层

2. 优化后的重建表

1. 表A重建出来的数据是临时文件
2. 没有把数据挪动的过程
3. 是一个原地操作

online和inplace的区别

1. inplace会堵塞增删改操作，是非Online的
2. DDL过程如果是Online就一定是inplace
3. 反过来未必

问题

执行表收缩之后，空间不但没小而且还变大了？

第十四讲：count（*）这么慢，我该怎么办

计算表的行数，记录数越来越多，这条语句越来越慢

count(*)的实现方式

1. MyISAM：把总行数存起来
2. InnoDB：一行一行读出来，然后计数
3. InnoDB为什么不存起来

1. 与事务设计有关系，可重复读是默认级别
2. 是用多版本并发控制MVCC实现
3. 每一行都要判断对这个会话是否可见
4. “可见的行”才能用于计算总行数

4. InnoDB其实也进行了优化

1. 数据存在B+树中
2. 普通索引树比主键索引树小很多
3. 优化器会找到最小那棵树来遍历

5. show table status很快，但是是估算出来的

当有一个页面需要经常显示总数

基本思路：找个地方把行数存起来

用缓存系统保存计数

用Redis服务来保存

1. 缓存系统可能会丢失更新
2. 需要定期的持久化，但是异常重启时可能会丢失
3. 解决办法，再单独执行一下count(*)

在逻辑上依然不准确

1. 有可能在数据插入和更新Redis值的间隙
2. 进来一个查询，该查询的结果就会不准确

用数据库保存计数

1. 把这个计数单独放到一个计数的表中

1. 能够解决数据丢失的问题

2. 能够解决数据不准确的问题

1. 利用事务的特性
2. 只要commit中，既插入数据，又行数+1
3. 只要commit了，两个操作就同步完成了

不同的count用法

原则：

1. count的语义

1. 有个累计值，符合要求就+1
2. 返回总计数

2. count(主键)——主键为ID

1. 对每个ID，判断不为空，则+1

3. count(1）

1. 对于每一行，判断不为空，则+1
2. 比count(主键)快，避免了解析

4. count(字段)

1. 这个字段是否允许为null
2. 不允许为null

1. 不为null，+1

3. 为null

1. 再把值取出来判断一下，不是null才累加

5. count(*)

1. 不取值

6. 结论

1. 效率：count(字段) < count(主键) < count(1）约等于 count(*)
2. 尽量多使用count(*)

问题

当有一个页面需要经常显示总数，事务中应该先插入记录还是先更新计数表

第十五讲：答疑

第十六讲：“orderby是怎么工作的”

需要按照指定字段排序来显示结果

全字段排序

1. 为了避免全表扫描，需要加上索引
2. explain命令查看执行情况

排序过程

1. 初始化一个sort_buffer用来存结果
2. 找到第一个满足字段排序条件的主键
3. 从主键取出整行，放入buffer中
4. 下一个，直至不满足

备注

1. 排序过程可能在内存或外部排序（磁盘临时文件）中
2. 取决于是否大于为排序开辟的内存sort_buffer_size
3. 可使用语句，判断排序是否使用了临时文件
4. 使用的是归并排序
5. 语句中查到的参数

1. 参与排序的行数/是否做了紧缩处理/扫描了的行数

rowid排序

全字段排序中，只对数据读了一遍，其余操作都在sort_buffer或者临时文件中执行的。
加入查询的字段太多，要分成很多个小文件，性能太差。

单行太大时

1. 单行长度太大会使用rowid排序（总定义长度）
2. 可通过某个参数，控制长度
3. max_length_for_sort_data

变化

1. 原本要排序三个字段
2. 排序时只剩下主键和要排序的列两个字段
3. 返回时少了两个字段的值，就不能直接返回了
4. 不能返回详细的一整行

排序过程

1. sort_buffer中放入两个字段
2. 取要排序字段中排在第一个的主键：id1
3. 取主键为id1的另两个字段
4. 放入sort_buffer
5. 取下一个主键

变化

1. rowid排序多访问了一次主键
2. 返回的结果集是直接返回的，不需要再耗费内存
3. 语句中查到的参数

1. 参数1

1. 在排序完成后还要去原表取值
2. 多读了1000行

2. 参数2

1. 参与排序的只剩两个字段了

3. 参数3

1. 行数不变，但是每一行都变小了
2. 所需要的临时文件也变小了

rowid排序三个字段，把第一个字段排好序，然后依次取主键来排另外两个字段

全字段排序 vs rowid排序

内存

1. MySQL是担心内存太小影响排序效率，才会使用rowid排序
2. 内存足够就会优先选全字段排序，不用去原表再取数据
3. 设计思路：尽量减少磁盘访问
4. rowid排序回表次数多，不会被优先选择

orderby

1. 不是所有的orderby都需要排序操作
2. 只有当表是无序表时才会排序
3. 只要保证从某个字段索引上取出来的行天然就是有序

1. 使用联合索引（两个字段） (city,name)
2. 不需要临时表
3. 也不需要排序

进一步简化

1. 覆盖索引：不需要回主键的索引，内容足够
2. 创建需要排序的三个字段都在的联合索引
3. 但是要注意索引是有维护代价的

问题

这个语句执行过程有排序过程吗？

第十七讲：如何正确地显示随机消息

MySQL中另外一种排序需求。
从单词表中随机选三个单词

内存临时表

order by rand()

1. 使用explain命令查看一下

1. 需要临时表
2. 需要排序
3. 即在临时表上排序

2. 会选择全字段排序 还是 rowid排序

1. 回表只是内存操作，不会导致多访问磁盘
2. 会选择rowid排序

执行流程与扫描行数

1. 生成临时表，
2. 对主键使用rand()函数，生成随机小数，存入临时表

1. 扫描行数是10000

3. 按照新生成的某个字段排序
4. 取前三个

1. 扫描行数增加了一倍，为20000个

5. 从慢查询日志(slow log)来验证

MySQL是靠什么定义“一行数据”

1. 主键删掉仍然可以回表
2. InnoDB会自动生成长度为6个字节的rowid

1. 作为唯一标识
2. 对于有主键的表，rowid就是主键

order by rand()使用了内存临时表，其排序时rowid排序方法

磁盘临时表

不是所有临时表都是内存临时表

1. 文件大小大于当tmp_table_size的配置
2. 内存临时表就会转为磁盘临时表
3. 使用的是没有显示索引的排序
4. 没有用到临时文件，使用的是新引入的排序算法
5. 优先队列算法

1. 之前的排序，明明只需要前三个有序，
2. 使用的是归并排序
3. 可是却排序了10000个，其中9997个是浪费的计算量
4. 解决办法是，使用堆排序
5. 由三个元素构成的堆

随机排序方法

随机排序方法1

1. 取ID最大值M，最小值N
2. rand()函数随机出一个M~N之间的数X
3. 第一个不小于X的行，就是随机出来的结果
4. 效率很高

1. max(ID)和min(id)是不需要扫描的

5. 但是并不是严格意义上的随机

1. 因为ID并不一定是均匀分布的
2. ID之间有空洞

随机排序方法2

1. 解决概率不均匀的问题
2. 总行数*rand()，然后取整，就可以得到一随机行
3. 使用floor()函数，取整
4. 然后取一行：limit Y,1
5. limit Y,1

1. 一个一个扫描
2. 然后丢掉前Y个

6. 要扫描的行数变多，执行代价比方法1高

随机排序方法3

1. 使用方法2的思路，
2. 取三个
3. 相当于使用三次 随机排序方法2

问题

随机排序方法3中，如何减少扫描行数

第十八讲：为什么这些SQL语句逻辑相同，性能却差异巨大

案例一：条件字段函数操作

1. 如果对字段作函数计算，不能使用索引

1. where ‘modi’ =‘2018-7-1’ 可以用索引
2. where month(modi) = 7 不可以用索引

2. 快速定位的能力来源于B+树，同一层兄弟节点的有序性
3. 但是month()函数，传入7之后，在树中就不知道该怎么办了
4. 优化器只能放弃树搜索功能
5. 优化器并不是放弃索引

1. 优化器对比此索引和主键索引大小
2. 选择小的索引

6. 加了month()函数，优化器只能使用全索引扫描

案例二：隐式类型转换

字段类型是varchar(32)，输入参数是整型

数据类型转换规则是什么

1. 简单方法

1. 看 select “10” > 9 的结果

1. 如果是“字符串转成数字”，就是数字比较，结果应该是1
2. 如果是“数字转成字符串”，就是字符串比较，结果应该是0

2. 如果是字符串和数字做比较，是将字符串转换成数字

为什么有数据类型转换就要走全索引扫描

1. 对于优化器来说
2. 类型转换相当于调用函数

案例三：隐式字符编码转换

字符集不同，用不上索引

1. 驱动表：从驱动表中取某个字段
2. 被驱动表：去被驱动表中匹配该字段
3. 用不了索引

1. 再次触发上述的规则，类型转换相当于调用函数

4. 为什么有的也用到了函数，却可以用索引

1. 函数是加在了参数上
2. 可以用

5. 业务上优化方法

1. 修改字符集
2. 修改SQL语句

问题

别的类似的性能问题

第十九讲：为什么我只查一行的语句，也执行这么慢

数据库压力大导致的语句慢不在讨论范围内

第一类：查询长时间不返回

1. select 语句长时间不反悔
2. 表被锁住了
3. 使用show processlist查看语句状态分类解决

等MDL锁

1. 有一个线程在此表 持有MDL写锁或 在请求写锁。
2. 而查询需要读锁
3. 解决办法

1. 使用命令：
2. 找到进程id
3. 找到谁持有MDL写锁，kill掉

等flush

1. 但是flush命令执行都很快（两种）
2. 说明有一个flush命令被别的命令堵住了，然后它又来堵住本select命令
3. 同上的解决办法

等行锁

1. 以上是表级锁，此处select命令进入引擎中
2. 也是占有写锁导致的结果
3. kill可以自动回滚，要找到kill的对象
4. kill这个查询会自动会滚这个连接里的正在执行的线程

第二类：查询慢

1. 当前读的查询，被别的线程影响了。

问题

该语句序列是如何加锁的，加的锁又是什么时候释放的
（加锁规则）

select 语句结束后，加写锁
两阶段锁：commit语句时候释放

第二十讲：幻读是什么，幻读有什么问题

InnoDB默认是可重复读：即前后一致

幻读是什么

1. select加了for update使用的是当前读
2. 同一个事务，两次查询，

1. 后一次查询看到前一次查询没有看到的行
2. 可重复读没有幻读，当前读才有

3. 幻读专指，新插入的行。
4. 同一行两次结果不一致不能称为幻读

幻读有什么问题

语义上

1. for update时应该加锁了，破坏了加锁

数据一致性

1. binlog和数据对不上
2. 到底是怎么引入的数据不一致

1. “执行”一条语句时，只给那一行加了锁
2. 但是binlog中，只会算commit那个时间节点

3. 如何改

1. 在扫描过程中碰到的行也都加上写锁

1. 解决了一行

2. 全部加锁

1. 依然阻止不了新插入的行

如何解决幻读

原因：行锁只能锁住行，不能锁住新加的记录

解决办法

1. 引入间隙锁
2. 锁住了两个值之间的间隙？ 只能5个一组？

间隙锁

1. 是大间隙 ，比如行0和行5之间，0～5都是间隙
2. 和行锁有冲突的是别的行锁
3. 和间隙锁有冲突关系的是“往间隙中插入一个数据”的动作
4. 间隙锁之间没有冲突关系

1. 修改/插入一行
2. 修改/插入另一行
3. 可以正常执行

next-key lock

1. 间隙锁和行锁合称next-key lock
2. 每个next-key lock都是前开后闭区间

带来的困扰

1. 有并发，就容易碰到死锁

1. 逻辑是：先加锁，再操作
2. Session A 先加上 5,10的间隙锁
3. Session B 也加上 5,10的间隙锁
4. Session A 操作，被Session B挡住
5. Session B 操作，被Session A挡住

2. 把级别设为读提交，就不用考虑间隙锁了，但是要解决数据不一致的问题

1. binlog格式设为row

1. 如果读提交隔离级别够用，业务不需要可重复读的保证
2. 读提交锁的范围更小（没有间隙锁），这么操作是可以

问题

你的MySQL配置的是什么隔离级别，为什么这么配置？
有没有碰到什么场景必须使用可重复读级别？
+
分析一个状态

第二十一讲：为什么我只改一行的语句，锁这么多

等值查询： id=7 这种

前提

1. MySQL后期版本可能会改动
2. 可能有bad case。别的情况
3. 一共2+2+1 ： 两个原则，两个优化，一个bug

加锁规则

原则

1. 加锁基本单位都是next-key lock：前开后闭区间
2. 查找过程中访问到的对象才会加

优化

1. next-key lock退化为行锁

1. 索引上等值查询，给唯一索引加锁时

2. next-key lock退化为间隙锁

1. 索引上等值查询，向右遍历且最后一个值不满足等值条件

bug

1. 唯一索引上的范围查询会访问到不满足条件的第一个值为止

案例一：等值查询间隙锁

where id=7

1. 加锁范围

1. 根据原则1：(5,10]
2. 根据优化2:退化为间隙锁，
3. 最终范围(5,10)

2. 别的事务，向其中插入id=8不行
3. 修改id=10，可以

案例二：非唯一索引等值锁

关于覆盖索引的锁，“该锁的不锁，不该锁的乱锁”的感觉

1. 普通索引，仅访问一条记录不会马上停下来

1. 需要向右遍历，知道c=10

2. 根据原则2，访问到的都要加锁，给(5,10]加锁
3. 根据优化2:退化为间隙锁，(5,10)
4. 使用了覆盖索引，没有访问主键，主键索引上没锁
5. 但是要在7插入数据时，就会被间隙锁锁住
6. lock in share mode，只锁覆盖索引
7. for update时，主键索引会被锁上

1. 说明锁是加在索引上
2. 主键也会被锁上

8. lock in share mode，加锁是，必须绕开覆盖索引

1. 方法是加上不存在的字段

案例三：主键索引范围锁

关于范围查询

1. 两个语句逻辑上等价，但是加锁规则不等价
2. 
   

案例四：非唯一索引范围锁

案例五：唯一索引范围锁bug

案例六：非唯一索引上存在“等值”的例子

案例七：limit语句加锁

案例八：一个死锁的例子

问题

第二十二讲：MySQL有哪些“饮鸩止渴”提高性能的方法

不管用什么方案，先让系统跑起来

短连接风暴

1. 建立连接的成本很高

1. 业务高峰期连接数暴涨
2. 除了三次握手还有登录权限判断和获取数据读写权限

2. 数据库压力小点时候，这些成本不明显
3. 数据库压力过大，超过max_connections限制
4. 若方法是调高max_connections的值

1. 可能会导致线程太多
2. CPU资源不够分
3. 故不能如此操作

先处理掉那些占着连接但是不工作的线程

1. kill_connection
2. 和设置wait_timeout一样

1. 一个线程空闲超过多少秒就会被断开连接

3. 如何判断哪些事务外空闲连接

1. show processlist
2. 其中的sleep连接

4. 事务具体状态

1. 查information_schema库的innodb_trx表
2. trx_mysql_thread_id=4
3. 表示id=4的线程，还在事务中

5. 断开哪些

1. 事务外空闲太久的连接
2. 事务内空闲太久的连接

6. 命令 kill connection + id

1. 被服务端主动断开
2. 客户端不会马上知道
3. 在客户端发起下一个请求的时候会报错

7. 可能是有损的

1. 不重新连接的话会一直有问题
2. 客户端会以为服务端一直没有恢复

减少连接过程的消耗

1. 有的业务，短时间大量申请数据库作为备用

1. 让数据库跳过权限验证阶段

2. 方法

1. 重启数据库
2. 使用-skip-grant-tables参数启动

3. 风险极高

慢查询性能问题

索引没有设计好

1. 紧急创建索引来解决
2. 高峰期数据库被语句打挂了

1. alter table语句

3. 比较理想是先在备用库执行

1. 在备用库上执行set sql_log_bin=off

1. 不写binlog

2. 执行alter table语句加上索引
3. 执行主备切换
4. 再去备库（主现在被切换成备库了），执行一遍

语句没写好

1. 犯了错误，导致语句没有使用上索引
2. 改写SQL语句， 使用query_rewrite功能
3. 增加一个语句改写功能

1. 增加方式（查询充血）
2. 确认是否生效

选错了索引

1. 应急方案

1. 加上：force index
2. 使用查询重写功能，给原来语句加上force index

总结

1. 索引没有设计好和语句没写好可以避免

1. 上线前，在测试环境，把慢查询日志打开
2. 把long_query_time设为0
3. 在测试表模拟线上数据，做一遍回归测试
4. 观察每类语句的输出

1. 特别是Rows_examined字段是否与预期一直

5. 如果语句不多，手动泡一下
6. 语句多，使用工具 pt-query-digest

QPS突增问题

1. 某个语句QPS突然暴涨/MySQL压力过大

1. bug
2. 业务高峰

2. 处理办法：下掉这个功能

1. 由全新的业务的bug导致

1. 直接把加的白名单曲调

2. 使用的是单独的数据库用户

1. 删掉这个用户
2. 断开现有连接

3. 和主题部署在一起

1. 处理语句
2. 使用查询重写功能，把压力最大的语句直接重写成 “select 1” 的结果返回

3. 风险很高，存在副作用

1. 别的功能也使用了这个SQL语句模板，会误伤
2. 单独改了这一个语句，可能导致很多业务一起失败

总结

1. 方案3用于止血，优先级最低
2. 方案1、2依赖规范的运维体系：很多功能（虚拟化、白名单机制、业务账号分离等）

第二十三讲：MySQL是怎么保证数据不丢的？

和数据可靠性相关
前面章节：WAL（redo log、binlog)

binlog的写入机制

写入逻辑

1. 事务执行时：先把日志写到binlog cache
2. 事务提交时：把binlog cache写到binlog文件

binlog cache

1. 同一个事务，binlog不能被拆开，一次性写入

1. binlog cache的保存问题

2. 系统给binlog cache分配了一片内存

1. 由参数binlog_cache_size控制

3. 超过这个大小，就要暂存到磁盘
4. 事务提交时，执行器把binlog cache完整事务写入binlog

1. 并清空binlog cache

binlog cache的保存问题

1. 每个线程都有自己的binlog cache
2. 但是共用一份binlog文件
3. write和fsync

1. write是写入内存
2. fsync是持久化到磁盘

4. write和fsync的时机，由sync_binlog参数控制

1. sync_binlog=0

1. 每次提交事务
2. 只write

2. sync_binlog=1

1. 每次提交事务
2. 只fsync

3. sync_binlog=N

1. 每次提交事务
2. 都write，积累N个时fsync

5. IO瓶颈时，把sync_binlog设置大一点

1. 常见为：100～1000

6. 但异常重启可能会丢失近N个事务的binlog

redo log的写入机制

1. redo log要先写到redo log buffer
2. redo log buffer也不是立即持久化到磁盘

1. 没有提交时，因为事务没有提交
2. 即使redo log丢失，也没有影响

3. redo log buffer中部分日志有可能被立即持久化

1. 三种状态

redolog的三种状态

三种状态

1. 存放在redo log buffer中（内存中）

1. 快

2. 写在磁盘（page cache）中，但没有持久化

1. 快

3. 被持久化了

1. 慢

通过参数控制

1. 通过 innodb_flush_log_at_trx_commit参数

1. 值为0

1. 只留在内存（redo log buffer）

2. 值为1

1. 直接持久化到磁盘

3. 值为2

1. 只提交到磁盘（page cache）

redolog的写入策略

1. 通过轮询来持久化

1. InnoDB的后台线程，每隔一秒
2. redo log buffer写到page cache
3. 然后持久化
4. 所以，没有commit也会有部分持久化

2. 空间过1/2

1. 达到innodb_log_buffer_size一半
2. 主动写盘，（page cache）
3. 但是没有调用fsync，没有持久化

3. 事务提交

1. B事务提交也可能带上A事务没提交的一起持久化
2. 多个事务共用一个buffer

补充说明

1. 时序上

1. redolog：prepare
2. binlog
3. redolog：commit

2. 例外

1. 如果innodb_flush_log_at_trx_commit=1
2. redolog：prepare阶段就要持久化

1. 因为有个崩溃恢复逻辑用到了

3. 加上每秒一次的轮询
4. commit时就不需要fsync持久化了

1. 只写入磁盘（page cache）

3. 双“1”配置

1. sync_binlog=1
2. innodb_flush_log_at_trx_commit=1
3. 一个事务完整提交前，需要等两次刷盘

1. redolog prepare阶段
2. binlog
3. 但是为什么时间上为什么不是*2？

1. 组提交

组提交机制

日志逻辑序列号（LSN）

1. 单调递增
2. 用来对应redo log的写入点
3. 长度为改redolog的length

保证不会执行重复redolog

1. 三个并发事务，长度为50+120+160
2. 第一个到达的被选为leader
3. LSN为最后一个事务的长度
4. leader返回时，最后一个事务也得完成持久化
5. leader顺带着后面两个事务一起返回
6. 组提交：即几个事务一起fsync

binglog也可以组提交

1. 实质上binlog是两步

1. 先写到磁盘上
2. 再持久化

2. 多个binlog也可以一起组提交
3. binglog组提交效果不如redolog效果好

1. 因为redolog持久化（fsync）更快
2. 组长不需要等组员，轮流着上就行
3. binlog能够集合成一组的组员比较少

通过设置参数提升效果

1. binglog_group_commit_sync_delay

1. 延迟多少秒后调用fsync

2. binglog_group_commit_sync_no_delay_count

1. 积累多少次后调用fsync

3. 两者是或的关系

1. 达成其一就可以调用fsync

总结

WAL是如何提升性能的

1. 尽管磁盘读写次数也挺多的，

1. 但是这些读写都是顺序写
2. 更快

2. 组提交机制的优越

MySQL在IO上出现性能瓶颈，如何解决

1. 设置参数

1. binglog_group_commit_sync_delay

1. binglog_group_commit_sync_no_delay_count
2. 减少写盘次数
3. 来减少故意等待

2. 将sync_binlog设置为大于100～1000

1. 可能异常断电会损失日志

3. innodb_flush_log_at_trx_commit

1. 设置为2
2. 可能异常断电会损失日志

补充

1. 不建议innodb_flush_log_at_trx_commit
2. 设置为0，表示只存在内存中

1. 写到page_cache也挺快的
2. 异常断电也会损失日志
3. 设置成2和设置成0性能差不多
4. 但是安全性增加了

第二十四讲：MySQL是怎么保证主备一致的？

binlog可以用来归档和做主备同步。但是它是怎么做到的？

MySQL主备的基本原理

概念

1. 客户端的读写都是对主库
2. 但是备库将主库的所有更新都同步过来
3. 在备库的本地执行

尽管备库没有被直接访问，但是要设置成只读

1. 有时也需要去查，设置成只读防止误操作
2. 防止切换时出错，双写
3. 可以方便判断是哪个主库/备库

设置成只读了，还怎么和主库一致？

1. 只读对超级用户无效
2. 用与更新的线程就是超级用户权限

从主库到备库的内部流程

1. 原理

1. 主库A 备库B，一个update语句在主库执行流程
2. 就是A执行时写binlog，B拿着binlog直接执行

2. A和B之间维持着一个长连接
3. A中有个线程服务于该长连接
4. 在B中设置好内容
5. change master命令

1. 包括A的IP、端口、用户名密码、binlog位置

6. 在备库B上和主库A建立连接

1. start slave命令
2. 备库会启动两个线程
3. io_thread和sql_thread

7. A按照B发来的binlog位置，读取binlog

1. 发给B

8. B拿到binlog

1. 写本地日志，称为中转日志（relay log）

9. B中的sql_thread解析日志，并执行

binlog三种格式对比

备库B可以直接执行binlog，那么binlog究竟是怎么写的

binlog有三种格式

1. statement
2. row
3. mixed（前两种的混合）

binlog为statement情况下

1. 记录的是SQL原文，包括注释
2. delete带了limit，是unsafe的

1. 主库和备库使用了不同的索引
2. 可能会选择到不同的行

binlog为row的情况下

1. 不会主备删除不同行
2. binlog中没有SQL语句的原文

1. 需要用解析工具解析（mysqlbinlog）

3. 记录了

1. 不同的表及对应参数
2. 会记录被操作行的

1. 所有字段信息
2. 或者必要字段信息（主键）
3. 取决于参数 binlog_row_image

为什么会有mixed格式的binlog

1. 如果只设置为statement

1. 有出错风险

2. 如果只设置为row

1. 太占空间
2. IO时间也长

3. 设为mixed，MySQL会自己判断

1. 是否有可能引起主备不一致
2. 选择statement或者row

4. 结论

1. 如果被设置成statement，一定不合理

为什么现在普遍要求设为row

方便恢复数据

delete

1. 如果出错
2. row格式下
3. 直接insert

1. 会连行数都会被记录下来
2. 精准定位
3. 直接恢复

insert

1. 如果insert错了
2. 精准定位
3. 直接删除

update

1. 如果update错了
2. 会记录下操作前的一行
3. 和操作后的一行
4. 直接调换

总结

1. MariaDB的Flashback工具就是基于这个原理

如果是mixed？

1. 用的不多
2. 可能会记录错，把row记录成statement
3. 并且很多语句的执行依赖于上下文

恢复方式

1. 用mysqlbinlog解析出来
2. 发给MySQL执行
3. 命令：

循环复制问题

结构

1. M-S结构

1. 一个为主库一个为备库

2. 双M结构

1. 互为主备
2. 用的更多
3. 切换时不需要修改主备关系

双M存在循环复制的问题

1. 描述

1. 主库A执行时写下binlog交给B执行
2. B执行完又写binlog，再给A执行

2. 解决办法

1. 给每个库定义一个server id
2. 如果server id和本库相同，丢弃

问题

除了循环复制，还有什么情况下会出现死循环？

第二十五讲：MySQL是怎么保证高可用的？

最终一致性

1. 主备关系中
2. 主库更新生成的索引binlog都被传到备库执行
3. 备库达到和主库一致的状态

主备延迟

概念

1. 主库执行完一个事务，写入binlog为T1时刻
2. 备库接受完这个binlog为T2时刻
3. 备库执行完这个事务，为T3时刻
4. 主备延迟了T3-T1的时间
5. 可以在备库上看到

1. 执行show slave status
2. 会显示seconds_behind_master
3. 表示延迟了多少秒

seconds_behind_master是如何计算的

1. binlog上有个时间字段，记录主库写入的时间T1
2. 备库取出该事务的时间字段T3
3. T3-T1，精确度为秒，就是主备延迟的值
4. 主库的系统时间和备库的系统时间不一致

1. 不会影响
2. 有函数
3. 会帮助把时间的差值扣掉

5. 网络正常时，binlog在主备之间传输很快

1. 延迟主要来自于备库的执行时间

6. 主备延迟的表现通常就是

1. 备库的relay log比主库binlog生产的快

主备延迟的来源

原因一

1. 备库所在机器的性能比主库差
2. 同一台机器上放了多个主库的备库

1. 备库必须开“非双1”模式
2. 各个备库都在读操作，争抢资源

原因二

1. 现在这种情况比较少，因为需要主备切换，所以要对称部署

1. 但还是会有延迟的现象

2. 备库的压力大

1. 主库直接影响业务，提供了写能力，大家用起来比较克制
2. 备库的查询时，耗费了大量CPU资源

3. 解决办法

1. 除了备库，多接几个从库，分担读读压力

1. 从库可以理解为不会主备切换的备库

原因三

1. 采用了一主多从，还可能主备延迟

1. 大事务

2. 原因

1. 主库必须等事务完成才会写入binlog
2. 再传给备库
3. 如果主库语句执行了10分钟
4. 从库就会延迟10分钟

3. 场景1

1. 快满了，一次性删除太多数据
2. 换成少量多次删

4. 场景2

1. 大表DDL
2. 使用gh-ost方案
3. 见13章

原因四

1. 备库的并行复制能力
2. 其他情况

切换策略

主备切换时，因为有主备延迟，所以有不同的切换策略

可靠性优先策略

1. 流程

1. 判断备库现在的seconds_behind_master
2. 小于一个值，就继续，不然重试
3. 把主库A改为只读
4. 判断seconds_behind_master，直到=0
5. 把备库B改为可读写
6. 切换业务请求

2. 数据库存在不可用时间
3. 比较耗时间的是

1. 判断seconds_behind_master，直到=0

4. 所以要尽量减少不可用时间，通过

1. 判断备库现在的seconds_behind_master
2. 小于一个值，就继续，不然重试

可用性优先策略

概念

1. 把不可用时间降到几乎为0
2. 强行把操作

1. 备库B改为可读写
2. 切换业务请求

3. 改到前面执行
4. 不等主备数据同步
5. 可能会出现数据不一致

例子

1. 有插入4，5两条命令
2. 主库A插入了4后，开始主备切换，
3. 而备库B此时有延迟，还没来得及插入4，就开始插入5
4. 于是备库B插入了5，然后把binlog传给A
5. 然后把延迟的4插入进了B
6. B插入了5-4，A插入了4-5
7. 出现了数据不一致

把binlog_format设为row

1. row格式会记录全部字段
2. 主备同步的线程会报错
3. 所以插入 4-5 和 5-4 都不会被执行
4. 结论

1. 使用row格式

1. 数据不一致更容易发现

2. mixed和statement

1. 可能数据不一致不可查
2. 连带着产生更多不一致

5. 大多数情况下建议使用可靠性优先策略

什么情况下使用可用性优先策略

1. 某个库的作用是记录日志，数据不一致可以通过binlog补
2. 短暂的不一致不影响业务
3. 但是业务依赖于日志系统，库不能有不可写的状态
4. 可以通过降低日志系统优先级来使用 可靠性优先策略

可靠性优先策略异常切换时是什么样

1. 主库A和备库B延迟30分钟
2. 主库A掉电了，此时HA要切换B作为主库
3. 如果是主动切换，要等到延迟小于一个值
4. 但现在是被动切换，别无选择
5. 不能直接切换到备库B，保持B只读

1. 中转日志没有应用
2. 直接主备切换
3. 客户端会任认为有数据丢失
4. 对于某些业务，后补是不能接受的

6. 在数据可靠的情况下，必须主备延迟小，才能达到高可用性

第二十六讲：备库为什么会延迟好几个小时

用多线程，防止备库一致追不上主库

备库的并行复制能力

MySQL5.5版本的并行复制策略

按表分发策略

按行分发策略

MySQL5.6版本的并行复制策略

MariaDB的并行复制策略

MySQL5.7版本的并行复制策略

MySQL5.7.22版本的并行复制策略

第二十七讲：主库出问题了，从库怎么办？

一主多从的主备切换流程

第二十八讲：读写分离有哪些坑

做读写分离是为了分担压力，主库写，从库读。
但是存在延迟，可能正在更新时请求读了。
就会读到非最新的数据

第二十九讲：如何判断一个数据库是不是出问题了

几种检查方法

第三十讲：

第三十一讲：误删数据后除了跑路，还能怎么办

几种可能误删的情况，误删后的处理

第三十二讲：为什么还有kill不掉的语句

不是强制的，还需要到埋点，本身也需要时间，只能等

第三十三讲：我查这么多数据，会不会把数据内存打爆

不会，是边算边发的，有淘汰策略

第三十四讲：到底可不可以使用join？

join，多表查询，有驱动表和被驱动表
驱动表是全表查，被驱动表是树搜索
所以数据量小的表作为驱动表

第三十五讲：join语句怎么优化

算法

第三十六讲：为什么临时表可以重名

因为是每个线程自己的

第三十七讲：什么时候会使用内部临时表

group by的实现，从中获得的指导原则

第三十八讲：都说InnoDB好，那还要不要使用Memory引擎

Memory引擎的特性，InnoDB的适用情况

第三十九讲：自增主键为什么不是连续的

自增值的存储，在内存上，没有被指久化
可以回退，但是需要判断，影响性能，所以主动放弃了

第四十讲：Insert语句的锁为什么这么多

几种Insert语句的使用场景

第四十一讲：怎么最快的复制一张表

讨论三种拷贝表的方式，最快，最灵活……

第四十二讲：grant之后要跟着flushprivileges吗？

MySQL用户权限的存在形式，grant的执行逻辑

第四十三讲：要不要使用分区表

Server层和引擎层对分区表的处理方式
MDL锁

第四十四讲：

第四十五讲：自增ID用完怎么办

MySQL 7*24小时全年无休，这是边界情况
讨论几种处理方式