从根上理解mysql

前置知识

mysql的连接

mysql的连接方式有三种：TCP,IP和命名管道与共享内存 ，unix

• 对于服务器中的环境，一般采用tcpip进行维护，其默认绑定3306端口号。mysql服务器默认监控3306端口，当然你可以考虑可以改
• 对于window除了上面的这个方式也可以考虑使用命名管道和共享内存
• 对于unix系统，我们可以考虑使用unix域套接字进行通信

服务器处理客户端请求

无论服务器和客户端最后采用那种方式进行通信，实现的效果都是：客户端进程向服务器发送一段文本——mysql语句，服务器进程处理后再向客户端进程发送一段文本——处理结果

第一步：连接管理

• 客户端进程可以采用上面的连接方式进行连接
• 每当一个客户端进程连接到服务器进程时候，服务器进程都会创建一个连接（线程），但是在离开的时候，服务器并不会销毁这个线程，而是会保留下来把这个线程分配给新的客户端
• 在客户端发起连接的时候，需要携带对应的主机信息，用户名，密码等这些东西，如果认证失败的话服务器就会拒绝连接

第二步：解析与优化

1.查询缓存：mysql可以通过进行缓存之前查询过的数据，不过因为没有什么用而在之后被删除 2.语法解析：mysql对收到的缓存进行解析，判断语法是否正确，从文本中提取出来需要查询的表 3.语法优化：mysql会对我们的语句进行优化，使得连接更加的简单方便

第三步：存储引擎

mysql支持非常多的存储引擎，其中比较常用的就是InnoDB和MyISAM

--创建表时指定储存引擎

CREATE TABLE XXX{
  	建表语句
}ENGINE = 储存引擎名称

--修改表的存储引擎

ALTER TABLE XXX ENGINE = 存储引擎名称;

在命令行上使用选项

• 如果我们想要禁用tcp/ip的通信方式的话可以这样

  mysqld --skip-networking
  --这个情况下使用IP去登陆就不行了
  

• 我们还可以修改默认表的存储引擎

  mysqld --default-storage-engine=MyISAM
  

• 上面使用的命令的选项都为长形式选项，还有一些常见的短形式选项供你参考

  长形式     短形式
  --host     -h
  --user     -u
  --password -p
  --port     -P(注意这里是大写)
  --version  -v
  

在配置文件上使用选项

在命令行中设置启动选项只是对当前次启动生效，如果想让其一直生效就需要更改启动文件

配置文件的内容

[server]
option1             #这里的option1不需要选项值
option2 = value     #这里的option2需要选项值
[mysqld]
...
[client]
...

配置文件的优先级

• 对于多个配置文件：如果我们在多个目录下配置有相同的配置文件，以最后一个配置文件为准

• 对于同一个配置文件：在同一个配置文件里面的不同【】下面如果出现了相同的选项以最后出现的一个为准

• 如果想让mysql在指定的目录下搜索配置文件的话

  mysqld --defaults-file=/tmp/myconfig.txt
  

系统变量

查看系统变量

我们可以使用类似《like》的命令去查看系统变量

SHOW VARIABLES LIKE 'XXXX%（加%的意思代表模糊查询）'

设置系统变量

• 通过命令行添加启动选项

  mysqld --default-storage-engine=MyISAM --max connections=10
  

• 通过配置文件添加启动选项

  [server]
  max-connection=10
  

更改系统变量

对于大部分系统变量来说，他们的值可以在服务器程序运行过程中进行动态修改，而无需停止并且重启服务器

系统变量分为两种

• GLOBAL 全局变量：这是每一个客户端默认的，在没有特殊指定的情况下都分配这个
• SESSION 会话变量：这是针对一个线程的，在需要的时候专门为对于的进行分配

查看系统变量

SHOW GLOBAL VARIABLES LIKE 'defaults-storage-engine';
SHOW SESSION VARIABLES LIKE 'defaults-storage-engine';

设置系统变量

SET SEESION defaults-storage-engine = MyISAMl
SET GLOBAL defaults-storage-engine = MyISAM;

注意事项

• 并不是所有系统变量都有GLOBAL 和 SESSION的作用范围
• 有些系统变量是只读的，并不能设置值

启动选项和系统变量的区别

启动选项是在程序启动的时候我们程序员传递的一些参数，而系统变量是影响服务器运行行为的变量，他们之间的关系如下

• 大部分系统变量都可以被当作启动选项传入
• 有些系统变量是程序运行过程中自动生成的，是不可以当作启动选项来设置的
• 有些启动选项也不是系统变量

状态变量

• 状态变量是用来了解服务器程序运行情况的一个客观变量
• 状态变量的值只能由服务器程序自己来设置，我们程序员是不能设置的

字符串的比较规则简介

字符串的映射规则

• 字符串的映射是通过一个字节映射成为一个二进制数据的方式进行

字符串的比较规则

• 字符串的比较规则是通过将字符转化为二进制的形式。然后通过二进制进行比较
• 字符集也有很多种，比如说gbk，utf-8等都是

字符串比较规则的应用

服务器级别的比较规则

• mysql提供了两个系统变量来设定服务器级别的字符集和比较规则。这个就是默认的比较规则
• 我们可以在服务器运行过程中或者在配置文件中的[server]栏中修改他们

数据库级别的比较规则

• 我们也可以在创建数据库的时候指定比较规则
• 一旦设定规则后，后续这两个变量都是只读的。
• 如果不指定，服务器会使用服务器级别的比较规则

表级别

• 与列级别相同，只有creative table
• 如果不指定，就会使用数据库的

列级别

• 与表级别相同
• 如果不指定，就会使用表的作为比较规则

