【后端之旅】四、MySQL 该怎么写（中篇）围绕 MySQL 的存储引擎、索引、优化、视图、存储过程、存储函数、触发器、

这里是 MySQL 相关知识点的中篇。全篇内容主要围绕着如何优化您的 SQL 性能展开，让你了解 MySQL 的底层工作原理，从而写出初阶人员无法写出的高性能 SQL。

存储引擎

MySQL 体系结构
- 第一级
  - 客户端连接器 —— 如 JDBC、ODBC、Python、Ruby
    - 系统管理和控制工具 —— 如 Authentication、Thread Reuse、Connection Limits、Check Memory、Caches
    - 连接池 —— 如 Backup & Recovery、Security、Replication、Cluster、Administrtion、Configuration、Migration & Metadata
      - SQL 接口 —— 如 DML、DDL、Stored Procedures、Views、Triggers
      - 解析器 —— 如 Query Translation、Object Privilege
      - 查询优化器 —— 如 Acess Paths、Statistics
      - 缓存 —— 如 Global and Engine Specific Caches、Buffers
- 第二级
  - 可插拔存储引擎 —— 如 InnoDB、MyISAM、CSV、Archive、Memory 等，主要负责内存、索引、存储等管理
- 第三级
  - 系统文件 —— 如 NTFS、ext4
  - 文件和日志 —— 如 Redo、Undo、Data、Index、Binary、Error、Query and Slow
存储引擎简介
- 存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式
- 存储引擎是基于表的，不是基于库的（所以存储引擎也被称为表类型）
- 相关命令
```
-- 建表时指定存储引擎，默认就是 InnoDB
CREATE TABLE 表名 (
    字段列表,
    ...
) ENGINE=InnoDB [COMMENT 表注释];

-- 查看当前数据库支持的存储引擎
SHOW ENGINES;
```
存储引擎特点
- InnoDB
  - DML 操作遵循 ACID 模型，支持事务
  - 行级锁，提高并发访问性能
  - 支持外键约束，保证数据的完整性和正确性
- MyISAM
  - 不支持事务，不支持外键
  - 支持表锁，不支持行锁
  - 访问速度快
- Memory
  - 内存存放
  - hash 索引
InnoDB
- InnoDB 的逻辑存储结构:
  - Tablespace —— 表空间，最大支持 64TB
    - Segment —— 段
      - Extent —— 区，大小固定，为 1MB
        
        Page —— 页，大小固定，为 16KB
        
        Row —— 行
        
        Colume —— 字段

索引

索引结构
- MySQL 的索引是在存储引擎层实现的，不同的存储引擎有不同的结构:
  - B+Tree 索引 —— 最常见的索引类型，大部分引擎都支持该类型的索引。MySQL 使用的是改进型的 B+Tree 数据结构
  - Hash 索引 —— 底层数据结构用哈希表实现，只有精确匹配索引列的查询(如 =、in)有效，不支持范围查询(如 between、>、<)。在 MySQL 中只有 Memory 引擎支持
  - R-Tree 索引 —— 又叫空间索引。是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，较少使用
  - Full-text —— 又叫全文索引。是一种通过建立倒排索引，快速匹配文档的方式，较少使用
- InnoDB 为何采用 B+Tree 数据结构而不是红黑树、B-Tree 等数据结构:
  - 相对于二叉树，层级更少，搜索效率高
  - B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样会导致一页中存储的键值减少，指针也跟着减少，要(像 B+Tree)同样保存大量数据，只能增加树的高度，导致性能降低
  - MySQL 对 B+Tree 做了特殊处理，使得叶子节点形成了一个双向循环链表，有利于范围匹配及排序操作
索引分类
- 根据索引的特点分类:
  - 主键索引
    - 对应的关键字为 PRIMARY
    - 是针对表中主键创建的索引
    - 默认自动创建，只能有一个
  - 唯一索引
    - 对应的关键字为 UNIQUE
    - 避免同一个表中某数据列中的值重复
  - 常规索引
    - 用于快速定位特定数据
  - 全文索引
    - 对应的关键字为 FULLTEXT
    - 用于查找文本中的关键词，不是用于比较索引中的值
