进阶篇

1.存储引擎

MySQL体系结构：

img

img

  • 存储引擎是 数据存储、索引建立、更新和查询 的具体实现方式。
  • 它是 基于表（table） 而不是基于库（database）的，所以一个数据库里不同的表可以用不同的引擎。
  • MySQL 5.5 之后默认是 InnoDB。

相关操作：

1
2
3
4
5
6
7
8
-- 查询建表语句
show create table account;
-- 建表时指定存储引擎
CREATE TABLE 表名(
	...
) ENGINE=INNODB;
-- 查看当前数据库支持的存储引擎
show engines;

img

接下来我们就来介绍下来上面
重点提到的三种存储引擎 InnoDB、MyISAM、Memory的特点

1.InnoDB

InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 引擎

特点：

  • DML 操作遵循 ACID 模型，支持事务
  • 行级锁，提高并发访问性能
  • 支持外键约束，保证数据的完整性和正确性

文件：

  • xxx.ibd: xxx代表表名，InnoDB 引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。

参数：是否一表一文件由 innodb_file_per_table 参数控制

每一个innodb引擎数据表都对应着一个ibd文件

img

知识点：

1
2
3
4
5
查看 Mysql 变量：
show variables like 'innodb_file_per_table';
从idb文件提取表结构数据：
（在cmd运行）
ibd2sdi xxx.ibd

InnoDB 逻辑存储结构：

img

适合场景
高并发、需要事务安全、数据一致性要求高的业务（如订单、支付系统）

2.MyISAM

MyISAM 是 MySQL 早期的默认存储引擎。

特点：

  • 不支持事务，不支持外键
  • 支持表锁，不支持行锁
  • 访问速度快

文件：

  • xxx.sdi: 存储表结构信息
  • xxx.MYD: 存储数据
  • xxx.MYI: 存储索引

img

适合场景
读多写少、不需要事务（如日志、文章内容、商品评论等）

3.Memory

Memory 引擎的表数据是存储在内存中的，受硬件问题、断电问题的影响，只能将这些表作为临时表或缓存使用。

特点：

  • 存放在内存中，速度快
  • hash索引（默认）

文件：

  • xxx.sdi: 存储表结构信息

适合场景
缓存、临时数据（如临时计算结果、会话数据）

4.存储引擎特点

特性 InnoDB MyISAM Memory
存储限制 64TB 有 有
事务安全 ✅ ❌ ❌
锁机制 行锁 表锁 表锁
B+Tree索引 ✅ ✅ ✅
Hash索引 ❌ ❌ ✅
全文索引 ✅(5.6+) ✅ ❌
空间使用 高 低 N/A
内存使用 高 低 中等
批量插入速度 低 高 高
支持外键 ✅ ❌ ❌

5.存储引擎的选择

  • InnoDB → 事务多、并发高、需要数据完整性（电商订单、支付、库存）
  • MyISAM → 读多写少、事务要求不高（新闻、评论、日志）
  • Memory → 临时数据、缓存（在线排行榜、临时分析表）

电商示例：

  • 用户订单表 → InnoDB（保证事务和一致性）
  • 商品评论表 → MyISAM（读多写少，性能高）
  • 热门搜索词缓存表 → Memory（速度快）

2.索引

定义：索引是数据库为了快速查找数据而维护的一种额外数据结构（有序）。

本质：在数据之外，多维护一份结构化的“目录”，通过它可以快速定位数据的位置，而不是全表扫描。

优缺点：

优点：

  • 提高数据检索效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

缺点：

  • 索引列也是要占用空间的
  • 索引大大提高了查询效率，但降低了更新的速度（写操作），比如 INSERT、UPDATE、DELETE

1.索引结构

索引结构 描述
B+Tree 最常见的索引类型，大部分引擎都支持B+树索引
Hash 底层数据结构是用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询
R-Tree(空间索引) 空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-Text(全文索引) 是一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucene, Solr, ES
索引 InnoDB MyISAM Memory
B+Tree索引 支持 支持 支持
Hash索引 不支持 不支持 支持
R-Tree索引 不支持 支持 不支持
Full-text 5.6版本后支持 支持 不支持

B-Tree

  • 多路平衡查找树，每个节点可存多个键值和指针
  • 数据既可能在非叶子节点，也可能在叶子节点
  • 数据量大时，非叶子节点存数据会降低单页可存的 key 数，从而增加树高

img

二叉树的缺点可以用红黑树来解决：

img

红黑树也存在大数据量情况下，层级较深，检索速度慢的问题。

为了解决上述问题，可以使用 B-Tree 结构。

B-Tree (多路平衡查找树) 以一棵最大度数（max-degree，指一个节点的子节点个数）为5（5阶）的 b-tree 为例（每个节点最多存储4个key，5个指针）

img

B-Tree 的数据插入过程动画参照：https://www.bilibili.com/video/BV1Kr4y1i7ru?p=68
演示地址：https://www.cs.usfca.edu/~galles/visualization/BTree.html

img

B+Tree

结构图：

img

我们可以看到，两部分：

  • 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
  • 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

与 B-Tree 的区别：

  • 所有的数据都会出现在叶子节点
  • 叶子节点形成一个单向链表→ 范围查询快
  • 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。→ 单页可存更多 key，树高度低

MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能。

img

Hash

  • 使用哈希函数计算 key 的 hash 值并映射到槽位
  • 冲突通过链表解决

img

特点：

  • Hash索引只能用于对等比较（=、in），不支持范围查询（betwwn、>、<、…）
  • 无法利用索引完成排序操作
  • 查询效率高，通常只需要一次检索就可以了，效率通常要高于 B+Tree 索引

存储引擎支持：

  • Memory
  • InnoDB: 具有自适应hash功能，hash索引是存储引擎根据 B+Tree 索引在指定条件下自动构建的

面试题

  1. 为什么 InnoDB 存储引擎选择使用 B+Tree 索引结构？
  • 相对于二叉树，层级更少，搜索效率高
  • 对于 B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针也跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
  • 相对于 Hash 索引，B+Tree 支持范围匹配及排序操作

img

2.索引分类

分类 含义 特点 关键字
主键索引 针对于表中主键创建的索引 默认自动创建，只能有一个 PRIMARY
唯一索引 避免同一个表中某数据列中的值重复 可以有多个 UNIQUE
常规索引 快速定位特定数据 可以有多个
全文索引 全文索引查找的是文本中的关键词，而不是比较索引中的值 可以有多个 FULLTEXT

在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类 含义 特点
聚集索引(Clustered Index) 将数据存储与索引放一块，索引结构的叶子节点保存了行数据 必须有，而且只有一个
二级索引(Secondary Index) 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 可以存在多个

img

回表查询过程：先走二级索引找到对应的主键值，再到聚集索引当中，再到聚集索引当中拿到这一行的行数据

img

聚集索引选取规则：

  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
  • 如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

思考题

  1. 以下 SQL 语句，哪个执行效率高？为什么？
1
2
3
select * from user where id = 10;
select * from user where name = 'Arm';
-- 备注：id为主键，name字段创建的有索引

答：第一条语句，因为第二条需要回表查询，相当于两个步骤。

  1. InnoDB 主键索引的 B+Tree 高度为多少？

答：假设一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB 的指针占用6个字节的空间，主键假设为bigint，占用字节数为8.
可得公式：n * 8 + (n + 1) * 6 = 16 * 1024，其中 8 表示 bigint 占用的字节数，n 表示当前节点存储的key的数量，(n + 1) 表示指针数量（比key多一个）。算出n约为1170。

如果树的高度为2，那么他能存储的数据量大概为：1171 * 16 = 18736；
如果树的高度为3，那么他能存储的数据量大概为：1171 * 1171 * 16 = 21939856。

另外，如果有成千上万的数据，那么就要考虑分表，涉及运维篇知识。

img

3.索引语法

创建索引：

1
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);

如果不加 CREATE 后面不加索引类型参数，则创建的是常规索引

1
2
3
4
查看索引：
SHOW INDEX FROM table_name;
删除索引：
DROP INDEX index_name ON table_name;

案例：

1
2
3
4
5
6
7
8
9
10
11
-- name字段为姓名字段，该字段的值可能会重复，为该字段创建索引
create index idx_user_name on tb_user(name);
-- phone手机号字段的值非空，且唯一，为该字段创建唯一索引
create unique index idx_user_phone on tb_user (phone);
-- 为profession, age, status创建联合索引
create index idx_user_pro_age_stat on tb_user(profession, age, status);
-- 为email建立合适的索引来提升查询效率
create index idx_user_email on tb_user(email);

-- 删除索引
drop index idx_user_email on tb_user;

4.SQL性能分析

查看执行频次

1
2
3
4
查看当前数据库的 INSERT, UPDATE, DELETE, SELECT 访问频次：
SHOW GLOBAL STATUS LIKE 'Com_______'; 
或者 SHOW SESSION STATUS LIKE 'Com_______';
例：show global status like 'Com_______'

img

  • Com_delete: 删除次数

  • Com_insert: 插入次数

  • Com_select: 查询次数

  • Com_update: 更新次数

  • 如果数据库主要执行 SELECT 查询操作，可能需要关注 索引优化。

  • 如果 INSERT, UPDATE, DELETE 操作较多，可能更多的是需要关注事务处理和数据表设计，而非索引。

慢查询日志

慢查询日志是记录执行时间超过指定时间(（long_query_time，单位：秒，默认10秒）)的 SQL 语句。通过分析慢查询日志，你可以识别出执行缓慢的 SQL 语句，并对它们进行优化。

img

1
2
3
4
5
6
7
8
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
# 开启慢查询日志开关
slow_query_log=1
# 设置慢查询日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
更改后记得重启MySQL服务，日志文件位置：/var/lib/mysql/localhost-slow.log
查看慢查询日志开关状态：
show variables like 'slow_query_log';

img

profile

Profile 功能可以帮助你查看 SQL 执行的各个阶段的时间消耗，从而确定哪一部分操作消耗较大。

1
2
3
4
5
6
7
8
9
10
通过 have_profiling 参数，能看到当前 MySQL 是否支持 profile 操作：
SELECT @@have_profiling;
profiling 默认关闭，可以通过set语句在session/global级别开启 profiling：
SET profiling = 1;
查看所有语句的耗时：
show profiles;
查看指定query_id的SQL语句各个阶段的耗时：
show profile for query query_id;
查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

查看每一条SQL的耗时情况:

img

查看指定SQL各个阶段的耗时情况 :

img

