深入MySQL查詢過(guò)程底層原理，我找到了MySQL查詢慢的根本原因

作者：石杉的架構(gòu)筆記 2022-10-12 17:42:54

其實(shí)說(shuō)白了，就是隨著數(shù)據(jù)表中的數(shù)據(jù)量，變得越來(lái)越大，導(dǎo)致磁盤(pán)IO發(fā)生的次數(shù)也相應(yīng)變多了，如果我們能把磁盤(pán)IO的次數(shù)降到常數(shù)級(jí)別，那么查詢速度是非常快的，所以，后邊的優(yōu)化都是以降低磁盤(pán)IO次數(shù)為目標(biāo)。?

這個(gè)時(shí)候，我們通過(guò)各種百度和Google，然后加上自己的理解，終于搞明白了MySQL一次查詢的全過(guò)程了。

首先，用戶想要查詢訂單數(shù)據(jù)時(shí)，會(huì)先發(fā)送一個(gè)查詢請(qǐng)求，如下圖所示：

可以看到，當(dāng)用戶發(fā)送查詢請(qǐng)求時(shí)，因?yàn)橥赓u(mài)訂單項(xiàng)目是部署在Tomcat中的，Tomcat中的線程首先就會(huì)接收到用戶的請(qǐng)求，然后把請(qǐng)求交給外賣(mài)訂單項(xiàng)目。

而外賣(mài)訂單項(xiàng)目，會(huì)根據(jù)代碼去數(shù)據(jù)庫(kù)中查詢訂單的數(shù)據(jù)，不管是使用原生的JDBC、還是Mybatis、Hibernate等框架，其實(shí)底層都是先獲取一個(gè)JDBC連接。

通過(guò)JDBC連接，我們才可以和數(shù)據(jù)庫(kù)建立連接，然后通過(guò)JDBC的連接，到MYSQL數(shù)據(jù)庫(kù)中執(zhí)行sql語(yǔ)句。

我們來(lái)看一下，在MYSQL數(shù)據(jù)庫(kù)中是如何執(zhí)行一個(gè)sql語(yǔ)句的，如下圖：

JDBC連接是負(fù)責(zé)和MYSQL通信的，會(huì)把sql語(yǔ)句發(fā)給MYSQL執(zhí)行，可以看到MYSQL中，也會(huì)有線程獲取到JDBC連接中的sql語(yǔ)句，然后去執(zhí)行。

線程會(huì)把sql語(yǔ)句交給MYSQL中的sql接口，sql接口會(huì)轉(zhuǎn)發(fā)給sql解析器去解析，比如，sql解析器會(huì)把一條sql語(yǔ)句中的表名稱(chēng)、WHERE關(guān)鍵字后面的查詢條件、以及具體查詢哪些字段等信息都解析出來(lái)，然后把解析好的信息交給查詢優(yōu)化器處理。
查詢優(yōu)化器會(huì)根據(jù)sql解析器解析好的sql語(yǔ)句信息，選擇一個(gè)效率最高的處理方案，來(lái)作為執(zhí)行sql語(yǔ)句的執(zhí)行計(jì)劃，然后轉(zhuǎn)交給執(zhí)行器去執(zhí)行。
而執(zhí)行器會(huì)調(diào)用MYSQL的存儲(chǔ)引擎，這里我們用InnoDB存儲(chǔ)引擎舉例，InnoDB存儲(chǔ)引擎中的接口就會(huì)來(lái)執(zhí)行sql語(yǔ)句，如下圖：

可以看到，MYSQL InnoDB存儲(chǔ)引擎中，有一塊內(nèi)存區(qū)域叫做Buffer Pool，也叫做緩沖池，一般情況，MYSQL的數(shù)據(jù)都存放在磁盤(pán)中，如果你去查詢數(shù)據(jù)，數(shù)據(jù)就會(huì)從磁盤(pán)加載到MYSQL內(nèi)存中，也就是放在緩沖池Buffer Pool中。

而B(niǎo)uffer Pool，如果我們要再細(xì)分一下的話，可以看到是由多個(gè)chunk組成的，每個(gè)chunk大概占128MB內(nèi)存的大小，每個(gè)chunk中都有多個(gè)緩存頁(yè)，每個(gè)緩存頁(yè)的大小是16kb，緩存頁(yè)就是用來(lái)存放加載到內(nèi)存的數(shù)據(jù)的。

每個(gè)緩存頁(yè)都有個(gè)對(duì)應(yīng)的描述數(shù)據(jù)塊，可以把描述數(shù)據(jù)理解為緩存頁(yè)的一個(gè)指針，通過(guò)描述數(shù)據(jù)就可以找到對(duì)應(yīng)的緩存頁(yè)。

這個(gè)時(shí)候，當(dāng)InnoDB存儲(chǔ)引擎執(zhí)行sql語(yǔ)句查詢數(shù)據(jù)時(shí)，首先，得要從磁盤(pán)中加載數(shù)據(jù)，如下圖：

