Mysql資料庫存取原理及性能優化

一、Mysql的系統架構圖

 二、Mysql存儲引擎

　　Mysql中的數據是通過一定的方式存儲在文件或者記憶體中的，任何方式都有不同的存儲、查找和更新機制，這意味著選擇不同的方式對於數據的存取有效率的差距。 這種不同的存儲方式在 MySQL中被稱作存儲引擎。

　　存儲引擎是Mysql資料庫系統的底層組件，資料庫管理系統通過這些組件來進行創建、查詢、更新和刪除數據，它操作的對象是表。存儲引擎類型在肉眼上體現為表的類型，Mysql的存儲引擎類型可以通過運行命令' SHOW ENGINES;  '獲得，目前Mysql8支持的存儲引擎有如下幾種：

• FEDERATED：將數據存儲在遠程資料庫中，用來訪問遠程表的存儲引擎。
• MRG_MYISAM：是一組MyISAM的組合，他將MyISAM引擎的多個表聚合起來，但是他的內部沒有數據，真正的數據依然是MyISAM引擎的表中，但是可以直接進行查詢、刪除更新等操作。
• MyISAM：是MySQL早期預設的存儲引擎，擁有較高的插入、查詢速度，表鎖設計，支持全文索引，但不支持事務和外鍵。
• BLACKHOLE：充當一個“黑洞”，接受數據，但將其扔掉，不存儲數據，類似於Linux系統中的/dev/null文件。主要用於查找和存儲引擎無關的其他方面的性能瓶頸。
• CSV：會在MySQL安裝目錄data文件夾中，和該表所在資料庫名相同的目錄生成一個.CSV文件，它可以將CSV類型的文件當做表進行處理，相比其他存儲引擎的文件內容，可以直接查看和編輯。
• MEMORY：數據保存在記憶體中，表結構保存在磁碟上。如果資料庫重啟或者發生崩潰，表中的數據都將消失，非常適用於存儲臨時數據的臨時表。
• ARCHIVE：用於數據存檔的引擎，僅僅支持最基本的插入(insert)和查詢(select)兩種功能。Archive擁有很好的壓縮機制，比MyISAM、InnoDB存儲引擎更加節約存儲空間。
• InnoDB：是MySQL當前版本預設的存儲引擎，支持事務安全表（ACID），支持行鎖定和外鍵。要求實現併發控制，需要頻繁的更新、刪除操作的資料庫，那選擇InnoDB有很大的優勢。
• PERFORMANCE_SCHEMA：MySQL資料庫系統專用引擎，用戶不能創建這種存儲引擎的表。

　　查看當前預設的存儲引擎：

SHOW VARIABLES LIKE 'default_storage_engine%';

　　修改預設存儲引擎(臨時生效，重啟後恢復InnoDB)：

SET default_storage_engine=< 存儲引擎名 >

　　修改單個數據表的存儲引擎：

ALTER TABLE <表名> ENGINE=<存儲引擎名>;

　　查看數據表的存儲引擎：

 SHOW CREATE TABLE tablename \G

　　修改表的預設存儲引擎，要想永久生效，需要在配置文件my.cnf中  [mysqld] 後面加入：

default-storage-engine=存儲引擎名稱

　　如何根據業務場景來選擇合適自己的存儲引擎呢?這需要先瞭解幾種存儲引擎的特性，以下是幾種存儲引擎的特性表，可做參考：

 三、InnoDB存儲引擎存取原理

　　InnoDB是最常用的存儲引擎，我們的業務系統在使用Mysql作為資料庫的時候，一般都選擇InnoDB作為存儲引擎，原因是該引擎支持行鎖、索引、事務安裝、主外鍵，而這些特性正是業務系統數據所需要的。

　　數據在InnoDB中將被劃分為若幹個頁，頁的大小一般為16KB，以頁作為存取數據的單位，前一節提到InnoDB的數據是存到磁碟上的，而程式代碼需要的數據是在記憶體中的，因此存取數據的操作實際上是磁碟和記憶體之間數據交換的過程。

 　　以下是Mysql數據頁的結構圖，不同的部分代表著不同的功能，其中：

• File Header：表示文件的頭部，存儲了一些頁的通用信息。
• Page Header：表示頁面的頭部，存儲一些有關數據頁的信息。
• Infimun+supremum：表示兩個虛擬的行記錄(最小記錄和最大記錄)。
• UserRecord：真正用於存儲數據行的內容。
• FreeSpace：空閑部分。
• PageDirectory：頁目錄，記錄了某些記錄的位置。
• FileTrailer：頁尾，用於校驗頁的完整性。

 　　UserRecord這部分空間是可變的，當向資料庫中插入記錄時，數據會被存到這個位置，UserRecord空間被增加，同時FreeSpace容量會變小，直到空閑空間FreeSpace被用完，如果還有新的數據插入，則引擎會申請新的數據頁再進行存儲。

