《MySQL是怎么运行的》读书笔记

第一章 初识MySQL

MySQL的服务器程序直接与要存储的数据打交道，多个客户端程序可以连接到这个服务器程序，向服务器发送增删改查的请求，服务器程序根据这些请求对存储的数据进行相应的处理。与微信一样，MySQL的每一个客户端都需要使用用户名和密码才能登陆服务器，而且只有在登录之后才能向服务器发送某些请求来操作数据。MySQL的日常使用场景是下面这样的。

1. 启动MySQL服务器程序。
2. 启动MySQL客户端程序，并连接到服务器程序
3. 在客户端程序中输入命令语句，并将其作为请求发送给服务器程序。服务器程序在收到这些请求后，根据请求内容来操作具体的数据并将处理结果返回给客户端。

众所周知，现在计算机的功能都很强大，一台计算机上可以运行多个程序。比如微信，QQ，LoL.计算机上运行的每一个程序也称为一个进程。运行过程中的MySQL服务器程序和客户端程序在本质上来说都算是计算机中的进程，其中代表MySQL服务器程序的进程成为Mysql数据库实例（instance）。

总结

MySQL采用客户端/服务器架构，用户通过客户端程序发送增删改查请求，服务器程序收到请求后处理，并且把处理结果返回给客户端。
MySQL安装目录的bin目录下存放了许多可执行文件，其中有一些是服务器程序（比如mysqld,mysqld_safe），有一些是客户端程序（比如mysql，mysqladmin）.
在类unix系统上启动服务器程序的方式有下面这些:

• msqld:
• mysqld_safe:
• mysql.server:
• mysql_multi。

在windows系统上启动服务器的方式有下面这些：

• mysqld：
• 将mysqld注册为Windows服务

启动客户端程序时常用的语法如下：

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

客户端进程和服务器进程通信时采用下面几种方式：

• TCP/IP
• 命名管道或共享内存
• UNIX域套接字

以查询请求为例，服务器程序在处理客户端发送过来的请求时，大部分为以下几个部分。

• 连接管理：主要负责连接的建立与信息的认证。
• 解析与优化：主要进行查询缓存，语法解析，查询优化。
• 存储引擎：主要负责读取和写入地城表中的数据。

MySQL支持的存储引擎有好多种，它们的功能各有侧重，我们常用的就是InnoDB和MyISAM，其中InnoDB为服务器程序的默认存储引擎。

• 查看当前服务器程序支持的存储引擎：show engines；
• 创建表时指定表的存储引擎： create table 表明（） engine = 存储引擎名称；
• 修改表的存储引擎：alter table 表明 engine = 存储引擎名称；

第二章 MySQL启动选项和系统变量

总结

1.启动选项可以调整服务器启动后的一些行为。它们可以在命令行中指定，也可以将它们写入配置文件中。在命令行中指定启动选项时，可以将各个启动选项写到一行中，每个启动选项名称前面添加–，而且各个启动选项之间使用空白字符隔开。有一些启动选项不需要指定选项值，有一些选项需要指定选项值。在命令行中指定有值的启动选项时要注意，选项名，=，选项值之间不可以有空白字符。一些常用的启动选项具有段形式的选项名，使用短形式选项时在选项名前只加一个短划线 - 前缀。

//在命令行上使用选项 比如我们想在启动服务器程序时就禁止各客户端使用TCP/IP网络进行通信 
  mysql --skip-networking 或者 mysql --ship_networking
//在使用mysql来启动客户端程序把服务器主机名指定为127.0.0.1的话会显示连接失败：
  mysql -h127.0.0.1 -uroot -p
//在举一个例子 如果我们在创建表时没有显式指定表的存储引擎，那就会默认使用InnoDB作为表的存储引擎。如果我们想改变表的默认存储引擎可以输入下面的启动服务器的命令：
mysqld --default-storage-engine=MyISAM

2.服务器程序在启动时将会在一些给定的路径下搜索配置文件，不同操作系统的搜索路径是不同的。
配置文件中的启动选项被划分为若干个组，每个组有一个组名，用中括号[]扩起来。在配置文件中指定的启动选项不允许添加 – 前缀，并且每行至指定一个选项，而且等号 = 周围可以有空白字符。我们可以使用#来添加注释。
3.系统变量是服务器程序中维护的一些变量，这些变量影响着服务器的行为。修改系统变量的方式如下。