如果更改了字符串集或者比较规则其中一个，则另外一个也会默认跟着变化

字符串转换

对于同一个字符串可能会有不同的情况，mysql在这时候就需要充当翻译官将字符翻译成为所需要的类型，写入表中

翻译的过程

• 输入一个字符假设为utf-8编码
• mysql在内部转换为表格需要的编码（比如说gbk）
• 写入元素
• 之后在按照utf-8返回写入的结果
• 打印到客户端上

我们为了减少翻译的过程，我们通常吧client，connection，results这三个系统变量都设置成一样的。

SET NAME utf8；--一步到位

InnoDB记录结构

InnoDB页简介

• InnoDB是一个将表中数据储存到磁盘上的储存引擎
• 对于读取数据的时候，InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间的交互单位，一般为16k，然后一次性的进行读取和写入

InnoDB行格式

InnnoDB有四种行格式。指定行格式的语法为

CREATE TABLE 表名 ROW-FORMAT=行格式名称

InnoDB行格式1：COMPACT 行格式

格式

• 一行中分为两大块，第一块用来记录额外信息，第二行用来记录真实数据
• 记录额外信息的块又分为变长字段长度列表，NULL值列表，记录头信息
• 记录真实数据的为记录值

记录额外信息——变长字段列表

• 这个变长字段列表占用为两部分：真正的数据内容，占用的字节数
• 我们把所有的变长字段的真实数据占用的字节长度都存放在记录开头部位，从而形成一个变长的字段长度列表，各变长字段数据占用字节数按照列的顺序逆序排放
• 变长字段长度列表只储存值为非NULL的列内容占用的长度，值为NULL的列的长度是不储存的

NULL值列表

• 首先统计表中允许储存NULL 的列有哪些
• 如果表中没有允许储存NULL 的列，NULL值列表也不存在了，否则允许每个允许储存NULL 的列对应一个二进制位置，他们按照列的逆序顺序存放
• 当二进制位的值为1时，代表该列的值为NULL，二进制位值为0的时候代表该列的值不为NULL -MYSQL规定NULL值列表必须用整数葛字节表示，如果使用二进制的个数不是整数葛字节，则在字节的高位补0

记录头信息

• 记录头信息由5个字节组成，也就是40个二进制位，代表不同的意思

记录的真实数据

• 对于每一个我们自己定义的列的数据外，Mysql也会为每个记录默认添加一些其他列，包括：
  • 行ID
  • 事物ID
  • 回滚指针

行溢出数据

VARCHAR最多能储存的数据

• varchar类最多能存储的数据最多可以占用65535个字节，但是具体储存多少，与其所使用的字符集有关系

记录数据太多所产生的溢出

• 对于内存占用非常大的列，在记录真实数据处只会存储该列一部分数据，把剩余的数据分散在其他几个页中。
• 然后记录真实数据处用20个字节存储指向这些页的地址
• 这个一部分数据一般也就是指768字节

Dynamic和Compressed行格式

• 这两个的行格式与Compact很想，但是这两对于溢出数据的处理不同，如果发送行溢出，就把溢出的行单独保存起来，主页里面只是使用一个指针指向它

• 这两个之间的不同是，Compressed行格式会采用压缩算法进行压缩，用来节省空间

InnoDB数据页结构

数据页的结构

一个InooDB数据页储存空间大致被划分为了7个部分

• file header 作为文件头部，占用38字节，里有页的一些通用信息
• page header 作为页面头部，占用56字节，这是数据页专有的一些信息
• infimum+supremum：最大记录和最小记录，占用26字节，这是两个虚拟的行记录
• user records：用户记录，占用空间不确定，他是实际储存行记录的内容
• free space ：空闲空间：占用空间不确定，页中尚未使用的空间
• page directory：页面目录，占用空间也不确定，他是记录页中某些记录的相对位置
• File Trailer：文件尾部，占用8个字节，校验页是否完整

记录头信息的作用

delete—mask

• 这个属性代表当前记录是否被删除，占用一个二进制为，0的时候代表没有被删除，1的时候代表已经被删除掉了
• 这个所谓的删除不是真的删除，它们这些数据不会立刻从磁盘上移除，而是会放入要给所谓的垃圾链表，后续进行重用

min—rec-mask

• 表示当前记录在本页中的一个位置

heap-no

• 表示当前记录在本页中的位置
• 对于最小记录和最大记录都是他们固定的，就是infimum以及supermum

record-type

• 这个表示当前记录的类型，一共有四种类型的记录，0表示普通记录，1表示B+树非叶子节点的记录，2表示最小记录，3表示最大记录

next-record

• 他表示从当前记录的真实数据到下一条记录的真实数据和地址偏移量
• 这个下一条记录指的并不是我们按照插入顺序的下一条记录，而是按照主键值由小到大的顺序下一条记录
• 规定infimum记录（最小记录）的下一条记录就是本页中键值最小的用户记录，本页中键值最大的就是supremum记录
• 我们的记录按照主键从小到大的顺序形成一条单链表，不论我们怎么对页中的记录做增删查改的操作，InnoDB始终维护一条记录的单链表，链表中各个节点是按照主键值由小到大的顺序连接起来的
• 如果存在垃圾链表，那么InnoDB不会因为新纪录的插入而给他申请新的存储空间，而是直接复用了原来被删除记录的存储空间

Page-Directory

对于这些数据，InnoDB引擎是这样的分类的

• 将所有正常的记录（包括最大和最小记录，不包括标记和已删除的记录）划分为几个组
• 每个组的最后一条记录（组内最大的记录）的头信息n-owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录
• 将每个组最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页的为部的地方。这个地方就是所谓的page Directory，也就是页目录
• 这个页目录就是所谓的槽

