優化SELECT語句是優化工作的重點，因為它是資料庫輸出資料的基本操作。

SELECT語句的調優技術也适用于CREATE TABLE...AS SELECT, INSERT INTO...SELECT, DELETE語句中的SELECT和WHERE子句。

優化查詢的主要考慮因素是:

為了加快慢查詢，首先檢查是是否可以對WHERE子句中使用的列建立索引。索引對于不同表的聯合查詢尤其重要，可以使用EXPLAIN語句來确定索引的使用情況。
不應将查詢的某一部分獨立于主體去做調優，比如函數調用，這會導緻浪費更多時間。應該基于查詢語句的結構去判斷，該函數是被調用一次得到了結果集中的所有行，還是低效地查詢每行時都被調用一次。
在查詢中盡量減少全表掃描的次數，尤其是對于大表。
通過定期使用ANALYZE TABLE語句來更新最新的表統計資訊，以便于優化器建構高效執行計劃。
了解特定于每個表的存儲引擎的優化技術、索引技術和配置參數。InnoDB和MyISAM都有一套在查詢中實作和保持高性能的指導原則。
如果有些性能問題通過以上基本準則依然無法解決，可以通過查閱EXPLAIN計劃來調整索引、WHERE從句、join從句等。
調整MySQL用于緩存的記憶體大小和屬性。通過有效利用InnoDB緩沖池、MyISAM鍵緩存和MySQL查詢緩存，重複查詢的運作速度會更快，因為結果是從記憶體中檢索的。
即使使用高速緩存後使查詢更快，但仍然可以考慮是否能通過進一步優化查詢進而更高效的使用高速緩存，即可以在更小的緩存空間内完成相同的查詢，這樣應用程式便更具可伸縮性。可伸縮性即在性能不大幅下降的情況下，應用程式可以處理更多的并發使用者、更大的請求等等。
處理鎖定問題。在競争資源時，查詢某一資源時，其他會話可能也在同時通路該資源，此時的查詢速度會受到影響。

WHERE 子句優化

本部分的優化政策也适用于DELETE和UPDATE語句中的WHERE子句。

MySQL優化器會自動優化語句的結構形式，具體如下：

删除不必要的括号：
((a AND b) AND c OR (((a AND b) AND (c AND d)))) -> (a AND b AND c) OR (a AND b AND c AND d)
常量折疊：
(a<b AND b=c) AND a=5 -> b>5 AND b=c AND a=5
常量條件去除：
(b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6) -> b=5 OR b=6

在MySQL 8.0.14及更高版本中，這發生在準備階段而不是優化階段，這有助于簡化連接配接。

索引使用的常量表達式隻計算一次。
從MySQL 8.0.16開始，如果有數值類型的列與常量值的比較，則折疊或删除無效的以及超出範圍的值：
# CREATE TABLE t (c TINYINT UNSIGNED NOT NULL); SELECT * FROM t WHERE c ≪ 256; -≫ SELECT * FROM t WHERE 1;
執行早期預檢測無效的常量表達式。MySQL會快速檢測到那些不合乎邏輯并且不傳回任何行記錄的SELECT語句。
如果沒使用GROUP BY或聚合函數（COUNT()、MIN()等），則HAVING 會與 WHERE 合并。
對于連接配接中的每個表， mysql會構造一個盡可能簡單的WHERE條件來獲得該表的快速WHERE評估，并盡可能跳過一些無關行記錄。
在查詢中常量表先于任何其他表被首先讀取。常量表是以下任何一種：
空表或隻有一行記錄的表。
WHERE中使用了PRIMARY KEY或者UNIQUE索引的表，其中所有索引成員被定義為NOT NULL并且都與常量表達式進行了比較。
以下所有表都用作常量表：
SELECT * FROM t WHERE primary_key=1; SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
盡可能找到表的最佳連接配接組合。如果ORDER BY和GROUP BY子句中的所有列都來自同一個表，則在連接配接時首選該表。
如果ORDER BY子句和GROUP BY子句不相同（清單不同），或者如果ORDER BY或者GROUP BY 的清單中的列來自多張表，而不是連接配接隊列中第一個表，則會建立一個臨時表。
如果使用了SQL_SMALL_RESULT修飾符，則MySQL會使用in-memory臨時表。
查詢每個表索引并使用最佳索引，除非優化器認為使用表掃描更有效。老版本的mysql曾經根據最佳索引是否跨越超過30%的表來決定是否使用表掃描，但新版本的mysql中固定百分比不再是使用索引或表掃描的決定因素，現在的優化器更加複雜，它的估計基于多重因素，例如表大小、行數和 I/O 塊大小等。
在某些情況下，MySQL 甚至可以在不查閱資料檔案的情況下從索引中讀取行。如果索引中使用的所有列都是數字，則僅使用索引樹來解析查詢。
在每一行輸出之前，那些不比對 HAVING子句的被跳過。

