Alibaba Cloud の POLARDB を試してみる（５）MySQL 8.0 互換版でパラレルクエリ（２）

この記事は **[MySQL Advent Calendar 2019](https://qiita.com/advent-calendar/2019/mysql)** 10 日目のエントリとなります。

昨日は tom__bo さんでした。

 - **[Dive into Binary logs](https://tombo2.hatenablog.com/entry/2019/12/09/190337)（tom__bo’s Blog）**

そして、以下の記事の続きでもあります。

 - **[Alibaba Cloud の POLARDB を試してみる（１）MySQL 8.0 互換版起動編](https://qiita.com/hmatsu47/items/661f9b7d7d0ed376e464)**
 - **[Alibaba Cloud の POLARDB を試してみる（２）MySQL 8.0 互換版のバッファプールまわりを中心に](https://qiita.com/hmatsu47/items/da024a9de6236d761cdc)**
 - **[Alibaba Cloud の POLARDB を試してみる（３）MySQL 8.0 互換版でパラレルクエリを試す](https://qiita.com/hmatsu47/items/1d92239e2064c99f5a24)**
 - **[Alibaba Cloud の POLARDB を試してみる（４）PostgreSQL 11 互換版起動＋パラレルスキャン編](https://qiita.com/hmatsu47/items/ec485766341c79bdce99)**

この記事では、その３の続きとして、MySQL 8.0 互換版のパラレルクエリのうち文字列を扱うパターンを試してみます。

# 試すこと

 - パラレルクエリ無しとパラレルクエリ 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 の文字コードについて話した](https://tmtms.hatenablog.com/entry/201805/mysql-innovation-day-tokyo)（＠tmtms のメモ）**

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

[こちら](https://qiita.com/hmatsu47/items/d66830c8a00c21f5edad)の記事のものを COLLATION にあわせて変更し、データ行数を 10 倍しました（各テーブル 100 万行）。

<details><summary>**テーブル定義**</summary>
<div>

```sql:テーブル定義
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)
```

</div>
</details>

<details><summary>**データ（一部）**</summary>
<div>

```sql:データ（計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;
```

</div>
</details>

## ソート用 SQL

```sql:ソート用SQL
mysql> SELECT id, LEFT(str, 30) FROM `utf8mb4_test` ORDER BY str, id LIMIT 100;
```

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

## `LIKE`検索用 SQL

```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 を試す](https://qiita.com/hmatsu47/items/7af490cf526d7b19a6ec)**

---

**[MySQL Advent Calendar 2019](https://qiita.com/advent-calendar/2019/mysql)** 明日（11 日目）は keny_lala さんです。

 - **[mysqldumpがFLUSH TABLESでタイムアウトしたので調べた話](http://kenken0807.hatenablog.com/entry/2019/12/11/152837)（kenken0807_DBメモ）**