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

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的計算上消耗稍微大點，見紅色字型。

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

構建索引是一個長期的過程，需要不斷重構，而達到最優，常見索引有單值索引，唯一索引，複合索引能滿足很多場景的需求。如果是MySQL資料庫，配合explain+SQL語句可以看到MySQL認為最優的執行計劃，從而發現效能的瓶頸，如果是Oracle可以使用PL/SQL等工具檢視執行計劃，兩者基本大同小異，具體問題，具體分析哈

這個問題可以看下，我已經發表的一篇文章，裡面講到如何設計高效能的索引

https://www.toutiao.com/i6637764918164587021/

第一步、

肯定要從業務背景開始出發啦。

比如業務資訊是需要一個產品表，裡面需要儲存產品名稱，產品庫存，產品價格，產品分類，是否顯示，備註資訊。

轉換成sql程式碼：

CREATE TABLE `product` (

`id` bigint(0) UNSIGNED NOT NULL AUTO_INCREMENT,

`productName` varchar(64) NULL,

`numCount` int(10) NULL,

`price` decimal(10, 2) UNSIGNED NULL,

`productClass` varchar(32) NULL,

`isShow` int(1) UNSIGNED NULL DEFAULT 1 COMMENT "0隱藏，1顯示",

`remark` varchar(255) NULL,

PRIMARY KEY (`id`),

INDEX `web_show`(`productClass`, `isShow`),

INDEX `web_search`(`productName`, `numCount`, `Price`, `productClass`, `isShow`)

);

首先，web_show這個索引代表著可能頁面會根據產品分類和是否顯示進行分類查詢，所以建立一個索引。

web_search 則代表在產品搜尋頁面，透過productName和numCount還有Price和productClass進行篩選，原則上isShow代表是否顯示預設都需要的。

第二步、

若後期業務增加，或執行sql的方式不一樣，透過desc select ****方式，來檢視SQL執行過程，透過執行過程在對應地建立索引即可。

另外索引並非越多越好,需要合適地建立

之前寫過一篇關於針對開發人員資料庫最佳化的文章，索引也是其中之一，那麼今天就針對Mysql索引講幾點。

索引的型別及什麼時候建立索引

說到MySQL的索引，大多數時候都是指B-Tree索引，M ySQL大部分引擎都是支援B-Tree索引的。B-Tree索引適用於全鍵值、範圍、字首的查詢；

主鍵、外來鍵必須有索引，當然很多系統都是邏輯外來鍵（或需要經常和其他表關聯），也需要建立索引；經常出現在where、order by、group by中的欄位；儘量把索引建立到小欄位上；對於文字欄位或者很長欄位，不要建索引；複合索引，文章第二部分再說明；

雜湊索引，是基於雜湊表，精確匹配索引所有列的查詢才有效；只有Memory引擎支援。

全文索引、聚簇索引、聚簇索引等等，就不詳細說了，因為...我也不太會，下面還是主要說B-Tree索引（後來說的索引，都是指B-Tree）。

聯合索引的限制

很多同學都喜歡給多個欄位建立聯合索引，那麼建立聯合索引需要注意些什麼呢：

索引的最左原則，如果不是按索引的最左列查詢，那麼將無法使用索引。最左原則：如果建立了一個聯合索引（name,age,gender）,相當於建立了三個索引（name）、（name,age）、（name,age,gender）。

聯合索引，左邊的列有範圍查詢，那麼右邊的列無法使用索引。比如index（age,gender），where age > 20 and gender = "M"；這時候就會有問題。解決辦法也很簡單，兩個欄位分別建立索引。

索引的一些小技巧前導模糊查詢，會導致索引失效：where name like "%三豐"；資料區分度不大，不建議使用索引：where gender = "M"；性別只有男、女、未知三種；等號左邊有函式，會索引失效：where LENGTH(col1) = 10；隱式轉換的問題：where col2 = "100"，col2列是數字，等號左右型別不一致，col2會隱式轉換成字串；儘量不好使用負向查詢，例如：!=、not in、not exists；索引不是越多越好。

索引應該是各種資料庫最佳化方案之中成本最低，見效最快的解決方案了，之前也發表過幾篇關於索引原理的文章，這次就重點講下：企業級應用如何構建高效索引，以及應該注意些什麼。

索引型別1、B-tree索引

Myisam和innodb中，預設用B-tree索引，是一種平衡樹。可以抽象一下---B-tree系統，可理解為"排好序的快速查詢結構”

2、hash索引

在memory表裡，預設是hash索引， hash的理論查詢時間複雜度為O(1)

既然hash的查詢如此高效,為什麼不都用hash索引？

1:hash函式計算後的結果，是隨機的，沒有辦法對範圍查詢進行最佳化.

2: 無法利用字首索引. 比如 在btree中, field列的值“hellopworld”,並加索引

查詢 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引. (左字首索引)

因為hash(‘helloword’),和hash(‘hello’),兩者的關係仍為隨機

3: 排序也無法最佳化.

4: 必須回行。就是說透過索引拿到資料位置，必須回到表中取資料

以下是建立索引時的注意事項索引不是越多越好，單張表中索引數量不宜超過8個合理建立聯合索引，(a，b，c) 相當於 (a) 、(a，b) 、(a，b，c)禁⽌冗餘索引

索引是雙刃劍，會增加維護負擔，增⼤大IO壓⼒力。(a,b,c)、(a,b)，後者為冗餘索引。可以利⽤用字首索引來達到加速的目的，減輕維護負擔

