1. MySQL架構(gòu)邏輯設(shè)計(jì)

1.1 MySQL的邏輯架構(gòu)圖

Mysql的架構(gòu)總體分為四層：

• 客戶端：各種語(yǔ)言都提供了連接mysql數(shù)據(jù)庫(kù)的方法，比如jdbc、php、go等，可根據(jù)選擇 的后端開(kāi)發(fā)語(yǔ)言選擇相應(yīng)的方法或框架連接mysql

• server層：包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等，涵蓋mysql的大多數(shù)核心服務(wù)功能，以及所有的內(nèi)置函數(shù)（例如日期、世家、數(shù) 學(xué)和加密函數(shù)等），所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)，比如存儲(chǔ)過(guò)程、觸發(fā)器、視圖等。

• 存儲(chǔ)引擎層：負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取，是真正與底層物理文件打交道的組件。 數(shù)據(jù)本質(zhì)是存儲(chǔ)在磁盤(pán)上的，通過(guò)特定的存儲(chǔ)引擎對(duì)數(shù)據(jù)進(jìn)行有組織的存放并根據(jù)業(yè)務(wù)需要對(duì)數(shù)據(jù)進(jìn)行提取。存儲(chǔ)引擎的架構(gòu)模式是插件式的，支持Innodb，MyIASM、Memory等多個(gè)存儲(chǔ)引擎?，F(xiàn)在最常用的存儲(chǔ)引擎是Innodb，它從mysql5.5.5版本開(kāi)始成為了默認(rèn)存儲(chǔ)引擎。

• 物理文件層：存儲(chǔ)數(shù)據(jù)庫(kù)真正的表數(shù)據(jù)、日志等。物理文件包括：redolog、undolog、binlog、errorlog、querylog、slowlog、data、index等。

  • binlog由server層產(chǎn)生，無(wú)論什么搜索引擎都有binlog；redolog只有innodb存儲(chǔ)引擎產(chǎn)生。

1.2 什么是MySQL高性能?

什么是高性能，每個(gè)人都有不同的回答，如：“每秒查詢次數(shù)”、“CPU利用率”、“可擴(kuò)展性”?！陡咝阅躆ysql》中給出的定義是“響應(yīng)時(shí)間”，它認(rèn)為CPU利用率來(lái)作為高性能的指標(biāo)是悖論，CPU利用率低恰恰說(shuō)明性能差對(duì)cpu利用率不高，真正有效的是“查詢響應(yīng)時(shí)間”。

mysql的高性能主要體現(xiàn)在寫(xiě)和讀上，在寫(xiě)的層面公司DBA已經(jīng)進(jìn)行了主從主備的高可用部署，同時(shí)對(duì)redolog、binlog等有很好的管理，基于我們目前對(duì)快速讀的需求比較多，本文主要探討怎么高性能讀？

1.3 基本概念

原理先行，實(shí)踐為后，我們需要先明白mysql優(yōu)化中常見(jiàn)的基本概念。

(1) 數(shù)據(jù)頁(yè)

為了避免一條一條讀取磁盤(pán)數(shù)據(jù)，InnoDB采取頁(yè)的方式，作為磁盤(pán)和內(nèi)存之間交互的基本單位。一個(gè)頁(yè)的大小一般是16KB。

頁(yè)存儲(chǔ)的過(guò)程：

（1）在一開(kāi)始生成頁(yè)的時(shí)候，沒(méi)有UserRecords部分。

（2）當(dāng)插入一條記錄時(shí)，就會(huì)從Free Space中申請(qǐng)一個(gè)記錄大小的空間，并將這個(gè)空間劃分到User Records部分。

（3）當(dāng)Free Space部分的空間全部都被User Records部分替代掉后，則這個(gè)頁(yè)使用完了，如果再有新的記錄插入，則需要去申請(qǐng)新的頁(yè)了。

(2) 回表

眾所周知Mysql默認(rèn)使用的InnoDB數(shù)據(jù)庫(kù)，引擎使用的數(shù)據(jù)結(jié)構(gòu)是B+樹(shù)，索引分類(lèi)如果按照物理存儲(chǔ)方式來(lái)看，分為聚簇索引和非聚簇索引。

聚簇索引：主鍵索引 ； 非聚簇索引：非主鍵索引

1.4 緩存命中及LRU算法

1.4.1 buffer pool緩沖池

上述我們講到Mysql再查詢時(shí)優(yōu)先命中緩存，此處的緩存區(qū)叫做“buffer pool(緩存池) ”。

