MySQLの高性能最適化スキルの概要

データベースコマンド仕様

1. すべてのデータベース オブジェクト名には小文字を使用し、アンダースコアで区切る必要があります。
2. すべてのデータベース オブジェクト名では、MySQL の予約キーワードの使用は禁止されています (テーブル名にキーワード クエリが含まれている場合は、一重引用符で囲む必要があります)
3. データベース オブジェクトの名前はわかりやすいもので、32 文字を超えてはなりません。
4. 一時データベース テーブルには、プレフィックスとして tmp_ を付け、サフィックスとして日付を付ける必要があります。また、バックアップ テーブルには、プレフィックスとして bak_ を付け、サフィックスとして日付 (タイムスタンプ) を付ける必要があります。
5. 同じデータを格納するすべての列は、同じ名前と型を持つ必要があります (通常、関連列として使用されます。クエリ中に関連付けられた列の型が一貫していない場合、データ型の暗黙的な変換が自動的に実行され、列のインデックスが失敗し、クエリの効率が低下します)

基本的なデータベース設計仕様

1. すべてのテーブルは Innodb ストレージ エンジンを使用する必要があります。特別な要件 (列ストレージ、ストレージ スペース データなど、Innodb が満たせない機能) がない場合は、すべてのテーブルは Innodb ストレージ エンジンを使用する必要があります (MySQL 5.5 より前はデフォルトで Myisam が使用され、5.6 以降はデフォルトで Innodb が使用されます)。
Innodb は、トランザクション、行レベルのロック、優れた回復性、および高い同時実行性における優れたパフォーマンスをサポートします。

2. データベースとテーブルの文字セットはUTF8を使用するように統一されています
互換性が向上します。統一された文字セットにより、文字セットの変換によって発生する文字化けを回避できます。異なる文字セットは比較前に変換する必要があり、これによりインデックス エラーが発生します。絵文字表現をデータベースに保存する必要がある場合は、文字セットで utf8mb4 文字セットを使用する必要があります。

3. すべてのテーブルとフィールドにコメントを付ける必要がある。コメント句を使用してテーブルと列にコメントを追加し、データ辞書を最初から維持する。

4. 1 つのテーブル内のデータのサイズを制御するようにしてください。500 万以内に制御することをお勧めします。
500 万は MySQL データベースの制限ではありません。数が多すぎると、テーブル構造の変更、バックアップ、リカバリに大きな問題が発生します。

履歴データ（ログデータに適用）、サブライブラリ、サブテーブル（業務データに適用）などをアーカイブすることで、データ量を制御できます。

5. MySQL パーティション テーブルは注意して使用してください。パーティション テーブルは物理的には複数のファイルで表されますが、論理的には 1 つのテーブルとして表されます。
パーティション間クエリの効率が低下する可能性があるため、パーティション キーは慎重に選択してください。
ビッグデータを管理するには、物理​​的なパーティショニングを使用することをお勧めします。

6. ホットデータとコールドデータを分離し、テーブルの幅を狭くする
MySQL では、各テーブルの最大列数は 4096 に制限されており、各データ行のサイズは 65535 バイトを超えることはできません。
ディスク IO を減らして、ホット データのメモリ キャッシュ ヒット率を確保します (テーブルが広いほど、テーブルをメモリ バッファー プールにロードするときに占有されるメモリが多くなり、消費される IO も多くなります)。

無駄なコールドデータの読み取りを避けるためにキャッシュをより効果的に使用します。

頻繁に一緒に使用される列を 1 つのテーブルに配置します (結合操作の増加を回避するため)。

7. テーブル内に予約フィールドを作成することは禁止されています。予約フィールドの命名は、明確で意味のあるものにすることが困難です。予約フィールドでは格納されているデータ型を確認できないため、適切な型を選択できません。予約済みのフィールド タイプを変更すると、テーブルがロックされます。

8. 画像やファイルなどの大きなバイナリ データをデータベースに保存することは禁止されています。通常、ファイルは非常に大きいため、短期間でデータ量が急速に増加します。データベースがデータベースを読み取るとき、通常は大量のランダム IO 操作が実行されます。ファイルが大きい場合、IO 操作には非常に時間がかかります。

通常、ファイルサーバーに保存されるデータベースには、ファイルのアドレス情報のみが保存されます。

9. データベースのストレステストをオンラインで実行しない
10.開発環境やテスト環境から本番環境のデータベースに直接接続することは禁止されています

