mysql是怎样运行的思维笔记模板_ProcessOn思维导图、流程图

第12章谁最便宜就选谁-基于成本的优化

12.1、执行成本

I/O成本

我们的表经常使用的myisam、innodb存储引擎都是将数据和索引存储到磁盘上。当查询表中的记录时，需要先把数据或者索引加载到内存中，然后再进行操作。这个从磁盘到内存的加载过程损耗的时间称为I/O成本

CPU成本

读取记录以及检测记录是否满足对应的搜索条件、对结果集进行排序等这写操作损耗的时间称为CPU成本

12.2、单表查询成本

12.2.2、基于成本的优化步骤

1、根据搜索条件，找出所有可能使用的索引

2、计算全表扫描的代价

3、计算使用不同索引执行查询的代价

4、对比各种执行方案的代价，找出成本最低的那个方案

查询成本=I/O成本+CPU成本

2、计算全表扫描的代价

show tables status like '表名'

rows

表示表中的记录条数

myisam

该值是准确的

innodb

该值是一个估计值

data_length

表示表占用的存储空间字节数

myisam

该值就是数据文件的大小

innodb

该值相当于聚簇索引占用存储空间的大小

聚簇索引的页面数据 * 每个页面的大小

聚簇索引的页面数量=x * 16 * 1024

12.3、连接查询的成本

12.3.2、条件过滤

对于两表连表查询来说，它的查询成本由两部分构成

单次查询驱动表的成本

多次查询被驱动的成本

12.3.3、两表连接的成本分析

连接查询总成本=单次访问驱动表的成本+驱动表扇出值 * 单次访问被驱动表的成本

优化重点

尽量减少驱动表的扇出

访问被驱动表的成本要尽量低

我们要尽量在被驱动表的连接列上建立索引，这样就可以使用ref访问方法来降低被驱动表的访问成本了

第13章兵马未动，粮草先行-innodb统计数据是如何收集的

13.1、统计数据的存储方式

永久性地存储统计数据

统计数据存储在磁盘上，在服务器重启之后这些统计数据依然存在

非永久性地统计数据

统计数据存储在内存中，当服务器关闭时这些统计数据就会被清除掉。等到服务器重启之后，在某些适当的场景下会重新收集这些统计数据

13.2、基于磁盘的永久性统计数据

show tables from mysql like 'innodb%status'

innodb_tables_stats

存储了关于表的统计数据，每一条记录对应着一个表的统计数据

innodb_index_stats

存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据

13.2.2、innodb_index_stats

select * from mysql.innodb_index_stats where table_name = '表名'

13.2.3、定时更新统计数据

开启innodb_stats_auto_recalc

默认是on

手动调用 ANALYZE TABLE 语句来更新统计信息

ANALYZE TABLE 表名

13.5、总结

innodb以表为单位来收集统计数据

innodb_stats_persistent

控制着服务器使用永久性统计数据还是非永久性统计数据

innodb_stats_persistent_sample_pages

控制着永久性统计数据的采样页面数量

innodb_stats_transient_sample_pages

控制着非永久性统计数据的采样页面数量

innodb_stats_auto_recalc

控制着是否自动重新计算统计数据

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

14.1、条件简化

14.1.1、移除不必要的括号

14.1.2、常量传递

14.1.3、移除没用的条件

14.1.4、表达式计算

14.1.5、HAVING子句和WHERE子句的合并

14.1.6、常量表检测

14.3、子查询优化

14.3.1、子查询语法

1、按返回的结果集区分子查询

标量子查询

只返回一个单值的子查询称为标量子查询

行子查询

返回一条记录的子查询，不过这条记录需要包含多个列

列子查询

查询出一个列的数据，不过这个列的数据需要包含多条记录

表子查询

子查询的结果包含很多条记录，又包含很多列

2、按与外层查询的关系来区分子查询

不相关子查询

如果子查询可以单独运行出结果，而不依赖外层查询的值，我们就可以把这个子查询称为不相关子查询

相关子查询

如果子查询的执行需要依赖与外层查询的值，我们就可以把这个子查询称为相关子查询

3、子查询在布尔表达式中的使用

使用=、>、<、>=、<=、<>、!=、<=>作为布尔表达式

[NOT] IN/ANY /SOME/ALL 子查询

IN或者NOT IN

ANY/ SOME /

ALL

EXISTS

4、子查询语法注意事项

子查询必须用小括号括起来

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

要想得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用limit 1 语句来限制记录数量

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

不允许在一条语句中增删改某个表的记录时，同时还对该表进行子查询