　　多條數據被存到UserRecord這個位置，那他們之間的物理位置有什麼關係呢？這裡牽涉到兩種主要關係，一種是同一數據頁之間的數據記錄位置關係，另一種是不同數據頁之間的記錄的位置關係。要談記錄的位置關係，我們要瞭解每條記錄的存儲結構。

　　每條記錄都包含我們存儲的真實欄位數據和記錄額外的信息數據，一行記錄可以以不同的行格式存在，目前有Compact、Redundant、Dynamic、Compressed等幾種格式，以下是Compact行格式的圖解：

 其中，每個屬性說明如下：

 　　記錄是以單向鏈表的方式存儲的，因此：

• 當我們插入一條記錄時，新的記錄會記錄下它在堆中的位置並更新相鄰的前一記錄的next_record值為它的heap_no，更新它的next_record值為它的相鄰位置後一記錄的heap_no值。
• 當我們刪除記錄時，該記錄的delete_mask會被標記為1，同時相鄰的前一記錄的next_record值被更改為它的下一記錄的heap_no值。

 　　思考一個問題：如果一個數據表的列欄位很多或者存儲的值太多，一個數據頁存不完怎麼辦？在Compact行格式中，對於占用存儲空間很大的列，在記錄真實數據的地方只存儲一部分數據，把剩餘的數據分散存儲在其它頁中，通過在記錄數據的尾部存儲指向其它頁數據的地址來關聯這些分散的數據。

四、InnoDB索引原理

　　我們知道，資料庫使用索引的目的是為了加快查詢速度，那麼MySQL是如何實現索引的呢？

　　對於數據行的插入，預設情況下是順序存儲(按主鍵排序)的，查詢的時候將按照插入的順序顯示結果。當一個數據頁存儲欄位較少且欄位值內容較少的時候，很有可能這個數據頁就能夠同時存儲上千行記錄，前一節我們說過，數據是以單向鏈表的方式存儲的，這種存儲方式對於插入來說比較快，而對於查詢來說就比較慢，如果我們需要使用where查詢條件從上千行記錄中獲取滿足條件的記錄，如何才能加快查找速度呢?

　　InnoDB引擎為我們提供了一個叫做PageDirectory的頁目錄，這個概念在前面介紹數據頁結構的時候提到過，它用於為數據頁的行記錄提供目錄索引，類似書籍的章節目錄，能夠幫助我們快速定位數據記錄所在的分組，從而加快查詢速度，其實現原理是：將頁內所有非刪除的記錄劃分為N個組，每個組裡最後一條記錄(主鍵最大的記錄)的n_owned屬性記錄了組內的記錄數量，將這條記錄的偏移地址取出按序從File Trailer位置開始向前寫入形成PageDirectory，其中偏移地址稱為＇槽＇，圖示如下：

 　　根據上圖所示．如果我們要獲取id=4的記錄，可以先通過二分法從目錄頁中快速找到這一記錄所在的頁，然後在快速定位數據記錄所在的位置。

.　　通過上面的解析，我們理解了從同一個數據頁中快速查找記錄的方式，再來想一想，如果數據行較多，分散存儲到了多個數據頁里，那又如何快速的確定數據在哪一個數據頁的哪一個分組呢？

　　InnoDB為我們提供了"數據頁"概念的同時，也給我們提供了一個叫做"目錄頁"的概念，目錄頁用於存儲數據頁的頁號和頁對應的記錄的最小主鍵(相當存了一組{key，value}鍵值對集合，key主鍵，value為頁號)。因此在執行條件查詢時，先通過主鍵確定記錄所在的頁，在根據頁內的頁目錄定位(通過主鍵定位)到分組，從而快速獲取結果。結構圖示如下：

 　　分析結構圖，發現結構是一棵B+樹，B+樹是一種樹數據結構，通常用於資料庫和操作系統的文件系統中。B+樹的特點是能夠保持數據穩定有序，其插入與修改擁有較穩定的對數時間複雜度。B+樹元素自底向上插入，這與二叉樹恰好相反。B+樹的葉子節點用於存儲真實數據，非葉子節點用於存儲主鍵值和指針(頁碼)。