- 根据索引的存储形式分类:
  - 聚集索引
    - 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。必须有，且只有一个
    - 选取规则
      - 如果存在主键，主键索引就是聚集索引
      - 如果不存在主键，则将第一个唯一索引作为聚集索引
      - 如果表没有主键，也没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引
  - 二级索引(辅助索引)
    - 将数据与索引分开存储，索引结构的叶子节点保持了该行的主键(值)。可以创建多个二级索引
    - 与二级索引相关的查询叫回表查询，因为它会先在二级索引中查找到 ID，然后拿该 ID 去聚集索引查找到整行数据

索引语法

-- 创建索引，不带 UNIQUE 或 FULLTEXT 关键字的话，默认就是常规索引
-- 如果只关联一个字段，则为单列索引；如果关联多个字段，则是联合索引或组合索引
-- 索引名一般采用 `idx_表名_字段名` 来命名
CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名 (字段名 [ASC | DESC], ...) [COMMENT 索引注释];

-- 查看索引
SHOW INDEX FROM 表名;

-- 以 键: 值 为格式查看索引（对于其他 SELECT 查询亦有效）
SHOW INDEX FROM 表名\G;

-- 删除索引
DROP INDEX 索引名 ON 表名;

SQL 性能分析

-- 查看服务器状态信息，可查看当前数据库的 INSERT、UPDATE、DELETE、SELECT 的执行频次
-- 下划线有 7 个
SHOW [SESSION | GLOBAL] STATUS LIKE 'Com_______';


-- 慢查询日志(记录了所有执行时间超过指定参数 long_query_time 默认为 10 秒的所有 SQL 语句)
-- 慢查询日志默认没有开启，在 /etc/my.cnf 中配置 slow_query_log=1 和 long_query_time=秒数 参数即可
-- 慢查询日志在 Linux 系统中默认位于 /var/lib/mysql/localhost-slow.log 中


-- 查看当前 MySQL 是否支持 profile 操作。默认是关闭的
SELECT @@have_profiling;

-- 开启 profiling
SET [SESSION | GLOBAL] profiling = 1;

-- 查看每一条 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 语句执行过程中如何连接和连接的顺序
-- 基本语法为: EXPLAIN + `SELECT 语句`
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 认为必须要执行查询的行数，是一个估计值
- filtered —— 表示返回结果的行数占需读取行数的百分比。当然是越大越好
- Extra ——
  - using index condition: 查找使用了索引，但是需要回表查询数据
  - using where;using index: 查找使用了索引，但是需要的数据都在索引列中能找到，不需要回表查询数据
索引使用
- 最左前缀法则
  - 如果索引了多列(联合索引)，要遵守最左前缀法则，即查询从索引的最左列开始，且不跳过索引中的列
  - 如果跳跃某一列，索引将部分失效，即该联合索引在后面字段匹配时会失效。所以如果查询匹配缺少(联合索引的)最左列，则会导致查询走全表扫描
  - 查询的列与匹配的顺序无关，只与匹配的列是否存在有关
- 范围查询
  - 联合索引中，如果出现范围查询 >、<，则范围查询右侧的列的索引失效。可以通过使用 >=、<= 解决
- 索引列运算
  - 不要在索引列上进行运算操作，否则索引将失效
- 字符串不加引号
  - 字符串类型字段使用时，如果不加引号，索引将失效
- 模糊查询
  - 如果仅仅是尾部模糊匹配，索引不会失效
  - 如果是头部模糊匹配，索引失效
- or 连接的条件
  - 用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
- 数据分布影响
  - 如果 MySQL 评估使用索引比全表更慢，则不使用索引
- SQL 提示
  - SQL 提示是优化数据库的一个重要手段，简单来说，就是在 SQL 语句中加入一些认为的提示来达到优化操作的目的
  - use index:
```
-- 建议 MySQL 使用特定索引
EXPLAIN SELECT * FROM 表名 USE INDEX(索引名) WHERE ...;
```
  - ignore index:
```
-- 建议 MySQL 忽略特定索引
EXPLAIN SELECT * FROM 表名 IGNORE INDEX(索引名) WHERE ...;
```
  - force index:
```
-- 强制 MySQL 使用特定索引
EXPLAIN SELECT * FROM 表名 FORCE INDEX(索引名) WHERE ...;
```
- 覆盖索引
  - 即一次索引扫描就找到了所有所需要的字段
  - 尽量使用覆盖索引(即查询使用了索引，并且需要返回的列，在改索引中已经全部能够找到)，减少 SELECT * 这种使用方式
