2021/05/31

東京都オープンデータカタログサイトのCSVを使ってLOAD DATA LOCAL INFILEの練習をする

TL;DR

東京都新型コロナウイルス陽性患者発表詳細 - 東京都_新型コロナウイルス陽性患者発表詳細 - 東京都オープンデータカタログサイトのCSVをLOAD DATA LOCAL INFILEでMySQLに突っ込む
この記事中に出てくるCSV由来のデータは東京都オープンデータカタログサイトによりCC BY 4.0によりライセンスされています。
- https://catalog.data.metro.tokyo.lg.jp/dataset/t000010d0000000068/resource/c2d997db-1450-43fa-8037-ebb11ec28d4c

取り敢えずまずはCSVをダウンロードして、突っ込むテーブルを考える。


$ wget https://stopcovid19.metro.tokyo.lg.jp/data/130001_tokyo_covid19_patients.csv

$ head 130001_tokyo_covid19_patients.csv

No,全国地方公共団体コード,都道府県名,市区町村名,公表_年月日,発症_年月日,確定_年月日,患者_居住地,患者_年代,患者_性別,患者_職業,患者_状態,患者_症状,患者_渡航歴の有無フラグ,患者_接触歴の有無フラグ,備考,退院済フラグ

1,130001,東京都,,2020-01-24,,,湖北省武漢市,40代,男性,,,,,,,1

2,130001,東京都,,2020-01-25,,,湖北省武漢市,30代,女性,,,,,,,1

3,130001,東京都,,2020-01-30,,,湖南省長沙市,30代,女性,,,,,,,1

4,130001,東京都,,2020-02-13,,,都内,70代,男性,,,,,,,1

5,130001,東京都,,2020-02-14,,,都内,50代,女性,,,,,,,1

6,130001,東京都,,2020-02-14,,,都内,70代,男性,,,,,,,1

7,130001,東京都,,2020-02-15,,,都内,80代,男性,,,,,,,1

8,130001,東京都,,2020-02-15,,,都内,50代,女性,,,,,,,1

9,130001,東京都,,2020-02-15,,,都内,50代,男性,,,,,,,1

No が連番っぽくPRIMARY KEYなのかな、130001と東京都は自明っぽいので取り込まなくて良い気がする、NULLABLEなものも特に扱いたくないので日付類は 公表_年月日 だけを使おう。居住地は取り回ししづらそうだしカーディナリティーが低くて面白くなさそうなので取り敢えず今回はパス、 患者_年代 と 患者_性別 はINT型、ENUM型に変えた上で取り込もうかな。

というわけでテーブル定義はこんな感じにすることにした。

mysql> SHOW CREATE TABLE covid19_tokyo\G
*************************** 1. row ***************************
       Table: covid19_tokyo
