三高Mysql - Mysql索引和查詢優化（偏理論部分）

lazytimes發表於2022-04-06
MySql索引優化

引言

​ 內容為慕課網的"高併發 高效能 高可用 MySQL 實戰"視訊的學習筆記內容和個人整理擴充套件之後的筆記，本節內容講述的索引優化的內容，另外本部分內容涉及很多優化的內容，所以學習的時候建議翻開《高效能Mysql》第六章進行回顧和了解，對於Mysql資料的開發同學來說大致瞭解內部工作機制是有必要的。

​ 由於文章內容過長，所以這裡拆分為兩部分，上下部分的內容均使用sakila-db，也就是mysql的官方案例。第一部分講述優化的理論和Mysql過去的優化器設計的缺陷，同時會介紹更高的版本中如何修復完善這些問題的（但是從個人看來新版本那些優化根本算不上優化，甚至有的優化還是照抄的Mysql原作者的實現的，發展了這麼多年才這麼一點成績還是要歸功於Oracle這種極致商業化公司的功勞）。

如果內容比較難，可以跟隨《Mysql是怎麼樣執行》個人讀書筆記專欄補補課，個人也在學習和同步更新中。

地址如下：https://juejin.cn/column/7024...。

【知識點】

  • Mysql索引內容的介紹
  • 索引的使用策略和使用規則
  • 查詢優化排查，簡單瞭解Mysql各個元件的職責

前置準備

sakila-db

​ sakila-db是什麼？國外很火的一個概念，指的是國外的電影租賃市場使用租賃的方式進行電影的觀看十分受外國的喜歡。這裡介紹是因為後續的內容都用到了這個案例。所以我們需要提前把相關的環境準備好，從如下地址進行下載：

​ 下載地址：https://dev.mysql.com/doc/ind...

《高效能Mysql》的SQL 案例也是使用官方的example

work-bench

​ work-bench是官方開發的資料庫關係圖的視覺化工具，使用官方案例的具體關係圖展示效果如下，通過這些圖可以看到Sakila-db之間的大致關係：

work-bench也是開源免費軟體，下載地址如下：

https://dev.mysql.com/downloa...

sakila-db示意圖

​ 安裝workbench和下載sakila-db的方式這裡不做記錄，在執行的時候需要注意先建立一個資料庫執行sheme檔案，然後執行data的sql檔案，最終在navicat中檢視資料：

資料庫關係圖

正文部分

索引型別

​ 首先是索引的特點以及作用：

  1. 索引的目的是為了提升資料的效率。
  2. 對於ORM框架來說索引的使用至關重要，但是ORM的優化往往難以顧及所有業務情況，後續被逐漸廢棄。
  3. 不同的索引型別適用於不同的場景。
  4. 索引關鍵在於減少資料需要掃描的量，同時避免伺服器內部對內容排序和臨時表（因為臨時表會索引失效），隨機IO轉順序IO等特點

​

​ 下面介紹Mysql相關的索引型別：

  • 雜湊索引：雜湊索引適合全值匹配和精確查詢，查詢的速度非常快 在MySQL中只有memory儲存引擎顯式支援此索引，memory還支援非唯一雜湊索引的，是雜湊索引設計裡面比較特殊的。
  • 空間索引：空間索引是myisam表支援，主要用作地理資料儲存，這裡包含一個叫做GIS的玩意，但是GIS在Postgre中使用比MySQL要出色很多，所以mysql中空間索引是無關緊要的東西。
  • 全文索引：全文索引也是myisam獨有支援的一種索引型別。適合使用的場景為全值匹配的場景和關鍵字查詢，對於大文字的關鍵字匹配可以有效處理。
  • 聚簇索引：聚簇索引是innodb儲存引擎的預設儲存引擎。
  • 字首壓縮索引：注意這個索引針對的是myisam儲存引擎，目的是為了讓索引放入記憶體中排序，，字首壓縮的方法是首先儲存索引塊的第一個值，然後在儲存第二個值，儲存第二個值類似（長度,索引值）的形式存放字首索引。