- 前缀索引
  - 对于 varchar、text 这种字段类型，可以只将字符串的一部分前缀，建立索引，从而节约索引空间，提高索引效率
```
-- 根据索引的选择性来确定前缀长度，而选择性是指不重复的索引值和数据表的记录总数的比值
CREATE INDEX idx_xxxx ON 表名(字段名(前缀长度));
```
  - 计算索引选择性
```
-- 计算整个字段的索引选择性
SELECT COUNT(DISTINCT 字段名) / COUNT(*) FROM 表名;

-- 计算字段值前缀的索引选择性，所以 START 通常都是 1 (因为下标是从 1 开始算)
SELECT COUNT(DISTINCT SUBSTRING(字段名, START, LENGTH)) / COUNT(*) FROM 表名;
```
- 单列索引与联合索引
  - 如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。即尽可能避免回表查询
索引设计原则
- 针对于数据量较大，且查询比较频繁的表建立索引
- 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引
- 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
- 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
- 尽量使用联合索引，减少单列索引，避免回表
- 用控制索引的数量，因为所以越多，维护所以结构的代价就越大，会影响增删改的效率
- 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含 NULL 值时，可以更好地确定哪个索引最有效地用于查询

SQL 优化

插入数据

少量插入数据(通常是少于一万条记录)时的优化原则:

-- 批量插入
INSERT INTO 表名 VALUES (...),(...),(...);

-- 手动提交事务
START TRANSACTION;
INSERT INTO 表名 VALUES (...),(...),(...);
INSERT INTO 表名 VALUES (...),(...),(...);
INSERT INTO 表名 VALUES (...),(...),(...);
COMMIT;

-- 主键顺序插入，性能要高于主键乱序插入

大批量插入数据，使用 LOAD 指令替代 INSERT 语句

# 客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p

-- 设置全局参数 local_infile 为 1，开启从本地加载文件导入数据的开关
SET GLOBAL local_infile = 1;

-- 执行 LOAD 指令将准备好的数据，加载到表结构中
-- 如果第一行不是表头，则应当省略 `IGNORE 1 LINES`
LOAD DATA LOCAL INFILE '绝对路径的文件名' INTO TABLE `表名` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;

主键优化
- 页分裂: 乱序插入时就会出现这种情况，即页剩余空间不足以插入最新的记录，就会将这个页的后半部分数据移动到新创建的页中，并把新记录放置在新页的后面。题外话: 通常来说每个页包含了 2-N 行数据(如果某行数据过大，会出现行溢出现象)
- 页合并: 当页中删除的记录达到 MERGE_THRESHOLD (默认为页的 50%)，InnoDB 会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用
- 满足业务需求的情况下，尽量降低主键的长度(有利于降低二级索引的使用空间)
- 插入数据时，尽量选择顺序插入(避免页分裂现象)，选择使用 AUTO_INCREMENT 自增主键
- 尽量不要使用 UUID 做主键或其他自然主键(如身份证号)
- 业务操作时，避免对主键的修改
order by 优化
- order by 的排序方式(会出现在执行计划表中的 Extra 字段)包括:
  - Using filesort: 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
  - Using index: 通过有序索引顺序扫描直接返回有序数据，即为 using index，不需要额外排序，操作效率高
- 所谓的 order by 优化，就是将 Using filesort 排序优化为 Using index 排序
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
- 尽量使用覆盖索引
- 多字段排序，一个升序(ASC)一个降序(DESC)，此时需要注意联合索引在创建时的规则
- 如果不可避免地出现 FileSort，大数据量排序时，可以适当增加排序缓冲区大小 sort_buffer_size (默认 256KB)
```
-- 查看排序缓冲区大小
SHOW VARIABLES LIKE 'sort_buffer_size';

-- 设置排序缓冲区大小
SET [GLOBAL|SESSION] sort_buffer_size = 1024 * 1024;
```
group by 优化
- 如果 explain 的结果显示了 Using temporary，说明分组时使用了临时表，是性能低下的表现
- 在分组操作时，可以通过索引来提升效率
- 分组操作时，索引的使用也是满足最左前缀法则的
limit 优化
- 一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化
```
EXPLAIN SLECT * FROM 表名 t1, (SELECT id FROM 表名 ORDER BY id limit 3000000,10) t2 WHERE t1.id = t2.id;
```
count 优化
- MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 时会直接返回这个数，效率很高。前提就是没有 WHERE 条件
- InnoDB 引擎在执行 count(*) 时，需要把数据一行一行地从引擎里面读出来，然后累积计数
- count 的主要用法:
  - count(字段名): 有 NOT NULL 约束时与 count(主键) 的结果一致；没有 NOT NULL 约束时，字段值为 null 的行不会被服务层累加
  - count(主键): 会遍历整张表，把每一行的主键 ID 值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加(主键不可能为 null)
  - count(1): InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字 "1" 进去，直接按行进行累加
  - count(*): InnoDB 针对这个形式做了优化，不取值，服务层直接按行进行累加。因此该用法性能最高
