后端学习 - MySQL存储引擎、索引与事务

MONA ODYSSEY

已于 2024-09-10 09:57:50 修改

阅读量1.3k

点赞数 1

分类专栏： Java开发文章标签：后端 java jar

于 2022-03-28 10:51:42 首次发布

本文链接：https://blog.csdn.net/weixin_43785688/article/details/123789542

版权

Java开发专栏收录该内容

19 篇文章 5 订阅

订阅专栏

文章目录

一存储引擎
- 1 MyISAM 与 InnoDB 的差异
- 2 InnoDB 外键约束
二索引
三 MySQL 事务
四设计规范与优化建议
五锁：服务器层与 InnoDB 存储引擎
- 1 表锁：S、X、IS、IX
- 2 行锁：S、X
六 SQL 查询
七 InnoDB 日志

一存储引擎

MySQL 服务器通过 API 与存储引擎交互，接口屏蔽了不同存储引擎之间的差异，对上层是透明的
不同存储引擎之间不会相互通信，只是简单地响应上层服务器的请求
索引在存储引擎层实现

1 MyISAM 与 InnoDB 的差异

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎，之后的默认引擎是 InnoDB
MyISAM 在大量读取操作的场景下性能较好，不支持事务
相比之下，InnoDB 具有 支持事务、支持行级锁、支持外键、支持数据库异常崩溃后的安全恢复（redo log） 的特性

2 InnoDB 外键约束

主表的主键和从表的字段存在对应关系（可以是一对一或一对多），从表的该字段就是外键
- 外键约束主要作用是让表与表之间的数据建立关联，使数据更加完整，关联性更强
- 外键列的数据类型必须和主键列的数据类型相同
创建外键时指定两表修改时的操作【主表影响从表，从表不能影响主表】

属性	作用
`CASCADE`	主表删除或修改记录时，从表也会对关联记录的外键字段进行修改
`ON UPDATE CASCADE`	主表修改记录时，从表关联记录的外键字段也会修改
`ON DELETE CASCADE`	主表删除记录时，从表关联记录的外键字段也会删除
`RESTRICT`	主表删除或修改记录时，子表中若有关联记录，则不允许主表删除或修改
`ON UPDATE RESTRICT`	主表修改记录时，子表中若有关联记录，则不允许主表删除或修改
`ON DELETE RESTRICT`	主表删除记录时，子表中若有关联记录，则不允许主表删除或修改
`SET NULL`	主表删除或修改主表记录时，从表会将关联记录的外键字段设为 null

二索引

索引在存储引擎层实现，而非服务器层（联想索引下推也是在存储引擎层实现的）
使用索引不一定能提升查询性能，在表比较小时，全表扫描的速度可能比使用索引更快
索引的优点
- 减少服务器需要扫描的数据量
- 避免创建排序表和临时表
- 将随机 I/O 变为顺序 I/O
联合索引中，把选择性最高的属性放在前面；把需要范围查询的属性放在后面

1 主键索引与二级索引、索引覆盖、延迟关联

主键索引
- 主键索引存放的数据是表中的一条数据，二级索引存放的数据是主键索引
- 一张数据表有只能有一个主键，并且主键不能为 null，不能重复
- 不发生索引覆盖时，在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据二级索引查找时，则需要先取出主键的值，再使用主索引进行数据查找
- 如果没有主键也没有合适的唯一索引，那么 InnoDB 内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节，类型为长整型的列，该列的值会随着数据的插入自增
二级索引
- 联合索引是二级索引，多个属性建立联合索引只会建立一棵B+树
- 二级索引的内节点，也保存了主键的值，避免相同的二级索引值造成歧义
因此主键索引不建议使用过长的字段，因为二级索引保存了主键索引的值
索引覆盖
- 除非发生索引覆盖的情况，否则使用二级索引会导致回表
- 回表：当查到索引对应的主键后，还需要根据主键再到主键索引查询
- 索引覆盖：需要查询的字段正好是索引的字段，无论主索引或二级索引
```
# 一种索引覆盖的特殊情况：假设在 age 属性建立二级索引，id 属性是主键索引
# 因为二级索引的叶子节点存放了主键索引，所以下面的查询发生索引覆盖
select id from test_table where age = 20;
```
- 索引覆盖直接从内存中的索引读取数据，避免了磁盘 I/O
延迟关联
- 使用索引覆盖查找目标数据行的主键 ID，再和原表根据主键进行关联查询
- 核心思想是在二级索引中，利用查询条件得到主键，再访问主键索引，而非直接扫描数据

# 场景：当偏移量很大时，如 limit 100000, 10
# 取第100001-100010条记录，会取出100010条记录然后将前100000条记录丢弃，这无疑是一种巨大的性能浪费

# 优化前：先访问所有数据，再截取
SELECT * FROM orders LIMIT 10000, 20;

