目錄

1 MySQL如何使用索引

2 主鍵優化

3 空間索引優化

4 外鍵優化

5 列索引

6 多列索引

7 驗證索引使用情況

8 InnoDB和MyISAM索引統計集合

9 B樹索引與哈希索引的比較

9.1 B-樹索引特征

9.2 哈希索引特征

10 索引擴展的使用

11 優化器使用生成的列索引

12 不可見索引

13 降序索引

14 TIMESTAMP列的索引查詢

---

1 MySQL如何使用索引

索引用于快速查找具有特定列值的行。如果沒有索引，MySQL必須從第一行開始，然后讀取整個表以找到相關的行。

表越大，耗時就越多。

如果表中有相關列的索引，MySQL可以快速確定要在數據文件中查找的位置，而無需查看所有數據。這比按順序讀取每一行要快得多。

大多數MySQL索引（PRIMARY KEY、UNIQUE、INDEX和FULLTEXT）都存儲在B+樹中。

例外：

空間數據類型的索引使用R樹；

MEMORY表還支持Hash索引；

InnoDB對FULLTEXT索引使用反向鏈表。

通常，索引的使用方式如下所述。下文第9節“B樹和哈希索引的比較”中描述了哈希索引（如MEMORY表中所用）的特定特性。

MySQL使用索引進行以下操作：

快速查找與WHERE子句匹配的行。

將行排除在考慮范圍之外。如果在多個索引之間進行選擇，MySQL通常使用查找行數最少的索引（最具選擇性的索引）。

如果表具有多列索引，則優化器可以使用索引最左邊的任何前綴來查找行。例如，如果您在（col1，col2，col3）上有一個三列索引，則您在（coll1）、（col1、col2）和（col1和col2，coll3）上具有索引搜索功能。有關更多信息，請參閱“多列索引”。

在執行聯接查詢時從其他表中檢索行。

如果列被聲明為相同的類型和大小，MySQL可以更有效地使用列上的索引。

在這種情況下，如果VARCHAR和CHAR被聲明為相同的大小，則它們被認為是相同的。例如，VARCHAR（10）和CHAR（10）的大小相同，但VARCHAR（10）與CHAR（15）的大小不同。

對于非二進制字符串列之間的比較，兩列應使用相同的字符集。例

如，將utf8mb4列與latin1列進行比較會排除索引的使用。

如果在不進行轉換的情況下無法直接比較值，則比較不同列（例如，將字符串列與時間列或數值列進行比較）可能會阻止使用索引。

對于給定的值，如數字列中的1，它可能與字符串列中的任意數量的值（如“1”、“1”，“00001”或“01.e1”）進行比較。

這就排除了對字符串列使用任何索引的可能性。

查找特定索引列key_col的MIN（）或MAX（）值。

這是由一個預處理器優化的，該預處理器檢查您是否在索引中key_col之前出現的所有鍵部分上使用WHERE key_part_N=常量。在這種情況下，MySQL為每個MIN（）或MAX（）表達式執行單個關鍵字查找，并將其替換為常量。如果所有表達式都替換為常量，則查詢將立即返回。例如

SELECT MIN(key_part2),MAX(key_part2)FROM tbl_name WHERE key_part1=10;

如果排序或分組是在可用索引的最左邊前綴上完成的，則對表進行排序或分組（例如ORDER BY key_part1、key_part2）。

如果所有字段部分后面都跟著DESC，則按相反順序讀取該字段部分。

（或者，如果索引是降序索引，則按正向順序讀取鍵。）

請參閱“排序優化”、“分組優化“，“降序索引”。后續更新

在某些情況下，可以優化查詢以檢索值，而無需查詢數據行。（為查詢提供所有必要結果的索引稱為覆蓋索引。）如果查詢僅從表中使用某些索引中包含的列，則可以從索引樹中檢索所選值以提高速度：

SELECT key_part3 FROM tbl_nameWHERE key_part1=1

對于小表或報表查詢處理大部分或全部行的大表上的查詢，索引不那么重要。當查詢需要訪問大部分行時，按順序讀取比通過索引更快。

即使不是查詢需要所有的行，順序讀取也可以最大限度地減少磁盤查找。

有關詳細信息，請參閱“避免全表掃描”。

【MySQL精通之路】SQL優化(1)-查詢優化(23)-避免全表掃描-CSDN博客

2 主鍵優化

表的主鍵表示在最重要的查詢中使用的列或列集。

它有一個關聯的索引，用于快速查詢性能。查詢性能得益于NOT NULL優化，因為它不能包含任何NULL值。

使用InnoDB存儲引擎，可以對表數據進行物理組織，以根據主鍵列進行超快速查找和排序。

如果表很大且很重要，但沒有一個明顯的列或一組列用作主鍵，則可以創建一個單獨的列，并使用自動遞增值作為主鍵。當您使用外鍵聯接表時，這些唯一的ID可以作為指向其他表中相應行的指針。

3 空間索引優化

