学习笔记--Mysql系统性(进阶)

存储引擎

Mysql体系结构

连接层，server层，引擎层，存储层

连接层：连接层负责处理客户端与MySQL服务器之间的连接和通信。它接收客户端的连接请求，并建立与客户端的网络连接。

Server层：包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数 （如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

引擎层：负责数据的存储和检索。架构模式是插件式，服务器通过AP和存储引擎进行通信。支持 InnoDB、 MylSAM、 Memory 等多个存储引擎。

存储层： MYSQL的物理存储部分，负责将数据(如：redolog、undolog、数据、索引l、二进制日志、错误日志、查询 日志、慢查询日志等）存储在磁盘上。

存储引擎

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

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

存储引擎：InnoDB(MySQL 5.5后默认的存储引擎)

特点：

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

文件：

逻辑存储结构：

表空间->段->区->页->行

一区可以有64个连续的页

各个存储引擎区别

InnoDB引擎与MyISAM引擎的区别 ?

• InnoDB 支持事务，而MyISAM不支持
• InnoDB 支持行锁和表锁，而MyISAM仅支持表锁, 不支持行锁
• InnoDB 支持外键, 而MyISAM不支持

适用场景：

InnoDB：对事务的完整性有高要求，且在并发条件下要求数据的一致性，数据操作包含很多更删操作

MyISAM ： 以读操作和插入操作为主，有少量更新、删除操作，且对事务完整性、并发性要求不高

MEMORY：数据保存在内存中，访问速度快，用于临时表及缓存。缺陷就是 对表的大小有限制，太大的表无法缓存在内存中，且断电后数据消失 无法保障数据的安全性。

MyISAM和MEMORY被MongoDB和Redies等 NoSQL 的DBMS所取代

索引

索引（index）是MySQL中高效获取数据的树结构（有序）

优缺点

• 优点：

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

• 缺点：

• • 索引占用了数据库的空间 （磁盘便宜
  • 索引提高了查询的效率，降低了更新表（Insert，update，delete）的速度，因为增删改表也需要同时维护索引。 （查询的次数远大于增删改操作的次数

索引结构

哈希

采用hash算法 将键key换算成新的hash值，映射到对应槽位上，存储在hash表中。若产生冲突，可以采用拉链法（相同的key往后延申成链表），线性探测法（逐个找哈希表中的空闲位置），二次探测法（以二次函数为步长找哈希表中空闲位置），双重哈希法（对key再计算一次hash值）等解决冲突

特点：

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

注意：

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

二叉树

红黑树

B-Tree 多路平衡查找树

B+树

MySQL优化后的B+ Tree：

索引分类

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

以下哪个SQL语句的执行效率会更高，为什么？（id为主键，name字段创建的有索引

① select * from user where id = 10;

② select * from user where name = 'Arm';

答：语句①只需要一次索引扫描，语句②需要先查找主键 再回表使用聚集索引获取一整行数据

因此语句①的执行效率会更高

索引语法

• 创建索引

CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name, ...) ;
#index_name 索引名；index_col_name, ...多个字段 

如果一个索引只关联一个字段，则该索引称为单列索引

如果一个索引关联多个字段，则该索引称为 联合索引/组合索引

• 查看该表的所有索引 SHOW INDEX FROM table_name ;
• 删除该表的indext_name的索引 DROP INDEX index_name ON table_name ;

SQL性能分析

执行频率，慢查询日志，profile，explain

索引使用

联合索引

一个索引包含了多个列

最左前缀法则：联合索引的使用要遵循最左法则，最左前缀法则指的是查询从索引的最左列开始， 并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)

单列索引

即一个索引只包含单个列

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时

建议建立联合索引， 而非单列索引。

前缀索引（字符串的前缀）

验证索引效率

索引失效情况

• 不遵循最左前缀法则(联合索引)：没有有从索引的最左列开始，联合索引失效；中间跳过了索引的中间字段，则该字段后的联合索引都失效
• 范围查询(联合索引)：联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效

# profession、age使用到了索引， 49
explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';
# profession、age、status三个字段都使用到了索引  57
explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';

• 索引列运算：在索引列上进行运算操作， 索引将失效
• 字符串不加引号：字符串类型字段使用时，不加引号，索引将失效
• 模糊查询：尾部模糊匹配，索引不会失效；头部模糊匹配，索引失效。
• or连接条件：or左右两侧的字段必须都有索引，若左有 右无 则左的索引失效
• 数据分布影响：如果MySQL评估使用索引比全表更慢，则不使用索引

sql提示

是在SQL语句中加入一些人为提示达成优化操作

