以 Mysql 為例，其中索引 BTree 型別 。以下幾種SQL設計會導致雖然使用了索引，但是索引不會生效，即引擎放棄使用索引而進行全表掃描：

WHERE 子句中使用 != 或 <> 運算子。WHERE 子句中對索引列使用 %字首模糊查詢。WHERE 子句中對索引列使用 OR 來連線條件。WHERE 子句中對索引列使用 NOT IN。WHERE 子句中對索引列使用計算、函式、型別轉換等操作。WHERE 子句中對索引列使用引數。

以下情況，MySQL的索引"失效"不可用

1、透過索引掃描的記錄超過20%~30%，可能會變成全表掃描

2、聯合索引中，查詢條件不符合左側前導要求

3、查詢條件列最左以萬用字元%開始

4、查詢條件發生資料型別隱式轉換，或者字符集不匹配

5、HEAP表使用HASH索引時，使用範圍檢索或者ORDER BY

6、多表關聯時，排序欄位不屬於驅動表，無法利用索引完成排序

7、JOIN查詢時，關聯列資料型別（字符集）不一致也會導致索引不可用

8、不可見索引，即便force index也不可用9、違反索引排序規則

程式設計師應該都知道，為了提高資料庫的查詢速度，我們可以對錶上的一個欄位或者多個欄位建立索引，但是有些 SQL 錯誤的寫法，可能會導致索引失效。

01. 檢視執行計劃

如何判斷 SQL 的執行是做了全表掃描還是走了索引，不是憑感覺判斷 SQL 執行的快慢，而是要看 SQL 的執行計劃；很多工具都提供了檢視執行計劃的功能，不過最原始的方法，還是透過 explain 進行檢視；下面的 SQL，是否使用的索引，一目瞭然。

1. 沒有索引

explain select * from user where gender = "M";

2. 有索引

explain select * from user where name = "Tom";02. 索引失效

1. 使用 like 時，% 在前面不走索引（在後面可以走索引）；

explain select * from user where name like "%om";

2. 資料型別出現隱式轉化，比如我們這裡手機號 mobile 欄位設定的是 varchar 型別，但是查詢的時候用的是數字，那麼就【可能】不走索引。

explain select * from user where mobile = 13800000000;

3. 在索引欄位上使用 not，<>，!= ；

explain select * from user where mobile <> "13800000000";explain select * from user where mobile != "13800000000";

4. 對索引欄位上使用函式；

explain select * from user where length(mobile) < 10

5. 聯合索引，如果查詢條件不滿足最左匹配原則，則不會走索引；

6. or 會使索引失效，儘管 or 左右的條件都有索引；

explain select * from user where name = "Tom" or mobile = "13800000000";

總之，MySQL 的索引最佳化和索引失效還是挺複雜的，主要體現在 MySQL 隨著版本升級，有一些我們熟知的技巧可能會不再正確，我們現在認為一定會索引失效的 SQL 寫法，可能會變成走索引，所以這也是為什麼我在上文中，多次用到【可能】會造成索引失效的原因。

1.以“%”開頭的LIKE語句，模糊匹配

2. OR語句前後沒有同時使用索引

3. 資料型別出現隱式轉化（如varchar不加單引號的話可能會自動轉換為int型）

函式索引顧名思義就是加給欄位加了函式的索引，這裡的函式也可以是表示式。所以也叫表示式索引。

MySQL 5.7 推出了虛擬列的功能，MySQL8.0的函式索引內部其實也是依據虛擬列來實現的。

我們考慮以下幾種場景：

1.對比日期部分的過濾條件。

SELECT ...FROM tb1WHERE date(time_field1) = current_date;

2.兩欄位做計算。

SELECT ...FROM tb1WHERE field2 + field3 = 5;

3.求某個欄位中間某子串。

SELECT ...FROM tb1WHERE substr(field4, 5, 9) = "actionsky";

4.求某個欄位末尾某子串。

SELECT ...FROM tb1WHERE RIGHT(field4, 9) = "actionsky";

5.求JSON格式的VALUE。

SELECT ...FROM tb1WHERE CAST(field4 ->> "$.name" AS CHAR(30)) = "actionsky";

以上五個場景如果不用函式索引，改寫起來難易不同。不過都要做相關修改，不是過濾條件修正就是表結構變更新增冗餘欄位加額外索引。

比如第1個場景改寫為，

SELECT ...FROM tb1WHERE time_field1 >= concat(current_date, " 00:00:00")AND time_field1 <= concat(current_date, "23:59:59");

再比如第4個場景的改寫，

由於是求最末尾的子串，只能新增一個新的冗餘欄位，並且做相關的計劃任務來一定頻率的非同步更新或者新增觸發器來實時更新此欄位值。

SELECT ...FROM tb1WHERE field4_suffix = "actionsky";

那我們看到，改寫也可以實現，不過這樣的SQL就沒有標準化而言，後期不能平滑的遷移了。

MySQL 8.0 推出來了函式索引讓這些變得相對容易許多。

不過函式索引也有自己的缺陷，就是寫法很固定，必須要嚴格按照定義的函式來寫，不然最佳化器不知所措。

我們來把上面那些場景例項化。

示例表結構，

總記錄數

mysql> SELECT COUNT(*)FROM t_func;+----------+| count(*) |+----------+| 16384 |+----------+1 row in set (0.01 sec)

我們把上面幾個場景的索引全加上。

