MySQL整理

MySQL是后端开发很重要的一块知识体系，在这里将一些之前学习的知识，我认为比较重要的知识点，系统的整理一下：

1、一条SQL语句是如何执行的

2、SQL更新语句

2.1执行过程

• 查询语句过程
• 分析器
• 优化器
• 执行器
• 调用引擎查询接口，返回数据
• 将数据返回修改
• 调用引擎的更新接口

2.2执行过程中的关键点

• redolog
• InnoDB特有的
• WAL技术
• 先写日志后写磁盘
• 循环写
• crash-safe
• 物理日志，记录做了什么修改
• binlog
• MySQL的Server层
• 追加写
• 不具备crash-safe 的能力
• 没有记录哪些已经刷盘，哪些未刷盘
• 逻辑日志，记录语句的原始逻辑
• redolog 和 binlog 的两阶段提交
• 两阶段提交保证redolog 和binlog 的一致性
• 两阶段提交是跨系统维持数据逻辑一致性时常用的一个方案
• 

3、事务

3.1ACID

• 原子性
• 一个事务的所有操作，要么全部成功，要么全部失败
• 一致性
• 事务前后数据的完整性必须保持一直
• 隔离性
• 读未提交
• 没有视图概念
• 一个事务还没提交时，它做的变更就能被别的事务看到
• 读提交
• 事务中每句SQL执行钱获取视图
• 一个事务提交之后，它做的变更才会被其他事务看到
• 可重复读
• 事务开始时获取视图
• 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的
• 串行化
• 直接用加锁的方式来避免并行访问
• 顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”
• 持久性
• 数据的改变永久保存在数据库中

3.2在引擎层实现

3.3InnoDB支持事务，MyISAM不支持事务

3.4事务隔离的实现（MVCC）

• undolog
• 记录回滚字段，每一个回滚段其实就是一个视图
• 当一个回滚段，也就是这个视图，以及之前的视图，没有被事务持有时，就可以删除了
• 长事务，会导致很久之前的视图无法被删除，回滚日志变的很大

3.5事务的启动方式

• 显式语句启动事务
• begin
• commit
• rollback
• start transaction
• commot
• rollback
• 非显式
• set autocommit=0
• 只要执行一个select 语句，就会开启事务，且不会自动提交
• set autocommit=1
• 只要执行一个select 语句，就会开启事务，且自动提交

4、索引（上）

4.1索引常见的数据模型

• 哈希表
• KV存储结构
• 通过索引列计算出哈希值
• 计算的哈希值作为key值
• 数据内容为value值
• key相同，拉出链表
• 特点
• 无序
• 插入简单
• 区间查询复杂，需要全部扫描
• 时间复杂度为O（N）
• 适合等值查询
• 时间复杂度为O（1）
• 有序数组
• 插入困难
• 适合静态存储引擎
• 插入需要做数据迁移
• 二分查找法
• 时间复杂度O(log(N))
• N叉树
• 平衡二叉树
• 时间复杂度O(log(N))
• B-树
• 时间复杂度O(log(N))，趋近O(1)
• B+树
• 时间复杂度O(log(N))，趋近O(1)
• 存储量
• InnoDB一个整数字段为索引，N约等于1200，树高为4的时候，可以存1200的3次方值，就是约17亿
• MySql中常见的索引
• B+树（MySQL底层是经过改进的B+树）
• 非叶子节点不存储数据，只存储键值对
• 根节点常驻内存
• 磁盘访问的数据也固定大小，InnoDB为16KB
• 哈希表
• 主键索引（聚集索引）
• 叶子节点存储郑航数据
• 普通索引（非聚集索引）
• 叶子节点存主键ID
• 查询到主键ID，再去主键索引进行检索检索的过程成为会标
• 索引维护
• B+树
• 保持有序性
• 页分裂
• 页合并
• 业务层的优化
• 使用自增主键
• 插入时追加数据，不会引起页分裂
• 逻辑删除，不会引起页合并
• 业务主键
• 最好只有一个索引
• 不需要考虑普通索引的叶子节点过大
• 如果有多个索引
• 不适合字段过长，不然会让普通索引占用空间过大

