看到 Percona https://www.percona.com/ 的 Using ProxySQL and VIRTUAL Columns to Solve ORM Issues https://www.percona.com/blog/2018/03/29/using-proxysql-and-virtual-columns-to-solve-orm-issues/ 這篇後去找 VIRTUAL 的資料, 發現其實以前就寫過了, 而且是兩年前寫的了: MySQL 5.7 的 JSON,Virtual Column 以及 Index https://blog.gslin.org/archives/2016/03/09/6406/mysql-5-7-的-json、virtual-column-以及-index/ .
在 2NF https://en.wikipedia.org/wiki/Second_normal_form 的規範中會禁止資料的重複性以及可推導性. 以這樣的資料結構開始:
- CREATE TABLE t1 (
- id INT PRIMARY KEY AUTO_INCREMENT,
birth DATE
);
與後者這樣延伸出來的資料結構:
- CREATE TABLE t2 (
- id INT PRIMARY KEY AUTO_INCREMENT,
birth DATE,
- year INT,
- month INT,
- day INT
- );
其中 t2 裡的 year , month , day 都可以被 birth 推導, 這就卡到 2NF... 會有 t2 這樣的資料結構通常都是因為效能而需要的設計.
像是
SELECT * FROM t1 WHERE MONTH(birth) = 12;
這樣的 SQL query, 即使在 birth 加上 index 也沒用, 因為查詢條件不是某個連續的區間. 另外建出 month 欄位, 再對 month 建立 index 後,
SELECT * FROM t2 WHERE month = 12;
才能利用這組 index 提昇效能.
但後者的設計會導致兩個問題, 一個是空間的增加, 另外一個是資料一致性管理的成本.
空間的增加還蠻好解釋的, 來自於多了 year , month , day 這些欄位要儲存. 而資料一致性管理的成本是因為你沒有強制性的方式讓 year , month , day 的值與 birth 的內容一致, 也就是資料庫內有可能會有 birth 是 2018-01-01 , 但 month 裡卻是 2 之類的數字.
一致性在 PostgreSQL https://www.postgresql.org/ 有 constraint 與 function 計算可以擋下, 但對應到 MySQL https://dev.mysql.com/ 的 constraint 就沒辦法用 function 判斷條件, 變成需要在 MySQL 外的地方 workaround 確保一致性...
而這次標題提到的 VIRTUAL column 算是 MySQL 5.7 推出來解這個問題的想法, 我們可以這樣設計資料結構:
- CREATE TABLE t3 (
- id INT PRIMARY KEY AUTO_INCREMENT,
birth DATE,
- year INT AS (YEAR(birth)) VIRTUAL,
- month INT AS (MONTH(birth)) VIRTUAL,
- day INT AS (DAY(birth)) VIRTUAL
- );
然後對 month 建立 index:
ALTER TABLE t3 ADD INDEX idx__month (month);
接著塞資料進去測試:
- INSERT INTO t3 (birth) VALUES ('2018-01-02');
- INSERT INTO t3 (birth) VALUES ('2018-01-03');
拉資料可以看到, 雖然塞資料進去時沒有指定 year , month , day , 但拉資料時會計算出來:
- mysql> SELECT * FROM t3;
- +----+------------+------+-------+------+
- | id | birth | year | month | day |
- +----+------------+------+-------+------+
- | 1 | 2018-01-02 | 2018 | 1 | 2 |
- | 2 | 2018-01-03 | 2018 | 1 | 3 |
- +----+------------+------+-------+------+
- 2 rows in set (0.00 sec)
也可以看到 VIRTUAL column 的唯讀特性:
- mysql> INSERT INTO t3 (year) VALUES (2018);
- ERROR 3105 (HY000): The value specified for generated column 'year' in table 't3' is not allowed.
當你資料量夠多時, 可以用 EXPLAIN 看 MySQL 的 optimizer 會使用哪個 index (太少的時候會 table scan...):
- mysql> EXPLAIN SELECT * FROM t3 WHERE month = 2 G
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: t3
- partitions: NULL
- type: ref
- possible_keys: idx__month
- key: idx__month
- key_len: 5
- ref: const
- rows: 4
- filtered: 100.00
- Extra: NULL
- 1 row in set, 1 warning (0.00 sec)
在這個例子裡用的欄位比較簡單, 但如果在更複雜的案例裡面, 應該會有更多地方可以發揮 (因為可以用 function 計算, 這使得很多可能性跑出來), 像是 Percona 的原文是以 application 沒辦法修改程式碼的前提下, 可以在 ProxySQL http://www.proxysql.com/ 與 MySQL 端做出哪些改變讓效能變好.
應該是有不少情境可以用, 再多想看看好了...
来源: https://www.thinksaas.cn/group/topic/839463/