• 在服务器启动时通过添加相应的启动选项进行修改
• 在运行时使用set语句修改，下面两种方式都可以：
• set [global|session] 系统变量名 = 值；
• set[@@(global|session).]系统变量名 = 值；

//比如我们想在服务器的运行过程中把作用范围为global的系统变量default_storage_engine的值
//修改为MyISAM,也就是想让之后新连接到服务器的客户端都用MyISAM作为默认的存储引擎：
 - set global default_storage_engine = MyISAM;
 - set @@global.default_storage_engine = MyISAM;
//如果只想对本客户端生效，也可以选择下面3条语句中的任意一条来设置。
 - set session default_storage_engine = MyISAM;
 - set @@session.default_storage_engine = MyISAM;
 - set default_storage_engine = MyISAM;
//查看系统变量
 - SHOW [GLOBAL|SESSION] VARIABLES [LIKE %]
 注意事项：
 并不是所有的系统变量都具有GLOBAL和SESSION的作用范围。
 - 有一些系统变量只具有GLOBAL作用范围，比如max_connections，
 - 它表示服务器程序支持同时最多有多少个客户端程序进行连接。
有一些系统变量只具有session作用范围,比如insert_id,它表示在对某个包含AUTO_INCREMENT列的表进行插入时，该列初始的值。

4.状态变量是用来显示服务器程序运行状态的，我们可以使用下面的命令来查看，而且只能查看：
SHOW [GLOBAL | SESSION] STATUS [LIKE 匹配的模式]；

第三章 字符集与比较规则（排序规则）

总结

1.字符集指的是某个字符范围的编码规则，包括不限于以下几种：

 - ASCII 字符集：共收录了128个字符，包括空格,标点符号,数字,大小写字母和一些不可见字符。
   由于ASCII字符集总共才128个字符，所以可以使用一个字节（8位）来进行编码。
 - ISO 8859-1 字符集：共收录256个字符，它在ASCII字符集的基础上有扩充了128个西欧常用字符（包括德法两国的字母）。ISO 8859-1 字符集也可以使用一个字节来进行编码。（别名：Latin1）。
 - GB2312 字符集：收录了汉字及拉丁字母，希腊字母，平假名以及片假名字母，俄语西里尔字母，收录汉字6763个，收录其他文字符号682个。这种字符集同时兼容ASCII字符集，所以在编码上有一些奇怪：如果该字符在ASCII字符集上，则采用一个字节编码；否则采用两个字节编码。
 - GBK字符集：GBK字符集只是在收录的字符范围上对GB2312字符集进行了扩充，编码方式兼容GB2312字符集。
 - UTF-8 字符集：几乎收录了当今世界各个国家/地区使用的字符，而且还在不断扩充。这种字符集兼容ASCII字符集，采用边长编码方式，编码一个字符时需要使用1~4字节。
MySQL中的utf8为阉割过的UTF-8字符集，只使用1~3个字节表示字符。（utf8mb3) 
      utf8mb4:正宗的UTF-8字符集，使用1~4个字节表示字符。

2.比较规则是对某个字符集中的字符比较大小的一种规则，也叫排序规则。
MySQL中，一个字符集可以有若干种比较规则，其中有一个默认的比较规则。一个比较规则必须对应一个字符集。

//比较规则名称后缀英文释义及描述
 - _ai : accent insensitive 不区分重音
 - _as ：accent sensitive 区分重音
 - _ci : case_insensitive 不区分大小写
 - _cs : case_sensitive 区分大小写
 - _bin : binary 以二进制方式比较
//在执行SHOW COLLATION语句中，default列的值为YES的比较规则就是该字符集的默认比较规则，比如utf8字符集默认的比较规则就是utf8_general_ci。

3.在MySQL中查看支持的字符集与比较规则的语句如下：

• show (character set | charset) [like 匹配的模式]；
• show collation [like 匹配的模式]；
  4.MySQL有4个级别的字符集和比较规则，具体如下。

 1. 服务器级别：character_set_server 表示服务器级别的字符集，collation_server表示服务器级别的比较规则。
 2. 数据库级别：create database 数据库名
               character set 字符集名称
               collate 比较规则名称