- 优化思路
  - 自己计数，例如利用 redis 自行在赠删的过程中维护这个计数
update 优化
- InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。所以 UPDATE 的条件必须有索引

视图

视图(View)是一种虚拟存在的表。视图只保存了查询的 SQL 逻辑，不保存查询结果。视图作用如下:
- 简单 —— 那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部条件
- 安全 —— 通过视图用户只能查询和修改他们所能见到的数据
- 数据独立 —— 帮助用户屏蔽真实表结构变化带来的影响

创建视图

-- `OR REPLACE` 用于替换一个已存在的视图
CREATE [OR REPLACE] VIEW 视图名[(列名列表)] AS SELECT 语句 [WITH [CASCADED | LOCAL] CHECK OPTION];

查询视图

-- 方式一
SHOW CREATE VIEW 视图名;

-- 方式二
SELECT * FROM 视图名 ...;

修改视图

-- 方式一
CREATE OR REPLACE VIEW 视图名[(列名列表)] AS SELECT 语句;

-- 方式二
ALTER VIEW 视图名[(列名列表)] AS SELECT 语句;

删除视图
```
DROP VIEW [IF EXISTS] 视图名;
```
检查选项 WITH [CASCADED | LOCAL] CHECK OPTION 的作用是为了避免插入、更新、删除时不满足定义视图时的 SELECT 语句 中的条件，以保证一致性
MySQL 允许基于另一个视图创建视图，所以检查选项的默认值为 CASCADED。LOCAL 则表示在增删改当前视图时，需要递归地检查其父级视图定义的条件（如果不满足则不允许增删改）
要使视图可更新，需要视图中的行与基础表中的行之间存在一对一的关系。不可更新视图的情况:
- 聚合函数或窗口函数，如 SUM()、MIN()、MAX()、COUNT() 等
- DISTINCT
- GROUP BY
- HAVING
- UNION 或 UNION ALL

存储过程

特点:
- 封装，重用
- 可接收参数，也可返回数据
- 减少网络交互，效率提升

创建:

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

调用:
```
CALL 存储过程名([参数列表]);
```

查看:

-- 查询指定数据库的存储过程及状态信息
SELECT * FROM information_schema.routines WHERE routine_schema = '数据库名';

-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名;

删除:

DROP PROCEDURE [IF EXISTS] 存储过程名;

变量

系统变量: MySQL 服务器提供的，包括全局变量(GLOBAL) 和会话变量(SESSION)

-- 查看所有系统变量
SHOW [SESSION | GLOBAL] VARIABLES;

-- 通过 LIKE 模糊匹配查找变量
SHOW [SESSION | GLOBAL] VARIABLES LIKE '变量名某一部分%';

-- 查看某个系统变量的值
SELECT @@[SESSION. | GLOBAL.]变量名;

-- 设置某个系统变量的值 方式一
SET [SESSION | GLOBAL] 变量名 = 值;

-- 设置某个系统变量的值 方式二
SET @@[SESSION. | GLOBAL.]变量名 = 值;

用户自定义变量: 用户变量不用提前声明，在用的时候直接用 @变量名 引用即可。其作用域为当前连接（即会话）

-- 赋值 方式一 (不推荐)
SET @变量名 = expr [, @变量名2 = expr];

-- 赋值 方式二
SET @变量名 := expr [, @变量名2 := expr];

-- 赋值 方式三
SELECT @变量名 := expr [, @变量名2 := expr];

-- 赋值 方式四，即把查询的结果赋值给变量
SELECT 字段名 INTO @变量名 FROM 表名;

-- 使用，如果变量名未定义，则结果为 NULL
SELECT @变量名;

