DBA专题
DBA授课
DBA公开课
DBA训练营三天
01.Mysql基础入门-数据库简介
02.Mysql基础入门-部署与管理体系
03.MySQL主流版本版本特性与部署安装
04.Mysql-基础入门-用户与权限
05 MySQL-SQL基础2
06 SQL高级开发-函数
07 MySQL-SQL高级处理
08 SQL练习 作业
09 数据库高级开发2
10 Mysql基础入门-索引
11 Mysql之InnoDB引擎架构与体系结构
12 Mysql之InnoDB存储引擎
13 Mysql之日志管理
14 Mysql备份,恢复与迁移
15 主从复制的作用及重要性
16 Mysql Binlog Event详解
17 Mysql 主从复制
18 MySQL主从复制延时优化及监控故障处理
19 MySQL主从复制企业级场景解析
20 MySql主从复制搭建
21 MySQL高可用-技术方案选型
22 MySQL高可用-MHA(原理篇)
23 MySQL MHA实验
24 MySQL MGR
25 部署MySQL InnoDB Cluster
26 MySQL Cluster(MGR)
27 MySQL ProxySQL中间件
相信可能就有无限可能
-
+
首页
12 Mysql之InnoDB存储引擎
# 1.InnoDB体系结构-物理存储结构 ## 1.1.表与数据字典 1.MySQL8.0之前 ```sql 表的组成: - .ibd数据+索引 数据字典: - .frm 该表的数据字典信息(表结构) - .opt 库基本信息 - .TRN,.TRG文件记录触发器信息 - INFORAMTION_SCHEMA，mysql,sys ``` 2.MySQL8.0之后 ```sql 8.0之后: 表: - .ibd 数据+索引+数据字典信息 数据字典 - MyISAM系统表全部换成InnoDB表(INFORMATION_SCHEM，mysql，sys系统库),支持原子DDL. - 所有放在数据字典文件信息,放入数据库系统表(.frm,.opt,.par,.TRN,.TRG,.isl文件都移除了) 影响: 1.INFORMATION_SCHEMA性能提升 - 数据库在查询INFORMATION_SCHEMA的表时，不再创建一张临时表,直接查询数据字典表。 - 例如获取表结构信息,不再读取底层.frm文件,通过数据字典信息直接查询 - 查询INFORMATIONS_SCHEMA中表，优化器可以优化SQL走索引 - INFORMATION_SCHEMA下的STATISTICS表和TABLES表,8.0中通过缓存提高查询性能 - information_schema_stats_expiry:86400 默认缓存失效时间 ``` Mysql5.7数据字典:  8.0.之后 都在INFORMATIONS_SCHEMA中  ## 1.2.索引 ### 1.2.1.聚簇索引 ```sql InnoDB表，永远存在的一类索引。 聚簇索引构建： 1. pk的列会自动作为聚簇索引。 2. 没有pk ,会选择非空UK。 3. 都没有，生成隐藏的ROW_ID(6字节) 功能： 1. IOT，索引组织表。 2. 针对ID条件查询快速找到记录。 3. 结构： 根节点:下层节点的ID+指针 非叶子节点: 下层节点的ID范围+指针 叶子节点: 按照ID顺序逻辑上有序存储的数据行。在同一个区内的数据页使用物理连续。 ``` ### 1.2.2.辅助索引 ```sql 结构: 根节点: 下层节点的column范围+指针 非叶子节点:下层节点的column范围+指针 叶子节点: 提取” 辅助索引(name)列值+ID列值，根据辅助列(name)值排序，生成叶子节点。 查询过程： 索引覆盖 : idx(a,b,c) select a,b,c from table_a where a=xx and b=xx and c>xx 回表查询 : idx(a,b) select a,b,c from table_a where a=xx and b=xx ``` ## 1.3.表空间 ### 1.3.1.共享表空间(System Tablespace) ```sql ##存储内容 5.5.(总包): 系统数据： （全局）数据字典信息（表基本结构信息、状态、系统参数、属性..）、 UNDO回滚信息(记录撤销操作) Double Write 信息 临时表信息 change buffer ... 用户数据: 表数据+索引数据 5.6.(必须记住的区别之一):用户数据独立出去,只保留系统数据 系统数据: 数据字典信息 UNDO信息 Double Write 信息 临时表信息 change buffer 5.7:临时表独立,UNDO通过参数可以单独设定独立 系统数据: 数据字典 UNDO回滚信息 Double Write 信息 change buffer 8.0.20之后：UNDO与Double Write独立,数据字典为InnoDB表(mysql.ibd) 系统数据: change buffer https://dev.mysql.com/doc/refman/8.0/en/innodb-system-tablespace.html ``` ### 1.3.2.独立表空间(File-Per-Table Tablespaces) ```sql #介绍 5.6版本之后,保存用户表的数据和索引,存储在文件系统单个文件. #配置 [mysqld] innodb_file_per_table=ON mysql> select @@innodb_file_per_table; #优点 1.每张表都有自己独立的表空间,数据和索引都会存储在自己的表空间中。 2.实现单表在不同的数据库中移动 3.空间回收（通过 optimize table 命令实现）,delete后(共享表空间分配后不能回缩) 4.无论怎么删除，表空间的碎片不会太严重影响系统性能 #缺点 单表增加过大 ``` ### 1.3.3.Undo Tablespace ```sql #介绍 用来做回滚操作,主要存储回滚日志(事务取消对聚簇索引记录的更改信息集合) #存储 5.7.版本,默认存储共享表空间(ibdata) 8.0.版本,默认独立(undo_001,undo_002) #特性 5.7:配置参数独立undo log,在线truncate undolog(长时间事务未提交,手工清理),最多128个回滚段 8.0:默认独立配置,支持更多混滚段,和动态增加undo tablespace （1）每个undo可以支持128个回滚段 #参数 （1）查看undo配置个数 SELECT @@innodb_undo_tablespaces; #可以单独设置,但更应该管控业务 （2）查看大小 SELECT @@innodb_max_undo_log_size; #默认1G （3）是否开启自动回收 SELECT @@innodb_undo_log_truncate; #57默认关闭,80默认开启 （4）回收条件 SELECT @@innodb_purge_rseg_truncate_frequency; #回收undolog的频率,表示purge undo触发128次后,进行一次rollback segment的free(释放)操作 （5）8.0的动态添加和删除 #添加,文件后缀必须以ibu结尾,新的为active状态 CREATE UNDO TABLESPACE myundo ADD DATAFILE 'myundo.ibu'; #删除,至少保证2个undo tablespace才可以(当有一个tablespace被truncate时，还有一个tablespace可用)，别删除的undo不会分配回滚段 ALTER UNDO TABLESPACE myundo SET INACTIVE; #设置下线状态 DROP UNDO TABLESPACE myundo; #删除。 #总结 （1）5.6.独立到单独tablespace中 （2）5.7支持在线undo文件truncate,解决undo膨胀问题 （3）8.0,每个undo talespace可以有18个回滚段,减少事务使用混滚段的缩冲突,在线动态增删undo tablespace,管理更加灵活。 ``` ### 1.3.4.临时表空间(Tmp tablespace) 1.定义 ```sql 定义:SQL读取数据无法直接得到结果,需要额外内存保存中间结果.这个额外内存就是 临时表 三种处理类型: TempTable,MEMORY 存储引擎,InnoDB存储引擎存储在磁盘上(物理表) 例子:group by,保存聚合函数结果,select a,count(*) from table_b group by a; 限制:内存超过设置阈值,存储在磁盘上,防止内存溢出.OOM ``` 触发操作 ```sql 哪些操作引擎内部临时表: explain SQL 有 (1)union语句 (2)视图:使用TEMPTABLE,union,聚合 (3)派生表:FORM后的独立子查询,查询后清楚 SELECT * FROM (SELECT * FROM table_a WHERE columa_a='广州') table_x (4)子查询,半连接查询 (5)ORDER BY + DISTINCT (6) INSERT...SELECT 保存SELECT用临时表 (7) GROUP_CONCAT OR COUNT(DISTINCT) (8)窗口函数 怎么查看 show global status like '%tmp_%tables% ``` ### 1.3.5.临时表不同版本特性 **MySQL5.6** ```sql 1.临时表超过内存限制,在临时目录创建 2.临时表有自己的表空间,SQL执行删除临时表和文件 3.禁用innodb_file_per_table,则在InnoDB 共享表空间（ibdata1）中创建. ``` **MySQL5.7** ```sql 1.改进:将临时表的tablespace从InnoDB ibdata分离,叫共享临时表空间.存储临时表的数据以及回滚信息 2.优势:减少每个临时表创建和删除的成本,因为都在一个表空间 单独划分表空间,可以随时重置大小,避免5.6集中ibdata1 释放难问题; 3.表现:MySQL启动在 datadir 创建 ibtmp1文件,默认无限扩展.（比如20MB临时表,ibtmp1为20MB） 执行完删除临时表，释放空间用于新临时表(ibtmp1文件大小不变,重启MySQL服务器真正回收) 4.相关参数： (1)tmp_table_size&max_heap_table_size，临时表是存在内存中的，使用 MEMORY 存储引擎，超过两者较小的值，转化为磁盘临时表； (2)internal_tmp_disk_storage_engine：内部临时表转化为磁盘临时表，指定了磁盘临时表的存储引擎，默认是 INNODB，还可以设置为 MYISAM； (3)innodb_temp_data_file_path:指定了临时表空间的位置和大小，默认值为 ibtmp1:12M:autoextend 5.遗留问题 (1)VARCHAR的变长存储。varchar(255)变成char(255)浪费空间 (2)大对象的默认磁盘存储.比如 TEXT，BLOB,JSON等，直接转化为InnoDB磁盘存储。 (3) ibtmp1需要重启mysql实例才能释放 ``` **MySQL8.0** ```sql 1.新增参数： internal_tmp_mem_storage_engine：指定内部临时表 存储引擎,默认TempTable引擎 temptable_max_ram:定义TempTable存储引擎开始在磁盘上存储数据之前可以占用的最大内存量，默认值1G。超过则存储在#ibtemp1.性能字典表 memory_summary_global_by_event_name 存储 mysql> SELECT * FROM performance_schema. memory_summary_global_by_event_name WHERE event_name like '%temptable%'\G temptable_use_mmap:超过temptable_max_ram后,TempTable为内存内部临时表分配空间,作为映射临时文件.禁用则用InnoDB引擎内部临时表,8.0.16弃用. temptable_max_mmap:设置0禁止,其他为开始将数据存储到磁盘上的 InnoDB 内部临时表之前，被允许从内存映射的临时文件分配的最大内存量（以字节为单位)官方建议设置为0。 2.新增引擎：门实现了一个临时表的引擎 TempTable,解决varchar变长和大对象存储. - 可以支持变长类型，例如 varchar(100)的数据”abcd”应该只占用4个字节而非100个字节，节省内存； - 支持二进制大对象，例如 blob, text 等。如果使用 MEMORY 引擎，这样的内部临时表会直接使用磁盘临时表，这个是为了提升性能。 - 问题：8.0.20,26,28都是有问题的 执行sql报错 - 建议设置:internal_tmp_mem_storage_engine=MEMORY 3.临时表空间变化 8.0 - 会话级别临时表空间: SQL 产生的内部临时表将存储在会话临时表空间 - 全局级别临时表空间: 存储用户创建的临时表的回滚段 - 参数:innodb_temp_tablespaces_dir ：定义了创建会话临时表空间的位置，默认位置是数据目录中 #innodb_temp的目录 shell> ls datadir/#innodb_temp temp_10.ibt temp_2.ibt temp_4.ibt temp_6.ibt temp_8.ibt temp_1.ibt temp_3.ibt temp_5.ibt temp_7.ibt temp_9.ibt - 会话临时表空间:10个临时表空间的池,1个session两个表空间(1个用于用户创建临时表,一个用于优化器创建临时表)，会话断开,清除空间释放回池 ``` **查看用户创建的临时表：** 任何 session 都可以执行下面的语句； 查看用户创建的当前 active 的临时表(不提供 optimizer 使用的内部 InnoDB 临时表信息) ```sql SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G ``` **注意：** 通过 INNODB_TEMP_TABLE_INFO 查看到的临时表的 NAME 是#sql开头的名字， 另外 information_schema.tables 表中是不会记录临时表的信息的。 **用户创建的临时表的回收：** 1. 会话断开，自动回收用户创建的临时表； 2. 可以通过 drop table 删除用户创建的临时表，例如：drop table t1; **用户创建的临时表的的其他信息&参数(8.0)：** ```sql select * from information_schema.innodb_session_temp_tablespaces ; ``` 查询全局临时表空间的数据文件大小： ```sql SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME = 'innodb_temporary'\G ``` 监控 TempTable 从内存和磁盘上分配的空间： ```sql select * from performance_schema.memory_summary_global_by_event_name \ where event_name in('memory/temptable/physical_ram','memory/temptable/physical_disk') \G ``` 具体的字段含义见：Section 27.12.20.10, “Memory Summary Tables”. 总结: ```sql 普通的磁盘临时表|临时文件(一般需要较小的空间)： 临时表|临时文件的一般所需的空间较小，会优先存放于内存中，若超过一定的大小，则会转换为磁盘临时表|临时文件； 磁盘临时表默认为 InnoDB 引擎，其存放在临时表空间中，由 innodb_temp_tablespaces_dir 定义表空间的存放目录，表空间文件类似：temp_[1-20].ibt ；MySQL 未定义 InnoDB 临时表空间的最大使用上限； 当临时表|临时文件使用完毕后，会自动回收临时表空间文件的大小； innodb_temp_data_file_path 定义了用户创建的临时表使用的回滚段的存储文件的相对路径、名字、大小和属性，该文件是全局临时表空间(ibtmp1)，该文件可以设置文件最大使用大小； **例外项(一般需要较大的空间)：** load data local 语句，客户端读取文件并将其内容发送到服务器，服务器将其存储在 tmpdir 参数指定的路径中； 在 replica 中，回放 load data 语句时，需要将从 relay log 中解析出来的数据存储在 slave_load_tmpdir(replica_load_tmpdir) 指定的目录中，该参数默认和 tmpdir 参数指定的路径相同； 需要 rebuild table 的在线 alter table 需要使用 innodb_tmpdir 存放排序磁盘排序文件，如果 innodb_tmpdir 未指定，则使用 tmpdir 的值； 若用户判断产生的临时表|临时文件一定会转换为磁盘临时表|临时文件，那么可以设置 set session big_tables=1;让产生的临时表|临时文件直接存放在磁盘上； **猜测其设计：** 对于需要较小空间的临时表|临时文件，MySQL 要么将其存储于内存，要么放在统一的磁盘临时表空间中，用完即释放； 对于需要较大空间的临时表|临时文件，可以通过设置参数，将其存储于单独的目录|挂载点；例如：load local data 语句或需要重建表的在线 alter table 语句，都有对应的参数设置其存放临时表|临时文件的路径； 当前只有 innodb_temp_data_file_path 参数可以限制 用户创建的临时表使用的回滚段的存储文件的大小，无其他参数可以限制临时表|临时文件可使用的磁盘空间； ``` ## 1.4.段,区,页,行  ```sql - 行(row) 1.对应表中行记录 2.每页存储最多行记录规定 16KB/2-200 = 7992行 (每个记录最少2字节,每个页预留200字节，硬性规定) 3.每个页最少存储2行,虚拟记录,限定记录边界,最大虚拟记录和最小虚拟记录 ``` ### 1.4.1.段 ```sql #概念 段(segment): (1)表空间由不同的段组成,表也可以叫段,逻辑概念,管理物理文件,构成索引,表和回滚段的基本元素. (2)创建一个索引树同时创建两个段(叶子节点段(数据段)和内节点段(索引段)),一个索引,两个段 (3)表中段的数目:索引X2 #存储: 1.数据段:B+树 索引及数据 叶子节点 2.索引段:非叶子节点 3.回滚段:存储undo日志,事务失败回滚和未提交之前的数据 ``` ### 1.4.2.区 ```sql #概念 (1)构成段的基本元素,连续的页组成(默认一页16K),每个区固定大小1MB。默认不压缩,一个区64个连续的页。 (2)一个段至少一个区,创建一个段时候创建一个区,如果存放更多数据,从段中申请分配新的区。 (3)区的位置没关系,通过指针建立连接。 ``` ### 1.4.3.页 ```sql 概念: 1.组成区的最小单位，64个页默认组成一个区.归区管理,逻辑上(页面号)及物理上都是连续的. 2.InnoDB存储引擎的最小管理单位,默认16KB 3.页类型:数据页,undo页,系统页... ``` **页的概览** InnoDB设计,页与页通过双向链表连接  存储页中的行,通过单链表连接,下一页指针快速调到下一页记录  最大与最小记录(Infimum & Supremum Records) ```sql 用处:不需要遍历某一页的记录,将最大最小记录和查询目标比较.比如查询101，比较下不在此页,通过下一页指针跳转到下页进行检索。 概念:最小记录和最大纪录的开区间，Infimum Records 会比当前页中的最小值还要小，而 Supremum Records 会比当前页中的最大值要大 ```  使用Page Directory ```sql #目的 如果在1-100之间的检索目标,为了防止遍历,引入Page Directory #概念 (1)一个有很多槽位的目录(Slots),每个槽位(Slots)都指向一条记录.每隔6条数据一个slot(类似数据结构 跳表) (2)Slot可以对页数据进行粗略的二分查找，然后进行匹配 ```  **页的物理组成** 上面讲了页的设计结构(为什么这么设计)，真是组成如下  ```sql 没聊过的部分 File Header Page Header Free Space:空间,没有需要新申请页 File Tailer ``` File Header： ```sql 暂时只需要知道两个: - FIL_PAGE_PREV：上一页指针 - FIL_PAGE_NEXT：下一页指针 指针:页在磁盘上的偏移量 ```  Page Header ```sql PAGE_N_HEAP：包含被标记删除的记录 PAGE_N_RECS：实际上查询到的所有数据 ```  **User Recoards** ```sql 数据的排列顺序: 聚簇索引:按照PK排序 非聚簇索引: (1)不会按照PK那样的顺序对数据重新排序,拉低MySQL处理效率 (2)通过单链表指针指向来保证,形成一个按照PK排列的顺序 ```  File Trailer ```sql 为了防止页在刷入磁盘的过程中，由于极端的意外情况（网络问题、火灾、自然灾害）导致失败，而造成数据不一致的情况，也就是说形成了脏页。 只有一个部分. FIL_PAGE_END_LSN（8字节） ``` ### 1.4.4.段,区,页的组织结构(InnoDB源代码)  ```sql 一个表空间初始有一个文件,叫做0号文件. ``` ### 1.4.5.行 ```sql #概念 4中行格式(Row Format)：Redundant、Compact、Dynamic、Compressed MySQL 5.7默认Dynamic #如何查看 mysql> show table status like '%%app_user'\G ``` Compact格式 ```sql 5.0被引入,高效存储数据,大致分为两部分 (1)记录额外数据:描述该条记录 (2)记录数据内容:存储各个column的数据值+隐藏列 额外额数详解: 1.变长字段的长度列表 (1)记录变长的数据类型,(VARCHAR等),额外存储数据的长度信息(占用字节数) 允许存储最大字节数不超过255个字节,长度信息需要一个字节 允许存储超过255个字节 字段数据实际使用不过127个字节 (2)如果没有变成字段或记录中所有变成字段值为NULL,则列表不存在 (3)列表信息按照列的顺序逆序排序 表 column1,column2,column3,column4 "a", null ,"ccc" ,99 变长列表内容column1为1,column3为3，则记录变长字段的长度列表内容:0X03(十进制3)0X01(十进制1) 2.NULL值标志位 (1)不记录数据内容,记录位向量存储(0 or 1),1位NULL,0非NULL (2)如果字段都部位NULL,则不存在 额外数据内容详解 ```  3.记录头信息 ```sql 描述ROW记录，固定5个字节,即40位。定义: - 预留位1、2：暂未使用 - delete_mask：当前记录被删除的标志位 - min_rec_mask：B+树的每层非叶子节点中的最小记录的标志位 - n_owned：当前记录拥有的记录数 - heap_no：当前记录在记录堆中的位置 - record_type：当前记录类型。具体地，0: 普通记录；1: B+树非叶子节点记录（即所谓的目录项记录）；2: 最小记录；3: 最大记录 - next_record：下一条记录的相对位置,将所有记录连接起来形成单链表. ```  4.记录的数据内容 ```sql 存储内容: 实际记录的column数据+隐藏列(默认插入) (1)DB_ROW_ID：占6个字节，用于标识一条记录(不一定存在,没有PK或者UQ的时候才有) (2)DB_TRX_ID：占6个字节，其值为事务ID(必存在) (3)DB_ROLL_PTR：占7个字节，其值为回滚指针(必存在) ``` 5.行溢出 ```sql 概念:某个记录的某个字段text类型等，值长度过大，一个页放不下.则在该记录的数据内容的相应字段处只存储该字段值前768个字节的数据和一个指向存储剩余数据的其他页(溢出页地址),20字节 ``` 6.Dynamic、Compressed行格式 ```sql (1)compact行格式比较相似。 (2)不同,Dynamic、Compressed行格式会把记录中数据量过大的字段值全部存储到溢出页中. ``` ## 1.5.Double Wrtie Buffer(双写缓冲区)  ```sql #概念 实现double write而设置的一块缓冲区,保证数据可靠性 #作用 保存一个页的副本,防止页失效.结合REDO LOG恢复数据 #情况 存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了 4K，这种情况叫做部分写失效（partial page write），可能会导致数据丢失。 #组成: (1)内存中double write buffer,大小为2MB (2)ibdata（共享表空间）连续128个页(2MB,2个区) #8.0可以设置参数独立 #原理 (1)对Buffer pool脏页刷新,不直接写磁盘,先将脏页复制到内存中的doublewrite buffer (2)分两次,每次1MB顺序写ibdata上的doublewrite (3)调用fsync(),数据以一个大的顺序块的形式写入磁盘.避免多次IO开销. (4)完成doublewrite写入后,讲buffer中的页写入到各个表空间中,此时写是离散IO. #恢复 InnoDB从doublewrite中找到页的副本,复制到tablespace,配合redo log进行恢复 #缺点 写DWB会导致系统有更多的fsync操作,多一次顺序写.降低10%性能 #参数 观察写情况 show status like "%InnoDB_dblwr%"; InnoDB_dblwr_pages_written #从bp flush 到 DBWB的个数 InnoDB_dblwr_writes #写文件的次数 启用 innodb_doublewrite #0为禁用 innodb_doublewrite_dir #8.0.20引入 独立DWB位置 innodb_doublewrite_files #双写文件数量,默认buffer pool instance两个双写文件,不用调整 innodb_doublewrite_pages #8.0.20引入,控制每个线程双写页的最大数目,默认不用调整 ```  ## 1.6.REDO LOG与UNDO LOG ```sql 1.REDO LOG #概念 重做日志，提供再写入操作。物理日志 #作用 记录尚未完成的DML（insert,delete,update）,数据库崩溃则用log恢复。保证事务持久性 (1)在页面修改完成之后，脏页刷出磁盘之前，写入Redo日志； (2)实现WAL（Write Ahead Log）:日志比数据页先写回磁盘 (3)聚簇索引/二级索引/Undo页面修改，记录Redo日志； #组成 (1)保存在内存中重做日志的缓冲 (redo log buffer),是易失的 (2)保存在硬盘中重做日志文件 (redo log file)，是持久的 2.UNDO LOG #概念 DML操作导致数据变化,将变化前的记录写入Undo日志。逻辑日志。 #作用 撤销还原，用于记录更改前的一份copy，在操作出错时，可以用于回滚、撤销还原，只将数据库逻辑地恢复到原来的样子 #细节 （1）Undo先于Redo写入 比如有两个用户访问数据库，当然并发。A是更改，B是查询。 --A更改还没有提交，B查询的话，数据肯定为历史数据，这个历史数据就是来源于UNDO段， --A更改未提交，需要回滚rollback，回滚rollback的数据也来至于UNDO段。 结论：为了并发时读一致性成功，那么DML操作，肯定先写UNDO段。 (2)Undo页面修改,同样需要记录Redo日志 #存储位置 5.6.3可以单独管理undo的表空间(段,区,页)，undo存储在回滚段(Rollback Segment)中,每个回滚段记录了102个undo log segment ``` # 2.InnoDB事务 ## 2.1.介绍 ```sql #概念 数据库事务(简称：事务)是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成 比如多次SQL组成的业务流程 #应用场景 (1)电商平台中例如常见的下单流程，同时操作订单表，库存表等，这些操作要在一个事务里面完成。 (2)金融系统业务的银行转账，需要保证一个账户增加另一个账户减少，这两个动作 一定是成功或者同时失败的。 #事务控制语句(显式) begin; #开启事务 SQL #执行逻辑 commit or rollback #提交或者回滚事务 #事务控制语句(auto_commit),正常默认自动提交，也就是一个SQL前后跟着一个begin和commit mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ mysql>set global autocommit=0 #临时关闭事务,基本用不上 #隐式提交事务 什么叫隐式提交？某些条件下 事业自动提交,没走begin和commit DDL语句 DCL语句 LOCK #隐式回滚事务 session关闭 数据库关闭 死锁 ``` ## 2.2.事务的ACID ```sql A:原子性(Automicity) (1)一个事务DML语句,只能全部成功或者失败,没有中间态(成功一半,失败一半) (2)如果需要回滚,通过undo log回滚数据修改之前的值,undo log保持原子性 C:一致性(Consistency) 事务前后的操作都是合法的正确状态，例如转账，转账前和转账后金额保持一致，能量守恒状态。 I:隔离性 (Isolation) (1)多个事务操作同一张表或者同一行数据，互不干扰。 (2)锁和隔离级别保持隔离性 D:持久性 (Durability) (1)事务提交,对数据的操作，结果永久(落盘) (2)不会因为系统宕机或者重启数据库导致恢复到原来的状态(事务提交之前)。 (3)redo log保证 原子性，隔离性，持久性，最后都是为了实现一致性。 ``` ## 2.3.REDO ### 2.3.1.简介 ```sql #概念 重做日志，提供再写入操作。物理日志，innodb存储引擎层。 #CR 用来做数据的crash recovery #作用 记录尚未完成的DML,数据库崩溃则用log恢复。保证事务持久性 (1)在页面修改完成之后，脏页刷出磁盘之前，写入Redo日志； (2)实现WAL:日志比数据页先写回磁盘 (3)聚簇索引/二级索引/Undo页面修改，记录Redo日志； #组成 (1)保存在内存中重做日志的缓冲 (redo log buffer),是易失的 (2)保存在硬盘中重做日志文件 (redo log file)，是持久的 ib_logfile0,ib_logfile1 #相关参数 - innodb_log_files_in_group #redo log 文件的个数，命名方式如：ib_logfile0，iblogfile1 - innodb_log_file_size #单个 redo log 文件设置大小 - innodb_log_group_home_dir #redo log 文件组所在的路径 - innodb_log_buffer_size #redo log buffer 大小,默认16M。延迟事务日志写入磁盘，把 redo log 放到该缓冲区，然后根据 innodb_flush_log_at_trx_commit 参数的设置，再把日志从 buffer 中 flush 到磁盘中 - innodb_flush_log_at_trx_commit （1）redo log刷新到磁盘策略,默认1 - 值为0,每次commit 事务,并不将事务的重做日志写入磁盘上日志文件，而是等待主线程每秒刷新 - 1,执行commit时将重做日志缓冲同步写到磁盘，即伴有fsync的调用 - 2，将重做日志异步写到磁盘，即写到文件系统的缓存中。不保证commit时肯定会写入重做日志文件 缺点 - 0，当数据库发生宕机时，部分日志未刷新到磁盘，因此会丢失最后一段时间的事务。 - 2，当操作系统宕机时，重启数据库后会丢失未从文件系统缓存刷新到重做日志文件那部分事务 ```  ### 2.3.2.工作流程 ```sql 更新一条记录,redo流程如下: （1）从磁盘加载索引数据页到buffer pool的索引页中 （2）写入数据的旧值便于rollback （3）更新缓冲页中的数据 （4）生产一条重做日志并写入redo log buffer，记录的是数据被修改后的值 （5）当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式 （6）定期将内存中修改的数据刷新到磁盘中 ```  ### 2.3.3.Write Ahead Log ```sql #概念 InnoDB 的更新操作采用的是 Write Ahead Log (预先日志持久化)策略，即先写日志，再写入磁盘。 在持久化一个数据页之前，先将内存中相应的日志页持久化. #作用: (1)修改了什么"这个操作记录在日志中, 而不必马上将更改内容刷新到磁盘上, 从而将随机写转换为顺序写, 提高了性能。 (2)内存中的数据页会和磁盘上的数据页内容不一致, 此时将内存中的这种数据页称为 脏页（保证脏页正确的落盘） ``` ### 2.3.4.Redo Log Buffer 空间管理 ```sql buffer pool中把数据修改情况记录到redo log buffer，出现以下情况，再把redo log刷下到redo log file： - Redo log buffer空间不足 - 事务提交（依赖innodb_flush_log_at_trx_commit参数设置） - 后台线程 - 做checkpoint - 实例shutdown - binlog切换 ``` ### 2.3.5.checkpoint与LSN ```sql 1.LSN #概念 LSN:（log sequence number）日志序列号，LSN主要用于发生crash 时对数据进行recovery，LSN是一个一直递增的整型数字，表示事务写入到日志的字节总量. #存在位置 (1)日志会携带一个LSN (2)每个数据页上也会记录一个LSN(日志序列号). #作用 (LSN)可以用于数据页是否是脏页的判断，比如说 write pos对应的LSN比某个数据页的LSN大, 则这个数据页肯定是干净页, 同时当脏页提前刷到磁盘时, 在应用Redo Log可以识别是否刷过并跳过. 查看lsn: show engine innodb status\G Log sequence number: 当前系统最大的LSN号 log flushed up to:当前已经写入redo日志文件的LSN pages flushed up to：已经将更改写入脏页的lsn号 Last checkpoint at就是系统最后一次刷新buffer pool脏中页数据到磁盘的checkpoint 以上4个LSN是递减的: LSN1>=LSN2>=LSN3>=LSN4. 内容: 每个数据页有LSN，重做日志有LSN，checkpoint有LSN。 2.checkpoint #概念 对于内存中的脏页，在一定条件下将脏页刷新到磁盘,这个点位行为叫checkpoint. #分类 (1)sharp checkpoint：在关闭数据库的时候，将buffer pool中的脏页全部刷新到磁盘中。 (2)fuzzy checkpoint：数据库正常运行时，在不同的时机，将部分脏页写入磁盘，进刷新部分脏页到磁盘，也是为了避免一次刷新全部的脏页造成的性能问题。 - Master Thread checkpoint：每秒或者每10秒一次的频率，将部分脏页从内存中刷新到磁盘 - FLUSH_LRU_LIST checkpoint:单独的page cleaner线程中执行的。 - innodb_lru_scan_depth参数控制(free lru list少于配置刷盘,提出LRU尾端) - mysql> show variables like '%lru%depth'; - dirty page too much checkpoint:脏页过多（占buffer pool百分比）,innodb_max_dirty_pages_pct参数控制(默认75%) - Async/Sync Flush checkpoint (1)单独的page cleaner线程中执行的,分为异步和同步.不可覆盖(待刷盘)redo log占logfile比例。75%--->异步、90%--->同步。 (2)设重做日志的LSN记为redo_lsn，将已经刷新回磁盘最新页的LSN记为 checkpoint_lsn.则checkpoint_age = redo_lsn - checkpoint_lsn (3)async_water_mark = 75% * total_redo_log_file_size #异步 sync_water_mark = 90% * total_redo_log_file_size #同步 (4) checkpoint_age<async_water_mark #无需刷盘 async_water_mark<checkpoint_age<sync_water_mark #Async Flush checkpoint,触发刷盘到checkpoint_age<async_water_mark checkpoint_age>sync_water_mark #强制同步刷盘,影响数据库性能会抖一下,基本很少发生 #参数控制 mysql> show global status like 'Innodb_buffer_pool_pages%t%';#查看page页数量 mysql> show global status like '%wait_free'; #不为0代表需要free page，没有可用，数据库性能有问题 ``` ## 2.4.UNDO ### 2.4.1.概念 ```sql #概念 DML操作导致数据变化,将变化前的记录写入Undo日志。逻辑日志。 #作用 撤销还原，用于记录更改前的一份copy，在操作出错时，可以用于回滚、撤销还原，只将数据库逻辑地恢复到原来的样子 #细节 （1）Undo先与Redo写入 比如有两个用户访问数据库，当然并发罗。A是更改，B是查询。 --A更改还没有提交，B查询的话，数据肯定为历史数据，这个历史数据就是来源于UNDO段， --A更改未提交，需要回滚rollback，回滚rollback的数据也来至于UNDO段。 结论：为了并发时读一致性成功，那么DML操作，肯定先写UNDO段。 (2)Undo页面修改,同样需要记录Redo日志 #存储位置 5.6.3可以单独管理undo的表空间(段,区,页)，undo存储在回滚段(Rollback Segment)中,每个回滚段记录了1024个undo log segment #分类 - insert undo log:insert操作的undo,对其他事物不可见,事物提交后删除,不用purge - update undo log：delete 或者update产生的undo log,提供MVCC机制,不能事物提交就删除,提交后放入undo log链表,等待purge线程进行删除 purge线程作用: (1)清理undo页和清除page里面带有Delete_Bit标识的数据行 (2)InnoDB中，事务中的Delete操作实际上并不是真正的删除掉数据行，而是一种Delete Mark操作，在记录上标识Delete_Bit，而不删除记录。是一种"假删除",只是做了个标记，真正的删除工作需要后台purge线程去完成。 ``` ### 2.4.2.redo & undo联动 ```sql 下面是redo log + undo log的简化过程，便于理解两种日志的过程： 假设有A、B两个数据，值分别为1,2. 1. 事务开始;begin 2. 记录A=1到undo log 3. 修改A=3 4. 记录A=3到 redo log 5. 记录B=2到 undo log 6. 修改B=4 7. 记录B=4到redo log 8. 将redo log buffer写入磁盘 redo log 9. 事务提交 实际上，在insert/update/delete操作中，redo和undo分别记录的内容都不一样，量也不一样。在InnoDB内存中，一般的顺序如下： (1)写undo的redo (2)写undo (3)修改数据页 (4)写Redo undo与redo的联系：不管是脏的还是旧的，都在redo日志中复制了一份 1.undo是一种“数据文件datafile”，具有表空间，当然具有块block; 2.redo是一种“文件file”，没有表空间。 3.数据库在DML事务时，先创建undo 4.读一致性与一致性(scn相同）的区别 5.undo与rollback的区别：在undo（撤销还原流程）中会使用rollback（回滚）这个动作 ``` ### 2.4.3.CR流程 ```sql 1.什么时候会进行Crash Recovery? - 实例崩溃之后重启使用一个备份还原后在 - “快速”(innodb_fast_shutdown不为0值关闭实例)关闭实例后重新启动 2. 检测实例是不是干净地关闭的 - 打开Redo Logs和系统表空间文件(ibdataN) - 读取并从中找到最大的Checkpoint LSN - 从最近的Checkpoint 开始往后扫描Redo Log - 如果能够找到Redo Log记录，说明还有数据页的更改没有刷新到数据文件上，启动Crash Recovery，使用Redo Log来恢复数据的一致性 3.使用所有独立表空间的表名和表空间ID创建一个名称到ID的映射 - 打开datadir下的所有.ibd文件 - 在这些表空间文件的offset 0的页（FSP_HDR页）头读取其表空间ID(FSP_HDR页中FSP Header的前四个字节记录着表空间ID) - 将表空间ID与表名建立映射 4.损坏页修复（检查是否有不完整的页，如果有则使用Double Write Buffer进行修复） - 检查双写缓冲区中的所有128个页： - 读取表空间中的每个“目标”页 - 如果页头和页尾的LSN不匹配或页面校验和无效，则使用双写缓冲区中的页进行还原 - 如果该页在双写缓冲区中的版本也被破坏，则server将crash（innodb_force_recovery=0） 5.前滚Redo，提交未提交事务 - 事务系统初始化（回滚段初始化） - 从最近的Checkpoint 往后扫描到的Redo Log记录将被应用到各个数据文件中 - 从Undo Log中恢复处于'ACTIVE'状态的事务，重新生成read view - 使用Undo Log回滚未提交的'ACTIVE'状态的事务 - 处于PREPARE状态的事务，如果打开了binlog且在binlog有找到对应事务的日志则重新提交，否则回滚 ``` ## 2.5.隔离级别 ### 2.5.1.概念与简介 ```sql #概念 解决事务工作期间 读 的隔离，保证并发读的性能 #读的分类 脏读:脏读指的是读到了其他事务未提交的数据 可重复读:可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据更新（UPDATE）操作。 不可重复读:对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操作。 幻读:幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，这就叫幻读。 #四种隔离级别 读未提交（READ UNCOMMITTED） 读提交 （READ COMMITTED） 可重复读 （REPEATABLE READ） 串行化 （SERIALIZABLE） 从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是 MySQL 的默认级别。 事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题，下面展示了 4 种隔离级别对这三个问题的解决程度。 ```  ### 2.5.2.实操 ```sql 设置隔离级别: # 查看事务隔离级别 5.7.20 之后 show variables like 'transaction_isolation'; SELECT @@transaction_isolation # 5.7.20 之后 SELECT @@tx_isolation show variables like 'tx_isolation' +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | 修改隔离级别的语句是： SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}。 其中作用于可以是 SESSION 或者 GLOBAL，GLOBAL 是全局的，而 SESSION 只针对当前回话窗口。隔离级别是 {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} 这四种，不区分大小写。 #设置全局隔离级别为读提交级别。 mysql> set global transaction isolation level read committed; ``` 验证隔离级别: ```sql CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, `age` tinyint(4) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; insert into user values (1,'古时的风筝',1); #RU-基本不加锁,性能最好 启动两个事务，分别为事务A和事务B，在事务A中使用 update 语句，修改 age 的值为10，初始是1 ，在执行完 update 语句之后，在事务B中查询 user 表，会看到 age 的值已经是 10 了，这时候事务A还没有提交，而此时事务B有可能拿着已经修改过的 age=10 去进行其他操作了。在事务B进行操作的过程中，很有可能事务A由于某些原因，进行了事务回滚操作，那其实事务B得到的就是脏数据了，拿着脏数据去进行其他的计算，那结果肯定也是有问题的。 set global transaction isolation level read uncommitted; update user set age=10 where id=1; #事物B发生脏读 ```  RC ```sql set global transaction isolation level read committed; 同样开启事务A和事务B两个事务，在事务A中使用 update 语句将 id=1 的记录行 age 字段改为 10。此时，在事务B中使用 select 语句进行查询，我们发现在事务A提交之前，事务B中查询到的记录 age 一直是1，直到事务A提交，此时在事务B中 select 查询，发现 age 的值已经是 10 了。 这就出现了一个问题，在同一事务中(本例中的事务B)，事务的不同时刻同样的查询条件，查询出来的记录内容是不一样的，事务A的提交影响了事务B的查询结果，这就是不可重复读，也就是读提交隔离级别。 本质:每个 select 语句都有自己的一份快照，而不是一个事务一份，所以在不同的时刻，查询出来的数据可能是不一致的。 ```  RR ```sql 可重复是对比不可重复而言的，上面说不可重复读是指同一事物不同时刻读到的数据值可能不一致。而可重复读是指，事务不会读到其他事务对已有数据的修改，及时其他事务已提交，也就是说，事务开始时读到的已有数据是什么，在事务提交前的任意时刻，这些数据的值都是一样的。但是，对于其他事务新插入的数据是可以读到的，这也就引发了幻读问题。 可重复读,默认. (1)同一个事务在第一次读取数据会创建快照,一直使用当前readview, (2)事务不会读到其他事务对已有数据的修改,在事务提交前的任意时刻，这些数据的值都是一样的 (3)所以mysqldump要开启RR后立马开启事务快照,不然会数据污染)。 #设置隔离级别 set global transaction isolation level repeatable read; 在这个隔离级别下，启动两个事务，两个事务同时开启。 首先看一下可重复读的效果，事务A启动后修改了数据，并且在事务B之前提交，事务B在事务开始和事务A提交之后两个时间节点都读取的数据相同，已经可以看出可重复读的效果。 ```  ```sql 可重复读做到了，这只是针对已有行的更改操作有效，但是对于新插入的行记录，产生幻读 事务A开始后，执行 update 操作，将 age = 1 的记录的 name 改为“风筝2号”； 事务B开始后，在事务执行完 update 后，执行 insert 操作，插入记录 age =1，name = 古时的风筝，这和事务A修改的那条记录值相同，然后提交。 事务B提交后，事务A中执行 select，查询 age=1 的数据，这时，会发现多了一行，并且发现还有一条 name = 古时的风筝，age = 1 的记录，这其实就是事务B刚刚插入的，这就是幻读。 #实际上不会出现幻读,因为MySQL用锁+RR解决了这个问题 ```  串行化 ```sql 串行化是4种事务隔离级别中隔离效果最好的，解决了脏读、可重复读、幻读的问题，但是效果最差，它将事务的执行变为顺序执行，与其他三个隔离级别相比，它就相当于单线程，后一个事务的执行必须等待前一个事务结束。 ``` # 3.InnoDB锁与MVCC ## 3.0.一致性非锁定读和锁定读(Day7概念补充) ```sql 1. InnoDB的两种SELECT - 普通select - 锁定读SELECT - select ... for update - select ... for share 2.SELECT加锁场景 - 普通SELECT 时使用一致性非锁定读，不加锁； - 锁定读SELECT 使用锁定读，加锁； - DML(INSERT/UPDATE/DELETE)时，需要先查询表中的记录，此时也使用锁定读，加锁； - FOR SHARE 语法是MySQL8.0时加入的，FOR SHARE 和 LOCK IN SHARE MODE 是等价。 3.一致性非锁定读(consistent nonlocking read) - InnoDB采用MVCC增加读操作并发性,通过基于时间点快照获取查询结果，读取访问表上不设置任何锁. - 事务T1读取(普通select)时刻,事务T2自由修改T1读取的数据,称为一致性非锁定读 - RU和Serializable时不需要MVCC,RC和RR需要MVCC - RC:T1事务内,每一个读取都要重新拍一个数据快照,读取到最新数据 - RR:只在事务第一个读取前照一个快照,之后读取都这个快照 4.锁定读(locking read) 作用:为了防止事务查询数据时候被其他事务更改删除查询的数据行. 类别:Mysql提供两种非锁定读,SELECT ... FOR SHARE 和 SELECT ... FOR UPDATE 功能:锁定读在搜索的每一条索引记录上设置排它锁和共享锁(RR级别设置gap lock) ``` ## 3.1.InnoDB锁介绍 ```sql #概念 锁机制:并发写隔离 #作用 保证并发访问数据的正确性 #锁的分类 1.latch（闩锁）： - 概念:轻量级锁,要求锁定时间短,时间长则性能差 - InnoDB:有rwlock(读写锁 IS IX,保证并发线程访问正确性)、mutex(互斥,主要保护内存资源)两种锁 - 无死锁机制 2.lock: - 对象是事务,锁定数据库对象,如表,页,行。 - 事务commit或者rollback后释放 - 具有死锁机制 - 下面都是lock锁 - InnoDB lock :MDL,表级锁,行级锁,自增锁 ###### 1.MDL： Metadata_lock,元数据(DDL操作) 2.表级锁: - lock table a read/write - FTWRL:mysqldump、XBK：备份非InnoDB数据时，触发FTWRL全局锁表 （Global）。 3.行级锁row lock：InnoDB 默认锁粒度，索引加锁的。 - record lock ： 记录锁，在索引锁定。RC级别只有record lock。 - gap lock ： 间隙锁，在辅助索引间隙加锁。RR级别存在。防止幻读。 - next lock ： 下一键锁， GAP+Record。 RR级别存在。防止幻读 4.自增锁 5.功能锁： - 分类:共享锁或排它锁(Shared and Exclusive Locks)以及意向锁（IX,IS） - 共享锁概念:不是锁类型,而是锁的模式,每个锁都有共享和排他两种模式 - 意向锁概念:已经持有表锁,之后获取表中某些行的行锁,分为IX,IS 5.1. S与X锁 - 一般指行上的共享锁或者行上的排它锁(表锁也有S和X锁) - S和X兼容性 - 假设T1持有数据行r上的S锁，则当T2请求r上的锁时： 1. T2请求r上的S锁，则，T2立即获得S锁。T1和T2同时都持有r上的S锁。 2. T2请求r上的X锁，则，T2无法获得X锁。T2必须要等待直到T1释放r上的S锁。 - 假设T1持有r上的X锁，则当T2请求r上的锁时： 1. T2请求r上的任何类型的锁时，T2都无法获得锁，此时，T2必须要等待直到T1释放r上的X锁 5.2. 意向锁IS与IX - 概念:含义是已经持有了表锁，稍候将获取该表上某个/些行的行锁。有shard或exclusive两种模式 - LOCK_MODE：IS或IX(意向共享锁和意向排他锁) - 作用 1. 锁定层级数据结构(获取自层级锁之前,必须获取父层级锁) - 比如,schema是表的集合，表是行的集合,意向锁获取自层级(行)的锁之前,首先获取父层级(表)锁 2. 告知其他事物,某事务已经锁定了或即将锁定某个/些数据行.事务在获取行锁之前，首先要获取到意向锁 - 事务在获取行上的S锁之前，事务必须首先获取表上的IS锁 - 事务在获取行上的X锁之前，事务必须首先获取表上的IX锁 - 兼容性,事务请求锁时 - 请求的锁与已存在的锁兼容，则该事务可以成功获得所请求的锁； - 请求的锁与已存在的锁冲突，则该事务无法获得所请求的锁。 - IS,S,IX,X - IS:表明事务将会对表的行施加S锁 - S:允许拥有共享锁的事务读取该行数据,一个事务拥有S锁,其他事务也可以在同一行数据拥有S锁,但是排斥X锁 - IX:事务将会对表中的施加X锁 - X:允许拥有排它锁的事务修改或删除该行数据,其他事务无法获得S和X锁 select...for share mode施加IS select..for update施加IX ``` ### 3.1.1.自增锁(AUTO-INC Locks)(Day7概念补充) ```sql 1.概念:表锁,向带有AUTO_INCREMENT列的表时插入数据行时，事务需要首先获取到该表的AUTO-INC表级锁，以便可以生成连续的自增值。插入语句开始时请求该锁，插入语句结束后释放该锁(注意：是语句结束后，而不是事务结束后)。 2.涉及SQL语句及系统变量innodb_autoinc_lock_mode: - insert-like语句 - insert - insert...select - replace - replace...select - load data - 按类型区分: - simple-inserts:待插入记录条数,提前确定,需要自增数个数也确定 - insert batch('a'),('b') - bulk-inserts: - 待插入记录的条数，不能提前确定，因此所需要的自增值的个数 也就无法提前确定 - insert ... select, replace ... select, load data - mixed-mode-inserts: - simple-inserts语句中包含指定自增列的值或者指定部分自增列的值,比如 - INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); - INSERT ... ON DUPLICATE KEY UPDATE 也是 - 系统变量 innodb_autoinc_lock_mode - 0,1,2三个值。8.0.3及之后默认值是2，即交织性锁定模式(interleaved lock mode) - 0:INSERT-like语句都需要获取到AUTO-INC表级锁； - 1:插入行的条数可以提前确定，不用AUTO-INC表级锁；插入行的条数无法提前确定，获取AUTO-INC表级锁。 - 2:不再使用AUTO-INC表级锁 ``` ## 3.2.四种锁的关系 - 表级锁(table-level lock) 兼容性矩阵,注意: - 在上面的兼容性矩阵中，S是表的(不是行的)共享锁，X是表的(不是行的)排它锁。 - 意向锁IS和IX 和任何行锁 都兼容(即：和行的X锁或行的S锁都兼容)。 - 意向锁只会阻塞 全表请求(例如：LOCK TABLES ... WRITE),不会阻塞其他任何请求,LOCK TABLES ... WRITE需要设置X表锁，会被意向锁IS或IX所阻塞。  ```sql 1.一个事务请求为数据加锁是否能立刻上锁,取决于改数据上已经存在的锁是否和请求的锁可以共存还是排斥关系,共存立刻加,排斥释放加 2.InnoDB允许表锁和行锁并存,使用意向锁来支持多粒度锁(multiple granularity locking) - 举例(这里讲解意向锁存在意义): - T1: SELECT * FROM t1 WHERE i=1 FOR UPDATE; - T2: LOCK TABLE t1 WRITE; - 如果有意向锁 - T1执行 - 需要获取i=1行的X锁(行锁) - 获取行锁前,必须获取t1的IX锁,不存在冲突，获取t1表IX锁 - 获取i=1的行锁 - T2执行 - 尝试获取t1表的X锁 - 发现t1表有IX锁,T2被阻塞(表X锁和IX锁不兼容) - 如果没有意向锁 - T1执行时，需要获取i=1的行的X锁(不需要获取t1表的意向锁了) - T2执行,获取表t1的X锁(表锁) - 无法立刻知道能否获取t1表的X锁。 - 遍历t1表的每一行数据,看是否有锁跟要上的表X锁冲突 总结: - 意向锁系实现 “表锁是否冲突”的快速判断 - 协调行锁与表锁之间关系保证多粒度锁机制的快速实现 ``` ## 3.3.InnoDB中行级锁 ```sql 1.InnoDB中锁的三种算法 - Record Lock：单个行记录上的锁 - Gap Lock:间隙锁,锁定一个范围,不包括记录。 - Next-key Lock:Record Loc+Gap Lock;锁定一个范围包括记录本身 1.1.Recork Lock #概念 1)索引记录锁，行级锁 2)作用范围:索引行上锁,比如select..from t where c1=10 ,c1=10上施加锁索引上锁. 3)当InnoDB表没有任何索引,行锁施加在聚簇索引上（即隐藏row_id的聚簇索引）,当SQL没走索引,在每一条聚簇索引上加X锁,最后形成效果类似表锁,但原理上和表锁完全不同 1.2.GAP锁 ##概念 1)范围条件:查询谓词不是等值条件 or update,delete SQL,请求X或S锁,InnoDB给符合条件的 已有数据记录的索引项加锁;对于 键值在条件范围内但并不存在的记录,叫做'间隙(GAP)',InnoDB也会对这个间隙加锁,防止幻读 ##gap lock前置条件 1.事务隔离级别 RR, innodb_lock_unsafe_for_binlog参数0,是否使用GAP锁,OFF为使用,ON为不使用,sql走索引非唯一索引 2.前置条件不变,sql是一个范围的当前读即使不是非唯一索引也会加gap lock 3.加锁范围,select * from table_a where c2>=11 and c2 <18 在第一个不满足条件查询的记录上加gap,防止新的插入 找到c2=11记录,接着扫描,找到c2=18记录,不满足条件,设置gap lock,区间为(11,18) 4.gap lock存在只为了阻塞 插入意向锁 5.gap锁之间不会相互阻塞 #next-lock锁 概念: - 记录锁和间隔锁结合,除了锁住该行,还要锁住索引之间的GAP - supremum pseudo-record：比索引存在的值还大的值,GAP锁,用来预防最大值后插入 (max,正无穷)之间 作用 - 解决幻读 #自增锁 - 针对事务插入表中自增列的特殊表级锁,一个事务插入时另一个事务必须等待 - 参数innodb_autoinc_lock_mode可以控制自增锁的使用方法 ```  ## 3.4.元数据锁MDL ```sql #概念 1.主要用来在并发环境下维护元数据的数据一致性。表明有活动事务(显示或者隐1式),不能对元数据进行操作 2.5.5.版本引入,与5.1的语句级,5.5是事务级的,解决DDL和DML操作的一致性 #作用 1)事务隔离问题:比如RR下,两次读取结果不一样,满足不了可重复读要求 2）事务复制问题:A执行多条update,B DDL先于A commit,导致slave重做时候,先做alter,在做update复制错误 #出现的地方 - FTWRL:先对global级别加意向独占锁,对schmae加意向排他锁,对table加 SHARED_NO_READ_WRITE锁 - DROP - RENAME - ALTER - LOCK TABLE ... - SELECT ..FOR UPDATE(SHARE MODE) ```    经典语句释放锁流程  SQL语句对应的锁  ## 3.5.死锁（Day7概念补充） ### 3.5.1.两阶段锁协议2PC(Day7概念补充) ```sql 1.概念: - 事务必须分两个阶段对数据加锁和解锁，在对任何数据进行读、写操作之前。 - 事务首先要获得对该数据的封锁； - 事务在释放一个封锁之后，事务不再申请和获得任何其他封锁。 2.Mysql的两个阶段 - 扩展阶段（事务开始后，commit 之前）：获取锁 - 收缩阶段（commit 之后）：释放锁 3.作用:实现可串行化调度（并发控制和性能保证） 4.缺点:并发控制方式会形成死锁 - 不要求事务必须一次将所有需要使用的数据加锁 - 加锁阶段没有顺序要求 ``` ### 3.5.2.死锁概念 ```sql 1.概念: 两个事务都持有对方需要的锁，并且在等待对方释放，并且双方都不会释放自己的锁。 - 举例:你有一个人质,我也有一个人质,谈判,你让对方放人,对方让你放人 2.产生死锁的四个条件 1)互斥条件:一个资源每次只能被一个进程使用 2)请求与保持:一个进程请求资源被阻塞,已有资源不释放 3)不剥夺条件:进程获得资源,未使用完,不能强行剥夺 4)循环等待:进程形成一种资源互相等待关系 ```  ### 3.5.3.Mysql如何处理死锁 ```sql 1.两种处理方式：一般用死锁监测处理死锁 - 等待，直到超时（innodb_lock_wait_timeout=50s） - 发起死锁检测，主动回滚一条事务，让其他事务继续执行（innodb_deadlock_detect=on） - 死锁检测:构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。 2.回滚:检测到死锁之后，选择插入更新或者删除的行数最少的事务回滚，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。 3.如何避免发生死锁 - 收集死锁信息 - 利用命令 SHOW ENGINE INNODB STATUS查看死锁原因。 - 调试阶段开启 innodb_print_all_deadlocks，收集所有死锁日志。 - 减少死锁： - 使用事务，不使用 lock tables 。 - 保证没有长事务 - 操作完之后立即提交事务，特别是在交互式命令行中 - 修改多个表或者多个行的时候，将修改的顺序保持一致 - 最好不要用 (SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE) - 业务上如果发生死锁,一定是业务的逻辑顺序有问题,收集冲突信息让业务调整逻辑 ``` ## 3.6.数据库如何看锁 1.information_schema.innodb_trx记录InnoDB执行事务和锁信息  2.information_schema.innodb_locks记录InnoDB事务中锁信息  3.innodb_lock_waits记录了InnoDB事务之间相互等待锁的信息  ## 3.7.MVCC ```sql #概念 多版本并发控制读取,可以读取历史数据快照(通过UNDO).通过保存数据在某个时间点的快照来实现的 #作用 数据库的行锁与行的多个版本结合起来，只需要很小的开销,就可以实现非锁定读，从而大大提高数据库系统的并发性能。 #工作过程 InnoDB的MVCC通过,每行记录后面保存的两个隐藏列实现,这里的时间指版本号,每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行纪录的版本号进行比较。 (1)保存行创建时间 (2)保存行的过期时间(删除时间) #InnoDB中的隐藏列(行结构讲过) DB_TRX_ID：事务标识符 DB_ROLL_PTR:回滚记录指针 DB_ROW_ID:行标识 ``` ### 3.7.1.工作原理 ```sql 1.select的查询结果 - InnoDB只查找版本早于当前事务版本的数据行，即，行的系统版本号小于或等于事务的系统版本号，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。 - 行的删除版本，要么未定义，要么大于当前事务版本号。这样可以确保事务读取到的行，在事务开始之前未被删除。 2.INSERT InnoDB为插入的每一行保存当前系统版本号作为行版本号。 3.DELETE InnoDB为删除的每一行保存当前系统版本号作为行删除标识。 4.UPDATE InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时，保存当前系统版本号到原来的行作为行删除标识。 ```
李延召
2024年3月20日 13:25
转发文档
收藏文档
上一篇
下一篇
手机扫码
复制链接
手机扫一扫转发分享
复制链接
Markdown文件
分享
链接
类型
密码
更新密码