// character_set_database 表示当前数据库的字符集，collation_datebase表示当前数据库的比较规则。
//这两个系统变量只用来读取，修改他们并不会改变当前数据库的字符集和比较规则。
//如果没有指定当前数据库，则这两个系统变量与服务器级别相应的系统变量具有相同的值。
3.MySQL有4个级别的字符集和比较规则
服务器级别：
character_set_server 表示服务器级别的字符集，
collation_server表示服务器级别的比较规则。
数据库级别：
创建和修改数据库时可以指定字符集和比较规则：
character_set_database表示当前数据库的字符集
collation_database表示当前数据可的比较规则。这两个系统变量只用来读取，修改他们并不会改变当前数据库的字符集和比较规则。如果没有指定当前数据库，则这两个系统变量与服务器级别相应的系统变量具有相同的值。
表级别：
create table (列的信息) 
     character set 字符集名称，
     collate 比较规则名称；
列级别：
 create table 表名（
      列名 字符串类型 character set 字符集名称 collate 比较规则名称...)


发送请求到接受响应的过程中发生的字符集转换如下所示。
 1. 客户端发送的请求字节序列是采用哪种字符集进行编码的。
 这一步骤主要取决于当前操作系统当前使用的字符集；对windows操作系统来说
 还与客户端启动时设置的default-character-set启动项有关。 
 2. 服务器接受字节序列后会认为它是采用哪种字符集进行编码的
 这取决于系统变量character_set_client的值
 3. 服务器在运行过程中会把请求的字节序列转换为以哪种字符集编码的字节序列
 这取决于系统变量character_set_connection的值
 4. 服务器在向客户端返回字节序列时采用哪种字符集进行编码。
 这取决于系统变量character_set_result的值
 5. 客户端在接收到响应字节序列后怎么把它们写道黑框框中的。
 这取决于当前操作系统所使用的字符集；windows操作系统来说还与客户端启动时设置的dafault-character-set有关。
 
 比较规则通常用来比较字符串的大小预计对某些字符串进行排列。

第四章 InnoDB记录的存储结构

总结

从简单的来说：页是InnoDB中磁盘和内存交互的基本单位，也是InnoDB管理存储空间的基本单位，默认大小为16KB。
指定和修改行格式的语法如下：
create table 表名（列的信息） row_format = 行格式名称；
alter table 表名 row_format = 行格式名称；

InnoDB 目前定义了4种行格式：

1. COMPAC行格式：
   

1.记录的额外信息
 （1）变长字段长度列表
   MySQL支持一些变长类型：varchar,varbinary,各种text类型，blob类型。
  变长字段占用的存储空间为两部分：真正的数据内容，该数据占用的字节数
  在COMPACT行格式中，所有变长字段的真实数据占用的字节数都存在记录的
  开头位置，从而形成一个变长字段长度列表，各变长字段的真实数据占用的字节数
  按照列的顺寻逆序存放。
  变长字段其真实内容占用的字节数可能用1个字节或者2个字节来表示：
 - 假设某个字符集中最多需要W字节来表示一个字符，比如utf8mb4字符集中的w就
  是4，utf8中的w就是3，gbk中的w为2，ascii字符集中w为1.
 - 对于变长类型varchar（M）来说，这种类型表示最多能存储M个字符，所以这种类型能表示的字符串最多占用的字节数就是W*M.
 - 假设该变长字段实际存储的字符串占用的字节数是L.
总结一下就是：如果该变长字段允许存储的最大字节数（M*W）超过255字节，并且真实数据所占用的字节数（L）超过127字节，则使用2字节来表示真实数据占用的字节数，否则用1字节。另外还需要注意一点是，变长字段长度列表中只存储值为非NULL的列的内容长度，不存储值为Null的的列的内容长度。
（2）NULL值列表
 COMPACT行格式把一条记录中值为null的所有列统一管理起来。
 - 首先统计表中允许存储NULL的列有哪些
 - 如果表中没有允许存储NULL的列，那NULL值列表也就不存在了，否则将每个允许存储NULL的列对应一个二进制位，二进制位按照列的书讯逆序排列。二进制位为1时则表示该列的值为NULL，二进制位为0时代表该列的值不为NULL。
 - MySQL中规定NULL值列表必须用整个字节的位表示，如果二进制位数不是整个字节则高位补0.
