MAGAZINE
ルーターマガジン
インフラ/運用
MySQLとPostgreSQLの速度比較
2023.11.07
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 wrapperPostgreSQLのインストール
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
5360702PostgreSQLで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 msMariaDB [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 msMariaDB [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
----------------+----------------------------------------
東京都 | 株式会社オール・ルーターズ
東京都 | リクルーターズ中野株式会社
東京都 | 株式会社ルーターズ
東京都 | 株式会社ルーターズ
福岡県 | ロート・ルーターサービス株式会社福岡
北海道 | 株式会社アグリルーターズ
千葉県 | ロート・ルーターサービス千葉株式会社
東京都 | リクルーター株式会社
東京都 | ロート・ルーターサービス株式会社城南
神奈川県 | 株式会社ルーターズ・アンリミテッド
大阪府 | 株式会社ルーター
大阪府 | ロート・ルーターサービス株式会社大阪
沖縄県 | ブルータートルファーム株式会社
福島県 | ＮＰＯ法人うつくしまスポーツルーターズ
埼玉県 | 株式会社ルーターホールディングス
東京都 | 株式会社ルーター
東京都 | ロート・ルーターサービス株式会社
東京都 | リクルーターズ株式会社
東京都 | 株式会社ボイドルーターシステムズ
東京都 | ルーターズ株式会社
東京都 | 株式会社ルータース
大阪府 | 株式会社サービスルーター
(22 rows)
Time: 409.332 msMariaDB [enterprises]> SELECT prefectureName,name FROM enterprises WHERE name like '%ルーター%';
+----------------+-----------------------------------------------------------+
| prefectureName | name |
+----------------+-----------------------------------------------------------+
| 北海道 | 株式会社デイリー・ブルーダー |
| 北海道 | 株式会社アグリルーターズ |
| 福島県 | ＮＰＯ法人うつくしまスポーツルーターズ |
| 埼玉県 | 株式会社ルーターホールディングス |
| 千葉県 | ロート・ルーターサービス千葉株式会社 |
| 東京都 | 株式会社オール・ルーターズ |
| 東京都 | リクルーターズ中野株式会社 |
| 東京都 | 株式会社ルーター |
| 東京都 | ロート・ルーターサービス株式会社 |
| 東京都 | リクルーターズ株式会社 |
| 東京都 | アポテーカーブルーダー株式会社 |
| 東京都 | 株式会社ルーターズ |
| 東京都 | 株式会社ボイドルーターシステムズ |
| 東京都 | ルーターズ株式会社 |
| 東京都 | 株式会社ルーターズ |
| 東京都 | リクルーター株式会社 |
| 東京都 | 株式会社ルータース |
| 東京都 | ロート・ルーターサービス株式会社城南 |
| 神奈川県 | 株式会社ルーターズ・アンリミテッド |
| 滋賀県 | 株式会社ペルーダー |
| 大阪府 | 株式会社サービスルーター |
| 大阪府 | 株式会社ルーター |
| 大阪府 | ロート・ルーターサービス株式会社大阪 |
| 広島県 | 株式会社ペントルーダー・ジャパン |
| 福岡県 | ロート・ルーターサービス株式会社福岡 |
| 沖縄県 | ブルータートルファーム株式会社 |
+----------------+-----------------------------------------------------------+
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 msMariaDB [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 msMariaDB [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を使った場合の速度比較をしてみたいと思います。
CONTACT
お問い合わせ・ご依頼はこちらから