MySQL实战45讲完整版

提炼自极客时间林晓斌MySQL实战45讲，包括：一条SQL查询语句是如何执行的?—条SQL更新语句是如何执行的?事务隔离:为什么你改了我还是看不见?全局锁和表锁:给表加个字段怎么有这么多阻碍?

MySQL实战45讲

• 01 | 一条SQL查询语句是如何执行的？
  • • 连接器
      • 如果密码正确，就去查询用户的权限
      • 修改完权限要重启链接才能生效
      • 查看空闲链接：show processlist，sleep
      • 建立连接、获取权限、维持和管理权限
      • 默认八小时后连接自动断开，参数：wait_timeout
      • 长连接：一直使用同一个连接
        • 占内存如何处理？
          • 定期断开长连接
          • 通过mysql_reset_connection来重新初始化连接资源，这个过程不需要重连和权限验证
      • 短连接：每次执行完几次查询就断开，下次重新建立
    • 查询缓存
      • MySQL拿到查询请求，先查找查询缓存
        • 缓存中有结果，直接返回结果
        • 缓存中无结果，继续后面的执行阶段
      • 多数情况下不建议使用查询缓存
        • 失效频繁，一更新，缓存就会被清空
        • 更新压力大，查询命中率低
      • 按需使用查询缓存机制
        • query_cache_type=demand, 默认的都不使用查询缓存
        • 使用查询缓存： SQL_CACHE显式指定
      • MySQL8已废弃
    • 分析器
      • 对SQL做词法分析
        • 根据mysql关键字进行验证和解析
      • 对SQL做语法分析
        • 在词法解析的基础上进一步做表名和字段的验证和解析
    • 优化器
      • 决定使用的索引、决定表的连接顺序
    • 执行器
      • 先判断是否有查询表T的权限
        • 没有：返回没有权限
          • 如命中缓存，在查询缓存返回结果时做权限验证
        • 有：打开表（根据定义去使用对应的引擎）继续执行
      • 慢查询日志中的rows_examined字段
        • 表示语句执行过程中扫描的行数
    • 存储引擎
      • 默认：InnoDB
      • 不同引擎共用一个server层
    • 举个栗子：表T中没有字段k，而你执行了select * from T where k=1，哪个阶段会报错不存在列？
      • 分析器
• 02 | 一条SQL更新语句是如何执行的？
  • 查询语句的流程，更新语句也会走一遍
    • 不同：更新还涉及日志模块
      • redo log 重做日志
      • binlog 归档日志
  • 更新流程
    • 连接器：连接数据库
    • 分析器：分析出这是更新语句
    • 优化器：决定使用ID这个索引
    • 执行器：负责执行
  • 加入物理日志redolog和逻辑日志binlog的update流程（浅色框：在InnoDB中执行，深色框：在执行器中执行）
    • redo log: 物理日志（重做日志、粉板）
      • 是InnoDb特有的日志
      • 孔乙己中记账有账本和粉板，粉板=redo日志，账本=磁盘
      • WAL：Write-Ahead Logging（WAL）：先写日志再写磁盘，（先写粉板，再写账本）。具体流程：InnoDb先将记录写到redo log，并更新内存，然后在适当的时候将记录更新到磁盘中（空闲时做）。
      • crash-safe：InnoDb可用redolog来保证数据库异常重启后，之前的记录不丢失。
    • binlog: 逻辑日志（归档日志）
      • MySQL分为Server层和引擎层，redolog是InnoDb引擎特有的日志，binlog是server层自己的日志。
      • MySQL自带的引擎MyISAM没有crash-safe能力，（binlog只能归档）所以InnoDb以插件的形式引入MySQL
      • 子主题 3
    • redo log与binlog 的区别
      • 1. redo log：InnoDb特有的，binlog：MySQL的server层实现的，所有引擎都可使用
      • 2. redo log物理日志：记录在某个数据页上做的修改； binlog逻辑日志：原始逻辑，如：给ID=2这一行的c字段加1
      • 3. redo log：循环写的，空间会用完；binlog：可追加写入，写完切换成下一个，并不会覆盖以前的日志
  • 两阶段提交
    • 如上图，先prepare再commit
    • 目的：让两份日志之间的逻辑一致
    • 证明
      • 先写redo log再写binlog
        • redo log写完就奔溃了，下次使用binlog进行临时库的恢复，就无法恢复成初始值
      • 先写binlog 再写redo log
        • binlog写完崩溃了，那么使用binlog恢复时就会多一个日志，与原库的值不同
    • 应用：扩容。做法：利用全量备份加上应用binlog来实现
  • 小结
    • redolog保证crash-safe能力，可将参数设为1来保证直接持久化到磁盘，保证MySQL异常重启之后数据不丢失。
    • binlog：将参数置为1，来保证binlog持久化到磁盘，保证异常重启之后binlog不丢失。
  • 举个栗子
    • 一天一备份什么情景下会比一周一备份更有优势？
      • 一天一备份的“最长恢复时间”更短，最坏情况只需要应用一天的binlog，而一周一备份需要应用一周的binlog。
      • 恢复目标时间RTO是有成本的，更频繁全量备份需要消耗更多存储空间。
• 03 | 事务隔离：为什么你改了我还是看不见？
  • 前言
    • MySQL原生引擎MyISAM不支持事务
  • 隔离性与隔离级别
    • 隔离性Isolation：一个事务执行时不能受其他事务的干扰。
    • 隔离级别
      • 读未提交
        • 事务还没提交时，他做的变更能被其他事务看到
      • 读已提交
        • 一个事务提交之后，他做的变更才能被其他事务看到
      • 可重复读
        • 一个事务在执行过程中看到的数据与这个事务在启动时看到的数据是一样的。（别人改数据的事务已经提交，我在我的事务中也不去读）
      • 串行化
        • 对同一行记录，写时加“写锁”，读时加“读锁”，出现冲突时按顺序完成。（我的事务尚未提交，别人别想改数据）
    • 数据库对隔离级别的实现
      • DB会创建一个视图
        • 可重复读：视图在事务启动时创建，整个事务存在期间都用这个视图。
        • 读已提交：视图在每个SQL语句开始执行的时候创建的。
      • 读未提交：直接返回记录上的最新值，没有视图概念
      • 串行化：直接用枷锁的方式来避免并行访问
      • Oracle默认：读已提交，MySQL中的设置：transaction-isolation设置为read-committed，用show variables来查看
      • 可重复读的场景
        • 数据校对逻辑的案例
          • 校对上个月的余额和这个月余额的差值，你希望在校对过程中，及时有用户发生了新的交易，也不影响校对结果。
  • 事务隔离的实现
    • ？？？？？
  • 事务的启动方式
    • 1. 显式启动事务语句
      • begin/start transaction、commit、rollback
    • 2. 关闭线程的自提交
      • set autocommit=0，事务会持续存在直到你主动执行commit或者rollback，或者断开连接
    • 所以，如果采取了第二种方法，就导致了接下来的查询都在事务中，如果是长连接，就导致了长事务。
    • 所以，建议使用set autocommit=1
    • 长事务的查询：在information_schema库下的innodb_trx表中查询。
  • 举个栗子
    • 如何避免长事务对业务的影响？
      • 从应用开发端来看
        • 1. 确定是否使用了set autocommit=0，如是，则改成1
        • 2. 确定是否有不必要的只读事务
        • 3. 业务连接数据库的时候，控制每个语句执行的最长时间 set max_execution_time
      • 从数据库端来看
        • 1. 监控相关表，设置长事务阈值，超过就报警/kill
        • 2. 可使用percona的pt-kill工具
        • 3. 在功能测试阶段输出所有的log，分析日志提前发现问题
        • 4. 把innodb_undo_tablespaces设置成2或者更大的值
