共計 6029 個字符，預計需要花費 16 分鐘才能閱讀完成。

這篇文章主要介紹“MySQL 優化原理是什么”，在日常操作中，相信很多人在 MySQL 優化原理是什么問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL 優化原理是什么”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學習吧！

Server 層主要   包含連接器、檢索內存、分析器、優化器、執行器等，所有跨存儲引擎的功能均于這一層構建，例如存儲過程、觸發器、視圖，函數等，有一個標準化的 binglog 日志模塊。

存儲引擎負責數據的存儲與存取，使用可更換的插件式架構，擁有 InnoDB、MyISAM、Memory 等多個存儲引擎，其中 InnoDB 引擎有 redo log 日志模塊。如下圖所示

實驗環境

操作系統內核版本：Tencent tlinux release 2.2

MySQL 數據庫版本：5.7.10

創建新表 tb_article，創建了兩個索引：index_title、index_author_id，表結構如下：

我們嘗試插入一些數據：

現執行 SQL 語句，select * from tb_article where author_id=20 and title= b 分析該 SQL 語句的執行過程和優化策略。

MySQL 執行 SQL 語句過程

一、MySQL 客戶端和服務器通訊

客戶端按照 MySQL 通信協議將 SQL 發送到服務端，SQL 到達服務端后，服務端會單起一個線程執行 SQL。MySQL 客戶端和服務器之間的通訊協議是“半雙工”的。

二、查詢狀態

對于 MySQL 連接，任何時刻都有一個狀態，該狀態表示了 MySQL 當前正在做什么。使用 show full processlist 命令查看當前狀態。在一個查詢生命周期中，狀態會變化很多次，下面是這些狀態的解釋：

1. sleep：  線程正在等待客戶端發送新的請求；

2. query：  線程正在執行查詢或者正在將結果發送給客戶端；

3. locked：  在 MySQL 服務器層，該線程正在等待表鎖。  在存儲引擎級別實現的鎖，例如 InnoDB 的行鎖，并不會體現在線程狀態中。  對于 MyISAM 來說這是一個比較典型的狀態；

4. analyzing and statistics：  線程正在收集存儲引擎的統計信息，并生成查詢的執行計劃；

5. copying to tmp table：  線程在執行查詢，并且將其結果集復制到一個臨時表中，這種狀態一般要么是做 group by 操作，要么是文件排序操作，或者 union 操作。  如果這個狀態后面還有 on disk 標記，那表示 MySQL 正在將一個內存臨時表放到磁盤上；

6. sorting result：  線程正在對結果集進行排序；

7. sending data：  線程可能在多個狀態間傳送數據，或者在生成結果集，或者在想客戶端返回數據。

三、查詢緩存

MySQL 的緩存主要的作用是為了提升查詢的效率，緩存以 key 和 value 的哈希表形式存儲，key 是具體的 sql 語句，value 是結果的集合。如果無法命中緩存, 就繼續走到分析器的的一步, 如果命中緩存就直接返回給客戶端。

如果使用查詢緩存，在進行讀寫操作時會帶來額外的資源消耗，如果在一個寫多讀少的環境中，緩存會頻繁的新增和失效。MySQL8.0 版本開始取消查詢緩存。

四、查詢優化處理

查詢的生命周期的下一步是將一個 SQL 轉換成一個執行計劃，MySQL 在依照這個執行計劃和存儲引擎進行交互。這包含多個子階段：解析 SQL、預處理、優化 SQL 執行計劃。這個過程中任何錯誤都可能終止查詢。

1. 語   法解析器和預處理：  首先 MySQL 通過關鍵字將 SQL 語句進行解析，并生成一顆對應的“解析樹”。 MySQL 解析器將使用 mysql 語法規則驗證和解析查詢；  預處理器則根據一些 MySQL 規則進一步檢查解析數是否合法。

2. 查詢優化器：  當語法樹被認為是合法的了，并且由優化器將其轉化成執行計劃。  一條查詢可以有很多種執行方式，最后都返回相同的結果。  優化器的作用就是找到這其中最好的執行計劃。

3. 執行計劃： MySQL 不會生成查詢字節碼來執行查詢，MySQL 生成查詢的一棵指令樹，然后通過存儲引擎執行完成這棵指令樹并返回結果。  最終的執行計劃包含了重構查詢的全部信息。

