参考: https://www.xiaolincoding.com/mysql/

MySQL 8.0

为什么缓存被删了: 大范围查找的场景下这东西太没用

数据库笔记

关于索引

索引整体分为两种: 聚簇索引(主值)和二级索引

MySQL 提供的几种索引创建方法:

// 主值
CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
);

// 唯一
CREATE TABLE table_name  (
  ....
  UNIQUE KEY(index_column_1,index_column_2,...) 
);

//普通索引
CREATE TABLE table_name  (
  ....
  INDEX(index_column_1,index_column_2,...) 
);

CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...); 

// 前缀索引
CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
); 

CREATE INDEX index_name
ON table_name(column_name(length));

联合索引:

CREATE INDEX index_product_no_name ON product(product_no, name);

使用联合索引时，存在最左匹配原则

优化索引

前缀索引优化:

减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。

order by 就无法使用前缀索引；
无法把前缀索引用作覆盖索引；

覆盖索引优化(联合索引)

避免回表操作, 二级索引查找时是先根据二级索引找到主值索引, 然后通过主值索引找到数据(回表), 联合索引可以用主值覆盖, 提高效率

主键索引最好是自增的

这里主要考虑到 SQL 数据库的数据存放形式: 根据主值的顺序存放, 并且以页为物理单位进行IO操作

且索引表是 B+tree, B+tree 对于自增的索引, 插入的开销很小

索引失效

6 种会发生索引失效的情况：

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效；
当我们在查询条件中对索引列使用函数，就会导致索引失效。
当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。
MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

为什么使用 B+tree 作为索引表

B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。
B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；
B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

为什么不要用重复数据进行

一个是二分查找对重复数据的比较定位很慢, 要两边找到边界

而是过多的重复索引导致大量的回表操作, 使得效率降低

关于事务

ReadView 快照

数据库中存放的基础单位是 Record , 每条 Record 都会有一个 trx_id 记录这个记录是哪个事务操作提交的, 同时后面有一个 undo_pointer 作为链表头指向这条记录以前的数据, 用于回滚.

所以实际上 ReadView 不负责存储任何数据, ReadView 存储的信息如下:

creator_trx_id	m_ids	min_trx_id	max_trx_id
创建这个 ReadView 的事务的 id	正在活跃的未提交的事务列表(静态的)	m_ids 的最小值	m_ids 最大值 +1 也表示下一个事务的 id

这样在查询undo log 的 record 的时候, 就可以根据 Record 里面记录的 trx_id 得知这个记录是否属于这个 ReadView

trx_id < min_id 表示是已经提交了的事务的记录
trx_id >= max_id 表示是后面新事务的记录, 需要访问旧版本
min_id <= trx_id < max_id 则需要查询现当时保存的静态事务列表, 判断事务是否已经提交, 然后继续.

这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。

几种程度的事务隔离是如何完成的:

读未提交: 简称不做隔离

读提交: 每次有事务提交之后都更新一个 ReadView, 然后其他事务都根据这个 ReadView 来读.

可重复读: 每次开启事务后各自生成一个 ReadView, 每个事务都只认自己最开始创建的这个 ReadView

串行化: 对记录加上读写锁

锁

三个级别:

全局锁: FTWRL 数据库只读
表级锁: 表锁, 元素据锁, 意向锁, AUTO-INC 锁
行级锁(Record 级别): Record Lock; Gap Lock; Next Key Lock.

表锁:

元数据锁（MDL）:

我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

对一张表进行 CRUD 操作时，加的是 MDL 读锁；
对一张表做结构变更操作的时候，加的是 MDL 写锁；

一般这种锁都是在对整张表做 CRUD 操作的时候加上的, 防止其他线程对这个表结构做了变更。

当有线程在执行 select 语句（加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（释放 MDL 读锁）。

反之，当有线程对表结构进行变更（加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（释放 MDL 写锁）。

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

一定要注意 MDL 长事务会引发线程爆满全部被阻塞, 原因在于长事务一般是读任务, 会加上读锁, 这是有短事务需要写表的时候会阻塞, 加入阻塞等待队列, 由于 MDL 优先偏向写锁, 所有后续的短读事务又会被这个正在等待的些表事务阻塞.