• 06 | 全局锁和表锁：给表加个字段怎么有这么多阻碍？
  • 前言
    • 锁的设计初衷：处理并发问题
    • 锁的分类
      • 全局锁
      • 表级锁
      • 行锁
  • 全局锁
    • 对整个数据库实例加锁
    • 加全局读锁的命令：Flush tables with read lock（FTWRL）
    • 典型使用场景：全库逻辑备份
      • 即：把整个库的表都select出来存成文本
      • 用mysqldump使用参数-single-transaction来启动一个事务，拿到一致性视图，这样数据就能够正常更新。使用前提: 引擎要支持这个隔离级别。适用使用事务引擎的库。
      • InnoDB推荐使用-single-transaction
      • MyISAM不支持事务的引擎，因此只能使用FTWRL
      • 为何不使用set global readonly=true？
        • 1. readonly会被用来做其他逻辑，比如判断一个库是主库还是备库，所以修改global变量的影响太大了，不建议使用
        • 2. 异常处理机制上有差异。
          • 使用FTWRL，发生异常会自动释放锁
          • 使用readonly，发生异常不会释放锁
  • 表级锁
    • 表锁
      • 语法：lock tables …… read/write
      • 释放锁
        • 1. unlock tables
        • 2. 客户端断开时自动释放
    • 元数据锁MDL
      • metadata lock
      • 在访问一个表时，MDL会被自动加上
      • 作用： 保证读写的正确性
      • 给一个小表加一个字段，导致整个库挂了？
        • 加字段需要扫描全表数据
        • 事务中的MDL锁，在语句执行开始时申请，语句结束后并不会马上释放，而是等整个事务提交后再释放。
      • 那么，如何给小表安全的加字段？
        • 1. 先解决长事务
          • 在做DDL变更时有长事务：可先暂停DDL或者kill掉长事务
        • 2. alter table中设定等待时间
          • 时间到了自动放弃，不阻塞后面的业务语句，之后DBA在重试任务。
  • 举个栗子
    • 备份一般在被库上执行，再用-single-transaction做逻辑备份时，如果主库上的一个小表做了DDL（加了一列），会有什么现象？
• 07 | 行锁功过： 怎么减少行锁对性能的影响？
  • 前言
    • 并不是所有引擎都支持行锁
    • MyISAM不支持，InnoDB支持
  • 两阶段锁
    • 两阶段锁协议
      • 行锁在需要时才加上，不是不需要了就释放，而是等到事务结束才释放。
    • 如果事务中需要锁多个行，就把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
  • 死锁和死锁检测
    • CPU消耗接近100%，但整个数据库每秒执行不到100个事务，可用死锁检测方法
    • 死锁出现后处理的策略
      • 1. 直接进入等待，直到超时
        • 超时时间的设置不能太长或者太短
      • 2. 主动检测，发现死锁后主动回滚事务
        • 死锁检测要耗费大量CPU资源
    • 如何解决由热点行更新导致的性能问题？
      • 问题在死锁检测耗费太多资源了
      • 1. 保证业务一定不会出现死锁时，就把死锁检测关掉
      • 2. 控制并发度，比如同一行同时最多只有10个线程在更新
        • 但是这个方法不太可行，因为客户端很多
      • 因此，并发控制要做在数据库服务端
        • 1. 在中间件实现
        • 2. 修改MySQL源码
        • 总结：在进入引擎之前先排队
      • 不从数据库着手，能否从设计上优化？
        • 将一行改成逻辑上的多行来减少锁冲突
          • 比如十个记录，账户总额等于十个记录的值的总和。
  • 举个栗子
    • 要删除一个表中的前1000行，选择哪种方法
    • 选择在一个连接中循环执行20次：delete from T limit 500
    • 不选择：直接执行delete from T limit 10000
      • 单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。
    • 不选择：在20个连接中同时执行delete from T limit 500
      • 会人为造成锁冲突
• 08 | 事务到底是隔离的还是不隔离的？
  • 前言
    • 当一个事务进入等待状态，那么等到这个事务自己获取到行锁要更新数据时，他读到的值是什么？
    • begin/start transaction并不是一个事务的起点，在执行到他们之后的第一个操作表的语句，事务才真正启动。
    • 举个栗子（id，k） values（1,1,）（2,2）
      • 事务B：k=3事务A：k=1
        • ？？？
    • 视图
      • 分类
        • view
          • 用查询语句定义的虚拟表
        • 一致性视图
          • 支持读提交和可重复读级别
      • 作用
        • 事务执行期间用来定义“我能看到什么数据”
      • 没有物理结构
  • 快照在MVCC中是如何工作的？
    • 拍摄快照并不需要拷贝出100G的数据
    • 快照如何实现？
      • InnoDB中每个事务有唯一id：transaction id，在事务开始时申请，按申请顺序严格递增
      • 每行数据是有多个版本的，每次事务更新数据时会生成一个新的数据版本，并将transaction id复制给这个数据版本的事务ID，记为row trx_id.
      • 同时要保留旧的数据版本，并且在新版本中，能够有信息可以直接拿到它。
      • 即：一行记录可能有多个版本，每个版本有自己的trx_id
    • InnoDB如何定义100G快照？
      • 所有数据都有多个版本，实现了秒级创建快照的能力
      • InnoDB为每个事务构造了一个数组，用来保存事务启动瞬间与所有启动了但还没提交的事务ID。
      • 以事务启动时刻为准，如果一个数据版本是在启动之前生成的，就认；启动之后生成的就不认，必须找到它的上一个版本。
      • 一致性视图
        • 视图数组
          • row trx_id的可能落下的几种区间
            • 绿色：已提交事务/当前事务自己生成的，数据可见
            • 红色：未开始事务，不可见
            • 黄色
              • row trx_id在数组中：表示这个版本由还没提交的事务生成的，不可见
              • row trx_id不在数组中：已经提交的事务生成的，可见
        • 高水位
          • 事务ID的最大值加1
          • 低水位：事务ID的最小值
