MySQL基本架构

flowchart TD 客户端 --> 连接器 subgraph 服务端连接器 --> 查询缓存 & 分析器查询缓存 --> 分析器分析器 --> 优化器 --> 执行器 end subgraph 存储引擎 InnoDB MyISAM Memory end 执行器 --> 存储引擎

客户端
- 处理用户交互
连接器
- 建立连接
- 权限认证
- 管理连接
- 最佳实践
  - 建立连接的过程比较复杂，尽量使用长连接
  - mysql执行过程中使用的内存是管理在连接对象里，大量的长连接会容易导致OOM
    - 定期断开连接
    - 使用mysql_reset_connection参数（5.7版本之后）
查询缓存
- 缓存以key-value形式存储，key为查询语句，value为查询结果
- 只要表有更新，缓存就会被清空。所以更新频繁的表缓存命中率很低
- 可通过参数设置是否需要开启（8.0版本删除了该功能）
分析器
- 词法分析
  - 判断字符串内容
- 语法分析
  - 判断sql逻辑是否正常
优化器
- 索引的选择
- 表的执行顺序
执行器
- 需要判断有没有查询权限
- 根据表的引擎定义去调用对应引擎的接口
- 没有索引会逐行判断，有索引则根据索引逐行判断

日志系统

binlog & redo log

flowchart TD subgraph 服务端 binlog end subgraph 存储引擎 direction LR InnoDB -.-> redolog end 服务端 --> 存储引擎

graph TD A["引擎基于B+树找数据"] --> B{"数据在内存中?"} B --是--> C["直接返回数据"] B --否--> D["从磁盘读到内存"] D --> C C --> E["执行器获取数据"] E --> F["执行器c+1"] F --> G["调用引擎写入数据"] G --> H["引擎更新内存数据"] H --> I["引擎写redo log(prepare)"] I --> J["通知执行器提交事务"] J --> K["执行器写binlog"] K --> L["通知引擎提交事务"] L --> M["引擎修改redo log(commit)"]

一条update语句的更新流程： update table_name set c=c+1 where id=2;
- 执行器让存储引擎找id为2的这行数据
- 引擎基于树结构快速找到这行数据。如果在内存中存在，则直接返回；不存在，则先从磁盘读到内存，然后再返回
  - 涉及知识点
    - B+树
- 执行器拿到这行数据，给c+1，然后调用引擎写入数据
- 引擎将数据更新到内存中，然后将操作记录更新到redo log，状态为prepare，通知执行器可以提交事务
- 执行器将操作记录更新到binlog（磁盘），通知引擎提交事务
- 引擎修改redo log状态为commit

binlog使用

数据恢复

找到数据删除前最近一次全量备份，导入一个临时库中
从生产库中取出binlog（从备份时间点到需要恢复的那个时间点）
将那段binlog导入临时库中恢复数据

数据库主备同步

全量备份+binlog

事务

ACID性质

MySQL支持事务处理，并且其事务处理遵循ACID特性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么全部完成，要么全部不完成。这意味着如果在一个事务中的某个操作失败，那么整个事务都会失败，并且数据库会回滚到事务开始前的状态。

一致性（Consistency）

一致性是指事务必须使数据库从一个一致性状态转变到另一个一致性状态。在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的任何数据都必须满足所有设置的约束，包括数据约束、级联更新、触发器等。

隔离性（Isolation）

隔离性是指多个事务并发执行时，一个事务的操作不应影响其他事务。即事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不会互相干扰。隔离性通过锁机制、时间戳、多版本并发控制（MVCC）等技术实现。

持久性（Durability）

持久性是指一旦事务提交，则其结果能够持久保存在数据库中。即使系统崩溃、重启或发生故障，数据库还能恢复到事务成功结束时的状态。这通常通过数据库的日志机制实现，确保在系统故障时能够恢复数据。

ACID特性是数据库事务处理的基础，它保证了事务的正确性和可靠性。在MySQL中，InnoDB存储引擎支持事务处理，并且遵循ACID特性。

隔离级别

实例理解事务的4种隔离级别

