MySQLの速度改善に役立つチェックリスト6項目

MySQLの速度を改善するために確認すべき項目として、以下の6つが挙げられます。

グローバルバッファのサイズは適切か
スレッドバッファのサイズは適切か
クエリは適切か
インデックスを適切に設定できているか
適切なストレージエンジンを選択しているか
チューニングツールを活用しているか

下記ではそれぞれの項目について解説します。

グローバルバッファのサイズは適切か

グローバルバッファとは、MySQLサーバー全体に適用されるバッファのことです。一般的に、データアクセスの際に都度データベースにアクセスするよりもバッファから読み込む方が早いため、バッファを用意することがMySQLの速度改善に繋がります。しかし、この値を大きく設定しすぎると、サーバー本体の空きメモリが不足する可能性があるため一概に大きい値に設定すれば良いというわけではありません。

代表的なグローバルバッファの項目は以下の通りです。

変数名	説明
innodb_buffer_pool_size	InnoDBで使用するバッファサイズです。専用サーバーの場合、搭載メインメモリの50%～75%を割り当てます。
innodb_log_buffer_size	InnoDBで使用するログバッファサイズです。innodb_buffer_pool_sizeの25%程度を割り当てます。
key_buffer_size	MyISAM で使用するバッファサイズです。専用サーバーの場合、搭載メインメモリの25%～30%程度を割り当てます。MyISAM利用してなければ0でも問題ありません。
net_buffer_length	送受信するパケットを格納するパケットメッセージバッファサイズの初期値です。この値の最大値は1MBです。

innodb_buffer_pool_sizeの確認方法と、環境に応じた適切な値の設定方法については下記ページをご参照ください。

【関連】MySQLメモリのチューニング手順と、手軽に実施する方法を解説

スレッドバッファのサイズは適切か

スレッドバッファは、特定の処理（各スレッド）ごとに使用されるバッファです。スレッドバッファに多くのメモリを割り当てると、スレッド数が増加した場合にスレッド数分だけメモリを大量に消費するため、割り当てメモリの量には注意が必要です。

スレッドバッファサイズに関連するパラメータは下記のとおりです。

変数名	説明
sort_buffer_size	ソート処理に利用されます。「show global status like 'Sort%';」を実行し、「Sort_merge_passes」の項目に0以外の数字が表示されている場合はsort_buffer_sizeが不足しているため、割り当てメモリ量を増加させることが望ましいです。
myisam_sort_buffer_size	MyISAM利用時にREPAIR TABLE・CREATE INDEX・ALTER INDEXのソートに利用されます。上記以外には使用されないため、多く確保する必要はありません。
read_buffer_size	テーブルフルスキャン時に利用されます。テーブルフルスキャンを行う頻度が多い設計となっている場合以外は多く確保する必要はありません。
read_rnd_buffer_size	インデックスを利用したソート処理に利用されます。ORDER BYのパフォーマンス向上が期待できますが、デフォルトで問題無い場合が多いです。
join_buffer_size	インデックスを使わない結合に利用されます。なお、高速な結合を得るにはインデックスを追加する方が効果的であるため、この値を大きくしすぎることは推奨されません。

クエリは適切か

ここからは、MySQLの環境やデータベースを利用するアプリケーションにより差がでやすいSQLクエリについてのパフォーマンス向上について触れます。 MySQLを利用する環境によって、最適化する必要のあるクエリはそれぞれ異なります。まずは、MySQL上で実行に時間が掛かっているクエリ（スロークエリ）を抽出します。スロークエリログの確認方法は以下の通りです。

1. MySQLの設定ファイルである「my.cnf」（Linux）または「my.ini」（Windows）の中にスロークエリ出力のための設定を追記または変更します。
※my.cnfはデフォルトで「/etc」配下、my.iniはデフォルトで「C:\ProgramData\MySQL\MySQL Server x.x\」（x.xはバージョン）に配置されています。

スロークエリ出力の設定は以下の通りです。

[mysqld]

slow_query_log=1

slow_query_log_file=’任意のファイル’

long_query_time=1

クエリの実行時間が「long_query_time」に設定した数字（秒）を上回った場合に、「slow_query_log_file」で指定したファイルにクエリが記録されます。

2. 「slow_query_log_file」で指定したファイルに出力されたクエリを確認します。

