MySQL 總結

胖毛發表於2020-09-07
MySql

寫在前面：
好久沒有寫文章了，原諒我最近比較懶。這篇文章花了兩週，總結了3本書的內容：《MySQL技術內幕：InnoDB儲存引擎》，《高效能MySQL》3th,《MySQL架構優化實踐》，總共寫了3次，每次寫了一半覺得不滿意又刪了，想把3本的內容都放一起真的比較難。對於MySQL，還是非常高深的，以下知識點都是簡單的提了一下，每一條都值得讀者仔細琢磨。建議先看書，再看這篇文章的內容

MySQL是一個開源的，執行緒關係型資料庫，其名字來源於創作者Monty的女兒的名字：My

在MySQL中，一般資料庫，指的是存放資料的檔案，而資料庫例項指的是用來執行SQL的程式。

以下總結，都是基於MySQL InnoDB儲存引擎。

該總結內容來源於：《MySQL技術內幕：InnoDB儲存引擎》，《高效能MySQL》3th,《MySQL架構優化實踐》

MySQL 的結構

MySQL是一個基於外掛式儲存引擎的資料庫。在MySQL的體系結構中，提供了統一的標準管理和服務支援，而儲存引擎則是底層的物理結構實現，每個開發者都可以按照自己的意願進行開發。

在MySQL中，儲存引擎是基於表的，而不是資料庫

CREATE TABLE `t_test` (
  ...
) ENGINE=InnoDB

從上圖中，我們可以看出，MySQL體系中包含以下幾個重要的部分：

  • 連線池（Connection Pool）：用來管理MySQL的連線，快取連線
  • 管理服務和元件（Management Services ）: 系統管理和控制工具，比如備份恢復，MySQL同步，叢集等
  • SQL介面（SQL Interface）： 用來接受SQL命令，返回使用者需要查詢的結果
  • 解析器（Parser）：按照SQL語法解析SQL命令
  • 優化器（Optimizer）: 查詢優化器，優化傳入的SQL命令
  • 快取器（Caches & Buffers）：快取，用來快取一部分熱點資料，比如表快取，記錄快取等
  • 外掛式儲存引擎（Pluggable Storage Engines）：真正執行查詢，儲存的資料引擎，不同的引擎支援的操作不同，MySQL的儲存引擎是基於表的。
  • 物理檔案（File System）：持久化的一些資料，包括儲存的資料，Redo,Undo,Index...

儲存引擎：InnoDB

InnoDB是由第三方開發，後來被官方收的一個儲存引擎，InnoDB是MySQL在OLTP中應用最為廣泛的儲存引擎，其具有以下特性：

  • ☆通過多版本併發（MVCC）實現高併發效能
  • ☆支援SQL標準的4種隔離級別，預設為Repatable級別
  • ☆通過間隙鎖（Next-Key locking）來避免幻讀
  • 提供插入緩衝（insert-buffer）
  • 支援二次寫（double write）
  • 自適應雜湊索引（adaptive hash index）
  • 預讀（read ahead）
  • ...

☆ 表示作為開發應該重點掌握的

插入緩衝：

如果每執行一次SQL,就重新整理一下磁碟，則會存在很大的效能問題，因此InnoDB將所有的IO操作都放在了緩衝中進行，緩衝以頁為單位。

緩衝雖然解決了效能問題，但是又帶來了永續性的問題，如果資料還沒來得及刷盤，系統就崩潰了，這樣會造成資料丟失，因此InnoDB使用了Redo日誌，Redo日誌的寫入是順序寫入，因此不會對效能帶來太大的影響。

執行一次IO過程如下： 執行SQL -> 記錄Redo日誌 -> 持久化Redo日誌 -> 更新插入緩衝

MySQL 的檔案

日誌檔案

Error Log ：

錯誤日誌檔案，錯誤日誌檔案記錄了MySQL啟動，執行，關閉等記錄，同時包含一些警告資訊，當發現MySQL有異常的時候，應該第一時間檢視錯誤日誌檔案。

SHOW VARIABLES LIKE 'log_error'

Slow Log :

慢查詢日誌可以監控執行超過指定時間的SQL，從而記錄到日誌中，預設情況下MySQL並不啟動慢查詢日誌，使用者需要手工將這個引數設定為ON:

SHOW VARIABLES LIKE 'log_slow_queries';  //查詢是否開啟慢查詢日誌