• buffer pool 是數(shù)據(jù)庫(kù)的一個(gè)內(nèi)存組件，里面緩存了磁盤(pán)上的真實(shí)數(shù)據(jù)，Java系統(tǒng)對(duì)數(shù)據(jù)庫(kù)的增刪改操作，主要是這個(gè)內(nèi)存數(shù)據(jù)結(jié)構(gòu)中的緩存數(shù)據(jù)執(zhí)行的。
• 緩存和磁盤(pán)是對(duì)應(yīng)的，都是使用頁(yè)存儲(chǔ)，緩存區(qū)叫緩存頁(yè)，也是16KB
• 緩存區(qū)的數(shù)據(jù)是從磁盤(pán)中查詢，然后通過(guò)LRU算法存入的緩存區(qū)中的
• Buffer Pool 對(duì)查詢的加速效果，依賴于一個(gè)重要的指標(biāo)，即：內(nèi)存命中率

buffer pool內(nèi)部存儲(chǔ)它是一個(gè)hash結(jié)構(gòu)。優(yōu)化器會(huì)通過(guò)這張表所對(duì)應(yīng)的“表空間+頁(yè)號(hào)”計(jì)算為key，然后通過(guò)value對(duì)應(yīng)的緩沖頁(yè)的控制塊

緩沖池（buffer pool）它是有固定大小的，雖然我們一頁(yè)是數(shù)據(jù)是16KB。但是數(shù)據(jù)頁(yè)多了，難免會(huì)把緩沖池（buffer pool）撐滿，此時(shí)通過(guò)lru算法淘汰數(shù)據(jù)頁(yè)。其實(shí)也可以理解為lru存儲(chǔ)的也是“表空間+頁(yè)號(hào)”數(shù)據(jù)。

1.4.2 LRU算法

策略：緩存區(qū)的數(shù)據(jù)更新是通過(guò)LRU算法更新，LRU算法的策略是：“最近最少使用”。

結(jié)構(gòu)：在 InnoDB 實(shí)現(xiàn)上，按照 5:3 的比例把整個(gè) LRU 鏈表分成了 young 區(qū)域和 old 區(qū)域。前5/8處是年輕代，后3/8是老年代。

算法：

• 新讀取的數(shù)據(jù)會(huì)被存入old區(qū)域，下一個(gè)新數(shù)據(jù)進(jìn)來(lái)會(huì)在old區(qū)前插，原數(shù)據(jù)以此后移；
• 如果數(shù)據(jù)在old區(qū)域存在時(shí)間超過(guò)1s,就會(huì)前移到首位，進(jìn)入young區(qū)域，其他數(shù)據(jù)依次超過(guò)1s前移；
• 對(duì)young區(qū)數(shù)據(jù)訪問(wèn)，被訪問(wèn)數(shù)據(jù)會(huì)前移到首位

根據(jù)上面的LRU算法，如果全表掃描，全表掃描的數(shù)據(jù)不會(huì)被寫(xiě)入緩存young區(qū)被命中。

掃描過(guò)程中，需要新插入的數(shù)據(jù)頁(yè)，都被放到 old 區(qū)域 ;

一個(gè)數(shù)據(jù)頁(yè)里面有多條記錄，這個(gè)數(shù)據(jù)頁(yè)會(huì)被多次訪問(wèn)到，但由于是順序掃描，這個(gè)數(shù)據(jù)頁(yè)第一次被訪問(wèn)和最后一次被訪問(wèn)的時(shí)間間隔不會(huì)超過(guò) 1 秒，因此還是會(huì)被保留在 old 區(qū)域；

再繼續(xù)掃描后續(xù)的數(shù)據(jù)，之前的這個(gè)數(shù)據(jù)頁(yè)之后也不會(huì)再被訪問(wèn)到，于是始終沒(méi)有機(jī)會(huì)移到鏈表頭部（也就是 young 區(qū)域），很快就會(huì)被淘汰出去。

2. 索引優(yōu)化

2.1 索引結(jié)構(gòu)

2.1.1 常見(jiàn)的索引結(jié)構(gòu)模型

（1）哈希表，數(shù)組+鏈表的行式，這種結(jié)構(gòu)適合用于等值查詢的場(chǎng)景 （2）有序數(shù)組，單從查詢效率上來(lái)說(shuō)，查詢快更新慢，所以有序數(shù)組只適合用于靜態(tài)存儲(chǔ)引擎 （3）搜索樹(shù)，平衡二叉樹(shù)查詢和更新的時(shí)間復(fù)雜度都是O(log(n))，InnoDB引擎使用的是B+樹(shù)； （4）數(shù)據(jù)庫(kù)技術(shù)發(fā)展到今天，跳表、LSM 樹(shù)等數(shù)據(jù)結(jié)構(gòu)也被用于引擎設(shè)計(jì)中