局部变量: 可用作存储过程内的局部变量和输入参数。访问之前，需要 DECLARE 声明，其作用域为在其声明内的 BEGIN ... END 块

-- 数据类型就是数据库字段的那个类型，如 INT、BIGINT、CHAR、VARCHAR、DATE 等
DECLARE 变量名 数据类型 [DEFAULT 值];

-- 赋值 方式一 (不推荐)
SET 变量名 = expr;

-- 赋值 方式二
SET 变量名 := expr;

-- 赋值 方式三
SELECT 字段名 INTO 变量名 FROM 表名;

-- 使用
SELECT 变量名;

语法

IF 条件1 THEN
    -- SQL 语句
ELSEIF 条件2 THEN       -- 可选
    -- SQL 语句
ELSE                    -- 可选
    -- SQL 语句
END IF;

参数
- 类型
  - IN: 该类参数作为输入，也就是需要调用时传入值。默认类型
  - OUT: 该类参数作为输出，也就是该参数可以作为返回值
  - INOUT: 既可以作为输入参数，也可以作为输出参数
- 语法
```
CREATE PROCEDURE 存储过程名([ IN/OUT/INOUT 参数名 参数类型])
BEGIN
    -- SQL 语句
END;
```

CASE

语法

-- 语法一
CASE case_value
    WHEN value_1 THEN -- SQL 语句
    [WHEN value_2 THEN -- SQL 语句] ...
    [ELSE -- SQL 语句]
END CASE;

-- 语法二
CASE
    WHEN 条件1 THEN -- SQL 语句
    [WHEN 条件2 THEN -- SQL 语句] ...
    [ELSE -- SQL 语句]
END CASE;

WHILE

语法

WHILE 条件 DO
    -- SQL 语句
END WHILE;

REPEAT

语法

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

LOOP

LOOP 实现简单的循环，如果不在 SQL 语句中增加退出循环的条件，则表示死循环
LOOP 可配合两个语句使用:
- LEAVE: 表示退出循环
- ITERATE: 表示跳过本次循环，直接进入下一次循环

语法

-- begin_label 和 end_label 和 label 是一样的
[begin_label:] LOOP
    -- SQL 语句
    LEAVE label;    -- 退出指定标记的循环
    ITERATE label;  -- 直接进入下一次循环
END LOOP [end_label];

游标

游标(Cursor)是用来存储查询结果集的数据类型
在存储过程和函数中，可以使用游标对结果集进行循环的处理
游标的声明必须在变量声明之后

游标的使用:

-- 声明游标
DECLARE 游标名 CURSOR FOR 查询语句;

-- 打开游标
OPEN 游标名;

-- 获取游标记录
FETCH 游标名 INTO 变量名1[, 变量名2, ...];

-- 关闭游标
CLOSE 游标名;

条件处理程序
- 条件处理程序(Handler)可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤
```
-- 声明条件处理程序
-- 这里的意思是: 如果在循环中遇到某些条件值(错误状态码)时，会触发 处理动作，同时执行 statement 这个后续 SQL 语句
DECLARE 处理动作 HANDLER FOR 条件值1 [, 条件值2, ...] statement;
```
- 处理动作(handler_action)包括:
  - CONTINUE: 继续执行当前程序
  - EXIT: 终止执行当前程序
- 条件值(condition_value)包括:
  - SQLSTATE sqlstate_value: sqlstate_value 表示状态码，如 '02000'
  - SQLWARNING: 警告(所有以 01 开头的状态码)，是一种缩写
  - NOT FOUND: 没有找到记录(所有以 02 开头的状态码)，是一种缩写
  - SQLEXCEPTION: 错误(所有没有被 SQLWARNING 和 NOT FOUND 捕获的状态码)，是一种缩写
注意事项
- 在命令行中，执行创建存储过程的 SQL 时，需要通过关键字 DELIMITER 指定 SQL 语句的结束符
```
DELIMITER $$
CREATE PROCEDURE 存储过程名([参数列表])
BEGIN
    -- 各种带有分号的 SQL 语句
END$$
DELIMITER ;
```
- MySQL 服务重新启动后，所设置的全局变量会失效。如果希望不失效，可在 /etc/my.cnf 中配置

存储函数

存储函数是有返回值的存储过程
存储函数的参数只能是 IN 类型（所以不用特别声明输入输出类型）