ShOW VARIABLES LIKE 'long_query_time';    //查詢慢日誌的閾值，預設10s

SHOW VARIABLES LIKE 'log_queries_not_using_indexes'; //記錄所有沒有使用索引的SQL

SHOW VARIABLES LIKE 'log_throttle_queries_not_using_indexes';  //設定沒有記錄索引的SQL的執行次數閾值，只有超過這個閾值才記錄
SHOW VARIABLES LIKE 'log_output';         //看出日誌輸出格式

如果資料量過多，可以設定log_output格式為TABLE，MySQL會將資料記錄到slow_log表中

查詢日誌:

查詢日誌記錄了所有對MySQL資料庫的所有請求資訊，無論這些請求是否得到了正確的執行。

Binary Log :

二進位制日誌記錄了MySQL資料庫執行的所有的更改操作，但是不包括SELECT和SHOW等操作。通過二進位制日誌，可以達到以下幾種功能：

  • 恢復：通過二進位制日誌恢復資料
  • 複製：在主從同步的時候，通過二進位制日誌，將主資料庫資訊同步到從資料庫中
  • 審計：通過二進位制日誌，可以統計操作，檢視是否存在SQL注入

Binary Log預設沒有啟動，需要手動啟動

Redo Log

Redo Log是存在InnoDB下的重做日誌檔案，主要用處在於記錄InnoDB儲存引擎的事務日誌，同時用來保證事務的一致性。

正如前面所說，當進行IO的時候，會先寫入Redo，然後再寫入緩衝中。

Redo VS bin log:

  • 二進位制日誌檔案記錄的是MySQL所有資料庫有關的日誌記錄，是MySQL服務層面的，不管底層使用什麼儲存引擎，都會存在，而Redo Log只有InnoDB才會存在，是基於儲存引擎層面的
  • 同時二進位制日誌檔案記錄的是邏輯日誌，而Redo Log記錄的是關於每個頁的物理更改情況
  • 二進位制日誌檔案僅在事務提交前提交，只寫磁碟一次，而對於Redo來說，隨著事務的進行，會不斷的有日誌寫入到重做日誌檔案中
  • redo log是迴圈使用的，因此如果超過一定的資料量，會被覆蓋，而bin log則不會重複使用

Undo Log

undo log主要應用在事務當中，undo log中記錄的是使用者操作的反向邏輯操作，當事務執行出錯或者需要回滾的時候，可以讀取undo log中的內容執行進行回滾。undo log的永續性保證也是通過redo log來實現，也就是undo log的產生會伴隨著redo log的產生。

表結構定義檔案

MySQL中，將表的結構定義存放在以frm為字尾的檔案中。

在InnoDB中，所有的資料儲存結構如下：

表空間:

表空間是InnoDB儲存引擎邏輯的最高層，所有的資料都存放在表空間中。

在InnoDB中，表空間可以設定為共享表空間和獨立表空間。

  • 共享表空間： 所有資料存放在同一個表空間中
  • 獨立表空間： 每張表的資料單獨放在一個表空間中，（僅僅是資料，索引，插入緩衝單獨存放，回滾資訊，事務資訊等還是放在原來的共享表空間中）

段：

表空間由各個段組成，一般分為資料段，索引段，回滾段等。

區：

區是由連續頁組成的空間，每個區大小1MB,多個區便組成了一個段。

頁：

多個頁組成了一個區，一般來說，一頁的大小為16K,因此64個頁組成了一個區，可以通過引數設定頁的大小為4K，8K,16K等，頁是資料檔案管理的最小單位，也是檔案空間分配的最小單位

InnoDB中常見的頁有：

  • 資料頁(B-tree Node)
  • undo頁（undo Log Page）
  • 系統頁（System Page）
  • ...

MySQL 索引

在InnoDB中，主要包含3種索引：

  • B+樹索引
  • 全文索引
  • 雜湊索引

B+樹索引

在InnoDB中，索引通過B+樹來實現，B+樹作為索引的優點：

  • 非葉子節點不儲存資料，使得每頁能儲存更多索引，減少IO的次數
  • 葉子節點存在相互索引的指標，便於範圍查詢
  • 效能穩定，每次查詢都需要通過相同的次數才能獲取到需要的資料

需要知道的是，在InnoDB中，B+樹的節點對應的是表結構中的頁(Page)，也就是每次IO都是讀取資料頁（B-Tree Node）一頁的資料。

