これは何？

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

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

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

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

前提

MySQL 8.0.32

Table of Contents

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

1. クエリの基本動作

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

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

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

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

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

SELECT

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

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

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

UPDATE

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

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

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

INSERT

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

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

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

DELETE

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

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

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

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

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

(速) 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 = 'customer_5555@example.com';

結果

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 点です。

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

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

EXPLAIN ANALYZE SELECT * FROM customers WHERE email = 'customer_5555@example.com';

結果

-> Filter: (customers.email = \'customer_5555@example.com\')  (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

上記の結果と比べると、

検索タイプ (type): 「const」 → これは主キーによる高速な検索を示しています
スキャン行数 (rows): 1 → これは MySQL が予測する、参照される行数です。1 行となっています
使用インデックス (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 = 'new_email@example.com' WHERE email = 'customer_5555@example.com';
SHOW PROFILES;

結果

Query ID	実行時間(秒)
1	0.09344050

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

SET profiling = 1;
UPDATE customers SET email = 'customer_5555@example.com' WHERE id = 5555;
SHOW PROFILES;

結果

Query ID	実行時間(秒)
2	0.00860600

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

INSERT クエリ

SET profiling = 1;
INSERT INTO customers (email) VALUES ('new_email@example.com');
SHOW PROFILES;

結果

Query ID	実行時間(秒)
3	0.00868300

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

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

SET profiling = 1;
DELETE FROM customers WHERE email = 'customer_5557@example.com';
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