MariaDB10.4.7から、text/blobについても、桁数を指定しないでINDEXが作成できるようになった。
以下のリンクの下のほうに書いてあった。
https://jira.mariadb.org/browse/MDEV-20918
しかし、UNIQUE KEYの場合、これによる弊害が現時点では発生する。
10.3まではエラーで抑止してくれたものの、10.4からはスルーして、HASHインデックスとして作成しまうのだ。
つまり、InnoDBにおいては、現状有効でないHASHインデックスが形だけ定義されるということである。
実際に例を挙げてみます。
MariaDB [test]> create table dummy (
-> c_tinytext tinytext,
-> c_text text,
-> c_mediumtext mediumtext,
-> c_longtext longtext,
-> cu_tinytext tinytext,
-> cu_text text,
-> cu_mediumtext mediumtext,
-> cu_longtext longtext,
-> key k1 (c_tinytext),
-> key k2 (c_text),
-> key k3 (c_mediumtext),
-> key k4 (c_longtext),
-> unique key u1 (cu_tinytext),
-> unique key u2 (cu_text),
-> unique key u3 (cu_mediumtext),
-> unique key u4 (cu_longtext)
-> ) Engine=InnoDB;
Query OK, 0 rows affected, 3 warnings (0.016 sec)
waringがあるものの、問題なくテーブルが作成できてしまった。
念のためwarningを見てみる。
MariaDB [test]> show warnings; +-------+------+----------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------+ | Note | 1071 | Specified key was too long; max key length is 3072 bytes | | Note | 1071 | Specified key was too long; max key length is 3072 bytes | | Note | 1071 | Specified key was too long; max key length is 3072 bytes | +-------+------+----------------------------------------------------------+ 3 rows in set (0.000 sec)
３つしかない。次にshow create tableを見てみる。
MariaDB [test]> show create table dummy;
+-------+-----------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------+
| dummy | CREATE TABLE dummy (
c_tinytext tinytext DEFAULT NULL,
c_text text DEFAULT NULL,
c_mediumtext mediumtext DEFAULT NULL,
c_longtext longtext DEFAULT NULL,
cu_tinytext tinytext DEFAULT NULL,
cu_text text DEFAULT NULL,
cu_mediumtext mediumtext DEFAULT NULL,
cu_longtext longtext DEFAULT NULL,
UNIQUE KEY u1 (cu_tinytext) USING HASH,
UNIQUE KEY u2 (cu_text) USING HASH,
UNIQUE KEY u3 (cu_mediumtext) USING HASH,
UNIQUE KEY u4 (cu_longtext) USING HASH,
KEY k1 (c_tinytext(255)),
KEY k2 (c_text(768)),
KEY k3 (c_mediumtext(768)),
KEY k4 (c_longtext(768))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------+
1 row in set (0.001 sec)
なんと、UNIQUE KEYが、自動的にHASH INDEXに変更されてしまった。
念のため、INDEXの状態を見てみる。
MariaDB [test]> show index from dummy;
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dummy | 0 | u1 | 1 | cu_tinytext | A | NULL | NULL | NULL | YES | HASH | | |
| dummy | 0 | u2 | 1 | cu_text | A | NULL | NULL | NULL | YES | HASH | | |
| dummy | 0 | u3 | 1 | cu_mediumtext | A | NULL | NULL | NULL | YES | HASH | | |
| dummy | 0 | u4 | 1 | cu_longtext | A | NULL | NULL | NULL | YES | HASH | | |
| dummy | 1 | k1 | 1 | c_tinytext | A | 0 | 255 | NULL | YES | BTREE | | |
| dummy | 1 | k2 | 1 | c_text | A | 0 | 768 | NULL | YES | BTREE | | |
| dummy | 1 | k3 | 1 | c_mediumtext | A | 0 | 768 | NULL | YES | BTREE | | |
| dummy | 1 | k4 | 1 | c_longtext | A | 0 | 768 | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.002 sec)
やはり、UNIQUE KEYだけHASH INDEXになっている。
この状態において、残念ながらUNIQUE KEYの検索時のインデックスは効きません。HASH INDEXだから等価なら効くのかと思いきや、InnDBなんで、フルスキャンになってしまいます。ご注意ください。
これ、見落とすと思う。少なくとも今までMySQLに触ってきた人なら。
ポジティブに考えるなら、InnoDBでもHASH INDEXを近いうちにサポートするということなのだろうか？とも考えられなくはない。10.5で導入されるのだろうか？
UNIQUE制約を張る以上、等価検索が往々にして行われるので、InnoDBのHASH INDEXサポートは個人的には歓迎かなと。
以上
コメントがあればどうぞ