MySql进阶

此笔记由本人学习 B 站黑马程序员 MySQL 数据库视频进阶篇内容后，总结提取摘要制成。
视频地址：黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括

MySQL体系结构

MySQL体系结构：连接层，服务层，引擎层，存储层。

2025-06-10115712

连接层：处理客户端连接、认证和线程管理。
- 连接器（Connector）：
  - 处理客户端连接请求，支持 TCP/IP、Unix Socket、命名管道等连接方式。
  - 验证用户身份（用户名、密码、主机权限）。
  - 为每个连接分配线程（或从线程池获取）。
- 线程池（Thread Pool）：
  - 管理数据库连接线程，减少频繁创建 / 销毁线程的开销。
  - 适用于高并发场景（如 MySQL Enterprise Edition）。
服务层：包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
- SQL 接口（SQL Interface）：
  - 接收 SQL 请求（SELECT、INSERT 等），返回查询结果。
- 查询解析器（Parser）：
  - 对 SQL 语句进行词法和语法分析，生成解析树（Parse Tree）。
  - 验证语句语法正确性（如关键字拼写、表名 / 列名是否存在）。
- 预处理器（Preprocessor）：
  - 进一步检查解析树的合法性（如权限检查、外键约束验证）。
  - 替换别名、展开视图等操作。
- 查询优化器（Optimizer）：
  - 生成最优执行计划（如选择索引、表连接顺序）。
  - 支持成本优化（Cost-Based Optimization, CBO）和规则优化（Rule-Based Optimization, RBO）。
- 查询执行引擎（Execution Engine）：
  - 根据执行计划调用存储引擎 API 执行查询。
- 缓存（Query Cache）：
  - 缓存 SQL 语句及其结果（5.7 版本后逐渐弃用，8.0 版本移除）。
  - 当数据发生变更时，相关缓存会被自动清除。
引擎层：负责数据的存储和检索。架构模式是插件式，服务器通过API和存储引擎进行通信。支持 InnoDB、MyISAM、Memory 等多个存储引擎。
- 插件式架构：支持多种存储引擎，通过统一接口与上层交互。
- 常见引擎：
  - InnoDB：默认引擎，支持事务、外键、行级锁。
  - MyISAM：不支持事务，表级锁，适合读多写少场景。
  - Memory：数据存储在内存，读写极快，重启丢失数据。
  - Archive：高度压缩，仅支持 INSERT/SELECT，适合历史数据归档。
- 核心功能：
  - 数据存储与检索（如 B + 树索引、哈希索引）。
  - 事务处理（InnoDB）。
  - 锁机制（行锁、表锁）。
存储层：MYSQL的物理存储部分，负责将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在磁盘上。
- 数据文件：
  - .frm：存储表结构定义。
  - .ibd：InnoDB 独立表空间文件（存储数据和索引）。
  - .MYD/.MYI：MyISAM 数据文件和索引文件。
- 日志文件：
  - 二进制日志（Binlog）：记录数据变更，用于主从复制和恢复。
  - 重做日志（Redo Log）：确保事务持久性，崩溃恢复。
  - 回滚日志（Undo Log）：支持事务回滚和 MVCC。
  - 错误日志（Error Log）：记录启动、运行时错误信息。
  - 慢查询日志（Slow Query Log）：记录执行时间超过阈值的 SQL。
- 配置文件：
  - my.cnf/my.ini：存储 MySQL 配置参数（如内存分配、字符集）。

存储引擎

他是mysql数据库的核心，我们也需要在合适的场景选择合适的存储引擎。存储引擎是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。可以在创建表的时指定选择的存储引擎，没有指定将自动选择默认的存储引擎。

建表时指定存储引擎

CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;

查询当前数据库支持的存储引擎

1	show engines;

MySQL 支持多种存储引擎，每种引擎都有其独特的特性和适用场景。以下是常见存储引擎的对比及选择建议。

InnoDB

介绍

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

特点

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

文件结构

xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的、sdi-新版的）、数据和索引。
参数：innodb_file_per_table

1	show variables like 'innodb_file_per_table'

2025-06-10163404

如果该参数开启，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件。ibd文件中不仅存放表结构、数据还会存放该表对应的索引信息。而该文件是基于二进制存储的，不能直接基于记事本打开，我们可以使用mysql提供的一个指令 ibd2sdi ，通过该指令就可以从ibd文件中提取sdi信息，而sdi数据字典信息中就包含该表的表结构。

逻辑存储结构

2025-06-10164034

表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以
包含多个Segment段。
段 : 表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管
理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
区 : 区是表空间的单元结构，每个区的大小为1M。默认情况下， InnoDB存储引擎页大小为
16K，即一个区中一共有64个连续的页。
页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默
认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时
所指定的字段以外，还包含两个隐藏字段(后面会详细介绍)。

适用场景

事务性应用（如电商、金融系统）。
高并发读写场景。
需要外键约束的表。

MyISAM

介绍

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

特点

不支持事务，不支持外键
支持表锁，不支持行锁
优点：更少的存储空间，支持全文索引，适用于读取频率较高、写入频率较低的应用场景

文件结构

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

适用场景

只读或写入少、查询多的场景（如日志表、统计数据）。
不需要事务支持的场景。

Memory

介绍

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

特点

数据存储在内存：读写速度极快，但重启后数据丢失。
支持哈希索引：适合快速查找。
表级锁：并发性能有限。

文件结构

xxx.sdi：存储表结构信息
数据，都在内存中

适用场景

临时表/中间结果集： MySQL 内部自动使用。
高速缓存： 存储频繁访问的小型、非关键、可丢失的只读/低频写数据（如会话信息、配置）。
需要极低延迟访问的只读查询。

重要警告：绝对不要用于存储重要或持久化数据。内存有限，大表易导致 OOM。

InnoDB, MyISAM, Memory的区别，使用场景

0fb3844e83912d08779b1b8a1ab51f24

面试题:
InnoDB引擎与MyISAM引擎的区别 ?
①. InnoDB引擎, 支持事务, 而MyISAM不支持。
②. InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁。
③. InnoDB引擎, 支持外键, 而MyISAM是不支持的。

索引

介绍

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。它类似于书籍的目录，允许数据库快速定位特定数据，避免全表扫描。

40c81bd2db81dab60339a59238cbf784

特点

优势	劣势
提高数据检索的效率，降低数据库的IO成本	索引列也是要占用空间的。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。	索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

索引结构

概述

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

2025-06-10174544

上述是MySQL中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持情况。

2025-06-10174544

注意：我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

二叉树

假如说MySQL的索引结构采用二叉树的数据结构，比较理想的结构如下：

如果主键是顺序插入的，则会形成一个单向链表，结构如下：

所以，如果选择二叉树作为索引结构，会存在以下缺点：

顺序插入时，会形成一个链表，查询性能大大降低。
大数据量情况下，层级较深，检索速度慢。

此时大家可能会想到，我们可以选择红黑树，红黑树是一颗自平衡二叉树，那这样即使是顺序插入数据，最终形成的数据结构也是一颗平衡的二叉树,结构如下:

但是，即使如此，由于红黑树也是一颗二叉树，所以也会存在一个缺点：

解决二叉树的顺序插入后，树不平衡的问题。
大数据量情况下，层级较深，检索速度慢。

B-Tree

B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5 个指针：

知识小贴士: 树的度数指的是一个节点的子节点个数。

我们可以通过一个数据结构可视化的网站来简单演示一下。B-Tree Visualization (usfca.edu)

插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 。然后观察一些数据插入过程中，节点的变化情况。

特点：

5阶的B树，每一个节点最多存储4个key，对应5个指针。
一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
在B树中，非叶子节点和叶子节点都会存放数据。

B+Tree

B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一下其结构示意图：

我们可以看到，两部分：

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

通过一个数据结构可视化的网站来简单演示一下。[B+ Tree Visualization (usfca.edu)

插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 。然后观察一些数据插入过程中，节点的变化情况。

最终我们看到，B+Tree 与 B-Tree相比，主要有以下三点区别：

所有的数据都会出现在叶子节点。
叶子节点形成一个单向链表。
非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

MySQL优化后的B+ Tree：

上述我们所看到的结构是标准的B+Tree的数据结构，接下来，我们再来看看MySQL中优化之后的 B+Tree。 MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在
hash表中。

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

特点：

Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）。
无法利用索引完成排序操作。
查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引。

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

为什么 InnoDB 存储引擎选择使用 B+tree 索引结构?

相对于二叉树，层级更少，搜索效率高；

对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；

相对Hash索引，B+tree支持范围匹配及排序操作；

索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

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

聚集索引选取规则：

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

聚集索引和二级索引的具体结构如下：

聚集索引的叶子节点下挂的是这一行的数据。
二级索引的叶子节点下挂的是该字段值对应的主键值。

回表查询

先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

当我们执行如下的SQL语句时，具体的查找过程是什么样子的。

具体过程如下:

①. 由于是根据name字段进行查询，所以先根据name=’Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。

②. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。

③. 最终拿到这一行的数据，直接返回即可。

思考题

以下两条SQL语句，那个执行效率高? 为什么?

A. select * from user where id = 10 ;
B. select * from user where name = ‘Arm’ ; 备注: id为主键，name字段创建的有索引；

解答： A 语句的执行性能要高于B 语句。因为A语句直接走聚集索引，直接返回数据。而B语句需要先查询name字段的二级索引，然后再查询聚集索引，也就是需要进行回表查询。

InnoDB主键索引的B+tree高度为多高呢?

假设: 一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8。

关键公式：
每个非叶子节点的索引条目数 n 需满足：n × 主键大小 + (n + 1) × 指针大小 ≤ 页大小。

n × 8 ：n 个主键值的总字节数；
(n + 1) × 6 ：n+1 个指针的总字节数（每个节点至少有 n+1 个指针指向子节点）。
算出n约为 1170，也就是说每个非叶子节点最多存储 1170 个索引条目，对应 1171 个子节点（n+1）。

B + 树高度为 2 时的最大数据量：

树结构：非叶子节点（根节点）+ 叶子节点。
叶子节点数量：根节点的子节点数 = 1171 个。
每个叶子节点存储数据量：16 行（由页大小决定）。
总数据量：1170 × 16 = 18,720条。可以存储 18000 多条记录。

B + 树高度为 3 时的最大数据量：

树结构：根节点 + 中间层 + 叶子层。
叶子节点数量：根节点的子节点数 = 1171 个。
每个叶子节点存储数据量：16 行（由页大小决定）。
总数据量：1170 × 1170 × 16 = 21,902,400条。可以存储 2190w 多条记录。

索引语法

创建索引

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

查看索引

1	SHOW INDEX FROM table_name ;

删除索引

1	DROP INDEX index_name ON table_name ;

SQL性能分析

SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

– session 是查看当前会话 ;

– global 是查询全局数据 ;

SHOW GLOBAL STATUS LIKE ‘Com_______’

通过查询SQL的执行频次，我们就能够知道当前数据库到底是增删改为主，还是查询为主。那假如说是以查询为主，我们又该如何定位针对于那些查询语句进行优化呢？次数我们可以借助于慢查询日志。

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有 SQL语句的日志。

MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。（默认是关闭的）。

1	show variables like 'slow_query_log';

如果要开启慢查询日志，需要在MySQL的配置文件中配置如下信息：

Windows 下通常位于 MySQL 安装目录或 C:\ProgramData\MySQL\MySQL Server X.Y。

Linux 下（/etc/my.cnf）。

[mysqld]
# 启用慢查询日志
slow_query_log = 1

# 指定慢查询日志文件路径
slow_query_log_file = "C:/ProgramData/MySQL/MySQL Server 8.0/slow_query.log"

# 设置慢查询阈值（单位：秒）
long_query_time = 10

# 可选：记录未使用索引的查询
# log_queries_not_using_indexes = 1

# 可选：记录管理语句（如 OPTIMIZE TABLE）
# log_slow_admin_statements = 1

重启MySQL 服务，然后，再次查看开关情况，慢查询日志就已经打开了。

测试：

执行如下SQL语句：
1
SELECT SLEEP(11);

打开慢日志文件，检查慢查询日志：

# Time: 2025-06-16T08:26:30.451314Z
# User@Host: root[root] @ localhost [::1]  Id:     4
# Query_time: 11.007434  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use huanyuan2;
SET timestamp=1750062390;
SELECT SLEEP(11);

那这样，通过慢查询日志，就可以定位出执行效率比较低的SQL，从而有针对性的进行优化。

profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过have_profiling 参数，能够看到当前MySQL是否支持profile操作：
1
SELECT @@have_profiling ;
查看当前数据库是否打开了 profiling ：
1
select @@profiling;

可以看到，当前MySQL是支持 profile操作的，但是开关是关闭的。可以通过set语句在 session/global级别开启profiling：

1	SET profiling = 1;

profile 开关打开后，我们所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去了。

可以通过如下指令查看指令的执行耗时：

-- 查看每一条SQL的耗时基本情况
show profiles;

-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

explain

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

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

Explain 执行计划中各个字段的含义:

字段	含义
id	select查询的序列号，表示查询中执行select子句或者是操作表的顺序 (id相同，执行顺序从上到下；id不同，值越大，越先执行)。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、 UNION（UNION 中的第二个或者后面的查询语句）、 SUBQUERY（SELECT/WHERE之后包含了子查询）等
type	表示连接类型，性能由好到差的连接类型为NULL、system、const、 eq_ref、ref、range、 index、all 。
possible_key	显示可能应用在这张表上的索引，一个或多个。
key	实际使用的索引，如果为NULL，则没有使用索引。
key_len	表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
rows	MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
filtered	表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

索引使用优化

单列索引与联合索引

单列索引：即一个索引只包含单个列。

联合索引：即一个索引包含了多个列。

我们先来看看 tb_user 表中目前的索引情况:

在查询出来的索引中，既有单列索引，又有联合索引。

接下来，我们来执行一条SQL语句，看看其执行计划：

通过上述执行计划我们可以看出来，在and连接的两个字段 phone、name上都是有单列索引的，但是最终mysql只会选择一个索引，也就是说，只能走一个字段的索引，此时是会回表查询的。

接着，我们再来创建一个phone和name字段的联合索引来查询一下执行计划。

1	create unique index idx_user_phone_name on tb_user(phone,name);

此时，查询时，就走了联合索引，而在联合索引中包含 phone、name的信息，在叶子节点下挂的是对应的主键id，所以查询是无需回表查询的。

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法

1	create index idx_xxxx on table_name(column(n)) ;

前缀长度

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

# 查询使用email整个字符串的索引选择比   1.0000
select count(distinct email) / count(*) from tb_user;
# 查询使用email 使用前缀5个字符串的索引选择比  0.9583
select count(distinct substring(email,1,5)) / count(*) from tb_user ;
# 查询使用email 使用前缀2个字符串的索引选择比   0.9167
select count(distinct substring(email,1,2)) / count(*) from tb_user ;
# 对字段email建立前缀索引，前缀长度为5  
create index email_idx on tb_user(email(5));
# 查看使用email前缀索引进行查询的执行结构
explain select * from tb_user where email = 'xiaoyu666@qq.com';

前缀索引的查询流程

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询条件必须从复合索引的最左列开始，并且不能跳过中间列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

🧩 四种典型使用场景分析

CREATE INDEX idx_name_age_city ON users (
    last_name,  -- 最左列
    age,        -- 中间列
    city        -- 最右列
);

✅ 场景 1：完整使用索引 (最佳)

SELECT * FROM users 
WHERE last_name = 'Smith' 
  AND age = 30 
  AND city = 'New York';

索引使用：
(last_name, age, city) 三列全使用
👉 查询效率最高

✅ 场景 2：使用最左连续列

1
2
3

SELECT * FROM users 
WHERE last_name = 'Smith' 
  AND age = 30;

索引使用：
(last_name, age) 两列
👉 有效使用索引

✅ 场景 3：仅使用最左列

1 2	SELECT * FROM users WHERE last_name = 'Smith';

索引使用：
(last_name) 单列
👉 有效但非最优

❌ 场景 4：违反最左前缀（常见错误）

-- 错误1：跳过最左列
SELECT * FROM users WHERE age = 30;

-- 错误2：缺少中间列
SELECT * FROM users 
WHERE last_name = 'Smith' 
  AND city = 'New York'; -- 跳过age列

索引使用：
无法使用索引或仅部分使用
👉 全表扫描风险

✅ 场景 5：条件编写的先后顺序

以下代码索引会失效吗？

SELECT * FROM users 
WHERE  age = 30
  AND  city = 'New York'
  AND  last_name = 'Smith';

答案：不会，MySQL 优化器会自动重排条件顺序：

-- 优化器重写后的等效查询
SELECT * FROM users 
WHERE last_name = 'Smith'  -- 最左列
  AND age = 30            -- 第二列
  AND city = 'New York';  -- 第三列

注意：最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。

索引失效情况

🚫 1. 违反最左前缀法则（复合索引）

-- 复合索引: (last_name, age, city)
SELECT * FROM users WHERE age = 30; -- 缺少最左列
SELECT * FROM users WHERE city = 'New York'; -- 缺少最左列
SELECT * FROM users WHERE last_name = 'Smith' AND city = 'New York'; -- 跳过中间列

✅ 解决方案：

调整查询条件顺序
创建新索引：CREATE INDEX idx_age_city ON users(age, city)

🚫 2. 在索引列上使用函数或计算

1
2
3

-- 索引: created_at
SELECT * FROM orders WHERE YEAR(created_at) = 2023; -- 函数操作
SELECT * FROM products WHERE price * 1.1 > 100; -- 计算操作

✅ 解决方案：

使用范围查询替代：

1 2	SELECT * FROM orders WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

预先计算存储：

1 2	ALTER TABLE products ADD COLUMN price_with_tax DECIMAL(10,2) AS (price * 1.1); CREATE INDEX idx_price_tax ON products(price_with_tax);

🚫 3. 隐式类型转换

1 2	-- phone 是 VARCHAR 索引列 SELECT * FROM customers WHERE phone = 13800138000; -- 数字 vs 字符串

✅ 解决方案：

1	SELECT * FROM customers WHERE phone = '13800138000'; -- 保持类型一致

🚫 4. 使用 `OR` 连接非索引列

1
2
3

-- 只有 name 有索引
SELECT * FROM users 
WHERE name = 'John' OR email = 'john@example.com'; -- email 无索引

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

✅ 解决方案：

创建联合索引

拆分为两个查询 UNION：

1
2
3

SELECT * FROM users WHERE name = 'John'
UNION
SELECT * FROM users WHERE email = 'john@example.com';

🚫 5. `LIKE` 以通配符开头

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

1
2
3

-- 索引: email
SELECT * FROM users WHERE email LIKE '@gmail.com%'; -- 后导通配符
SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- 前导通配符 索引失效

🚫 6. 范围查询后的列失效

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

-- 复合索引: (category, price, rating)
SELECT * FROM products 
WHERE category = 'Electronics' 
  AND price > 1000 
  AND rating > 4; -- rating 无法使用索引

✅ 解决方案：

调整索引列顺序：

1	CREATE INDEX idx_category_rating_price ON products(category, rating, price); -- 让等值条件和高选择性范围查询优先使用索引

使用覆盖索引：

1	CREATE INDEX idx_cover ON products(category, price, rating, product_id);

使用 >=：

SELECT * FROM products 
WHERE category = 'Electronics' 
  AND price >= 1000 
  AND rating > 4; -- 在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 < 。

🚫 7. 使用 `!=` 或 `<>`

1 2	-- 索引: status SELECT * FROM orders WHERE status != 'completed';

✅ 解决方案：

改为范围查询：

1	SELECT * FROM orders WHERE status < 'completed' OR status > 'completed';

使用特定值列表：

1 2	SELECT * FROM orders WHERE status IN ('pending', 'processing', 'cancelled');

🚫 8. 索引列使用 `IS NULL`/`IS NOT NULL`

1 2	-- 索引: phone SELECT * FROM customers WHERE phone IS NOT NULL; -- 可能失效

✅ 解决方案：

添加条件限制：

1 2	SELECT * FROM customers WHERE phone IS NOT NULL AND phone > ''; -- 利用索引扫描

使用覆盖索引：

1	CREATE INDEX idx_phone_cover ON customers(phone) INCLUDE (name, email);

🚫 9. 数据分布不均导致优化器放弃索引

1 2	-- 索引: status (90% 值为 'active') SELECT * FROM products WHERE status = 'active'; -- 可能全表扫描

✅ 解决方案：

强制使用索引：

1 2	SELECT * FROM products FORCE INDEX(idx_status) WHERE status = 'active';

调整优化器设置：

1	SET optimizer_switch='index_condition_pushdown=off';

🚫 10. 使用 `NOT IN`

1
2
3

-- 索引: category
SELECT * FROM products 
WHERE category NOT IN ('Books', 'Clothing');

✅ 解决方案：

改用 NOT EXISTS：

SELECT * FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM excluded_categories e 
  WHERE e.category = p.category
);