這里的表空間呢，指的是獨(dú)立表空間，在MySQL中，表空間分為2種，分別是共享表空間和獨(dú)立表空間，不過(guò)在MySQL 5.6.6及后續(xù)版本默認(rèn)使用的是獨(dú)立表空間，說(shuō)白了就是一個(gè)獨(dú)立表空間在磁盤(pán)中會(huì)單獨(dú)對(duì)應(yīng)一個(gè)表空間文件，而一個(gè)表空間文件存放著MYSQL數(shù)據(jù)庫(kù)中一張表的數(shù)據(jù)。

在表空間中有很多數(shù)據(jù)區(qū)組，每個(gè)數(shù)據(jù)區(qū)組中包含256個(gè)數(shù)據(jù)區(qū)，而每個(gè)數(shù)據(jù)區(qū)中又包含64個(gè)數(shù)據(jù)頁(yè)，因?yàn)槊總€(gè)數(shù)據(jù)頁(yè)的大小默認(rèn)是16KB，所以也就是說(shuō)一個(gè)數(shù)據(jù)區(qū)的大小是1MB。

從磁盤(pán)加載數(shù)據(jù)到MYSQL內(nèi)存中，其實(shí)就是通過(guò)磁盤(pán)IO的方式，把數(shù)據(jù)頁(yè)中的數(shù)據(jù)加載到緩沖池Buffer Pool中的緩存頁(yè)中，然后通過(guò)InnoDB存儲(chǔ)引擎和sql接口，一步步返回給用戶。

那么，在查詢的整個(gè)流程中，哪個(gè)環(huán)節(jié)最容易拖后腿呢？答案就是磁盤(pán)IO，也就是將磁盤(pán)中的數(shù)據(jù)頁(yè)數(shù)據(jù)讀取到Buffer Pool的緩存頁(yè)這個(gè)過(guò)程。

那么，磁盤(pán)IO為什么會(huì)拖后腿呢？磁盤(pán)IO的過(guò)程大概是什么樣子的呢？接下來(lái)，就很有必要來(lái)看下這一塊內(nèi)容了。

查詢慢深層次原因揭秘：磁盤(pán)IO的過(guò)程

先來(lái)看下磁盤(pán)的物理結(jié)構(gòu)，如下圖：

磁盤(pán)內(nèi)部的組成部分，主要為主軸、磁盤(pán)盤(pán)片、讀寫(xiě)磁頭、傳動(dòng)軸和傳動(dòng)手臂，其中數(shù)據(jù)就是存放在磁盤(pán)盤(pán)片上的，磁盤(pán)盤(pán)片被劃分為了無(wú)數(shù)個(gè)小扇區(qū)，每個(gè)扇區(qū)中都有很多半徑不同的環(huán)形磁道，不同的磁道中存放著不同的數(shù)據(jù)。

在實(shí)際讀寫(xiě)數(shù)據(jù)時(shí)，主軸會(huì)讓磁盤(pán)盤(pán)片轉(zhuǎn)動(dòng)，然后再通過(guò)傳動(dòng)手臂的伸展，讓讀寫(xiě)磁頭在磁盤(pán)扇區(qū)的磁道上讀取和寫(xiě)入數(shù)據(jù)，一次磁盤(pán)IO花費(fèi)的時(shí)間，主要由尋道時(shí)間、旋轉(zhuǎn)延遲和數(shù)據(jù)傳輸時(shí)間三部分構(gòu)成，接下來(lái)，我們分別來(lái)看下這三部分的耗時(shí)情況。

1 尋道時(shí)間

剛才我們知道了，磁盤(pán)盤(pán)片表面上被分為了無(wú)數(shù)小扇區(qū)，每個(gè)扇區(qū)中都有很多半徑不同的磁道，不同的磁道上放著不同的數(shù)據(jù)。

而尋道時(shí)間，指的是將讀寫(xiě)磁頭移動(dòng)到正確半徑的磁道上所需要的時(shí)間，尋道時(shí)間越短，磁盤(pán)IO操作越快，目前磁盤(pán)的平均尋道時(shí)間，一般在3~15ms，主流磁盤(pán)一般在5ms以下。

2 旋轉(zhuǎn)延遲

尋道結(jié)束后，還需要讀寫(xiě)磁頭旋轉(zhuǎn)到這個(gè)磁道的正確位置上才能讀寫(xiě)數(shù)據(jù)，而旋轉(zhuǎn)延遲，指的是從尋道時(shí)間結(jié)束開(kāi)始，到讀寫(xiě)磁頭旋轉(zhuǎn)到磁道正確位置的這段時(shí)間間隔。

但是，我們一般將磁盤(pán)旋轉(zhuǎn)周期值的一半，作為旋轉(zhuǎn)延遲的近似值；常見(jiàn)的磁盤(pán)轉(zhuǎn)速有5400轉(zhuǎn)和7200轉(zhuǎn)，表示每分鐘能轉(zhuǎn)5400和7200圈。

比如，我們以7200轉(zhuǎn)舉例，也就是說(shuō)1秒鐘能轉(zhuǎn)120圈，磁盤(pán)的旋轉(zhuǎn)周期就是 1/120 秒，所以，旋轉(zhuǎn)延遲的近似值為 1/120/2 = 4.17ms。