MySQL允許在NOT NULL幾何值列上創建空間索引（請參見第13.4.10節“創建空間索引”）。優化器檢查索引列的SRID屬性，以確定要使用哪個空間參考系統（SRS）進行比較，并使用適用于SRS的計算。（在MySQL 8.0之前，優化器使用笛卡爾計算對空間索引值進行比較；如果列包含非笛卡爾SRID的值，則此類操作的結果是未定義的。）

為了使比較正常工作，SPATIAL索引中的每一列都必須受到SRID限制。也就是說，列定義必須包括顯式SRID屬性，并且所有列值都必須具有相同的SRID。

優化器只考慮SRID限制列的SPATIAL索引：

1.限制為笛卡爾SRID的列上的索引啟用笛卡爾邊界框計算。

2.限制為地理SRID的列上的索引可以進行地理邊界框計算。

3.優化器忽略沒有SRID屬性（因此不受SRID限制）的列上的空間索引。MySQL仍然維護這樣的索引，如下所示：

4.它們會針對表修改（INSERT、UPDATE、DELETE等）進行更新。即使列可能包含笛卡爾和地理值的混合，更新也會像索引是笛卡爾的一樣發生。

它們的存在只是為了向后兼容（例如，在MySQL 5.7中執行轉儲和在MySQL 8.0中執行恢復的能力）。因為沒有SRID限制的列上的SPATIAL索引對優化器沒有用處，所以應該修改每個這樣的列：

驗證列中的所有值是否具有相同的SRID。要確定幾何體列col_name中包含的SRID，請使用以下查詢：

SELECT DISTINCT ST_SRID(col_name) FROM tbl_name;

如果查詢返回多行，則該列包含SRID的混合。在這種情況下，請修改其內容，使所有值都具有相同的SRID。

將列重新定義為具有顯式SRID屬性。

重新創建空間索引。

4 外鍵優化

如果一個表有許多列，并且查詢了許多不同的列組合，那么將不太頻繁使用的數據拆分成單獨的表，每個表有幾列，并通過復制主表中的數字ID列將它們關聯回主表，這可能會更有效。這樣，每個小表都可以有一個主鍵來快速查找其數據，并且可以使用聯接操作只查詢所需的列集。根據數據的分布方式，查詢可能會執行更少的I/O，占用更少的緩存內存，因為相關列被打包在磁盤上。（為了最大限度地提高性能，查詢嘗試從磁盤中讀取盡可能少的數據塊；只有幾列的表可以在每個數據塊中容納更多的行。）

5 列索引

最常見的索引類型包括一列，將該列中的值的副本存儲在數據結構中，從而可以快速查找具有相應列值的行。B樹數據結構使索引能夠快速找到一個特定值、一組值或一系列值，這些值對應于WHERE子句中的運算符，如=、>、≤、BETWEEN、IN等。

每個表的最大索引數和最大索引長度是按存儲引擎定義的。請參閱第17章“InnoDB存儲引擎”和第18章“替代存儲引擎”。所有存儲引擎都支持每個表至少16個索引，總索引長度至少為256字節。大多數存儲引擎都有更高的限制。

有關列索引的更多信息，請參閱第15.1.15節“CREATE INDEX語句”。

6.1 索引前綴

在字符串列的索引規范中使用col_name（N）語法，可以創建僅使用列的前N個字符的索引。以這種方式僅對列值的前綴進行索引會使索引文件變得更小。為BLOB或TEXT列編制索引時，必須為索引指定前綴長度。例如

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

對于使用REDUNDANT或COMPACT行格式的InnoDB表，前綴長度可達767字節。對于使用DYNAMIC或COMPRESED行格式的InnoDB表，前綴長度限制為3072字節。對于MyISAM表，前綴長度限制為1000字節。

注意：

前綴限制以字節為單位測量，而CREATE TABLE、ALTER TABLE和CREATE INDEX語句中的前綴長度被解釋為非二進制字符串類型（CHAR、VARCHAR、TEXT）的字符數和二進制字符串類型的字節數（binary、VARBINARY、BLOB）。為使用多字節字符集的非二進制字符串列指定前綴長度時，請考慮這一點。

如果搜索項超過索引前綴長度，則使用索引排除不匹配的行，并檢查其余行是否可能匹配。

有關索引前綴的更多信息，請參閱第15.1.15節“CREATE index語句”。

6.2 FULLTEXT索引

FULLTEXT索引用于全文搜索。只有InnoDB和MyISAM存儲引擎支持FULLTEXT索引，并且僅支持CHAR、VARCHAR和TEXT列。索引總是在整列上進行，不支持列前綴索引。有關詳細信息，請參閱第14.9節“全文搜索功能”。

優化應用于針對單個InnoDB表的某些類型的FULLTEXT查詢。具有這些特征的查詢特別有效：

僅返回文檔ID或文檔ID和搜索排名的FULLTEXT查詢。

FULLTEXT查詢按得分降序對匹配行進行排序，并應用LIMIT子句獲取前N個匹配行。為了應用此優化，必須沒有WHERE子句，只有一個按降序排列的ORDER BY子句。

FULLTEXT查詢只檢索與搜索項匹配的行的COUNT（*）值，不包含其他WHERE子句。將WHERE子句編碼為WHERE MATCH（text）ANTION（'other_text'），不包含任何>0的比較運算符。

