mysql日常运维 mysql运行

MySQL是怎样运行的

第一章：初识MySQL

1.记住MySQL的安装路径

2.bin目录下的可执行文件启动服务器程序：mysqld(可执行文件)、mysql_safe(启动脚本，当出现错误时会帮助重启并记录日志)、mysql.server(启动脚本，间接调用了mysql_safe)、mysqld_multi(启动多个服务器，本质上也是一个Shell脚本)

3.启动客户端时 -p后面接密码不能有空格，最好输入启动命令行时不输入密码回车之后再输入，防止查看 history时密码泄露

4.MySQL默认采用TCP/IP协议作为客户端和服务器端的网络通讯协议

5.Windos的本机客户端和服务器端可以用管道(-pipe)来通讯

6.UNIX可以用套接字通讯，默认为/tmp/mysql.sock，可以在启动服务器端时指定修改

mysqld --socket=/tmp/a.txt

客户端需要显式指定连接名字

mysql -hlocalhost -uroot --socket=/tmp/a.txt -p

7.服务器处理客户端请求分三大部分：

1）连接管理：连接处理

2）解析与优化：查询缓存——语法解析——查询优化

查询过的数据会被缓存，当修改后会被移除，系统函数、自定义变量和函数、系统表不会被缓存

3）存储引擎：InnoDB、MyISAM等

8.MySQL默认存储引擎为InnoDB，常用的是InnoDB和MyISM

---

第二章：启动选项和系统变量

1.启动选项有长形式和段形式（–host -h），启动选项段形式区分大小写

2.启动选项通用形式

–启动选项[=值]

3.配置文件在my.cnf,配置文件中的不同组是给不同的程序使用的（[sever]组用于所有的服务器程序 [client]组用于所有的服务器程序）

4.命令行中启动选项优先级高于配置文件

5.系统变量可以通过启动选项设置（命令行和配置文件），还可以在程序运行时设置修改

SET GLOBAL 类型 =值

SET SESSION 类型=值（等价于 SET 类型=值）

6.GLOBAL变量影响服务器的整体操作，SESSION变量只影响某个客户端连接的操作

7.某个客户端修改GLOBAL的值不影响自己的SESSION值，只会影响后面连接的客户端

8.并不是所有的系统变量都有GLOBAL和SESSION作用范围

---

第三章：字符集和比较规则

1.重要的字符集：ASCII、ISO8859-1、GB2312、GBK、UTF-8

2.MySQL中使用utf8（utf8mb3）：阉割后的UTF-8字符集，用1_{3字节表示字符；utf8mb4：正宗的UTF-8字符集，用1}4字节表示字符

SHOW CHARSET;(查看字符集)

3.常用字符集及最大表示字符的字节长度

ASCII——1 latin1——1 gb2312——2 gbk——2 utf8——3 utf8mb4——4

4.查看字符集比较规则

SHOW COLLATION LIKE “”;

5.MySQL有四个级别的字符集和比较规则：服务器、数据库、表、行

服务器：character_set_server和collation_server系统变量

数据库：character_set_database和collation_database系统变量可以在创建、修改表的时候设置，不能修改其系统变量来改变，默认使用服务器级别

CREAT DATABSE base

CHARACTER SET utf8

COLLATION utf8_general_ci

表：也是在创建和修改时设置，语法同数据库，默认使用数据库级别

行：在创建和修改时设置，默认使用表级别

CRAET TABEL 表名{

列名 字符串类型 CHARACTER_SET utf8 COLLATION utf8_general_ci

}；

6.只修改字符集，比较规则会变为对应的默认规则，只修改比较规则，字符集会变为对应的默认规则

7.服务器将收到的字符序列按照character_set_client解析，在处理时按照character_set_connection对对应字符集编码，返回时按character_set_results编码，为每一个客户端单独维护这三个系统变量，且均为SESSION级别。客户端在收到后按操作系统默认的编码格式进行解码

8.当列的编码规则和连接编码规则不一致时，列编码规则优先级更高

---

第四章InnoDB记录存储结构

1.InnoDB采取页读取方式，一页大小16KB

2.记录在磁盘上的存放形式也被称之为行格式或者记录格式，MySQL有四种：COMPACT、REDUNDANT、DYNAMIC、COMPRESSED

3.COMPACT

一条记录分为：记录的额外信息（变长字段长度列表–NULL值列表–记录头信息）记录的真实信息（列1的值–列2的值–列3的值…）

变长字段：varchar、blob、text

NULL值字段：统计允许NULL的列，按列顺序将NULL列位置置为1，并将其倒序，再将高位补0

记录头信息：由固定的5字节组成

记录的真实数据：添加的隐藏列：row_id、trix_id（事务ID–必有）、roll_pointer（回滚指针–必有）

4.InnoDB的主键生成策略：优先自定义，无则选取一个UNIQUE的非NULL列，再无则默认添加一个row_id的列作为主键

5.对于CHAR类型如果采用定长字符集则不加入变长字段长度列表，反之则加入

6.REDUNDANT

记录的额外信息（字段长度偏移列表–记录头信息）记录的真实信息（列1的值–列2的值–列3的值…）

7.当一列存不了一列数据时会在页末尾添加溢出页地址

8.MySQL规定一页最少存储两行数据

9.DYNAMIC行格式不会像COMPACT一样在真实信息存储一部分数据，而是只存储指向溢出页的地址，将所有真实数据存储到溢出页当中去

10.COMPRESSED行格式会采用压缩算法对页面进行压缩，以节省空间

---

第五章InnoDB数据页结构

1.InnoDB数据页结构

File Header----页的一些通用信息——38字节

Page Header----数据页专有的一些信息——56字节

Infimum+Supermum----两个虚拟的记录——26字节（规定Infimum为最小记录，Supermum为最大记录，规定！）

User Records----用户存储的记录内容——不确定

Free Space----页中尚未使用的空间——不确定

Page Dictionary—页中某些记录的相对位置——不确定

File Trailer----校验页是否完整——8字节

2.所有被删除的行只是在记录头信息中将其delete_flag置1，并将其连成一个垃圾链表

3.对于一条完整的记录来说，比较记录的大小就是比较其主键的大小

4.记录头信息

delete_flag：记录是否被删除——1bit

min_rec_flag：目录项记录为1，普通记录为0

n_owned：“大哥”的值为该组的记录总数，“小弟”的值为0

heap_no：当前记录在页面堆的相对位置，从2开始，规定0为Infimum，1为Supermum

record_type：表示记录类型：0—普通记录，1—B+树非叶节点记录（目录项记录），2—Infimum记录，3—Supermum记录

next_record：表示下一记录的相对位置，可以理解为链表的next指针，0表示为最后一个，各个节点按照主键来连成一个链表

5.删除一行数据本质操作

将其delete_flag置1，从列表中删除操纵同理处理上一节点和下一节点，next_record置为0，Supermum的n_owned值-1；

恢复：直接复用而不重新开辟

6.Page Dictionary

存储将记录进行分组后的每组最后一条记录（即n_owned不为0的记录）的地址，这些地址所处位置称之为槽，一个槽占用2字节，Infimum最多1条记录一组Supermum最多8条一组，其他的组4–8条一组（9条则分为4+5）

查找过程：对于槽进行二分，然后拿需查找的主键值去进行对比，找到组之后遍历链表。

7.Page Header

存储数据页中已经存储了多少条记录、Free Space在页面中的偏移量、页目录有多少个槽、修改当前页面的最大事务id（仅在二级索引页面中定义）、最后插入记录的位置、记录插入的方向等等

8.File Header（通用部分）

