深入MySQL查询过程底层原理及查询慢的根本原因

这个时候，我们通过各种百度和Google，然后加上自己的理解，终于搞明白了MySQL一次查询的全过程了。

首先，用户想要查询订单数据时，会先发送一个查询请求，如下图所示：

可以看到，当用户发送查询请求时，因为外卖订单项目是部署在Tomcat中的，Tomcat中的线程首先就会接收到用户的请求，然后把请求交给外卖订单项目。

而外卖订单项目，会根据代码去数据库中查询订单的数据，不管是使用原生的JDBC、还是Mybatis、Hibernate等框架，其实底层都是先获取一个JDBC连接。

通过JDBC连接，我们才可以和数据库建立连接，然后通过JDBC的连接，到MYSQL数据库中执行sql语句。

我们来看一下，在MYSQL数据库中是如何执行一个sql语句的，如下图：

JDBC连接是负责和MYSQL通信的，会把sql语句发给MYSQL执行，可以看到MYSQL中，也会有线程获取到JDBC连接中的sql语句，然后去执行。

• 线程会把sql语句交给MYSQL中的sql接口，sql接口会转发给sql解析器去解析，比如，sql解析器会把一条sql语句中的表名称、WHERE关键字后面的查询条件、以及具体查询哪些字段等信息都解析出来，然后把解析好的信息交给查询优化器处理。
• 查询优化器会根据sql解析器解析好的sql语句信息，选择一个效率最高的处理方案，来作为执行sql语句的执行计划，然后转交给执行器去执行。
• 而执行器会调用MYSQL的存储引擎，这里我们用InnoDB存储引擎举例，InnoDB存储引擎中的接口就会来执行sql语句，如下图：

可以看到，MYSQL InnoDB存储引擎中，有一块内存区域叫做Buffer Pool，也叫做缓冲池，一般情况，MYSQL的数据都存放在磁盘中，如果你去查询数据，数据就会从磁盘加载到MYSQL内存中，也就是放在缓冲池Buffer Pool中。

而Buffer Pool，如果我们要再细分一下的话，可以看到是由多个chunk组成的，每个chunk大概占128MB内存的大小，每个chunk中都有多个缓存页，每个缓存页的大小是16kb，缓存页就是用来存放加载到内存的数据的。

每个缓存页都有个对应的描述数据块，可以把描述数据理解为缓存页的一个指针，通过描述数据就可以找到对应的缓存页。

这个时候，当InnoDB存储引擎执行sql语句查询数据时，首先，得要从磁盘中加载数据，如下图：

这里的表空间呢，指的是独立表空间，在MySQL中，表空间分为2种，分别是共享表空间和独立表空间，不过在MySQL 5.6.6及后续版本默认使用的是独立表空间，说白了就是一个独立表空间在磁盘中会单独对应一个表空间文件，而一个表空间文件存放着MYSQL数据库中一张表的数据。

在表空间中有很多数据区组，每个数据区组中包含256个数据区，而每个数据区中又包含64个数据页，因为每个数据页的大小默认是16KB，所以也就是说一个数据区的大小是1MB。

从磁盘加载数据到MYSQL内存中，其实就是通过磁盘IO的方式，把数据页中的数据加载到缓冲池Buffer Pool中的缓存页中，然后通过InnoDB存储引擎和sql接口，一步步返回给用户。

那么，在查询的整个流程中，哪个环节最容易拖后腿呢？答案就是磁盘IO，也就是将磁盘中的数据页数据读取到Buffer Pool的缓存页这个过程。

那么，磁盘IO为什么会拖后腿呢？磁盘IO的过程大概是什么样子的呢？接下来，就很有必要来看下这一块内容了。

查询慢深层次原因揭秘：磁盘IO的过程

先来看下磁盘的物理结构，如下图：

磁盘内部的组成部分，主要为主轴、磁盘盘片、读写磁头、传动轴和传动手臂，其中数据就是存放在磁盘盘片上的，磁盘盘片被划分为了无数个小扇区，每个扇区中都有很多半径不同的环形磁道，不同的磁道中存放着不同的数据。

在实际读写数据时，主轴会让磁盘盘片转动，然后再通过传动手臂的伸展，让读写磁头在磁盘扇区的磁道上读取和写入数据，一次磁盘IO花费的时间，主要由寻道时间、旋转延迟和数据传输时间三部分构成，接下来，我们分别来看下这三部分的耗时情况。

