MySQL データベースに関するよくある面接の質問を要約して整理する-mysql チュートリアル-php.cn

この記事では、mysql に関する関連知識を紹介しており、主にデータベースメーカーの面接質問の概要を紹介していますので、一緒に見ていきましょう。

MySQL データベースに関するよくある面接の質問を要約して整理する

# 推奨学習:

mysql ビデオチュートリアル

##1. データベースの一般的なパラダイム:

第一正規形 (1NF): テーブルの列をさらに分割できないことを意味します。データベース内のテーブルの各列は分割できない基本データ項目です。複数の値をテーブルに含めることはできません。同じ列;

第 2 正規形 (2NF): 1NF に基づいており、2 つの部分も含まれています: まず、テーブルには主キーが必要です。第 2 に、テーブル内の非主キー列は完全に依存している必要があります。パート;

第 3 正規形 (3NF): 2NF に基づいて、主キーに対する非主キー列の推移的な依存関係が排除されます。 -主キー列は主キーに直接依存する必要があります。

BC 標準形式 (BCNF): 3NF に基づいて、コード部分の main 属性の推移的な依存性を排除します。

2. の実行プロセスSQL ステートメント:

2.1. クライアントデータベースドライバーとデータベース接続プール:

(1) クライアントがデータベースと通信する前に、クライアントとの接続を確立します。 MySQL は、データベースドライバーを介して、確立が完了した後、SQL ステートメントを送信します。

(2) 頻繁な接続の作成と切断によるシステムパフォーマンスの低下を軽減するために、一定数の接続スレッドが使用されます。データベース接続プールを通じて維持され、接続が必要な場合は接続プールから直接取得し、使用後は接続プールに戻します。一般的なデータベース接続プールには、Druid、C3P0、DBCP

2.2. MySQL アーキテクチャのサーバー層の実行プロセス:

(1) コネクタ: 主に顧客との通信クライアントは接続を確立し、権限を取得し、接続を維持および管理します

(2) クエリキャッシュ: 最初にキャッシュ内でクエリを実行し、見つかった場合は直接返します。クエリがキャッシュ内で見つからない場合は、、データベース内のクエリ。

MySQL キャッシュはデフォルトでオフになっており、キャッシュの使用は推奨されず、MySQL8.0 バージョンではクエリキャッシュ機能全体が削除されました。これは主に、その使用シナリオの制限によって発生します。

まず、キャッシュ内のデータストレージ形式について説明します。キー (SQL ステートメント) - 値 (データ値) です。 (キー) のみが存在します少しの違いが直接データベースクエリにつながります;
テーブル内のデータは静的ではないため、そのほとんどは頻繁に変更され、データベース内のデータが変更されると、関連付けられます抽象構文ツリーを取得し、プリプロセッサを使用して抽象構文ツリーに対して意味検証を実行し、抽象構文ツリー内のテーブルが存在するかどうかを判断します。存在する場合は、選択されたテーブルが存在するかどうかを判断します。テーブルなどに射影列フィールドが存在します。
(4) オプティマイザー: 主に SQL の字句解析と構文解析後に得られた構文ツリーを使用し、データディクショナリの内容と統計情報を介して、一連の操作を経て最終的に実行を取得します。使用するインデックスの選択を含む計画

インデックスクエリを使用するかどうかを分析する場合、

動的データサンプリング統計分析

によって決定されます。統計的に分析される限り、

分析エラーが発生する可能性があります

。そのため、インデックスを作成せずに SQL を実行する場合は、この要素も考慮する必要があります。
(5) Executor: 一連の実行計画に従って呼び出されます。ストレージエンジンが提供する API インターフェイスは、操作データの呼び出しと SQL の実行に使用されます。
2.3. Innodb ストレージエンジンの実行プロセス:

(1) まず、MySQL エグゼキュータはストレージエンジンの API を呼び出し、実行計画に従ってデータをクエリします。

(2) ストレージエンジンは、まずストレージエンジンからデータをクエリします。そうでない場合はディスクにクエリが実行され、クエリが実行された場合はバッファプールに格納されます

(3) データがバッファプールにロードされている間、このデータの元のレコードがバッファプールにロードされます。データはUNDOログファイルに保存されます

(4) innodbはバッファプールで更新操作を実行します
## (5) 更新されたデータはREDOログバッファ##に記録されます
# (6) 送信されたトランザクションが送信されます。同時に次の 3 つの処理が行われます。

(7)(1 つ目) REDO ログバッファ内のデータを REDO ログにフラッシュします。 file

(8)(2つ目) 事) この操作記録をbinログファイルに書き込みます

(9)(3つ目) binログファイルの名前と、 bin 内の更新されたコンテンツの場所を redo ログに記録し、同時に redo ログの最後にコミットマーク

を追加します (10) バックグラウンドスレッドを使用して、更新されたデータをフラッシュします特定の機会にバッファプール内のデータを MySQL データベースに転送し、メモリとデータベースを結合しますデータは統合されたままになります

#3. 一般的に使用されるストレージエンジン? InnoDB と MyISAM の違いは何ですか?

ストレージエンジンは、基盤となる物理データに対して実際の操作を実行し、サーバーサービス層のデータを操作するためのさまざまな API を提供するコンポーネントです。一般的に使用されるストレージエンジンには、InnoDB、MyISAM、Memory などがあります。ここでは主に InnoDB と MyISAM の違いを紹介します。

(1) トランザクション: MyISAM はトランザクションをサポートしませんが、InnoDB はトランザクションをサポートします

(2) ロックレベル: MyISAM はテーブルレベルのロックのみをサポートします, InnoDB は行レベルのロックとテーブルレベルのロックを行います。デフォルトでは行レベルのロックが使用されますが、行ロックはインデックスを介してデータがクエリされる場合にのみ使用され、それ以外の場合はテーブルロックが使用されます。行レベルのロックは、ロックの取得と解放の各操作でテーブルロックよりも多くのリソースを消費します。行ロックを使用するとデッドロックが発生する可能性がありますが、テーブルレベルのロックではデッドロックは発生しません

(3) 主キーと外部キー: MyISAM では、インデックスと主キーのないテーブルの存在が許可されます。外部キーをサポートします。 InnoDB の主キーは空にすることはできず、主キーの自動拡張をサポートしています。主キーまたは空でない一意のインデックスが設定されていない場合は、6 バイトの主キーが自動的に生成され、外部キー整合性制約をサポートします。

(4) インデックス構造: MyISAM と InnoDB は両方とも B ツリーインデックスを使用しており、MyISAM の主キーインデックスと補助インデックスのデータフィールドは行データレコードが保存されるアドレスです。ただし、InnoDB の主キーインデックスのデータフィールドは行データレコードのアドレスではなく、行のすべてのデータ内容を保存しますが、補助インデックスのデータフィールドは主インデックスの値を保存します。

InnoDB の補助インデックスは主キーインデックスの値を保存するため、補助インデックスを使用するにはインデックスを 2 回取得する必要があります。まず、補助インデックスを取得して主キーを取得し、次に主キーを使用します。プライマリインデックス内のレコードを取得します。これが、長すぎるフィールドを主キーとして使用することをお勧めしない理由です。補助インデックスには主キー列が含まれているため、主キーが長すぎるフィールドを使用すると、他の補助インデックスが大きくなる原因となるため、主キーはできるだけ小さく、小さいほど小さくします。

(5) フルテキストインデックス: MyISAM はフルテキストインデックスをサポートします。InnoDB はバージョン 5.6 より前のフルテキストインデックスをサポートしません。バージョン 5.6 以降のバージョンではフルテキストインデックスのサポートが開始されます。

(6) テーブル具体的な行数:

② InnoDB: テーブル内の総行数が保存されないため、select count() from table を使用するとテーブル全体を走査する必要があり、多大なコストがかかります。

(7) 記憶構造:

② InnoDB: データとインデックスをテーブルスペースに保存します。すべてのテーブルは同じデータファイルに保存されます。InnoDB テーブルのサイズは、オペレーティングシステムファイルのサイズによってのみ制限されます。通常は 2GB です。。

(8) 記憶容量:

② InnoDB: より多くのメモリとストレージを必要とし、データとインデックスをキャッシュするためにメインメモリ内に独自の専用バッファプールを確立します。