3 數(shù)據(jù)傳輸時(shí)間

傳輸時(shí)間，指的是將數(shù)據(jù)從磁盤(pán)盤(pán)片讀出或?qū)懭氲臅r(shí)間，一般在零點(diǎn)幾毫秒，相對(duì)于前兩個(gè)時(shí)間幾乎可以忽略不計(jì)，這樣來(lái)看訪問(wèn)一次磁盤(pán)即一次磁盤(pán)IO的時(shí)間，約等于 5ms + 4.17ms = 9ms。

磁盤(pán)的順序讀寫(xiě)和隨機(jī)讀寫(xiě)

另外，磁盤(pán)的數(shù)據(jù)讀寫(xiě)，分為隨機(jī)讀寫(xiě)和順序讀寫(xiě)這兩種，這兩種讀寫(xiě)數(shù)據(jù)的方式，與讀寫(xiě)磁頭讀寫(xiě)數(shù)據(jù)的方式有關(guān)。

順序讀寫(xiě)，顧名思義就是讀寫(xiě)磁頭從磁盤(pán)中的一個(gè)位置，按照順序依次讀寫(xiě)磁盤(pán)盤(pán)片中的數(shù)據(jù)，速度還是挺快的，比如像MYSQL的redo log日志、binglog日志這些日志信息，比如，順序?qū)憯?shù)據(jù)時(shí)，會(huì)相應(yīng)在一個(gè)大日志文件末尾，按照順序添加日志信息。

隨機(jī)讀寫(xiě)時(shí)，讀寫(xiě)磁頭則會(huì)在磁盤(pán)盤(pán)片中，隨機(jī)切換到不同半徑的磁道上讀寫(xiě)數(shù)據(jù)，頻繁切換磁道的這個(gè)過(guò)程，是非常耗時(shí)的。

所以，隨機(jī)讀寫(xiě)的速度相比于順序讀寫(xiě)來(lái)說(shuō)，是會(huì)慢很多的，而MYSQL從磁盤(pán)中讀寫(xiě)數(shù)據(jù)，正好是比較耗時(shí)的隨機(jī)讀寫(xiě)。

正是因?yàn)閺腗YSQL中查詢數(shù)據(jù)，往往要發(fā)生多次耗時(shí)的隨機(jī)IO，所以，我們對(duì)于一些對(duì)查詢效率要求較高的數(shù)據(jù)，一般都會(huì)選擇固態(tài)硬盤(pán)來(lái)存放。

固態(tài)硬盤(pán)的工作原理，簡(jiǎn)單來(lái)說(shuō)就是通過(guò)電子的移動(dòng)來(lái)實(shí)現(xiàn)數(shù)據(jù)的讀寫(xiě)，相比于磁盤(pán)這種物理機(jī)械的運(yùn)作方式，速度是快很多的，但是固態(tài)硬盤(pán)是比較貴的，基于成本考慮，一般公司大部分機(jī)器還是會(huì)選擇普通機(jī)械磁盤(pán)的。

磁盤(pán)IO到底會(huì)有多慢呢？

我們回到剛才，已經(jīng)知道磁盤(pán)IO的工作原理，我們也簡(jiǎn)單計(jì)算了一下，一次磁盤(pán)IO大概是9ms的樣子，看上去還可以，但是9ms已經(jīng)非常慢了，那到底有多慢呢，我們可以和內(nèi)存的速度對(duì)比一下。

一般一次內(nèi)存隨機(jī)讀取的速度，大概在100ns以內(nèi)，而 1ms = 1000000ns，可以看到，一次磁盤(pán)IO耗時(shí)是毫秒級(jí)的，而內(nèi)存是納秒級(jí)的。

9ms = 9 * 1000000 ns / 100 ns = 90000，說(shuō)白了磁盤(pán)的速度比內(nèi)存慢 9萬(wàn)倍左右，那為什么從內(nèi)存讀寫(xiě)數(shù)據(jù)會(huì)那么快呢，簡(jiǎn)單來(lái)說(shuō)，內(nèi)存其實(shí)是被CPU控制的，而CPU的時(shí)鐘頻率的速度相比于磁盤(pán)機(jī)械運(yùn)轉(zhuǎn)速度，速度可以說(shuō)是非常快了。

當(dāng)用戶發(fā)起一次查詢請(qǐng)求，一次磁盤(pán)IO一般是搞不定的，具體發(fā)生磁盤(pán)IO的次數(shù)，還得要取決于B+樹(shù)的高度和當(dāng)時(shí)使用索引的情況。

極端情況下，比如沒(méi)用到索引，一次查詢可能會(huì)發(fā)生100多次磁盤(pán)IO，這時(shí)，磁盤(pán)IO所需的總時(shí)間大概是 9ms * 100 = 900ms，也就是0.9秒，這就差不多到秒級(jí)別了。

隨著數(shù)據(jù)的快速增長(zhǎng)，比如達(dá)到了好幾億的數(shù)據(jù)量，那需要的磁盤(pán)IO次數(shù)會(huì)大幅增加，那這個(gè)時(shí)候，一次查詢所需要的時(shí)間，就會(huì)達(dá)到好幾秒。