四大特性

Atomicity 事务是不可分割的，只会整体的成功或者失败
Consistency 事务前后数据库中数据的总量是不变的
Isolation 并发事务之间是互不影响的
Durability 事务提交后对数据库的影响是永久的

并发事务可能存在的问题

脏读

并发事务可以读取到其他并发事务没有提交的数据修改

不可重复读

并发事务在多次读取中读到的结果因为其他并发事务对数据的修改而不一样

幻读

并发事务读取不到对应数据，在插入该数据时报错，该数据幻觉存在（其他并发事务在某一个位置插入数据后提交事务）

数据库结构

连接层

连接池的管理

服务层
- sql 接口，接收sql命令，返回结果
- 解析器**，解析sql语法，生成语法树，进行查询优化，重写查询**
- 查询优化器**，选择解析器中sql，制定查询计划，决定用什么索引**
- 缓存如果判断为可以走缓存则sql不会执行，8版本已经被移除
存储引擎层
- memory
- myasm
- innodb
磁盘数据层
- redo undo bing error slow logs 日志文件
- 数据

存储引擎

主要三类

InnoDB
Myaism
Memory

InnoDB 和 Myaism 的主要区别

InnoDB 支持外键，行级锁和事务，提供了更高的写并发和数据一致性保证
Myaism 的读性能更强，但写性能因为表锁所以比较弱
Myasim 的存储单元最小是表，存储空间利用较低

InnoDB 存储逻辑结构

行，然后是页（16KB），接着是区（1MB），区组段，段组成表空间（.ibd文件）

段分为数据段叶子节点，索引段非叶子节点和回滚日志段存储 undo log

最小的存储单元是行

Memory 的特点

数据存储在内存当中，仅仅支持 hash 索引

Redis 相对于 Memory 存储引擎的优势

数据结构丰富
Redis 可以持久化
Redis 速度比 Memory 更快
Redis 可以进行集群搭建和哨兵搭建

索引

优势和劣势

快速的搜索到对应数据，降低 IO 消耗
可以按照索引进行排序，降低 CPU 的消耗
索引的创建需要额外磁盘空间
在数据的增删改时索引需要修改

索引类型

B+ 树索引
Hash 索引
R 树索引
Full-Text 索引

B+ 树的索引数据结构

5 阶 B+ 树，每一个节点有 4 个 key，有 5 个子节点

一般节点的只存储子节点的指针数据和索引，不存储字段或者行数据

叶子节点包含一个行所有的索引数据，顺序排列，形成双向链表

InnoDB 自适应 Hash 索引

InnoDB 在构建 B+ 树索引时，会在一定情况下构建出 Hash 索引

B+树的优势

相对于红黑树或者二叉平衡树，B+ 树的层数更少，能减少大量的 IO 次数
相对于 B 树，B+ 树在非叶子节点上存储的信息是索引键值和指向子节点的指针，并不存储行数据或者单个字段数据，一个 B+ 树和 B 树的节点在逻辑上对应 MySQL InnoDB 引擎存储逻辑单元中的页，而 MySQL 中的存储逻辑单元中的页就是操作系统中操作磁盘的常用单位，在一次磁盘 IO 中读出的页，B+ 树可以包含更多的子节点指针，这一点上导致 B+ 树的层数比 B 树更低，读取相同数量的节点时 B+ 树的 IO 次数更少
B+ 树的叶子节点存储所有的数据和键值，在 IO 次数固定，查询稳定
所有的叶子节点按照顺序形成双向链表，有利于范围查询和排序查询

InnDB 存储引擎中 B+ 树的索引

聚集索引
- 叶子节点存储的是一整行的数据和主键字段数据
非聚集索引
- 叶子节点有相应的字段数据和该表的包含这个字段的这一行的主键数据

聚集索引 B+ 树的大概高度

假设主键选择 bigint 格式，MySQL 中一个节点指针占用 6 byte
一个节点直接占用空间 16KB，那么一个节点的字节点个数可以有 n 个 (n + 1) * 6 + n * 8 = 16 * 1024
每增加一层，节点个数乘以 n

explain 字段

id 越大，表越先执行

type

查询类型

null system const eq_ref ref range index all

possible_keys

可能用到的索引

实际用到的索引

key_len

索引的长度

查询扫描到的行数

filtered

真正返回的行数

extra

额外数据

最左前缀法则

联合索引中左边的列存在为查询条件时右边的列才可能使用索引

范围查询时使用 > < 字段后其右边的字段不会在使用索引，使用 >= =< 字段后其右边的字段还会使用索引

索引失效的情况

索引字段使用函数
字段类型为 string 的索引在查询条件中没有使用 '' 单引号包裹
模糊查询时模糊匹配索引字段头部
or 两侧的条件有一边没有索引，整个 or 都不会使用索引
全表扫描快于使用索引

SQL 优化

insert

批量插入大量数据
大事务手动提交
主键递增顺序插入