• 05 | 深入浅出索引（下）
  • 前言
    • select * from T where k between 3 and 5需要执行几次树的搜索操作？
      • 1. 在k索引树上找到k=3的记录，取得ID=300；
      • 2. 在主键索引树上查到ID=300对应的R3；
      • 3. 在k索引树上取下一个值k=5，取得ID=500；
      • 4. 再回到主键索引树上查到ID=500对应的R4；
      • 5. 在k索引树上取下一个值k=6，不符合条件，循环结束。
  • 覆盖索引
    • 在查询里，索引k已经覆盖了查询需求
      • 比如根据k索引查询出主键ID
    • 是否有必要将身份证和名字建立联合索引？
      • 如果有高频请求，要根据身份证号查询姓名，这个联合索引就有意义了
  • 最左前缀原则
    • B+树这种索引，可利用索引的”最左前缀“，来定位记录
    • 在建立联合索引时，如何安排索引内的字段顺序？
      • 优先考虑
        • 1. 建立联合索引可以少维护一个索引
          • 如果有索引（a,b）, 就可以不单独建立索引(a)
          • 如果有（a,b）的联合查询，又有基于a，b的查询？
            • 这时得维护两个索引：（a,b）和 (b)
        • 2. 考虑空间
          • 如（name，age）的联合索引，name字段比较大
            • 建议建两个索引：index（name，age）和index（age）
  • 索引下推
    • 前提：联合主键index（name，age），查询姓名以张开头，且年龄为10
      • 无索引下推过程
        • 根据最左前缀，查询到“张”时，就回表
      • 有索引下推过程
        • 查询到“张”时，继续判断年龄，符合了才回表
  • 举个栗子
    • 查询语句select *from geek where c=N order by a limit 1; select *from geek where c=N order by b limit 1; 为何要新建三个索引（a, b）,(c,a), (c,b)? 创建（a, b）和（c）不够吗？
      • 对于主键（a,b）
        • 先按a排序，再按b排序，c无序
      • 对于索引（c，a）
        • 先按c排序，再按a排序, 同时记录主键b
      • 对于索引（c，b）
        • 先按c排序，再按b排序, 同时记录主键a
      • 综上，ca可以去掉，cb需要保留
• 04 | 深入浅出索引（上）
  • 前言
    • 索引是为了提高数据查询的效率，类似于书的目录
  • 索引常见模型
    • 哈希表
      • 键值对来存储数据
        • 思路：将值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。
        • 哈希碰撞：拉链法
        • 因为不是有序的，所以哈希索引做区间查询的速度很慢
        • 适用情景：只有等值查询，如Mencached、NoSQL
    • 有序数组
      • 适用场景：等值查询和范围查询
      • 但更新数据时成本过高
      • 所以只适用于静态存储引擎，（不会再修改的数据）
    • 搜索树
      • 查询、更新时间复杂度O（logn）
  • InnoDB的索引模型
    • B+树索引模型
      • 每个索引在InnoDB中对应一棵B+树
      • InnoDB中表根据主键顺序以索引的形式存放
      • 索引类型
        • 主键索引（聚簇索引）
          • 叶子的值：整行数据
        • 非主键索引（二级索引）
          • 叶子节点的内容是主键的值
      • 基于主键索引和普通索引的区别
        • 主键查询：只需要搜索主键的索引树
        • 普通查询：先搜索普通索引树，拿到主键值，再搜索主键索引树。（回表）
      • 索引维护
        • 为了保护索引的有序性
        • 自增主键
          • 使用自增主键每次插入新记录都是追加操作，不涉及挪动，也不触发叶子结点的分裂。
        • 用身份证做主键还是用自增主键
          • 用自增主键
            • 如何用身份证号，则每个二级索引的叶子节点占用约20个字节，如果用整形做主键则只要4个字节，长整形则8个。
            • 主键长度越小，普通索引的叶子节点就越小，占用的空间也越小。
        • 适合用业务字段直接做主键的场景
          • 1. 只有一个索引
          • 2. 该索引必须是唯一索引
          • 因为没有其他索引，所以不用考虑其他索引的叶子节点大小的问题
  • 举个栗子
    • 如果要重建索引k：alter table T drop index k；alter table T add index（k）；重建主键：alter table T drop primary key；alter table T add primary key（id）；
      • 重建索引：索引可能因为删除或者页分裂的原因，导致数据页有空洞。
      • 重建索引k的过程合理
      • 重建主键不合理，删除/创建主键会将整个表重建
      • 所以连着执行这两个语句，第一个语句就白做了
      • 这两个语句可用 “alter table T engine=InnoDB” 来代替
• 09 | 普通索引和唯一索引，应该怎么选择？
  • 查询过程
    • 举个栗子select id from T where k=5
      • 查找过程：从B+树的树根开始，按层搜索到叶节点（数据页），然后根据二分法在数据页内部来定位。
      • 建立普通索引还是唯一索引？
        • 普通索引：查到第一个满足的记录后，需要查找下一个记录，直到碰到不满足k=5，结束
        • 唯一索引：因为有唯一性，所以找到了第一个满足的记录后就结束了
        • 性能差距：微乎其微
          • InnoDB按数据页为单位读写
          • 所以都是读取一页，只是怕普通索引要多做一次查找和判断下一次记录（一次指针寻找和计算）
  • 更新过程
    • change buffer
      • 使用场景
        • 只限于用在普通索引的场景下，不使用于唯一索引
          • 因为对于唯一索引，所有的更新操作都要先判断这个操作是否违反唯一性约束，而这需要将数据页读入内存才能判断，既然已经读到内存了，直接更新内存会更快，没必要change buffer
        • 但是并不是所有普通索引都适用
          • 写多读少的业务，页面在写完以后马上被访问到的概率小，如账单类、日志类系统，所以用change buffer的效果好
          • 写入之后马上会做查询的业务不适用
            • 因为马上要访问这个数据页，会立即触发merge过程，这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。
      • 在内存中有拷贝，也会被写入到磁盘上
      • merge
        • 将change buffer中的操作应用到原数据页，得到最新结果的过程。
        • 触发条件
          • 访问这个数据页
          • 系统后台线程会定期merge
          • 数据库正常关闭也会merge
      • 主要是减少读磁盘，提升运行速度
    • 更新流程
      • 如果数据页在内存中
        • 唯一索引
          • 判断有没有冲突再更新
        • 普通索引
          • 直接更新
      • 数据页不在内存中
        • InnoDB会将这些更新操作缓存在change buffer中，下次查询需要访问这个数据页时，将数据页读入内存，然后执行change buffer中与这个页有关的操作。
        • 唯一索引
          • 将数据页读入内存，判断是否有冲突再更新
        • 普通索引
          • 直接将记录更新在change buffer中
  • 索引选择和实践
    • 尽量选择普通索引
    • 如果所有更新后都马上伴随着查询，则应该关闭changebuffer
    • 普通索引和change buffer配合使用对于数据量大的表的更新优化明显
  • change buffer和redo log
    • 表上插入一条语句insert into t(id, k) values(id1, k1), (id2,k2)
      • k索引树，k1所在数据页在内存中，k2所在数据页不在内存中
      • 1. page1在内存中，直接更新内存
      • 2. page2不在内存中，就在change buffer区域下，记录“我要往page2插入一行”这个信息
      • 3. 将上述两个动作记入redo log中
    • 插入后马上进行查询select * from t where k in(k1,k2)
      • 1. 读page1时， 直接从内存中返回
      • 2. 读page2时，需要把page2从磁盘读入内存，然后应用change buffer里的操作日志，生成并返回结果
    • 综上，redo log主要节省随机写磁盘的IO消耗，而change buffer主要节省随机读磁盘的IO消耗。
  • 举个栗子
    • change buffer写入内存了，断电重启了怎么办？会丢失吗
      • 不会丢失，在事务提交的时候，会把change buffer的操作页记录到redo log里。
