MySQL - Virtual Column

Virtual Columns in InnoDB

INFORMATION_SCHEMA 記錄了一些關於 InnoDB 管理的 schema 物件的 metadata,
這些資訊來自於 InnoDB 內部 system tables, 這些資訊沒辦法跟一般 table 一樣,
可以直接查詢, 傳統上的做法應該是解析 SHOW_ENGINE_INNODB_STATUS 的輸出.
不過 INFORMATION_SCHEMA 這張表有提供介面可以讓你使用 SQL 查詢這類型的資料.

這個跟 Virtual Column 有什麼關係? 
Virtual Column 其實並不會儲存實體資料在 table 的 clustered index 內!
不過 Virtual Column 的 metadata 會存在 InnoDB 的 INFORMATION_SCHEMA  的 SYS_COLUMNS 內！

舉例來說

## 建立一張表 t 有著 a, b, c 三個欄位, c 是 virtual column
CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), PRIMARY KEY(a));

## 
SELECT * FROM t;
+----+------+------+
| a  | b    | c    |
+----+------+------+
| 11 |    3 |   14 |
+----+------+------+

## 從 SYS_COLUMNS 中觀察
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS 
WHERE TABLE_ID IN 
(
  SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
  WHERE NAME LIKE "t%"
);
+----------+------+-------+-------+--------+-----+
| TABLE_ID | NAME | POS   | MTYPE | PRTYPE | LEN |
+----------+------+-------+-------+--------+-----+
|       74 | a    |     0 |     6 |   1283 |   4 |
|       74 | b    |     1 |     6 |   1027 |   4 |
|       74 | c    | 65538 |     6 |   9219 |   4 |
+----------+------+-------+-------+--------+-----+

## 從 INNODB_SYS_VIRTUAL 來看 c 這個欄位
## 由於 c 是由 a, b 欄位產生而成, 所以在 INNODB_SYS_VIRTUAL.BASE_POS 會紀錄
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_VIRTUAL;
+----------+-------+----------+
| TABLE_ID | POS   | BASE_POS |
+----------+-------+----------+
|       74 | 65538 |        0 |
|       74 | 65538 |        1 |
+----------+-------+----------+
## POS 指的就是 c 這個欄位的 POS 值
## BASE_POS 指的就是跟原本 table 哪個 POS 的值所產生 c 欄位
## 依這裡的範例就是 a, b 兩個欄位
## 從上面看就知道 a 欄位的 POS = 0, b 欄位的 POS = 1
## 這樣就知道 c 欄位是由 a, b 欄位所產生的!

Virtual Column 跟其他 columns 在 SYS_COLUMNS 中的差異是在 PRTYPE 有額外的 DATA_VIRTUAL (8192) bit 設定上去。
註:
MTYPE (main type): 6 = INT, 2 = CHAR, 1 = VARCHAR
PRTYPE (precise type): 呈現比較精確的欄位 data type 的二進位的值, character set, 還有是否為 nullable 這些。

Creating Indexes on Non-Materialized Virtual Columns

因為 virtual column 沒有存在 clustered index 內, 每次撈取的時候才計算,
有時候會比較慢與沒有效率, 所以可以針對 virtual columns 建立 secondary index!
所建立的 secondary index 會被實際儲存在 secondary index records 內.

其餘的部分可以參考這篇
https://dev.mysql.com/blog-archive/json-labs-release-effective-functional-indexes-in-innodb/