其他索引型別注意事項：

​ Archive 在5.1之後才支援單列自增索引。

​ MyISAM 支援壓縮之後的字首索引，使得資料結構佔用更小。

雜湊索引

​ 在Mysql中唯一顯式實現雜湊索引的儲存引擎為Memory，Memory是存在非唯一雜湊索引，同時BTree也支援“自適應雜湊索引的方式“相容雜湊索引。

下面是雜湊索引特點：

  • 鍵儲存的是索引雜湊值，注意不是索引值本身，而值儲存的是指向行的指標
  • 注意此雜湊索引無法避免行掃描，但是在記憶體中指標非常快通常可以忽略不計
  • 注意只有雜湊值按照順序排序，但是行指標不是按照順序排序
  • 雜湊不支援：部分索引覆蓋，只支援全索引覆蓋，因為使用全部的索引列計算雜湊值
  • 雜湊索引支援等值匹配操作不支援範圍查詢，比如等於，in子查詢，不全等。
  • 如果出現雜湊衝突，雜湊索引將退化為連結串列順序查詢，同時維護索引的開銷也會變大

聚簇索引

​ 聚簇表示資料行的值緊湊儲存在一起。而innodb聚簇的值就是主鍵的值，所以通常使用都是主鍵上的索引，針對主鍵索引的選擇十分重要。由於本部分著重索引優化，聚簇索引這裡就不再講述了。

​ MyISam和Innodb的主鍵索引區別是MyISam的索引很簡單，因為資料行只包含行號，所以索引直接儲存列值和行號，資料單獨存放另一處，類似於一個唯一非空索引，索引和資料不在一處，MyISam的索引設計比InnoDB簡單很多，這和MyIsam不需要支援事務也有直接關係，而innodb將索引和行資料放入一個資料結構，將列進行緊湊的儲存。

​

聚簇索引有下面優點

  • 緊湊儲存資料行，所以可以只掃描少量磁碟就可以獲取到資料
  • 資料訪問的速度非常快，索引和資料放在同一顆BTree中，比非聚簇索引查詢快很多
  • 覆蓋索引可以直接減少回表

當然索引也有下面的缺點：

  • 對於非IO密集型應用，聚簇索引的優化無意義。
  • 插入速度依賴於插入順序，但是如果不是自增插入則需要optimize table重新組織表。
  • 更新代價非常高，因為BTree要保證順序排序需要挪動資料頁位置和指標。
  • 主鍵資料插入過滿資料頁存在頁分裂問題，行溢位會導致儲存壓力加大。
  • 聚簇索引導致全表掃描變慢，頁分裂導致資料問題等。
  • 二級索引需要回表查詢聚簇索引才能查詢資料。
  • 二級索引由於需要儲存主鍵開銷會更大，至少在InnoDb中維護一個二級索引的開銷是挺大的。

壓縮索引

​ 壓縮索引的特點是使用更少的空間存放盡可能多的內容，但是這樣的處理方式僅僅適用於IO密集型的系統，壓縮字首儲存形式最大的缺陷是無法使用二分法進行查詢，同時如果使用的倒序索引的方式比如order by desc 的方式可能會因為壓縮索引的問題存在卡頓的情況。

Bree索引的特點

  • 葉子結點存在邏輯頁和索引頁兩種，通常非最底層葉子結點都是索引頁，最底層索引頁由連結串列串聯。
  • Btree索引會根據建表順序對於索引值進行排序，索引建表時候建議將經常查詢的欄位往前挪。
  • Btree索引適合的查詢型別：字首查詢，範圍查詢，鍵值查詢（雜湊索引）。

自適應雜湊索引

​ 當innodb發現某些索引列和值使用頻繁的時候，BTree會在此基礎上自動建立雜湊索引輔助優化，但是這個行為是不受外部控制的，完全是內部的優化行為，如果不需要可以考慮關閉。