5、索引（下）

5.1回表

• 回表次数计算
• 找到一个主键ID，回表一次，计算一次回表（对于MySQL的Server层来说，是找引擎拿到的记录，得到的扫描行数，可能会比实际读取的行数要少）
• 避免回表过程
• 覆盖索引
• 联合索引
• 查询条件和查询项目都在该索引上
• 最左前缀原则
• 联合索引的最左N个字段
• 字符串索引的最左M个字段
• like 语句的使用
• 索引下推（MySQL5.6）
• 在索引内部进行条件的判断，不符合的数据直接跳过，减少回表的次数

6、全局锁和表锁

6.1全局锁

• Flush tables with read lock（FTWRL）
• 用途
• 用于做全库逻辑备份
• 弊端
• 无法更新，业务停摆
• 代替方案
• 可重复的隔离级别下，开启一个事务
• mysqldump single-transaction
• set global readonloy=true（有弊端，不建议用）

6.2表锁

• 显示表锁
• lock table ... read/write
• 元数据锁（MDL）
• 对表进行增删改数据（DML)时，自动加上MDL读锁
• 对表进行修改表结构的操作（DDL）时，会自动加上写锁
• 读锁之间不互斥
• 读写锁之间、写锁之间是互斥
• MySQL5.5引入MDL
• MDL的风险
• MDL读锁（长事务）
• MDL写锁（等待读锁释放）
• MDL读锁N个，等待写锁，线程爆满，卡死
• 解决方案
• 尝试kill 长事务
• 停掉DDL
• 事前给MDL写锁设定等待时间

7、行锁

7.1相关知识点

• 行锁时有各个引擎实现的
• InnoDB支持
• MyISAM不支持
• 行锁可以有效的控制并发

7.2两阶段锁协议

• 行锁时在需要的时候加上，事务结束时释放
• 由于两阶段锁协议，程序设计时，对并发高的行的SQL语句，尽量放在事务的后面

7.3死锁

• 概念及场景
• 事务A更新语句锁住行1
• 事务B更新语句锁住行2
• 事务A更新语句拿到行2的写锁，卡住
• 事务B更新语句拿到行1的写锁，卡住
• 死锁的解决方案
• 等待超时
• innodb_lock_wait_timeout设置具体超时时间
• 设置超时时间太长，高并发的业务无法接受
• 设置时间过短，会误杀部分正常的锁等待
• 死锁检测
• 判断自己加入是否会产生死锁的时间复杂度为O（N）
• 当访问同一行的线程较多时，例如1000个，死锁检测就是100万
• 死锁检测的关闭，可能回出现大量的超时
• 控制对行修改的并发量
• 一行变多行，负载均衡的思路

8、事务2

8.1一致性读视图（快照）consistent view

• RC读提交
• RP可重复读

8.2快照在MVCC的工作方式

• 事务ID
• 每个事务都有事务ID，事务ID严格递增
• 行数据版本ID， row trx_id
• 每行数据有可能有多个版本
• 事务在更新了该行后会将事务ID记录成改行的row trx_id
• 每个版本的数据，并非真实的物理妇女在，而是根据undolog 推算出来的
• 事务在启动后第一个SQL，拿到最新可见的row trx_id（可重复读）
• 产生了row trx_id，但是事务未提交，则不可见
• 产生了row trx_id，但是事务已经提交，则可见
• 事务在执行update语句（或行写锁语句）（可重复读）
• 重新拿到最可见的row trx_id（当前读）
• 额外说明
• 上面都是在RP可重复读的隔离级别下
• RC的隔离级别下，没一个SQL语句，都会执行当前读

9、普通索引和唯一索引的选择

