MySQL 8.0.17 がリリースされたので「何か小ネタを試さねば」と思い、新たに追加された COLLATION（照合順序）であるutf8mb4_0900_binを試してみました。
※実はその前にアンチジョイン（NOT IN）の高速化を試してみたのですが…試し方が悪かったようで走査行数が MySQL 8.0.16 比で同じか増えてしまい検証に失敗してしまいました…。
照合順序utf8mb4_0900_binとは
MySQL 8.0.17 でutf8mb4_binとは別の照合順序として登場しました。パディングに違いがあるようです。
MySQL 8.0 Release Notes の Changes in MySQL 8.0.17 (2019-07-22, General Availability) によると、
- The utf8mb4 character set has a new binary collation, utf8mb4_0900_bin, which differs from the existing utf8mb4_bin binary collation as follows:
- For collating weights, utf8mb4_bin uses code points, possibly with leading zero bytes added, whereas utf8mb4_0900_bin uses the utf8mb4 encoding bytes. The sort order is the same for both collations, but sorting for utf8mb4_0900_bin is much faster.
- The pad attribute for utf8mb4_bin is PAD SPACE, whereas for utf8mb4_0900_bin it is NO PAD. Consequently, operations involving utf8mb4_0900_bin do not add trailing spaces, and comparisons involving strings with trailing spaces may differ for the two collations.
ということなので、1 つ目の「ソートの高速化」を試してみます。
テスト内容
照合順序以外は全く同じテーブルを用意し、utf8mb4_binとutf8mb4_0900_binでソートして比較してみます。
utf8mb4_bin用テーブル
CREATE TABLE `utf8mb4_test`.`utf8mb4_test` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`str` varchar(1000) COLLATE utf8mb4_bin NOT NULL,
PRIMARY KEY (`id`),
KEY `str` (`str`(767))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
utf8mb4_0900_bin用テーブル
CREATE TABLE `utf8mb4_0900_test`.`utf8mb4_test` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`str` varchar(1000) COLLATE utf8mb4_0900_bin NOT NULL,
PRIMARY KEY (`id`),
KEY `str` (`str`(767))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;
これらのテーブルに↓のデータを投入します。
データ投入用ファイル（計100,000行）
INSERT INTO utf8mb4_test SET str=REPEAT('１２３４５６７８９０', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺABCDEFGHIJKLMN', 25);
INSERT INTO utf8mb4_test SET str=REPEAT('ａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚabcdefghijklmn', 25);
INSERT INTO utf8mb4_test SET str=REPEAT('あいうえおかきくけこ', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('さしすせそたちつてと', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('なにぬねのはひふへほ', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('まみむめもやゆよらり', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('るれろわをんっゃゅょ', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('がぎぐげござじずぜぞ', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('だぢづでどばびぶべぼ', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('１２３４５６７８９０', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺABCDEFGHIJKLMN', 25);
INSERT INTO utf8mb4_test SET str=REPEAT('ａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚabcdefghijklmn', 25);
INSERT INTO utf8mb4_test SET str=REPEAT('あいうえおかきくけこ', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('さしすせそたちつてと', 100);
（中略）
INSERT INTO utf8mb4_test SET str=REPEAT('なにぬねのはひふへほ', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('まみむめもやゆよらり', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('るれろわをんっゃゅょ', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('がぎぐげござじずぜぞ', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('だぢづでどばびぶべぼ', 100);
テスト結果
テスト結果(1)
mysql> SELECT id, LEFT(str, 30) FROM utf8mb4_test.utf8mb4_test ORDER BY str, id LIMIT 10;
+----+--------------------------------------------------------------------------------------------+
| id | LEFT(str, 30) |
+----+--------------------------------------------------------------------------------------------+
| 4 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
| 14 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
| 24 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
| 34 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
| 44 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
| 54 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
| 64 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
| 74 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
| 84 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
| 94 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
+----+--------------------------------------------------------------------------------------------+
10 rows in set (0.49 sec)
mysql> SELECT id, LEFT(str, 30) FROM utf8mb4_0900_test.utf8mb4_test ORDER BY str, id LIMIT 10;
+----+--------------------------------------------------------------------------------------------+
| id | LEFT(str, 30) |
+----+--------------------------------------------------------------------------------------------+
| 4 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
| 14 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
（中略）
| 94 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
+----+--------------------------------------------------------------------------------------------+
10 rows in set (0.34 sec)
確かにソートが速くなっているようです。
ところが…
テスト結果(2)
mysql> SELECT id, LEFT(str, 30) FROM utf8mb4_test.utf8mb4_test ORDER BY str, id LIMIT 100;
+-----+--------------------------------------------------------------------------------------------+
| id | LEFT(str, 30) |
+-----+--------------------------------------------------------------------------------------------+
| 4 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
| 14 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
（中略）
| 994 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
+-----+--------------------------------------------------------------------------------------------+
100 rows in set (0.49 sec)
mysql> SELECT id, LEFT(str, 30) FROM utf8mb4_0900_test.utf8mb4_test ORDER BY str, id LIMIT 100;
+-----+--------------------------------------------------------------------------------------------+
| id | LEFT(str, 30) |
+-----+--------------------------------------------------------------------------------------------+
| 4 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
| 14 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
（中略）
| 994 | あいうえおかきくけこあいうえおかきくけこあいうえおかきくけこ |
+-----+--------------------------------------------------------------------------------------------+
100 rows in set (0.78 sec)
おや？結果が逆転したぞ？
調べてみると、テストに使った環境・データではLIMIT 64以上でutf8mb4_0900_binのほうが遅くなるようです（ソート順をDESCにしても同様）。
通常はこのような量のソートは行わないはずなので大丈夫だとは思いますが、少し注意が必要なようです。
- Qiitaに投稿したMySQL 8.0関連記事
- MySQL 8.0 の薄い本（無料で配布中！）