背景環境準備解決方式冗餘欄位函式索引總結背景

今天有同事諮詢了一個SQL最佳化的問題。他有一個SQL語句執行時間比較長，是在前端頁面中展現一個查詢頁面的SQL。這個頁面中有幾個列支援排序，並且有一個列並不是真實存在資料庫表中的一個欄位，而是透過表中的兩個欄位計算得到的，並且這個計算得到的列也需要支援排序的功能。

針對這樣的一個需求，我想到Oracle資料庫中函式索引，不知道MySQL是否支援函式索引，於是查了一下，果然也有這樣的函式索引。但是，在老版本的MySQL中是不支援的，比如5.6版本中就不支援這個功能。

下面讓我們來看一下，函式索引到底是一個什麼東東。

環境準備

現在有這樣一個表tab，它有3個列，分別是列a、b、c，他們都是int型別的欄位，建表語句如下所示：

drop table if exists tab;create table tab(	a int,  b int,  c int);

現在需要在頁面展示4個欄位，分別是列a、列b、列c、列a+b的和，並且這四個展示的列都需要有排序的功能。如下所示：

解決方式

先說一下同事遇到的這個問題，針對他的這個需求，目前我想到的有兩個方式：

建立冗餘欄位函式索引冗餘欄位

所謂的建立冗餘欄位，就是在原先的表中，增加一個列，這個列就用來儲存透過其他兩個列計算得到的值，直接在插入資料的時候，就計算好這個列的值，然後直接維護到這個列中，這樣在查詢的時候，就不需要再次計算這兩個列的值了，同時，如果查詢效率的問題，可以在這個列上面增加索引。

針對我們上面準備的環境，表tab的結構需要改為如下的結構，增加一個列d，同時在插入資料的時候，需要在維護d列的值，這個列的值再插入的時候就計算好，由a+b得到的結果作為d列的值插入到表tab中。列d是一個真實存在的物理欄位。

drop table if exists tab;create table tab(	a int,  b int,  c int,  d int);/*插入資料，此時手動的維護了列d的值。*/insert into tab(a, b, c, d) values(1,1,1,2);insert into tab(a, b, c, d) values(2,2,2,4);insert into tab(a, b, c, d) values(3,3,3,6);

如果我們的資料量比較大，需要提高查詢效率，我們還可以基於這個列d增加索引，如下就是增加索引的SQL語句。

alter table tab add index idx_tab_d(d) using btree;

函式索引

這種實現方式，是我們具體要探討的實現方式。我們的目的是為了在頁面展示一個列，但是這個列的值透過其他兩個或多個列的值計算得到的。並且想對這個列增加上索引，以便於提高查詢的效率，這樣的一個訴求，我們是沒有辦法直接在這個計算得到的列上面增加索引的，因為這個列是不存在的。

MySQL中提供了一種叫做Generated Column的概念。它可以分為兩種型別。

Virtual：虛擬列，這個列是虛擬的，它不佔用任何儲存空間，它的值，是在讀取表中的資料行的時候，根據定義的計算公式，動態的計算出來的。Stored：儲存列，這個列是真實的存在磁碟上的，它會佔用儲存空間，它的值，是在插入資料的時候根據公式計算出來的，所以在讀取的時候，不需要再次計算這個列的值，直接從磁碟上讀取出來返回給客戶端。

以上兩種型別的Generated Column的列，他們在向表中插入資料的時候，都不需要手動的維護這個列的值，只要你維護了這個列公式中用到的列的值之後，這個列的值，就會根據計算好的公式，自行的計算這個列的值到底是多少。一個是在資料行真正被讀取到的時候，才去執行公式計算得到值，一個是在插入資料行的時候，就計算出了具體的值，然後儲存在磁碟中。

根據上面準備的環境，此時的表tab，需要改為如下結構：

drop table if exists tab;create table tab(	a int,  b int,  c int,  d int as (a + b));

插入資料的時候，使用下面的SQL，從SQL語句中可以看出列d的值並沒有在插入的時候維護，但是當我們查詢表中的資料的時候，列d的值是可以查詢出來的。

insert into tab(a, b, c) values(1,1,1);insert into tab(a, b, c) values(2,2,2);insert into tab(a, b, c) values(3,3,3);

實驗過程如下所示：

在上面的建立表的語句中，我們沒有指定列d的型別到底是virtual還是stored，但是它的型別是virtual型別，也就是說當我們定義一個Generated Column列的時候，如果不顯示的指定是stored型別的，那麼它預設就是virtual型別。

如果我們要顯示的宣告一個stored型別的列，請檢視如下的SQL語句：

drop table if exists tab;create table tab(	a int,  b int,  c int,  d int as (a + b) stored);insert into tab(a, b, c) values(1,1,1);insert into tab(a, b, c) values(2,2,2);insert into tab(a, b, c) values(3,3,3);

實驗過程如下所示：

對於上面，我們建立的列d，為了提高查詢的效率，我們也可以在這個列上面建立索引，此時的索引就類似於Oracle中的函式索引，因為這個列d的值是透過其他列計算而來，這個計算的公式是一個函式表示式，上面的例子中，我們使用的是加法，它支援各種函式表示式，像MySQL中sum、avg、concat、max、min等都支援。可以根據自己的業務需求自行更換。然後我們在基於這個列，建立了一個索引。建立索引的語句如下：

mysql> alter table tab add index idx_tab_d(d) using btree;Query OK, 0 rows affected (0.06 sec)Records: 0  Duplicates: 0  Warnings: 0mysql>

總結

以上就是MySQL中函式索引的使用示例。對於Virtual Generated Column，MySQL只是將這一列的元資訊儲存在資料字典中，並不會將這一列資料持久化到磁碟上，因此，在MySQL的Virtual Generated Column上建立索引和Oracle的函式索引類似，並不需要更多的代價，只是使用方式有點不一樣而已。