　　前面我們提到數據在插入時是按照主鍵的順序來進行排序存儲的，所以這就是為什麼我們在設計表時建議設置主鍵自增的原因(新增記錄時不會對之前的數據進行重新排列，這會加快插入的速度)。其實一個數據行欄位除了我們自己創建的欄位之外，還存在三個隱藏的欄位row_id(行ID)、transaction_id(事務ID)、roll-pointer(回滾指針)，如果我們設計表時沒有設置主鍵，也沒有設置唯一索引，那麼它會自動以隱藏的欄位row_id來作為自增主鍵進行排序存儲。

　　思考一個問題：如果一個列的值占用空間較多，會發生什麼？當一行記錄存儲的數據較多時，意味著要使用更多的數據頁，更多的數據頁意味著更多的目錄頁，這會增加" B+結構的高度 "，這也會隱形的降低查詢效率。

　　到這裡我們已經知道，對於主鍵欄位來說，它會通過頁目錄和目錄頁的方式來增加數據查詢速度，但是在實際開發中，我們可能還需要通過其他欄位的查詢條件來篩選數據，那麼InnoDB引擎又是如何通過什麼方式來增加查詢速度的呢？

　　其實，我們也可以為非主鍵欄位來創目錄頁，這些目錄頁同樣組成了B+樹結構，只不過它的葉子節點存儲的是主鍵值，當通過非主鍵欄位查詢記錄時，首先會通過非主鍵目錄頁B+樹結構查找到主鍵，再去調用主鍵的目錄頁B+樹，去查找真實數據。我們知道，除了可以為單獨為非主鍵欄位的某一個欄位創建索引，還可以使用聯合多個欄位來創建一個索引，這兩種方式的實現方式都是相同的。

　　根據前面的分析和探究，我們需要知道如下幾點：

• InnoDB引擎在磁碟和記憶體之間進行數據傳輸時是以“ 數據頁 ”為基本單位傳輸的。
• 數據行記錄是以單向鏈表的方式存儲的，特點是插入快，更新和查詢慢。
• 目錄頁能夠幫助引擎快速定位記錄所在的數據頁。
• 頁內目錄能夠幫助引擎快速定位記錄所在的分組。
• 為某個欄位創建索引就相當於為這個欄位創建了一個B+樹結構的目錄頁。
• 以多個欄位為一體創建了一個索引就相當為這些欄位組合創建了一個B+樹結構的目錄頁。
• 如果定義表時指定了主鍵和唯一索引，則存儲時將以主鍵和唯一索引的邏輯順序進行物理存儲，這種索引中鍵值的邏輯順序決定了表中相應行的物理順序，這種索引稱為聚集索引，反正稱為非聚集索引。
•  如果定義表時沒有指定主鍵和唯一索引，則InnoDB會以隱藏的欄位row_id為主鍵進行數據存儲。
• 綜上前兩點證明，建表時會產生一個聚集索引(每張表值只有一個)，聚集索引產生的B+樹結構的葉子節點存儲的是真實數據(完整數據)，非葉子節點存儲的是主鍵和頁碼指針。
• 非聚集索引是手動創建的，用於在聚集索引之外創建目錄頁B+樹，葉子節點存儲的是索引列和主鍵值(不是指針)，非葉子節點存儲的是索引列和頁碼指針。
• 為已經存在大量數據的表創建和修改索引時，可能需要長時間的等待操作，甚至導致資料庫崩潰。

五、表的掃描

　　我們已經瞭解了索引的原理，知道了可以為表建立多種索引方式，來加快我們的查詢速度，但是不同的索引類型的查詢效率是有區別的，mysql有幾種表的掃描機制：

• 全表掃描：遍歷整個主鍵索引的B+樹，並且需要讀葉子節點數據。
• 全索引掃描：遍歷整個二級索引的B+樹。
• 回表查詢：對於非聚集索引的查詢，會先查非聚集索引的B+樹定位主鍵值，再用這個值去聚集索引的B+樹上查找數據。

在進行數據查詢是應該儘量使用最小的掃描代價去獲得結果，這是SQL性能優化的核心思想，需要註意的是並非避免全表掃描就能獲得最佳效果，反之在具有多個索引的中，過多的索引組合反而可能導致效果不如全表掃描實在，這就是在創建索引時並非越多越好的原因。

　　Mysql給我提供了一個關鍵字explain用於分析和測試sql語句的性能，在SQL語句前增加該關鍵字，運行後會返回SQL執行計劃的信息，通過這些信息，可以幫助我們更好的選擇索引和優化SQL語句，例如運行如下語句得到結果如圖：

 其中，結果集的每個欄位和可能的值說明如下：

六、資料庫優化