记录页号、前一个页的页号、下一个页的页号、页面最后被修改时的LSN值，页面类型、页属于哪个表空间

9.File Trailer（通用部分）

由八个字节组成，前面四个字节代表页的校验和，后四个字节代表页面被最后修改时对应的LSN的后四字节，应该与File Header中的FIL_PAGE_LSN的后四个字节相同，用于校验

---

第六章B+树索引

1.对于每个页也类比Page Dictionary建立页索引，页索引复用数据页来存储目录项

2.聚簇索引

对于每个完整（包括隐藏列）记录按照主键的升序排列在页中，在增删过程中动态维护这个递增链表，各个叶子数据页双向链表连接。

然后建立目录项数据页，记录目录项记录，每条记录包含对应页的最小主键和页数，然后使用单向链表将其连接，在各个页面间用双向链表连接。

依此往上建立一层一层索引，最后得到B+树结构

3.二级索引

将主键换为自选列，按照聚簇索引一样建立B+树，但是叶子页中的记录只记录自选列和主键值，不存储完整记录。

二级索引搜索方式：从根节点开始采用二分法一直往下搜索，到目标页之后二分找到记录然后根据其对应主键进行回表行为

回表行为：根据主键值在聚簇索引中搜索完整记录。

二级索引的目录项不仅只记录自选列和页号同时还记录主键值，当进行增删时，一旦自选列相同则按照主键大小动态排序

4.联合索引

自选列变为多个列进行索引建立，过程跟二级索引一样，本质上就是二级索引

5.B+树索引建立过程

根页面不动原则，先在根页面填入记录，满了之后复制到一个新页（a），并对新页进行分裂（b），然后把a和b的对应目录项放入根节点

6.一个数据页最少存放2条记录就是因为索引

7.MyISAM索引

所有记录存放在一个文件（数据文件）中，将其主键和行号存放在另一个文件（索引文件），搜索时找索引，再从索引找行号，也可以建立二级索引，只不过把主键变为自选列，跟InnoDB一样。

8.MyISAM中“索引是索引，数据是数据”，InnoDB中“索引即数据，数据即索引”

9.InnoDB和MyISAM自动为主键或带有UNION属性的列创建索引

---

第七章B+树索引的使用

1.在选择索引进行搜索时，一定要考虑其生成的扫描区间，选择扫描区间最小的索引方式进行搜索

2.选择搜索区间的方法：先选择一个索引，不能形成合适扫描区间的搜索条件换位TRUE，然后化简得到扫描区间，如此选择后，再选出最小的扫描区间，使用其对应的索引。当最后区间为全部时，选择聚簇索引全表扫描，减少回表操作

3.对于联合索引（index_key1_key2_key3）

SELECT * FROM DB WHERE KEY1<=‘C’ AND KEY2 >5这种情况，只有key1=C时能够对于KEY2使用索引，因为只有此时排列是按KEY2排序的

4.不能使用索引排序的情况

1）ASC、DESC混用

2）排序列包含非同一索引的列(GROUP BY KEY1 ID)

3）排序列是某个联合索引的索引列，但是在索引列中不连续（GROUP BY KEY1 AND KEY3）

4)用来形成扫描区间的索引列与排序列不同（SELECT * FROM DB WHERE KEY1 = ‘A’ GROUP BY UK_KEY2 LIMIT 10）

5)排序列不是以单独列名出现在GROUP BY子句中（GROUP BY UPPER（KEY1））

5.回表代价很大，在执行回表操作较少时倾向于二级索引+回表，否则倾向于全局扫描

6.只为用于搜索、排序、分组的列创建索引

7.考虑索引列中不重复值的个数

8.索引列的类型尽量小（例：主键用BIG INT比用INT开销大很多，因为每个索引都会携带主键）

9.为列前缀创建索引（字符串很长的时候可以只以前面一部分字符创建索引）

10.覆盖索引（在查询时尽量只含有索引列而不是简单用*代替（业务需求除外））

11.让索引列以列名的形式在搜索条件中单独出现（where key1 < 4/2,而不是where key1*2 < 4）

12.新插入记录时主键大小对效率有影响（主键最好单调增1，有间隔的话在中间插入会造成分表，造成性能损耗）

13.避免冗余（有index_key1_key2_key3就没必要有index_key1了）和重复索引

---

第八章MySQL的数据目录

1.查看数据目录

SHOW VARIABLES LIKE “datadir”

2.创建一个数据库时，MySQL会在数据目录下创建一个跟数据库同名的子目录，并在此目录中创建一个db.opt的文件，包含数据库的一些属性

3.数据库中的表创建时会在对应的数据库目录之下建立一个 表名.frm描述表结构

4.InnoDB存储数据表，在数据库目录中创建ibdatal文件作为系统表空间，5.6.6以后会在对应表目录下创建独立表空间即——表名.idb文件

5.MyISAM建表时会在对应数据库目录下创建三个文件：表名.frm(数据表属性信息)、表名.MYD(数据文件)、表名.MYI(索引文件)

6.数据目录下还有其他文件

服务器进程文件：每个服务器意味着一个服务器ID，存在此文件

服务器日志文件、SSL和RSA证书与密钥文件

7.文件系统对数据库的影响

数据库名称和表名不得超过文件系统所允许的最大长度

特殊字符会被映射成编码值

文件长度受文件系统最大长度的限制

8.系统数据库

mysql：非常重要，存储MySQL用户账户和权限信息等等

information_schema：保存MySQL服务器维护的所有其他数据库信息，也称元数据

performance_schema：保存MySQL服务器运行过程中的状态信息

sys：通过视图模式结合information_schema和performance_schema数据库，方便开发人员了解MySQL性能信息、

---

第九章 InnoDB的表空间

1.File Header 和 File Trailer为通用部分

2.64页分为一个区，256个区作为一个组，独立表空间第一个组的前三个页面固定：FSP_HDR（记录整个表空间的一些整体属性以及本组的所有的区对应的XDES entry）、IBUF_BITMAP（用来存储Change Buffer）、INODE（存储很多称为INODE entry的数据结构）

3.其余每组的前两个页面也是固定的：XDES（登记本组256区的属性）、IBUF_BITMAP

4.InnoDB引擎生成一个索引会生成两个段——叶子节点段和非叶子节点段

5.分配策略：刚开始时先从碎片区进行分配，32个碎片区页面之后申请完整的区

6.区分四类：FREE、FREE_FRAG、FULL_FRAG、FSEG,每个区对应一个XDES entry

XDES entry包含：Segment ID（12字节，表示该区被分给哪个段，没分则无意义），List Node（8字节，指向前面和后面XDES entry地址的两个指针），State（4字节，表明区状态即是哪类区），Page State Bitmap（16字节，每两位对应一页，第一位表示页是否空闲，第二位表示还没用到）

7.FREE、FREE_FRAG、FULL_FRAG由XDES entry连成的链表直属于表空间，每个段会为其归属下的FSEG区维护三个链表：FREE（全页面空闲）、NOT_FULL（部分页面空闲）、FULL（全满页面）链表，这些所有的链表每个链表对应一个List Base Node结构（存储对应链表的头尾指针和链表节点数）

8.段的结构——每个段都有一个INODE entry

Segment ID：段号

NOT_FULL_N_USED：NOT_FULL链表中使用页面的个数

3个List Base Node

Magic Number：用来标记该INODE entry是否被初始化，是——97937874，否则没有

Framework Array Entry：每个此结构对应一个零散页面

9.FSP_HDR页面

1）File Header

2）File Space Header：

Space ID:表空间ID

Not used：未使用

Size：表空间的页面数