使用左连接：

SELECT p.* 
FROM products p
LEFT JOIN excluded_categories e ON p.category = e.category
WHERE e.category IS NULL;

SQL提示

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

use index ：建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。

1	explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

ignore index ：忽略指定的索引。

1	explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

force index ：强制使用索引。

1	explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

覆盖索引

尽量使用覆盖索引，减少select *。那么什么是覆盖索引呢？覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

执行计划 EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 查询结果中 Extra 的含义：

Extra	含义
Using where; Using Index	查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
Using index condition	查找使用了索引，但是需要回表查询数据

为了更清楚理解，什么是覆盖索引，什么是回表查询，我们一起再来看下面的这组SQL的执行过程。

思考题：

一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对以下SQL语句进行优化, 该如何进行才是最优方案?

select id,username,password from tb_user where username = ‘itcast’

答案:

针对于 username, password建立联合索引, sql为: create index idx_user_name_pass on tb_user(username,password);

这样可以避免上述的SQL语句，在查询的过程中，出现回表查询。

索引设计原则

针对于数据量较大，且查询比较频繁的表建立索引。
针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含 NULL值时，它可以更好地确定哪个索引最有效地用于查询。

SQL优化（后面在做）

视图/存储过程/触发器

视图

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

基本语法

创建