语法:

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

type 就是随后的函数体的返回值类型，如 INT
可选的 characteristic 说明（MySQL v8.0 后是必选）:
- DETERMINISTIC: 相同的输入参数总是产生相同的结果
- NO SQL: 不包含 SQL 语句
- READS SQL DATA: 包含读取数据的语句，但不包含写入数据的语句

触发器

触发器是与表有关的数据库对象，指在 INSERT/UPDATE/DELETE 之前或之后，触发并执行触发器中定义的 SQL 语句集合
可使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，如 OLD.id、NEW.name
现在触发器只支持行级触发，不支持语句级触发（行级是指某个语句影响了 N 行，就触发 N 次；语句级是指某个语句只影响了 N 行，就只触发 1 次）

语法:

-- 创建触发器（只支持行级触发）
CREATE TRIGGER 触发器名
[BEFORE | AFTER] [INSERT | UPDATE | DELETE] ON 表名 FOR EACH ROW
BEGIN
    -- SQL 语句
END;

-- 查看触发器
SHOW TRIGGERS;

-- 删除触发器(如果没有指定数据库名，则默认为当前数据库)
DROP TRIGGER [数据库名.]触发器名;

锁

全局锁

对整个数据库实例加锁。加锁后整个实例处于只读状态，后续的 DML 的写语句、DDL 语句、已更新操作的事务提交语句都将被阻塞
典型的使用场景是做全库的逻辑备份

示例:

-- 方式一：加锁后只能读，不能增删改
FLUSH TABLES WITH READ LOCK;

-- 
MYSQLDUMP -u root -p 数据库名 > 备份名.sql

UNLOCK TABLES;

-- 方式二：通过单一事务的方式保证备份的数据一致性
MYSQLDUMP --single-transaction -u root -p 数据库名 > 备份名.sql

表级锁
- 每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低
- 表级锁进一步分类:
  - 表锁
    - 表共享读锁(Read Lock)
```
-- 加锁
LOCK TABLES 表名1[, 表名2, ...] READ;

-- 释放锁
UNLOCK TABLES;
```
    - 表独占写锁(Write Lock)
```
-- 加锁
LOCK TABLES 表名1[, 表名2, ...] WRITE;

-- 释放锁
UNLOCK TABLES;
```
  - 元数据锁(Meta Data Lock，即 MDL)
    - MDL 加锁过程是系统自动控制，无需显式使用(在访问一张表时会自动加上)
    - MDL 锁主要作用是维护表元数据的数据一致性，在表上有活动事务时，不可对元数据进行写入操作(就是某张表上存在未提交的事务时，不允许修改表结构)
    - 查看元数据锁:
```
SELECT object_type, object_schema, object_name, lock_type, lock_duration FROM performance_schema.metadata_locks;
```
  - 意向锁
    - 为了避免 DML 在执行时，加的行锁与表锁冲突，在 InnoDB 中引入了意向锁，使得表锁不用检查每行数据是否加锁(使用意向锁来减少表锁的检查)
    - 分类:
      - 意向同享锁(IS):
        
        由 SELECT ... LOCK IN SHARE MODE 添加
        
        与表锁共享锁(read)兼容，与表锁排他锁(write)互斥
      - 意向排他锁(IX):
        
        由 INSERT、UPDATE、DELETE、SELECT ... FOR UPDATE 添加
        
        与表锁共享锁(read)及排他锁(write)均互斥。意向锁之间不会互斥
    - 查看意向锁及行锁:
```
SELECT object_schema, object_name, index_name, lock_type, lock_mode, lock_data FROM information_schema.data_locks;
```
行级锁
- 每次操作锁住对应的行数据
- 该类型的锁粒度最小，发生锁冲突的概率最低，并发度最高
- 应用在 InnoDB 存储引擎中
- InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的(而不是对记录加的锁)
- 分类:
  - 行锁(REC_NOT_GAP): 锁定单个行记录的锁
    - 共享锁(S): 允许一个事务是读一行，阻止其他事务获得相同数据集的排它锁
    - 排它锁(X): 允许获取排它锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排它锁
  - 间隙锁(GAP): 锁定索引记录间隙(不含该记录)
    - 间隙锁唯一目的是防止其他事务插入间隙
    - 间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁
    - 索引上的等值查询(唯一索引)，给不存在的记录加锁时，优化为间隙锁
    - 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，临键锁退化为间隙锁
    - 索引上的范围查询(唯一索引)，会访问到不满足条件的第一个值为止
  - 临键锁(Next-Key Lock): 行锁与间隙锁组合，同时锁住数据和数据前面的间隙
