嬉し楽しい式インデックス。
PostgreSQLのこれが結構うらやましかった機能がついにMySQLにも！
PostgreSQLのこれが結構うらやましかった機能がついにMySQLにも！
MySQL 5.7からgenerated columnが入ってそのカラムにインデックスを張ればそれっぽい高速化は実現できたんだけれども、generated columnは如何せんORMと相性が悪いことがあって（ORMはそのカラムがgeneratedかbasicか特に気にしてくれないけど、generatedなカラムは更新しようとするとエラーになる、など）そういうケースではカラムを定義せずに式インデックスが使えるといいのに…と思っていたのでしたん。
- 日々の覚書: MySQL 5.7.6のgenerated columnは関数インデックスの夢を見るか
- 日々の覚書: MySQL 5.7.8からInnoDBのgenerated columnは実体を取らずにインデックスを作れるようになった
- 日々の覚書: MySQL 5.7のgenerated columnでついにCHECK制約っぽいことを実現できる
という訳で式インデックス、定義の仕方はこちら。
ALTER TABLE .. ADD KEY .. や CREATE INDEX .. の、普段なら (カラム名, ..) になっているところを ((式), ..) にするだけ。式そのものを括弧でくくるのを忘れずに。mysql80 41> SELECT * FROM t1;
+------+-------+
| num | val |
+------+-------+
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
| 5 | five |
+------+-------+
5 rows in set (0.00 sec)
mysql80 41> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`num` int(11) NOT NULL,
`val` varchar(32) COLLATE utf8mb4_ja_0900_as_cs DEFAULT NULL,
PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.00 sec)
こんなテーブルがあるじゃろ？
mysql80 41> EXPLAIN SELECT * FROM t1 WHERE num = 2;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql80 41> EXPLAIN SELECT * FROM t1 WHERE num + 1 = 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
num + 1 とか左辺に演算子がくるとインデックスが使えんじゃろ？mysql80 41> ALTER TABLE t1 ADD KEY ((num + 1));
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql80 41> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`num` int(11) NOT NULL,
`val` varchar(32) COLLATE utf8mb4_ja_0900_as_cs DEFAULT NULL,
PRIMARY KEY (`num`),
KEY `functional_index` (((`num` + 1)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.00 sec)
mysql80 41> EXPLAIN SELECT * FROM t1 WHERE num + 1 = 3;
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | functional_index | functional_index | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
関数インデックスを使うとそれが引けるんじゃ。
しかしこれ、オプティマイザーでたたんでくれるわけではなさそうなので、
WHERE や ORDER BY に出てくる式と一致しないといけないっぽい。AS を使ったエイリアスに ORDER BY からアクセスするのはいけた。mysql80 41> EXPLAIN SELECT * FROM t1 WHERE num + 2 - 1 = 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql80 41> EXPLAIN SELECT num + 1 AS c FROM t1 ORDER BY c;
+----+-------------+-------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | functional_index | 8 | NULL | 5 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
ユニークキーは作れたけど、全文検索、あまりに変な（？）関数、
NOW(), AUTO_INCREMENT な列に対する関数はエラーになって弾かれる。mysql80 41> ALTER TABLE t1 ADD FULLTEXT KEY ((num + 3));
ERROR 3759 (HY000): Fulltext functional index is not supported.
mysql80 41> ALTER TABLE t1 ADD KEY ((SLEEP(1)));
ERROR 3758 (HY000): Expression of functional index 'functional_index_4' contains a disallowed function.
mysql80 41> ALTER TABLE t1 ADD KEY ((RAND(1)));
ERROR 3758 (HY000): Expression of functional index 'functional_index_4' contains a disallowed function.
mysql80 41> ALTER TABLE t1 ADD KEY ((NOW(1)));
ERROR 3758 (HY000): Expression of functional index 'functional_index_4' contains a disallowed function.
個人的にはアレかな、
idx_status ((status IN (1, 2, 3)), (activate <> 1)) みたいな式インデックスを作ると捗るかなと思いました！