explain(执行计划分析）

EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法：

1
2
# 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 HWERE 条件;

img

EXPLAIN 各字段含义：

  • id: 查询的序列号，表示查询操作的执行顺序。id 越小，越先执行。
  • select_type: 表示查询的类型，常见值有：
  • SIMPLE: 单表查询
  • PRIMARY: 主查询（外层查询）
  • UNION: UNION 查询中的第二个或后续的查询
  • SUBQUERY: 包含子查询的查询
  • type: 连接类型，决定查询效率，从高到低排序为：NULL、system、const、eq_ref、ref、range、index、all。越低效的连接类型对查询性能的影响越大。
  • possible_key: 表示可能会使用的索引列表。
  • Key: 实际使用的索引。
  • Key_len: 表示 MySQL 使用的索引的字节数。
  • rows: MySQL 预计需要扫描的行数。
  • filtered: 表示返回的行数占总扫描行数的比例。值越高越好，说明返回结果更加精确。

总结

这部分内容总结了几种 MySQL 性能分析 的方法和工具，帮助我们：

  • 查看数据库的操作频率，判断优化的方向；
  • 开启并分析慢查询日志，定位执行慢的 SQL 语句；
  • 使用 Profile 来分析 SQL 的执行过程，找出消耗时间最多的部分；
  • 使用 EXPLAIN 来分析 SQL 查询的执行计划，从而找出潜在的性能问题。

通过这些方法，我们可以精准地定位性能瓶颈，进而进行优化，提高数据库查询性能。

5.索引使用规则

验证索引效率

img

img

img

结果为0.01s我们明显会看到，sn字段建立了索引之后，查询性能大大提升。建立索引前后，查询耗时都不是一个数量级的

最左前缀法则

定义：如果索引关联了多个列（即联合索引），查询时必须从索引的最左列开始，且不能跳过任何列。如果跳过某一列，后面的列索引将会失效。

规则：最左列（A）必须出现在查询条件中，且不跳过任何列。

例子：假设有一个联合索引 (A, B, C)，如果查询条件是 WHERE A = ? AND C = ?，那么 B 列索引会失效。

img

img

img

img

应用场景：

  • 在创建联合索引时，查询时应尽量保证按照最左前缀顺序使用索引，避免不必要的索引失效。

范围查询

在联合索引中，如果查询条件涉及范围查询（例如 >, <, BETWEEN），那么索引会从范围查询条件右侧的列开始失效。

  • 解决方法：可以使用 >= 或 <= 代替 > 或 <，以避免索引失效。
  • 例子：假设索引是 (A, B, C)，如果查询条件是 WHERE A = ? AND B > ?，那么C列的索引将失效。

img

索引失效情况

1. 在索引列上进行运算

  • 例子：SELECT * FROM tb_user WHERE substring(phone, 10, 2) = '15';
  • 问题：在 phone 列上使用了 substring() 函数，这会导致索引失效，因为运算会导致 MySQL 无法直接利用索引查找数据。

2. 字符串类型字段没有加引号

  • 例子：SELECT * FROM tb_user WHERE phone = 17799990015;
  • 问题：数字型字段（如 phone）没有加引号，MySQL 会将其当作数字处理，而不是字符串，导致索引失效。

3. 模糊查询

  • 例子：SELECT * FROM tb_user WHERE profession LIKE '%工程';
  • 问题：尾部模糊匹配（%工程）不一定会导致索引失效，但如果是头部模糊匹配（LIKE '工程%'），索引会失效。

4. 使用 OR 时，部分条件没有索引

  • 例子：SELECT * FROM tb_user WHERE phone = '123' OR address = 'China';
  • 问题：如果 phone 列有索引，而 address 列没有索引，整个 OR 查询将无法使用索引。

img

5. 索引不一定比全表扫描更快

问题：当查询的结果集过大时，MySQL 可能会选择不使用索引，而直接进行全表扫描。尤其是在索引选择性较低的情况下。

SQL 提示

是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

通过 USE INDEX、IGNORE INDEX 和 FORCE INDEX 提示，开发者可以手动干预 MySQL 的索引选择。

  • USE INDEX：建议 MySQL 使用某个索引，但 MySQL 仍会根据查询的实际情况选择最优索引。
  • IGNORE INDEX：明确告诉 MySQL 忽略某个索引。
  • FORCE INDEX：强制 MySQL 使用指定的索引，忽略其它可能的索引。
1
2
3
4
5
6
7
8
-- 使用 idx_user_pro 索引
SELECT * FROM tb_user USE INDEX(idx_user_pro) WHERE profession="软件工程";

-- 忽略 idx_user_pro 索引
SELECT * FROM tb_user IGNORE INDEX(idx_user_pro) WHERE profession="软件工程";

-- 强制使用 idx_user_pro 索引
SELECT * FROM tb_user FORCE INDEX(idx_user_pro) WHERE profession="软件工程";

use 是建议，实际使用哪个索引 MySQL 还会自己权衡运行速度去更改，force就是无论如何都强制使用该索引。

覆盖索引&回表查询

尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？ 覆盖索引：当查询使用的字段完全包含在索引中时，称为覆盖索引，这样查询时不需要回表，从索引中就能获取所需的全部数据。

回表查询：当索引列不包含所有查询的字段时，MySQL 会先用索引查找数据的位置（id），然后再通过主键回表查找其它列。

img

img

explain 中 extra 字段含义：
using index condition：查找使用了索引，但是需要回表查询数据
using where; using index;：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询

如果在聚集索引中直接能找到对应的行，则直接返回行数据，只需要一次查询，哪怕是select *；如果在辅助索引中找聚集索引，如select id, name from xxx where name='xxx';，也只需要通过辅助索引(name)查找到对应的id，返回name和name索引对应的id即可，只需要一次查询；如果是通过辅助索引查找其他字段，则需要回表查询，如select id, name, gender from xxx where name='xxx';

所以尽量不要用select *，容易出现回表查询，降低效率，除非有联合索引包含了所有字段

面试题：

问题：如果表有字段 id, username, password, status，对于查询 SELECT id, username, password FROM tb_user WHERE username='itcast';，如何优化？

解答：可以为 username 和 password 创建联合索引，这样可以避免回表查询，直接通过索引获取所需的数据，优化查询性能。

前缀索引

当字段是字符串类型（如 VARCHAR 或 TEXT）时，索引会占用大量空间。通过创建前缀索引，只对字段的一部分进行索引，能显著减少索引空间的占用，提高查询效率。

语法：

1
create index idx_xxxx on table_name(columnn(n));
  • 其中，n 表示索引前缀的长度。例如，如果字段是 VARCHAR(100)，我们可以创建索引只对前 10 个字符建立索引，从而减少索引大小。

前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

求选择性公式：

1
2
select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email, 1, 5)) / count(*) from tb_user;

show index 里面的sub_part可以看到截取的长度

img

单列索引&联合索引

单列索引：每个索引仅涉及一个字段。
联合索引：每个索引涉及多个字段，可以提高多条件查询的效率。

使用建议：

  • 在有多个查询条件时，应该考虑创建联合索引，而不是单列索引。
1
2
单列索引情况：
explain select id, phone, name from tb_user where phone = '17799990010' and name = '韩信';

img

注意事项

  • 多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询

6.索引设计原则

1.针对数据量大且查询频繁的表建立索引

背景

  • 数据量大，查询操作频繁的表通常是数据库的性能瓶颈所在。
  • 在这种情况下，索引的作用尤为重要，因为索引可以大大减少全表扫描，提高查询效率。

建议

  • 对这些表中的常用查询字段建立索引，特别是WHERE、ORDER BY、GROUP BY条件涉及的字段，能显著提高查询性能。
  • 注意：虽然索引可以提升查询速度，但也会增加插入、更新、删除等操作的开销。因此，选择索引时需要权衡性能。

2.针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引

背景

  • 查询条件、排序（ORDER BY）、分组（GROUP BY）字段通常在查询时最为频繁。为这些字段建立索引，可以大大提高查询性能。

建议

  • WHERE 子句中的字段：这些字段经常用于条件查询，所以对其建立索引可以显著提高检索速度。
  • ORDER BY 和 GROUP BY 子句中的字段：这些操作通常会导致数据库需要对结果集进行排序或分组，索引能够提高这些操作的效率，避免全表扫描。
  • 例如：SELECT * FROM employees WHERE department_id = 5 ORDER BY salary DESC;，可以在 department_id 和 salary 字段上建立联合索引。

3.选择区分度高的列作为索引，尽量建立唯一索引

背景

  • 区分度指的是某一列能够区分出多少不同的值。区分度高的列，能够让数据库通过索引快速定位数据。
  • 唯一索引对区分度高的列尤其有效，因为它确保每个值是唯一的，索引的查找效率非常高。

建议

  • 建立索引时，应该尽量选择那些区分度高的字段，例如 ID、邮箱等字段，尤其适合建立唯一索引。
  • 唯一索引不仅能提高查询效率，还能确保数据的完整性，避免重复数据的插入。

4.对于字符串类型的字段，字段较长时考虑使用前缀索引

背景

  • 字符串类型（如 VARCHAR、TEXT）的字段通常需要较大的存储空间，而建立索引时，较长的字符串字段会占用大量磁盘空间，查询时也可能导致性能下降。

建议

  • 前缀索引是指只对字符串字段的一部分（前缀）建立索引，而不是整个字段。这样可以节省存储空间，并提高索引效率。
  • 例如：如果一个 VARCHAR(255) 类型的字段包含大多数相似的前缀，可以建立前 10 个字符的前缀索引。

5.使用联合索引，减少单列索引

背景

  • 单列索引每次只能对一个字段进行索引，不能满足多个查询条件的高效检索。
  • 联合索引是指一个索引同时包含多个字段，它能提升多条件查询的效率。

建议

  • 在多条件查询中，使用联合索引可以大大提高性能。例如，查询条件涉及多个字段时（如：WHERE column1 = ? AND column2 = ?），可以为这两个字段创建联合索引。
  • 覆盖索引：如果查询的字段完全被索引覆盖，数据库可以直接从索引中获取数据，而无需回表查询，从而提升查询效率。

6.控制索引数量

背景

  • 索引虽然能提升查询性能，但它也会占用额外的存储空间，并增加 插入、更新、删除 等操作的负担。
  • 每次执行 INSERT、UPDATE 或 DELETE 时，MySQL 都需要更新相关索引，增加额外的性能开销。

建议

  • 不要建立过多的索引，应当根据查询实际需求选择性地创建索引。
  • 索引的维护成本高，因此需要在查询性能和写入性能之间找到平衡点。

7.使用 NOT NULL 约束优化索引

背景

  • 如果索引列允许 NULL 值，MySQL 在处理这些列时需要额外的开销，因为 NULL 值的处理会让索引效率下降。

建议

  • 对于经常被用于查询的索引列，最好使用 NOT NULL 约束，避免索引中出现 NULL 值，从而提高查询效率。
  • 例如，username 列如果有很多 NULL 值，可以通过 NOT NULL 约束来限制，以确保索引更高效。

索引设计原则概括：

  1. 为大数据量和频繁查询的表建立索引。
  2. 索引应覆盖查询条件、排序、分组的字段。
  3. 选择区分度高的字段进行索引，尽量使用唯一索引。
  4. 对于较长的字符串字段，使用前缀索引。
  5. 多条件查询时，使用联合索引而非多个单列索引。
  6. 控制索引数量，避免索引过多带来的维护成本。
  7. 使用 NOT NULL 约束，避免索引列出现 NULL 值，提高查询效率。

7.索引总结

img

img

3.SQL优化

1.插入数据

普通插入：

img

  1. 采用批量插入（一次插入的数据不建议超过1000条）
  2. 手动提交事务
  3. 主键顺序插入

大批量插入：
如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令插入。

1
2
3
4
5
6
7
# 客户端连接服务端时，加上参数 --local-infile（这一行在bash/cmd界面输入）
mysql --local-infile -u root -p
# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
select @@local_infile;
# 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

2.主键优化

数据组织方式：

  • 在 InnoDB 存储引擎中，数据是按照 主键顺序组织存放的，这意味着表的数据实际上是一个索引组织表（IOT），数据行会按照主键排序存储。
  • 每个数据页可以存放多行数据，页大小是固定的（通常为 16KB）。在这些页中，数据行会根据主键排序，填充满页，直到达到 100%。如果数据行过大，就会出现行溢出。

img

页分裂：

页可以为空，也可以填充一半，也可以填充100%，每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排列。

img

img

img

img

img

img

页合并：

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录到达 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前后）看看是否可以将这两个页合并以优化空间使用。

img

img

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或创建索引时指定

主键设计原则：

  • 减少主键长度：主键应该尽量短，以减少索引大小，提升查询性能。
  • 顺序插入：使用 AUTO_INCREMENT 来保证主键按顺序插入，避免插入时产生大量的页分裂。
  • 避免使用 UUID 或自然主键：UUID 是随机的，这会导致插入时产生更多的页分裂，降低性能。
  • 避免修改主键：修改主键会导致重新定位数据行，带来较大的性能开销。

3.order by优化

  1. Using filesort：MySQL 在排序时，通过表的索引或全表扫描读取数据行，并在内存的排序缓冲区（sort_buffer）中进行排序。这种排序被称为 Filesort，通常比通过索引直接排序慢。
  2. Using index：如果查询字段已经被索引，并且查询结果能够按照索引顺序返回，那么就不需要额外的排序操作，称为 Using Index，这种方式的效率最高。

img

img

img

img

img

img

ORDER BY 中的升序和降序

如果 ORDER BY 中的排序字段全是升序或降序，且与索引的顺序一致，那么 MySQL 可以直接使用索引返回排序结果，无需额外排序。

如果 升序和降序排序混合使用（例如：ORDER BY age ASC, phone DESC），MySQL 无法直接利用单个索引返回结果，而是会执行 Filesort 排序操作。

优化方案

  1. 创建合适的索引

    ：根据查询的排序字段创建合适的索引，特别是当排序字段是多个时，使用联合索引。

    • 如果排序字段混合使用升序和降序，可以创建一个适应的联合索引（例如：CREATE INDEX idx_user_age_phone_ad ON tb_user(age ASC, phone DESC)），这样查询就能完全通过索引来排序，而无需进行 Filesort。

  2. 使用覆盖索引：尽量使用 覆盖索引，即查询的字段都在索引中，这样可以避免回表查询，提高性能。

  3. 调整排序缓冲区大小：如果查询涉及大数据量的排序操作，并且不能避免 Filesort，可以考虑增大排序缓冲区的大小（sort_buffer_size）。默认大小是 256KB，可以根据实际需求增大，但也要注意，增加内存会影响 MySQL 的整体性能。

升序/降序联合索引结构图示:

img

img

由上述的测试,我们得出order by优化原则：

  • 根据排序字段创建适当的索引。
  • 使用联合索引时，遵循最左前缀法则。
  • 升序/降序联合索引结构：对于混合升降序的排序字段，创建相应的索引结构。
  • 在大数据量排序时，适当调整 sort_buffer_size 大小。

4.group by优化

GROUP BY 用于按指定字段对数据进行分组。这个操作在数据量较大时，可能会非常耗时。通过合理设计索引，可以显著提高 GROUP BY 的性能。

  • 在分组操作时，可以通过索引来提高效率
  • 分组操作时，索引的使用也是满足最左前缀法则的

img

如索引为idx_user_pro_age_stat，则句式可以是select ... where profession order by age，这样也符合最左前缀法则

所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：
A. 在分组操作时，可以通过索引来提高效率。
B. 分组操作时，索引的使用也是满足最左前缀法则的