Btree查詢型別

​ 針對Innodb的Btree索引，有下面幾種常見的查詢方式：

  • 全值匹配：等值匹配的方式，全值匹配適合雜湊索引進行查詢
  • 最左匹配原則：二級索引的查詢條件放在where最左邊
  • 字首匹配：只使用索引的第一列，並且like ‘xxx%’
  • 範圍匹配：範圍匹配索引列到另一列之間的值
  • 範圍查詢和精確匹配結合，一個全值匹配，一個範圍匹配
  • 覆蓋索引查詢：覆蓋索引也是一種查詢方式，

索引策略

​ 下面是關於建立索引的一些常見策略：

  1. 第一件事情需要考慮的是預測那些資料為熱點資料或者熱點列，按照《高效能Mysql》介紹，對於熱點列來說有時候要違背最大選擇性的原則，通過建立時常搜尋的索引作為最左字首的預設的設定。同時優化查詢需要考慮所有的列，如果一個查詢的優化會破壞另一個查詢，那麼就需要優化索引的結構。
  2. 第二件事情是考慮where的條件組合，通過組合多種where條件，需要考慮的是儘可能讓查詢重用索引而不是大規模的建立新索引。
  3. 避免多個範圍進行掃描，一方面是範圍查詢會導致，但是對於多個等值的條件查詢，最好的辦法是儘量控制搜尋範圍。

​

​ 對於索引的策略我們還需要了解下面的細節

  • 單行訪問很慢，特別是隨機訪問要比順序訪問要慢更多，一次性載入很多資料頁會造成效能的浪費。
  • 順序訪問範圍資料很快，順序IO的速度不需要多磁軌查詢，比隨機的訪問IO塊很多，順序訪問也可以使用group by進行聚合計算。
  • 索引覆蓋速度很快，如果查詢欄位包含了索引列，就不需要回表。

索引碎片優化

​ Innodb的資料結構和特性會導致索引存在資料碎片，對於任何儲存結構來說順序的儲存結構是最合適的，並且索引順序訪問要比隨機訪問快更多，資料儲存的碎片比索引本身複雜很多，索引碎片通常包含下面的情況：

  • 行碎片：資料行的資料被儲存在多個資料頁當中，碎片可能會導致效能的下降。
  • 行間碎片：邏輯順序上的頁，行在磁碟上不順序儲存，行間資料碎片會導致全表掃描。
  • 剩餘空間碎片：資料頁的間隙有大量的垃圾資料導致的浪費。

​ 對於上面幾點，對於myisam 都有可能出現，但是innodb的行碎片不會出現，內部會移動碎片重寫到一個片段。

​ 索引碎片的處理方式：在Mysql中可以通過optimize table 匯入和匯出的方式重新整理資料，防止資料碎片問題。

​

索引規則

  • 索引必須按照索引順序從左到右匹配
  • 如果在查詢中間出現範圍，則範圍查詢之後的索引失效
  • 不能跳過索引列的方式查詢（和B+tree索引資料結構設計有關係）

​ 接著是索引順序問題，由於BTree的結構特性，索引都是按照建立順序進行查詢的，通常不包含排序和分組的情況下，把選擇性最高的索引放在最左列是一個普遍正確策略。

​ 如何檢視索引基數：show index from sakila.actor，還有一種方式是通過information_schema.statistics 表查詢這些資訊，可以編寫為一個查詢給出選擇性較低的索引。

​ 當innodb開啟某些表的時候會觸發索引資訊的統計，比如開啟information_schema表或者使用show table status和show index的時候，所以如果在系統要執行壓力較大的業務時期儘量避開這些操作。

冗餘重複索引

​ Mysql允許同一個列上建立多種型別的索引，有時候會因為建表的特性問題給欄位重複建索引造成不必要的效能浪費。冗餘索引和重複索引有什麼區別？