InnoDB的這種按照索引的結構組織資料的資料表被稱為索引組織表，索引組織表對應的索引被稱為聚集索引，指的是索引和資料在邏輯上儲存順序相同，索引組織表的有點如下：

  • 資料的相關性大，因為資料都是按照主鍵順序存放，因此需要查詢可主鍵相關的資料時，只需要從磁碟讀取少量資料就能獲取全部相關資訊。
  • 資料訪問更快，因為索引和資料存放在一起，因此在查詢完索引後，就能直接獲取到資料
  • 使用索引覆蓋掃描的查詢可以直接使用葉節點的主鍵值

索引組織表的缺點如下：

  • 插入速度嚴重依賴於插入順序，如果插入的資料不是有順的，則會導致InnoDB隨機IO磁碟。
  • 更新聚集索引的代價很高，因為資料是按照聚集索引的順序存放，因此會強制移動所有被更新的行
  • 聚集索引可能會導致全表掃描變慢
  • 聚集索引會導致非聚集索引訪問資料需要進行兩次查詢

輔助索引

輔助索引也叫二級索引，是非聚集索引，其內部結構也是一顆B+樹，不過和聚集索引不同的是，聚集索引的葉子節點儲存的是資料，而輔助索引葉子節點存放的是聚集索引的key.這樣就會帶來一個問題就是對於輔助索引的查詢，會存在一個二次回表的操作。

比如

 SELECT * FROM t_test WHERE name='小紅';   //主鍵為`id`

InnoDB會先在name輔助索引上查詢對應的id,然後再通過id在聚集索引中查詢具體的資料。

如果一個B+樹為3層，則此次查詢就需要經過6次IO

索引的管理

檢視索引： SHOW INDEX FROM t_rule;，返回結果如下：

  • Table: 對應的表
  • Non_unique: 是否非唯一
  • Key_name: 索引名
  • Seq_in_index: 索引中，該列的位置
  • Cloum_name: 索引的列的名稱
  • Collection: 索引存放方式，一般都是A,表示是B+樹
  • Cardinality：非常關鍵的值，表示索引中唯一值的數目的估計值。Cardinality與表的行數比應儘可能接近1，如果非常小，那麼使用者需要考慮是否可以刪除此索引。
  • Sub_part：是否是列的部分被索引。如果看idx_b這個索引，這裡顯示100，表示只對b列的前100字元進行索引。如果索引整個列，則該欄位為NULL。
  • Packed：關鍵字如何被壓縮。如果沒有被壓縮，則為NULL。
  • Null：是否索引的列含有NULL值。
  • Index_type：索引的型別。InnoDB儲存引擎只支援B+樹索引，所以這裡顯示的都

索引的使用

  • 不是對於任何查詢都適合新增索引的，InnoDB會判定索引的Cardinality值，當該值與資料庫的行的比值過小時，InnoDB會忽略此索引,比如性別

  • 索引不是越多越好，索引對於插入和修改存在一定的效能影響，因此對於索引的新增需要有一定的考量

  • 最好在建立表的時候就建立索引，MySQL線上DDL會阻塞資料庫

  • 由於B+樹的特殊性，因此對於聯合索引，需要遵循最左匹配原則。

  • 同理，由於輔助索引的二次回表的特性，對於輔助索引name,等效於name,id（id為主鍵）

  • 可以利用索引覆蓋原理，來避免二次回表，索引覆蓋就是需要查詢的欄位，都在索引中能找到

  • 如果對於資料庫來說，二次回表查詢的資料過多，導致效能低於全表掃描，MySQL可能會放棄使用索引

自適應Hash索引

在MySQL中，對於等值查詢，如果達到一定的條件，MySQL會在內部建立Hash索引優化查詢速度，這種索引由MySQL自動建立，自己維護，因此又叫做自適應雜湊（AHI,Auto Hash Index）索引，自適應哈索引建立的條件如下：

  • 需要對這個也得連續訪問模式是一樣的，比如where a=xxx 和where a=xxx and b=xxx屬於兩種訪問模式
  • 以同一個模式訪問了100次
  • 此頁通過該模式訪問了N次，其中N=頁中記錄數/16

InnoDB儲存引擎官方文件顯示，啟用AHI後，讀取和寫入速度可以提高2倍，輔助索引的連線操作效能可以提高5倍。AHI的設計思想是資料庫自優化，不需要DBA對資料庫進行手動調整。