- InnoDB 的行锁是针对于索引加的锁，如果不通过索引条件检索数据，那么 InnoDB 将对表中的所有记录加锁，此时就会升级为表锁

InnoDB 核心

逻辑存储结构
- 表空间(Tablespace): ibd 文件，一个 MySQL 实例可以对应多个表空间，用于存储记录、索引等数据。表空间用来管理多个段(Segment)
- 段(Segment): 段，分为数据段(Leaf node segment)、索引段(Non-leaf node segment)、回滚段(Rollback segment)，InnoDB 是索引组织表，数据段就是 B+ 树的叶子节点，索引即 B+ 树的非叶子节点。段用来管理多个区(Extent)
- 区(Extent): 区，表空间的单元结构，每个区的大小为 1M。默认情况下，InnoDB 存储引擎中页大小为 16KB，即一个区中一共有 64 个连续的页(Page)
- 页(Page): 页，是 InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区
- 行(Row): InnoDB 存储引擎数据是按行进行存放的
  - Trx_id: 每次对某条记录进行改动时，都会把对应的事务 id 赋值给 trx_id 隐藏列
  - Roll_pointer: 每次对某条记录进行增删改时，都会把旧的版本写入到 undo 日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到记录修改前的信息
架构
- In-Memory Structures —— 内存结构
  - Buffer Pool —— 缓冲池
    - 缓冲池里面可以缓存磁盘上经常操作的真实数据
    - 缓冲池以页(Page)为单位，底层采用链表数据结构管理这些页。根据状态，页又分为三种类型
      - 空闲页(free page): 即未被使用的页
      - 净页(clean page): 被使用过的页，但数据未被修改过
      - 脏页(dirty page): 被使用过的页，数据被修改过。即页中数据与磁盘数据产生了不一致
  - Change Buffer —— 更改缓冲区
    - 更改缓冲区针对于非唯一二级索引页
    - 执行 DML 语句时，如果这些数据页不在 Buffer Pool 中，则不会直接操作磁盘，而会将数据变更存放于 Change Buffer 中，在未来数据被读取时，再将数据合并到 Buffer Pool 中，最后将合并后的数据刷新到磁盘中
  - Log Buffer —— 日志缓冲区
    - 用来保存要写入到磁盘中的 log 数据(redo log、undo log)，默认大小为 16MB
    - 日志缓冲区的日志会定期刷新到磁盘中
  - Adaptive Hash Index —— 自适应哈希索引
    - 无需人工干预，是系统根据情况自动完成
- On-Disk Structures —— 磁盘结构
  - System Tablespace —— 系统表空间
    - 它是更改缓冲区的存储区域
  - File-Per-Table Tablespaces —— 每个表的独立表空间
    - 它包含单个 InnoDB 表的数据和索引
  - General Tablespacse —— 通用表空间
    - 是需要通过 CREATE TABLESPACE 语法创建的表空间
```
CREATE TABLESPACE 表空间名 ADD DATAFILE '表空间对应的磁盘文件名' ENGINE=InnoDB;
```
    - 在创建表时，可以指定该表空间
  - Undo Tablespaces —— 撤销表空间
    - MySQL 实例在初始化时会自动创建两个默认的 undo 表空间，用于存储 undo log
    - 撤销表空间的初始大小为 16MB
  - Temporary Tablespaces —— 临时表空间
    - 用于存储用户创建的临时表等数据
  - Doublewrite Buffer Files —— 双写缓冲区
    - InnoDB 将数据页从 Buffer Pool 刷新到磁盘前，先将数据写入到双写缓冲区，便于系统异常时恢复数据
    - 文件以 .dblwr 结尾
  - Redo Log —— 重做日志
    - 用于实现事务的持久性
    - 在刷新脏页到磁盘时，发生错误时，进行数据恢复使用
