Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
1
Help us understand the problem. What is going on with this article?
@hmatsu47

小ネタ/MySQL 8.0.17 で utf8mb4_0900_bin を試す

More than 1 year has passed since last update.

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にしても同様)。

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


1
Help us understand the problem. What is going on with this article?
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
hmatsu47
名古屋で士業向けWebサービスのインフラ構築管理、たまにアプリケーション開発をやっています。 業務利用しているもの、個人研究など、気長にのんびり投稿していきます。ニッチ狙いが多めです。 IPA RISS(001158)・NW・DB/日商・大商2級コレクター?(簿記・ビジネス法務・ビジネス会計)。
infra-workshop
インフラ技術を勉強したい人たちのためのオンライン勉強会です

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
1
Help us understand the problem. What is going on with this article?