5.limit优化

  • 当使用 LIMIT 时，尤其是在进行大范围分页查询时，查询的性能可能会受到显著影响。特别是 LIMIT 2000000, 10 这种查询，MySQL 需要先扫描前 2000000 条记录，然后丢弃前 2000000 条数据，再返回需要的 10 条数据。

优化方案：

覆盖索引：通过创建覆盖索引来减少全表扫描，从而提高分页查询的效率。特别是可以使用主键索引直接进行排序和查询。

1
2
3
4
5
-- 查询耗时较长
SELECT * FROM tb_sku LIMIT 9000000, 10;

-- 通过覆盖索引优化查询，直接使用主键索引进行排序
SELECT id FROM tb_sku ORDER BY id LIMIT 9000000, 10;

此时，由于 id 是索引的一部分，查询只会扫描索引，提高了效率。

使用连接查询：如果 LIMIT 的子查询中包含 ORDER BY，并且查询非常耗时，可以通过连接查询来优化：

1
2
3
4
-- 通过连表查询即可实现第一句的效果，并且能达到第二句的速度
SELECT * FROM tb_sku AS s
JOIN (SELECT id FROM tb_sku ORDER BY id LIMIT 9000000, 10) AS a
ON s.id = a.id;

6.count优化

  • 在执行 COUNT 查询时，MyISAM 引擎可以直接从表的元数据中返回总行数，效率非常高。InnoDB 引擎则需要遍历整个表来逐行统计，因此效率相对较低。

优化方案：自己计数优化，如创建key-value表存储在内存或硬盘，或者是用redis

count的几种用法：

  • 如果count函数的参数（count里面写的那个字段）不是NULL（字段值不为NULL），累计值就加一，最后返回累计值
  • 用法：count(*)、count(主键)、count(字段)、count(1)
  • count(主键)跟count()一样，因为主键不能为空；count(字段)只计算字段值不为NULL的行；count(1)引擎会为每行添加一个1，然后就count这个1，返回结果也跟count()一样；count(null)返回0

各种用法的性能：

  • count(主键)：InnoDB引擎会遍历整张表，把每行的主键id值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加（主键不可能为空）
  • count(字段)：没有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加；有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加
  • count(1)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一层，放一个数字 1 进去，直接按行进行累加
  • count(*)：InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加

按效率排序：count(字段) < count(主键) < count(1) < count()，所以尽量使用 count()

7.update优化（避免行锁升级为表锁）

在 InnoDB 存储引擎中，行锁是基于索引的，只有索引能够指向的数据行才会被锁定。如果查询不使用索引，行锁可能会升级为 表锁，导致性能严重下降。

优化方法：

  • 确保 UPDATE 查询的条件字段上有索引。如果没有索引，MySQL 会进行全表扫描，并对所有数据行加锁，性能极差。

如以下两条语句：
update student set no = '123' where id = 1;，这句由于id有主键索引，所以只会锁这一行；
update student set no = '123' where name = 'test';，这句由于name没有索引，所以会把整张表都锁住(行锁变表锁)进行数据更新，解决方法是给name字段添加索引

总结：更新某个字段是一定要走索引，否则走全表扫描会变成表级锁

8.SQL优化总结

img

4.视图/存储过程/触发器

1.视图

视图(View)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
创建视图：
CREATE [OR REPLACE] VIEW 视图名称(列名列表)】AS SELECT语句[WITH[CASCADED|LOCAL] CHECK OPTION]

查看创建视图语句：SHOW CRETE VIEW 视图名称;

查看视图数据:SELECT*FROM 视图名称…;
修改视图
--方式一
CREATE [OR REPLACE]VIEW 视图名称(列名列表)AS SELECT语句[WITH[CASCADEDLLOCAL] CHECK OPTION
--方式二
ALTER VEW 视图名称(列名列表)AS SELECT语句[WITH[CASCADED|LOCAL]CHECK OPTION]

删除视图
DROP VIEW [IF EXISTS]视图名称[,视图名称]

例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建视图
create or replace view stu_v_1 as select id, name from student where id <= 10;

-- 查询视图
show create view stu_v_1;
select * from stu_v_1;

-- 修改视图
create or replace view stu_v_1 as select id, name, no from student where id <= 10;
alter view stu_v_1 as select id, name from student where id <= 10;

-- 删除视图
drop view if exists stu_v_1;/*  */

检查选项

视图的检查选项：

  • 在创建视图时使用 WITH CHECK OPTION 子句，意味着当进行 插入、更新、删除 操作时，MySQL 会检查操作的数据是否符合视图定义中的条件。
  • 如果数据不符合视图中的条件，将被拒绝操作。例如，在视图中定义了一个过滤条件，只有符合该条件的数据才能插入或更新。

为了确定检查的范围，mysql 提供了两个选项:CASCADED 和 LOCAL，默认值为CASCADED。

cascaded（默认选项）：

含义：当使用 CASCADED 时，MySQL 会检查所有依赖视图中的规则，确保所有条件都满足才能进行数据插入或更新。

工作原理：如果视图 A 依赖于视图 B，并且 B 依赖于视图 C，那么在插入或更新数据时，MySQL 会检查 A、B 和 C 中的规则，只有满足所有视图的条件，操作才能成功。

示例：假设视图 A 依赖视图 B，而视图 B 又依赖视图 C，使用 CASCADED 时，插入操作会依次检查 A、B、C 中的约束条件，确保数据符合所有视图的定义。

img

local：

含义：当使用 LOCAL 时，MySQL 只会检查直接依赖的视图中的条件，而不去检查视图依赖链中的其他视图。

工作原理：如果视图 A 依赖视图 B，使用 LOCAL 时，插入或更新数据时只会检查视图 A 和 B 中的条件，而不检查视图 C 中的规则。

示例：

  • 假设视图 A 依赖视图 B，但 B 又依赖视图 C，使用 LOCAL 时，插入操作会只检查 A 和 B 中的约束条件，C 中的条件不会被检查。

img

总结这两者的区别

选项 含义 检查范围 操作示例
CASCADED 级联检查所有依赖视图的规则（包括多层视图依赖） 会依次检查所有依赖视图中的约束条件，必须符合所有视图的定义 插入数据时，MySQL 会检查视图 A、B 和 C 的所有条件
LOCAL 仅检查直接依赖的视图规则，忽略层级更深的视图依赖 只检查直接依赖的视图中的约束条件，忽略其他依赖层次的规则 插入数据时，仅检查视图 A 和 B 的条件，不检查 C

local也会查询上一个视图，区别是上一个视图没有给with check就不查了，但cascaded会查

选择合适的选项：

  • CASCADED 适用于需要严格约束的场景，确保每一层依赖的视图都符合条件。
  • LOCAL 更适用于对某些视图的直接操作时，不需要层层级联检查的情况，能够减少计算开销。

更新及作用

视图的更新：

可更新视图的条件：

  • 视图的行与基础表中的行必须存在 一对一的关系，也就是说，视图中的每一行都需要和基础表的对应行相匹配。

  • 如果视图中包含以下任意一种情况，那么该视图将无法更新：

    1. 聚合函数（如 SUM()、MIN()、MAX()、COUNT() 等）
    2. DISTINCT 关键字
    3. GROUP BY 子句
    4. HAVING 子句
    5. UNION 或 UNION ALL 操作

    这些操作会使得视图的结果集不再是单行与基础表的直接映射，因此无法进行更新操作。

作用：

1. 简化查询操作：

  • 视图可以将复杂的查询逻辑封装成简单的视图，用户无需每次都编写复杂的 SQL 语句。
  • 比如，常用的查询可以通过定义视图来简化，使得后续的操作更方便。

2. 提高数据安全性：

  • 使用视图可以限制用户访问数据库的特定列或行，确保用户只能查看他们授权查看的数据。
  • 例如，你可以创建视图来屏蔽敏感数据（如手机号、邮箱等），用户只能看到必要的字段。

3. 数据独立性：

  • 视图帮助用户屏蔽了底层表结构的变化。即使基础表的结构发生了变化，只要视图保持不变，应用程序或用户查询视图时不受影响。
  • 视图提供了数据的抽象层，帮助用户忽视底层数据库的具体实现细节。

视图案例

案例 1：隐藏敏感信息

  • 在这个案例中，我们创建了一个视图 tb_user_view，该视图只展示了 tb_user 表的基本字段，隐藏了敏感信息（如手机号和邮箱）。
1
2
3
CREATE VIEW tb_user_view AS 
SELECT id, name, profession, age, gender, status, createtime 
FROM tb_user;

案例 2：简化多表查询

  • 这个例子展示了如何通过视图简化多表联查。在实际业务中，查询学生选修的课程可能涉及多个表的联合查询。通过创建一个视图 tb_stu_course_view，我们可以简化后续查询操作。
1
2
3
4
CREATE VIEW tb_stu_course_view AS 
SELECT s.name AS student_name, s.no AS student_no, c.name AS course_name
FROM student s, student_course sc, course c
WHERE s.id = sc.studentid AND sc.courseid = c.id;

此后，每次查询学生选修的课程，只需执行以下查询即可：

1
SELECT * FROM tb_stu_course_view;

通过这种方式，我们避免了每次都编写复杂的联表查询，提升了开发效率和查询性能。

2.存储过程

存储过程 是一段事先编译并存储在数据库中的 SQL 语句集合。调用存储过程可以提高应用程序的性能，并减少数据库和应用程序之间的数据传输。存储过程的主要思想是 封装 和 重用 SQL 代码。

存储过程思想上很简单，就是数据库 SOL语言层面的代码封装与重用。

特点：

  • 封装与复用：将常用的 SQL 语句封装到存储过程里面，方便多次调用。
  • 接收参数与返回数据：存储过程可以接收输入参数，也可以返回输出结果。
  • 减少网络交互：由于存储过程直接在数据库中执行，可以减少应用与数据库之间的数据传输，提升效率。

基本语法

创建存储过程：

1
2
3
4
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    -- SQL 语句
END;
  • CREATE PROCEDURE 用于创建存储过程。
  • 参数列表 是存储过程可以接收的输入参数，参数的使用在存储过程中非常重要，可以通过它传递数据。

调用存储过程：

1
CALL 存储过程名称([参数]);
  • CALL 用于执行已创建的存储过程。

查看存储过程：

1
2
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx';
SHOW CREATE PROCEDURE 存储过程名称;
  • INFORMATION_SCHEMA.ROUTINES 表提供有关存储过程的元数据。
  • SHOW CREATE PROCEDURE 可以查看存储过程的定义。

删除存储过程：

1
DROP PROCEDURE [IF EXISTS] 存储过程名称;
  • DROP PROCEDURE 用于删除存储过程，如果存储过程不存在，可以加 IF EXISTS 避免错误。

例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建存储过程 p1，返回学生表的记录数
CREATE PROCEDURE p1()
BEGIN
  SELECT COUNT(*) FROM student;
END;

-- 调用存储过程 p1
CALL p1();

-- 查看存储过程 p1 的定义
SHOW CREATE PROCEDURE p1;

-- 删除存储过程 p1
DROP PROCEDURE IF EXISTS p1;

注意:

  • 在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。

变量

存储过程可以使用 系统变量、用户定义变量 和 局部变量。

系统变量

系统变量由 MySQL 提供，分为 全局变量 (GLOBAL) 和 会话变量 (SESSION)，用于管理 MySQL 服务器的配置和状态。

查看系统变量

1
2
3
SHOW [SESSION |GLOBAL] VARIABLES ; --查看所有系统变量
SHOW[SESSION|GLOBAL] VARIABLES LIKE'; --可以通过LKE模糊匹配方式查找变量
SELECT @@[SESSION|GLOBAL]系统变量名; -- 查看指定变量的值

设置系统变量：

1
SET SESSION autocommit = 1;

例子：

1
2
3
4
5
6
7
8
9
10
11
-- 变量：系统变量
-- 查看系统变量
show session variables;
show session variables like 'auto%';
show glabal variables like 'auto%';
select @@global.autocommit;

-- 设置系统变量
set session autocommit = 1;
insert intto course(id, name) values (6, 'ES');
set global auto commit = 0;

注意：

  • 如果没有指定 session / global，默认 session，会话变量
  • myesql 服务器重启之后，所设置的全局参数会失效，要想不失效，需要更改/etc/my.cnf 中的配置。
用户定义变量

用户定义变量是由用户定义的临时变量，它们不需要预先声明，且作用域为当前数据库连接。用户变量通过 @ 符号来使用。

赋值：

1
2
3
4
5
6
7
SET @var name = expr [, @var_name = expr]...;
SET @var name := expr [, @var_name := expr]...;

SELECT @var name := expr , @var name := expr ...;
SELECT 字段名 INTO @var_name FROM 表名;

SELECT @var_name;

例子：

1
2
3
4
5
6
7
8
9
10
11
12
-- 变量：用户变量
-- 赋值
set @myname = 'itcast';
set @myage := 10;

select @mycolor := 'red';
select count(*) into @mycount from tb_user;

-- 使用
select @myname, @myage, @mycolor, @mycount;

select @abc; -- 输出为NULL

注意：

用户定义的变量无需对其进行声明或者初始化，只不过获取到的值为 NULL。

局部变量

局部变量是存储过程内部使用的变量，它们的作用范围仅限于存储过程的 BEGIN ... END 块。在使用前，必须通过 DECLARE 声明。

声明：

1
DECLARE 变量名 变量类型 [DEFAULT..];

赋值：

1
2
3
4
5
SET 变量名=值;

SET 变量名:=值;

SELECT 字段名 INTO 变量名 FROM 表名 ...;

例子：

1
2
3
4
5
6
7
8
9
10
-- 使用局部变量存储学生人数
CREATE PROCEDURE p2()
BEGIN
  DECLARE stu_count INT DEFAULT 0;
  SELECT COUNT(*) INTO stu_count FROM student;
  SELECT stu_count;
END;

-- 调用存储过程 p2
CALL p2();

if 判断

语法：

1
2
3
4
5
6
7
IF 条件1 THEN
    -- 执行逻辑
ELSEIF 条件2 THEN  -- 可选
    -- 执行逻辑
ELSE  -- 可选
    -- 执行逻辑
END IF;

案例：

img

1
2
3
4
5
6
7
8
9
10
11
12
13
create procedure p3()
begin
  declare score int default 58;
  declare result varchar(10);
  if score >= 85 then
    set result :='优秀';
  elseif score >= 60 then
    set result :='及格';
  else
    set result :='不及格';
  end if;
  select result;
end;

说明：此存储过程根据传入的 score 来判断成绩并返回对应的结果（如：优秀、及格、不及格）。

参数（in, out, inout)

