mysql スタンドアロン データベース最適化のいくつかの実践_MySQL

データベースの最適化については、サポートされるデータの量に応じて 2 つの段階に分けることができます。前者は通常、500 W 以内のデータをサポートできます。 10G を超える場合は、サブデータベースとサブテーブルを考慮する必要があります。さらに、大企業との面接は、多くの場合、単一マシンのデータベースに関する質問から始まり、次にサブデータベースとテーブルについて段階的に質問され、途中にデータベース最適化に関する多くの質問が散りばめられます。この記事では、スタンドアロン データベースの最適化の実践について説明します。データベースは mysql に基づいています。不合理な点がある場合は、修正してください。

1. テーブル構造の最適化

アプリケーションの構築を開始するとき、データベースのテーブル構造の設計は、後のアプリケーションのパフォーマンス、特にユーザー数が増加した後のパフォーマンスに影響を与えることがよくあります。したがって、テーブル構造の最適化は非常に重要なステップです。

1.1、キャラクターセット

一般的に、GBK はデータを保存するときに UTF-8 よりも使用するストレージ容量が少なくなりますが、実際には、UTF-8 はさまざまな言語と互換性があり、このストレージ容量の拡張性を犠牲にする必要はありません。実際、後で GBK から UTF-8 に変換したい場合は、非常にコストがかかり、データの移行が必要になり、ストレージ容量はお金をかけてハードディスクを拡張することで解決できます。

1.2、主キー

mysql の innodb を使用する場合、innodb の基礎となるストレージ モデルは B+ ツリーであり、主キーをクラスター化インデックスとして使用し、挿入されたデータをリーフ ノードとして主キーを通じてすばやく見つけることができるため、レコードを迅速に取得できます。したがって、テーブルを設計するときに主キーを追加する必要があり、これを自動的に増やすことが最善です。自動増加する主キーにより、挿入されたデータは主キーの順序で基になる B+ ツリーのリーフ ノードに挿入されるため、この種の挿入では他の既存のデータを移動する必要がほとんどありません。なので挿入効率は非常に高いです。主キーが自動増加しない場合、主キーの値は毎回ほぼランダムになるため、B+ ツリーの特性を確保するために大量のデータを移動する必要があり、不要なオーバーヘッドが追加される可能性があります。 。

1.3、フィールド

1.3.1. インデックス付きフィールドは not null 制約を使用して追加し、デフォルト値を設定する必要があります

1.3.2. 精度の低下を防ぐために、float または double を使用して小数点を格納することはお勧めしません。

1.3.3. 大きなテキストの読み取りと書き込みは比較的大きな I/O オーバーヘッドを引き起こし、mysql キャッシュを占有するため、大量のデータを保存するために Text/blob を使用することはお勧めできません。たとえば、MySQL はファイルの相対アドレスのみを保存できます。

1.3.4. varchar 型の長さは 8K を超えないようにすることをお勧めします。

1.3.5. 時刻タイプにはタイムスタンプの代わりに日時を使用することをお勧めしますが、日時は 8 バイトを占め、タイムスタンプは 4 バイトのみを占めますが、後者は空であってはならず、タイムゾーンの影響を受けます。

1.3.6. データ作成の変更時刻を記録するために、テーブルに gmt_create と gmt_modified の 2 つのフィールドを追加することをお勧めします。これら 2 つのフィールドを設定する理由は、トラブルシューティングを容易にするためです。

1.4、インデックスの作成

1.4.1. この段階ではビジネスを理解していないため、やみくもにインデックスを追加せず、確実に使用される一部のフィールドにのみ通常のインデックスを追加するようにしてください。

1.4.2. innodb の単一列インデックスを作成する長さは 767 バイトを超えてはなりません。それを超える場合は、最初の 255 バイトがプレフィックス インデックスとして使用されます。

1.4.3. innodb 結合インデックスを作成するときの各列インデックスの長さは 767 バイトを超えてはならず、合計は 3072 バイトを超えてはなりません

2. SQL の最適化

一般的に言えば、SQL にはいくつかの種類しかありません: 基本的な追加、削除、変更、クエリ、ページング クエリ、範囲クエリ、あいまい検索、複数テーブル接続

2.1、基本的なクエリ