• 10 | MySQL为什么有时候会选错索引？ （可进行项目扩展）
  • 优化器的逻辑
    • 优化器的工作：选择索引
    • 选择索引
      • 目标： 最小的代价执行
      • 1. 扫描行数
        • 根据统计信息来估算记录数
          • 统计信息不准确，用analyze table来解决
        • 基数越大，索引的区分度越好
          • 基数：一个索引上不同值得个数
            • 使用show index方法查询索引基数
            • MySQL如何得到索引的基数？
              • 采样统计，默认选择N个数据页，统计这些页面上的不同的值，得到平均值再乘以页面数
          • 区分度：统计信息
        • 使用普通索引进行回表也要代价
      • 2. 是否使用临时表
      • 3. 是否排序
  • 索引选择异常和处理
    • 1. 采用force index强行选择一个索引
      • 弊端：如果索引改名了语句也得改；另外，如果迁移到其他数据库，语法可能会不兼容
    • 2. 修改语句，引导MySQL使用我们期望的索引
      • 例如：order by b limit 1 改为 order by b,a limit 1
        • 原：优化器选择使用索引b，因为它认为b本身有序（是索引），就可以避免排序，只需遍历
        • 后：要求按照b，a排序，既然如此，主要取决于扫描行数
    • 3. 新建一个更合适的索引/删除误用索引
  • 举个栗子
    • 事务A先开启，接着事务B删除数据后重新进行插入然后进行区间查询，然后A才提交，发现优化器检测到扫描行数增加了三倍，而如果没有先开启事务A，只是单独执行事务B则扫描行数没有改变，什么原因？
      • delete删掉了所有的数据，重新进行插入，看起来是覆盖了原来的数据
      • but， A开启了事务后并没有进行提交，因此，每一行数据都有 两个版本！！！
      • 这样的话，索引上的数据也有两份！
      • 但要注意一点，主键是根据表的行数来估计的，表的行数，优化器直接用show table status的值
• 11 | 怎么给字符串加索引？
  • 前言
    • 要解决的问题：支持邮箱登录的情况下，如何给邮箱加索引？
      • 1. 前缀索引
      • 2. 区分度不够时用不了前缀索引，可试试倒叙存储
        • 记得用count（distinct）验证下区分度
      • 3. 使用hash字段
        • 表上再创建一个字段，来保存身份证的校验码（使用函数crc32()），同时在此字段上设置索引。 ===》转移查询目标
      • 4. 直接创建完整索引（占用空间大）
      • 倒叙存储和使用hash字段的异同点
        • 1. 占用的额外空间
          • 倒叙存储在主键索引上，没有消耗额外空间
          • hash需要额外增加一个字段
        • 2. CPU消耗
          • 倒叙每次读和写要调用reverse，占用资源更少
          • hash需额外调用crc32()
        • 3. 查询效率
          • 倒叙用的还是前缀索引的方式，还是会增加扫描行数
          • 而hash虽然有冲突，但概率小，扫描行数接近1
  • 前缀索引（类似模糊查询）
    • 弊端
      • 1. 增加扫描行数
      • 2. 有可能用不上覆盖索引对查询性能的优化
      • 3. 会损失区分度，可预先设定可接受的损失比例
    • 创建索引的语句不指定前缀长度，默认包含整个字符串
    • 定义好前缀长度可以节省空间和查询成本
      • 如何确定前缀长度？
        • 区分度：区分度越高越好（重复的键值越少），根据统计索引上不同的值的数量来确定前缀长度。
  • 前缀索引对覆盖索引的影响
    • 有可能用不上覆盖索引对查询性能的优化
    • 使用email（6），不得不回到主键索引树去判断email字段的值，因此覆盖索引被浪费了
  • 举个栗子
    • 如何设计学生登录名（学号[e-mail]）的索引？（学号的规则不管正向还是反向的前缀索引，重复度很高）
      • 1. 只存入学年份和顺序编号，长度9位，如果用数字类型存放，只占4个字节 （变相hash，字符串转数字的规则）
      • 2. 直接存原来的字符串。一个学校，50年才100万数据，是小表，为了业务简单，直接存原字符串
• 12 | 为什么我的MySQL会抖一下？
  • 前言
    • 一条SQL偶尔特别慢，且场景难复现，原因？
    • 做个比喻
      • 粉板：redo log
      • 掌柜的记忆： 内存
      • 账本： 数据文件
    • 脏页：内存数据页与磁盘数据页内容不一致
    • 刷脏页： 将内存页写入磁盘
    • 抖一下：可能实在刷脏页，平常执行很快的更新操作实在写内存和日志
  • 什么情况会引发数据库的flush过程？
    • 1. 粉板满了，记不下了（redo log写满了，系统停止所有更新操作）
      • 尽量避免这种情况，全阻塞了，更新数跌为0
    • 2. 生意太好，掌柜要记不住了（内存不足，要将脏页写到磁盘）
      • 常态，要有控制脏页比例的机制来避免下面两种情况
        • 1. 要淘汰的脏页个数过多，导致查询的响应时间变长
        • 2. 日志写满，更新全堵了
    • 3. 生意不忙时更新账本（MySQL认为系统空闲时，进行刷脏页）
      • 系统空闲，无压力
    • 4. 年底清账本（MySQL正常关闭时进行刷脏页）
      • 系统空闲，无压力
  • InnoDB刷脏页的控制策略
    • 1. 告诉InnoDB所在主机的IO能力
      • innodb_io_capacity
      • 刷脏页速度的影响因素
        • 脏页比例
        • redo log写盘速度
    • 2. 刷脏页的连坐机制
      • innodb_flush_neighbors
      • 当刷到的脏页的邻居也是脏页时，会一起刷了
      • 将参数设置为0，关闭连坐机制
  • 举个栗子
    • 一个内存配置为128GB，innodb_io_capacity=20000情况下，正常建议你将redo log设置成4个1GB文件。如果在配置时将redo log设置成一个100MB文件，会发生什么情况？（高配机器，redo log设置太小发生的情况。）
      • 每次事务提交都要写redo log，设置太小很快就会被写满，这时系统被迫停止所有更新，出现的现象：磁盘压力很小，但数据库出现间歇性的性能下跌。