# 优化后：先截取，获得目标数据的主键，再访问数据
SELECT * FROM orders AS o1 
	JOIN (SELECT id FROM orders LIMIT 10000, 20) AS o2  # 内层扫描使用了索引覆盖
ON o1.id = o2.id;

2 聚簇索引与非聚簇索引

聚簇索引指的是，索引和数据存放在一起
- 对于 InnoDB 引擎的表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据
- 一个表只能存在一个聚簇索引，因为无法把数据行存放在两个不同的地方
- 主键索引是聚簇索引，二级索引是非聚簇索引
聚簇索引提高了 I/O 密集型应用的性能，如果数据全部存放在内存中，则访问顺序没那么重要，聚簇索引无法体现优势

在这里插入图片描述

索引类型	优点	缺点
聚簇索引	查询速度快，无需回表	需要依赖有序的数据（因为数据结构是B+树，无序插入会导致页分裂）；更新代价大（如果对索引列的数据被修改时，那么对应的索引也将会被修改）
非聚簇索引	更新代价比聚簇索引小（叶子节点存放的是主键值，数据移动时不需要修改二级索引）	需要依赖有序的数据；可能会回表

3 数据结构

3.1 哈希表

在这里插入图片描述

插入数据时，根据 key 进行哈希运算，得到 bucket 的位置，如果该位置无 value 则插入成功，如果有则发生了哈希冲突，使用拉链法或者红黑树解决（类似 Java 的 HashMap）
哈希索引的缺点
- 只支持全值查询，不能使用部分索引字段，因为根据全部字段才能计算哈希值
- 只支持等值比较查询，包括 =, IN(), <>，不支持范围查询，同样也无法用于 ORDER BY 排序
- 如果哈希函数设计不合理，会频繁出现哈希碰撞，性能降低

自定义哈希索引
适用场景：需要在长字符串 url 上建立索引
使用方法：
1. 选择合适的哈希函数 MyHashFunc
2. 新增一个列 url_hash，用该列存储字符串的哈希值，并在该列上建立索引
3. 设置触发器维护该列，使哈希值随 url 的修改同步更新
4. 执行查询 SELECT id FROM url_table WHERE url="http://www.mysql.com" AND url_hash=MyHashFunc("http://www.mysql.com")，如果发生哈希冲突，仅需比较相同哈希值的行的 url 是否相同

3.2 B树

MongoDB 采用的索引类型，多路平衡查找树
B树的中间节点存放的也是数据；叶节点之间没有连接
B树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了

B树的阶：树的节点最多的孩子结点（子树）数
根节点至少2个子树（一个关键字），非根节点至少有 ⌈M /2⌉ 个子树（⌈M /2⌉ - 1 个关键字）

3.3 B+树

在这里插入图片描述

MyISAM 和 InnoDB 均采用的数据结构，是多路平衡查找树
B+树的中间节点只存放索引，数据全部都在叶节点上，叶节点之间有连接
相对于B树而言，对范围查找的支持更好
查询效率更加稳定，因为每次查询必定会访问到叶节点
叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接
B+树的阶数选取，要尽量让每个节点的大小等于一个页的大小
- 阶数很大时，一个节点的大小会超过一个页的大小，读取这样一个节点会导致多次I/O操作，造成性能下降
- 阶数小时，会导致B+树的高度变高，同样会增加I/O操作次数

3.4 跳表

在 Redis 中的 Zset 使用到的数据结构，在 Java 容器类中也有实现
基于链表，能够实现近似二分查找，查询时间复杂度 O(logn)
为什么在 MySQL 中选择 B+ 树而非跳表作为索引的数据结构
- 如果是查询磁盘文件，B+ 树会比跳表的性能好很多，因为磁盘查询性能比内存差，所以要尽量减少查询的次数
- B+ 树每个节点按页存放数据，每次查询可以查询一批数据到内存中；而且 B+ 树的层数更低，可以减少访问磁盘的次数

3.5 为什么不使用红黑树

MySQL 用 I/O 次数衡量查询效率，磁盘查找存取的次数往往由树的高度所决定
1. 红黑树是内排序使用的数据结构，B+ 树常用于外排序
2. 红黑树的高度通常更高（因为是二叉树），需要的磁盘 I/O 次数更多，效率低；B+树可以有多个子节点，树的高度通常为1~3层，效率高
3. B+树的一个节点对应一个页，如果使用红黑树的话每个节点（页）存放一条数据，造成很大的浪费

3.6 为什么不使用B树**

B树执行范围查询时只能按照类似于中序遍历的方式进行，效率不如B+树在叶节点的顺序访问
存储相同的数据规模，B+树比B树更宽、更矮：B+树由于只在叶子节点存储数据，使得非叶子节点能够存储更多的关键字（即索引），从而在相同磁盘页大小下，B+树能够拥有更多的分支，降低树的深度，磁盘 I/O 次数更少