# SQL提示 使用者根据自己的倾向 建议/忽略/强制使用 MySQL使用哪个索引进行查询
# profession字段有两个索引：单列索引 profession_idx，联合索引 pro_age_sta_idx

#use index: 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）
explain select * from tb_user use index(profession_idx) where profession = '软件工程';
#ignore index: 忽略指定的索引
explain select * from tb_user ignore index(pro_age_sta_idx) where profession = '软件工程';
#force index: 强制使用索引
explain select * from tb_user force index(profession_idx) where profession = '软件工程';

覆盖索引

查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。尽量使用覆盖索引，减少select *。 尽量返回使用索引就能得到的列，而不是需要回表

设计原则

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

SQL优化

插入数据

• 批量插入数据，手动开启事务：若插入数据较多，不建议使用insert单条插入，MySQL自动开启事务，每一次insert都会开启并且提交事务，影响效率。建议批量插入数据，若数据较多，则手动开启事务，多次批量提交数据后 提交事务。
• Load数据文件：使用MySQL数据库提供的load指令插入大批量数据

# 1. 本地数据库 创建数据库，创建表结构
# 2. 将数据脚本文件上传至 本地/服务器 某路径下
# 3. 客户端连接服务端时，加上参数
mysql –-local-infile -u root -p

# 4. 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

# 执行load指令将准备好的数据，加载到表结构中 
# '/root/sql1.log'数据脚本文件路径， ',' 一行数据的各个列分隔符, '\n' 行和行的分隔符
load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n' ;

主键优化

数据组织方式

• InnoDB存储引擎中，表数据根据主键顺序组织存放的，以该存储方式存储的表为索引组织表
• 行数据存储在聚集索引的叶子节点上
• 表空间 -> 段 -> 区 -> 页

页分裂

主键乱序插入下，如果页满 会导致页分裂；每个页包含了2-N行数据(如果一行数据过大，会行溢出；只有1个数据 形成链表)，根据主键排列

主键顺序插入

主键乱序插入

插入50，但应该放在1页后2页前，但1页和2页都满，因此产生页分裂

开启页3，移动元素23，47，并插入新数据50至新页面

再断开原1页 2页的双向链表，并插入分裂出来的新页面，维护左右的双向链表关系

页合并

删除操作并未 对记录进行物理删除，而是记录被逻辑标记为删除。且其空间变为允许被其他记录声明使用。当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前 或后）看看是否可以将两个页合并以优化空间使用。

主键设计原则

• 满足业务需求的情况下，尽量降低主键的长度。
• 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
• 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
• 业务操作时，避免对主键的修改。

order by 优化

MySQL的排序有两种方式（Explian的Extra内容：

• Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
• Using index : 通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高

Using index的性能高，Using filesort的性能低，在优化排序操作时，尽量要优化为 Using index

order by 语句需要与索引的顺序完全匹配，创建联合索引时默认升序排列

create index age_phodes_idx on tb_user(age asc, phone desc);

order by优化原则：

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
• 尽量使用覆盖索引
• 多字段排序, 一个升序一个降序，需要注意联合索引在创建时的规则（ASC/DESC）
• 如果不可避免的出现filesort，大数据量排序 可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。

group by 优化

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

满足联合索引的最左前缀法则 Explain语句中显示Using idex，不满足则显示Using temporary

limit 优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，并仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序代价非常大

优化：分页查询时，通过创建 覆盖索引 能够提高性能，可以通过覆盖索引加子查询形式进行优化。

# id 为主键索引 执行耗时11.46 sec， 低于直接limit查询的 19.39 sec
select s.* from tb_sku s, (select id from tb_sku order by id limit 9000000,10) as a wehre s.id = a.id;

count 优化

select count(*) from tb_user ; 若数据量很大，在执行count操作时，是非常耗时的

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 时候会直接返回这个 数，效率很高； 但是如果是带条件的count，MyISAM也耗时
• InnoDB 引擎就麻烦了，执行 count(*) 的时候，需要把数据一行一行从引擎里面读取，然后累积计数

优化：自己计数（借助于redis这样的数 据库进行，但对于带条件的count也耗时）

count的用法：

• count（*）：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。
• count（主键）：InnoDB 引擎遍历表，把每一行主键id值取出来返回给服务层。 服务层拿到主键后，直接按行进行累加(主键不可能为null)
• count（字段）：

• • 没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
  • 有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

• count（数字）：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1” 进去，直接按行进行累加。

按照效率排序：

count(字段) < count(主键 id) < count(1) ≈ count(*)