意向锁:

在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；

当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

意向锁的目的是为了快速判断表里是否有记录被加锁

AUTO-INC 锁:

之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。

AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。

因此，在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。

一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

行锁:

Record Lock: 锁住的是一条记录。而且记录有 S 锁(共享锁)和 X 锁(独占锁)之分

Cap Lock: 表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生, 间隙锁之间是兼容的.

Next-Key Lock (临键锁)= Record Lock + Gap Lock

表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

插入意向锁: 不是意向锁，它是一种特殊的间隙锁，属于行级别锁, 一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）, 如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

如何加锁:

加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间

在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成记录锁或间隙锁

加锁的目的主要是为了避免幻读, 所以以下情景从避免幻读的情况下考虑:

对于主值索引, 等值查询会退化成记录锁和间隙锁, 范围查询也会根据情况退化为记录锁和间隙锁
对于二级索引, 若是唯一索引, 其实和主值索引的退化情况是一样的
二级非唯一索引等值查询, 由于索引不唯一, 查询到的加 Next-Key 没扫描到的加间隙锁
二级非唯一索引范围查询, 全是 Next-Key, 甚至有 (0,inf] 这种全索引锁
所有二级索引查到的记录, 都会在主值索引上打上记录锁

所以二级索引的范围查询一定要避免, 会严重阻塞索引表的使用, 导致其他事务失效.

其他总结:

在 MySQL 的可重复读隔离级别下，针对当前读的语句会对索引加记录锁+间隙锁，这样可以避免其他事务执行增、删、改时导致幻读的问题。

有一点要注意的是，在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

日志

不过，更新语句的流程会涉及到 undo log（回滚日志）、redo log（重做日志）、binlog （归档日志）这三种日志：

undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC
redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复
binlog（归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制

缓存池(Buffer Pool): 操作后的记录都会先暂时放入缓存池中, 再慢慢更新到磁盘里, 提高数据库的读写性能, 称为 WAL 技术.

WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

其中先写的日志就是 redo log, 可以保证系统崩溃, 掉电, 内存中的脏页没来的及更新的时候, 可以根据 redo log 做恢复更新

redo log 在磁盘中是顺序写, 数据更新是随机写(考虑事务对数据的访问是随机的, 大概率不是同一页数据页), 所以 redo log 写入的开销更小, 提高效率.

在内存修改该 Undo 页面后，需要记录对应的 redo log

redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；

undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

binlog 是在 Server 层的日志文件, 现在主要是为了异步主从复制, 备份恢复.

binlog 只有在事务提交的时候才能写入磁盘

MySQL 集群的主从复制过程梳理成 3 个阶段：

写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。

同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。

回放 Binlog：回放 binlog，并更新存储引擎中的数据。

两阶段提交:

prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；
commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）

关于缓存池的tips

下面几种情况会触发脏页的刷新：

当 redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；
Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
MySQL 认为空闲时，后台线程会定期将适量的脏页刷入到磁盘；
MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；

Innodb 通过三种链表来管理缓页：

Free List （空闲页链表），管理空闲页；
Flush List （脏页链表），管理脏页；
LRU List，管理脏页+干净页，将最近且经常查询的数据缓存在其中，而不常查询的数据就淘汰出去。

如果觉得文章对你有用，请随意赞赏

数据库

数据库笔记

https://blog.mydawn.space/archives/271fde53-a21d-4427-9e6f-45d204da4454

作者

艾若Dawn

发布于

2024-06-19

更新于

2024-06-05

许可协议

CC BY 4.0

数据库笔记

数据库笔记

关于索引

优化索引

前缀索引优化:

覆盖索引优化(联合索引)

主键索引最好是自增的

索引失效

为什么使用 B+tree 作为索引表

为什么不要用重复数据进行

关于事务

ReadView 快照

锁

表锁:

行锁:

如何加锁:

其他总结:

日志

两阶段提交:

关于缓存池的tips

作者

发布于

更新于

许可协议

评论