MySQL总结

MySQL 执行一条查询语句的流程

连接建立
- 建立连接
- 管理连接
- 校验用户身份
Serve层
- 查询缓存
  - 查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
- 进行词法分析
- 进行语法分析
- 构建语法树
- 执行SQL
  - prepare 预处理
    - 检查 SQL 查询语句中的表或者字段是否存在；
    - 将 select * 中的 * 符号，扩展为表上的所有列；
  - optimize 优化
    - 优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。
  可以使用 explain 来解释查询语句的执行过程
  1
  explain select * from table_name where id=1;
  - execute 执行
    - 主键索引查询
    - 全表扫描
    - 索引下推

mysql中的一行记录是如何存储的

其中，库的数据存储于/var/lib/mysql/databaseName

.opt文件用来存储当前数据库的默认字符集和字符校验规则
.frm文件用来保存表的结构
.ibd文件用来存储表中的数据

表中间的文件的结构

innodb 的数据是以页为单位来读写的。默认的页大小为 16KB；page是innoDB存储引擎磁盘管理的最小单位
为了保证链表中相邻的物理位置也相邻，按照extent为单位进行分配，其大小为1M，对于16KB的page来说，连续的64个page为一个extent
表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。
- 索引段：存放 B + 树的非叶子节点的区的集合；
- 数据段：存放 B + 树的叶子节点的区的集合；
- 回滚段：存放的是回滚数据的区的集合，之前讲事务隔离 (opens new window)的时候就介绍到了 MVCC 利用了回滚段实现了多版本查询数据。

B+树如何进行查询数据

只有叶子节点存放了数据；非叶子节点仅用来存放目录项作为索引
非叶子节点分为不同的层次，通过分层开降低每一层的搜索量
所有的节点按照索引键大小排序，构成一个双向链表，便于范围查询

通过二分法快速定位到包含该记录的page。定位到page后，又会在该page中进行二分法快速定位记录所在的分组，最后在分组中进行遍历查询

其中，又分为主键索引+二级索引

主键索引中是直接查找到对应行的数据
在使用二级索引进行查找数据时，如果查询的数据能在二级索引找到，那么就是「索引覆盖」操作，如果查询的数据不在二级索引里，就需要先在二级索引找到主键值，需要去聚簇索引中获得数据行，这个过程就叫作「回表」

mysql的写操作的执行流程 todo

mysql如何分库分表 todo

分库

分库同样是为了应对超大数据带来的巨大的IO需求，如果不拆库，那么单库所能支持的吞吐能力和磁盘空间，就会成为制衡业务发展的瓶颈。分库的主要目的是为突破单节点数据库服务器的I/O能力限制，解决数据库水平扩展性问题。

分区和分表可以把单表分到不同的硬盘上，但不能分配到不同服务器上。一台机器的性能是有限制的，用分库可以解决单台服务器性能不够，或者成本过高问题。

可以将一个库分成多个库，并在多个服务器上部署，就可以突破

分表

水平分表

按照范围进行切分
hash取模
.按照某个字段进行分表（国籍、地理位置、安卓端|IOS端）
按照时间进行分表

垂直分表

一般是表中的字段较多，或者有数据较大长度较长（比如text，blob，varchar(1000)以上的字段）的字段时，我们将不常用的，或者数据量大的字段拆分到“扩展表”上

这样避免查询时，数据量太大造成的“跨页”问题。

为什么选择B+树作为innoDB的索引数据结

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

三范式

数据库范式有 3 种：

1NF(第一范式)：属性不可再分。
2NF(第二范式)：1NF 的基础之上，且非主属性完全依赖于主属性
3NF(第三范式)：3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖。

基本数据类型

MySQL 中有多种数据类型，每种类型用于存储不同类型的数据。以下是一些常见的 MySQL 数据类型：