2.1.2 B+ tree

1）“B+樹(shù)”+“數(shù)據(jù)頁(yè)”的視角了解索引的查詢邏輯，才能更清晰了解查詢過(guò)程。

• 只有葉子節(jié)點(diǎn)（最底層的節(jié)點(diǎn)）才存放了數(shù)據(jù)，非葉子節(jié)點(diǎn)（其他上層節(jié)）僅用來(lái)存放目錄頁(yè)作為索引。
• 非葉子節(jié)點(diǎn)分為不同層次，通過(guò)分層來(lái)降低每一層的搜索量；
• 所有節(jié)點(diǎn)按照索引鍵大小排序，構(gòu)成一個(gè)雙向鏈表，便于范圍查詢；

（2）B+樹(shù)的時(shí)間復(fù)雜度：O(logN) ,上述我們想查到6，只需要查詢二次即可。

(3) 通過(guò)上述查找過(guò)程，我們看出，頁(yè)數(shù)越少，層級(jí)約淺，越好查找。所以索引創(chuàng)建規(guī)則中：“索引越小越好（即索引要有辨識(shí)度） ”

2.2 索引優(yōu)化

2.2.1 索引分類(lèi)

按照物理存儲(chǔ)方式，索引可以分為：聚簇索引(主鍵索引) 和非聚簇索引(二級(jí)索引) 。

• 聚簇索引的葉子節(jié)點(diǎn)存放的是實(shí)際數(shù)據(jù)，所有完整的用戶記錄都存放在聚簇索引的葉子節(jié)點(diǎn)；
• 二級(jí)索引的葉子節(jié)點(diǎn)存放的是主鍵值，而不是實(shí)際數(shù)據(jù)。

所以二級(jí)索引查詢非索引字段時(shí)，需要回表。

2.2.2 各索引優(yōu)缺點(diǎn)

• 覆蓋索引

覆蓋索引是select的數(shù)據(jù)列只用從索引中就能夠取得，不必讀取數(shù)據(jù)行，換句話說(shuō)查詢列要被所建的索引覆蓋。

優(yōu)點(diǎn)：避免回表，覆蓋索引特權(quán)很大，可以無(wú)視最左匹配原則

缺點(diǎn)：需要?jiǎng)?chuàng)建大量復(fù)合索引，建議單表索引數(shù)量低于5個(gè)

• 最左前綴原則

最左前綴可以是聯(lián)合索引的最左N個(gè)字段也可以是字符串索引的最左N個(gè)字符。

此處需要注意的是，如"where name like ‘張 %’"，一般來(lái)講like是不走索引的，但是索引支持字符最左匹配，所以，匹配了字符走了索引

創(chuàng)建復(fù)合索引時(shí)，需要注意順序，存在(a,b)則不需要額外建立a索引，節(jié)約空間

• 索引下沉（MYSQL5.6）

(name, age)，select * from table where name like 'zhan%' and age = 10 and male = 1，這個(gè)用到了什么索引？是否比單索引更快一點(diǎn)？

根據(jù)最左匹配，雖然age 引失效了，但是會(huì)起到過(guò)濾作用，無(wú)需回表過(guò)濾查詢age=10的，直接從二級(jí)索引中取值了，這個(gè)過(guò)程叫做**“索引下沉”。

• 全文索引（MYSQL5.7.6）

你是否對(duì)多個(gè)字段想一起模糊查詢，只要有一個(gè)字段匹配，即展示結(jié)果？

這個(gè)可以使用全文索引實(shí)現(xiàn)，全文索引是FultLeXT類(lèi)型的索引，只能用于InnoDB或MyISAM表,只能為CHAR、VARCHAR、TEXT列創(chuàng)建，MySQL5.7.6中支持了中文。

索引的最終選擇是優(yōu)化器的職責(zé)，優(yōu)化器選擇的依據(jù)是：掃描行數(shù)、臨時(shí)表、排序

3. 總結(jié)

我們從Mysql查詢鏈路的架構(gòu)設(shè)計(jì)以及存儲(chǔ)查詢的物理邏輯分析，得到如上的邏輯圖，從而在日常mysql查詢中，我們應(yīng)當(dāng)考慮創(chuàng)建合適的索引，從而提升mysql查詢的性能。

以上就是一文帶大家由淺入深的了解MySQL底層查詢邏輯的詳細(xì)內(nèi)容，更多關(guān)于MySQL底層查詢邏輯的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！