9.1从查询来看

• 两者之间差距过小，基本一直

9.2从更新来看

• 优先选择普通索引，因为使用了change buffer

9.3change buffer

• 简述
• 数据页没在内存中，在不影响数据一致性的前提下，将更新操作缓存在change buffer中
• 减少读取磁盘的操作
• 减少内存的使用
• 持久化数据
• merge
• 当访问到这个数据页的时候
• 定期merge
• 数据库正常关闭
• 唯一索引需要判断唯一性约束，所以不能使用change buffer，实际上也只能普通索引可以使用
• 对写多读少的表，有很好的优化效果，但是对于更新后立即查询的表，反而增加了change buffer 的维护成本。

10、MySQL的索引选择

10.1 优化器的逻辑

• 扫描行数
• 扫描行数越少，访问磁盘数据的次数越少，消耗CPU的资源越少
• 区分度，采用的是采用统计
• 临时表
• 是否排序

10.2关于选择错误索引的问题

• 扫描行数计算错误
• 回表次数较多，不如全表扫描，放弃回表
• 排序和临时表的影响，选择扫描行数大的索引

10.3 关于选择错误索引的问题

• 重新统计索引信息（analyze table t）
• 使用强制索引（force index）
• 调整索引，删除垃圾索引等等

10.4 使用 EXPLAIN 去分析SQL语句

11、字符串字段加索引

11.1 前缀索引

• 前缀索引会增加扫描行数
• 调查其区分度，决定前缀长度，控制扫描行数
• 使用前缀索引，无法利用索引覆盖的优势
• 索引选取的越长，占用的磁盘空间就越大，相同数据页能放下的索引值就越少，搜索的效率也就会越低

11.2 倒序存储

• 根绝索引字段的特殊性，将字段内容倒序，然后再利用前缀索引，例如身份证

11.3 hash

• 新增一列，用来存储hash值，然后以该列为索引

11.4 hash和倒序存储的区别

• 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。
• 在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
• 从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

12、flush

12.1 fiush触发的场景

• redolog写满了
• 不接受更新
• 系统内存不足
• 此时系统的反应，淘汰最久不适用的数据页
• 干净页，直接淘汰
• 脏页，flush到磁盘，再淘汰
• 系统空闲
• MySQL正常关闭

12.2 flush 的策略

• 合理设置innodb_io_capacity 的值，不要让它经常接近75%
• flush脏页的时候，如果“邻居”也是脏页，也会一起刷掉，“邻居”的“邻居”也是如此，蔓延下去

13、数据删除

13.1 参数 innodb_file_per_table

• ON
• 表数据存储在.ibd为后缀的文件中
• drop table，删除文件，空间回收
• delete，标识行记录为已删除，空间不回收，后续复用
• 待复用的行记录变成空洞
• 当一个数据所有记录都被删除，那么这个数据页标记成可复用
• 页合并，两个数据页利用率都很小，就合并成一个数据页，多出来的数据页标记成可复用
• insert 语句在产生页分裂的场景下，也会产生空洞
• update 也会产生空洞
• OFF
• 表数据存放在系统共享表空间
• drop table ，空间不会回收

13.2 解决空洞的办法

• 重建表
• 优势：简单
• 劣势：需要阻止其他更新，非 online 模式
• online DDL
• 建立临时文件，扫描对象表主键的所有数据页，MDL写锁
• 扫描完成后，写锁退化为MDL读锁
• rowlog 记录所有在【临时文件生成】过程中的更新
• 将rowlog 应用到临时文件中
• 将临时文件替换原有的数据文件

14、count()

14.1 count(*)

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高
• 而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数
• 这里需要注意的是，讨论的是没有过滤条件的 count(*)，如果加了where 条件的话，MyISAM 表也是不能返回得这么快的
• MyISAM 表虽然 count(*) 很快，但是不支持事务
• show table status 命令虽然返回很快，但是不准确
• InnoDB 表直接 count(*) 会遍历全表，虽然结果准确，但会导致性能问题
• 并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加