1	CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [CASCADED \| LOCAL ] CHECK OPTION ]

查询

1 2	查看创建视图语句：SHOW CREATE VIEW 视图名称; 查看视图数据：SELECT * FROM 视图名称 ...... ;

修改

1
2

方式一：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

删除

1	DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...

检查选项

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。 MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项： CASCADED 和 LOCAL ，默认值为 CASCADED 。

CASCADED 级联

比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。

LOCAL 本地

比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。则在执行检查时，只会检查v2，不会检查v2的关联视图v1。

视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
DISTINCT
GROUP BY
HAVING
UNION 或者 UNION ALL

视图的作用

简单：视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
安全：数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
数据独立：视图可帮助用户屏蔽真实表结构变化带来的影响。

存储过程

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

特点

封装，复用：可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可。
可以接收参数，也可以返回数据：在存储过程中，可以传递参数，也可以接收返回值。
减少网络交互，效率提升：如果涉及到多条SQL，每执行一次都是一次网络传输。而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。

基本语法

创建

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN
-- SQL语句
END ;

调用
1
CALL 名称 ([ 参数 ]);

查看

1
2
3

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; -- 查询指
定数据库的存储过程及状态信息
SHOW CREATE PROCEDURE 存储过程名称 ; -- 查询某个存储过程的定义

删除

1	1 DROP PROCEDURE [ IF EXISTS ] 存储过程名称；

案例

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

-- 存储过程基本语法
-- 创建
create procedure p1()
begin
select count(*) from student;
end;

-- 调用
call p1();

-- 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';
show create procedure p1;

-- 删除
drop procedure if exists p1;

变量

在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量。

系统变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）。

全局变量(GLOBAL): 全局变量针对于所有的会话。
会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了。
如果没有指定SESSION/GLOBAL，默认是SESSION会话变量。

mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。

查看&设置系统变量

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

# 设置系统变量
SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
SET @@[SESSION | GLOBAL]系统变量名 = 值 ;

用户定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 “@变量名” 使用就可以。其作用域为当前连接。

赋值，可以使用 = ，也可以使用 := 。

方式一:
	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 表名;

使用
1
SELECT @var_name ;

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

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN … END块。

声明

1 2	DECLARE 变量名变量类型 [DEFAULT ... ] ; -- 变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

赋值

1
2
3

SET 变量名 = 值 ;
SET 变量名 := 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;

使用
1
select 变量名;

if

if 用于做条件判断，具体的语法结构为：

IF 条件1 THEN
.....
ELSEIF 条件2 THEN -- 可选
.....
ELSE -- 可选
.....
END IF;

# ---------------------举例：根据定义参数score，判定当前分数对应等级--------------------
drop procedure if exists p3;
create procedure p3()
begin
    declare score int default 58; #声明变量score为58，判断其分数等级
    declare grade varchar(10); #用于接收等级
    if score >= 85 then
        set grade := '优秀';
    elseif score >= 60 then
        set grade := '及格';
    else
        set grade := '不及格';
    end if;
    select grade;
end;
call p3; # 不及格

在if条件判断的结构中，ELSE IF 结构可以有多个，也可以没有。 ELSE结构可以有，也可以没有。

参数

参数的类型，主要分为以下三种：IN、OUT、INOUT。具体的含义如下：

类型	含义	备注
IN	该类参数作为输入，也就是需要调用时传入值	默认
OUT	该类参数作为输出，也就是该参数可以作为返回值
INOUT	既可以作为输入参数，也可以作为输出参数

用法：

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

案例

-- 案例一 根据传入参数score，判定当前分数对应的分数等级，并返回。
-- score >= 85分，等级为优秀。
-- score >= 60分 且 score < 85分，等级为及格。
-- score < 60分，等级为不及格。

