MySql - 基础知识 - 《Java 复习》

聚簇索引
二级索引

服务器处理客户端请求

连接管理
- TCP/IP、命名管道或共享内存、Unix域套接字
解析与优化
- 查询缓存
  - MySQL服务器程序处理查询时会把把查询的结果缓存起来，直到这张表被执行了写操作，导致缓存失效。
  - 并且如果两个查询请求在任何字符上的不同（例如：空格、注释、大小写），以及包含mysql的内置表或者部分可变函数、变量等，都会导致缓存不会命中；
  - 维护缓存开销大，从MySQL 5.7.20开始，不推荐使用查询缓存，并在MySQL 8.0中删除。
- 语法解析
  - MySQL服务器程序首先要对这段文本做分析，判断请求的语法是否正确，然后从文本中将要查询的表、各种查询条件都提取出来放到MySQL服务器内部使用的一些数据结构上来
- 查询优化
  - MySQl会对解析后的语法进行优化，优化的结果就是生成一个执行计划，使用EXPLAIN语句来查看。
- 储存引擎
  - 数据的存储和提取操作的封装
  - 常用的储存引擎：InnoDB和MyISAM

字符集和比较规则、

字符集:：字符的编码和解码
比较规则：字符集之间的比较
常用的字符集
- ASCII字符集：共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符
- ISO 8859-1字符集：共收录256个字符，是在ASCII字符集的基础上又扩充了128个西欧常用字符
- GB2312字符集
  - 如果该字符在ASCII字符集中，则采用1字节编码。
  - 否则采用2字节编码。
- GBK字符集
- utf8字符集：收录地球上能想到的所有字符，而且还在不断扩充
MySQL中支持的字符集和排序规则
- MySQL中的utf8和utf8mb4
  - utf8mb3：阉割过的utf8字符集，只使用1～3个字节表示字符
  - utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符
- 比较规则
  - utf8字符集默认的比较规则就是utf8_general_ci，_ci表示不区分大小写

InnoDB记录存储结构

概述：MySQL是将数据存储到磁盘，这样断电也不会丢失数据。但是处理数据的时候是发生到内存的，但是读取磁盘又是非常慢的，为了加快数据处理效率，InnoDB将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，页的大小一般为 16 KB。
InnoDB行格式：记录在磁盘上的存放方式也被称为行格式
- COMPACT行格式

  - 变长字段长度列表：存储记录中可变字段的长度列表（逆序）
  - NULL值列表：存储记录中可为NULL的字段的情况（逆序），1表示为NULL
  - 记录头信息

  - 记录的真实数据

     - InnoDB表对主键的生成策略：优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键
  - **行溢出**：如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中。
     - 行溢出的临界点
        - MySQL中规定一个页中至少存放两行记录，每个页除了存放我们的记录以外，也需要132个字节存储一些额外的信息。
        - 每个记录需要的额外信息是27字节
           - 2个字节用于存储真实数据的长度
           - 1个字节用于存储列是否是NULL值
           - 5个字节大小的头信息
           - 6个字节的row_id列
           - 6个字节的transaction_id列
           - 7个字节的roll_pointer列

InnoDB数据页结构

页的结构

一开始生成页的时候，并没有User Records，每当我们插入一条记录，都会从Free Space部分划分到User Records部分
记录头信息

delete_mask ：删除标志，当删除记录时标记为1，实现逻辑删除，如果再次插入重复数据将重新启用该记录
min_recgaised：
heap_no：这个属性表示当前记录在本页中的位置，MySQL会自动给每个页里边儿加了两个记录，称为伪记录
record_type：0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录
next_record：从当前记录的真实数据到下一条记录的真实数据的地址偏移量

Page Directory（页目录）
- 概述：为了加快查询效率，MYSQL将记录进行了分组，每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录，将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方，这个地方就是所谓的Page Directory，也就是页目录
- 分组规则
  - 对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间
- 在一个数据页中查找指定主键值的记录的过程分为两步：
  - 通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录。
  - 通过记录的next_record属性遍历该槽所在的组中的各个记录。