對于包含全文表達式的查詢，MySQL會在查詢執行的優化階段評估這些表達式。優化器不僅僅查看全文表達式并進行估計，它實際上還在開發執行計劃的過程中對它們進行評估。

這種行為的一個含義是，全文查詢的EXPLAIN通常比優化階段未進行表達式求值的非全文查詢慢。

全文查詢的EXPLAIN可能會在Extra列中顯示由于優化過程中發生匹配而優化的Select表；在這種情況下，在以后的執行過程中不需要進行表訪問。

6.3 空間索引

可以對空間數據類型創建索引。MyISAM和InnoDB支持空間類型上的R樹索引。其他存儲引擎使用B樹對空間類型進行索引（ARCHIVE除外，它不支持空間類型索引）。

6.4 MEMORY存儲引擎中的索引

MEMORY存儲引擎默認使用HASH索引，但也支持BTREE索引。

6 多列索引

MySQL可以創建復合索引（即多列上的索引）。一個索引最多可以由16列組成。對于某些數據類型，可以對列的前綴進行索引（請參閱第10.3.5節“列索引”）。

MySQL可以對測試索引中所有列的查詢使用多個列索引，也可以只測試第一列、前兩列、前三列的查詢等等。如果在索引定義中按正確的順序指定列，單個復合索引可以加快同一表上的多種查詢。

多列索引可以被視為排序數組，其中的行包含通過連接索引列的值而創建的值。

筆記

作為復合索引的替代方案，您可以引入一個基于其他列的信息進行“哈希”處理的列。如果此列很短，并且具有合理的唯一性和索引，那么它可能比許多列上的“寬”索引更快。在MySQL中，使用這個額外的列非常容易：

SELECT * FROM tbl_nameWHERE hash_col=MD5(CONCAT(val1,val2))AND col1=val1 AND col2=val2;

假設一個表具有以下規范：

CREATE TABLE test (id         INT NOT NULL,last_name  CHAR(30) NOT NULL,first_name CHAR(30) NOT NULL,PRIMARY KEY (id),INDEX name (last_name,first_name)
);

名稱索引是對last_name和first_name列的索引。該索引可用于查詢中的查找，這些查詢為last_name和first_name值的組合指定了已知范圍內的值。它也可以用于僅指定last_name值的查詢，因為該列是索引的最左邊前綴（如本節稍后所述）。因此，名稱索引用于以下查詢中的查找：

SELECT * FROM test WHERE last_name='Jones';SELECT * FROM testWHERE last_name='Jones' AND first_name='John';SELECT * FROM testWHERE last_name='Jones'AND (first_name='John' OR first_name='Jon');SELECT * FROM testWHERE last_name='Jones'AND first_name >='M' AND first_name < 'N';

但是，名稱索引不用于以下查詢中的查找：

SELECT * FROM test WHERE first_name='John';SELECT * FROM testWHERE last_name='Jones' OR first_name='John';

假設您發出以下SELECT語句：

SELECT * FROM tbl_nameWHERE col1=val1 AND col2=val2;

?如果col1和col2上存在多列索引，則可以直接提取相應的行。如果col1和col2上存在單獨的單列索引，優化器會嘗試使用索引合并優化（請參閱第10.2.1.3節“索引合并優化”），或者嘗試通過決定哪個索引排除更多行并使用該索引提取行來找到限制性最強的索引。

如果表具有多列索引，則優化器可以使用索引最左邊的任何前綴來查找行。例如，如果您在（col1，col2，col3）上有一個三列索引，則您在（coll1）、（col1、col2）和（col1和col2，coll3）上具有索引搜索功能。

如果列不構成索引的最左邊前綴，MySQL就不能使用索引來執行查找。假設您的SELECT語句如下所示：

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果（col1、col2、col3）上存在索引，則只有前兩個查詢使用該索引。第三個和第四個查詢確實涉及索引列，但不使用索引來執行查找，因為（col2）和（col2，col3）不是（col1，col2，coll3）的最左邊前綴。

7 驗證索引使用情況

始終檢查您的所有查詢是否真的使用了您在表中創建的索引。使用EXPLAIN語句，

如“使用EXPLAIN優化查詢”所述。

8 InnoDB和MyISAM索引統計集合

存儲引擎收集有關表的統計信息以供優化器使用。表統計信息基于值組，其中值組是具有相同鍵前綴值的一組行。出于優化器的目的，一個重要的統計信息是平均值組大小。

MySQL通過以下方式使用平均值組大小：

估計每次引用訪問必須讀取的行數

估計一個部分聯接產生的行數，即窗體的一個操作產生的行的數量

(...) JOIN tbl_name ON tbl_name.key = expr

隨著索引的平均值組大小的增加，索引對這兩個目的的用處就越小，因為每次查找的平均行數也會增加：為了使索引更好地用于優化目的，最好每個索引值都以表中的少量行為目標。當給定的索引值產生大量行時，該索引的用處就小了，MySQL也不太可能使用它。

平均值組大小與表基數有關，表基數是值組的數量。SHOW INDEX語句顯示基于N/S的基數值，其中N是表中的行數，S是平均值組大小。該比率得出了表中值組的大致數量。