14.3.2、子查询在mysql中是怎么执行的

2、标量子查询、行子查询的执行方式

select * from s1 where key1 = (select common_field from s2 where key3 = 'a' limit 1)

1、单独执行（）里面的子查询

2、然后将子查询的结果当作外层查询的参数，再执行外层查询 select * from s1 where key1= ...

select * from s1 where key1 = (select common_field from s2 where s1.key3 = s2.key3 limit 1)

1、先从外层查询中获取一条记录，在本例中就是先从 s1 表中获取一条记录

2、然后从这条记录中找出子查询中涉及的值。在本例中就是从s1表中获取的那条记录中找出s1.key3列的值，然后执行子查询

3、最后根据子查询的查询结果来检测外层查询where子句的条件是否成立。如果成立，就把外层查询的那记录加入到结果集，否则就丢弃

4、跳到步骤1，知道外层查询中获取不到记录为止

3、IN子查询优化

1、物化表的提出

不直接将相关子查询的结果集当作外层查询的参数，而是将该结果集写入了一个临时表中

该临时表的列就是子查询结果集中的列

写入临时表的记录会被去重

一般情况下，子查询的结果集不会打的离谱，所以会为它建立基于内存的MEMORY存储引擎的临时表，而且会为该表建立哈希索引

in语句的本质就是判断某个操作数是否存在于某个集合中。如果集合中的数据建立了哈希索引，那么这个判断匹配的过程就相当快

如果子查询的结果集非常大，超过了tmp_table_size或则max_heap_table_size的值，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型页相应地转变为b+数索引

将子查询结果集中的记录保存到临时表的过程称为物化。方便起见，我们把那个存储子查询结果集的临时表称为物化表

2、物化表转连接

优化查询器会优先把该子查询转换为半连接，然后再考虑下面5种执行半连接查询的策略中的哪个成本最低

table pullout(子查询中的表上拉)

duplicate weedout（重复值消除）

loosescan（松散扫描）

semi-join materialization（半连接物化）

first match（首次匹配）

如果in子查询不符合转换为半连接的条件，查询优化器会从下面的两种策略中找出一种成本更低的方式执行子查询

先将子查询物化，再执行查询

执行in到exists的转换

mysql在处理带有派生表的语句时，优先尝试把派生表和外层查询进行合并；如果不行，再把派生表物化掉，然后执行查询

第15章查询优化的百科全书-explain详解

explain各项说明

id

在一个大的查询语句时，每个select 关键字都对应一个唯一的id

select_type

select关键字对应的查询的类型

table

表名

partitions

匹配的分区信息

type

针对单表的访问方法

possible_keys

可能用到的索引

key

实际使用到的索引

key_len

实际使用的索引长度

ref

当使用索引列等值查询时，与索引列进行等值匹配的对象信息

rows

预估需要读取的记录条数

filtered

针对预估需要读取的记录，经过搜索条件过滤后剩余记录条数的百分比

extra

一些额外的信息

15.1、执行计划输出中各列详解

15.1.2、id

union

会有临时表

union all

不需要临时表

15.1.3、select_type

SIMLPE

查询语句中不包含UNION或者子查询的查询都算作SIMPLE类型

PRIMARY

对于包含UNION、UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的。其中最左边那个查询select_type值就是PRIMARY

UNION

对于包含UNION或者UNION ALL 的大查询来说，它是由几个小查询组成的；其中除了最左边的那个小查询以外，其余小查询的select_type值就是UNION

UNION RESULT

mysql选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT

SUBQUERY

如果包含子查询的查询语句不能够转为对应的半连接形式，并且该子查询是不相关子查询，而且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY

DEPENDENT SUBQUERY

如果包含子查询的查询语句不能转为对应的半连接形式，并且该子查询被查询优化器转换为相关子查询的形式，则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY

DEPENDENT UNION

在包含UNION或者UNION ALL 的大查询中，如果各个小查值就是DEPENDENT UNION

DERIVED

在包含派生表的查询中，如果是以物化派生表的方式执行查询，则派生表对应的字查询的select_type就是DERIVED

MATERIALLZED

当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询，该子查询对应的select_type属性就是MATERIALLZED

15.1.4、type

system

当表中只有一条记录并且该表使用的存储引擎（比如 myisam、memory）的统计数据时精准的，那么对该表的访问方法就是system

const

当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const

eq_ref

执行连接查询时，如果被驱动表是通过主键或者不允许存储null值的唯一二级索引列等值匹配的方式进行访问的，（如果该主键或者不允许存储null值的唯一二级索引时联合索引，则所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq_ref

