MAGAZINE

ルーターマガジン

インフラ/運用

MySQLとPostgreSQLの速度比較

2023.11.07
Pocket

MySQLとPostgreSQLの速度比較をしている記事があまりなかったので、自分で比較してみることにしました。 MySQLのDBは、弊社で標準で使用しているMariaDBを選択しました。 今回行ったことは、同じ環境にMariaDB、PostgreSQLをインストールし、設定はいじらず、500万行のCSVのインポート、インポートしたデータでいくつかの検索クエリを実行し、その速度を比較しました。

MySQLとPostgreSQLともにチューニングしだいで下記の結果は変わってくるのは承知のうえで、一旦、公平にチューニング無しの状態での速度比較記事となります。その点はご了承下さい。

実行環境

Ubuntu 22.04 4CPUx2.592GHz 8GBRAM

MariaDBのインストール

https://mariadb.org/download/?t=repo-config

↑このページでUbuntu 22.04を選択、現時点で最新の安定バージョン11.1を選択し、リポジトリ登録、インストールを行いました。

$ sudo mkdir -p /etc/apt/keyrings
$ sudo curl -o /etc/apt/keyrings/mariadb-keyring.pgp 'https://mariadb.org/mariadb_release_signing_key.pgp'

$ cat /etc/apt/sources.list.d/mariadb.sources
# MariaDB 11.1 repository list - created 2023-09-29 08:40 UTC
# https://mariadb.org/download/
X-Repolib-Name: MariaDB
Types: deb
# deb.mariadb.org is a dynamic mirror if your preferred mirror goes offline. See https://mariadb.org/mirrorbits/ for details.
# URIs: https://deb.mariadb.org/11.1/ubuntu
URIs: https://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/11.1/ubuntu
Suites: jammy
Components: main main/debug
Signed-By: /etc/apt/keyrings/mariadb-keyring.pgp

$ sudo apt-get update
$ sudo apt-get install mariadb-server

$ mariadb --version
mariadb from 11.1.2-MariaDB, client 15.2 for debian-linux-gnu (x86_64) using  EditLine wrapper

PostgreSQLのインストール

https://www.postgresql.org/download/linux/ubuntu/

↑このページの手順に従ってリポジトリを登録、最新のPostgreSQL16をインストールしました。

$ sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo apt-get update
$ sudo apt-get -y install postgresql

$ apt seach postgresql-16
$ apt install postgresql-16

$ sudo -u postgres psql --version
psql (16.0 (Ubuntu 16.0-1.pgdg22.04+1))

テストデータ、テーブルの作成

今回使用したテストデータは、国税庁の「法人番号公表サイト」の基本3情報の全件データを利用しました。

https://www.houjin-bangou.nta.go.jp/download/zenken/

↑CSV形式・Unicodeの全国版をダウンロードします。

PostgreSQL用のテーブル構造ですが、

https://one-it-thing.com/4861/#i-7

こちらのサイトを参考に以下のように作成しました。

# データベース作成
CREATE DATABASE enterprises;
# データベース接続
\c enterprises
# テーブル作成
create table enterprises (
   "sequenceNumber" integer
 , "corporateNumber" char(13) not null
 , "process" char(2) not null
 , "correct" char(1) not null
 , "updateDate" date
 , "changeDate" date
 , "name" varchar(150)
 , "nameImageId" char(8)
 , "kind" char(3) not null
 , "prefectureName" varchar(10)
 , "cityName" varchar(20)
 , "streetNumber" varchar(300)
 , "addressImageId" char(8)
 , "prefectureCode" char(2)
 , "cityCode" char(3)
 , "postCode" char(7)
 , "addressOutside" varchar(300)
 , "addressOutsideImageId" char(8)
 , "closeDate" date
 , "closeCause" char(2)
 , "successorCorporateNumber" char(13)
 , "changeCause" varchar(500)
 , "assignmentDate" date
 , "latest" char(1) not null
 , "enName" varchar(300)
 , "enPrefectureName" varchar(9)
 , "enCityName" varchar(600)
 , "enAddressOutside" varchar(600)
 , "furigana" varchar(500)
 , "hihyoji" char(1)
 , primary key ("sequenceNumber")
) ;