DROP PROCEDURE if EXISTS p1;
CREATE PROCEDURE p1(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;
END;

-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明
call p1(99, @result);
select @result; -- 优秀


-- 案例二 将传入的200分制的分数，进行换算，换算成百分制，然后返回。

DROP PROCEDURE if EXISTS p2;
CREATE PROCEDURE p2(INOUT score DOUBLE)
BEGIN
	set score := score * 0.5;
END;

SET @score = 60;
call p2(@score);
SELECT @score; -- 30

case

case结构及作用，和我们在基础篇中所讲解的流程控制函数很类似。有两种语法格式：

语法1：

-- 含义： 当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时，
执行statement_list2， 否则就执行 statement_list
CASE case_value
	WHEN when_value1 THEN statement_list1
	[ WHEN when_value2 THEN statement_list2] ...
	[ ELSE statement_list ]
END CASE;

语法2：

-- 含义： 当条件search_condition1成立时，执行statement_list1，当条件search_condition2成
立时，执行statement_list2， 否则就执行 statement_list
CASE
	WHEN search_condition1 THEN statement_list1
	[WHEN search_condition2 THEN statement_list2] ...
	[ELSE statement_list]
END CASE;

案例：

# 根据传入的月份，判定月份所属的季节（要求采用case结构）。
# 1-3月份，为第一季度
# 4-6月份，为第二季度
# 7-9月份，为第三季度
# 10-12月份，为第四季度

DROP PROCEDURE if EXISTS p3;
CREATE PROCEDURE p3(IN month INT)
BEGIN
	DECLARE season VARCHAR(10);
	CASE 
		WHEN month >= 1 and month <= 3 THEN
			SET season := '第一季度';
		WHEN month >= 4 and month <= 6 THEN
			SET season := '第二季度';
		WHEN month >= 7 and month <= 9 THEN
			SET season := '第三季度';
		WHEN month >= 10 and month <= 12 THEN
			SET season := '第四季度';
		ELSE
			SET season := '非法参数';
	END CASE;

	select concat('您输入的月份为: ',month, ', 所属的季度为: ',season);
END;

call p3(10);# 您输入的月份为: 10, 所属的季度为: 第四季度

注意：如果判定条件有多个，多个条件之间，可以使用 and 或 or 进行连接。

while

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

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
	SQL逻辑...
END WHILE;

案例

# 计算从1累加到n的值，n为传入的参数值。
-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行减1 , 如果n减到0, 则退出循环

DROP PROCEDURE if EXISTS p4;
CREATE PROCEDURE p4(IN num INT)
BEGIN
	declare result INT DEFAULT 0; 
	WHILE num > 0 DO
		set result := result + num;
		set num := num - 1;
	END WHILE;
	SELECT result;
END;

call p4(10); # 55

repeat

repeat是有条件的循环控制语句, 当满足until声明的条件的时候，则退出循环。具体语法为：

-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
	SQL逻辑...
	UNTIL 条件
END REPEAT;

案例

# 计算从1累加到n的值，n为传入的参数值。(使用repeat实现)
-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行减1 , 如果n减到0, 则退出循环

DROP PROCEDURE if EXISTS p5;
CREATE PROCEDURE p5(IN num INT)
BEGIN
	declare result INT DEFAULT 0; 
	REPEAT
		set result := result + num;
		set num := num - 1;
		UNTIL num <= 0
	END REPEAT;
	SELECT result;
END;

call p5(10); #

loop

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

LEAVE ：配合循环使用，退出循环。
ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

[begin_label:] LOOP
	SQL逻辑...
END LOOP [end_label];

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

-- 上述语法中出现的 begin_label，end_label，label 指的都是我们所自定义的标记。

案例

#  案例一
# 计算从1累加到n的值，n为传入的参数值。
-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx

DROP PROCEDURE if EXISTS p6;
CREATE PROCEDURE p6(IN num INT)
BEGIN
	DECLARE result INT DEFAULT 0; 
	getSum: LOOP
		IF num <= 0 THEN
			LEAVE getSum; 
		END IF; 
		set result := result + num;
		set num := num - 1;
	END LOOP getSum;
	SELECT result;
END;

call p6(10); # 55

#  案例二
# 计算从1到n之间的偶数累加的值，n为传入的参数值。
-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
-- C. 如果当次累加的数据是奇数, 则直接进入下一次循环. --------> iterate xx

DROP PROCEDURE if EXISTS p7;
CREATE PROCEDURE p7(IN num INT)
BEGIN
	DECLARE result INT DEFAULT 0; 
	getSum: LOOP
		IF num <= 0 THEN
			LEAVE getSum; 
		END IF; 
		
		if num%2 = 1 then
			set num := num - 1;
			iterate getSum;
		end if;

		set result := result + num;
		set num := num - 1;
		
	END LOOP getSum;
	SELECT result;
END;

call p7(10); # 30

游标

游标（CURSOR）是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。

# 声明游标
DECLARE 游标名称 CURSOR FOR 查询语句 ;
# 打开游标
OPEN 游标名称 ;
# 获取游标记录
FETCH 游标名称 INTO 变量 [, 变量 ] ;
# 关闭游标
CLOSE 游标名称 ;

案例

#  根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名
#（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表
# (id,name,profession)中。

-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标

DROP PROCEDURE if EXISTS p8;
CREATE PROCEDURE p8(IN iage INT)
BEGIN
	# 有先后顺序：先声明普通变量，再声明游标
	declare uname varchar(100);
	declare upro varchar(100);
	# 1.声明游标 存储查询结果集
	DECLARE u_cursor CURSOR FOR SELECT `name`,profession FROM tb_user WHERE age <= iage;
	# 2.创建新表的 表结构
	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)
	);
	# 3.开启游标
	OPEN u_cursor;
	# 4.获取游标中的记录
	while true do
		fetch u_cursor into uname,upro;
		# 5.插入数据到新表中
		insert into tb_user_pro values (null, uname, upro);
	end while;
	# 6.关闭游标
	CLOSE u_cursor;
END;

call p8(30);

上述的存储过程，最终我们在调用的过程中，会报错，之所以报错是因为上面的while循环中，并没有退出条件。当游标的数据集获取完毕之后，再次获取数据，就会报错，从而终止了程序的执行。

但是此时，tb_user_pro表结构及其数据都已经插入成功了，我们可以直接刷新表结构，检查表结构中的数据。
上述的功能，虽然我们实现了，但是逻辑并不完善，而且程序执行完毕，获取不到数据，数据库还报错。接下来，我们就需要来完成这个存储过程，并且解决这个问题。要想解决这个问题，就需要通过MySQL中提供的条件处理程序 Handler 来解决。

条件处理程序

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：

DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement ;
handler_action 的取值：
    CONTINUE: 继续执行当前程序
    EXIT: 终止执行当前程序
condition_value 的取值：
    SQLSTATE sqlstate_value: 状态码，如 02000
    SQLWARNING: 所有以01开头的SQLSTATE代码的简写
    NOT FOUND: 所有以02开头的SQLSTATE代码的简写
    SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

案例

通过SQLSTATE指定具体的状态码

# 我们继续来完成在上一小节提出的这个需求，并解决其中的问题。
#  根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名
#（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表
# (id,name,profession)中。

-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标

DROP PROCEDURE if EXISTS p8;
CREATE PROCEDURE p8(IN iage INT)
BEGIN
	# 有先后顺序：先声明普通变量，再声明游标
	declare uname varchar(100);
	declare upro varchar(100);
	# 1.声明游标 存储查询结果集
	DECLARE u_cursor CURSOR FOR SELECT `name`,profession FROM tb_user WHERE age <= iage;
	
	-- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出
	declare exit handler for SQLSTATE '02000' close u_cursor;
	
	# 2.创建新表的 表结构
	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)
	);
	# 3.开启游标
	OPEN u_cursor;
	# 4.获取游标中的记录
	while true do
		fetch u_cursor into uname,upro;
		# 5.插入数据到新表中
		insert into tb_user_pro values (null, uname, upro);
	end while;
	# 6.关闭游标
	CLOSE u_cursor;
END;

call p8(30);

通过SQLSTATE的代码简写方式 NOT FOUND。02 开头的状态码，代码简写为 NOT FOUND

# 我们继续来完成在上一小节提出的这个需求，并解决其中的问题。
#  根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名
#（name）和专业（profession），并将用户的姓名和专业插入到所创建的一张新表
# (id,name,profession)中。

-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标

