MySQL
2020-04-23 18:46:56 7 举报AI智能生成
掘金小册《从根儿上理解mysql》知识点整理笔记
作者其他创作
大纲/内容
基础知识
一条SQL语句的流程
1.连接
当客户端连接到MySQL服务器时，服务器对其进行认证
登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限
登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限
2.查询缓存
在正式查询之前，服务器会检查查询缓存，如果能找到对应的查询，
则不必进行查询解析，优化，执行等过程，直接返回缓存中的结果集。
则不必进行查询解析，优化，执行等过程，直接返回缓存中的结果集。
3.解析器
MySQL的解析器会根据查询语句，构造出一个解析树，主要用于根据语法规则来验证语句是否正确，
比如SQL的关键字是否正确，关键字的顺序是否正确。
比如SQL的关键字是否正确，关键字的顺序是否正确。
4.预处理器
主要是进一步校验，比如表名，字段名是否正确等
5.查询优化器
查询优化器将解析树转化为查询计划，一般情况下，一条查询可以有很多种执行方式，最终返回相同的结果，
优化器就是根据成本找到这其中最优的执行计划
优化器就是根据成本找到这其中最优的执行计划
6.查询执行引擎
通过一系列API接口查询到数据
得到数据之后，在返回给客户端的同时，会将数据存在查询缓存中
MySQL的客户端／服务器架构
MySQL的使用
1. 启动MySQL服务器程序
2. 启动MySQL客户端程序并连接到服务器程序
3. 在客户端程序输入命令语句作为请求发送到服务器程序，服务器程序收到请求后，会根据请求的内容来操作具体的数据并向客户端返回操作结果。
计算机多进程运行
每个进程都有一个唯一的编号，称为进程ID，英文名叫PID
这个编号是在我们启动程序的时候由操作系统随机分配的
进程结束后会被收回，重分配
每个进程都有一个名称，这个名称是编写程序的人自己定义的
MySQL服务器进程的默认名称mysqld
MySQL客户端进程的默认名称为mysql
bin目录下的可执行文件
1.mysqld
启动一个mysql服务端进程.但该命令不常用
2.mysqld_safe
间接调用mysqld
同时启动了另外一个监控进程
监控进程在服务端进程挂了的时候,可以重启进程.
并且将服务器程序的出错信息和其他诊断信息生成日志
同时启动了另外一个监控进程
监控进程在服务端进程挂了的时候,可以重启进程.
并且将服务器程序的出错信息和其他诊断信息生成日志
3.mysql.server start/stop
间接的调用mysqld_safe
建立客户端连接
mysql -h主机名 -u用户名 -P端口 -p密码
如果服务器和客户端安装在同一台机器上，-h参数可以省略
-p之后回车，可以使密码隐式输入
断开客户端连接
quit或exit或\q
客户端进程和服务器进程的通信方式
客户端进程向服务器进程发送请求并得到回复的过程本质上是一个进程间通信的过程
MySQL支持下面三种通信方式
MySQL支持下面三种通信方式
1. TCP/IP
MySQL服务器启动的时候就会申请默认的3306端口号，等待客户端的连接
自定义端口号的话，就在命令mysql -P自定义的端口号
自定义端口号的话，就在命令mysql -P自定义的端口号
例如： mysql -h127.0.0.1 -uroot -P3307 -p
2. 命名管道和共享内存(Windows系统)
命名管道
启动服务器程序的命令中加上--enable-named-pipe参数
启动客户端程序的命令中加入--pipe或者--protocol=pipe参数
共享内存（服务端和客户端需要在同一主机）
启动服务器程序的命令中加上--shared-memory参数
启动客户端程序的命令中加入--protocol=memory参数来显式的指定使用共享内存进行通信
3. Unix域套接字文件
MySQL服务器程序默认监听的Unix域套接字文件路径为/tmp/mysql.sock
启动客户端的命令指定的主机名为localhost或指定了--protocol=socket的启动参数
服务器处理客户端请求
1.把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划为MySQL server的功能，把真实存取数据的功能划分为存储引擎的功能
2.各种不同的存储引擎向上边的MySQL server层提供统一的调用接口（也就是存储引擎API），包含了几十个底层函数，像"读取索引第一条内容"、"读取索引下一条内容"、"插入记录"等
3.所以在MySQL server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了
2.各种不同的存储引擎向上边的MySQL server层提供统一的调用接口（也就是存储引擎API），包含了几十个底层函数，像"读取索引第一条内容"、"读取索引下一条内容"、"插入记录"等
3.所以在MySQL server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了
MySQL server
连接管理
客户端程序发起连接，需要携带主机信息、用户名、密码
服务器程序会启动一个线程用于和客户端进行交互
断开连接后服务端会缓存连接线程用于分配下次的交互，节省开销
可进行设置连接线程数量,用于控制同时连接到服务器的客户端数量
解析与优化
查询缓存
查询请求和结果缓存下来，不同的客户端之间可以共享
如果两个查询请求在任何字符上的不同（空格、注释、大小写）
或者含有用户自定义变量和函数，都会导致缓存不会命中
或者含有用户自定义变量和函数，都会导致缓存不会命中
若设计到的表的结构数据被修改过，则将变为无效并从高速缓存中删除
由于需要维护该缓存，已在MySQL 8.0中删除
语法解析
对接收到的文本分析，判断请求的语法是否正确
查询优化
对语句做一些优化,如外连接转换为内连接、表达式简化、子查询转为连接
生成执行计划,表明了应该使用哪些索引进行查询，表之间的连接顺序
可以使用EXPLAIN语句来查看某个语句的执行计划
存储引擎
常用的存储引擎
InnoDB
具备外键支持功能的事务存储引擎
MyISAM
主要的非事务处理存储引擎
ARCHIVE
用于数据存档（行被插入后不能再修改）
MEMORY
置于内存的表
查看当前服务器程序支持的存储引擎
SHOW ENGINES;
可以为不同的表设置不同的存储引擎
也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式
创建表时指定存储引擎
CREATE TABLE 表名(
建表语句;
) ENGINE = 存储引擎名称;
建表语句;
) ENGINE = 存储引擎名称;
修改表的存储引擎
ALTER TABLE 表名 ENGINE = 存储引擎名称;
启动选项和配置文件
启动选项
mysqld --skip-networking
mysqld --default-storage-engine = MyISAM
选项的长形式和短形式 是等价的
配置文件中使用选项
把需要设置的启动选项都写在这个配置文件中，每次启动服务器的时候都从这个文件里加载相应的启动选项
$MYSQL_HOME/my.cnf
多个配置文件设置了相同的启动选项，则以最后一个配置文件为准
同一个配置文件的多个组中出现了相同的配置项，以最后一个出现的组中的启动选项为准
如果同一个启动选项既出现在命令行中，又出现在配置文件中，那么以命令行中的启动选项为准
系统变量
对于大部分系统变量来说，它们的值可以在服务器程序运行过程中进行动态修改而无需停止并重启服务器
GLOBAL：全局变量，影响服务器的整体操作
SESSION：会话变量，影响某个客户端连接的操作
通过启动选项设置的系统变量的作用范围都是GLOBAL的，也就是对所有客户端都有效的
如果在设置系统变量的语句中省略了作用范围，默认的作用范围就是SESSION。
SHOW VARIABLES语句默认查看的是SESSION作用范围的系统变量
并不是所有系统变量都具有GLOBAL和SESSION的作用范围
有些系统变量是只读的，并不能设置值
状态变量
状态变量是用来显示服务器程序运行状况的，所以它们的值只能由服务器程序自己来设置
Threads_connected表示当前有多少客户端与服务器建立了连接
Threads_connected表示当前有多少客户端与服务器建立了连接
字符集
MySQL中的utf8和utf8mb4
utf8mb3：阉割过的utf8字符集，只使用1～3个字节表示字符
utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符
每种字符集对应若干种比较规则，每种字符集都有一种默认的比较规则
MySQL有4个级别的字符集和比较规则
服务器级别
数据库级别
表级别
列级别
数据库级别
表级别
列级别
比较规则的作用通常体现比较字符串大小的表达式以及对某个字符串列进行排序中
引擎
InnoDB
简介
将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB
InnoDB行格式
Compact
变长字段长度列表
1.把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，
从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放
从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放
2. 如果该可变字段允许存储的最大字节数（M×W）超过255
并且真实存储的字节数（L）超过127， 则使用2个字节，否则使用1个字节
并且真实存储的字节数（L）超过127， 则使用2个字节，否则使用1个字节
3.变长字段长度列表中只存储值为 非NULL 的列内容占用的长度，值为 NULL 的列的长度是不储存的
4.并不是所有记录都有这个 变长字段长度列表 部分，比方说表中所有的列都不是变长的数据类型的话，这一部分就不需要有
NULL值列表
1.Compact行格式把这些值为NULL的列统一管理起来，存储到NULL值列表中
2.如果表中没有允许存储 NULL 的列，则 NULL值列表 也不存在了
3.二进制位的值为1时，代表该列的值为NULL 二进制位的值为0时，代表该列的值不为NULL
同样为逆序排列
同样为逆序排列
4.MySQL规定NULL值列表必须用整数个字节的位表示， 如果使用的二进制位个数不是整数个字节，则在字节的高位补0
记录头信息
用于描述记录的记录头信息，它是由固定的5个字节组成
delete_mask 标志该记录是否被删除,0:未删除 1:已删除
被删除的记录不会立即从磁盘上移除， 是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗
所以通过该位置进行标志,并组成一个垃圾链表
该链表占用的空间被称为可重用空间
之后如果有新记录插入到表中的话， 可能把这些被删除的记录占用的存储空间覆盖掉。
heap_no表示该记录在本页中的位置
（从2开始）
（从2开始）
heap_no的值为0,1，分别代表着最小记录和最大记录
next_record
从当前记录的真实数据到下一条记录的真实数据的地址偏移量 按照主键从小到大的顺序形成了一个单链
指向下一条记录的真实数据的地址的优点: 向左读取就是记录头信息，向右读取就是真实数据。
变长字段长度列表、NULL值列表中的信息都是逆序存放，
这样可以使记录中位置靠前的字段和它们对应的字段长度信息在内存中的距离更近,
可能会提高高速缓存的命中率
这样可以使记录中位置靠前的字段和它们对应的字段长度信息在内存中的距离更近,
可能会提高高速缓存的命中率
真实的数据
隐藏列
记录的真实数据除了我们自己定义的列的数据以外，MySQL会为每个记录默认的添加一些列
InnoDB表对主键的生成策略
优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键;
如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。
所以我们从上表中可以看出：
InnoDB存储引擎会为每条记录都添加 transaction_id 和 roll_pointer 这两个列，
但是 row_id 是可选的（在没有自定义主键以及Unique键的情况下才会添加该列）。
这些隐藏列的值不用我们操心，InnoDB存储引擎会自己帮我们生成的。
如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。
所以我们从上表中可以看出：
InnoDB存储引擎会为每条记录都添加 transaction_id 和 roll_pointer 这两个列，
但是 row_id 是可选的（在没有自定义主键以及Unique键的情况下才会添加该列）。
这些隐藏列的值不用我们操心，InnoDB存储引擎会自己帮我们生成的。
NULL值
被存储在了前边的NULL值列表处，在记录的真实数据处就不再冗余存储，从而节省存储空间。
CHAR(10)和VARCHAR(10)的区别
1.CHAR(10)是固定长度的,当内容未填满时,会在其右边填充空格
比较适合固定长度的数据存储,如密码
2. char固定长度，存储空间一次性分配，没有碎片的困扰；
但是varchar在更改前后难免会有长度不一致导致存储碎片的情况，因此需要定时清理碎片子主题
但是varchar在更改前后难免会有长度不一致导致存储碎片的情况，因此需要定时清理碎片子主题
如果CHAR(10)采用的是非定长字符集时,该列存储的数据字节长度的范围是10～30个字节。
即使我们向该列中存储一个空字符串也会占用10个字节，
这是怕将来更新该列的值的字节长度大于原有值的字节长度而小于10个字节时，可以在该记录处直接更新，
而不是在存储空间中重新分配一个新的记录空间，导致原有的记录空间成为所谓的碎片。
即使我们向该列中存储一个空字符串也会占用10个字节，
这是怕将来更新该列的值的字节长度大于原有值的字节长度而小于10个字节时，可以在该记录处直接更新，
而不是在存储空间中重新分配一个新的记录空间，导致原有的记录空间成为所谓的碎片。
3. 读取数据时，char用trim()去掉多余的空格，varchar直接读出数据
CHAR(m)针对不同字符集的情况
采用变长字符集时，该列占用的字节数会被加到 变长字段长度列表
采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表
表record_format_demo使用的是ascii字符集，所以0x61616161就表示字符串'aaaa'，0x626262就表示字符串'bbb'，以此类推。
注意第1条记录中c3列的值，它是CHAR(10)类型的，它实际存储的字符串是：'cc'，而ascii字符集中的字节表示是'0x6363'，虽然表示这个字符串只占用了2个字节，但整个c3列仍然占用了10个字节的空间，除真实数据以外的8个字节的统统都用空格字符填充，空格字符在ascii字符集的表示就是0x20。
注意第2条记录中c3和c4列的值都为NULL，它们被存储在了前边的NULL值列表处，在记录的真实数据处就不再冗余存储，从而节省存储空间。
Redundant
（了解即可）
（了解即可）
MySQL5.0之前用的一种行格式
Dynamic
MySQL 5.7默认行格式
类似于COMPACT行格式
行溢出时列空间直接存储溢出页空间地址
Compressed
其他与Dynamic相同,只是会采用压缩算法对页面进行压缩，以节省空间
行溢出数据
一个页的大小为16KB,也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65532个字节, 将会导致一个页无法满足
Compact
Dynamic和Compressed
InnoDB数据页结构
数据页的存储空间大致被划分成了7个部分
存储的记录会按照我们指定的行格式存储到User Records部分
但是在一开始生成页的时候，其实并没有User Records这个部分，每当我们插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了
Page Directory（页目录）
页面目录就是由槽(Slot)组成的
将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组
对于最小记录所在的分组只能有 1 条记录， 最大记录所在的分组拥有的记录条数只能在 1~8 条之间，
剩下的分组中记录的条数范围只能在是 4~8 条之间
剩下的分组中记录的条数范围只能在是 4~8 条之间
每个组的最后一条记录的地址偏移量(next_record属性)单独提取出来存储到槽中
每个槽对应的记录都是该组中主键值最大的记录
数据页中查找指定主键值的记录的过程分为两步
通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录
借用上一个槽来找本槽最小记录
通过记录的next_record属性遍历该槽所在的组中的各个记录
Page Header（页面头部）
专门针对数据页记录的各种状态信息，比方说页里头有多少个记录了呀，有多少个槽
File Header（文件头部）
描述了一些针对各种页都通用的一些信息，比方说这个页的编号是多少，它的上一个页、下一个页是谁
通过建立一个双向链表把许许多多的页就都串联起来了，而无需这些页在物理上真正连着
并不是所有类型的页都有上一个和下一个页的属性
FIL_PAGE_OFFSET
每一个页都有一个单独的页号，InnoDB通过页号来可以唯一定位一个页
File Trailer
每当一个页面在内存中修改了，在同步之前就要把它的校验和算出来，因为File Header在页面的前边，所以校验和会被首先同步到磁盘，当完全写完时，校验和也会被写到页的尾部，如果完全同步成功，则页的首部和尾部的校验和应该是一致的。否则，意味着没有同步完整。
Infimum + Supremum
两个虚拟的伪记录，分别表示页中的最小和最大记录
B+树索引
record_type
0：普通的用户记录
1：目录项记录
2：最小记录
3：最大记录
1：目录项记录
2：最小记录
3：最大记录
节点
叶子节点： 实际用户记录其实都存放在B+树的最底层的节点上
非叶子节点：
根节点：一个B+树索引的根节点自诞生之日起，便不会再移动
内节点： 内节点中目录项记录的内容是 （ 索引列 +） 主键 + 页号
索引特点
下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值
否则，会进行页分裂操作
页内的记录是按照主键的大小顺序排成一个单向链表
各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
聚簇索引
B+树的叶子节点存储的是完整的用户记录
InnoDB存储引擎会自动的为我们创建聚簇索引
在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），
也就是所谓的索引即数据，数据即索引。
也就是所谓的索引即数据，数据即索引。
二级索引
（辅助索引）
（辅助索引）
使用非主键字段排序建立的B+树
叶子节点中除了索引字段外,还包含主键字段
因为需要保证在B+树的同一层内节点的 目录项记录除页号这个字段以外是唯一的
回表操作
利用二级索引的B+树查询后,根据主键去聚簇索引中快速定位其他字段信息
联合索引
同时以多个列的大小作为排序规则建立的B+树
排序是先以前面的索引字段排序,再根据后面的索引字段排序
sql查询语句时,如果未利用前序索引字段,将无法使用该联合索引
为了避免层级过多，一个页面最少存储2条记录
索引的代价
空间上的代价
每建立一个索引,需要占用许多的页空间
时间上的代价
增、删、改操作会对可能会对节点和记录的排序造成破坏, 每个索引对应的B+树都要进行相关的维护操作
索引的使用
B+树索引适用的条件
全值索引
如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';
WHERE子句中的几个搜索条件的顺序对查询结果没有影响, 因为MySQL的查询优化器会对其进行优化
匹配左边的列
如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列
匹配列前缀
对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定位记录的
匹配范围值
所有记录都是按照索引列的值从小到大的顺序排好序的
如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引
排序列使用了复杂的表达式,则无法利用索引
回表的代价
顺序I/O
记录在磁盘中的存储是相连的，集中分布在一个或几个数据页中， 我们可以很快的把这些连着的记录从磁盘中读出来
随机I/O
记录可能分布在不同的数据页中，这样读取完整的用户记录可能要访问更多的数据页
需要回表的记录越多，使用二级索引的性能就越低
文件排序
查询的结果集太大以至于不能在内存中进行排序的话， 还可能暂时借助磁盘的空间来存放中间结果，排序操作完成后再把排好序的结果集返回到客户端
覆盖索引
为了彻底告别回表操作带来的性能损耗，我们建议：最好在查询列表里只包含索引列
如何挑选索引
只给用于搜索、排序或分组的列创建索引
考虑列的基数
列的基数指的是某一列中不重复数据的个数
最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好
索引列的类型尽量小
数据类型越小，在查询时进行的比较操作越快（这是CPU层次)
数据类型越小，索引占用的存储空间就越少.一个页内可存储的数据也越多,减少I/O消耗
也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率
索引字符串值的前缀
只对字符串的前几个字符进行索引
优点
字符串越长，在索引中占用的存储空间越大
做长字符串比较时会占用更多的时间
缺点
可能导致排序时无法利用该索引
让索引列在比较表达式中单独出现
如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的
WHERE my_col < 4/2 而不是 WHERE my_col * 2 < 4
主键插入顺序
主键插入顺序最好可以按顺序自增
B+树的叶子节点中是按照主键顺序排序的, 主键如果插入到之前的页中,会发生页面分裂
和记录移位,导致性能损耗
和记录移位,导致性能损耗
让主键具有AUTO_INCREMENT，让存储引擎自己为表生成主键，而不是我们手动插入
冗余和重复索引
定位并删除表中的重复和冗余索引
MyISAM
索引是索引、数据是数据,分开存储
按照记录的插入顺序单独存储在一个数据文件中(不会主动排序),不划分若干页,通过行号迅速定位
MyISAM会单独为表的主键创建一个索引，而索引的叶子节点是主键值 + 行号的组合
也就是先通过索引找到对应的行号，再通过行号去找对应的记录
当采用定长记录格式时,MyISAM会直接在索引叶子节点处存储该条记录在数据文件中的地址偏移量
所有通过主键定位到偏移量后,可以直接回数据文件中取到数据
InnoDB和MyISAM的区别
1. InnoDB索引即数据，也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了
2. MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储
2. MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储
1.InnoDB是mysql默认的事务性存储引擎
2.MyISAM是非事务型的存储引擎
2.MyISAM是非事务型的存储引擎
1.InnoDB支持表级锁,行级锁
2.MyISAM是表级锁
2.MyISAM是表级锁
1.InnoDB支持外键（从A表一个外键去检索B表的主键）
清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
执行select count(*) from table 时，InnoDB要扫描一遍整个表来计算有多少行， 但是MyISAM只要简单的读出保存好的行数即可
数据目录
数据库在文件系统中的表示
每个数据库都会数据目录下创建一个同名的子目录(文件夹)
在该与数据库名同名的子目录下创建一个名为db.opt的文件， 这个文件中包含了该数据库的各种属性
表在文件系统中的表示
表结构的定义
表名.frm
据库子目录下创建了一个专门用于描述表结构的文件
表中的数据
InnoDB
表空间
是一个抽象的概念
对应文件系统上一个或多个真实文件 （不同表空间对应的文件数量可能不同）
MySQL5.5.7到MySQL5.6.6之间
把各个表的数据存储到系统表空间中
系统表空间（system tablespace）
ibdata1
初始12M大小
自扩展文件，会自动增加大小
MySQL5.6.6以及之后的版本
为每一个表建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间
独立表空间(file-per-table tablespace)
表名.ibd
其他类型的表空间
通用表空间（general tablespace）、 undo表空间（undo tablespace）、临时表空间（temporary tablespace）
MyISAM
MyISAM并没有什么所谓的表空间一说，表数据都存放到对应的数据库子目录下
表名.MYI
.MYI代表表的索引文件，我们为该表创建的索引都会放到这个文件中
表名.MYD
.MYD代表表的数据文件，也就是我们插入的用户记录
视图
MySQL中的视图其实是虚拟的表，也就是某个查询语句的一个别名而已；
所以在存储视图的时候是不需要存储真实的数据的，只需要把它的结构存储起来就行了。
和表一样，描述视图结构的文件也会被存储到所属数据库对应的子目录下边，只会存储一个视图名.frm的文件
所以在存储视图的时候是不需要存储真实的数据的，只需要把它的结构存储起来就行了。
和表一样，描述视图结构的文件也会被存储到所属数据库对应的子目录下边，只会存储一个视图名.frm的文件
其他的文件
服务器进程文件
每运行一个MySQL服务器程序，都意味着启动一个进程
服务器日志文件
在服务器运行过程中，会产生各种各样的日志，比如常规的查询日志、错误日志
.默认/自动生成的SSL和RSA证书和密钥文件
主要是为了客户端和服务器安全通信而创建的一些文件
文件系统对数据库的影响
数据库名称和表名称不得超过文件系统所允许的最大长度
特殊字符
MySQL会把数据库名和表名中所有除数字和拉丁字母以外的 所有字符在文件名里都映射成 @+编码值的形式作为文件名
例如，test?--->test@003f.frm
文件的大小受限于文件系统支持的最大文件大小
MySQL的四个系统数据库
mysql
这个数据库贼核心，它存储了MySQL的用户账户和权限信息
information_schema
保存着MySQL服务器维护的所有其他数据库的信息，
比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。
这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。
比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。
这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。
performance_schema
保存MySQL服务器运行过程中的一些状态信息，算是对MySQL服务器的一个性能监控
sys
这个数据库主要是通过视图的形式把information_schema和performance_schema结合起来，
让程序员可以更方便的了解MySQL服务器的一些性能信息。
让程序员可以更方便的了解MySQL服务器的一些性能信息。
InnoDB的表空间
常用的页面类型
页面通用部分
任何类型的页都会包含这两个部分
1.File Header：记录页面的一些通用信息
FIL_PAGE_OFFSET
由4个字节组成
就是32个比特位，所以一个表空间最多可以拥有2³²个页
16KB来算，一个表空间最多支持64TB的数据
页号从0开始
就是32个比特位，所以一个表空间最多可以拥有2³²个页
16KB来算，一个表空间最多支持64TB的数据
页号从0开始
.File Trailer：校验页是否完整，保证从内存到磁盘刷新时内容的一致性
区（extent）的概念
为了便于管理表空间提出了区的概念
64个页组成一个区(1MB)
64个页组成一个区(1MB)
物理位置上连续的64个页
在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了 而是按照区为单位分配，
甚至在表中的数据十分非常特别多的时候，可以一次性分配多个连续的区
甚至在表中的数据十分非常特别多的时候，可以一次性分配多个连续的区
可以避免大量的随机I/O
256个区组成一个组
区的分类
Free：现在还没有用到这个区中的任何页面。
Free_Frag：表示碎片区中还有可用的页面。
Full_Frag：表示碎片区中的所有页面都被使用，没有空闲页面。
Fseg：每一个索引都可以分为叶子节点段和非叶子节点段，除此之外InnoDB还会另外定义一些特殊作用的段，在这些段中的数据量很大时将使用区来作为基本的分配单位。
Free_Frag：表示碎片区中还有可用的页面。
Full_Frag：表示碎片区中的所有页面都被使用，没有空闲页面。
Fseg：每一个索引都可以分为叶子节点段和非叶子节点段，除此之外InnoDB还会另外定义一些特殊作用的段，在这些段中的数据量很大时将使用区来作为基本的分配单位。
处于FREE、FREE_FRAG以及FULL_FRAG这三种状态的区都是独立的，算是直属于表空间；而处于FSEG状态的区是附属于某个段的
每一个区都对应着一个XDES Entry结构
向某个段中插入数据的过程
当段中数据较少的时候，首先会查看表空间中是否有状态为FREE_FRAG的区，也就是找还有空闲空间的碎片区;
如果找到了，那么从该区中取一些零碎的页把数据插进去；
否则到表空间下申请一个状态为FREE的区，也就是空闲的区，把该区的状态变为FREE_FRAG;
然后从该新申请的区中取一些零碎的页把数据插进去。
之后不同的段使用零碎页的时候都会从该区中取，直到该区中没有空闲空间，然后该区的状态就变成了FULL_FRAG
如果找到了，那么从该区中取一些零碎的页把数据插进去；
否则到表空间下申请一个状态为FREE的区，也就是空闲的区，把该区的状态变为FREE_FRAG;
然后从该新申请的区中取一些零碎的页把数据插进去。
之后不同的段使用零碎页的时候都会从该区中取，直到该区中没有空闲空间，然后该区的状态就变成了FULL_FRAG
为了避免申请区空间时遍历所有的区,设计了针对3种区状态的链表 (这3种链表是直属于表空间的)
FREE链表: 把状态为FREE的区对应的XDES Entry结构通过List Node来连接成一个链表
FREE_FRAG链表: 把状态为FREE_FRAG的区对应的XDES Entry结构通过List Node来连接成一个链表
FULL_FRAG链表: 把状态为FULL_FRAG的区对应的XDES Entry结构通过List Node来连接成一个链表
extent 0这个区最开始的3个页面的类型是固定的
FSP_HDR类型
用来登记整个表空间的一些整体属性以及本组所有的区
整个表中只有一个
整个表中只有一个
File Space Header
XDES Entry链表
把256个区划分成一组，在每组的第一个页面中存放256个XDES Entry结构
XDES Entry 0就对应着extent 0....
每一个XDES Entry结构对应表空间的一个区
IBUF_BITMAP类型
这个类型的页面是存储本组所有的区的所有页面关于INSERT BUFFER的信息
INODE类型
这个类型的页面存储了许多称为INODE的数据结构
其余各组最开始的2个页面的类型是固定的
XDES类型
IBUF_BITMAP类型
段（segment）的概念
为了再次提高效率,把叶子节点的页和非叶子节点的页分别存储在不同的区中
存放叶子节点的区的集合就算是一个段
.存放非叶子节点的区的集合也算是一个段
也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段
同时在表中刚插入数据时,按完整的区分配给段是十分浪费空间的 所以提出了碎片区（fragment）的概念
.碎片区直属于表空间，并不属于任何一个段
碎片区中的页可以用于不同的目的，比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于
某个段分配存储空间的策略
在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的
当某个段已经占用了32个碎片区页面之后，就会以完整的区为单位来分配存储空间
所以说，段是一些零散的页面以及一些完整的区的集合
每一个索引都对应两个段，每个段都会维护上述的3个链表
FREE链表
同一个段中，所有页面都是空闲的区对应的XDES Entry结构会被加入到这个链表。
注意和直属于表空间的FREE链表区别开了，此处的FREE链表是附属于某个段的。
注意和直属于表空间的FREE链表区别开了，此处的FREE链表是附属于某个段的。
NOT_FULL链表
同一个段中，仍有空闲空间的区对应的XDES Entry结构会被加入到这个链表
FULL链表
同一个段中，已经没有空闲空间的区对应的XDES Entry结构会被加入到这个链表
为了更有效的定位链表,提出了链表基节点(List Base Node)
链表的头、尾节点的位置以及该链表中包含的节点数
一般我们把某个链表对应的List Base Node结构放置在表空间中固定的位置 这样就可以加快定位
若一个表共有两个索引，一个聚簇索引，一个二级索引
那么有4个段
每个段3个链表，再加上表空间的3个链表
所以，总共是15个链表
那么有4个段
每个段3个链表，再加上表空间的3个链表
所以，总共是15个链表
段的结构
段其实不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念，由若干个零散的页面以及一些完整的区组成
像每个区都有对应的XDES Entry来记录这个区中的属性一样
每个段都定义了一个INODE Entry结构来记录一下段中的属性
每个段都定义了一个INODE Entry结构来记录一下段中的属性
系统表空间
总结
https://user-gold-cdn.xitu.io/2019/5/1/16a739f4a99c9a08?imageslim
访问类型
const
通过主键或者唯一二级索引列来定位一条记录的访问方法
只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效
查询速度是常数级别
查询该列为NULL值的情况比较特殊
因为唯一二级索引列并不限制 NULL 值的数量，所以可能访问到多条记录，
也就是说不可以使用const访问方法来执行
也就是说不可以使用const访问方法来执行
采用key IS NULL这种形式的搜索条件最多只能使用ref的访问方法，而不是const的访问方法
ref
搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法
但需要注意的是根据二级索引查询的结果集数量不可太多, 否则回表成本太大,性能可能还不如直接全表查询
如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为ref
ref_or_null
不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来
range
利用索引进行范围匹配的访问方法
index
采用遍历二级索引记录的执行方式,不需要回表
因为聚簇索引包含了所有的数据,所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多
index merge(索引合并)
在一个查询中使用到多个二级索引
在一个查询中使用到多个二级索引
Intersection合并(交集)
二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况
这个好理解，如果只是匹配部分的话，还得需要继续聚簇索引
主键列可以是范围匹配
Union合并(并集)
二级索引列是等值匹配的情况，对于联合索引来说， 在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况
主键列可以是范围匹配
使用Intersection索引合并的搜索条件
Sort-Union合并
SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'
先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并
联合索引替代Intersection索引合并
all
使用全表扫描执行查询的方式
连接
笛卡尔积
结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合
驱动表 被驱动表
两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次
内连接和外连接
对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集
左外连接:选取左侧的表为驱动表
右外连接:选取右侧的表为驱动表
内连接中的WHERE子句和ON子句是等价的
内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接条件时 不会把该记录加入到最后的结果集
对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果
连接的原理
嵌套循环连接（Nested-Loop Join）
驱动表只访问一次，但被驱动表却可能被多次访问， 访问次数取决于对驱动表执行单表查询后的结果集中的记录条数
使用索引加快连接速度
eq_ref
连接查询中对被驱动表使用主键值或者唯一二级索引列的值进行等值查找的查询执行方式
基于块的嵌套循环连接（Block Nested-Loop Join）
因为每次那结果集查询被驱动表都需要从磁盘中将数据加载到内存中,会导致许多重复加载
因此提出了join buffer的空间,将驱动表的结果集放在其中, 每次都被驱动表中读取出一条记录,则和join buffer中的所有记录比较
显著减少被驱动表的I/O代价,减少重复从磁盘上加载被驱动表的代价
显著减少被驱动表的I/O代价,减少重复从磁盘上加载被驱动表的代价
通过启动参数或者系统变量join_buffer_size进行配置,可以加大join buffer的空间,来优化查询
最好不要把*作为查询列表,这样可以节省join buffer空间
成本
执行成本
I/O成本
CPU成本
步骤
根据搜索条件，找出所有可能使用的索引
计算全表扫描的代价
.计算使用不同索引执行查询的代价
.对比各种执行方案的代价，找出成本最低的那一个
调节成本常数
InnoDB 统计数据
永久性的统计数据
非永久性的统计数据
MySQL 5.6.6之后统计数据默认存储在磁盘上
以表为单位来收集和存储统计数据的
都是估计值,而不是准确数值
因为是统计了个别页的数据做为平均值
定期更新统计数据
开启innodb_stats_auto_recalc,当表中改动数据超过10%时,更新统计数据
查询重写/查询优化
子查询优化
in子查询优化
将子查询结果集写入到临时表
写入临时表的记录会被去重
建立基于内存的使用Memory存储引擎的临时表， 而且会为该表建立哈希索引
子查询的结果集非常大,则改为使用存储B+树索引
将子查询结果集中的记录保存到临时表的过程称之为物化
Explain
id
一般来说一个select一个唯一id
如果id相同执行顺序由上至下
因为MySQL在进行优化的时候已经将子查询改成了连接查询，而连接查询的id是一样的
如果id不相同，id的序号会递增，id值越大优先级越高，越先被执行
select_type
simple：不包括union和子查询的查询都算simple类型
primary：包括union，union all，其中最左边的查询即为primary。
union：包括union，union all，除了最左边的查询，其他的查询类型都为union
table
显示这一行是关于哪张表的
type：访问方法
const：主键或唯一二级索引列与常量进行等值匹配
ref：普通二级索引与常量进行等值匹配
ref_or_null：普通二级索引与常量进行等值匹配，该索引可能是null
range：范围区间的查询
all：全表扫描
possible_keys
对某表进行单表查询时可能用到的索引
key
经过查询优化器计算不同索引的成本，最终选择成本最低的索引
rows
如果使用全表扫描，那么rows就代表需要扫描的行数
如果使用索引，那么rows就代表预计扫描的行数
filtered
如果全表扫描，那么filtered就代表满足搜索条件的记录的满分比
如果是索引，那么filtered就代表除去索引对应的搜索，其他搜索条件的百分比
子主题
optimizer trace 表
MySQL 5.6以及之后的版本
可以让我们方便的查看优化器生成执行计划的整个过程
InnoDB 的 Buffer Pool(缓冲池)
InnoDB存储引擎在处理客户端的请求时，当需要访问某个页的数据时， 就会把完整的页的数据全部加载到内存中
即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中
free链表（或者说空闲链表）
如何快速确定某个表空间下某个页是否依旧被缓存
用表空间号 + 页号作为key， 缓存页作为value创建一个哈希表
脏页（英文名：dirty page）
修改了Buffer Pool中某个缓存页的数据，那它就和磁盘上的页不一致
创建了一个flush链表来存储脏页
结构与free链表相似
刷新脏页到磁盘
从LRU链表的冷数据中刷新一部分页面到磁盘
从flush链表中刷新一部分页面到磁盘
LRU链表
Least Recently Used
Least Recently Used
预读（英文名：read ahead）
InnoDB认为执行当前的请求可能之后会读取某些页面， 就预先把它们加载到Buffer Pool中
线性预读
随机预读
按照某个比例将LRU链表分成两半的
一部分存储使用频率非常高的缓存页，所以这一部分链表也叫做热数据，或者称young区域
另一部分存储使用频率不是很高的缓存页，所以这一部分链表也叫做冷数据，或者称old区域。
可能降低Buffer Pool的两种情况
加载到Buffer Pool中的页不一定被用到
当磁盘上的某个页面在初次加载到Buffer Pool中的某个缓存页时，该缓存页对应的控制块会被放到old区域的头部
如果非常多的使用频率偏低的页被同时加载到Buffer Pool时，可能会把那些使用频率非常高的页从Buffer Pool中淘汰掉。
在对某个处在old区域的缓存页进行第一次访问时就在它对应的控制块中记录下来这个访问时间，如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该页面就不会被从old区域移动到young区域的头部，否则将它移动到young区域的头部
为了避免频繁移动节点
只有被访问的缓存页位于young区域的1/4的后边，才会被移动到LRU链表头部
多个Buffer Pool实例
在Buffer Pool特别大而且多线程并发访问特别高的情况下，单一的Buffer Pool可能会影响请求的处理速度
MySQL 5.7.5之后支持在系统运行期间调整Buffer Pool大小
事务
ACID
原子性（Atomicity）
隔离性（Isolation）
一致性（Consistency）
持久性（Durability）
隔离性（Isolation）
一致性（Consistency）
持久性（Durability）
开启事务
begin, start
提交事务
COMMIT
手动中止事务
ROLLBACK
自动提交
如果我们不显式的使用START TRANSACTION或者BEGIN语句开启一个事务，
那么每一条语句都算是一个独立的事务，这种特性称之为事务的自动提交
那么每一条语句都算是一个独立的事务，这种特性称之为事务的自动提交
隐式提交
因为某些特殊的语句而导致事务提交的情况称为隐式提交
保存点
定义保存点
SAVEPOINT 保存点名称
回滚到某个保存点
ROLLBACK [WORK] TO [SAVEPOINT] 保存点名称
删除某保存点
RELEASE SAVEPOINT 保存点名称
redo日志
目的是想让已经提交的事务对数据的修改是永久的，就算他重启，数据也能恢复出来。
log buffer（日志缓冲区）
为了解决磁盘速度过慢的问题，redo日志不能直接写入磁盘，咱先整一大片连续的内存空间给他放数据。这一大片内存就叫做日志缓冲区
redo日志刷盘时机
log buffer空间不足
事务提交时
如果数据提交了，我们是可以不把数据提交到磁盘的，但为了保证持久性，必须 把修改这些页面的redo日志刷新到磁盘。
checkpoint
redo日志文件组
redo日志是先写入log buffer，之后才会被刷新到磁盘的redo日志文件
将缓冲区log buffer里面的redo日志刷新到这个两个文件(ib_logfile0和ib_logfile1)里面，他们写入的方式 是循环写入的
checkpoint
日志被刷新到磁盘中的话，那么这些刷新后的日志是可以被覆盖的
计算当前系统可以被覆盖的redo日志对应的日志序列号（Log Sequence Number）最大值是多少，小于这个值的都是可以覆盖的
undo日志
前滚和回滚
前滚：当实例崩溃时，可以使用redo从以前正常的点前滚到崩溃点。
回滚
数据在没有commit前，是随时从内存中写入到表数据块的，属于脏数据
构造的表数据块中，有已修改的脏数据但未提交，就需要利用前滚中构造的undo数据块里的信息来undo撤销还原
事务隔离级别和MVCC
事务并发执行遇到的问题
脏写
一个事务修改了另一个未提交事务修改过的数据
脏读
一个事务读到了另一个未提交事务修改过的数据
不可重复读（Non-Repeatable Read）
一个事务只能读到另一个已经提交的事务修改过的数据， 并且其他事务每对该数据进行一次修改并提交后，
该事务都能查询得到最新值
该事务都能查询得到最新值
幻读
一个事务先根据某些条件查询出一些记录， 之后另一个事务又向表中插入了符合这些条件的记录，
原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来
原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来
SQL标准中的四种隔离级别
READ UNCOMMITTED：未提交读
READ COMMITTED：已提交读
REPEATABLE READ：可重复读
MySQL的默认隔离级别为REPEATABLE READ
SERIALIZABLE：可串行化
MVCC原理
Multi-Version Concurrency Control ，多版本并发控制）
对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列
trx_id：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id赋值给trx_id隐藏列。
roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，
然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息
然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息
每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性，可以将这些undo日志都连起来，串成一个链表
随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值
生成ReadView的时机不同，READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了
锁
锁的结构
trx信息：代表这个锁结构是哪个事务生成的。
is_waiting：代表当前事务是否在等待。
共享锁和独占锁
共享锁，英文名：Shared Locks，简称S锁。在事务要读取一条记录时，需要先获取该记录的S锁。
独占锁，也常称排他锁，英文名：Exclusive Locks，简称X锁。在事务要改动一条记录时，需要先获取该记录的X锁
S锁和S锁是兼容的，S锁和X锁是不兼容的，X锁和X锁也是不兼容的
InnoDB中的表级锁
意向共享锁，英文名：Intention Shared Lock，简称IS锁。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁
意向独占锁，英文名：Intention Exclusive Lock，简称IX锁。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁
表级别的AUTO-INC锁
采用AUTO-INC锁，也就是在执行插入语句时就在表级别加一个AUTO-INC锁
采用一个轻量级的锁，在为插入语句生成AUTO_INCREMENT修饰的列的值时获取一下这个轻量级锁'
可能会造成不同事务中的插入语句为AUTO_INCREMENT修饰的列生成的值是交叉的， 在有主从复制的场景中是不安全的
InnoDB中的行级锁
Record Locks
正经记录锁
Gap Locks
仅仅是为了防止插入幻影记录而提出的
Next-Key Locks
本质就是一个正经记录锁和一个gap锁的合体
Insert Intention Locks
插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁
隐式锁
一个事务对新插入的记录可以不显式的加锁（生成一个锁结构), 而是利用事务Id,别的事务在对这条记录加S锁或者X锁时，
由于隐式锁的存在，会先帮助当前事务生成一个锁结构，然后自己再生成一个锁结构后进入等待状态
由于隐式锁的存在，会先帮助当前事务生成一个锁结构，然后自己再生成一个锁结构后进入等待状态
InnoDB锁的内存结构
锁所在的事务信息
不论是表锁还是行锁，都是在事务执行过程中生成的，哪个事务生成了这个锁结构，这里就记载着这个事务的信息。
索引信息
对于行锁来说，需要记录一下加锁的记录是属于哪个索引的。
表锁／行锁信息
type_mode
锁的模式（lock_mode）
锁的类型（lock_type）
行锁的具体类型（rec_lock_type）
补充
1. B树和B+树的区别
数据库系统将索引的一个节点大小设置为页的大小，使得一次I/O就能完成载入一个节点。
需要减少IO次数，而B树的特征就是矮胖，所以选择了B树做索引
B树
叶子节点具有相同的深度，叶子节点的指针为空
每个关键字都保存数据
所有关键字不重复，也就是说只能存在一个节点上，并且所有关键字和数据都分布在整棵树中
搜索有可能在非叶子节点就结束了
节点中的数据从左到右递增排列
搜索性能相当于在关键字全集中做一次二分查找
B+树
非叶子节点不存储数据，只用来做索引
所有数据都保存在了叶子节点
叶子节点用指针连接，提高连续访问性能
查询数据需要三次IO操作
0 条评论
下一页