14.2 count(主键id)

• InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加

14.3 count(1)

• InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加

14.4 count(字段)

• 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加
• 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加

14.结论

• 按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以建议尽量使用count(*)

15、order by

15.1 MySQL会给每个线程分配一块内存用于排序，称为sort_buffer

15.2 由max_length_for_sort_data决定排序方法

• 行长度 <= max_length_for_sort_data
• 全字段排序
• 排序数据量 > sort_buffer_size —》 外部排序
• 排序数据量 <= sort_buffer_size —》内部排序
• 行长度 > max_length_for_sort_data
• rowid排序
• 取出主键ID和要排序的字段 —》排序，拿着主键ID去获取其他数据
• 排序数据量 > sort_buffer_size —》外部排序
• 排序数量 <= sort_buffer_size —《 内部排序

15.3 全字段和rowid 排序对比

• rowid 排序会要求回表造成磁盘IO，因此不会被优先选择
• 如果内促够多，就要多利用内存，尽量减少磁盘访问

15.4 利用联合索引减少排序

16、临时表

16.1 什么时候用到临时表

• union
• group by
• ......

16.2 临时表的种类

• 小于 tmp_table_size
• 内存临时表，memory 引擎
• 大于 tmp_table_size
• 磁盘临时表，InnoDB 引擎

16.3 临时表对排序方法的选择

• 内存表选择rowid 排序，因为临时内存表的回表，也只是通过数据行的位置，访问内存数据而已
• 磁盘表的排序方式，同order by 的选择

17、关于索引的案例

17.1 条件字段函数操作

• create index xx_time ....;
• select count(*) from T where month(xx_time) = 2;
• 1.由于在字段上加了函数，所以不会走所以
• 2.但是查询的是count(*)，又由于主键索引比普通索引大，又走了该索引
• 3.虽然走了该索引，但是扫描行数还是很大，说明把整个索引树都进行了扫描
• 小结
• 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。需要注意的是，优化器并不是要放弃使用这个索引

17.2 隐式类型转换

• select * from T where xxString = 1001
• 1.xxString是一个varchar类型的字段
• 2.那么上面的SQL就相当于 select * from T CAST(xxString as signed int) = 1001
• 因为做了函数操作，所以也会放弃走索引
• 需要注意的是，上面的例子中的where条件，其实是字符串和数字的对比！需要注意的是，上面的例子中的where条件，其实是字符串和数字的对比！则条件字段不会有函数操作，不会放弃走索引

18、一条SQL执行的慢

18.1 阻塞

• 等待MDL 写锁
• 表数据操作语句（DML）会产生MDL读锁
• 表结构变更语句（DDL）会产生MDL写锁
• MDL读写锁互斥
• MDL 是表的元数据锁
• 等待 flush
• flush 的执行速度一般都很快，其本身有可能被其他语句堵死了
• 等待行锁

18.2 性能慢

• 没有走索引，扫描行数过多
• 事务开启时，就拿到了一致性视图，而其他事务产生了过多的回滚段
• 

19、各种读的概念

19.1 脏读

• 主要出现在读未提交的隔离级别下，读取到了未提交的数据结果
• 解决办法：变更事务的隔离级别

19.2 不可重复读

• 主要出现在RU，RC两种隔离级别下
• 解决办法：变更事务的隔离级别为可重复读

19.3 幻读

• 读取到了其他事务插入的数据
• 产生的问题
• 破坏了数据的一致性，binlog的顺序发生了错乱
• 解决方法
• 将隔离级别调整到可重复读，间隙锁（gap lock）
• 读提交的隔离级别，将binlog设置成row的格式

20、加锁的规则

20.1 两个“原则”、两个“优化”和一个“bug”

• 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间
• 原则 2：查找过程中访问到的对象才会加锁。
• 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
• 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