MySQL 的事務

事務：是資料庫操作的最小工作單元，是作為單個邏輯工作單元執行的一系列操作；這些操作作為一個整體一起向系統提交，要麼都執行、要麼都不執行；事務是一組不可再分割的操作集合（工作邏輯單元）

事務滿足四大特性：

  • 原子性 (Atomicity)
    事務是資料庫的邏輯工作單位，事務中包含的各操作要麼都做，要麼都不做
  • 一致性 (Consistency)
    事務執行的結果必須是使資料庫從一個一致性狀態變到另一個一致性狀態。因此當資料庫只包含成功事務提交的結果時，就說資料庫處於一致性狀態。
  • 隔離性 (Isolation)
    一個事務的執行不能其它事務干擾。即一個事務內部的操作及使用的資料對其它併發事務是隔離的，併發執行的各個事務之間不能互相干擾。
  • 永續性 (Durability)
    也稱永久性，指一個事務一旦提交，它對資料庫中的資料的改變就應該是永久性的。接下來的其它操作或故障不應該對其執行結果有任何影響

其中，ACID中，最終的目的都是為了保證一致性（C），而原子性，隔離性，永續性都是達到一致性的方式。

ACD

在InnoDB中，ACD則通過資料庫的redo log和undo log來完成，其中redo log成為重做日誌，用來保證原子性和永續性，而undo log則用來保證事務的一致性。

隔離性

InnoDB的隔離性則通過鎖和MVCC來實現，由於完整的隔離性，只能通過互斥鎖來實現，而互斥鎖帶來的問題便是效能急劇下降，因此處於對效能的妥協和結合日常業務的使用，根據加鎖的程度不同，又將隔離性分為以下四個級別：

  • 讀未提交(READ UNCOMMITTED)： 會出現髒讀的問題
  • 讀已提交(READ COMMITTED)： 不會出現髒讀，但是會出現不可能重複讀的問題
  • 可重複讀(REPEATABLE READ)： 不會出現不可重複讀，但是會出現幻讀的問題
  • 序列化(SERIALIZABLE)： 不會出現幻讀問題

髒讀

對於髒讀，便是當前事務讀取了其他事務未提交的資料，這明顯是錯誤的，因為如果其他事務如果回滾，則會帶來邏輯上的錯誤。解決髒讀的方法也很簡單，可以將事務中修改的資料快取起來，等真正提交的時候再提交(InnoDB通過MVCC解決)

不可重複讀

當解決了髒讀問題後，在某些業務場景中，可能還會出現一種情況，就是對於同一個事務中，如果兩次相同的條件的讀取，卻返回了不同的結果，這樣對於某些業務場景來說，可能是不能接受的，解決不可重複讀的簡單粗暴的方案就是，每讀取一行資料，就將這行資料鎖定，不讓其他事務修改，這樣便避免了不可重複讀的問題，但是由於新增了一定的鎖，因此可能會帶來一定的效能問題。（InnoDB通過MVCC解決）

幻讀

幻讀是對於兩次相同的查詢，返回了原本不存在的資料。為什麼要區分幻讀和不可重複讀呢，具體的原因便是不可重複讀能夠標記需要鎖定哪些行，而幻讀需要鎖定的是原本不存在的行，因此加鎖的維度不一樣，一般來說，解決不可重複讀，只用鎖定一些被讀取的行即可，而想要解決幻讀，則需要新增範圍鎖，甚至是表鎖，這樣就變成每個事物的進行，都需要序列化排隊。(InnoDB通過MVCC加上Next-key Lock解決)

MySQL 鎖 和 MVCC

前面說到了InnoDB的事務的隔離性通過鎖和MVCC實現，接下來詳細介紹鎖和MVCC：

想要明白MySQL的鎖，需要首先明白對於資料訪問，主要分為兩類：

  • 讀取資料
  • 修改資料

修改資料控制