データベースフィールド設計仕様

1. ストレージのニーズを満たす最小のデータ型を優先する

理由：

列フィールドが大きいほど、インデックスを作成するために必要なスペースが大きくなります。ページに格納できるインデックス ノードが少ないほど、トラバーサルに必要な IO 時間が増え、インデックスのパフォーマンスが低下します。

方法：

IPアドレスを整数に変換するなど、文字列をデジタル型に変換して保存する
MySQLはIPアドレスを処理する2つの方法を提供する

inet_aton は ip を符号なし整数 (4-8 ビット) に変換します。
inet_ntoa は整数 IP アドレスをアドレスに変換します。データを挿入する前に、inet_aton を使用して IP アドレスを整数に変換し、スペースを節約します。データを表示するときは、inet_ntoa を使用して整数 IP アドレスを表示用のアドレスに変換します。

非負データ（自動増分 ID、整数 IP など）の場合、符号なし整数を使用して保存する必要があります。

署名されていないデータは、署名されたデータの 2 倍のストレージ容量を占有する可能性があります。

符号付き整数 -2147483648~2147483647
符号なし整数 0~4294967295
VARCHAR(N) の N はバイト数ではなく文字数を表します。UTF8 を使用して 255 個の中国語文字を保存する場合、Varchar(255) = 765 バイトになります。 * 長さが大きすぎると、メモリの消費量が増えます。

2. TEXT および BLOB データ型の使用は避けてください。最も一般的な TEXT 型は 64k のデータを格納できます。

BLOB 列または TEXT 列を別々の拡張テーブルに分離することをお勧めします。
Mysql メモリ一時テーブルは、TEXT や BLOB などの大きなデータ型をサポートしていません。クエリにこのようなデータが含まれている場合、メモリ一時テーブルはソートなどの操作に使用できず、ディスク一時テーブルを使用する必要があります。

さらに、この種のデータの場合、Mysql は 2 番目のクエリを実行する必要があり、SQL パフォーマンスは非常に低下しますが、このようなデータ型が使用できないことを意味するわけではありません。

どうしても使用する必要がある場合は、BLOB 列または TEXT 列を別の拡張テーブルに分離することをお勧めします。クエリを実行するときは、select * を使用せず、必要な列のみを取得します。列内のデータが必要ない場合は、TEXT 列をクエリしないでください。

MySQL ではインデックス フィールドの長さに制限があるため、TEXT または BLOB 型ではプレフィックス インデックスのみを使用できます。そのため、TEXT 型ではプレフィックス インデックスのみを使用でき、TEXT 列にはデフォルト値を設定できません。

3. ENUM型の使用を避ける

ENUM値を変更するには、ALTERステートメントを使用する必要があります。

ENUM型のORDER BY操作は非効率であり、追加の操作が必要である。

ENUM列挙値として数値を使用することは禁止されています

4. 可能な限りすべての列をNOT NULLとして定義する

理由：

NULL 列にインデックスを付けるには保存するための余分なスペースが必要なので、比較や計算にさらに多くのスペースが使用されます。NULL 値は特別に処理する必要があります。

5. TIMESTAMP（4バイト）またはDATETIME型（8バイト）を使用して時間を保存します。

TIMESTAMPは1970-01-01 00:00:01～2038-01-19-03:14:07の時間範囲を保存します。
TIMESTAMP は INT と同じ 4 バイトを占めますが、INT よりも読みやすくなります。TIMESTAMP 値の範囲外の値は、DATETIME 型を使用して保存されます。

日付データを保存するのに文字列がよく使用される（誤った方法）

デメリット1: 計算や比較に日付関数を使用できない デメリット2: 文字列を使用して日付を保存すると、より多くのスペースが必要になります

6. 金融関連の通貨データは小数型を使用する必要があります

不正確な浮動小数点数: float、double
精度浮動小数点数: 10進数
Decimal 型は、計算中に精度が失われない正確な浮動小数点数です。占有されるスペースは、定義された幅によって決まります。4 バイトごとに 9 桁を格納でき、小数点は 1 バイトを占有します。bigint より大きい整数データを格納するために使用できます。

インデックス設計仕様

1. 各テーブルのインデックス数を制限します。1 つのテーブルには 5 個以下のインデックスを含めることをお勧めしますが、多ければ多いほど良いです。インデックスは効率を向上させることができますが、効率を低下させることもできます。