类型 含义 备注
IN 输入参数，调用时传入值，默认类型 默认
OUT 输出参数，返回计算结果
INOUT 即可以作为输入，也可以作为输出参数

用法：

1
2
3
4
CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
    -- SQL语句
END :

案例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 根据传入(in)参数score，判定当前分数对应的分数等级，并返回(out)
-- score >= 85分，等级为优秀。
-- score >= 60分 且 score < 85分，等级为及格
-- score < 60分，等级为不及格。
create procedure p3(in score int, out result varchar(10))
begin
  if score >= 85 then
    set result :='优秀';
  elseif score >= 60 then
    set result :='及格';
  else
    set result :='不及格';
  end if;
  select result;
end;

-- 将传入的200分制的分数，进行换算，换算成百分制，然后返回分数 --> inout
create procedure p5(inout score double)
begin
  set score := score * 0.5;
end;

set @score = 198;
call p5(score);
select @score;

说明：在第一个例子中，存储过程根据输入的 score 判断等级并通过 OUT 参数返回结果；在第二个例子中，使用 INOUT 参数进行成绩的转换，既传入一个值，又返回一个更新后的值。

case

**语法一：**基于值的 CASE：

1
2
3
4
5
CASE case value
  WHEN when_value1 THEN statement_list1
  [WHEN when_value2 THEN statement_list2]...
  [ELSE statement_list ]
END CASE;

**语法二：**基于条件的 CASE：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CASE
  WHEN search_conditionl THEN statement_list1
  WHEN search_condition2 THEN statement_list2]...
  [ELSE statement_list]
END CASE;
-- case
-- 根据传入的月份，判定月份所属的季节(要求采用case结构)
-- 1-3月份，为第一季度
-- 4-6月份，为第二季度
-- 7-9月份，为第三季度
-- 10-12月份，为第四季度

create procedure p6(in month int)
begin 
  declare result varchar(10);
  case 
    when month >= 1 and month <= 3 then
      set result := '第一季度';
    when month >= 4 and month <= 6 then
      set result := '第二季度';
    when month >= 7 and month <= 9 then
      set result := ' 第三季度';
    when month >= 10 and month <= 12 then
      set result := '第四季度';
    else
     set result := '非法参数';
  end case;

  select concat('你输入的月份为：', month, '，所属季度为：', result);
end;

说明：此存储过程使用 CASE 来根据输入的 month 参数判断并返回该月份所属的季度。

循环

while

while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。

语法：

1
2
3
4
#先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
  SOL逻辑...
END WHILE;

案例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- while计算从1累加到 n 的值，n 为传入的参数值。
-- A.定义局部变量，记录累加之后的值;
-- B.每循环一次，就会对 n 进行减1，如果 n 减到0，则退出循环

create procedure p7(in n int)
begin
  declare total int default 0;
  
  while n>0 do
    set total := total + n
    set n:=n-1;
  end while;
  
  select total;
end;
call p7( n: 100);
repeat

repeat是有条件的循环控制语句,当满足条件的时候退出循环。

与 while 区别：

与 WHILE 循环的主要区别在于，REPEAT 会先执行一次循环体，再判断条件是否满足，直到条件为 TRUE 才退出。

语法：

1
2
3
4
5
#先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
  SOL逻辑.
  UNTIL 条件
END REPEAT;

案例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- while计算从1累加到 n 的值，n 为传入的参数值。
-- A.定义局部变量，记录累加之后的值;
-- B.每循环一次，就会对 n 进行减1，如果 n 减到0，则退出循环

create procedure p8(innint)
begin
  declare total int default 0;
  
  repeat
    set total := total + n;
    set n := n - 1;
  until n <= 0
  end repeat;
  
  select total;
end;

call p8( n: 10);
call p8( n: 100);

说明：REPEAT 循环在执行一次逻辑后再检查条件，直到条件满足才退出。

loop

LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用。

  1. LEAVE:配合循环使用，退出循环。
  2. ITERATE:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。
1
2
3
4
5
6
[begin label:] LOOP
  SQL逻辑..
END LOOP [end label];

LEAVE label;  -- 退出指定标记的循环体
ITERATE label;-- 直接进入下一次循环

**案例：**计算从 1 到 n 之间的偶数累加值：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- loop 计算从1到n之间的偶数累加的值，n为传入的参数值。
-- A.定义局部变量，记录累加之后的值;
-- B.每循环一次，就会劝进行-1，如果n减到0，则退出循环。------> leave xx
-- C.如果当次累加的数据是奇数，则直接进入下一次循坏。-------> iterate xx

create procedure p10(in n int)
begin 
  declare total int defatult 0;

  sum: loop
    if n <= 10 then
      leave sum;
    end if;

    if n %2 = 1 then
      set n := n - 1;
      iterate sum;
    end if;

    set total := total + n;
    set n := n - 1;
  end loop sum;

  select total;
end;

说明：LOOP 通过 LEAVE 跳出循环，ITERATE 跳过当前循环并进入下一次循环。这里的例子是计算从 1 到 n 之间的偶数的和。

游标-cursor

游标是一个用于存储查询结果集的数据结构，允许在存储过程或函数中对结果集进行逐行处理。游标的使用通常包括声明游标、打开游标、获取数据和关闭游标。与常规的 SQL 查询不同，游标可以逐行获取查询结果，使得复杂的逻辑操作可以通过编程方式逐步实现

通俗点讲：类似于 c 语言中的结构体，java 中的实体类。

声明游标

1
DECLARE 游标名称 CURSOR FOR 查询语句;

打开游标：

1
OPEN 游标名称;

获取游标记录

游标获取查询结果集中的一行数据，将其存储到变量中。

1
FETCH 游标名称 INTO 变量[,变量];

关闭游标：

1
CLOSE 游标名称;

img

**案例：**通过游标遍历数据并插入到新表

假设有一个用户表 tb_user，我们希望根据传入的参数 uage 查询年龄小于等于该值的所有用户，并将他们的姓名和专业插入到一个新表 tb_user_pro 中。

img

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 游标
-- 根据传入的参数uage，来查询用户表tb_user 中， 所有的用户年龄小于uage的用户姓名（name)和专业（profession），
-- 并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。
-- 逻辑:
-- A.声明游标，存储查询结果集-
-- B.准备:创建表结构
-- C.开启游标-
-- D.获取游标中的记录
-- E.插入数据到新表中-
-- F.关闭游标

create procedure p11(in uage int)
begin 
  declare uname varchar(100);
  declare upro varchar(100);
  declare u_cursor cursor for select name, profession from tb_user where age <= uage;

  drop table if exists tb_user_pro;
  create table if not exists tb_user_pro(
    id int primary key auto_increment,
    name varchar(100),
    profession varchar(100)
  );
  
  open u_cursor;
  while true do
    fetch u_cursor into uname,upro;
    insert into tb_user_pro values(null, uname, upro);
  end while;
  close u_cursor;
end;
  • DECLARE uname VARCHAR(100);：声明用于存储查询结果中 name 字段的变量。
  • DECLARE u_cursor CURSOR FOR ...：声明游标 u_cursor，它将执行查询以选择符合条件的 name 和 profession 字段。
  • OPEN u_cursor;：打开游标，开始获取查询结果。
  • FETCH u_cursor INTO uname, upro;：逐行获取游标中的数据，并存储在变量 uname 和 upro 中。
  • INSERT INTO tb_user_pro ...：将每一行获取的数据插入到 tb_user_pro 表中。
  • CLOSE u_cursor;：操作完成后，关闭游标以释放资源。

条件处理程序-handler

条件处理程序（Handler）用于在 SQL 代码执行过程中，当遇到特定的错误或状态时，采取相应的行动。它可以用于处理异常、警告或其他状态码，并决定程序的执行流程。常见的操作包括：

  • 继续执行（CONTINUE）
  • 退出程序（EXIT）

通过使用条件处理程序，存储过程可以在出现错误或特定状态时进行处理，避免程序中断或错误传播。

语法：

1
2
3
4
5
6
7
8
9
DECLARE handler action HANDLERFOR condition value l, condition value.... statement;
handler action
  CONTINUE: 继续执行当前程序
  EXIT: 终止执行当前程序
condition value
  SOLSTATE sqlstate_value:状态码，如 02000
  SQLWARNING:所有以01开头的SQLSTATE代码的简写
  NOT FOUND:所有以02开头的SOLSTATE代码的简写
  SOLEXCEPTION:所有没有被SOLWARNING 或 NOT FOUND捕获的SOLSTATE代码的简写

img

**案例：**使用条件处理程序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
create procedure p11(in uage int)
begin 
  declare uname varchar(100);
  declare upro varchar(100);
  declare u_cursor cursor for select name, profession from tb_user where age <= uage;
  
  -- 监控到02000的状态码后，关闭游标后执行exit退出操作。
  declare exit handler for not found close u_cursor; 
  -- 创建新表存储查询结果
  drop table if exists tb_user_pro;
  create table if not exists tb_user_pro(
    id int primary key auto_increment,
    name varchar(100),
    profession varchar(100)
  );
   -- 打开游标
  open u_cursor;
  -- 循环遍历游标，逐行插入数据
  while true do
    fetch u_cursor into uname,upro;
    insert into tb_user_pro values(null, uname, upro);
  end while;
  -- 关闭游标
  close u_cursor;
end;

游标 u_cursor：查询 tb_user 表中年龄小于或等于 uage 的用户的姓名和专业。

条件处理程序：使用 DECLARE EXIT HANDLER FOR NOT FOUND CLOSE u_cursor;，这表示如果游标无法找到更多记录（即 NOT FOUND 状态），则关闭游标并退出。

存储过程流程：

  1. 创建新表：删除可能存在的 tb_user_pro 表，并创建新表存储查询结果。
  2. 打开游标：通过 OPEN 打开游标，开始从 tb_user 表中获取数据。
  3. 获取数据：使用 FETCH 获取游标中的每一行数据，并将数据插入 tb_user_pro 表中。
  4. 关闭游标：当没有更多记录时，NOT FOUND 状态会被触发，游标被关闭，存储过程结束。

3.存储函数

存储函数与存储过程类似，都是存储在数据库中的一段 SQL 代码。存储函数的特点是，它必须有返回值。存储函数通常用于执行一些复杂的计算或数据转换，并返回计算结果。

存储函数的特点：

  • 替代存储过程：在可以使用存储函数的地方，也可以使用存储过程，通常存储过程比存储函数更灵活。
  • 有返回值：存储函数必须通过 RETURN 语句返回一个值。
  • 参数：存储函数的参数只能是 IN 类型（输入参数），不能有输出参数或输入输出参数。
  • 较少使用：存储函数的应用场景较少，通常在数据库中需要一些计算或转换时使用。

语法：

1
2
3
4
5
6
CREATE FUNCTION 存储函数名称([ 参数列表 ])
RETURNS type [characteristic ...]
BEGIN
  -- SQL语句
  RETURN ...;
END ;

characteristic说明：

  • DETERMINISTIC：表示相同的输入参数总是产生相同的结果。如果指定了这个特性，MySQL 会进行优化，假定函数的结果是可以缓存的。
  • NO SQL：表示存储函数不包含任何 SQL 语句，仅进行计算或逻辑操作。
  • READS SQL DATA：表示存储函数包含了读取数据的 SQL 语句，但不包含对数据的写操作。
  • MODIFIES SQL DATA：表示存储函数包含了修改数据的 SQL 语句。

案例：

1
2
3
4
5
6
7
8
9
10
11
12
create function fun1(n int)
returns int deterministic
begin
  declare total int default 0;

  while n > 0 do 
    set total := total + n;
    set n := n - 1;
  end while;

  return total;
end;