4 索引下推 **

参考链接

一句话总结：在部分列索引失效的情况下，存储引擎层依然按照指定的索引字段过滤（如果不使用索引下推，违反最佳左前缀原则的字段则不生效），对过滤后的结果逐行回表查找，然后再返回给 Server 层
索引下推的目的是减少回表次数，即减少 I/O 操作
对 InnoDB 存储引擎来说，索引下推 只适用于二级索引，因为对于 InnoDB 的主索引（聚簇索引）来说，完整的行记录已经加载到缓存区了，无需再执行 I/O 操作，索引下推也就失去意义
在使用 ICP 的情况下，如果存在某些被索引的列的判断条件时（即使这些列无法使用索引），Server 层将这一部分判断条件传递给存储引擎，然后由存储引擎判断索引是否符合服务器传递的条件，只有当 索引符合条件时才会将数据检索出来返回给服务器

举例：

假设在 (name, city) 建立了联合索引，查询条件为 name = 'LiSi' and city like '%z%' and age > 25
不使用索引下推 回表4次：
1. 存储引擎根据 (name, city) 联合索引，找到 name = 'LiSi' 的记录，共4条记录
2. 因为 city 的模糊查询违反了最佳左前缀原则，所以只能根据 name 过滤的这4条记录中的 id 值，逐一进行回表扫描（索引只用到了 name 列，而没有用 city 列），去聚簇索引中取出完整的行记录，并把这些记录返回给 Server 层
3. Server 层接收到这些记录，并按条件 name="LiSi" and city like "%Z%" and age > 25 进行过滤，最终留下 ("LiSi", "ZhengZhou", 30) 这条记录
使用索引下推 回表2次：
1. 存储引擎根据 (name, city) 联合索引，找到 name='LiSi' 的记录，共4条
2. 由于联合索引中包含 city 列，存储引擎直接在联合索引中按 city like "%Z%" 进行过滤（即使它已经无法使用索引），过滤后剩下2条记录
3. 根据过滤后的记录的 id 值，逐一进行回表扫描，去聚簇索引中取出完整的行记录，并把这些记录返回给 Server 层；
4. Server 层根据 WHERE 语句的其它条件 age > 25，再次对行记录进行筛选，最终只留下 ("LiSi", "ZhengZhou", 30) 这条记录

在这里插入图片描述

5 索引失效（索引不命中）的情况

违背最佳左前缀法则，跳过了联合索引的前缀列
- 对于列 (age, class, name) 建立了联合索引，如果查询条件是 class=5 and name='zy' 等违背左前缀，则无法使用该索引
- 优化器可以决定 where 后面条件的顺序，所以 class=5 and name='zy' and age=10 可以使用索引
计算、函数、类型转换导致索引失效（索引列作为表达式的一部分）
- WHERE name=123 不能使用索引，因为发生了数据类型转换
- WHERE name='123' 可以使用索引
IS NULL 可以使用索引，IS NOT NULL 不能使用索引；等于 = 可以使用索引，不等于 <> 不能使用索引
范围条件右边（指的是定义联合索引的右，而非 where 条件中的右）的列索引失效
对于列 (age, class, name) 建立了联合索引，WHERE student.age=30 AND student.name = 'abc' AND student.classId>20 会导致 name 索引失效
OR 前后存在非索引的列，索引失效：因为对于非索引的条件需要全表扫描，该情况下直接放弃使用索引而执行全表扫描
对于模糊查询，通配符（%）在搜寻词首出现，一般会导致不使用索引，"%ABC"不能使用，但"A%BC"可以使用（类似情况4）

6 前缀索引和索引选择性

为类型 TEXT/BLOB/长的VARCHAR 添加索引时，不允许索引列的完整长度，需要使用自定义哈希索引或前缀索引
前缀索引需要选择合适的前缀长度，使前缀选择性接近于完整列的选择性
显然前缀索引无法用于索引覆盖，因为索引中不包含完整的列

# 例如对于 TEXT 类型的字段 city
# 1.计算完整的列的选择性
SELECT COUNT(DISTINCT city) / COUNT(*) FROM demo;

# 2.计算前5个字符的选择性
SELECT COUNT(DISTINCT LEFT(city, 5)) / COUNT(*) FROM demo;

# 添加前缀索引
ALTER TABLE demo ADD KEY (city(5));

7 索引用于 ORDER BY

索引的列顺序和 ORDER BY 子句的顺序完全一致，且排序方向相同时，才能使用索引对结果排序
如果是关联查询，只有 ORDER BY 子句引用的字段完全属于第一个表的时候，才能用索引排序
ORDER BY 子句需要满足最佳左前缀的要求，除非前导列是常量
前缀索引无法用于 ORDER BY / GROUP BY