DROP PROCEDURE if EXISTS p8;
CREATE PROCEDURE p8(IN iage INT)
BEGIN
	# 有先后顺序：先声明普通变量，再声明游标
	declare uname varchar(100);
	declare upro varchar(100);
	# 1.声明游标 存储查询结果集
	DECLARE u_cursor CURSOR FOR SELECT `name`,profession FROM tb_user WHERE age <= iage;
	
	-- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02开头时，将关闭游标u_cursor，并退出
	declare exit handler for not found close u_cursor;
	
	# 2.创建新表的 表结构
	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)
	);
	# 3.开启游标
	OPEN u_cursor;
	# 4.获取游标中的记录
	while true do
		fetch u_cursor into uname,upro;
		# 5.插入数据到新表中
		insert into tb_user_pro values (null, uname, upro);
	end while;
	# 6.关闭游标
	CLOSE u_cursor;
END;

call p8(30);

具体的错误状态码，可以参考官方文档：
https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html
https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下：

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

characteristic说明：

DETERMINISTIC：相同的输入参数总是产生相同的结果
NO SQL ：不包含 SQL 语句
READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句

案例

# 计算从1累加到n的值，n为传入的参数值。

CREATE FUNCTION fun1 (n INT)
RETURNS INT DETERMINISTIC
BEGIN
    declare sum int default 0;
    while n > 0 do
        set sum := sum + n;
        set n := n - 1;
    end while;
    return sum;
END ;

select fun1(100); # 5050

触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作。

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型	NEW 和 OLD
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

基本语法

创建

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
	trigger_stmt ;
END;

查看
1
SHOW TRIGGERS ;

删除

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

案例

通过触发器记录 tb_user 表的数据变更日志，将变更日志插入到日志表user_logs中, 包含增加, 修改 , 删除。

表结构准备:

-- 准备工作 : 日志表 user_logs
create table user_logs(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作类型, insert/update/delete',
    operate_time datetime not null comment '操作时间',
    operate_id int(11) not null comment '操作的ID',
    operate_params varchar(500) comment '操作参数',
    primary key(`id`)
)engine=innodb default charset=utf8;

插入数据触发器

-- 创建插入触发器
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;

-- 插入数据到tb_user
insert into tb_user(id, name, phone, email, profession, age, gender, status,createtime) 
VALUES (26,'三皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());

-- 查询插入触发器
SELECT * FROM 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;

-- 查看触发器
SHOW TRIGGERS;

-- 更新tb_user数据
update tb_user set profession = '会计' where id = 23;

-- 查询更新触发器
SELECT * FROM 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,'delete',now(),old.id,
		concat('删除的数据内容为:id=',old.id,'name=',old.NAME,', phone=',old.phone,', email=',old.email,',profession=',old.profession));
END;

-- 查看触发器
SHOW TRIGGERS;

-- 删除tb_user数据
delete from tb_user WHERE id=26;

-- 查询删除触发器
SELECT * FROM user_logs;

锁

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

MySQL中的体系全景图

graph TD
    A[MySQL 锁体系] --> B[按粒度划分]
    A --> C[按功能划分]
    A --> D[按模式划分]
    
    B --> B1[全局锁]
    B --> B2[表级锁]
    B --> B3[行级锁]
    B --> B4[页级锁]
    
    C --> C1[共享锁 S]
    C --> C2[排他锁 X]
    C --> C3[意向共享锁 IS]
    C --> C4[意向排他锁 IX]
    
    D --> D1[悲观锁]
    D --> D2[乐观锁]

共享锁 (S Lock)

一个事务已获取共享锁，当另一个事务尝试对具备共享锁的数据进行读操作时，可正常读；进行写操作时，会被共享锁排斥。

特性：允许多事务并发读取
兼容性：兼容其他 S 锁，排斥 X 锁

使用场景：

-- 保证读取期间数据不变
SELECT * FROM table WHERE ... LOCK IN SHARE MODE;
-- MySQL8.0之后也优化了写法，如下：
SELECT ... FOR SHARE;

排他锁 (X Lock)

当一个线程获取到独占锁后，会排斥其他线程（进行读写操作），如若其他线程也想对共享资源/同一数据进行操作，必须等到当前线程释放锁并竞争到锁资源才行。

特性：独占资源，禁止其他操作
兼容性：排斥所有其他锁

使用场景：

1	SELECT * FROM table WHERE ... FOR UPTATE;

全局锁 (Global Lock)

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

典型的使用场景是做全库的逻辑备份。

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

基本语法

加全局锁

1	flush tables with read lock;

数据备份

1	mysqldump -u username -p database_name > backup.sql -- （备份指定数据库到 backup.sql，执行后输入密码）

释放全局锁

1	unlock tables;

特点

数据库中加全局锁，是一个比较重的操作，存在以下问题：

如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数 –single-transaction 参数来完成不加锁的一致性数据备份。

mysqldump –single-transaction -u username -p database_name > backup.sql

表级锁 (Table Lock)

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

使用表锁的开销相对较小，加锁快，不会产生死锁；但是加锁粒度大，发生锁冲突的概率更高，并发度更低。在innoDB存储引擎中不推荐使用表锁，只有在没有事务支持的存储引擎中才会使用，如MyISAM

对于表级锁，主要分为以下三类：

表锁
元数据锁
意向锁

表锁

对于表锁，分为两类：

表共享读锁（read lock）
表独占写锁（write lock）

基本语法

加锁
1
lock tables 表名... read/write。

释放锁

1	unlock tables / 客户端断开连接。

特点

读锁：
写锁：

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

元数据锁（meta data lock, MDL）

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与 DDL冲突，保证读写的正确性。

这里的元数据，大家可以简单理解为就是一张表的表结构。也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作的时候，加MDL写锁(排他)。

常见的SQL操作时，所添加的元数据锁：

对应SQL	锁类型	说明
lock tables xxx read / write（表锁）	SHARED_READ_ONLY / SHARED_NO_READ_WRITE
select 、select … lock in share mode（普通读、共享锁）	SHARED_READ（元数据共享锁）	与SHARED_READ、 SHARED_WRITE兼容，与 EXCLUSIVE互斥
insert 、update、 delete、select … for update（增、改、删、排他锁）	SHARED_WRITE（元数据共享锁）	与SHARED_READ、 SHARED_WRITE兼容，与 EXCLUSIVE互斥
alter table …（修改表结构）	EXCLUSIVE（元数据排他锁）	与其他的MDL都互斥

案例

当执行SELECT、INSERT、UPDATE、DELETE等语句时，添加的是元数据共享锁（SHARED_READ / SHARED_WRITE），之间是兼容的。

当执行SELECT语句时，添加的是元数据共享锁（SHARED_READ），会阻塞元数据排他锁（EXCLUSIVE），之间是互斥的。

我们可以通过下面的SQL，来查看数据库中的元数据锁的情况：

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

我们在操作过程中，可以通过上述的SQL语句，来查看元数据锁的加锁情况。

意向锁（Intention Lock）

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

分类

意向共享锁（IS）：由语句select … lock in share mode添加，与表锁共享锁（read）兼容，与表锁排他锁（write）互斥。在准备给表数据添加一个S锁时，需要先获得该表的IS锁
意向排他锁（IX）：由insert、update、delete、select…for update添加。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。在准备给表数据添加一个X锁时，需要先获得该表的IX锁

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

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

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

