Generated (Virtual) columns in MySQL InnoDB

Generated columns là một tính năng mới của MySQL InnoDB phiên bản 5.7. Có hai loại Generated columns là Virtual (default) và Stored. Virtual có nghĩa là giá trị của column được tính toán khi thực hiện câu truy vấn. Storeed thì ngược lại, giá trị của column được tính toán khi record được ghi vào table. Cả hai loại này đều có thể ràng buộc NOT NULL, nhưng chỉ có Stoered có thể tham gia key tạo index.

1. Virtual columns

Đúng như tên gọi, virtual columns là column ảo, nghĩa là MySQL InnnoDB không lưu trữ dữ liệu cho column ảo đó. Nhưng cột ảo vẫn được mô tả trong hệ thống tables của InnoDB. Chúng cùng xem ví dụ đơn giản về virtual columns sau.

mysql> CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), PRIMARY KEY(a));
Query OK, 0 rows affected (0.01 sec)
 
mysql> INSERT INTO t VALUES (11, 3, default);
Query OK, 1 row affected (0.00 sec)

Ở đây c là column ảo của table t. Xem cấu trúc vật lý của table t ta chỉ thấy table t có 2 user column là a và b, và 2 column ẩn của InnoDB là DATA_TRX_ID và DATA_ROLL_PTR.

not-deleted PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;           /* column 'a' */
 1: len 6; hex 00000000670b; asc     g ;;     /* InnoDB hidden column */
 2: len 7; hex a90000011d0110; asc        ;;  /* InnoDB hidden column */
 3: len 4; hex 80000003; asc     ;;           /* column 'b' */

Như vậy column c không dược lưu trữ cùng các column thực trong InnoDB table. Nó chỉ được tính toán trong quá trình bản ghi dữ liệu được đọc và trả về khi truy vấn.

mysql> SELECT * FROM t;
+----+------+------+
| a  | b    | c    |
+----+------+------+
| 11 |    3 |   14 |
+----+------+------+
1 row in set (0.00 sec)

2. Metadata của Virtual columns

Như đã nói ở trên, dữ liệu của cột ảo không được lưu trữ trong InnoDB, nhưng metadata của nó thì được. Việc lữu trữ metadata của cột ảo sẽ hỗ trợ ta tạo Index cho cột ảo. Metadata của của cột ảo dược lưu ở bảng hệ thống InnoDBSYS_COLUMNS cùng với các cột thực khác.

mysql> 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 |
+----------+------+-------+-------+--------+-----+
3 rows in set (0.01 sec)

Dấu hiệu để nhận biết một cột là cột ảo trong bảng hệ thống của InnoDB là giá trị ở trường POS và MTYPE. Giá trị ở cột POS là giá trị encodes của Số thứ tự cột trong bảng (trường hợp này là 3) với thứ tự cột ảo (trường hợp này là first column)

3. Lợi ích của Virtual columns

Để hiểu lợi ích của virtual columns ta xét ví dụ bảng ontime lưu trữ thông tin các chuyến bay, bảng này có lượng bản ghi dữ liệu rất lớn. Cấu trúc bảng onime như sau:

CREATE TABLE `ontime` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lfight_date` date DEFAULT NULL,
  `carrier` char(2) DEFAULT NULL,
  `origin_airport_id` int(11) DEFAULT NULL,
  `origin_city_name` varchar(100) DEFAULT NULL,
  `origin_state` char(2) DEFAULT NULL,
  `dest_airport_id` int(11) DEFAULT NULL,
  `dest_city_name` varchar(100) DEFAULT NULL,
  `dest_state` char(2) DEFAULT NULL,
  `DepDelayMinutes` int(11) DEFAULT NULL,
  `arr_delay_minutes` int(11) DEFAULT NULL,
  `cancelled` tinyint(4) DEFAULT NULL,
  `cancellation_code` char(1) DEFAULT NULL,
  `diverted` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FlightDate` (`flight_date`)
) ENGINE=InnoDB

Giờ ta cần truy vấn để lấy số lượng chuyến bay các ngày Sunday của năm 2013 group theo carrier.

EXPLAIN SELECT carrier, count(*) FROM ontime_sm
	    WHERE dayofweek(flight_date) = 7 group by carrier
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ontime_sm
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 151253427
        Extra: Using where; Using temporary; Using filesort
Results:
32 rows in set (1 min 57.93 sec)

Vấn đề ở đây là MySQL không hỗ trợ sử dụng index trong trường hợp dùng hàm đối với cột được đánh Index (ở đây cột flight_date được đánh index. Giải pháp là ta tạo virtual column flight_of_week cho bảng trên.

ALTER TABLE ontime ADD flight_day_of_week INT GENERATED ALWAYS AS (dayofweek(FlightDate)) VIRTUAL;

Và giờ thì MySQL có thể sử dụng index trong câu truy vấn.

mysql> EXPLAIN SELECT carrier, count(*) FROM ontime_sm_virtual  WHERE Flight_dayofweek = 7 group by carrier
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ontime_sm_virtual
   partitions: NULL
         type: ref
possible_keys: Flight_dayofweek
          key: Flight_dayofweek
      key_len: 2
          ref: const
         rows: 165409
     filtered: 100.00
        Extra: Using where; Using temporary; Using filesort

Để tổng kết về Virtual columns, sau đây liệt kê những Tính năng và Hạn chế của nó

3.1 Những tính năng của Virtual columns

• Virtual columns không được lưu trong InnoDB row, do vậy nó không làm tăng kích thước của table.
• Thêm mới hay loại bỏ virual columns (cũ) không cần build lại table. Nó chỉ yêu cầu MySQL update metadata trong bảng hệ thống.
• Index của virtual columns một khi được tạo thì nó được lưu ở Index thứ cấp (secondary indexes) chứ không lưu ở primary key (clustered index), do vậy nó không là phình to table.

3.2 Những hạn chế cảu Virtual columns

• Không thể dùng virtual columns làm Primary key hay Forein key.
• Không thể tạo Index cho bảng gồm virtual columns và normal columns.
• Không sử dụng full text search cho virtual columns.