Help us understand the problem. What is going on with this article?

小ネタ/MySQL 8.0.17 で utf8mb4_0900_bin を試す

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 NotesChanges 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_binutf8mb4_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('1234567890', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMN', 25);
INSERT INTO utf8mb4_test SET str=REPEAT('abcdefghijklmnopqrstuvwxyzabcdefghijklmn', 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('1234567890', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMN', 25);
INSERT INTO utf8mb4_test SET str=REPEAT('abcdefghijklmnopqrstuvwxyzabcdefghijklmn', 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にしても同様)。

通常はこのような量のソートは行わないはずなので大丈夫だとは思いますが、少し注意が必要なようです。


Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away