mysql > ALTER TABLE t_func ADD INDEX idx_log_time ( ( date( log_time ) ) ),ADD INDEX idx_u1 ( ( rank1 + rank2 ) ),ADD INDEX idx_suffix_str3 ( ( RIGHT ( str3, 9 ) ) ),ADD INDEX idx_substr_str1 ( ( substr( str1, 5, 9 ) ) ),ADD INDEX idx_str2 ( ( CAST( str2 ->> "$.name" AS CHAR ( 9 ) ) ) );QUERY OK,0 rows affected ( 1.13 sec ) Records : 0 Duplicates : 0 WARNINGS : 0

我們再看下錶結構， 發現好幾個已經被轉換為系統自己的寫法了。

MySQL 8.0 還有一個特性，就是可以把系統隱藏的列顯示出來。

我們用show extened 列出函式索引建立的虛擬列，

上面5個隨機字串列名為函式索引隱式建立的虛擬COLUMNS。

我們先來看看場景2，兩個整形欄位的相加，

mysql> SELECT COUNT(*)FROM t_funcWHERE rank1 + rank2 = 121;+----------+| count(*) |+----------+| 878 |+----------+1 row in set (0.00 sec)

看下執行計劃，用到了idx_u1函式索引，

mysql> explain SELECT COUNT(*)FROM t_funcWHERE rank1 + rank2 = 121\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t_funcpartitions: NULLtype: refpossible_keys: idx_u1key: idx_u1key_len: 9ref: constrows: 878filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)

那如果我們稍微改下這個SQL的執行計劃，發現此時不能用到函式索引，變為全表掃描了，所以要嚴格按照函式索引的定義來寫SQL。

mysql> explain SELECT COUNT(*)FROM t_funcWHERE rank1 = 121 - rank2\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t_funcpartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 16089filtered: 10.00Extra: Using where1 row in set, 1 warning (0.00 sec)

再來看看場景1的的改寫和不改寫的效能簡單對比，

mysql> SELECT *FROM t_funcWHERE date(log_time) = "2019-04-18"LIMIT 1\G*************************** 1. row ***************************id: 2rank1: 1str1: test-actionsky-teststr2: {"age": 30, "name": "dell"}rank2: 120str3: test-actionskylog_time: 2019-04-18 10:04:531 row in set (0.01 sec)

我們把普通的索引加上。

mysql > ALTER TABLE t_func ADD INDEX idx_log_time_normal ( log_time );QUERY OK,0 rows affected ( 0.36 sec ) Records : 0 Duplicates : 0 WARNINGS : 0

然後改寫下SQL看下。

mysql> SELECT *FROM t_funcWHERE date(log_time) >= "2019-04-18 00:00:00"AND log_time < "2019-04-19 00:00:00"*************************** 1. row ***************************id: 2rank1: 1str1: test-actionsky-teststr2: {"age": 30, "name": "dell"}rank2: 120str3: test-actionskylog_time: 2019-04-18 10:04:531 row in set (0.01 sec)

兩個看起來沒啥差別，我們仔細看下兩個的執行計劃：

普通索引mysql> explain format=json SELECT *FROM t_funcWHERE log_time >= "2019-04-18 00:00:00"AND log_time < "2019-04-19 00:00:00"LIMIT 1\G*************************** 1. row ***************************EXPLAIN: {"query_block": {"select_id": 1,"cost_info": {"query_cost": "630.71"},"table": {"table_name": "t_func","access_type": "range","possible_keys": ["idx_log_time_normal"],"key": "idx_log_time_normal","used_key_parts": ["log_time"],"key_length": "6","rows_examined_per_scan": 1401,"rows_produced_per_join": 1401,"filtered": "100.00","index_condition": "((`ytt`.`t_func`.`log_time` >= "2019-04-18 00:00:00") and (`ytt`.`t_func`.`log_time` < "2019-04-19 00:00:00"))","cost_info": {"read_cost": "490.61","eval_cost": "140.10","prefix_cost": "630.71","data_read_per_join": "437K"},"used_columns": ["id","rank1","str1","str2","rank2","str3","log_time","cast(`log_time` as date)","(`rank1` + `rank2`)","right(`str3`,9)","substr(`str1`,5,9)","cast(json_unquote(json_extract(`str2`,_utf8mb4"$.name")) as char(9) charset utf8mb4)"]}}}1 row in set, 1 warning (0.00 sec)函式索引mysql> explain format=json SELECT COUNT(*)FROM t_funcWHERE date(log_time) = "2019-04-18"LIMIT 1\G*************************** 1. row ***************************EXPLAIN: {"query_block": {"select_id": 1,"cost_info": {"query_cost": "308.85"},"table": {"table_name": "t_func","access_type": "ref","possible_keys": ["idx_log_time"],"key": "idx_log_time","used_key_parts": ["cast(`log_time` as date)"],"key_length": "4","ref": ["const"],"rows_examined_per_scan": 1401,"rows_produced_per_join": 1401,"filtered": "100.00","cost_info": {"read_cost": "168.75","eval_cost": "140.10","prefix_cost": "308.85","data_read_per_join": "437K"},"used_columns": ["log_time","cast(`log_time` as date)"]}}}1 row in set, 1 warning (0.00 sec)mysql>

從上面的執行計劃看起來區別不是很大， 唯一不同的是，普通索引在CPU的計算上消耗稍微大點，見紅色字型。

當然，有興趣的可以大併發的測試下，我這僅僅作為功能性進行一番演示。