# 例如对于联合索引(rental_date, inventory_id, customer_id)

# 正确示例1
... WHERE rental_date = '2022-12-24'  # 索引的第一列被指定为常数，该索引可以用于 ORDER BY 排序
ORDER BY inventory_id, customer_id
# 正确示例2
... WHERE rental_date > '2022-12-24'
ORDER BY rental_date, inventory_id

# 错误示例1：排序方向不同
... WHERE rental_date = '2022-12-24'
ORDER BY inventory_id ASC, customer_id DESC
# 错误示例2：字段不在索引中
... WHERE rental_date = '2022-12-24'
ORDER BY inventory_id
# 错误示例3：违反最佳左前缀
... WHERE rental_date = '2022-12-24'
ORDER BY customer_id
# 错误示例4：第一列是范围条件，违反最佳左前缀
... WHERE rental_date > '2022-12-24'
ORDER BY inventory_id
# 错误示例5：多个等于条件，对排序而言是范围查询
... WHERE rental_date = '2022-12-24'
AND inventory_id IN(1, 2)
ORDER BY customer_id

三 MySQL 事务

1 ACID 及其保证手段

原子性：事务是最小的执行单位，不允许分割（事务内的一系列操作，要么全都做，要么全不做）
一致性：事务执行前后，数据库的一致性保持不变
隔离性：并发进行的事务之间互不影响
持久性：事务提交后，对数据库中数据的修改是永久的
InnoDB 引擎如何保证 ACID
- 使用 redo log + bin log 保证事务的持久性
- 使用 undo log 来保证事务的原子性
- 锁机制、MVCC 等手段来保证事务的隔离性（默认隔离级别是可重复读）
- 保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障

2 事务的隔离级别

隔离级别&出现的异常	脏读（读到未提交数据）	不可重复读（两次读取同一数据结果不同）	幻读（两次查询结果数目不同）
读未提交	√	√	√
读提交	×	√	√
可重复读	×	×	√
串行化	×	×	×

可重复读的隔离级别下，使用 MVCC + Next-key Lock 也可以避免幻读

3 多版本并发控制 MVCC

MVCC 处理的是 读数据 的问题，避免读加锁，写数据必须依靠加锁
普通的 SELECT 语句在 读提交 和 可重复读 隔离级别下会使用到 MVCC，主要针对的也是这两种隔离级别（因为这两种隔离级别要求读到的是 已经提交了的 事务修改过的记录）
另外两种隔离级别不适用 MVCC
- 可串行化对读取的每一行记录加锁
- 读未提交总是读取最新的数据，无需同步
MVCC 的读指的是 快照读（读取的是快照数据） , 而非当前读（当前读读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁）
- 普通的 SELECT 操作是快照读
- SELECT ... FOR UPDATE 和 SELECT ... IN SHARE MODE 是当前读

3.1 MVCC 实现

其实现主要依赖于三个数据结构： 记录的隐藏字段 + UndoLog + ReadView
读提交和可重复读关于 ReadView 的区别是

	读提交	可重复读
快照读	每次查询时创建 ReadView	事务开始时创建 ReadView
当前读	加锁	加锁

ReadView 包含主要结构

creator_trx_id	创建这个 ReadView 的事务 ID（只读事务为0，修改记录的事务会被分配 ID）
trx_ids	生成 ReadView 时，活跃的读写事务的事务 ID 列表
up_limit_id	活跃的事务中最小的事务 ID
low_limit_id	生成 ReadView 时，系统中应该分配给下一个事务的 ID 值

注意：low_limit_id 并不是 trx_ids 中的最大值，事务id是递增分配的。比如，现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成 ReadView 时，trx_ids就包括1和2，up_limit_id的值就是1，low_limit_id的值就是4

ReadView 规则：
1. 如果被访问版本的 trx_id 属性值与 ReadView 中的 creator_trx_id 值相同，说明该记录的修改是由当前事务创建的，允许访问
2. 如果被访问版本的 trx_id 属性值小于 ReadView 中的 up_limit_id 值，表明生成该版本的事务在当前事务生成 ReadView 之前已经提交，允许访问
3. 如果被访问版本的 trx_id 属性值大于或等于 ReadView 中的 low_limit_id 值，表明生成该版本的事务在当前事务生成 ReadView 后才开启，不允许访问
4. 如果被访问版本的 trx_id 属性值在 ReadView 的 up_limit_id 和 low_limit_id 之间，那就需要判断一下 trx_id 属性值是不是在 trx_ids 列表中：如果在，说明生成该版本的事务，在当前事务开启时尚未提交，不允许访问；反之说明生成该版本的事务，在当前事务开启时已经提交，允许访问
隐藏字段：存放生成当前记录的事务 ID
UndoLog：存放当前记录的历史版本