FREE Limit：尚未被初始化的最小页号

Space Flags：表空间的一些属性由位图表示

FRAG_N_USED：FREE_FRAG链表中已经使用的页面数目

List Base Node for FREE List 、 List Base Node for FREE_FRAG List、 List Base Node for FULL_FRAG List ：三个直属列表的基节点

Next Unused Segment ID：下个未使用的段ID

List Base Node for SEG_INODES-FULL List List Base Node for SEG_INODES-FREE List ：INODE页对应链表的基节点，INODE页面会被分为两类：已经满了的（FULL）和还未满的（FREE）

3）File Trailer

10.XDES页面：基本跟FSD_HDR相同，没有File Space Header部分

11.IBUF_BITMAP

12.INODE页面

除了都有的file header和file trailer还有List Node for INODE Page List（记录前后INODE页面的指针）、INODE entry （85个）、Empty Space

13.索引的根页面存储着Segment Header结构（这个结构能够找到索引所对应的段）

14.系统表空间与独立表空间有个非常明显的不同之处——系统表空间的开头具有很多记录整个系统属性的页面，系统表空间的表空间ID为0

15.SYS_TABLES、SYS_COLUMNS、SYS_INDEXES、SYS_FIELDS四个表非常重要，称之为基本系统表，系统表空间的第七个页面记录了数据字典的头部信息

---

第十章 单表访问方法

1.const访问方法：当条件与索引进行等值比较时（当索引由多个列组成时，则需条件列全部一一等值），且保证最多只有一条命中

2.ref访问方法：搜索条件为二级索引列与常数进行等值比较，形成扫描区间为单点扫描区间，采用二级索引来执行查询

3.ref_or_null访问方法：不仅找二级索引列的值等于常数且值等于NULL（值为NULL会被放在索引最左边）

SELECT * FROM TABLE WHERE KEY1 = ‘A’ OR KEY1 = NULL

4.range访问方法：对应扫描区间为若干个单点区间或者范围扫描

5.index访问方法：SELECT KEY1,KEY2,KEY3 FROM TABLE WHERE KEY2 = ‘ABC’ 或者 ORDER BY 主键

6.all访问方法：全表扫描

7.MRR多范围读取

8.intersection索引合并（对应的索引列找出的结果所得主键需要是连续递增的）

SELECT * FROM TABLE WHERE KEY1 = ‘A’ AND KEY2 = ‘B’

先对key1索引找到合适的然后在key2索引找到合适的，对于索引的对应主键取交集

9.Union索引合并（对应结果的主键也必须时连续递增的）

SELECT * FROM TABLE WHERE KEY1 = ‘A’ OR KEY2 = ‘B’

SELECT * FROM TABLE WHERE KEY1 > ‘A’ AND KEY2 = ‘B’——不行

10.Sort-Union索引合并

将结果集取出后按照主键排序然后按照Union索引合并方法合并

---

第十一章 连接的原理

1.两表连接的过程

首先确定驱动表，从驱动表中得到一条记录然后到表二中取匹配（按照匹配条件【ON或者WHERE】）

2.内连接和外连接

内连接：不匹配的记录不加入结果

外连接：分为左外和右外（以哪个为驱动表），驱动表会全部显示，无法匹配项均以NULL填充　SELECT * FROM TABLE1 LEFT/RIGHT JOIN TABLE2 ON CONDITION WHERE CONDITION

3.嵌套循环连接

被驱动表会被访问很多遍（选取驱动表，然后根据条件过滤条件，选取代价最低的单表访问方法，然后拿着结果去被驱动表查找匹配结果），在被驱动表被访问时同样可以使用索引来加快查询

4.基于块的嵌套循环连接

从驱动表中选出一个结果集存储在join buffer中批量与被驱动表去匹配

---

第十二章 基于成本的优化

1.成本分为两部分：I/O成本（从磁盘加载到内存损耗的时间，我们默认成本为1.0，一般指加载一个页面），CPU成本（每一条数据的读取和检测是否符合条件，进行排序损耗的时间，默认成本为0.2）

2.基于成本的优化步骤

1）找出所有可能使用索引

2）计算全表扫描代价：SHOW TABLE STATUS 查看表的统计数据

3）计算使用不同索引执行查询的代价：在二级索引查询中每个扫描区间算1.0成本，一次回表操作认为是1.0成本

例子 I/O成本：1.0（扫描区间）+95*1.0（回表成本）+1.0（微调值）

CPU成本：95 * 0.2（读取二级索引数据成本） +0.01 +95 * 0.2（读取聚簇索引数据成本）

还得考虑能否使用索引合并

4）对比执行计划的代价，选取成本最低的那个

3.索引统计数据属性：SHOW INDEX FROM TABLE

Table：所属表名

Non_unique：是否为唯一索引，聚簇和唯一二级索引——0，普通二级索引——1

Key_name：列所属索引名字

Seq_in_index：列在所属索引的位置

Column_name：列名字

Collation：索引是否升序存放，是——A，否——NULL

Cardinality：索引中不重复的值的数量（估算值）

index_key1_key2_key3：对于key2列表示key1_key2的不同值数量，对于key3列表示key1_key2_key3的不同值数量

Sub_part：前缀多少作为索引，完整列则存NULL

Packed：是否被压缩，NULL为未被压缩

Null：是否可以为NULL

Index_type：索引存储类型，通常为BTREE——B+树

Comment：列所属索引的一些额外信息

Index_comment：创建索引时写的一些注释

4.连接查询的成本

1）条件过滤（大致进行一个范围的猜测确认——启发式规则）

对于两表查询来说成本由两部分组成：驱动表的查询（单表查询）生成扇出，被驱动表多次查询所产生的成本

2）两表成本的成本分析 总成本 = 驱动表查询成本 + 驱动表查询扇出结果 * 访问被驱动表的成本

优化重点：尽量减少驱动表的扇出结果，访问被驱动表的成本尽量低

5.多表连接的成本分析

1）维护一个成本全局最小值，计算出的成本超过就放弃此方法

2）系统变量optimizer_serch_depth，设置最大连接表数，不超过就穷举，超过就只按这个数量来穷举

3）启发式规则

6.调用成本常数

1）mysql.server_cost表——CPU成本(具体哪些查文档或本书----P207)

cost_name——成本函数名称；cost_value——对应值，NULL表示默认；last_updata——最后更新时间；comment——注释

更新方法：UPDATA mysql.server_cost SET cost_value = 值 WHERE cost_name

FLUSH OPTIMIZER_COSTS

2）mysql.engine_cost表——I/O成本(具体哪些查文档或本书----P208)

---

第十三章 InnoDB统计数据是如何收集的

1.统计数据有存储在内存(重启消失)也有存储在磁盘(重启不影响),可以修改innodb_stats_persistent系统变量来控制存储位置,5.6.6以后默认存储在磁盘

2.统计数据存储在mysql系统数据库的innodb_index_stats(索引统计数据)和innodb_table_stats(表的统计数据)表中

3.innodb_table_stats表（主键——database_name,table_name）

database_name:数据库名字

tabel_name:表名

last_updata:最后更新时间

n_rows:有多少行数据

n_rows求法:随机选几个页求平均行数,乘以总页面数

clustered_index_size:聚簇索引占用的页面数量

sum_of_other_index_size:其他所有占用的页面数量

clustered_index_size和sum_ofother_index_size求法:从系统表SYS_INDEX中找到索引根节点,然后读取Segment Header,找到其对应的两个INODE entry,通过INODE entry去读取其所属的三个区链表基节点中的list_lenth读出有多少区 * 64求出页数加上携带的零散页面数