一般來說，對於修改資料，一般的都是通過加鎖來實現，在InnoDB中，鎖主要分為以下幾種：

  • 按鎖定的資料量區分

    • 表鎖： 鎖定整個資料表
    • 行鎖： 鎖住單個資料行

  • 按鎖的型別分

    • 共享鎖：共享鎖可以和共享鎖相容，但是不能和排他鎖相容，也被稱作為讀鎖
    • 排他鎖：排他鎖會排斥其他任何鎖，也被稱作為寫鎖
    • 意向共享鎖： 在InnoDB中主要為表鎖，用來標記這張表某些行存在共享鎖，便於加標鎖的時候判斷
    • 意向排他鎖： 同上，標記這張表中存在排他鎖

    一般來說，修改資料(Insert,Update,Delete)預設都是新增的排他鎖

    當然，對於讀取資料，也可以通過手動加鎖的方式來實現:

    • 共享鎖： SELECT... LOCK IN SHARE MODE
    • 排他鎖： SELECT... FOR UPDATE

    而對於讀，InnoDB則通過多版本併發控制（Multi Version Concurrency Control，MVCC） 實現。

讀取資料控制

在日常資料庫的操作中，可能存在對同一個事務，多次讀取的資料，又被其他的連線修改，這樣便會存在資料不一致的情況，而InnoDB則通過undo日誌，記錄了同一行資料的不同時刻的值，用來解決上述問題，這樣的方式便是多版本併發控制.

MVCC

mvcc：多版本併發控制，主要通過undo log記錄每行資料的不同版本的值，從而實現對於不同級別的讀，不用加鎖，而通過標記事務版本號，讀取到對應版本的資料，從而實現了資料隔離。

InnoDB 通過調整生成ReadView的時間與判斷策略，從而實現了RC和RR隔離級別。

ReadView記錄了一些關鍵的事務版本號，每次執行通過對比版本號的大小，選擇需要讀取的資料版本，主要屬性如下：

  • up_limit_id: 活躍事務列表trx_ids中最小的事務ID,如果當前版本號小於up_limit_id，則表示此事務一定已經提交
  • low_limit_id： 當前最大的事務號+1，如果當前版本號大於low_limit_id，則表示此事務是在當前事務之後建立的
  • trx_ids ： 當前活躍的事務id，表示當前事務建立的時候，其他已經開始但是還未提交的事務

假設當前事務需要讀取一個資料行，當前資料的版本號為trx_id，則：

  • 如果trx_id < up_limit_id, 那麼表明“最新修改該行的事務”在“當前事務”建立快照之前就提交了，所以該記錄行的值對當前事務是可見的。則直接返回此資料。

  • 如果 trx_id >= low_limit_id, 那麼表明“最新修改該行的事務”在“當前事務”建立快照之後才修改該行，所以該記錄行的值對當前事務不可見。

  • 如果 up_limit_id <= trx_id < low_limit_id, 表明“最新修改該行的事務”在“當前事務”建立快照的時候可能處於“活動狀態”或者“已提交狀態”，則需要依據當前隔離級別而定。

對於InnoDB來說，RR和RC產生Read View的區別在於：

RC: 讀已提交，在事務中每次讀的時候，都會重新生成一個ReadView

RR: 不可重複讀，在事務第一次讀的時候，會生成一個ReadView，並一直保持到事務結束

簡單來說，對於RC來說，由於是每次讀都會生成一個ReadView，因此ReadView中已提交的事務是實時更新的，因此會讀取到其他事務已經讀取的資料，同時也能隔離掉未提交的資料。

對於RR來說，當第一次讀取資料後，ReadView就一直保持著以前的版本號，因此也就保證了後面的資料能和第一次讀的資料相同。

MVCC 中的當前讀和快照讀

通過MVCC可以讀取不同版本的資料，解決了髒讀和不可重複讀的問題。同時，基於這種版本控制資料的讀取，也能解決一部分的幻讀問題：簡單的兩次SELECT返回的資料永遠是相同的。

按道理來說，通過MVCC，當前事務是讀取不到其他事務在其後面插入/刪除的資料的。然後，由於MVCC的特殊性，也帶來了另外一種特殊的幻讀：

SELECT * FROM t_student WHERE id =1;               //當前事務.....1

INSET INTO t_student (id,name) VALUES (1,"dcc");   //其他事務.....2

INSET INTO t_student (id,name) VALUES (1,"dcs");   //當前事務.....3

其中，①和③為當前事務執行的操作，②為另外一個事務執行的操作。

可以預見的是，③會執行失敗，對於當前事務來說，明明查詢了當前資料庫中不存在id=1的資料，為什麼插入的時候還是報id衝突呢？這樣的現象也算是產生了幻讀。

產生這樣的問題的原因在於由於存在多版本資料控制，因此在InnoDB中，會將資料的讀取分為兩種：

  • 當前讀：讀取的永遠都是最新的資料。INSERT/DELETE/UPDATE/SELECT...FOR UPDATE/SELECT...IN SHARE MODE
  • 快照讀：讀取的可能是某個資料的快照版本。 SELECT...FROM

