MySQL中的函數索引（Generated Column）及一次SQL優化

MySQL 中是沒有 Oracle 的函數索引功能的，把 MySQL 的 Generated Column 稱為“函數索引”並不准確，但可以和函數索引達到同樣的效果，也有人把這個特性稱為“衍生列”。

Generated Column 是什麼

Generated Column 的值是根據其定義的表達式所計算而來的，下麵使用官方文檔中的例子做個簡單介紹。

有一張表存儲直角三角形的三條邊長，大家都知道，根據直角三角形的邊長公式，斜邊的長度可以通過另外兩條邊長計算得到，這樣就可以在表中只存儲兩條直角邊，而斜邊通過 Generated Column 定義，創建這張表並插入一條數據：

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);

sidea 和 sideb 是兩條直角邊，sidec 是斜邊，insert 時只需要插入兩條直角邊，也就是說 Generated Column 不能人為操作（插入、更新、刪除），會自動根據其定義表達式計算得到。

查詢這張表：

mysql> SELECT * FROM triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|     1 |     1 | 1.4142135623730951 |
|     3 |     4 |                  5 |
|     6 |     8 |                 10 |
+-------+-------+--------------------+

Generated Column 定義語法

Generated Column 的定義語法如下：

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

關鍵字“AS”指明瞭這個欄位是衍生的，是 Generated Column，AS 後面就是用以計算的表達式。GENERATED ALWAYS 使定義更明確，可以省略。

VIRTUAL 和 STORED 是 Generated Column 的兩種類型，指明該欄位的值如何存儲：

• VIRTUAL: Virtual Generated Column 的值不會持久化到磁碟，只保存在數據字典中（表的元數據），每次讀取時在 BEFORE 觸發器後就會立即計算。
  
• STORED：Stored Generated Column 的值會持久化到磁碟上，而不是每次讀取時計算。

如果不指明的話，MySQL 會預設以 VIRTUAL 的形式實現，STORED 需要更多的磁碟空間，性能也沒有明顯的優勢，所以一般使用 VIRTUAL。

Generated Column 定義要點

• 一般情況下，Generated Column 可以使用內置函數及操作符定義。如果給定相同的數據，多次調用會產生相同的結果，這樣的定義是明確被允許的。否則，定義會失敗，例如使用 NOW()、CURRENT_USER()、CONNECTION_ID(）的定義會失敗。
  
• 自定義的函數和存儲過程，不允許使用。
  
• 變數，例如系統變數、自定義變數等不允許使用。
  
• 子查詢不允許使用。
  
• Generated Column 的定義中可以依賴其他 Generated Column 欄位，但所依賴的衍生欄位必須定義在它的前面。如果只依賴非衍生欄位，則定義順序沒有要求。
  
• 自增長 AUTO_INCREMENT 不允許使用。
  
• 自增長的列，不能用到 Generated Column 的定義中。
  
• 從 MySQL 5.7.10 開始，如果表達式計算導致截斷或給函數提供了不正確的輸入，則create table語句將終止，並返回DDL操作。

一次SQL優化

通過慢查詢日誌找到一條慢SQL，執行計劃如下：

mysql> EXPLAIN
SELECT
    c.id,
    b.customer_status
FROM
    t_core_customer c
    INNER JOIN t_core_customer_bizinfo b ON c.id = b.customer_id AND b.biz_id = 'maintain' 
WHERE
    REPLACE ( REPLACE ( c.customer_name, '（', '(' ), '）', ')' ) = '天津買斯扣科技有限公司';
+----+-------------+-------+------------+--------+----------------------------------+---------+---------+--------------------------------+---------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys                    | key     | key_len | ref                            | rows    | filtered | Extra       |
+----+-------------+-------+------------+--------+----------------------------------+---------+---------+--------------------------------+---------+----------+-------------+
|  1 | SIMPLE      | b     | NULL       | ALL    | idx_core_customer_bizinfo_cidbid | NULL    | NULL    | NULL                           | 1263918 |    10.00 | Using where |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY                          | PRIMARY | 110     | b.customer_id                  |       1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+----------------------------------+---------+---------+--------------------------------+---------+----------+-------------+
2 rows in set (0.05 sec)

客戶表中有117萬行數據，這條SQL執行耗時4秒多，通過執行計劃可以看到，客戶表沒有走索引而進行全表掃描，customer_name 欄位的索引由於 replace 函數沒有被利用到。

增加 Generated Column ：

ALTER TABLE `t_core_customer` 
ADD COLUMN `customer_name_replaced` varchar(200)  AS (REPLACE(REPLACE(customer_name, '（', '(' ), '）', ')' )); 

創建索引：

ALTER TABLE `t_core_customer` 
ADD INDEX `customer_name_replaced`(`customer_name_replaced`) USING BTREE;

優化後再看執行計劃：

mysql> EXPLAIN
SELECT
    c.id,
    b.customer_status
FROM
    t_core_customer c
    INNER JOIN t_core_customer_bizinfo b ON c.id = b.customer_id AND b.biz_id = 'maintain' 
WHERE
    REPLACE ( REPLACE ( c.customer_name, '（', '(' ), '）', ')' ) = '天津買斯扣科技有限公司';
+----+-------------+-------+------------+------+----------------------------------+----------------------------------+---------+-----------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys                    | key                              | key_len | ref                         | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------------------------+----------------------------------+---------+-----------------------------+------+----------+-------+
|  1 | SIMPLE      | c     | NULL       | ref  | PRIMARY,customer_name_replaced   | customer_name_replaced           | 603     | const                       |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | b     | NULL       | ref  | idx_core_customer_bizinfo_cidbid | idx_core_customer_bizinfo_cidbid | 222     | c.id,const                  |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------------------------+----------------------------------+---------+-----------------------------+------+----------+-------+
2 rows in set (0.40 sec)

執行計劃正常，利用了索引，SQL耗時到了10毫秒以內。