存储过程（Stored Procedure）与存储函数（Stored Function）的主要区别：

特性 存储过程（Stored Procedure） 存储函数（Stored Function）
返回值 没有返回值 必须有返回值
参数类型 可以是 IN、OUT 或 INOUT 类型 只能是 IN 类型
调用方式 可以在 SQL 语句中调用（例如 CALL） 只能在 SQL 语句中作为表达式调用
作用范围 用于执行复杂的业务逻辑或数据操作 用于执行计算和转换，返回计算结果
使用场景 更广泛，适用于大部分数据库操作和业务逻辑的封装 通常用于简单的计算和数据转换，功能较为单一
执行的语句 存储过程中可以执行 DML 语句（例如 INSERT、UPDATE） 只能执行读取数据的语句，不能修改数据库数据
调用结果 调用后没有直接的返回结果 调用后必须有一个返回值
事务控制 可以包含事务控制（BEGIN、COMMIT、ROLLBACK） 不支持事务控制
  • 存储过程：适用于较复杂的数据库操作，可以包含多条 SQL 语句、事务控制和多种类型的参数。它的功能更广泛，适合封装复杂的业务逻辑。
  • 存储函数：相对简单，专注于计算和数据转换，具有返回值，通常用来执行单一的计算任务或作为查询的一部分嵌入 SQL 语句中。

4.触发器

触发器是与表有关的数据库对象，触发器是在对表执行 插入（INSERT）、更新（UPDATE） 或 删除（DELETE） 操作时自动执行的数据库对象。触发器的执行可以在数据操作之前（BEFORE）或之后（AFTER）进行。

常见用途：

  • 数据完整性：通过触发器可以保证数据的一致性，例如在插入数据时，自动生成字段值或校验数据。
  • 日志记录：记录对表的操作，如插入、更新或删除的具体内容，以便审计和追踪。
  • 自动化操作：在数据变动时，触发器可以执行一些自动的任务，如更新相关表的数据。

NEW 和 OLD：

  • OLD：表示修改或删除之前的记录数据。
  • NEW：表示将要或已经新增、更新的记录。
触发器类型 NEW 和 OLD
insert 型触发器 NEW 表示将要或者已经新增的数据
update 型触发器 OLD 表示修改之前的数据，NEW 表示将要或已经修改后的数据
delete 型触发器 OLD 表示将要或者已经删除的数据

触发器的基本语法语法

创建：

1
2
3
4
5
CREATE TRIGGER trigger name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl name FOR EACH ROW --行级触发器BEGIN
  trigger_stmt;
END;
  • trigger_name：触发器的名称。
  • BEFORE/AFTER：触发器执行的时机，BEFORE 在操作之前执行，AFTER 在操作之后执行。
  • INSERT/UPDATE/DELETE：指定触发的操作类型。
  • table_name：指定触发器关联的表。
  • FOR EACH ROW：表示该触发器是行级触发器（每行数据都会触发一次）。

查看：

1
SHOW TRIGGERS;

删除：

1
DROP TRIGGER [schema_name.]trigger_name; --如果没有指定 schema name，默认为当前数据库

案例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- 插入数据触发器
--该触发器在 tb_user 表插入数据后，向 user_logs 表插入日志记录，记录插入的内容。
create trigger tb_user_insert_trigger
  after insert on tb_user for each row
  begin 
  insert into user_logs(id, operation, operate_time, operate_id, operate_params)values
  (null, 'insert', now(), new.id, concat('插入的数据内容为：id=', new.id, ',name=', new.name, ', phone=', new.phone, ', email=', new.email, ', profession=', new.profession));
end;

-- 查看
show triggers;

-- 删除
drop trigger tb_user_insert_trigger;

-- 插入数据tb_user
insert into tb_user(id, name, phtone, email, profession, age, gender, status, createtime) values(25, '二皇子', '1880901212', 'erhuangzi@163.com', '软件工程', 23, '1', '1'1, now());

-- 修改数据触发器
--该触发器在 tb_user 表更新数据后，记录更新前后的数据变化到 user_logs 表中。
create trigger tb_user_update_trigger
  after update on tb_user for each row
  begin 
  insert into user_logs(id, operation, operate_time, operate_id, operate_params)values
  (null, 'update', now(), new.id, 
   concat('更新之前的数据：id=', old.id, ',name=', old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession,
    '更新之后的数据：id=', new.id, ',name=', new.name, ', phone=', new.phone, ', email=', new.email, ', profession=', new.profession));
end;

update tb_user set age = 32 where id = 23;
update tb_user set age = 32 where id <= 5; -- 触发器为行级触发器，所以更改几行数据则出发几次，该语句出发5次

-- 删除数据触发器
--该触发器在 tb_user 表删除数据后，记录删除的数据内容到 user_logs 表。
create trigger tb_user_delete_trigger
  after delete on tb_user for each row
  begin 
  insert into user_logs(id, operation, operate_time, operate_id, operate_params)values
  (null, 'insert', now(), old.id, 
   concat('删除之前的数据：id=', new.id, ',name=', old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession));
end;

delete from tb_user where id = 26;

5.视图/存储过程/触发器 小结

img

5.锁

1.概述

介绍：

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源(CPU、RAM、I/0)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

分类：

MySQL中的锁，按照锁的粒度分，分为一下三类：

  1. 全局锁：锁定数据库中的所有表。
  2. 表级锁：每次操作锁住整张表。
  3. 行级锁：每次操作锁住对应的行数据。

2.全局锁

介绍：

全局锁是对整个数据库实例进行的加锁，一旦加锁，数据库的所有表都将被锁定，整个数据库实例进入只读状态。此时，所有的 DML（数据操作语言） 和 DDL（数据定义语言） 语句（如 INSERT、UPDATE、DELETE、ALTER、DROP 等）都会被阻塞，直到释放全局锁。

典型使用场景：

  • 全库逻辑备份：当需要对整个数据库进行一致性的逻辑备份时，通常会使用全局锁来确保数据的一致性。
  • 保证数据完整性：通过加锁，确保备份过程中数据库中的数据不会被其他事务修改，从而避免数据不一致。

基本操作：

1
2
使用全局锁：flush tables with read lock
释放全局锁：unlock tables

为什么全库逻辑备份，就需要加全就锁呢？

img

A. 我们一起先来分析一下不加全局锁，可能存在的问题。
假设在数据库中存在这样三张表: tb_stock 库存表，tb_order 订单表，tb_orderlog 订单日志表。

img

  • 在进行数据备份时，先备份了tb_stock库存表。
  • 然后接下来，在业务系统中，执行了下单操作，扣减库存，生成订单（更新tb_stock表，插入tb_order表）。
  • 然后再执行备份 tb_order表的逻辑。
  • 业务中执行插入订单日志操作。
  • 最后，又备份了tb_orderlog表

此时备份出来的数据，是存在问题的。因为备份出来的数据，tb_stock表与tb_order表的数据不一致(有最新操作的订单信息,但是库存数没减)。
那如何来规避这种问题呢? 此时就可以借助于MySQL的全局锁来解决。

B. 再来分析一下加了全局锁后的情况

img

对数据库进行进行逻辑备份之前，先对整个数据库加上全局锁，一旦加了全局锁之后，其他的DDL、DML全部都处于阻塞状态，但是可以执行DQL语句，也就是处于只读状态，而数据备份就是查询操作。
那么数据在进行逻辑备份的过程中，数据库中的数据就是不会发生变化的，这样就保证了数据的一致性和完整性

特点：

优点：

  • 保证了备份过程中的数据一致性。由于数据库处于只读状态，所有的 DML 和 DDL 操作被阻塞，避免了在备份过程中数据的改变。

缺点：

  1. 主库备份：如果在主库上进行备份，整个备份期间都不能执行更新操作，业务将基本停滞。
  2. 从库备份：如果在从库上备份，从库不能执行主库同步过来的二进制日志（binlog），会导致主从复制延迟。

解决方法：

使用 --single-transaction 参数进行备份：

在使用 InnoDB 存储引擎时，可以通过在备份命令中加上 --single-transaction 参数来避免使用全局锁。该参数可以确保在备份时创建一个一致性的快照，从而不需要加全局锁。

1
mysqldump --single-transaction -uroot -p123456 itcast > itcast.sql（只适用于支持「可重复读隔离级别的事务」的存储引擎）

原理：

通过加上 --single-transaction 参数，MySQL 会启动一个新的事务，使用 可重复读（Repeatable Read）隔离级别，并从该事务读取一致性的快照数据。这样，备份过程中的数据读取不会受到其他事务的影响，保证数据的一致性，同时不阻塞其他操作。

适用条件：

  • --single-transaction 参数仅适用于 支持事务的存储引擎（如 InnoDB）。对于不支持事务的存储引擎（如 MyISAM），无法通过该方法实现一致性备份。

3.表级锁

表级锁是对整个表进行加锁操作。相较于行级锁（仅锁定特定的行），表级锁的锁粒度更大，锁定了整个表。表级锁的优点是实现简单，但它的缺点是当多个事务试图操作同一张表时，容易发生锁冲突，导致较低的并发度。

表级锁的类型：

  1. 表锁
  2. 元数据锁（meta data lock，MDL）
  3. 意向锁

表锁

对于表锁，分为两类：

  • 表共享读锁（read lock）

作用：读锁允许多个会话同时读取表中的数据，但阻止其他会话对表进行写操作。

特性：当表共享读锁被一个事务加锁后，其他事务只能读取该表，不能进行任何写操作（如 INSERT、UPDATE、DELETE）。

  • 表独占写锁（write lock）

作用：写锁允许当前会话对表进行读写操作，但会阻止其他会话对表进行任何操作，包括读和写。

特性：写锁是排他性的，意味着当一个事务持有写锁时，其他任何事务都无法访问该表，无论是读取还是修改。

读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

语法：

1
2
3
4
5
6
7
//表级别的共享锁，也就是读锁；
//允许当前会话读取被锁定的表，但阻止其他会话对这些表进行写操作。
lock tebles t_student read;

//表级锁的独占锁，也是写锁；
//允许当前会话对表进行读写操作，但阻止其他会话对这些表进行任何操作（读或写）。
lock tables t_stuent write;

释放所有锁：

1
unlock tables （会话退出，也会释放所有锁）

img

img

元数据锁

元数据锁（Metadata Lock, MDL） 是 MySQL 用于保护表的元数据一致性的锁机制。当进行 DML 操作（如 SELECT、INSERT 等）时，MySQL 会自动加上 MDL 锁。MDL 锁主要用于避免在执行 DML 操作时，表的结构发生变化（如 ALTER 操作），从而保证数据的正确性。

  • MDL 读锁：在对表进行 CRUD（创建、读取、更新、删除）操作时，MySQL 会自动加上 MDL 读锁，防止在操作过程中表结构发生变化。
  • MDL 写锁：在对表进行结构变更（如 ALTER 表）时，MySQL 会自动加上 MDL 写锁，防止其他操作修改表的结构。
对应 SQL 操作 锁类型 说明
LOCK TABLES xxx READ / WRITE SHARED_READ_ONLY / SHARED_NO_READ_WRITE 用于显式加锁，控制读写权限。
SELECT ... LOCK IN SHARE MODE SHARED_READ 允许其他事务共享读取，但不允许写操作。
INSERT / UPDATE / DELETE / SELECT... SHARED_WRITE 允许其他事务共享读取，但不允许修改。
ALTER TABLE EXCLUSIVE 与其他 MDL 锁互斥，保证表结构不被修改。

查看元数据锁：

1
2
select object_type,object_schema,object_name,lock_type,lock_duration 
from performance_schema.metadata_locks;

img

img

意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

  • 意向锁使得表级锁和行级锁之间的冲突减少。当事务对表中的某些行加锁时，其他事务可以通过意向锁快速判断是否可以对该表加表锁，避免了逐行检查的开销。

假如没有意向锁，客户端一对表加了行锁后，客户端二如何给表加表锁呢，来通过示意图简单分析一下：
首先客户端一，开启一个事务，然后执行DML操作，在执行DML语句时，会对涉及到的行加行锁。

img

当客户端二，想对这张表加表锁时，会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就会从第一行数据，检查到最后一行数据，效率较低。

img

有了意向锁之后 :
客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。

img

而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而
不用逐行判断行锁情况了。

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read)和独占表锁（lock tables … write）发生冲突

意向锁的目的是为了快速判断表里是否有记录被加锁。

分类

  • 意向共享锁(IS): 由语句select … lock in share mode添加 与表锁共享锁(read)兼容，与表锁排他锁(write)互斥。
  • 意向排他锁(IX): 由insert、update、delete、select…for update添加 与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

可以通过以下SQL，查看意向锁及行锁的加锁情况：

1
2
 select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from 
performance_schema.data_locks;

补充-自增锁（AUTO-INC锁）

我们将讨论 MySQL 中关于 AUTO_INCREMENT 锁 和 并发插入 的机制，尤其是在使用 innodb_autoinc_lock_mode 配置时，如何管理 AUTO_INCREMENT 列的插入，并且确保 多个会话并发执行插入 时生成的 ID 是有序且一致的。

