はじめに
「アプリを作ったはいいけど、データが増えてきたらページの表示がどんどん遅くなってきた…」
Web開発をしていると、多くの人が一度は経験する悩みだと思います。この記事では、MySQLのパフォーマンスチューニングについて、初学者の方でも今日から実践できる基本のステップを、優先度の高い順にまとめました。
チューニングの全体像
パフォーマンスチューニングは、だいたい以下の順番で進めると効率的です。
- 遅いクエリを見つける(スロークエリログ)
- なぜ遅いのかを調べる(EXPLAIN)
- インデックスを見直す
- クエリの書き方を見直す
- サーバー設定(my.cnf)を見直す
- キャッシュ・アーキテクチャで対応する
上から順に「効果が出やすく」「難易度が低い」ものになっています。いきなり6番から手を付けず、まずは1〜3を確実に押さえましょう。
STEP 1: 遅いクエリを見つける(スロークエリログ)
チューニングの第一歩は「そもそもどのクエリが遅いのか」を知ることです。勘や経験だけで直そうとすると、時間を無駄にしがちです。
MySQLには、実行に時間がかかったクエリを自動で記録してくれるスロークエリログという機能があります。
設定方法
-- スロークエリログを有効化
SET GLOBAL slow_query_log = 'ON';
-- 何秒以上かかったクエリを「遅い」とみなすか(例: 1秒)
SET GLOBAL long_query_time = 1;
-- ログの保存場所を確認
SHOW VARIABLES LIKE 'slow_query_log_file';
my.cnf(設定ファイル)に恒久的に設定する場合は以下のように記述します。
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
ログの見方
たくさんログが溜まると人力で読むのは大変なので、mysqldumpslowやpt-query-digestといったツールで集計するのがおすすめです。
# 実行回数が多い順にTOP10を表示
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
ここで「実行回数が多く、かつ時間がかかっているクエリ」を見つけたら、それがチューニングの最優先ターゲットです。
STEP 2: なぜ遅いのかを調べる(EXPLAIN)
遅いクエリが見つかったら、EXPLAINを使ってMySQLが「どういう手順でそのクエリを実行しているか」を確認します。
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;
実行結果の見方(特に注目すべき列)はこちらです。
| 列名 | 意味 | 初学者が見るべきポイント |
|---|---|---|
type |
どうやってデータを探すか |
ALL(全件走査)は要注意 |
key |
実際に使われたインデックス |
NULLならインデックス未使用 |
rows |
見積もられる走査行数 | 数値が大きいほど遅い可能性 |
Extra |
補足情報 |
Using filesortやUsing temporaryは要注意 |
特に注意すべきキーワード
-
type = ALL: テーブルを全件スキャンしている状態です。インデックスがうまく使われていません。 -
Using filesort: メモリ上または一時ファイルでソートが発生しています。ORDER BYの対象カラムにインデックスがない可能性があります。 -
Using temporary: 一時テーブルが作られています。GROUP BYやDISTINCTが重い処理になっているサインです。
まずはこのEXPLAIN結果を見て「全件走査になっていないか」をチェックする癖をつけましょう。
STEP 3: インデックスを見直す
EXPLAINでtype = ALLだった場合、多くはインデックス不足が原因です。
インデックスとは
本の「索引」のようなものです。索引がないと本を最初から最後まで読んで探す(全件走査)しかありませんが、索引があれば該当ページに一発で飛べます。
基本的な貼り方
-- WHERE句でよく使われるカラムにインデックスを貼る
CREATE INDEX idx_customer_id ON orders (customer_id);
複合インデックスの考え方
複数カラムで検索する場合は、複合インデックスを検討します。この時、カラムの順番が非常に重要です。
-- customer_id → order_date の順でよく検索するなら
CREATE INDEX idx_customer_date ON orders (customer_id, order_date);
複合インデックスは「左側から順番に」使われる性質があるため、WHERE order_date = ...単独の検索にはこのインデックスは効きません。検索条件の組み合わせパターンに合わせて順番を決めるのがコツです。
注意点:インデックスは「多ければ良い」わけではない
- インデックスが増えると、検索(SELECT)は速くなりますが、更新(INSERT/UPDATE/DELETE)は遅くなります(インデックス自体もメンテナンスが必要なため)。
- カーディナリティ(値の種類の多さ)が低いカラム(例: 性別など2〜3種類しかない値)にインデックスを貼っても、あまり効果がないことが多いです。
「よく検索に使われ、値のバリエーションが多いカラム」を優先してインデックスを貼りましょう。
STEP 4: クエリの書き方を見直す
インデックスを貼っても、書き方によってはインデックスが使われないことがあります。よくあるアンチパターンを紹介します。
❌ SELECT * を使う
-- 悪い例
SELECT * FROM users WHERE id = 1;
-- 良い例(必要なカラムだけ取得)
SELECT id, name, email FROM users WHERE id = 1;
不要なカラムまで転送するのは無駄なI/Oです。特にTEXT型やBLOB型のカラムがある場合は影響が大きくなります。
❌ カラムに対して関数や演算をかけてしまう
-- 悪い例(インデックスが効かない)
SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- 良い例(範囲検索にすることでインデックスが効く)
SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
カラム側に関数をかけると、インデックスが使えなくなるケースが多いです。
❌ N+1問題
アプリケーション側でループしながら都度クエリを発行してしまうパターンです。
# 悪い例(注文数分だけクエリが発行される)
for order in orders:
customer = db.query("SELECT * FROM customers WHERE id = %s", order.customer_id)
-- 良い例(JOINでまとめて1回のクエリにする)
SELECT orders.*, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id;
ORMを使っていると気づかないうちにN+1が発生していることが多いので、発行されるSQLをログで確認する習慣をつけましょう。
❌ LIMITなしの大量データ取得
画面に表示する分だけ、あるいは処理する分だけをLIMITで絞り込みましょう。ページネーションを実装する際はOFFSETが大きくなるほど遅くなる点にも注意が必要です(大規模データの場合はカーソルベースのページネーションを検討します)。
STEP 5: サーバー設定(my.cnf)を見直す
ここまでのステップで改善が見られない、あるいはサーバー全体のリソースが逼迫している場合は、設定値を見直します。ただし、アプリ側の改善(STEP1〜4)の方が費用対効果が高いことがほとんどなので、優先順位としては後回しで問題ありません。
バッファプールサイズ(最重要)
innodb_buffer_pool_sizeは、データやインデックスをメモリ上にキャッシュしておく領域のサイズです。ここが小さいと、毎回ディスクへの読み込みが発生してしまいます。
[mysqld]
# 専用サーバーであれば、物理メモリの50〜70%程度が目安
innodb_buffer_pool_size = 4G
接続数
[mysqld]
max_connections = 200
アプリ側のコネクションプール設定と合わせて調整します。多すぎるとメモリを圧迫し、少なすぎると接続エラーが発生します。
設定変更後の確認
-- 現在のバッファプールのヒット率を確認(理想は99%以上)
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
STEP 6: キャッシュ・アーキテクチャで対応する
STEP1〜5でDB自体は十分速くなったとしても、「そもそもDBへのアクセス回数自体を減らす」というアプローチも有効です。
- Redis/Memcachedなどのキャッシュ層を挟む: 頻繁に読まれるが更新頻度が低いデータ(マスタデータなど)をキャッシュする
- リードレプリカを用意する: 参照(SELECT)処理を複数のレプリカに分散させる
- テーブル分割・シャーディング: データ量が非常に大きくなった場合の最終手段
これらはインフラ構成の変更を伴うため、まずはSTEP1〜5の基本を押さえた上で、必要に応じて検討しましょう。
まとめ:困ったらこの順番で
- スロークエリログで遅いクエリを特定する
-
EXPLAINで原因を分析する(
type = ALLになっていないか) - インデックスを適切に貼る(貼りすぎにも注意)
- クエリの書き方を見直す(SELECT *、N+1、関数の使用に注意)
- それでも足りなければmy.cnfを調整する
- 最終手段としてキャッシュやレプリカなどアーキテクチャで対応する
参考
JISOUのメンバー募集中！
プログラミングコーチングJISOUでは、新たなメンバーを募集しています。日本一のアウトプットコミュニティでキャリアアップしませんか？
興味のある方は、ぜひホームページをのぞいてみてください！