MYSQL の基礎となる原則を理解するのに役立つ記事

mysql ビデオ チュートリアル コラムでは、基礎となる原則を紹介します。

#MYSQL

SQL 実行プロセス

最初にクエリ SQL を見てみましょう

(各ストレージ エンジン Mysql ストレージ エンジンの公式ドキュメントの説明は次のとおりです)

更新 SQL の実行

更新の実行は # から始まります。 # #Client=> ··· => 実行エンジン

プロセスは同じですが、まずこのデータを見つけてから更新する必要があります。 UPDATE プロセスを理解するために、まず Innodb のアーキテクチャ モデルを見てみましょう。 Innodb アーキテクチャ

最後の MYSQL 公式 InnoDB アーキテクチャ図:

内部モジュール

コネクタ (JDBC、ODBC、など) =>

[MYSQL Internal

[Connection Pool] (授权、线程复用、连接限制、内存检测等)
=>

[SQL Interface] (DML、DDL、Views等) [Parser] (Query Translation、Object privilege) [Optimizer] (Access Paths、 统计分析) [Caches & Buffers]
=>

[Pluggable Storage Engines]复制代码

]

=> [ファイル]

メモリ構造

データをクエリするときは、まず現在クエリしている

page

を取得し、バッファ プール に移動して、現在のページ は バッファ プール にあります。ある場合は、直接入手してください。 そして、

更新操作

の場合、Buffer の値が直接変更されます。現時点では、バッファ プール内のデータは実際にディスクに保存されているデータと矛盾しており、ダーティ ページと呼ばれます。時々、Innodb ストレージ エンジンは ダーティ ページ データ をディスクにフラッシュします。一般に、データを更新するときは、変更のためにデータを バッファ に読み取り、それをディスクに書き戻して disk IO 操作を完了する必要があります。 update

の動作パフォーマンスを向上させるため、Mysqlがメモリ内で最適化されており、

アーキテクチャのバッファプールに領域があることがわかります。ダイアグラムの名前:バッファを変更。名前が示すように、 は変更されたデータ用のバッファを作成するために使用されます。一意のインデックス を持たずにデータを更新する場合、変更されたデータは change バッファ に直接配置されます。その後、merge 操作を通じて更新が完了するため、その ディスク ドロップに対する IO 操作が削減されます。 上で述べたことには条件があります: 一意のインデックスのないデータが更新されるとき

、なぜ

一意のインデックスのないデータが更新されるとき
• 直接配置できます changebuffer と入力してみてはいかがでしょうか? 一意制約のあるフィールドの場合、データを更新すると、更新されたデータが既存のデータと重複する可能性があるため、ディスクからすべてのデータを読み取って比較することしかできません一意性を判断するため。 したがって、データの書き込みが 多くなり、読み取りが少なくなる 場合、
innodb_change_buffer_max_size
• の割合を増やすことで、 の changebuffer を調整できます。バッファ プール、デフォルトは 25 (つまり 25%)です。マージがどのように機能するかという質問がまた出てきます。
状況は 4 つあります。

他のアクセスがある場合、現在のページのデータはディスクにマージされます

バックグラウンド スレッドのスケジュールされたマージ

1. システムが正常にシャットダウンする前に、一度マージします
2. redo ログ
3. いっぱいになったら、ディスクにマージします
4. 1. redo ログとは
redo について話すとき、innodb について話さなければなりません

クラッシュ セーフ

、WAL メソッドを使用します (ログの書き込み、書き込み前にログを記録します)

この方法では、データベースがクラッシュしたときに、redo から直接ログにデータを復元しますデータの正確性を確保するため、

REDO ログはデフォルトで 2 つのファイルに保存されますib_logfile0

ib_logfile1

、両方のファイルは固定サイズです。なぜ固定サイズが必要なのでしょうか? これは、redo ログ の

シーケンシャル読み取り

機能が原因で発生します。この機能は、継続的なストレージ スペースである必要があります 2. ランダムな読み取りと書き込み、およびシーケンシャルな読み取りと書き込み画像を見てください

通常、データはディスク上に分散しています:

メカニカル ハードディスクの読み取りと書き込みのシーケンスは次のとおりです。

トラックを見つけます

対応するセクターへの回転を待ちます