(9) 該当するシナリオ:

② データテーブルが主にレコードのクエリに使用され、読み取り操作が書き込み操作よりはるかに多く、データベーストランザクションのサポートを必要としない場合、MyISAM エンジンはより高度な処理を提供できます。

注: MyISAM ストレージエンジンは、mysql8.0 バージョンで廃止されました

4. ACID とその実装原理取引？

データベーストランザクションは同時実行制御の基本単位であり、操作の論理セットを指します。操作のすべてが実行されるか、まったく実行されません。

4.1. トランザクション ACID:

(1) 原子性: トランザクションは分割できない作業単位です。トランザクション内のすべての操作は成功または失敗します。トランザクションが失敗した場合は、ロールバックする必要があります。

(2) 分離: トランザクションによって操作されるデータが、送信される前に他のトランザクションから見える程度。

(3) 永続性: トランザクションがコミットされると、データベース内のデータに対する変更は永続的になります。

(4) 一貫性: トランザクションによってデータの整合性とビジネスの一貫性が破壊されることはありません。たとえば、送金する場合、トランザクションが成功しても失敗しても、両当事者間の合計金額は変わりません。

4.2. ACID 実装原則:

4.2.1. アトミック性: アトミック性は MySQL のロールバックログ undo ログを通じて行われます来 実装: トランザクションがデータベースを変更すると、InnoDB は対応する Undo ログを生成します。トランザクションの実行が失敗するかロールバックが呼び出され、トランザクションがロールバックされると、Undo ログ内の情報を使用してデータを転送できます。改造前の状態に戻りました。

4.2.2. 分離:

(1) トランザクション分離レベル:

同時環境でそれを確保するにはデータベースには、読み取りデータの整合性と一貫性を保証するために 4 つのトランザクション分離レベルが用意されています。分離レベルが高いほど、データの整合性と整合性はより良く保証されますが、高い同時実行パフォーマンスへの影響が大きくなり、実行速度が低下します。効率。。 (4 つの分離レベルは上から下に増加します)

#Read uncommitted: トランザクションが実行中に他のトランザクションのコミットされていないデータを読み取ることを許可します;
Read commit: トランザクションを許可します実行中に他のトランザクションによって送信されたデータを読み取るため;
反復読み取り (デフォルトレベル): 同じトランザクション内でいつでもクエリ結果に一貫性があります;
読み取りシリアル化: すべてのトランザクションが実行されます読み取りごとにテーブルレベルの共有ロックを取得する必要があり、読み取りと書き込みは相互にブロックされます。

(2) トランザクションの同時実行性の問題:

トランザクションの分離が考慮されていない場合、トランザクションの同時実行環境で問題が発生する可能性があります。

更新の喪失: 2 つ以上のトランザクションが同じデータを操作し、選択された値に基づいて行を更新する場合。これは、各トランザクションが他のトランザクションの存在を認識していないためです。、更新が失われる問題が発生します。最後の更新が他のトランザクションによって行われた更新を上書きします。
ダーティリード: トランザクション A がデータにアクセスし、データを変更したことを意味します (トランザクションはコミットされていません)。このとき、トランザクション B もこのデータを使用します。その後、トランザクション A はロールバックをキャンセルし、データを元の値に戻すと、B が読み取ったデータはデータベース内のデータと不一致、つまり、B が読み取ったデータはダーティデータになります。
非反復読み取り: トランザクション内で同じデータが複数回読み取られますが、この期間中に別のトランザクションがデータを変更してコミットしたため、前後に読み取られたデータは不一致になります。
ファントム読み取り: トランザクションでは、同じデータ (通常は範囲クエリ) が 2 回読み取られますが、別のトランザクションがデータを追加または削除するため、2 回の結果は矛盾します。

トランザクション分離レベルが異なると、同時環境での同時実行性の問題も異なります。

(3) トランザクション実装の原則分離のレベル:

Innodb トランザクションの分離レベルは、MVVC とロック機構によって実装されます:

① MVCC (Multi-Version Concurrency Control、マルチバージョン同時実行制御) は、特定のMySQL の InnoDB ストレージエンジンがトランザクション分離レベルを実装する方法は、コミットされた読み取りと反復可能な読み取りという 2 つの分離レベルを実装することです。読み取り非コミット分離レベルは、MVCC を使用せずに常に最新のデータ行を読み取ります。読み取りシリアル化分離レベルでは、すべての読み取り行をロックする必要がありますが、これは MVCC を使用するだけでは実現できません。

MVCC は、レコードの各行の背後に 2 つの非表示列を保存することによって実装されます。1 つは行のトランザクション ID を保存し、もう 1 つは行のロールバックセグメントポインターを保存します。新しいトランザクションが開始されるたびに、新しいトランザクション ID が自動的に増加します。トランザクションが開始されると、現在のトランザクションの影響を受ける行のトランザクション ID フィールドにトランザクション ID が配置され、ロールバックセグメントのポインタには行レコード上のすべてのバージョンデータが含まれます。これは、リンクされた形式で編成されます。 UNDO ログのロールバックログ内のリスト。つまり、値は実際には UNDO ログ内の行の履歴リンクリストを指していると言われています。

データベースに同時にアクセスする場合、書き込み操作が読み取り操作をブロックすることを回避するために、トランザクション内のデータに対して MVCC マルチバージョン管理が実行されます。また、スナップショット読み取りのファントム読み取りの問題は、バージョンを比較することで解決できますが、現在の MVCC はファントム読み取りを解決できず、一時的なキーロックを通じて解決する必要があります。

② ロックメカニズム:

MySQL ロックメカニズムの基本的な動作原理は次のとおりです: トランザクションはデータベースを変更する前に、対応するロックを取得する必要があります。ロックを取得できるのは、ロックを取得したトランザクションだけです。データを変更する; このトランザクション内操作中、データのこの部分はロックされています。他のトランザクションがデータを変更する必要がある場合、現在のトランザクションがコミットまたはロールバックしてロックを解放するまで待つ必要があります。

#排他的ロックはダーティ読み取りを解決します
共有ロックは反復不可能な読み取りを解決します
プロキーロックはファントム読み取りを解決します

# #4.2.3. 永続性:

永続性は

を達成するために REDO ログに依存しており、

は SQL 実行時に実行された SQL ステートメントを REDO ログファイルに保存しますが、効率を向上させるために、 REDO ログにデータを書き込む前に、まずメモリ内の REDO ログバッファキャッシュにデータが書き込まれます。書き込みプロセスは次のとおりです: データベースにデータを書き込むとき、実行プロセスはまず REDO ログバッファに書き込みます。REDO ログバッファ内の変更されたデータは、ディスク上の REDO ログファイルに定期的に更新されます。このプロセスはこれはディスクフラッシュと呼ばれます (つまり、REDO ログバッファがディスク上の REDO ログファイルにログを書き込みます)。

REDO ログバッファを使用すると、データの読み取りと書き込みの効率が大幅に向上しますが、新たな問題も発生します。MySQL がダウンしても、REDO ログバッファ内で変更されたデータはまだ残っています。ディスクへのフラッシュに失敗するとデータが失われ、トランザクションの耐久性は保証できません。トランザクションの耐久性を確保するために、トランザクションがコミットされると、fsync インターフェイスが呼び出され、REDO ログがフラッシュされます。リフレッシュ頻度は innodb_flush_log_at_trx_commit 変数によって制御されます:

0: 意味ディスクをフラッシュしません;

1: トランザクションが送信されるたびに、バッファプール内のデータがディスクにフラッシュされます;

2: トランザクションが送信されると、バッファプール内のデータはディスクにフラッシュされますバッファプールはディスクファイルに書き込まれます。ディスクファイルに直接入力する代わりに、対応する OS キャッシュキャッシュに移動します。 OS キャッシュ内のデータがディスクファイルに書き込まれるまでに 1 秒かかる場合があります。

4.2.4. 一貫性:

一貫性とは、トランザクションがデータの整合性とビジネスの一貫性を破壊できないことを意味します:

データの整合性: エンティティの整合性、列の整合性 (フィールドのタイプ、サイズ、長さが要件を満たす必要があるなど)、外部キー制約など。
ビジネスの一貫性: たとえば、銀行振込では、取引が成功しても失敗しても、両当事者間の合計金額は変わりません。

#5. データベースのロックメカニズムとは何ですか?

データベース内の複数のトランザクションが同時に同じデータにアクセスする場合、同時操作が制御されていないと、誤ったデータが読み取られて格納され、データベースの一貫性が破壊される可能性があります。 MySQL ロックメカニズムの基本的な動作原理は、トランザクションがデータベースを変更する前に、対応するロックを取得する必要があるということです。ロックを取得したトランザクションのみがデータを変更できます。トランザクション操作中、この部分は、他のトランザクションの場合データを変更する必要がある場合は、現在のトランザクションがコミットまたはロールバックされた後にロックを解放する必要があります。

さまざまな分類方法に従って、ロックのタイプは次のタイプに分類できます:

ロックの粒度に従って: テーブルレベルロック、行レベルのロック、ページレベルのロック;

ロックの種類ごとに分割: 共有 (ロック S ロック)、排他的ロック (X ロック);
ロックの使用方法ごとに分割: 楽観的ロック、悲観的ロック;

5.1、テーブルレベルのロック、行レベルのロック、ページレベルのロック:

テーブルレベルのロック: 最大粒度ロックレベルでは、ロック競合の可能性が最も高く、同時実行性は最も低くなりますが、オーバーヘッドは小さく、ロックは高速で、デッドロックは発生しません。

行レベルロック: すべてのレベルの最小粒度、ロック競合の可能性最小で最高の同時実行性ですが、高いオーバーヘッド、遅いロック、デッドロックが発生します;
ページレベルのロック: ロックの粒度は制限されていますテーブルレベルのロックと行レベルのロックの間のロックであり、ロックは妥協によるものであり、同時実行性は平均的です。オーバーヘッドとロック時間もテーブルロックと行ロックの間で制限され、デッドロックが発生します。

ストレージエンジンが異なればサポートされるロックメカニズムも異なります:

InnoDB ストレージエンジンは、行レベルのロックとテーブルレベルのロックをサポートしています。デフォルトでは、行レベルのロックが使用されますが、行レベルのロックは、データがインデックスを通じてクエリされる場合にのみ使用されます。それ以外の場合は、テーブルレベルのロックが使用されます。

MyISAM および MEMORY ストレージエンジンはテーブルレベルのロックを使用します;
BDB ストレージエンジンはページロックを使用しますが、テーブルレベルのロックもサポートします;

5.2. InnoDB 行ロック:

InnoDB 行ロックには 2 つのタイプがあります:

共有ロック (S ロック、読み取りロック): 複数のトランザクションで共有可能同じデータ行に対する S ロック、ただし読み取りのみ可能で変更はできません。

更新、削除、挿入操作の場合、InnoDB は関連するデータ行に排他ロックを自動的に追加しますが、通常の SELECT ステートメントの場合、InnoDB はロックを追加しません。

5.3. InnoDB テーブルロックとインテンションロック:

InnoDB エンジンでは行ロックとテーブルロックを共存させることができるため、複数粒度のロックメカニズムを実現できます。 , ただし、テーブルロックと行ロックはロック範囲が異なりますが、互いに競合します。テーブルロックを追加する場合は、まずテーブル内のすべてのレコードを調べて、排他ロックがあるかどうかを確認する必要があります。このトラバーサルチェック方法は明らかに非効率的な方法であり、MySQL ではテーブルロックと行ロックの間の競合を検出するためにインテンションロックが導入されています。

意図ロックもテーブルレベルのロックであり、読み取り意図ロック (IS ロック) と書き込み意図ロック (IX ロック) に分けられます。トランザクションがレコードに行ロックを追加する場合、まず対応するインテンションロックをテーブルに追加します。トランザクションが後でテーブルをロックする必要がある場合、最初に目的のロックが存在するかどうかを判断するだけで済みます。ロックが存在する場合は、すぐにテーブルに戻ることができます。テーブルロックは有効にできません。有効にしない場合は、効率を向上させるために待機する必要があります。

5.4. InnoDB 行ロックと一時キーロックの実装:

InnoDB 行ロックは、インデックスのインデックスエントリをロックすることによって実装されます。行ロックは、インデックスを通じてデータを取得する場合にのみ使用できます。それ以外の場合は、テーブルロックが使用されます。

InnoDB では、ファントムリード現象を解決するために、ネクストキーロック (ネクストキー) が導入されています。インデックスによれば、左が開き、右が閉じた区間に分かれています。範囲クエリを実行するときに、インデックスがヒットしてデータを取得できる場合、レコードが配置されている間隔とその次の間隔がロックされます。実際、Next-Key = Record Locks ギャップロック

ギャップロック: データを取得するために正確なクエリではなく範囲クエリを使用する場合、共有ロックまたは排他的ロックをリクエストすると、InnoDB は範囲条件を満たす既存のデータレコードのインデックスエントリ。キー値が条件範囲内にあるが存在しないレコードはギャップ (GAP) と呼ばれます。

レコードロック: 一意のインデックスとレコードの存在の正確なクエリを使用する場合は、レコードロックを使用します

#5.5. ロックメカニズムを使用して同時実行性の問題を解決します:

#X ロックはダーティリードを解決します
S ロックは反復不可能なリードを解決します
プロキーロックはファントムリードを解決します

InnoDB ストレージエンジンのロックメカニズムと MyISAM ストレージエンジンのロックメカニズムの詳細については、次の記事を参照してください: MySQL データベース: ロックメカニズム_Zhang Weipeng のブログ - CSDN ブログ_データベースのロックメカニズム

6. MySQL インデックスの実装原則:

インデックスは本質的に、必要な行数を減らすことでクエリのパフォーマンスを高速化するデータ構造です。クエリによって走査されます。書籍の目次と同様に、データベースがテーブル全体のスキャンを実行するのを防ぎ、コンテンツをより速く見つけられるようにします。 (テーブルには最大 16 個のインデックスを作成できます)

6.1. インデックスのメリットとデメリット:

(1) インデックスのメリット:

クエリによって取得する必要がある行の数を減らし、クエリを高速化し、テーブル全体のスキャンを回避するこれが、インデックスを作成する主な理由でもあります。
インデックスのデータ構造が B ツリーの場合、グループ化と並べ替えを使用すると、クエリでのグループ化と並べ替えの時間を大幅に短縮できます。
一意のインデックスを作成すると、データベーステーブル内のデータの各行の一意性が保証されます。

(2) インデックスのデメリット:

テーブル内のデータを追加、削除、変更するとインデックスも更新する必要があり、メンテナンス時間がかかります。データ量に応じて増加します。
インデックスは物理的なスペースを占有する必要があります。クラスター化インデックスを作成する場合、必要なスペースはさらに大きくなります。

6.2. インデックスの使用シナリオ:

(1) インデックスを作成する列:

WHERE句に頻繁に出現する列にインデックスを作成し、条件の判定を高速化します。
範囲によってアクセスされる列、または group by または order by で使用される列は、インデックスが並べ替えられているため、インデックスを使用して並べ替えクエリ時間を短縮できます。
は、接続の列でよく使用されます。これらの列は主に外部キーであり、接続を高速化できます。
は、列の一意性を強制するための主キー列として使用されます。データの配置構造;

(2) インデックスを作成しない列はどれですか?

区別性の低い列。これらの列には、クエリ結果内の性別などの値が非常に少ないため、結果セット内のデータ行がテーブル内のデータ行の大部分を占めます。つまり、データ行の大部分を表で検索しました。インデックスを増やしても、検索が大幅に高速化されるわけではありません。
クエリにほとんど含まれない列にはインデックスを作成しないでください。これらの列はめったに使用されないため、インデックスを追加すると、実際にはシステムのメンテナンス速度が低下し、必要なスペースが増加します。
インデックスを追加すると、検索パフォーマンスの向上よりも変更コストの増加がはるかに大きくなる場合は、インデックスを作成しないでください。インデックスを追加すると、検索パフォーマンスは向上しますが、変更パフォーマンスは低下します。インデックスを減らすと、変更パフォーマンスは向上しますが、検索パフォーマンスは低下します。
テキスト、イメージ、およびビットデータ型として定義された列にはインデックスを作成しないでください。これらの列のデータ量は非常に大きいか、値が非常に少ないかのいずれかです。

