mysql 学习笔记_mysql学习笔记

目录

一 基础架构

1.1 Server 层

1.2 存储引擎层

1.3 SQL 读写执行过程

二 日志系统

三 事务隔离

四 索引

五 锁

5.1 全局锁

5.2 表级锁

5.3 行锁

六 一致性读、当前读、幻读

6.1 一致性读

6.2 当前读

6.3 幻读

---

一 基础架构

一般来说，MySQL 可以分为 Server 层和存储引擎层两部分。

1.1 Server 层

Server 层主要由连接器、查询缓存、分析器、优化器、执行器构成。

1.1.1 连接器

主要功能是管理连接、权限验证。

如何管理连接？

尽量使用长连接，定期断开长连接，比如8小时断开连接。

如果想查看连接状态使用：show processlist。

什么是长连接和短连接？

• 长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
• 短连接是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

1.1.2 查询缓存

主要功能是缓存数据，命中直接返回结果，不建议使用查询缓存。

为什么不建议使用查询缓存？

一旦表有更新，查询缓存就被清空。MySQL 8.0 已经去掉了查询缓存功能。

1.1.3 分析器

对 SQL 语句进行词法分析、语法分析，明确 SQL 语句符合 Mysql 词法和语法规范。

1.1.4 优化器

生成执行计划，索引选择，最优执行路径选择。说白了就是有 N 调执行路径，选择最优路径。

1.1.5 执行器

与存储引擎交互，对数据进行读写操作。

1.2 存储引擎层

存储引擎层负责数据的存储和提取。

1.3 SQL 读写执行过程

整体来说 MySQL 各个组件，边界清晰，权责分明。那查询语句和更新语句是怎么样执行的呢？

简单来说：

1. 连接器建立连接通道；
2. 分析器分析你要做什么；
3. 优化器指导你要怎么做；
4. 执行器是执行做这个动作；
5. 存储引擎层执行数据读写。

二 日志系统

Mysql 每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。

2.1 如何解决 IO 成本、查找成本高的问题？

使用 WAL 技术来解决问题，WAL 的全称是 Write-Ahead Logging，就是先写日志，再写磁盘。

redo log：重做日志，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

binlog：归档日志，对 sql 语句进行归档。

2.2 为什么有两个日志？

最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。

InnoDB 是以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用 redo log 来实现 crash-safe 能力。

2.3 两个日志有什么不同？

1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

2.4 什么是两阶段提交？

1. 更新一条数据的时候，存储引擎将更新记录写到 redo log 里面，此时 redo log 处于 prepare 状态。通知执行器我这块已经 ok 了，随时能提交事务。
2. 执行器生成更新操作的 binlog，并将日志写入磁盘。
3. 执行器通知存储引擎可以提交了，存储引擎将 redo log 更新成 commit 状态。

两阶段提交是为了保证通过归档日志恢复的数据和真实数据库的数据保证一致。

三 事务隔离

3.1 什么是事务？

事务是一组操作，要么成功，要么失败。

3.2 多个事务同时执行？

有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题。

3.3 如何解决多个事务执行出现的各种问题？

引入事务隔离，事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

读未提交：一个事务还没提交时，它做的变更就能被别的事务看到。

读提交：一个事务提交之后，它做的变更才会被其他事务看到。

可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

串行化：顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

oracle 数据库的默认隔离级别是“读提交”，mysql 数据库的默认隔离级别是 “可重复读”。

3.4 事务隔离怎么实现呢？

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

不同时刻启动的事务会有不同的视图，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。

四 索引

4.1 怎么理解索引？

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

4.2 索引怎么实现？

二叉搜索树的特点是：父节点左子树所有结点的值小于父节点的值，右子树所有结点的值大于父节点的值。

树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。这个多叉树就是 B+ 树。

每一个索引在 InnoDB 里面对应一棵 B+ 树。N 个索引对应 N 棵 B+ 树。

根据叶子节点的内容，索引类型分为主键索引和非主键索引：

在 InnoDB 里，主键索引也被称为聚簇索引（clustered index），主键索引的叶子节点存的是整行数据。

在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。非主键索引的叶子节点内容是主键的值。

由于非主键索引存储的是主键的值，所以针对非主键索引的查询，需要查询主键索引，也就是回表，比主键查询多查一棵树。

五 锁

为什么需要锁？

锁是为了解决并发问题而存在的。

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

5.1 全局锁

顾名思义，就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。

全局锁的典型使用场景是，做全库逻辑备份。

5.2 表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

MDL（metadata lock)。

不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。

在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

1 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

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

5.3 行锁

什么是两阶段锁？

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

行锁有哪些呢？

分为读锁和写锁

• 读锁之间是兼容的，
• 读写锁、写锁之间是冲突的。

和行锁有冲突关系的是另外一个行锁。

什么是间隙锁？

• 跟间隙锁存在冲突关系的是”往这个间隙插入一个记录“这个操作。
• 间隙锁之间不存在冲突关系。

间隙锁和行锁合称 next-key lock。每个 next-key lock 都是前开后闭区间。

间隙锁是在可重复读隔离级别下才生效。

六 一致性读、当前读、幻读

6.1 一致性读

1. 一致性读，也就是一致非锁定读，也可以称为快照读，其实就是普通的读取即普通 SELECT 语句。其中普通的 SELECT 操作不包括 select ... lock in share mode，select ... for update。
2. 对于可重复读隔离级别，快照会在事务开始的时候生成，在本事务中对有数据变更才会更新快照。
3. 对于读提交隔离级别，每次读取都会重新生成一个快照，读取只承认在语句启动前就已经提交完成的数据。

一致性读是如何实现的？     

可重复读隔离级别下，一致性读是通过 MVCC 和 undo log 来实现的。

6.2 当前读

     1. 更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。 

     2. select ... lock in share mode、select ... for update、insert、update、delete 都是当前读。

以 update 为例，假设有两个事务 A 和 B 都对同一行记录进行变更，A 事务先启动，后提交；B 事务后启动，先提交。

如果不是当前读，由于事务 A 后提交，A 的变更将导致事务 B 的变更丢失，这个是不合理的，所以肯定得当前读。

6.3 幻读

      1. 幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

      2. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的，幻读只有在“当前读”下才会出现。

幻读是如何解决的？

当前读出现的幻读是通过对行记录添加 next-key lock 来解决幻读问题的。