MySQL8新增的三種索引方式

MySQL 8.x中新增了三種索引方式：隐藏索引、降序索引 及 函數索引。

1. 隐藏索引

隐藏索引又叫不可見索引，主要應用于索引的 軟删除 和 灰階釋出。

在之前MySQL的版本中，隻能通過顯式的方式删除索引，如果删除後發現索引删錯了，又隻能通過建立索引的方式将删除的索引添加回來，如果資料庫中的資料量非常大，或者表比較大，這種操作的成本非常高。在MySQL 8.0中，隻需要将這個索引先設定為隐藏索引，使查詢優化器不再使用這個索引，但是，此時這個索引還是需要MySQL背景進行維護，當确認将這個索引設定為隐藏索引系統不會受到影響時，再将索引徹底删除。這就是索引軟删除功能。

灰階釋出，就是說建立索引時，首先将索引設定為隐藏索引，通過修改查詢優化器的開關，使隐藏索引對查詢優化器可見，通過explain對索引進行測試，确認這個索引有效，某些查詢可以使用到這個索引，就可以将其設定為可見索引，完成灰階釋出的效果。

建立隐藏索引具體代碼如下（隻需要在建立索引的後面加上invisible即可）：

create index 索引名稱 on 表名稱('索引字段名') invisible;           

此時建立的索引就是隐藏索引，我們可以檢視目前表中索引清單：

show index from 表名稱 \G;           

\G：是格式化的意思

顯示的索引中有一個Visible屬性，YES表示這個索引可見，NO表示不可見。接下來我們在使用explain分析，發現剛剛建立的隐藏索引不生效。在灰階釋出的場景中我們需要同過explain分析隐藏索引是否對某些SQL有幫助，但是隐藏索引又不生效，怎麼辦呢？

在MySQL8 中提供了一種新的測試方式，可以通過優化器的一個開關來打開某個設定，使隐藏索引對查詢優化器可見。我們可以通過如下代碼檢視這個開關是否開啟：

select @@optimizer_switch \G;           

查詢結果如下所示：

use_invisible_indexes=off

這個開關預設是off，off表示關閉，ON表示開啟。

我們可以通過如下SQL在目前會話中開啟和關閉（不影響其他會話程序）：

set session optimizer_switch="use_invisible_indexes=on";           

現在我們可以通過explain分析隐藏索引是否對某些SQL有幫助。

注意：主鍵不能設定隐藏索引哦

2. 降序索引

MySQL 8.0開始真正支援降序索引（descending index），并且隻有InnoDB存儲引擎支援降序索引，隻支援BTREE降序索引。MySQL 8.0不再對GROUP BY操作進行隐式排序。

說實話，我個人絕對這個沒有什麼實際應用場景，一般我們查詢資料都需要where條件，故而一般都會優先優化where條件字段的索引問題。不過我們還是來說說具體操作吧！

create table if not exists test(
c1 int, 
c2 int, 
index c1_c2(c1 asc, c2 desc)
);           

如上所示，建立表的時候建立了索引 c1_c2 ，并且在索引中指定了c1是升序，c2是降序。

當我們使用如下SQL查詢時：

explain select * from test order by c1, c2 desc;           

在MySQL5.7中按照c2字段進行降序排序，并沒有使用索引。但是在MySQL8中使用索引，并使用了索引的反向掃描。

我們再來看看下面這條SQL：

explain select * from test order by c1, c2 asc;           

此時索引就不起作用了，是不是感覺有點雞肋。如果我們使用了where條件查詢，MySQL優化器會使用where條件中的字段優化查詢，是以我感覺這個降序索引沒有什麼作用。

3. 函數索引

在前的版本中，我們的SQL中使用了某個函數（例如：upper，json相關函數），那麼就不會走索引查詢，為此MySQL8新增了一個函數索引，以此解決函數不走索引的問題。

3.1 建立函數索引，下面以轉大寫函數為例：

create index 索引名稱 on 表名稱( ( UPPER( 字段名 ) ) );           

3.2 測試函數索引

explain select * from test where upper(c1) = 'ABC'           

test是測試表，并且在c1字段上添加了小寫轉大寫的函數索引

分析結果表明：目前SQL使用了函數索引。

3.3 再來看看對json的支援

建立測試表，如下所示：

create table if not exists test(
data json, 
index( (CAST(data->>'$.name' as char(30) ) ) )
);           

JSON資料長度不固定，如果直接對JSON資料進行索引，可能會超出索引長度，通常，會隻截取JSON資料的一部分進行索引。

CAST()類型轉換函數，把資料轉化為char(30)類型。使用方式為CAST(資料 as 資料類型)。

data ->> '$.name'表示JSON的運算符

簡單的了解為，就是取name節點的值，将其轉化為char(30)類型

SQL中使用同一個json函數查詢時，依然會使用函數索引進行優化。

如果有什麼不對的地方大家可以在我的公衆号留言或者加入我們的粉絲交流群。

粉絲交流群可以在公衆号檢視哦！