对于这些分组，是这样规定的

• 对于最小记录所在的分组只能有一条记录
• 最大记录所在的分组拥有的记录只能在1-8条之间
• 剩下的分组中记录的条数范围只能是4-8条之间

分组的步骤

• 初始情况下一个数据页里只有最小记录和最大记录两条记录，他们分属于两个组
• 之后每插入一条记录，都会从页目录中找到主键值比本记录的主键值大且差值最小的槽，然后把该槽对应的记录的n-owned值+1，表示本组又添加了一条记录，直到该组的记录数等于8个
• 在一个组的记录数等于8个后再插入一条记录时，会将组中的记录拆分为两个组，一个组四条记录，另外一个组五条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大那条记录的偏移量

在一个数据页中查找指定主键值的记录过程分为两步

• 通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录
• 通过记录next-record属性遍历该槽所在组中的各个记录

Page Header（页面头部）

page header的主要作用就是记录每一个对应的页的一些信息，包括什么已经删除占用的字节数，最后插入记录的为止，记录插入的方向等

File Header（文件头部）

File Header作为一个通用的头部，他描述了一些针对各种页都通用的一些信息，比方说这个页是多少，上一个页是谁，下一个页是谁

几个比较重要的部分

FIL-PAGE-SPACE-OR-CHKSUM:这个代表校验和

FIL-PAGE-OFFSET：每一个页都有一个单独的页号，就和身份证一样，InnoDB通过页号来可以唯一定位一个页。

FIL-PAGE-TYPE：这个代表页的类型，比如说数据页和索引页

FIL-PAGE-PREV和FIL-PAGE-NEXT：这两个分别代表本页上一个和下一个页的页号，这样通过一个双向链表把许许多多的页串联起来了，所有的数据页其实是一个双链表

File Trailer

为了检测一个页是否完整，我们在每一个页的尾部都增加了一个File Trailer部分，这个部分由八个字节组成，可分为两个小部分

• 前四个字节代表页的校验和
• 猴子个字节代表页面最后被修改时对应的日志序列位置

B+树索引的使用

索引的代价

空间上的代价

• 显而易见：对每一个页都建立b+树势必要耗费大量的空间 时间上的代价
• 每次对表进行增删查改的操作的时候，都需要看B+树 索引，按照索引排序建立起来了双向链表

B+树索引查找快速的方法

1.二分查找是B+树的一个基本方法 2.数据排列的方式一般是按照最左边的名称向最右边的名称依次排列 3.（mysql匹配最左边的列）：如果我们想使用联合索引中尽可能多的列，搜索条件中各个列必须是联合索引从最左边连续的列 4.（mysql匹配列前缀）：匹配列前缀的时候，使用单一的%匹配速度最快，例如 as%，因为这个mysql里面是不需要全表扫描的，速度最快 5.（mysql匹配范围值）如果多个列同时进行范围查找 话，只有索引最左边那个列进行范围查找的时候才能用到B+树索引 6.用于排序的情况：对于需要排序的东西，我们通过索引取出我们直接将它的语句提取出来然后进行找到对应的语句进行排序，使用快排之类的即可

这里列举以下不能使用索引进行排序的情况
1.asc，desc混用
2.whereh出现非排序用到的索引列
3.排序列包含非同一个索引的列
4.排序列使用的复杂的表达式

回表的代价

回表

回表操作

• 就是首先通过二级索引查找
• 再经过聚簇索引进行查找得出的结果

避免回表

我们可以考虑告别回表带来的性能损耗，我们最好在查询列表中（where）只包含索引列

我不不鼓励使用*作为查询列表，因为这会带来较大的性能损耗

如何挑选索引

只为用于搜索，排序，或分组的列创建索引

考虑列的基数

• 列的基数是指某一列中不重复数据的个数，在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基数越小，该列中的值越集中
• 最好为那些列的基数大的列建立索引，为基数太小列建立索引的效果可能不好

索引列的类型尽量小

• 索引列的类型表示该类型能表示的数据范围大小，在表示的数据范围允许的情况下，尽量让索引使用较小的类型（能使用int久不用bigint）
  • 数据类型越小，查询的时候进行的操作会比较快
  • 数据类型越小，占用的储存空间越少，一个数据页里面能放下的东西也就越多

索引字符串的前缀

• 我们一般只是对字符串的前几个字符进行索引
• 这么做加快索引速度

让索引列在比较表达式中单独出现

where a*2>4;
where a>4/2;

以上这两种里面选第二种

• 第一种会依次对数据进行遍历，计算这个表达式的值是不是小于4
• 第二种会使用索引进行判断，尽可能的使用索引

主键的插入顺序

• 我们应当让主键具有AUTO-INCREMENT，让储存引擎自己为表生成主键，而不是我们手动插入

避免冗余和重复索引

MySQL的数据目录

前言

数据库与文件系统的关系：像InnoDB，MyISAM这样的储存引擎是把我们的表储存在文件系统之上的

InnoDB是如何存储表数据的

InnoDB是通过表空间/文件空间存放的，这是一个抽象的概念，它对应文件系统是一个或者多个真实的文件

• 系统表空间：在没有指定的情况下，表中的数据都会存储在这表空间里面，我们把它叫做系统表空间
• 独立表空间：这个在mysql 5.6.6版本后，InnoDB并不会默认把各个表的数据都储存在系统表空间中。而是为每一个表建立一个独立的表空间，我们创建了多少个表就会有多少个表空间

MyISAM如何存储表数据的呢