案例

A.意向共享锁与表读锁是兼容的

B.意向排他锁与表读锁、写锁都是互斥的

兼容矩阵：

请求\持有	X	IX	S	IS
X（共享锁）	❌	❌	❌	❌
IX（意向排他锁）	❌	✅	❌	✅
S（排他锁）	❌	❌	✅	✅
IS（意向共享锁）	❌	✅	✅	✅

行级锁 (Row Lock)

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在 InnoDB存储引擎中。InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

行锁（Record Lock）
间隙锁（Gap Lock）
临键锁（Next-Key Lock）

行锁 / 记录锁（Record Lock）

锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。

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

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

两种行锁的兼容情况如下:

常见的SQL语句，在执行时，所加的行锁如下：

案例

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

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

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

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

间隙锁（Gap Lock）

锁定索引记录间隙（不含该记录），左右开区间，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

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

加间隙锁的规则

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

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

临键锁（Next-Key Lock）

行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap，左开右闭。在RR隔离级别下支持。

案例

A. 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁

B. 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。

分析： InnoDB的B+树索引，叶子节点是有序的双向链表。假如，我们要根据这个二级索引查询值为18的数据，并加上共享锁，我们是只锁定18这一行就可以了吗？并不是，因为是非唯一索引，这个结构中可能有多个18的存在，所以，在加锁时会继续往后找，找到一个不满足条件的值（当前案例中也就是29）。此时会对18加临键锁，并对29之前的间隙加锁。

C. 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

查询的条件为id>=19，并添加共享锁。此时我们可以根据数据库表中现有的数据，将数据分为三个部分： [19] (19,25] (25,+∞] 所以数据库数据在加锁是，就是将19加了行锁，25的临键锁（包含25及25之前的间隙），正无穷的临键锁(正无穷及之前的间隙)。

乐观锁/悲观锁

悲观锁（Pessimistic Locking）

假设冲突必然发生，因此在访问数据前先加锁，阻止其他事务同时修改。
适用场景：写操作频繁、并发冲突概率高的场景（如库存扣减、金融转账）。

实现方式：

-- 行级锁
-- 共享锁（S锁）：允许多事务同时读
SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE;

-- 排他锁（X锁）：阻止其他事务读写
SELECT * FROM products WHERE id = 1 FOR UPDATE;

-- 表级锁
LOCK TABLES products WRITE;  -- 写锁（排他）
UNLOCK TABLES;

优点：确保数据一致性，避免脏写。
缺点：
- 增加锁等待时间，降低并发性能。
- 可能导致死锁（如事务循环等待锁）。

乐观锁（Optimistic Locking）

假设冲突很少发生，不提前加锁，而是在提交时检查数据是否被修改。
适用场景：读操作频繁、冲突概率低的场景（如商品浏览量统计）。

实现方式：

版本号（Version）：

-- 表结构增加 version 字段
CREATE TABLE products (
  id INT PRIMARY KEY,
  stock INT,
  version INT DEFAULT 0
);

-- 事务1：读取数据
SELECT stock, version FROM products WHERE id = 1;

-- 事务1：更新时校验 version
UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = 上次读取的version;

时间戳（Timestamp）：类似版本号，使用时间戳字段记录数据修改时间。
优点：
- 无需加锁，提升并发性能。
- 避免死锁。
缺点：
- 需要应用层处理冲突（如重试机制）。
- 不适合高冲突场景（重试频繁会降低效率）。

InnoDB引擎

InnoDB的逻辑存储结构

表空间

表空间是InnoDB存储引擎逻辑结构的最高层，如果用户启用了参数 innodb_file_per_table(在 8.0版本中默认开启) ，则每张表都会有一个表空间（xxx.ibd），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。
段

段，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个Extent（区）。
区

区，表空间的单元结构，每个区的大小为1M。默认情况下，InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。
页

页，是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
行

行，InnoDB 存储引擎数据是按行进行存放的。

在行中，默认有两个隐藏字段：
- Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。
- Roll_pointer：每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

架构

MySQL5.5 版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构。

内存结构

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

Buffer Pool

InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了尽可能弥补这两者之间的I/O效率的差值，就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘I/O。

在InnoDB的缓冲池中不仅缓存了索引页和数据页，还包含了undo页、插入缓存、自适应哈希索引以及 InnoDB的锁信息等等。

缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增、删、改、查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：
- free page：空闲page，未被使用
- clean page：被使用page，数据没有被修改过
- dirty page：脏页，被使用page，数据被修改过，页中数据与磁盘的数据产生了不一致
在专用服务器上，通常将多达80％的物理内存分配给缓冲池。参数设置： show variables like ‘innodb_buffer_pool_size’;
Change Buffer

Change Buffer，更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据Page 没有在Buffer Pool中，是不会直接操作磁盘，而是会将数据变更存在更改缓冲区 Change Buffer 中，在以后数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。

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

自适应hash索引，用于优化对Buffer Pool数据的查询。MySQL的innoDB引擎中虽然没有直接支持 hash索引，但是给我们提供了一个功能就是这个自适应hash索引。hash索引在进行等值匹配时，一般性能是要高于B+树的，因为hash索引一般只需要一次IO即可，而B+树，可能需要几次匹配，所以hash索引的效率要高，但是hash索引又不适合做范围查询、模糊匹配等。

InnoDB存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。自适应哈希索引，无需人工干预，是系统根据情况自动完成。

1	参数： adaptive_hash_index

Log Buffer

日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log 、undo log），默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。

参数
- innodb_log_buffer_size：缓冲区大小
- innodb_flush_log_at_trx_commit：日志刷新到磁盘时机，取值主要包含以下三个：
  - 1：日志在每次事务提交时写入并刷新到磁盘，默认值
  - 0: 每秒将日志写入并刷新到磁盘一次
  - 2: 日志在每次事务提交后写入，并每秒刷新到磁盘一次

磁盘结构

System Tablespace

系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典、undolog等)。参数：innodb_data_file_path。系统表空间，默认的文件名叫 ibdata1。
File-Per-Table Tablespaces

如果开启了innodb_file_per_table开关，则每个表的文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中。开关参数：innodb_file_per_table ，该参数默认开启。我们每创建一个表，都会产生一个表空间文件（.ibd）。
General Tablespaces

通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间。

创建表空间

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

创建表时指定表空间

1	CREATE TABLE xxx ... TABLESPACE ts_name;

Undo Tablespaces

撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储 undo log日志。
Temporary Tablespaces

InnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。
Doublewrite Buffer Files

双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。
Redo Log

重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时,发生错误时, 进行数据恢复使用。以循环方式写入重做日志文件，涉及两个文件：

后台线程

在Innodb存储引擎中，后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据。此外它会将已经修改的数据文件刷新到磁盘文件中，保证在不发生异常的情况下，Innodb能够恢复到正常的运行状态。

Master Thread

核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收。

IO Thread

在InnoDB存储引擎中大量使用了AIO来处理IO请求, 这样可以极大地提高数据库的性能，而IO Thread主要负责这些IO请求的回调。