（3）记录头信息
 - 整个记录头信息由5个字节组成，40个二进制位。
 - deleted_flag：标记该记录是否被删除,min_rec_flag:B+树的每层非叶子节点中最小的目录项记录都会添加该标记,n_owned:一个页中的记录会分为若干个组，每个组中有一条记录中的n_owned值代表该组中所有记录条数，heap_no:表示当前记录在页面堆中的相对位置,record_type:表示当前记录的类型 0:为普通记录，1表示B+树非叶子节点的目录项记录，2：表示Infimum记录，3：表示Supremum记录,next_record表示下一条记录的相对位置。
对于除我们自己定义的列的数据外，MySQL会为每个记录默认地添加一些列。比如：row_id, trx_id(事务ID), roll_pointer(回滚指针)
InnoDB优先使用用户自定义的主键作为主键;如果用户没有定义主键，则选取一个不为NULL的UNIQUE键作为主键，否则InnoDB会默认添加一个名为row_id的隐藏列作为主键。

2.REDUNDANT行格式

REDUNDANT行格式是MySQL5.0之前在使用的一种行格式。
（1）字段长度偏移列表：没有个变长意味着REDUNDANT行格式把该条记录的所有列包括隐藏列的长度信息按照逆序存储到字段长度偏移列表，它是采用两个相邻偏移量的差值来计算各个列值的长度。
 (2) 1byte_offs_flag : 标记字段长度偏移列表中每个列对应的偏移量是使用1个字节还是2个字节。
   - 当记录的真实数据占用的字节数不大于127，用1字节表示。
   - 当记录的真实数据占用的字节数大于127但不大于32767时，每个列对应的偏移量占用2字节
   - 当记录的真实数据大于32767时，将一部分记录放到溢出页中，在本页中只保留前768字节和20字节的溢出页面地址，此时用2个字节来存储每个列对应的偏移量就够了。
（3）没有NULL值列表，我们将第一个比特位作为NULL值比特位，如果为1则该列的值为NULL，否则不是NULL。（此时也就说明了第二条只要记录的真实数据大于127就用2个字节了）

3.DYNAMIC行格式与COMPRESSED行格式
这两个行格式与COMPACT行格式类似，只不过再处理溢出列的数据时有些分歧：它们不会在记录的真实数据存储溢出列真实数据的前768字节，而是把该列的所有真实数据都放到溢出页中，只在记录的真实数据存储20字节大小的指向溢出页的地址。COMPRESSED行格式会采用压缩算法对页面进行压缩，以节省空间。
溢出列的临界值（132 + 2(27 + n ) < 16384）即一条记录的某个列中存储的数据占用的字节数非常多时（8099）该列可能会变为溢出列。

第五章 InnoDB数据页结构

总结

1.我们最关心的就是那些存放表中记录的那种类型的页，官方称为索引页。（数据页）
数据页代表的16KB大小的存储空间可以划分为多个部分：

 - File Header：文件头部 页的一些通用信息 38字节
 - Page Header：页面头部 数据页专有的一些信息 56字节
 - Infimum+Supremum：页面中最小记录和最大记录 26字节
 - User Records：用户记录 用户存储的记录内容 
 - Free Space：空闲空间 页中尚未使用的空间
 - Page Directory：页目录  页中某些记录的相对位置（存放每个组槽的位置）
 - File Trailer：文件尾部 校验页是否完整（内存与磁盘之间交互是通过页，校验是否为一个完整页的依据）

2.记录在页中的存储
从最开始生成页的时候其实并没有UserRecords部分，每当插入一条记录都会从FreeSpace部分申请一个记录的大小的空间并将这个空间划分到UserRecords部分。当Free Space部分的空间全部被UserRecords部分顶替后也就说明当前页用完了，在插入记录时要申请新的页。

记录头信息