インデックスはクエリの効率を高めることができますが、挿入と更新の効率、さらには場合によってはクエリの効率も低下させる可能性があります。

MySQL オプティマイザがクエリを最適化する方法を選択する際、統合された情報に基づいて利用可能な各インデックスを評価し、最適な実行プランを生成します。クエリに同時に多くのインデックスを使用できる場合、MySQL オプティマイザが実行プランを生成する時間が長くなり、クエリのパフォーマンスも低下します。

2. テーブル内の各列に個別のインデックスを作成しないでください。
バージョン 5.6 より前では、1 つの SQL ステートメントでテーブル内の 1 つのインデックスしか使用できませんでした。バージョン 5.6 以降では、インデックスをマージする最適化方法がありますが、それでも結合インデックスを使用するクエリ方法ほど優れているとは言えません。

3. すべてのInnodbテーブルには主キーが必要です
Innodb はインデックス構成テーブルです。データが格納される論理的な順序は、インデックスが付けられる順序と同じです。各テーブルには複数のインデックスを設定できますが、テーブルに設定できるストレージ順序は 1 つだけです。

Innodb は、主キー インデックスの順序でテーブルを整理します。

頻繁に更新される列を主キーとして使用しないでください。また、複数列の主キー（結合インデックスに相当）も使用しないでください。
UUID、MD5、HASH、または文字列の列を主キーとして使用しないでください (データの順次的な増加は保証されません)。
主キーとして自動増分ID値を使用することをお勧めします。

一般的なインデックス列の推奨事項

SELECT、UPDATE、および DELETE ステートメントの WHERE 句に出現する列。ORDER BY、GROUP BY、および DISTINCT に含まれるフィールド。1 と 2 のフィールドに一致するすべての列に対してインデックスを作成しないでください。通常は、1 と 2 のフィールドに対して結合インデックスを作成する方が適切です。複数テーブル結合の関連列

インデックス列の順序を選択する方法

インデックスを作成する目的は、インデックスを使用してデータを検索し、ランダム IO を減らし、クエリのパフォーマンスを向上させることです。インデックスでフィルターできるデータが少ないほど、ディスクから読み取られるデータが少なくなります。

最も識別力の高い列は、結合インデックスの左側に配置されます（識別力 = 列内の異なる値の数 / 列内の行の総数）
フィールド長が短い列を結合インデックスの左側に配置するようにしてください (フィールド長が短いほど、1 ページに格納できるデータが多くなり、IO パフォーマンスが向上するため)
最も頻繁に使用される列を共同インデックスの左側に配置します（これにより、インデックスの数が少なくなります）

冗長なインデックスや重複したインデックスを作成しないようにします (クエリ オプティマイザが実行プランを生成するのにかかる時間が長くなります)

重複インデックスの例: 主キー(ID)、インデックス(ID)、一意のインデックス(ID)
冗長なインデックスの例: index(a,b,c)、index(a,b)、index(a)

頻繁に実行されるクエリの場合は、まずカバーリング インデックスの使用を検討してください。

カバーリング インデックス: すべてのクエリ フィールド (where、select、ordery by、group by に含まれるフィールド) を含むインデックス。カバーリング インデックスの利点:

Innodbテーブルインデックスの二次クエリを避ける

Innodb は、クラスター化されたインデックスの順序でデータを保存します。Innodb の場合、セカンダリ インデックスには、リーフ ノードの行のプライマリ キー情報が格納されます。セカンダリ インデックスを使用してデータをクエリする場合は、対応するキー値を見つけた後、プライマリ キーを介してセカンダリ クエリを実行して、実際に必要なデータを取得する必要があります。

カバーリング インデックスでは、すべてのデータをセカンダリ インデックスのキー値から取得できるため、プライマリ キーに対するセカンダリ クエリが回避され、IO 操作が削減され、クエリの効率が向上します。

ランダム IO をシーケンシャル IO に変換して、クエリの効率を上げることができます。カバーリング インデックスはキー値の順序で保存されるため、IO 集約型の範囲検索では、ディスクから各行をランダムに読み取る場合よりもデータ IO が大幅に少なくなります。そのため、アクセス中にカバーリング インデックスを使用すると、ディスクのランダム読み取り IO をインデックス検索のシーケンシャル IO に変換できます。

インデックス SET 仕様