AUTO_INCREMENT 锁

定义：在 MySQL 中，当我们向表中插入数据并使用 AUTO_INCREMENT 列时，MySQL 会对该列加锁，确保每次插入时生成的 ID 是唯一的且递增的。

问题：如果没有正确处理 AUTO_INCREMENT 锁的释放，就可能会导致并发插入时，生成的 ID 出现不一致或跳号的问题。

innodb_autoinc_lock_mode 参数

  • 作用

    ：

    1
    innodb_autoinc_lock_mode

    参数控制

    1
    AUTO_INCREMENT

    锁的行为，影响并发插入时 ID 的生成方式，主要有三种模式：

    • 模式 0：传统的 AUTO-INCREMENT 锁，执行插入时会锁定整个 AUTO_INCREMENT 列，其他会话的插入会被阻塞，直到当前事务完成。此模式下，每个插入的 ID 按顺序生成。
    • 模式 1：在插入时锁定表的 AUTO_INCREMENT 列，但只会在事务提交前产生锁，避免了插入时阻塞其他会话。
    • 模式 2：轻量级的 AUTO_INCREMENT 锁，执行插入时只锁定 AUTO_INCREMENT 列，而不需要锁定整个表的其他行，允许更多并发插入操作。

innodb_autoinc_lock_mode = 2 的行为

  • 在 innodb_autoinc_lock_mode = 2 模式下，MySQL 使用轻量级的锁，仅锁定 AUTO_INCREMENT 列，这样 插入操作会更快，并且 不同会话 可以同时进行插入操作。

img

场景描述：

  • Session A 和 Session B 同时插入数据到相同的表 t 和 t2。
  • Session A 执行插入操作（插入几行数据），而 Session B 在同一时刻执行插入 t2 表的操作。

执行步骤：

  • Session A 插入数据并获得 ID（如 1, 2, 3）。然后，它将这些数据插入到 t2 表。
  • Session B 插入两行数据，并获得 ID（如 1, 2）。接着，Session A 获得 ID 为 3。

分析：

  • 由于使用 innodb_autoinc_lock_mode = 2，两个 session 的插入操作是并行执行的，生成的 ID 虽然看起来是顺序的，但在不同 session 中，生成的 ID 可能会不连续。
  • 这是因为 MySQL 在每个会话中独立生成了一个 ID 并没有阻塞其他会话。

影响和解决方案

  • 通过 binlog_format = row 配置，并发插入生成不一致的 ID 会被避免，生成的 ID 会保持顺序和一致性。
  • 如果你希望插入的数据 ID 一致，并且没有跳号，可以选择 innodb_autoinc_lock_mode = 1 或 innodb_autoinc_lock_mode = 0，这将保证顺序性，但牺牲了一定的并发性能。

总结：

  1. AUTO_INCREMENT 锁 是 MySQL 用来确保 AUTO_INCREMENT 列值顺序和唯一性的一种机制，默认情况下会加锁整个表。
  2. innodb_autoinc_lock_mode 参数 控制着 AUTO_INCREMENT 锁的行为，提供了不同的锁粒度选择（传统锁、轻量级锁等），以平衡性能和数据一致性。
  3. 在高并发环境下，使用 innodb_autoinc_lock_mode = 2 可以提高插入性能，但会产生不连续的 ID。
  4. 通过合理选择锁模式，可以在 性能 和 数据一致性 之间找到平衡。

这些设置和概念有助于理解在 高并发插入 中如何优化 AUTO_INCREMENT 列的行为，避免性能瓶颈，并确保数据的准确性和一致性。

4.行级锁

行级锁是 MySQL 中对单行数据进行加锁的锁机制，它是 InnoDB 存储引擎支持的一种锁类型。行级锁允许多个事务并发地操作不同的行，因此在并发度上具有很高的性能。

行级锁的优点：

  • 并发度高：由于只锁定表中的一行数据，其他事务可以继续访问不同的行，因此行级锁的并发性非常高。
  • 锁粒度小：行级锁是最细粒度的锁，锁定的范围最小，因此发生锁冲突的概率最低。

行级锁的缺点：

  • 实现复杂：与表级锁相比，行级锁的实现较为复杂，因为需要管理每行数据的锁定和释放。
  • 潜在的死锁风险：由于多个事务可能同时对不同的行加锁，可能会引发死锁问题。

行级锁的类型

  1. 行锁(Record Lock):锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
  2. 间隙锁(GapLock):锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
  3. 临键锁(Next-Key Lock):行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

Record Lock（行锁）

Record Lock 称为记录锁，记录锁是对单行数据的锁定，防止其他事务在该行上执行更新或删除操作。记录锁是有共享锁（S锁）和排他锁（X锁）之分：

InnoDB实现了以下两种类型的行锁：

  1. 共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
  2. 排他锁(X)：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。
S(共享锁) X(排他锁)
S(共享锁) 兼容 冲突
X(排他锁) 冲突 冲突

行锁类型：

SQL 行锁类型 说明
insert，update，delete … 排他锁 自动加锁
select 不加任何锁
select … lock in share mode 共享锁 需要手动select之后加上lock in share mode
select … for update 排他锁 需要手动在select之后for update

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key锁进行搜索和索引扫描，以防止幻读。

  1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
  2. InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时 就会升级为表锁。

img

事务隔离级别与行锁的关系

REPEATABLE READ 隔离级别：

在 REPEATABLE READ 隔离级别下，InnoDB 会使用 Next-Key Lock 锁定行及其前面的间隙，从而防止 幻读 的问题。

  • 幻读：当一个事务读取某个范围的数据时，另一个事务可能会插入新数据到这个范围内，导致读取结果不一致。行锁和间隙锁的结合有助于避免这种情况。

READ COMMITTED 隔离级别：

在 READ COMMITTED 隔离级别下，行锁通常只在查询时锁定当前被选中的数据行，而不会加上间隙锁。因此，这种隔离级别下，事务可能会读取到其它事务已经提交的数据，导致 不可重复读。

演示：

img

img

img

img

img

查看意向锁及行锁的加锁情况：

1
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from peformance_schema.data_locks;

Gap Lock（间隙锁）

img

Next-Key Lock（临键锁）

img

默认情况下，InnODB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

  1. 索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁 。
  2. 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-keylock退化为间隙锁。
  3. 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

img

5.锁 小结

img

6.InnoDB引擎

1.逻辑存储结构

InnoDB的逻辑存储结构如下图所示:

img

1). 表空间(Tablespace）
表空间是 InnoDB 存储引擎 逻辑存储结构的最高层次。表空间负责存储数据库的表数据、索引、回滚段等内容。

  • 表空间的作用

    ：

    • 用于存储数据和索引等重要内容。
    • 如果启用了 innodb_file_per_table 参数（在 MySQL 8.0 中默认启用），每个表都有自己的表空间，即每个表会有一个单独的 .ibd 文件。这种方式下，表的数据、索引等信息存储在独立的文件中。
    • 在没有启用 innodb_file_per_table 时，所有表的存储都会存在一个共享的表空间文件（ibdata1），包含了所有的表数据。

表空间的特点：

  • 多个表空间：一个 MySQL 实例可以包含多个表空间，每个表可以有一个单独的表空间文件。
  • 可扩展性：表空间可以跨多个磁盘设备，支持数据库的扩展。

2). 段(Segment）
段是表空间中的一部分，用于组织和管理数据。InnoDB 中有三种类型的段：

  1. 数据段（Leaf node segment）

    ：

    • 数据段存储了表的实际数据，也就是 B+ 树的叶子节点。
    • InnoDB 是索引组织表（Index-Organized Table，IOT），数据段中的数据按主键排序，存储在 B+ 树的叶子节点中。

  2. 索引段（Non-leaf node segment）

    ：

    • 索引段存储了 B+ 树的非叶子节点，帮助定位数据页的实际位置。
    • 每个表都有一个主键索引段用于存储主键的索引信息。

  3. 回滚段（Rollback segment）

    ：

    • 回滚段用于存储事务的回滚日志（undo日志），它保证了事务的原子性和一致性。当一个事务回滚时，回滚段会用来恢复数据的原始状态。

段的作用：

用来管理多个 Extent（区）。每个段负责特定的任务，如存储数据或索引。
3). 区（Extent）
区是表空间的单元结构，每个区的大小为 1MB。一个区由多个页（Page）组成。区的作用是为了优化数据存储和提高查询效率。

  • 区的大小：默认每个区的大小是 1MB。
  • 每个区包含 64 个页，每个页的大小为 16KB，因此一个区包含 64 个连续的页。

区的特点：

区是 InnoDB 存储引擎管理的一个重要单位，它将数据分散到不同的页中，确保数据能按需快速访问。
4). 页（Page）
页是 InnoDB 存储引擎磁盘管理的最小单元。每个页的大小默认为 16KB。

页的作用：

行是存储在 InnoDB 中的基本数据单位，每一行代表一条记录。每行数据会包含两个隐藏字段：

  1. trx_id：每次对记录进行修改时，InnoDB 会把对应的事务 ID 赋值给该行的 trx_id 隐藏列，便于事务的管理和恢复。
  2. roll_pointer：当对记录进行修改时，InnoDB 会将旧的版本保存到 undo 日志 中，roll_pointer 就是指向该旧版本的指针，用于事务回滚操作。

行的存储：

  • 行数据会按行存储在页面中。InnoDB 会根据主键的顺序在 B+ 树的叶子节点中存储数据。

img

InnoDB 存储引擎的逻辑存储结构：

  1. 表空间：存储表数据、索引、回滚段等内容，可以有多个表空间。
  2. 段：每个表空间由不同的段组成，如数据段、索引段、回滚段等，用于管理多个 Extent。
  3. 区：区是表空间的单位，每个区的大小为 1MB，包含多个页。
  4. 页：是磁盘管理的最小单位，每个页默认大小为 16KB。
  5. 行：行是存储的基本单元，每个行包含两个隐藏字段：trx_id 和 roll_pointer，用于事务管理和回滚。

2.架构

img

内存架构

img

在左侧的内存结构中，主要分为这么四大块儿： Buffer Pool、Change Buffer、Adaptive
Hash Index、Log Buffer。 接下来介绍一下这四个部分

  1. Buffer Pool（缓冲池）

定义：

Buffer Pool 是 InnoDB 存储引擎中的一个关键内存区域，用于缓存从磁盘读取的数据页（如数据页、索引页、undo 页等）。通过将经常使用的数据加载到内存中，Buffer Pool 减少了磁盘 I/O 操作，从而提高了查询和更新的性能。

工作原理：

  • 数据缓存：Buffer Pool 中不仅缓存了数据和索引，还包括 undo 页、插入缓存、自适应哈希索引以及锁信息等。

  • 页管理

    ：缓冲池中的数据以

    Page 页为单位

    管理。根据数据的使用情况，页面分为以下几种类型：

    • Free page：空闲页，未被使用。
    • Clean page：已被使用但未修改的页。
    • Dirty page：已修改的页，与磁盘的数据不一致。

  • 刷新机制：当数据发生变化时，修改过的数据会被标记为 脏页，并定期刷新到磁盘以保持一致性。

缓冲池的重要性：

缓冲池是提高 InnoDB 性能的核心组件，它减少了与磁盘的交互，使得数据的读写速度大大提升。

  1. Change Buffer（更改缓冲区）

定义：

Change Buffer 是专门用于缓存二级索引（非唯一索引）数据的内存区域。当执行 INSERT、UPDATE 或 DELETE 等操作时，如果相关的索引页不在缓冲池中，InnoDB 会将这些更改先存储到 Change Buffer 中，而不是直接修改磁盘上的数据。

工作原理：

  • 延迟磁盘 I/O：Change Buffer 的最大优势是减少磁盘 I/O。对于二级索引的更新，如果索引页不在缓冲池中，操作不会立即写入磁盘，而是先写入 Change Buffer，待相关数据页被访问时，再将更改合并到缓冲池并刷新到磁盘。
  • 减少 I/O 操作：这种机制避免了频繁的磁盘操作，提升了性能，尤其是在二级索引更新频繁时。

应用场景：

Change Buffer 特别适用于非唯一二级索引，因为这些索引通常是随机插入和更新的，直接修改磁盘会产生大量 I/O 操作。

img

与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新
可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了
ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO。

  1. Adaptive Hash Index（自适应哈希索引）

定义：

自适应哈希索引是一种自动生成的索引，目的是优化缓冲池中数据的查询。虽然 InnoDB 没有原生支持哈希索引，但它提供了 自适应哈希索引 机制，通过监控对索引页的查询，自动在内存中创建哈希索引。

工作原理：

  • 哈希索引的优势：哈希索引在进行 等值查询 时通常比 B+ 树索引更高效，因为哈希索引只需要一次 I/O 操作，而 B+ 树可能需要多个匹配。
  • 自动生成：当系统检测到某些条件下哈希索引能提高查询性能时，会自动创建哈希索引，无需人工干预。
  • 限制：哈希索引不适用于范围查询或模糊匹配，因此它主要用于等值查询。

参数：

  • adaptive_hash_index：该参数用于控制自适应哈希索引的行为。
  • Log Buffer（日志缓冲区）