ref

当通过普通的二级索引列与常量进行等值匹配的方式来查询某个表时，对该表的访问方法就是ref

fulltext

全文索引，这里不展开讲解

ref_or_null

当对普通二级索引列进行等值匹配并且该索引列的值可以是null值时，对该表的访问方法就是可能是ref_or_null

index_merge

一般情况下只会为单个索引生成扫描区间，但是我们在唠叨单表访问方法时，特意强调了在某写场景下可以使用 Intersection、Union、Sort-Union这3种索引合并的方式来执行查询

unique_subquery

类似于两表连接中被驱动表的eq_ref访问方法，unique_subquery针对的是一些包含IN子查询的查询语句

index_subquery

index_subquery 与unique_subquery类似，只不过在执行查询中的表时使用的是普通索引

range

如果使用索引获取某些单点扫描区间的记录，那么就可能使用到range访问方法

index

当可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index

all

全表扫描

15.1.7、ken_len

该列的实际数据最多占用的存储空间长度

int类型就是4字节

变长类型，如utf8字符集，类型varchar（100）长度就是 3 * 100 = 300字节

如果该列可以存储null，+1字节

对于使用变长类型的列来说，+2字节

15.1.11、extra

No table uesd

当查询语句中没有FROM子句时将会提示该额外信息

Impossible WHERE

查询语句中的where子句永远为false时将会提示该额外信息

No matching min/max row

当查询列表出有MIN或者MAX聚集函数，但是并没有记录符合where子句中的搜索条件时，将会提示该额外信息

Using index

使用覆盖索引执行查询时，extra列将会提示额外信息

Using index condition

有些搜索条件中虽然出现了索引列，但不能充当边界条件来形成扫描区间，也就是不能用来减少需要扫描的记录数量，将会提示该额外信息

Using where

当某个搜索条件需要server层进行判断时，在extra列中会提示Using where

Using join buffer（Block Nested Loop）

在连接查询的执行过程中，当被驱动表不能有效地利用索引加快访问速度时，mysql一般会为其分配一块名为连接缓冲区的内存快来加快查询速度；也就是使用基于快的嵌套循环算法来执行连接查询

Using intersect（...）、Using union(...)、Using sort_union(...)

intersect

准备使用intersection索引合并的方式执行查询

Zero limit

当limit 子句的参数为0时，表示压根不打算从表读出任何记录，此时将会提示该额外信息

Using filesort

当对结果集的记录进行排序时

Using temporary

临时表

15.2、json格式的执行计划

explain FORMAT=JSON

第16章神兵利器-optimizer trace 的神奇功效

1、简介

show variables like 'optimizer_trace'

默认是关闭的

set optimizer_trace='enable=on'

select * from information_schema.OPTIMIZER_TRACE;

2、具体工作过程

prepare阶段

optimize阶段

execute阶段

第17章调节磁盘和cpu的矛盾-innodb的buffer pool

17.2、innodb的buffer pool

17.2.1、啥事buffer pool

在mysql服务器启动是就向操作系统申请了一片连续的内存，就是buffer pool（缓冲池）

默认128M

修改配置

[server] innodb_buffer_pool_size = xxx

单位字节

17.2.2、buffer pool内部组成

页默认16KB

缓冲页

17.2.3、free链表的管理

所有空闲的缓冲页对应的控制快作为一个节点放到一个链表中，这个链表也可以称为free链表

17.2.4、缓冲页的哈希处理

表空间号+页号作为key，用缓冲页控制块的地址作为value来创建一个哈希表

17.2.5、flush链表的管理

如果我们修改了buffer pool中某个缓冲页的数据，它就与磁盘上的页不一致了，这样的缓冲页也称为脏页

每次修改完缓冲页后，我们并不着急立即把修改刷新到磁盘上，而是在未来的某个时间点进行刷新

凡是被修改过的缓冲页对应的控制快都会作为一个节点加入到这个链表中。因为这个链表节点对应的缓冲页都是需要被刷新到磁盘上的，所以也称为flush链表

17.2.6、LRU链表的管理

1、缓冲区不够的窘境

free链表没有多余的空闲缓冲页，需要把旧的缓冲页从buffer pool 中移除

2、简单的LRU链表

淘汰掉最近最少使用的部分缓冲页

3、划分区域的LRU链表

问题

加载到buffer pool中的页不一定被用到

如果有非常多的使用频率偏低的页被同时加载到buffer pool，则会把那些使用频率非常高的页从buffer pool 中淘汰

