MySQL 5.7.8からInnoDBのgenerated columnは実体を取らずにインデックスを作れるようになった

MySQL 5.7.6から入ったgenerated column、STOREDで作るとデータ領域に計算後の結果が格納されてインデックスも貼れるようになるというシロモノだったのが、5.7.8ではVIRTUALで作るとデータ領域には計算後の結果が格納されないけど *インデックスは計算後の値を使って貼れるようになった* 

日々の覚書: MySQL 5.7.6のgenerated columnは関数インデックスの夢を見るか

InnoDB: InnoDB now supports secondary indexes on virtual generated columns. A secondary index on a virtual generated column stores the column's generated values within the records of the index. Such indexes can be scanned and searched more efficiently than virtual generated columns, which are computed “on the fly” when rows are read.

MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.8 (Not yet released)


ただしInnoDB限定でMyISAMとかはダメ。まあ、いまさら、MyISAMさんにそんな変更入ってもしょうがないので全然かまわないけども。


まずテスト用のデータを作って入れておく。

$ perl -M"Digest::MD5 qw/md5_hex/" -e 'for (my $n= 1; $n <= 100000; $n++) {printf("%d\t%s\n", $n, md5_hex($n));}' > /var/lib/mysql-files/md5

mysql> CREATE TABLE t1 (num int unsigned PRIMARY KEY, md5 varchar(32) NOT NULL, KEY (md5));
Query OK, 0 rows affected (0.01 sec)

mysql> LOAD DATA INFILE '/var/lib/mysql-files/md5' INTO TABLE t1;
Query OK, 100000 rows affected (0.89 sec)
Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0

手抜き :)
この状態でinnodb_rubyを使ってテーブルスペースの様子を見てみると、

mysql> SELECT tbl.name, idx.name, idx.index_id FROM INNODB_SYS_INDEXES AS idx INNER JOIN INNODB_SYS_TABLES AS tbl USING(table_id) WHERE tbl.name= 'd1/t1';
+-------+---------+----------+
| name  | name    | index_id |
+-------+---------+----------+
| d1/t1 | PRIMARY |       87 |
| d1/t1 | md5     |       88 |
+-------+---------+----------+
2 rows in set (0.00 sec)

もちろんこう、PRIMARY(InnoDBなので行のデータそのものはここに格納されいてる)とmd5というセカンダリーキーが詰められる。

ここに、まずは5.7.7でもできたようにSTOREDな生成列を作ってみる。

mysql> ALTER TABLE t1 ADD stored_sha256 varchar(64) AS (sha2(num, 256)) STORED;
Query OK, 100000 rows affected (1.33 sec)
Records: 100000  Duplicates: 0  Warnings: 0

STOREDな生成列は演算した結果をデータ領域に書き込むので、Index 90(=md5)のページは変わらず406ページに対してIndex 89(=PRIMARY)は367 => 802ページに増えている。
そしてこれにインデックスを貼ると

mysql> ALTER TABLE t1 ADD KEY (stored_sha256);
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT tbl.name, idx.name, idx.index_id FROM information_schema.INNODB_SYS_INDEXES AS idx INNER JOIN information_schema.INNODB_SYS_TABLES AS tbl USING(table_id) WHERE tbl.name= 'd1/t1';
+-------+---------------+----------+
| name  | name          | index_id |
+-------+---------------+----------+
| d1/t1 | PRIMARY       |       89 |
| d1/t1 | md5           |       90 |
| d1/t1 | stored_sha256 |       91 |
+-------+---------------+----------+
3 rows in set (0.00 sec)

Index 89(=PRIMARY)にはページ数も配置も変化がなく、Index 91(=stored_sha256)が足される。470ページ。

これに対してVIRTUALな生成列は

mysql> ALTER TABLE t1 ADD virtual_sha256 varchar(64) AS (sha2(num, 256)) VIRTUAL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

カラムを追加しただけでは全く変化がなく、

mysql> ALTER TABLE t1 ADD KEY (virtual_sha256);
Query OK, 0 rows affected (0.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT tbl.name, idx.name, idx.index_id FROM information_schema.INNODB_SYS_INDEXES AS idx INNER JOIN information_schema.INNODB_SYS_TABLES AS tbl USING(table_id) WHERE tbl.name= 'd1/t1';
+-------+----------------+----------+
| name  | name           | index_id |
+-------+----------------+----------+
| d1/t1 | PRIMARY        |       89 |
| d1/t1 | md5            |       90 |
| d1/t1 | stored_sha256  |       91 |
| d1/t1 | virtual_sha256 |       92 |
+-------+----------------+----------+
4 rows in set (0.00 sec)

ちょっと画面の上切れてる(´･ω･｀)
ながら、Index 89(=PRIMARY)はgenerated columnを作る前から変わりがなく(=容量を食わずに) Index 92(=virtual_sha256)が追加されている。


もちろんこれインデックスとして既に固定化されているので、

mysql> EXPLAIN SELECT virtual_sha256 FROM t1 WHERE virtual_sha256 LIKE '8da731305e2397659f7ebc6ef225ddfd3b%';
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | virtual_sha256 | virtual_sha256 | 67      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

covering indexが効く。


うわあ、なんか良さそうな気がしてきた。楽しみ。
これで、JSON型と合わせて無理矢理SQL de NoSQLとかできますねわーい。