count(*) 和 count(1) 不取值，count(id)取值但不用判断null，count(字段)取值还需要判断null

所以尽量使用 count(*)。

update优化

update 表名 set 字段1 where 字段2 = 'xxx'

当 WHERE 子句中的字段2没有索引时，数据库通常会使用表锁。表锁会锁定整个表，以确保在更新过程中没有其他事务可以修改表中的数据。可能会导致并发性能下降，因为其他事务无法同时访问该表的其他行。

当 WHERE 子句中的字段有索引时，数据库通常会使用行锁。行锁仅锁定满足条件的行，而不是整个表。这样可以提高并发性能，因为其他事务可以同时访问不受锁限制的其他行。

要根据索引字段进行更新，否则行级锁变成表级锁

InnoDB的行锁是针对索引，不是针对记录加的锁，并且该索引不能失效，否则会从行锁 升级为表锁

视图

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

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

视图的创建、查看、修改、删除

# 创建
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
# 查询
SHOW CREATE VIEW 视图名称;      # 查看创建视图语句
SELECT * FROM 视图名称 ...... ; # 查看视图数据
# 修改 方式一
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH
[ CASCADED | LOCAL ] CHECK OPTION ]
# 修改 方式二
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED |
LOCAL ] CHECK OPTION ]
# 删除
DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ..

操作视图，以插入基表中 数据

可以像操作表一样去操作视图，但视图并不存储数据，数据都存放在基表中

# 创建视图
create or replace view stu_v_1 as select id,name,no from student where id <= 10;
# 通过操作视图 以在基表中插入数据
insert into stu_v_1 values('6,Tom'); # 基表插入数据成功
# 插入数据成功，但视图中看不到，因为创建视图时指定了 id<= 10
insert into stu_v_1 values(15,'Amy'); 
#需要在创建视图的时候加上后面的选项，再次插入id=15数据，报错 阻止了不满足视图的数据插入
create or replace view stu_v_1 as select id,name from student where id <= 10; with cascaded check option;

视图的检查选项

使用子句with check option创建视图时，MYSQL会通过视图检查正在更改的每行（插入，更新，删除等操作）是否符合视图的定义。MySQL允许基于一个视图创建另一个视图，并检查依赖视图中的规则以保持一致性。为了确定检查的范围，提供了两个选项

• WITH CASCADED CHECK OPTION 是指在更新视图时，会检查视图中所有相关的视图和基表的约束。如果更新操作违反了任何一个相关表的约束条件，更新将被拒绝。
• WITH LOCAL CHECK OPTION 是指在更新视图时，仅检查当前视图的约束条件。如果更新操作违反了当前视图的约束条件，更新将被拒绝。不会检查其他相关表的约束条件。

视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。

如果视图包含以下任何一 项，则该视图不可更新：

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

举例：create view v1 as select count(*) from student; insert int v1 values(10);

报错，提示当前视图不能插入数据，因为count(*) 的结果 没有 和student一样是一一对应的关系

视图的作用

• 简单：把复杂查询条件定义为视图，简化用户操作
• 安全：数据库可以通过视图 保证了某些数据的安全性，通过视图 用户只能查询和修改它们所能见到的数据
• 数据独立：帮助用户屏蔽真实结构变化带来的影响

存储过程

是一段预先编译并保存在数据库中的可重复使用的代码块；存储过程由一系列的 SQL 语句和控制流语句组成，可以接受参数，并且可以返回结果。存储过程通常用于执行复杂的数据库操作，实现业务逻辑和数据处理等任务。

特点：

• 封装，复用。可以把某一业务SQL封装在存储过程中，使用时直接调用
• 可以接收参数，也可以返回数据。存储过程中，可以传递参数，也可以接收返回值
• 减少网络交互，效率提升。如果分步执行多条SQL，则每执行一次都是一次网络传输。 而如果封装在存储过程中，则只需要一次网络交互即可。

# ****************************创建****************************
CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN
-- SQL语句
END ;
# ****************************调用****************************
CALL 名称 ([ 参数 ]);
# ****************************查看****************************
-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; 
-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称; 
# ****************************删除****************************
DROP PROCEDURE [ IF EXISTS ] 存储过程名称;
# ****************************举例****************************
delimiter $$  #设置MySQL语句结束符为$$
create procedure p1()
begin
    select count(*) from student;
end$$
delimiter ; #再设置为默认初始的 ;
call p1;  # 调用p1
drop procedure if exists p1; # 删除p1

变量

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

系统变量

是MySQL服务器提供，非用户定义，属于服务器层面。分为全局变量、会话变量

