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

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

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

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

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

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

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

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

1.2 什么是MySQL高性能?

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

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

1.3 基本概念

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

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

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

頁存儲的過程：

（1）在一開始生成頁的時候，沒有UserRecords部分。

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

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

(2) 回表

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

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

1.4 緩存命中及LRU算法

1.4.1 buffer pool緩沖池

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

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

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

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

1.4.2 LRU算法

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

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

算法：

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

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

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

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

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

2. 索引優(yōu)化

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

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

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

2.1.2 B+ tree

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

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

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

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

2.2 索引優(yōu)化

2.2.1 索引分類

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

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

所以二級索引查詢非索引字段時，需要回表。

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

• 覆蓋索引

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

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

缺點：需要創(chuàng)建大量復(fù)合索引，建議單表索引數(shù)量低于5個

• 最左前綴原則

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

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

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

• 索引下沉（MYSQL5.6）

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

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

• 全文索引（MYSQL5.7.6）

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

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

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

3. 總結(jié)

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

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

最新評論