MySQL实战45讲笔记

文章目录

• redo 日志 ，undo log 和 binlog
• • 两阶段提交保证一致性
  • 参考
• MYSQL 事务
• 全局锁，表锁 和 行锁
• • 全局锁
  • 表级锁
  • 行锁
  • • 两阶段锁协议
    • 死锁和死锁检测
• MVCC 机制
• 10 09 | 普通索引和唯一索引，应该怎么选择？
• 12 11 | 怎么给字符串字段加索引？
• 13 12 | 为什么我的MySQL会“抖”一下？
• • InnoDB刷脏页的控制策略
• 14 13 | 为什么表数据删掉一半，表文件大小不变？
• • 参数innodb_file_per_table
• 15 14 | count(*)的执行原理
• • 不同的 count 用法
• 17 16 | “order by”是怎么工作的？
• 19 18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？
• 21 20 | 幻读是什么，幻读有什么问题？
• 37 36 | 为什么临时表可以重名？
• • 特征
• 41 40 | insert 语句的锁为什么这么多？
• • insert … select 语句
  • insert 唯一键冲突

redo 日志 ，undo log 和 binlog

MYSQL 的整体架构如下：

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。
有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

• redo 日志，undo log 是引擎层记录的日志，是物理日志，记录的是“在某个数据页上做了什么修改”，MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。让InnoDB存储引擎拥有了崩溃恢复能力。

• binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用，是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”，MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

过程是使用两阶段提交来保证他们的一致性。因为不保证的话，会造成DB数据不一致。

两阶段提交保证一致性

参考

MySQL三大日志(binlog、redo log和undo log)详解

MYSQL 事务

https://blog.csdn.net/liushengxi_root/article/details/98723224

假设一个值从1被按顺序改成了2、3、4，在回滚日志里面就会有类似下面的记录。

当前值是4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的read-view。如图中看到的，在视图A、B、C里面，这一个记录的值分别是1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于read-view A，要得到1，就必须将当前值依次执行图中所有的回滚操作得到。
长事务的缺陷：

• 回滚段长，占用系统资源，占用锁资源，拖垮整个。

全局锁，表锁 和 行锁

全局锁

全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。

表级锁

一共有两种：

1. 表锁：表锁的语法是 lock tables … read/write
2. 元数据锁（其实就是表结构的锁）：MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

行锁

两阶段锁协议

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

• 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。

死锁和死锁检测

• 超时
• 进行死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。

MVCC 机制

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整个库的。是为每一行加上了一个事务ID字段。如图所示，就是一个记录被多个事务连续更新后的状态。

图中的三个虚线箭头，就是undo log；而V1、V2、V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。比如，需要V2的时候，就是通过V4依次执行U3、U2算出来。

10 09 | 普通索引和唯一索引，应该怎么选择？

12 11 | 怎么给字符串字段加索引？

13 12 | 为什么我的MySQL会“抖”一下？

刷磁盘的时机：

1. redo log buffer 满了。
2. 内存不够用了
3. 回写线程定时刷：LRU 淘汰机制。
4. MySQL正常关闭的情况

InnoDB刷脏页的控制策略

• InnoDB的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是redo log写盘速度
• innodb_io_capacity 这个参数了，它会告诉InnoDB你的磁盘能力。这个值我建议你设置成磁盘的IOPS
• 因此 Mysql 刷新磁盘的影响 在于 上述三者。

14 13 | 为什么表数据删掉一半，表文件大小不变？

简单地删除表数据达不到表空间回收的效果，这是为什么呐？

参数innodb_file_per_table

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数innodb_file_per_table控制的：

• 这个参数设置为OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
• 这个参数设置为ON表示的是，每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。
  从MySQL 5.6.6版本开始，它的默认值就是ON了。

15 14 | count(*)的执行原理

在不同的MySQL引擎中，count(*)有不同的实现方式。

• MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；
• 而 InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

InnoDB引擎这样麻烦，是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB表“应该返回多少行”也是不确定的。这里，我用一个算count(*)的例子来为你解释一下。

假设表t中现在有 10000 条记录，我们设计了三个用户并行的会话。

• 会话A先启动事务并查询一次表的总行数；
• 会话B启动事务，插入一行后记录后，查询表的总行数；
• 会话C先启动一个单独的语句，插入一行记录后，查询表的总行数。

我们假设从上到下是按照时间顺序执行的，同一行语句是在同一时刻执行的。

你会看到，在最后一个时刻，三个会话A、B、C会同时查询表t的总行数，但拿到的结果却不同。这是因为 每一行记录都要判断自己是否对这个会话可见，因此对于count(*)请求来说，InnoDB只好把数据一行一行地读出依次判断，可见的行才能够用于计算“基于这个查询”的表的总行数。

所以如果想要实现：快速计数，那么只能自己实现。

不同的 count 用法

弄清楚count()的语义。count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加。最后返回累计值。

所以，count(*)、count(主键id)和count(1) 都表示返回满足条件的结果集的总行数；而count(字段），则表示返回满足条件的数据行里面，参数“字段”不为NULL的总个数。

• 对于count(主键id)来说，InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不为空，就按行累加。
• 对于count(1)来说，InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不为空，按行累加。

单看这两个用法的差别的话，你能对比出来，count(1)执行得要比count(主键id)快。因为从引擎返回id会涉及到解析数据行，以及拷贝字段值的操作。

• 对于count(字段)来说：

  • 如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；

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

也就是server层要什么字段，InnoDB就返回什么字段。

但是count(*)是例外，并不会把全部字段取出来，而是专门做了优化，不取值。只判断行数即可。count(*)肯定不是null，按行累加。

参考：执行count(1)、count(*) 与 count(列名) 到底有什么区别？

所以结论是：按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)。

17 16 | “order by”是怎么工作的？

19 18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？

• 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

21 20 | 幻读是什么，幻读有什么问题？

事务的特性-隔离性之隔离级别之脏读，幻读

37 36 | 为什么临时表可以重名？

create temporary table temp_t like t1;

特征

1. 一个临时表只能被创建它的 session 访问，对其他线程不可见。所以，session A 创建的临时表t，对于 session B 就是不可见的。
2. 临时表可以与普通表同名。
3. session A 内有同名的临时表和普通表的时候，show create语句，以及增删改查语句访问的是临时表。
4. 临时表只能被创建它的session访问，所以在这个session结束的时候，会自动删除临时表

41 40 | insert 语句的锁为什么这么多？

主键自增会有自增锁来保证唯一性。MySQL对自增主键锁做了优化，尽量在申请到自增id以后，就释放自增锁。

insert … select 语句

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。

• 在可重复读隔离级别下，这个语句会给select的表里扫描到的记录和间隙加读锁。

insert 唯一键冲突