数据库引擎和数据写入流程

红色那个应该是指优化器吧。其中存储引擎主要指innodb这个是默认的。存储引擎主要是通过执行器对数据库内存和存储的数据进行读取和写入。主要的读取和写入发生在buffer pool，为了支持回滚旧数据被放在磁盘的undo Log；为了防止buffer pool的数据断电挂起之类的丢失，内存中还有块Redo Log Buffer，会将信息刷到磁盘的Redo Log，这个过程是经过或者同时放在操作系统page cache的，然后要频繁刷盘。

有意思：innodb在用io线程写入磁盘文件时，采用系统调用的open+write函数，但是自己提供了一套完整的缓存架构，在调用open函数获取fd文件描述符时，使用O-direct方式跳过了内核提供的缓存区page cache

为了使一些操作失误可以恢复，BinLog日志模块和InnoDB没有关系，用于变更历史查询、数据库备份和恢复、主从复制。

存储结构

frm体积较小。独立表空间由16KB的页组成，多个相关的页被一直访问可以组成一个区防止磁盘读取时访问过于磁头移动的耗时。32个页一个区，256个区一个组（256MB），与区和区组不同还有一个逻辑概念段，不对应表空间中的连续物理区域。用于区分不同功能的区和在碎片区中的页，分为叶子节点段和非叶子节点段，和B+属索引的叶子、非叶子相对应。

执行原理

其中查询缓存已经被舍弃了,命中率低会引入缓存查询开销还有添加缓存的开销。

执行1条select语句，发生了什么？

• 连接器：跟客户端建立连接、获取权限、维持和管理连接。
• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL8.0已删除该模块； 因为发现缓存区是个包袱，不命中次数多增加了查找次数。
• 解析SQL，通过解析器对SQL查询语句进行词法分析、语法分析，然后构建语法树。
• 执行SQL：执行SQL共有三个阶段：
  • 预处理阶段：检查表或字段是否存在；将select*中的符号扩展为表上的所有列。
  • 优化阶段：基于查询成本的考虑，选择查询成本最小的执行计划；
  • 执行阶段：根据执行计划执行SQL查询语句，从存储引擎读取记录，返回给客户端；

在存储引擎中会将命令拆分为微事务Mini Transaction（MTR），对于底层页来说每一个MTR都是原子访问，

索引

mysql聚簇和非聚簇索引的区别：

都是B+树的数据结构

• 聚簇索引：将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的
• 非聚簇索引：叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置再取磁盘查找数据，这个就有点类似一本树的目录，比如我们要找第三章第一节，那我们先在这个目录里面找，找到对应的页码后再去对应的页码看文章。

更贴近于数据结构的方式：

表里面的B+树竟然两三层是最合适的，查询底层节点的磁盘 I/O次数会更少。

explan加到sql语句之前，会显示出使用的哪种索引方式，比如key在树上就是聚簇索引，没在走全表扫描（比如查询条件是 WHERE name = 'Alice'，但主键索引是按 id 组织的，与 name 完全无关）。

默认情况下，数据库不会自动创建所有索引：

• 主键索引（Primary Key）：
  如果表定义了主键（如 PRIMARY KEY (id)），数据库会自动创建主键索引（聚簇索引）。这是唯一由数据库自动创建的索引类型（在大多数数据库中，如 MySQL/InnoDB）。
• 唯一约束（Unique Key）：
  如果字段被标记为 UNIQUE，数据库会自动创建唯一索引来保证唯一性。
• 其他普通索引：
  不会自动创建，需要开发者手动建立。

常见需要手动创建的索引类型:

1. 普通索引（加速查询）：

   CREATE INDEX idx_name ON users(name);  -- 为 name 列创建索引

2. 联合索引（优化多列查询）：

   CREATE INDEX idx_name_age ON users(name, age);  -- 同时查询 name 和 age 时使用

3. 覆盖索引（避免回表）：

   CREATE INDEX idx_covering ON users(name, age, email); 
   -- 若查询只需这3列，无需回表查主键

事务体系ACID

Atomicity Consistency Isolation Durability
一致性、隔离性、持久性、原子性

• 原子性的问题就是保证事务操作只能全部成功，部分失败都得撤回，由undo log机制保证。一条数据改动对应一条undo log通常。

• 在多个事务同时执行时会有三大明显问题：1. A事务读到了B事务还没提交的数据，B数据说不定会回滚，称之为脏读；2. A事务执行过程中对同一条数据进行两次读取，在这两次读取之间B事务修改了这个数据并进行了完整提交，于是A事务两次读取属于不可重复读； 3. A也是两次读取，在两次读取间B事务在集合中增加或者删除了部分数据，A事务读到了不一致的行数据，称之为幻读。

四种隔离级别解决上述三种问题：

读未提交：读取数据时MVCC读取版本链中的最新版本；更新数据加共享行锁

读已提交：MVCC快照读，更新数据使用独占行锁

可重复读：MVCC快照读，next-key行锁

串行利用的表锁

• 持久性的问题：刷盘时由redo log保证内存中数据不丢失；操作系统4KB，数据库16KB，为了保证每一个磁盘中数据是完整的，建立双写缓冲区。

MVCC

和undo log中的版本链有关，但是不能解决幻读问题。在查询的时候创建readview快照信息，根据事务ID决定怎么去找Undo Log中的数据，是不是事务保存之前保存下来的。这里的ID是行中的隐藏字段。

数据库锁原理

Mysql锁有哪些，如何理解
按锁粒度分类：

1. 行锁：锁某行数据，锁粒度最小，并发度高

2. 表锁：锁整张表，锁粒度最大，并发度低

3. 间隙锁：锁的是一个区间

   还可以分为：

   4. 共享锁：也就是读锁，一个事务给某行数据加了读锁，其他事务也可以读，但是不能写
   5. 排它锁：也就是写锁，一个事务给某行数据加了写锁，其他事务不能读，也不能写
      还可以分为：
      1. 乐观锁：并不会真正的去锁某行记录，而是通过一个版本号来实现的
      2. 悲观锁：上面所的行锁、表锁等都是悲观锁

• 排它锁也叫独占锁、写锁或X锁，其他事务无法对已锁定的数据进行查询或修改。当我们对数据进行更新的时候，也就是INSERT、DELETE或者UPDATE的时候，数据库也会自动使用排它锁，防止其他事务对该数据行进行操作。
• 共享锁也叫读锁或S锁，在进行SELECT的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改。
• RecordLock记录锁，仅仅把一条记录锁上，记录锁分为排他锁和共享锁。
• GapLock间隙锁，锁定一个范围，但是不包含记录本身。间隙锁之间是兼容的，两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系。
• Next-Key Lock临键锁：Record Lock+Gap Lock的组合，锁定一个范围，并且锁定记录本身。next-keylock即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。