4.innodb_index_stats表（主键——database_name,table_name,index_name）

database_name，tabel_name，index_name，last_updata

stat_name：统计项名称

n_leaf_pages：叶子节点实际占据多少页面

size：该索引共占用多少页面

n_diff_pfxNN：表示对应索引列不重复值的数量（例：n_diff_pfx03——key1_key2_key3的不重复值数量）

stat_value：对应统计项的值

sample_size：采样的页面数量

stat_description：对应的统计项描述

5.innodb_stats_method可以设定对于NULL的处理方式：nulls_equal——所有NULL相等，nulls_unequal——所有NULL都互不相同，nulls_ignored——所有NULL被忽略

---

第十四章 基于规则的优化（内含子查询优化二三事）

1.条件化简

1）移除不必要的括号

2）常量传递

3）移除无用条件

5！=6这种。。。

4）表达式计算

abs(a)>5和-a<-8这种不会被优化

5）having和where子句合并

6）常量表检测——表中无数据或者仅有一条数据；使用主键等值匹配或者唯一二级索引列等值匹配

2.外连接消除

在外连接中指定被驱动表列不为NULL值的条件称之为空值拒绝，此时内外连接可以转换

SELECT * FROM table1 LEFT JOIN table2 ON table1.a = table2.a WHERE table2.b IS NOT NULL 等价于

SELECT * FROM table1 INNER JOIN table2 ON table1.a = table2.a

3.子查询优化

子查询按返回结果集分类：标量子查询（1 * 1）、行子查询（1 * n）、列子查询（n * 1）、表子查询（n * n）

子查询按外层查询的关系分类（是否依赖外层查询）：不相关子查询、相关子查询

子查询语法注意事项

子查询必须用括号括起来

在SELECT子句中的子查询必须是标量子查询

对于IN/ANY/SOME/ALL子查询来说，子查询中不允许有LIMIT语句

不允许在一条查询语句增删改某个表还对该表子查询

4.子查询在MySQL中执行方法

标量子查询和行子查询

不相关——单独执行子查询，然后将子查询结果作为外层查询参数

相关——1、从外层获取一条2、找出子查询中涉及的值3、根据子查询结果来检测外层条件，成立放到结果集，反之丢弃

IN子查询优化

1）物化表：把子查询单独存储的一个临时表，临时表会去重，一般使用MEMORY引擎存储在内存并建立哈希表

2）物化（materialize）：将子查询的结果集中的记录保存到临时表的过程

3）物化表转连接：例如：SELECT * FROM table1 WHERE key1 IN （SELECT common_column FROM table2 WHERE key3 = ‘a’）；

等价于：SELECT * FROM table1 INNER JOIN materialized_table ON key1 = m_val ；

4）子查询转换为半连接

半连接：对于表1中的某条记录来说，我们只关心在表2中是否存在与之匹配的记录，而不关心有多少条与之匹配，最终的结果集中只保留表1的记录

Table pullout（子查询中的表上拉）：当子查询的查询列表处只有主键或者唯一索引列时，把子查询中的表上拉到外层查询的FROM子句中

Duplicate Weedout（重复值消除）：转换为半连接查询后，建立一个临时表（作为去重集合set）把每个结果插入，重复则丢弃

Loose Scan（松散扫描）：在子查询中，对于表2的访问可以使用到表1的key1列的索引，而子查询的查询列就是表1的key1

Semi-join Materialization（半连接物化）：之前说的子查询物化然后连接

FirstMatch（首次匹配）：1、从外层获取一条2、找出子查询中涉及的值3、根据子查询结果来检测外层条件，成立放到结果集，反之丢弃

5.半连接的适用条件

1）子查询必须是跟IN操作符组成的布尔表达式且在外层查询的where或on中出现

2）外层查询条件必须使用AND连接

3）子查询必须是单一查询，不能是由UNION连接起来的若干个查询

4）子查询不能包含GROUP BY、HAVING或者聚集函数

6.半连接的不适用条件

1）外层搜索条件使用OR连接

2）使用NOT IN而不是IN

3）位于SELECT子句中的IN子查询的情况：SELECT key1 IN（。。。） FROM table1；

4）子查询中包含GROUP BY、HAVING或者聚集函数

5）子查询中包含UNION

7.对于不能转换的子查询优化

1）不相关子查询，将其物化再参与查询

2）尝试将IN转换为EXISTS子查询

8.ANY/ALL子查询优化：变成最大最小

9.对于派生表（子查询放在FROM 后面）的优化

1）派生表物化

2）派生表与外层查询合并

---

第十五章 EXPLAN详解

1.EXPLAIN各列

id：对于每个SELECT关键字的唯一id值

当有子查询时，优化器如果对于子查询进行了优化进行了半连接则子查询的select的id会被变为跟外层一样

对于包含UNIOＮ子句的查询语句中会包含一个特殊记录

id——NULL：表示创立了一个临时表用于去重，UNION ALL则没有

select_type：小查询在整个大查询中扮演什么角色

SIMPLE：连接查询、不包含UNION或者子查询都是

PRIMARY：对于包含UNION、UNION ALL或者子查询的大查询来说，最左边那个就是PRIMARY

UNION：UNION、UNION ALL连接的那个查询

UNION RESULT：UNION临时表用来去重的

SUBQUERY：不能转换为半连接，且最终决定用物化表的形式的子查询

DEPENDENT SUBQUERY：不能转换为半连接，最后被转化为相关子查询形式的子查询

DEPENDENT UNION：相对于UNION来说，这种的UNION都依赖于PRIMARY

DERIVED：物化的派生表

MATERIALIZED：子查询物化后跟外层查询连接

UNCACHEABLE SUBQUERY和UNCACHEABLE UNION：不常用

table：代表该记录对应访问的单表表名

partitions：NULL

type：对某个表采用的访问方法

system：表中只有一条记录且该表使用的引擎的统计数据是精确的（MyISAM、MEMORY）

const、eq_ref、ref、ref_or_null、index_merge、range、index：见前文

fulltext：全文索引

unique_subquery：类似于eq_ref只是针对子查询

index_subquery：类似于unique_subquery只是用到的是普通索引

all：全表扫描

possible_keys：可能用到的索引

key：实际使用的索引

key_len：该列实际数据最多占用的存储空间长度，如果可以存储NULL则加一字节，如果是变长则加二字节

ref：当type是const/ref/eq_ref/ref_or_null/unique_subquery/index_subquery之一时，展示与索引列匹配的东西

rows：扫描预估行数

filtered：对于扇出集来说有百分之多少是符合条件的

Exter：额外信息，参考官方文档

2.GROUP BY语句默认会ORDER BY

3.JSON格式的执行计划

在EXPLAIN和语句中间加入 FORMAT=JSON（例：EXPLAIN FORMAT=JSON SELECT * FROM table1 INNER JOIN table2 ON table1.key1 = table2.key2 WHERE table1.column = ‘a’）

4.在EXPLAN之后可以输入 SHOW WARNINGS来查看额外的更多信息，其中的message一行表示查询优化器将查询语句重写后的语句

---

第十六章 optimizer trace的神奇功效

1.optimizer_trace系统变量，查看优化器生成执行计划的整个过程，默认关闭

2.information_schema系统数据库下的optimizer_trace表中存放着完整的执行计划生成过程，有四列

QUERY：查询语句

TRACE：优化过程的JSON文本格式

MISSING_BYTES_BEYOND_MAX_MEM_SIZE：超出限制被忽略的文本字节数

INSUFFICIENT_PRIVILEGES：是否有权查看生成过程默认0表示有