整数类型（Integer Types）:
- TINYINT: 1 字节，范围为 -128 到 127 或 0 到 255。
- SMALLINT: 2 字节，范围为 -32,768 到 32,767 或 0 到 65,535。
- MEDIUMINT: 3 字节，范围为 -8,388,608 到 8,388,607 或 0 到 16,777,215。
- INT 或 INTEGER: 4 字节，范围为 -2^31 到 2^31-1 或 0 到 2^32-1。
- BIGINT: 8 字节，范围为 -2^63 到 2^63-1 或 0 到 2^64-1。
浮点数类型（Floating-Point Types）:
- FLOAT: 单精度浮点数，4 字节。
- DOUBLE 或 REAL: 双精度浮点数，8 字节。
定点数类型（Fixed-Point Types）:
- DECIMAL 或 NUMERIC: 存储固定精度的小数，通常用于货币。
字符串类型（String Types）:
- CHAR: 固定长度字符串。
- VARCHAR: 变长字符串。
- TEXT: 较大的文本字段。
- BINARY: 固定长度二进制字符串。
- VARBINARY: 变长二进制字符串。
- BLOB: 较大的二进制对象。
日期和时间类型（Date and Time Types）:
- DATE: 日期，格式为 ‘YYYY-MM-DD’。
- TIME: 时间，格式为 ‘HH:MM:SS’。
- DATETIME: 日期和时间，格式为 ‘YYYY-MM-DD HH:MM:SS’。
- TIMESTAMP: 时间戳，与 DATETIME 类似，但受时区影响。
- YEAR: 年份，以四位数表示。
枚举和集合类型（Enum and Set Types）:
- ENUM: 用于存储枚举类型的值。
- SET: 用于存储多个枚举类型的值。
其他类型:
- BOOLEAN 或 BOOL: 布尔类型，MySQL 中通常用 TINYINT(1) 实现。
- JSON: 存储 JSON 数据。

NULL 和 ‘’ 的区别是什么？

NULL 跟 ''(空字符串)是两个完全不一样的值，区别如下：

NULL 代表一个不确定的值,就算是两个 NULL,它俩也不一定相等。例如，SELECT NULL=NULL的结果为 false，但是在我们使用DISTINCT,GROUP BY,ORDER BY时,NULL又被认为是相等的。
''的长度是 0，是不占用空间的，而NULL 是需要占用空间的。
NULL 会影响聚合函数的结果。例如，SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。 COUNT 的处理方式取决于参数的类型。如果参数是 *(COUNT(*))，则会统计所有的记录数，包括 NULL 值；如果参数是某个字段名(COUNT(列名))，则会忽略 NULL 值，只统计非空值的个数。
查询 NULL 值时，必须使用 IS NULL 或 IS NOT NULLl 来判断，而不能使用 =、!=、 <、> 之类的比较运算符。而''是可以使用这些比较运算符的。

drop、delete、truncate的区别

drop(丢弃数据) ：drop table table_name 直接将表删除
truncate（清空数据）： truncate table table_name 只删除表中的数据，但表的结构不发生变化。插入数据从第一行开始，自增id从1开始
delete（删除数据）：delete from tanble_name where coloum=xxx删除行数据，如果不加where条件和truncate 作用类似，但id是从之前最大的开始增长

数据库设计通常分为哪几步?

需求分析 : 分析用户的需求，包括数据、功能和性能需求。
概念结构设计 : 主要采用 E-R 模型进行设计，包括画 E-R 图。
逻辑结构设计 : 通过将 E-R 图转换成表，实现从 E-R 模型到关系模型的转换。
物理结构设计 : 主要是为所设计的数据库选择合适的存储结构和存取路径。
数据库实施 : 包括编程、测试和试运行
数据库的运行和维护 : 系统的运行与数据库的日常维护。

事务

ACID

atomicity 原子性用 undoLog (回滚)实现

一个事务中的所有操作，要么全部完成，要么全都不完成，不回结束在中间某个状态。

consistency 一致性用 原子性+隔离性+持久性 实现

是指事务操作前后，数据满足完整性约束，数据库保持一致性。比如，用户 A 和用户 B 在银行分别有 800 元和 600 元，总共 1400 元，用户 A 给用户 B 转账 200 元，分为两个步骤，从 A 的账户扣除 200 元和对 B 的账户增加 200 元。一致性就是要求上述步骤操作后，最后的结果是用户 A 还有 600 元，用户 B 有 800 元，总共 1400 元，而不会出现用户 A 扣除了 200 元，但用户 B 未增加的情况（该情况，用户 A 和 B 均为 600 元，总共 1200 元）。