3.2 MVCC 执行流程

首先获取事务自己的版本号，也就是事务 ID；
获取 ReadView；
查询得到的数据，然后与 ReadView 中的事务版本号进行比较；
如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；
最后返回符合规则的数据，如果无符合规则的数据则返回空

3.3 RR 等级下使用 MVCC 防止幻读

幻读：某个事务读取某个范围内的记录时，另外一个事务在该范围内插入了新的记录，导致当前事务再次读取该范围的记录时产生幻行
针对两种不同的查询操作
- 快照读：可重复读只会在事务开启后的第一次查询生成 Read View ，并使用至事务提交。所以在生成 ReadView 之后其它事务所做的更新、插入记录版本对当前事务并不可见，防止快照读下的幻读
- 当前读：InnoDB 使用 Next-key Lock 来防止这种情况。当执行当前读时，会锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据

四设计规范与优化建议

1 索引、数据表设计

让主键具有 AUTO_INCREMENT ，通过存储引擎自己生成主键，让插入的记录的主键值依次递增，避免页面分裂带来的性能损耗

页分裂的负面影响

插入之前需要从磁盘中找到目标页，并读取到内存中，产生大量随机 I/O
导致大量的数据移动，一次插入至少需要修改三个页
页变得稀疏，产生碎片

字段的数据类型定义准确
设计数据表时适当遵循范式规则 1/2/3NF
- 1NF：数据表的每个字段不可拆分
- 2NF：数据表中非主属性完全依赖于主属性（针对多字段主键而言，必须依赖主键的所有而非部分字段）
- 3NF：数据表中的非主属性不传递依赖于主属性
避免对经常更新的表建立过多的索引
数据库和表的字符集统一使用 utf8mb4
表属性尽量设置为 NOT NULL，因为 IS NOT NULL 不能使用索引，且 NULL 会带来额外的问题
联合索引中，把选择性最高的属性放在前面；把需要范围查询的属性放在后面
数据量小的表不要建立索引，可能遍历更快
不要在值种类少、分布均匀的列上建立索引（例如性别）

3 连接查询优化

驱动表：无法避免全表扫描的表称为驱动表
- 在决定哪个表做驱动表的时候，两个表按照各自的条件过滤，过滤后计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表
- 为了提高连接查询效率，需要将小表作为驱动表，大表作为被驱动表，减少外层循环的次数
- LEFT JOIN 保证左表的每个记录都会出现，即左表为驱动表，所以右表是关键，一定需要建立索引（右外连接反之）
- INNER JOIN 会自动决定驱动表、被驱动表
- 对于连接属性，如果两表只有一个具有索引，则它作为被驱动表
- 如果两个表都在该属性有索引，小表作为驱动表，大表作为被驱动表
需要JOIN 的字段，数据类型保持绝对一致，避免类型转换导致索引失效

4 分库和分表

切分	定义	应用场景
分库	数据库中的数据分散到不同的数据库上	应用的并发量太大；数据库中的数据占用的空间越来越大，备份时间越来越长
分表	对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分	单表的数据达到千万级别以上，或QPS 1000以上，数据库读写速度比较缓慢

分库带来的问题
- join 操作：同一个数据库中的表分布在了不同的数据库中，导致无法使用 join 操作，需要手动进行数据的封装，比如在一个数据库中查询到一个数据之后，再根据这个数据去另外一个数据库中找对应的数据
- 事务问题：同一个数据库中的表分布在了不同的数据库中，如果单个操作涉及到多个数据库，那么数据库自带的事务就无法满足要求
- 分布式 id ：分库之后，数据遍布在不同服务器上的数据库，数据库的自增主键已经没办法满足生成的主键唯一了，需要为系统引入分布式 id

5 ALTER TABLE

MySQL 执行的大部分修改表结构的操作是用新结构创建一个空表，将旧表数据插入新表，最后删除旧表
大部分的 ALTER TABLE 操作会导致 MySQL 服务中断，一般解决方案：
- 在不提供服务的机器上执行 ALTER TABLE，再和提供服务的主机进行切换
- 影子拷贝：用新结构创建一个空表（和原表无关），通过原子的重命名操作切换两张表
ALTER TABLE 修改列的三种操作
- ALTER COLUMN
  - 改变、删除列的默认值
  - 直接修改 .frm 文件而不涉及表数据，所以操作很快
- CHANGE COLUMN
  - 重命名列和修改列的数据类型
- MODIFY COLUMN
  - 修改列数据类型，改变、删除列的默认值
  - 这个操作会有数据的读取和插入操作，拷贝整张表到一张新表

6 如何优化慢查询

explain 分析 SQL 语句，查看执行计划，根据执行计划优化 SQL
优化索引结构，适当添加索引
对数量大的表，可以考虑进行分表（分表经验阈值：QPS>1000，数据规模>1000万）
数据库主从分离，读写分离
查看执行日志，分析是否有其他方面的问题