3.整个执行计划分为三个阶段：prepare阶段、optimizer阶段、execute阶段

对于单表查询更多关注rows_estimation过程，多表连接更多关注considered_execution_plans过程

---

第十七章 InnoDB的Buffer Pool

1.在MySQL启动时自动在内存分配了一片区域——Buffer Pool，此区域也按页划分

2.Buffer Pool的前面存放着缓存页的控制信息，而我们申请分配的大小是不包含控制信息所占空间的，控制信息一般为缓存页的百分之五大小，所以实际内存大小为申请大小的105%

3.Buffer Pool的空闲缓冲页控制信息也连成双向链表（free），然后单独申请一小块地址空间存放此链表的基节点

4.Buffer Pool会建立哈希来存储已经缓存的页

5.当我们修改Buffer Pool中的页面后造成内存跟磁盘数据不一致的页面称之为脏页，会为此维护一个flush链表

6.LRU链表：新来缓存页的控制信息入链表头，已经在的缓存页被访问则移动至链表头。

7.但是当加载到Buffer Pool中的页不一定用得到（预读）或者非常多的使用频率低的页面加载到Buffer Pool中，很可能把高频的刷掉（全表扫描），我们选择的处理方法是将LRU链表分为两部分，冷数据old区（频率低）和热数据young区（频率高），innodb_old_blocks_pct系统变量确定比例。

8.优化方案：当第一次加入到Buffer Pool中的数据先放入old区，在对于某个位于old区的数据访问时记下时间，如果在之后的访问与第一次访问的时间间隔（系统变量innodb_old_blocks_time）大于某个值则将其移入young区头部（对于一个页面的一个数据读取一次算作访问页面一次）

9.Buffer Pool中还有很多其他的链表

10.LRU优化策略：对于young区当数据位于头1/4之后时，才进行移动到头部的行为

11.刷新数据到磁盘

1）从LRU链表的old区刷新一部分页面到磁盘

2）从flush链表中刷新一部分页面到磁盘

12.对于多线程高并发的情况下可以将特别大的Buffer Pool分成一个个小的Buffer Pool

（启动时设置innodb_buffer_pool_instances可以设定个数）当Buffer Pool小于1G时，只能有一个

13.Buffer Pool会被分成多个chunk（innodb_buffer_pool_chunk_size）

14.配置Buffer Pool注意事项

1）innodb_buffer_pool_size（a）必须是innodb_buffer_pool_chunk_size（b） * innodb_buffer_pool_instances（c）的整数倍，如果我们指定不是，系统会自动取成整数倍

2）当b*c大于a时，将b设置为a/c；

15.SHOW ENGINE INNODB STATS 查看引擎状态，其中包含Buffer Pool

---

第十八章 事务简介

1.原子性Ａ

2.隔离性Ｉ

3.一致性Ｃ

CHECK语法能够自定义约束

4.持久性Ｄ

5.需要保证AICD性质的操作称之为事务，事务的状态

1）活动的：事务正在执行

２）部分提交的：操作完成但是只是在内存中

３）失败的：操作中途出错或者手动中止，或者刷新到磁盘出错

４）中止的：执行出错并且已经回滚

５）提交的：执行成功并且已经刷新到磁盘中

6.只有中止和提交状态才算一个完整的操作生命周期

7.START TRANSACTION也可以开启事务,后面还可以增加修饰语句（可以带多个修饰语句）默认情况是读写模式

READ ONLY：只读事务

READ WRITE：读写事务

WITH CONSISTENT SNAPSHOT：启动一致性读

8.autocommit系统变量设置自动事务提交，默认on，每条语句为单独的一个事务

9.隐式提交

1）定义或修改数据库对象的数据定义语言（DDL）

2）隐式使用或者修改mysql数据库中的表

3）事务控制或关于锁定的语句

BEGIN;…(语句) BEGIN(第二个BEGIN);

4)加载数据的语句

5）关于MySQL复制的一些语句

10.事务保存点

SAVEPOINT 保存点名字

ROLLBACK TO 保存点名字

RELEASE SAVEPOINT 保存点名字（删除保存点）

---

第十九章 redo日志

1.redo日志的本质就是记录了事务对于数据库进行了哪些修改

2.redo日志格式

type：类型

space ID：表空间ID

page number：页号

data：这条redo日志的具体内容

3.row_id的赋值

1）服务器维护一个全局变量，每当向含有此列的表加记录时，该全局变量加一

2）当该全局变量变为256的倍数时，刷新到系统表空间的页号7的页面中的Max Row ID属性中

3）当系统启动时，会将Max Row ID加载到内存中，并将值加256后赋值给全局变量

4.物理redo日志

1）MLOG_1BYTE、MLOG_2BYTE、MLOG_4BYTE、MLOG_8BYTE、MLOG_WRITE_STRING：type字段分别为1、2、4、8、30，表示在某个偏移量写入大小1、2、4、8字节或者不确定长度的redo日志或一个字节序列

对应结构： type——space ID——page number——offset——（len）——具体数据

5.还有复杂的redo日志格式见书（P311-314），其实只需要明白redo日志会把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统因为崩溃而重启后可以把事务所做的任何修改都恢复过来

6.redo日志以组的形式写入：例如向聚簇索引插入为一组，向二级索引插入为一组，更新Max Row ID属性的为一组

7.插入类型分为两种：乐观插入和悲观插入

乐观插入：插入的叶子节点页面还有空间，直接进行插入

悲观插入：叶子节点没有空间了，需要分裂出新的空间，并相对应的更新其非叶子节点的目录项记录，对于悲观插入必须保证其原子性，如果插入过程中的更改只发生了一部分，那么这个插入是不完整的，所以对此分了两种情况。

1）当需要保证原子性的redo日志有多条时，在最后一条日志的后面加入一个特殊的redo日志：只有type的redo日志，type设为MLOG_MULTI_REC_END

2）当需要保证原子性的redo日志只有一条时，因为type占据一字节，而redo类型就几十种，所以第一位是可以空出来的，所以规定当第一位为1，表示需要保证原子性的redo日志只有这一条

8.Mini-Transaction：对底层页面进行一次原子访问的过程称之为Mini-Transaction（MTR）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-30apOdx6-1648452513793)(C:\Users\32088\AppData\Roaming\Typora\typora-user-images\image-20220322161437951.png)]

9.redo日志存储在512字节的redo log block（相当于数据中的页）中

结构： log block header——log block body——log block trailer

log block header：

LOG_BLOCK_HDR_NO:每个block都有个编号

LOG_BIOCK_HDR_DATA_LEN:表示body中使用了多少字节

LOG_BLOCK_FIRST_REC_GROUP:一个MTR对应redo组的第一条日志的偏移量

LOG_BLOCK_CHECKPOINT_NO:checkpoint的序号

10.redo日志也有redo log buffer（相当于Buffer Pool），被划分成很多log block

11.log buffer中有个全局变量buf_free记录着下一个redo日志写入位置

12.redo日志的刷新机制

1）log buffer空间达到一半的时候，刷新到磁盘

2）事务提交时

3）后台存在一个线程按一定频率刷新redo日志

4）正常关闭服务器

5）做checkpoint时

13.redo日志的文件组

innodb_log_group_home_dir:指定存储位置的启动项

innodb_log_file_size:指定每个redo文件大小的启动项

innodb_log_file_in_group:指定有多少个文件的启动项

14.redo日志文件格式

每个文件的前四个block存储管理信息，其余全部存储redo日志

前四个block的格式：log file header——checkpoint1——无用空间——checkpoint2

log file header

LOG_HEADER_FORMAT:redo日志版本