一般的なクエリにはインデックスを付ける必要があります。インデックスがない場合は、クエリを変更してインデックスを含むフィールドを追加することをお勧めします。ビジネス シナリオによりこのフィールドが使用できない場合は、コール量を確認する必要があります。このクエリが大きい場合 (たとえば、毎日 10W 以上呼び出される場合)、新しいインデックスを追加する必要があります (1 日あたり 100 回以上の呼び出しなど)。 。さらに、SQL ステートメントで使用されるフィールドはできるだけ追加しないようにしてください。不要なフィールドはチェックされません。これにより、I/O とメモリ領域が無駄になります。

2.2、効率的なページング

limit m,nの本質は、最初にlimit m+nを実行し、その後m行目からn行を取得することです。このように、limitページを後ろにめくると、mが大きくなり、パフォーマンスが低下します。たとえば

select * from A の制限は 100000,10 です。この SQL ステートメントのパフォーマンスは非常に悪いので、次のバージョンに変更することをお勧めします。

A から ID、名前、年齢を選択、ID >=(A から ID を選択、制限 100000,1) 制限 10

2.3、範囲クエリ

範囲クエリには、between、greater than、less than、in が含まれます。 Mysqlのinクエリの条件は数が少ない場合はインデックスクエリを使用できますが、数が多い場合はフルテーブルスキャンになります。 between、greater than、less than などについては、これらのクエリはインデックスに登録されないため、インデックスが作成されるクエリ条件の後に配置するようにしてください。

2.4、ファジークエリ

のような

%name% のようなステートメントを使用すると、インデックスは実行されません。これは、データ量が少ない場合、パフォーマンスに大きな問題はありません。将来的には、このあいまい検索の代わりに検索エンジンを使用することをお勧めします。それができない場合は、あいまいクエリの前にインデックス付けの条件を追加する必要があります。

2.5、マルチテーブル接続

サブクエリと結合の両方を使用して複数のテーブルからデータを取得できますが、サブクエリのパフォーマンスが低いため、サブクエリを結合に変更することをお勧めします。 mysql の結合では、ネストされたループ結合アルゴリズムが使用されます。これは、前のテーブル クエリの結果セットを通じて次のテーブルをクエリすることを意味します。たとえば、前のテーブルの結果セットは 100 個のデータで、後のテーブルのデータ量は 10W です。次に、100*10W データ セットをフィルタリングして、最終的な結果セットを取得する必要があります。したがって、小さな結果セットを持つテーブルを使用して大きなテーブルを結合し、同時に結合フィールドにインデックスを作成するようにしてください。インデックスを構築できない場合は、十分な大きさの結合バッファ サイズを設定する必要があります。上記のどの手法でも結合によるパフォーマンス低下の問題を解決できない場合は、単純に結合の使用を中止し、結合クエリを 2 つの単純なクエリに分割します。また、複数のテーブルの接続は 3 つを超えてはなりません。一般に、3 つを超えるテーブルを使用すると、パフォーマンスが非常に低下します。

3. データベース接続プールの最適化

データベース接続プールは本質的にキャッシュであり、高い同時実行性に対抗する手段です。データベース接続プールの最適化は主にパラメーターの最適化に関するもので、通常は DBCP 接続プールを使用します。その具体的なパラメーターは次のとおりです。

3.1 初期サイズ

初期接続の数。ここでの初期とは、アプリケーションの起動時ではなく、初めて getConnection が取得されたときのことを指します。初期値は同時実行の過去の平均に設定できます

3.2、minIdle

予約するアイドル接続の最小数。 DBCP は、アイドル状態の接続をリサイクルするためにバックグラウンドでスレッドを開始し、スレッドがアイドル状態の接続をリサイクルするとき、minIdle 接続の数を保持します。通常は 5 に設定されますが、同時実行の量が非常に少ない場合は 1 に設定できます。

3.3、maxIdle

予約されたアイドル接続の最大数は、ビジネス同時実行のピークに応じて設定されます。たとえば、同時実行のピークが 20 の場合、ピークが過ぎても、これらの接続はすぐにはリサイクルされません。短期間後に別のピークが発生しても、接続プールはこれらのアイドル接続を頻繁に作成する必要なく再利用できます。そして接続を閉じます。

3.4、maxActive

アクティブな接続の最大数は、許容される同時実行の極値に従って設定されます。たとえば、単一マシンの同時実行の許容極値は 100 です。この場合、maxActive が 100 に設定されている場合、同時に処理できるリクエストは 100 件のみとなり、超過したリクエストは最大待機時間が経過すると破棄されます。この値は、悪意のある同時実行攻撃を防止し、データベースを保護するために設定する必要があります。

3.5、maxWait

