第158回　Invisible Columnsの使いどころ

MySQL 8.0.23では、新たな機能としてInvisible Columnsが導入されました。この機能は、あるカラムを「存在はしているけれども明示的に指定しない場合は参照しないカラムとして扱う」ことができるようになっています。今回はこのInvisible Columnsの機能について見ていきましょう。

なお、似た機能である、invisible indexesについては第110回　Invisible Indexesを使って気軽にチューニングを始めてみるで紹介しておりますのでそちらをご参照ください。また、今回利用しているMySQLのバージョンは8.0.26となります。

Invisible columnsのあるテーブルの作成

Invisibleなカラムのあるテーブルを作成するには、InvisibleにしたいカラムにINVISIBLEをつけてCREATE TABLE文で実行するか、ALTER TABLE文で既存のカラムを指定して実行します。

mysql> CREATE TABLE t1(id int auto_increment primary key, name1 varchar(256), name2 varchar(256) INVISIBLE);
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name1` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
  `name2` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL /*!80023 INVISIBLE */,
  PRIMARY KEY (`id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)

mysql> ALTER TABLE t1 ALTER COLUMN name1 SET INVISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

実際に、作ったテーブルに対してデータを投入して確認してみましょう。

mysql> INSERT INTO t1(id, name1, name2) VALUES(1, "a", "AA"),(2, "b", "BB"), (3, "c", "CC");
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql> SELECT id, name1, name2 FROM t1;
+----+-------+-------+
| id | name1 | name2 |
+----+-------+-------+
|  1 | a     | AA    |
|  2 | b     | BB    |
|  3 | c     | CC    |
+----+-------+-------+
3 rows in set (0.00 sec)

SELECT *で取得したときはid列しか取得できませんでしたが、明示的にカラムを指定することでname1, name2のデータを参照することができました。このようにInvisible Columnsは、明示しない場合はInvisibleなカラムを取得せずにテーブルを扱うことができます。

詳細な動きを確認する

Invisible Columnsの基本的な機能は理解したところで、もう少し詳細な動きを確認してみましょう。

すべてのカラムをinvisibleにすることは可能？

すべてのカラムをinvisibleにすることはできません。全カラムをinvisibleにしようとするとエラーが発生します。

mysql> ALTER TABLE t1 ALTER COLUMN id SET INVISIBLE;
ERROR 4028 (HY000): A table must have at least one visible column.

デフォルト値やNot Null制約のあるカラムにInvisible Columnsは設定できる？

mysql> CREATE TABLE t1(id int auto_increment primary key, name1 varchar(256) not null INVISIBLE, name2 varchar(256) default "ZZZ" INVISIBLE);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t1(id, name2) values (1, "AA");
ERROR 1364 (HY000): Field 'name1' doesn't have a default value
mysql> INSERT INTO t1(id, name1) values (1, "a");
Query OK, 1 row affected (0.00 sec)
mysql> SELECT id, name1, name2 FROM t1;
+----+-------+-------+
| id | name1 | name2 |
+----+-------+-------+
|  1 | a     | ZZZ   |
+----+-------+-------+
1 row in set (0.00 sec)

設定することはできます。デフォルト値がある場合は、通常のテーブルと同じようにカラムを指定しない場合、デフォルト値を利用します。また、Not Null制約がある場合は、INSERT文で明示的に指定しないとNot Null制約でエラーとなります。

Invisible ColumnsにINDEXをつけることは可能か？

InvisibleなカラムにINDEXを利用することは可能です。さらに、UNIQUE制約やPrimaryKey制約のあるカラムにもInvisible Columnsにすることも可能です。

mysql> CREATE TABLE t1(id int auto_increment primary key invisible, name1 varchar(256), name2 varchar(256) INVISIBLE);
Query OK, 0 rows affected (0.03 sec)

GENERATED COLUMNSやCHECK制約にInvisible Columnsは有効か？

GENERATED COLUMNSやCHECK制約にもInvisible Columnsは有効になります。更新しようとした行がCHECK制約を満たさない場合も、CHECK制約違反でエラーが返ってきます。

mysql> CREATE TABLE t1(a int, b int, c int as (a + b) VIRTUAL invisible, d int as (a - b) STORED check(d < 100) invisible);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1(a, b) VALUES (200, 10);
ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated.