定义：

Log Buffer 用于缓存 redo log 和 undo log，即事务日志。在事务操作期间，修改的数据会首先记录到日志缓冲区，然后定期刷写到磁盘，确保数据的持久性和事务的原子性。

工作原理：

  • 日志缓存：Log Buffer 缓存待写入磁盘的日志，减少了磁盘写入的次数。默认情况下，日志缓冲区的大小为 16MB。

  • 刷新机制

    ：日志缓冲区的内容会定期刷新到磁盘，刷新策略根据参数

    1
    innodb_flush_log_at_trx_commit

    设置，控制何时将日志写入磁盘：

    • 0：每秒刷新一次。
    • 1：每次事务提交时刷新。
    • 2：每次事务提交后写入，但每秒刷新一次。

性能优化：

通过增加日志缓冲区的大小，可以减少磁盘 I/O 操作，尤其在需要频繁写入大量日志的事务中。

总结

InnoDB 内存架构的关键组件：

  1. Buffer Pool：缓存磁盘数据，减少 I/O 操作，提高性能。
  2. Change Buffer：缓存二级索引更新，延迟磁盘 I/O 操作，优化性能。
  3. Adaptive Hash Index：自动创建哈希索引，优化等值查询的性能。
  4. Log Buffer：缓存日志，减少磁盘 I/O 操作，确保事务日志的持久性。

整体架构的作用：

  • InnoDB 内存架构通过这些组件的协作工作，显著提高了数据库操作的效率，特别是在高并发环境下，优化了读写性能，减少了磁盘访问次数。
  • 通过合理配置这些内存组件，可以针对不同的工作负载进行优化，从而最大化 MySQL 的性能。

这些内存组件和机制构成了 InnoDB 存储引擎的性能优化基础，合理配置和使用这些机制是提升数据库性能的关键。

磁盘结构

接下来，再来看看InnoDB体系结构的右边部分，也就是磁盘结构：

img

  1. 系统表空间 (System Tablespace)

定义：

系统表空间是 InnoDB 存储引擎用来存储表和索引数据的地方。它通常用于存储多个表和索引数据，特别是在没有启用 innodb_file_per_table（每表一个表空间）时，所有的数据都存储在这个共享的系统表空间中。

主要特点：

  • 默认文件名通常为 ibdata1。
  • 存储数据字典、回滚日志（undo log）等元数据。
  • 配置：使用 innodb_data_file_path 参数来指定数据文件路径。

优点：

  • 在旧版本的 MySQL 中（如 5.x），所有数据和表都存储在系统表空间中，管理相对简单。

缺点：

随着数据量的增长，ibdata1 文件会变得越来越大，且不容易删除或清理。

  1. File-Per-Table Tablespaces

定义：

当启用 innodb_file_per_table 参数时，每个表会有一个独立的表空间文件来存储该表的数据和索引。这种方式使得每个表的数据都存储在独立的文件中，而不是存储在共享的 ibdata1 文件中。

主要特点：

  • 每个表有单独的 .ibd 文件来存储数据和索引。
  • 启用：在 MySQL 5.x 中，默认启用了该选项。
  • 配置：使用 innodb_file_per_table 参数来控制是否启用。

优点：

  • 更加灵活，可以单独管理每个表的存储空间，便于备份和迁移。
  • 表空间文件更容易管理，且不需要像系统表空间那样随着数据量的增长而膨胀。

缺点：

每个表都有一个独立的文件，可能会增加文件系统管理的负担。

  1. 通用表空间 (General Tablespaces)

定义：

通用表空间是一个独立的表空间，用于存储多个表的数据和索引。不同于系统表空间和每表一个表空间，它允许用户自定义表的存储位置。

A. 创建通用表空间

1
CREATE TABLESPACE ts_name  ADD  DATAFILE  'file_name' ENGINE = engine_name;

B. 在创建表时指定表空间

1
CREATE  TABLE  xxx ...  TABLESPACE  ts_name;

用途：

通用表空间可以用于存储多个表的数据，提供灵活的表空间管理。

  1. 撤销表空间 (Undo Tablespaces)

定义：

撤销表空间用于存储 undo log，帮助事务回滚。InnoDB 在初始化时会自动创建两个默认的 undo 表空间，初始大小为 16MB。

作用：

  • 每个事务开始时，都会在 undo 表空间中写入相应的记录，以便在事务回滚时恢复数据。
  • 存储事务的撤销日志，确保事务的 原子性。
  • 临时表空间 (Temporary Tablespaces)

定义：

临时表空间用于存储会话级的临时数据，如用户创建的临时表。

特点：

  • 包含 会话临时表空间 和 全局临时表空间。
  • 用于存储临时表和排序操作等，避免这些操作占用表空间。
  • 双写缓冲区 (Doublewrite Buffer Files)

定义：

双写缓冲区是 InnoDB 提供的一种机制，用于增强系统的可靠性。数据页从缓冲池写入磁盘时，首先会被写入到双写缓冲区。

作用：

  • 防止数据丢失：在系统崩溃或异常关机时，双写缓冲区保证了数据的一致性，因为即使发生崩溃，写入缓冲区的数据也不会丢失。
  • 恢复机制：通过双写缓冲区，InnoDB 能够在恢复过程中重新写回数据页，从而确保数据的可靠性。

img

  1. 重做日志 (Redo Log)

定义：

重做日志用于实现事务的持久性和恢复。它包括 redo log buffer 和 redo log 文件 两部分。

工作原理：

  • Redo log buffer：在内存中缓存 redo log 数据。
  • Redo log 文件：存储在磁盘中，记录已提交事务的修改，确保即使数据库崩溃，已经提交的事务仍然能够恢复。

特点：

  • 持久性：通过记录每个事务的修改操作，提供事务的持久性保障。
  • 循环写入：Redo log 文件采用循环写入方式，旧的日志会被新日志覆盖。

以循环方式写入重做日志文件，涉及两个文件：

img

总结：

InnoDB 存储引擎的磁盘结构：

  1. 系统表空间：存储数据字典、回滚日志等。
  2. 每表一个表空间：每个表有独立的存储文件，便于管理和备份。
  3. 通用表空间：可供多个表共享的存储空间。
  4. 撤销表空间：存储事务的撤销日志，保证事务的原子性。
  5. 临时表空间：用于存储会话级别的临时数据。
  6. 双写缓冲区：增强数据的可靠性，确保系统崩溃时的数据恢复。
  7. 重做日志：确保事务持久性和系统恢复。

后台线程

前面我们介绍了InnoDB的内存结构，以及磁盘结构，那么内存中我们所更新的数据，又是如何到磁盘中的呢？ 此时，就涉及到一组后台线程，接下来，就来介绍一些InnoDB中涉及到的后台线程。

img

在InnoDB的后台线程中，分为4类，分别是：Master Thread 、IO Thread、Purge Thread、Page Cleaner Thread。

  1. Master Thread（主线程）

功能：

  • 调度其他线程：作为核心线程，主线程负责协调其他后台线程的运行。
  • 数据异步刷新：主线程负责将 缓冲池 中的脏页（即已修改但未写入磁盘的数据）异步地刷新到磁盘，保证数据的一致性。
  • 脏页刷新：当缓冲池中的页被修改后，主线程会定期将这些脏页刷新到磁盘，以防止数据丢失。
  • 合并插入缓存：主线程还负责管理 插入缓冲（Insert Buffer），将待写入的数据合并，减少写入操作。
  • UNDO 页回收：当事务提交后，某些不再需要的 UNDO 页 会被回收。Undo 页用于实现事务的回滚操作。

作用：

主线程是 InnoDB 引擎的核心，承担了数据一致性、缓冲池管理和其他后台任务的调度，确保数据在内存和磁盘之间的同步。

  1. IO Thread（I/O 线程）

功能：

  • AIO（异步 I/O）处理：InnoDB 使用大量的异步 I/O 来处理磁盘的读写请求，IO 线程负责处理这些异步请求的回调操作。
  • 提高性能：通过 AIO，InnoDB 可以在进行磁盘操作时继续执行其他任务，避免 I/O 阻塞，显著提高数据库的并发性能。

img

我们可以通过以下的这条指令，查看到InnoDB的状态信息，其中就包含IO Thread信息。

1
show engine innodb status \G;

作用：

I/O 线程使得 InnoDB 在执行磁盘 I/O 操作时能够最大限度地利用系统资源，提高数据库的整体性能，特别是在处理大量数据读写时。

  1. Purge Thread（回收线程）

功能：

  • 确保空间回收：Purge 线程确保不再需要的 UNDO Log 被及时回收，以避免数据库中积累大量无用的数据。
  • 回收 UNDO Log：事务提交后，InnoDB 会生成 UNDO Log，用于事务回滚。在事务提交后，这些 UNDO Log 可能不再需要，Purge 线程会定期清理这些日志，以释放存储空间。

作用：

Purge 线程帮助回收不再使用的事务日志，保证 InnoDB 存储引擎的高效运行，并防止存储空间的浪费。

  1. Page Cleaner Thread（页面清理线程）

功能：

  • 协助主线程：Page Cleaner 线程的主要任务是协助主线程将脏页（修改后的数据页）刷新到磁盘。
  • 减轻主线程负担：它通过异步地将数据页写入磁盘，减少主线程的工作量，从而避免了由于脏页刷新而产生的阻塞，提高了数据库的响应速度。

作用：

Page Cleaner 线程是 InnoDB 内存管理中的重要一环，它帮助减少主线程的负担，提高系统的整体性能，避免了主线程在数据刷写过程中的阻塞。

总结：

InnoDB 引擎的后台线程：

  1. Master Thread：核心线程，负责协调和调度，异步刷新脏页，合并插入缓存，回收 undo 页。
  2. IO Thread：处理磁盘的异步 I/O 操作，提高数据库的 I/O 性能。
  3. Purge Thread：回收不再需要的 undo log，保持磁盘空间的高效使用。
  4. Page Cleaner Thread：协助主线程刷新脏页到磁盘，减轻主线程的工作压力。

这些后台线程共同工作，确保 InnoDB 引擎能够高效地管理内存、磁盘和事务，从而提供良好的性能和稳定性。在高并发环境下，它们能够显著提高数据库的响应速度和整体吞吐量。

3.事务原理

事务：

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

