はじめに
この記事はプログラミング初学者による備忘録用の記事であり、また、少しでも他の初学者のお役に立てればと思い書いています。
今回は、MySQL8.0のパフォーマンスチューニングについて調べたので、まとめておきたいと思います。MyISAMについては触れておりませんのでご了承下さい。(新しい情報を見つけ次第、随時更新します)
間違いなどがございましたら、ご指摘のほどよろしくお願い致します。
データベースのパフォーマンスチューニングとは
データベースのパフォーマンスは、テーブル、クエリ、構成設定など、データベースレベルのいくつかの要因に依存します。チューニングをすることで、ユーザビリティの向上を図ります。
DBのパフォーマンスチューニングでは、下記のような性能の向上を図ります。
スループット
・スループットとは
単位時間あたりの処理能力のことを指し、スループットは必ず「○○あたり」という限定付きの数値となっています。
例えば、秒間のSQL処理件数（クエリ／秒，QPS）やトランザクション数（トランザクション／秒，TPS）などが指標(単位)とされます。
単位時間は、可能であれば「秒」、少なくとも「分」単位で定義する必要があります。
・スループットの重要性
スループットが重要である理由の１つとして、ハードウェアのリソース使用量がスループットに比例することが挙げられます。
従って、スループットが高ければ高いほど多くの処理が単位時間内に実行され、CPU、メモリ、ストレージなどのリソースを多く消費することになります。
・単位時間が小さいほどスループットの指標としての精度は高くなる
スループットは、割り算で求めるので単位時間内では負荷が均等に分散していることを前提としています。
従って、「1時間あたり」や「1日あたり」という単位時間だと、集中度が現実と乖離して不当に低い値になるというリスクが生じます。
並列処理が出来る件数が増えるほどこのスループットの値が向上します。
レスポンスタイム
処理を実行後、結果の応答までの時間
1つの処理にどの程度の時間がかかるかを測ります。
スケーラビリティ
ユーザ数や作業量の増加への適応性
メモリやCPUの追加、ディスクのSSD化などに柔軟に対応できる必要があります。
上記の項目は、いずれも多重度の変化に伴って複雑に変化するので、特にユーザやデータベースのアクセスの増加が見込まれるシステムでは，テストを通じて多重度別の数値の推移を測定し，システムの要件に見合った状況になっているかを検証する必要性が出てきます。
補足
・スループットとレスポンスタイム、カーディナリティ(多重度)の関係式
カーディナリティ(多重度) / (レスポンスタイム + シンクタイム) = スループット
~解説~
・カーディナリティ(多重度):テーブルのカラムに格納されているデータの種類がどのくらいあるのかを表す
・レスポンスタイム: 処理を実行後、結果の応答までの時間(ユーザが体感する待ち時間)
・シンクタイム: ユーザが次のアクションに移るまでの思考時間
MySQL最適化のアプローチ
DBのパフォーマンス最適化には、大きく分けて2種類のアプローチがあります。
・データベースレベルでの最適化
下記項目の最適化を図ります。
・データサイズの最適化
・MySQLデータ型の最適化
・多数のテーブルの最適化
・データベースおよびテーブルの数に対する制限
・テーブルサイズの制限
・テーブルカラム数と行サイズの制限
引用データベース構造の最適化
・SQLの最適化
下記項目の最適化を図ります。
・データ型の最適化
・SQL文の最適化
・インデックスの追加と修正による最適化
・正規化と非正規化による最適化
他にも、MySQLサーバーの最適化やロック操作の最適化など、パフォーマンス向上に繋がる最適化がありますが、現段階では上記2つの最適化アプローチについて注力したいと思います。
データベースレベルでの最適化
データサイズの最適化
データサイズの最適化とは
基本的なテーブル設計ルールとして、ディスク上の領域を最小にするようにテーブルを設計します。
この設計ルールを守ることで、ディスクに対して読み書きされるデータサイズが小さくなり、処理速度の改善が期待できます。
開発の際は、アプリケーションに適切なテーブル形式を選択して、パフォーマンスの最適化が求められます。
テーブルのパフォーマンス改善とストレージ領域の最小化を図る方法
下記に示す方法を取り入れることで、データサイズの最適化が見込めます。
1.テーブルカラム
MySQLでは、効率的なデータ型を使用することでディスク領域とメモリーの節約を実現できます。
~例~
よく使用されるint型ですが、MySQLにはint型の中でも複数のint型が存在します。
| 型 | 記憶域(バイト) | 最小値 | 最大値 |
|---|---|---|---|
| TINYINT | 1 | -128 | 127 |
| SMALLINT | 2 | -32768 | 32767 |
| MEDIUMINT | 3 | -838608 | 8388607 |
| INT | 4 | -2147483648 | 2147483647 |
| BIGINT | 8 | -2^63 | 2^63-1 |
上記のように、記憶域(バイト)の数値が異なるので、何でもINT等を選択するのではなく、効率良く動作するように考えて型を選ぶ必要があります。
また、可能な場合はカラムをNOT NULLとして宣言します。
これにより、インデックスを適切に使用し、各値がNULLであるかをテストする必要がなくなることで、SQLの操作が速くなります。
2.インデックス
インデックスは特定のカラム値のある行をすばやく見つけるために使用されます。
インデックスがないと、MySQLは関連する行を見つけるために、先頭行からテーブル全体を読み取る必要があります。
~注意~
・クエリーパフォーマンスを向上するために必要なインデックスのみを作成する
インデックスは取得には有効ですが、挿入および更新操作を遅くします。
・インデックスの最初の部分は、最も使用されるカラムにする
テーブルから選択する際に、常に多くのカラムを使用する場合、適切なインデックスの圧縮を取得するため、インデックスの最初のカラムは最も重複の多いカラムを使用します。
3.結合
・対応するカラムに基づいた結合を高速化する為に、異なるテーブル内の同一の情報を持つカラムを同一のデータ型で宣言します。
・異なるテーブルで同じカラム名を使用し、結合クエリーを簡略化できるようにカラム名を簡単にします。
4.正規化
通常、すべてのデータを非冗長に維持することを心がけます。
冗長になる場合、それらに一意のIDを割り当て、複数の小さなテーブルで必要なだけこれらのIDを繰り返し、結合句でIDを参照して、クエリーでテーブルを結合する手段を選択する場合もあります。
MySQLデータ型の最適化
1.数値データの最適化
一意のIDやその他のデータ値を文字列または数値として表すことが可能な場合、数値カラムを選択する。
数値カラムは、対応する文字列より少ないバイト数で格納できるため、使用するメモリーが少なくなります。
2.文字および文字列型の最適化
・MySQLではデータベース、テーブル、カラムのそれぞれに対して文字セットと照合順序を設定することができます。
言語固有の照合機能が必要でない場合のみ、比較およびソート操作を速くするため、バイナリ照合順序(binary)の使用を推奨されています。
私はデフォルト設定の手間が少なく、パフォーマンスも良いutf8_general_ciを使用しています。
~注意~
ALTER TABLE CONVERT TO時にCOLLATION指定が必要
MySQL8.0でutf8mb4のデフォルトの照合順序がutf8mb4_general_ciからutf8mb4_0900_as_ciに変更になりました。
変換するときにCOLLATEでutf8mb4_general_ciを明示的に指定しないと、utf8_general_ciからutf8mb4_0900_ai_ciへとテーブルのデフォルト照合順序になってしまいます。
mysql> ALTER TABLE utf8t CONVERT TO CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
--略
mysql> SHOW CREATE TABLE utf8t \G
*************************** 1. row ***************************
--略
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
・様々なカラムの値を比較する場合、それらのカラムを同じ文字セットと照合順序で宣言し、クエリー実行中の文字列変換を防ぐようにします。
・テーブルに名前や住所などの文字列カラムが含まれるが、多くのクエリーでそれらのカラムを取得しない場合、文字列カラムを個別のテーブルに分割し、必要に応じて、外部キーで結合クエリーを使用するようにします。
詳細は下記リンク先を参照
MySQLでサポートされる文字セットおよび照合順序 一覧
MySQLでの文字セットと照合順序
多数のテーブルの最適化
クエリーを高速化する技法には、複数テーブルへのデータの分割が含まれます。 テーブルの数が数千以上に及ぶ場合、これら全てのテーブルの処理のオーバーヘッドはパフォーマンス低下に繋がります。
テーブルの数が多くパフォーマンスが低下する際の対策として、下記状況下ではMySQLは自動的に未使用のテーブルをクローズするようになっています。
・キャッシュがいっぱいで、スレッドがキャッシュにないテーブルを開こうとした場合
・キャッシュに table_open_cache を超えるエントリがあり、キャッシュ内のテーブルがどのスレッドによっても使用されなくなった場合
・テーブルフラッシュ操作が発生した場合。 これは、だれかが FLUSH TABLES ステートメントを発行するか、または mysqladmin flush-tables または mysqladmin refresh コマンドを実行した場合に行われます。
引用MySQL8.0 公式ドキュメント
テーブルキャッシュがいっぱいになると、MySQLサーバーは次の手順に従って使用するキャッシュエントリを見つけます。
・現在使用されていないテーブルは、最も最近使用されていないテーブルからリリースされます。
・新しいテーブルを開く必要があるが、キャッシュがいっぱいでテーブルを解放できない場合、キャッシュは必要に応じて一時的に拡張されます。 キャッシュが一時的に拡張された状況で、テーブルが使用中から未使用状態になったときは、そのテーブルが閉じられ、キャッシュから解放されます。
引用MySQL8.0 公式ドキュメント
テーブルキャッシュが小さすぎるかどうかを判断するには、サーバーの起動後のテーブルオープン操作の数を示すOpened_tablesステータス変数を確認します。
多くのFLUSH TABLESステートメントを発行していない場合でも、値が非常に大きい又は増加する場合は、サーバーの起動時にtable_open_cache値を増やします。
mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2222 |
+---------------+-------+
データベースおよびテーブルの数に対する制限
MySQLでは、データベース数とテーブル数の制限はありません。
しかし、個々のストレージエンジンには、エンジン固有の制約が課される場合があります。
InnoDBでは、最大40億個のテーブルが使用可能です。
テーブルサイズの制限
MySQLの最大テーブルサイズは、通常、MySQLの内部制限ではなくオペレーティングシステムのファイルサイズに制約されます。(代表的なOSとしてWindows、MacOS、Linuxなど)
テーブルサイズに関するエラーが発生した場合、下記の原因が該当する可能性があります。
全テーブルエラーの原因
・ディスクの容量が最大になっている可能性
・InnoDBテーブルを使用しており、InnoDBテーブルスペースファイルの領域が不足している可能性(InnoDBの制限を参照する)
・Windows、MacOS等のオペレーティングシステムのファイルサイズ制限に達している可能性
テーブルカラム数と行サイズの制限
・カラム数制限
MySQLにはテーブル当たり4096カラムという制限がありますが、特定のテーブルの有効な最大値が減少する可能性があります。 カラムの正確な制限は、いくつかの要因によって異なります。
例えば、ストレージエンジンにInnoDBを選択した場合、テーブル当たり1017カラムに制限されます。
・すべてのカラムの合計長がこのサイズを超えることはできないため、テーブルの最大行サイズによってカラムの数 (および場合によってはサイズ) が制限されます。
・個々のカラムの記憶域要件によって、指定された最大行サイズ内に収まるカラム数が制限されます。 一部のデータ型の記憶域要件は、記憶域エンジン、記憶域形式、文字セットなどの要因によって異なります。
・ストレージエンジンは、テーブルカラム数を制限する追加の制限を課す場合があります。 たとえば、InnoDB には、テーブル当たり 1017 カラムの制限があります。(InnoDBの制限を参照する)
・関数キー部分(CREATE INDEX ステートメントを参照)は、非表示の仮想生成ストアドカラムとして実装されるため、テーブルインデックス内の各関数キー部分は、テーブルの合計カラム制限に対してカウントされます。
引用:テーブルカラム数と行サイズの制限
・行サイズ制限
特定のテーブルの最大行サイズは、いくつかの要因によって決定されます。
・ストレージエンジンがより大きな行をサポートできる場合でも、MySQLテーブルの内部表現の最大行サイズは 65,535 バイトです。 BLOB および TEXT のカラムは、行サイズ制限に 9 から 12 バイトのみ寄与します。これは、その内容が行の他の部分とは別に格納されるためです。
・データベースページ内にローカルに格納されたデータに適用される InnoDB テーブルの最大行サイズは、4KB、8KB、16KB および 32KB の innodb_page_size 設定のページの半分未満です。 たとえば、デフォルトの 16KB の InnoDB ページサイズでは、最大行サイズは 8KB 未満です。 64KB ページの場合、最大行サイズは 16KB 未満です。
variable-length columns を含む行が InnoDB の最大行サイズを超える場合、InnoDB は、行が InnoDB の行サイズ制限内に収まるまで、外部オフページストレージの可変長カラムを選択します。 ページ外に格納される可変長カラムに対してローカルに格納されるデータ量は、行形式によって異なります。・記憶域形式が異なると、ページヘッダーおよびトレーラデータが異なるため、行に使用可能な記憶域の量に影響します。
引用:テーブルカラム数と行サイズの制限
SQLの最適化
SQLに関するチューニングは、クエリを見直したりすることで処理速度の向上を図ります。
クエリの変更の敷居は低く、性能を大幅に向上させるための最も有効な手段なので、必ずクエリのチューニングは一度行うべきです。
最適化のポイントとして下記の項目が挙げられます。
・データ型の見直し
・クエリの見直し
・インデックスの追加と修正
・正規化と非正規化
・キャッシュの調整
データ型の見直し
設定したデータ型が、アプリケーションの仕様上、適切な型となっているかを再確認する。
例えば、INT型の中でも複数のINT型があり、記憶域(bite)のサイズも異なるので、適切な型を選ぶことで、パフォーマンスが最適化されます。
また、テーブルに設定したデータ型と入力したデータのデータ型が異なっていた場合、変更可能であればエラーとならずに暗黙の型変換が行われます。この型変換にかかる時間を排除するためにも、適切なデータ型を入力させるようにします。
クエリの見直し
無駄なループを修正するなどの処理を見直したり、実行が遅い命令(スロークエリ)を修正するなどで対応します。
~例~
スロークエリを解決する
スロークエリとは、実行が遅いクエリーのことであり、レスポンスタイムが1.5~2秒以上であれば遅い部類に入ると思います。
原因
・大きなテーブルで、クエリーでの完全テーブルスキャンの回数が多い等のDB設計やクエリ記述に問題がある場合
・システムの性能に対して過度な処理要求が生じ、ストレージエンジンなどの処理が飽和状態になって遅延している場合
対策
1.クエリ自体を修正する
2.インデックスを追加できるかどうかを判断して、可能であれば追加する
無駄なディスク領域を避けるため、アプリケーションで使用される関連クエリーを高速化するインデックスを構築します。
インデックス(MySQLインデックスの使用の仕組み)は、結合や外部キーなどの機能を使用して、さまざまなテーブルを参照するクエリーに重要です。 EXPLAINステートメント(EXPLAINによるクエリーの最適化)を使用して、SELECTに使用するインデックスを判断できます。
ストレージエンジンには、クエリーの高いパフォーマンスを可能にし、維持するための一連のガイドラインがあるのでそちらも参考にすると良いと思います。
リンク先:InnoDB クエリーの最適化
その他、演算子等に関する最適化は別記事としてまとめたので参考にしてください。
適切なインデックスを貼る
適切なインデックスを貼ることで、処理速度が向上します。
また、誤ったインデックスが貼られている場合、削除することで速度が向上する場合もあります。
インデックスを使用する際は、どんなSQL文を多用する予定であるかを考え、実行計画を確認しながらきちんと検討する必要があります。
正規化と非正規化
本当に正規化が正しいのか、また、非正規化が必要なのか、正規化を崩すデメリットを上回るメリットが非正規化にはあるのか、慎重に考えて設計する必要があります。
キャッシュの調整
MySQLがキャッシュに使用するメモリー領域のサイズとプロパティーを調整します。
InnoDBバッファープールや、MySQLクエリーキャッシュを効率的に使用することで、2回目以降はメモリーから結果が取得されるため、繰り返しのクエリーの実行が高速化します。
また、キャッシュメモリーを使用して高速に実行するクエリーでも、必要なキャッシュメモリーを減らすことでさらに最適化できます。
補足:スキーマとは
データベース全体としてのスキーマは、データベースの構造を表現しています。
データベースの構造は、構造を3つに分けて定義する3層スキーマという考え方があります。
3層スキーマの分けた方として、下記のように分類する考え方があります。
・外部スキーマ[ユーザー側のDB]
概念スキーマで定義された論理データから必要なデータのことを指します。ユーザーがデータを取得した時のデータのことです。
画面のUIや入力データなども外部スキーマに含まれます。
・概念スキーマ[開発者側のDB]
データベースに保存しているデータや、データとデータ同志の関係性の定義したものです。
・内部スキーマ
概念スキーマで定義された論理データを具体的にどのようにデータベース管理システム内部に格納するかを定義したものです。
上記３つに分類することで、データベースの構造をわかりやすく定義しています。
次に、個々のデータベースにおけるスキーマについてまとめておきたいと思います。
データベースには様々な種類があり、データベースによって「スキーマ」と呼ばれる概念を持っているデータベースと持っていないデータベースがあります。
今回まとめている、MySQLにはスキーマと呼ばれる概念が存在しません。
(もしMySQLドキュメントでスキーマについて言及している箇所があれば教えて欲しいです...)
参考文献
SQL大量発行処理をいかにして高速化するか
ヤフー社内でやってるMySQLチューニングセミナー大公開
スループットとは何か ～ 改善に役立つ性能試験を行うための前提知識
達人に学ぶSQL徹底指南書
MySQL公式