　　我們已經瞭解了索引的原理，明白了數據頁的存儲結構是一棵B+樹，同時目錄頁存儲結構也是一棵B+樹，能夠通過索引定位數據所在的父節點，減少表的掃面，從而快速找到記錄。但是當存在的索引越多時，查找數據的實現方案就越多，那如何才能儘量讓我們編寫的SQL儘量使用最優的方案呢？

　　文章頭部的Mysql架構圖中我們可以看到，我們編寫的SQL語句在經過分析器進行語法分析後，傳遞給優化器，優化器幫助我們選擇索引並生成執行計劃，然後再交給執行器獲取操作結果，但是優化器並非萬能的，它只能為我們執行一些可控的優化。我們還需要從減少數據的碎片存儲、不同類型數據的索引效率、表的掃面機制等方面考慮，特此總結出如下幾個優化和設計規則：

• 控制欄位長度：在設計表的欄位的時候，根據業務需求，儘量控制欄位的長度空間不浪費，比如存儲身份證號的欄位不超過18個字元，手機號的欄位不超過11位。
• 控制欄位類型：根據mysql提供的欄位類型特性，來選擇合理的欄位類型存儲業務數據，比如存狀態0和1使用boolean類型而不使用字元類型CHAR(1)。
• 儘量滿足表設計的三大範式，建立合理的資料庫結構，但同時也要考慮需求本身。
• 控制熱表數據量：結合業務需求，儘量讓熱表的數據量偏小，必要時採用分庫分表的方式，分表包括垂直分表和水平分表，具體根據業務需求來定。
• 儘量控制事務儘快提交和回滾，以免造成長時間鎖表鎖行。
• 儘量減少SQL參與過多的計算邏輯，比如createtime欄位不做時間格式轉換處理。
• 禁止使用外鍵來約束數據的一致性，如需約束請在業務代碼中實現。
• 儘量使用自增INT/BIGINT類型作為主鍵，使用CHAR和UUID作為主鍵會導致數據的存儲順序離散，產生磁碟碎片。
• 統一庫、表、存儲過程的字元集，字元集的不同會導致隱式轉換或報錯，或導致無法使用索引，InnoDB通常推薦使用utf8mb4。
• 索引會影響插入性能，單表索引數量不建議超過5個，必要時可以建立合適的多列索引並註意其順序。
• 核心涉密數據加密後存儲，這主要是為了數據泄露安全考慮。
• 使用INT類型來替代浮點型，避免使用浮點型這種效率較低的類型。
• 遇到BlOB、TEXT等大對象類型的數據，儘量拆分為單獨表，然後使用主鍵做關聯。
• 字元類型儘量使用靈活高效的varchar來代替char類型，儘量減少字元串類型的加長更新操作。
• 日期類型儘量選擇datetime類型，因為timestamp類型只能存儲日期時間為1970-2038年，而char類型查詢效率比datetime低。
• 多表JOIN時，條件列的數據類型要一致，否則可能導致無法使用索引。
• 多表JOIN時，將過濾後結果集較小的表作為驅動表。
• SELECT時儘量不讀取不使用的列，這可以減少IO代價。
• 減少LIKE  '% xxxx %'格式的使用，該語句會導致查詢不使用索引，必要時可以使用LIKE  ' xxxx %'格式，它會使用首碼索引。
• 儘量主要' != '條件的使用，這有可能導致全表掃描，當然並非一定會全表掃描，具體情況的具體分析。
• 當能確定返回結果數量時，最好加上LIMIT N，當查詢到指定數量結果後就會立即結束掃描。
• 優先使用UNION ALL代替UNION，因為UNION會產生臨時表，前者還不會產生去重的代價。 

七、SQL的慢查詢分析

　　SQL的執行總是有一個執行時間的，我們可以給資料庫設置一個時間節點參數long_query_time，當SQL執行超過這個值時，便認為本次查詢是慢查詢，將被記錄到慢查詢日誌中，有利於我們進行性能排查。預設的long_query_time預設值為10s。

開啟慢查詢日誌：

• 在my.cnf中配置：

[mysqld]
log-slow-queries=/data/mysqldata/slow-query.log  #慢查詢日誌的位置
long_query_time=10   #表示查詢超過10s就作為慢查詢

• 命令臨時開啟：

set global slow_query_log='ON';  #開啟慢查詢

set global slow_query_log_file='/usr/local/slow.log'; #指定日誌文件路徑

set global long_query_time=10;  #自定慢查詢的時長　　

八、常用的SQL演算法設計

• 使用 WITH RECURSIVE tmp AS 語法實現遞歸查詢，比如獲取數結構的所有子節點Id：

with RECURSIVE  tmp as
(
    select * from work_order_question where id=4
    union all
    select c.* from work_order_question as c,tmp t where c.parent_id=t.id
)
select id from tmp