一些非常快的查詢示例：

```
SELECT COUNT(*) FROM tbl_name;
    
    SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
    
    SELECT MAX(key_part2) FROM tbl_name
      WHERE key_part1=constant;
    
    SELECT ... FROM tbl_name
      ORDER BY key_part1,key_part2,... LIMIT 10;
    
    SELECT ... FROM tbl_name
      ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;
```

MySQL 僅使用索引樹解析以下查詢，假設索引列是數字：

```
SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
    
    SELECT COUNT(*) FROM tbl_name
      WHERE key_part1=val1 AND key_part2=val2;
    
    SELECT MAX(key_part2) FROM tbl_name GROUP BY key_part1;
```

以下查詢使用索引的排序檢索行：

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... ;

範圍優化

範圍通路方法使用單個索引來檢索包含在一個或多個索引值區間内的表行子集。它可用于單部分或多部分索引。

單部分索引的範圍通路方法

對于單部分索引，索引值區間可以友善地用WHERE子句中對應的條件來表示，表示為範圍條件而不是“區間”。

單部分索引的範圍條件定義如下：

對于BTREE索引和HASH索引，關鍵部分與常量值的比較時，範圍條件中使用了=, <=>, IN(), IS NULL 或 IS NOT NULL運算符。
此外，對于BTREE索引，關鍵部分與常量值的比較時，範圍條件中使用了>, <, >=, <=, BETWEEN...AND, !=或<> 運算符或者LIKE比較（不以通配符開頭的常量字元串）。
對于所有索引類型，使用OR或AND将多個範圍條件結合形成一個範圍條件。

上述描述中的 “常量值”是指以下之一：

來自查詢字元串的常量
來自同一連接配接的const或system表的列（explain中的const和system類型）
不相關子查詢的結果
完全由上述類型的子表達式組成的任何表達式

以下示例是WHERE子句中具有範圍條件的查詢：

SELECT * FROM t1
  WHERE key_col > 1
  AND key_col < 10;

SELECT * FROM t1
  WHERE key_col = 1
  OR key_col IN (15,18,20);

SELECT * FROM t1
  WHERE key_col LIKE 'ab%'
  OR key_col BETWEEN 'bar' AND 'foo';

随着常量的階段傳遞，優化器可能會把一些非常量值轉換為常量。

MySQL 會嘗試從 WHERE子句中為每個可能的索引提取範圍條件。在提取過程中，丢棄不能用于建構範圍條件的條件，合并産生重疊範圍的條件，并去除産生空範圍的條件。

考慮以下語句，其中 key1是索引列 nonkey沒有索引：