6.3. インデックス分類:

(1) 通常インデックス、一意インデックス、主キーインデックス、全文インデックス、複合インデックス。

通常のインデックス: 制限のない最も基本的なインデックス

一意のインデックス: ただし、インデックス列の値は一意である必要があり、NULL 値が許可されます。複数の NULL 値にする必要があります。複合インデックスの場合、列値の組み合わせは一意である必要があります。

主キーインデックス: null 値を許可しない特別な一意のインデックス。

フルテキストインデックス: フルテキストインデックスは MyISAM テーブルにのみ使用でき、CHAR、VARCHAR、または TEXT タイプのみをサポートします。ファジーマッチング操作などの効率の悪い操作を置き換えるために使用され、以下と組み合わせることができます。全文インデックス作成による複数フィールドの同時複数フィールドの完全なあいまいマッチング。

複合インデックス: 主に mysql の効率を向上させるため、複合インデックスを作成する場合、制限条件として最もよく使用される列を降順で左端に配置する必要があります。

(2) クラスター化インデックスと非クラスター化インデックス:

データの物理的な格納順序とインデックス値の順序に従って分類すると、インデックスは次のようになります。クラスター化インデックスと非クラスター化インデックスには 2 つのタイプがあります:

クラスター化インデックス: テーブル内のデータストレージの物理的な順序は次のとおりです。インデックス値の順序。基本的なテーブルには最大でも 1 つのクラスターしか含めることができません。インデックス、クラスター化インデックス列のデータを更新すると、多くの場合、テーブル内のレコードの物理的な順序が変更され、コストがかかります。したがって、頻繁に更新される列にクラスター化インデックスを確立するのは適切ではありません。

非クラスター化インデックス: テーブルデータの物理的な順序がインデックス値の順序と一致しないインデックス構成では、基本テーブルに複数のインデックスを含めることができます。クラスター化インデックス。

6.4. インデックスデータ構造:

一般的なインデックスデータ構造には、B ツリー、ハッシュインデックスが含まれます。

(1) ハッシュインデックス: MySQL の Memory ストレージエンジンのみが、Memory テーブルのデフォルトのインデックスタイプであるハッシュインデックスをサポートします。ハッシュインデックスはデータをハッシュ値の形式で編成するため、クエリ効率が非常に高く、一度に検索できます。

ハッシュインデックスの欠点:

ハッシュインデックスは等しい値クエリのみを満たすことができますが、範囲クエリや並べ替えには対応できません。データがハッシュアルゴリズムを通過すると、そのサイズ関係が変化する可能性があるためです。

複合インデックスを作成する場合、複合インデックスの一部の列のみをクエリに使用することはできません。ハッシュインデックスは複数の列データを結合してハッシュ値を計算するため、列データごとにハッシュ値を計算しても意味がありません。

ハッシュの衝突が発生した場合、ハッシュインデックスはテーブルデータのスキャンを避けることができません。ハッシュ値を比較するだけでは十分ではないため、実際の値を比較して要件を満たしているかどうかを判断する必要があります。

(2) B ツリーインデックス: B ツリーは、mysql で最も頻繁に使用されるインデックスデータ構造で、Innodb および Myisam ストレージエンジンモードのインデックスタイプです。 B Tree インデックスは、検索中にルートノードからリーフノードまで複数の IO 操作が必要で、クエリ速度はハッシュインデックスほどではありませんが、ソートなどの操作に適しています。

B ツリーインデックスの利点:

ページ内のノードにはコンテンツが保存されず、IO ごとにより多くの行を読み取ることができるため、ディスク I/O 読み取り時間が大幅に削減されます

順次アクセスポインタを持つ B ツリー: B ツリーのインデックスデータはすべてリーフノードに格納され、順次アクセスポインタが追加されます。各リーフノードは隣接するリーフノードへのポインタを持つため、間隔を改善するためにこれが行われます。クエリ効率。

6.5. B ツリーをインデックスとして使用する理由:

インデックス自体も非常に大きいため、すべてを格納することは不可能です。インデックスは、多くの場合、インデックスファイルの形式でディスクに保存されます。この場合、インデックス検索処理時にディスク I/O 消費が発生しますディスク I/O アクセスの消費量は、メモリアクセスに比べて数桁大きいため、メリットを評価するのが最善です。インデックスとしてのデータ構造重要な指標は、検索プロセス中のディスク I/O 操作の数の漸近的な複雑さです。言い換えると、インデックスのデータ構造は、検索プロセス中のディスク I/O アクセスの数を最小限に抑える必要があります。

(1) 局所性原理とプログラムの先読み:

ディスク自体は、機械的な動作の消費と相まって、メインメモリよりもアクセスがはるかに遅いため、効率を向上させるには、ディスク I/O を最小限に抑える必要があります。この目標を達成するために、ディスクは厳密にオンデマンドで読み取るのではなく、毎回事前に読み取ります。たとえ 1 バイトしか必要でない場合でも、ディスクはこの位置から開始して、一定の長さのデータを順番に後方に読み取ります。メモリ。この理論的根拠は、コンピュータサイエンスにおける有名な局所性原理です。つまり、データの一部が使用されると、通常は近くのデータがすぐに使用されます。通常、プログラム実行中に必要なデータは集中しています。

ディスクのシーケンシャル読み取りは非常に効率的であるため (シーク時間なし、スピン時間が非常に短い)、局所性のあるプログラムに先読みを使用して I/O 効率を向上させることができます。先読みの長さは通常、ページの整数倍です。プログラムが読み込むデータがメインメモリにない場合、ページフォールト例外が発生し、システムはディスクに読み取り信号を送り、ディスクはデータの開始位置を見つけます。 1 つまたは複数のページを逆方向に読み取り、メモリにロードしてから異常終了し、プログラムは実行を続けます。

(2) B Tree インデックスのパフォーマンス分析:

前述したように、ディスク I/O 時間は通常、インデックス構造の品質を評価するために使用されます。 B ツリー分析から始めましょう。B ツリーの取得には、一度に最大 h 個のノードにアクセスする必要があります。同時に、データベースはディスク先読みの原理を巧みに使用して、ノードのサイズを 1 に設定します。ページ、つまり、新しいノードが作成されるたびに、直接アプリケーションが作成されます。ページスペース。これにより、ノードがページに物理的に格納され、コンピュータのストレージ割り当てがページに合わせて配置されるため、各ノードをI/O が 1 つだけで完全にロードされます。 B ツリーでの取得には最大 h-1 個の I/O (ルートノードはメモリ内に常駐) が必要で、時間計算量は O(h)=O(logdN) です。一般的な実際のアプリケーションでは、出次数 d は非常に大きな数 (通常は 100 を超える) であるため、h は非常に小さくなります。要約すると、B ツリーをインデックス構造として使用することは非常に効率的です。

赤黒ツリー構造に関しては、時間計算量も O(h) ですが、h は明らかにはるかに深く、論理的に近いノードは物理的に遠い可能性があるため、これを利用することはできません。局所性があるため、IO 効率は B ツリーよりも明らかに劣ります。

また、インデックスデータ構造としてはB Treeの方が適していますが、その理由は内部ノードの出次数dに関係しています。上記の分析から、d が大きいほどインデックスのパフォーマンスが向上し、出次数 d の上限はノード内のキーとデータのサイズに依存することがわかります。 B ツリー内のノードの場合、より大きな出次数を持つことができ、ディスク IO の数は少なくなります。

(3) B-treeインデックスとB-treeインデックスの比較?

B ツリーと B ツリーの構造によれば、次の理由により、ファイルシステムやデータベースシステムでは B ツリーの方が B ツリーよりも優れていることがわかります。

(1) B ツリーはデータベーススキャンに役立ちます。B ツリーはディスク IO パフォーマンスを向上させますが、要素トラバーサルの非効率性の問題は解決しません。一方、B ツリーはリーフノードをトラバースするだけですべての問題を解決できます。キーワード情報をスキャンするため、B ツリーは範囲クエリや並べ替えなどの操作のパフォーマンスが高くなります。

