mysql是怎样运行的
2021-06-15 15:33:17 50 举报
登录查看完整内容
为什么这个SQL语句执行得这么慢？为什么我明明建立了索引，但是查询计划显示没用？为什么IN查询中的参数一多就不使用索引了？为什么我的数据显示成了乱码？……每一位DBA和后端开发人员在与MySQL打交道时，或多或少都会遇到这些问题。之外，索引结构、MVCC、隔离级别的实现、锁的使用等知识，也是求职人员在MySQL面试中躲不过去的高频问题。 本书针对上面这些问题给出了相应的解答方案。本书的表达方式与司空见惯的学术派、理论派IT图书有显著区别，但也是相当正经的专业技术图书，内容涵盖了使用MySQL的同学在求职面试和工作中常见的一些核心概念。无论是身居MySQL专家身份的技术人员，还是技术有待进一步提升的DBA，甚至是刚投身于数据库行业的“萌新”人员，本书都是他们彻底了解MySQL运行原理的优秀图书。
作者其他创作
大纲/内容
第12章 谁最便宜就选谁-基于成本的优化
12.1、执行成本
I/O成本
我们的表经常使用的myisam、innodb存储引擎都是将数据和索引存储到磁盘上。当查询表中的记录时，需要先把数据或者索引加载到内存中，然后再进行操作。这个从磁盘到内存的加载过程损耗的时间称为I/O成本
CPU成本
读取记录以及检测记录是否满足对应的搜索条件、对结果集进行排序等这写操作损耗的时间称为CPU成本
12.2、单表查询成本
12.2.2、基于成本的优化步骤
1、根据搜索条件，找出所有可能使用的索引
2、计算全表扫描的代价
3、计算使用不同索引执行查询的代价
4、对比各种执行方案的代价，找出成本最低的那个方案
查询成本=I/O成本+CPU成本
show tables status like '表名'
rows
表示表中的记录条数
myisam
该值是准确的
innodb
该值是一个估计值
data_length
表示表占用的存储空间字节数
该值就是数据文件的大小
该值相当于聚簇索引占用存储空间的大小
聚簇索引的页面数据 * 每个页面的大小
聚簇索引的页面数量=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
当使用索引列等值查询时，与索引列进行等值匹配的对象信息
预估需要读取的记录条数
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
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日志格式
这条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中已经有哪些日子被刷新到磁盘中
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。
undo日志版本链
我们之后会利用这个记录的版本链来控制并发事务访问相同记录时的行为，我们把这种机制称之为多版本并发控制（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了。
在隔离级别是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
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 COMMITTED
可能发生不可重复读和幻读现象，但是不可能发生脏读现象
REPEATABLE READ
可能发生幻读现象，但是不可能发生脏读和不可重复读的现象
各种现象都不能发生
聚簇索引记录和undo日志中的roll_pointer属性可以串连成一个记录的版本链
当前系统中，如果最早生成的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、常用的存储引擎
ARCHIVE
用于数据存档（记录插入后不能再修改）
BLACKHOLE
丢弃写操作，读操作会返回空内容
CSV
在存储数据时，以逗号分隔各个数据项
FEDERATED
用来访问远程表
InnoDB
支出事务、行级锁、外键
MEMORY
数据只存储在内存，不存储在磁盘，多用于临时表
MERGE
用来管理多个MyISAM表构成的表集合
MyISAM
主要的非事务处理存储引擎
NDB
MySql集群专用存储引擎
1.8、关于存储引擎的一些操作
SHOW ENGINES
查询当前服务器支持的存储引擎
修改表的存储引擎
ALTER TABLE 表名 ENGINE = 存储引擎名称;
第2章 MySql的调控按钮-启动选项和系统变量
2.1、启动选项和配置文件
默认设置
允许同时连入的客户端的默认数量 151
表的默认存储引擎
2.2、系统变量
2.2.2、查看系统变量
SHOW VARIABLES LIKE '匹配的模式';
SHOW VARIABLES LIKE
‘max_connections’
允许同时连入的客户端数量
第3章 字符集和比较规则
3.1.3、一些重要的字符集
ASCII字符集
128个字符
ISO 8859-1
256个字符
GB2312
汉字6763个
GBK
GB2312的扩充
UTF-8
一个字符1~4字节
3.2、支持的字符集和比较规则
utf8_general_ci
通用的比较规则
ci说明不区大小写
第4章 从一条记录开始说起-InnoDB记录存储结构
4.2、InnoDB页简介
将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位
页大小16KB
一次最少从磁盘中读取16KB到内存中，一次也最少把内存中的16KB内容刷新到磁盘中
4.3、InnoDB行格式
4种不同类型的行格式
COMPACT
REDUNDANT
DYNAMIC
COMPRESSED
4.3.1、指定行格式的语法
1、记录的额外信息
变长字符长度列表
VARCHAR(n)
真正的数据内容
该数据占用的字节数
列表按照真实数据所占用的字节数的顺序逆存放
逆存放
NULL值列表
记录头信息
5个字节组成，40个二进制位
预留位_1
大小：1
没有使用
预留位_2
deleted_flag
标记该记录是否被删除
min_rec_flag
b+树的每层非叶子节点中的最小的目录项记录都会添加该标记
n_owned
大小：4
一个页面中的记录会被分成若干个组，每个组中有一个记录是“带头大哥”，其余的记录都是“小弟”。“带头大哥”记录的n_owned值代表该组中所有的记录条数，“小弟”记录n_owned值都是0
heap_no
大小：13
表示当前记录在页面堆中的相对位置
record_type
大小：3
表示当前记录的类型，0表示普通记录，1表示b+树非叶子节点的目录项记录，2表示Infimum记录，3表示Supremum
next_record
大小：16
记录下一条的相对位置
2、记录的真实数据
每个记录默认添加的列
row_id
是否必须：否
占用空间：6字节
是否必须：是
描述：事务ID
占用空间：7字节
描述：回滚指针
主键生成策略
优化使用用户自定义的主键；如果没有自定义主键，则选取一个不允许为null值的UNIQUE键作为主键；如果以上都不满足，默认添加一个名为row_id的隐藏列作为主键
第5章 盛放记录的大盒子-InnoDB数据页结构
5.2、数据页结构快览
File Header
文件头部
38字节
页的一些通用信息
Page Header
页面头部
56字节
数据页专有的一些信息
Infimum+Supremum
页面中的最小记录和最大记录
26字节
2个虚拟的记录
User Records
用户记录
不确定
用户存储的记录内容
Free Space
空闲空间
不确定
页中尚未使用的空间
Page Directory
页目录
页中某些记录的相对位置
File Trailer
文件尾部
8字节
效验页是否完整
第6章 快速查询的秘籍-b+树索引
内节点中目录项记录的唯一性
二级索引的内节点的目录项记录3部分构成
索引列的值
主键值
对于二级索引记录来说，先二级索引排序，再主键排序
第7章 B+树索引的使用
7.4、回表的代价
需要执行回表的记录越多，使用二级索引进行查询的性能也就越低，某些查询宁愿使用全表扫描也不是使用二级索引
查询优化器
什么时候采用全表扫描
什么时候使用二级索引+回表
可以给查询语句指令limit子句来限制查询返回的记录数，这可能会让查询优化器倾向于选择使用二级索引+回表的方式进行查询，原因是回表的记录越少，性能提升就越高
对于需要对结果进行排序的查询，如果在采用二级索引执行查询时需要执行回表操作的记录特别多，也倾向于使用全表扫描+文件排序执行方式进行查询
7.5、更好地创建和使用索引
7.5.1、只为用于搜索、排序或分组的列创建索引
where子句中的列、连接子句中的连接列、order by 或者 group by子句中的列创建索引
7.5.2、考虑索引列中不重复值的个数
列中不重复的个数占全部记录条数的比例，比例太低，则说明该列包含过多重复值，可能执行太多次回表操作
7.5.3、索引列类型尽量小
7.5.4、为列前缀创建立索引
7.5.5、覆盖索引
查询列表中只包含索引列
7.5.6、让索引列以列名的形式在搜索条件中单独出现
key2 * 2 表达式全表扫描
7.5.7、新插入记录时主键大小对效率的影响
为了尽可能少地让聚簇索引引发页面分裂的情况，建议主键自增
7.5.8、重复索引
第8章 数据的假-mysql的数据目录
8.2、mysql数据目录
mysql服务器启动的时，会到系统文件系统的某个目录下加载一些数据，之后在运行过程中产生的数据也会存储到这个目录下的某些文件中。这个目录就称为数据目录。
8.2.2、如何确定mysql的安装目录
\bSHOW VARIABLES LIKE 'datadir'
8.3、数据目录的结构
8.3.1、数据库在文件系统中的表示
1、在数据目录下创建一个与数据库名同名的子目录（或者说是文件夹）
2、在与该数据库名同名的子目录下创建一个名为db.opt的文件。这个文件中包含了该数据库中的一些属性，比如该数据库的字符集和比较规则
8.3.2、表在文件系统中的表示
表结构的定义
表名.frm
表中的数据
innodb是如何存储的
表名.ibd
mylsam
表名.MYD
表的数据文件
表名.MYI
表的索引文件
第9章 存放页面的大池子-innodb的表空间
9.2、独立表空间的结构
9.2.1、区的概念
对于16kb的页来说，连续的64个页就是一个区，也就是说一个区默认占1MB空间的大小
无论是系统表空间还是独立表空间，都可以看成是由若干个连续的区组成的，每256个区被划分成一组
9.2.2、段的概念
存放叶子节点的区的集合就算是一个段，存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成两个段：一个叶子节点的段和一个非叶子节点的段
碎片区直属于表空间，并不属于任何一个段
9.2.3、区的分类
空闲的区
现在还没有用到这个区中的任何一个页面
有剩余空闲页面的碎片区
表示碎片区中还有可被分配的空闲页面
没有剩余空闲页面的碎片区
表示碎片区中的所有页面都被分配使用，没有空闲页面
附属于某个段的区
每一个索引都可以分为叶子节点段和非叶子节点段。除此之外，innodb还会另外定义一些特殊用途的段。当这些段中的数据量很大时，将使用区作为基本的分配单位，这些区中的页面完全用于存储该段中的数据
XDES Entry
SegmentID
List Node
12字节
Prev Node Page Number
4字节
Prev Node Offset
2字节
Next Node Page Number
Next Node Offset
State
Page State Bitmap
16字节
3个链表
FREE链表
同一个段中，所有页面都是空闲页面的区对应的XDES Entry 结构会被加入到这个链表中
NOT_FULL链表
同一个段中，仍有空闲页面的区对应的 XDES Entry结构会被加入到这个链表中
FULL链表
同一个段中，已经没有空闲页面的区对应的 XDES Entry结构会被加入到这个链表中
9.2.4、段的结构
INODE Enrty
Segment ID
NOT_FULL_N_USED
3个list base node
Magic Number
Fragment Array Entry
9.2.5、各类型页面详细情况
1、FSP_HDR
存储了表空间的一些整体属性以及第一个组内256个区对应的XDES Entry结构
组成部分
File Space Header
表空间头部
112字节
表空间的一些整体属性信息
区描述信息
10240字节
存储本组256个区对应的属性信息
Empty Space
尚未使用的空间
5986字节
用于页结构的填充，没啥实际意义
2、XDES
3、IBUF_BITMAP
记录了一些有关 change buffer的东西
4、INODE
组成
List Node for INODE Page List
通用链表节点
存储上一个INODE页面和一个INODE页面的指针
INODE Entry
段描述信息
16320字节
具体的INODE Entry 结构
尚未使用空间
6字节
9.3、系统表空间
9.3.1、系统表空间的整体结构
第10章 条条大路通罗马-单表访问方法
10.2、const
通过主键或者二级索引列来定位一条记录的访问方法定义为const（意思是常量级别的，代价是可以忽略不计的）
10.3、ref
搜索条件为二级索引与常数进行等值比较，形成的扫描区间为单点扫描区间，采用二级索引来执行查询的访问方法称为ref
会匹配多条二级索引
10.4、ref_or_null
ref_or_null访问方法只是比ref访问方法多扫描了一些值为NULL的二级索引记录
值为NULL的记录会被放在索引的最左边
10.5、range
使用索引执行查询时，对应的扫描区间为若干个单点扫描区间或范围扫描区间的访问方法称为range
仅包含一个单点扫描区间的访问方法不能称为range访问方法，扫描区间（-00，+00）的访问方法不能称为range访问方法
10.6、index
符合条件
它的查询列表只有key_part1、key_part2和key_part3这3个列，而索引index_key_part又恰好包含这3个列
搜索条件只有key_part2列，这个列也包含在索引index_key_part中
扫描所有的二级索引比扫描所有的聚簇索引的成本小
扫描所有的二级索引记录的访问方法称为index访问方法
10.7、all
使用全面扫描执行查询的访问方法称为all访问方法
第11章 两个表的亲密接触-连接的原理
11.1、连接简介
11.1.1、连接的本质
连接就是把各个表中的记录都取出来进行依次匹配，并把匹配后的组合发送给客户端
11.1.3、内连接和外连接
1、左外连接的语法
select * from t1 left [outer] join t2 on 连接条件 [where 普通过滤条件]
左边的表称为外表或者驱动表，放在右边的表称为内表或者被驱动表
2、右外连接的语法
select * from t1 right [outer] join t2 on 连接条件 [where 普通过滤条件]
驱动表是右边的表，被驱动表是左边的表
3、内连接的语法
内连接和外连接的根本区别就是在驱动表中的记录不符合on子句中的连接条件时，内连接不会把该记录加入到最后的结果集中
select * from t1 [inner ｜cross] join t2 [on 连接条件] [where 普通过滤条件]
无论哪个表作为驱动表，两表连接产生的笛卡尔积肯定是一样的
0 条评论
回复 删除
下一页