特征：

  • 原子性(Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性(Consistency) ：事务完成时，必须使所有的数据都保持一致状态。
  • 隔离性(lsolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

而对于这四大特性，实际上分为两个部分。 其中的原子性、一致性、持久化，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志。 而持久性是通过数据库的锁，加上MVCC来保证的。

特性原理分类图：

img

redo log 重做日志

Redo Log 是用来保证事务持久性的关键机制。它记录了 事务提交时的数据页物理修改，确保在系统崩溃后，事务的修改可以通过 Redo Log 恢复。

Redo Log 由两部分组成：

  1. Redo Log Buffer：存在内存中，临时存储待写入磁盘的日志数据。
  2. Redo Log File：存储在磁盘中，记录所有已提交事务的变更，供系统在崩溃后恢复数据。
Redo Log 的作用

Redo Log 解决了在内存中的 脏页 刷新到磁盘时出现错误的潜在问题。我们知道，在 InnoDB 中，数据会先写入内存的 缓冲池（Buffer Pool），然后才会定期刷新到磁盘。由于 刷新脏页到磁盘 是异步操作，可能会存在如下问题：

  • 事务提交成功后，数据尚未成功写入磁盘。
  • 如果在刷新过程中发生错误，事务提交成功但数据未持久化。

解决方案：

使用 Redo Log，即使在刷新脏页时发生错误，我们也能依靠 Redo Log 恢复数据，保证事务的持久性。Redo Log 记录了所有修改操作的物理日志，当系统崩溃后，能够根据 Redo Log 进行数据恢复。

Redo Log 的工作原理

事务的执行过程：

  1. 当事务对数据进行修改时，首先将 修改的记录 存储在 Redo Log Buffer 中。
  2. 在事务提交时，Redo Log Buffer 会被刷新到 Redo Log 文件 中。
  3. 如果数据写入缓冲池时出现错误，而刷新到磁盘的过程中发生崩溃，通过 Redo Log 可以将修改恢复，从而确保数据的持久性。

img

WAL（Write-Ahead Logging）机制：

WAL 是写前日志机制，即在实际写入数据之前，先将修改操作记录到日志中。这样即使在数据写入过程中发生崩溃或系统失败，我们可以使用日志恢复数据。

为什么先写入 Redo Log？

  • 顺序写效率高：与随机写相比，日志文件的顺序写入效率更高，因此采用 Redo Log 来保证数据持久性。磁盘的顺序写入比随机写要高效得多。
  • 避免阻塞：通过先将日志写入磁盘，避免了数据页在脏页刷写时可能出现的长时间阻塞。
Redo Log 的恢复机制

如果发生系统崩溃，InnoDB 会根据 Redo Log 文件进行数据恢复。恢复的流程如下：

  1. 从最近的 Checkpoint 恢复：在事务提交时，数据库会定期将脏页刷新到磁盘，形成一个 Checkpoint。
  2. 根据 Redo Log 恢复未持久化的数据：系统崩溃后，InnoDB 会通过 Redo Log 从最后一个 Checkpoint 开始恢复事务，重新执行未完成的修改。
Redo Log 的循环使用

Redo Log 文件是循环写入的，意味着当日志文件达到最大大小时，新的日志会覆盖旧的日志。这种循环写入机制是为了避免日志文件不断增长，导致存储空间不足。

  • 日志文件大小：innodb_log_file_size 参数可以设置每个日志文件的大小。
  • 日志文件数量：innodb_log_files_in_group 参数设置日志文件的数量，通常默认是 2 个文件。

img

img

undo log 回滚日志

定义：

Undo Log 是 InnoDB 存储引擎用于记录数据被修改前的状态的日志。当数据发生修改时，Undo Log 记录了这次修改的反向操作，它提供了事务回滚和 多版本并发控制（MVCC） 的基础。

Undo Log 的特点
  • 逻辑日志：与 Redo Log（物理日志）不同，Undo Log 记录的是 逻辑日志，即记录的操作描述的是“发生了什么”，而不是数据如何在内存中变化。例如，当执行 DELETE 操作时，Undo Log 记录的并不是删除的数据页，而是该行的 插入操作。反之亦然，对于 UPDATE 操作，Undo Log 记录的也不是修改后的数据页，而是对数据的 反向修改。
  • 回滚机制：Undo Log 的核心作用就是提供回滚（Rollback）功能，当事务发生错误或用户请求回滚时，Undo Log 会帮助恢复到修改前的状态。
Undo Log 的作用

Undo Log 主要有两个重要作用：

1. 提供事务回滚（Rollback）

  • 事务回滚：当事务发生错误或被显式回滚时，InnoDB 会利用 Undo Log 来撤销事务所做的修改，恢复到事务执行前的状态。
  • 例如，如果在执行一个 UPDATE 操作时，将一条记录的字段从原值修改为新值，Undo Log 中会记录该操作的反向操作，即将字段值恢复到原值。

2. 支持多版本并发控制（MVCC）

  • MVCC：InnoDB 通过 Undo Log 支持 MVCC，它允许 多个事务 并发执行，并且每个事务都能看到自己事务开始时的数据版本，而不会被其他事务的修改影响。
  • 事务隔离性：MVCC 通过为每个事务提供一致的视图，允许事务之间并发执行，并能有效控制事务隔离级别。例如，读取未提交（Read Uncommitted）级别的事务可以读取其他事务未提交的修改，而 可重复读（Repeatable Read）级别的事务会确保读取的数据版本在整个事务过程中是一致的，利用 Undo Log 来实现这一点。
Undo Log 的工作原理

Undo Log 的产生

  • 执行 SQL 语句时：每当执行 INSERT、UPDATE 或 DELETE 等修改数据的操作时，InnoDB 会为每个操作生成相应的 Undo Log。
  • 反向操作记录：Undo Log 会记录每个操作的反向逻辑。例如，当 DELETE 一行数据时，Undo Log 会记录 INSERT 操作，用于撤销该行的删除；当 UPDATE 一行数据时，Undo Log 会记录该行的反向 UPDATE，将数据恢复到更新前的状态。

回滚操作：

当事务发生错误或显式回滚时，InnoDB 会从 Undo Log 中读取这些反向操作，并按顺序逐个执行，从而将数据恢复到事务开始前的状态。

Undo Log 的存储与管理

Undo Log 的存储：

  • Rollback Segment（回滚段）：Undo Log 被存储在回滚段中，回滚段是 InnoDB 用于管理和存储 Undo Log 的逻辑结构。
  • 段的管理：每个回滚段包含多个 Undo Log 段（即多个日志记录），这些段可以动态地被分配和回收。当回滚段中的数据被处理后，它会被标记为空闲，供其他事务使用。
  • 回滚段数量：每个回滚段可以包含多个 Undo Log 记录，默认情况下，InnoDB 会维护 1024 个 Undo Log 段（这一数字可以通过参数进行调整）。

Undo Log 的回收机制：

  • 回收：当一个事务提交或回滚后，相应的 Undo Log 会被回收。尽管事务提交后，Undo Log 会被清除，但这些日志并不会立即删除，因为它们在 MVCC 中可能会被其他未提交的事务继续使用。
  • MVCC 保证：即使事务已提交，Undo Log 可能会继续保留，直到它不再被任何事务所需（例如在读取快照或回滚过程中不再需要时）。
Undo Log 销毁
  • 事务提交时不立即删除 Undo Log：当事务提交时，Undo Log 并不会立即销毁。即使事务完成并提交，它的 Undo Log 仍然可能会被保留，因为 Undo Log 也被用于 MVCC，即事务在并发执行时，可能需要在 读取历史版本 或 回滚事务 时使用这些日志。
  • 日志清理：Undo Log 在不再需要时，才会被清理。例如，回滚段中的 Undo Log 在回滚操作完成后会被销毁。

img

4.MVCC(多版本并发控制)

基本概念

当前读：

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:select…lock in share mode(共享锁)，select… for update、update、insert、delete(排他锁)都是一种当前读。

快照读：

简单的select(不加锁)就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

  • Read committed:每次select，都生成一个快照读。
  • Repeatable Read:开启事务后第一个select语句才是快照读的地方。
  • Serializable:快照读会退化为当前读。

MVCC：

全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MVSOL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、read View。

隐藏字段

img

当我们创建了上面的这张表，我们在查看表结构的时候，就可以显式的看到这三个字段。 实际上除了这三个字段以外，InnoDB还会自动的给我们添加三个隐藏字段及其含义分别是：

img

img

而上述的前两个字段是肯定会添加的， 是否添加最后一个字段DB_ROW_ID，得看当前表有没有主键，如果有主键，则不会添加该隐藏字段

undolog版本链

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。

当insert的时候，产生的undoloq日志只在回滚时需要，在事务提交后，可被立即删除。

而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

那么何时删除？

  • 事务提交后

    ：

    • 对于INSERT操作，事务提交后，undo log可以被立即删除，因为不再需要用于回滚。
    • 对于UPDATE和DELETE操作，undo log不会立即被删除，因为它们可能在后续的快照读取中被使用。

  • 快照读取结束

    ：

    • 当所有依赖于该undo log的快照读取操作结束后，undo log才会被删除。这意味着如果有一个事务正在进行快照读取，并且依赖于某个undo log，那么这个undo log会一直保留直到该事务结束。

img

一个包含 id、age 和 name 的数据表。通过几个不同的事务（事务 2、事务 3、事务 4、事务 5），数据记录发生了变化：

  • 事务 2：修改了 id 为 30 的记录，将 age 改为 3。
  • 事务 3：修改了 id 为 30 的记录，将 name 改为 A3。
  • 事务 4：查询 id 为 30 的记录。
  • 事务 5：修改了 id 为 30 的记录，将 age 改为 10。

每个事务在提交时，都会在 Undo Log 中生成对应的逻辑记录。Undo Log 记录的数据不是物理日志，而是记录 逻辑操作，即修改前的旧数据。

img

如何通过 Undo Log 实现 MVCC

  • 在并发的事务中，每个事务可能会修改同一条数据。Undo Log 通过版本链表的方式记录所有事务的修改，确保每个事务能够看到它在开始时的数据快照，而不受其他事务提交的影响。
  • MVCC 允许事务在读取数据时，看到的是数据在事务开始时的状态，即使其他事务已提交新的更改。

readview

ReadView 是一个逻辑结构，用来帮助事务在快照读取时访问正确的数据版本。在 MVCC 实现中，每次执行查询时，ReadView 会记录当前活跃事务的 ID（即未提交的事务）。它帮助事务获取当前一致的视图数据，而不受其他事务的修改影响。

ReadView中包含了四个核心字段:

字段 含义
m_ids 当前活跃的事务ID集合
min_trx_id 最小活跃事务ID
max_trx_id 预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
creator_trx_id ReadView创建者的事务ID

而在readview中就规定了版本链数据的访问规则：
trx_id 代表当前undolog版本链对应事务ID。

img

RC隔离级别（Read Committed）

特点：

在 RC 隔离级别下，每次执行快照读时，都会生成一个新的 ReadView。这意味着在事务的生命周期内，不同的快照读可能会获取到不同版本的数据。

img

我们就来分析事务5中，两次快照读读取数据，是如何获取数据的?
在事务5中，查询了两次id为30的记录，由于隔离级别为Read Committed，所以每一次进行快照读都会生成一个ReadView，那么两次生成的ReadView如下

img

那么这两次快照读在获取数据时，就需要根据所生成的ReadView以及ReadView的版本链访问规则，
到undolog版本链中匹配数据，最终决定此次快照读返回的数据。

A. 先来看第一次快照读具体的读取过程：

img

在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配：

先匹配

img

这条记录，这条记录对应的trx_id为4，也就是将4带入右侧的匹配规则中。 ①不满足 ②不满足 ③不满足 ④也不满足 ，都不满足，则继续匹配undo log版本链的下一条。

再匹配第二条

img

这条记录对应的trx_id为3，也就是将3带入右侧的匹配规则中。①不满足 ②不满足 ③不满足 ④也
不满足 ，都不满足，则继续匹配undo log版本链的下一条。

再匹配第三条

img

这条记录对应的trx_id为2，也就是将2带入右侧的匹配规则中。①不满足 ②满足 终止匹配，此次快照
读，返回的数据就是版本链中记录的这条数据。

B. 再来看第二次快照读具体的读取过程:

img

在进行匹配时，会从undo log的版本链，从上到下进行挨个匹配：

先匹配

img

这条记录，这条记录对应的trx_id为4，也就是将4带入右侧的匹配规则中。 ①不满足 ②不满足 ③不满足 ④也不满足 ，都不满足，则继续匹配undo log版本链的下一条。

再匹配第二条

img

这条记录对应的trx_id为3，也就是将3带入右侧的匹配规则中。①不满足 ②满足 。终止匹配，此次
快照读，返回的数据就是版本链中记录的这条数据。

RR隔离级别（Repeatable Read）

特点：

在 RR 隔离级别下，事务在开始时生成一个 ReadView，并且整个事务期间都使用同一个 ReadView。这确保了事务在执行过程中，任何查询都能看到相同的数据版本。

那MySQL是如何做到可重复读的呢? 我们简单分析一下就知道了

img

img

RC 和 RR 隔离级别的比较
隔离级别 ReadView 创建时机 快照读的行为
RC 每次执行快照读时生成 ReadView 每次查询时，快照读可能读取到不同版本的数据
RR 事务开始时生成一个 ReadView 所有查询使用相同的 ReadView，保证事务内数据一致性

RC 隔离级别：

  • 每次读取数据时生成新的 ReadView，因此不同查询可能会读取到不同版本的数据，可能导致“脏读”。

RR 隔离级别：

  • 在事务开始时生成一个 ReadView，整个事务期间复用这个视图，确保了事务内的 可重复读，避免了“幻读”问题。

所以呢，MVCC的实现原理就是通过 InnoDB表的隐藏字段、UndoLog 版本链、ReadView来实现的。
而MVCC + 锁，则实现了事务的隔离性。 而一致性则是由redolog 与 undolog保证。

img

5.InnoDB引擎 小结

img

7.MySQL管理

1.系统数据库介绍

Mysql数据库安装完成后，自带了一下四个数据库，具体作用如下：

数据库 含义
mysql 存储MVSQL服务器正常运行所需要的各种信息(时区、主从、用户、权限等)
information_schema 提供了访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等
performance_schema 为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数
sys 包含了一系列方便 DBA和开发人员利用 performance_schema性能数据库进行性能调优和诊断的视图

img

2.常用工具

mysql

该mysql不是指mysql服务，而是指mysql的客户端工具。

1
2
3
4
5
6
7
8
语法 ：    
    mysql   [options]   [database]
选项 ： 
    -u, --user=name         #指定用户名
    -p, --password[=name]           #指定密码
    -h, --host=name         #指定服务器IP或域名
    -P, --port=port             #指定连接端口
    -e, --execute=name          #执行SQL语句并退出

-e选项可以在Mysql客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤其方便。

示例：

1
mysql -uroot –p123456 db01 -e "select * from stu";

mysqladmin

mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。

1
2
通过帮助文档查看选项：
mysqladmin --help

img

1
2
3
4
5
6
7
语法: 
mysqladmin [options] command ...
选项:-u, --user=name       #指定用户名
-p, --password[=name]      #指定密码
-h, --host=name            #指定服务器IP或域名

-P, --port=port            #指定连接端口

示例：

1
2
mysqladmin -uroot –p1234 drop 'test01';
mysqladmin -uroot –p1234 version;

mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使
用到mysqlbinlog 日志管理工具。

img

mysqlshow

mysqlshow 客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索
引。

img

mysqldump

mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及
插入表的SQL语句。

img

mysqlimport/source

1). mysqlimport
mysqlimport 是客户端数据导入工具，用来导入mysqldump 加 -T 参数后导出的文本文件。

img

2). source
如果需要导入sql文件,可以使用mysql中的source 指令 :

img

3.常用工具小结

img

进阶篇完结