​ 冗餘索引：是符合最左匹配法則的情況下重複對相同列建立索引。

​ 重複索引：是對於不最做的方式建立的索引就有可能是重複建立索引。

​ 比如聯合索引：(A,B) 如果在建立 （A）或者（A，B）都是重複索引，但是建立（B）就不是重複索引而是冗餘索引。另外某些十分特殊的情況下可能用到冗餘索引，但是這會極大的增加索引維護的開銷，最為直觀的感受是插入、更新、刪除的開銷變得很大。

多列索引

​ 首先多列索引不是意味著where欄位出現的地方就需要加入，其次多列索引雖然在現在主流使用版本中（5.1版本之後）實現了索引內部合併，也就是使用and or或者and和or合併的方式相交使用索引，但是他存在下面幾個缺點

  • 內部優化器的合併和計算十分耗費CPU的效能，索引反而增加資料查詢複雜度，效率也不好
  • 往往會存在優化過度的情況，導致執行效果還不如全表掃描
  • 出現多列索引合併通常意味著建立索引的方式不對，存在反向優化的嫌疑

檔案排序

​ 檔案排序遵循Innodb的Btree索引的最基本原則：最左字首原則，如果索引列的順序和order by排序一致，並且查詢列都和排序列都一樣才會用索引替代排序，對於多表查詢則排序欄位全為第一個表才能進行索引排序。但是有一個特例那就是排序欄位的前導列為常量的時候依然可以使用索引排序。

​ 案例：rental 表的聯合索引列進行排序

Backward index scan 是 MySQL-8.0.x 針對上面場景的一個專用優化項，它可以從索引的後面往前面讀，效能上比加索引提示要好的多
EXPLAIN select rental_id,staff_id from rental where rental_date = '2005-05-25' order by inventory_id desc, customer_id asc;
-- 1 SIMPLE rental ref rental_date rental_date 5 const 1 100.00 Using filesort

EXPLAIN select rental_id,staff_id from rental where rental_date = '2005-05-25' order by inventory_id desc;
-- Backward-index-scan
-- Backward index scan 是 MySQL-8.0.x 針對上面場景的一個專用優化項，它可以從索引的後面往前面讀，效能上比加索引提示要好的多
-- 1 SIMPLE rental ref rental_date rental_date 5 const 1 100.00 Backward index scan

EXPLAIN select rental_id,staff_id from rental where rental_date = '2005-05-25' order by inventory_id, staff_id;
-- 1 SIMPLE rental ref rental_date rental_date 5 const 1 100.00 Using filesort
-- 無法使用索引
EXPLAIN select rental_id,staff_id from rental where rental_date > '2005-05-25' order by inventory_id, customer_id;
-- 1 SIMPLE rental ALL rental_date 16008 50.00 Using where; Using filesort

EXPLAIN select rental_id,staff_id from rental where rental_date = '2005-05-25' and inventory_id in (1,2) order by customer_id;
-- 1 SIMPLE rental range rental_date,idx_fk_inventory_id rental_date 8 2 100.00 Using index condition; Using filesort

explain select actor_id, title from film_actor inner join film using(film_id) order by actor_id;
-- 1 SIMPLE film index PRIMARY idx_title 514 1000 100.00 Using index; Using temporary; Using filesort
-- 1 SIMPLE film_actor ref idx_fk_film_id idx_fk_film_id 2 sakila.film.film_id 5 100.00 Using index

查詢優化排查

​ 查詢優化的排查意味著我們需要先了解Mysql的各個元件在各步驟中做了哪些事情，下面這張圖來自於《高效能Mysql》，對於一次客戶端的請求，大致分為下面的流程：

  1. 客戶端傳送請求

  2. 伺服器查詢執行快取

    • 不重要，8.0之後已經刪除

  3. 服務端進行SQL解析和預處理

    • 許可權檢查
    • 詞法解析
    • 語法樹

  4. 優化器生成執行計劃

    • 優化器存在的問題？
    • 優化器如何工作？
  5. 根據執行計劃呼叫儲存引擎的APi介面執行查詢
  6. 結果返回客戶端