Create Table: CREATE TABLE `covid19_tokyo` (
  `no` int unsigned NOT NULL,
  `published` date NOT NULL,
  `age` int unsigned NOT NULL,
  `gender` enum('M','F') NOT NULL,
  PRIMARY KEY (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.04 sec)

LOAD DATA LOCAL INFILEの書式は取り敢えずこんな感じにして、後でちょこちょこ直していこう。

CSVでカラムは , で区切られているので FIELDS TERMINATED BY ',' 、カラムの中の値はクォートされていなかったので ENCLOSED BY .. の指定は要らない。

括弧書きで羅列しているのは、CSVファイル内での位置とMySQL側のカラムを対応させるための記述。テーブル側のカラム名だけでなく、 @変数 にも代入できる。必ずしもCSVファイルとテーブル定義のカラムの順番が合っている必要はないし、CSV側に余剰なフィールドがあれば @dummy にでも突っ込んで読み捨てさせることができる。

ここを (no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) と指定することで、

No -> noカラム
全国地方公共団体コード -> @dummyユーザー変数
都道府県名 -> @dummyユーザー変数
市区町村名 -> @dummyユーザー変数
公表_年月日 -> publishedカラム
発症_年月日 -> @dummyユーザー変数
確定_年月日 -> @dummyユーザー変数
患者_居住地 -> @dummyユーザー変数
患者_年代 -> @ageユーザー変数
患者_性別 -> @genderユーザー変数
患者_職業 -> @dummyユーザー変数
患者_状態 -> 未指定で読み飛ばされる
患者_症状 -> 未指定で読み飛ばされる
患者_渡航歴の有無フラグ -> 未指定で読み飛ばされる
患者_接触歴の有無フラグ -> 未指定で読み飛ばされる
備考 -> 未指定で読み飛ばされる
退院済フラグ -> 未指定で読み飛ばされる

という意味になる。

さあレッツゴー。

mysql> LOAD DATA LOCAL INFILE '/home/yoku0825/down/130001_tokyo_covid19_patients.csv' INTO TABLE covid19_tokyo FIELDS TERMINATED BY ',' (no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) SET age = @age, gender = @gender;
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

で、早速ERROR 3948の洗礼を食らう。これね。

日々の覚書: MySQL 8.0でLOAD DATA LOCAL INFILEが “ERROR 1148 (42000): The used command is not allowed with this MySQL version” または “Error: 3948. Loading local data is disabled; this must be enabled on both the client and server sides” で失敗する時

mysql80 9> SELECT @@local_infile;
+----------------+
| @@local_infile |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

mysql80 9> SET GLOBAL local_infile = 1;
Query OK, 0 rows affected (0.00 sec)

mysql80 9> SELECT @@local_infile;
+----------------+
| @@local_infile |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

サーバー側の local_infile をONにしてクライアント側はOFFのままにして叩いてみると

mysql> LOAD DATA LOCAL INFILE '/home/yoku0825/down/130001_tokyo_covid19_patients.csv' INTO TABLE covid19_tokyo FIELDS TERMINATED BY ',' (no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) SET age = @age, gender = @gender;
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

エラー番号が変わった。サーバーサイドの local_infile が問題なのかクライアントサイドの local_infile が問題なのかわかりやすくなってた。

というわけで mysql コマンドラインクライアント側の --local-infile をつけてもう一度。

$ mysql -h127.0.0.1 d1 -A --local-infile

mysql> LOAD DATA LOCAL INFILE '/home/yoku0825/down/130001_tokyo_covid19_patients.csv' INTO TABLE covid19_tokyo FIELDS TERMINATED BY ',' (no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) SET age = @age, gender = @gender;
Query OK, 160233 rows affected, 65535 warnings (2.44 sec)
Records: 160248  Deleted: 0  Skipped: 15  Warnings: 480761

ドバっとワーニングと、15件の登録できなかったレコード( Skipped: 15 )があるっぽい。

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------+
| Level   | Code | Message                                                                     |
+---------+------+-----------------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'No' for column 'no' at row 1                     |
| Warning | 1265 | Data truncated for column 'published' at row 1                              |
| Warning | 1366 | Incorrect integer value: '患者_年代' for column 'age' at row 1              |

..
| Warning | 1262 | Row 340 was truncated; it contained more data than there were input columns |
| Warning | 1265 | Data truncated for column 'age' at row 341                                  |
| Warning | 1265 | Data truncated for column 'gender' at row 341                               |
+---------+------+-----------------------------------------------------------------------------+
1024 rows in set (0.04 sec)

SHOW WARNINGS 用に確保されているバッファは1024行ぶんしか入らないことが判明。
とはいえまあ全部潰していけばいいかな…。

取り敢えず row 1 はヘッダ行をスキップさせるのを忘れたせいっぽいので IGNORE 1 ROWS を足してやる。

mysql> TRUNCATE covid19_tokyo;
Query OK, 0 rows affected (0.03 sec)

mysql> LOAD DATA LOCAL INFILE '/home/yoku0825/down/130001_tokyo_covid19_patients.csv' INTO TABLE covid19_tokyo FIELDS TERMINATED BY ',' IGNORE 1 ROWS
(no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) SET age = @age, gender = @gender;
Query OK, 160232 rows affected, 65535 warnings (1.89 sec)
Records: 160247  Deleted: 0  Skipped: 15  Warnings: 480756

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------+
| Level   | Code | Message                                                                     |
+---------+------+-----------------------------------------------------------------------------+
| Warning | 1265 | Data truncated for column 'age' at row 1                                    |
| Warning | 1265 | Data truncated for column 'gender' at row 1                                 |
| Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns   |
| Warning | 1265 | Data truncated for column 'age' at row 2                                    |
| Warning | 1265 | Data truncated for column 'gender' at row 2                                 |
| Warning | 1262 | Row 2 was truncated; it contained more data than there were input columns   |

..

ageカラムとgenderカラムはINTとENUMにそれぞれ変換を噛ますつもりがまだ噛ませていないだけなので、変換部分( SET )を書き足してやって再チャレンジ。

ageの nn台 の部分から数字の部分だけを正規表現で引っ張り出してやる ( REGEXP_SUBSTR ) のと、 CASE で男性女性をMFの一文字に変換。

mysql> TRUNCATE covid19_tokyo;
Query OK, 0 rows affected (0.04 sec)

mysql> LOAD DATA LOCAL INFILE '/home/yoku0825/down/130001_tokyo_covid19_patients.csv' INTO TABLE covid19_tokyo FIELDS TERMINATED BY ',' IGNORE 1 ROWS
(no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) SET age = REGEXP_SUBSTR(@age, '\\d+'), gender = CASE @gender WHEN '男性' THEN 'M' WHEN '女性' THEN 'F' END;
Query OK, 160232 rows affected, 65535 warnings (2.46 sec)
Records: 160247  Deleted: 0  Skipped: 15  Warnings: 160283

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1262 | Row 1 was truncated; it contained more data than there were input columns    |
| Warning | 1262 | Row 2 was truncated; it contained more data than there were input columns    |
| Warning | 1262 | Row 3 was truncated; it contained more data than there were input columns    |

..

ん－、まだ truncated って言ってるなと思ったけど、よく見たら it contained more data than there were input columns だから「リストに何も指定せずに読み飛ばさせる」ってしたやつがこのワーニングになっているっぽい。

しかし Skipped: 15 だしこの状態じゃ他のワーニングが混ざってないかどうかを判定できないのでperformance_schemaに頼ることにする。

mysql80 14> use performance_schema
Database changed

mysql80 14> TRUNCATE events_errors_summary_by_thread_by_error;
Query OK, 0 rows affected (0.03 sec)

過去のワーニングやエラーが混じってもしょうがないので、 events_errors_summary_by_thread_by_error テーブルを一度クリアする。

mysql> TRUNCATE covid19_tokyo;
Query OK, 0 rows affected (0.03 sec)

mysql> LOAD DATA LOCAL INFILE '/home/yoku0825/down/130001_tokyo_covid19_patients.csv' INTO TABLE covid19_tokyo FIELDS TERMINATED BY ',' IGNORE 1 ROWS
(no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) SET age = REGEXP_SUBSTR(@age, '\\d+'), gender = CASE @gender WHEN '男性' THEN 'M' WHEN '女性' THEN 'F' END;
Query OK, 160232 rows affected, 65535 warnings (2.18 sec)
Records: 160247  Deleted: 0  Skipped: 15  Warnings: 160283

これでまたワーニングを発生させてから

mysql80 14> SELECT * FROM events_errors_summary_by_thread_by_error WHERE first_seen > 0;
+-----------+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| THREAD_ID | ERROR_NUMBER | ERROR_NAME               | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN          | LAST_SEEN           |
+-----------+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
|        47 |         1048 | ER_BAD_NULL_ERROR        | 23000     |               21 |                 0 | 2021-05-31 18:43:50 | 2021-05-31 18:43:51 |
|        47 |         1062 | ER_DUP_ENTRY             | 23000     |               15 |                 0 | 2021-05-31 18:43:51 | 2021-05-31 18:43:51 |
|        47 |         1262 | ER_WARN_TOO_MANY_RECORDS | 01000     |           160247 |                 0 | 2021-05-31 18:43:50 | 2021-05-31 18:43:52 |
+-----------+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
3 rows in set (0.03 sec)

こうじゃ。

1262: ER_WARN_TOO_MANY_RECORDS はあきらめたから良いとして、 ER_BAD_NULL_ERROR と ER_DUP_ENTRY は何じゃい…。

取り敢えずキー重複エラー ( ER_DUP_ENTRY )はPRIMARY KEY制約に引っかかってるはずなのでいったんPRIMARY KEYを外して突っ込んでみる。

mysql> TRUNCATE covid19_tokyo;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE covid19_tokyo DROP PRIMARY KEY;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE '/home/yoku0825/down/130001_tokyo_covid19_patients.csv' INTO TABLE covid19_tokyo FIELDS TERMINATED BY ',' IGNORE 1 ROWS
(no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) SET age = REGEXP_SUBSTR(@age, '\\d+'), gender = CASE @gender WHEN '男性' THEN 'M' WHEN '女性' THEN 'F' END;
Query OK, 160247 rows affected, 65535 warnings (1.84 sec)
Records: 160247  Deleted: 0  Skipped: 0  Warnings: 160268

mysql> SELECT no, COUNT(*) FROM covid19_tokyo GROUP BY no HAVING COUNT(*) > 1;
+--------+----------+
| no     | COUNT(*) |
+--------+----------+
| 100045 |        2 |
| 100046 |        2 |
| 100047 |        2 |
| 100048 |        2 |
| 100049 |        2 |
| 100050 |        2 |
| 100051 |        2 |
| 100052 |        2 |
| 100053 |        2 |
| 100054 |        2 |
| 100055 |        2 |
| 100056 |        2 |
| 100057 |        2 |
| 100058 |        2 |
| 100059 |        2 |
+--------+----------+
15 rows in set (0.25 sec)

:(；ﾞﾟ’ωﾟ’): no 、お前、ユニークじゃないのかYO!!!

$ grep -n '^100045,' 130001_tokyo_covid19_patients.csv
1739:100045,130001,東京都,,2020-04-10,,,都外,20代,男性,,,,,,,1
100930:100045,130001,東京都,,2021-02-01,2021-01-24,2021-01-31,都内,90代,女性,無職,,,,1,,1

全然ユニークじゃなかった。思い込みって怖い。
しかしこれがないとなると、このテーブル上のレコードを一意に識別できそうなものはないな…（そもそも、同じ人間が2回罹った場合におそらく別の行として計上されるであろうことから一意識別もへったくれもない気がする）

NOT NULL制約違反の方も取り敢えず NOT NULL を外して突っ込んでから考えてみることにする。

mysql> TRUNCATE covid19_tokyo;
Query OK, 0 rows affected (0.04 sec)

mysql> desc covid19_tokyo;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| no        | int unsigned  | NO   |     | NULL    |       |
| published | date          | NO   |     | NULL    |       |
| age       | int unsigned  | NO   |     | NULL    |       |
| gender    | enum('M','F') | NO   |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> ALTER TABLE covid19_tokyo MODIFY published date, MODIFY age int unsigned, MODIFY gender enum('M', 'F');
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE '/home/yoku0825/down/130001_tokyo_covid19_patients.csv' INTO TABLE covid19_tokyo FIELDS TERMINATED BY ',' IGNORE 1 ROWS
(no, @dummy, @dummy, @dummy, published, @dummy, @dummy, @dummy, @age, @gender, @dummy) SET age = REGEXP_SUBSTR(@age, '\\d+'), gender = CASE @gender WHEN '男性' THEN 'M' WHEN '女性' THEN 'F' END;
Query OK, 160247 rows affected, 65535 warnings (2.25 sec)
Records: 160247  Deleted: 0  Skipped: 0  Warnings: 160247

取り敢えず、ワーニングは当然出なくなった。

mysql80 14> SELECT * FROM events_errors_summary_by_thread_by_error WHERE first_seen > 0;
+-----------+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| THREAD_ID | ERROR_NUMBER | ERROR_NAME               | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN          | LAST_SEEN           |
+-----------+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
|        47 |         1262 | ER_WARN_TOO_MANY_RECORDS | 01000     |           160247 |                 0 | 2021-05-31 18:52:04 | 2021-05-31 18:52:06 |
+-----------+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
1 row in set (0.03 sec)

肝心のNULLだった中身はというと

mysql> SELECT * FROM covid19_tokyo WHERE no IS NULL OR published IS NULL OR age IS NULL OR gender IS NULL;
+--------+------------+------+--------+
| no     | published  | age  | gender |
+--------+------------+------+--------+
|   1023 | 2020-04-05 |   20 | NULL   |
|   2071 | 2020-04-12 |   40 | NULL   |
|   2140 | 2020-04-13 |   20 | NULL   |
|   2187 | 2020-04-14 | NULL | M      |
|   2640 | 2020-04-17 |   60 | NULL   |
|   2769 | 2020-04-17 | NULL | F      |
|   2781 | 2020-04-17 | NULL | M      |
|   2919 | 2020-04-18 |   10 | NULL   |
|   2924 | 2020-04-18 |   50 | NULL   |
|   3601 | 2020-04-24 | NULL | NULL   |
|   3695 | 2020-04-24 | NULL | F      |
|   3807 | 2020-04-25 | NULL | M      |
|   4339 | 2020-05-02 | NULL | F      |
|   4425 | 2020-05-02 | NULL | F      |
|   9878 | 2020-07-22 |   20 | NULL   |
|  12459 | 2020-07-31 |   50 | NULL   |
|  30977 | 2020-10-31 |   40 | NULL   |
|  56424 | 2020-12-27 | NULL | F      |
|  58555 | 2020-12-30 | NULL | M      |
| 114613 | 2021-03-15 | NULL | M      |
+--------+------------+------+--------+
20 rows in set (0.18 sec)

ん－ホントに入ってない…。（しかも - と ― が揺れてたりするところにもんにょりするな…）

$ grep '^1023,' 130001_tokyo_covid19_patients.csv
1023,130001,東京都,,2020-04-05,,,都内,20代,-,,,,,,,1

]$ grep '^114613,' 130001_tokyo_covid19_patients.csv
114613,130001,東京都,,2021-03-15,,2021-03-06,―,-,男性,－,,,,,,1