解决

一部分存储使用频率非常高的缓冲页；这一部分链表也称为热数据，或者称为young区域

另一部分存储使用频率不是很高的缓冲页；这一部分链表也称为冷数据，或者称为old区域

默认占 3/8

修改old区域占LRU链表比例的

[server] innodb_old_blocks_pct = 40

40%

set global innodb_old_blocks_pct = 40

4、更近一步优化LRU链表

只有被访问的缓冲页位于young区域1/4的后面时，才会被移动到LRU链表头部

17.2.7、其他的一些链表

管理解压的unzip LRU

管理压缩页的zip clean 链表

17.2.8、刷新脏页到磁盘

刷新方式

从LRU链表的冷数据中刷新一部分页面到磁盘

从flush链表中刷新一部分到磁盘

17.2.12、查看buffer pool的状态信息

show engine innodb status

total memory allocated

代表buffer pool 向操作系统申请的连续内存空间大小，包括全部控制快、缓冲页，以及碎片大小

dictionary memory allocated

为数据字典信息分配的内存空间大小。和buffer pool 没有关系

buffer pool size

可以容纳多少缓冲页，注意单位是页

free buffers

还有多少空闲缓冲页

17.3、总结

磁盘太慢，用内存作为缓冲区很有必要

buffer pool 本质上是 innodb 向操作系统申请一段连续的内存空间。可以通过innodb_buffer_pool_size来调整它的大小

LRU链表分为young区域和old区域，在buffer pool中没有可用的空闲缓冲页时，会首先淘汰掉old区域汇中的一些页

第18章从猫爷借钱说起-事务简介

事务特性

原子性

隔离性

一致性

持久性

18.3、mysql中事务的语法

开启事务

begin

start transaction

提交事务

commit

手动中止事务

rollback

保存点

savepoint 保存点名称

rollback to 保存点名称

第19章说过的话就一定要做到-redo日志

19.2、redo日志是啥

因为在系统因崩溃而重启需要按照上述内容所记录的步骤重新更新数据页，所以上述内容也称为重做日志（redo log）

redo日志占用的空间非常小

redo日志是顺序写入磁盘的

19.3、redo日志格式

type

这条redo日志的类型

53种类型

space id

表空间ID

page number

页号

data

这条redo日志的具体内容

19.5、redo 日志的写入过程

通过MTR生成的redo日志都放在了大小为512个字节的页中

存储redo日志的页称为block

真正的redo日志都存储到占用496字节的log block body中

redo log buffer（redo 日志缓冲区）

19.6、redo 日志文件

19.6.1、redo日志刷盘时机

log buffer 空间不足时

50%

事务提交时

redo日志刷盘

后台有一个线程，大约每秒一次的频率将log buffer 中的redo日志刷新到磁盘

正常关闭服务器时

做checkpoint时

19.6.2、redo日志文件组

ib_logfile0

ib_logfile1

innodb_log_group_home_dir

指定来redo日志文件的目录，默认值就是当前的数据目录

innodb_log_file_size

指定来每个redo日志文件的大小， 5.7.22版本默认48M

innodb_log_files_in_group

指定来redo日志文件的个数，默认值2，最大值为100

19.6.3、redo日志文件格式

前2048个字节（也就是前4个block）用来存储一些管理信息

从第2048字节往后的字节用来存储log buffer 中的block镜像

19.7、log sequence number

8704开始

19.7.1、flushed_to_disk_lsn

用来标记log buffer中已经有哪些日子被刷新到磁盘中

8704开始

19.11、innodb_flush_log_at_trx_commit

0

在事务提交时不立即向磁盘同步redo日志，这个任务交给后台线程处理；这样会明显加快请求处理速度。但是，如果事务提交后服务器挂了，后台现场没有及时将redo日志刷新到磁盘，那么该事务对页面的修改就丢失

1

在事务提交时需要将redo日志同步到磁盘；这可以保证事务的持久性。默认

2

在事务提交时需要将redo日志写到操作系统的缓冲区，但并不需要保证将日志真正刷新到磁盘。在这种情况下如果数据库挂了而操作系统没有挂，事务的持久性还是可以保证的。但如果操作系统也挂了，那就不保证持久性

第20章后悔了怎么办-undo日志

20.12、总结

为了保证事务的原子性，引入了undo日志。undo日志记载了回滚一个操作所需的必要内容

在事务对表的记录进行改动时，才会为这个事务分配一个唯一的事务id，事务ID值就是一个递增的数字