• 13 | 为什么表数据删掉一般，表文件大小不变？
  • 前言
    • 为什么简单删除表数据达不到回收空间的效果？
    • 表
      • 表结构定义
        • 放在.frm为后缀的文件里
      • 数据
  • 参数Innodb_file_per_table
    • 为ON时，表数据存放在以.db为后缀的文件中
    • 为OFF时，表数据放在系统共享表空间
    • mysql5.6开始，默认值为ON，推荐设置为ON，（表单独存储为文件更易管理），不需要表时，可直接drop table，而如果放在共享表空间中，即使删了也不会回收空间。
  • 数据删除流程
    • delete删除行数据时，只是将其标记为可复用，磁盘大小并不会改变，所以delete不能回收表空间。
    • 可以复用而没有复用的空间，看起来是“空洞”。
    • 插入、删除数据也会造成空洞
    • 如何解决空洞？
      • 重建表
  • 重建表
    • Online DDL
      • 1. alter table A engine=InnoDB
      • 2. 建立临时文件，将表A中的数据存储到临时文件中
      • 3. 生成临时文件过程中，对A的增改等操作记录在日志文件中
      • 4. 临时文件生成后，将日志中的操作应用到临时表上
      • 5. 用临时文件替换表A的数据文件
    • online DDL有日志文件，因此在重建表的时候允许对原表做增删改操作
    • DDL之前要拿MDL写锁（alter启动时），但这个写锁在拷贝数据之前就退化成读锁了
      • 退化原因
        • 为了实现online， MDL读锁不会阻塞增删改
      • 不直接解锁的原因
        • 禁止其他线程对这个表同时做DDL
  • online和inplace
    • inplace
      • 整个DDL在InnoDB内部完成，对于server层来说，没有把数据挪动到临时表。（临时操作）
    • 区别
      • 1. DDL过程如果是Online的，就一定是inplace的
      • 2. inplace的DDL，不一定是online的
    • tip
      • analyse table t：不是重建表，只是对表的索引信息做重新统计，没有修改数据，且这个过程加了MDL读锁
      • optimize table t：recreate + analyse
  • 举个栗子
    • 想要缩小表空间（执行 alter table t engine=InnoDB），结果空间变大了的原因？
      • 这个表本身没有空洞了（如，刚刚重建过表），再重建的话空间就变大了
      • 说人话：本身很紧凑了，没剩多少空间了，重新建表的过程中页会按一定的比例（90%）来重新整理数据，未整理之前页已经占用90%以上，收缩之后文件反而变大了。
    • 重建表时，InnoDB不会把整张表占满，每个页留了1/16给后续的更新用，所以重建表之后不是最紧凑的。
• 14 | count(*) 这么慢，我该怎么办？
  • 前言
    • 使用count(*) 来计算交易系统的所有变更记录
      • 自己计数
        • 思路：找一个地方，把操作记录表的行数存起来
  • count(*)的实现方式（没加过滤条件的count）
    • 1. MyISAM：把表的总数存在了磁盘上，执行count(*)时会直接返回这个数，效率很高（快，但是不支持事务）
    • 2. InnoDB：会把数据一行一行地从引擎中读出，然后累积计数（遍历全表，有性能问题）
    • 3. show table status中的table_rows来计算总行数？虽然快，但是不精确，采样估计来的，误差在40%~50%
  • 为什么InnoDB不跟MyISAM一样也把数字存起来？
    • 同一时刻的多个查询有不同版本（多版本并发控制的原因），InnoDB应该返回多少行是不确定的
  • MySQL对count(*)的优化
    • MySQL会找到最小的索引树进行遍历，尽量减少扫描的数据量
  • 用缓存系统保存计数
    • 两个弊端
      • 1. 缓存系统可能会丢失更新（如Redis异常重启）
      • 2. 即使正常工作，这个值在逻辑上不精确（两个事物并发）
        • 1. 已经插入，但redis还没加1
        • 2. 没有最新插入的记录，但已经加1
    • 无法保证Redis与MySQL中数据精确一致的原因
      • 两个不同的存储构成的系统，不支持分布式事务，无法拿到精确一致的视图。
  • 在数据库中保存计数（把计数单独放到一张表中）
    • 利用“事务”，就可以保证在逻辑上是一致的
  • 不同的count用法
    • count（）
      • 聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是null，累计值加1，否则不加，最后返回累计值
    • count(*)
      • 不取值，肯定不是null，只需按行累加
    • count（主键id）
      • 遍历全表，取出每行id返回给server层，不为空就累加
    • count(1)
      • 遍历全表，但不取值，server对返回的每一行放入数字“1”，判断不可能为空，则按行累加
    • count(字段)
      • 1. 字段定义为not null：一行行地从记录里读出这个字段，判断不能为null，按行累加
      • 2. 字段运行为null：执行时将值取出，进行判断是否为空，不空则累加
  • 效率排序：count（字段）<count(主键id) < count(1) ~~count(*)
    • 建议尽量使用count(*)
  • 举个栗子
    • 在并发系统性能角度考虑，在这个事务序列中，先插入操作记录，还是应该先更新技术表？
      • 先插入操作记录，再更新计数表
        • 更新计数表涉及到行锁的竞争，先插入再更新能最大程度减少失误之间的锁，再提升并发度
• 15 | 答疑文章（一）：日志和索引相关问题
  • 日志相关问题
    • 1. 在两阶段提交的不同瞬间，MySQL如果发生异常重启，如何保证数据完整性？
      • 两阶段提交图时刻A： 写binlog之前时刻B：写binlog之后
        • 在时刻A时重启：binlog还没写，redo log还没提交，所以事务会回滚
        • 在时刻B重启：binlog写完，redo log还没commit，如何处理
          • 1. 如果redo log里面的事务是完整的（有commit标识）：直接提交
          • 2. redo log中只有完整的prepare，判断binglog是否存在并完整
            • binlog存在并完整：提交事务
            • 否则，回滚
      • MySQL如何知道binlog是完整的？
        • 一个事务的binlog有完整格式：commit/XID event
      • redo log 和 binlog 如何关联起来的？
        • 他们都有XID字段，崩溃恢复时，会顺序扫描redo log
          • redo log中既有prepare又有commit：直接提交
          • 只有prepare没有commit：拿着XID去binlog找对应的事务
      • 为什么设计成：处于prepare的redolog加上完整的binlog，重启就能恢复成功？
        • 保持一致性
      • 如果这样，那为什么还要两阶段提交？直接先redolog写完再binlog，崩溃恢复时，必须两个日志都完整才可以，不是一样吗？
        • 对于InnoDB，如果redolog提交完成就不能回滚（否则可能覆盖别的事务的更新）。所以如果redolog直接提交，binlog写入失败，InnoDB又回滚不了，数据和binlog日志就会不一致。
          • 两阶段提交：没有人都ok，才ok
      • 不引入两个日志，就不用两阶段提交了，只用binlog来崩溃恢复，又能支持归档，不可以吗？
        • 那么流程变成：数据更新到内存--》写binlog--》提交事务。这样就没有崩溃恢复的能力了！！！
          • 之所以用redolog 是因为binlog没有崩溃恢复的能力，binlog没有能力恢复数据页（因为没有记录数据页的更新细节，补不回来），那我优化binlog让它能记录数据页的更改不就好了？这不就是又做了个redo log出来吗？？？？
      • 那能不能只用redolog不用binlog？
        • 不能。
          • 1. binglog可以归档：redolog是循环写的，历史日志无法保留
          • 2. MySQL系统依赖于binlog，binlog复制是MySQL高可用的基础
      • redolog一般设置为多大？
        • 如果是几个T的磁盘，可设置为：4个文件，每个1GB
      • 正常运行中的实例，数据写入后的最终落盘（把内存中的数据页写盘），是从redolog更新过来的还是从buffer pool更新来的？
        • redolog里面并没有记录数据页的完整数据，所以没有能力自己更新磁盘数据页，所以不可能是从redo log更新过来的
        • 1. 正常运行的实例，就是讲脏页写盘，这个过程与redo log无关
        • 2. 崩溃恢复场景中，将数据页读入内存，让redolog更新内存，更新完成后，内存页变成脏页，回到第一中状态
      • redo log buffer 是什么？是先修改内存还是先写redo log？
        • redo log buffer是一块内存，用来存redo日志（粉板中的粉板）
        • 真正将日志写入redo log文件是在执行commit语句时
  • 业务设计问题
    • 业务问题：互相关注成为好友问题设计上：like表（user_id、like_id）、friend表， 复合唯一索引：uk_user_id_liker_id。语句逻辑如下：以A关注B为例：
      • 1. 先查询对方有没有关注自己
        • 有：成为好友insert into friend
        • 没有：单向关注的关系insert into like
      • 2. AB同时关注对方，会出现不会成为好友的情况。因为上面第一步双方都没关注对方，第一步即使使用了排它锁也不行，因为记录不存在，行锁无法生效。这种情况MySQL锁层面有没有办法处理？
    • 将表模拟出来
      • create table like（ id int(11) not null auto_increment, user_id int(11) not null, primary key(id), unique key 'uk_user_id_liker_id'(user_id, like_id),）engine=Innodb
      • create table friend（ id int(11) not null auto_increment, friend_1_id int(11) not null, friend_2_id int(11) not null, primary key(id), unique key 'uk_friend'(friend_1_id, friend_2_id),）engine=Innodb
  • 举个栗子
    • 新建表，表中已有一条数据（1,2），这时执行：当id=1时，a=2，发现显示0 rows affected，哪种情况？MySQL真的会去修改吗，还是看到值相同就直接返回？
      • 答案：InnoDB认真执行了把值修改成（1,2）的操作，该加锁的加锁，该更新的更新。mysql认为读出来的值只有一个确定的（id=1），而要写的（a=2）,只有这两个条件不足以判断“不需要修改”
