Tag: index

看到 Percona 的「Using ProxySQL and VIRTUAL Columns to Solve ORM Issues」這篇後去找 VIRTUAL 的資料，發現其實以前就寫過了，而且是兩年前寫的了：「MySQL 5.7 的 JSON、Virtual Column 以及 Index」。

在 2NF 的規範中會禁止資料的重複性以及可推導性。以這樣的資料結構開始：

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 有 constraint 與 function 計算可以擋下，但對應到 MySQL 的 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 與 MySQL 端做出哪些改變讓效能變好。

應該是有不少情境可以用，再多想看看好了...