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

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

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

#### UPDATE

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

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

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

#### INSERT

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

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

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

#### DELETE

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

```sql
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 を構築し、以下のようにして検証テーブルを作成しました。

```sql
-- 存在する場合は削除
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 内にコピー、実行します。

```bash
# コンテナ内で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 の実行計画を確認し、どのような処理が行われているかを確認します。

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

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

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

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

**結果**

```sql
-> 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 インデックスが存在するため、高速に検索できます。

```sql
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 → 主キーによる検索が使用されています

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

```sql
EXPLAIN ANALYZE SELECT * FROM customers WHERE id = 5555;
```

**結果**

```sql
-> 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 クエリ

```sql
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 クエリ

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

**結果**

| Query ID | 実行時間(秒) |
| -------- | ------------ |
| 2        | 0.00860600   |

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

#### INSERT クエリ

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

**結果**

| Query ID | 実行時間(秒) |
| -------- | ------------ |
| 3        | 0.00868300   |

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

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

```sql
SET profiling = 1;
DELETE FROM customers WHERE email = 'customer_5557@example.com';
SHOW PROFILES;
```

**結果**

| Query ID | 実行時間(秒) |
| -------- | ------------ |
| 4        | 0.09640550   |

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

```sql
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](https://dev.mysql.com/doc/refman/8.0/en/)