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

MySQL的事务与索引

事务

特性

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

事务的启动方式:

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

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

长事务的危害

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

索引

索引分类

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

索引类型:

  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), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件(索引字段)的记录,减少回表次数。

上一篇:FastAPI(45)- JSONResponse


下一篇:45.跳跃游戏II