MySQL8.0の機能を調べてまとめている。 Functional Key Partsについて読んだまとめ。
MySQL 8.0.13から関数index(functional key parts)がサポートされる。
5.7以前ではカラムの値そのものかカラムのprefixでしかindexを作成することができなかったが、8.0.13からはテーブルに直接入っている値以外でもindexを作成することができるようになる。
- マルチカラムインデックスでは関数インデックスと通常indexを混在して指定できる
- ASC, DESC指定は関数インデックスでも可能
- PKに指定できない
- PKがない場合InnoDBは
UNIQUE NOT NULLのkeyをPKにするが、これが関数indexを含んでいる場合はpromoteされない - 関数indexにUNIQUEを指定できる
- SPATIAL, FULLTEXT indexには関数indexを指定できない
- 以下の構文的な制約がある
- 関数インデックス(functional key parts)は通常indexやprefix指定のindexと区別するためにカッコ
()でククラ無くてはならない- OK:
index((col1 + col2), (col3 - col4)) - NG:
index(col1 + col2, col3 - col4)
- OK:
- functional key part(カッコの中)で1つのカラムだけ指定することはできない
- NG:
index((col1), (col2))
- NG:
- functional key partではcolumnのprefixを指定することができない
- これについてはSUBSTRING(), CAST()を含めて後述する
- functional key partは外部キー制約に含められない
- 関数インデックス(functional key parts)は通常indexやprefix指定のindexと区別するためにカッコ
- functional key partsを持つindexは重複してもwarningsを発生させない
- functional key partsに含まれているカラムを削除する場合、関数indexを先に削除しないといけない
generated columnとしての実装
関数indexは隠された(hidden) virtual generated columnsとして実装されている。
なので、以下の制約がある
- それぞれのfunctional key partはテーブルのカラム数としてカウントされ、テーブルのカラム数上限を考慮する必要がある
- generated columnsの制約をすべてうける、例えば、
- generated columnsに許可される関数しか使えない
- subquery, parameters, variables, stored functions, user-defined functionsは許可されない
virtual generated columns自体はストレージ容量を食わないが、このindexは他のindex同様に容量を食う
PREFIXで指定できなないことへのwork around
関数indexではprefix長を指定したindexの作成はできない、そのかわりsubstring()やcast()を利用して文字列の先頭部分を取得してindexを作成することができる。
mysql> show create table tt\G *************************** 1. row *************************** Table: tt Create Table: CREATE TABLE `tt` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` varchar(50) DEFAULT NULL, `c2` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c1` (`c1`(10)), KEY `c2fun` ((substr(`c2`,1,12))) ) ENGINE=InnoDB AUTO_INCREMENT=505 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> explain select * from tt where c2 like 'abcefghij%'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tt | NULL | ALL | NULL | NULL | NULL | NULL | 384 | 11.11 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> explain select id from tt where substr(c2, 1, 12) like 'abcdefghijkl'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tt | NULL | ALL | NULL | NULL | NULL | NULL | 384 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
substr(c2, 1, 12) 指定しても効かないんだけど？
思ってるより関数indexが効かないサンプル
mysql> create table funidx ( -> id int not null auto_increment, -> c1 int not null, -> c2 int not null, -> primary key(id), -> key ((c1+c2)) -> ); Query OK, 0 rows affected (0.02 sec) mysql> show create table funidx\G *************************** 1. row *************************** Table: funidx Create Table: CREATE TABLE `funidx` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) NOT NULL, `c2` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `functional_index` (((`c1` + `c2`))) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> insert into funidx (c1, c2) values (1, 10), (2, 20), (3, 30), (4, 40), (5,50); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> insert into funidx (c1, c2) (select c1*2, c2*10 from funidx); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> insert into funidx (c1, c2) (select c1*2, c2*10 from funidx); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> insert into funidx (c1, c2) (select c1*2, c2*10 from funidx); Query OK, 20 rows affected (0.01 sec) Records: 20 Duplicates: 0 Warnings: 0
値を=で指定したときくらいしか効かない?
mysql> explain select * from funidx where (c1+c2) > 20; +----+-------------+--------+------------+------+------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | funidx | NULL | ALL | functional_index | NULL | NULL | NULL | 40 | 97.50 | Using where | +----+-------------+--------+------------+------+------------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> explain select * from funidx where (c1+c2) = 20; +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | funidx | NULL | ref | functional_index | functional_index | 8 | const | 1 | 100.00 | NULL | +----+-------------+--------+------------+------+------------------+------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
