GA

2018/10/25

MySQL 8.0.13の式インデックス

嬉し楽しい式インデックス。
PostgreSQLのこれが結構うらやましかった機能がついにMySQLにも!
MySQL 5.7からgenerated columnが入ってそのカラムにインデックスを張ればそれっぽい高速化は実現できたんだけれども、generated columnは如何せんORMと相性が悪いことがあって(ORMはそのカラムがgeneratedかbasicか特に気にしてくれないけど、generatedなカラムは更新しようとするとエラーになる、など)そういうケースではカラムを定義せずに式インデックスが使えるといいのに…と思っていたのでしたん。
という訳で式インデックス、定義の仕方はこちら。
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)
関数インデックスを使うとそれが引けるんじゃ。
しかしこれ、オプティマイザーでたたんでくれるわけではなさそうなので、 WHEREORDER 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)) みたいな式インデックスを作ると捗るかなと思いました!

0 件のコメント :

コメントを投稿