​ 對於關係型的資料庫來說，核心部分在於查詢優化器和執行計劃的部分，因為不管我們如何編寫SQL語句，如果沒有強大的優化器和執行計劃那麼一切都是空談，所以本部分的重點也會圍繞優化器進行講解，在此之前我們先看看其他元件的工作：

​ 首先查詢快取不需要過多解釋，他的作用是當使用者重複執行一個查詢的時候會內部對於結果進行快取，但是一旦使用者修改查詢條件，快取就失效了，在早期的網際網路環境中這種處理很不錯，可以減少磁碟IO和CPU的壓力，但是到了現在的環境下顯然不適合，所以8.0刪除也是可以理解的。

​ 接著是解析器，解析器這部分主要工作是通過解析語法形成解析樹對於語句進行預處理，預處理可以類看作我們編譯器把我們寫的程式設計語句“翻譯”為機器程式碼的過程，讓下一步的優化器可以認識這顆解析樹去進行解析，

​ 如果想要了解SQL解析優化的底層過程，可以從這篇文章入手：

​ SQL解析在美團的應用 - 美團技術團隊 (meituan.com)

​ 在上面的部落格中提到了一個DBA必須掌握的工具pt-query-digest，分析慢查詢日誌，下面這個文章中提供了一個實際的案例來排查和優化，案例較為簡單適合剛接觸這個工具的人進行學習和思考，這裡一併列出來了。

​ 使用 pt-query-digest 分析 RDS MySQL 慢查詢日誌 | 亞馬遜AWS官方部落格 (amazon.com)

SQL解析部分筆記：

詞法分析：核心程式碼在sql/sql_lex.c檔案中的，MySQLLex→lex_one_Token

MySQL語法分析樹生成過程：全部的原始碼在sql/sql_yacc.yy中，在MySQL5.6中有17K行左右程式碼

最核心的結構是SELECT_LEX，其定義在sql/sql_lex.h中

​ 下面我們來深入看看優化器的部分工作內容以及Mysql優化歷史：

​ 由於講述優化器的內容較少，這裡直接總結《高效能Mysql》的內容，優化器也不需要研究和記憶，因為隨著版本的迭代不斷更新優化器會不斷調整，一切要以真實實驗為準：

1. 子查詢關聯：

​ 下面的查詢在通常情況下我們會認為先進行子查詢，然後通過for迴圈掃描film表進行匹配操作，然後從explain的結果中可以看到這裡的查詢線進行了全表掃描，然後通過關聯索引進行第二層的for迴圈查詢，這樣的寫法類似exists。

explain select * from sakila.film where film_id in (select film_id from film_actor where actor_id)
-- 1    SIMPLE    film        ALL    PRIMARY                1000    100.00    
-- 1    SIMPLE    film_actor        ref    idx_fk_film_id    idx_fk_film_id    2    sakila.film.film_id    5    90.00    Using where; Using index; FirstMatch(film)

​ 優化這個子查詢的方式使用關聯查詢替代子查詢，但是需要注意這裡存在where條件才會走索引，否則和上面的結果沒有區別：

explain select film.* from sakila.film film  join film_actor actor using (film_id) where actor.actor_id = 1

​ 另一種是使用exists的方式進行關聯匹配。

explain select * from film where exists (select * from film_actor actor where actor.film_id =  film.film_id and actor.actor_id = 1);

​ 可以看到哪怕到了5.8的版本，Mysql的子查詢優化既然沒有特別大的改進，所以通常情況下如果不確定in查詢的內容大小，建議用exists或者join進行查詢，另外也不要相信什麼in查詢就一定慢點說法，在不同的mysql優化器版本中可能會有不同的效果。

2. union查詢

