技術めも

MariaDB10.2で導入されたCHECK CONSTRAINTについて挙動を確認してみました。

使用したバージョンは、10.3.8です。

まず、create table時に一緒に制約を入れてみる。

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nickname` varchar(32) NOT NULL,
  `status` tinyint(4) NOT NULL,
  `reg_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `check_status` CHECK (`status` between 1 and 10)
)

これで、statusカラムは1以上10以下しか入らないはずで、以下のSQLを流してみる。

MariaDB [test]> insert into users (nickname, status, reg_date) values ('u1', 1, now());
-> OK

MariaDB [test]> insert into users (nickname, status, reg_date) values ('u2', 10, now());
-> OK

MariaDB [test]> insert into users (nickname, status, reg_date) values ('u3', 0, now());
ERROR 4025 (23000): CONSTRAINT `check_status` failed for `test`.`users` 

MariaDB [test]> insert into users (nickname, status, reg_date) values ('u4', 11, now()); 
ERROR 4025 (23000): CONSTRAINT `check_status` failed for `test`.`users` 

見事に、0と11が弾かれている。
もちろん、0と11を登録可能なようにするためには、一度制約を解除する必要がある。

次に、ALTERのときの動きを見てみる。

前回作って800万件入っている以下のテーブルを利用する。

 
create table user ( 
  id integer not null auto_increment, 
  name varchar(64), 
  money_total_virtual integer as (money_free + money_paid) virtual
  money_free integer, 
  money_paid integer, 
  primary key (id) 
); 

・通常カラムへの制約

MariaDB [test]> alter table user add constraint check_money_free check (money_free <= 100);
Query OK, 8388608 rows affected (59.516 sec)           
Records: 8388608  Duplicates: 0  Warnings: 0

・virtualカラムへの制約

MariaDB [test]> alter table user add constraint check_money_total_virtual check (money_total_virtual <= 200);
Query OK, 8388608 rows affected (1 min 0.316 sec)      
Records: 8388608  Duplicates: 0  Warnings: 0

やはり、大量のレコードがある状態では、制約を掛けるのも時間がかかることがわかる。

最後にCHECK制約の仕様をまとめておきます。

• すでに入っているデータが制約違反の場合、制約自体が掛からない
• 制約の発動はINSERT/UPDATE時
• primary keyに対しても制約は掛けられる
• auto_incrementに対しては制約は掛けることができない

合わせて公式のマニュアルも参照ください。

https://mariadb.com/kb/en/library/constraint/

以上

MariaDBの生成カラムをALTERするときの速度検証してみました。

確認バージョンは、10.3.8です。

準備として、以下のようなテーブルを作成し、データを投入しておきます。

create table user (
  id         integer not null auto_increment,
  name       varchar(64),
  money_free integer,
  money_paid integer,
  primary key (id)
);

insert into user () values ();
insert into user (id) select 0 from user;
: 上を20回くらい実行

update user set name = concat('name-', lpad(id, 10, '0')), money_free = ceil(rand() * 100), money_paid = ceil(rand() * 100);

MariaDB [test]> select count(*) from user;
+----------+
| count(*) |
+----------+
| 8388608  |
+----------+
1 row in set (1.579 sec)

下準備として、約800万件のデータを投入。

この状態で、通常・PERSITENT・VIRTUALのカラムをALTERで追加してみます。

通常カラムは、最後尾に追加すると、INSTANTが発動してしまうので、すべてAFTERで途中に差し込んでみます。

・通常

# カラム追加
MariaDB [test]> alter table user add column money_total_nornaml integer after name;
Query OK, 0 rows affected (17.338 sec)
Records: 0 Duplicates: 0 Warnings: 0

# インデックス追加
MariaDB [test]> create index user_idx01 on user (money_total_nornaml);
Query OK, 0 rows affected (20.096 sec)
Records: 0 Duplicates: 0 Warnings: 0

# カラム削除
MariaDB [test]> alter table user drop column money_total_nornaml;
Query OK, 0 rows affected (16.986 sec)
Records: 0 Duplicates: 0 Warnings: 0

カラム追加：17秒

インデックス追加：20秒

カラム削除：16秒

という結果でした。

・PERSISTENT

# カラム追加
MariaDB [test]> alter table user add column money_total_persitent integer as (money_free + money_paid) persistent after name;
Query OK, 8388608 rows affected (36.700 sec)
Records: 8388608 Duplicates: 0 Warnings: 0

# インデックス追加
MariaDB [test]> create index user_idx02 on user (money_total_persitent);
Query OK, 0 rows affected (19.660 sec)
Records: 0 Duplicates: 0 Warnings: 0

