SQL基本ステートメントごとの実行速度をDocker上で実験してみた

これは何？

SELECT / UPDATE / INSERT / DELETE の実行速度とその理由を調べ、Docker 上の MySQL で実験してみました。

なぜこの記事を書いたの？

データベースのパフォーマンスは、アプリケーション全体の応答性やスケーラビリティに直結します。またリレーショナルデータベースでは、テーブル設計を後から変更することは容易ではないことが多いため、パフォーマンスの高いテーブル設計は重要です。そのため、設計段階で SQL 自体のパフォーマンス特性を理解しておくことが重要になります。

業務で新しい機能を作るときに、要件定義をもとにデータベーステーブルの設計を行うことがあったのですが、その際に、パフォーマンスの高いテーブル設計とはどういうものか、を考える必要があったため基本的な内容を調べてみました。

前提

• MySQL 8.0.32

Table of Contents

1. クエリの基本動作
2. ステートメントの基本動作（SELECT / UPDATE / INSERT / DELETE）
3. インデックスの基本動作
4. 検証環境準備（Docker 構成、データ量など）
5. 検証
6. まとめ

1. クエリの基本動作

クエリの基本動作は以下の通りです。

• クエリの解析
• 実行計画の生成
• 実行
• 結果の返却

また上記の大前提として、DB への接続、認証、接続クローズが行われます。上記は SQL ステートメントの種類に関わらず行われ、それぞれで時間コストが発生します。今回はこれらの最適化ではなく、あくまで SQL ステートメントの実行速度について検証します。

2. ステートメントの基本動作

まず基本的なステートメントがどのように実行されるのかを勉強しました。

SELECT

SELECT クエリはデータベースからデータを取得するための基本操作です。テーブルから指定した列のデータを抽出し、条件に基づいてフィルタリングや並べ替えを行います。

SELECT [列名] FROM [テーブル名] WHERE [条件] ORDER BY [列名] [ASC|DESC] LIMIT [数値]

1. テーブルアクセス: WHERE 句の条件に基づき、以下のいずれかの方法でアクセス
2. レコード検索:
   a. インデックスが利用可能: B-tree インデックスを使って該当レコードを効率的に特定
   b. インデックスがない場合: フルテーブルスキャンで全レコードを検査
3. 結果セット生成: 条件に合致したレコードから必要な列だけを抽出
4. 整列・加工処理: GROUP BY/ORDER BY がある場合は一時テーブルを作成して処理
5. 結果返却: クライアントに結果セットを返却

UPDATE

UPDATE クエリはテーブル内の既存データを変更します。指定した条件に一致する行の列値を更新します。

UPDATE [テーブル名] SET [列名] = [新しい値] WHERE [条件]

1. テーブルアクセス: WHERE 句の条件に基づき、以下のいずれかの方法でアクセス
2. レコード検索:
   a. インデックスが利用可能: B-tree インデックスを使って該当レコードを効率的に特定
   b. インデックスがない場合: フルテーブルスキャンで全レコードを検査
3. ロック取得: 該当レコードに排他ロックを取得
4. データ変更: レコードのデータページ上で該当列の値を更新
5. (更新列にインデックスがある場合)
   5-1. 古いインデックスエントリを削除
   5-2. 新しい値に基づくインデックスエントリを追加
6. 必要に応じて B-tree の分割・再バランス処理
7. ログ記録: ログに変更内容を記録
8. コミット/ロールバック: トランザクション終了時に確定/破棄

INSERT

INSERT は、テーブルにデータを挿入するためのクエリです。

INSERT INTO [テーブル名] ([列名]) VALUES ([値])

1. 空き領域確認: 新規レコード用の空きスペースをデータファイル内で検索
   a. 空き領域がない場合: データファイルの拡張
   b. 空き領域がある場合: 空き領域にレコードを書き込む
2. データ書き込み: レコードをデータページに書き込み
3. インデックス更新: 各インデックスに新規エントリを追加
   3-1. プライマリキーインデックス: B-tree 構造に挿入
   3-2. セカンダリインデックス: 各インデックスツリーに挿入
