MySQL 5.7 的 JSON、Virtual Column 以及 Index

Percona 提到了 MySQL 5.7 的 JSON 與 virtual column,再加上 index 後的效能提昇:「JSON document fast lookup with MySQL 5.7」。

每一家都把這些功能給做出來了,在 MySQL 5.7 提供了 JSON 格式:

CREATE TABLE `test_features` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feature` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

而你可以對 JSON 欄位運算,拉出資料後產生出 virtual column:

ALTER TABLE test_features ADD COLUMN street VARCHAR(30) GENERATED ALWAYS AS (json_unquote(json_extract(`feature`,'$.properties.STREET'))) VIRTUAL;

然後就可以對 virtual column 下 index:

ALTER TABLE test_features ADD KEY `street` (`street`);

接著對 virtual column 查詢的速度就會超快:

SELECT count(*) FROM test_features WHERE street = 'BEACH';

其實不一定要 JSON,光是 virtual column 與 index 就可以解決老問題:

已經有使用者所在的國家,想要快速查詢使用者是住在哪個洲 (亞洲、歐洲、美洲、...)。

以前是另外拆出一個欄位來做,用 trigger 更新確保資料正確性後,再對拆出來的欄位下 index。現在可以用 virtual column 建立出來下 index。

One thought on “MySQL 5.7 的 JSON、Virtual Column 以及 Index”

Leave a Reply

Your email address will not be published. Required fields are marked *