LOG_HEADER_PAD1:无意义

LOG_HEADER_START_LSN:偏移量为2048字节处对应的LSN

LOG_BLOCK_CHECKSUM:本block的校验和，所有block都有

checkpoint1

LOG_CHECKPOINT_NO:执行checkpoint的编号

LOG_CHECKPOINT_LSN:结束checkpoint时对应的LSN；系统崩溃后从此值开始

LOG_CHECKPOINT_OFFSET:上个属性的LSN在redo日志文件组中的偏移量

LOG_CHECKPOINT_LOG_BUF_SIZE:服务器在执行checkpoint操作时对应的log buffer的大小

LOG_BLOCK_CHECKSUM:本block的校验和

checkpoint2同checkpoint1

15.LSN全局变量记录当前总共写入的redo日志大小，规定第一个LSN为8704，每组MTR都有一个唯一的LSN与其对应，对应的LSN越小，表示写入得越早

16.buf_next_to_write和flushed_to_disk_lsn全局变量表示哪些日志已经被刷入磁盘，系统第一次启动时其值同LSN为8704

17.LSN=8704对应偏移量2048

18.在Buffer Pool中的控制块信息中有两个属性oldest_modification和newest_modification

oldest_modification：第一次修改此页面，将修改此页面的MTR对应的LSN写入

newest_modification：最近一次修改此页面对应MTR的LSN

flush链表中的页面控制信息按照oldest_modification属性从大到小排序，多次修改的数据只更改newest_modification，不更改其在flush链表的位置

19.有一个全局变量checkpoint_lsn表示已经被刷新的页面对应的lsn，我们把checkpoint_lsn一次增加操作称之为执行一次checkpoint，刷新页面到磁盘和checkpoint是两回事，不是说每次进行刷新到磁盘就进行checkpoint

20.执行checkpoint步骤

1）计算可以被覆盖的最大lsn，将系统最早修改的脏页对应的oldest_modification赋值给checkpoint_lsn

2）将checkpoint_lsn与之对应的redo日志文件组偏移量以及此次checkpoint的编号写到日志文件的管理信息中（checkpoint1和checkpoint2），checkpoint_no为偶数写入2，奇数写入1

21.SHOW ENGINE INNODB STATUS\G

log sequence number：lsn

log flushed up to：flushed_to_disk_lsn

pages flushed up to：flush链表中最早被修改的页面对应的oldest_modification

last checkpoint at：当前的checkpoint_lsn

22.innodb_flush_log_at_trx_commit系统变量关于刷新到磁盘和事务的关系，详细自己查

23.选出checkpoint1和checkpoint2中大的那个checkpoint_lsn和checkpoint_offset开始崩溃恢复，一直扫描到log block中log block header部分中LOG_BLOCK_HDR_DATA_LEN属性不为512的页面

24.将redo日志对应的表空间ID和页号做一个哈希表，File Header中FIL_PAGE_LSN大于checkpoint_lsn的就不需要根据lsn小于FIL_PAGE_LSN的redo日志进行恢复了

---

第二十章 undo日志

1.对于只读事务来说，只有第一次对某个临时表进行增删改操作时才分配事务id；对于读写事务来说，只有第一次对某个表进行增删改时才分配事务id

2.事务id的分配方法和row_id差不多

3.记录中的trx_id：对于该记录进行增删改对应的事务id

4.insert操作对应的undo日志——TRX_UNDO_INSERT_REC类型

end of record：下一条undo日志在页面中的地址

undo type：日志类型

undo no：对应的编号

table id：对应记录所在表的table id

主键各列信息：以<len，value>形式保存存储空间大小和真实值

start of record：本条redo日志开始的地址

5.记录中的roll_pointer隐藏列指向对应的redo日志

6.删除一条记录会经历两个过程

1）delete mark阶段：将deleted_flag置为1，但是不移入free链表，事务提交之前一直位于此位置

2）purge阶段：事务提交后，更改页面各项相关数据，将记录移至free链表

7.delete操作对应的undo日志——TRX_UNDO_DEL_MARK_REC类型

end of record：下一条undo日志在页面中的地址

undo type：日志类型

undo no：对应的编号

table id：对应记录所在表的table id

info bits：记录头信息的前四个比特的值

trx_id：旧记录对应的trx_id

roll_pointer：旧记录对应的roll_pointer

主键各列信息：以<len，value>形式保存存储空间大小和真实值

len of index_col_info：下方部分以及本部分占用存储空间大小

索引列各列信息：凡是被索引列的各列信息，以<pos,len,value>的形式保存，pos表示该列在记录中的位置

start of record：本条redo日志开始的地址

8.updata操作对应的undo日志TRX_UNDO_UPD_EXIST_REC类型，分为两种情况——更新主键和不更新主键

end of record：下一条undo日志在页面中的地址

undo type：日志类型

undo no：对应的编号

table id：对应记录所在表的table id

info bits：记录头信息的前四个比特的值

trx_id：旧记录对应的trx_id

roll_pointer：旧记录对应的roll_pointer

主键各列信息：以<len，value>形式保存存储空间大小和真实值

n_updated：有多少个列被更新了

被更新的列更新前信息：以<pos,old_len,old_value>类型存储

len of index_col_info：下方部分以及本部分占用存储空间大小

索引列各列信息：凡是被索引列的各列信息，以<pos,len,value>的形式保存，pos表示该列在记录中的位置

start of record：本条redo日志开始的地址

1）不更新主键

当更改记录的存储空间大小相同则就地更新

当更改记录的存储空间大小不相同则先删除记录（直接放入free链表而不是delete mark）再插入

2）更新主键——记录两条undo日志

将旧记录进行delete mark，记录一条TRX_UNDO_DEL_MARK_REC类型的记录

根据更新后的各列数据创建一条新记录，插入到聚簇索引中，记录一条TRX_UNDO_INSERT_REC类型的记录

9.增删改操作对于二级索引当修改到二级索引时也是先删除对应的二级索引记录然后创建一个再插入

10.FIL_PAGE_UNDO_LOG页面专门用来存储undo日志

File Header——Undo Page Header——存储undo日志——File Trailer

Undo Page Header段

TRX_UNDO_PAGE_TYPE：本页为哪种类型的undo日志，分为两种：TRX_UNDO_INSERT、TRX_UNDO_UPDATE（除了TRX_UNDOINSERT_REC类）

TRX_UNDO_PAGE_START：第一条日志在本页面中的起始偏移量

TRX_UNDO_PAGE_FREE：本页中可以继续写入的位置

TRX_UNDO_PAGE_NODE：链表节点

11.在UNDO链表中第一个页面叫做first undo page 其他叫做normal undo page

12.一个事务最多对应四个链表（普通表的insert 和update链表，临时表的insert和update链表），但是并不是最开始就分配好，当需要之时才分配，每个页面链表都对应着一个段

13.first undo page中在Undo Page Header后还有Undo Log Segment Header段以及Undo Log Header段

Undo Log Segment Header

TRX_UNDO_STATE：本Undo页面链表处于何种状态

TRX_UNDO_ACTIVE：正有一个事务在往里面写undo日志

TRX_UNDO_CACHED：等待之后被其他事务重用

TRX_UNDO_TO_FREE：等待被释放，insert链表对应事务在被提交后不能重用就处于这个状态

TRX_UNDO_TO_PURGE：等待被purge，update链表对应事务在被提交后不能重用就处于这个状态

TRX_UNDO_PREPARED：用于存储于prepare阶段的事务产生的日志

TRX_UNDO_LAST_LOG：本undo页面链表中最后一个Undo Log Header