MyISAM没有所谓了表空间，表数据都会被存放到对应的数据库子目录下

文件系统对数据库的影响

Mysql的数据都是存在在文件系统中的。就不得不受到文件系统的一些制约，这在数据库和表的命名，表的大小和性能方面体现的比较明显，比如

• 数据库名称和表名称不得超过文件系统所允许的最大长度
• 一些特殊字符
• 文件长度受到文件系统最大长度限制

MySQL系统数据库简介

mysql

• 核心数据库，储存了Mysql用户账户和权限信息

information schema

• 储存了mysql服务器维护的其他数据库的信息
• 包括视图，表，触发器等

performance-schma

• 这个数据库主要保存mysql服务器运行过程中的一些状态信息
• 对mysql服务器的一个性能监控

sys

• 把上面两者结合起来，让程序员可以更加方便的了解mysql服务器的一些性能信息

InnoDB表空间

区，与组

• 为了更好的管理这些“页”InnoDB引擎还设计了区这个概念

• 对于16kb的页来说，连续64个页就是一个区

• 连续256个区就是一个组

对于组来说

第一个组的最开始三个页面都是固定的，它们分别是

• FSP-HDR：记录表空间的整体属性和本组的所有区
• IBUF-BITMAP：记录关于INSERT-BUFFER的信息
• INODE：记录了INODE的数据结构

其他各组最开始的两个页面都是固定的

• XDES：用来登记本组256个区的属性
• IBUF-BITMAP

段

概述

为了区分B+树中的叶子结构，他们把叶子结构和扉页子结构放在独特的区中，所以存放叶子节点的集合就又被称为段，非叶子节点也算一个段

所以一个节点会有两个段，一个叶子节点段，一个非叶子节点段

分配策略

• 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配储存空间的
• 当某个段已经占用了32个碎片区页面之后，就会以完整的区为单位来分配储存空间

区的分类

区大致上可以分为四类（区的四种状态）

• 空闲的区（FREE)：没有用到对应区的界面
• 有剩余碎片的区(FREE-FRAG)：表示碎片区中还有可用的界面
• 没有剩余空间的碎片区(FULL-FRAG)：表示碎片区中所有页面都能使用，没有空血页面
• 附属于某个段的区(FSEG)：对于一些具有特殊作用的段，这些段中的数据量很大的时候将使用区来作为基本的分配单位

为了方便管理这些区，我们有一个叫做XDES Entry的结构专门管理这些区

• Segment ID（8字节）：表示该区所在的段，每一个段都有唯一的编号
• List Node（12字节）：这些部分将若干个XDES Entry结构串成一个链表，作为双向链表
• State（4字节）这个字段表明区的状态，也就是上面那个
• Page State Bitmap（16字节）：表示对应的页是否为空闲的

链表

XDES Entry链表

XDES Entry通过下属的链表，为让门分门别类的管理这些区

• 把状态为FREE的区对应的XDES Entry结构通过List Node来连接成为一个链表，这个链表我们称它为FREE链表
• 把状态为FREE-FRAG的区对应的XDES Entry结构通过List Node来连接为一个链表
• 把状态为FULL-FRAG的区对应的结构也链接为一个链表

这样当我们在插入的时候，通过拿出对应的链表头结点，然后取一些页去插入数据即可

当段中数据占满32页了之后，就直接申请完整的区来插入数据就可以了

段中的XDES Entry链表

为了方便维护，每一个段中也为XDRS Entry建立了链表

• FREE链表：同一段中，所有页面都是空闲的区的XDES Entry结构会被加入到这个链表
• NOT-NULL链表：同一段中，任有空闲空间的区对应的XDES Entry结构会被加入到这个链表
• FULL链表：同一段中，已经没有空闲空间的区对应的XDES Entry会被加入到这个链表

这两个链表每个结构都记录了头结点，尾节点的位置以及对应的结点数。

段的结构

对于每一个段，我们都定义了一个INODE Entry结构来记录一下段中的属性

• Segment ID（8字节）：指这个INODE Entry结构对应段的编号
• NOT-FULL-N-USED（4字节）：指的是在NOT-FULL链表中已经使用了多少个页面。下次分配空闲页面的时候可以直接根据这个字段的值定位到
• 三个LIST BASE NODE（共16x3字节）分别为段的FREE链表，NOT-NULL链表，FULL链表
• Magic Number（4字节）：标记INODE Entry这个值是否被初始化了
• Fragment Array Entry（4字节）：每一个这样的结构都对应着一个零散的页面，他有32个，一共为4字节，表示一个零散页面的页号

单表访问方法

mysql的访问方法

大致分为两种

• 使用全表扫描进行查询：把表的每一行记录都扫描一边，把符合条件的加入结果集
• 使用索引：使用主键/普通索引/二级索引/整个索引进行查询

const

当使用主键/二级索引定位一条记录的时候，查询步骤如下：

• 先直接利用主键值在聚簇索引中定位对应的用户记录，找到id（二级索引）
• 然后再从聚簇索引中得到上一步id的值（聚簇索引）

ref

当使用二级索引与常数进行等值比较的时候，查询步骤如下

• 先从二级索引找到和值相等的所有记录
• 然后将上一步对应的id，返回到聚簇索引中进行查询

注意：

对于NULL值，一般之恩呢采用ref，而不是const

const比较方法是具体到单一记录的，ref是所有的相等记录

对于存在and 的索引比较的花，如果最左边的连续索引列不全是等值比较的话，他的访问方法就不能称为ref了

ref-or-null

有的时候我们不仅想要找出某个常数，还需要所有记录为null的记录

