Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

MySQLAdvent Calendar 2019

Day 10
@hmatsu47

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

Last updated at Posted at 2019-12-09

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

昨日は tom__bo さんでした。

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

この記事では、その３の続きとして、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('１２３４５６７８９０', 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 機能を試します。

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

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?