🐘
DBチューニングを試してみる
ぱお〜〜ん🐘
9月は祝日多くて嬉しいね！
やること
DBのチューニングについて、理解度が浅かったので初めの一歩として簡易的な実験をして、理解度を深めます
具体的には、テーブルに対してインデックスを設定して、どれだけクエリが早くなるかということを実験します
使用したコードはこちらです
目次
実験環境の構築
データベース設計
ECサイトを想定した以下のテーブル構成で実験を行います：
-- カテゴリテーブル
CREATE TABLE categories (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 商品テーブル
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
price DECIMAL(10,2) NOT NULL,
category_id INT,
stock_quantity INT DEFAULT 0,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES categories(id)
);
-- 注文テーブル
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
product_id INT,
quantity INT NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (product_id) REFERENCES products(id)
);
サンプルデータ
- カテゴリ: 20件
- 商品: 100,000件（アクティブ: 75,269件）
- 注文: 500,000件
- 総売上: 約687億円
リアルなECサイトのデータ構造を再現し、過去1年間の商品データと過去6ヶ月の注文データを生成しました。
分析：ボトルネックの特定
測定したクエリ
実務でよく使われる**商品一覧表示（ページネーション）**のクエリでパフォーマンスを測定します：
-- 商品一覧表示クエリ
SELECT * FROM products
WHERE is_active = TRUE
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
このクエリは、ECサイトで最も頻繁に実行されるクエリの一つで、以下の処理を行います：
- アクティブな商品のみを取得（
is_active = TRUE） - 作成日時の新しい順に並び替え（
ORDER BY created_at DESC） - 上位20件のみを表示（
LIMIT 20）
クエリ実行時間の測定方法
パフォーマンス測定は、Pythonのtimeモジュールを使用して実装します
import time
import statistics
def execute_query(self, query_name, query, params=None, iterations=5):
"""クエリを複数回実行して性能を測定"""
execution_times = []
for i in range(iterations):
# 開始時刻を記録
start_time = time.time()
try:
# クエリを実行
self.cursor.execute(query, params)
# 結果を取得（実際のアプリケーションでは必要）
results = self.cursor.fetchall()
# 終了時刻を記録
end_time = time.time()
execution_time = end_time - start_time
execution_times.append(execution_time)
print(f"実行 {i+1}: {execution_time:.4f}秒 (結果件数: {len(results)})")
except mysql.connector.Error as err:
print(f"クエリエラー: {err}")
return None
# 統計を計算
avg_time = statistics.mean(execution_times)
min_time = min(execution_times)
max_time = max(execution_times)
std_dev = statistics.stdev(execution_times) if len(execution_times) > 1 else 0
print(f"平均実行時間: {avg_time:.4f}秒")
実際の測定結果
実行 1: 0.0333秒 (結果件数: 20)
実行 2: 0.0240秒 (結果件数: 20)
実行 3: 0.0694秒 (結果件数: 20)
実行 4: 0.0301秒 (結果件数: 20)
実行 5: 0.0212秒 (結果件数: 20)
平均実行時間: 0.0356秒
問題の分析
- 今回はEXPLAINを用いて、問題点を調べます
EXPLAINとは
EXPLAINは、MySQLがクエリをどのように実行するかを事前に分析するためのコマンドです。
実際にクエリを実行する前に、実行計画（クエリプラン）を確認できます
-- 使い方
EXPLAIN SELECT * FROM products WHERE is_active = TRUE;
EXPLAINの主な目的：
- クエリの実行方法を可視化
- パフォーマンスの問題点を特定
- インデックスの使用状況を確認
- 最適化の方向性を決定
重要な出力項目：
| 項目 | 説明 | 重要な値 |
|---|---|---|
| type | アクセスタイプ |
ALL（フルテーブルスキャン）、ref（インデックス使用） |
| key | 使用するインデックス |
NULL（未使用）、index_name（使用するインデックス） |
| rows | スキャンする行数 | 少ないほど良い |
| Extra | 追加情報 |
Using filesort（ソート処理）、Using index（インデックスのみ使用） |
EXPLAIN分析によるクエリの確認
各クエリに対してEXPLAINを実行して、どのような処理が行われているかを分析します
-- 商品一覧表示のEXPLAIN分析
EXPLAIN SELECT * FROM products
WHERE is_active = TRUE
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
出力結果：
+----+-------------+----------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+--------+----------------+
| 1 | SIMPLE | products | ALL | NULL | NULL | NULL | NULL | 100000 | Using filesort |
+----+-------------+----------+------+---------+------+---------+------+--------+----------------+
EXPLAIN結果の読み方：
-
type: ALL→ フルテーブルスキャンが発生（最も重い処理） -
key: NULL→ インデックスが使用されていない -
rows: 100000→ 全10万行をスキャンする必要がある -
Extra: Using filesort→ ソート処理が重い（メモリ上でソート）
問題点の特定：
- インデックスが使われていないため、全行をスキャンしている -> データ量が増えるほど性能が悪化してしまう
-
is_activeとcreated_atの両方にインデックスがない
インデックスを追加してみる
商品一覧表示クエリの最適化のために、以下のインデックスを追加します：
※インデックスを追加すると、そのインデックスを使うクエリは早くなりますが、逆にそのインデックスを使わないクエリは、不要なインデックスを読み込む分遅くなるので、注意が必要
1. 単一インデックス
-- WHERE句用インデックス
CREATE INDEX idx_products_is_active ON products(is_active);
-- ソート用インデックス
CREATE INDEX idx_products_created_at ON products(created_at);
2. 複合インデックス
-- 商品一覧表示用（フィルタ + ソート）
CREATE INDEX idx_products_active_created ON products(is_active, created_at);
複合インデックスの設計ポイント：
-
カラム順序:
is_active（WHERE句）→created_at（ORDER BY句） - 効果: WHERE句とORDER BY句を同時に最適化
- 期待される改善: フルテーブルスキャンからインデックススキャンに変更
インデックスをつけると...
| 項目 | 改善前 | 改善後 |
|---|---|---|
| 平均実行時間 | 0.0356秒 | 0.0011秒 |
まとめ
こういった改善系のアプローチは、メリデメあるので別案と検討できると良きかな🍵
引き出し増やしていこう〜〜
Discussion