一、Java进阶-MySQL-基础
1.1 客户端连接
image.png
1.2 字符集和比较规则
SHOW CHARSET; //字符集
SHOW COLLATION [LIKE 匹配的模式]; //排序规则
image.png
- 服务器级别
SHOW VARIABLES LIKE 'character_set_server'
SHOW VARIABLES LIKE 'collation_server'
- 数据库级别
SHOW VARIABLES LIKE 'character_set_database'
SHOW VARIABLES LIKE 'collation_database'
- 表级别
- 列级别
image.png
image.png
1.3 InnoDB记录存储结构
InnoDB页：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为16KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。
InnoDB行格式：我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。1）Compact 2）Redundant 3）Dynamic 4）Compressed
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
ALTER TABLE 表名 ROW_FORMAT=行格式名称
CREATE TABLE record_format_demo (
c1 VARCHAR(10),
c2 VARCHAR(10) NOT NULL,
c3 CHAR(10),
c4 VARCHAR(10)
) CHARSET=ascii ROW_FORMAT=COMPACT;
INSERT INTO record_format_demo(c1, c2, c3, c4) VALUES('aaaa', 'bbb', 'cc', 'd'),
('eeee', 'fff', NULL, NULL);
image.png
记录的额外信息：
- 变长字段长度列表（倒序）
image.png
image.png
假设某个字符集中表示一个字符最多需要使用的字节数为W，也就是使用SHOW CHARSET语句的结果中的Maxlen列。对于变长类型VARCHAR(M)来说，这种类型表示能存储最多M个字符（注意是字符不是字节），所以这个类型能表示的字符串最多占用的字节数就是M×W。
如果该可变字段允许存储的最大字节数（M×W）超过255字节并且真实存储的字节数（L）超过127字节，则使用2个字节，否则使用1个字节。
变长字段长度列表中只存储值为非NULL的列内容占用的长度，值为NULL的列的长度是不储存的。
01 03 04
image.png
- NULL值列表（可能为NULL的列/倒序）
二进制位的值为1时，代表该列的值为NULL；二进制位的值为0时，代表该列的值不为NULL。NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0。
image.png
image.png
image.png
- 记录头信息（5个字节）
image.png
delete_mask：标记着当前记录是否被删除，占用1个二进制位，值为0的时候代表记录并没有被删除，为1的时候代表记录被删除掉了。这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。
min_rec_mask：B+树的每层非叶子节点中的最小记录都会添加该标记。
heap_no：表示当前记录在本页中的位置。自动给每个页里边儿加了两个记录，由于这两个记录并不是我们自己插入的，所以有时候也称为伪记录或者虚拟记录。这两个伪记录一个代表最小记录，一个代表最大记录。并不存放在页的User Records部分，他们被单独放在一个称为Infimum + Supremum的部分。
image.png
image.png
record_type：表示当前记录的类型，一共有4种类型的记录，0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录。
next_record：表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。下一条记录指得并不是按照我们插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录。而且规定 Infimum记录（也就是最小记录） 的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum记录（也就是最大记录）。
image.png
image.png
记录的真实数据：
隐藏列：
image.png
优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。
image.png
对于CHAR(M)类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。变长字符集的CHAR(M)类型的列要求至少占用M个字节，而VARCHAR(M)却没有这个要求。
- 行溢出
一个页一般是16KB，当记录中的数据太多，当前页放不下的时候，会把多余的数据存储到其他页中，这种现象称为行溢出。
对于Compact和Redundant行格式来说，如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页。
image.png
image.png
1.4 InnoDB数据页结构
InnoDB数据页结构示意图：
image.png
image.png
数据插入过程：
Free Space/User Records。
image.png
Page Directory（页目录）：
- 将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。
- 每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录。
- 将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方，这个地方就是所谓的Page Directory，也就是页目录。页面目录中的这些地址偏移量被称为槽（英文名：Slot），所以这个页面目录就是由槽组成的。
image.png
对于最小记录所在的分组只能有1条记录，最大记录所在的分组拥有的记录条数只能在1-8条之间，剩下的分组中记录的条数范围只能在是4-8条之间。
image.png
在一个数据页中查找指定主键值的记录的过程分为两步：
- 通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录。
- 通过记录的next_record属性遍历该槽所在的组中的各个记录。
Page Header（页面头部）：56个字节，专门针对数据页记录的各种状态信息。
image.png
File Header（文件头部）：38个字节
image.png
FIL_PAGE_PREV和FIL_PAGE_NEXT：
image.png
File Trailer：为了检测一个页是否完整（也就是在同步的时候有没有发生只同步一半的尴尬情况），每个页的尾部都加了一个File Trailer部分。为保证从内存中同步到磁盘的页的完整性，在页的首部和尾部都会存储页中数据的校验和和页面最后修改时对应的日志序列位置（LSN）值，如果首部和尾部的校验和和LSN值校验不成功的话，就说明同步过程出现了问题。
1.5 B+树索引
目录项记录：
- 目录项记录的record_type值是1，而普通用户记录的record_type值是0。
- 目录项记录只有最小主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外还有InnoDB自己添加的隐藏列。
- 只有在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask值为1，其他别的记录的min_rec_mask值都是0。
聚簇索引：
image.png
二级索引：
image.png
联合索引：
image.png
一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。
二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：1）索引列的值 2）主键值 3）页号。二级索引的叶子节点包含的用户记录由索引列+主键组成。
1.6 B+树索引的使用
查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引+回表的方式。
最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。
如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。
image.png
1.7 MySQL 的数据目录
MySQL服务器程序在启动时会到文件系统的某个目录下加载一些文件，之后在运行过程中产生的数据也都会存储到这个目录下的某些文件中，这个目录就称为数据目录。
SHOW VARIABLES LIKE 'datadir';
每一个表空间可以被划分为很多很多很多个页，我们的表数据就存放在某个表空间下的某些页里。
- 系统表空间（system tablespace）
- 独立表空间（file-per-table tablespace）
- 其他类型的表空间
在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间。
1.8 InnoDB的表空间
表空间被划分为许多连续的区，每个区默认由64个页组成，每256个区划分为一组，每个组的最开始的几个页面类型是固定的。
image.png
一个区就是在物理位置上连续的64个页。叶子节点有自己独有的区，非叶子节点也有自己独有的区。存放叶子节点的区的集合就算是一个段（segment），存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段。
段：某些零散的页面以及一些完整的区的集合。
区的四种状态：
image.png
表空间是由若干个区组成的，每个区都对应一个XDES Entry的结构，直属于表空间的区对应的XDES Entry结构可以分成FREE、FREE_FRAG和FULL_FRAG这3个链表；每个段可以附属若干个区，每个段中的区对应的XDES Entry结构可以分成FREE、NOT_FULL和FULL这3个链表。每个链表都对应一个List Base Node的结构，这个结构里记录了链表的头、尾节点的位置以及该链表中包含的节点数。
1.9 一些工具
数据库碎片整理：
#查询数据库整体情况
select
table_schema,
table_name,
table_rows,
truncate(data_free/1024/1024, 2) as '碎片容量(MB)',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc limit 100;
alter table '数据库表名' engine = innodb;
SELECT table_schema,
table_name,
table_rows,
truncate(data_free/1024/1024,
2) AS '碎片容量(MB)', truncate(data_length/1024/1024, 2) AS '数据容量(MB)', truncate(index_length/1024/1024, 2) AS '索引容量(MB)'
FROM information_schema.tables
where table_name = '数据库表名' and table_schema = 'dos'
ORDER BY data_length desc, index_length DESC limit 100;
网友评论