• 全局变量(GLOBAL)：全局变量针对于所有的会话
• 会话变量(SESSION)：会话变量针对于单个会话，在另外一个会话窗口就不生效了

注意：

如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量

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

用户定义变量

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

局部变量

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

# 查看系统变量 如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量
SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE 'xxx'; -- 可以通过LIKE模糊匹配方式查找变量
SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值
# 设置系统变量
SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
SET @@[SESSION | GLOBAL]系统变量名 = 值 ;

# 赋值 用户定义变量  赋值时，可以使用 = ，也可以使用 := 
SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;
# 使用 用户定义变量  用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL
SELECT @var_name ;

# 声明 局部变量
DECLARE 变量名 变量类型 [DEFAULT ... ] ;
# 赋值 局部变量
SET 变量名 = 值 ;
SET 变量名 := 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;

if条件判断

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

IF 条件1 THEN
	..... # 条件1 成立执行该THEN后的语句
ELSEIF 条件2 THEN -- 可选
	..... # 条件2 成立执行该THEN后的语句
ELSE -- 可选
	..... # 以上条件都不成立 成立执行该THEN后的语句
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; # 不及格

参数

参数的类型 主要分为以下三种：IN、OUT、INOUT。

• IN（默认）：该类参数作为输入，也就是需要调用时传入值
• OUT：该类参数作为输出，也就是该参数可以作为返回值
• INOUT：既可以作为输入参数，也可以作为输出参数

CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
	-- SQL语句
END ;
#---------------------举例：根据传入的200分制的分数，返回换算成百分制分数---------------------
create procedure  p5(inout score double)
begin
    set score := score * 0.5;
end;
set @myscore := 180;
call p5(@myscore);
select @myscore; # 90
# ---------------------举例：根据传入参数score，判定并返回当前分数对应等级--------------------
drop procedure if exists p4;
create procedure p4(in score int, out grade varchar(10))
begin
    if score >= 85 then
        set grade := '优秀';
    elseif score >= 60 then
        set grade := '及格';
    else
        set grade := '不及格';
    end if;
end;
call p4(66,@mygrade); # 调用时，直接传入两个参数，使用用户自定义变量接收grade的值
select @mygrade;

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;
#---------------------举例：根据传入的月份，判定所属季度---------------------
drop procedure if exists p6;
create procedure p6(in month int)
begin
    declare ans varchar(10);
    case
        when month in (1,2,3)
            then set ans := '第一季度';
        when month in (4,5,6)
            then set ans := '第二季度';
        when month in (7,8,9)
            then set ans := '第三季度';
        when month in (10,11,12)
            then set ans := '第四季度';
    end case;
    select concat('您输入的月份为：',month, ',所在季度是：',ans);
end;

call p6(10); # 第四季度

while

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

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
	SQL逻辑...
END WHILE;
#---------------------举例：计算从1累加到n的和，n为传入的参数值---------------------
drop procedure if exists p7;
create procedure p7(in n int)
begin
    declare sum int default 0;
    while n > 0 do
        set sum := sum + n;
        set n := n - 1;
    end while;
    select concat('从1到n的和为：',sum);
end;
call p7(10); #55

repeat

是有条件的循环控制语句, 当满足until声明的条件的时候，则退出循环

while是不满足条件则退出循环，repeat是满足条件则退出循环

repeat先执行一次，再判断是否满足循环条件

-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
	SQL逻辑...
	UNTIL 条件
END REPEAT;
#---------------------举例：计算从1累加到n的和，n为传入的参数值---------------------
drop procedure if exists p8;
create procedure p8(in n int)
begin
    declare sum int default 0;
    repeat
        set sum := sum + n;
        set n := n - 1;
    until  n = 0
    end repeat;
    select concat('从1到n的和为：',sum);
end;
call p8(100); #5050

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为传入的参数值---------------------
drop procedure if exists p10;
create procedure p10(in n int)
begin
    declare sum int default 0;
    getsum:loop #循环代码
        if n = 0 then
            leave getsum; # 退出循环 leave
        end if;
        if n%2 = 1 then
            set n := n - 1;
            ITERATE getsum;
        end if;
        set sum := sum + n;
        set n := n - 1;
    end loop getsum;

    select concat('从1到n的和为：',sum);
end;
call p10(10); #30

游标（CURSOR）

是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE

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

条件处理程序（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代码的简写
	drop procedure if exists p12;


