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毫秒以內，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/74697.html
標籤：MySQL