​ 雖然多數情況下我們會用union替換or，但是更多的情況是應該儘量避免使用union，因為union查詢會產生臨時表和中間結果集容易導致優化索引失效，需要注意的是 union會觸發內部的排序動作，也就是說union會等價於order by的排序，如果資料不是強烈要求不能重複，那麼更建議使用union all，對於優化器來說這樣工作更加簡單，直接把兩個結果集湊在一起就行，也不會進行排序。

​ union查詢能不用就不用，除非是用來代替or查詢的時候酌情考慮是否有必要使用。

​ 最後注意union的產生排序不受控制的，可能會出現意料之外的結果。

3. 並行查詢優化

​ 並行查詢優化在8.0中終於有了實現，可以根據引數：innodb_parallel_read_threads =並行數來驗證。

​ 由於個人是M1的CPU，讀者可以根據自己的實際情況進行實驗。

set local innodb_parallel_read_threads = 1;
select count(*) from payment;
set local innodb_parallel_read_threads = 6;
select count(*) from payment;

從執行結果可以看到僅僅是1萬多條資料的count(*)查詢就有明顯直觀的差距：

4. 雜湊關聯

​ 官方文件的介紹地址：Mysql官方文件雜湊關聯

​ 在MySQL 8.0.18中Mysql終於增加了雜湊關聯的功能。在此之前的版本中，Mysql的優化器通常只支援for迴圈巢狀關聯，曲線救國的方法是建立一個雜湊索引或者使用Memory儲存引擎，而新版本提供的雜湊關聯則提供了一種新的對關聯方式，雜湊關聯的方式如下：

​ 把一張小表資料儲存到記憶體中的雜湊表裡，通過匹配大表中的資料計算雜湊值，並把符合條件的資料從記憶體中返回客戶端。

​ 對於Mysql的雜湊關聯，我們直接使用官方的例子：

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);

EXPLAIN
     SELECT * FROM t1
         JOIN t2 ON t1.c1=t2.c1;
-- Using where; Using join buffer (hash join)

​ 除開等值查詢以外，Mysql的8.0.20之後提供了更多的支援，比如在 MySQL 8.0.20 及更高版本中，連線不再需要包含至少一個等連線條件才能使用雜湊連線，除此之外它還包括下面的內容：

-- 8.0.20 支援範圍查詢雜湊關聯
EXPLAIN  SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1;
-- 8.0.20 支援 in關聯
EXPLAIN  SELECT * FROM t1 
        WHERE t1.c1 IN (SELECT t2.c2 FROM t2);
-- 8.0.20 支援 not exists 關聯
EXPLAIN  SELECT * FROM t2 
         WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.c1 = t2.c2);
-- 8.0.20 支援 左右外部連線
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1;

注意8.0.18版本的雜湊關聯僅僅支援join查詢，對於可能會帶來笛卡爾積的左連和右連線查詢是不支援的。但是在後續的版本中提供了更多查詢條件支援

另外，8.0.20版本之前想要檢視是否使用hash join，需要結合 format=tree 選項。

雜湊關聯

​ 最終Mysql在8.0.18版本中曾經提供過開關雜湊索引和設定優化器提示optimizer_switch等引數來判定是否給予hash join的提示，真是閒的蛋疼（官方自己也這麼認為）所以在8.0.19立馬就把這些引數給廢棄。

​ 注意雜湊連線不是沒有限制的，瞭解雜湊關聯的流程就會發現如果雜湊表過大，會導致整個雜湊關聯過程在磁碟中完成其速度可想而知，所以官方提供了下面的建議：

  • 增加join_buffer_size，也就是增加雜湊關聯的雜湊表快取大小，防止進入磁碟關聯。
  • 增加open_files_limit數量，這個引數什麼意思這裡就不介紹了，意義是增加這個引數可以增加關聯的時候關聯次數。