五、查詢執行引擎

在解析和優化階段，MySQL 將生成查詢對應的執行計劃，MySQL 根據優化器生成的執行計劃，調用存儲引擎的 API 來執行查詢。

六、返回結果給客戶端

了解 select * from tb_article where author_id=20 and title= b 性能和優化策略，一般采用 explain 命令進行分析。

MySQL explain

MySQL Query Optimizer 通過執行 explain 命令來獲取一個 Query 在當前狀態的數據庫中的執行計劃。expain 出來的信息有 10 列，分別是 id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

下面對這些字段出現的可能進行解釋：

1. id

id 列的編號是 select 的序列號，有幾個 select 就有幾個 id，并且 id 的順序是按 select 出現的順序增長的。MySQL 將 select 查詢分為簡單查詢和復雜查詢。復雜查詢分為三類：簡單子查詢、派生表（from 語句中的子查詢）、union 查詢。

2.select_type

(1) SIMPLE(簡單 SELECT, 不使用 UNION 或子查詢等)；

(2) PRIMARY(查詢中若包含任何復雜的子部分, 最外層的 select 被標記為 PRIMARY)；

(3) UNION(UNION 中的第二個或后面的 SELECT 語句)；

(4) DEPENDENT UNION(UNION 中的第二個或后面的 SELECT 語句，取決于外面的查詢)；

(5) UNION RESULT(UNION 的結果)；

(6) SUBQUERY(子查詢中的第一個 SELECT)；

(7) DEPENDENT SUBQUERY(子查詢中的第一個 SELECT，取決于外面的查詢)；

(8) DERIVED(派生表的 SELECT, FROM 子句的子查詢)；

(9) UNCACHEABLE SUBQUERY(一個子查詢的結果不能被緩存，必須重新評估外鏈接的第一行)。

3. table

這一列表示 explain 的一行正在訪問哪個表。當 from 子句中有子查詢時，table 列是   derivenN   格式，表示當前查詢依賴 id=N 的查詢，于是先執行 id=N 的查詢。當有 union 時，UNION RESULT 的 table 列的值為   union1,2，1 和 2 表示參與 union 的 select 行 id。

4. type

表示 MySQL 在表中找到所需行的方式，又稱“訪問類型”。

常用的類型有：ALL, index,  range, ref, eq_ref, const, system, NULL（從左到右，性能從差到好）

ALL：Full Table Scan，MySQL 將遍歷全表以找到匹配的行

index: Full Index Scan，index 與 ALL 區別為 index 類型只遍歷索引樹

range: 只檢索給定范圍的行，使用一個索引來選擇行

ref: 表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值。相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴，索引要和某個值相比較，可能會找到多個符合條件的行。

eq_ref: 類似 ref，區別就在使用的索引是唯一索引，對于每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連接中使用 primary key 或者 unique key 作為關聯條件

const、system: 當 MySQL 對查詢某部分進行優化，并轉換為一個常量時，使用這些類型訪問。如將主鍵置于 where 列表中，MySQL 就能將該查詢轉換為一個常量,system 是 const 類型的特例，當查詢的表只有一行的情況下，使用 system

NULL: MySQL 在優化過程中分解語句，執行時甚至不用訪問表或索引，例如從一個索引列里選取最小值可以通過單獨索引查找完成。

5. possible_keys

這一列顯示查詢可能使用哪些索引來查找。

explain 時可能出現 possible_keys 有列，而 key 顯示 NULL 的情況，這種情況是因為表中數據不多，MySQL 認為索引對此查詢幫助不大，選擇了全表查詢。

如果該列是 NULL，則沒有相關的索引。在這種情況下，可以通過檢查 where 子句看是否可以創造一個適當的索引來提高查詢性能，然后用 explain 查看效果。

6. key

key 列顯示 MySQL 實際決定使用的鍵（索引）

如果沒有選擇索引，鍵是 NULL。要想強制 MySQL 使用或忽視 possible_keys 列中的索引，在查詢中使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX。

7. key_len

表示索引中使用的字節數，可通過該列計算查詢中使用的索引的長度（key_len 顯示的值為索引字段的最大可能長度，并非實際使用長度，即 key_len 是根據表定義計算而得，不是通過表內檢索出的）。

