MySQL优化学习手札(一)

前言

写本篇的时候想起刚学完SQL基础语法的时候，那个时候的感觉是就这，我是不是得掌握点更高端的啊，这样才好找工作。于是开始搜索优化，碰到了索引这个词，当时记得一个几百万的数据，查单条数据，没在条件上索引的时候，查询是很慢的，大概是一分钟还是多少，有些记不清了，为对应的条件加上索引之后，一秒就查出来了。当时觉得索引这个东西真的是好强大，但我当时还是想搞清楚为什么加了索引，然后查询就快了这么多。查网上的博客说的是，加索引相当于加目录，我当时的反应是这样，MySQL的数据组织形式是一本字典吗？或者像书一样，为这一列数据都加上目录？那好像跟遍历查询也没啥不一样啊。

于是我就在网上搜索相关的文章，在深入浅出数据库索引原理[1]找到了我想要的答案:

如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面说的「平衡树」结构.

对还是从结构上入手，不是相当于加了目录，而是改变了结构，这才是最本质的回答。但上面这句话也不完全对，还有一些疑惑，这个整齐排列的结构是数据结构的哪种结构？这个疑惑还环绕在我的心头，直到某天我看到了《MySQL 是怎样运行的：从根儿上理解 MySQL》这本书，所有的疑惑全都一扫而清。在正文开始之前我们首先要解释清楚的两个问题是:

• 一条SQL语句发送给MySQL服务端之后，经历了怎样的过程，才取出来了数据?
  
• 我们写查询的时候，取出来的数据结构是像一个表格，一行一行的，那么真正的数据结构是什么？
  

我们首先回答第一个问题:一条SQL语句发送给MySQL服务端之后，经历怎样的过程，才取出来了数据?

走近MySQL

MySQL客户端向MySQL服务端发送SQL语句，服务端向客户端发送数据响应本质上是一个进程间通信的过程！MySQL支持下面三种客户端进程和服务器进程的通信方式:

• 命名管道和共享内存
• TCP/IP
• Unix域套接字文件

不管客户端选取哪种方式和服务端进行通信，最终的效果都是客户端进程向服务器进程发送一段文本，服务器进程处理过后向客户端进程响应一段数据。那服务端究竟对客户端进程发送的请求做了什么样的处理呢？我们可以用一段处理SQL流程来展示一下大致的过程:

服务端进程处理客户端发送的数据大致要经过三个过程，分别是连接管理、解析优化、存储引擎。

• 连接管理:   这个很好理解类似于两个进程之间打电话，接通了，才能说话。
  
• 解析优化:
  

• 查询缓存: 缓存查询结果，MySQL服务器进程出来查询请求的过程会将刚刚处理过的请求和结果缓存起来，如果下一次有一模一样的请求过来，直接从缓存中查找结果就好了，就不用再去表中去查找。这个查询缓存可以在不同的客户端之间共享，也就是说客户端A刚刚查询了一个语句，而客户端B之后发送了同样的查询请求，那么客户端的这次查询就可以直接使用查询缓存中的数据。
  当然MySQL服务端并没有那么智能，如果两个查询请求在存在任何字符上的不同，都会无法命中缓存。另外，如果请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如mysql、information_schema、performance_schema数据中的表，这个请求同样不会被缓存。既然是缓存，也就有缓存失效的时候，MySQL会检测缓存涉及的每张表，只要该表的结构或者数据被修改，那么使用该表的所有缓存查询都会失效。
  虽然查询缓存可以提升系统性能，但为了维护这块缓存而造成的开销也是不小的，从MySQL 5.7.20开始不推荐使用查询缓存，并且在MYSQL 8.0中被移除。
  
• 语法解析：如果查询缓存没有命中，接下来就需要进入正式的查询阶段了，毕竟SQL语句只是一段文本，所以MySQL服务器进程会首先对这段文本做分析，判断请求的语法是否正确，然后从文本中将要查询的表、各种条件提取出来放到MySQL服务器内部使用的一些数据上来。
  
• 查询优化

语法解析之后，服务器获得了查询数据所必要的信息，比如想查询的列是哪些，表示哪个等等，但光有这些是不够的，因为我们写的MySQL语句可能性能不佳，MySQL的优化程序会对我们的语句做一些优化，例如外连接转换为内连接、表达式简化、子查询转换为连接等等。优化的结果就是生成一个执行计划，这个计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的。我们可以使用EXPLAIN语句来查看某个语句的执行计划。执行计划这部分后面会讲。

• 存储引擎:
  

到目前为止，MySQL服务端进程还没有访问真实的数据表，MySQL的开发人员将数据的存储和提取操作都封装到了一个叫存储引擎的模块里。我们直到查出来结果是由一行一行的记录组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是存储引擎负责的事情。为了实现不同的功能，MySQL提供了各式各样的存储引擎，不同的存储引擎管理的表具体的存储结构可能不同，采用的存取算法也可能不同。