吐槽：說句心裡話自Mysql被Oracle收購之後，越來越商業化的同時進步也越來越小，in查詢優化這一點其實在很多開源庫甚至Mysql的原作者給解決了，但是Mysql到了8.0依然和多年前的《高效能Mysql》結果沒有差別。哎。。。。。

Mysql資料庫的發展也告訴我們時刻保持開放的心態，吸取教訓正視不足和改進，才不會被時代逐漸淘汰。

5. 鬆散索引

​ 鬆散索引在Mysql5.6之後已經支援，鬆散索引簡單理解就是在進行多列索引掃描的時候，即使次索引不是有序的，但是跳過索引是有序的，也可以走索引來快速匹配資料。

 鬆散索引的優化細節放到了下半部分的文章，這裡簡單講述一下大致的工作原理。

  1. 查詢同時更新資料

​ 在Postgresql中，支援下面的語法：

update tbl_info
set name = tmp.name
from 
(select name from tbl_user where name ='xxx')
tmp
[where ....]

-- 比如下面的寫法：
UPDATE `sakila`.`actor` SET `first_name` = 'PENELOPE'
from 
(select address,address_id from address where address_id = 1) tmp
 WHERE `actor_id` = 1 and actor.actor_id = tmp.address_id;

​ 但是很可惜這種語法在Mysql是沒有辦法實現也是不支援的，哪怕到了8.0.26依然沒有支援，這和Mysql的優化器設計有著本質的關係。

  1. 優化器提示設定

優化器提示沒有多少意義，這裡直接略過了。

  1. 最大值和最小值優化

​ 從實際的情況來看Mysql最大值和最小值這兩個函式使用並不是很多所以不再進行介紹了，另外無論什麼樣的資料庫都不是很建議頻繁使用函式，而是改用業務+簡單SQL實現高效索引優化。

其他慢查詢優化

​ 對於慢查詢的優化我們需要清楚優化是分為幾種類別的，在Mysql中優化策略分為動態優化和靜態優化：靜態優化主要為優化更好的寫法，比如常數的排序和一些固定的優化策略等，這些動作通常在一次優化過程中就可以完成。而動態優化策略要複雜很多，可能會在執行的過程中優化，有可能在執行過後重新評估執行計劃。

​ 靜態優化是受優化器影響的，不同版本有不同情況，所以這裡講述動態優化的情況，而動態優化主要包含下面的內容：

  • 關聯表順序，有時候關聯表順序和查詢順序不一定相同。
  • 重寫外連線為內連線：如果一個外連線關聯是沒有必要的就優化掉外連線關聯。
  • 等價替換，比如 a>5 and a= 5被優化為a >= 5 ，類似數學的邏輯公式簡化
  • 優化count()、max()、min()等函式：有時候找最大和最小值只需要找最大和最小的索引記錄，這時候由於不需要遍歷，可以認為直接為雜湊的獲取記錄的方式，所以在查詢分析的 extra 裡面進行體現（Select tables optimized away），比如：explain select max(actor_id) from actor;
  • 預估和轉化常數：以連線查詢為例，如果在查詢條件中可以實現預估關聯的記錄條數，那麼對於一個關聯查詢來說就有可能被優化器作為常數進行優化，因為事先取出記錄的條數被優化器知曉。所以優化起來十分簡單。
  • 子查詢優化：子查詢雖然有可能被索引優化但是需要儘量避免使用。
  • 覆蓋索引掃描：讓索引和查詢列一致，是非常高效的優化和執行方式
  • 提前終止查詢：提前終止查詢指的是當遇到一些查詢條件會讓查詢提前完成，優化器會提前判斷加快資料的匹配和搜尋速度
  • 等值傳遞，如果範圍查詢可以根據關聯表查詢優化，那麼無需 顯式的提示則可以直接搜尋資料。

​

參考資料：

這裡彙總了文章中出現的一些參考資料：

寫在最後

​ 上半部分以理論為主，下半部分將會著重實戰內容進行介紹。

相關文章