對于基于<=>比較運算符的聯接，NULL與任何其他值都沒有區別：NULL<=>NULL，就像N<=>N表示任何其他N一樣。

但是，對于基于=運算符的聯接，NULL與非NULL值不同：當expr1或expr2（或兩者）為NULL時，expr1=expr2不為true。這會影響tbl_name.key=expr形式的比較的ref訪問：如果expr的當前值為NULL，MySQL不會訪問該表，因為比較不可能為true。

對于=比較，表中有多少NULL值并不重要。出于優化目的，相關值是非NULL值組的平均大小。然而，MySQL目前無法收集或使用該平均大小。

對于InnoDB和MyISAM表，您可以分別通過InnoDB_stats_method和MyISAM_stats_method系統變量對表統計信息的收集進行一些控制。這些變量有三個可能的值，其差異如下：

當變量設置為nulls_equal時，所有NULL值都被視為相同（即，它們都形成一個值組）。

如果NULL值組大小遠高于非NULL值組的平均大小，則此方法會使平均值組大小向上傾斜。這使得索引在優化器看來不如對于查找非NULL值的聯接實際有用。因此，nulls_equal方法可能會導致優化器在應該使用索引進行ref訪問時不使用索引。

當變量設置為nulls_equival時，NULL值不被視為相同。相反，每個NULL值形成一個單獨的值組，大小為1。

如果有許多NULL值，此方法會向下傾斜平均值組的大小。如果非NULL值組的平均大小較大，則將每個NULL值計算為一個大小為1的組會導致優化器高估查找非NULL值的聯接的索引值。因此，當其他方法可能更好時，nulls_equival方法可能會導致優化器使用此索引進行ref查找。

當變量設置為nulls_ignored時，將忽略NULL值。

如果您傾向于使用許多使用<=>而不是=的聯接，則NULL值在比較中并不特殊，一個NULL等于另一個NULL。在這種情況下，nulls_equal是適當的統計方法。

innodb_stats_method系統變量具有全局值；myisam_stats_method系統變量同時具有全局值和會話值。設置全局值會影響相應存儲引擎中表的統計信息收集。設置會話值僅影響當前客戶端連接的統計信息收集。這意味著您可以通過設置myisam_stats_method的會話值，在不影響其他客戶端的情況下，強制使用給定方法重新生成表的統計信息。

要重新生成MyISAM表統計信息，可以使用以下任意方法：

執行myisamchk--stats_method=method_name--analyze

更改表以使其統計信息過期（例如，插入一行，然后刪除它），然后設置myisam_stats_method并發出ANALYZE table語句

關于innob_stats_method和myisam_stats_method的使用的一些注意事項：

可以強制顯式收集表統計信息，如前所述。但是，MySQL也可以自動收集統計信息。例如，如果在為表執行語句的過程中，其中一些語句修改了表，MySQL可能會收集統計信息。（例如，這可能發生在大容量插入或刪除，或某些ALTER TABLE語句中。）如果發生這種情況，則會使用innodb_stats_method或myisam_stats_method當時的任何值來收集統計信息。因此，如果您使用一種方法收集統計信息，但當稍后自動收集表的統計信息時，系統變量被設置為另一種方法，則會使用另一種方式。

無法判斷使用哪種方法為給定的表生成統計信息。

這些變量僅適用于InnoDB和MyISAM表。其他存儲引擎只有一種收集表統計信息的方法。通常它更接近于nulls_equal方法。

9 B樹索引與哈希索引的比較

了解B-樹和散列數據結構有助于預測不同查詢在索引中使用這些數據結構的不同存儲引擎上的執行情況，特別是對于允許您選擇B-樹或散列索引的MEMORY存儲引擎。

9.1 B-樹索引特征

B樹索引可用于使用=、>、>=、<、<=或BETWEEN運算符的表達式中的列比較。如果LIKE的參數是不以通配符開頭的常量字符串，則索引也可用于LIKE比較。例如，以下SELECT語句使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

在第一個語句中，只考慮具有“Patrick”<=key_col<“Patricl”的行。在第二個語句中，只考慮“Pat”<=key_col<“Pau”的行。

以下SELECT語句不使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

在第一條語句中，LIKE值以通配符開頭。在第二個語句中，LIKE值不是常量。

如果您使用。。。類似于“%string%”，字符串長度超過三個字符，MySQL使用Turbo Boyer-Moore算法初始化字符串的模式，然后使用此模式更快地執行搜索。

如果對col_name進行了索引，則使用col_name IS NULL的搜索將使用索引。

任何不跨越WHERE子句中所有AND級別的索引都不會用于優化查詢。換句話說，為了能夠使用索引，必須在每個AND組中使用索引的前綴。

以下WHERE子句使用索引：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3/* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2/* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5/* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

這些WHERE子句不使用索引：

    /* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2/*  Index is not used in both parts of the WHERE clause  */
... WHERE index=1 OR A=10/* No index spans all rows  */
... WHERE index_part1=1 OR index_part2=10