- Operationing System Cache —— 后台线程
  - Master Tread —— 核心后台线程
    - 负责调度其他线程，以及将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性
    - 主要工作还包括脏页的刷新、合并插入缓存、undo 页的回收
  - IO Thread —— IO 线程
    - 负责 AIO 异步请求的回调
  - Purge Tread —— 回滚线程
    - 主要用于回收事务已经提交了的 undo log（在事务提交之后，undo log 就不再需要了，就用它来回收）
  - Page Cleaner Thread —— 页刷新线程
    - 协助 Master Thread 刷新脏页到磁盘的线程
    - 它可以减轻 Master Thread 的工作压力，减少阻塞
事务原理
- 通过 redo log 和 undo log 两种日志来保证原子性、一致性、持久性:
  - redo log 记录了事务提交时的物理修改，用来实现事务的持久性
  - redo log 由 redo log buffer 和 redo log file 两部分组成
  - undo log 用于记录数据被修改前的信息，用来实现回滚和 MVCC（多版本并发控制）
  - undo log 采用段的方式进行管理和记录
- 通过 Lock 和 MVCC 来保证隔离性
MVCC
- 当前读 —— 读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
- 快照读 —— 简单的 SELECT (不加锁)就是快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。每次 SELECT，都会生成一个快照读
- MVCC —— 多版本并发控制，指维护一个数据的多个版本，使得读写操作没有冲突。MVCC 的具体实现需要依赖于:
  - 数据库记录中的三个隐式字段:
    - DB_TRX_ID: 最近修改事务 ID
    - DB_ROLL_PTR: 回滚指针
    - DB_ROW_ID: 隐藏主键（如果表结构没有指定主键，将会生成该隐藏字段）
  - undo log 日志
  - ReadView
    - 是快照读 SQL 执行时 MVCC 提取数据的依据，记录并维护系统当前活跃的事务(未提交的) id
    - 核心字段:
      - m_ids: 当前活跃的事务 ID 集合
      - min_trx_id: 最小活跃事务 ID
      - max_trx_id: 最大活跃事务 ID + 1（因为事务 ID 是自增的），又叫预分配事务 ID
      - creator_trx_id: ReadView 创建者的事务 ID
    - 使用 trx_id （当前事务 ID）与四个核心字段进行比较，来确定版本链的访问规则:
      - trx_id == creator_trx_id ? 可以访问该版本
      - trx_id < min_trx_id ? 可以访问该版本（说明数据已经提交了）
      - trx_id > max_trx_id ? 不可以访问该版本（说明事务是在 ReadView 生成后才开启）

MySQL 管理

系统数据库
- information_schema —— 提供访问数据库的元数据（包括各种表和视图），包含数据库、表、字段类型、访问权限等信息
- mysql —— 存储 MySQL 服务器正常运行所需的各种信息，如时区、主从、用户、权限等
- performance_schema —— 为 MySQL 服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数
- sys —— 包含了一系列方便 DBA 和开发人员利用 performance_schema 性能数据库进行性能调优和诊断的视图
常用工具
- mysql —— mysql 的客户端工具
```
-- 基本语法为: mysql [options] database_name
-- -e 表示需要执行的 SQL 语句，执行完后退出，而不登录
mysql -hremotehost -Pport -uroot -p123456 dbname -e "select * from table_name"
```
- mysqladmin —— 一个执行管理操作的客户端程序，可用来检查服务器的配置和当前状态、创建并删除数据库等
```
-- 基本语法为: mysqladmin [options] command
mysqladmin -hremotehost -Pport -uroot -p123456 version
```
- mysqlbinlog —— 查看 MySQL 服务器的二进制日志文件
```
-- 基本语法为: mysqlbinlog [options] binlog_files
mysqlbinlog binlog.000001
```
- mysqlshow —— 客户端对象查找工具，用于很快地查找存在哪些数据库、数据库中的表、表中的列或索引及其统计信息
```
-- 基本语法为: mysqlshow [options] [database_name|table_name|column_name|index_name]
mysqlshow -hremotehost -Pport -uroot -p123456 dbname
```
- mysqldump —— 备份数据库，生成 SQL 脚本文件，可用于恢复数据库或迁移数据库。备份内容包含成绩表、插入表的 SQL 语句
```
-- 基本语法为: mysqldump [options] database_name [table_names]
mysqldump -hremotehost -Pport -uroot -p123456 dbname > dbname.sql
```
- mysqlimport —— 导入 mysqldump 加 -T 参数后导出的文本文件
- source —— 导入 sql 文件（会执行里面的 SQL 语句）