SELECT只獲取必要的欄位，不建議使⽤ SELECT *

好處：減少網路頻寬消耗；能有效利用覆蓋索引，表結構變更對程式基本⽆無影響

合理使⽤用覆蓋索引減少IO，避免排序

索引覆蓋是指如果查詢的列恰好是索引的一部分,那麼查詢只需要在索引檔案上進行,不需要回行到磁碟再找資料。這種查詢速度非常快，稱為"索引覆蓋”

理想的索引：1:查詢頻繁 2:區分度高 3:長度小 4: 儘量能覆蓋常用查詢欄位

注：主鍵查詢是天然的覆蓋索引

儘量避免用NULL()

原因: NULL不利於索引，要用特殊的位元組來標註。在磁碟上佔據的空間其實更大。

索引的主鍵值，應儘量是連續增長的值，而不是要是隨機值(不要用隨機字串或UUID)，否則會造成大量的頁分裂與頁移動

不對過⻓的VARCHAR欄位建⽴立索引。建議優先考慮字首索引，或新增CRC32或MD5偽列並建⽴立索引

在常用的列上加上適當索引

例: where cat_id=3 and price>100 ; //查詢第3個欄目,100元以上的商品

誤: cat_id上,和, price上都加上索引.

錯: 只能用上cat_id或Price索引,因為是獨立的索引,同時只能用上1個

多列索引上,索引發揮作用,需要滿足左字首要求

以 index(a,b,c) 為例

建立高效能索引

選擇正確的索引順序

在選擇索引的順序的時候有一個原則：將索引選擇性最高的列放在左側，同時索引的順序要與查詢索引的順序一致，並且要兼顧考慮排序和分組的需要。在一個多列B樹多列中索引的順序意味著索引首先按照最左側的列進行排序，其次是第二列。所以無論是where語句還是order by語句都需要儘量滿足這個順序，這樣才能更好的使用索引。

索引的選擇性

列的選擇性高的含義是透過這一列能夠更多的過濾掉無用的資料，舉個極端的例子，如果把自增id建成索引那麼它的選擇性是最高的，因為會把無用的資料都過濾掉，只會剩下一條有效資料。我們可以透過下面的方式來簡單衡量某一個列的選擇性：

select count(distinct columnA)/count(*)as selectivity from table

當上面的資料越大的時候意味著columnA的選擇性越高。這種方式提供了一個衡量平均選擇性的辦法，但是也不一定是有效的，需要具體情況具體分析。

字首索引

當遇到特別長的列，但又必須要建立索引的時候可以考慮建立字首索引。字首索引的含義是把某一列的前N個字元作為索引，建立字首索引的方式如下：

alter table test add key(columnA(5));

上面這個語句就是columnA的前5個字元建為字首索引。字首索引是一種使索引更小、更快的有效辦法。但是字首所有有一個缺點：MySQL無法使用字首索引來做order by和group by，也無法使用字首索引做覆蓋掃描。

聚簇索引和非聚簇索引

聚簇索引

聚簇索引代表一種資料的儲存方式，表示同一個結構中儲存了B-Tree索引和資料行。也就是說當建立聚簇索引的時候實際的資料行存放在索引的葉子節點上。這也決定了每個表只能有一個聚簇索引。

聚簇索引組織資料的方式如下圖所示：

從圖中可以看到索引的葉子節點和資料行是存放在一起的，這樣的好處是可以直接讀取到資料行。在建立表的時候如果我們不顯式指定聚簇索引，那麼MySQL將會按照下面的邏輯來選擇聚簇索引：首先會透過主鍵列來聚集資料，如果沒有主鍵列那麼會選擇唯一的非空索引來替代。如果還沒有這樣的索引那麼會隱式的建立一個主鍵列來作為聚簇索引。

聚簇索引優點：

1、相關資料存放在一起，檢索的時候降低IO的次數

2、資料訪問更快

3、使用覆蓋索引掃描的查詢可以直接使用節點中的主鍵值

在使用上面的優點的時候聚簇索引也有一定的缺點：

1、聚簇索引將資料聚集在一起限制了插入速度，插入速度比較依賴於主鍵的順序

2、更新索引的時候代價會變高

3、二級索引的訪問的時候需要查詢兩次

非聚簇索引

非聚簇索引通常被稱為二級索引，與聚簇索引的不同在於，非聚簇索引的葉子節點存放的是資料的行指標或者是一個主鍵值。這樣在查詢資料的時候首先定位到葉子節點上的主鍵值(或者行指標)，然後透過主鍵值再到聚簇索引中查詢到對應的資料。從中我們可以看到對於非聚簇索引的查詢需要走兩次索引。下圖是一個非聚簇索引：

這個索引是InnoDB中的耳機索引，葉子節點中儲存的是索引和主鍵。對於MyISAM葉子節點儲存的是索引和行指標。

覆蓋索引

如果一個索引包含或者說覆蓋所有需要查詢的欄位的值，那麼就稱為覆蓋索引。覆蓋索引可以極大的提高查詢的效率，如果我們的查詢中只查詢索引，而不用去回表那應該最好不過了。

通常我們使用explain關鍵字來檢視一個查詢語句的執行計劃，透過執行計劃我們可以瞭解到查詢的細節。如果是覆蓋索引，我們會看到執行計劃的Extra列裡有"Using Index"的資訊。在查詢語句中一般我們希望是where條件中的語句儘量能被覆蓋，並且順序要跟索引的保持一致。還有一個需要注意的點是MySQL不能在索引中使用like操作，這樣會導致後面的索引失效。