mysql导读
2021-01-23 18:25:55 34 举报AI智能生成
登录查看完整内容
解析mysql的一些秘密，适合小白和进阶阅读
作者其他创作
大纲/内容
mysql导读
安装与使用
1.下载源代码编译安装
2.使用官方安装包进行安装
3.启动
启动服务器进程
mysqld
./bin/mysqld
/usr/local/mysql/bin/mysqld
将目录路径加入到环境变量PATH中，避免每次输入路径
mysqld_safe
mysql.server
启动客户端进程：mysql -h主机名 -u用户名 -p密码
-h 服务器域名或ip地址
-u 用户名
-p 密码
退出客户端：quit/exit
4.客户端与服务器连接的过程
TCP/IP
mysql服务器启动默认端口3306
命名管道和共享内存
unix域套接字文件
5.服务器处理客户端请求
连接管理
解析与优化
查询缓存
语法解析
查询优化
存储引擎
InnoDB
MEMORY
MyISAM
...
启动项和配置文件
命令行上使用选项
mysqld --skip-networking
mysqld --default-storage-engine=MyISAM
配置文件中使用选项
配置文件路径
系统变量
查看系统变量：show variables [like 匹配的模式]
设置系统变量
通过启动选项设置
服务器程序运行过程中设置
设置不同作用范围的系统变量
查看不同作用范围的系统变量
状态变量
SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式];
字符集
字符集是某个字符范围的编码规则
字符集中的字符有特定的比较规则
查看支持的字符集合比较规则
四种级别字符集合比较规则
服务器级别
数据库级别
表级别
列级别
字符集转换
客户端使用操作系统的字符集编码请求字符串，向服务器发送的是经过编码的一个字节串。
服务器将客户端发送来的字节串采用character_set_client代表的字符集进行解码，将解码后的字符串再按照character_set_connection代表的字符集进行编码。
如果character_set_connection代表的字符集和具体操作的列使⽤的字符集一致，则直接进行相应操作，否则的话需要将请求中的字符串从character_set_connection代表的字符集转换为具体操作的列使⽤的字符集之后再进行操作
将从某个列获取到的字节串从该列使用的字符集转换为character_set_results代表的字符集后发送到客户端。
客户端使用操作系统的字符集解析收到的结果集字节串
保持三个变量一致：set names utf8
InnoDB记录结构
InnoDB页
InnoDB将数据存储到磁盘上
将数据划分成若干页，，以页为单位作为磁盘和内存的交互的基本单位，大小为16kb
行格式
Compact
记录的额外信息
变长字段长度列表
NULL值列表
统计表中允许存储NULL的列有哪些
如果表中没有允许存储NULL的列，则NULL值列表也不存在
将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列
NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0
记录头信息
预留位1
预留位2
delete_mask
min_rec_mask
n_owned
heap_no
record_type
next_record
记录的真实数据
隐藏列
row_id
transaction_id
roll_pointer
行溢出
记录中的数据太多产生的溢出
行溢出的临界点
Redundant
字段长度偏移列表，如Compact不同点
有了变长两个字，意味着Redundant行格式会把该条记录中所有列（包括隐藏列）的长度信息都按照逆序存储到字段长度偏移列表
多了个偏移两个字，这意味着计算列值长度的方式不像Compact行格式那么直观，它是采用两个相邻数值的差值来计算各个列值的长度。
预留位2\t
min_rec_mask\t
n_owned\t
heap_no\t
n_field\t
1byte_offs_flag\t
Dynamic
Compressed
页结构
InnoDB数据页结构
File Header文件头部
FIL_PAGE_SPACE_OR_CHKSUM：页的校验和（checksum值
FIL_PAGE_OFFSET：页号
FIL_PAGE_PREV：上一页的页号
FIL_PAGE_NEXT：下一页的页号
FIL_PAGE_LSN：页被最后修改时对应的日志序列位置（英文名是：Log Sequence Number）
FIL_PAGE_TYPE：页类型
FIL_PAGE_UNDO_LOG：undo日志类型
FIL_PAGE_INODE：段信息结点
FIL_PAGE_TYPE_FSP_HDR：系统表空间头部信息
FIL_PAGE_TYPE_XDES：扩展描述也
FIL_PAGE_INDEX:索引页，也就是我们说的数据页
FIL_PAGE_FILE_FLUSH_LSN：仅在系统表空间的1个页中定义，代表文件至少被刷新到了对应的LSN值
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID：页属于哪个表空间
Page Header页面头部
PAGE_N_DIR_SLOTS 2：在页目录中的槽数量
PAGE_HEAP_TOP：还未使用的空间最小地址，也就是说从该地址之后就是Free Space
PAGE_N_HEAP：本页中的记录的数量（包括最小和最大记录以及删除记录）
PAGE_FREE:第1个已经标记为删除的记录地址（各个已删除的记录通过next_record也会组成1个单链表，这个单链表中的记录可以被重新利用）
PAGE_GARBAGE ：已删除记录占用的字节数
PAGE_LAST_INSERT：最后插入的位置
PAGE_DIRECTION：记录的插入方向
PAGE_N_DIRECTION：1个方向连续插入的记录数量
PAGE_N_RECS：页中记录数量(不包括最大最小和删除)
PAGE_MAX_TRX_ID：修改当页的最大事务id
PAGE_LEVEL：该页在B+树中所处的层级
PAGE_INDEX_ID:索引Id，也即该页属于哪个索引
PAGE_BTR_SEG_LEAF：B+树叶页段的头部信息，仅在B+树的Root页定义
PAGE_BTR_SEG_TOP ：B+树非叶子段的头部信息，仅在B+树的Root页定义
Infimum+Supremum最小记录和最大记录
User Records用户记录
Free Space空闲空间
Page Directory页面目录
将所有正常记录划分几个组
每个组的最后1条记录（也就是组内最后的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录
插入规则
初始情况下1个数据页中只有最小记录和最大记录两条记录，它们分属于两个分组
之后每插入1条记录，都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的n_owned值加1，表示本组内又添加了1条记录，直到该组中的记录数等于8个。
在1个组中的记录数等于8个后再插入1条记录时，会将组中的记录拆分成两个组，1个组中4条记录，另1个5条记录。这个过程会在页目录中新增1个槽来记录这个新增分组中最大的那条记录的偏移量。
File Trailer文件尾部
页的校验和
页面被最后修改时对应的日志序列位置（LSN）
特点
不论我们怎么对页中的记录做增删改操作，InnoDB始终会维护一条记录的单链表，链表中的各个节点是按照主键值由小到大的顺序连接起来的
B+树索引
目录项
key：页的用户记录中的最小主键值
value:页号，也即page_no
加上目录项的结构
聚簇索引
使用记录主键值的大小进行记录和页的排序
页内记录时按照主键的大小顺序排成一个单向链表
各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
存放目录项记录的页分为不同的层次，在统一层次中的页也是根据目录项记录的主键大小顺序排列
B+树的叶子节点存储的是完整的用户记录
二级索引
使用非主键列进行记录和页的排序
页内记录按照非逐渐列大小顺序排成个单项链表
各个存放用户记录的页，比如C2列，按照C2列大小顺序排成双向链表
存放目录项记录的页分成多个层次，同一层次中的页也是根据页中目录项记录的C2大小顺序排成双向链表
B+树的叶子节点不是用户的完整记录，而是C2+主键这两个列的值
查找过程
确定目录项记录页
根据目录项记录页确定用户记录真实所在的页
在真实存储用户记录的页中定位到具体的记录
二级索引的叶子节点中值存储了主键值和C2，索引需要再根据主键值去聚簇索引中再查找一边完整的用户记录
联合索引
每条目录项记录都由c2、c3、主键值、页号这四个部分组成，各条记录先按照c2列的值排序，如果记录的c2列相同，则按照c3列的值排序。
B+树叶子节点处的用户记录由c2、c3和主键c1列组成。
索引使用
全值匹配
匹配左边的列
匹配列前缀
匹配范围值
精确匹配某列并范围匹配另一列
用于排序
回表的代价
根据不连续的主键值到聚簇索引访问完整用户记录可能分布在不同的数据页中，这种读取是随机IO
回表的记录越多，使用二级索引的性能越低
覆盖索引：最好在查询列表中只包含索引列
挑选索引
只为用于搜索、排序或分组的列创建索引
某列不重复数据的个数越多建立索引效果越好
索引列的类型尽可能小，如能使用INT就不要用BIGINT
让索引列在比较表达式中单独出现
让主键值顺序插入
数据目录
创建数据库
mysql
nformation_schema
performance_schema
sys
创建表
表结构：表明.frm
表数据
系统表空间：ibdata1
独立表空间
文件系统对数据库的影响
数据库名称和表名称不得超过文件系统所允许的最大长度
数据库名和表名中除了数字和阿里字母意外的字符在文件名里都映射成@+编码值的形式作为文件吗
文件长度受文件系统最大长度限制
InnoDB的表空间
独立表空间结构
区
XDES Entry结构
Segmengt Id
List Node
链表基结点
第一组的前3个页面类型固定
FSP_HDR
File Header
File Space Header
字段详情
List Base Node for SEG_INODES_FULL List和List Base Node for SEG_INODES_FREE List
XDES Entry
Empty Space
File Trailer
IBUF_BITMAP
INODE
结构
List Node for INODE Page List
SEG_INODES_FULL
SEG_INODES_FREE
创建一个段时
其余组开始两个页面类型固定
XDES
IBUF_BITMAP类型
区的状态
FREE空闲的区
FREE_FRAG有剩余空间的碎片区
FULL_FRAG没有剩余空间的碎片区
FSEG附属于某个段的区
分配区
段
Segmeng ID
NOT_FULL_N_USED
3个List Base Node
Magic Number
Fragment Array Entry
Free链表
NOT_FULL链表
FULL链表
系统表空间
不同点
SYS
内部系统表
内部系统表中4个表是表中表，很重要
单表访问方法
全表扫描
索引查询
const
ref
ref or null
range
index
all
索引合并
Intersection交集
Union合并
Sort-Union合并
两表连接的原理
简介
内连接和外连接
外连接
左外连接
右外连接
where子句的过滤条件
on子句的过滤条件
内连接
连接的原理
嵌套循环连接
使用索引加快连接速度
基于块的嵌套循环连接（Block Nested-Loop Join）
join buffer
MySQL基于成本的优化
单表查询的成本
根据搜索条件，找出所有可能使用的索引
计算全表扫描的代价
计算使用不同索引执行查询的代价
对比各种执行方案的代价，找出成本最低的那个
基于统计数据的成本计算
索引统计数据估算
连接查询的成本
单次查询驱动表的成本
如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条
如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条
多次查询被驱动表的成本
对外连接来说，驱动表是固定的，所以分别为驱动表和被驱动表选择成本最低的访问方法
不同表作为驱动表最终的查询成本可能是不同的，也就是考虑最优的表连接顺序
分别为驱动表和被驱动表选择成本最低的访问方法
InnoDB统计数据的收集
0 条评论
回复 删除
下一页