#---------------------举例：根据传入的参数uage，查询用户表tb_user中所有用户年龄小于等于uage的name和profession，并将这两个字段插入到新表 (id,name,profession)中---------------------
create procedure p12(in user_age int)
begin
    # 有先后顺序：先声明普通变量，再声明游标
    declare namenew varchar(100);
    declare profnew varchar(100);
    # 1.声明游标 存储查询结果集
    declare getInfo cursor for
        select name,profession from tb_user where age <= user_age;
    # 定义 条件处理程序，当状态码为02000时触发条件处理程序 -> 退出操作，关闭游标
    declare exit handler for sqlstate '02000' close getInfo;
    # declare exit handler for not found close getInfo;
    # 2.创建新表的 表结构
    drop table if exists tb_user_new;
    create table if not exists tb_user_new(
        id int primary key auto_increment,
        name varchar(100),
        profession varchar(100)
    );
    # 3.开启游标
    open getInfo;
    # 4.获取游标查询的结果集，循环遍历
    # 4.1 遍历的时候把查询结果集的name和profession赋值给两个局部变量,声明两个变量
    while true do
        # 获取游标记录
        fetch getInfo into namenew,profnew;
        # 4.3 插入新表数据
        insert into tb_user_new values(null,namenew,profnew);
    end while;
    # 关闭游标
    close getInfo;
end;

call p12(30);

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的

CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
RETURNS type [characteristic ...] # 指定返回值类型
BEGIN
	-- SQL语句
	RETURN ...; # 返回结果
END ;
#---------------------举例：计算从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);

characteristic说明：

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

触发器 Triggers

触发器（Triggers）是一种数据库对象，它与表相关联，并在表上的特定事件发生时自动执行一系列动作。是在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。

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

old用来引用原来的记录内容，new用来引用新的记录内容

• 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;
# 查看
SHOW TRIGGERS ;
# 删除
DROP TRIGGER [schema_name.]trigger_name ; 
-- 如果没有指定 schema_name，默认为当前数据库

锁

是计算机协调多个进程或线程并发访问某一资源的机制，用于保护并发访问数据的一致性和有效性

全局锁

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

使用场景：全库逻辑备份（将数据库中的数据备份成一个SQL文件保存在磁盘中）

• 若执行备份过程中，还有数据的修改和插入，则备份的数据无法保证数据的一致性和完整性。例如表订单，表库存，表订单日志，逐个备份该表的过程（备份顺序：订单->库存->日志）中依然有下单的操作，即备份了订单 但库存已经减少等操作 会导致数据库的数据不一致
• 加锁后，DML和DDL无法操作，DQL查询数据 依然可以使用

flush tables with read lock ; # 加全局锁
sudo mysqldump -h 127.0.0.1 -uroot -p123456 -d db01 > db01.sql # 数据备份 shell界面中执行
unlock tables; # 再登录到MySQL，释放锁

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

• 如果在主库上备份，那么在备份期间都不能执行更新
• 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟

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

sudo mysqldump -single-transaction -h 127.0.0.1 -uroot -p123456 -d db01 > db01.sql

表级锁

表锁

• 表共享读锁(read lock)
• 表独占写锁(write lock)

lock tables 表名... read/write  # 加锁
unlock tables / 客户端断开连接  # 释放锁

元数据锁（meta data lock, MDL）

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上

主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与 DDL冲突（增删改 DDL：数据库 表 表字段 ，DML：表中数据），保证读写的正确性。

元数据就是表结构，元数据锁就是维护表结构一致性的锁

• 当对一张表进行增删改查的时候 DML语句，加MDL读锁(共享)
• 当对表结构进行变更操作的时候 DDL语句，加MDL写锁(排他)

意向锁

若两个线程操作一张表，线程A对表加了行锁/表锁，之后 线程B要对表加表锁，线程B需要先遍历表 查看表是否已经被加行锁 或者 表锁。

为了减少类似以上，DML执行时加的行锁与后来要加的表锁冲突的现象。InnoDB引入了意向锁，使表锁不用再检查每行是否被加锁，使用意向锁减少了表锁的检查。

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

与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥

通过SQL查看意向锁和行锁的加锁情况：select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;

行级锁

行级锁每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突概率最低，并发度最高。

行级锁应用在 InnoDB存储引擎中，InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁

行锁（Record Lock）

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

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

间隙锁（Gap Lock）

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

临键锁（Next-Key Lock）

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

InnoDB引擎

事务原理（ACID）

事务：一组操作的集合，要么全部成功，要么全部失败：

事务的四大特性：（ACID）原子性，一致性，隔离性，持久性

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

MVCC

Multi-Version Concurrency Control，多版本并发控制

维护一个数据的多个版本， 使得读写操作没有冲突，快照读 为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现 依赖于数据库记录中的三个隐式字段、undo log日志、readView