# カラム削除
MariaDB [test]> alter table user drop column money_total_persitent;
Query OK, 0 rows affected (16.810 sec)
Records: 0 Duplicates: 0 Warnings: 0

カラム追加：36秒

インデックス追加：19秒

カラム削除：16秒

という結果でした。

・VIRTUAL

# カラム追加
MariaDB [test]> alter table user add column money_total_virtual integer as (money_free + money_paid) virtual after name;
Query OK, 0 rows affected (0.012 sec)
Records: 0 Duplicates: 0 Warnings: 0

# インデックス追加
MariaDB [test]> create index user_idx03 on user (money_total_virtual);
Query OK, 0 rows affected (20.224 sec)
Records: 0 Duplicates: 0 Warnings: 0

# カラム削除
MariaDB [test]> alter table user drop column money_total_virtual;
Query OK, 0 rows affected (0.011 sec)
Records: 0 Duplicates: 0 Warnings: 0

カラム追加：0.01秒

インデックス追加：20秒

カラム削除：0.01秒

という結果でした。

PERSISTENTは、実データの生成も一緒に行われるので、

通常に比べて、ADDも遅くなるという結果でした。

VIRTUALは、実データの生成が無いので、

通常に比べて、ADD/DROPも圧倒的に高速ですね。

インデックスの作成速度は変わらないですね。

VIRTUALはPERISITENTに比べて、演算コストがかかるため、

そのあたりは注意が必要であると思いますが、

基本的には、VIRTUALを使っていく方針でよいのかな？という考察です。

以上

まず自分自身がMariaDBの生成カラム周りおよびJSONについて、

以下２点勘違いをしていた。

• MariaDBでもMySQLと同様に、VIRTUAL COLUMNにインデックスを張ることができる（すいません、できないと思ってました。。）
• MariaDBでもCHECK制約を使うことで、「JSONの正しさ」を保証できる（すいません、できないと思ってました。。）

以前書いた記事にも訂正を追記しておきました。

というわけで、MariaDB10.2.17、10.3.10のバージョンで生成カラムについて見ていきます。

ではまず、以下のようなテーブルを作ってみます。

CREATE TABLE `mail` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`to` varchar(255) NOT NULL,
`cc` varchar(255),
`bcc` varchar(255),
`subject` varchar(255) NOT NULL,
`body` json NOT NULL,
`addrs` text AS (CONCAT_WS(',', `to`, `cc`, `bcc`)) PERSISTENT,
`sign` varchar(255) AS (JSON_VALUE(`body`, '$.sign')) VIRTUAL,
CHECK (JSON_VALID(`body`)),
PRIMARY KEY (`id`),
FULLTEXT KEY `mail_fidx01` (`addrs`),
KEY `mail_idx01` (`sign`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ちょっと長いのですが、ポイントは以下の通り。

1. addrsカラムに対して、PERSISTENTタイプの生成カラムを利用して、全文検索INDEXを張る
2. bodyカラムに対して、JSONの型チェックを行う
3. signカラムに対して、VIRTUALタイプの生成カラムを利用して、JSONの部分抽出を行い、セカンダリーINDEXを張る

1はカラムを結合した状態を全文検索にしたい場合、

2はJSONの正しさを保証したい場合、

3はJSONの中の特定のキーを条件に高速にデータアクセスしたい場合、

に用いられるような想定です。

この状態のテーブルを見てみます。

MariaDB [test]> desc mail;
+---------+--------------+------+-----+---------+-------------------+
| Field   | Type         | Null | Key | Default | Extra             |
+---------+--------------+------+-----+---------+-------------------+
| id      | int(10)      | NO   | PRI | NULL    | auto_increment    |
| to      | varchar(255) | NO   |     | NULL    |                   |
| cc      | varchar(255) | YES  |     | NULL    |                   |
| bcc     | varchar(255) | YES  |     | NULL    |                   |
| subject | varchar(255) | NO   |     | NULL    |                   |
| body    | longtext     | NO   |     | NULL    |                   |
| addrs   | text         | YES  | MUL | NULL    | STORED GENERATED  |
| sign    | varchar(255) | YES  | MUL | NULL    | VIRTUAL GENERATED |
+---------+--------------+------+-----+---------+-------------------+
8 rows in set (0.002 sec)

MariaDB [test]> show index from mail;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mail  | 0          | PRIMARY     | 1            | id          | A         | 0           | NULL     | NULL   |      | BTREE      |         |               |
| mail  | 1          | mail_idx01  | 1            | sign        | A         | 0           | NULL     | NULL   | YES  | BTREE      |         |               |
| mail  | 1          | mail_fidx01 | 1            | addrs       | NULL      | NULL        | NULL     | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.000 sec)