4. 必要に応じて B-tree の分割・再バランス処理
5. ユニーク制約確認: 制約があれば一意性チェック（通常はインデックスを使用）
6. 外部キー制約確認: 参照整合性チェック
7. ログ記録: ログに挿入内容を記録

DELETE

DELETE は、テーブルのデータを削除するためのクエリです。

DELETE FROM [テーブル名] WHERE [条件]

1. テーブルアクセス: WHERE 句の条件に基づき、以下のいずれかの方法でアクセス
2. レコード検索
   a. インデックスが利用可能: B-tree インデックスを使って該当レコードを効率的に特定
   b. インデックスがない場合: フルテーブルスキャンで全レコードを検査
3. ロック取得: 該当レコードに排他ロックを取得
4. インデックス更新: すべてのインデックスから該当エントリを削除
   4-1. プライマリキーインデックス: B-tree 構造からエントリを削除
   4-2. セカンダリインデックス: 各インデックスツリーからエントリを削除
5. B-tree の再バランス処理が発生する場合あり
6. データ削除:
   a. 論理削除: レコードを「削除済み」としてマーク（実際のデータはすぐには消えない）
   b. 物理削除: ストレージ管理システムが後で領域を再利用可能に
7. ログ記録: ログに削除操作を記録
8. コミット処理: トランザクション終了時に確定

それぞれのステートメントの実行速度

上記それぞれのステートメントのスピードはさまざまな条件によって変化します。が、基本的なスピードとしては

(速) SELECT > DELETE ≈ INSERT > UPDATE (遅)

となります。(条件次第で可変のため、あくまで一般的なケースでの傾向です。)

まず UPDATE や DELETE が遅い理由として、以下が挙げられます。

• 対象レコード特定 → 更新/削除処理を行う必要があること
• 変更にあたって、行レベルまたはページレベルのロック取得が必要であること
• 更新/削除にあたって、インデックスからの削除や更新が必要であること

また INSERT 文が遅い理由として、以下が挙げられます。

• 空きページがない場合、新規ページ割り当てが必要。その際にページ分割が発生する可能性があること
• ユニーク制約確認: 制約があれば一意性チェック（通常はインデックスを使用）
• 外部キー制約確認: 参照整合性チェック

DELETE, UPDATE の速度を決める要因として大きいのが、対象レコード特定処理です。この検索が高速かどうかによっては、INSERT よりも UPDATE の方が速くなる可能性も十分にあります。

3. インデックスの基本動作

インデックスは、データベースのパフォーマンスを向上させるための重要な機能です。今回は、最も一般的な B-tree インデックスがどのように動作するかを説明します。

これは内部的には、データベースがデータを効率的に検索できるようにするためのものです。インデックスがない場合、データベースは目的のデータを見つけるために全てのデータを検索することになります。これはフルスキャンと呼ばれ、レコード数 n に対して、O(n) の時間がかかります。
インデックスは、対象のデータと配置されているデータページの保存場所を紐付けて保持するバイナリーツリー構造です。そのため、対象のデータに対して、O(log2(n)) の時間で検索でき、データベースのパフォーマンスを向上させます。この構造は、バイナリーツリーのため、等号や不等号、区間検索 でも有効な絞り込みが可能ですが、NOT や OR などでは有効ではありません。
具体的に B-tree インデックスでは、以下の特徴があります：

• ルートノード: 最上位の親ノードで、子ノードへのポインタを持つ
• 内部ノード: 探索経路を導くキー値と子ノードへのポインタを持つ
• リーフノード: 実際のキー値とテーブルの行データへのポインタ（または行データそのもの）を格納

またバイナリーツリーのため、インデックスはソート済みであり、ORDER BY 句がインデックス列と一致する場合、追加のソート処理が不要になるという利点もあります。

4. 検証環境準備

今回は、Docker 上に MySQL 8.0.32 を構築し、以下のようにして検証テーブルを作成しました。

-- 存在する場合は削除
DROP PROCEDURE IF EXISTS insert_customers;
DROP PROCEDURE IF EXISTS insert_orders;

-- テーブル作成
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  created_at DATETIME
);

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  product_name VARCHAR(100),
  amount DECIMAL(10,2),
  order_date DATETIME,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- 20万件の顧客データを作成