(2) B ツリーのディスク IO コストが低い: B ツリーの内部ノードのデータフィールドにはデータが格納されないため、内部ノードは B ツリーの内部ノードよりも小さくなります。同じ内部ノードのすべてのキーワードが同じディスクブロックに格納されている場合、ディスクブロックはより多くのキーワードを保持できます。検索する必要があるキーワードが一度にメモリに読み込まれるほど、I/O の読み取りおよび書き込みの数が相対的に減少します。

(3) B ツリーのクエリ効率はより安定しています。B ツリーの内部ノードはリーフノード内のキーワードのインデックスにすぎず、データを格納しないためです。したがって、キーワード検索では、ルートノードからリーフノードまでのパスをたどる必要があります。すべてのキーワードクエリのパス長は同じであるため、各データのクエリ効率は同等になります。

(4) MySQL の InnoDB および MyISAM ストレージエンジンでの B Tree インデックスの実装?

MyISAM と InnoDB はどちらも B ツリーインデックスを使用します。MyISAM の主キーインデックスと補助インデックスのデータフィールドは行のアドレスを保存しますが、InnoDB の主キーインデックスは行のアドレスではなく、行を保存します。すべてのデータ (補助インデックスのデータフィールドには主インデックスの値が保存されます)。

インデックスの長さ制限:

Innodb の結合インデックスの場合、各列の長さが 767 バイトを超える場合、767 バイトを超える列はプレフィックスインデックスになります。 Innodb の単一列インデックス、列の長さが 767 を超える場合は、プレフィックスインデックスを取得します (最初の 255 文字を取得します)

MyISAM の結合インデックスの場合、作成されるインデックスの合計長は 1000 バイトを超えることはできません。それ以外の場合はエラーが発生します。報告され、作成は失敗します。MyISAM の単一列インデックスの場合、最大長は 1000 を超えることはできません。それ以外の場合はアラームが発行されますが、作成は成功し、最終的な作成はプレフィックスインデックスになります (最初の 333 文字が取得されます)。 )

7. SQL 最適化、インデックス最適化、テーブル構造最適化:

(1) MySQL の SQL 最適化とインデックス最適化: https: //blog.csdn.net/a745233700 /article/details/84455241

(2) MySQL テーブル構造の最適化: https://blog.csdn.net/a745233700/article/details/84405087

8. データベースパラメーターの最適化:

MySQL は IO 集中型のアプリケーションであり、その主な役割はデータの管理とストレージです。また、メモリからデータベースを読み取る時間はマイクロ秒レベルであるのに対し、通常のハードディスクから IO を読み取る時間はミリ秒レベルであり、両者の差は 3 桁であることがわかっています。したがって、データベースを最適化するには、最初に IO を最適化する必要があり、ディスク IO を可能な限りメモリ IO に変換します。したがって、MySQL データベースのパラメータを最適化するときは、主にディスク IO を削減するパラメータを最適化します。たとえば、query_cache_size を使用してクエリキャッシュのサイズを調整し、innodb_buffer_pool_size を使用してバッファのサイズを調整します。

9. Explain の実行計画:

実行計画は、抽象構文ツリーと、SQL 文を渡した後の SQL 文で得られる関連テーブルの統計情報から作成されるクエリです。プラン、このプランはクエリオプティマイザーによって自動的に分析され、生成されます。動的データサンプリングと統計分析の結果であるため、分析エラー、つまり実行計画が最適ではない可能性があります。 Explain キーワードを使用して、MySQL が SQL クエリステートメントを実行する方法を確認し、select ステートメントのパフォーマンスのボトルネックを分析し、クエリを改善します。 Explain 結果は次のとおりです:

# # 重要なのは、id、type、key、key_len、rows、extra:

(1) id: id 列は、SQL の実行順序の識別子として理解できます。いくつかの選択。

異なる ID 値: ID 値が大きいほど優先度が高く、最初に実行されます。

同じ ID 値: 上から下に順番に実行されます。

id 列は null です。これは、これが結果セットであり、クエリに使用する必要がないことを示します。

(2) select_type: クエリのタイプ。主に通常のクエリ、結合クエリ、サブクエリなどの複雑なクエリを区別するために使用されます。

(3) table: 表現行がどのテーブルにアクセスしているのかを説明します

(4) type: アクセスタイプ、つまり、テーブル内の行を検索する方法を MySQL が決定します。最良から最悪の順: system > const > eq_ref > ref > fulltext > ref_or_null > Index_merge > unique_subquery > Index_subquery > range > Index > ALL、例外 all、その他のタイプすべてのタイプが使用可能Index_merge を除いて、他のタイプは 1 つのインデックスのみを使用できます。一般に、type は ref レベルである必要があり、範囲検索は range レベルに達する必要があります。

system: テーブル (システムテーブルに等しい) 内に一致するデータは 1 つだけあり、これは const 型

const の特殊なケースとみなすことができます。これはインデックスを通じて 1 回検出され、主キーインデックスまたは一意のインデックスの使用を示します。

eq_ref: 主キーまたは一意のインデックスのフィールドが接続に使用され、一致するデータの 1 行のみが返されます

ref: 通常のインデックススキャンでは、複数の一致するデータのクエリ条件行が返される場合があります。

fulltext: フルテキストインデックスの取得、フルテキストインデックスの優先度は非常に高く、フルテキストインデックスと通常のインデックスが同時に存在する場合、mysql はフルテキストインデックスを優先します。コストに関係なくテキストインデックスを作成します。

ref_or_null: ref メソッドと似ていますが、null 値の比較が追加される点が異なります。

index_merge: クエリで 3 つ以上のインデックス、インデックスマージの最適化方法が使用され、最後に交差または和集合が使用されることを示します。共通の and and or 条件では異なるインデックスが使用されます。

unique_subquery: サブクエリが値を繰り返さずに一意の値を返す形式内サブクエリに使用されます;

index_subquery: 補助インデックスまたは定数リストを使用した形式内サブクエリ、サブクエリに使用されます重複した値を返す可能性があり、インデックスを使用してサブクエリの重複を排除できます。

range: インデックス範囲スキャン。>、<、between、in、like などの演算子を使用するクエリでよく使用されます。

index: インデックス全体のテーブルスキャン、インデックスツリーを最初から最後までスキャンします;

all: テーブル全体を走査して一致する行を見つけます (Index と ALL は両方ともテーブル全体を読み取りますが、インデックスはインデックスから読み取られ、ALL はハードディスクから読み取られます)
#NULL: MySQL は最適化プロセス中にステートメントを分解し、実行中にテーブルやインデックスにアクセスする必要さえありません

(5) possible_keys: クエリで使用できるインデックス

(6) key: テーブルへのアクセスを最適化するために実際に使用されるインデックス

(7) key_len: 実際に使用されるクエリを最適化するためのインデックスの長さ、つまりインデックスで使用されるバイト数。この値を使用して、インデックスのどのフィールドが複数列インデックスで実際に使用されているかを計算できます。

(8)ref: どのフィールドまたは定数がキーとともに使用されているかを表示します。

(9)rows: テーブルの統計とインデックスの選択に基づいて、クエリが実行するデータ量を大まかに見積もります。ここでは、正確な値ではなく、行数を読み取る必要があります。

(10)extra: その他の追加情報

インデックスの使用: カバリングインデックスの使用

インデックス条件の使用: クエリされた列にはインデックスが付けられていません。、フィルター条件はインデックスを使用します
usingtemporary: 中間結果を保存するために一時テーブルを使用します。グループ化操作や並べ替え操作でよく使用されます。通常はグループ化列にインデックスがないためですが、次の理由も考えられます。同時に group by と order by がありますが、group by と order by の列は異なります。一般的に言えば、クエリを最適化する必要があることを意味します。クエリ結果を並べ替えるには、インデックスを使用する方法と、ファイルソート (クイックソートに基づいた外部並べ替えであり、パフォーマンスは低い) があります。データ量が大きい場合、これは CPU を大量に使用するプロセスになります。パフォーマンス

実行計画の詳細の説明に興味がある読者は、この記事を参照してください: https://blog.csdn.net/a745233700/記事/詳細/84335453