生成カラムの情報がdescのExtraにも表現されていますね。

ちなみに、生成カラムは「NOT NULL DEFAULT ・・・」は付けられないようで、生成カラムの評価ができない（失敗？）の場合は、NULLが入る仕様のようです。

次に以下のようなデータを流し込んで、結果を見てみます。

INSERT INTO `mail` (`to`, `cc`, `bcc`, `subject`, `body`) VALUES ('to-00001@example.com', 'cc-00001@example.com', 'bcc-00001@example.com', 'subject-00001', '{"sign": "sign-00001", "message": "message-00001"}');
INSERT INTO `mail` (`to`, `cc`, `bcc`, `subject`, `body`) VALUES ('to-00002@example.com', 'cc-00002@example.com', null , 'subject-00002', '{"sign": "sign-00002", "message": "message-00002"}');
INSERT INTO `mail` (`to`, `cc`, `bcc`, `subject`, `body`) VALUES ('to-00003@example.com', null , null , 'subject-00003', '{"sign": "sign-00003", "message": "message-00003"}');
INSERT INTO `mail` (`to`, `cc`, `bcc`, `subject`, `body`) VALUES ('to-00004@example.com', null , null , 'subject-00004', '{"message": "message-00004"}');

MariaDB [test]> select * from mail\G;
*************************** 1. row ***************************
     id: 1
     to: to-00001@example.com
     cc: cc-00001@example.com
    bcc: bcc-00001@example.com
subject: subject-00001
   body: {"sign": "sign-00001", "message": "message-00001"}
  addrs: to-00001@example.com,cc-00001@example.com,bcc-00001@example.com
   sign: sign-00001
*************************** 2. row ***************************
     id: 2
     to: to-00002@example.com
     cc: cc-00002@example.com
    bcc: NULL
subject: subject-00002
   body: {"sign": "sign-00002", "message": "message-00002"}
  addrs: to-00002@example.com,cc-00002@example.com
   sign: sign-00002
*************************** 3. row ***************************
     id: 3
     to: to-00003@example.com
     cc: NULL
    bcc: NULL
subject: subject-00003
   body: {"sign": "sign-00003", "message": "message-00003"}
  addrs: to-00003@example.com
   sign: sign-00003
*************************** 4. row ***************************
     id: 4
     to: to-00004@example.com
     cc: NULL
    bcc: NULL
subject: subject-00004
   body: {"message": "message-00004"}
  addrs: to-00004@example.com
   sign: NULL
4 rows in set (0.000 sec)

ちゃんとデータが入っていますので、
PERSISTENTタイプのaddrsへの全文検索と、
VIRTUALタイプのsignへの検索を行ってみます。

・PERSISTENTタイプのaddrsへの全文検索

MariaDB [test]> select * from mail where match(addrs) against('cc-00002')\G;
*************************** 1. row ***************************
     id: 2
     to: to-00002@example.com
     cc: cc-00002@example.com
    bcc: NULL
subject: subject-00002
   body: {"sign": "sign-00002", "message": "message-00002"}
  addrs: to-00002@example.com,cc-00002@example.com
   sign: NULL
1 row in set (0.001 sec)

MariaDB [test]> explain select * from mail where match(addrs) against('cc-00002')\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mail
         type: fulltext
possible_keys: mail_fidx01
          key: mail_fidx01
      key_len: 0
          ref:
         rows: 1
        Extra: Using where
1 row in set (0.000 sec)

・VIRTUALタイプのsignへの検索

MariaDB [test]> select * from mail where sign = 'sign-00003'\G;
*************************** 1. row ***************************
     id: 3
     to: to-00003@example.com
     cc: NULL
    bcc: NULL
subject: subject-00003
   body: {"sign": "sign-00003", "message": "message-00003"}
  addrs: to-00003@example.com
   sign: sign-00003
1 row in set (0.001 sec)

MariaDB [test]> explain select * from mail where sign = 'sign-00003'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mail
         type: ref
possible_keys: mail_idx01
          key: mail_idx01
      key_len: 1023
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.001 sec)

ちょっと件数少ないですが、両方共INDEXが効いていることがわかります。

生成カラムって実際どのくらい使われているのかわかりませんが、うまく使うことで、機能追加にも柔軟な対応ができそうな予感です。

生成カラムの進化はこれからも期待しています。

おっと、INSTANT ADDは、生成カラムには効かないので、ご注意ください。INSTANT対応も今後はあるのでは、と期待してます。

以上