特意把记录头信息的5字节的数据给标出来了，下面是记录头信息中各个属性的大体意思：
 - deleted_flag：1bit 标记该记录是否被删除（值为1时表示纪录被删除，被删除的记录不从磁盘上移除，因为在移除它们值后，还需要在磁盘上重新排列其他记录。所有被删除掉的记录会形成一个垃圾链表，记录在这个链表所占的空间成为可重用空间，之后有新的记录插入到表中就有可能覆盖掉被删除的这些记录占用的存储空间。）
 - min_rec_flag: 1bit B+树中每层非叶子节点中的最小的目录项记录都会添加这个标记（也就是索引目录项）
 - n_owned: 4bit 一个页面中的记录会被分成若干组，每个组中有一条最大记录使用n_owned记录该组中有几条数据
 - heap_no：13bit 当前记录在页面堆中的相对位置（我们向表中插入的记录从本质上说都是放到数据页的UserRecords部分，这些记录一条一条紧密排列。这个结构我们称为堆，每新申请一条记得存储空间时，该条记录比物理位置在它前面的那条记录的heap_no值大1。heap_no值为0，1的两条记录就是Infimum与Supremum，无论我们向页中插入了多少条记录，都规定任何记录都比Infimum记录大，比Supremum小，这两条记录只包含5字节大小的记录头信息和8字节大小的一个固定单词组成），值得注意的是即使之后删除了除堆中的某条记录，这条被删除记录的heap_no值也依旧保持不变。
 - record_type: 3bit 表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点目录项，2表示Infimum记录，3表示Supremum记录
 - next_record: 16bit 表示下一条记录的相对位置（它表示从当前记录的真实数据到下一条记录的真实数据的距离，如果该属性值为正数，说明当前记录的吓一跳记录在当前记录的后面；如果该属性值为负数，说明当前记录的下一条记录在当前记录的前面，比如第一条记录的next_record值为32，意味着从第一条记录的真实数据的地址向后找32字节便是下一条记录的真实数据。比如第4条记录的next_record值为-111，意味着从第4条记录的真实数据的地址处向前找111字节遍试下一条记录的真实数据。需要注意的一点是下一条记录的真实数据不是指插入顺寻的下一条记录，而是主键值由小到大的顺序排列的下一条记录。并且规定Infimum记录的下一条记录就是本页中主键值最小的用户记录，本页中主键值最大的用户记录的下一条记录就是Supremum记录，为了更形象的表示next_record，如下第二幅图所示。）

可以看到图中record_type为2的是Infimum记录，为3的是Supremum记录，0代表我们插入的记录

注意：箭头指向的位置，每个箭头都指向记录的真实数据开始的地方，可以看出来记录按照主键大小的顺寻形成了一个单向链表。Supremum记录的next_record值为0，也就是说Supremum记录之后就没有下一条记录了，这也意味着Supremum记录就是这个单向链表中的最后一个节点。如果从表中删除一条记录，单向链表也是发生变化的，如下图删掉第二条记录后的示意图所示。

//从上图可以看到删除第二条记录后的示意图
 1. 第二条记录其实并没有从存储空间中移除，而是把deleted_flag值设置为1
 2. 第二条记录的next_record值变为0，意味着该记录没有下一条记录
 3. 第一条记录的next_record指向了第三条记录
 4. Supremum记录的o_owned值从5变成了4（就是该组中所有记录的条数，按分组的规矩来定就是Infimum记录自己一个组，Supremum记录与用户记录一个组最多8条数据）
补充：next_record指针指向记录头信息和真实数据之间的位置的原因：向左读取记录头的信息，向右读取真实数据。并且变长字段长度列表，null值列表中的信息都是逆序存放的，这样可以使记录中位置靠前的字段和他们对应的字段长度信息在内存中靠的更近，提高高速缓存的命中率。
其实还有一个特别有意思的地方：主键为2的记录被删除掉了，但是却没有回收存储空间，并且heap_no的值也未发生改变，我们重新插入这条数据后InnoDB并没有因为新记录的插入而为它申请新的存储空间，而是直接复用了原来被删除记录的存储空间。
当数据页中存在多条被删除的记录时，可以使用这些记录的next_record属性将这些被删除的记录组成一个垃圾链表，以备之后重用这部分空间。

3.页目录

 1. 将所有正常的记录（包括Infimum和Supremum，不包括已经删除的记录）划分为几个组 Infimum记录所在的分组只能有一条记录，Supremum记录所在的分组拥有的记录条数只能在1~