事实上存储引擎以前叫“表处理器”，可能是后面人们觉得太土，就改叫“存储引擎了”，它的功能就是接收上层下来的指令，然后对表中的数据进行提取或修改操作。

为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化、查询优化这些并不涉及真实数据存储的功能划分为MySQL Server的功能，把真实存取数据的功能划分为存储引擎的功能。各种不同的存储引擎向上边的MySQL Server层提供统一的调用接口(也就是存储引擎API)，包含了几十个底层函数, 像“ 插入记录”，“修改记录”等等。

所以在MySQL 完成了查询优化后，只需要生成的执行计划调用底层存储引擎的API，提取到数据返回给客户端就好了。

在MySQL 执行SHOW ENGINES; 可以查出MySQL支持的存储引擎：

我们本篇研究的MySQL优化的存储引擎就是InnoDB. InnoDB是MySQL目前的存储引擎，接下来我们就介绍一下InnoDB中的数据组织形式。

数据组织形式

InnoDB是一个将表中的数据存储到磁盘上的存储引擎，所以即使重启后我们的数据还是存在的。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。而我们知道读写磁盘的速度是非常慢，和内存读写差了几个数量级，所以当我们想读取表中的某些记录时，InnoDB不是逐条从磁盘中加载数据，而是将数据划分为若干页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为16KB，通过show global status like 'innodb_page_size'; 可查看一页的大小.

16384B = 16KB.

行格式

我们平时以记录为单位向表中添加数据，呈现在我们眼前的是我们插入的数据，事实上MySQL对我们插入的数据也做了处理，也就是为记录再添加一部分数据，在5.7版本，MySQL一共有四种处理行记录的方式:

• Compact 紧凑的

Compact行格式将一条完整的记录分成“记录的额外信息” 和记录的真实数据。

记录的真实数据除了我们自定义的列的数据以外，MySQL会为每个记录默认添加一些列(也称隐藏列)，如下图所示:

存储引擎为InnoDB的表对主键的生成策略为: 优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。除此之外InnoDB还会为每条记录都添加DB_TRX_ID(transaction_id) 和roll_pointer。

• Redundant 冗余的

Redundant 是MyQL 5.0之前用的一种格式，这里不再介绍。

• Dynamic  动态的
• Compressed  压缩的

Dynamic和Compressed和Compact格式很像，InnoDB不是逐条从磁盘中加载数据，而是将数据划分为若干页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为16KB，在插入某行数据的时候，这行数据的前半部分这一页能放的下，后半部分这一页放不大下了，这种 现象我们称之为行溢出。Dynamic和Compressed会将这行数据整体都放到下一页，在本页只记录这行记录的地址。

Compressed行格式和Dynamic不同的一点是，Compressed行格式会采用压缩算法对页面进行压缩，以节省空间。

执行SELECT @@innodb_default_row_format; 可以看出MySQL当前默认的行格式：

可以看出当前默认的行格式是Dynamic的.

由行到页

我们现在已经直到了, 表中最基本的单位的存储形式，同时也直到了MySQL以页为单位做为磁盘和内存的基本的交互单位，但是数据页之内行与行之间的联系是什么呢，或者一个数据页内的数据结构是什么？再有我们MySQL一个表的数据从几条到几百万不等，该如何设计页与页之间的数据结构来保证磁盘和内存之间不频繁的进行交换数据操作。这也就是我们在《数据结构与算法分析学习笔记(七) 索引与查找技术》一文中讨论的树形索引技术。我们首先来回答行与行之间的联系是什么，或者这些行组成了一个什么样的数据结构？答案是单链表，行格式里记录的额外信息中记录了下一条数据的偏移量，你可以理解为下一条数据的地址。所有的记录按照主键，从小到大组成了一个单链表。

那么现在问题来了，如果我想要在这一页中按主键查找某条记录怎么办，一个显而易见的方法就是从头遍历，遍历这种方案在一页数据量比较少的情况下还算可行，一旦数据量比较大一点，性能损耗就比较严重了。MySQL当然不会采用这种方案去查找数据，MySQL的开发者设计了一种更为性能更加良好的方案，这种方案很像是书籍的目录，对应的制作过程是这样的:

• 将所有正常的记录(包括最大和最小记录，不包括标记为已删除的记录)划分为几个组。
• 每个组的最后一条记录(也就是组内最大的那条记录)的头信息中n_owned表示有多少条记录，也就是该组内共有几条记录。
• 将每个组的最后一条记录的偏移量单独提取出来按顺序存储到靠近页的尾部的地方，这个地方也就是所谓的Page Directory, 也就是页目录。页面目录这些地址的偏移量被称为槽, 所以这个页面目录就是由槽组成的。

