TL;DR

MySQL 8.0なら ibd2sdi の結果から判断がつく
Foeign Key制約によって暗黙に作成されたINDEXとは何ぞや、という人は↓
- 第63回　MySQLの外部キー（Foreign Key）制約 | gihyo.jp

(num, val) を持ったテーブルが2つ、numはどちらでもPKE、valはt1でだけUNIQUE KEY。

mysql80 46> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`),
  UNIQUE KEY `val` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql80 46> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `num` bigint unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

ここでt2にADD FOREIGN KEYすると

mysql80 46> ALTER TABLE t2 ADD FOREIGN KEY (val) REFERENCES t1 (val);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 46> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `num` bigint unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`),
  KEY `val` (`val`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`val`) REFERENCES `t1` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

KEY val(val) が勝手に追加される。これがFKによって暗黙に作成されたINDEX。

この暗黙のインデックス、「それを左端プレフィックスに持つインデックスが作られると勝手に消える」という性質を持っているらしい。

第63回　MySQLの外部キー（Foreign Key）制約 | gihyo.jp

mysql80 46> ALTER TABLE t2 ADD KEY idx_text(val, num);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 46> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `num` bigint unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`),
  KEY `idx_text` (`val`,`num`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`val`) REFERENCES `t1` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

最初からインデックス付きで作ったテーブルにFOREIGN KEYを足した場合はこれは起こらない。

mysql80 46> CREATE TABLE t3 (num serial, val varchar(32), KEY(val));
Query OK, 0 rows affected (0.02 sec)

mysql80 46> SHOW CREATE TABLE t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `num` bigint unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`),
  KEY `val` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql80 46> ALTER TABLE t3 ADD FOREIGN KEY (val) REFERENCES t1 (val);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 46> SHOW CREATE TABLE t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `num` bigint unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`),
  KEY `val` (`val`),
  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`val`) REFERENCES `t1` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql80 46> ALTER TABLE t3 ADD KEY idx_text(val, num);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 46> SHOW CREATE TABLE t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `num` bigint unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`),
  KEY `val` (`val`),
  KEY `idx_text` (`val`,`num`),
  CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`val`) REFERENCES `t1` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

この区別がつかないかなと思ってたら、ギリギリibd2sdiで読めそうだった。
t2を暗黙のインデックス、t3を明示のインデックスにして

mysql80 46> DROP TABLE t2, t3;
Query OK, 0 rows affected (0.01 sec)

mysql80 46> CREATE TABLE t2 (num serial, val varchar(32));
Query OK, 0 rows affected (0.01 sec)

mysql80 46> CREATE TABLE t3 (num serial, val varchar(32), KEY(val));
Query OK, 0 rows affected (0.02 sec)

mysql80 46> ALTER TABLE t2 ADD FOREIGN KEY (val) REFERENCES t1 (val);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 46> ALTER TABLE t3 ADD FOREIGN KEY (val) REFERENCES t1 (val);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

ibd2sdiでかじる。

$ ibd2sdi d2/t2.ibd | jq '.[1].object.dd_object.indexes[] | {"name": .name, "is_generated": .is_generated}'
{
  "name": "num",
  "is_generated": false
}
{
  "name": "val",
  "is_generated": true
}

$ ibd2sdi d2/t3.ibd | jq '.[1].object.dd_object.indexes[] | {"name": .name, "is_generated": .is_generated}'
{
  "name": "num",
  "is_generated": false
}
{
  "name": "val",
  "is_generated": false
}

この is_generated がそうっぽい。
これデータディクショナリ周りで追加されてるっぽくて、5.7で調べる方法はわからなかった。どうやって5.7は勝手に消してるんだろう。

(ちなみに information_schema.statistics からは読めない)

mysql80 46> SELECT * FROM information_schema.statistics WHERE table_schema = DATABASE();
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT | IS_VISIBLE | EXPRESSION |
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+
| def           | d2           | t1         |          0 | d2           | num        |            1 | num         | A         |           0 |     NULL |   NULL |          | BTREE      |         |               | YES        | NULL       |
| def           | d2           | t1         |          0 | d2           | val        |            1 | val         | A         |           0 |     NULL |   NULL | YES      | BTREE      |         |               | YES        | NULL       |
| def           | d2           | t2         |          0 | d2           | num        |            1 | num         | A         |           0 |     NULL |   NULL |          | BTREE      |         |               | YES        | NULL       |
| def           | d2           | t2         |          1 | d2           | val        |            1 | val         | A         |           0 |     NULL |   NULL | YES      | BTREE      |         |               | YES        | NULL       |
| def           | d2           | t3         |          0 | d2           | num        |            1 | num         | A         |           0 |     NULL |   NULL |          | BTREE      |         |               | YES        | NULL       |
| def           | d2           | t3         |          1 | d2           | val        |            1 | val         | A         |           0 |     NULL |   NULL | YES      | BTREE      |         |               | YES        | NULL       |
+---------------+--------------+------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+
6 rows in set (0.00 sec)

(デバッグビルドを使えばデータディクショナリでも確認できそう)

mysql80 8> SELECT @@version;
+--------------+
| @@version    |
+--------------+
| 8.0.32-debug |
+--------------+
1 row in set (0.00 sec)

mysql80 8> SET SESSION debug='+d,skip_dd_table_access_check';
Query OK, 0 rows affected (0.00 sec)

mysql80 8> SELECT schemata.name, tables.name, indexes.name, indexes.options, indexes.se_private_data, indexes.is_generated, indexes.is_visible, indexes.hidden FROM indexes JOIN tables ON indexes.table_id = tables.id JOIN schemata ON tables.schema_id = schemata.id WHERE schemata.name = 'd2';
+------+------+------+----------+------------------------------------------------------+--------------+------------+--------+
| name | name | name | options  | se_private_data                                      | is_generated | is_visible | hidden |
+------+------+------+----------+------------------------------------------------------+--------------+------------+--------+
| d2   | t1   | num  | flags=0; | id=172;root=4;space_id=20;table_id=1081;trx_id=3348; |            0 |          1 |      0 |
| d2   | t1   | val  | flags=0; | id=174;root=5;space_id=20;table_id=1081;trx_id=3366; |            0 |          1 |      0 |
| d2   | t2   | num  | flags=0; | id=191;root=4;space_id=29;table_id=1090;trx_id=3553; |            0 |          1 |      0 |
| d2   | t2   | val  | flags=0; | id=192;root=5;space_id=29;table_id=1090;trx_id=3553; |            1 |          1 |      0 |
| d2   | t3   | num  | flags=0; | id=193;root=4;space_id=30;table_id=1091;trx_id=3583; |            0 |          1 |      0 |
| d2   | t3   | val  | flags=0; | id=194;root=5;space_id=30;table_id=1091;trx_id=3583; |            0 |          1 |      0 |
+------+------+------+----------+------------------------------------------------------+--------------+------------+--------+
6 rows in set (0.00 sec)

日々の覚書

GA

2023/03/03

Foeign Key制約によって暗黙に作成されたINDEX or NOT

TL;DR

0 件のコメント :

コメントを投稿