• 16 | order by是怎么工作的？
  • 前言
    • 查询杭州所有人名字，按照姓名排序返回前1000个人的姓名、年龄
      • select city，name age from t where city=’杭州‘ order by name limit 1000
        • 为什么不使用优先队列进行排序而使用归并排序？
        • limit 1000，如果用优先队列的话要维护大小为1000的堆，所以使用归并
  • 全字段排序
    • tip
      • 为避免上述问题的全表扫描，可在city上建索引
      • sort_buffer: MySQL给每个线程分配一块用于排序的内存
    • 1. 初始化sort_buffer,放入要显示的三个字段
    • 2. 从索引city找到第一个满足city=杭州的主键id
    • 3. 到id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中
    • 4. 从索引city取下一个记录的主键id
    • 5. 重复3、4，直到不满足查询条件
    • 6. 对sort_buffer重点的数据按照字段name做快排
    • 7. 按排序结果取前1000行返回给客户端
  • rowid排序
    • 如果MySQL认为排序的单行长度太大就会采用rowid
    • 排序过程同全字段排序
    • 区别：最后一步，取前1000行，会按照id回到原表取出要显示的字段进行返回。（因为要显示三个字段（超出了限定字节数），所以MySQL只能将不超过限定字节数的字段进行查询，查询后再到原表中取出其他数据）。
  • 全字段排序 VS rowid排序
    • MySQL如果担心排序内存太小是会采用rowid排序。rowid排序虽然可以一次排序更多行，但是需要再回到原表去取数据
  • 举个栗子
    • 如果表中已有city_name联合索引（city，name），你要查杭州和苏州所有市民的姓名，且按名字排序，显示前100条记录。请问这个语句有排序过程吗
      • select * from t where city in（‘杭州’， ‘苏州’） order by name limit 100
        • 虽然有city和name联合索引，但对于单个city内部，name是递增的，但这条SQL语句不是单独查一个city的值，而是同时查了杭州和苏州的，因此你所有满足条件的name就不是递增的了，所以需要排序。
        • 如何避免排序？
        • 拆成两条查询语句，再用归并排序
        • 1. select * from t where city=“杭州” order by name limit 100,
          • 这个SQL不用排序，客户端用100长的内存数组A保存结果
        • 2. select * from t where city=“苏州” order by name limit 100,
          • 将结果存进数组B
        • 现在AB是两个有序数组，然后利用归并排序思想，得到name最小的前100值
• 17 | 如何正确显示随机消息？
  • 前言
    • 英语背单词app，根据用户级别随机选出三个单词出现在首页(每个级别用户有对应的单词表，随机在对应单词表中进行抽取)
    • tip
      • 在sql语句前加explain可以查看语句的执行情况
  • 随机选择三个单词的实现方法
    • 1. 内存临时表
      • order by rand();select word from words order by rand() limit 3; (SQL简单，但流程复杂)
        • 需要临时表，并且需要在临时表上排序
        • 对于InnoDB表，执行全字段排序会减少磁盘访问，因此会被优先选择；但对于内存表，回表的过程只是简单地根据数据行的位置，直接访问内存得到数据，不会导致多访问磁盘，所以mysql这时选择rowid
        • 执行流程
          • 1. 创建临时表
          • 2. 从word表中，按主键顺序取出所有的word值，对所有的word值调用rand() 生成>0,<1的随机小数，将随机小数和word值存入临时表中，扫描行数为10000
          • 3. 在临时表上按字段（随机小数）排序
          • 4. 初始化sort_buffer
          • 5. 从临时表中一行一行地取出随机小数值和位置信息，存入sort_buffer。 这个过程要对内存临时表做全表扫描，这时扫描行数为20000
          • 6. 在sort_buffer中根据随机小数的值进行排序，没有涉及表操作，无扫描行数
          • 7. 排序完成，取出前三个的位置信息，从内存临时表中取出word值，返回到客户端，扫描行数20003
      • MySQL用什么方法定位“一行数据”？使用rowid
        • 关于rowid
          • 对于有主键的InnoDB表来说，rowid就是主键ID
          • 无主键的表：rowid由系统生成
      • 简言之：order by rand() 使用了内存临时表，而内存临时表排序时使用了rowid排序方法
    • 2. 磁盘临时表
      • 不是所有的临时表都是内存表，当临时表内存大小超过了设定值，则会转成磁盘临时表
      • 使用没有显式索引的InnoDB表的排序过程
      • 采用优先队列排序算法
        • 只需得到三个最小值，而归并要全部排一遍
        • 1. 对于10000行，先取前三行，构成一个堆
        • 2. 取下一行，跟当前堆里面最大的R比较，选择是否进行替换
        • 3. 重复第二步，直至完成
    • 3. 随机排序方法
      • 随机算法一：‘只随机选择一个word值（效率很高） 很难处理id为1,2,40000,50000的情况
        • 1. 取得表的主键id的最大值M和最小值N
        • 2. 用随机函数生成结余M、N之间的数
        • 3. 取不小于X的第一个ID的行
      • 随机算法二（更严格）：随机选一个值
        • 1. 取得表的行数C
        • 2. Y=floor（C*rand()）,floor:取整数部分
        • 3. 用limit Y，1取得一行
      • 随机算法三：按照2的思路，取三个值
        • 1. 取得表的行数C
        • 2. 取得相同的随机方法得到Y1，Y2，Y3
        • 3. 再执行三个limit Y，1 语句得到三行数据
  • 举个栗子
    • 上述随机算法三的扫描行数：C+（Y1+1）+（Y2+1）+（Y3+1），如何优化？
      • 取Y1，Y2，Y3中最大的一个数记为M，最小的数记为N，然后执行 select * from t limit N，M-N+1
        • 总扫描行数：C+M+1