有時MySQL不使用索引，即使有可用的索引。發生這種情況的一種情況是，優化器估計使用索引需要MySQL訪問表中很大比例的行。（在這種情況下，表掃描可能會更快，因為它需要更少的查找。）然而，如果這樣的查詢使用LIMIT只檢索其中的一些行，MySQL無論如何都會使用索引，因為它可以更快地找到結果中返回的幾行。

9.2 哈希索引特征

哈希索引的特性與剛才討論的有些不同：

它們僅用于使用=或<=>運算符的相等性比較（但速度非常快）。它們不用于查找值范圍的比較運算符，如<。依賴于這種類型的單值查找的系統被稱為“鍵值存儲”；要將MySQL用于此類應用程序，請盡可能使用哈希索引。

優化器不能使用哈希索引來加快ORDER BY操作的速度。（這種類型的索引不能用于按順序搜索下一個條目。）

MySQL無法大致確定兩個值之間有多少行（這是范圍優化器用來決定使用哪個索引的）。如果將MyISAM或InnoDB表更改為哈希索引的MEMORY表，這可能會影響某些查詢。

只有整排關鍵字才能用于搜索一行。（對于B-樹索引，鍵的任何最左邊的前綴都可以用來查找行。）

10 索引擴展的使用

InnoDB通過將主鍵列附加到每個輔助索引上來自動擴展它

CREATE TABLE t1 (i1 INT NOT NULL DEFAULT 0,i2 INT NOT NULL DEFAULT 0,d DATE DEFAULT NULL,PRIMARY KEY (i1, i2),INDEX k_d (d)
) ENGINE = InnoDB;

該表定義了列（i1，i2）上的主鍵。它還在列（d）上定義了一個輔助索引k_d，但InnoDB在內部擴展了這個索引，并將其視為列（d，i1，i2）。

優化器在確定如何以及是否使用擴展的輔助索引時，會考慮該索引的主鍵列。這可以帶來更高效的查詢執行計劃和更好的性能。

優化器可以將擴展的輔助索引用于ref、range和index_merge索引訪問、松散索引掃描訪問、聯接和排序優化以及MIN（）/MAX（）優化。

以下示例顯示了優化器是否使用擴展的二級索引對執行計劃的影響。假設t1由以下行填充：