聚簇索引记录中有一个trx_id隐藏列，它代表对这个聚簇索引进行改动的语句所在的事务对应的事务ID

不同类型的undo日志

TRX_UNDO_INSERT_REC

TRX_UNDO_MARK_REC

TRX_UNDO_UPD_EXIST_REC

在一个事务执行过程中，最多分配4个UNDO页面链表

针对普通表的 insert undo 链表

针对普通表的 udate undo 链表

针对临时表的insert undo 链表

针对临时表的update undo 链表

每个undo页面链表都对应一个undo log segment

第21章一条记录的多副面孔-事务隔离级别和MVCC

21.2、事务隔离级别

21.2.1、事务并发执行时遇到的一致性问题

脏写

如果一个事务修改了另一未提交的事务修改过的数据，就意味着发生了脏写现象

脏读

如果一个事务读到了另一个未提交事务修改过的数据，就意味着发生了脏读现象

不可重复读

如果一个事务修改了另一个未提交事务读取的数据，就意味着发生了不可重复读现象，或者叫模糊读

幻读

如果一个事务先根据某些搜索条件查询出一些记录，在该事务未提交时，另一个事务写入一些了符合那些搜索条件的记录（这里的写入可以值 insert、delete、update操作），就意味着发生了幻读现象

21.2.2、sql标准中的4种隔离级别

脏写>脏读>不可重复读>幻读

READ UNCOMMITTED

未提交读

可能发生脏读、不可重复读、幻读

READ COMMITTRED

已提交读

可能发生不可重复读、幻读

REPEATABLE READ

可重复读

可能发生幻读

SERIALIZABLE

可串行化

21.2.3、msyql中支出的4种隔离级别

设置事务的隔离级别

SET GOLBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE

通过查看系统变量transaction_isolation的值来确定当前会话默认的隔离级别

show variables like ‘transaction_isolation’

select @@ transaction_isolation

21.3、MVCC原理

21.3.1、版本链

聚簇索引包含2个必要的隐藏列

trx_id

一个事务每次对某条聚簇索引引起记录进行改动时，都会把该事务的事务id赋值给trx_id隐藏列

roll_pointer

每次对某条聚簇索引记录进行修改时，都会把旧的版本写入到undo日志中。这个隐藏列就相当于一个指针，可以通过它找到该记录修改前前的信息

在每次更新记录后，都会将旧值放到一条undo日志中（就算是该记录的一个旧版本）。随着更新次数的增多，所有的版本都会被poll_pointer属性连接成一个链表，这个链表称为版本链。版本链的头结点是当前记录的最新值。另外，每个版本中还包含生成该版本时对应的事务id。

我们之后会利用这个记录的版本链来控制并发事务访问相同记录时的行为，我们把这种机制称之为多版本并发控制（multi-version-concurrency control，MVCC）

21.3.2、ReadView

对于使用REAS UNCOMMITTED隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以一直读取记录的最新版本就好；对于使用SERIALIZABLE隔离级别的事务来说，设计InnoDB的大叔规定使用加锁的方式来访问记录；对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，都必须保证读到已经提交的事务修改过的版本记录。也是就说假如另一个事务已经修改了记录但是尚未提交，则不能直读取最新的版本记录。

核心问题就是：需要判断版本链中的哪个版本是当前事务可见的，为此，设计InnoDB的大叔提出了ReadView（一致性视图）的概念

ReadView4个比较重要的内容

m_ids

在生成ReadView时，当前系统中活跃的读写事务的事务ID列表

min_trx_id

在生成ReadView时，当前系统中活跃的读写事务中最小的事务id；也就是m_ids中最小的值

max_trx_id

在生成ReadView时，系统应该分配给下一个事务ID值

creator_trx_id

生产该ReadView的事务的事务id

1、READ COMMITTED-每次读取数据前都生成一个ReadView

2、REPEATABLE READ-在第一次读取数据生成一个ReadView

对于使用REPEATABLE READ隔离级别的事务来说，只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会重复生成ReadView了。

m_ids列表的内容是[100,200]，说明2个事务活跃

在隔离级别是REPEATABLE READ时，如果使用START TRANSACTION WITH CONSISTENT SNAPSHOP语句开启事务，会执行该语句后立即生成一个ReadView，而不是在执行第一条SELECT语句时才生成

21.3.3、二级索引与MVCC

21.3.4、MVCC小结

所谓的MVCC指的就是使用READ COMMITTED、REPEATABLE REPEATABLE READ这两种隔离级别的事务执行普通的SELECT操作时，访问记录的版本链的过程。这样可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