• 先从二级索引中找到对应的值
• 然后再根据上一步得到的id获得完整的用户记录

range

对于有的时候我们需要找出一定范围内的比较方法的时候，我们会使用range的比较方法

• 这种操作可以是全表扫描，也可以是对应的二级查询
• 对于range查询，使用的语句可以经过mysql本身进行化简之后查询

mysql的索引合并

Intersection合并

对于a and b（注意这个b不是null）的查询，我们可以使用交集查询，这种查询只有一下特殊几个情况会使用

• 二级索引列是等值匹配的情况
• 主键列可以是范围匹配

在某些情况下，联合索引可能会替代Intersection合并

union合并，sort union合并 对于where a or b这种情况，我们可以采用并集去合并，并集合并有以下这几种情况

• 二级索引列是等值匹配的情况
• 主键列可以是范围匹配
• 使用Intersection索引合并的搜索条件

对于sort union，相比较于union 为了满足第一条条件，一般情况下我们可以考虑将对应的列排序之后插入

InnoDB统计数据是如何收集的

InnoDB是默认以表为单位来进行收集和统计数据的，包括两种方式

基于磁盘的永久性统计数据

这些数据都储存在了两个表里面

• innodb-index-stats
• innodb-table-stats
  • single-table代表记录的估计值
  • clustered-index-size代表一个表聚簇索引的估计值
  • sum-of-other-index-sizes代表single-table表其他索引一共占用175个页面，这个也是个估计值
  • 收集方式如下
    • 从数据字典里面找到表各个索引对应根页面的信息
    • 从根页面page header找到叶子节点和非叶子节点段的segment header
    • 找到对应的inode entry结构

如果需要定期更新统计数据，就需要

• 开启innodb-stats-auto-recalc
• 或者手动analyze table来更新数据

基于服务器的非永久性统计数据

他的主要用途是生成经常变化的执行计划，已经不常用了

Mysql基于规则的优化

条件化简

移除不必要的括号：mysql会利用表达式的原意，移除括号

常量传递：对于表达式里面的已经有的常量，在后面进行判断的时候使用进行常量替代变量

移除没用的条件：将表达式里面永远是true/false的式子移除

计算表达式：提前将表达式计算出来，并且用常量替代

HAVING/WHERE子句合并：如果表达式中没有出现sum/max等聚集函数以及group by子句，优化器就会合并这两个

外连接消除

什么时候内连接可以和外连接相互转换：

当驱动表和被驱动表的都没有null值的时候，此时内外连接就是相同的

子查询优化

子查询语法

select子句中：select（select。。。）：子查询

from子句中：from（select 。。）：派生表

where …in…子句中：select from t1 where m1 in（select m2 from t2）

order by group by语句也同样支持，但是没有什么用

子查询优化

标量子查询：那些只返回单一值的子查询称为标量子查询：select （select m1 from t1）/select from t1 where n1=(select m2 from t2)

行子查询：返回一条记录的子查询，不过这条记录需要包括多个列：select *from t1 where（m1，n1）=select （m2，n2 from t2 limit 1）

列子查询：查询一个列的数据 ，不过这个列需要包含多个记录：select *from t1 where m1 in（select m2 from t2）

表子查询：子查询结果包含很多个记录，又包含很多个列 ：select * from t1 where （m1，n1） in （select m2，n2 from t2）

按与外层查询的关系来区分子查询

不相关子查询：我们可以单独运行出结果，而不依赖于外层查询的值

相关子查询：子查询的执行需要依赖于外层查询的值，我们就可以把这个查询称为相关子查询

子查询在mysql中是怎么执行的

对于不相关子查询（=/>/<…）：mysql会分别独立执行外层查询和子查询，就当作两个单表查询就好了

• 先单独执行子查询
• 然后按照上一步子查询的结果当作外层查询的参数再执行外层查询

对于相关子查询来说

• 先从外层查询获取一条记录，先从s1表中获取一条记录
• 然后上一步骤获取的那条记录中找出子查询涉及到的值，然后执行子查询
• 最后根据子查询的查询结果来检测外层查询where子句条件是否成立，如果成立，把外层查询的那条记录加入结果集，否则就丢弃
• 再次执行第一步

对于IN 子查询来说（where xx in…)

物化表的提出

• 一般不直接将不相关的子查询的结果集当作外层查询的参数，而是将结果写入一个临时表里面，并且去重（让占用空间表空间减少）

• 如果临时表空间过大，就会采用基于磁盘的储存引擎进行存储，否则就是临时引擎memory

将子查询转换为半连接

半连接的定义：对于s1表的某条记录来说，我们只关心s2表中是否存在与之匹配的记录，而不关心具体是多少条，最终结果只保留s1表终的记录，一般用三种办法

• 子查询的表向上拉
• 重复值消除
• 松散索引扫描
• 将子查询转化为半连接
• 首次匹配

半连接查询的适用条件

• 该子查询必须是IN语句组成的布尔表达式，并在外层查询的where或者ON子句中出现
• 外层查询也可以有其他搜索条件，只不过和IN子查询的搜索条件必须使用and连接起来
• 该子查询必须是一个单一的查询，不能是若干个查询由union连接起来的形式
• 不能包括group by 或者having语句或者聚集函数

查询优化的百科全书——Explain select。。

一条查询语句在经过优化之后会生成一个所谓的执行计划，explain语句输出每条记录都对应着某个单表的访问方法

记录的东西包括以下几种

table：代表该表的表名

id：查询语句中每次出现一个select关键字，都会有唯一的id值，from后面可以跟着多个表，但是这些记录的id是相同的