21、主库、备库、从库

21.1 准备切换流程

• 流程图
• 

• 内部流程图
• 

• 内部流程图说明：
• 1.在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
• 2.在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
• 3.主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。
• 4.备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
• 5.sql_thread 读取中转日志，解析出日志里的命令，并执行。
• 保证高可用

21.2 binlog的三种格式

• statement （SQL的原语句）
• row
• mixed

21.3 主库和从库

• 从库和备库在概念上其实差不多，在HA过程中被选成新主库的，称为备库，其他的称为从库
• HA过程，和高可用性专一

22、数据库的误删除操作

22.1 使用delete语句误删除数据化

• 使用Flashback工具
• Flashback 工具原理：修改binlog 的内容

23、JOIN

• 有两张表结构一样的表，t1,t2

23.1 Index Nested-Loop Join（NLJ）

• select * from t1 straight_join t2 on (t1.a=t2.a)
• 在这个语句里，t1 是驱动表，t2 是被驱动表。
• 在这条语句里，被驱动表 t2 的字段 a 上有索引，join 过程用上了这个索引，这个语句执行流程如下
• 1.从表 t1 中读入一行数据 R；
• 2.从数据行 R 中，取出 a 字段到表 t2 里去查找；
• 3.取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；
• 4.重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。
• 小结：
• 对驱动表 t1 做了全表扫描，而对于每一行 R，根据 a 字段去表 t2 查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行
• 如何选择驱动表
• 在上面 join 语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。
• 假设被驱动表的行数是 M，驱动表的行数是 N
• 被驱动表每次搜索一棵树近似复杂度是以 2 为底的 M 的对数，记为 log M，所以在被驱动表上查一行的时间复杂度是 2*log M。
• 驱动表执行过程就要扫描驱动表 N 行，然后对于每一行，到被驱动表上匹配一次。
• 整个执行过程，近似复杂度是 N + N*2*log M。
• N 对扫描行数的影响更大，因此应该让小表来做驱动表
• 小结：
• 使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；
• 如果使用 join 语句的话，需要让小表做驱动表。

23.2 Simple Nested-Loop Join

• select * from t1 straight_join t2 on (t1.a=t2.b);
• 这个Simple Nested-Loop Join 算法太“笨重了”，mysql没有使用这个算法，而是使用了另外一个叫做“Block Nested-Loop Join” 的算法，简称 BNL。

23.3 Block Nested-Loop Join（BNL）

• 被驱动表上没有可用的索引
• 把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存；
• 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。
• join_buffer ：
• 大小是由join_buffer_size 设定的，默认是256k。如果放不下表t1 的所有数据，策略很简单，就是分段放。
• 如果join语句很慢，就把 join_buffer_size 改大
• 小表的概念：
• 在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。
• 能不能使用 join 语句？
• 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
• 如果使用Block Nested-Loop Join，扫描行数就会过多。尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。
• 在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样。
• 如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？
• 如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；
• 如果是 Block Nested-Loop Join 算法：
• 在 join_buffer_size 足够大的时候，是一样的；
• 在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。
• 所以，这个问题的结论就是，总是应该使用小表做驱动表。
• 小结：
• 1. 如果可以使用被驱动表的索引，join 语句还是有其优势的；
• 2. 不能使用被驱动表的索引，只能使用 Block Nested-Loop Join 算法，这样的语句就尽量不要使用；
• 3. 在使用 join 的时候，应该让小表做驱动表。

23.4 join语句怎么优化

• 1. BKA 优化是 MySQL 已经内置支持的，建议你默认使用；
• 2. BNL 算法效率低，建议你都尽量转成 BKA 算法。优化的方向就是给被驱动表的关联字段加上索引；
• 3. 基于临时表的改进方案，对于能够提前过滤出小数据的 join 语句来说，效果还是很好的；
• 4. MySQL 目前的版本还不支持 hash join，但你可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。