開発・動作確認環境：GitHub Codespaces（以前本ブログで構築した環境を使用）
はじめに
前回は、「MySQLで学ぶ受注管理システム開発（4）「トリガーとユーザー権限管理」」にて、トリガーによる自動ログ記録と、ユーザーごとの権限管理を行った。
これで、テーブル設計・CRUD・ビュー／プロシージャ・トリガー／権限管理と、受注管理システムの主要な機能はひと通りそろった。
最終回となる今回は、視点を「機能」から「運用」に移し、クエリを速くするためのインデックス設計と、万が一に備えたバックアップ・リストアという、実務で欠かせない2つのテーマを扱う。
なぜパフォーマンスとバックアップが重要か
- パフォーマンス
今回のorder_dbはデータ件数がごく少ないため体感しにくいが、実務のデータベースは数十万〜数億件のデータを扱う。適切なインデックスがなければ、簡単な検索でも数秒〜数十秒かかることがある - バックアップ
どれだけ設計や実装に気を配っても、操作ミスやハードウェア障害でデータが失われるリスクはゼロにならない。「バックアップを取っていなかった」という事態だけは避けなければならない
EXPLAINでクエリの実行計画を確認する
MySQLには、SELECT文がどのように実行されるか（テーブル全体を舐めているのか、インデックスを使っているのか）を確認できるEXPLAINという機能がある。
sql
USE order_db;
EXPLAIN SELECT * FROM products WHERE product_name = 'ワイヤレスマウス';実行例
mysql> EXPLAIN SELECT * FROM products WHERE product_name = 'ワイヤレスマウス';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | products | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)見るべきポイント
type: ALL：テーブル全体を1行ずつ確認する「フルスキャン」が行われていることを示す。データ件数が増えるほど遅くなるkey: NULL：インデックスが使われていないrows: 4：今回はデータが4件しかないため一瞬だが、これが数百万件になると致命的に遅くなる
インデックスを設計する
検索によく使う列にインデックスを張る
商品名で検索することが多いなら、product_nameにインデックスを張っておこう。
sql
CREATE INDEX idx_products_product_name ON products(product_name);再度EXPLAINで確認する。
sql
EXPLAIN SELECT * FROM products WHERE product_name = 'ワイヤレスマウス';実行例
mysql> EXPLAIN SELECT * FROM products WHERE product_name = 'ワイヤレスマウス';
+----+-------------+----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | products | NULL | ref | idx_products_product_name | idx_products_product_name | 403 | const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)typeがALLからrefに、rowsが4から1に変わった。インデックスによって、該当行だけをピンポイントで探せるようになったということである。
複合インデックス（2列以上の組み合わせ）
第3回で作成したcustomer_order_summaryビューのように、「特定の顧客の、キャンセルされていない注文」という条件（customer_idとstatusの組み合わせ）は頻繁に使う。この場合、2つの列をまとめた複合インデックスが効果的である。
sql
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);sql
EXPLAIN SELECT * FROM orders WHERE customer_id = 'C001' AND status <> 'キャンセル';ポイント
customer_idで絞り込み、さらにstatusでも条件を指定する検索を想定して、複合インデックスを作成する。ただし、status <> 'キャンセル'のような否定条件では、status列まで効率よく絞り込めるとは限らないため、実際の効果はEXPLAINで確認する。複合インデックスは列の順番も重要で、(customer_id, status)の順であれば「customer_idだけで絞り込む」検索にも使えるが、(status, customer_id)の順にすると効果が薄れる場合がある。
インデックスを張りすぎない
インデックスは検索を速くする一方、INSERT・UPDATE・DELETEのたびにインデックス自体も更新されるため、書き込み性能とのトレードオフになる。「よく検索・絞り込みに使う列」に絞って設定するのが基本方針である。
現在のインデックス一覧は、以下で確認できる。
sql
SHOW INDEX FROM products;
SHOW INDEX FROM orders;バックアップを取る
続いて、mysqldumpコマンドを使ったバックアップを行う。これはMySQLに接続して実行するSQLではなく、ターミナルから直接実行するコマンドである点に注意してほしい。
※実運用では、コマンドラインにパスワードを直接記述することは避け、-pのみを指定して入力を促す方法や、オプションファイル・ログインパスを利用しよう。本記事の-ppasswordは学習環境での例である。
データベース全体をバックアップする
mkdir -p backups
mysqldump -h db -u root -ppassword \
--routines \
--events \
order_db > backups/order_db_backup.sql実行後、backups/order_db_backup.sqlというファイルに、テーブル定義・データ・ビューの内容がSQL文として書き出される。トリガーはデフォルトで含まれるが、ストアドプロシージャ・関数は--routines、イベントは--eventsを指定しないと出力に含まれない点に注意してほしい。
ls -lh backups/
head -n 20 backups/order_db_backup.sql特定のテーブルだけをバックアップする
mysqldump -h db -u root -ppassword order_db orders order_details > backups/orders_only_backup.sql構造（テーブル定義）だけをバックアップする
データを含めず、テーブル定義だけを残したい場合は--no-dataオプションを使う。
mysqldump -h db -u root -ppassword --no-data order_db > backups/order_db_structure_only.sqlバックアップファイルはGit管理から除外する
backups/フォルダの中身は、実際のデータを含むSQLファイルである。誤ってこのままコミットしてしまうと、リポジトリに顧客データや売上データが残り続けることになるため、.gitignoreに追加して、Gitの追跡対象から外しておこう。
echo "backups/" >> .gitignore
git add .gitignore
git commit -m "backups/をGit管理対象から除外"これで、mysqldumpを実行するたびにバックアップファイルが増えても、意識せず安全に運用できる。
リストアする（復元する）
バックアップが正しく機能するかどうかは、実際に復元してみるまで分からない。別のデータベース名で復元し、動作確認をしておこう。
mysql -h db -u root -ppassword -e "CREATE DATABASE order_db_restore_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -h db -u root -ppassword order_db_restore_test < backups/order_db_backup.sql復元先のデータベースで、正しくデータが入っているか確認する。
mysql -h db -u root -ppassword order_db_restore_test -e "SELECT * FROM customer_order_summary;"実行例
+-------------+---------------+-------------+--------------+
| customer_id | customer_name | order_count | total_amount |
+-------------+---------------+-------------+--------------+
| C001 | 山田太郎 | 0 | 0.00 |
| C002 | 佐藤花子 | 1 | 6000.00 |
| C003 | 鈴木一郎 | 1 | 11400.00 |
| C004 | 高橋健太 | 1 | 7600.00 |
+-------------+---------------+-------------+--------------+
4 rows in set (0.00 sec)ビュー（customer_order_summary）も含めて正しく復元されていることが確認できた。確認用のデータベースは、不要になったら削除しておこう。
mysql -h db -u root -ppassword -e "DROP DATABASE order_db_restore_test;"実務での考え方：バックアップファイルは、Codespacesのコンテナ内だけに置いておくと、Codespacesを削除したときに一緒に失われてしまう。実運用では、バックアップ先をクラウドストレージや別サーバーなど、DB本体とは物理的に離れた場所にすることが基本である。
動作確認
sql
SHOW INDEX FROM products;
SHOW INDEX FROM orders;bash
ls -lh backups/GitHubへの反映
mysql-learning/
├── order-management/
│ ├── 01_schema.sql
│ ├── 02_crud.sql
│ ├── 03_view_procedure.sql
│ ├── 04_trigger_permission.sql
│ └── 05_index_backup.sql ← 今回作成したインデックス定義注意mysqldumpはターミナルコマンドであり、SQLファイルとしてリポジトリに含める性質のものではないため、バックアップ手順自体はコマンドとして記事とREADMEに残す形にする。バックアップファイル自体（.sqlの中身）はデータを含むため、Gitリポジトリにはコミットしない。
git add order-management/
git commit -m "インデックス設計を追加（第5回・最終回）"
git pushまとめ
今回は、以下を学んだ。
EXPLAINによる実行計画の確認方法と、type・key・rowsの読み方- 単一列インデックスと複合インデックスの設計、および列の順番の考え方
- インデックスを張りすぎないことの重要性（読み取りと書き込みのトレードオフ）
mysqldumpによるバックアップと、実際に復元して確認するまでを一連の流れとして行うことの大切さ
この連載を振り返って
全5回を通じて、以下のようにMySQLの基礎から実務的な運用までを扱った。
- テーブル設計とER図を描こう：正規化とテーブル設計
- CRUD操作を極めよう：INSERT/UPDATE/DELETEとトランザクション
- ビューとストアドプロシージャを作ろう：処理の再利用と自動ロールバック
- トリガーとユーザー権限管理：自動処理と権限制御
- パフォーマンスチューニングとバックアップ（今回）：運用の視点
company_db（社員表・部署表）で基本操作を学んだ後、order_db（受注管理システム）でより実務に近い設計・実装・運用まで体験できたのではないかと思う。ここで学んだ内容を土台に、ぜひご自分自身のアイデアで別のシステムのテーブル設計にも挑戦してみてほしい。
コードのダウンロード
今回のコードは、私のGitHubリポジトリからダウンロードできる。
▶ Rocky-Seven/mysql-learning/order-management
関連記事
MySQLで学ぶ受注管理システム開発（1）「テーブル設計とER図を描こう」
MySQLで学ぶ受注管理システム開発（2）「CRUD操作を極めよう」
MySQLで学ぶ受注管理システム開発（3）「ビューとストアドプロシージャを作ろう」
MySQLで学ぶ受注管理システム開発（4）「トリガーとユーザー権限管理」
コメント