3. MySQLにログインし、以下のクエリを実行します。

explain ;

explainをつけてクエリを実行すると、そのクエリの実行計画を確認できます。実行計画とは、MySQLがどのようにクエリを処理するかが記載されている計画書です。

このクエリの実行結果の中から、特に「type」「key」カラムを確認します。

カラム名	説明
type	テーブルの結合方法が表示されます。「ALL」となっていた場合、フルスキャンが行われていますので、特にフルスキャンに意図がなければ見直すべき箇所となります。
key	選択されたインデックスが表示されます。「NULL」となっていた場合はインデックスが使用されていないことを意味します。頻繁にデータを追加するテーブルでない場合は、インデックスの作成を検討しましょう。インデックスについての詳細は次項をご覧ください。

スロークエリ以外のクエリのチューニングとしては、以下の点に注意してクエリを設計することが重要です。

SQL文をシンプルに記述する
出来るだけ小さいサイズの型を使用する
ワイルドカード＊の使用は避け、カラムを指定する

インデックスを適切に設定できているか

データベースのインデックスとは、目的のデータに素早くアクセスするための索引のことです。インデックスは、データベーステーブル内のカラムを識別する値（キー値）と、目的のデータが格納されている位置を示すポインタで構成されます。

前項で紹介した実行計画でインデックスが設定されていないクエリが発見され、かつ以下のケースに当てはまる場合は、インデックス作成によるパフォーマンスの向上の可能性がありますので作成を検討しましょう。

SQLの検索条件で頻出する列や結合条件に指定されている列
テーブル中の全データに対し取得するデータが少ない列
（Bツリーインデックスの場合）格納されている値の種類が多い列
（ビットマップインデックスの場合）格納されている値の種類が少ない列

ただし、頻繁にデータを追加するテーブルの場合はデータ追加の都度インデックスの更新が必要となるため、インデックスの設定によりパフォーマンスが悪化する可能性があります。

適切なストレージエンジンを選択しているか

MySQLは、ストレージエンジンをテーブル毎で異なるものを設定できるという特徴があります。

【関連】MySQLメモリのチューニング手順と、手軽に実施する方法を解説

代表的なストレージエンジンである「InnoDB」「MyISAM」のほか、機能の異なるストレージエンジンがあります。各ストレージエンジンの特徴は以下の通りです。

ストレージエンジン名	特徴
InnoDB	行ロック／トランザクションが利用可能
MyISAM	テーブルロック／アクセスが比較的早い
MEMORY	テーブルをメモリ上に保存
CSV	データファイルにCSVを使用
ARCHIVE	圧縮したデータベースを使用／データが小さい
MERGE	複数のMyISAMテーブルを結合し1つのテーブルとして使用可能
FEDERATED	レプリケーション・クラスタ無しでリモートのデータベースのデータを参照可能
BLACKHOLE	データを格納しない／空のデータを返す
EXAMPLE	ストレージエンジン開発用サンプル

アプリケーションの規模や構成により、最適なストレージエンジンを選択しましょう。

チューニングツールを活用しているか

MySQLは世界中で幅広く使用されるデータベースのうちの1つであるため、MySQLのための便利なツールも数多く存在しています。

MySQLの速度を改善するには、アプリケーション稼働後も定期的にチューニングツールを活用してデータベースとデータのパフォーマンスを確認し、問題が発生していないかを確認しましょう。

MySQL公式から提供されているチューニングツールは以下の通りです。

ツール名	特徴
MySQL Workbench	公式のオープンソースソフトウェア／データ設計・SQL最適化・設定確認・ユーザー管理・バックアップなどが可能な包括的な管理ツール
MySQL Query Analyzer	有償版のMySQLユーザーが利用可能な公式ツール／クエリの可視化に優れる

ただし、MySQLのパフォーマンスはデータベースだけで完結するものではなく、アプリケーション側の利用状況や仕様にも大きく影響を受けます。
より良いチューニングのためには、MySQLのパフォーマンスだけを見てチューニングするのではなく、アプリケーションの仕様やWebサーバー・アプリケーションサーバーなどの利用状況なども併せて確認した上でチューニングを行うことが望ましいです。

MySQLのパフォーマンスをより高い視点から最適化して速度を改善するための方法を次項で紹介します。