偏移量的有点接近机器，我们将偏移量理解为编号，如果我们有五个槽，编号分别为：0,1,2,3,4。如果我们想找主键值为6的记录，过程是这样的:

1. 计算中间槽的位置,也就是2，如果对应槽2的主键值大于6，那说明这条记录在之前的槽, 所以接着二分查找。
   
2. 再次计算中间槽的位置为1，如果对应的槽的位置小于6，所以这个这条记录应该在槽1或槽2，槽1不是，那就是槽2. 行内又是一个按主键从小到达的单链表，槽中记录的又是组内最大元素的位置，那最小的怎么拿，或者说单链表的头指针该怎么拿，我们有槽1，槽1的最大元素的下一个元素就是槽2的第一个元素。MySQL规定一个组包含的记录条数只能是1~8条，这个就避免了全页遍历。
   由此我们确定在一个数据页中查找指定主键值的记录过程可以分为两步:
   

• 通过二分法确定该记录所在的槽，并且找到该槽所在分组中主键值最小的记录。
• 然后从最小的记录遍历该槽对应的组的各个记录。

由页到B+树

如果以主键为搜索条件在单个页中我们可以借助页目录来加速查找，如果以非主键列查找呢，那就只能从单链表的头遍历到直到找到了。页内单向链表，页与页之间是双向链表，但是数据量很大的时候，这个双向链表是不是有些长了呢，我们查找元素的时候，只能一页一页来？这样是不是太慢了啊，下面有请索引同志闪亮登场，上面我们要查找一条记录，遍历页组成的双向链表的原因在于到目前为止，页内是有规律，而页与页之间目前是没规律的，事实上在MySQL页与页之间也是有规律的，那如何让页与页之间也有规律呢，我们可以将上面的分组和槽中找到灵感:

• 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。

为了保证这一条我们需要，我们可能需要移动记录值来保证，比如第一页的最大主键值是5，目前只有一页，再假如一条主键为4的记录值，我们此时就需要将4移动到第一页，将5放到下一页。这个过程我们称之为页分裂。

• 为所有的页建立一个目录。
  

有没有分块索引的感觉，目录中的每一个目录项都对应一页，每个目录项包含两部分: 页的用户记录最小的主键值，页号(我们可以理解为数据页的位置)

这样我们在查找元素的时候，就可以先看目录，再去对应的页去找了。上面是一种简单的索引设计方案，事实上上面的设计方案仍然存在一些问题，比如我们删掉了一部分数据，整个目录项可能就需要重新移动一下。事实上MySQL仍然使用之前的数据页来作为目录项，只不过此时一行中的真实记录只有主键和页号而已。我们可以将这个目录项理解为分块索引，还可以再向上再引入一层吗？可以, 为目录项再引入一层目录项即可，但不要套娃，层数越多，磁盘和内存交换数据的次数越多，我们应当尽量避免磁盘和内存交换数据，这很慢。这也就是B+树。

上面我们讨论事实上只是数据页，其实MySQL内部还有其他类型的页，比如存放表空间头部信息的页等等。目前我们讨论的数据页和目录页都放在这棵B+树中。叶子结点是页存储记录数据，第二层存储页的目录，目录项是每一页的最小主键值和页的位置。

B+树也被称为索引，上面的B+树有两个特点:

1. 使用记录主键值的大小进行记录和页的排序:
   1.1 页内的记录时按照主键的大小顺序排成一个单向链表。
   1.2  各个存放用户记录的页也是根据用户记录的主键大小顺序排成一个双向链表。
   1.3 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
   
2. B+树的叶子结点存储丶是完整的用户记录
   

所谓完整的用户记录，就是指这个记录中存储了所有列的值(包括隐藏列)。

具备这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子结点处。这种聚簇索引并不需要我们在MySQL显式的使用INDEX语句去创建，InnoDB存储引擎自动的为我们创建聚簇索引。在InnoDB存储引擎中，聚簇索引就是数据的存储方式。

由B+树到索引

• 二级索引
  聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序。那如果我们想以别的列作为搜索条件该怎么办呢？对其他列建立B+树喽，MySQL提供了表中的列建B+树的语法，也就是建索引。这颗B树根上面的聚簇索引的不同之处有:
  

• 页内的记录按照索引列的大小顺序排成一个单向链表
• 各个存放用户记录的页也是根据页中记录的索引列大小顺序排成一个双向链表
• B+树的叶子结点存储的并不是完整的用户记录，而只是索引列+主键这两个列的值。

• 联合索引
  我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引。

SELECT * FROM Student ORDER BY age,numbers

• 像SQL中的order by 后面跟多个字段的含义一样，如果我们想让B+树按照age和numbers的大小进行排序，表达的含义为:
  联合索引也是二级索引，只不过这里单独拿出来讨论了一下而已。

• 先把各个记录和页按照age进行排序。
• 如果age相同，就采用numbers进行排序。