TRX_UNDO_FSEG_HEADER：本undo页面链表对应的段的segment header信息

TRX_UNDO_PAGE_LIST：页面链表的基节点

14.同一个事务向一个undo页面链表中写入的undo日志算作一个组

Undo Log Header

TRX_UNDO_TRX_ID：生成本组的事务id

TRX_UNDO_TRX_NO：事务提交后的一个序号

TRX_UNDO_DEL_MARKS：是否包含delete mark产生的undo日志

TRX_UNDO_LOG_START：本组中第一条undo日志的偏移量

TRX_UNDO_XID_EXISTS：是否包含XID信息

TRX_UNDO_DICT_TRANS：本组日志是不是由DDL语句（创建、增删改）产生的

TRX_UNDO_TABLE_ID：如果上一条为真记录修改的表id

TRX_UNDO_NEXT_LOG：下一undo日志的偏移量

TRX_UNDO_PREV_LOG：上一日志的偏移量

TRX_UNDO_HISTORY_NODE：history链表的节点

XID信息

15.undo页面链表重用

1）该链表中只包含一个Undo页面

2）该页面使用空间小于3/4

insert类的链表直接覆盖旧的记录写入

update类的链表从旧记录之后写入，先写入一个Undo Log Header再写记录

16.回滚段（Rollback Segment Header）存储了每个Undo链表的第一个页面的页号（undo slot）

File Header——…——File Trailer

TRX_RSEG_MAX_SIZE：管理的所有链表的Undo页面总和的最大值，默认是无限大

TRX_RSEG_HISTORY_SIZE：History链表占用的页面数量

TRX_RSEG_HISTORY：History链表的基节点

TRX_RSEG_FSEG_HEADER：该回滚段对应的Segment Header

TRX_RSEG_UNDO_SLOTS：各个页面链表第一页的页号集合一个页号4字节，共4096字节

无用区域

17.回滚段中分配Undo Slot

1）所有的Undo Slot初始为FIL_NULL，分配之时进行修改

2）当slot满了报错

3）事务提交后，其占用的slot如果满足重用条件则先将其对应页面链表的TRX_UNDO_STATE修改为TRX_UNDO_CACHED再将该slot放入对应的链表中（insert放入insert undo cached链表，update放入update cached链表）每个回滚段都对应这两个链表，当新事物要分配时，优先从这两个链表中分配

4）如果不符合重用先设置其对应的TRX_UNDO_STATE，然后将其值置为FIL_NULL

18.系统空间的第5号页面存储着128个回滚段的表空间id和页号，普通表和临时表的回滚段对应编号不一样

19.聚簇索引中的roll_pointer隐藏列结构

is_insert——rseg id——page number——offset

is_insert：表示是否为插入类型的undo

rseg id：指向undo日志的对应回滚段id

page number：指向undo日志的页号

offset：undo日志在页面中的偏移量

---

第二十一章 事务隔离级别和MVCC

1.脏写：一个事务修改了另一个未提交事务修改过的数据

w1[x]…w2[x]… ((c1 or a1)and(c2 or a2)in any order)

下面两个例子要求x=y一致性

w1[x=1]w2[x=2]w2[y=2]c2w1[y=1]c1 ——破坏一致性原则

w1[x=2]w2[x=3]w2[y=3]c2a1——破坏一致性和原子性

2.脏读：一个事务读到了另一个为提交事务修改过的数据

w1[x]…r2[x]……((c1 or a1)and(c2 or a2)in any order)

w1[x=1]r2[x=1]r2[y=0]c2w1[y=1]c1——破坏一致性

严格解释：w1[x]…r2[x]…(a1 or c2 in any order)

3.不可重复读：一个事务修改了另一个未提交事务读取的数据

r1[x]…w2[x]……((c1 or a1)and(c2 or a2)in any order)

严格解释：r1[x]……w2[x]……c2……r1[x]……c1

4.幻读：一个事务先根据某些搜索条件查询出一些记录，在该事务未提交时，另一事务写入了一些符合搜索条件的记录

r1[P]……w2[y in P]……((c1 or a1)and(c2 or a2)in any order)

严格解释：r1[P]……w2[y in P]……c2…… r1[P]……c1

5.SQL标准中的四个隔离级别

脏读 不可重复读 幻读

READ UNCOMMITTED： 可能 可能 可能

READ COMMITTED： 不可能 可能 可能

REPEATABLE READ： 不可能 不可能 可能————————默认情况

SERIALIZABLE： 不可能 不可能 不可能

脏写情况都不允许发生

6.修改隔离级别

SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别————对应之后新产生的会话，当前会话无效

SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别————对应当前会话该语句之后事务有效，如果在事务内该事务也无效

SET TRANSACTION ISOLATION LEVEL 隔离级别—————对应当前会话的下一个事务，下一个事务执行完后恢复之前的隔离级别，不能在事务中输入该语句

7.MVCC：多版本并发控制，依靠版本链来实现

8.ReadView

m_ids：生成ReadView时，当前系统活跃的读写事务的事务id列表

min_trx_id：m_ids中的最小值

max_trx_id：系统应该分配给下一个事务的事务id值

creator_trx_id：生成该ReadView的事务id

9.依据ReadView判断某个版本是否可见的步骤

1）被访问版本的trx_id与creator_trx_id相同，意味着当前事务在访问它自己修改过的记录，所以可以被访问

2）trx_id小于min_trx_id，说明在此之前已经被提交，所以可以访问

3）trx_id大于或等于max_trx_id，说明修改此记录的事务在此之后才开启，所以不可访问

4）trx_id在min_trx_id和max_trx_id中间，则判断是否在m_ids中，如果在，则不可访问，如果不在则说明已经提交，可以访问

如果不可见，则顺着版本链往下找直到版本链的组后一个版本

（其实各个隔离级别的实现本质就是对于ReadView的创建时间不同来实现的）

10.READ UNCOMMITTED 直接读最新版本就行，SERIALIZABLE 依靠锁实现，READ COMMITTED 每次读取数据之前都生成一个ReadView，REPEATABLE READ在第一次读取数据时生成一个ReadView，详细实例见书P391~396

11.二级索引与MVCC判断可见性分两步

1）先在二级索引的Page Header部分查看PAGE_MAX_TRX_ID如果min_trx_id大于它则所有记录均可见，否则回表指向步骤2

2）找到索引对应的列按照ReadView找到对应的第一个版本

12.当前系统中如果最早生成的ReadView不再访问undo日志以及打了删除标记的记录，则可以通过purge删除

---

第二十二章 锁

1.锁结构比较重要的两项：trx：该锁与哪个事务相关联；is_waiting：当前事务是否在等待

2.避免脏读、不可重复读、幻读有两种解决方案

1）读操作使用MVCC，写操作加锁

2）读写操作都加锁

3.事务利用MVCC进行的读取操作称之为一致性读，一致性读不会对表中的任何记录加锁

4.锁分为两类：共享锁（Ｓ锁）、独占锁（Ｘ锁）

兼容性	Ｘ锁	Ｓ锁
Ｘ锁	不兼容	不兼容
Ｓ锁	不兼容	兼容

5.锁定读

SELECT…… LOCK IN SHARE MODE——加Ｓ锁

SELECT……FOR UPDATE——加Ｘ锁

6.写操作

1）DELETE：先获得X锁然后进行delete mark

2）UPDATE：

未修改键值且原地更新：先找到该记录，然后加上X锁，然后原地更新

未修改键值且非原地更新：先找到记录，然后获得X锁，然后删除，然后新插入记录，新纪录也拥有原X锁