對於所有的資料修改操作，都是當前讀，因為如果修改的是歷史版本，那不符合邏輯。

而對於普通的SELECT,則是前面的說的快照讀。快照讀和當前讀帶來的問題便是資訊不一致，也就導致了上述舉例中的幻讀問題,解決上面的幻讀問題也很簡單，新增一個FOR UPDATE加鎖即可。

但是使用FOR UPDATE又會帶來另外一個問題：

SELECT * FROM t_student WHERE id > 4 FOR UPDATE;    //當前事務 ①

INSET INTO t_student (id,name) VALUES (1,"dcc");   //其他事務 ②

SELECT * FROM t_student WHERE id > 4 FOR UPDATE;   //當前事務 ③

由於使用了當前讀，會導致當前事務能讀取到其他事務已經提交的資料，這樣不僅再次帶來了幻讀問題，還會帶來不可重複讀問題。

InnoDB給的解決方案便是Next Key Lock，Next Key Lock是行鎖與間隙鎖的結合：

  • Record Lock： 鎖定當前資料行
  • Gap Lock : 間隙鎖，鎖定一個範圍，但是不包含記錄本身
  • Next-Key Lock: Gap Lock+Record Lock,鎖定範圍以及記錄本身。

當使用Next-Key Lock的時候，InnoDB會根據索引範圍，鎖定住查詢的範圍。

比如：

SELECT * FROM t_student WHERE id > 4 FOR UPDATE;

則InnoDB會鎖定(4,+∞)的所有行，當其他事務需要插入此範圍的值的時候，會被阻塞直到當前事務提交。

對於Next-Key Lock的理解，需要注意以下幾點：

  • 想要通過InnoDB在RR隔離級別完全解決幻讀問題，需要通過Next-Key Lock

  • 在RR級別中，普通的SELECT不會新增Next-Key Lock,需要顯示新增排他鎖

  • 對於唯一隻的查詢，Next-Key Lock會降級為Record Lock

關於鎖的注意事項：

雖然InnoDB支援行鎖( Record Lock),但是由於InnoDB內部是B+樹來實現，因此鎖定一個資料行其實是鎖定了通過索引來實現的。因此如果在需要鎖定的時候資料行中沒有走索引的話，行鎖則會退化成表鎖。

MySQL 執行過程

SELECT id,count(sex) as number 
FROM t_test 
WHERE name='aaa' 
GROUP BY class 
HAVING  times>1
ORDER BY name 
LIMIT 2;

想要理解MySQL的執行過程，可以嘗試自己設計一個DB的時候，應該怎麼做：

  • 首先，應該明確資料來源，因此需要解析FROM，找到對應的資料表
  • 找到資料表後，下一步便是通過過濾條件，獲取具體的資料行，因此需要解析WHERE
  • 拿到具體的資料行之後，接下來需要處理資料行，因此需要解析GROUP BY
  • 通過GROUP BY分組之後，就可以將同一個組的資料進行聚合，因此解析count(),sum(),arg()等
  • 通過聚合函式之後，則可以將聚合之後的資料進行過濾，因此解析HAVING
  • 處理完資料行之後，接下來檢視使用者需要的資料列，因此解析SELECT
  • 剩下的資料便是使用者真真需要的資料，但是SQL可能會對資料進行排序等，因此處理 DISTINCT以及 ORDER
  • 最後，排序完成後，檢視使用者是否需要返回指定的行，因此處理LIMIT

總結出來執行順序便是：

FROM->WHERE->GROUP BY->count()->HAVING->SELECT->DISTINCT->ORDER->LIMIT

MySQL 優化方案

對於MySQL來說，想要優化MySQL的時候，就需要理解前面的知識點。

優化MySQL，可以從各個方面入手：