READ COMMITTED、REPEATABLE READ这两个隔离级别有一个很大的不同，就是生成ReadView的时机不同

READ COMMITTED在每一次进行普通SELECT操作前都会生成一个ReadView

REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都是重复使用这个ReadView

只有我们进行普通的SELECT查询时,MVCC才生效

21.4、关于purge

insert undo日志在事务提交之后就可以释放掉了，而update undo日志由于还需要支持MVCC，因此不能立即删除

一个事务写的一组undo日志中都有一个Undo Log Header部分，这个Undo Log Header中有一个名为TRX_UNDO_HISTORY_NODO的属性，表示一个名为History链表的节点。当一个事务提交之后，就会把这个事务执行过程中产生的这一组update undo日志插入到History链表的头部

每个回滚段都对应一个名为Rollback Segment Header的页面，这个页面中有两个属性

TRX_RSEG_HISTORY

表示History链表的基节点

TRX_RESG_HISTORY_SIZE

表示History链表占用的页面数量

为了支持MVCC，delete mark 操作仅仅是在记录上打一个删除标记，并没有真正将记录删除

为了节省空间，我们应该在合适的时候把update undo日志以及仅仅被标记为删除的记录彻底删除掉，这个删除操作就称为purge

21.5、总结

脏写

一个事务修改了另一个未提交事务修改过的数据

脏读

一个事务读到了另一个未提交事务修改过的数据

不可重复读

一个事务修改了另一个未提交事务读取的数据

幻读

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

READ UNCOMMITTED

可能发生脏读、不可重复读和幻读

READ COMMITTED

可能发生不可重复读和幻读现象，但是不可能发生脏读现象

REPEATABLE READ

可能发生幻读现象，但是不可能发生脏读和不可重复读的现象

SERIALIZABLE

各种现象都不能发生

聚簇索引记录和undo日志中的roll_pointer属性可以串连成一个记录的版本链

通过生成ReadView来判断记录的某个版本的可见性，其中READ COMMITTED在每一个次进行普通SELECT操作前都会生成一个ReadView,而在REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView,之后的查询操作都重复使用这个ReadView

当前系统中，如果最早生成的ReadView不再访问undo日志以及打了一个删除标记的记录，则可以通过purge操作将他们清除

第22章工作面试老大难-锁

22.1、解决并发事务带来问题的两种基本方式

22.1.1、写-写情况

在写-写情况下会发生脏写的现象，任何一种隔离级别都不允许这种现象的发生。所以在多个未提交事务相继对一条记录进行改动时，需要让它们排队执行。这种排队的过程其实是通过为该记录加锁来实现的。

这个锁本质上是一个内存中的结构，在事务执行之前本来是没有锁的，也就是说一开始是没有锁结构与记录进行关联的

当一个事务想对这条记录进行改动时，首先会看看内存中有没有与这条记录关联的锁结构;如果没有，就会在内存中生成一个锁结构与之关联

锁结构的信息很多，两个重要的属性

trx信息

表示这个锁结构是与那个事务关联的

is_waiting

表示当前事务是否在等待

总结

获取锁成功，或者加锁成功；在内存中生成了对应的锁结构，而且锁结构的is_waiting属性为false，也就是事务可以继续执行操作，当然并不是所有的加锁操作都需要生成对应的锁结构，有时候会有一种‘加隐式锁’的说法，隐式锁并不会生成实际的锁结构，但是仍然可以起到保护记录的作用。我们把为记录添加隐式锁的情况也认为是获取锁成功

获取锁失败，或者加锁失败，或者没有获取到锁；在内存中生成了对应的锁结构，不过锁结构的is_waiting属性为true，也就是事务需要等待，不可以继续执行操作

不加锁；不需要在内存中生成对应的锁结构，可以直接执行操作。不包括为记录加隐式锁的情况

22.1.2、读-谢或写-读情况

怎么避免脏读、不可重复读、幻读

方案1、读操作使用多版本并发控制（MVCC），写操作进行加锁

方案2、读、写操作都采用加锁的方式

22.1.3、一致性读

事务利用MVCC进行读取操作称为一致性读，或者一致性无锁读（有的资料也称为快照读）

22.1.4、锁定读

1、共享锁和独占锁

共享锁

简称S锁。在事务要读取一条记录时，需要先获取该记录的S锁

独占锁

也常为排他锁，简写X锁。在事务要改动一条记录时，需要先获取该记录的X锁