• 注意，对于子查询，因为有可能查询优化器会将子查询转化为连接查询，这个时候会出现两个id都一样的情况

select—type：我们一般可以查询小的select-type属性，就知道整个小查询在大查询中扮演了一个什么样子的角色

type：代表了访问方法是什么

possible——keys，keys：代表待定使用的查询索引，在mysql比较之后会使用最少的那个。这个列中的值也不是越多越好，可能使用的索引越多，查询优化器计算查询成本的时候就会花费很长时间

key——len：表示当优化器决定使用某个索引执行查询的时候，该索引记录最大长度

• 对于使用固定长度的索引来说，就是固定值
• 如果该索引列可以储存null值，key-len比不可以存储null值多一个字节
• 对于变长字段来说，都会有两个字节的空间来储存该变长列的实际长途

ref：ref表示的就是索引列等值匹配的东西是什么

row：代表预计需要扫描的行数

filtered：代表mysql在计算驱动表扇出的时候采用的一个策略

Extra：这个列是用来说明一些额外信息的，她更准确的介绍了mysql到底要执行什么样的查询语句

• no table used：这个就是没有from子句的时候会提示的
• Impossible where ：如果查询语句永远是false就是这样的信息
• no matching min/max row：没有匹配的搜索结果
• using index：搜索只包含索引的列
• using index condition：虽然出现了索引列，但是用不到
• using where：全表扫描并且使用where有针对性的去选择该表
• using join buffer 使用join buffer来加快速度
• no exist：当我们使用左外连接的时候，如果where子句中包含要求被驱动表某个列等于null值的搜索条件，而且那个列又不是储存null值的就会这样
• using intersect：准备使用索引合并的方式进行查询
• zero limit：当limit为0的时候会出现
• using filesort：对目录排序之后就可以用到索引
• using temporary：使用临时表去完成一些功能
• start temporary end temporary：使用临时表（semi join）完成去重操作
• loosescan：将in 查询转换为 semi join时候
• firstmatch：采用firstmatch技术进行semi join匹配

json格式的执行计划

我们可以通过在explian单词和真正的查询语句中间加上一个 FORMAT=JSON

我们在最下面的cost_info查看成本

• read_cost：IO成本：CPU成本
• eval_cost：单独查询的数据成本和数据量组成
• prefix_cost：代表连接查询的成本
• 总成本等于各个成本相加

Optimizer trace 功能

使用Optimizer trace可以看到优化器为你带来的完整的优化，步骤如下

• 打开optimizer trace功能：SET optimizer_trace = "enable=on"
• 输入自己的查询语句：select。。。
• 从表中查询上一个查询的优化过程：SELECT *FROM information_ schema.OPTIMIOZER_TRACE
• 停止查看的时候关闭：SET optimizer_trace = "enable=off"

调节磁盘和CPU 矛盾的InnoDB Buffer Pool

在mysql服务器启动的时候，就向操作系统申请了一片连续的内存，这个内存就叫做buffer pool，默认的大小是128mb

buffer pool 的内部组成

buffer pool的128mb也是被分为了一个一个页，每一个页的大小都是16kb，并且有基本的控制信息表空间编号，页号，缓存页在buffer pool的信息，节点信息，锁信息等

free链表的管理

对于已经被使用的缓存页面，就被使用了，那么剩下的页面就需要一个双向链表管理，控制块其中之一就是实现这个功能

缓存页面的哈希处理 对于快速找到对应的缓存页，我们应该使用hash table对于哈希表进行处理，使用表空间号+页号作为key，缓存页作为value 创建一个哈希表

flush链表管理

如果我们修改了buffer pool某个缓存页的数据，那就会和磁盘上的不一致，这种页叫做脏页

一般情况下，mysql会在一定的时间统一对脏页进行同步，方法是创建一个链表，凡是修改过的都会被刷新到磁盘上，这个链表于free差不多

LRU链表的管理

mysql的链表的基本原理是LRU链表，就是将越频繁访问的放在头部，越不平凡的放在末尾。

mysql除了LRU，还做了分区处理，把高频使用的叫做young区域，反之叫做old，随着程序的运行，区域可能会发生变化。

管理方法如下

• 当磁盘上某个页面在初次加载到buffer pool某个缓存页的时候，该缓存页对应的控制块会被放到old头部
• 在对某个处在old区域的缓存页进行第一次访问时就在它对应的控制块中记录下来这个访问的时间，如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该页面就不会从old区域移动到young区域头部，否则它移动到young区域的头部

刷新脏页到磁盘

从LRU链表中的冷数据中刷新一部分页面到磁盘

• 后台程序会定时从LRU链表为尾部扫描一些页面，如果发现脏页就刷到磁盘里面

从flush链表刷新一部分页面到磁盘

• 后台程序也会从flush链表中刷新，这种情况出现在当前页面不是很繁忙

多个buffer pool

buffer pool不用考虑并发管理的问题，因为每一个buffer pool都管理的自己的链表，独立的申请空间，独立的释放内存，并不会相互影响

事务简介

事务的起源

所谓事务，就是让数据库发生的事情符合状态转换的规则。他要满足几个条件

事物的特性

原子性：也就是指事务只有结果没有过程，只有做事是否成功，没有说成功了一半的情况

隔离性：因为对于数据库来说，操作可能交替执行，所以必须保证其访问相同的数据

一致性：数据库的数据必须与现实中保持一致性（比如高考没有负分），这一部分需要数据库和程序员共同保证

持久性：数据库所执行的转换操作都应该在磁盘上对应的保留下来

事务的状态

大概有这几个状态

活动的：事务对应的数据库在执行的时候，我们就说事务处在活动的状态