リクエストが接続の取得を待機している間はスレッドを解放できず、スレッドの同時実行性が低下するため、リクエストがすぐに失敗する可能性があるため、接続を取得するための最大待機時間を 3 秒など短く設定することをお勧めします。はい、この時間が長すぎるように設定されている場合 (インターネットで推奨されている 60 秒など)、そのようなリクエストが多い限り、このスレッドはこの 60 秒以内に解放されません。スレッドとサービスが利用できなくなります。

3.6、分EvictableIdleTimeMillis

接続がリサイクルされずにアイドル状態を維持する時間、デフォルトは 30 分です。

3.7、検証クエリ

接続が有効かどうかを確認するために使用される SQL ステートメント。通常は単純な SQL であり、設定することをお勧めします

3.8、testOnBorrow

接続の申請時に接続が検出されますが、パフォーマンスに重大な影響を与えるため、オンにすることはお勧めできません。

3.9、testOnReturn

返却時に接続を確認してください。パフォーマンスに重大な影響を与えるため、オンにすることはお勧めできません。

3.10、アイドル中テスト

これをオンにすると、バックグラウンドで接続をクリーンアップするスレッドが時々アイドル状態の接続を検証します。接続が失敗した場合は、パフォーマンスに影響しません。 に

3.11、numTestsPerEvictionRun

毎回チェックされるリンクの数を表します。すべてのリンクを毎回効果的にチェックできるように、maxActive と同じ大きさに設定することをお勧めします。

3.12. 接続プールを予熱します

接続プールについては、アプリケーションの起動時に予熱し、外部アクセスを提供する前に簡単な SQL クエリを実行して、必要な数の接続で接続プールを埋めることをお勧めします。

4. インデックスの最適化

データ量が一定レベルまで増加すると、SQL 最適化によってパフォーマンスを向上させることができなくなり、インデックス作成という大きな手段が必要になります。一般に、インデックス作成には 3 つのレベルがあり、さらに、インデックスを作成するフィールドの選択性を考慮する必要があります。

4.1、プライマリインデックス

where の背後にある条件に基づいてインデックスを作成します。単一の列では通常のインデックスを作成でき、複数の列では結合インデックスを作成できます。複合インデックスでは、左端の接頭辞の原則に注意する必要があります。

4.2、セカンダリインデックス

order by または group by で使用されるフィールドがある場合は、このフィールドにインデックスを構築することを検討できます。この方法では、インデックスが自然に順序付けされるため、order by や group by による並べ替えが回避され、パフォーマンスが向上します。 。

4.3、3 レベルのインデックス

上記の 2 つの方法が機能しない場合は、クエリ対象のフィールドにインデックスを追加します。これにより、MySQL がデータをクエリするときに、いわゆるインデックス カバレッジが形成され、I/O 操作が 1 つ削減されます。最初に主キー インデックスを確認し、次に主キー インデックスに基づいて通常のインデックスを確認し、次に通常のインデックスに基づいて対応するレコードを確認します。必要なレコードがすべて通常のインデックスにある場合、3 番目の手順は必要ありません。もちろん、このインデックスの作成方法は非常に極端であり、一般的なシナリオには適していません。

4.4. インデックスの選択性

インデックスを構築するときは、選択性の高いフィールドに基づいてインデックスを構築するようにしてください。選択性が高いとはどういう意味ですか?いわゆる高い選択性とは、このフィールドで検出されるデータの量が少ないことを意味します。たとえば、名前で個人の情報を確認した場合、検出されるデータの量は通常非常に少量になりますが、性別で確認した場合は半分になります。したがって、名前は選択性の高いフィールドであり、性別は選択性が低いフィールドです。

5. 履歴データのアーカイブ

データ量が 1 年で 500 万件増加すると、インデックスでは何もできなくなります。一般的な考え方は、サブデータベースとテーブルを検討することです。ビジネスが爆発的に成長するわけではないが、データがゆっくりと増加する場合は、データベースのシャーディングやテーブルのシャーディングという複雑な技術的手段を無視して、代わりに履歴データをアーカイブできます。 6 か月前のデータなど、ライフサイクルが終了した履歴データをアーカイブします。 Quartz のスケジューリング タスクを使用して、早朝に 6 か月前のデータを定期的にチェックアウトし、それをリモートの hbase サーバーに保存できます。もちろん、緊急時に備えて履歴データのクエリ インターフェイスも提供する必要があります。

上記はmysqlスタンドアロンデータベースの最適化データです。今後も関連データを追加していきますので、どうぞよろしくお願いいたします。