线程类型	默认个数	职责
Read thread	4	负责读操作
Write thread	4	负责写操作
Log thread	1	负责将日志缓冲区刷新到磁盘
Insert buffer thread	1	负责将写缓冲区内容刷新到磁盘

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

1	show engine innodb status \G;

Purge Thread

主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用它来回收。

Page Cleaner Thread

协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞。

事务原理

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

事务特性：

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

redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用。

没有redo log，可能会存在什么问题
- 在InnoDB引擎中的内存结构中，主要的内存区域就是缓冲池，在缓冲池中缓存了很多的数据页。
- 当我们在一个事务中，执行多个增删改的操作时，InnoDB引擎会先操作缓冲池中的数据，如果缓冲区没有对应的数据，会通过后台线程将磁盘中的数据加载出来，存放在缓冲区中。
- 然后将缓冲池中的数据修改，修改后的数据页我们称为脏页。
- 脏页会在一定的时机，通过后台线程将缓冲区的数据刷新到磁盘中，从而保证缓冲区与磁盘的数据一致。
- 但是缓冲区的脏页数据并不是实时刷新的，而是一段时间之后将缓冲区的数据刷新到磁盘中，假如刷新到磁盘的过程出错了，而提示给用户事务提交成功，而数据却没有持久化下来，这就出现问题了，没有保证事务的持久性。
InnoDB中提供了一份日志 redo log
- 有了redolog之后，当对缓冲区的数据进行增删改之后，会首先将操作的数据页的变化，记录在redo log buffer中。
- 在事务提交时，会将redo log buffer中的数据刷新到redo log磁盘文件中。
- 过一段时间之后，如果刷新缓冲区的脏页到磁盘时，发生错误，此时就可以借助于redo log进行数据恢复，这样就保证了事务的持久性。
- 而如果脏页成功刷新到磁盘或者涉及到的数据已经落盘，此时redolog就没有作用了，就可以删除了，所以存在的两个redolog文件是循环写的。
  
  为什么每一次提交事务，要刷新redo log 到磁盘中呢，而不是直接将buffer pool中的脏页刷新到磁盘呢 ?
  
  因为在业务操作中，我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘。而redo log在往磁盘文件中写入数据时，由于是日志文件，所以都是顺序写的。顺序写的效率，要远大于随机写。这种先写日志的方式，称之为 WAL（Write-Ahead Logging）。

undo log

回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和 MVCC(多版本并发控制) 。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的 update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment 回滚段中，内部包含1024个undo log segment。
Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。

MVCC

基本概念

当前读

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

案例
- 上面案例中当前隔离级别是RC（可重复读）中，同时开启两个事务。在事务A中，使用普通select 查询语句无法查询事务B中修改的数据。
- 但是在查询语句后面加上了 lock in share mode 共享锁，此时是当前读操作。当然，我们加排他锁的时候，也是当前读操作。可以读取到事务B最新提交的内容。
快照读

简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
- Read Committed 读已提交：每次select，都生成一个快照读
- Repeatable Read 可重复高读：开启事务后第一个select语句才是快照读的地方。即第一次select查询产生快照读，后面的select查询直接使用前面的快照数据
- Serializable 串行化：快照读会退化为当前读，每次读取都需要加锁
案例

在测试中,我们看到即使事务B提交了数据,事务A中也查询不到。原因就是因为普通的select是快照读，而在当前默认的RR隔离级别下，开启事务后第一个select语句才是快照读的地方，后面执行相同的select语句都是从快照中获取数据，可能不是当前的最新数据，这样也就保证了可重复读。
MVCC

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

隐藏字段

当我们创建表的时候，除了我们自己定义的字段以为，InnoDB还会自动的给我们添加三个隐藏字段。

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

undo log

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。
当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

undo log 版本链

演示：如果四个事务需要同时访问同一条记录时。

DB_TRX_ID : 代表最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID，是自增的。 DB_ROLL_PTR ：由于这条数据是才插入的，没有被更新过，所以该字段值为null。

不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

readview

ReadView（读视图）是快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。

不同的隔离级别，生成ReadView的时机不同：

READ COMMITTED：在事务中每一次执行快照读时生成ReadView。
REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

原理分析

RC隔离级别

RC隔离级别下，在事务中每一次执行快照读时生成ReadView。

分析：
1. 将左下记录根据 DB_TRX_ID （当前事务id 为4）带入右下版本链规则 ①②③④ 中，发现都不成立。说明本次快照读查找的数据不是事务id为4的记录。
2. 按照版本链往下找（根据表尾地址查找）下一条记录，找到事务id为3的记录，在 ①②③④ 都不成立，继续向下寻找。
3. 找到一条事务id为2的记录，发现②成立。说明本次快照读查找的数据=是事务id为2的记录。
RR隔离级别

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。而RR 是可重复读，在一个事务中，执行两次相同的select语句，查询到的结果是一样的。

在RR隔离级别下，只是在事务中第一次快照读时生成ReadView，后续都是复用该 ReadView，那么既然ReadView都一样， ReadView的版本链匹配规则也一样，那么最终快照读返回的结果也是一样的。

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

MySql进阶

MySQL体系结构

存储引擎

InnoDB

介绍

特点

文件结构

逻辑存储结构

适用场景

MyISAM

介绍

特点

文件结构

适用场景

Memory

介绍

特点

文件结构

适用场景

InnoDB, MyISAM, Memory的区别，使用场景

索引

介绍

特点

索引结构

概述

二叉树

B-Tree

B+Tree

Hash

索引分类

回表查询

思考题

索引语法

SQL性能分析

SQL执行频率

慢查询日志

profile详情

explain

索引使用优化

单列索引与联合索引

前缀索引

最左前缀法则

🧩 四种典型使用场景分析

✅ 场景 1：完整使用索引 (最佳)

✅ 场景 2：使用最左连续列

✅ 场景 3：仅使用最左列

❌ 场景 4：违反最左前缀（常见错误）

✅ 场景 5：条件编写的先后顺序

索引失效情况

🚫 1. 违反最左前缀法则（复合索引）

🚫 2. 在索引列上使用函数或计算

🚫 3. 隐式类型转换

🚫 4. 使用 OR 连接非索引列

🚫 5. LIKE 以通配符开头

🚫 6. 范围查询后的列失效

🚫 7. 使用 != 或 <>

🚫 8. 索引列使用 IS NULL/IS NOT NULL

🚫 9. 数据分布不均导致优化器放弃索引

🚫 10. 使用 NOT IN

SQL提示

覆盖索引

索引设计原则

SQL优化（后面在做）

视图/存储过程/触发器

视图

基本语法

检查选项

视图的更新

视图的作用

存储过程

特点

基本语法

变量

if

参数

case

while

repeat

loop

游标

🚫 4. 使用 `OR` 连接非索引列

🚫 5. `LIKE` 以通配符开头

🚫 7. 使用 `!=` 或 `<>`

🚫 8. 索引列使用 `IS NULL`/`IS NOT NULL`

🚫 10. 使用 `NOT IN`