7 EXPLAIN

参考链接

主要关注字段

列名	含义
type	本次查询表联接类型，从这里可以看到本次查询大概的效率
key	最终选择的索引
key_len	本次查询用于结果过滤的索引实际长度
rows	预计需要扫描的记录数，预计需要扫描的记录数越小越好
Extra	额外信息，主要确认是否出现 `Using filesort`、`Using temporary` 两种情况

type 列的结果
Extra 列的结果

8 SQL 注入与避免

SQL 注入的原理是将 SQL 代码伪装成参数，传递到服务器端解析执行，导致意料之外的操作

例如登录的场景，输入用户名和密码 SELECT * FROM User WHERE username = 'zhangsan' AND password = '123';
将 '' or 1 = 1#作为用户名传递进去，SQL 就变成 SELECT * FROM User WHERE username = '' or 1 = 1# AND password = '123';，校验密码的逻辑被注释掉，返回所有用户信息

避免方法
- 参数校验
- SQL 预编译，将 SQL 语句在传入参数前进行编译（参数用占位符进行占位），编译后再用参数替换占位符，之后将不再进行编译
  - MyBatis 通过 #{} 传递参数采用预编译实现，为参数自动添加引号，可以避免 SQL 注入
  - ${} 基于变量替换，不会自动添加引号，无法避免 SQL 注入

五锁：服务器层与 InnoDB 存储引擎

该部分的参考

服务器层实现了最基本的表锁，该表锁也实现了读锁、写锁的划分
- 读锁之间不阻塞，其它情况存在阻塞
存储引擎层管理自己的锁策略，设置自己的锁粒度，服务器层是不可见的
- InnoDB 采用两阶段锁协议：两阶段指的是加锁阶段和解锁阶段。事务执行过程中，随时可以执行锁定，只有执行 COMMIT 和 ROLLBACK（事务提交或回滚时），同时释放所有的锁

1 表锁：S、X、IS、IX

表锁是 MySQL 中锁定 粒度最大 的一种锁，是最基本的锁策略，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁；锁定粒度最大，触发锁冲突的概率最高，并发度最低
意向锁
- 共享意向锁 IS：事务想获得表中某几行的共享锁
- 排他意向锁 IX：事务想获得表中某几行的排他锁
- 当事务要在记录上加上行锁时，要首先在表上加上意向锁，意向锁使判断表中是否有记录正在加锁更简单，无需遍历整张表的数据
- 意向锁不与行级锁发生冲突，意向锁之间不发生冲突
表级锁的兼容矩阵

2 行锁：S、X

行锁在存储引擎层实现，以下结论均基于 InnoDB
行级锁是 MySQL 中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁
行级锁能大大减少数据库操作的冲突：加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁
行锁是 加在索引 上的，如果查询语句不使用索引（索引失效/索引不命中）的话，那么可能会升级到表锁
行锁根据行为划分
- 共享锁（S）：加了锁的记录，所有事务都能去读取但不能修改，同时阻止其他事务获得相同数据集的排他锁
- 排他锁（X）：允许已经获得排他锁的事务去更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁

执行的命令与加锁的情况
修改语句加X锁
快照读不加锁，由 MVCC 实现事务隔离
当前读加锁：select ... for update 加X锁；select ... in share mode 加S锁

行锁根据位置划分：根据行锁的位置不同进行划分，每种情况又会分为共享锁（读锁）/排他锁（写锁）
1. Next-Key Lock
  - 本质上是 Gap Lock + Record Lock，左开右闭
  - MySQL 默认隔离级别是可重复读，在这种级别下，如果执行当前读（select in share mode 或 select for update），那么 InnoDB 会使用 Next-Key Lock，防止幻读，在上面已经讨论过
2. Gap Lock
  - 使用范围条件而不是相等条件去检索，并请求锁时，InnoDB就会给符合条件的记录的索引项加上锁；而对于键值在条件范围内但并不存在的记录，就叫做间隙，InnoDB此时也会对间隙加锁
  - 在读未提交和读提交两种隔离级别下，执行当前读（select in share mode 或 select for update）无法防止幻读，因为这两种隔离级别下只会有行锁，而不会有间隙锁
  - 可重复读隔离级别执行当前读，会在间隙上加上间隙锁
3. Record Lock
  - 最简单的行锁

在这里插入图片描述

六 SQL 查询

1 语法

having 子句只能使用分组字段或聚合函数
having 子句用于过滤分组（操作的对象是分组），即过滤一些分组，得到一些分组
where 子句不能跟聚合函数（需要综合多行得到结果的函数，例如min/max/sum/count/avg，相比之下 having 字句可以），但可以跟 year/month/date/timestampdiff 等非聚合函数（每行都有一个映射，而不用综合多行）