isolation 隔离性用MVCC （多版本并发控制）实现

数据库允许多个并发事务同时对同一行数据进行读写，隔离性可以防止多个事务并发执行时由于交叉执行的数据不一致。

durability 持久性用redoLog（重做）实现

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

并发事务会引发什么问题

脏读

如果一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象。

不可重复读

在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。

幻读

在一个事务中，由于其他事务的插入或删除操作，导致同一个查询在事务执行期间返回不同的行数；虽然可重复读可以保证同一事物内数据的一致性，但不能阻止其他满足要求事务对数据表进行插入和删除操作。要解决这个问题，可以使用更高级别的隔离级别，如串行化（Serializable），但这可能会导致性能下降。在实际应用中，需要权衡事务的隔离级别和性能需求，选择合适的隔离级别。

此外，一些数据库系统还提供了特定的机制，如行级锁或MVCC（多版本并发控制），以减轻幻读的影响。

隔离级别

SQL 标准提出了四种隔离级别来规避这些现象，隔离级别越高，性能效率就越低，这四个隔离级别如下：

读未提交

☞ 事务未提交，它的变更其他事务能看到

读提交

☞ 事务提交后，它的变更其他事务才能看到

可重复读

☞ 事务执行过程中看到的数据和这个事务一开始是一致的，这也是MySQL InnoDB引擎的默认隔离级别

串行化

☞ 对记录加上读写锁，多个事务对这条记录进行读写操作时，如果发送了冲突，后访问的事务必须等待前一个事务执行完成，阻塞，才能继续执行

MySQL InnoDB 引擎的可重复读隔离级别（默认隔离级），根据不同的查询方式，分别提出了避免幻读的方案：

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读。
针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读。

我举例了两个发生幻读场景的例子。

第一个例子：对于快照读， MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。

第二个例子：对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读。

所以，MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上避免了幻读现象的发生。

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select … for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

日志篇

undoLog

是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。

每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undo log 里，比如：

在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了

一条记录里面每次更新都会产生的undolog格式里都有一个roll_pointer 和trx_id

通过trx_id 可以知道记录是被哪些事务修改的
roll_pointer可以将这些 undolog 串成一个链表，这个链表即使版本链

undo log 记录的格式：

Undo Log的物理格式在不同版本的MySQL中可能会有所不同，但它通常包含以下几个关键部分：

事务ID（Transaction ID）：每个事务都会被分配一个唯一的事务ID。
记录类型（Record Type）：标识undo记录的类型，例如插入、更新或删除。
表ID（Table ID）：标识被修改的表。
行ID（Row ID）：标识被修改的行。
旧值（Old Value）：记录被修改之前的值。
新值（New Value）：记录被修改之后的值。
指针（Pointer）：指向下一个或前一个undo记录的指针。

{
  "transaction_id": 12345,
  "records": [
    {
      "record_type": "INSERT",
      "table_id": 1,
      "row_id": 1,
      "old_value": null,
      "new_value": {"id": 1, "name": "John"}
    },
    {
      "record_type": "UPDATE",
      "table_id": 1,
      "row_id": 1,
      "old_value": {"id": 1, "name": "John"},
      "new_value": {"id": 1, "name": "Jane"}
    },
    {
      "record_type": "DELETE",
      "table_id": 1,
      "row_id": 1,
      "old_value": {"id": 1, "name": "Jane"},
      "new_value": null
    }
  ]
}

undo log的另一个作用：通过ReadView +undoLog 实现MVCC

undoLog 和数据页的刷盘策略一样，都需要通过redoLog实现持久化

其中，buffer pool中也有 undo页，对于undo的修改页也都会记录到redoLog中。redoLog 会每秒刷盘,同时，提交事务时也会刷盘

数据页和

redoLog

redoLog 是Innodb引擎特有的日志，实现了事务的持久性，主要用于断电后的故障修复，实现了 crash-saafe的功能
将写操作从【随机写】变为【顺序写】
redoLog 是物理日志，记录的是 在某个数据页上做了什么修改
redo log是循环写的，空间固定会用完

#todo

redolog什么时候落盘

binlLog

Server特有的日志，binLog实现了数据的备份和主从复制
binLog记录的是逻辑日志，记录的是这个语句的原始逻辑
binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