語法

  • 一般情況下，應該儘量使用可以正確儲存資料的最小資料型別，更小意味著更快。

    例如：整型比字元操作代價更低，因為字串涉及到字符集和校對規則問題

  • 通常情況下最好指定列為NOT NULL，除非真的需要儲存NULL值

    對於MySQL來說，可以為NULL的列更難優化，因此NULL會影響索引，索引統計和值的比較等，可以為NULL的列會使用更多的儲存空間。當對可為NULL的列建立索引的時候，每個索引記錄都需要額外使用一個直接。

  • 對於浮點型別，優先選擇FLOAT,DOUBLE,只有在真正需要DECIMAL的時候才使用DECIMAL,如果資料量過大的時候，可以考慮使用BIGINT代替DECIMAL

  • 注意使用VARCHAR

    由於VARCHAR型別是一個邊長字串，因此如果這個字串長度會頻繁變化的話，會給MySQL帶來記憶體碎片的問題，同時其在臨時表和排序時可能導致悲觀的按最大長度分配記憶體。

  • 定義表的時候，對於浮點型不要指定進度，對於整數，不要指定寬度

    在MySQL中對浮點數指定精度會使得MySQL悄悄選擇不同的資料型別，或者在儲存時對值進行取捨。而這些精度定義是非標準的，因此最好的做法就是隻指定資料型別，不指定精度。

  • 注意count(*)和count(欄位)的區別在於前者會統計NULL，而後者不會統計NULL

索引

說到MySQL優化，可能第一想法就是加索引，因為使用索引能將效能提高几個數量級，但是索引不是萬能的，有時候有些場景可能並不適合使用索引，使用索引，首先需要遵從下面的原則：

  • 聯合索引需要遵從最左匹配原則
  • 對於很長的值，則可以選擇使用字首索引
  • 通常來說，對於聯合索引，將選擇性更高的索引放在前面可以更好的避免隨機IO

索引覆蓋：

可以將需要獲取的資料都新增索引，從而實現僅僅通過索引就能獲取需要的資料，避免二次回表。例如：

SELECT name,sex FROM t_student WHERE name='dcc';

正常來說，如果只建立了name的索引，那麼InnoDB再通過二級索引name查詢到dcc後，會獲取聚集索引id，然後再通過id查詢聚集索引來獲取具體的資料行。這就是二次回表。

而如果建立的索引是(name,sex)，那麼在查詢到name為dcc的節點後，就能直接通過二級索引找到sex的值，這樣就避免了二次回表，從而帶來效能的提升。

使用索引覆蓋能解決一些不好優化的問題：

SELECT id FROM t_student WHERE name LIKE '%cc%';

對於MySQL,這種萬用字元開頭的模糊查詢，是不會走索引的。

但是如果把name加上索引，又由於id是主鍵，因此上述查詢可以直接通過索引覆蓋就能完成，因此可以通過索引覆蓋是的上述語句走索引。

延遲關聯：

明白了索引覆蓋的原理後，後續一些優化就可以通過索引覆蓋來實現：

SELECT * FROM t_student WHERE name LIKE '%cc%';

對比這條SQL和上面的SQL，唯一的區別就是需要獲取的資料不同，SELECT *如果列比較多的話，很可能無法實現索引覆蓋，那對於這樣的查詢，應該如何優化呢？

我們可以使用延遲關聯：

SELECT * FROM t_student s JOIN (SELECT id FROM t_student WHERE name LIKE '%cc%') AS s1 ON  s.id = s1.id;

通過前面的例子我們知道，子語句是會走索引的，而外層查詢id是主鍵，也是會走索引的， 因此這條SQL就被完全優化成走SQL的索引。

延遲關聯只是一種優化思路，其實在MySQL5.6 推出ICP(預設開啟)後，有些情況就可以直接通過ICP解決，因為ICP會自動將where過濾條件下沉到儲存引擎，儲存引擎則可以直接通過索引過濾掉相應的資料。但是ICP也有相應的限制，關於ICP後續詳細介紹

索引的除錯：

對於一個查詢，是否走了索引，索引的型別是什麼，走的聯合索引還是部分索引，這些都需要結合SQL進行分析，而在MySQL中，最好分析的索引的方法便是檢視執行計劃：EXPLAIN

EXPLAIN SELECT * FROM t_student WHERE class='17' AND name LIKE '%cc%';

該SQL返回的欄位如下：

id： 1
select_type: SIMPLE
table: t_student
partions: NULL
type: ref
possible_keys:idx_class
key:idx_class
key_len:43
ref:const
rows:749
filterd:11.11
Extra: using index