INSERT INTO t1 VALUES
(1, 1, '1998-01-01'), (1, 2, '1999-01-01'),
(1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
(1, 5, '2002-01-01'), (2, 1, '1998-01-01'),
(2, 2, '1999-01-01'), (2, 3, '2000-01-01'),
(2, 4, '2001-01-01'), (2, 5, '2002-01-01'),
(3, 1, '1998-01-01'), (3, 2, '1999-01-01'),
(3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
(3, 5, '2002-01-01'), (4, 1, '1998-01-01'),
(4, 2, '1999-01-01'), (4, 3, '2000-01-01'),
(4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
(5, 1, '1998-01-01'), (5, 2, '1999-01-01'),
(5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
(5, 5, '2002-01-01');

現在考慮這個查詢：

EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'

執行計劃取決于是否使用擴展索引。

當優化器不考慮索引擴展時，它只將索引k_d視為（d）。查詢的EXPLAIN會產生以下結果：

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t1type: ref
possible_keys: PRIMARY,k_dkey: k_dkey_len: 4ref: constrows: 5Extra: Using where; Using index

當優化器考慮索引擴展時，它將k_d視為（d，i1，i2）。在這種情況下，它可以使用最左邊的索引前綴（d，i1）來生成更好的執行計劃：

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t1type: ref
possible_keys: PRIMARY,k_dkey: k_dkey_len: 8ref: const,constrows: 1Extra: Using index

當優化器考慮索引擴展時，它將k_d視為（d，i1，i2）。在這種情況下，它可以使用最左邊的索引前綴。在這兩種情況下，key表示優化器使用輔助索引k_d，但EXPLAIN輸出顯示了使用擴展索引的這些改進：

key_len從4個字節變為8個字節，這表明密鑰查找使用的是列d和i1，而不僅僅是d。

ref值從const變為const，const是因為鍵查找使用了兩個鍵部分，而不是一個。

行數從5減少到1，這表明InnoDB應該需要檢查更少的行才能產生結果。

Extra值從Using where更改；使用索引到使用索引。這意味著可以僅使用索引讀取行，而無需查詢數據行中的列。

使用擴展索引時優化器行為的差異也可以從SHOW STATUS中看出：

FLUSH TABLE t1;
FLUSH STATUS;
SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
SHOW STATUS LIKE 'handler_read%'

前面的語句包括用于刷新表緩存和清除狀態計數器的FLUSH TABLES和FLUSH STATUS。

在沒有索引擴展的情況下，SHOW STATUS會產生以下結果：

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 5     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

使用索引擴展，SHOW STATUS會產生此結果。Handler_read_next值從5減小到1，表示索引的使用效率更高：

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 1     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

optimizer_switch系統變量的use_index_extensions標志允許控制優化器在確定如何使用InnoDB表的輔助索引時是否考慮主鍵列。默認情況下，會啟用use_index_extensions。要檢查禁用索引擴展是否可以提高性能，請使用以下語句：

SET optimizer_switch = 'use_index_extensions=off';

優化器對索引擴展的使用受到索引中關鍵部分數量（16）和最大密鑰長度（3072字節）的通常限制。

11 優化器使用生成的列索引

MySQL支持生成列上的索引。例如

CREATE TABLE t1 (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc));

生成的列gc被定義為表達式f1+1。列也會被索引，優化器可以在執行計劃構建期間將該索引考慮在內。在下面的查詢中，WHERE子句引用gc，優化器會考慮該列上的索引是否會產生更高效的計劃：

SELECT * FROM t1 WHERE gc > 9;

優化器可以使用生成列的索引來生成執行計劃，即使在查詢中沒有按名稱直接引用這些列的情況下也是如此。如果WHERE、ORDER BY或GROUP BY子句引用的表達式與某個索引生成列的定義匹配，就會發生這種情況。以下查詢不直接引用gc，但使用了與gc定義匹配的表達式：

SELECT * FROM t1 WHERE f1 + 1 > 9;

優化器識別出表達式f1+1與gc的定義匹配，并且gc已被索引，因此它在執行計劃構建過程中會考慮該索引。您可以使用EXPLAIN看到這一點：

mysql> EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 9\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t1partitions: NULLtype: range
possible_keys: gckey: gckey_len: 5ref: NULLrows: 1filtered: 100.00Extra: Using index condition

優化器識別出表達式f1+1與gc的定義匹配，并且gc已被索引，因此它在執行計劃構建過程中會考慮該索引。您可以看到，實際上，優化器已將表達式f1+1替換為與該表達式匹配的生成列的名稱。這在SHOW WARNINGS顯示的擴展EXPLAIN信息中可用的重寫查詢中也很明顯：

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************Level: NoteCode: 1003
Message: /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc`AS `gc` from `test`.`t1` where (`test`.`t1`.`gc` > 9)

以下限制和條件適用于優化器使用生成的列索引：

要使查詢表達式與生成的列定義匹配，該表達式必須相同，并且必須具有相同的結果類型。例如，如果生成的列表達式是f1+1，那么如果查詢使用1+f1，或者f1+1（整數表達式）與字符串進行比較，優化器將無法識別匹配項。

優化適用于以下運算符：=、<、<=、>、>=、BETWEEN和IN（）。

對于除BETWEEN和IN（）之外的運算符，任一操作數都可以替換為匹配的生成列。對于BETWEEN和IN（），只有第一個參數可以替換為匹配的生成列，其他參數必須具有相同的結果類型。對于涉及JSON值的比較，還不支持BETWEEN和IN（）。

生成的列必須定義為至少包含一個函數調用或前一項中提到的一個運算符的表達式。表達式不能由對另一列的簡單引用組成。例如，gc INT AS（f1）STORED僅由列引用組成，因此不考慮gc上的索引。

為了將字符串與索引生成的列進行比較，這些列從返回帶引號字符串的JSON函數中計算值，列定義中需要JSON_UNQUOTE（）來刪除函數值中的額外引號。（為了將字符串與函數結果直接進行比較，JSON比較器會處理引號刪除，但索引查找不會出現這種情況。）例如，不要像這樣編寫列定義：

doc_name TEXT AS (JSON_EXTRACT(jdoc, '$.name')) STORED

這樣寫：

doc_name TEXT AS (JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name'))) STORED

doc_name文本AS（JSON_UNQUOTE（JSON_EXTRACT（jdoc，'$.name'））已存儲

使用后一種定義，優化器可以檢測這兩種比較的匹配：

... WHERE JSON_EXTRACT(jdoc, '$.name') = 'some_string' ...
... WHERE JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name')) = 'some_string' ...

如果列定義中沒有JSON_UNQUOTE（），優化器將僅為這些比較中的第一個檢測匹配。

如果優化器選擇了錯誤的索引，則可以使用索引提示來禁用它，并強制優化器做出不同的選擇。

12 不可見索引

MySQL支持不可見索引；即優化器未使用的索引。該功能適用于主鍵以外的索引（顯式或隱式）。

默認情況下，索引是可見的。若要顯式控制新索引的可見性，請使用VISIBLE或INVISIBLE關鍵字作為CREATE TABLE、CREATE index或ALTER TABLE的索引定義的一部分：

CREATE TABLE t1 (i INT,j INT,k INT,INDEX i_idx (i) INVISIBLE
) ENGINE = InnoDB;
CREATE INDEX j_idx ON t1 (j) INVISIBLE;
ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;

若要更改現有索引的可見性，請將VISIBLE或INVISIBLE關鍵字與alter TABLE…一起使用。。。ALTER INDEX操作：

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

有關索引是可見還是不可見的信息可從Information Schema STATISTICS表或SHOW index輸出中獲得。例如

mysql> SELECT INDEX_NAME, IS_VISIBLEFROM INFORMATION_SCHEMA.STATISTICSWHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 't1';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| i_idx      | YES        |
| j_idx      | NO         |
| k_idx      | NO         |
+------------+------------+

通過不可見索引，可以測試刪除索引對查詢性能的影響，而無需進行破壞性更改，如果需要索引，則必須撤消該更改。對于大表來說，刪除和重新添加索引可能代價高昂，而使其不可見和可見是快速的就地操作。

如果優化器實際上需要或使用一個不可見的索引，有幾種方法可以注意到它的缺失對表查詢的影響：

對于包含引用不可見索引的索引提示的查詢，會發生錯誤。

性能模式數據顯示受影響查詢的工作負載有所增加。

查詢具有不同的EXPLAIN執行計劃。

以前沒有出現在慢速查詢日志中的查詢會出現在該日志中。

optimizer_switch系統變量的use_invisible_indexes標志控制優化器是否使用不可見索引來構建查詢執行計劃。如果標志關閉（默認設置），優化器將忽略不可見的索引（與引入此標志之前的行為相同）。如果該標志處于啟用狀態，則不可見索引保持不可見，但優化器會在構建執行計劃時將其考慮在內。

使用SET_VAR優化器提示臨時更新optimizer_switch的值，您可以僅在單個查詢的持續時間內啟用不可見索引，如下所示：

mysql> EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */>     i, j FROM t1 WHERE j >= 50\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t1partitions: NULLtype: range
possible_keys: j_idxkey: j_idxkey_len: 5ref: NULLrows: 2filtered: 100.00Extra: Using index conditionmysql> EXPLAIN SELECT i, j FROM t1 WHERE j >= 50\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t1partitions: NULLtype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 5filtered: 33.33Extra: Using where

索引可見性不影響索引維護。例如，根據表行的更改，索引將繼續更新，而唯一索引可防止在列中插入重復項，無論該索引是可見還是不可見。

如果沒有顯式主鍵的表在NOT NULL列上具有任何UNIQUE索引，則該表可能仍然具有有效的隱式主鍵。在這種情況下，第一個這樣的索引在表行上放置與顯式主鍵相同的約束，并且不能使該索引不可見。考慮下表定義：

CREATE TABLE t2 (i INT NOT NULL,j INT NOT NULL,UNIQUE j_idx (j)
) ENGINE = InnoDB;

該定義不包括顯式主鍵，但NOT NULL列j上的索引對行的約束與主鍵相同，不能使其不可見：

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;
ERROR 3522 (HY000): A primary key index cannot be invisible.

現在假設表中添加了一個顯式主鍵：

ALTER TABLE t2 ADD PRIMARY KEY (i);

不能使顯式主鍵不可見。此外，j上的唯一索引不再充當隱式主鍵，因此可以使其不可見：

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;
Query OK, 0 rows affected (0.03 sec)

13 降序索引

?MySQL支持降序索引：索引定義中的DESC不再被忽略，而是按降序存儲鍵值。以前，可以按相反的順序掃描索引，但會降低性能。降序索引可以按前向順序掃描，這樣效率更高。當最有效的掃描順序混合了某些列的升序和其他列的降序時，降序索引也使優化器可以使用多個列索引。

考慮下表定義，其中包含兩列和四個兩列索引定義，用于列上各種升序和降序索引的組合：

CREATE TABLE t (c1 INT, c2 INT,INDEX idx1 (c1 ASC, c2 ASC),INDEX idx2 (c1 ASC, c2 DESC),INDEX idx3 (c1 DESC, c2 ASC),INDEX idx4 (c1 DESC, c2 DESC)
);

表定義產生了四個不同的索引。優化器可以對每個ORDER BY子句執行前向索引掃描，并且不需要使用文件排序操作：

ORDER BY c1 ASC, c2 ASC    -- optimizer can use idx1
ORDER BY c1 DESC, c2 DESC  -- optimizer can use idx4
ORDER BY c1 ASC, c2 DESC   -- optimizer can use idx2
ORDER BY c1 DESC, c2 ASC   -- optimizer can use idx3

遞減索引的使用受以下條件限制：

只有InnoDB存儲引擎才支持降序索引，但有以下限制：

如果輔助索引包含降序索引鍵列或主鍵包含降序索引列，則不支持更改緩沖。

InnoDB SQL解析器不使用降序索引。對于InnoDB全文搜索，這意味著索引表的FTS_DOC_ID列上所需的索引不能定義為降序索引。有關更多信息，請參閱第17.6.2.4節“InnoDB全文索引”。

所有可使用升序索引的數據類型都支持降序索引。

普通列（非生成列）和生成列（虛擬列和存儲列）都支持降序索引。

DISTINCT可以使用任何包含匹配列的索引，包括降序鍵部分。

具有降序鍵部分的索引不用于調用聚合函數但沒有GROUP BY子句的查詢的MIN（）/MAX（）優化。

BTREE支持降序索引，但不支持HASH索引。FULLTEXT或SPATIAL索引不支持降序索引。

為HASH、FULLTEXT和SPATIAL索引顯式指定ASC和DESC指示符會導致錯誤。

您可以在EXPLAIN輸出的Extra列中看到，優化器可以使用降序索引，如下所示：

mysql> CREATE TABLE t1 (-> a INT, -> b INT, -> INDEX a_desc_b_asc (a DESC, b ASC)-> );mysql> EXPLAIN SELECT * FROM t1 ORDER BY a ASC\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t1partitions: NULLtype: index
possible_keys: NULLkey: a_desc_b_asckey_len: 10ref: NULLrows: 1filtered: 100.00Extra: Backward index scan; Using index

在EXPLAIN FORMAT=TREE輸出中，遞減索引的使用通過在索引名稱后面添加（reverse）來表示，如下所示：

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 ORDER BY a ASC\G 
*************************** 1. row ***************************
EXPLAIN: -> Index scan on t1 using a_desc_b_asc (reverse)  (cost=0.35 rows=1)

另請參閱EXPLAIN Extra信息。

14 TIMESTAMP列的索引查詢

時間值作為UTC值存儲在TIMESTAMP列中，插入TIMESTAMP和從TIMESTAMP中檢索的值在會話時區和UTC之間轉換。（這與CONVERT_TZ（）函數執行的轉換類型相同。如果會話時區是UTC，則實際上沒有時區轉換。）

由于當地時區更改的約定，如夏令時（DST），UTC和非UTC時區之間的轉換并非雙向一對一。不同的UTC值在另一個時區中可能不同。以下示例顯示了在非UTC時區中變得相同的不同UTC值：

mysql> CREATE TABLE tstable (ts TIMESTAMP);
mysql> SET time_zone = 'UTC'; -- insert UTC values
mysql> INSERT INTO tstable VALUES('2018-10-28 00:30:00'),('2018-10-28 01:30:00');
mysql> SELECT ts FROM tstable;
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 00:30:00 |
| 2018-10-28 01:30:00 |
+---------------------+
mysql> SET time_zone = 'MET'; -- retrieve non-UTC values
mysql> SELECT ts FROM tstable;
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+

注意：

要使用命名時區，如“MET”或“歐洲/阿姆斯特丹”，必須正確設置時區表。

有關說明，請參閱“MySQL Server時區支持”。

您可以看到，當轉換到“MET”時區時，兩個不同的UTC值是相同的。對于給定的TIMESTAMP列查詢，這種現象可能會導致不同的結果，這取決于優化器是否使用索引來執行查詢。

假設查詢使用WHERE子句從前面顯示的表中選擇值，以在ts列中搜索單個特定值，例如用戶提供的時間戳文字：

SELECT ts FROM tstable
WHERE ts = 'literal';

進一步假設查詢在以下條件下執行：

會話時區不是UTC，而是夏令時。例如

SET time_zone = 'MET';

由于夏令時的變化，存儲在TIMESTAMP列中的唯一UTC值在會話時區中不是唯一的。（前面顯示的示例說明了這種情況是如何發生的。）

查詢指定一個搜索值，該搜索值在會話時區中輸入DST的小時內。

在這些條件下，WHERE子句中的比較對于無索引查找和索引查找以不同的方式發生，并導致不同的結果：

如果沒有索引或優化器無法使用索引，則會在會話時區中進行比較。優化器執行表掃描，檢索每個ts列的值，將其從UTC轉換為會話時區，并將其與搜索值（也在會話時區中進行解釋）進行比較：

mysql> SELECT ts FROM tstableWHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+

由于存儲的ts值被轉換為會話時區，因此查詢可能返回兩個時間戳值，這兩個值與UTC值不同，但在會話時區中相等：一個值出現在時鐘更改時的夏令時偏移之前，另一個值發生在夏令時偏移之后。

如果有可用的索引，則以UTC進行比較。優化器執行索引掃描，首先將搜索值從會話時區轉換為UTC，然后將結果與UTC索引條目進行比較：

mysql> ALTER TABLE tstable ADD INDEX (ts);
mysql> SELECT ts FROM tstableWHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
+---------------------+

在這種情況下，（轉換的）搜索值僅與索引條目匹配，并且由于不同存儲的UTC值的索引條目也是不同的，因此搜索值只能與其中一個匹配。

由于非索引查找和索引查找的優化器操作不同，查詢在每種情況下都會產生不同的結果。無索引查找的結果返回會話時區中匹配的所有值。索引查找無法執行以下操作：

它是在存儲引擎中執行的，存儲引擎只知道UTC值。

對于映射到同一UTC值的兩個不同會話時區值，索引查找僅匹配相應的UTC索引條目，并且僅返回一行。

在前面的討論中，存儲在tstable中的數據集恰好由不同的UTC值組成。在這種情況下，所示形式的所有使用索引的查詢最多匹配一個索引條目。

如果索引不是UNIQUE，則表（和索引）可能存儲給定UTC值的多個實例。例如，ts列可能包含UTC值“2018-10-28 00:30:00”的多個實例。在這種情況下，使用查詢的索引將返回它們中的每一個（在結果集中轉換為MET值“2018-10-28 02:30:00”）。的確，使用索引的查詢將轉換后的搜索值與UTC索引條目中的單個值匹配，而不是與轉換為會話時區中搜索值的多個UTC值匹配。

如果返回會話時區中匹配的所有ts值很重要，則解決方法是通過IGNORE index提示禁止使用索引：

mysql> SELECT ts FROM tstableIGNORE INDEX (ts)WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+

對于兩個方向的時區轉換，同樣缺乏一對一映射的情況也發生在其他上下文中，例如使用FROM_UNIXTIME（）和UNIX_TIMESTAMP（）函數執行的轉換。參見第14.7節“日期和時間函數”。