主键

尽量选择递增的列作为主键
主键尽量设计短些
插入数据尽量顺序插入
尽量不要修改主键列

order by

排序尽量使用索引
排序索引有升序和降序的区别
排序索引出现回表时会使用 filesort
大数据量的 filesort 需要提高排序缓冲区大小

group by

分组的字段需要建立联合索引
分组在 where 条件之后，where 中的字段也可以结合 group by 字段组成联合索引

limit

覆盖索引 (order by id) 减少回表
子查询

▼
SQL
复制代码
explain
select q.*
from question q,
     (select id from question order by id limit 1000, 10) tmp
where q.id = tmp.id;

update

在更新记录时应该使用索引条件
避免无法加索引行锁而去加整张表的表锁

锁

全局锁

flush tables with read lock 加上全局读锁，此时只能进行读操作，不能进行写操作

表级锁

表锁

共享读锁
独占写锁

lock tables --- read/write

加读锁所有联接可以读，当前联接的写会失败，其他联接的写会阻塞

加写锁当前联接可以读和写，其他联接不可以读和写

元数据锁

避免 DML 和 DDL 的冲突，保证数据一致性

当进行加表锁，读取，插入修改删除时会自动加上元数据共享的读锁或者元数据共享写锁，这两种锁不冲突，但是这两种锁与元数据排他锁冲突

当进行表结构修改时会加上元数据排他锁

意向锁

为解决行锁与表锁冲突问题: 加表锁前需要确认有没有相关的行锁在表里面

意向锁分为意向共享锁和意向排他锁，意向共享锁与表读锁不互斥但与表写锁互斥，意向排他锁与表读锁和表写锁均互斥

行级锁

锁粒度小，并发度高

InnoDB 存储引擎中的数据是基于 B+ 树的叶子节点来组织的，行级锁的操作对象为索引项

行锁

分为行共享锁和行排他锁，共享锁之间不互斥，共享和排他锁间互斥

间隙锁

在 RR 隔离级别下支持，对相邻的两个主键节点间的间隙加锁，避免其他事务对中间的值进行 insert，预防幻读

临键锁

行锁和间隙锁的组合

InnoDB 存储引擎的内存结构

Buffer Pool

数据以页的形式 IO 读取到这个缓冲区，一次性读取 4-5 个页，这其中的数据会被在一定频率下顺序刷新到磁盘

页分为未使用页，使用未修改页和修改页

这种数据存储组织格式减少了大量的非连续 IO，间接减少 IO 次数，在内存中的操作也提高了处理的速度

Change Buffer

在使用非唯一的二级索引修改数据时，相应的页不会被 IO 读取进入 Buffer Pool，而是直接将修改逻辑保存在 Change Buffer 中，在将 buffer Pool 中的数据刷新回磁盘时会顺带将 Change Buffer 的修改逻辑带上

Log Buffer

数据的增删改逻辑会被写入 Log Buffer 中，其中的数据默认会在每次事务提交时被刷新进入磁盘中的 redo log 和 undo log 日志

Adaptive Hash Index

InnoDB 会判断哪些操作字段可以加上 Hash 来提升效率

事务原理

持久性 Durability

运用 redo log 保证，当事务提交时 Log Buffer 默认会刷新到磁盘，其中就包含这次事务对数据的操作，这些操作会被刷新到 Redo Log 日志文件中，用于确保 Buffer Pool 中的脏页在刷新到磁盘出错后可以任然从 Redo Log 中恢复修改后的数据 write ahead logging

日志的追加写性能优于脏页的随机写

原子性

undo log 记录了事务每一步操作的反向操作，当事务操作失败进行回滚时会参照 undo log 的记录进行回滚

一次事务中被记录的 insert 和 delete 操作在事务提交后会被从 undo log 日志中删除，update 操作要配合 MVCC 使用，事务提交后不会立即删除

在进行相关的 insert，delete 和 update 前会先将操作写入 undo log 日志

MVCC

当前读

一次事务中一些被加上了共享读锁或者排他写锁的读取操作，此时的并发事务都不能对这些当前读数据进行修改

快照读

简单的不加锁的 select 就是快照读，读取到的是数据的可见的版本，可能不是最新的数据

在 RC 情况下每次 select 都是快照读

在 RR 情况下第一次读取是快照读，之后的读取都和这次快照读的数据一致，这种读取是表级别的读取

三个隐藏字段

可能的 DB_ROW_ID （在不显示声明主键并且没有一个唯一索引时存在）
DB_TRX_ID 最后一次修改这条记录的事务 ID
DB_ROLL_PTR 回滚指针，结合 undo log 使用

▼
JSON
复制代码
ibd2sdi user.ibd

"columns": [         
            {
                "name": "id"
            },           
            {
                "name": "DB_TRX_ID"
            },
            {
                "name": "DB_ROLL_PTR"
            }
]

undo log 日志