這裡簡單說下幾個重要的欄位：

  • type: 表示查詢的方式，常見的值有：

    • ALL:全表掃描
    • index: 索引掃描
    • range: 選擇索引的範圍進行掃描
    • ref: 表示上述表的連線匹配條件，即哪些列或常量被用於查詢索引列上的值
    • eq_ref: 類似ref,區別在於使用的是唯一索引
    • const,system: 當MySQL對查詢某部分進行優化，並轉換為一個常量時，使用這些型別訪問。例如where id=xxx

  • Key: MySQL實際使用的索引

  • rows:表示MySQL根據表統計資訊及索引選用情況，估算的找到所需的記錄所需要讀取的行數

  • Extra:

    • Using where:不用讀取表中所有資訊，僅通過索引就可以獲取所需資料，這發生在對錶的全部的請求列都是同一個索引的部分的時候，表示mysql伺服器將在儲存引擎檢索行後再進行過濾

    • Using temporary：表示MySQL需要使用臨時表來儲存結果集，常見於排序和分組查詢，常見 group by ; order by

    • Using filesort：當Query中包含 order by 操作，而且無法利用索引完成的排序操作稱為“檔案排序”

    • Using join buffer：強調在獲取連線條件時沒有使用索引，並且需要連線緩衝區來儲存中間結果。如果出現了這個值，那應該注意，根據查詢的具體情況可能需要新增索引來改進能。

    • Using index condition: 使用了ICP

MySQL 自帶的優化

在MySQL中，也自帶一些優化方案，這些優化策略都是MySQL自帶了，瞭解他們可以避免一些無效優化：

Change Buffer

前面說過，對於聚集索引，存在的一個缺點就是如果插入的資料是無序的，那麼可能導致InnoDB隨機寫磁碟，這樣會帶來效能問題。

因此在InnoDB中，使用了Change Buffer，Change Buffer會將插入的資料暫時快取，然後將裡面的資料進行排序，最後再進行插入/修改，這樣就能減少插入離散資料帶來的效能問題。

但是不要過分依賴Change Buffer，如果資料量過大，則可能導致Change Buffer被裝滿，那麼後面的資料依然是離散的，因此如果要插入大量的資料，可以考慮先在程式碼中排序，然後再插入。

ICP

ICP,(Index Condition PushDown,索引條件下推)： 原本對於MySQL來說，有些過濾條件不會推送到儲存引擎，因此對於一條查詢:

SELECT * FROM employees WHERE first_name='Anneke' AND last_name LIKE '%Preusig'

MySQL會先將first_name='AnneKe'下推到InnoDB，InnoDB則通過索引查詢所有符合要求的資料並返回給MySQL,MySQL拿到資料後，再過濾不滿足last_name LIKE '%Preusig'的條件。

這樣帶來的問題就是儲存引擎上推的資料比較大，並且經過了兩次過濾。

當開啟ICP過後，MySQL會將兩個條件都下推給InnoDB，InnoDB可以直接通過索引過濾到所有的資料，直接返回正確的結果。

使用執行過程分析的時候，如果使用了ICP,則Extra 會返回Using index condition

ICP的生效條件如下：

  1. 當sql需要全表訪問時，ICP的優化策略可用於range, ref, eq_ref, ref_or_null型別的訪問資料方法 。

  2. 支援InnoDB和MyISAM表。

  3. ICP只能用於二級索引，不能用於主索引。

  4. 並非全部where條件都可以用ICP篩選，如果where條件的欄位不在索引列中，還是要讀取整表的記錄到server端做where過濾。

  5. ICP的加速效果取決於在儲存引擎內通過ICP篩選掉的資料的比例。

  6. 當sql使用覆蓋索引時，不支援ICP優化方法。

MRR

MRR,(Multi-Range Read Optimization) ,MRR和Chaneg Buffer有異曲同工之妙，在沒有開啟MRR的時候，如果通過聚集索引讀取資料，那樣是順序查詢，效能比較高，但是對於二級索引的順序查詢，如果再經過二次回表，可能會變成離散IO:

SELECT * FROM t_student WHERE birthday<2019

如上述SQL,birthday是二級索引，id為主鍵索引。

在InnoDB中，會首先通過birthday的索引查詢滿足birthday<2019的主鍵id，由於查詢是按照birthday排序的，比如2019,2018,2017,2016，因此返回的主鍵id則可能是無序的，比如199,1,200,23,3,5040,188,那麼再通過id獲取所有的資料的時候，就變成了離散讀取：

MRR的思想就很簡單，開啟MRR之後，MySQL會將所有返回的主鍵先進行排序，然後再進行回表。這樣就避免了離散讀取的問題。

相關文章