SELECT * FROM t1 WHERE
  (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
  (key1 < 'bar' AND nonkey = 4) OR
  (key1 < 'uux' AND key1 > 'z');

key1的提取過程如下：

從原始WHERE條款開始：

(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z')

删除nonkey = 4和key1 LIKE '%b'，因為它們不能用于範圍掃描。删除它們的正确方法是用 TRUE替換它們，這樣我們在進行範圍掃描時就不會錯過任何比對的行。用TRUE代替後如下：

(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(key1 < 'uux' AND key1 > 'z')

折疊始終為真或假的條件：

(key1 LIKE 'abcde%' OR TRUE)永遠是真的

(key1 < 'uux' AND key1 > 'z')總是假的
再次替換這些條件後如下：
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
删除不必要TRUE和FALSE常量：
(key1 < 'abc') OR (key1 < 'bar')
處理完重疊的範圍區域後，最後産生一個用于範圍掃描的最終條件：
(key1 < 'bar')

通常（如前面的示例所示），用于範圍掃描的條件沒有WHERE子句那麼嚴格。MySQL 執行額外的檢查以過濾掉滿足範圍條件但不滿足完整WHERE子句的行。

範圍條件提取算法可以處理任意深度的AND/OR嵌套結構，其輸出不依賴于條件在 WHERE子句中出現的順序。

多部分索引的範圍通路方法

多部分索引的範圍條件是單部分索引的範圍條件的擴充。多部分索引上的範圍條件将索引行限制在一個或多個鍵元組區間内。鍵元組區間是基于鍵元組集合，使用索引中的排序規則。

例如，定義為key1(key_part1, key_part2, key_part3)的多部分索引，鍵元組集的排序按索引順序排列如下：

key_part1  key_part2  key_part3
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

條件key_part1 = 1定義的區間如下：

(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)

區間覆寫了前面資料集中的第 4、5、6 三個元組，可以被範圍通路方法使用。相比之下，條件key_part3 = 'abc'由于沒有定義單個區間則不能被範圍通路方法使用。

以下描述更詳細地說明了範圍條件如何适用于多部分索引。

對于HASH索引，每個區間可以使用相同值。如下：key_part1 cmp const1 AND key_part2 cmp const2 AND ... AND key_partN cmp constN;

這裡, const1, const2, ... 是常量，cmp是 =, <=>, 或IS NULL比較運算符之一，條件涵蓋所有索引部分。（即有N個條件，對于N-part索引的每個部分都有一個條件。）例如，以下是擁有三部分的HASH索引的範圍條件：

key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'

對于BTREE索引，區間有可能用于以AND連接配接的條件，其中每個條件使用 =, <=>, IS NULL, >, <, >=, <=, !=, <>, BETWEEN或LIKE 'pattern'(其中LIKE 'pattern' 不以通配符%開頭）。隻要可以确定包含所有比對條件的行的單個鍵元組，就可以使用間隔（如果使用<> 或，!= 則使用兩個間隔）。

隻要比較運算符是、或，優化器就會嘗試使用其他關鍵部分來 =确定 <=>區間IS NULL。如果運算符是 >, <, >=, <=, !=, <>, BETWEEN, or LIKE，優化器使用它但不再考慮關鍵部分。對于以下表達式，優化器使用 =第一次比較。它還使用 >= 來自第二個比較，但不考慮其他關鍵部分，并且不使用第三個比較進行區間構造：

key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10

單個區間為：

('foo',10,-inf) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)

建立的間隔可能包含比初始條件更多的行。例如，前面的區間包含('foo', 11, 0)不滿足原始條件的值。

如果覆寫區間内包含的行集的條件與組合 OR，則它們形成一個條件，覆寫區間内并集内包含的行集。如果條件與結合 AND，則它們形成一個條件，該條件涵蓋包含在其區間交集内的一組行。例如，對于兩部分索引的這種情況：

(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)

間隔是：

(1,-inf) < (key_part1,key_part2) < (1,2)

(5,-inf) < (key_part1,key_part2)

在此示例中，第一行的間隔使用一個關鍵部分作為左邊界，兩個關鍵部分作為右邊界。第二行的間隔隻使用了一個關鍵部分。輸出中的key_len列EXPLAIN訓示使用的鍵字首的最大長度。

在某些情況下，key_len可能表明使用了關鍵部件，但這可能不是您所期望的。假設 key_part1和 key_part2可以是 NULL。然後該 key_len列顯示以下條件的兩個關鍵部分長度：

key_part1 >= 1 AND key_part2 < 2

但是，實際上，條件轉換為：

key_part1 >= 1 AND key_part2 IS NOT NULL

有關如何執行優化以組合或消除單部分索引的範圍條件間隔的描述，請參閱單部分索引的範圍通路方法。對多部分索引的範圍條件執行類似的步驟。

多值比較的等式範圍優化

考慮這些表達式，其中 col_name是索引列：

col_name IN(val1, ..., valN)

col_name = val1 OR ... OR col_name = valN

col_name如果等于多個值中的任何一個，則每個表達式都為真。這些比較是相等範圍比較（其中“範圍”是單個值）。優化器估計讀取符合條件的行以進行相等範圍比較的成本如下：

如果上存在唯一索引 col_name，則每個範圍的行估計值為 1，因為最多一行可以具有給定值。

否則，任何索引 col_name都是不唯一的，優化器可以通過深入索引或索引統計資訊來估計每個範圍的行數。

使用索引潛水，優化器在範圍的每一端進行潛水，并使用範圍内的行數作為估計值。例如，表達式 col_name IN (10, 20, 30)具有三個相等範圍，優化器對每個範圍進行兩次潛水以生成行估計。每對潛水都會産生具有給定值的行數的估計值。

索引潛水提供準确的行估計，但随着表達式中比較值數量的增加，優化器需要更長的時間來生成行估計。索引統計的使用不如索引潛水準确，但允許對大值清單進行更快的行估計。

系統 eq_range_index_dive_limit 變量使您能夠配置優化器從一種行估計政策切換到另一種的值的數量。要允許使用索引潛水來比較最多N 相等的範圍，請設定 eq_range_index_dive_limit 為N+ 1。要禁用統計資訊并始終使用索引潛水而不考慮 N，請設定 eq_range_index_dive_limit 為 0。

要更新表索引統計資訊以獲得最佳估計，請使用 ANALYZE TABLE.

在 MySQL 8.0 之前，沒有辦法跳過使用索引潛水來估計索引有用性，除非使用 eq_range_index_dive_limit 系統變量。在 MySQL 8.0 中，滿足所有這些條件的查詢可以跳過索引潛水：

該查詢是針對單個表的，而不是針對多個表的聯接。

存在單索引FORCE INDEX索引提示。這個想法是，如果強制使用索引，那麼執行深入索引的額外開銷将沒有任何好處。

該索引是非唯一的并且不是 FULLTEXT索引。

不存在子查詢。

不存在DISTINCT, GROUP BY, orORDER BY子句。

對于EXPLAIN FOR CONNECTION，如果跳過索引潛水，輸出會發生如下變化：

對于傳統輸出，rows和 filtered值為 NULL。

對于 JSON 輸出， rows_examined_per_scan并 rows_produced_per_join沒有出現， skip_index_dive_due_to_forceis true和 cost 計算不準确。

如果沒有FOR CONNECTION， EXPLAIN則跳過索引潛水時輸出不會改變。

在執行跳過索引潛水的查詢後， INFORMATION_SCHEMA.OPTIMIZER_TRACE 表中的相應行包含的 index_dives_for_range_access值 skipped_due_to_force_index。

跳過掃描範圍通路方法

考慮以下場景：

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));

INSERT INTO t1 VALUES

(1,1), (1,2), (1,3), (1,4), (1,5),

(2,1), (2,2), (2,3), (2,4), (2,5);

INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;

INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;

INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;

INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;

ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

要執行此查詢，MySQL 可以選擇索引掃描來擷取所有行（索引包括要選擇的所有列），然後應用子句中的f2 > 40 條件來生成最終結果集。WHERE

範圍掃描比全索引掃描更有效，但不能在這種情況下使用，因為在 f1第一個索引列上沒有條件。f1但是，從 MySQL 8.0.13 開始，優化器可以使用類似于 Loose Index Scan 的稱為 Skip Scan 的方法執行多個範圍掃描，針對的每個值進行一次（請參閱第 8.2.1.17 節，“GROUP BY 優化”）：

在第一個索引部分 f1（索引字首）的不同值之間跳過。

f2 > 40對剩餘索引部分的條件的每個不同字首值執行子範圍掃描。

對于前面顯示的資料集，算法操作如下：

f1 = 1擷取第一個關鍵部分 ( ) 的第一個不同值。

根據第一個和第二個關鍵部分 ( f1 = 1 AND f2 > 40) 建構範圍。

執行範圍掃描。

擷取第一個關鍵部分 ( f1 = 2) 的下一個不同值。

根據第一個和第二個關鍵部分 ( f1 = 2 AND f2 > 40) 建構範圍。

執行範圍掃描。

使用此政策會減少通路的行數，因為 MySQL 會跳過不符合每個構造範圍的行。此跳過掃描通路方法适用于以下條件：

表 T 至少有一個複合索引，其關鍵部分為 ([A_1, ..., A_ k,] B_1, ..., B_ m, C [, D_1, ..., D_ n])。關鍵部分 A 和 D 可以為空，但 B 和 C 必須為非空。

該查詢僅引用一張表。

該查詢不使用GROUP BYor DISTINCT。

該查詢僅引用索引中的列。

A_1, ..., A_ 上的k謂詞必須是相等謂詞并且它們必須是常量。這包括 IN()操作員。

查詢必須是連詞查詢；也就是一個 AND條件OR ： (cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR ...) AND ...

C 上必須有一個範圍條件。

D 列的條件是允許的。D 上的條件必須與 C 上的範圍條件相結合。

Skip Scan 的使用在EXPLAIN 輸出中訓示如下：

Using index for skip scan列 Extra中表示使用松散索引Skip Scan通路方法。

如果索引可用于跳過掃描，則該索引應在possible_keys 列中可見。

跳過掃描的使用在優化器跟蹤輸出中由 "skip scan"以下形式的元素訓示：

"skip_scan_range": {

"type": "skip_scan",

"index": index_used_for_skip_scan,

"key_parts_used_for_access": [key_parts_used_for_access],

"range": [range]

}

您可能還會看到一個 "best_skip_scan_summary"元素。如果選擇跳過掃描作為最佳範圍通路變量， "chosen_range_access_summary"則寫入 a。如果選擇跳過掃描作為整體最佳通路方法， "best_access_path"則存在一個元素。

跳過掃描的使用取決于系統變量的skip_scan标志值。optimizer_switch請參閱第 8.9.2 節，“可切換的優化”。預設情況下，此标志為on. 要禁用它，請設定skip_scan為 off。

除了使用 optimizer_switch系統變量來控制優化器在會話範圍内使用 Skip Scan 之外，MySQL 還支援優化器提示以在每個語句的基礎上影響優化器。請參閱第 8.9.3 節，“優化器提示”。

行構造函數表達式的範圍優化

優化器能夠将範圍掃描通路方法應用于這種形式的查詢：

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

以前，要使用範圍掃描，必須将查詢編寫為：

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )

OR ( col_1 = 'c' AND col_2 = 'd' );

為了讓優化器使用範圍掃描，查詢必須滿足以下條件：

隻IN()使用謂詞，不使用NOT IN().

在謂詞的左側 IN()，行構造函數僅包含列引用。

在謂詞的右側 IN()，行構造函數僅包含運作時常量，它們是在執行期間綁定到常量的文字或本地列引用。

在謂詞的右側 IN()，有不止一個行構造函數。

有關優化器和行構造函數的更多資訊，請參閱第 8.2.1.22 節，“行構造函數表達式優化”

限制用于範圍優化的記憶體使用

要控制範圍優化器可用的記憶體，請使用 range_optimizer_max_mem_size 系統變量：

值 0 表示“沒有限制”。”

使用大于 0 的值，優化器在考慮範圍通路方法時跟蹤消耗的記憶體。如果即将超過指定的限制，則放棄範圍通路方法，并考慮其他方法，包括全表掃描。這可能不太理想。如果發生這種情況，則會出現以下警告（ N目前 range_optimizer_max_mem_size 值在哪裡）：

Warning 3170 Memory capacity of N bytes for

'range_optimizer_max_mem_size' exceeded. Range

optimization was not done for this query.

對于UPDATE和 DELETE語句，如果優化器回退到全表掃描并且 sql_safe_updates啟用了系統變量，則會發生錯誤而不是警告，因為實際上沒有使用任何鍵來确定要修改哪些行。有關詳細資訊，請參閱使用安全更新模式 (--safe-updates)。

對于超出可用範圍優化記憶體并且優化器回退到不太優化的計劃的單個查詢，增加該 range_optimizer_max_mem_size 值可能會提高性能。

要估計處理範圍表達式所需的記憶體量，請使用以下準則：

對于像下面這樣的簡單查詢，其中有一個範圍通路方法的候選鍵，每個謂詞組合OR 使用大約 230 個位元組：

SELECT COUNT(*) FROM t

WHERE a=1 OR a=2 OR a=3 OR .. . a=N;

類似地，對于如下查詢，每個謂詞組合AND 使用大約 125 個位元組：

SELECT COUNT(*) FROM t

WHERE a=1 AND b=1 AND c=1 ... N;

對于帶有IN() 謂詞的查詢：

SELECT COUNT(*) FROM t

WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);

清單中的每個文字值都 IN()算作一個與 . 組合的謂詞OR。如果有兩個IN() 清單，則結合的謂詞 OR數量是每個清單中文字值數量的乘積。OR是以，在前一種情況下結合的謂詞數量為M× N。

MySQL的SELECT語句優化詳解

WHERE 子句優化

範圍優化

單部分索引的範圍通路方法

多部分索引的範圍通路方法

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案