部分提交的：因为在内存中执行的数据，他没有刷新到磁盘，就是部分提交

失败的：当事务在提交/处理的时候出现问题，就是失败的状态。那么就会撤销之前的影响，这个操作叫做回滚

终止的：如果事务出现失败的状态，那么在当他回滚后就会变成终止的

提交的：也就是同步到磁盘上的状态

事务操作回顾

begin：开始事务

commit：提交事务

rollback：终止事务

innodb：支持事务，myisam：不支持事务

savepoint：保存点

事务id

对于一个事务来说，一个事务可以是只读事务，或者是一个读写事务。

• 对于一个只读事务来说，这个事务是不会分配事务id的，因为他不涉及到增删改的操作。
• 对于读写事务来说，就会有一个独一无二的事务id

服务器会在内存中维护一个全局变量，当需要时候，就会用他的值分配一个事务id

redo日志

redo日志的定义

redo日志用来记录修改的数据，并且在刷新数据的时候将需要的数据刷新到磁盘就可以了

使用redo日志的好处

• redo日志占用的空间比较小。
• redo日志是顺序写入磁盘的

redo日志的格式

格式： type spaceID pagenumber data

• type：该条redo日志的类型
• space ID：表空间ID
• page number：页号
• data：该条redo日志的具体内容

mini-transaction

以组的方式写入日志

• 日志一个比较重要的属性就是其是否可分割，这个的原因就是其是否在插入后空间出现溢出的情况
• 为了防止这种情况，同时保证原子性，就会生成多条redo日志，执行插入，这种插入也叫做悲观插入

redo 日志刷新的时机

• log buffer不足的时候，
• 事务提交的时候
• 后台线程刷新的时候
• 正常关闭服务器的时候
• 作checkpoint的时候

redo日志文件的格式

redo日志本质上是一块连续的内存空间，这些空间被划分成为一个小块一个小块的，从logfile0-logfileN

每个redo日志前2048个字节是用来储存一些管理信息的，他们被分为四块

• log file header：描述redo日志的整体属性
• checkpoint1：记录checkpoint的一些属性
• 第三个block没有使用
• checkpoint2：结构和checkpoint1一样

在写入日志的时候，mysql会通过mtr生成一组一组的进行写入，类似于指针的操作进行，每次写完一组，mtr_i的i就会加一

每一组mtr生成的redo日志都有一个唯一的LSN值相对应。LSN值越小，说明产生的越早

判断redo日志占用的磁盘空间是否可以覆盖的主要依据是脏页是否已经刷新到磁盘里面

在mtr执行的过程中，可能修改过的页面是会被加入到flush链表当中的。他们的排序顺序是按照页面第一次修改的时间从大到小进行排序的，被多次更新的页面不会插入flush链表当中。

崩溃恢复

redo日志在崩溃恢复的时候会有大作用。在服务器崩溃的时候，首先

• 确定最近发生的checkpoint的信息
• 按照redo日志的顺序顺次扫描checkpointlsn的个条redo日志，按照日志中记载的内容将他恢复出来。一般使用哈希表的方法加快恢复过程
• 在恢复的过程中，跳过已经刷新到磁盘的页面了

undo日志

undo日志存在的意义

undo日志是需要满足事务回滚的需求，undo日志就是要把回滚的时候的东西记下来。

undo日志的排列和操作

为了实现事务的原子性，在引擎实际进行增删查改的时候，都要先把对应的undo日志记录下来，一般每一次记录做一个改动，对应一条undo日志

与redo日志类似，undo日志也是从undo1一直到undon这样按顺序排列

索引中roll-pointer列也就是代表着undo日志，其本质就是一个指针，指向对应的undo日志

undo日志的插入，删除操作

插入

• undo日志在插入的时候需要记录id列和占用储存空间的长度

• 和redo日志一样，undo日志会记录对某些属性做压缩处理

删除

我们使用链表来对记录进行管理

• 首先在删除链表要删除的位置进行标记，标记序号为1
• 然后在删除语句将事务提交之后，会有专门的线程来把记录进行删除
• 注意：在事务提交之前，垃圾链表会存在，但是不会删除。也就是说只会经历第一条，不会经历第二条

update

在不更新主键的情况下

• 就地更新：如果列前后占用的数据空间一样大就进行就地更新
• 先删除掉旧记录，再插入新纪录：这是两个储存空间大小不一致的情况下

在更新主键的情况下

• 使用一个新的undo日志。
• 这个undo日志的后面跟着被更新列在记录中的位置，更新前的储存空间大小，更新前该列的真实值

更新完成后

• 将旧的记录进行delete mark操作，确定他要删除的
• 后续会有线程把他放入垃圾链表，然后删除

undo日志的结构

undo日志也是使用链表，他有一个通用的链表结构，是一个循环双向链表

对于一个undo日志可能放不下的情况下，undo日志提供了多个链表。记录管理信息

mysql规定普通表和临时表的记录改动的时候产生的undo日志要分别记录，一个事物最多以四个undo页面为节点产生链表

undo日志的链表是按需分配的，啥时候需要啥时候分配，不需要就不分配

不同事务执行过程中产生的undo日志需要被写入到不同undo页面的链表中

重用undo页面

undo列表重用的条件

• 该链表中只包含一个undo页面
• 该undo页面使用的空间小于整个页面的3/4

重用策略不同

• insert undo链表：对于这种链表对应的策略一般就是在临时undo事务提交之后，而且这个事务是临时事务，这种就可以直接清除掉
• update undo链表：对于这种正式事务提交了之后，update undo事务提交之后，不能立刻删除，如果想重用之前事务写入的undo日志，就不能像上面那一个一样直接覆盖