1 寻道时间

刚才我们知道了，磁盘盘片表面上被分为了无数小扇区，每个扇区中都有很多半径不同的磁道，不同的磁道上放着不同的数据。

而寻道时间，指的是将读写磁头移动到正确半径的磁道上所需要的时间，寻道时间越短，磁盘IO操作越快，目前磁盘的平均寻道时间，一般在3~15ms，主流磁盘一般在5ms以下。

2 旋转延迟

寻道结束后，还需要读写磁头旋转到这个磁道的正确位置上才能读写数据，而旋转延迟，指的是从寻道时间结束开始，到读写磁头旋转到磁道正确位置的这段时间间隔。

但是，我们一般将磁盘旋转周期值的一半，作为旋转延迟的近似值；常见的磁盘转速有5400转和7200转，表示每分钟能转5400和7200圈。

比如，我们以7200转举例，也就是说1秒钟能转120圈，磁盘的旋转周期就是 1/120 秒，所以，旋转延迟的近似值为 1/120/2 = 4.17ms。

3 数据传输时间

传输时间，指的是将数据从磁盘盘片读出或写入的时间，一般在零点几毫秒，相对于前两个时间几乎可以忽略不计，这样来看访问一次磁盘即一次磁盘IO的时间，约等于 5ms + 4.17ms = 9ms。

磁盘的顺序读写和随机读写

另外，磁盘的数据读写，分为随机读写和顺序读写这两种，这两种读写数据的方式，与读写磁头读写数据的方式有关。

顺序读写，顾名思义就是读写磁头从磁盘中的一个位置，按照顺序依次读写磁盘盘片中的数据，速度还是挺快的，比如像MYSQL的redo log日志、binglog日志这些日志信息，比如，顺序写数据时，会相应在一个大日志文件末尾，按照顺序添加日志信息。

随机读写时，读写磁头则会在磁盘盘片中，随机切换到不同半径的磁道上读写数据，频繁切换磁道的这个过程，是非常耗时的。

所以，随机读写的速度相比于顺序读写来说，是会慢很多的，而MYSQL从磁盘中读写数据，正好是比较耗时的随机读写。

正是因为从MYSQL中查询数据，往往要发生多次耗时的随机IO，所以，我们对于一些对查询效率要求较高的数据，一般都会选择固态硬盘来存放。

固态硬盘的工作原理，简单来说就是通过电子的移动来实现数据的读写，相比于磁盘这种物理机械的运作方式，速度是快很多的，但是固态硬盘是比较贵的，基于成本考虑，一般公司大部分机器还是会选择普通机械磁盘的。

磁盘IO到底会有多慢呢？

我们回到刚才，已经知道磁盘IO的工作原理，我们也简单计算了一下，一次磁盘IO大概是9ms的样子，看上去还可以，但是9ms已经非常慢了，那到底有多慢呢，我们可以和内存的速度对比一下。

一般一次内存随机读取的速度，大概在100ns以内，而 1ms = 1000000ns，可以看到，一次磁盘IO耗时是毫秒级的，而内存是纳秒级的。

9ms = 9 * 1000000 ns / 100 ns = 90000，说白了磁盘的速度比内存慢 9万倍左右，那为什么从内存读写数据会那么快呢，简单来说，内存其实是被CPU控制的，而CPU的时钟频率的速度相比于磁盘机械运转速度，速度可以说是非常快了。

当用户发起一次查询请求，一次磁盘IO一般是搞不定的，具体发生磁盘IO的次数，还得要取决于B+树的高度和当时使用索引的情况。

极端情况下，比如没用到索引，一次查询可能会发生100多次磁盘IO，这时，磁盘IO所需的总时间大概是 9ms * 100 = 900ms，也就是0.9秒，这就差不多到秒级别了。

随着数据的快速增长，比如达到了好几亿的数据量，那需要的磁盘IO次数会大幅增加，那这个时候，一次查询所需要的时间，就会达到好几秒。

用户查询请求慢的根本原因

现在，我们知道用户查询请求慢的根本原因了吗？

其实说白了，就是随着数据表中的数据量，变得越来越大，导致磁盘IO发生的次数也相应变多了，如果我们能把磁盘IO的次数降到常数级别，那么查询速度是非常快的，所以，后边的优化都是以降低磁盘IO次数为目标。

原文地址：https://www.toutiao.com/article/7153551351728046604/