2、锁定读的语句

对读取的记录加S锁

select 。。。 lock in share mode；

对读取的记录加X锁

select 。。。 for update；

22.1.5、写操作

delete

对一条记录执行delete操作的过程其实是先在B+树中定位到这条记录的位置，然后获取到这条记录的x锁，最后再执行delete mark操作。我们页可以把这个“先定位待删除记录在B+树中的位置，然后获取这条记录的x锁的过程”看成是一个获取x锁的锁定读

update

3种情况

先定位待修改记录在B+树中的位置，然后再获取记录的X锁的过程

如果修改了该记录的键值，则相当于在原记录上执行delete操作之后再来一次insert操作，加锁操作就需要按照delete和insert的规则进行了

insert

一般情况下，新插入的一条记录受隐式锁保护，不再需要在内存中为其生成对应的锁结构

22.2、多粒度锁

意向锁

意向共享锁

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

意向独占锁

简称IX锁，当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁

总结

IS锁、IX锁是表级锁，它们的提出仅仅为了在之后表级别的S锁和X锁时，可以快速判断表中的记录是否被锁上，以避免用遍历的方式来查看表中有没有上锁的记录；也就是说其实IS锁和IX锁时兼容的，IX锁和IX锁是兼容的

22.3、mysql中的行锁和表锁

22.3.1、其他存储引擎中的锁

对于MyISAM、MEMORY、MERGE这些存储引擎来说，它们只支持表级锁，而且这些存储引擎并不支持事务，所以当我们为使用这些存储引擎的表加锁时，一般都是针对当前会话来说的

比如在session1中对一个表执行select操作，就相当于为这个表加了一个表级别的S锁。如果在select操作未完成时，在session2中对这个表执行update操作，相当于要获取表的X锁，此操作将被阻塞。直到session1中的select操作完成，释放掉表级别的S锁后，在session2中对这个表执行update操作才能继续获取X锁，然后执行具体的更新语句

另外，在MyISAM存储引擎中有一个称为并发插入的特性，它支持在读取了MyISAM表的同时插入记录，这样可以提升插入速度

23.3.2、InnoDB存储引擎中锁

1、InnoDB中的表级锁

表级别的S锁、X锁

元数据锁

某个事务在对某个表执行select、insert、delete、update语句时，在其他会话中对这个表执行DDL语句会发生阻塞。这个过程其实是通过在server层使用一种称为元数据锁的来实现的

表级别的AUTO-INC锁

采用AUTO-INC锁，也就是在执行插入语句时，就要加入一个表级别的AUTO-INC锁，然后为每条待插入记录的AUTO-INCREMENT修饰的列分配递增的值。在该语句执行结束后，再把AUTO-INC锁释放掉

2、InnoDB中的行级锁

行级锁，也称为记录锁，顾名思义就在记录上加的锁

常用的行级锁类型

Record Lock

前面提到的记录锁就是这种类型，也就是仅仅把一条记录锁上

官方名称LOCK_REC_NOT_GAP

Gap Lock

官方名称LOCK_GAP

为number值为8的记录加了gap锁，这意味着不允许别的事务在number值为8的记录前面的间隙插入新纪录，其实就是number列的值在区间（3，8）的新记录时不允许立即插入的

gap锁的作用仅仅是为了防止插入幻影记录而已

Next-key Lock

有时候，我们既想锁住某条记录，又想阻止其他事务在该记录前面的间隙插入新记录

官方名称LOCK_ORDINARY

简称next-key

next-key锁的本质就是一个 record lock 和gap lock的合体，它既能保护该条记录，又能阻止别的事务将新记录插入到被保护记录前面的间隙中

Insert Intention Lock

一个事务在插入一条记录时，需要判断插入位置是否已被别的事务加了gap 锁（next-key锁也包含gap锁）。如果有的话，插入操作需要等待，直到拥有gap锁的那个事务提交为止，事务在等待时也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在处于等待状态。设计InnoDB的大叔把这种类型的锁命名为 Insert Intertion Lock。插入意向锁

插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁（插入意向锁就是这么鸡肋）

隐式锁

延迟生成锁结构的用处，如果别的事务在执行过程中不需要获取与该隐式产品锁相冲突的锁，就可以避免在内存中生成锁结构

23.3.3、InnoDB锁的内存结构

对一条记录加锁的本质就是在内存中创建一个锁结构与之关联（隐式锁除外）

在对不同记录加锁时，如果符合下面的这些条件，这些记录的锁就可以放到一个锁结构中

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

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

加锁的类型是一样的

