Loading... # MySQL笔记 ## 事务 ### ACID - Atomicity 原子性 - Consistency 一致性 - Isolation 隔离性 - Durability 持久性 ### 并发事务问题 - 脏读：一个事务在未提交时读到其他事务未提交的数据 - 不可重复读：一个事务内两次读取同一行的结果不一致 - 幻读（虚读）：一个事务内无法查询到某一行数据，因此进行添加，却无法无法添加，提示已存在，但是又无法查询到 ### 隔离级别 - Read Uncommitted 读未提交：无法解决所有，性能高 - Read Committed 读已提交：解决脏读 - Repeatable Read 可重复读（默认）：解决脏读和不可重复读 - Serializable 串行化：解决所有，但性能低，独占锁实现 ## 存储引擎 存储引擎是存储数据、建立索引、更新/查询数据等技术的实现。存储引擎是基于表的，而不是基于库的，所以也可以称为表类型。 ### 体系结构 - 连接层：负责与客户端的连接，鉴权，连接数量限制等 - 服务层：SQL接口，解析器，查询优化器，缓存（跨存储引擎的具体实现） - 引擎层：负责数据的存储和提取，有InnoDB，MyISAM，Memory等 - 存储层：负责持久化，日志等 ### InnoDB 在MySQL 5.5之后，作为默认的存储引擎，是兼顾高可靠性和高性能的通用存储引擎。 #### 特点 - DML遵守ACID模型，支持事务 - 行级锁 - 外键 #### 文件 表空间文件形如`xxx.ibd`，`xxx`为表名，用于存储该表的表结构（frm, sdi)、数据以及索引 #### 逻辑存储结构 1. Tablespace 表空间 2. Segment 段 3. Extent 区 1M 4. Page 页 16K 5. Row 行 ### MyISAM MyISAM为MySQL早期的默认引擎 支持表锁，访问速度较快 #### 文件 - sdi：表结构 - MYD：数据 - MYI：索引 ### Memory 内存存放，hash索引 文件：sdi ### 对比  ## 索引 索引是一种有序的数据结构，用于高效的获取数据。数据库维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以基于此实现高级查找算法，这就是索引。 ### 语法 创建索引 `CREATE [UNIQUE|FULLTEXT] INDEX idx_index_name ON table_name (col1, ...)` 删除索引 `DROP INDEX idx_index_name ON table_name` 查看索引 `SHOW INDEX FROM table_name` ### 结构 常见 - B+Tree：最常见的索引类型，大部分引擎都支持B+树索引 - Hash：哈希表实现 - R-tree：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型 - Full-text：通过倒排索引实现，类似于ES #### B+Tree 在B树的基础上进行修改，所有数据都放在叶子结点，并形成一个单向链表，树枝部分仅充当索引 MySQL对B+树进行修改，在叶子节点部分增加一个指向前一个节点的指针，形成双向链表，提高区间访问的性能，每一个节点都存储在一个Page中 #### Hash 使用Hash函数，算出键值对应的hash值，并映射到对应的hash表槽位上，记录数据值和行hash值，并采用链表解决hash冲突 ##### 特点 - 只能用于等值比较，无法范围查询 - 无法利用索引进行排序 - 查询效率高 ##### 存储引擎支持 Memory引擎，而InnoDB引擎具有自适应hash功能，hash索引是InnoDB根据B+树索引在指定条件下自动构建的 ### 分类 | 分类 | 含义 | 特点 | 关键字 | | :------: | :------: | :------: | :-------: | | 主键索引 | 针对表中主键创建的索引 | 默认自动创建，唯一 | PRIMARY | | 唯一索引 | 避免一张表中某列的值重复 | 可以有多个 | UNIQUE | | 常规索引 | 快速定位特定数据 | 可以有多个 | - | | 全文索引 | 全文索引查找的是文本中的关键词，而不是索引中的值 | 可以有多个 | FULLTEXT | 在InnoDB引擎中，根据索引的存储形式，可分为以下两种： | 分类 | 含义 | 特点 | | ------------------------------------- | ---------------------------------------------------------- | ------------ | | 聚集索引 (Clustered Index) | 将数据存储与索引放到了一块，索引结构的叶子结点保存了行数据 | 必要，唯一 | | 二级索引 (Secondary Index) (辅助索引) | 将数据与索引分开存储，索引结构的叶子结点关联对应主键 | 可以存在多个 | 聚集索引选取规则： - 如果存在主键，逐渐索引就是聚集索引 - 如果不存在主键，将使用第一个唯一索引作为聚集索引 - 如果都没有，则InnoDB会自动生成一个rowid作为隐藏的聚集索引  回表查询：在二级索引中拿到聚集索引后，到聚集索引中查找 ### 性能分析 使用 `show global status like 'Com_______'` 来查看相关命令的执行次数 #### 慢查询日志 慢查询日志记录了所有执行时间超过指定参数 (long_query_time，unit: s, default: 10s) 的所有SQL语句的日志。 MySQL的慢查询日志默认没有开启，需要在MySQL配置文件中配置如下信息： ```properties # 开启慢查询日志 slow_query_log=1 # 设置慢查询的时间为2s long_query_time=2 ``` 在`/var/mysql`下会生成`xxxx-slow.log`日志文件，通过相关命令查看 #### show profiles 通过`have_profiling`参数，可以查看当前数据库是否支持profile操作: `SELECT @@have_profiling;` 默认 profiling 是关闭的，可以通过set在 session / global 中开启: `SET profiling = 1` 通过`show profiles`展示最近执行的SQL的Query_ID，耗时，语句 通过`show profiles for query [Query_ID]`查看具体SQL的耗时 通过`show profiles cpu for query [Query_ID]`查看具体SQL的CPU使用情况 #### explain执行计划 在select语句前加上关键字 `explain / desc` - Id select查询的序列号，表示查询中执行select子句或是操作表的顺序（相同ID，从上往下；不同ID，从大到小 先执行） - select_type | 值 | 含义 | | :------: | :--: | | SIMPLE | 简单表，即不使用连接或子查询 | | PRIMARY | 主查询，即最外层的查询 | | UNION | UNION中第二个及以后的查询 | | SUBQUERY | SELECT/WHERE包含的子查询 | - type 表示连接类型，尽量向前优化 性能由高到低分别为：NULL、system (系统表)、const (唯一索引)、eq_ref、ref (非唯一索引)、range (范围)、index (遍历全部索引)、all (全表扫描) - possible_key 可能应用在这张表上的索引，一个或多个 - key 实际使用的索引 - key_len 索引长度 - rows MySQL认为必须要执行查询的行数，在InnoDB引擎的表中是一个估计值，并不准确 - filtered 返回结果的行数占读取行数的百分比，越大越好 - extra 额外信息（为null 回表） ### 使用规则 #### 最左前缀法则 对于关联了多列的联合索引，需要遵守最左前缀法则，即*从索引的最左列开始，且不跳过索引中间列* 有一个联合索引 `idx_pro_age_status` 关联字段 `profession, age, status` 想让其生效，则查询可以是： - 最左字段存在，位置无关 `select * from tb_user where profession = '土木工程';` - 最左字段存在，其他字段自左向右，不跳过中间字段 `select * from tb_user where profession = '土木工程' and age = 23;` `select * from tb_user where profession = '土木工程' and age = 23 and status = 1;` ##### 特殊情况 索引跳跃扫描 MySQL 8.0版本开始增加了索引跳跃扫描的功能 当第一列索引的唯一值较少时，即使where条件没有第一列索引，查询的时候也可以用到联合索引 遇到 Index Skip Scan 得时候, 说明索引字段选择有问题, 应该创建一个新的合适索引 详见[掘金](https://juejin.cn/post/7127656601044910094) #### 索引失效 - 范围查询 联合索引中，出现范围查询(>, <) (含=仍有效)，则范围查询右侧的列索引失效 - 索引列运算 不要在索引列上进行运算操作，否则索引将失效 - 隐式类型转换 字符串类型使用时，不加`''`，索引失效 - 模糊查询 如果只是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效 - or连接的条件 用 or 分隔的条件，如果 or 前的条件中的列有索引，而后面的条件列中没有索引，则都不会用到索引 #### 数据分布影响 如果MySQL认为使用索引比全表扫描更慢，则不使用索引 有一索引`idx_user_age`和对应字段`age`，值为`1-20` 如查询条件为 `where age >= 1`, `where age >= 11`，则走全表扫描 而`where age >= 12`开始走索引 #### SQL提示 SQL提示是在SQL语句中加入一些人为的提示来达到SQL优化的目的 在上面的联合索引之外，给`profession`单独创建索引`idx_user_pro`，在不使用SQL提示时，使用的是联合索引 - use index `select * from tb_user use index(idx_user_pro) where profession = '土木工程';` - ignore index `select * from tb_user ignore index(idx_user_pro) where profession = '土木工程';` - force index `select * from tb_user force index(idx_user_pro) where profession = '土木工程';` 需要注意的是，除了focre强制使用外，use仅仅只是建议，是否使用MySQL会综合条件自行判断 #### 覆盖索引 尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能找到），减少`select *`的使用 > 如extra中出现： > > using index condition：查找使用了索引，但是需要回表查询，即索引不包含部分需要返回的列 > > using where; using index：查找使用了索引，所有数据都能在索引列中找到，不需要回表查询 #### 前缀索引 当字段类型为字符串等时，可能会索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率 此时可以只将字符串的一部分前缀建立索引，这样可以优化索引空间，从而提高查询效率 表tb_user中存在varchar类型字段`email` 为其新建一个长度为10的前缀索引：`create index idx_user_email_10 on tb_user (email(10));` 前缀长度可以根据索引的选择性来设置：`select count(distinct substring(email, 1, 10)) / count(*) from tb_user;` #### 单列索引和联合索引 在业务场景中，如果存在多个查询条件，针对查询字段建立索引时，建议使用联合索引，避免回表查询 多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，并选择该索引完成本次查询 ### 设计原则 1. 针对数据量较大（>100w），且查询较为频繁的表建立索引 2. 针对常作为查询条件、排序、分组操作的字段建立索引 3. 尽量选择区分度高的列作为索引，尽量简历唯一索引，区分度越高，使用索引的效率越高 4. 如果是字符串类型的字段，字段的长度较长，可使用前缀索引 5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率 6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也越大，会影响增删改的效率 7. 如果索引列不能存储NULL值，在创建表时使用NOT NULL约束。当优化器知道每列是否包含NULL值时，它可以更好的确定哪个索引最有效的用于查询 ## SQL优化 ### insert 优化 - 批量插入 使用批量插入 `values(),(),()`，且不超过1000条 - 手动提交事务，在全部插入完成后提交，避免频繁开启事务 - 主键顺序插入 #### 大批量插入数据 一次性插入大批量数据，使用insert语句的性能较低，可使用MySQL提供的`load`指令进行插入 连接时加上`--local-infile`参数：`mysql --local-infile -u root -p` 设置全局`local_infile`为1：`set global local_infile=1` 执行`load`指令将准备好的数据加载到表结构中： ```sql load data local infile './sql/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n'; ``` ### 主键优化 - 数据组织方式 在InnoDB存储引擎中，表数据哦度是根据主键顺序组织存放的，这种存储方式的表被称为索引组织表 (Index Organized Table, IOT) - 页分裂 页可以为空，也可以填充一半/全部，每个页包含2~N行数据（一行数据过大，会发生行溢出），根据主键排列 在主键乱序插入时，会出现页分裂，即新插入的行大于应在页的剩余空间，将应在页后50%(默认)的数据添加到新的页上，并将新插入的放入，同时修改链表指针，保证主键顺序 - 页合并 删除一行数据时，实际上数据并没有被物理删除，而是被标记为删除并且它的空间可以被其他记录使用 当页中删除的记录达到 `MERGE_THRESHOLD`(默认50%) 时，InnoDB会开始寻找它前后的页，并判断是否可以将两个页合并，以优化空间使用 #### 主键设计原则 - 满足业务需求的情况下，尽量降低主键的长度 - 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键 - 尽量不要使用UUID做主键或其他自然主键，如身份证号 - 业务操作时，避免对主键的修改 ### order by 优化 Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区中完成排序操作，所有不是通过索引直接返回排序结果的排序都是 FileSort 排序 Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外资源，效率高 - 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则 - 尽量使用覆盖索引 - 多字段排序，一个升序一个降序，需要注意联合索引创建时的规则 - 如不可避免的出现filesort，大数据量排序时，可以适当增大排序表缓冲区大小`sort_buffer_size`(默认256K) ### group by 优化 - 分组时，可以通过索引提高效率 - 索引的使用满足最左前缀法则 ### limit 优化 `limit 2000000, 10`需要查询前 2000010 条数据，仅返回最后10条，其余丢弃，因此查询的开销非常大 可以通过覆盖索引和子查询来进行优化 ```sql select prod.* from tb_prod prod, # 子查询，通过order by拿到id，再通过id覆盖查询拿到数据 (select id from tb_prod order by id limit 2000000, 10) pageProds where prod.id = pageProds.id; ``` > 这种方法和直接查询仅仅只是减少了列的查询，因为id字段使用的空间和整行相比较小，相当于两次覆盖查询，一次查id，一次拿行数据 ### count 优化 MyISAM：将一个表的总行数存在磁盘上，在不加条件 count(*) 时会直接返回这个数 InnoDB：一行一行读取并累加 优化：自行计数 #### count对比 - count(主键)： InnoDB会遍历整张表，取每一行的主键id值，返回给服务层，服务层拿到后按行累加 - count(字段)： - 没有 not null 约束： InnoDB会遍历整张表，取出每行对应的字段值，返回给服务层，服务层进行判空并累加非空 - 有 not null 约束：InnoDB会遍历整张表，取每行对应的字段值，返回给服务层，服务层拿到后按行累加 - count(1)： InnoDB会遍历整张表，不取值，服务层将每行返回视为1 (传其他的也可以，对结果没影响)，直接按行累加 - count(*)： InnoDB会遍历整张表，但不取值，服务层直接按行累加 总结：count(字段) < count(主键) < count(1) ≈ count(*) ### update 优化 InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁 因此如果update的条件字段没有索引，则会对整张表加锁，影响并发性能，需要对相应字段加锁 ## 视图 视图View是一种虚拟存在的表 视图中的数据并不在数据库中真实存在，行和列的数据来自于定义视图的查询中使用的表（基表），是在使用视图时动态生成的 视图只保留了查询的SQL逻辑，不保存查询结果 ### 语法 - 创建 ```sql CREATE [OR REPLACE] VIEW 视图名称(列) AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION] ``` - 删除 ```sql DROP VIEW [IF EXISTS] 视图名称1 [, 2, 3...] ``` - 修改 ```sql # 方法一 CREATE OR REPLACE VIEW 视图名称[(列)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION] # 方法二 ALTER VIEW 视图名称[(列)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION] ``` - 查询 查看创建视图语句：`SHOW CREATE VIEW 视图名称` 查看视图语句(看做表)：`SELECT * FROM 视图名称` ### 视图的检查选项 当使用`WITH CHEAK OPTION`子句创建视图时，MySQL会通过视图检查修改的每一个行，以使其符合视图的定义 MySQL允许基于另一个视图创建视图，它还会检查充当基表的视图中的规则以保持一致性 为了确定检查的范围，MySQL提供了两个选项：`CASCADED`和`LOCAL`，默认为`CASCADED` - CASCADED 级联，会检查添加CASCADED的视图以及其所依赖的视图的条件，即便被依赖的视图没有添加检查选项 - LOCAL 本地，检查添加LOCAL视图的条件及其依赖视图，但需要被依赖视图添加检查选项 ### 视图的更新 要使视图可更新，需要满足视图中的行与基础表中的行之间存在一对一的关系（即视图中的行并非计算得来） ### 视图的作用 - 简单 视图不仅可以简化用户对数据的理解，也可以简化他们的操作 将经常被使用到的查询定义为视图，从而使得用户不必为这些查询每次指定全部条件 - 安全 数据库可以授权，但不能授权到数据库特定行和列上 通过视图用户只能查询和修改他们所能看到的数据 - 数据独立 视图可以帮助用户屏蔽真实表结构变化带来的影响 ## 存储过程 存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据库和服务器之间的传输次数，提高数据处理效率 存储过程就是SQL语言层面的代码封装与重用（函数） ### 语法 - 创建 ```sql CREATE PROCEDURE 存储过程名(参数列表) BEGIN # SQL语句 END; ``` - 调用 ```sql CALL 存储过程名(参数列表); ``` - 查看 ```sql # 查询指定存储过程的定义 SHOW CREATE PROCEDURE 存储过程名称; # 查询指定数据库的存储过程及状态信息 SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; ``` - 删除 ```sql DROP PROCEDURE [IF EXISTS] 存储过程名称; ``` ### 变量 #### 系统变量 系统变量是MySQL提供的，不是用户定义的，属于服务器层面 分为全局变量（GLOBAL）和会话变量（SESSION） - 查看系统变量 ```sql # 查看所有系统变量 SHOW [SESSION | GLOBAL] VARIBLES; # 模糊匹配查找系统变量 SHOW [SESSION | GLOBAL] VARIBALES LIKE 'xxx'; # 查看指定变量 没有空格 SELECT @@[SESSION. | GLOBAL.]系统变量名; ``` - 设置系统变量 ```sql SET [SESSION | GLOBAL] 系统变量名 = VAL; SET @@[SESSION. | GLOBAL.]系统变量名 = VAL; ``` 如果没有指定SESSION/GLOBAL，默认SESSION MySQL服务重新启动后，所有变量会失效，除非在/etc/my.cnf文件中配置 #### 用户定义变量 用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，如未声明，则为NULL 作用域为当前连接 - 赋值 ```sql SET @var_name = expr [, @var_name2 = expr2, ...]; SET @var_name := expr [, @var_name2 = expr2, ...]; SELECT @var_name := expr [, @var_name2 = expr2, ...]; SELECT field_name INTO @var_name FROM table_name ``` - 使用 ```sql SELECT @var_name [, @var_name2, ...]; ``` #### 局部变量 局部变量是根据需要定义在局部生效的变量，访问之前需要`DECLARE`声 可用作存储过程内的局部变量和输入参数，有效范围在其声明的`BEGIN...END`块内 - 声明 ```sql DECLARE 变量名 类型 [DEFAULT = xxx]; ``` - 赋值 ```sql SET var_name = expr; SET var_name := expr; SELECT field_name INTO var_name FROM table_name ``` ### 参数 | 类型 | 含义 | | :-------: | :--------------------------: | | IN (默认) | 作为入参 | | OUT | 返回值 | | INOUT | 既可以作为入参，也可作为返回 | 用法： ```sql CREATE PROCEDURE 存储过程名称(IN/OUT/INOUT 参数名 参数类型) BEGIN ... END; ``` ### 条件控制语句 - IF ```sql IF CONDITION1 THEN ... ELSEIF CONDITION2 THEN ... ELSE ... END IF; ``` - CASE ```sql CASE [CONDITION_VAL] WHEN CASE1 THEN ... [WHEN CASE2 THEN ... ... ELSE ...] END CASE; ``` - WHILE ```sql WHILE CONDITION DO ... END WHILE; ``` - REPEAT ```sql REPEAT ... # 满足则退出, do while UNTIL CONDITION END REPEAT; ``` - LOOP ```sql [begin_label]: LOOP ... END LOOP [end_label]; # 退出指定的循环 break@label LEAVE label; # 进入下一次循环 continue@label; ITERATE label; ``` ### 游标 游标（CURSOR）是用来存储查询结果集的数据类型（迭代器），在存储过程和函数中可以使用游标对结果集进行循环的处理 游标的使用包括游标的声明、OPEN、FETCH、CLOSE - 声明 ```sql DECLARE 游标名称 CURSOR FOR 查询语句 ``` - 打开游标 ```sql OPEN 游标名称 ``` - 获取游标记录 ```sql FETCH 游标名称 INTO 变量 [, var2, ...] ``` - 关闭游标 ```sql CLOSE 游标名称; ``` ### 条件处理程序 条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时的处理方案（try..catch) ```sql DECLARE handler_action HANDLER FOR condition [, condition2, ...] statement; -- 参数说明 hander_action: # 继续 CONTINUE # 终止 EXIT condition: # 状态码 SQLSTATE sqlstate_value # 所有以01开头的状态码简写 SQLWARNING # 所有以02开头的状态码 NOT FOUND # 所有没有被上面捕获的状态码 SQLEXCEPTION ``` ## 存储函数 存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的 - 定义 ```sql CREATE FUNCTION 函数名(参数) RETURNS type characteristic BEGIN ... RETURN ... END; -- 参数说明 characteristic: # 相同的入参总是产生相同的结果 DETERMINSTIC # 不包含SQL语句 NO SQL # 包含读取数据的语句，但不包含增删改的语句 READS SQL DATA ``` - 使用 ```sql select fun(111); 其他同理 ``` ## 触发器 触发器是与表有关的数据库对象，在增删改之前或之后，触发并执行触发器中定义的SQL语句集合（事件监听器） 可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作 使用别名 OLD (原来的数据) 和 NEW (新的数据) 来引用触发器中发生变化的记录内容，这与其他数据库是类似的 只支持行级触发，不支持语句触发 | 类型 | 解释 | | :----: | :--------------------: | | INSERT | NEW 将要或已新增的数据 | | UPDATE | OLD 旧数据 NEW 新数据 | | DELETE | OLD 将要或已删除的数据 | - 创建 ```sql CREATE TRIGGER trigger)name BEFORE/AFTER INSERT/UPDATE/DELETE # EACH ROW 行级触发器 ON table_name FOR EACH ROW BEGIN ... END; ``` - 查看 ```sql SHOW TRIGGERS; ``` - 删除 ```sql # 不指定数据库默认为当前 DROP TRIGGER [schema_name.]trigger_name; ``` ## 锁 MySQL中的锁，按粒度分为三类： - 全局锁：锁定数据库中的所有表 - 表级锁：锁住整张表 - 行级锁：锁住对应行 ### 全局锁 加全局锁后，整个数据库实例处于只读状态，后续的DDL，DML，事务提交将被阻塞 典型使用场景是做全库的逻辑备份 (mysqldump)，对所有表进行锁定，从而获得一致性视图，保证数据完整性 #### 语法 - 加锁：`FLUSH TABLES WITH READ LOCK;` - 释放锁：`UNLOCK TABLES` #### 注意事项 加全局锁，是一个比较重的操作，存在以下问题： - 如果在主库上备份，那么备份期间都不能执行更行操作，业务停摆 - 如果 (读写分离) 在从库上备份，那么在备份期间从库不能只执行主库同步过来的二进制日志 (binlog)，导致主从延迟 在InnoDB中，可以在备份时加上参数`--single-transaction`来完成不加锁的一致性数据备份 > InnoDB底层通过快照读实现 ### 表级锁 #### 表锁 分为两类： - 表共享读锁（read lock）（读共享，持有锁不能写，阻塞其他客户端写） - 表独占写锁（write lock）（独占，阻塞其他客户端读写） ##### 语法 - 加锁：`LOCK TABLES table_name... READ/WRITE;` - 释放锁：`UNLOCK TABLES` / 客户端断开连接 #### 元数据锁 元数据锁（Meta Data Lock，MDL）主要作用是维护表元数据的数据一致性，在表上有活动事务时，不可以对元数据进行写入操作 MDL加锁过程是系统自动控制，无需显式使用 避免DML与DDL冲突，保证读写的正确性 在MySQL 5.5中引入MDL，当对一张表进行CRUD时，加MDL读锁(共享)；当对表结构进行变更操作时，加MDL写锁(排他) > 元数据简单理解为表结构 | 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兼容 | | insert \| update \| delete \| select ... for update | SHARED_WRITE | 与SHARED_READ、SHARED_WRITE兼容 | | alter table | EXCLUSIVE | 与其他MDL互斥 | 查看库中的元数据锁： ```sql select object_type, object_schema, object_name, lock_type, lock_duration from performance_schema.metadata_locks; ``` #### 意向锁 为了避免DML在执行时，加的行锁与表锁之间的冲突，InnoDB引入了意向锁 意向锁使得表锁不用检查每一行数据是否加锁，减少了表锁的检查 流程：A在加行锁之后，给整张表加意向锁；B在加表锁时，检查意向锁，如果锁兼容，则加表锁，否则阻塞 意向锁分为两种： | 类型 | SQL | 兼容性 | | :-----------: | :-------------------------------------------: | :--------------------------------: | | 意向共享锁 IS | select ... lock in share mode | 与表锁共享锁兼容，与表锁排它锁互斥 | | 意向排它锁 IX | insert、update、delete、select ... for update | 与表锁互斥，意向锁之间兼容 | > 一旦事务提交了，意向共享锁、意向排他锁，都会自动释放 查看表中的意向锁： ```sql select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks; ``` ### 行级锁 行级锁每次锁住对应的行数据，粒度最小，发生锁冲突的概率最低，并发程度最高 InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而非对记录加锁 行级锁主要分为三类： - 行锁（Record Lock），锁定单个行记录的锁，防止其他事物对此行进行update和delete，在 rc、rr 级别下都支持 - 间隙锁（Gap Lock），锁定索引记录间隙（不涵该记录），确保索引记录间隙不变，防止其他事物在这个间隙进行 insert，产生幻读，在 rr 级别下支持 - 临键锁（Next-Key Lock），行锁合间隙锁的组合，同时锁住数据和Gap，在 rr 级别下支持 #### 行锁 InnoDB提供以下两种类型的行锁： - 共享锁 S：允许一个事务读某一行，阻止其他事务获得相同数据集的排它锁 - 排它锁 X：允许排它锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排它锁 CRUD加锁情况： | SQL | 锁类型 | 说明 | | :---------------------------: | :----: | :------: | | INSERT | X | 自动加锁 | | UPDATE | X | 自动加锁 | | DELETE | X | 自动加锁 | | SELECT | - | - | | SELECT ... LOCK IN SHARE MODE | S | 手动 | | SELECT ... FOR UPDATE | X | | 默认情况下，InnoDB在 rr 级别运行，使用Next-Key锁进行搜索和索引扫描，以防止幻读 1. 针对唯一索引进行检索时，对已在的记录进行等值匹配时，将会自动优化为行锁 2. InnoDB的行锁针对索引，不使用索引条件检索数据，那么InnoDB将对表中所有记录加锁，此时升级为表锁 查看表中的行锁 (意向锁)： ```sql select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks; ``` #### 间隙锁 / 临键锁 默认情况下，InnoDB在 rr 级别运行，使用临键锁进行搜索和索引扫描，以防止幻读 1. 索引上的等值查询(唯一索引)，给不存在的记录加锁时，优化为间隙锁 2. 索引上的等值查询(普通索引)，从B+树叶子结点向右遍历时最后一个值不满足查询需求时，临键锁退化为间隙锁 3. 索引上的范围查询(唯一索引)，会访问到不满足条件的第一个值为止，然后退化为间隙锁 > 间隙锁唯一的目的是防止其他事务插入间隙，造成幻读 > > 间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁 ## InnoDB ### 逻辑存储结构  - Tablespace 表空间 表空间(idb文件)，一个MySQL实力可以对应多个表空间，用于存储记录、索引等数据 - Segment 段 段，分为数据段(Leaf Node Segment)、索引段(Non-Leaf Node Segment)、回滚段(Rollback Segment) InnoDB是索引组织表，数据段就是B+树的叶子结点，索引段即为B+树的非叶子结点 段用于管理多个 Extent区 - Extent 区 (1MB) 区，表空间的单元结构，每一个区的大小为1M 默认情况下，InnoDB存储引擎页大小为16KB，即一个区中共有64个连续页 - Page 页 (16KB) 页，是InnoDB存储引擎磁盘管理的最小单元，每个页的默认大小为16KB 为了保证页的连续性，InnoDB存储引擎每次从磁盘申请 4 - 5 个区 - Row 行 行，InnoDB存储引擎数据是按行进行存放的 Trx id：最后一次事务的id Roll pointer：每次对某条记录进行改动时，都会把旧的版本写入到undo日志中，这个隐藏列相当于一个指针，可以通过它来找到该记录修改前的信息 ### 架构 从MySQL 5.5版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛  #### 内存 - Buffer Pool 缓冲池是主内存中的一个区域，缓存磁盘上经常操作的数据，在CRUD时，先操作缓冲池中的数据，再以一定频率刷新到磁盘，从而减少内核切换次数，提高IO性能 缓冲池以页为单位，底层采用链表结构管理Page，根据状态，将Page分为三种类型： - free page：空闲页 从未使用 - clean page：被使用的页 数据未被修改过 - dirty page：脏页 被使用的页 有未同步的数据 与磁盘数据不一致 - Change Buffer 更改缓冲区（针对 非唯一 二级索引页） MySQL8.0后引入 在执行DML语句时，如果这些数据页没有在Buffer Pool中，则不会直接操作磁盘，而是将数据的变更暂存在更改缓冲区 Change Buffer 中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，最后将合并后的数据刷新到磁盘 与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每次都操作磁盘，会造成大量I/O - Adaptive Hash Index 自适应Hash索引，用于优化对Buffer Pool数据的查询，InnoDB会监控对表上各索引页的查询，如果观察到Hash索引可以提升速度，则建立Hash索引，称之为自适应Hash索引 无需人工干预，系统自动完成，相关参数：`adaptive_hash_index` - Log Buffer 日志缓冲区，用来保存要写入到磁盘中的log日志数据 (redo log, undo log)，默认大小为16MB 日志缓冲区会定期刷新到磁盘中，如果需要更新、插入、删除多行事务，增加日志缓冲区的大小可以节省磁盘IO 相关参数： 缓冲区大小：`innodb_log_buffer_size` 日志刷新到磁盘时机：`innodb_flush_log_at_trx_commit` `1: 日志在每次事务提交时写入并刷新到磁盘 0: 每秒写入并刷新 2: 包含0, 1` #### 磁盘 略 ### 事务原理 #### redo log ACID中的Durability是由`redo log`保证的 重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性的 该日志文件由两部分组成：重做日志缓冲(redo log buffer) 和 重做日志文件 (redo log file)，前者在内存中，后者在磁盘中 事务提交后会把所有修改信息都保存到该日志文件中，用于在刷新脏页到磁盘发生错误时，进行数据恢复使用 #### undo log ACID中的Atomicity是由`undo log`保证的 回滚日志，用于记录数据被修改前的信息，包含两个作用：`Rollback` (回滚) 和 `MVCC` (多版本并发控制) undo log 和 redo log 记录物理日志不一样，它是逻辑日志 当delete一条记录时，undo log中会记录一条对应的insert日志；当update一条记录时，会记录一条相反的update记录 因此当执行rollback时，可以从undo log中读到相应的内容并进行回滚 - undo log销毁：undo log在事务执行时产生，事务提交时，并不会立刻删除，因为这些日志可能还用于MVCC - undo log存储：undo log采用段的方式进行管理和记录，存放在`rollback segment`回滚段中，内部包含1024个`undo log segment` ### MVCC MVCC + 锁，实现了事务的隔离性 一致性则是由 redo log 与 undo log 保证。 - 当前读 读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁 `select ... lock in share mode`, `select ... for update`, `update`, `insert`, `delete`都是一种当前读 - 快照读 简单的select (不加锁) 就是快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读 - rc：每次select都生成一个快照读 - rr：开启事务后第一个select语句才是快照读 - s：快照读会退化为当前读(加锁) - MVCC 多版本并发控制 `Multi-Version Concurrency Control` 指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能 MVCC的具体实现还需要依赖数据库记录中的三个隐式字段、undo log日志、readView #### 记录中的隐式字段 指的是row中的 `DB_TRX_ID`, `DB_ROLL_PTR`, `DB_ROW_ID` | 字段 | 说明 | | :---------: | :----------------------------------------------------------: | | DB_TRX_ID | 最近修改事务id，记录插入这条记录或最后一次修改该记录的事务id | | DB_ROLL_PTR | 回滚指针，指向这条记录的上一个版本，配合 undo log 使用 | | DB_ROW_ID | 隐藏主键，如果表结构没有指定主键，将会生成该字段 | #### undo log 销毁 在insert时，产生的undo log日志只在回滚时需要，在事务提交后，可以被立即删除 在update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会被立即删除 #### 版本链   不同事务或相同事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本的链表 链表的头部是最新的旧记录，链表的尾部是最早的旧记录 #### ReadView 读视图`ReadView`，是快照读SQL执行时MVCC提取数据的依据，记录并维护当前活跃的事务（未提交）的id 包含四个核心字段： | 字段 | 说明 | | :------------: | :------------------------------------------: | | m_ids | 当前活跃(未提交)事务的ID集合 | | min_trx_id | 最小活跃事务ID | | max_trx_id | 预分配事务ID，当前最大事务ID+1（事务ID自增） | | creator_trx_id | ReadView创建者的事务ID | 不同的隔离级别，生成ReadView的时机不同： - rc：在事务中每一次执行快照读时生成ReadView - rr：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView #### 版本链数据访问规则 | 条件 | 访问权 | 说明 | | :--------------------------------: | :-------------------------------------: | :--------------------------------: | | trx_id == creator_trx_id | 可以访问该版本 | 说明数据是当前这个事务更改的 | | trx_id < min_trx_id | 可以访问该版本 | 说明数据已经提交 | | trx_id > max_trx_id | 不可以访问该版本 | 说明该事务是在ReadView生成后才开启 | | min_trx_id <= trx_id <= max_trx_id | 如果trx_id不在m_ids中，可以访问该版本的 | 说明数据已经提交 | 最后修改：2023 年 07 月 23 日 © 允许规范转载 打赏 赞赏作者 支付宝微信 赞 本作品采用 CC BY-NC-SA 4.0 International License 进行许可。