Binlog有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。

~~GPT的解释~~

以下是一个简单的 Binlog 记录的示例，假设我们执行了一个插入操作：

1 2	`sqlCopy code INSERT INTO example_table (ID, Value) VALUES (3, 'D');`

对应的 Binlog 记录可能类似于：

1
2
3

sqlCopy code| Event Type | SQL Statement                                   |
|------------|-------------------------------------------------|
| INSERT     | INSERT INTO example_table (ID, Value) VALUES (3, 'D') |

在这个示例中：

Event Type（事件类型）： 表示执行的操作类型，这里是一个插入操作（INSERT）。
SQL Statement（SQL 语句）： 表示实际执行的 SQL 语句。

与 Redo Log 不同，Binlog 记录的是逻辑层面的数据库更改。这使得 Binlog 具有以下特点：

跨数据库平台可用： Binlog 记录的是 SQL 语句，因此它可以更轻松地在不同的数据库平台之间迁移和复制数据。
易读性： Binlog 记录可以相对容易地阅读，因为它记录的是实际执行的 SQL 语句，而不是物理层面的更改。
灵活性： Binlog 记录的是逻辑操作，可以更好地支持对数据库的灵活操作，例如数据定义语言（DDL）操作。

在 MySQL 中，Binlog 主要用于以下方面：

主从复制： Binlog 用于将数据从一个 MySQL 服务器复制到另一个，以实现主从复制的功能。
数据恢复： Binlog 记录允许在发生故障时从备份中进行恢复，以最小化数据丢失。
数据审计： Binlog 记录的 SQL 语句可以用于审计数据库的更改历史。

MVCC

**multi-veriosn concurency control **

对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同：

「读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。

这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列」的比对，来控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）。

如何避免幻读

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

索引

索引的分类

数据结构
- B+ 树、HASH索引、FULL-TEXT索引
物理存储
- 主键索引（聚簇索引）、二级索引（辅助索引）
字段特性
- 主键索引、唯一索引、普通索引、前缀索引
字段个数

索引的模式

哈希表
有序表
搜索树

B+ 树

从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

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

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

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

建立索引

联合索引

-- 在表创建时添加索引
CREATE TABLE employees (
    id INT,
    name VARCHAR(255),
    department VARCHAR(255),
    INDEX idx_department (department)
);

-- 在已存在的表上添加唯一索引
CREATE UNIQUE INDEX idx_unique_name ON employees (name);
-- 前缀索引
CREATE INDEX index_name ON table_name (column_name(length));
-- 联合索引
CREATE INDEX index_product_no_name ON product(product_no, name);

索引覆盖

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

最左前缀原则

使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效，这样就无法利用到索引快速查询的特性了。

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

where b=2；
where c=3；
where b=2 and c=3；