读未提交
- v1,v2,v3均为2
- 直接返回最新值
读已提交
- v1为1，v2，v3为2
- 基于视图隔离，在sql语句执行的时候创建
可重复读
- v1，v2为1，v3为2
- 基于视图隔离，在事务启动的时候创建
串行化
- v1，v2为1，v3为2
- 通过加锁实现

实践经验

怎样设置mysql的事务隔离级别？

在MySQL中，可以通过设置系统变量来更改事务的隔离级别。以下是如何设置MySQL的事务隔离级别的步骤：

查看当前隔离级别：首先，你可以查看当前的隔离级别设置，通过执行以下SQL语句：
```
SHOW VARIABLES LIKE 'transaction_isolation';
```
设置隔离级别：要更改隔离级别，你可以使用SET TRANSACTION语句或者修改MySQL配置文件。
- 使用SET TRANSACTION语句：在会话级别（只对当前连接有效）设置隔离级别，可以使用以下命令：
```
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 读未提交
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- 读已提交
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- 可重复读
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;     -- 串行化
```
- 修改MySQL配置文件：要永久更改隔离级别（对所有新连接有效），你需要在MySQL的配置文件（通常是my.cnf或my.ini）中的[mysqld]部分添加或修改transaction-isolation选项。例如：
```
[mysqld]
transaction-isolation = READ-COMMITTED
```
  修改配置文件后，需要重启MySQL服务以应用更改。
注意事项：
- 不同的存储引擎可能支持不同的事务隔离级别。例如，InnoDB存储引擎支持所有四个隔离级别，而MyISAM存储引擎只支持READ UNCOMMITTED级别。
- 在选择隔离级别时，需要权衡性能、一致性和并发性。较高的隔离级别（如REPEATABLE READ和SERIALIZABLE）提供了更强的一致性保证，但可能降低并发性能。
- 在实际应用中，通常推荐使用REPEATABLE READ（可重复读）隔离级别，因为它在大多数场景下提供了足够的一致性保证，同时具有较好的并发性能。

视图隔离的实现

每一条记录更新的时候同时记录一条回滚操作日志，通过回滚操作能够回到其它事务所看到值
回滚日志在没有事务view的时候才会删除
长事务的风险
- 产生大量的回滚日志
- 占用锁资源，影响整个库
事务使用的最佳实践
- 显示的启用事务: start transaction
- set autocommit=1
- 查询超过60s的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

索引

常见索引模型

Hash表：hash表+链表结构
- 不适用于范围查询
有序数组：二分查询（log(n)）
- 主键必须是有序的
- 适合静态数组
搜索树

InnoDB索引模型

B+树

在InnoDB存储引擎中，主键索引采用的就是B+树结构。每个叶子节点包含了主键的值和对应的数据行。由于叶子节点之间通过链表相连，因此可以非常高效地进行范围查询。非主键索引的叶子节点并不直接包含数据行，而是包含了相应主键的值。当通过辅助索引查询数据时，InnoDB首先找到主键值，然后再通过主键索引找到对应的数据行。B+树的设计使得它能够很好地处理磁盘I/O操作。由于磁盘读写操作相对较慢，因此B+树通过减少磁盘访问次数来提高查询性能。通过将数据分布在多个节点中，B+树可以确保每次磁盘I/O操作都能获取尽可能多的数据，从而减少了磁盘访问次数。

最佳实践

自增主键的优势
- 新增记录不会导致索引表的页分裂或者合并等处理
- 使得非主键索引的叶子节点占用的空间较少
什么场景适合用业务字段做索引？
- 只有一个索引
- 且是唯一索引
什么是覆盖索引
- 不需要回表，非主键索引上有查询返回的数据
- 通过指定主键为查询字段来避免回表
- 通过联合索引来避免回表（这个是怎么实现的？）
最左前缀原则
- 可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符
- 联合索引(A联合索引(A, B)意味着不需要建立A的索引了，因为这个联合索引意味着建立了(A,B)和(A)这两种索引, B)意味着不需要建立A的索引了，因为这个联合索引意味着建立了(A,B)和(A)这两种索引

索引下推，没怎么看懂
- like 'hello%’and age >10 检索，MySQL5.6版本之前，会对匹配的数据进行回表查询。5.6版本后，会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度

目录CONTENT

MySQL笔记