• 18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？（可进行项目扩展）
  • 总思想
    • 对索引字段做函数操作，可能会破坏索引值得有序性，因此优化器就决定放弃走树搜索的功能
  • 案例一：条件字段函数操作
    • 统计发生在所有年份中7月份的交易记录总数：select count（*） from tradelog where month(t_modified)=7;
      • 现象：t_modefied字段上有索引，但执行了很久才返回结果。month()是个函数
    • 为什么where t_modefied=‘2018-7-1‘ 能用上索引，而改成where month（t_modefied）=7的时候不能？
      • 原因
        • B+树提供的快速定位能力，来源于同一层兄弟节点的有序性。 B+tree的每一层存储的都是’2018-7-1‘的格式，但你传入7时，树（索引t_modefied）的第一层不知道往哪走，破坏了值得有序性，所以优化器决定放弃走树搜索的功能，但优化器并不会放弃使用索引。
      • 走不了t_modefied这个树搜索，优化器可以选择遍历主键索引，也可以选择遍历t_modefied索引，优化器对比后发现t_modefied更小，所以最终选择t_modefied。
  • 案例二：隐式类型转换
    • select * from tradelog where tradeid=110717
      • tradeid上有索引，但是explain显示需要全表扫描。原因：tradeid字段类型：varchar(32), 但输入的参数确是整形，所以要做类型转换
    • 类型转换的规则
      • MySQL中，字符串和数字做比较，会将字符串转换成数字
      • select * from tradelog where CAST（tradeid AS signed int）=110717
    • 为什么有数据类型转换，就需要走全索引扫描
      • 索引字段有cast函数，优化器会放弃走树搜索功能
  • 案例三：隐式字符编码转换
    • 两个表的字符集不同，做表连接查询时用不上关联字段的索引（索引上有convert函数操作），所以不能快速定位
    • 为什么字符集不同就用不上索引？
    • mysql内部会将utf8字符串转成utf8mb4（超集）字符集，再做比较。所以执行语句时，会将表中的字段一个个转换成utf8mb4（convert函数），然后再与参数进行比较
    • 优化
      • 1. 将表上的字符集都改成utf8mb4
      • 2. 修改SQL语句，将函数操作放在参数的位置上
        • 如：where d.tradeid=convert(l.tradeid)
• 19 | 为什么我只查一行的语句，页执行这么慢？
  • 1. 查询长时间不返回
    • select * from t where id=1
    • 大概率是表被锁住了，分析原因首先show processlist查看状态
      • 1. 等MDL锁waiting for table metadata lock
        • 有线程正在表t上请求或者持有MDL写锁，把select语句堵住了。
        • 解决方法：找到谁持有MDL写锁，然后把他kill掉
      • 2. 等flushwaiting for table flush
        • 有一个flush tables命令被别的语句堵住了，而它又堵住了我们的select语句
      • 3. 等行锁statistics
        • select * from t where id=1 lock in share mode访问id=1记录时要加读锁
        • 如果这时已经有一个事务在这行记录上持有一个写锁，我们的select语句就会被堵住（A启动了事务占有写锁，还不提交）
        • 杀死这个占有写锁的线程即可
  • 2. 查询慢
    • select * from t where c=50000 limit 1；
      • c上没有索引，走id主键顺序扫描，需要扫描50000行
  • 举个栗子
    • begin；select * from t where d=5 for update；commit；这个语句怎么加锁？何时释放锁？
      • 在select语句完成后，id=5这一行会加一个写锁，由于两阶段协议，写锁会在执行commit语句时释放
      • 由于d上没有索引，所以会做全表扫描
• 20 | 幻读是什么，幻读有什么问题？
  • 幻读
    • 一个事务在前后两次查询用一个范围的时候，后一次查询看到了前一次查询没有看到的行
  • 产生幻读的原因
    • 行锁只能锁住行，但是新插入的记录，是记录之间的“间隙”。
  • 幻读的问题
    • 1. 前后读取数据不一致
      • 要求锁住所有d=5的行（此时只锁住了一行），但这时对第二行d=5的行进行更新操作，会发现能成功更新，这样就破坏了最初的加锁声明。
    • 2. 数据和日志的一致性问题
      • 即使把所有的记录都加上锁，还是阻止不了新插入的记录
        • 我们给所有行加锁的时候，某一行还没被插入，不存在也就加不上锁
  • 如何解决幻读？
    • InnoDB引入“间隙锁”来解决，间隙锁在可重复读级别下才有效
    • 在select * from t where d=5 for update的一行行扫描过程中，给数据库已有的6个记录加了行锁，同时还加了7个间隙锁
    • 间隙锁之间不存在冲突，间隙锁与“往间隙中插入一个记录”这个操作有冲突
    • next-key lock： 间隙锁+行锁，前开后闭区间
      • 存在的问题
        • 可能会导致同样的语句锁住了更大的范围，影响了并发度
