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

Alibaba Cloud の POLARDB を試してみる(5)MySQL 8.0 互換版でパラレルクエリ(2)

この記事は MySQL Advent Calendar 2019 10 日目のエントリとなります。

昨日は tom__bo さんでした。

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

この記事では、その3の続きとして、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 についてはこちらが参考になります。

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

こちらの記事のものを 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('1234567890', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('ABCDEFGHIJKLMNOPQRS TUVWXYZABCDEFGHIJKLMN', 25);
INSERT INTO utf8mb4_test SET str=REPEAT('abcdefghijklmnopqrs tuvwxyzabcdefghijklmn', 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 機能を試します。


MySQL Advent Calendar 2019 明日(11 日目)は keny_lala さんです。

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