tom__bo’s Blog

えんじにゃ〜 @tom__bo

MySQL 8.0 Functional Key Parts

MySQL8.0の機能を調べてまとめている。 Functional Key Partsについて読んだまとめ。

dev.mysql.com

MySQL 8.0.13から関数index(functional key parts)がサポートされる。
5.7以前ではカラムの値そのものかカラムのprefixでしかindexを作成することができなかったが、8.0.13からはテーブルに直接入っている値以外でもindexを作成することができるようになる。

  • マルチカラムインデックスでは関数インデックスと通常indexを混在して指定できる
  • ASC, DESC指定は関数インデックスでも可能
  • PKに指定できない
  • PKがない場合InnoDBUNIQUE 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)
    • functional key part(カッコの中)で1つのカラムだけ指定することはできない
      • NG: index((col1), (col2))
    • functional key partではcolumnのprefixを指定することができない
      • これについてはSUBSTRING(), CAST()を含めて後述する
    • functional key partは外部キー制約に含められない
  • 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)

Reference