回滚段

回滚段的概念

我们都知道一个事务在执行的过程中，可以分配4个undo页面链表，在同一时刻不同事物拥有不同undo页面链表是不一样的。为了更好的管理这个链表，我们又有一个叫做rollback segment header的他页面，这里面放了各个undo页面链表的first undo page页号

mysql规定，每一个rollback segment header都对应一个段，这个段称为rollbacksement，他理论上是无限大的，回滚段有多个，一共128个

类似比喻：undo页面的链表头就相当于班长，回滚段相当于会议室，所有的界面的班长都到这个叫做回滚段的会议室来开会

运行过程

首先对于进入的undo的链表，确定他是班长，不是就报错

事务在提交之后会被重用/释放掉

事务的隔离级别与MVCC

事务并发容易遇到的问题

脏写

如果一个事务修改了另外一个事务未提交过的数据那就意味着发生了脏写

脏读 如果一个事务读到了另一个未提交事务修改过的数据，那就意味着脏读

不可重复读

如果一个事务只能读到另外一个事务已经提交事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务的都能查到最新值

幻读 一个事务先根据某些条件查询出一些记录，之后另外一个事务又向表中插入了这些符合条件的记录，原先的事务再次按照该条件查询时，也能把另外一个事务插入的记录读出来

事务问题的严重性，四种隔离级别

严重性：脏写>脏读>不可重复读>幻读

隔离级别：

• read uncommitted：未提交读：可能发生：脏读，不可重复读，幻读
• read committed：已提交读：可能发生：不可重复读，幻读
• repeatable read：可重复读：可能发生：幻读
• serializable：可串行化：可能发生：都不可能

MVCC

版本链

每次对undo日志进行一次改动，都会记录一条undo日志，每条undo日志都有一个roll-pointer属性，这些undo日志连起来，就会成为一个链表。这个就叫做版本链，他的头节点就记录着目前最新的值

readview

readview指的是去查看版本链的最后一次改动，根据改动的不同，我们根据事务的级别会生成不同的readview，也就是版本链里面不同的undo日志

• read-committed——每次读取数据前生成一个独立的readview

• read-uncommitted——直接读取事务的最新版本

• repeatable-read：在第一次读取数据的时候生成一个readview

锁

解决并发事务带来问题的两种基本方式

• 一般情况下，并发读/并发写会带来问题。我们通过两种方式去解决

行锁

总述

• 对于事务，我们一般在获取锁之后才能执行事务，可以解决脏写问题
• 根据多版本并发控制，我们可以对读写操作进行加锁
• 将读，写操作都上锁：这是一部分特殊情况会带来的问题

一致性读

• 利用MVCC进行单纯的读读操作的的都是一致性读
• 这种读一般不加锁

锁定读

• 对于读写，写读，写写操作，我们都需要加锁，锁又分为几类
  • 共享锁：在事务要读取一条记录的时候，需要先拿到共享锁
  • 独占锁：在事务要更改一条记录的时候，需要拿到独占锁
  • 我们可以认为，共享锁在多个事务的时候，共享锁可以拿到多个，但是独占锁只有一个，独占锁拿到的前提是没有其他事务持有共享锁

加锁的时机

读

• 读时候加共享锁：select …lock in share mode
• 读时候加独占锁：select … for update

写

• delete：独占锁
• update：独占锁，如果有插入按照insert处理
• insert：并不加上述两种锁，加隐式锁

表锁

表锁有两种

• 意向共享锁：当事务准备在某条记录上面加上共享锁的时候，需要在表上先加上意向共享锁
• 意向独占锁：当事务准备在某条记录上面加上独占锁的时候，需要在表上先加上意向独占锁

mysql中其他储存引擎中的锁

其他引擎中的锁

对于myisam memory merge这些储存引擎来说，他们只支持表级锁，而且这些引擎是不支持事务的，所以也没必要弄那么复杂

innodb支持的锁

innodb同时支持行锁和表锁

对于表级别的锁

• 共享锁，独占锁：支持，但是只有在更新/删除语句的时候才会其作用，一般用不到
• 意向共享/意向独占锁：如之前所说的
• AUTO-INC锁：这个东西主要做连续递增插入使用，在插入过程中连续获取递增值

innodb锁的功能

行锁类型

record locks

• 这种锁仅仅会将一条记录锁上。
• 他是有共享锁和独占锁之分的，获取原理同上

gap locks

• 这种锁加上之后表示，他和他前面那条记录之间不允许插入记录
• 这种锁的提出一般都是防止插入幻影记录而提出的

next-key locks

• 这种锁可以同时锁住某条记录，并且可以像gap locks一样阻止记录前面的间隙插入新记录

insert intention locks

• 这种锁是用来确定前面是否有gap locks这样的锁。类似于。。排队
• 插入意向锁并不会阻止别的事务继续获取该记录上面的任何锁

隐式锁

• 这个隐式锁的话，其实并不是一个真的锁（没有锁结构）他的保证是来自于事务id，他相当于加了一个锁
• 在执行的时候，别的事务在执行的时候，他自己会先帮助当前事务生成一个隐式锁，然后自己再生成一个锁进入等待状态

innodb加锁的条件

当满足以下条件的时候，innodb会把这些记录的锁放在一个锁结构中

• 在同一个事务进行加锁
• 被加锁的界面在同一个界面中
• 加锁的类型是一样的
• 等待状态是一样的

生成行锁的过程

• 确定锁结构是对应的事务
• 直接对聚簇索引进行加锁
• 记录行锁所需要的重要信息：space id，page number，nbits
• 记录一些其他信息