mysql 基础
mysql分为 客戶端/服务端
客户端向服务端发送一段文本（mysql语句），服务器处理后向客户端进程返回一段文本。
查询请求执行过程
客户端-》处理连接-》查询缓存-》语法解析-》查询优化-》存储引擎-》文件系统-》磁盘
大致分为3部分：连接管理、解析与优化、存储引擎。
连接管理
客户端进程可以采用TCP/IP\命名管道、共享内存、UNIC域套接字与服务器建立连接。
每建立一个连接，服务器进程创建一个线程专门处理与这个客户端的交互。客户端退出时，不会销毁当前线程，而是缓存起来，当新的客户端连接是，把这个线程分配个新的。不用频繁创建和销毁线程。
解析与优化
查询缓存
处理查询请求时，会把刚刚处理过的查询请求和结果缓存起来。下一次同样的请求过来，直接从缓存中查找结果。
这个查询缓存在不同的客户端共享。
俩个查询请求字符必须一模一样。
当查询请求中包含某些系统函数。就不使用缓存了。缓存失效。
缓存系统会监测每张表，当表的结构或 数据被修改了。则关于此表的所有缓存删除。
语法解析
查询缓存没有命中，开始进入查询阶段，服务器会对文本进行分析，判断请求的语法是否正确。将要查询的表、各种查询条件都提取出来，放到服务器内部使用的一些数据结构上。
查询优化
mysql的优化程序会对我们的语句做一些优化，比如外连接转换为内连接、 表达方式简化，子查询转为 连接等。
可以使用EXPLAIN语句来查看某个语句的执行计划。
存储引擎
mysql把对数据的存储和提取操作都封装到了存储引擎模块中。
表示由一行一行的记录组成。这只是逻辑上的概念。在物理上如何表示记录，把数据写入具体的物理存储器，都是存储引擎负责的事。
server层与存储引擎交互时，一般以记录为单位。当某个记录符合要求时，先发送到一个缓冲区，缓冲区满了，才向客户端发送真正的记录。
常用的存储引擎
InnoDB 从Mysql 5.5.5 版本开始作为mysql的默认存储引擎。
MyISAM
查询当前服务器程序支持的存储引擎 show engines;
support ：该存储引擎是否可用
default : 默认的存储引擎
comment : 存储引擎的介绍
transactions: 是否支持事务处理
xa: 是否支持分布式事务
savepoints: 是否支持事务的部分回滚
设置表示可以指定表的存储引擎
create table 表名(
建表语句
) engine = 存储引擎名称；
修改表的存储引擎
alter table 表名 engine=存储引擎名称;
连接mysql
mysql -h主机名 -u用户名 -p密码 -P端口
Mysql的调控按钮-启动选项和系统变量
启动选项和配置文件
Mysql服务器程序和客户端程序有很多设置向。
比如：允许同时连入的客户端数量（默认151）、客户端和服务器的通信方式、表的默认存储引擎（InnoDB）、查询缓存大小等。
在命令行上使用选项
禁止客户端使用TCP/IP网络进行通信
mysqld --skip-networking
存储引擎
mysqld --default-storage-engine=MyISAM
配置文件中使用选项
启动服务器从这个配置文件中加载相应的启动选项。
配置文件的路径
配置文件内容
分为若干个组
每个组有一个组名，用中括号[]扩起来。
[server]
[mysqld]
[mysqld_safe]
[client]
[mysql]
[mysqladmin]
同一个配置文件中多个组的优先级，以最后一个组中的启动选项为准。
系统变量
Mysql服务器运行过程中会用到许多影响程序行为的变量。被称为系统变量。
查看系统变量
show variables [like 匹配的模式]；
如果最后一个连接用户是超级用户。那么就是151+1
设置系统变量
通过启动选项设置
启动服务器是传送启动选项的方式来设置。
mysqld --default-storage-engine=MyISAM --max-connections=10
服务器程序运行过程中设置
对于大部分系统变量，值可以在服务器程序运行过程中进行动态修改，
（1）设置不同作用范围的系统变量
对于不同的客户端，有不同的值。
俩个范围：global，session.
set global default_storage_engine=MyISAM;
set session default_storage_engine=MyISAM;
show session variables like 'default_storage_engine';
show global variables like 'default_storage_engine';
状态变量
为了更好的了解服务器程序的运行情况，维护了好多关于程序运行状态的变量。称为状态变量。
Threads_connected:多少客户端连接
Innodb_rows_updated: 更新了多少条以InnoDB为存储引擎的表中的记录
show status like 'thread%';
字符集和比较规则
字符集和比较规则简介
计算机中实际存储的是二进制数据，建立字符与二进制数据的映射关系。
1、要把哪些字符映射成二进制数据。
2、怎么映射。将字符映射为二进制数据：编码， 将二进制数据映射到字符：解码
字符集的概念来描述某个字符范围的编码规则。
比较规则简介
怎么比较俩个字符的大小
二进制比较规则
直接比较二进制编码的大小，但并不符合现实需求，比如英文字符不区分大小写。a和A是相等的。
需要将大小写不同的字符全部转为大写或小写，然后再比较二进制大小。
一些重要的字符集
ASCII字符集：128个字符，空格、标点符号、数字、大小写字母和一些不可见字符。使用一个字节来进行编码
ISO 8859-1字符集：256个字符，在ASCII的字符集上扩充了128个西欧常用字符。
GB2312字符集：收录了汉字以及拉丁字母、希腊字母、。兼容ASCII字符集。是ASCII采用一字节编码，否则采用俩字节编码。
不同的字节数表示一个字符的编码称为：变长编码方式。
计算机在读取一个字节序列时，怎么区分某个字节代表的是一个单独的字符还是某个字符的一部分。最高位为0就是代表一个单独的字符。
该字节最高位为1，就是俩个字节代表一个单独的字符。
GBK字符集： 对GB2312的扩充。
UTF-8字符集： 兼容ASCII字符集，变长编码，1-4字节。
UTF-8是Unicode字符集的一种编码方案，Unicode字符集可以采用UTF-8、UTF-16、UTF-32这几种编码方案，
Mysql不区分字符集和编码方案的概念。都当做字符集来对待。
Mysql支持的字符集和比较规则
字符集表示一个字符所用的最大字节长度在某些方面会影响系统的存储和性能。
utf8mb3：阉割过的UTF-8字符集，只使用 1-3字节表示字符
uft8mb4：正宗的UTF-8字符集，使用1-4字节表示字符。
字符集的查看
Default collation表示字符集中的一种默认的比较规则。
Maxlen:最多需要几个字节来表示一个字符。
比较规则的查看
_ai: accent insensitive 不区分重音
_as: accent sensitive 区分重音
_ci: case insensitive 不分分大小写
_cs: case sensitive 区分大小写
_bin: binary 以二进制方式比较
utf8_general_ci: 不区分大小写。
字符集和比较规则的应用
mysql有4个级别的字符集和比较规则
服务器级别
character_set_server
collation_server
数据库级别
表级别
列级别
客户端和服务器通信过程中使用的字符集
字符串在计算机上的体现就是一个字节序列，如果使用不同的字符集去解码这个字节序列。得到的是不同的，或者报错。
字符集转换
'我'在utf-8字符集编码下的字节序列0xE68891.
先按照utf-8字符集进行解码，然后按照GBK字符集进行编码：0xCED2.
mysql中字符集转换过程。
客户端发送请求：遵循mysql通信协议。
一般情况下，客户端编码请求字符串时使用的字符集与操作系统当前使用的字符集一直。
类UNIC操作系统是，LC_ALL,LC_TYPE，LANG这三个环境变量的值决定了操作系统当前使用的字符集。
服务器接受请求
每个客户端与服务器建立连接后，服务器都会为该客户端维护一个单独的character_set_client变量。这个变量是session级别的。
客户端在编码请求字符串使用的字符集与服务器在收到一个字节序列后认为该字节序列所采用的编码字符集是俩个独立的字符集。
一般情况下， 这俩个字符集是一致的。
服务器处理请求
在处理请求时，将其转化为session级别的系统变量 character_set_connection对应的字符集编码字节序列。
与之对应的比较规则：collation_connection.这个系统变量表示字符串该使用哪种比较规则。
按照就近原则。列解绑的字符集和排序规则优先级最高
服务器生成响应
会按照session级别的系统变量character_set_results的值进行返回。
character_set_client: 服务器认为请求是按照该系统变量指定的字符集进行编码的
character_set_connection：服务器在处理请求是，会把请求字节序列从character_set_client转换为character_set_connection
character_set_results: 服务器采用该系统变量指定的字符集对返回给客户端的字符串进行编码
客户端接受到响应
客户端会使用默认的字符集来解释这个字节序列。
比较规则的应用
用来比较字符串的大小以及对字符串进行排序。
主要看是否区分大小写。
从一条记录说起-InnoDB记录存储结构
InnoDB页简介
InnoDB是一个将表中数据存储到磁盘上的引擎。
将数据划分为若干个页。以页作为磁盘和内存之间交互的基本单位。大小为16KB=16384字节。
InnoDB行格式
以记录为单位向表中插入数据。记录在磁盘上的存放形式被称为行格式，或者记录格式。
设计了4种不同类型的行格式：compact 、redunant、dynamic、compressed.
指定航哥是的语法
create table 表名 （） row_format=行格式名称
compact行格式
一条完整的记录可以被分为记录的额外信息和记录的真实数据
记录的额外信息
分为三个部分
1）变长字段长度列表
变长数据类型， varchar(M)、varbinary(M)、text、blob。这些数据类型的列称为变长字段。
变长字段中存储多少字节的数据是不固定的。
我们在存储真实数据的时候需要把这些数据占用的字节数也存起来。
各变长字段的真实数据占用的字节数按照列的顺序逆序存放。
每个变长字段的内容占用的字节数用1字节表示，如果变长字段的内容占用的字节数比较多。就可能需要2字节来表示。
至于用1字节还是2字节来表示变长字段的真实数据占用的字节数。InnoDB有自己的规则。
W\M\L这三个符号。
假设某个字符集中最多需要W字节来表示一个字符。
对于变长类型varchar(M)来说，这种类型表示能存储最多M个字符。
假设该变长字段实际存储的字符串占用的字节数 是 L
确定使用1字节还是2字节表示一个变长字段的真实数据规则就是
如果 M * W <= 255.那么使用1字节来表示真实数据占用的字节数。
如果 M * W > 255,
如果 L <= 127 ,则用1字节来表示
如果 L > 127 ,则用2字节来表示真实数据占用的字节数。
变长字段长度列表 中 只存储值为非NULL的列的内容长度。
2字节一定能够表示当前页 变长字段的数据长度。InnoDB会把一部分数据存放到溢出页中。该字段只表示当前页的数据长度。
2）NULL值列表
一条记录中的某些列可能存储NULL值，如果把这些NULL值都放到记录的真实数据中存储会很占地方。所以compact行格式把一条记录中值为NULL的列统一管理起来。存储到NULL值列表。
首先统计表中允许存储NULL的列有哪些。 NOT NULL修饰的列就不会统计。
如果表中没有允许NULL值存储的列。那么NULL值列表也就没了。 每个允许存储NULL的列对应一个二进制位，逆序排列，二进制为的值为1时，代表该列的值为NULL.
3）记录头信息
固定5字节组成。用于描述记录的一些属性。 40个二进制位。
预留位1 预留位2 各1个bit
deleted_flag 1 标记该记录是否被删除
min_rec_flag 1 B+树的每层非叶子节点中最小的目录项记录都会添加该标记
n_owned 4bit. 一个页面中的记录会被分为若干个组，每个组中有一个记录是“带头大哥”，其余的记录是“小弟”，带头大哥记录的n_owned值代表该组中所有的记录条数。 小弟记录的n_owned值都为0
heap_no: 13bit. 当前记录在页面堆中的相对位置
record_type: 3bit. 表示当前记录的类型， 0-普通记录，1-B+树非叶子节点的目录项记录，2-Infimum记录，3-Supremum记录
next_record: 16bit 表示下一条记录的相对位置。
记录的真实数据
mysql会为每个记录默认的添加一些列，隐藏列。
row_id 6字节 行ID,唯一标识一条记录
trx_id 6字节， 事务ID
roll_pointer 7字节 回滚指针
redunant行格式
比较原始的行格式。非紧凑。
溢出列
在compact和redundant行格式中，对于占用存储空间非常多的列，在记录的真实数据出只会存储该列的一部分数据，而把剩余的数据分散存储在几个其他的页（溢出页）中。然后再记录的真实数据处用20字节存储指向这些页的地址。这20字节还包括分散在其他页面中的数据所占用的字节数。
产生溢出页的临界点，
mysql规定一个页中至少存放俩行记录。
每个页除了存放记录一维，还需要存储一些额外的信息，这些信息需要132字节，
每个 记录需要的额外信息是27字节，27字节包括
2字节存储真实数据的长度
1字节存储列是否是NULL值
5字节大小的头信息
6字节的row_id列
6字节的trx_id列
7字节的roll_pointer列
dynamic和compressed行格式
5.7版本默认行格式dynamic。
这俩个行格式和compack行格式挺像，在处理溢出列的数据有点儿分歧。
它们不会在记录的真实数据处存储该溢出列真实数据的前768字节。而是把该列的所有真实数据都存储到溢出页中。只是记录的真实数据出存储20字节大小的指向溢出页的地址。包括真实数据占用的字节数。
compressed行格式会采用压缩算法对页面进行压缩。以节省空间。
盛放记录的大盒子----InnoDB数据页结构
不同类型的页简介
InnoDB为不同的目的而设计了多种不同类型的页。
比如存放表空间头部信息的页、存放change buffer信息的页、存放inode信息的页、存放undo日志信息的页；
存放表中记录的页，称为索引页， index. 也叫数据页
数据页结构快览
File Header 文件头部， 页的一些通用信息
Page Header 页面头部 数据页转悠的一些信息
Infimum + Supremum 页面中的最小记录和最大记录26字节。 俩个虚拟的记录
User Records 用户记录。
Free Space 空闲空间 页中尚未使用的空间
Page Directory 页目录， 页中某些记录的相对位置
File Trailer 文件尾部，检验页是否完整
记录在页中的存储
每当插入一条记录时，都会从FreeSpace部分申请一个记录大小的空间。并将这个空间划分到UserRecords部分
deleted_flag|min_rec_flag|n_owned|heap_no|record_type|next_record|......
deleted_flag:记录是否被删除，删除一条记录，置为1，被删掉的记录会组成一个垃圾链表，记录在这个垃圾链表中占用的空间称为可重用空间。。若有新记录插入到表中，可能覆盖掉被删除的记录占用的空间。
置为1 和 加入到垃圾链表是俩个阶段。 undo 日志相关。
min_rec_flag: 每层非叶子子节点中的最小的目录项记录都会添加该标记。
heap_no： 我们向表中插入的记录从本质上来说都是放到User Records部分，记录一条一条紧密排列，设计师把记录一条一条排列的结构称为 堆 heap. 把一条记录在堆中的相对位置称之为heap_no.
新增的比前一条大1.
heap_no 值为0和1的俩条记录分别为最小记录 Infimum记录 和 最大记录 Supremum记录。伪记录。
记录可以比大小。就是主键的大小。 用户记录 大于 最小记录，小于最大几率。
Infimum 和 supremum不存放在User Records.
堆中记录的heap_no在分配之后就不会发生改动了。既是删除了依然保持不变。
record_type: 表示当前记录的类型。
0 表示普通记录
1 表示B+树非叶节点的目录项记录
2 表示Infimum记录
3 表示Supremum记录
next_record: 表示当前记录的真实数据到下一条记录的真实数据的距离（向左是额外信息，向右是真实数据）。值为正数，当前记录的下一条记录在后面。
值为负数，下一条记录在前面。
按照主键从小到大的顺序形成了一个单向链表。如果删除了一条记录。单向列表也会跟着变化。
删除一条记录。supremum记录的n_ownedc也会减一。
再次插入，直接复用被删的空间。
当数据页存在多条被删除的记录是，使用next_record属性将被删除的记录组成一个垃圾链表，以备之后重用这部分存储空间。
Page Directory 页目录
记录在页中是按照主键值 由小到大的顺序串联成一个单向链表。
如果根据主键值查找页中的某条记录，该咋么办！
最笨的办法是从Infimum记录开始查找，当某个节点代表的记录的主键值大于想要查找的主键值时，就可以停止查找。
页中记录数量较少时，这种方法还行，当记录较多时，性能就差了。所以设计师就做了一个目录。根据目录来查找。就很快。
1、将所有正常的记录，不包括垃圾链表的记录，划分为几个组。
2、每个组的最后一条记录，组内最大的那条记录，就是带头大哥。带头大哥的n_owned属性表示该组内共有几条记录。
3、将每个组中最后一条记录在页面中的地址偏移量，就是该记录的真实数据与页面中第0个字节之间的距离单独提取出来，按顺序存储到靠近页尾部的地方，这个地方就是Page Directory.页目录中的这些地址偏移量称为槽 slot.每个槽占用2字节。页目录就是由多个槽组成的。
页目录中有俩个槽，就意味着记录被分成了俩个组。
槽对应的记录越小，位置越靠近File Trailer
规定： Infimum记录所在的分组只能有1条记录。Supremum记录所在的分组记录条数只能在1-8之间。
剩下的分组记录条数范围只能在4-8条之间。
1、初始情况下，只有Infimum和Supremum俩个记录，俩个槽。
2、之后每插入一条记录，都会从页目录中找到对应记录的主键值 比待插入记录的主键值大，并且差值最小的槽。然后把该槽对应的记录的n_owned值加1，表示本组内又添加了一条记录。直到改组中的记录数等于8个。
3、当一个组中的记录数等于8后。再插入一条记录，会将组中的记录拆分成俩个组，其中一个组中4条记录，一个5条。新增一个槽，记录这个新增分组中最大记录的偏移量
找主键值为a的记录过程：
1、计算中间槽的位置。查看中间槽mid对应的主键值M,如果M大于a,则向槽小的地方继续二分。如果M < a，则向槽大的地方继续二分
2、直到剩余一个槽。那么主键值为a的记录肯定在中间槽M对应的组中。。找到槽所在分组中主键值最小的那条记录。然后沿着单向链表遍历槽2中的记录。上一个槽的记录的下一个next_record就是当前槽中主键值最小的记录，遍历槽，直到找到主键值a的记录。
Page Header 页面头部
设计师为了能够得到存储在数据页中的记录的状态信息，比如数据页中已经存储了多少条记录，Free Space在页面中的地址偏移量，页目录中存储了多少个槽等等。固定56个字节。页结构的第二部分。
page_n_dir_slots: 2B,页目录中的槽数量
page_heap_top: 2B, 还未使用的空间最小地址，就是Free Space的地址之前的一个地址。
page_n_heap: 2B, 第1位表示本激励是否为紧凑型的记录，剩余的15位表示本页的堆中记录的数量，包括被删的
page_free: 2B, 各个已经删除的记录通过next_record组成一个单向链表，这个单向链表中的记录所占用的存储空间可以被重新利用。表示该单向链表头节点对应记录在页面中的偏移量。
page_garbage: 2B，已删除记录占用的字节数。
page_last_insert: 2B, 最后插入记录的位置
page_direction: 2B, 记录插入的方向
page_n_direction: 2B，一个方向连续插入的记录数量
page_n_recs： 2B,该页中用户记录的数量，不包括被删的记录，
page_max_trx_id: 8B, 修改当前页的最大事务id,该值仅在二级索引页面中定义。
page_level: 2B， 当前页在B+树中所处的层级
page_index_id: 8B, 索引ID,表示当前页属于哪个索引。
page_btr_seg_leaf: 10B，B+树叶子节点段的头部信息，仅在B+树的根页面中定义
page_btr_seg_top: 10B, B+树非叶子节点段的头部信息，仅在B+树的根页面中定义。
File Header 文件头部
Page Header 专门针对数据页的各种状态信息。
File Header 是通用的，用于各种类型的页。
fil_page_space_or_checksum: 4B，页的校验和
file_page_offset: 4B，页号
file_page_prev: 4B,上一个页号
file_page_next: 4B,下一个页号
fil_page_lsn: 8B，页面被修改时对应的lsn, log sequence number, 日志序列号值。
fil_page_type: 2B, 该页的类型
fil_page_file_flush_lsn: 8B，仅在系统表空间的第一个页中定义，代表文件至少被刷新到了对应的LSN值
fil_page_arch_log_no_or_space_id: 4B，页属于哪个表空间
其他类型的页：
undo日志页、存储段信息、change buffer空闲列表、change buffer 的一些属性、存储一些系统数据、事务系统数据、表空间头部信息、存储区的一些属性、溢出页、索引页（数据页）
File Trailer 文件尾部
InnoDB存储引擎会把数据存储到磁盘上，但是磁盘速度太慢。需要以页为单位把数据加载到内存中处理，如果该页中的数据在内存中被修改了，还需要刷新到磁盘中，刷新的过程中断电了。就需要校验一个页是否完整的刷新到了磁盘。所以每个页的尾部增加了一个File Trailer .8字节组成。
前4字节代表校验和。与FileHeader中的校验和相对应，二者不同意味着刷新期间发生了错误。
后4字节代表页面被最后修改时对应的LSN的后4字节，正常情况下应该与FileHeader中fil_page_lsn的后4 字节相同。也是用于校验页的完整性。
快速查询的秘籍-B+树索引
各个数据页可以组成一个双向链表。
每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表。
每个数据页都有一个页目录，使用二分法可以快速定位到对应的槽。
没有索引时进行查找
搜索条件为某个列等于某个常数
在一个页中查找
以主键为搜索条件：页目录中使用二分法快速定位到对应的槽，
以其他列作为搜索条件：只能从Infimum记录开始遍历单向链表。
在很多页中查找
1）定位到记录所在的页，
2）从所在的页内查找相应的记录
没有索引的情况下，只能从第一页沿着双向链表查找。
索引
根据某个搜索条件查找一些记录。 不一定要遍历所有的数据页。
我们可以想方法为快速定位记录所在的数据页而建立一个别的目录，这个目录必须完成俩件事
1）下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。
在插入的时候，就有可能伴随着记录的移动。保持这个状态的成立。 这个过程称为页分裂
2）给所有的页建立一个目录项
数据页的页号并不是连续的。需要建立一个目录
key:页中最小的主键值
value: 页号
把这些记录放到一个数组中。然后就可以根据二分法快速确定主键值所在的页号。这个目录有个别名：索引
InnoDB中的索引方案
所有的目录项连续存储有2个问题
1）InnoDB使用页作为管理存储空间的基本单位，也就是最多只能保证16KB的连续存储空间。
2）执行增删改操作，某个页可能就会被删除，那么就会移动后面所有的页，不好。
所以，设计师复用之前存储用户记录的数据页来存储目录项。
为了与用户记录进行区分，表示目录项的记录称为 目录项记录。 根据 record_type属性。
目录项记录的record_type值是1
目录项记录只有主键值和页的编号俩个列。
头信息的min_rec_flag属性值为1
除了这三点值外，和数据页没区别。也会生成页目录。从而按照主键值进行查找是可以使用二分法来加快查询速度。
步骤1：确定存储目录项记录的页
步骤2：通过存储目录项记录的页确定用户记录真正所在的页
步骤3：在真正存储用户记录的页中定位到具体的记录
步骤1中，需要定位存储目录项 记录的页，这些页很多，如何快速定位呢。
就是为这些存储目录项记录的页 再生成一个更高级的目录。 就像是一个多级目录一样。大目录里嵌套小目录。小目录里才是实际的数据。
这个结构就是B+树，数据页称为B+树的节点，真正的用户记录都在B+树最底层的节点上。其余目录项记录的节点称为非叶子节点或者内节点。
设计师称最下面那层称为第0层。
B+树一般不会超过4层。最多只需要进行4个页面内的查找。
聚簇索引
B+树本身是一个目录，一个索引。
特点1：使用记录主键值的大小进行记录和页的排序，有三个含义
1） 页内的记录按照主键的大小排序成一个单向链表。页内的记录被划分为若干个组，每个组中主键值最大的记录在页内的偏移量会被当做槽依次存放在页目录中，可以在页目录中通过二分法快速定位到主键列等于某个值的记录
2）各个存放用户记录的页也是根据页中用户记录的主键大小排序成一个双向链表
3）存放目录项 记录的页分为不同的层级，在同一层级中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
特点2：B+树的叶子节点存储的是完整的用户记录，存储了所有列的值，包括隐藏列
具有这俩个特点的B+数称为聚簇索引，所有完整的用户记录都存放在整个聚簇索引的叶子节点处，这种聚簇索引不需要我们在mysql语句中显示的使用INDEX语句去创建。
InnoDB存储引擎会自动为我们创建聚簇索引。聚簇索引就是数据的存储方式， 索引即数据，数据即索引
二级索引
聚簇索引只能在搜索条件是主键值时才能发挥作用。原因是B+树中的数据都是按照主键进行排序的。如果我们想以别的列作为搜索条件该咋办？
我们可以多建几颗B+树。不同的B+树中的数据采用不同的排序规则
使用记录C2列的大小进行记录和页的排序，代表3个含义
1）页内的记录是按照C2列的大小顺序排成一个单向列表。页内的记录被划分为若干个组，每个组中c2列值最大的记录在页内的偏移量会被当做槽依次存放在页目录中。
2）各个存放用户记录的页也是根据页中记录的c2列大小顺序排序成一个双向链表。
3）存放目录项记录的页分为不同的层级，在同一层级中的页也是根据页中目录项记录的C2列的大小顺序排成一个双向链表。
B+树的叶子节点存储的并不是完整的用户记录，而只是记录了c2列+主键这俩个列的值，如果存储所有的信息，太浪费存储空间了。
所以再根据主键值在聚簇索引中查找所有的列。这个过程叫回表。
目录项记录中不再是主键+页号的搭配，而是C2列+页号的搭配。
这种以非主键列的大小为排序规则而建立的B+树需要执行回表操作才可以定位到完整的用户记录，所以这种B+树也称为二级索引Secondary Index 或辅助索引。
联合索引
我们页可以同事以多个列的大小作为排序规则。同时为多个列建立索引。比如C2和C3列
1）先把各个记录和页按照C2列进行排序
2）在记录的C2列相同的请阔下，再采用c3列进行排序
每个目录项记录都由C2、C3列、页号这三部分组成，
B+树叶子子节点出的用户记录由C2列、c3列、主键列组成
InnoDB中B+树索引的注意事项
1）根页面万年不动窝
每当为某个表创建一个B+树索引是，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点没有用户记录，也没有目录项记录
插入记录是，先把用户记录存储到根节点中
在根节点中的可用空间用完是继续插入记录，此时会根据根节点中所有记录复制到一个新分配的页。然后对整个页进行页分裂操作。得到另一个新页。这时，新插入的记录会根据键值的大小分配到对应的页中。根节点此时便升级为存储目录项记录的页，页需要把各个页对应的目录项九路插入到根节点中
一个B+树索引的根节点创建后不再移动，也就是页号不会再变。
2）内节点中目录项记录的唯一性
目录项记录的是索引列+页号的搭配。对于二级索引来说不严谨。
为了让新插入的记录能找到自己在哪个页中，就需要保证B+树同一层内节点的目录项记录除了页号这个字段以外是唯一的。
二级索引的内节点目录项记录实际由三部分组成。
索引列的值、主键值、页号。
3）一个页面至少容纳2条记录
B+树本质上就是一个大的多层级目录，每经过一个目录是，都会过滤掉许多无效的子目录，
如果一个大的目录只存放一个子目录，那么目录层级会非常多。所以InnoDB的一个数据页至少可以存放2条记录。
MyISAM
在MyISAM中，索引是索引，数据是数据
在InnoDB中，索引是数据，数据是索引
MySQL中创建和删除索引的语句。
create table 表名（
各个列的信息。。。，
（key|index） 索引名 （需要被索引的单个列或多个列）
）
B+树索引的使用
扫描区间
索引用于排序
回表的代价
索引可以用于减少需要扫描的记录数量，也可以用于排序和分组
只为用于搜索、排序或分组的列创建索引
当列中不重复值得个数在总记录条数中的占比很大时，才为列建立索引
索引列的类型尽量小
可以只为索引列前缀创建索引，减少存储空间
尽量使用覆盖索引进行查询，避免回表操作带来的性能损耗
让索引列以列名的形式单独出现在搜索条件中
为了尽可能少的让聚簇索引发生页面分裂的情况，建议主键拥有auto_increament属性
定位并删除表中冗余和重复索引。
数据的家-mysql的数据目录
存储引擎都是把数据存储在文件系统上。读取数据的时候会从文件系统中把数据读取出来返回给我们。
Mysql数据目录
数据目录和安装目录的区别。
安装目录存储了许多用来控制客户端程序和服务端程序的命令。
数据目录是用来存储mysql在运行过程中产生的数据。
如何确定mysql中的数据目录
数据目录对应着一个系统变量datadir
数据目录结构
数据库在文件系统中的表示
每个数据库对应数据目录下的一个子目录
表在文件系统中的表示
表的信息分为俩种。
1）表结构的定位， *.frm 文件
2）表中的数据
不同的存储引擎不一样
InnoDB:提出了表空间 table space 或者 文件空间 file type.是一个抽象的概念。对应文件系统上一个或者多个真实文件。
系统表空间：system tablespace.
就是ibdata1文件。大小12MB.这个文件是自扩展文件。可以自己增加大小，只有一份。5.5.7-5.6.5 表中数据都会被默认存储到这个系统表空间。
独立表空间 file-per-table tablespace
5.6.6之后，InnodDB不再默认把各个表的数据存储到系统表空间。而是为每个表建立一个独立表空间。
表名.ibd
还有通用表空间 general tablespace、undo表空间 undo tablespace、临时表空间 temporary tablespace等。
数据目录下还包括其他的文件：
服务进程文件：每运行一个mysql服务器程序，都意味着启动一个进程。mysql服务器会把自己的进程ID写入到这个文件中
服务器日志文件：在服务器运行期间，会产生各种各样的日志，查询日志、二进制日志、redo日志
SSL和RSA证书和密钥文件：
文件系统对数据库的影响
数据都是存储在文件系统中，所以受到文件系统的一些制约。
数据库名称和表名称不得超过文件系统所允许的最大程度
特殊字符问题会处理编码
文件长度受文件系统最大长度的限制
mysql系统数据库简介
mysql：存储了mysql的用户账号和权限信息、一些运行过程中产生的日志信息
infomation_schema: myusql服务器维护其他数据库的信息。比如有哪些表、视图、触发器、列、索引等。不是真实的用户数据，而是一些描述性信息，称为元数据
performance_schema: 类似于性能监控。最近执行了哪些语句、花费了多长时间、内存使用情况等
sys: 通过视图形式把infomation_schema和performance_schema结合起来。方便了解性能信息
存放页面的大池子---InnoDB的表空间
表空间想象成被切分为许多个页的池子，当想为某个表插入一条记录的时候，就从池子里捞出一个对应的页把数据写进去。
聚簇索引和其他二级索引都是以B+树的形式保存到表空间中。B+树的节点就是数据页。
页面通用部分
数据页由7部分组成。其中俩个部分是所有类型的页面通用的。
File Header: 记录页面的一些通用信息
File Traniler: 校验页是否完整，保证页面从内存刷新到磁盘后内容是相同的
1）表空间中每一个页都对应着一个页号，fil_page_offset。可以通过这个页号在表空间中快速定位到指定的页面。
页号由4字节组成，32位，所以一个表空间最多可拥有2^32个页，一个页按照16KB来算，一个表空间最多支持64TB的数据。
独立表空间结构
区的概念
表空间中的页实在是太多了，为了更好的管理这些页面，设计师提出了区 extent的概念。对于16kb的页，连续的64个页就是一个区。
一个区默认占用1MB空间大小。
无论是系统表空间还是独立表空间，都可以看出是若干个连续的区组成的，每256个区被划分为成一组。
这些组的头几个页面的类型都是类似的。
第一个组最开始的三个页面的类型是固定的。
- FSP_HDR： 这个类型的页面用来登记整个表空间的一些整体属性以及本组所有的区的属性。
2）IBUF_BITMAP: 这个类型的页面用来存储关于Change Buffer的一些信息，
3）INODE: 这个类型的页面存储了许多称为INODE Entry的数据结构。
其余各组最开始的2个页面的类型是固定的
1） XDES: 全称：extent descriptor.用来登记本组256个区的属性。
2）IBUF_BITMAP: 同上
段的概念
为什么提出区的概念？
因为表中的数量多的时候，B+树中每一层的页都会形成一个双向链表。
我们向表中插入一条记录，本质上就是向该表的聚簇索引以及所有的二级索引代表的B+树的节点中插入数据。
B+树每一层中的页都会形成一个双向链表。如果以页未单位来分配存储空间，双向链表相邻的俩个页之间的物理位置可能离得非常远。
如果相邻的俩个页的物理位置不连续，对于传统的机械硬盘来说，需要重新定位磁头位置，也就是会产生随机I/O.这样会影响磁盘的性能。所以我们应该尽量让页面链表中相邻的页的物理位置页相邻。
所以引入了区的概念。一个区就是物理位置上连续的64个页。区里的页号都是连续的。
在表中的数据量很大时，为某个索引分配空间的时候就不再按照页为单位分配了。而是按照区为单位进行分配。
如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请的区中，扫描效果就大打折扣了。
所以把叶子节点和非叶子节点进行了区别对待。
也就是说叶子节点右自己独有的区。
非叶子节点也有自己独有的区。
存放叶子节点的区的集合就算是一个段 segment
存放非叶子节点的区的集合也算是一个段。
也就是说一个索引会生成2个段。一个叶子节点段，一个非叶子节点段。
“以完整的区为单位分配给某个段，对于数据量较小的表来说太浪费存储空间”
设计师提出了 碎片区 fragment的概念。也就是在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在。可以用于不同的目的。
在刚开始向表中插入数据时，段是从某个碎片区以单个页面为单位来分配存储空间的。
当某个段已经占用了32个碎片区页面后，就会以完整的区为单位来分配存储空间。原先占用的碎片区页面并不会复制到新申请的完整的区中。
区的分类
表空间是由若干个区组成，区分为四种类型。
空闲的区： 现在还没有用到这个区中的任何页面
有剩余空闲页面的碎片区： 表示碎片区中还有可被分配的空闲页面
没有剩余空闲页面的碎片区： 碎片区中所有的页面都被分配使用
附属于某个段的区： 每一个索引都可以分为叶子节点段和非叶子节点段。除此之外，InnoDB还会定义一些特殊用途的段，当这些段中数据量很大时，将使用区作为基本的分配单位。这些区中的页面完全用于存储该段中的数据。碎片区可以存储属于不同段的数据。
4种类型的区也可以称为区的四种状态 state.
free\free_frag\full_frag\fseg\
段相当于师，区相当于团，团都是隶属于某个师，而free\free_page\full_page可以直接隶属于表空间。类似于独立团直接听命于军部一样。
为了方便管理这些区，设计师设计了一个称为XDES Entry Extent descriptor Entry结构。
每一个区都对应着一个XDES Entry结构。这个结构记录了对应的区的一些属性。
Segment ID : 8B，每个段都有一个唯一的编号。用ID表示。表示该区所在的段，前提是该区分配给某个段了。
List Node: 12B,这个部分可以将若干个XDES Entry结构串成一个链表。
State: 4B，表明区的状态。
Page State Bitmap:16B，128bit.一个区默认64个页，128位分为64个部分，每个部分有2位。对应区中的一个页。第一个位表示是否空闲。第二位未用到。
1）XDES Entry链表。
区、段、碎片区、附属于段的区、XDES Entry结构，这些东西设计的初心是为了减少随机I/O。又不至于让数据量少的表浪费空间。
向表中插入数据 本质上就是向表中各个索引的叶子节点段、非叶子节点段插入数据。
不同的区有不同的状态。
当段中数据较少时，首先会查看表空间中是否有状态为free_page的区， 空闲页面的碎片区， 如果找到了，从该区去一个零散页把数据插进去。
否则到表空间中申请一个状态为free的区， 空闲的区。 把该区的状态变为 free_frag.然后从该区中取一个零散的页把数据插进去。直到该区中没有空闲的页面。状态变为 full_frag.
现在的问题是，我们怎么直到表空间中哪些区的状态是free，哪些区是free_frag.
当表空间不断增大，区的数量上千了。不能每次都遍历这些区对应的XDES Entry。
这时候就需要XDES Entry中List Node部分的时候了。
通过List NOde把状态为Free的区对应的XDES Entry结构连接成一个链表。这个链表称为free链表
通过list node把状态为free_frag的区对应的XDES Entry结构连接成一个链表， free_frag链表。
通过list node 把状态为full_frag的区对应的XDES Entry结构连接成一个链表，full_frag链表。
这样依赖，每当想找一个free_page状态的区是，把free_page链表的头节点拿出来。
我们怎么直到哪些区属于哪个段？
我们可以基于链表来快速查找只属于某个段的区。
所以我们把状态为fseg的区对应的xdes_entry结构都假如到一个链表中，
索引a的叶子节点段和索引b的叶子节点段 都存储到一个区中？
我们想要每个段都有它队里的链表，多以可以根据 段号 来建立链表。 有多少个段就建立多少个链表。
需要继续细分，因为一个段中有好多区，
设计师为每个段中的区对应的XDES Entry结构建立了3个链表。
free 链表： 同一个段中，所有页面都是空闲页面的区 对应的xdes entry结构都会被假如到这个链表。
not null链表： 同一个段中，仍有空闲页面的区对应的xdes entry结构都会被假如到这个链表。
full链表： 同一个段中，已经没有空闲页面的区对应的xdes entry结构都会被加入到这个链表。
每一个索引都对应俩个段，每个段都会维护上述三个链表。
2）链表基节点
如何找某个链表的表头？
设计了一个名为List Base Node 链表基节点的结构。这个结构中包含了链表的头结点和尾节点的指针，以及这个链表中包含了多少个节点的信息。
每个链表都对应这么一个List Base Node结构。
3）链表小结
表空间是由若干个区组成，每个区对应一个XDES Entry结构。直属于表空间的区对应的XDES Entry结构可以分成free free_frag full_frag三个链表。每个段可以拥有若干个区，每个段中的区对应的xdes entry结构可以构成 free\not_null\full这三个链表。每个链表对应一个list base node结构。
段的结构
段是一个逻辑上的概念。若干个零散的页面以及一些完整的区组成。
设计师为每个段都定义了一个INODE Entry结构。来记录这个段中的属性。
Segment ID: 对应段的编号
NOT_FULL_N_USED: 在not_null链表中已经使用了多少个页面
3个List Base Node： 上面所述的。
Magic Number: 是否已经初始化
Fragment Array Entry: 表示零散页面的页号
各类型页面详细情况
表空间、段、区、XDES Entry、INODE Entry，各种链表。
1） FSP_HDR类型
第一个组的第一个页面，表空间的第一个页面， 页号为0.这个页面的类型是FSP_HDR。
存储了表空间的一些整体属性以及第一个组内256个区对应的XDES Entry结构。
File Header: 文件头部，页的一些通用信息
File Space Header： 表空间头部，一些整体属性信息。
XDES Entry: 区描述信息， 存储本组256个区对应的属性信息。
Empty Space: 尚未使用的空间。
File Trailer: 文件尾部。
File Space Header:
XDES Entry就存储在表空间的第一个页面中。一个大小是40字节。由于一个页面的大小有限，只能存放数量有限的XDES Entry结构。所以我们才把256个区划分为一组，在每组的第一个页面存放256个XDES Entry结构。
2）XDES 类型。
一个XDES Entry对应一个区。区的数量非常多是，一个单独的页无法存放足够多的XDES Entry结构。 所以我们把表分为若干个组。每组开头的第一个页面记录着本组内所有的区对应的XDES Entry结构。
3）IBUF_BITMAP类型
每个分组中第二个页面的类型都是IBUF_BITMAP.
这种类型的页记录了一些有关Change_Buffer的东西。
向表中插入一条记录，本质是向每个索引对应的B+树中插入记录，该记录首先插入聚簇索引页面，然后再插入每个二级索引页面。
这些页面在表空间中随机分布。将会产生大量随机IO
对于UPDATE 和 DELETE操作来说。
设计师设计了Change Buffer结构。也是表空间中的一颗B+树。根节点存储在系统表空间中。
修改非唯一 二级索引页面时，如果该页面尚未被加载到内存中。那么修改将先被暂时缓存到change buffer中。之后服务器空闲或者其他原因导致对应的页面加载到内存中。再将修改合并到对应的页面。
IBUF___ Insert BUffer .
- INODE类型
第一个分组中第三个页面的类型是INODE.
INODE类型的页就是为了存储INODE Entry结构而存在。
List node for inode page list :通用链表节点。上一个和下一个inode页面的指针。
系统表空间
与独立表空间基本类似。
整个mysql进程只有一个系统表空间。系统表空间需要记录一些与整个系统相关的信息。
属于带头大哥。space id是0.
系统表结构
前三个页面和独立表空间的类型一致。3-7页面时系统表特有的。
3 sys: 存储change buffer的头部信息
4 index: 存储change buffer的根页面
5 trx_sys: 事务系统的相关信息
6 sys: 第一个回滚段的信息
7 sys: 数据字典头部信息
extent1 和 extent2 这俩个区。64-191这128个页面称为Doublewrite Buffer 双写缓冲区。涉及事务和多版本控制。
InnoDB数据字典
使用insert语句向表中插入记录的称为用户数据。
mysql只是作为一个软件来保管这些数据。提供方便的增删改查接口而已。
每当向一个表中插入一条记录是，mysql先要校验插入语句对应的表是否存在。以及插入的列和表中的列是否符合。
如果语法没有问题，还需要知道该表的聚簇索引和所有二级索引对应的根页面是哪个表空间的哪个页面，然后把记录插入对应的索引的B+树中。
所以需要额外的信息
比如，某个表属于哪个表空间，表里面有多少列。列类型，该表有多少个索引，索引对应的字段。外键。
为了更好的管理用户数据而不得已引入的一些额外的数据，这些数据称为元数据。
Innode存储引擎定义了一系列的内部系统表 internal system table来记录这些元数据。
1)sys_tables:所有表的信息
2）sys_columns:所有列的信息
3）sys_indexes: 所有索引的信息
4）sys_fields: 索引对应的列的信息
5）sys_foreign: 所有外键的信息
6）sys_foreign_cols: 外键对应的列
7）sys_tablespaces: 所有表空间信息
- sys_datafiles: 表空间对应的文件系统的文件路径信息
9）sys_virtual: 所有虚拟生成的列的信息。
这些系统表也被称为数据字典。都是以B+树的形式保存在系统表空间的某些页面中。
sys_tables,sys_columns,sys_indexes,sys_fields,4个表称为基本系统表 basic system table.
sys_tables
name: 表的名称, 主键。
id: 唯一的ID， 二级索引
n_cols: 拥有的列数
mix_id: 过时，忽略
mix_len:表的额外属性。
cluster_id: 未使用，忽略
space: 该表所属 表空间ID
sys_columns
table_id: 表id
pos: 表中第几列
name: 列名称
mytpye: 主数据类型
prtype: 精确数据类型，是否允许null.负数等。
len: 最多占用存储空间的字节数
prec: 该列的精度。
sys_indexs:
table_id: 表对应的ID
ID：每个索引都有一个唯一的ID
name：索引的名称
n_fields: 索引包含的列的个数
type: 索引的类型， 聚簇索引、唯一二级索引、全文索引
space: 索引根页面所在的表空间ID
page_no: 索引根页面所在的页面号
merge_threshold： 页面中的记录被删除到某个比例。就尝试把该页面和相邻页面合并。
sys_fields
index_id: 所属列的ID
pos: 索引列的第几列
col_name: 列的名称
条条大路通罗马---单表访问方法
const
通过主键或者唯一二级索引列的等值比较来定位一条记录，是常数级别的，代价可以不计。
ref
搜索条件 为二级索引列与常数进行等值比较，形成的扫描区间为单点扫描区间，采用二级索引来执行查询的访问方法称为ref.
根据二级索引来执行查询时，每获取一条记录，就会立刻对其执行回表操作。
ref_or_null
select * from single_table where key1 = 'abc' or key1 is null;
扫描区间是【null,null】, ['abc','abc'],
range
select * from single_table where key2 in (1438,6328) or (key2 >= 38 and key2 <= 79);
对应的扫描区间：【1438,1438】,[6328, 6328],[38,79]
使用索引执行查询时，若干个单点扫描区间或扫描范围。称为range.
index
select key_part1,key_part2,key_part3 from single_table where key_part2 = 'abc';
扫描全部的二级索引记录的方法，称为index访问方法，不需要执行回表操作。
all
全表扫描
注意事项
重温二级索引 + 回表
索引合并
俩个表的情迷接触--连接的原理
连接就是把各个表中的记录都取出来进行依次匹配。
把俩个表组成一个新的更大的记录。
如果连接查询的结果集中包含一个表中的每一条记录，那么这样的结果集就可以称为笛卡尔积。
select * from t1,t2
连接过程简介
连接是过滤掉特定的记录组合 是有必要的。
过滤条件分成下面俩种。
涉及单表的条件：这种只涉及单表的过滤条件。 t1.m1 > 1 或 t2.n2 <'d'
涉及俩表的条件： t1.m1 = t2.m2 、 t1.n1 >= t2.n2 .
select * from t1,t2 where t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 < 'd';
三个过滤条件
步骤1：首先确定第一个需要查询的表， 称为驱动表。
使用t1作为驱动表，查找t1.m1 > 1的记录
步骤2：从驱动表每获取一条记录，都要到t2表中查找匹配的记录。
t2表被称为被驱动表，涉及俩个表的列的过滤条件 t1.m1 = t2.m2 开始过滤。
t2表开始查找的时候有俩个条件 t2.m2 = t1.m1 = 2 , t2.n2 <'d'.执行单表查询。
驱动表访问1次，被驱动表访问2次。
内连接 和 外连接
一个学生信息表，一个学生成绩表。
要想把学习的考试成绩查询出来，就需要进行俩表连接了。
连接过程就是从 stundent表中取出记录，然后在 score表中查找number相同的成绩记录。
select * from student, score where student.number = score.number
这样子查询出来，有个问题就是没有考试的学习就没有返回记录。
student表中所有的记录都要有。score表中的记录可以没有。为了解决这个问题，提出了内连接和外连接的概念
对于内连接的俩个表，若驱动表中的记录在被驱动表中没有匹配的记录，则该记录不会加入到最后的结果集。
对于外连接的俩个表，驱动表中的记录在被驱动表中没有匹配的记录，也仍然要加入到结果集
左外连接： 选取左侧的表为驱动表
右外连接：选取右侧的表为驱动表
where子句中的过滤条件
凡是不符合where子句中的过滤条件的记录都不会加入到最后的结果集
on 子句的过滤条件
外连接中，on子句中的过滤 条件无法在 被驱动表 中找到记录，驱动表记录仍然会加入到结果集。
内连接中，on和where 一样。
左（右）外连接的语法
select * from t1 left outer join t2 on 连接条件 where 普通过滤条件
就是把左边的表称为 驱动表。 右边的表称为 被驱动表。
对于外连接，必须使用ON条件来指出连接条件。 内连接 不必要包含ON子句。
内连接
内连接和外连接的根本曲边就是驱动表中的记录不符合ON子句的连接条件是，内连接不会把该记录加入到最后的结果集中。
连接的原理
mysql采用了什么样的算法来进行表与表之间的连接。
循环嵌套连接
对于俩表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问好多遍。
具体访问几遍取决于对驱动表执行单表查询之后的结果集中有多少条记录。
步骤1： 选取驱动表，然后根据驱动表的过滤条件，执行单表查询
步骤2：步骤1查询出来的每条记录，都分别到被驱动表中查询匹配的记录。
这个过程就像是一个嵌套的循环，是最简单 最笨拙的一种连接查询方法。
使用索引加快连接速度
设计俩个表的过滤条件在针对t2表进行查询是，t1的条件已经确定了。所以我们只需要单单优化针对t2表的查询即可。
在查找的列建立索引。
最好不要使用*作为查询列表，而是把真正用到的列作为查询列表。
基于块的嵌套循环连接
需要尽量减少被驱动表的访问次数。
驱动表结果集中有多少条记录，就需要被驱动表从磁盘加载到内存中多少次。
我们是否可以在把被驱动表中的记录加载到内存时，一次性的与驱动表中的多条记录进行匹配。
设计师提出了一个名为join buffer 连接缓冲区的概念。
join buffer就是在执行连接查询前申请的一块固定大小的内存。
先把驱动表结果集中的记录装在这个 内存中，
每一条被驱动表的记录一次性的与join buffer中的多条驱动表记录进行匹配。
加入了join buffer的嵌套循环连接算法 称为基于块的嵌套循环连接 block nested loop join
谁最便宜就选谁---基于成本的优化
mysql执行成本
I/O成本： 数据和索引存储到磁盘上。 查询表中的数据，需要先加载到内存。
CPU成本： 记录是否满足搜索条件、对结果集进行排序
规定：读取一个页面成本：1.0 。 读取以及检测一条记录是否符合搜索条件：0.2，
单表查询的成本
在执行一条单表查询语句之前，mysql的优化器会找出所有可以用来执行该语句的方案。并找出成本最低的方案。
示例：
select * from single_table where key1 in ('a','b','c') and key2 > 10 and key2 < 1000 and key3 > key2
and key_part1 like '%hello%' and common_field = '123';
1）根据搜索条件，找出所有可能使用的索引
对于B+树索引来说，只要索引列和常数使用 = 《=》in not in ,is null, is not null, > between != like.就会产生一个扫描区间。可能使用到的索引称为： possible keys
key1 in （'a','b','c'）：可以使用二级索引
key2 > 10 and key2 < 1000: 二级索引
key3 > key 2 :没有与常数进行比较。不能产生合适的扫描区间
key_part1 like '%hello%': 以通配符开头，不能产生合适的扫描区间
common_field = '123':没有建立索引，
2）计算全表扫描的代价
全表扫描就是把聚簇索引中的记录都依次与给定的搜索条件进行比较。
代价：
聚簇索引占用的页面数
该表中的记录数
查看single_table的统计信息
show table status like 'single_table'\G
rows: 表示记录条数，innodb不朱雀。是估计值。
data_length: 表占用的存储空间字节数。
data_length = 聚簇索引 页面数量 * 每个页面大小（16KB）
- 计算使用不同索引执行查询的代价
先分析 使用唯一二级索引的成本。 再分析使用普通索引的成本。
uk_key2: key2 > 10 and key2 < 1000. 扫描区间（10，1000）
扫描区间数量和需要回表的记录数。
读取一个扫描区间和一个页面的IO成本是相同的。
区间最左记录和区间最右记录，间隔不大于10个页面。可以精确统计记录条数。
否则只沿着区间最左记录向右读10个页面，计算每个页面平局包含多少记录。然后用这个平均值乘以 最左 记录和 最右记录之间的页面数量就可以了（父节点目录项记录隔着几个记录）。如果不是同一个目录项，再递归计算。
每次回表操作相当于访问一个页面。
回表操作之后得到完整的用户记录。然后再检测其他搜索条件是否成立。
3）计算使用不同索引执行查询的代价
全表扫描成功：x1
使用uk_key2: x2
使用idx_index: x3
4）找出成本最低的代价
x1 x2 x3 谁最低，来执行查询。
基于索引统计数据的成本计算
如果有许多单点扫描区间。直接通过访问索引对应的B+树来计算 某个扫描区间内对应的索引记录条数的方式称为 index dive.
如果有20000个单点扫描区间，需要进行20000次index dive操作。可能比全表扫描的成本都大。
设计师提供了一个系统变量 eq_range_index_dive_limit. 默认值200 。
如果 单点扫描区间对应的记录条数大于等于200个。就不能使用index dive了。而是使用索引统计数据 index statistics来进行估算。
mysql会为每个索引维护一份统计数据。 show index from 表名。
每个列有多个属性，其中 Cardinality属性，基数的意思。表示类中不重复的值的个数。
使用 show table status 显示出来 rows值。表示一个表中有多少条记录。
使用show index 显示出来 Cardinality属性。
我们可以计算出某一个列中一个值平均重复多少次。
假如 rows = 9000. cardinality = 900. 那么这个列的单个值的平均重复值为10.
假设 in语句对应着2000个 单点扫描区间。 每个单点扫描区间对应着大约10条记录。 总共需要回表记录数 2000 * 10 = 20000.
当查询中没有使用 索引查询语句时，可能英文 eq_range_index_dive_limit值太小导致的。
连接查询的成本
条件过滤 Condition Filtering
连接查询采用的是嵌套循环连接算法。 驱动表访问依次。被驱动表访问多次。
查询成本由俩部分组成
单词查询驱动表的成本
多次查询被驱动表的成本。
查询驱动表后得到的记录条数 称为驱动表的扇出 fanout.
对于内连接来说，需要选择最优的表连接顺序
兵马未动，粮草先行---innodb统计数据是如何收集的。
统计数据是如何来的。以什么方式收集的。
统计数据的存储方式
永久性的存储统计数据： 统计数据存储在磁盘上。在服务器重启之后这些数据依然存在
非永久性的存储统计数据：存储在内存中。
系统变量 innodb_stats_persistent用来控制。不同的表可以选择不同的存储方案。
基于磁盘的永久性统计数据
统计数据存储到了俩个表中：
innodb_index_stats : 关于表的统计数据
innodb_table_stats ： 关于索引的统计数据
基于规则的优化 内含子查询优化二三事
条件化简
移除不必要的括号
常量传递
移除没用的条件
表达式计算
having 和 where子句的合并
常量表检测
外连接消除
内连接的驱动表和被驱动表的位置可以相互转换。
子查询优化
在select 子句中
在from子句中
在where 或 on子句中
查询优化的百科全书---explain详解。
查询语句前面加一个explain.
各个列的作用
id：每个select对应一个唯一的id
select_type: select关键字对应的查询的类型。
table：表名
partitions: 匹配的分区信息
type: 针对单表的访问方法
possible_keys: 可能用到的索引
key: 实际使用的索引
key_len: 实际使用的索引的长度
ref: 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows: 预估的需要读取的记录条数
filtered: 经过搜索条件过滤后剩余记录条数的百分比。
Extra: 一些额外的信息
执行计划输出中各列详解
table
查询语句不管多么复制，包含多少多少个表。最后都是对每个表进行单表访问。
explan 语句输出的每条记录都对应着某个单表的访问方法。
id
查询语句中没出现一个select关键字，就会为分配一个唯一的id值。
type
单表访问方法。有system、const、eq_ref、ref、full_text、ref_or_null、index_merge、unqiue_subquery、index_subquer、range、index 、all
possible_keys key
key会在possible_keys选择一个成本最低的。possible_keys不是越多越好。
key_len
三部分组成：
1）实际数据最多占用的存储空间长度。
2）如果该列可以存储NULL值，则key_len值在该列的实际数据最多占用的存储空间的基础上再加1字节。
3）对于变长类型的列来说，都会有2字节的空间来存储该编列的实际数据占用的长度。还要再加2字节。
ref
索引列进行等值匹配的东西是啥。
rows
结果集的行数。
filtered
满足 condition filtering 条件过滤后的记录数。
Extra
- no tables used：没有from 语句
impossible where ：查询语句where 子句永远为false.
no matchint min/max row: 有min / max 聚集函数，但是并没有记录符合where子句中的搜索条件时。
using index: 使用覆盖索引执行查询时
json格式的执行计划
explan format=json *** \G
Extented Explan
show warnings\G
神兵利器---optimizer trace的神奇功效
show variables like 'optimizer_trace';
enabled=on # 开启功能
查询优化器像一个黑盒子。optimizer trace可以让用户方便地查看优化器生成执行计划的整个过程。
select * from information_schema.OPTIMIZER_TRACE\G
有4列：
query: 输入的查询语句
trace: 优化过程的json格式的文本
missing_bytes_max_mem_size: 被忽略的文本字节数
insufficient_privileges: 是否有权限查看执行计划。
优化过程分为三个阶段
prepare 阶段
optimize阶段
execute 阶段
调节磁盘和CPU的矛盾-- InnoDB的Buffer Pool
缓存的重要性
表空间 是一个或几个实际文件的抽象。
如果需要访问某个页的数据，就会把完整的页中的数据全部加载到内存中。读写访问完不会释放。而是缓存起来。
InnoDB的Buffer Pool
为了缓存 磁盘中的页，mysql服务器启动时向操作系统申请了一片连续的内存，这片内存叫 Buffer Pool缓存池
innodb_buffer_pool_size = ****字节
buffer pool内部组成
连续的内存划分为若干个页面。与表空间使用的页面大小一致， 默认都是16KB. 叫缓冲页
为每个缓冲页创建了一些控制信息。
包括表空间编号、页号、缓冲页在缓冲池的地址、链表节点信息
每个页对应的控制信息占用的一块内存称为一个控制块。控制块与缓冲页一一对应。
free链表的管理
当需要缓冲的时候，放在buffer pool的哪个页上。哪些可以用。这个 时候，控制块信息有作用了。把所有空闲的缓冲页对应的控制块作为一个节点放到链表中，这个链表就是free链表。
刚初始完buffer pool。所有的缓冲页都是空闲的。加入的时候，就从free 链表去一个。然后移除
缓冲页的哈希处理
需要缓存的时候，这个页是否已经在buffer pool中。需要依次遍历吗？
可以使用表空间号+页号作为key, 用缓冲页控制块的地址作为value来创建一个哈希表。
flush 链表的管理
如果我们 修改了buffer pool中某个缓冲页的数据，与磁盘上的页不一致了。这样的缓冲页 也称为脏页 dirty page.
如果修改玩某个缓冲页，就立即刷新到磁盘中，频繁的写数据会影响性能。而是在未来某个时间点进行刷新。
因为不是立即刷新数据，需要记录哪些页已经脏了。就需要创建一个存储脏页的链表。也是控制块作为一个节点加入到这个链表。
LRU链表的管理
缓冲区不够的窘境
Buffer Pool对应的内存大小有限。如果需要缓存的页占用的内存大小超过了buffer pool的大小。
是不是需要把旧的缓冲页从 buffer pool中移除。移除哪些呢？
我们期望 缓冲页 被命中的次数越高越好。
需要记录哪些链表最少使用。
least recently used. LRU链表。
如果该页不再buffer pool中，把该页从磁盘加载到buffer pool中。然后把对应的控制块作为节点加入到lru链表的头部。
如果该页已经加载到buffer pool中，贼直接把该页对应的控制块移动到lru链表的头部。
这种简单的lru链表有俩种比较尴尬的情况
1）InnoDB提供了 预读 read ahead服务，就是认为执行当前的请求时，可能会在后面读取某些页面，于是就预先把这些页面加载到buffer pool中。根据触发方式不同，分为线性预读和随机预读。
但是如果使用不到。就是大大降低buffer pool的命中率。
2）当进行全表扫描时，一波来了又来一波。使用频率低的页被同时加入到buffer pool中。
设计师就把lru链表分为俩种。
一部分存储使用频率非常高的缓冲页：这部分数据称为热数据。 young区域。
另一部分存储使用频率不是很高的缓冲页：冷数据，old 区域。
属性脏页到磁盘
后台有专门的线程负责每隔一段时间就把脏页 刷新到磁盘。这样不影响用户线程处理正常的请求。
刷新方式主要有下面俩种
1） 从LRU冷链表的冷数据中刷新一部分页面到磁盘
2） 从flush链表中刷新一部分页面到磁盘
事务简介
任务：把现实世界的业务场景 映射 到数据库世界中。
原子性 Atomicity
转账操作是一个不可分割的操作。要么没转，要么转成功，不能存在中间的状态。
要么全做，要么全不做 的规则称为 原子性。
为了保证数据库世界中某些操作的原子性，设计师需要保证：如果在执行操作的过程中发生了错误，把已经执行的操作恢复成没执行前的样子。
隔离性 Isolation
俩次状态的转换是互不影响的。
T1 和 T2 是顺序执行，不能交叉执行。
不仅要保证原子性，还要保证其他状态转换不影响到本次状态转换。
一致性 Consistency
数据符合约束。不能乱来。
更多的一致性需要靠业务代码来保证。
比如余额不能是负的。身份证号不能重复。
持久性 Durability
状态转换完成后，这个转换的结果将永久保留。意味着在磁盘中保留下了
事务的概念
AICD。 - 》ACID
把需要保证原子性、隔离性、一致性、持久性的一个或多个数据库操作 称为 事务 transaction
事务是一个抽象的概念，对应着一个或多个数据库操作。
设计师把这些操作所执行的不同阶段把事务划分成了下面几个状态。
活动的 Active :事务对应的数据库操作正则执行过程中。
部分提交的 partially committed: 操作都在内存中执行，所造成的影响并没有刷新到磁盘。
失败的：failed: 当事务处于活动的状态或者部分提交的状态时，遇到了错误。无法继续执行。
中止的：aborted: 遇到错误后，已经操作了的需要回滚。回滚操作完毕后，就是中止的状态
提交的：committed: 操作结果刷新到磁盘后。
Mysql 中事务的语法
数据库操作符合ACID特性，就是事务的本质。
开启事务
begin；
start transaction; 可以跟随修饰符，只读、读写、一致性读
提交事务
commit;
手动中止事务
rollback;
支持事务的存储引擎
只有InnoDB和NDB存储引擎支持。
自动提交
autocommit系统变量
默认值为ON.就是说，每一条语句都算是一个独立的事务。
隐式提交
输入了某些语句，会导致之前的事务悄悄地提交掉。
定义或修改数据库对象的数据定义语言 DDL
隐式使用或修改mysql数据库中的表
事务控制或关于锁定的语句
加载数据的语句
mysql复制的一些语句
其他语句 analyze table, cache index,...
保存点。
不使用rollback一夜回到解放前，就是可以指定回滚到哪个点。
说过的话一定要做到----redo日志
如何保持持久性呢？简单的做法就是事务提交完成后，把该事务修改的所有页面都刷新到磁盘。这样做有以下弊端
刷新一个完整的数据页太浪费了
随机IO刷新起来比较慢。
我们向让已经提交了的事务对数据库中的数据所做的修改能永久生效，即使后来系统崩溃，在重启后也能把这种修改恢复过来。
只需要把每次修改的内容记录一些就好。
这样在事务提交时，把记录内容刷新到 磁盘中。即使系统奔溃了，重启之后，只需要按照记录的内容重新更新数据页。
因系统奔溃而重启时需要按照上述内容所记录的步骤重新更新数据页，上述内容也称为重做日志 redo log.
redo log 占用的空间非常小:在存储表空间ID、页号、偏移量已经需要更新的值时，需要的存储空间很小。
redo log是顺序写入磁盘的。顺序IO
redo log 格式
type space_id page_number data
type：日志类型， 53种。
space_id: 表空间ID
page_number: 页号
data：redo日志的具体内容。
后悔了怎么办---- undo 日志
事务回滚的需求
为了保证事务的原子性，我么你需要改回原来的样子。这个过程称为回滚 rollback
执行操作时需要把回滚时所需要的东西记下来。
在插入一条记录时，至少要把这条记录的主键值记下来
在删除一条记录时，至少要把这条记录中的内容都记录下来。
在修改一条记录时，至少要把这条记录更新的列的旧值记下来。
事务ID
分配事务ID的时机。
一个事务可以是一个只读事务，也可以是一个读写事务。
如果某个事务在执行过程中对某个表执行了增删改操作。那么InnoDB存储引擎就会给它分配一个独一无二的事务ID.
对于只读事务，只有在它第一次对某个用户创建的临时表执行增删改操作时，才会为这个事务分配一个事务ID.
对于读写事务，只有在它第一次对某个表执行增删该操作时。
事务ID是怎么生成的
本质是一个数字，
服务器会在内存中维护一个全局变量，每当需要为某个事务分配事务ID时，就会把该变量的值当做事务ID分配给该事务。变量自增1
每当这个变量的值为256的倍数时，就会将该变量的值刷新到系统表空间中页号为5的页面中一个名为max trx id的属性中
当系统下一层重新启动时，会将整个max trx id属性加载到内存中。并将该值+256之后复制给全局变量。
聚簇索引记录的trx_id 就是 进行改动语句所在事务的ID.
一条记录的多副面孔---事务隔离基本和MVCC
服务器可以同时处理多个客户端的多个事务
并发执行的事务访问相同的数据。导致数据的不一致性问题。
要求我们随时用某种手段来强制让事务按照顺序一个一个单独地执行。
我们希望这些事务隔离地执行。
最粗暴的方式：同一时刻最多只允许一个事务运行。性能太差了
还是并发执行，但是访问相同的数据进行限制。排队。
当前事务提交之后，其他事务才能继续访问这个数据。
事务并发执行时遇到的一致性问题
脏写 Dirty Write
一个事务修改了另一个未提交事务修改过的数据。
脏读 Dirty Read
一个事务读到了另一个未提交事务修改过的数据
不可重复读 Non-Repeatable Read
一个事务修改了另一个未提交事务读取的数据， 模糊读现象。
T1先读取了x1.然后T2修改了x1的值，T1再次读取x1的值不一样了。
幻读 Phantom
一个事务读取了 另一个提交事务的数据。
T1读取数据表x1.
T2修改数据表x1，增加记录，并提交了。
T1再次读取x1.和第一次不一样了。
俩次读取的记录不一样。
事务隔离级别
隔离级别越低，越可能发生严重的问题。
read uncommitted: 未提交读，可能出现 脏读、不可重复读、幻读
read committed：已提交读， 可能出现 不可重复读、幻读
repeatable read: 可重复读， 可能出现幻读
serializable: 可串行化 。上述现象都不可能发生
MVCC 原理
版本链
roll_pointer:每次对聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中。这个隐藏列相当于一个指针，可以通过它找到该记录修改前的信息。
每条undo日志也都有一个roll_pointer属性。
每次更新记录后，都会将旧值放到一条undo日志中。就算是该记录的一个旧版本。
随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表。这个链表称为版本链。
每个版本中还包含生成该版本时对应的事务ID。
我们会利用这个记录的版本链来控制并发事务访问相同记录时的行为。称为多版本并发控制 multi-version concurrency control.
ReadView
对于使用read uncommitted隔离级别的事务来说，由于可以读到未提交事务修改过的记录。所以直接读取最新版本就好了。
对于使用serializable隔离级别的事务，使用枷锁的方式来访问记录。
对于使用read committed和repeatable read隔离级别的事务来说，都必须保证读到已经提交的事务修改过 的记录。也就是说加入另一个事务已经修改了记录，但是尚未提交。则不能直接读取最新版本的记录。
核心问题就是：需要判断版本链中的哪个版本是当前事务可见的。
设计师提出了ReadView的概念，一致性视图。4个比较重要的内容。
m_ids: 在生成readview时，当前系统中活跃的读写事务的事务id列表
min_trx_id: 当前系统中活跃的读写事务中最小的事务ID.
max_trx_id:系统应该分配给下一个事务的ID值。
creator_trx_id: 生成该readview的事务ID
有了readview后，在访问某条记录时，只需要按照下面步骤来判断记录的某个版本是否可见
1）如果被访问版本的trx_id属性值与readview中的creator_trx_id值相同。意味着当前事务在访问它自己修改过的记录。
2）如果被访问版本的trx_id属性值小于readview中的min_trx_id值，表名生成该版本的事务在当前事务生成readview前已经提交。索引可以被当前事务访问。
3）如果被访问版本的trx_id属性值大于或等于readview中的max_trx_id。表明生成该版本的事务在当前事务生成readview后才开启。所以该版本不可以被当前事务访问
4）在min_trx_id \max_trx_id之间。则需要判断trx_id是否在m_ids列表中，如果在，说明生成该版本的事务还是火雨的，该版本不可以被访问，如果不再，说明已经被提交，可以被访问。
如果某个版本的数据对当前事务不可见，那么顺着版本链找到下一个版本的数据。
锁
解决并发事务带来问题的俩种基本方式
并发事务访问相同记录的情况大致分为3种。
读-读情况：并发事务相继读取相同的记录。允许这种情况发生
写-写情况：相继执行改动
读写-写读情况：一个事务进行读取，一个事务进行改动
写-写情况
会发生脏写情况，任何一种隔离级别都不允许发生。需要排队执行。
通过为该记录加锁来实现。这个锁本质上是一个内存中的结构。
在事务执行前是没有锁的，当一个事务想对这条记录进行改动时，首先会看看内存中有没有与这条记录关联的锁结构。
如果没有，就会在内存中生成一个锁结构与之关联。
锁结构有很多信息，只把俩个比较重要的属性拿了出来。
trx信息：表示整个锁结构与哪个事务关联。
is_waitting: 表示当前事务是否在等待
因为没有别的事务为这条记录加锁，所以is_waiting属性就是FALSE。这个场景称为获取锁成功。
事务T2也想对这条记录进行改动，先看有没有对应的锁结构，发现有，T2也生成一个锁结构与这条记录相关联。但是is_waiting为true..表示没有成功的获取到锁。
事务T1，提交之后，就会把锁结构释放掉。然后检测一下还有没有与该记录关联的锁结构。发现有，那么就把事务T2is_waiting改为false。把该事务对应的线程唤醒。让T2继续执行。
读-写 或 写-读情况
怎么避免脏读、不可重复读、幻读这些？
方案1：读操作采用多版本并发控制 MVCC。写操作进行加锁。
查询语句只能读到在生成readview之前提交事务所做的更改。
写操作肯定是针对最新版本的记录。读记录的历史 版本和改动记录的最新版本这俩者并不冲突。
方案2：读、写操作都采用加锁方式。
如果业务场景不允许读取记录的旧版本。，而是每次读取记录的最新版本。意味着读操作和写操作一样排队执行。
一般采用MVCC方式来解决。
一致性读
事务利用 MVCC进行的读取操作称为一致性读 consistent read。 或者一致性无锁读 快照读。
锁定读
1、共享锁和独占锁
由于既要运行读读情况不受影响，又要使写-写 读-写 写-读情况中相互排队。设计师 给锁分了个类。
共享锁 shared lock：简称 S锁。在事务要读取一条记录时，需要先获取该记录的S锁。
独占锁 exclusive lock: 排他锁，X锁。 在事务要改动一条记录时，需要先获取该记录的X锁。
事务T1和T2进行读取，可以同时获得S锁。
如果事务T2想要获取X锁，那么此操作会被阻塞。直到事务T1提交之后将S锁释放掉为止。
S锁是兼容的，S和X锁是不兼容的。X锁和X锁是不兼容的，
锁定读的语句
有时候我们想在读取记录时就获取记录的X锁。从而禁止别的事务读写该记录。把这种在读取记录前就为该记录加锁的读取方式称为锁定读。 Locking Read.
对读取的记录加S锁
select ... lock in share mode;
对读取的记录加X锁
select ... for update.
写操作
平常所用到的写操作无非就是delete\update\insert三种
delete:对一条记录执行delete操作。其实就是先在B+树中定位到这条记录。然后获取这条记录的X锁。最后执行delete mark操作。
update:分为3种情况
1）如果为修改该记录的键值，并且被更新的列所占用的存储空间在修改前后未发生变化，则先在B+树中定位到这条记录的位置。然后获取记录的X锁。最后在原记录的位置上进行修改操作。
2）存储空间发生变换，获取该记录的X锁，之后将该记录彻底删除掉。最后再插入一条新纪录。
3）如果修改了该记录的键值。相当于先执行delete操作，然后执行insert操作。
insert：新插入的一条记录受隐式锁保护，不需要在内存中为其生成对应锁的结构。
多粒度锁
前面的锁都是针对记录的。可以将其称为行级锁。对一条记录加锁，影响的也只是这条记录而已。粒度比较细。
对一个表加锁，会影响表中的所有记录
节省锁结构，会把符合下面条件的锁放到同一个锁结构中。
在同一个事务中进行加锁操作
被加锁的记录在同一个页面中
加锁的类型是一样的
等待的状态是一样的。
死锁
死锁发生时，InnoDB会选择一个较小的事务进行回滚
这本书，总体还行吧，满分10分，打7分。。。
这书为了凑字数，好多重复的话和冗余的句子。
9月计划读：《Go语言设计与实现》