不損失精確性的情況下，長度越短越好。

8. ref

表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值。

9. rows

表示 MySQL 根據表統計信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數，這個不是結果集里的行數。

10. Extra

該列包含 MySQL 解決查詢的詳細信息, 有以下幾種情況：

Using index：這發生在對表的請求列都是同一索引的部分的時候，返回的列數據只使用了索引中的信息，而沒有再去訪問表中的行記錄，是性能高的表現。

Using where：列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對表的全部的請求列都是同一個索引的部分的時候，表示 mysql 服務器將在存儲引擎檢索行后再進行過濾。

Using temporary：表示 MySQL 需要使用臨時表來存儲結果集，常見于排序和分組查詢。

Using filesort：MySQL 中無法利用索引完成的排序操作稱為“文件排序”，對結果使用一個外部索引排序，而不是按索引次序從表里讀取行。此時 mysql 會根據聯接類型瀏覽所有符合條件的記錄，并保存排序關鍵字和行指針，然后排序關鍵字并按順序檢索行信息。這種情況下一般也是要考慮使用索引來優化的。

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

Impossible where：這個值強調了 where 語句會導致沒有符合條件的行。

執行 explain 語句

explain select * from tb_article where author_id=20 and title= b

可以發現，執行這條 SQL 語句實際上沒有走 index_title 索引，而是選擇走 index_author_id 索引。

打開 optimizer trace 功能：

SET optimizer_trace= enabled=on  select * from information_schema.optimizer_trace\G;

執行計劃最終選擇了 index_author_id 索引，原因是 index_author_id 的 cost 小于 index_title。這里需要介紹 MySQL 的代價計算模型。

MySQL 代價模型

總代價模型：COST = CPU Cost + IO Cost

MySQL 在 cost 類型上分為 IO、CPU 和 Memory，MySQL5.7 的代價模型還在完善中，Memory 的代價雖然已經收集了，但還沒有計算在最終的代價中。

MySQL5.7 在源碼上對 cost 模型進行了大量重構，代價分為 server 層和 engine 層。server 層主要是 CPU 代價，而 engine 層主要是 IO 代價。MySQL5.7 引入了兩個系統表 mysql.server_cost 和 mysql.engine_cost 來分別配置這兩個層的代價。

以下分析均基于 MySQL5.7.10

server_cost

1. row_evaluate_cost (default 0.2) 計算符合條件的行的代價，行數越多，此項代價越大；

2. memory_temptable_create_cost (default 2.0) 內存臨時表的創建代價；

3. memory_temptable_row_cost (default 0.2) 內存臨時表的行代價；

4. key_compare_cost (default 0.1) 鍵比較的代價，例如排序；

5. disk_temptable_create_cost (default 40.0) 內部 myisam 或 innodb 臨時表的創建代價；

6. disk_temptable_row_cost (default 1.0) 內部 myisam 或 innodb 臨時表的行代價；

可以看出創建臨時表的代價是很高的，尤其是內部的 myisam 或 innodb 臨時表。

engine_cost

1. io_block_read_cost (default 1.0) 從磁盤讀數據的 cost，對 innodb 來說，表示從磁盤讀一個 page 的 cost；

2. memory_block_read_cost (default 1.0）；

從內存讀數據的 cost，對 innodb 來說，表示從 buffer pool 讀一個 page 的 cost。

目前 io_block_read_cost 和 memory_block_read_cost 默認值均為 1，實際生產中建議酌情調大 memory_block_read_cost，特別是對普通硬盤的場景。

對表 tb_article 創建復合索引 index_title_author

ALTER TABLE tb_article ADD KEY index_title_author(`title`,`author_id`); select * from tb_article where author_id=20 and title= b

index_author_id 和 index_title_author 的 cost 相等，MySQL 會優先選擇葉子塊數量較少的索引。

對于 SQL 語句：select title, author_id from tb_article where author_id=20 and title= b

MySQL 會優先選擇走復合索引 index_title_author，原因是 index_title_author 是索引覆蓋掃描，不需要回表，性能較高。

到此，關于“MySQL 優化原理是什么”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注丸趣 TV 網站，丸趣 TV 小編會繼續努力為大家帶來更多實用的文章！