CREATE TABLE AS SELECT … と INSERT INTO … SELECT

では、下記のようなテーブルに対してCREATE TABLE AS SELECT … と INSERT INTO … SELECTはどうなるのでしょうか。

mysql> CREATE TABLE t1(id int auto_increment primary key, name1 varchar(256), name2 varchar(256) INVISIBLE);

mysql> INSERT INTO t1(id, name1, name2) VALUES(1, "a", "AA"),(2, "b", "BB"), (3, "c", "CC");

まずCREATE TABLE AS SELECT … を*を指定して作成してみると、Invisible Columnsはコピーされずにテーブルが生成されました。

mysql> CREATE TABLE cp_t1 as SELECT * FROM t1;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE cp_t1\G
*************************** 1. row ***************************
       Table: cp_t1
Create Table: CREATE TABLE `cp_t1` (
  `id` int NOT NULL DEFAULT '0',
  `name1` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)

続いてInvisible Columnsのカラム名も指定して実施してみます。Invisible Columnsを指定した場合は、その列も対象としてコピーされました。ただし、コピーされた方のテーブルはInvisible Columns属性は付与されておりません。

mysql> CREATE TABLE cp_t1 as SELECT id, name1, name2 FROM t1;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

> SHOW CREATE TABLE cp_t1\G
*************************** 1. row ***************************
       Table: cp_t1
Create Table: CREATE TABLE `cp_t1` (
  `id` int NOT NULL DEFAULT '0',
  `name1` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
  `name2` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

INSERT INTO … SELECT * FROM の方はどうでしょうか。

mysql> CREATE TABLE cp_t2(id int auto_increment primary key, name1 varchar(256), name2 varchar(256) INVISIBLE);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO cp_t2 SELECT * FROM t1;
Query OK, 3 rows affected (0.01 sec)

mysql> SELECT id, name1, name2 FROM cp_t2;
+----+-------+-------+
| id | name1 | name2 |
+----+-------+-------+
|  1 | a     | NULL  |
|  2 | b     | NULL  |
|  3 | c     | NULL  |
+----+-------+-------+
3 rows in set (0.00 sec)

Invisible Columnsの列は参照されずにコピーされて、name2はNULLとなりました。ただし、下記のように挿入先、挿入元ともにInvisible Columnsを指定した場合はInvisible Columnsの列も挿入されています。

mysql> INSERT INTO cp_t2(id, name1, name2) SELECT id, name1, name2 FROM t1;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT id, name1, name2 FROM cp_t2;
+----+-------+-------+
| id | name1 | name2 |
+----+-------+-------+
|  1 | a     | AA    |
|  2 | b     | BB    |
|  3 | c     | CC    |
+----+-------+-------+
3 rows in set (0.00 sec)

VIEWについて

VIEWを作成する際にSELECT * FROMを利用した場合は、Invisible Columnsは対象とならず、参照することはできません。これはSELECT * FROMで指定してViewを作成するときに、Visibleなカラムのみに展開されてVIEWが作成されてしまうためです。1つ上の同じt1テーブル（name2がinvisibleなカラム）に対して下記のようなVIEWを作成すると、visibleなカラムのみに展開されて作成されていることがわかります。

mysql> CREATE view view_t1 AS SELECT * FROM t1;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE VIEW  view_t1\G
*************************** 1. row ***************************
                View: view_t1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t1` AS select `t1`.`id` AS `id`,`t1`.`name1` AS `name1` from `t1`
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Invisible Columnsの確認

Invisible Columnsについては、Information_schemaのCOLUMNSテーブルのEXTRAを参照することで確認できます。 Invisible ColumnにはINVISIBLE値がEXTRAに入っているのを確認することができます。

まとめ

今回はInvisible Columnsの機能について、さまざまな動作を見ていきました。明示的に参照しないと表示されない機能は、どういったところで有用なのでしょうか。

たとえば、既存のテーブル等にカラムを追加したときに「実は知らないところでSELECT * FROM で参照されててカラム追加の改修がもれてマッピングができません！」といった場合にすぐさまInvisibleにすることで、障害を小さくすることが可能になるかもしれません（これは追加したカラムは明示的に指定している場合に限ります⁠）⁠。それ以外にも、あえて明示的に指定しないと参照させないようにしたいカラムがある場合などに使うことを想定してみると良いかも知れません。