外部キー制約の使用はできるだけ避けてください。外部キー制約の使用は推奨されませんが、テーブル間の関連キーにインデックスを作成する必要があります。外部キーはデータの参照整合性を確保するために使用できますが、ビジネス側で外部キーを実装することをお勧めします。これにより、親テーブルと子テーブルの書き込み操作に影響し、パフォーマンスが低下します。

データベースSQL開発仕様

1. データベース操作にはプリコンパイルされたステートメントを使用することをお勧めします。

プリコンパイルされたステートメントはこれらのプランを再利用できるため、SQL コンパイルに必要な時間が短縮され、動的 SQL によって発生する SQL インジェクションの問題が解決されます。

パラメータのみを渡す方が、SQL ステートメントを渡すよりも効率的です。

同じステートメントを一度解析して複数回使用することで、処理効率を向上させることができます。

2. データ型の暗黙的な変換を避ける

次のような暗黙的な変換により、インデックスが失敗します。

ID = '111' の顧客から名前と電話番号を選択します。

3. テーブル上の既存のインデックスを最大限に活用する

クエリ条件で二重の % 記号を使用しないでください。たとえば、「%123%」のような文字列（先頭に%がなく、末尾に%のみがある場合は、列のインデックスを使用できます）

SQL ステートメントでは、範囲クエリの複合インデックスで 1 つの列のみを使用できます。たとえば、列 a、b、c に結合インデックスがあり、クエリ条件に列 a の範囲クエリがある場合、列 b と c のインデックスは使用されません。

結合インデックスを定義するときに、列 a を範囲検索に使用する場合は、列 a を結合インデックスの右側に配置し、not in 操作を最適化するために左結合または not exists を使用する必要があります。not in によっても通常はインデックスの無効化が発生するためです。

4. データベースを設計する際には、将来の拡張を考慮する必要があります

5. プログラムは異なるアカウントを使用して異なるデータベースに接続し、データベース間のクエリを実行します。

データベースの移行、サブデータベース、サブテーブルの余地を残して、ビジネスの結合を減らし、過剰な権限によるセキュリティリスクを回避します。

6. SELECT * を使用しないでください。クエリを実行するには、SELECT <フィールド リスト> を使用する必要があります。

理由：

CPU、IO、ネットワーク帯域幅のリソースをより多く消費します。カバーリング インデックスを使用してテーブル構造の変更の影響を軽減することはできません。

7. フィールドリストなしでINSERT文を使用しないでください

のように：

値に挿入します（'a'、'b'、'c'）;

使用すべきもの:

t(c1,c2,c3)に値（'a'、'b'、'c'）を挿入します。

8. サブクエリの使用を避けます。サブクエリを結合操作に最適化できます。

通常、サブクエリが in 句にあり、サブクエリが単純な SQL (union、group by、order by、limit 句を除く) である場合にのみ、サブクエリを最適化のために相関クエリに変換できます。

サブクエリのパフォーマンスが低下する理由:

サブクエリの結果セットはインデックスを使用できません。通常、サブクエリの結果セットは一時テーブルに格納されます。メモリ内の一時テーブルであっても、ディスク上の一時テーブルであっても、インデックスは存在しないため、クエリのパフォーマンスはある程度影響を受けます。特に、より大きな結果セットを返すサブクエリの場合、クエリ パフォーマンスへの影響は大きくなります。

サブクエリはインデックスのない一時テーブルを大量に生成するため、CPU と IO リソースを過剰に消費し、低速クエリを大量に生成します。

9. JOINを使用して多くのテーブルを結合しないようにする

MySQL には関連するキャッシュがあり、キャッシュのサイズは join_buffer_size パラメータで設定できます。

MySQL では、同じ SQL 文で複数のテーブルを結合すると、関連付けキャッシュが 1 つ多く割り当てられます。1 つの SQL 文で関連付けられるテーブルが多いほど、占有されるメモリが多くなります。

プログラムで多数のマルチテーブル結合操作が使用され、join_buffer_size が適切に設定されていない場合、サーバー メモリのオーバーフローが簡単に発生し、サーバー データベースのパフォーマンスの安定性に影響します。

同時に、関連付け操作では、一時テーブル操作が生成され、クエリの効率に影響します。Mysql では最大 61 個のテーブルを関連付けることができますが、5 個を超えないようにすることをお勧めします。

10. データベースとのやり取りの回数を減らす

データベースはバッチ操作の処理に適しています。複数の同一操作を組み合わせることで、処理効率が向上します。

11. 同じ列に対して判定または判断を行う場合は、またはの代わりにinを使用します。