修改键值：相当于进行了一次DELETE和INSERT

3)INSERT：一般情况下使用隐式锁

7.意向锁

IS：当事务准备向某条记录加S锁时，先在表级别加一个IS锁

IX：当事务准备向某条记录加X锁时，先在表级别加一个IX锁

表级别的各种锁的兼容性

兼容性	X	IX	S	IS
X	不兼容	不兼容	不兼容	不兼容
IX	不兼容	兼容	不兼容	兼容
S	不兼容	不兼容	兼容	兼容
IS	不兼容	兼容	兼容	兼容

8.InnoDB还有一个表级别的AUTO-INC锁：对于AUTO_INCREMENT修饰的列进行递增时为表加此锁，然后整个插入语句执行完之后立即释放；还有一种轻量级的锁当该AUTO_INCREMENT列递增完后立即释放，不用等待整个语句

9.常用行级锁的类型

1）Record Lock：单纯为该条记录加锁，有S、X之分

2）Gap Lock：为此记录加锁意味着在此纪录和上条记录之间加锁（不包括该记录和其上一条记录），防止在其中间插入

3）Next-Key Lock：为该记录加锁意味着该记录加上Record Lock和Gap Lock

4）Insert Intention Lock：在gap锁的位置想要插入记录时为gap锁的记录加的锁

5）隐式锁：对于聚簇索引根据trx_id的来判断，其对应事务是否活跃，不活跃正常读取，活跃再帮此事务创建一个X锁，再帮自己创建一个X锁；对于二级索引来说则根据其Page Header中的PAGE_MAX_TRX_ID来判断

10.锁结构

锁所在的事务信息——索引信息——表锁/行锁信息——type_mode——其他信息——一堆比特位

锁所在的事务信息：在内存结构中只是个指针，根据指针指向的位置获得信息

索引信息：同上

表锁/行锁信息：

Space ID：记录所在的表空间

Page Number：记录所在的页号

n_bits：页中一条记录对应一个bit位，该属性表示有多少个bit位

type_mode：32比特数

低四位：lock_mode：十进制的0、1、2、3、4分别表示IS、IX、S、X、AUTO_INC

5~8位：lock_type：第五位：LOCK_TABEL表示表级别第六位LOCK_REC表示行级别

剩余位数：rec_lock_type（行锁具体类型）：十进制的0、512、1024、2048分别表示：next-key锁、gap锁、record锁、Insert intention锁；十进制的256还用来表示is_waiting：1表示true，0表示false

其他信息：设立的一些用来管理的哈希表和链表

一堆比特位：如果是行级锁则有此部分，用来表示哪些记录有锁

11.当不同记录的锁满足下列条件可以放到同一个锁结构中去存储

1）在同一个事务中进行加锁操作

2）被加锁的记录在同一个页面中

3）加锁的类型是一样的

4）等待的状态是一样的

12.普通的SELECT语句加锁分析

1）在READ UNCOMMITTED 隔离级别下直接读最新数据

2）READ COMMITTED、REPEATABLE READ 隔离级别下不加锁但有ReadView

3）SERIALIZABLE：分两种情况

autocommit = 0情况下普通的SELECT语句会被转化为LOCK IN SHARE MODE，具体情况同REPEATABLE READ 一样

autocommit = 1情况下按照每个语句都是一个事务处理

13.锁定读语句加锁分析详细实例见书P426~441

匹配模式：index_a_b索引，

（1）a=1，形成[1，1]范围称之为精确匹配

（2）a = 1 AND b = 1形成扫描区间[（1，1）（1，1）]称之为精确匹配

（3）a = 1 AND b>=1形成扫描区间[（1，1）（1，+∞）]就不是精确匹配

唯一性搜索：满足下面四个条件

1）匹配模式为精确匹配

2）使用索引为主键或者唯一二级

3）如果使用唯一二级索引，搜索条件不能为NULL

3）索引包含多列，生成扫描区间时所有列都要用到

14.半一致性语句：当隔离级别不大于READ COMMITTED且执行UPDATE时执行

当读到被其他事务加上X锁的记录时，取出其最新值与搜索条件匹配，如果不匹配则直接跳过，而不是阻塞等待

15.INSERT语句特殊情况

1）在唯一二级和主键列，遇到重复键时，插入会报错，在生成错误信息之前，给重复键的记录加锁，隔离级别不大于READ COMMITTED 加S型的record锁，不小于REPEATABKLE READ加S型的next-key锁

2）外键检查：当在主表中能找到时直接给主表加上一个S型的record锁；当在主表中找不到的时候，副表插入失败但是会对主表的聚簇索引加锁，不大于READ COMMITTED 不对记录加锁，不小于REPEATABLE READ 加gap锁

16.使用information_schema数据库中的表获取锁信息

1）INNODB_TRX：存储了InnoDB存储引擎当前正在执行的事务信息

2）INNODB_LOCKS：存储了一些锁信息

17.使用SHOW ENGINE INNODB STATUS获取锁信息

k_type（行锁具体类型）：十进制的0、512、1024、2048分别表示：next-key锁、gap锁、record锁、Insert intention锁；十进制的256还用来表示is_waiting：1表示true，0表示false

其他信息：设立的一些用来管理的哈希表和链表

一堆比特位：如果是行级锁则有此部分，用来表示哪些记录有锁

11.当不同记录的锁满足下列条件可以放到同一个锁结构中去存储

1）在同一个事务中进行加锁操作

2）被加锁的记录在同一个页面中

3）加锁的类型是一样的

4）等待的状态是一样的

12.普通的SELECT语句加锁分析

1）在READ UNCOMMITTED 隔离级别下直接读最新数据

2）READ COMMITTED、REPEATABLE READ 隔离级别下不加锁但有ReadView

3）SERIALIZABLE：分两种情况

autocommit = 0情况下普通的SELECT语句会被转化为LOCK IN SHARE MODE，具体情况同REPEATABLE READ 一样

autocommit = 1情况下按照每个语句都是一个事务处理

13.锁定读语句加锁分析详细实例见书P426~441

匹配模式：index_a_b索引，

（1）a=1，形成[1，1]范围称之为精确匹配

（2）a = 1 AND b = 1形成扫描区间[（1，1）（1，1）]称之为精确匹配

（3）a = 1 AND b>=1形成扫描区间[（1，1）（1，+∞）]就不是精确匹配

唯一性搜索：满足下面四个条件

1）匹配模式为精确匹配

2）使用索引为主键或者唯一二级

3）如果使用唯一二级索引，搜索条件不能为NULL

3）索引包含多列，生成扫描区间时所有列都要用到

14.半一致性语句：当隔离级别不大于READ COMMITTED且执行UPDATE时执行

当读到被其他事务加上X锁的记录时，取出其最新值与搜索条件匹配，如果不匹配则直接跳过，而不是阻塞等待

15.INSERT语句特殊情况

1）在唯一二级和主键列，遇到重复键时，插入会报错，在生成错误信息之前，给重复键的记录加锁，隔离级别不大于READ COMMITTED 加S型的record锁，不小于REPEATABKLE READ加S型的next-key锁

2）外键检查：当在主表中能找到时直接给主表加上一个S型的record锁；当在主表中找不到的时候，副表插入失败但是会对主表的聚簇索引加锁，不大于READ COMMITTED 不对记录加锁，不小于REPEATABLE READ 加gap锁

16.使用information_schema数据库中的表获取锁信息

1）INNODB_TRX：存储了InnoDB存储引擎当前正在执行的事务信息

2）INNODB_LOCKS：存储了一些锁信息

17.使用SHOW ENGINE INNODB STATUS获取锁信息