取り敢えずここまで。

2021/05/24

サイボウズさんの開運研修(データベース)で話してきました

運気が上がるわけではなくて、「開発運用本部」を詰めて「開運」のようです :D

俺が新入社員の研修を担当するときは 残念ながら SQLの書き方やMySQLのインストールなんてことはほぼやらず、「それ、中堅エンジニア以上が笑うやつだ」みたいな感じになります。

SQLの書き方もMySQLのインストールの仕方も(マニアックなことを言い出さなければ)ググれば出てきたり、それぞれのプロダクトチームによってお作法が違ったりする訳で、それらを無視して汎用的な話をするのも面白くないだろうし、かといってプロダクトチームに合わせて解説しても世界が広がらない訳で難しいですよね。

という訳で、「ググってもたまにしか出てこない」「プロダクトチームのお作法に依らない」「汎用的で世界が広がる」話題として今回はインデックスの話をしてきました。

多くの新卒さんには「なんじゃこりゃ」だと思うんですが、数年してインデックスが思うように効かなかったりネクストキーロックに悩まされた時にふっと思い出していただければ幸いです。

この資料を映しながらグリグリ矢印を引いたりしながら解説したので、資料だけだとなかなかわかりづらいような気がします（普段、なるべく「聞いてくれた人も聞いていない人も、資料だけ読めばほぼわかるように」を心がけてはいるんですが今回のやつはなかなか奥が深かった…）

末筆ですが資料の公開を快く許可してくれたサイボウズさん、ありがとうございます :)

2021/05/21

オプティマイザヒントで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

登録: 投稿 ( Atom )

GA