• 21 | 为什么我只改一行的语句，锁这么多？
  • 加锁规则
    • 1. 原则1： 加锁的基本单位：next-key lock（前开后闭区间）
    • 2. 原则2： 查找过程中访问到的对象才会加锁
    • 3. 优化1： 索引上的等值查询，给唯一索引加锁时，next-key lock退化成行锁
    • 4. 优化2： 索引上的等值查询，向右遍历时且最后一个值不满足等值条件时，next-key lock退化为间隙锁
    • 5. 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止
  • 案例的前提
    • t（id，c，d）（0,0,0），（5,5,5），（10,10,10），（15,15,15），（20,20,20），（25,25,25）
    • lock in share mode：只锁覆盖索引；for update：系统会顺便给主键索引上满足条件的行加锁
    • 所有案例的隔离级别：可重复读隔离级别
      • 可重复读遵循，所有加锁的资源，都是在事务提交或者回滚的时候才释放的
  • 案例一：等值查询间隙锁
    • 等值查询的间隙锁 （表t中没有id=7的记录）
      • 1. 根据原则一：加锁单位next-key lock，sesssion A 的加锁范围（5，10】
      • 2. 根据优化二：这是一个等值查询（id=7），id=10不满足查询条件，所以next-key lock退化成间隙锁，所以最终的加锁范围：（5,10）
      • 所以session B要往间隙里插入id=8的记录会被锁住，但是id=10的是可以的
  • 案例二：非唯一索引等值锁
    • • 1. 加锁单位next-key lock，区间：（0,5】
      • 2. c是普通索引，因此仅访问c=5这条记录不能马上停下来，需要向右遍历，查到c=10，才放弃。原则二：访问到的都加锁：（5,10】
      • 3. 等值判断，向右遍历，最后一个值不满足条件c=5，退化成间隙锁（5,10）
      • 4. 根据原则二：只有访问到的对象才会加锁，因为查询使用覆盖索引，不需要访问主键索引，所以主键索引上没有加任何锁，所以session B可以执行
      • 但session C的（777）会被间隙锁（5,10）锁住
      • lock in share mode：只锁覆盖索引；for update：系统会顺便给主键索引上满足条件的行加锁
      • 锁是加在索引上的
  • 案例三：主键索引范围锁
    • • 1. 开始执行时，要找到第一个id=10的行，next-key lock（5,10】，优化一：索引上的等值条件，给唯一索引加锁时，退化成行锁，所以只加了id=10这一行的行锁
      • 2. 范围查找继续往后找，找到id=15后停下来，因此需加next-key lock（10,15]
      • 注意：查找id=10时：等值判断； id=15：范围判断
  • 案例四：非唯一索引范围锁
    • 将案例三中where条件后的id改成了c
      • 第一次用c=10定位记录时，索引c上加了（5,10】的next-key lock后，因此c是非唯一索引，没有优化规则，索引不会蜕变成行锁，索引session A加的锁是索引c上的（5,10】和（10,15】两个next-key lock
      • 这里需要扫描到c=15才直到不需要继续往后找了
  • 案例五：唯一索引范围锁bug
    • • 1. session A 范围查询，索引id上加next-key lock （10,15】，id是唯一键，索引循环判断到id=15这一行就停止了
      • 2. 但实际上InnoDB会扫描到第一个不满足条件的行为止，id=20，所以（15,20】也会被锁上
  • 案例六：非唯一索引上存在“等值”的例子
    • 给表中插入：insert into t values（30,10,30）
      • 现在表中有两个c=10的行，因为非唯一索引上包含主键的值，所以两个c=10记录之间，也是有间隙的
    • • session A在遍历时，先访问第一个c=10的记录，加的next-key lock（c=5,id=5）到（c=10，id=10）
      • session A继续向右，查到（c=15,id=15），循环结束。等值查询，查到不满足的行退化成间隙锁（c=10,id=10）到（c=15,id=15）。加锁的是两个c=10的索引
  • 案例七： limit语句加锁
    • • 此案例在案例六的基础上进行，所以c=10的记录有两条
      • 明确加了limit 2的现在，因此遍历到（c=10，id=30）之后，循环结束。c上的加锁范围变成了从（c=5，id=5）到（c=10，id=30）的前开后闭区间。
      • 提示：删除数据的时候尽量加limit，更安全，减小加锁的范围
  • 案例八：一个死锁的例子
    • • session A 在索引c上加了next-key lock（5.10】和间隙锁（10,15）
      • session B的update也在c上加next-key lock（5,10】，进入锁等待
        • 1. 先加（5,10）的间隙锁，加锁成功
        • 2. 再加c=10的行锁，这时被锁住
      • sessionA插入会被sessionB的间隙锁锁住，出现了死锁，InnoDB让session B回滚
  • 举个栗子
    • 表为上述案例的表
      • 1. 由于是order by c desc， 第一个要定位的是索引c上c=20的行，所以会加间隙锁（20,25）和next-key lock（15,20】
      • 2. 索引c向左遍历，扫描到c=10停下，所以next-key会加到（5,10】，所以阻塞了session B
      • 3. c=20、c=15、c=10这三行都存在值，由于是select *，所以会在主键id上加三个行锁
      • 索引，session A的select语句锁的范围：索引c上（5,25），主键索引上id=15、20两个行锁
• 22 | MySQL有哪些“饮鸩止渴”提高性能的方法？
  • 短连接风暴
    • 原因：连接成本很高，一旦数据库处理的慢一些，连接数会暴涨
    • 解决方法
      • 1. 先解决掉那些占着连接但不工作的线程
        • 优先断开事务外空闲太久的连接，再考虑断开事务内空闲太久的连接
        • 参数max_connections：连接数的上限，只要连着就占用一个连接位置，对于不需要保持的连接，可以通过kill connection主动踢掉。
        • 查看事务的具体状态：information_schema库中的innodb_trx表
      • 2. 减少连接过程中的损耗
        • 让数据库跳过权限验证阶段
          • 重启数据库，使用-skip-grant-tables 参数启动
        • 在MySQL8中，如果启用-skip-grant-tables参数，mysql会默认把--skip-networking参数打开，表示这时候数据库只能被本地的客户端连接
  • 慢查询性能问题
    • 1. 索引没有设计好
      • 解决办法
        • 紧急创建索引
        • 直接执行alter table
          • 前提：服务器一主A一备B,，在备库先执行
          • 1. 在备库上执行set sql_log_bin=off, 就是不写binlog，然后执行alter table语句加上索引
          • 2. 执行主备切换
          • 3. 这时主库B，备库A，在A上执行set sql_log_bin=off,然后执行alter table语句加上索引
    • 2. SQL语句没写好
      • 比如索引上加上函数，导致巨慢
        • 解决办法
          • 重写
    • 3. MySQL选错了索引
      • 解决办法
        • 给语句加上force index
        • 使用查询重写功能，给原来的语句加上force index
    • 前两种方法可以预先发现问题，是可以避免的
      • 1. 上线前，在测试环境中打开slow log，并把log_query_time设置为0，确保每个语句都会被记录慢查询日志
      • 2. 在测试表里插入模拟线上的数据，做一遍回归测试
      • 3. 观察慢查询日志里每类语句的输出，特意留意Rows_examined方法。
  • QPS突增的问题
    • 有新功能的bug导致的，解决方法：使新功能下掉
      • 1. 由全新业务的bug导致的
        • 从数据库端直接将这个功能去掉
      • 2. 新功能使用的是单独的数据库用户
        • 用管理员找好把这个用户删掉，然后断开现有链接，这样新功能连接不成功，由它引发的QPS就会变成0
      • 3. 新增的功能跟主体功能是部署在一起的
        • 通过处理语句来限制，使用查询重写功能，把压力最大的SQL语句直接重写成“select 1”返回
          • 风险
            • 1. 别的功能也用到了这个sql语句的话就会误伤
            • 2. 很多业务并不是由一个语句完成逻辑的，所以单独改一个语句，后面的逻辑可能会失败
• 23 | MySQL是怎么保证数据不丢的？
  • 前言
    • WAL机制
      • 只要redo log和binlog保证持久化到磁盘，就能确保MySQL异常重启后，数据可以恢复
  • binlog的写入机制
    • 子主题 1
  • redo log的写入机制
  • 子主题 4
• 直播回顾 | 心路历程
• 常用SQL语句
  • explain +SQL
    • 查看语句的执行结果
  • show processlist
    • 查看当前语句处于什么状态