MySQL实战45讲学习笔记(三)

ordinaryBlog 2022-01-15 02:31:32 阅读数:484

学习 Mysql 实战 讲学 讲学习

事务

特性

  1. 原子性
  2. 一致性
  3. 隔离性
  4. 持久性

事务的启动方式:

  1. 显式的启动:begin或 start transaction。提交语句为commit,回滚事务语句是rollback
  2. set autocommit=0,这个命令会将这个线程的自动提交关掉。意味着如果你只执行一 个 select 语句,这个事务就启动了,而且并不会自动提交。这个事务持续存在直到你主 动执行 commit 或 rollback 语句,或者断开连接。

第二种方法可能会导致长事务
可使用commit work and chain 命令提交并开启新事务

可重复读隔离级别实现的原理

事务在启动时会创建一个视图 read-view 之后事务 T 执行期间,即使有其他事务修改了数据,事务 T 看到的仍然跟在启动时看到的一样
在这里插入图片描述
(图片来自极客时间)

创建视图的原理

在InnoDB中,每个事务都有一个唯一的事务ID—transaction id(按照申请顺序严格递增)
在InnDB的表中,每行数据都有多个版本,在每次事务更新数据的时候,会生成一个新的数据版本,并将transaction id 赋值给这个数据版本的事务ID (row trx_id)即数据表的一行数据有多个版本,每个版本都有自己的row trx_id
创建视图原理
(图片来自极客时间)
注意:V1 V2 V3 并不是物理上真实存在的,是根据当前版本和undo log 计算出来的
可知:在每次启动事务时,只需记录此时所有的“活跃”事务ID(启动还未提交的事务)
数组里面事务 ID 的最小值记为低水位,当前系统里面已经创建过的事务 ID 的最大值加 1
记为高水位。
在这里插入图片描述
(图片来自极客时间)

  1. 如果落在绿色部分,表示这个版本是已提交的事务或者是当前事务自己生成的,这个数
    据是可见的;
  2. 如果落在红色部分,表示这个版本是由将来启动的事务生成的,是肯定不可见的;
  3. 如果落在黄色部分,那就包括两种情况
    a. 若 row trx_id 在数组中,表示这个版本是由还没提交的事务生成的,不可见;
    b. 若 row trx_id 不在数组中,表示这个版本是已经提交了的事务生成的,可见

长事务的危害

  • 长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里面它可能用到的回滚记录都必须保留,这就会导致大量占用存储空间
  • 长事务会占用锁资源

更新逻辑

更新数据都是先读后写的,而这个读,只能读当前的值,称为“当前读”(current read)。

可重复读

可重复读的核心就是一致性读
在可重复读隔离级别下,只需要在事务开始的时候创建一致性视图,之后事务里的其他查询都共用这个一致性视图;

索引

索引分类

  • 单一索引
  • 复合索引
  • 主键索引
  • 唯一性索引

索引类型:

  1. 主键索引 (叶子节点存的是整行数据
  2. 非主键索引 (叶子节点内容是主键的值

普通索引和主键索引的区别

如果语句是 select * from T where ID=500,即主键查询方式,则只需要搜索 ID 这棵B+ 树;
如果语句是 select * from T where k=5,即普通索引查询方式,则需要先搜索 k 索引树,得到 ID 的值为 500,再到 ID 索引树搜索一次。这个过程称为回表
可知,普通索引需要多扫描一棵主键索引树

页的内部原理

页可以空或者充满,行记录会按照主键顺序来排序。
根据B树的特性,它可以自顶向下遍历,但也可以在各叶子节点水平遍历。因为每个叶子节点都有着一个指向包含下一条(顺序)记录的页的指针。

例如,页#5有指向页#6的指针,页#6有指向前一页(#5)的指针和后一页(#7)的指针

页分裂

页可能填充至100%,在页填满了之后,下一页会继续接管新的记录。
分裂后 上一页存 MERGE_THRESHOLD 容量的数据。在数据挪动的过程中会影响性能。除了性能外,页分裂操作还影响数据页的利用率。原本放在一个页的数据,现在分到两个页中,整体空间利用率降低大约 50%(MERGE_THRESHOLD 参数控制)

页合并

当你删了一行记录时,实际上记录并没有被物理删除,记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。
当相邻两个页由于删除了数据,利用率小于(MERGE_THRESHOLD 默认为50%)之后,会将数据页做合并

覆盖索引

覆盖索引可以减少树的搜索次数,显著提升查询性能

最左前缀原则

使用复合索引的时候,没有使用左侧的列查找,索引失效。
对于模糊查询 以‘%’开头的会造成索引失效

索引下推

以联合索引(name,age)为例

mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;

在 MySQL 5.6 之前,只能从 ID3 开始一个个回表。到主键索引上找出数据行,再对比字段值。
而 MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件(索引字段)的记录,减少回表次数。

普通索引和唯一索引

对于查询操作

在InnoDB中,数据是按照页读写的也就是说,当需要读一条记录时,需要以页为单位,整体读入内存。(InnDB中每个数据页默认为16KB)
可知在查询的过程中,普通索引和唯一索引在性能上的差异是微乎其微的

对于更新操作

change buffer机制

在更新一个数据页的时候,如果数据页在内存中就直接更新,如果不存在,就会先将这些更新操作缓存在chang buffer中(在内存中有拷贝,也会被写入到磁盘上)
将 change buffer 中的操作应用到原数据页,得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外,系统有后台线程会定期 merge。在数据库正常关闭(shutdown)的过程中,也会执行 merge 操作。
因此,对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时change buffer 的使用效果最好

更新操作过程

  • 要更新的目标页不在内存中
    唯一索引:找到满足条件的位置,判断有没有冲突,插入该值,执行结束
    普通索引:找到满足条件的位置,插入该值,执行结束
  • 要更新的目标页不在内存中
    对于唯一索引来说,需要将数据页读入内存,判断到没有冲突,插入这个值,语句执行结束;
    对于普通索引来说,则是将更新记录在 change buffer,语句执行就结束了。

change buffer 和 redo log

insert into t(id,k) values(id1,k1),(id2,k2);
(k1 所在的数据页在内存 (InnoDB buffer pool) 中,k2 所在的数据页不在内存中)

  • 在内存中,直接更新内存;
  • 没有在内存中,就在内存的 change buffer 区域,记录下“我要往 Page 2 插入一行”这个信息
  • 将上述两个动作记入 redo log 中
    在随后的读请求中
  • 读 Page 1 的时候,直接从内存返回
  • 要读 Page 2 的时候,需要把 Page 2 从磁盘读入内存中,然后应用 change buffer 里面的操作日志,生成一个正确的版本并返回结果。

redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

版权声明:本文为[ordinaryBlog]所创,转载请带上原文链接,感谢。 https://blog.csdn.net/kang2411212/article/details/120606012