MariaDB用のテーブル構造は以下のサイトを参考にして作成しました。 https://www.ipvx.info/everyones-db/corp_code-nta/import-env-pre/

# データベース作成
CREATE DATABASE enterprises;
# データベース接続
USE enterprises;
# テーブル作成
CREATE TABLE `enterprises` (
`sequenceNumber` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '都道府県コード+データシーケンス10桁、0埋め',
`corporateNumber` bigint(11) unsigned NOT NULL,
`process` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '01 新規/11 商号又は名称の変更/12 国内所在地/13 国外所在地/21 登記記録/22 登記記録/71 吸収合併/72 吸収合併無効/81 商号の登記の抹消/99 削除',
`correct` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '-/0 訂正以外/1 訂正',
`updateDate` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`changeDate` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`name` varchar(256) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`nameImageId` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`kind` char(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '- -\n101 国の機関\n201 地方公共団体\n301 株式会社  値「301」は、株式会社の情報であることを意味する。\n302 有限会社  値「302」は、特例有限会社の情報であることを意味する。\n303 合名会社  値「303」は、合名会社の情報であることを意味する。\n304 合資会社\n305 合同会社\n399その他の設立登記法人\n401 外国会社等\n499 その他',
`prefectureName` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`cityName` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`streetNumber` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`addressImageId` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`prefectureCode` char(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`cityCode` char(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`postCode` char(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`addressOutside` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`addressOutsideImageId` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`closeDate` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`closeCause` char(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`successorCorporateNumber` char(13) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`changeCause` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`assignmentDate` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`latest` char(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '0 過去情報  値「0」は、過去の情報であることを意味する。\n1 最新情報',
`enName` varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`enPrefectureName` varchar(9) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`enCityName` varchar(600) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`enAddressOutside` varchar(600) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`furigana` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`ext_notes` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`ext_delflg` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`sequenceNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CSVインポート

CSVをインポートします。 2023年08月31日時点で約536万件を確認しました

$ cat 00_zenkoku_all_20230831.csv |wc -l
5360702

PostgreSQLでCSVをインポートします。 PostgreSQLは、デフォルトでクエリの実行時間を出力しないため、\timingを打っておきます。

\timing
enterprises=# copy enterprises from '/var/lib/postgresql/15/main/00_zenkoku_all_20230831.csv' with csv;
COPY 5360702
Time: 24542.808 ms (00:24.543)

取り込みは約24秒でした。 次にMariaDBで取り込みを行います。

SET GLOBAL local_infile=on;

load data LOCAL infile '/var/lib/mysql/00_zenkoku_all_20230831.csv' into table enterprises FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY ''
LINES STARTING BY '' TERMINATED BY '\n' IGNORE 0 LINES
(sequenceNumber, corporateNumber, process, correct, updateDate, changeDate, name, nameImageId, kind, prefectureName,cityName, streetNumber, addressImageId, prefectureCode, cityCode, postCode, addressOutside, addressOutsideImageId,closeDate, closeCause, successorCorporateNumber, changeCause, assignmentDate, latest, enName, enPrefectureName, enCityName, enAddressOutside, furigana);

Query OK, 5360702 rows affected, 65535 warnings (1 min 15.341 sec)
Records: 5360702  Deleted: 0  Skipped: 0  Warnings: 5360702

取り込みが約1分15秒となりPostgreSQLが速い結果になりました。

検索クエリの速度比較

本題の検索速度の比較です。 PostgreSQL、MariaDBの順に記録してます。 クエリは、数回連続で実行してもそれほど時間が変わらなかったため、最初の記録を表示しております。 結果は最後にまとめてあります。

全行カウント

# PostgreSQL
\timing

enterprises=# select count(*) from enterprises;
  count  
---------
 5360702
(1 row)

Time: 2643.153 ms (00:02.643)
MariaDB [enterprises]> select count(*) from enterprises;
+----------+
| count(*) |
+----------+
|  5360702 |
+----------+
1 row in set (15.022 sec)

完全一致検索

# PostgreSQL
\timing

enterprises=# SELECT prefectureName,name FROM enterprises WHERE name = '株式会社ルーター';
 prefecturename |       name       
----------------+------------------
 東京都         | 株式会社ルーター
 大阪府         | 株式会社ルーター
(2 rows)

Time: 332.918 ms
MariaDB [enterprises]> SELECT prefectureName,name FROM enterprises WHERE name = '株式会社ルーター';
+----------------+--------------------------+
| prefectureName | name                     |
+----------------+--------------------------+
| 東京都         | 株式会社ルーター         |
| 大阪府         | 株式会社ルーター         |
+----------------+--------------------------+
2 rows in set (12.020 sec)

あいまい検索(前方一致)

# PostgreSQL
\timing
enterprises=# SELECT prefectureName,name FROM enterprises WHERE name like 'ルーター%';
 prefecturename |        name        
----------------+--------------------
 東京都         | ルーターズ株式会社
(1 row)

Time: 284.137 ms
MariaDB [enterprises]> SELECT prefectureName,name FROM enterprises WHERE name like 'ルーター%';
+----------------+-----------------------------+
| prefectureName | name                        |
+----------------+-----------------------------+
| 東京都         | ルーターズ株式会社          |
+----------------+-----------------------------+
1 row in set (12.427 sec)

あいまい検索(中間一致)

# PostgreSQL
\timing
enterprises=# SELECT prefectureName,name FROM enterprises WHERE name like '%ルーター%';
 prefecturename |                  name                  
----------------+----------------------------------------
 東京都         | 株式会社オール・ルーターズ
 東京都         | リクルーターズ中野株式会社
 東京都         | 株式会社ルーターズ
 東京都         | 株式会社ルーターズ
 福岡県         | ロート・ルーターサービス株式会社福岡
 北海道         | 株式会社アグリルーターズ
 千葉県         | ロート・ルーターサービス千葉株式会社
 東京都         | リクルーター株式会社
 東京都         | ロート・ルーターサービス株式会社城南
 神奈川県       | 株式会社ルーターズ・アンリミテッド
 大阪府         | 株式会社ルーター
 大阪府         | ロート・ルーターサービス株式会社大阪
 沖縄県         | ブルータートルファーム株式会社
 福島県         | NPO法人うつくしまスポーツルーターズ
 埼玉県         | 株式会社ルーターホールディングス
 東京都         | 株式会社ルーター
 東京都         | ロート・ルーターサービス株式会社
 東京都         | リクルーターズ株式会社
 東京都         | 株式会社ボイドルーターシステムズ
 東京都         | ルーターズ株式会社
 東京都         | 株式会社ルータース
 大阪府         | 株式会社サービスルーター
(22 rows)

Time: 409.332 ms
MariaDB [enterprises]> SELECT prefectureName,name FROM enterprises WHERE name like '%ルーター%';
+----------------+-----------------------------------------------------------+
| prefectureName | name                                                      |
+----------------+-----------------------------------------------------------+
| 北海道         | 株式会社デイリー・ブルーダー                              |
| 北海道         | 株式会社アグリルーターズ                                  |
| 福島県         | NPO法人うつくしまスポーツルーターズ                    |
| 埼玉県         | 株式会社ルーターホールディングス                          |
| 千葉県         | ロート・ルーターサービス千葉株式会社                      |
| 東京都         | 株式会社オール・ルーターズ                                |
| 東京都         | リクルーターズ中野株式会社                                |
| 東京都         | 株式会社ルーター                                          |
| 東京都         | ロート・ルーターサービス株式会社                          |
| 東京都         | リクルーターズ株式会社                                    |
| 東京都         | アポテーカーブルーダー株式会社                            |
| 東京都         | 株式会社ルーターズ                                        |
| 東京都         | 株式会社ボイドルーターシステムズ                          |
| 東京都         | ルーターズ株式会社                                        |
| 東京都         | 株式会社ルーターズ                                        |
| 東京都         | リクルーター株式会社                                      |
| 東京都         | 株式会社ルータース                                        |
| 東京都         | ロート・ルーターサービス株式会社城南                      |
| 神奈川県       | 株式会社ルーターズ・アンリミテッド                        |
| 滋賀県         | 株式会社ペルーダー                                        |
| 大阪府         | 株式会社サービスルーター                                  |
| 大阪府         | 株式会社ルーター                                          |
| 大阪府         | ロート・ルーターサービス株式会社大阪                      |
| 広島県         | 株式会社ペントルーダー・ジャパン                          |
| 福岡県         | ロート・ルーターサービス株式会社福岡                      |
| 沖縄県         | ブルータートルファーム株式会社                            |
+----------------+-----------------------------------------------------------+
26 rows in set (11.014 sec)

あいまい検索(後方一致)

# PostgreSQL
\timing
enterprises=# SELECT prefectureName,name FROM enterprises WHERE name like '%ルーター';
 prefecturename |           name           
----------------+--------------------------
 大阪府         | 株式会社ルーター
 東京都         | 株式会社ルーター
 大阪府         | 株式会社サービスルーター
(3 rows)

Time: 414.851 ms
MariaDB [enterprises]> SELECT prefectureName,name FROM enterprises WHERE name like '%ルーター';
+----------------+--------------------------------------------+
| prefectureName | name                                       |
+----------------+--------------------------------------------+
| 北海道         | 株式会社デイリー・ブルーダー               |
| 東京都         | 株式会社ルーター                           |
| 滋賀県         | 株式会社ペルーダー                         |
| 大阪府         | 株式会社サービスルーター                   |
| 大阪府         | 株式会社ルーター                           |
+----------------+--------------------------------------------+
5 rows in set (10.817 sec)

PrimaryKey(sequenceNumber)での検索

# PostgreSQL
\timing
enterprises=# SELECT sequenceNumber,prefectureName,name FROM enterprises WHERE sequenceNumber=1324875;
 sequencenumber | prefecturename |       name       
----------------+----------------+------------------
        1324875 | 東京都         | 株式会社ルーター
(1 row)

Time: 0.319 ms
MariaDB [enterprises]> SELECT sequenceNumber,prefectureName,name FROM enterprises WHERE sequenceNumber=1324875;
+----------------+----------------+--------------------------+
| sequenceNumber | prefectureName | name                     |
+----------------+----------------+--------------------------+
|        1324875 | 東京都         | 株式会社ルーター         |
+----------------+----------------+--------------------------+
1 row in set (0.003 sec)

結果

比較クエリ PostgreSQL(秒) MariaDB(秒)
536万件のCSV取り込み 24.5 75.3
全行カウント 2.6 15
完全一致検索 0.3 12
あいまい検索(前方一致) 0.28 12.4
あいまい検索(中間一致) 0.4 11
あいまい検索(後方一致) 0.4 10.8
PrimaryKey(sequenceNumber)での検索 0.0003 0.003

すべてのクエリでPostgreSQLがはやい結果になりました。 設定のチューニングなどを行わずに、PostgreSQLがMariaDBに比べてここまで速いのには驚きました。 自分のようにPostgreSQLを食わず嫌いしていた方はぜひ一度試してみてください。 次回は、PostgreSQLとMySQLでindexを使った場合の速度比較をしてみたいと思います。

Pocket

CONTACT

お問い合わせ・ご依頼はこちらから