等待状态是一样的

InnoDB存储引擎事务锁结构

锁所在的事务信息

无论是表级锁还是行级锁，一个锁属于一个事务，这里记载着该锁对应的事务信息

在内存结构中只是一个指针

索引信息

对于行级锁来说，需要记录一下加锁的记录属于哪个索引

表锁/行锁信息

表级锁结构和行级锁结构在这个位置的内容是不同的，具体表现为表级锁记载着这是对哪个表加锁，还有其他的一些信息；而行级锁记载了下面3个重要的信息

Space ID

记录所在的表空间

Page Number

记录所在的页号

n_bits

对于行级锁来说，一条记录对应着一个比特

22.4、语句加锁分析

22.4.1、普通的select语句

在READ UNCOMMITTED隔离级别下，不加锁，直接读取记录的最新版本；可能出现脏读、不可重复读和幻读现象

在READ COMMITTED隔离级别下，不加锁；在每次执行普通的select语句时都会生成一个ReadView，这样避免了脏读现象，但没有避免不可重复读和幻读现场

在REPEATABLE READ隔离级别下，不加锁；只在第一次执行普通的select语句时生成一个ReadView，这样就把脏读、不可重复读和幻读现象都避免了

22.4.2、锁定读的语句

四种语句

语句1:select。。。lock in share mode;

语句2:select。。。for update

语句3:update。。。

语句4:delete

语句1和语句2是mysql中规定的两种锁定读的语法格式，而语句3和语句4由于执行过程中需要首先定位到被动的记录并给记录加锁，因此也可以认为是一种锁定读

22.4.3、半一致性读的语句

半一致性读是一种夹在一致性读和锁定读之间的读取方式。当隔离级别级不大于READ COMMITTED且执行UPDATE语句时使用半一致性读

22.4.4、INSERT语句

1、遇到重复键

2、外键检查

22.5、查看事务加锁情况

22.5.1、使用information_schema数据库中的表获取锁信息

select * from information_schema

INNODB_TRX

INNODB_LOCKs

INNODB_LOCK_WAITS

22.5.2、使用show eninge innodb status获取锁信息

子主题

22.6、死锁

22.7、总结

MVCC和加锁是解决并发事务带来的一致性问题的两种方式

事务利用MVCC进行的读取操作称为一致性读，在读取记录前加锁的读取操作称为锁定读。设计InnoDB的大叔提供了两种语法来进行锁定读

select。。。lock in share mode语句来为读取的记录加S锁

select。。。for update语句为读取的记录加X锁

insert 语句一般情况下不需要在内存中生成锁结构，并单纯依靠隐式锁保护插入的记录。update 和delete语句在执行过程中，在B+树中定位到待改动记录并给该记录加锁的过程也算是一个锁定读

IS、IX锁时表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时，可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录

第1章装作自己是个小白--初识mysql

1.2.1、bin目录下的可执行文件

使用可执行文件的相对/绝对路径来执行

相对

./bin/mysqld

绝对

/usr/local/mysql/bin/mysqld

将bin目录的绝对路径加入到环境变量PATH中

环境变量PATH是一系列路径的集合，各个路径之间使用冒号(:)隔离开

/usr/local/mysql/bin

直接输入可执行文件的名字来启动 mysqld

1.3、启动mysql服务器程序

1.3.1、在类unix系统中启动服务器程序

3、mysql.server

mysql.server start

mysql.server stop

1.3.2、在windows系统中启动服务器程序

1、手动启动

双击bin目录下mysqld

2、以服务的方式启动

注册为一个windows服务

“完整的可执行文件路径”--install [-manual] [服务名]

-manual 表示在windows系统启动的实时不字段启动该服务

服务名可以省略，默认的服务名就是MySql

“c:\MySql\MySql Server 5.7\bin\mysqld”--install

把mysqld 注册为windows服务之后，通过命令启动/关闭

net start MySql

net stop MySql

1.4、启动mysql客户端程序

mysql -h主机名 -u用户名 -p密码

-p和密码之间不能有空白字符

1.5、客户端与服务的连接过程

端口取值范围0~65535

默认监听3306

-P参数来明确指定端口号

mysqld -P3307

1.6、服务器处理客户端的请求

1.6.1、连接管理

1.6.2、解析与优化

1、查询缓存

缓存失效

只要该表的结构或者数据被修改

从mysql 5.7.20开始，不推荐使用查询缓存，在mysql 8.0中直接将其删除

2、语法解析

3、查询优化

1.7、常用的存储引擎