試すこと

パラレルクエリ無しとパラレルクエリ 4 並列で、以下を比較
- 文字列のソート
- 文字列のLIKE検索
COLLATION（照合順序）による速度向上の違いを見る
- utf8mb4_0900_ai_ci（MySQL 8.0 デフォルト・アクセントと大文字小文字を区別しない）
- utf8mb4_0900_as_cs（アクセントと大文字小文字を区別する）
- utf8mb4_bin（文字コードの違いを区別する）
インスタンスタイプは前回同様 4 Core 16 GB（polar.mysql.x4.large）を選択

※COLLATION についてはこちらが参考になります。

MySQL Innovation Day Tokyo で MySQL 8 の文字コードについて話した（＠tmtms のメモ）

テーブル定義とテストデータ

こちらの記事のものを COLLATION にあわせて変更し、データ行数を 10 倍しました（各テーブル 100 万行）。

**テーブル定義**

テーブル定義

mysql> CREATE DATABASE `utf8mb4_0900_ai_ci_test`;
Query OK, 1 row affected (0.03 sec)

mysql> USE `utf8mb4_0900_ai_ci_test`;
Database changed
mysql> CREATE TABLE `utf8mb4_0900_ai_ci_test`.`utf8mb4_test` (
    ->   `id` int(10) NOT NULL AUTO_INCREMENT,
    ->   `str` varchar(1000) COLLATE utf8mb4_0900_ai_ci NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `str` (`str`(767))
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.43 sec)

mysql> CREATE DATABASE `utf8mb4_0900_as_cs_test`;
Query OK, 1 row affected (0.01 sec)

mysql> USE `utf8mb4_0900_as_cs_test`;
Database changed
mysql> CREATE TABLE `utf8mb4_0900_as_cs_test`.`utf8mb4_test` (
    ->   `id` int(10) NOT NULL AUTO_INCREMENT,
    ->   `str` varchar(1000) COLLATE utf8mb4_0900_as_cs NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `str` (`str`(767))
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE DATABASE `utf8mb4_bin_test`;
Query OK, 1 row affected (0.03 sec)

mysql> USE `utf8mb4_bin_test`;
Database changed
mysql> CREATE TABLE `utf8mb4_bin_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;
Query OK, 0 rows affected (0.94 sec)

**データ（一部）**

データ（計100万行分）

SET AUTOCOMMIT=0;
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);
COMMIT;

ソート用 SQL

ソート用SQL

mysql> SELECT id, LEFT(str, 30) FROM `utf8mb4_test` ORDER BY str, id LIMIT 100;

※バッファプールに載っていない状態と載っている状態で試すために 2 回連続で実行しています。

`LIKE`検索用 SQL

LIKE検索用SQL

SELECT COUNT(*) FROM `utf8mb4_test` WHERE str LIKE '%かきくけこ%';

※同じく 2 回連続で実行しています。

結果

それぞれのセットを 2 回実行した平均を取りました。
※変化が少なかったため 2 回で打ち切りました（時間とお金の関係上…）。

バッファプールに載っていない状態で約 3 倍、載っている状態で約 2 倍パラレルクエリによって高速化した
- 前回、数値カラムの集計で試したときとほぼ同じ傾向
高速化の比率については COLLATION による差はなかった
バッファプールに載っていない状態での比率のばらつきが多少大きくなった
- I/O が発生するため当然の結果？

`utf8mb4_0900_ai_ci`

パラレルクエリ有無	ソート・バッファプール無	ソート・バッファプール有	`LIKE`・バッファプール無	`LIKE`・バッファプール有
無効 (a)	65.60s	10.14s	62.81s	9.55s
有効・4 並列 (b)	19.46s	5.14s	19.85s	4.62s
(a) / (b)	3.37	1.97	3.16	2.07

`utf8mb4_0900_as_cs`

パラレルクエリ有無	ソート・バッファプール無	ソート・バッファプール有	`LIKE`・バッファプール無	`LIKE`・バッファプール有
無効 (a)	88.79s	33.00s	63.35s	10.76s
有効・4 並列 (b)	31.82s	16.54s	20.23s	5.38s
(a) / (b)	2.79	2.00	3.13	2.00

`utf8mb4_bin`

パラレルクエリ有無	ソート・バッファプール無	ソート・バッファプール有	`LIKE`・バッファプール無	`LIKE`・バッファプール有
無効 (a)	57.81s	3.18s	66.10s	12.92s
有効・4 並列 (b)	17.44s	1.53s	23.31s	6.21s
(a) / (b)	3.31	2.08	2.84	2.08

次回は

PostgreSQL 11 互換版で GIS 機能を試します。

Alibaba Cloud の POLARDB を試してみる（６）PostgreSQL 11 互換版で Ganos を試す

MySQL Advent Calendar 2019 明日（11 日目）は keny_lala さんです。

mysqldumpがFLUSH TABLESでタイムアウトしたので調べた話（kenken0807_DBメモ）