オプティマイザヒントでDELETEステートメントに使わせるインデックスを強制する(MySQL 8.0から)

日々の覚書: MySQLから大量のレコードをちまちま削除するメモのコメント にあった、「DELETEでFORCE INDEXするにはテーブルリファレンス構文しかないの？」の話の続き(?)

MySQL 8.0のオプティマイザヒント には INDEX ヒントがあるので、こいつを使えばテーブルリファレンス構文でこねくり回さなくてもDELETEでインデックス指定ができそう。

mysql80 12> SHOW CREATE TABLE city\G
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql80 12> EXPLAIN DELETE FROM city WHERE countrycode > 'A';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | DELETE      | city  | NULL       | ALL  | CountryCode   | NULL | NULL    | NULL | 4046 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql80 12> EXPLAIN DELETE FROM city FORCE INDEX(PRIMARY) WHERE countrycode > 'A'; -- SELECTと同じようなFORCE INDEXをするとシンタックスエラー
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FORCE INDEX(PRIMARY) WHERE countrycode > 'A'' at line 1

mysql80 12> EXPLAIN DELETE FROM city USING city FORCE INDEX(PRIMARY) WHERE countrycode > 'A'; -- USINGでテーブルリファレンス構文、5.7とそれ以前はこれ一択
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | DELETE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4046 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql80 12> EXPLAIN DELETE /*+INDEX(city PRIMARY) */ FROM city WHERE countrycode > 'A'; -- INDEXヒント構文
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | DELETE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4046 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql80 12> EXPLAIN DELETE /*+NO_INDEX(city countrycode) */ FROM city WHERE countrycode > 'A';  -- NO_INDEX構文も
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | DELETE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4046 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

[NO_]INDEX(テーブル名 インデックス名) と、 USE INDEX や IGNORE INDEX と違ってテーブル名まで引数で渡す。成歩堂。

MySQL :: MySQL 8.0 Reference Manual :: 8.9.3 Optimizer Hints

​