はじめに

前の記事では実行計画とは何なのかまとめていたので、今回は実際にクエリの最適化をしてみようと思います。
結局、SQLの実行計画って何が嬉しいのって話その1 ~基本とSQLパフォーマンスの初歩~

実行計画を使ったパフォーマンス改善の流れ

1. EXPLAINを使って実行計画を確認

あらためて、SQLクエリに対してEXPLAINを使用して実行計画を確認します。

// SQL
EXPLAIN SELECT * FROM users WHERE age > 10;

// 実行結果
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

2. 実行計画の問題点を特定

次にEXPLAINの結果を分析して、問題となっている部分を特定します。

フルスキャン（Table Scan）の確認

EXPLAINの出力でTypeがALLとなっている場合、テーブルの全行をスキャンする「フルスキャン」が行われていることを意味している。
フルスキャンは、インデックスを利用せずに全データを検索しているため、非常にコストが高くなるらしい。

改善策
フルスキャンを避けるために、適切なインデックスを作成する。
もしすでにインデックスが作成されている場合、クエリの条件がインデックスを有効に使えるよう、クエリを修正する。

インデックスの利用状況の確認

EXPLAINの出力で、Key列にインデックスが表示されていない場合、インデックスが使われていない。

改善策
インデックスの追加：頻繁に検索や絞り込み条件に使われる列にインデックスを追加する。
インデックスの再検討：既存のインデックスが最適かどうかを再評価する。
複合インデックスや不要なインデックスがある場合は、削除するようにする。

不要なソートや結合の確認

実行計画において、Extra列にUsing filesortが表示されるとソート処理を行っているらしい。
ソート処理は、メモリに負荷をかけ、処理時間を長引かせる原因になっている。

改善策:
ORDER BY句でソートの順番やインデックスの利用方法を再評価し、結合順序を見直し、最も小さいテーブルを先に結合するように変更する。

SQLチューニングの実践

1. インデックスを活用したクエリの改善

インデックスを適切に設定することで、データ検索のパフォーマンスを最適化できる。
下記の改善前のクエリはcustomer_id列にインデックスがない場合、テーブル全体をフルスキャンする可能性がある。そのため、インデックスを作成することで、検索がインデックスを使って高速化されるようになる。

// 改善前
SELECT * FROM users WHERE customer_id = 123;

// 改善後
CREATE INDEX idx_customer_id ON users (customer_id);

2. 不要なソートや結合を避ける

不要なソートや結合はパフォーマンスを低下させるため、最適化する必要があるらしい。
下記の改善前のクエリは必要な情報だけを選択し、ソートを省略することでパフォーマンスを改善できている。

// 改善前
SELECT * FROM products p JOIN categories c ON p.category_id = c.id ORDER BY p.price;

// 改善後
SELECT p.id, p.name, p.price FROM products p JOIN categories c ON p.category_id = c.id;

3. ORDER BYや集計関数の最適化

ORDER BYや集計関数（COUNT, SUM, AVGなど）は多くのリソースを消費することがあり、これを最適化するためにインデックスを活用したり、集計を分割したりする。
下記はインデックスを使用して、グループ化と集計のパフォーマンスを向上させている。

// 改善前
SELECT category_id, COUNT(*) FROM orders GROUP BY category_id ORDER BY COUNT(*) DESC;

// 改善後
CREATE INDEX idx_category_id ON orders (category_id);

さらなる最適化

1. クエリの並列実行とその効果

大規模なデータベースでクエリ処理を高速化するために、クエリを並列で実行する方法が存在している。
MySQLでは、特定の設定を行うことで並列クエリ処理を実現できるよう。
下記ではinnodb_parallel_read_threadsやinnodb_parallel_write_threadsなどの設定を調整することで、並列実行を可能にしている。

// 並列クエリの設定
SET GLOBAL innodb_parallel_read_threads = 4;
SET GLOBAL innodb_parallel_write_threads = 4;

2. クエリキャッシュの有効活用

クエリキャッシュを活用することで、繰り返し実行されるクエリの結果をキャッシュに保存し、再実行時のパフォーマンスを向上させることができる。

// キャッシュの設定
SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_size = 1048576;  -- 1MB

3. シャーディングやレプリケーションによるパフォーマンス向上

シャーディングやレプリケーションにより、データを複数のサーバーに分散させることができ、負荷分散と可用性の向上を図ることができる。

シャーディング

シャーディングは、データを複数のデータベースに分割して格納する方法です。例えば、顧客データをcustomer_idに基づいて複数のデータベースに分けることで、1つのデータベースへの負荷を軽減できます。

レプリケーション

レプリケーションを活用することで、データの冗長性を確保し、読み込みの負荷を分散させることができます。例えば、1つのマスターサーバーと複数のスレーブサーバーを使用することで、読み込みの負荷をスレーブサーバーに分散できます。

4. 設定変更でのパフォーマンス改善

設定ファイル（MySQLの場合、my.cnf）を調整することで、システム全体のパフォーマンスを向上させることができる。

// 例: InnoDB設定の最適化
[mysqld]
innodb_buffer_pool_size = 4G   # メモリ設定
innodb_log_file_size = 512M    # ログファイルサイズ
innodb_flush_log_at_trx_commit = 2   # トランザクションログのフラッシュ設定

INDEXを追加する時のポイント

カーディナリティの高いカラムを選択する

複合インデックスのカラム順に注意。指定順が重要。（country_id, city_id）
更新性能・キャッシュ効率が低下するため必要なものにだけ貼ること

カーディナリティとは？

インデックス内のユニークな値の多さを表した指標。
・高い例：AUTO_INCREMENT, 住所, 更新日時
・低い例：フラグ, 性別, カテゴリー, 都道府県
カーディナリティが低いと効果が少ない。絞り込めない可能性が高い。
また、インデックスを経由することによるオーバーヘッドが無視できない
分布が偏っていれば効果が大きくなる可能性がある、条件によって効果が異なる

まとめ

SQLのパフォーマンスを最適化するためには、実行計画を活用することがとても大切。
EXPLAINを使用してクエリの実行計画を確認し、フルスキャンやインデックスの利用状況、結合の順序などをチェックし、その上で、インデックスの追加やクエリの書き換えを行うことで、パフォーマンスを改善できる。

実行計画を確認するだけでも大きな効果が期待できるため、SQLのパフォーマンスが遅いと感じた際には、まず実行計画をチェックし、適切なチューニングを行うことで、アプリケーションの性能を大きく向上させることができそう。