select [distinct]
from
join
on
where
group by
having
union
order by
limit

2 执行顺序

步骤6执行的是聚合函数，特征是要结合多个行才能得到值，非聚合函数 year()、substring()、if() 等执行时机更早

1. from  # 首先进入from字句
2. on  # 根据条件选择需要连接的行
3. join  # 执行连接
4. where  # 对连接结果过滤
5. group by  # 分组（在分组之前执行了 select 后面的 if、substring_index... 等非运算操作）
6. avg(), sum(), count()...  # **聚合**函数
7. having  # 对各个分组分别进行过滤
8. select  # 选择结果
9. distinct  # 结果去重
10. union  # 将当前结果并上其它结果
11. order by [asc, desc] # 排序
12. limit  # 选择指定行作为结果

3 常用函数 / 关键字

day(date) / month(date) / year(date)：获取日/月/年

# 获取2021年8月里，每天的练习量
select day(date) day, count(*) question_cnt
from question_practice_detail
where year(date) = 2021 and month(date) = 8
group by day

if(cond, true_val, false_val)：条件正确时选择前者，否则后者

# 分别查看 25岁以下和25岁及以上 两个年龄段的用户数量
# 这里先执行了 if 再执行了 group by
select if(age>=25, '25岁及以上', '25岁以下') as age_cut, 
		count(device_id) as number
from user_profile
group by age_cut

# 复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况
# 主要问题是统计 result = 'right' 的行数，count(result = 'right') 是错误的写法，应该 sum(if(result = 'right', 1, 0))
# count 只能统计非空（可选择不重复）的字段的个数
select t1.device_id, t1.university, 
		count(t2.question_id) question_cnt, 
		sum(if(t2.result = 'right', 1, 0)) right_question_cnt
from user_profile t1
left join (
    select device_id, result, question_id
    from question_practice_detail
    where month(date) = 8) t2
on t1.device_id = t2.device_id
where university = '复旦大学'
group by device_id

case...when...then...else...end

# 将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段
select
    device_id,
    gender,
    case
        when age >= 25 then '25岁及以上'
        when age >= 20 then '20-24岁'
        when age < 20 then '20岁以下'
        else '其他'
    end age_cut
from user_profile

# bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%
# 请你给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus
select eb.emp_no, 
		e.first_name, 
		e.last_name, 
		eb.btype, 
		s.salary, 
        round(s.salary * (case eb.btype 
                            when 1 then 0.1
                            when 2 then 0.2 
                            else 0.3
                            end), 1) bonus
from employees as e 
    join salaries as s on e.emp_no = s.emp_no 
    join emp_bonus as eb on eb.emp_no = s.emp_no and eb.recevied <= s.to_date and eb.recevied >= s.from_date
order by emp_no asc

union [all]：不加 all 会自动去重
substring_index(field, separator, index)：如果 index > 0 则取前缀，否则取后缀
upper(str)：转为大写
concat(str1, str2)：拼接字符串
timestampdiff(unit,begin,end)：获取时间差，需要指定单位

4 SQL 更新数据的执行流程 & 两阶段提交

在这里插入图片描述
（上图存疑：先将数据加载到内存，再将修改记录到 Redo Log）

两阶段提交是为了保证 Bin Log 和 Redo Log 的一致性
1. 一阶段：将 Redo Log 写入到磁盘，并将其状态设置为 Prepare
2. 二阶段：将 Bin Log 写入磁盘，并将 Redo Log 状态设置为 Commit
数据库崩溃时，根据 Bin Log 和 Redo Log 的一致性判断恢复行为
1. Bin Log 有记录，Redo Log 状态 Commit：事务正常完成，不需要恢复
2. Bin Log 有记录，Redo Log 状态 Prepare：如果 Bin Log 事务完整则提交事务，否则回滚事务
3. Bin Log 无记录，Redo Log 状态 Prepare：回滚事务

七 InnoDB 日志

1 日志对比

类型	执行任务	作用	所属层
`Redo Log`	记录物理级别的页的修改操作（页号, 偏移量, 数据）	保证事务的持久性	存储引擎层，事务在内存中修改后写入 Buffer，在事务发起提交后写入磁盘
`Undo Log`	记录逻辑操作日志（比如执行 INSERT 时，在 Undo Log 中记录与之相反的 DELETE 操作，即每个修改操作的逆操作）	保证事务的原子性；MVCC	存储引擎层，事务在内存中修改前写入
`Bin Log`	记录逻辑操作日志	数据恢复、数据复制	数据库层，事务发起提交后根据策略写入

Redo Log 针对的是已提交数据的恢复操作，即提交后未刷盘的情况下，操作系统 / 数据库发生停机时，用到 Redo Log
Undo Log 针对的是未提交数据的恢复操作，在事务未提交的情况下需要 rollback，用到 Undo Log
Undo Log 不是 Redo Log的逆过程，Undo Log 无需持久化，而 Redo Log 需要