10. MySQL マスター/スレーブレプリケーション:

10.1. MySQL マスター/スレーブレプリケーションの原理:

スレーブはマスターから binlog バイナリログファイルを取得し、ログファイルを対応する SQL ステートメントに解析して、スレーブサーバー上でマスターサーバーの操作を再実行します。データは確保されています。マスター/スレーブのレプリケーションプロセスは非同期であるため、スレーブとマスターの間でデータに遅延が発生する可能性があり、データの最終的な整合性のみが保証されます。マスターとスレーブ間のレプリケーションプロセス全体は、主に 3 つのスレッドによって完了します。

(1) スレーブ SQL スレッドスレッド: リレーログを読み取り、更新を実行するために作成されます。スレーブ側にあるログに含まれます

(2) スレーブ I/O スレッド: マスターサーバーの Binlog Dump スレッドによって送信されたコンテンツを読み取り、それをスレーブサーバーのリレーログに保存します。スレーブ側:

(3) ビンログダンプスレッドスレッド (IO スレッドとも呼ばれます): bin-log バイナリログの内容をマスター側

にあるスレーブサーバーに送信します。
注: マスターサーバーに 2 つのスレーブサーバーが装備されている場合、マスターサーバーには 2 つの Binlog ダンプスレッドがあり、各スレーブサーバーには 2 つのスレッドがあります。

10.2. マスター/スレーブレプリケーションプロセス:

# (1) マスターサーバーが SQL ステートメントを実行すると、SQL ステートメントは binlog バイナリファイルに記録されます。

(2) スレーブ側の IO スレッドはマスター側に接続し、指定された bin ログログファイルの指定された pos ノード位置 (またはファイルの先頭から) からコピーされる次のログコンテンツを要求します。ログ）。

(3) スレーブ側から IO スレッドリクエストを受信したマスター側は、レプリケーション処理を担当する IO スレッドに通知し、リクエスト情報に従って、指定された pos ノード位置以降の指定された binlog ログを読み取ります。ログ情報はスレーブ側の IO スレッドに返されます。返される情報には、binlog ログに含まれる情報に加えて、マスター側で返される情報の binlog ファイル名と、binlog ログ内の POS ノードの位置も含まれます。

(4) マスター側 IO によって返された情報を受信した後、スレーブ側の IO スレッドは、受信した binlog ログの内容をスレーブ側のリレーログファイルの末尾に順番に書き込みます。マスター側の binlog ファイル名と pos ノードの場所はマスター情報ファイル (このファイルはスレーブ側に保存されます) に記録されるため、マスターは次回の同期中にどの位置からデータ同期を開始するかを指示できます。 synchronization;

(5) スレーブマスター側の SQL スレッドは、リレーログファイルの新しい内容を検出すると、すぐにリレーログファイルの内容を解析し、実際に実行された SQL 文に復元します。マスター側でこれらの SQL ステートメントを実行し、マスターとスレーブの間でデータの一貫性を実現します;

10.3. マスター/スレーブレプリケーションの利点:

(1) 読み取りと書き込みの分離により、スレーブサーバーを動的に追加し、マスターサーバーで書き込みと更新を実行し、スレーブサーバーで読み取り機能を実行することにより、データベースのパフォーマンスが向上します。

(2) データのセキュリティの向上データがスレーブサーバーにコピーされているため、スレーブサーバーはレプリケーションプロセスを終了でき、対応するデータを破壊することなくスレーブサーバーにバックアップできます。マスターサーバー。

(3) マスターサーバーでリアルタイムデータを生成し、スレーブサーバーでデータを分析することで、マスターサーバーのパフォーマンスを向上させます。

10.4. MySQL でサポートされるレプリケーションタイプとその長所と短所:

binlog ログファイルには 2 つの形式があり、1 つはステートメントベース (ステートメントベースのレプリケーション）、もう 1 つは行ベース（行ベースのレプリケーション）です。デフォルトの形式はステートメントベースです。形式を変更したい場合は、サービスの開始時に -binlog-format オプションを使用します。具体的なコマンドは次のとおりです:

mysqld_safe –user=msyql – binlog-format=format&

(1) ステートメントベースのレプリケーション: SQL ステートメントがマスターサーバーで実行され、同じステートメントがスレーブサーバーで実行されます。効率は比較的高いです。正確なコピーが不可能であることが判明すると、行ベースのコピーが自動的に選択されます。

利点:

① SQL ステートメントが記録されるため、必要なストレージ容量が少なくなります。 binlog ログにはデータベース操作を説明するイベントが含まれますが、これらのイベントには、挿入、更新、作成、削除などのデータベースを変更する操作のみが含まれます。逆に、select や desc などの同様の操作は記録されません。

② binlog ログファイルには、データベースを変更するすべてのステートメントが記録されるため、このファイルはデータベースを監査するための基礎として使用できます。

欠点:

① 安全ではありません。データを変更するすべてのステートメントが記録されるわけではありません。非決定的な動作はログに記録されません。たとえば、delete または update ステートメントの場合、limit が使用されているが order by がない場合、これは非決定的ステートメントであるため、記録されません。

② インデックス条件のない update、insert...select ステートメントの場合、より多くのデータをロックする必要があり、データベースのパフォーマンスが低下します。

(2) 行ベースのコピー: スレーブサーバーでコマンドを実行する代わりに、変更された内容をコピーします。mysql5.0 以降でサポートされます。

利点:

① すべての変更がコピーされ、これが最も安全なコピー方法です。

② 更新の場合は、挿入...ステートメントがロックする行が少なくなるまで待機するを選択します。

欠点:

① binlog ログファイルを通じてどのステートメントが実行されたかを確認することはできず、スレーブサーバーで何が受信されたかを知る方法もありません。どのデータが変更されたかを確認することしかできません。

② データを記録するため、binlog ログファイルが占有する記憶領域はステートメントベースよりも大きくなります。

③ 大量のデータを扱う操作には時間がかかります。

(3) 混合型レプリケーション: デフォルトではステートメントベースのレプリケーションが採用されますが、ステートメントベースのレプリケーションが正確ではないことが判明した場合は、行ベースのレプリケーションが採用されます。

マスター/スレーブレプリケーションの詳細については、次の記事を参照してください: https://blog.csdn.net/a745233700/article/details/85256818

11。書き込み分離:

11.1. 実装原理:

読み取りと書き込みの分離により、データベースの書き込み操作がデータベースの効率に影響を与えるという問題が解決されます。書き込みよりも読み取りの方がはるかに重要なシナリオに適しています。読み取りと書き込みの分離を実現するための基礎は、マスター/スレーブレプリケーションです。マスターデータベースは、マスター/スレーブレプリケーションを使用して、自身のデータ変更をスレーブデータベースクラスターに同期します。その後、マスターデータベースは、書き込み操作の処理を担当します (もちろん、読み取り操作も実行します)、スレーブデータベースは読み取り操作の処理を担当しますが、書き込み操作は実行できません。また、負荷の状況に応じて、複数のスレーブデータベースを展開して、読み取り操作の速度を向上させ、メインデータベースへの負荷を軽減し、システム全体のパフォーマンスを向上させることができます。

11.2 読み取りと書き込みの分離によってパフォーマンスが向上する理由:

(1) 物理サーバーと負荷分散を追加します。
(2) マスターとスレーブは自身の書き込みと読み取りのみを担当するため、X ロックと S ロックの競合が大幅に軽減されます;
(3) スレーブライブラリは MyISAM で構成できます。システムオーバーヘッド;
(4) マスター/スレーブレプリケーションのもう 1 つの主な機能は、冗長性を高め、可用性を向上させることです。1 つのデータベースサーバーがダウンした場合でも、迅速に復元できます。別のスレーブデータベースを調整して可能な限り対応します。

11.3. Mysql 読み書き分割実装:

(1) プログラムコードに基づく内部実装:コードでは、ルーティング分類は選択と挿入に基づいて実行されます。利点は、プログラムがコードに実装され、追加のハードウェア費用が必要ないため、パフォーマンスが向上することですが、欠点は、開発者が実装する必要があり、運用保守担当者が開始する手段がないことです。

(2) 中間プロキシ層に基づく実装: プロキシは通常、アプリケーションサーバーとデータベースサーバーの間にあり、アプリケーションサーバーからリクエストを受信した後、プロキシデータベースサーバーはそれをバックエンドに転送します。エージェント層は以下の代表例が挙げられます。

#12. サブデータベースとテーブル: 垂直サブテーブル、垂直サブデータベース、水平サブテーブル、水平サブデータベース

読み取り/書き込みの分離は、データベースの読み取りおよび書き込み操作の負荷を解決しますが、データベースのストレージの負荷を分散しません。サブデータベースとサブテーブルを使用すると、データベースのストレージのボトルネックを解決し、データベースのクエリ効率を向上させることができます。データベース。

12.1. 垂直分割:

(1) テーブルの垂直分割: テーブルをフィールドごとに複数のテーブルに分割し、各テーブルにフィールドの一部を格納します。それフィールド。一般に、よく使用されるフィールドは 1 つのテーブルに配置され、あまり使用されないフィールドは別のテーブルに配置されます。

利点:

(1) IO 競合を回避し、テーブルがロックされる可能性を減らします。大きなフィールドは効率が低いため、まず、大きなフィールドはより多くのスペースを占有し、単一ページに格納される行数が減少するため、IO 操作が増加します。次に、データ量が多く、読み取り時間が長くなります。。

(2) 人気のあるデータのクエリ効率を向上させることができます。

(2) 垂直データベース分割: さまざまなビジネスモジュールに応じてテーブルをさまざまなデータベースに分割します。これは、ビジネスとビジネスロジック間の結合が非常に低い場合に適しています。明確なシステムです。

利点:

ビジネス内の結合を減らし、異なるビジネスの階層管理を容易にする

IO およびデータベース接続の数を増やし、問題を解決できる単一マシンの問題ハードウェアストレージリソースのボトルネック問題

# (3) 垂直分割 (サブデータベース、サブテーブル) のデメリット:

主キーが冗長化され、冗長なカラムを管理する必要がある
##トランザクションの処理が複雑になる
##単一テーブルのデータ量が多すぎるという問題が依然としてある

#12.2、水平分割:

(1) テーブルの水平分割: 同一データベース内で、同一テーブルのデータを一定の規則に従って複数のテーブルに分割します。

利点:

単一テーブル内の過剰なデータ量の問題を解決します

IO競合を回避し、テーブルロックの可能性を低減します

(2) データベースの水平分割: 同じテーブルのデータを一定の規則に従って異なるデータベースに分割し、異なるデータベースを異なるサーバーに配置することができます。

利点:

単一データベース内の大量のデータによるボトルネック問題を解決します。

IO 競合、ロック競合、およびデータベースに関する問題を削減します。特定のデータベースは他のデータベースに影響を与えず、システムの安定性と可用性が向上します

# (3) 水平分割の欠点 (サブテーブル、サブデータベース):

シャーディングされたトランザクションの一貫性を解決するのは困難です
クロスノード JOIN のパフォーマンスが低く、ロジックが複雑になります
データの拡張が難しく、保守も困難です

12.3. サブデータベースおよびサブテーブルに存在する問題の解決策:
(1) トランザクションの問題:

① 解決策 1: 分散トランザクションを使用する :

利点: データベースによって管理され、シンプルかつ効果的です。
欠点: 特にシャードの数が増えると、パフォーマンスコストが高くなります。

② オプション 2: プログラムとデータベースが共同して実装を制御する原理は、複数のデータベースにわたる分散トランザクションを、単一のデータベース上にのみ存在する複数の小さなトランザクションに分解し、アプリケーションに渡すことです。個々の小さなトランザクションを全体的に制御するプログラム。

利点: パフォーマンス上の利点;
欠点: アプリケーションではトランザクションの柔軟な制御が必要です。 Spring のトランザクション管理を使用する場合、変更を行う際に特定の困難に直面することになります。

(2) クロスノード結合の問題:
この問題を解決する一般的な方法は、2 回クエリを実行することです。最初のクエリ結果は、関連データの ID を見つけることに重点を置き、これらの ID に基づいて 2 番目のリクエストを開始して、関連データを取得します。

(3) クロスノード数、順序付け、グループ化、ページング、および集計関数の問題:
このような問題では、データ収集全体に基づいて計算する必要があるためです。ほとんどのエージェントはマージ作業を自動的に処理しません。解決策はクロスノード結合の問題を解決するのと似ています。結果は各ノードで取得され、アプリケーション側でマージされます。結合とは異なり、各ノードのクエリを並列実行できるため、単一の大きなテーブルよりもはるかに高速です。ただし、結果セットが大きい場合、アプリケーションメモリの消費が問題になります。

12.4. データベースをテーブルに分割した後、ID キーをどのように扱うか? # データベースをテーブルに分割した場合、各テーブルの ID は 1 から始めることができないため、グローバル ID が必要になります。グローバル ID を設定するには、主に以下の方法があります。

##(1) UUID:

利点: ローカルで生成された ID、リモート呼び出しは不要、グローバルに一意で繰り返されません。

欠点: 多くのスペースを占有するため、インデックスとしては適していません。

(2) データベース自動インクリメント ID: データベースをテーブルに分割した後、データベース自動インクリメント ID を使用します主キーを生成するための専用ライブラリが必要ですサービスがリクエストを受信するたびに、最初にこれを要求します。意味のないデータをデータベースに挿入し、データベースによって自動的にインクリメントされる ID を取得し、この ID を使用して別のデータベースとテーブルにデータを書き込みます。

利点: シンプルで実装が簡単です。

欠点: 同時実行性が高いとボトルネックが発生します。

(3) Redis は ID:

を生成します。利点: データベースに依存せず、パフォーマンスが向上します。

欠点: 新しいコンポーネントの導入によりシステムの複雑さが増加します

(4) Twitter のスノーフレークアルゴリズム: これは 64 ビット長の ID であり、そのうちの 1 ビットは使用されず、41 ビットがミリ秒として使用され、10 ビットが作業マシン ID として使用され、12 ビットがシリアル番号として使用されます。

1bit: バイナリの最初のビットが 1 の場合、それは負の数ですが、ID を負にすることはできないため、最初のビットはデフォルトで 0 になります。タイムスタンプ、単位はミリ秒です。

10bit: 稼働中のマシン ID を記録します。そのうち 5 ビットはコンピュータ室 ID を表し、5 ビットはマシン ID を表します。
12 ビット: 同じミリ秒内に生成された異なる ID を記録するために使用されます。

(5) Meituan の Leaf 分散 ID 生成システム、Meituan-Dianping の分散 ID 生成システム:

13、パーティション:

パーティショニングとは、テーブルデータを特定のルールに従って異なる領域に格納すること、つまり、テーブルデータファイルを複数の小さなブロックに分割することです。データをクエリする場合、データがどの領域に格納されているかがわかっていれば問題ありません。を実行し、対応する領域で直接クエリを実行します。すべてのテーブルデータをクエリする必要がないため、クエリのパフォーマンスが向上します。同時に、テーブルデータが特に大きく 1 つのディスクに収まらない場合は、ストレージのボトルネック問題を解決するためにデータを別のディスクに割り当てることもできます。複数のディスクを使用すると、ディスクの IO 効率が向上し、データベースのパフォーマンス。パーティションテーブルを使用する場合は、パーティションフィールドを主キーまたは一意のインデックスに配置する必要があり、各テーブルのパーティションの最大数は 1024 であることに注意する必要があります。一般的なパーティションタイプは、レンジパーティション、リストパーティションです。、ハッシュパーティション、キーパーティション、

(1) 範囲分割: 連続間隔範囲に従って分割します。

(2) リスト分割: 指定されたセット内の値に従って分割を選択します。

(3) ハッシュ分割: テーブルに挿入する行の列値を使用して計算されるユーザー定義式の戻り値に基づいて分割します。この関数には、負でない整数値を生成する MySQL の有効な式を含めることができます。

(4) キーパーティショニング: HASH パーティショニングと似ていますが、キーパーティショニングが 1 つ以上の列の計算のみをサポートし、キーパーティショニングのハッシュ関数が MySQL サーバーによって提供される点が異なります。

(1) テーブルパーティショニングの利点:

① スケーラビリティ:

パーティショニングはオンになります。異なるディスクを使用すると、単一ディスクの容量ボトルネックの問題を解決でき、より多くのデータを保存できるほか、単一ディスクの IO ボトルネック問題も解決できます。

② データベースのパフォーマンスの向上:

データベースの取得中に走査する必要があるデータの量を削減します。クエリを実行する場合、クエリを実行する必要があるのは、データベースに対応するパーティションのみです。データ。

Innodb の単一インデックスの相互排他的アクセス制限を回避します

sum() や count() などの集計関数については、各パーティションで並列処理を実行でき、最終的にのみ実行できます。得られた結果

③ データの運用・保守管理が便利:

保存の意味を失ったデータについては、対応するパーティションを削除することで、迅速な削除を実現できます。たとえば、特定の時点で履歴データを削除するには、truncate を直接実行するか、パーティション全体を直接削除する方が、削除するより効率的です。

シナリオによっては、単一のパーティションテーブルのバックアップの方が効率的です。効率的に復元できます。

#14. 通常、主キーとして自動インクリメント ID または UUID を使用しますか?

(1) 自動インクリメント ID:

自動インクリメント ID を使用する利点:

フィールド長が大幅に小さくなりますUUIDよりも。

データベースには自動的に番号が付けられ、順番に保存されるため、検索に便利です。

主キーの重複を心配する必要はありません。

self- を使用するデメリットID の増加:

これは自己増加するため、特定のビジネスシナリオでは、他の人がビジネスの量を確認するのが簡単です。
#データの移行やテーブルのマージが発生すると、非常に面倒になります
同時実行性が高いシナリオでは、自己増加するロックの競合によってデータベースのスループットが低下します

## (2) UUID: ユニバーサル固有の識別コード UUID は、現在時刻、カウンタ、ハードウェア ID およびその他のデータに基づいて計算および生成されます。

UUID を使用する利点:

一意の識別、重複の問題を考慮する必要がなく、データの分割とマージ時にグローバルな一意性を実現できます。

は、データベースのスループットを向上させるためにアプリケーション層で生成できます。

業務量の漏洩を心配する必要はありません。

UUID を使用するデメリット:

UUID はランダムに生成されるため、ランダム IO が発生し、挿入速度に影響を及ぼし、ハードディスクの使用率が低下します。

UUID は多くのスペースを占有し、より多くのインデックスが作成されるほど影響は大きくなります。

UUID 間のサイズの比較は、自己増加する ID よりもはるかに遅く、クエリの速度に影響します。

通常の状況では、
MySQL は自動インクリメント ID を使用することをお勧めします。これは、MySQL の InnoDB ストレージエンジンでは、主キーインデックスがクラスター化インデックスであり、B のリーフノードであるためです。 -主キーインデックスのツリー主キーの値とデータを順番に格納主キーインデックスが自動インクリメントIDの場合は順番に並べるだけでよい UUIDの場合はランダムにIDを生成これにより、データ挿入中に大量のデータ移動が発生し、大量のメモリの断片化により挿入パフォーマンスが低下します。
15. ビュー:
ビューは 1 つ以上のテーブル (またはビュー) から派生したテーブルであり、その内容は次のように定義されます。クエリ。ビューは仮想テーブルです。データベースにはビューの定義のみが保存され、ビューに対応するデータは保存されません。ビューのデータを操作するとき、システムは、ビューの定義に従って、対応する基本テーブルを操作します。景色。
ビューは基本テーブルの上に構築されたテーブルであると言え、その構造と内容は基本テーブルから派生し、基本テーブルの存在に基づいて存在します。ビューは 1 つの基本テーブルまたは複数の基本テーブルに対応できます。ビューは、基本的なテーブルと論理的な意味で確立された新しい関係を抽象化したものです。
(1) ビューの利点:

操作を簡素化し、頻繁に使用されるデータをビューとして定義します

セキュリティ、ユーザーはデータのクエリと変更のみ可能

#論理的な独立性、実際のテーブルの構造の影響を保護する

(2) ビューの欠点:

悪いパフォーマンスを向上させるために、データベースはビューのクエリを基本テーブルのクエリに変換する必要があります。ビューが複雑な複数テーブルクエリによって定義されている場合、ビューの単純なクエリであってもデータベースによって処理される必要があります。複雑な組み合わせなので時間がかかります。

16. ストアドプロシージャプロシージャ:
SQL ステートメントは最初にコンパイルしてから実行する必要があり、
ストアドプロシージャは特定の操作を完了するためのプロシージャのセット関数 SQL ステートメントのセットはコンパイルされてデータベースに保存され、ユーザーはストアドプロシージャの名前を指定し、パラメータを指定することによってそれを呼び出します。
データベースを操作するための複雑なロジックもプログラムを使用して実装できるのに、なぜストアドプロシージャが必要なのでしょうか?主な理由は、プログラムを使用して API を呼び出す効率が比較的遅いためです。アプリケーションは、実行のためにエンジンを介して MYSQL エンジンに SQL ステートメントを渡す必要があります。MySQL に直接処理を任せたほうがよいでしょう。それは最も熟練しており、完了することができます。

ストアドプロシージャの利点:

(1) 標準コンポーネントプログラミング: ストアドプロシージャを作成した後、書き直すことなくプログラム内で複数回呼び出すことができます。プロシージャストアドプロシージャの SQL ステートメント。また、DBA は、アプリケーションのソースコードに影響を与えることなく、いつでもストアドプロシージャを変更できます。

(2) 実行速度の高速化: 操作に大量の Transaction-SQL コードが含まれる場合、または複数回実行される場合、ストアドプロシージャはバッチ処理よりもはるかに高速に実行されます。ストアドプロシージャはプリコンパイルされているため、ストアドプロシージャが初めてクエリされると、オプティマイザがストアドプロシージャを分析して最適化し、最終的にシステムテーブルに保存される実行プランを提供します。バッチ Transaction-SQL ステートメントは、実行するたびにコンパイルして最適化する必要があり、速度は比較的遅くなります。

(3) SQL 言語の機能と柔軟性の強化: ストアドプロシージャは制御文で記述でき、柔軟性が高く、複雑な判断や複雑な操作を完了できます。

(4) ネットワークトラフィックの削減: 同じデータベースオブジェクトに対する操作 (クエリ、変更など) の場合、この操作に含まれる Transaction-SQL ステートメントがストアドプロシージャに編成されている場合、操作の実行時にクライアントコンピュータ上でストアドプロシージャが呼び出されるとき、呼び出しステートメントのみがネットワーク経由で送信されるため、ネットワークトラフィックが大幅に削減され、ネットワーク負荷が軽減されます。

(5) セキュリティ機構として活用する: 特定のストアドプロセスの実行権限を制限することで、該当するデータへのアクセス権限を制限し、不正なユーザーによるデータへのアクセスを回避できます。データのセキュリティを確保します。

17. トリガー:

トリガーはテーブルに関連するデータベースオブジェクトです。トリガーがテーブルに表示されると、トリガーが見つかります。イベントが指定され、定義された条件が満たされると、トリガーで定義された一連のステートメントが実行されます。トリガーの特性をデータベース側に適用して、データの整合性を確保できます。トリガーは特別なストアドプロシージャです。違いは、ストアドプロシージャは call を使用して呼び出す必要があるのに対し、トリガーは呼び出しや手動呼び出しを必要としないことです。特定のテーブルでデータを挿入、削除、または変更するときに実行をトリガーします。データベース自体の標準機能よりも高度かつ複雑なデータ制御機能を備えています。

18. カーソル:

カーソルは水泳の識別子であり、ポインターとして機能します。データベースにクエリを実行し、結果セット内のすべてのレコードを返します。ただし、一度に抽出できるレコードは 1 つだけです。つまり、対応する操作を実行するために一度に指定および取得できるデータ行は 1 行だけです。カーソルを使用しないときは、誰かが一度にすべてを与えているのと同じですが、カーソルを使用した後は、誰かがそれを 1 つずつあなたに与えているのと同じです。このとき、これを見てください。それが良いか悪いかは、自分で決めるのです。

推奨学習: mysql ビデオチュートリアル