1. 読み取りと書き込みの開始
2. ソリッド ステートの読み取りと書き込み:
3. 1. フラッシュ メモリ チップを直接見つけます (これが、ソリッドステートが機械式よりも高速である理由です)
   2. 読み取りと書き込みを開始します

   実際には、機械式かどうかに関係なく、ソリッドステートの場合、ストアに行くとき、それらはすべて ファイル システム を通じてディスクを処理します。それらを処理する方法は 2 つあります。 ランダム読み取りおよび書き込み および シーケンシャル読み取りおよび書き込み

   1. ランダム読み取りおよび書き込みで保存されたデータは、異なる ブロックに分散されます (デフォルトは 1 ブロック = 8 セクター = 4K)
   2. シーケンシャル ストレージに関しては、名前が示すように、データが 一連の連続したブロック に分散されるため、読み取り速度が大幅に向上します

   3. アーキテクチャ図に戻ります

   

   バッファ プール の ログ バッファを参照してください。 REDO ログの前に存在していたバッファを書き込むために使用されます。

   ここでは、REDO ログの具体的な実行戦略が 3 つあります。

   0. Log Buffer を書き込む必要はありません。 1 秒ごとに書き込むだけで済みます。REDO はディスク データを 1 回だけログに記録するため、高いパフォーマンスを発揮しますが、1 秒以内にデータの一貫性の問題が発生します。 強いリアルタイム パフォーマンス、弱い一貫性に適用されます。たとえば、コメント領域のコメント
   1. 書き込み ログ バッファ と同時にディスクに書き込みます。パフォーマンスは最悪ですが、一貫性は最高です。 弱いリアルタイム、強い一貫性に適用可能(支払いシナリオ
   2. writeログバッファ、書き込みなど) os バッファ に転送します (毎秒 fsync を呼び出してデータをディスクにフラッシュします)。優れたパフォーマンスと高いセキュリティを備えています。これは、中程度のリアルタイム 中程度の一貫性 (注文タイプなど)です。

   実行ポリシーは、innodb_flush_log_at_trx_commit を通じて設定できます。デフォルトは 1

   メモリ構造の概要

   

   ##バッファ プールは読み取りを高速化するために使用されます
   1. 変更バッファは、非一意のインデックスを使用せずに書き込みを高速化するために使用されます
   2. ログ バッファは、REDO ログの書き込みを高速化するために使用されます
   4. アダプティブ ハッシュ インデックス主にクエリを高速化するために使用されますページ。クエリを実行するとき、Innodb はインデックス検索メカニズムを監視することによって、現在のクエリが ハッシュ インデックス を通過できるかどうかを判断します。たとえば、LIKE 演算子と % ワイルドカード文字は使用できません。
   #ハードディスク構造

   1. システム テーブルスペース

   は、

   ibdata1

   というファイルに保存され、次の内容が含まれます。 #InnoDB データ ディクショナリは、テーブル構造情報、インデックスなどのメタデータを保存します。

   Doublewrite Buffer
   バッファ プール
   1. がデータ ページを書き込むとき、データ ページはファイルに直接書き込まれません。代わりに、最初にこの領域に書き込まれます。この利点は、オペレーティング システム、ファイル システム、または mysql がハングしたときに、この
   Buffer
   2. からデータを直接取得できることです。 変更バッファ Mysql がシャットダウンすると、変更はディスクに保存されますUndo ログはトランザクション変更操作を記録します
   3. 2. テーブルごとのファイル テーブルスペース
   4. 各テーブルには、データとインデックスを保存するファイル
   .ibd

   があります。

   file-per-table tablespace

   を使用すると、
   ALTER TABLE
   1. および TRUNCATE TABLE のパフォーマンスを大幅に向上できます。たとえば、ALTER TABLE では、共有テーブル スペースに存在するテーブルと比較して、テーブルを変更するときに テーブル コピー操作 が実行され、テーブル スペースの数が増加する可能性があります。占有ディスク容量。このような操作では、テーブル内のデータとインデックスと同じくらいの追加スペースが必要になる場合があります。この領域は、file-per-table tablespace のようにオペレーティング システムに解放されません。 File-per-table テーブルスペース データ ファイルは、I/O の最適化、スペース管理、またはバックアップのために別のストレージ デバイス上に作成できます。これは、テーブルのデータと構造を異なるデータベース間で簡単に移行できることを意味します。 データ破損が発生した場合、バックアップやバイナリ ログが利用できない場合、または MySQL サーバー インスタンスを再起動できない場合、テーブルを単一のテーブルスペース データ ファイルに保存すると時間が節約され、リカバリが成功する可能性が高まります。
   2. もちろん、利点と欠点があります:
   3. 1. ストレージスペースの使用率が低いため、断片化が発生し、テーブルを削除するときにパフォーマンスに影響します (断片化を自分で管理しない場合)
      2. 各テーブルが分割されているためそれぞれのテーブル ファイルにデータを書き込むと、オペレーティング システムは fsync ファイルへのデータの 1 回限りのフラッシュを実行できません。
      3. mysqld は各テーブル ファイルの ファイル ハンドル を維持し続けます。ファイルへの継続的なアクセスを提供します

      3. 一般テーブルスペース

      1. 一般テーブルスペースは共有テーブルスペースとも呼ばれ、 データを保存できます。複数のテーブルから
      2. 同じ数のテーブルを保存する場合、消費されるストレージは テーブルあたりのテーブル領域
      3. より小さくなります。 MySQL 通常のテーブルスペースにテーブル パーティションを配置するためのサポートは 5.7.24 で非推奨となり、将来の MySQL バージョンではサポートされなくなります。

      4. 一時テーブルスペース

      は、ibtmp1 というファイルに保存されます。通常の状況では、Mysql は起動時に一時テーブルスペースを作成し、停止時に一時テーブルスペースを削除します。そしてそれは自動的に拡張することができます。

      5. 元に戻すテーブルスペース

      1. 変更操作の 原子性 を提供します。つまり、変更の途中で例外が発生した場合、それをロールバックできます。元に戻すログを介して。
      2. トランザクションとこの変更操作の開始前の元のデータが保存されます。
      3. アーキテクチャに示すように、UNDO ログはロールバック セグメント (ロールバック セグメント) に存在し、ロールバック セグメントは システム表スペース「UNDO 表スペース」一時表スペース に存在します。ダイアグラム。

      Redo Log

      前述のとおり、

      要約すると、更新 SQL ステートメントを実行すると何が起こるかについてです。

      1. お問い合わせください。変更されるデータの一部 (ここでは origin と呼びます) は、実行プログラム
      2. に返されます。実行プログラムでは、データの変更は modification
      と呼ばれます。
      3. 変更をメモリにフラッシュし、#バッファプールのバッファを変更します
      エンジン層: アンドゥログを記録します(トランザクションのアトミック性を達成するため)
      4. エンジン レイヤー: REDO ログを記録 (クラッシュ リカバリに使用)
      5. サービス レイヤー: bin ログを記録 (DDL を記録)
      6. 更新成功結果を返す
      7. データ待機中 ワーカー スレッドによってディスクにフラッシュされました

      

      Bin log
      は

      Undo、Redo## と言いました# ちなみに Bin ログ .

      このログは
      innodb
      1. エンジンとはほとんど関係がありません。前述の 2 つのログはどちらも innodb エンジンです。層。そして、Bin log は サービス層 にあります。そのため、さまざまなエンジンで使用できます。その主な機能は何ですか?まず、
      Bin log
      2. は、各 DDL DML ステートメントをイベントの形式で記録するもので、論理的な意味でのログです。
      マスター/スレーブ レプリケーション
      3. を実現できます。 サーバーから main サーバーの bin log ログを取得します。を選択して実行します。 データリカバリ
      を行い、一定期間のログを取得し、再度実行してください。
      4. SQL ステートメントに従ってグローバル プレビューを完了したら、SQL を振り返ってリッチにしてみましょう。
      index
      Try

      ## を追加しましょう # ゴージャスな分割線#インデックス記事

      ##InnoDB のインデックス

      が何であるかを完全に理解したい場合は、その

      ファイル ストレージ レベルを理解する必要があります

      ---

      Innodb はファイル ストレージを 4 つのレベルに分割します

      ページ、エクステント、セグメント、テーブルスペースそれらの関係は次のとおりです:

      デフォルトの

      エクステント

      サイズは

      1M、つまり

      64
      • 16KB Page です。通常、ファイル システムで参照されるページ サイズは 4KB で、512Byte の 8 セクターが含まれています。 ストレージ構造 B ツリー バリアント B ツリー
      では、なぜ主キーを順序付けする必要があるのか​​を尋ねられることがあります。順序付きフィールドでインデックスを作成し、データを挿入します。 保存する際、innodb は
      page

      に 1 つずつ順番に保存し、1 ページがいっぱいになると新しいページに適用して保存を続けます。

      ただし、フィールドが順序付けされていない場合、保存場所は別のページになります。データが full

      である

      ページ に保存されると、 ページ分割

      が発生し、

      フラグメント が形成されます。

      いくつかの異なるインデックス構成形式

      1. 上の B ツリー図に示すように、クラスター化インデックスでは、 行のデータが子ノードに保存されます。 , そして、インデックス の 並び順 とインデックスのキー値の順序 が一致していれば、クラスター化インデックス になります。主キー インデックスはクラスター化インデックスです。主キー インデックスを除き、その他はすべて 補助インデックス
      補助インデックスです。
      2. 補助インデックスを作成すると、そのインデックスはリーフノード 自身の値 と 主キーインデックスの値 のみが保存されます。これは、補助インデックスを通じてすべてのデータをクエリする場合、まず 補助インデックスで 主キー値を見つけてから、主キー インデックス#に移動することを意味します。 ## 内部で、関連する data が見つかりました。このプロセスは テーブルに戻る
      rowid
      3. と呼ばれます。 主キー インデックス がない場合はどうすればよいですか? 主キーはありませんが、一意のキーがあり、それが null ではない場合、このキーに基づいて
         クラスター化インデックス
         1. が作成されます。 上記のいずれも持っていない場合でも、心配する必要はありません。innodb は
         rowid
         2. と呼ばれるものを維持し、この id# に基づいて クラスター化インデックスを作成します。
         ##インデックスの仕組み
      インデックスとは何か、その構造が何であるかを理解した後。 インデックスを使用する必要がある場合を見てみましょう。これらを理解すると、正しく効率的なインデックスを作成するのに役立ちます。
      分散が低い場合、つまり、インデックスは構築されません。データに大きな違いがない場合は、インデックスを作成する必要はありません。 (インデックスの作成により、クエリを実行すると innodb 内のほとんどのデータが同じになります。インデックスとテーブル全体に違いがない場合は、直接
      full table query
      1. を実行します)。たとえば、性別フィールドです。これにより、多くの保管スペースが無駄になります。
         ジョイント フィールド インデックス (
      idx(name, class_name) など)

      2. select * from stu where class_name の実行時= xx および name = lzw
         をクエリする場合、オプティマイザは SQL を
         1. name = lzw および class_name = xx# に最適化するため、インデックス idx を使用することもできます。 #select ··· where name = lzw
         が必要な場合は、別の
         2. name インデックスを作成する必要はなく、idx に直接移動します。インデックスカバーインデックス
         。今回クエリする
         3. すべてのデータ がインデックスに含まれている場合、クエリのために テーブルに戻る必要はありません。例: select class_name from stu where name =lzw
         インデックス条件プッシュダウン (index_condition_pushdown)
      3. このようなものがあります。記事 SQL、

         select * from stu where name = lzw and class_name like '%xx'

         1. インデックス条件がない場合は、 を押します。
         like ' %xx'
         2. クエリ条件が続くため、ここではまず name に基づいて idx ジョイント インデックス に移動します。いくつかのデータをクエリした後、 テーブルクエリ全行データに戻り、サーバー層でフィルタリングと同様に実行してデータを検索しますこれは、サーバー層のフィルタリング操作
         をエンジン層
         3. にプッシュすることと同じです。図に示すように:
         #インデックス作成に関する注意事項
      On where, order, join When頻繁に使用されるインデックス

      離散性の高いフィールドを追加してインデックスを作成できます。

      結合インデックスは離散性が高いことを優先します (最初のフィールドに基づいて最初に照合されるため、迅速に検索できます)

      頻繁に更新されるフィールドにはインデックスを作成できません (
      1. ページ分割が発生します
      、インデックスは順番に保存されます。ストレージ ページがいっぱいの場合、再度挿入するとページ分割が発生します)
      2. replace、sum、count などの
      3. 関数を使用する場合、インデックスは使用されないため、追加の関数を構築する必要はありません。 int への文字列変換などの処理が発生し、
      4. 特に長いフィールドにインデックスを付ける必要はありません。最初の数ビットをインターセプトしてインデックスを作成できます (select count(distinct left( name, 10))/count(*) へ 分散の程度を見て、上位のいくつかを抽出することを決定します)
      5. ヒント: SQL を実行するとき、次のことができます。インデックスを使用できるかどうかを正確に言うことはできません。結局のところ、それはすべてオプティマイザーです。決定した
      。たとえば、
      6. Cost Base Optimizer
      コストベースのオプティマイザーを使用する場合は、コストが最も低い最適化を使用します。
      7. インデックスを理解した後、ロックの章のコピーを開くことができます。
      もう 1 つのゴージャスな分割線
      • ロックの章 4 つの主要な機能
      まず、よく知られているいくつかの基本概念を確認してみましょう:
      1. 原子性 (Undo ログによって実装)
      2. 一貫性
      3. 分離
      4. 永続性 (クラッシュ リカバリ、REDO ログの二重書き込み)

      読み取り一貫性の問題は、データベースのトランザクション分離レベル (SQL92 標準) によって解決する必要があります。

      トランザクション内の前提条件:

      1. ダーティ リード (データが読み取られていない)まだ他の人によってコミットされ、その後他の人がそれをロールバックします)
      2. 反復不可能な読み取り (データが初めて読み取られ、その後、他の誰かがコミットを変更し、再度読み取り、データを読み取ります)データ)
      3. ファントム読み取り (範囲クエリ中に他の人が新たに追加したデータを読み取る)

      SQL92 標準規則: (同時実行性は左から右に減少します)

      

      • #ヒント: Innodb では、Repeatable Read のファントム読み取りはそれ自体で解決されるため、存在できません

      Innodb のRepeatable Read (RR) でのファントム読み取りの問題を解決する方法

      Lock Model

      1. LBCC (Lock Based Concurrency Control) 読み取り前にロックを追加するただし、これによりパフォーマンスの問題が発生する可能性があります => 読み取り中にロックすると、他のトランザクションの読み取りと書き込みができなくなり、パフォーマンスが低下します
      2. MVCC (Multi Version Concurrency Control) スナップショットの読み取り時に現在時刻を記録し、他のユーザーは読み取りだけを行うことができますスナップショット => パフォーマンス消費、ストレージ消費

      これら 2 つのソリューションは Innodb で一緒に使用されます。 RR の MVCC 実装について簡単に説明します。図中のロールバック ID の初期値は 0 ではなく NULL にする必要があります。便宜上、0

      # と記述します。

      • ##RC の MVCC 実装は、同じトランザクションの複数の読み取りのバージョンを作成します。一方、RR は、同じトランザクションのいずれか 1 つのバージョンを作成します

      MVCC と LBCC を組み合わせることにより、InnoDB は、ロックなし条件下でのファントム読み取りの問題を解決できます。 Serializable の代わりに、トランザクションは 同時実行 なしで シリアル 化される必要があります。

      InnoDB ロック

      がどのように実装されるかを詳しく見てみましょう RR トランザクション分離レベル ロック Innodb での MVCC の詳細な実装

      1. Innodb のロック

      共有ロックと排他ロック共有ロックと排他ロック=> (S, X)
      1. インテンション ロックインテンション ロック=> これは何ですか「2 つのロックがあり、実際には
      テーブル レベル
      2. => (IS, IX)
      の共有ロックと排他ロックです。上記の
      4 つのロック

      」を参照します。 最も基本的なロックの種類

      レコード ロック レコード ロック
      3. ギャップ ロック ギャップ ロック
      4. ネクスト キー ロック
      5. これら 3 つのロックは、上記の
      4 つのロック

      に対して実装された 3 つのアルゴリズム メソッドとして理解されます。ここでは、それらを一時的に次のように呼びます: 高次ロック #Intention Locks 挿入ロック

      AUTO-INC ロック自動インクリメント キー ロック
      6. 空間インデックスの述語ロック特別に使用される空間インデックス
      8. 上の 3 つは追加の拡張ロックです。
      2. 読み取り/書き込みロックの詳細な説明

      共有ロックを使用するには、

      lock in share mode
      を声明 。排他的ロックは、デフォルトで
      1. Insert、Update、および Delete によって使用されます。ステートメントの後に for update を使用して表示します。 意図ロックはデータベース自体によって維持されます。 (主な機能は、テーブルがロックされているかどうかを記録するためにテーブル をマークすることです) => そのようなロックがない場合、他のトランザクションがテーブルをロックしたいとき、テーブル全体にアクセスする必要があります。ロックのスキャンも面倒です。非効率的な。それが、意図ロックが存在する理由です。
      2. 補足: Mysql では何がロックされていますか?
      ロックされているのはインデックスなので、この時点で「インデックスを作成しないとどうなるのですか?」と尋ねる人がいるかもしれません。

      上でインデックスの存在について説明しました。ここで復習してみましょう。次のような状況がいくつかあります。

      クラスター化インデックス (クラスター化インデックス) である主キーを作成しました。

      完全なデータ

      )
      1. 主キーはありませんが、一意のキーがあり、それがnullではない場合、このキーに基づいてクラスター化インデックスが作成されます
      2. その場合、上記 2 つのどちらでもありません。心配しないでください。innodb は rowid と呼ばれるものを維持し、この ID
      ## に基づいて
      3. クラスター化インデックスを作成します。 #したがって、テーブルにはインデックスが必要です。したがって、ロックをロックするためのインデックスが常に存在します。 明示的に
      index
      を作成していないテーブルに対して

      ロック クエリ

      を実行したい場合、データベースは実際にはどのデータをチェックすべきかを知りません。テーブルを使用することもできます。したがって、単純に

      テーブル全体をロックします。

      • たとえば、補助インデックスに書き込みロックを追加する場合は、select * from where name = 'xxx' for update 最後に、returnテーブルチェック主キーの情報を確認するため、この時点では、補助インデックスをロックすることに加えて、主キーインデックス
      もロックする必要があります。 ##3. 高次ロックの詳細な説明

      ##最初に、上記の 3 つの概念について、次のようなデータのセットがあります: 主キーは 1、3、6、9 です。 保存時は、 x 1 x 3 x x 6 x x x 9 x....

      レコードロック、つまり 1、3、6、9 の各レコードをロックします。 ギャップ ロック、各 #xx

      、(-∞,1)、(1,3)、(3,6)、(6,9)、(9, ∞) のレコード ギャップをロックします。 ロックする場合、ロックされるのは (-∞,1]、(1,3]、(3,6]、(6,9]、(9, ∞]) です。左開きと右閉じの区間

      まず、この 3 つのロックはすべて 排他ロック

      であり、一時キー ロック = レコード ロック ギャップ ロック

      when

      select * from xxx where id = 3 for update
      When
      1. select * from xxx where id = 5 for update, a gap lock is generated => (3,6) is locked. ここで特に注意してください。 : 競合はありません
      Update
      2. に select * from xxx where id = 5 を選択すると、一時的なキー ロックが生成されます => locked (3,6] 、mysql は次の方法で一時的なキー ロックを使用します条件 1 と 2 が満たされない場合、すべての行ロックは一時的なキー ロックです
      3. 元の質問に戻ります。ここでレコード ロック行ロック
      は、他の行ロックを防止します。トランザクションの変更または削除を防止します。
      Gap Lock ギャップ ロック
      他のトランザクションの追加を防ぎます。
      • ギャップ ロックとレコード ロック を組み合わせて Next- キー ロックを形成します。共同で解決します。 RR レベル データ書き込み時のファントム読み取り問題。ロックに関しては逃れる方法はありませんが、デッドロックについて話しましょう後で確認してくださいデッドロックが発生しました

      #「innodb_row_lock_%」のようなステータスを表示

      Innodb_row_lock_current_waits 現在待機しているロックの数
      1. Innodb_row_lock_time 合計の時間waitInnodb_row_lock_time_avg 平均待機時間
         1. Innodb_row_lock_time_max 最大待機時間
         2. Innodb_row_lock_waits 合計で発生した待機数
         select * from information_schema.INNODB_TRX
         5. 現在実行中のトランザクションとロックされたトランザクションを表示できます
      show full processlist
      2. = select * from information_schema.processlist どのトランザクションをクエリできますかユーザー
      3. はどのマシン ホストとどのポート上にありますか どのデータベースが接続されていますか どの命令が実行されるか #ステータスと時刻デッドロックの防止データへのアクセス順序を確保するwhere を使用する場合はインデックスの使用を避けてください (これによりテーブルがロックされ、デッドロックが発生する可能性が高くなるだけでなく、ただし、パフォーマンスは低下します)
      非常に大規模なトランザクションを複数の小さなトランザクションに分割します
      同等のクエリを使用してみます (範囲クエリを使用する場合でも) 単に開いたり閉じたりするのではなく、範囲を制限しますたとえば、id > 1 の場合、次のすべてをロックします)
      1. 最適化の章
      2. サブデータベースとサブテーブル
      3. データ ソースの動的選択
      4. エンコーディング層 -- AbstractRoutingDataSource の実装 => フレームワーク層 -- Mybatis プラグインの実装 => ドライバー層 - シャーディング JDBC (複数のデータ ソースを構成し、カスタム実装戦略に従ってデータを個別のデータベースとテーブルに保存) コア プロセス、SQL 解析 => 実行最適化 => SQL データベース ルーティング => SQL 変更 (例:テーブルの分割とテーブル名の変更)=>SQL 実行=>結果のマージ) => プロキシ層 -- Mycat (データベースへのすべての接続から独立しています。すべての接続は Mycat によって行われ、他のサービスは Mycat にアクセスしてデータを取得します) => サービス層 - 特別な SQL バージョン

      MYSQL を最適化する方法

      結局のところ、MYSQL をより良く使用するために多くの知識を学びましたので、実際にそれを実行して完全な最適化システムを確立しましょう

      #クエリのパフォーマンスを向上させたい場合は、この

      クエリ実行プロセス

      、クライアント接続プール

      から開始できます。 毎回接続を作成したり破棄したりすることを避けるために、接続プールを追加します。

      では、接続プールは多ければ多いほど良いのでしょうか? 興味のある方はこの記事を読んでください: プールのサイジングについて

      大まかに要約します:

      1. SQL の同時実行は、接続数が増加しても高速化されません。なぜ？ 10,000 の接続を同時に実行している場合、10 の接続よりもはるかに高速ではないでしょうか? 答えは「ノー」で、高速ではないだけでなく、ますます遅くなります。
         1. コンピュータでは、CPU だけが実際に スレッドを実行できることを誰もが知っています。オペレーティング システムは タイム スライシング テクノロジを使用しているため、1 つの CPU コア が 複数のスレッド を実行していると考えられます。
         2. しかし、実際には、以前の CPU は、一定の 期間中に 1 つの スレッド しか実行できないため、同時実行性をどのように高めても CPU は、この期間内にまだ限られた量のデータしか処理できません。
         では、
         3. CPU がそれほど多くのデータを処理できない場合でも、どのようにして速度が低下するのでしょうか? タイム スライシング のため、複数のスレッドが 「同時に実行」しているように見えても、実際にはスレッド間での コンテキストの切り替え に非常に時間がかかります したがって、スレッドの数が CPU コアの数を超えると、スレッドの数を増やしてもシステムは遅くなるだけで、高速になることはありません。
         もちろん、これは主な理由にすぎません。ディスクは速度にも影響し、接続数の構成にも影響します。
      2. たとえば、私たちが使用している機械式ハードディスクの場合、ディスクを回転させ、特定の場所にアドレス指定してから、
         I/O
         1. 操作を実行する必要があります。このとき、CPU は、他の スレッドに時間をスライスして、処理効率と速度を向上させることができますしたがって、機械式ハード ドライブを使用している場合は、通常、高い同時実行性を維持するために接続を追加できます。
         2. ただし、SSD を使用している場合は、
         I/O
         3. 待ち時間が非常に短いため、あまり多くの接続を追加することはできません
         一般的に言えば、の場合は、次の式に従う必要があります:
      スレッド数 = ((コア数 * 2) 有効ディスク数)
      3. 。たとえば、i7 4 コア 1 ハードディスク マシンは 4 * 2 1 = 9 この式をご存知でしょうか?これはデータベース接続だけでなく、任意の
      多数の CPU コンピューティングおよび I/O シナリオ
      4. 例: 最大スレッド数の設定など
      2. 全体的なデータベース設計計画

      3 番目-パーティ キャッシュ

      同時実行性が非常に大きい場合、それらすべてをデータベースにヒットさせることはできません。クライアントがクエリのためにデータベースに接続するときに、

      Redis

      # などのサードパーティ キャッシュを追加します。 ##データベースをクラスター モードでデプロイする

      データベースの 1 つでは大規模な同時実行に耐えられないため、さらにいくつかのマシンを追加してみてはいかがでしょうか? マスター/スレーブ レプリケーションの概略図

      この図から、Mysql のマスター/スレーブ レプリケーション 読み取り/書き込み分離

      非同期レプリケーション# が簡単にわかります。 ＃＃ 特性。 ヒント: バイナリ ログ

      を
      リレー ログ
      • に書き込んだ後、スレーブは最新の バイナリ ログ位置##を読み取ります。 # は master info に記録され、次回はこの位置から直接取得されます。 マスター/スレーブ レプリケーションのさまざまな方法上記の
      非同期
      マスター/スレーブ レプリケーションの明らかな問題は、更新が適時に行われないことです。データが書き込まれ、ユーザーがすぐに読み取る場合、読み取られるデータはまだ前のデータであるため、遅延が発生します。 遅延の問題を解決するには、トランザクション モードで実行される

      トランザクション

      完全同期レプリケーションを導入する必要があります。マスター ノードが最初に書き込みを行い、その後、すべてのスレーブが許可されます。すべてのスレーブは書き込み成功を返す前にノードがデータの書き込みを完了する必要があります。これは書き込みパフォーマンスに大きな影響を与えます。 準同​​期レプリケーションでは、書き込みデータが 1 つある限り、それが考慮されます成功。 (半同期レプリケーションが必要な場合は、マスター ノードとスレーブ ノードの両方で semisync_mater.so プラグインと semisync_slave.so プラグインをインストールする必要があります)

      GTID (グローバル トランザクション ID) レプリケーション (マスター ライブラリがレプリケートされる場合)マスターとスレーブの同期レプリケーション遅延により、自動
      1. フェイルオーバー
      アクションが実現されます。つまり、マスター ノードがハングアップし、スレーブ ノードが選択された場合、データ損失が発生する可能性があります。素早く自動的に回避します。
      3. クラスター高可用性ソリューション
      マスター/スレーブ HAPrxoy keeplive

      NDB

      Glaera Cluster for MySQL
      1. MHA (MySQL のマスター-マスター レプリケーション マネージャー)、MMM (MySQL マスター ハイ アベイラブル)
      2. MGR (MySQL グループ レプリケーション) => MySQL クラスター
      4. 分割テーブル
      5. データを分類して別のテーブルに分割し、単一のテーブルへの過度の影響を軽減します
      ロック操作
      影響を受けるパフォーマンス

      テーブル構造

      1. 合理的なフィールド タイプの設計
      2. 合理的なフィールド長の設計

      3. オプティマイザーと実行エンジン

      遅いlog

      をオンにすると、show_query_log が実行され、実行時間が変数 long_query_time を超える SQL ステートメントが記録されます。 mysqldumpslow /var/lib/mysql/mysql-slow.log を使用できます。これよりも洗練された分析を提供できるプラグインが多数あるため、ここでは詳しく説明しません。

      分析 SQL の説明

      SQL を作成した後は、explain

      を行う必要があります。

      1. ドライバー テーブル - たとえば、left/right join の乱用はパフォーマンスの低下につながります

      1. left/right join を使用すると、直接指定されます。 MYSQL では、テーブルの関連付けにデフォルトで Nest ループ結合 が使用されます (つまり、 駆動テーブル の結果セットがループの基本データとして使用され、次に、次に関連付けられたテーブルが、このセット内のデータの各部分でフィルター処理され、最後に結果がマージされて、よく 一時テーブル ) と呼ばれるものが得られます。
      2. 駆動テーブル のデータが 100 万 レベルの場合、この結合テーブルのクエリがどれほど遅くなるか想像できるでしょう。しかし、その一方で、小さいテーブルを駆動テーブルとして使用すると、数千万のテーブルのindex の助けを借りてクエリが非常に高速になる可能性があります。 。 誰を
      駆動テーブル
      3. として使用すべきかわからない場合は、オプティマイザに決定させてください。たとえば、次のようになります。 select xxx from table1, table2, table3 where ... ·、オプティマイザは、少数のクエリ レコード行を含むテーブルを駆動テーブルとして使用します。 ドライバー テーブル
      を自分で指定したいだけの場合は、
      4. Explain 武器を入手してください。Explain の結果のうち、最初の 1 つは Basic driver tableSort です。同様に、異なる テーブル
      をソートすると、パフォーマンスに大きな違いがあります。マージされた結果である
      5. 一時テーブルではなく、 ドライバー テーブル をソートしようとします。 ＃ 分類する。つまり、実行計画に usingtemporary が表示され、最適化を実行する必要があります。 2. 実行プランの各パラメーターの意味
      select_type (クエリの種類):

      通常のクエリ

      および
      複雑なクエリ
      1. (ユニオンクエリ、サブクエリなど) SIMPLE、クエリにサブクエリまたはUNION
         1. PRIMARYが含まれていない場合、クエリに
         複雑なクエリ
         2. サブ構造が含まれる場合は、主キー クエリ SUBQUERY を使用し、
         サブ構造を
         3. select# に含める必要があります。 ## または where クエリ DERIVED、from にサブクエリ
         4. UNION RESULT が含まれます、union からの
         テーブル クエリ サブクエリ
         5. table 使用するテーブル名
         type (アクセス タイプ)、必要な行の検索方法、上から下へのクエリ Speed
         高速化
      3. const または system 定数レベルのスキャン、テーブルをクエリする最速の方法、system は const の特殊なケースです (テーブル内 データは 1 つだけ）
         #eq_ref
         1. 一意のインデックス スキャン
         ref
         2. 非一意のインデックス スキャン
         range
         3. between、 などのインデックス範囲スキャン、およびその他の範囲クエリ
         index
         4. (インデックス フル) すべてのインデックス ツリーをスキャン
         ALL
         5. テーブル全体
         NULL
         6. をスキャンします。テーブルまたはインデックス
         7. #possible_keys にアクセスする必要はありません。どのインデックスにアクセスするかを指定します。テーブル内のレコードを検索するために使用します。ここにリストされているインデックス は、必ずしも
         キーを使用しているわけではありません。実際に使用されるインデックス
      は
      4. です。そうでない場合は、NULLkey_len: 使用されるインデックスによって占有されるバイト数
      5. ref: index (キー) と一緒に使用されるフィールドまたは定数
      6. rows: 合計でスキャンされた行数
      7. filtered (percentage): サーバー層でフィルターされたデータの量 Extra: 追加情報
      8. インデックスのみ
      9. 情報はインデックスから取得するだけで済みます。カバーインデックスを使用することもでき、クエリは非常に高速です。
      10. using where
          1. If the queryインデックスは使用しません。 サーバー レイヤ フィルタリングで使用され、結果セットをフィルタリングするために
          where
          2. を使用します。impossible where 何もありませんでしたfound
          using filesort
          3. (インデックスによる並べ替えがなく、他の並べ替え方法が使用される場合に限ります)、filesort
          usingtemporary
          4. (結果セットは、一時テーブルを介して一時的に保存してから計算する必要があります。) 一般に、この場合、DISTINCT、並べ替え、およびグループ化
          5. インデックス条件を使用します プッシュダウンされます。前述したように、インデックスはプッシュダウンされます。サーバー層このフィルタリング操作
          はエンジン層にプッシュダウンされます
          6. #4. ストレージ エンジン
          挿入とクエリ
      しかない場合は、
      MyISAM

      ストレージ エンジン

      を使用できます。一時データのみを使用します。
      1. memory挿入、更新、クエリなどの同時操作が多数ある場合は、
      InnoDB を使用できます。
      概要
      3. 各レベルでの MYSQL 最適化に関する 5 つの回答 (上から下まで)SQL とインデックス
      ストレージ エンジンとテーブル構造
      データベース アーキテクチャ
      MySQL 構成
      1. ハードウェアとオペレーティング システム
      2. さらに、データ クエリが遅い場合は、データベースをただやみくもに「最適化」するのではなく、ビジネス アプリケーション レベルから分析する必要があります。たとえば、データのキャッシュ、リクエストの制限などです。
      3. また次の記事でお会いしましょう

      4. 関連する無料学習の推奨事項: mysql ビデオ チュートリアル

以上がMYSQL の基礎となる原則を理解するのに役立つ記事の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。