DELIMITER $$
CREATE PROCEDURE insert_customers()
BEGIN
    DECLARE i INT DEFAULT 1;

    -- 外部キー制約を一時的に無効化
    SET FOREIGN_KEY_CHECKS = 0;

    START TRANSACTION;
    WHILE i <= 200000 DO
        INSERT INTO customers (name, email, created_at)
        VALUES (
            CONCAT('Customer_', i),
            CONCAT('customer_', i, '@example.com'),
            NOW() - INTERVAL FLOOR(RAND() * 1000) DAY
        );

        -- 1万件ごとにコミット
        IF i % 10000 = 0 THEN
            COMMIT;
            SELECT CONCAT('Inserted customers: ', i);
            START TRANSACTION;
        END IF;

        SET i = i + 1;
    END WHILE;
    COMMIT;

    -- 外部キー制約を有効化
    SET FOREIGN_KEY_CHECKS = 1;
END$$
DELIMITER ;

-- 注文データ生成
DELIMITER $$
CREATE PROCEDURE insert_orders(IN total INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE max_customer_id INT;

    -- 最大顧客IDの取得
    SELECT MAX(id) INTO max_customer_id FROM customers;

    -- 外部キー制約を一時的に無効化
    SET FOREIGN_KEY_CHECKS = 0;

    START TRANSACTION;
    WHILE i <= total DO
        INSERT INTO orders (customer_id, product_name, amount, order_date)
        VALUES (
            FLOOR(1 + RAND() * max_customer_id),
            CONCAT('Product_', FLOOR(1 + RAND() * 100)),
            ROUND(RAND() * 10000, 2),
            NOW() - INTERVAL FLOOR(RAND() * 365) DAY
        );

        -- 1万件ごとにコミット
        IF i % 10000 = 0 THEN
            COMMIT;
            SELECT CONCAT('Inserted orders: ', i, ' / ', total);
            START TRANSACTION;
        END IF;

        SET i = i + 1;
    END WHILE;
    COMMIT;

    -- 外部キー制約を有効化
    SET FOREIGN_KEY_CHECKS = 1;
END$$
DELIMITER ;

-- 実行方法:
-- CALL insert_customers();
-- CALL insert_orders(1000000);

上記ファイルを test.sql として保存し、docker 内にコピー、実行します。

# コンテナ内でSQLファイルをコピー（ホストからコンテナへ）
docker cp test.sql [コンテナ名またはID]:/tmp/

# コンテナ内でSQL実行
mysql -u root -p your_database_name < /tmp/test.sql

# プロシージャ実行
mysql -u root -p -e "USE your_database_name; CALL insert_customers(); CALL insert_orders(1000000);"

今回はパフォーマンステストのため、20 万件の顧客データと 100 万件の注文データを作成しました。

5. 検証

インデックスを使わない SELECT クエリ

まずインデックスを使わない、SELECT クエリを実行してみます。EXPLAIN を使用して、SQL の実行計画を確認し、どのような処理が行われているかを確認します。

EXPLAIN SELECT * FROM customers WHERE email = '[e-mail]';

結果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	customers	NULL	ALL	NULL	NULL	NULL	NULL	199214	10.00	Using where

特に注目したいのが以下の 3 点です。

1. 検索タイプ (type): 「ALL」 → これはフルスキャンを示しています。
2. スキャン行数 (rows): 199214 → これは MySQL が予測する、参照される行数です。約 20 万行となっています。
3. 使用インデックス (key): NULL → インデックスが全く使用されていません。

実際にどのくらいに時間がかかるかは、以下のように実行してみます。

EXPLAIN ANALYZE SELECT * FROM customers WHERE email = '[e-mail]';

結果

-> Filter: (customers.email = \'[e-mail]\')  (cost=20154 rows=19921) (actual time=4.84..87.7 rows=2 loops=1)
-> Table scan on customers  (cost=20154 rows=199214) (actual time=0.168..55.6 rows=200000 loops=1)

これは、スキャン 1 行に 0.168ms, 200000 行に 55.6ms かかっています。また 途中からフィルタリングが開始され、87.7ms かかっています。
そのため、最終的には 87.7ms かかるということがわかります。

インデックスを使った SELECT クエリ

次にインデックスが存在するケースです。Primary Key には B-tree インデックスが存在するため、高速に検索できます。

EXPLAIN SELECT * FROM customers WHERE id = 5555;

結果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	customers	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	NULL

上記の結果と比べると、

1. 検索タイプ (type): 「const」 → これは主キーによる高速な検索を示しています
2. スキャン行数 (rows): 1 → これは MySQL が予測する、参照される行数です。1 行となっています
3. 使用インデックス (key): PRIMARY → 主キーによる検索が使用されています

実際にどのくらいに時間がかかるかは、以下のように実行してみます。

EXPLAIN ANALYZE SELECT * FROM customers WHERE id = 5555;

結果

-> Rows fetched before execution  (cost=0..0 rows=1) (actual time=166e-6..249e-6 rows=1 loops=1)

これは、166e-6ms, つまり 0.166ms で検索できています。インデックスを使用したため、非常に高速に検索できています。

インデックスを有効使用することで、かなりの時間短縮が可能です。ではここからは、各ステートメントごとにそれぞれ検証してみます。
EXPLAIN ANALYZE は SELECT クエリのみ使用可能なため、プロファイリングを使ってそれぞれの実行時間を計測します。

インデックスを使わない UPDATE クエリ

SET profiling = 1;
UPDATE customers SET email = '[e-mail]' WHERE email = '[e-mail]';
SHOW PROFILES;

結果

Query ID	実行時間(秒)
1	0.09344050

インデックスを使った UPDATE クエリ

SET profiling = 1;
UPDATE customers SET email = '[e-mail]' WHERE id = 5555;
SHOW PROFILES;

結果

Query ID	実行時間(秒)
2	0.00860600

これは、8.6ms で更新できています。

INSERT クエリ

SET profiling = 1;
INSERT INTO customers (email) VALUES ('[e-mail]');
SHOW PROFILES;

結果

Query ID	実行時間(秒)
3	0.00868300

これは、8.68ms で更新できています。
若干 UPDATE よりも遅いですが、上述した通り、これはテーブルの状況によって変化します。たとえば、インデックスを使わない場合は、INSERT の方が速くなることが容易に想像できます。

インデックスを使わない DELETE クエリ

SET profiling = 1;
DELETE FROM customers WHERE email = '[e-mail]';
SHOW PROFILES;

結果

Query ID	実行時間(秒)
4	0.09640550

インデックスを使った DELETE クエリ

SET profiling = 1;
DELETE FROM customers WHERE id = 5555;
SHOW PROFILES;

結果

Query ID	実行時間(秒)
5	0.00639775

これは、6.39ms で削除できています。UPDATE や INSERT よりも高速となりました。

6. まとめ

結果をまとめたのが以下の図です。

結果のまとめ

結果をまとめて、上から早い順に並べたのが以下の表です。

クエリ	インデックス有無	実行時間(秒)
SELECT	有	0.00166
DELETE	有	0.00640
UPDATE	有	0.00861
INSERT	-	0.00868
SELECT	無	0.08770
UPDATE	無	0.09344
DELETE	無	0.09640

検証した結果、テーブルから WHERE 句で検索をしている SELECT / UPDATE / DELETE は、インデックスを使うことで速度が向上することがわかりました。インデックス有無で、UPDATE / DELETE の
順序が変わっていますが、これはまだ調べきれていない内部処理が関わっている気がします。

一般的には、MySQL は DELETE の速度が遅いと言われていますが、今回の結果だけを見ると大きな差は出ませんでした。

これらから、以下のような結論が出ました。

• インデックスを使うことで、SELECT / UPDATE / DELETE の速度が向上する
• 比較すると INSERT の速度はインデックスに左右されづらい
  • レコード検索が高速であれば、UPDATE, DELETE は速くなる
  • INSERT は、レコードを追加し、インデックスを貼り直すため、レコード検索スピードに左右されない
• テーブルの最終形を想定して、どのようなクエリが実行されるべきかを考える必要がある

今後は、同様のテーブルを使ってJOINやORDER BYやGROUP BYなどのクエリの検証を行い、その速度を計測してみたいと思います。

参考文献

• MySQL 8.0 Reference Manual