2 Redo Log

2.1 解决持久性的问题

事务执行并且 COMMIT 后（如果没有 COMMIT 则无需进行任何恢复，原子性），数据的修改只在内存中完成，并未同步到外存。此时发生宕机，持久性的保持问题
Redo Log 的解决方法是，每次仅仅记录如何修改而非具体的数据，所以占用空间很小
数据库按一定频率将已提交的内存的数据同步到外存中。在此之前采用 WAL（Write-Ahead Logging）机制，先将日志写入到外存，再将数据写入到外存，只有日志写入成功，事务才算提交成功

2.2 执行流程

在这里插入图片描述

2.3 Redo Log 刷盘策略

此处讨论的是 Redo Log 的持久化策略，而非数据，只有 Redo Log 持久化过的数据修改，这些修改后的数据才能写入外存

innodb_flush_log_at_trx_commit	行为
0	事务提交时不写入 page cache，也不刷盘，交给后台线程完成
1（默认）	事务提交时立刻写入 page cache 并刷盘
2	事务提交时立即写入 page cache，刷盘交给后台线程完成

下图中的编号代表事务提交时，不同策略下 Redo Log 的位置
在这里插入图片描述

3 Undo Log

3.1 数据分类

未提交的回滚数据 (uncommitted undo information)
已经提交但未过期的回滚数据 (committed undo information)，特指未过期的 Update Undo Log，用于 MVCC 的实现
事务已经提交并过期的数据 (expired undo information)

3.2 Undo Log 的两种类型

Insert Undo Log
- INSERT 过程中产生的 Undo Log，因为 INSERT 操作的记录只对事务本身可见，其它事务不可见，所以可以在事务提交后直接删除
Update Undo Log
- DELETE 和 UPDATE 过程中产生的 Undo Log，用于实现 MVCC，所以不能在事务提交后就删除，而是放入 Undo Log 链表，等待过期后再删除

3.3 Redo Log + Undo Log 生成过程（SQL在执行引擎层的流程）*

在这里插入图片描述

事务开始，发起更新数据的请求
对于内存不存在的数据，需要先从外存加载到内存
INSERT / UPDATE / DELETE 数据前，首先更新 Undo Log
在内存 Buffer Pool 中更新数据
更新 Redo Log Buffer
（事务操作执行完毕，准备提交，进入两阶段提交）一阶段：将 Redo Log 刷新到外存，状态设置为 Prepare
二阶段：将 Bin Log 刷新到外存，将 Redo Log 状态设置为 Commit，此时才算提交成功
内存中修改后的数据按一定策略同步到外存

4 Bin Log

4.1 记录 Bin Log 的三种模式

在这里插入图片描述

模式	行为	优点	缺点
Statement Level（默认）	记录每一条修改数据的SQL语句	日志占用空间小，节约磁盘I/O	对一些特殊功能的复制效果不是很好，比如函数、存储过程的复制
Row Level	记录的方式是行：如果批量修改数据，记录的不是批量修改的SQL语句，而是每条记录被更改的SQL语句	记录每一行数据被修改的细节，不会出现特殊操作无法被复制的情况	日志占用空间大
Mixed	两种模式的结合，根据具体的SQL语句决定记录的日志形式	-	-

如果使用 MySQL 的特殊功能相对少（存储过程、触发器、函数），选择 Statement Level
如果使用 MySQL 的特殊功能较多，可以选择 Mixed
如果使用 MySQL 的特殊功能较多，又希望数据最大化一致，选择 Row level

4.2 Bin Log 的刷盘策略

sync_binlog	策略
0	由系统决定写入时机
1（默认）	每次事务提交时将写入磁盘
N	每N次事务提交时写入磁盘，牺牲一致性换取更高的性能

4.3 使用 Bin Log 进行主从复制

在这里插入图片描述

整个过程需要依赖三个线程：主库的二进制日志转储线程，从库的I/O线程和SQL线程
1. 主库的数据更新事件（update、insert、delete）写入 Bin Log
2. 从库发起连接，连接到主库
3. 主库创建一个 BinLog Dump Thread，把 Bin Log 的内容发送到从库
4. 从库启动之后，创建一个 I/O Thread，读取主库传过来的 Bin Log 内容并写入到 Relay Log
5. 从库创建一个 SQL Thread，从 Relay Log 里面读取内容，并执行其中包含的事务，使从库数据和主库保持一致
数据库主库和从库不一致，常见优化方案
- 业务可以接受，系统不优化
- 强制读主：使用高可用主库，把读写任务都交给主库
- 选择性读主：在 cache 里记录哪些记录发生过写请求，用来路由读主还是读从