MySQL優化原理是什么

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

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優化原理是什么”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！