in値の数は500を超えてはなりません。in操作では、インデックスをより効果的に使用したり、ほとんどの場合にインデックスをほとんど使用しないようにすることができます。

12. ランダムソートに order by rand() を使用しない

order by rand() は、テーブル内の条件を満たすすべてのデータをメモリにロードし、ランダムに生成された値に従ってメモリ内のすべてのデータをソートし、各行にランダムな値を生成する可能性があります。条件を満たすデータセットが非常に大きい場合、CPU、IO、およびメモリリソースを大量に消費します。

プログラム内でランダムな値を取得してから、データベースからデータを取得することをお勧めします。

13. WHERE句では関数変換と列の計算は禁止されています

関数を使用して列を変換または計算する場合、インデックスは使用できません。

推奨されません:

where date(create_time)='20190101'


推薦する：

where create_time >= '20190101' and create_time < '20190102'

14. 明らかに重複する値がない場合には、UNION ではなく UNION ALL を使用する

UNION は、2 つの結果セットのすべてのデータを一時テーブルに格納し、重複排除を実行します。
UNION ALLは結果セットの重複排除を実行しなくなりました

15. 複雑で大きなSQLを複数の小さなSQLに分割する

大きな SQL は論理的に複雑であり、計算に大量の CPU を必要とします。
MySQL では、1 つの SQL ステートメントは計算に 1 つの CPU しか使用できません。
SQL分割は並列実行を通じて処理効率を向上させることができる

データベース操作動作仕様

100万行を超えるバッチ書き込み（UPDATE、DELETE、INSERT）操作は、バッチで複数回実行する必要があります。

1. 大規模なバッチ操作はマスタースレーブ間の重大な遅延を引き起こす可能性がある

マスター スレーブ環境では、大規模なバッチ操作によって重大なマスター スレーブ遅延が発生する可能性があります。大規模なバッチ書き込み操作は通常、実行に長い時間がかかります。

マスター データベースでの実行が完了した場合にのみ、他のスレーブ データベースで実行されるため、マスター データベースとスレーブ データベース間で長い遅延が発生します。

2. binlogログが行形式の場合、大量のログが生成されます

大規模なバッチ書き込み操作では、特に行形式のバイナリ データの場合、大量のログが生成されます。各データ行の変更は行形式で記録されるため、一度に変更するデータが多いほど、生成されるログの数が多くなり、ログの送信と復元にかか​​る時間が長くなります。これも、マスター スレーブ遅延の原​​因です。

3. 大規模なトランザクション操作を避ける

1 つのトランザクションで大量のデータを変更する必要があるため、テーブル内の大量のデータがロックされ、大量のブロックが発生し、MySQL のパフォーマンスに非常に大きな影響を及ぼします。

特に、長期間のブロックはデータベースの利用可能な接続をすべて占有し、実稼働環境の他のアプリケーションがデータベースに接続できなくなります。したがって、大規模なバッチ書き込み操作はバッチで実行する必要があることに注意することが重要です。

大きなテーブルの場合は、pt-online-schema-changeを使用してテーブル構造を変更します。

大規模なテーブル変更によるマスタースレーブ遅延の回避

テーブル フィールドを変更するときにテーブルをロックしないでください。大きなテーブルのデータ構造を変更する場合は注意してください。特に実稼働環境では許容できない重大なテーブル ロック操作が発生します。

pt-online-schema-change は、まず元のテーブルと同じ構造を持つ新しいテーブルを作成し、新しいテーブルのテーブル構造を変更し、元のテーブルのデータを新しいテーブルにコピーし、元のテーブルにいくつかのトリガーを追加します。

元のテーブルに新しく追加されたデータを新しいテーブルにコピーします。すべての行データがコピーされたら、新しいテーブルに元のテーブルと同じ名前を付け、元のテーブルを削除します。元の DDL 操作を複数の小さなバッチに分割します。

プログラムが使用するアカウントにスーパー権限を付与することは禁止されています

最大接続数に達すると、スーパー権限を持つ別のユーザーが実行されます。
スーパー権限は、DBAアカウントが問題を処理する場合にのみ使用できます。

プログラムがデータベースアカウントに接続する場合は、最小権限の原則に従ってください。

プログラムが使用するデータベースアカウントは、1つのDBでのみ使用できます。プログラムが複数のDB間で使用するアカウントには、原則としてドロップ権限を与えることはできません。