MySQL自古以來就不提供函數(shù)索引這么復(fù)雜的功能。那怎么在MySQL里面實現(xiàn)這樣的功能呢？ 我們先來看看函數(shù)索引的概念。函數(shù)索引，也可稱為表達式索引，也就是基于字段以特定函數(shù)（表達式）建立索引來提升查詢性能之需。函數(shù)索引的優(yōu)勢在于更加精確的獲取所需要的數(shù)據(jù)。

MySQL 5.7提供了一個新的特性，虛擬列，可以很完美的解決這個問題。

在介紹虛擬列之前，我們來看看在MySQL里面普通索引的范例。

示例表結(jié)構(gòu)：

CREATE?TABLE?t1?(id?INT?,rank?INT,?log_time?DATETIME,?nickname?VARCHAR(64))?ENGINE?INNODB; ALTER?TABLE?t1?ADD?PRIMARY?KEY?(id),?ADD?KEY?idx_rank?(rank),ADD?KEY?idx_log_time?(log_time);

示例表數(shù)據(jù)量,這里我增加了5000條記錄：

mysql>?select?count(*)?from?t1; +----------+ |?count(*)?| +----------+ |?????5000?| +----------+ 1?row?in?set?(0.00?sec)

假設(shè)我們來檢索2015年4月9號的數(shù)據(jù)。（結(jié)果是有兩條記錄，id 分別為95和3423。）

mysql>?SELECT?*?FROM?t1?WHERE?DATE(log_time)?=?'2015-04-09'\G ***************************?1.?row?***************************id:?95rank:?24 log_time:?2015-04-09?05:53:13 nickname:?test ***************************?2.?row?***************************id:?3423rank:?42 log_time:?2015-04-09?02:55:38 nickname:?test 2?rows?in?set?(0.01?sec)

下來我們看看這條語句的查詢計劃。

mysql>?explain?SELECT?*?FROM?t1?WHERE?DATE(log_time)?=?'2015-04-09'\G ***************************?1.?row?***************************id:?1select_type:?SIMPLEtable:?t1partitions:?NULLtype:?ALL possible_keys:?NULLkey:?NULLkey_len:?NULLref:?NULLrows:?5000filtered:?100.00Extra:?Using?where 1?row?in?set,?1?warning?(0.00?sec)

我們發(fā)現(xiàn)TYPE是ALL，掃描的函數(shù)是5000，也就是說這條語句進行了一個全表掃描。 雖然給字段log_time 加了索引，但是沒有用到，那這個時候怎么辦？

在MySQL里面一般這樣修改：

mysql>?SELECT?*?FROM?t1?WHERE?log_time?>=?'2015-04-09?00:00:00'?AND?log_time?<='2015-04-10?00:00:00'\G ***************************?1.?row?***************************id:?3423rank:?42 log_time:?2015-04-09?02:55:38 nickname:?test ***************************?2.?row?***************************id:?95rank:?24 log_time:?2015-04-09?05:53:13 nickname:?test 2?rows?in?set?(0.00?sec)

通過查詢結(jié)果，發(fā)現(xiàn)結(jié)果集一致，那再來看看查詢計劃

mysql>?explain?SELECT?*?FROM?t1?WHERE?log_time?>=?'2015-04-09?00:00:00'?AND?log_time?<=?'2015-04-10?00:00:00'\G ***************************?1.?row?***************************id:?1select_type:?SIMPLEtable:?t1partitions:?NULLtype:?range possible_keys:?idx_log_timekey:?idx_log_timekey_len:?6ref:?NULLrows:?2filtered:?100.00Extra:?Using?index?condition 1?row?in?set,?1?warning?(0.00?sec)

可以看到這條修改過的語句很好的利用到了idx_log_time這條索引。

那好，這個是之前在MySQL 5.6以及之前的舊版本解決方法，隨著MySQL 5.7的發(fā)布，虛擬列的出現(xiàn)讓這個問題更加簡單。

?現(xiàn)在修改下之前的表結(jié)構(gòu)：

ALTER?TABLE?t1?ADD?COLUMN?log_date??DATE?AS?(DATE(log_Time))?stored,?ADD?KEY?idx_log_date?(log_date);

這樣，增加了一新列，用來存放date(log_time)這個表達式，并且給他加了一列索引。

那么，之前的語句就變成如下：

mysql>?SELECT?*?FROM?t1?WHERE?log_date?=?'2015-04-09'\G ***************************?1.?row?***************************id:?95rank:?24 log_time:?2015-04-09?05:53:13 nickname:?test log_date:?2015-04-09 ***************************?2.?row?***************************id:?3423rank:?42 log_time:?2015-04-09?02:55:38 nickname:?test log_date:?2015-04-09 2?rows?in?set?(0.00?sec)

執(zhí)行后結(jié)果集和之前的一致。

我們來看看查詢計劃，發(fā)現(xiàn)很好的利用了idx_log_date索引列。

mysql>?explain??SELECT?*?FROM?t1?WHERE?log_date?=?'2015-04-09'\G ***************************?1.?row?***************************id:?1select_type:?SIMPLEtable:?t1partitions:?NULLtype:?ref possible_keys:?idx_log_datekey:?idx_log_datekey_len:?4ref:?constrows:?2filtered:?100.00Extra:?NULL 1?row?in?set,?1?warning?(0.00?sec)

通過以上介紹，我們看到虛擬列實現(xiàn)起來相對之前的方法來的容易的多。但是這里筆者還是得說上幾句。

函數(shù)索引的用法以及SQL語句雖然寫起來簡單，但是在大部分場合下，只能說不得已而為之，是一種設(shè)計上的缺陷，后期增加了運維人員的運維難度以及繁瑣度。這也就是為什么MySQL 直到5.7才推出了這項類似的功能的原因。

總結(jié)

以上是生活随笔為你收集整理的【原创】MySQL5.7 虚拟列实现表达式索引的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。