Page Header（页面头部）
- 概述：就是页数据看板。
File Header（文件头部）
- 概述：页通用的东西
File Trailer
- 概述：由8个字节组成，用来检测页的完整性
- 前4个字节代表页的校验和
  - 如果同步磁盘成功，则页首尾的校验和应该相同
- 后4个字节代表页面被最后修改时对应的日志序列位置（LSN）

B+树索引

索引：B+树（聚簇索引）
- 使用记录主键值的大小进行记录和页的排序
  - 页内的记录是按照主键的大小顺序排成一个单向链表
  - 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
  - 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
- B+树的叶子节点存储的是完整的用户记录
  - 所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）
二级索引
- 通过二级索引只能找到对应的主键，再通过聚簇索引查找对应的记录数据（回表）
联合索引
- 同时以多个列的大小作为排序规则
MyISAM中的索引：将索引和数据分开存储
- 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件
- 使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件，是主键值 + 行号的组合

索引的使用

全值匹配：查询条件中包含索引的所有字段，与顺序无关，因为MySQl的SQL优化器会对其进行优化
匹配左边的列：查询条件必须包含联合索引左边的字段，不然走不了索引
匹配列前缀：对于字符类型的索引，由于大多数排序规则都是逐个字符进行比较排序的，所以使用前缀进行匹配也是可以走索引，也就是左匹配
匹配范围值：使用联合进行范围查找的时候需要注意，如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引
精确匹配某一列并范围匹配另外一列：对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找
用于排序
- 不可以使用索引进行排序的几种情况
  - ASC、DESC混用
  - 排序列包含非同一个索引的列
  - 排序列使用了复杂的表达式
用于分组
回表的代价
- 如果包含了索引以外的字段就需要回表，有limit限制会使查询优化器更倾向于二级索引 + 回表，否则全表扫描
覆盖索引：最好在查询列表里只包含索引列
如何挑选索引
- 只为用于搜索、排序或分组的列创建索引
- 考虑列的基数：索引字段的重复率，重复度太高就没必要建索引
- 索引列的类型尽量小：索引列的数据类型应该尽量小不然既消耗空间，又消耗排序时间
- 索引字符串值的前缀：对于字符串索引列，我们可以只对前缀建立索引，查询时先查询前缀的范围，再回表来筛选出全部数据
  - 不支持排序
主键插入顺序
- 如果插入的主键忽大忽小有可能触发页分裂造成性能消耗

单表访问方法

const
- 主键或者唯一二级索引列与常数的等值比较
ref
- 普通的二级索引列与常数进行等值比较，需要回表
ref_or_null
- 使用二级索引而不是全表扫描的方式执行该查询时
range
- 匹配查询
index
- 采用遍历二级索引记录的执行方式
all
- 全表扫描
Intersection合并
- 二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况
- 主键列可以是范围匹配
Union合并
- 二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况
- 主键列可以是范围匹配
- 使用Intersection索引合并的搜索条件

子查询优化

如果IN子查询符合转换为semi-join的条件，查询优化器会优先把该子查询转换为semi-join，然后再考虑下边5种执行半连接的策略中哪个成本最低
- Table pullout
- DuplicateWeedout
- LooseScan
- Materialization
- FirstMatch
如果IN子查询不符合转换为semi-join的条件，那么查询优化器会从下边两种策略中找出一种成本更低的方式执行子查询：
- 先将子查询物化之后再执行查询
- 执行IN to EXISTS转换。

索引

Innodb数据结构是B+树，非叶子节点储存页目录，叶子节点储存真正的页，页内储存真实数据

聚簇索引

我们上边介绍的B+树本身就是一个目录，或者说本身就是一个索引。它有两个特点：

使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
- 页内的记录是按照主键的大小顺序排成一个单向链表。
- 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
- 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
B+树的叶子节点存储的是完整的用户记录。所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建（后边会介绍索引相关的语句），InnoDB存储引擎会自动的为我们创建聚簇索引。另外有趣的一点是，在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

二级索引

二级索引是以指定列进行排序建立索引，叶子节点储存主键，查询到对应主键后再进行回表查询到真实数据