上面这些查询条件之所以会失效，是因为(a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。

索引下推

对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表的次数

什么时候需要/不需要索引

索引是数据集合的目录

索引的最大好处是提高查询的速度，但索引也是有缺点的，比如：

需要占用物理空间，数量越大，占用空间越大
创建索引和维护索引选哟耗费时间，这种时间随数据量的增大而增大
会影响数据表的增删改，因为每次增删改都需要维护B+ 树的有序性，这需要动态的维护

适合索引的场景

字段唯一
经常用于 where 查询条件的字段
经常用于group by order by 的字段，这样查询的时候就可以直接利用 B+ tree 内部字段的有序性的特点

不适合实用索引的场景

使用不到 where order by group by 的场景
字段中存在大量的重复
数据量不够的场景，没必要去，费空间
经常用于更新的字段，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

什么时候发生索引失效

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

mysql为什么有时候会选错索引

优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。

当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

那么，MySQL是怎样得到索引的基数的呢？这里，我给你简单介绍一下MySQL采样统计的方法。

为什么要采样统计呢？因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。

采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。

在MySQL中，有两种存储索引统计的方式，可以通过设置参数innodb_stats_persistent的值来选择：

设置为on的时候，表示统计信息会持久化存储。这时，默认的N是20，M是10。
设置为off的时候，表示统计信息只存储在内存中。这时，默认的N是8，M是16。

由于是采样统计，所以不管N是20还是8，这个基数都是很容易不准的。

字符串如何添加索引

直接创建完整索引，这样可能比较占用空间；
创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描

如何优化索引

防止索引失效
- 对于前缀索引，字符串不要模糊匹配
- 对于联合索引，满足最左前缀原则
- 对于索引，不要对其进行计算、函数、类型转换等操作
- 在where条件中，如果OR前的条件是索引列，而OR后的条件不是索引列，索引会失效。
前缀索引优化
主键索引是自增的
索引不为NULL

锁

全局锁

全局锁(主要用于全库逻辑备份)

1	`flush tables with read lock`

1	`unlock tables`

既然备份数据库数据的时候，使用全局锁会影响业务，那有什么其他方式可以避免？

有的，如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

因为在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。

InnoDB 存储引擎默认的事务隔离级别正是可重复读，因此可以采用这种方式来备份数据库。

但是，对于 MyISAM 这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法。

表锁

//表级别的共享锁，也就是读锁；
lock tables t_student read;

//表级别的独占锁，也就是写锁；
lock tables t_stuent write;

行锁

1
2
3

select ... lock in share mode; # 对读取的记录加共享锁 S型锁
 
select ... for update; #对读取的记录加独占锁 X锁

日志

为什么要两阶段提交

优化

主从分离

主从复制的过程

mysql的主从复制依赖于 binglog，也就是记录在mysql上的所有变化会以二进制的格式存储在磁盘上。而mysql的主从复制就是将主节点上的binlog传输到从节点上。

首先从库在连接到主节点时会创建一个 IO 线程，用以请求主库更新的 binlog，并且把接收到的 binlog 信息写入一个叫做 relay log 的日志文件中
而主库也会创建一个 log dump 线程来发送 binlog 给从库；
同时，从库还会创建一个 SQL 线程读取 relay log 中的内容，并且在从库中做回放，最终实现主从的一致性。这是一种比较常见的主从复制方式。

随着从库数量增加，从库连接上来的 IO 线程比较多，主库也需要创建同样多的 log dump 线程来处理复制的请求，对于主库资源消耗比较高，同时受限于主库的网络带宽，所以在实际使用中，一般一个主库最多挂 3～5 个从库。

问题

主从之间有数据的延迟。

假设一种场景，数据写入主库后，有异步的场景去读取相应的新数据，此时读从库可能发现数据还没同步过来。

方案

数据冗余：异步的场景直接把所有需要的数据发过去，不去从库读数据了
使用缓存：直接把数据写到Memcache中，既可以保证数据一致性，还可以读到对应的数据
查询主库

分库分表

两个维度：垂直和水平

经历过分库分表后的系统，才能够突破单机的容量和请求量的瓶颈

水平分库分表的策略

按范围分片（Range Sharding）：
- 根据数据的某个字段的值范围进行分片，例如按照用户ID范围将数据分片。
- 优点：实现简单。
- 缺点：数据容易倾斜，导致某些分片的负载过高。
按哈希分片（Hash Sharding）：
- 根据数据的某个字段的哈希值进行分片，例如按照用户ID的哈希值将数据分片。
- 优点：数据分布较均匀。
- 缺点：增加节点时需要重新分片，数据迁移成本较高。

垂直分库分表

垂直分库分表（Vertical Partitioning）是将不同的表或不同字段的数据分散到不同的数据库实例中。目的是将逻辑上相关的数据放在一起，从而减小单个数据库的表的数量和大小，提高查询性能。

垂直分库分表的策略

按业务模块分库：
- 不同的业务模块放在不同的数据库中，例如用户信息放在一个数据库中，订单信息放在另一个数据库中。
- 优点：模块化清晰，不同业务的数据库负载相互独立。
- 缺点：跨库操作复杂。
按表分库：
- 将不同的表放在不同的数据库中，例如将大表分离到独立的数据库中。
- 优点：减少单个数据库的负载，提高查询性能。
- 缺点：管理多个数据库实例较复杂。

#mysql,总结,八股

MySQL总结

http://example.com/2023/11/26/MySQL总结/

作者

Forrest

发布于

2023年11月26日

许可协议

web协议详解与抓包实战课程笔记上一篇

linux指令收集下一篇