MYSQL 上級をマスターする

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

記事ディレクトリ

1 はじめに
• 1.1 データベース アーキテクチャ
  • 1.2 監視情報
2 影響データベース要因
• 2.1 超高 QPS および TPS
  • 2.2 大量の同時実行性と超高 CPU 使用率
  • 2.3 ディスク IO
  2.4 ネットワーク カードのトラフィック
  • 2.5 大きなテーブル
  • 2.5.1 大きなテーブルのクエリへの影響
    • 2.5.2 大きなテーブルのクエリDDL 操作の影響
    • 2.5.3 データベース内の大きなテーブルを処理する方法
    #2.6 大規模なトランザクション
  • ##2.6.1トランザクションとは
  • 2.6.2 トランザクションの原子性
    • 2.6.3 トランザクションの一貫性(CONSISTENCY)
    • 2.6.4 トランザクションの分離(ISOLATION)
    • #2.6.5 トランザクションの耐久性 (DURABILITY)
    ##2.6.7 大規模トランザクションとは
    ##1 はじめに
サーバーへの負荷の大部分はデータベースのパフォーマンスによるものです。安定したデータベースとサーバー環境がないと、サーバーに障害が発生したり、ダウンタイムが発生したりする可能性があります。結果として生じる影響も計り知れないため、データベースのパフォーマンスの最適化が不可欠です。

1.1 データベース アーキテクチャ

一般的なデータベース アーキテクチャはマスター サーバーであり、マスターとスレーブの同期のために数台または十数台のスレーブ サーバーが装備されています。クラッシュ後、プログラマはマスター サーバーを引き継ぐ最新データを含むスレーブ サーバーを手動で選択し、新しいマスター サーバーを同期する必要があります。ただし、多くのスレーブ サーバーがあるため、このプロセスに非常に時間がかかる場合があり、このプロセスはネットワーク カードの容量に対する課題でもあります。

1.2 監視情報

QPS および TPS: 値が高いほど優れています。

同時実行性: 同時に処理されるリクエストの数。

CPU 使用率: 低いほど優れています。 ディスク IO: 読み取りおよび書き込みのパフォーマンスが高いほど優れています。 注: 一般に、企業は、サーバーのパフォーマンスに重大な損害を与えるため、大規模なプロモーションの前後にメイン データベースのデータベース バックアップを実行しないこと、または大規模なイベントの前にそのような計画をキャンセルしないことをお勧めします。

2 データベースに影響を与える要因


SQL クエリ速度、サーバー ハードウェア、ネットワーク カード トラフィック、ディスク IO など、データベースに影響を与える要因は数多くあります。ここでは、モニタリング情報でフィードバックされる情報の一部と、それを最適化する方法を紹介します。

2.1 超高 QPS および TPS

SQL が非効率であるため、多くの場合、超高 QPS および TPS のリスクがあります。一般的なプロモーション期間中は、Web サイトへのアクセス数が大幅に増加し、データベースの QPS と TPS も増加します。

QPS とは: 1 秒あたりに処理されるクエリの数。たとえば、CPU があり、10 ミリ秒で 1 つの SQL を処理できる場合、1 秒で 100 の SQL を処理できます (QPS2.2 大規模な同時実行と非常に高い CPU 使用率

これによりどのようなリスクが発生しますか?

大量の同時実行では、データベース接続の数がいっぱいになる可能性があります。この場合は、データベース パラメータ

max_connections をより大きな値 (デフォルト値は 100) に設定してみてください。この値に達すると、500 エラーが報告されます。 CPU 使用率が非常に高い場合、CPU リソースの枯渇によりダウンタイムが発生する可能性があります。

2.3 ディスク IO
データベースのボトルネックの 1 つはディスク IO であり、次のリスクをもたらします:

ディスクの突然の発生IO パフォーマンスの低下 これは、ホット データがサーバーの利用可能なメモリよりも大きい場合によく発生します。通常、この状況は、より高速なディスク デバイスを使用することによってのみ解決できます。

ディスク パフォーマンスを大量に消費するその他のスケジュールされたタスク

これについても上で説明しましたが、大規模なプロモーションの前にメイン データベースをバックアップすることを避けるか、スレーブ サーバーで実行して調整することが最善です。スケジュールされたタスク。ディスクのメンテナンスをしっかりと行います。

1. 
   
2.4 ネットワーク カード トラフィック
2. 
   
明らかに、過剰なネットワーク カード トラフィックは、ネットワーク カード IO がいっぱいになる危険性を引き起こします。

一般的なネットワーク カードはギガビット ネットワーク カード (1000Mb/8 ≒ 100MB) です。

接続数がネットワーク カードの最大容量を超えると、サービスに接続できなくなります。データベースに接続できません? 条件:

1. スレーブ サーバーの数を減らす
   各スレーブ サーバーはマスター サーバーからログをコピーする必要があるため、スレーブ サーバーの数が増えると、ネットワーク カードのトラフィックも増加します。
2. 階層キャッシュの実行
   フロントエンド キャッシュの突然の障害によって引き起こされるバックエンド アクセスの突然の増加を必ず避けてください。
3. クエリには select * を使用しないでください
   これはデータベース最適化の最も基本的な方法です。不要なフィールドをクエリで削除すると、大量のネットワーク トラフィックも消費されます。
4. ビジネス ネットワークとサーバー ネットワークを分離する
   これにより、マスター/スレーブ同期やネットワーク バックアップがネットワーク パフォーマンスに影響を与えるのを回避できます。

2.5 大きなテーブル

どんなテーブルを大きなテーブルと呼ぶことができますか?実際、これはすべて相対的なものであり、ストレージ エンジンごとに異なる制限が存在します。 NoSQL のようなデータ ストレージではテーブルの行数に制限がなく、理論上はディスク容量が許す限り保存できます。ただし、テーブル内の行数が数千万行を超えると、データベースのパフォーマンスに大きな影響を与えます。次に、大きなテーブルの特徴を要約します。

• レコード行数が膨大で、1 つのテーブルが数千万行を超えます
• テーブル データ ファイルが巨大で、テーブル データ ファイルは 10G を超えます

ただし、たとえ上記の特性を満たしていても、データベースのパフォーマンスに大きな影響を与えない可能性があるため、このステートメントは相対的なものです。一般的なデータベースのログテーブルでは、レコード行数が多くても、ファイルのサイズは大きくなりますが、通常は追加とクエリのみであり、大量の変更や削除操作を必要としないため、データベースのパフォーマンスには大きな影響を与えません。
しかし、ある日業務変更により、この 10G ログ テーブルに列を追加する必要が生じた場合、その作業量は間違いなく膨大になります。

2.5.1 クエリに対する大きなテーブルの影響

大きなテーブルは、多くの場合、遅いクエリの発生を表します。遅いクエリは、特定の範囲内でフィルタリングすることが困難であることを意味します。必要なデータを出力します。
たとえば、数千万のデータを含むログ テーブルには、注文が生成されたプラットフォームを記録する注文ソースと呼ばれるフィールドがあります。最初はビジネスに必要のないデータであれば、データベースのパフォーマンスに影響はありませんが、後からビジネスに必要になると、これらの数千万件のデータがどのプラットフォームからの注文量であるかを確認する必要があります。これは問題です、大きな疑問です。
これらの注文にはソース チャネルが少数しかないため、区別が非常に低く、数千万のデータの中から特定のデータをクエリすると、大量のディスク IO が消費され、ディスクの効率が大幅に低下します。ユーザーが注文を表示するたびに、注文のソースがデータベースからクエリされ、大量の低速クエリが生成されます。

2.5.2 DDL 操作に対する大きなテーブルの影響

DDL 操作に対する大きなテーブルの影響。これはどのようなリスクをもたらしますか?

1. インデックスの作成に時間がかかる
   MySQL バージョン 5.5 より前では、データベースはインデックスの作成時にテーブルをロックしていましたが、バージョン 5.5 以降ではテーブルはロックされなくなりました。ただし、MySQL のレプリケーション メカニズムは、ログを通じてスレーブに送信される前に新しいホストで実行されるため、マスターとスレーブ間の長い遅延が発生し、通常のビジネスに影響を及ぼします。
2. テーブル構造を変更するには、長時間テーブルをロックする必要があります
   テーブル構造を変更するプロセス中にテーブルをロックすると、マスター/スレーブ遅延が長くなる危険性があります。 MySQL のマスター/スレーブ レプリケーション メカニズムにより、多くの場合、すべてのテーブル構造操作は最初にホストで実行され、次に同じ操作がログ モードを通じてスレーブに送信され、その後テーブル構造のマスター/スレーブ レプリケーションが完了します。 。
   テーブルの構造を変更し、マスター サーバーでの変更時間が 480 秒であると仮定すると、スレーブ データベースでの変更時間も 480 秒になります。現在、MySQL はマスター/スレーブ レプリケーションにシングル スレッドを使用しているため、大きなテーブルが変更されると、関連する操作がスレーブ サーバー上で完了するまで他のデータ変更操作を実行できなくなります。少なくとも480秒。
   同時に、データの通常の操作に影響を及ぼし、すべての挿入操作がブロックされ、接続数が大幅に増加してサーバーがいっぱいになり、サーバー上で 500 接続エラーが発生します。 。

2.5.3 データベース内の大きなテーブルを扱う方法

1. サブデータベースとテーブル、大きなテーブルを複数の小さなテーブルに分割する
   難易度:
   1. テーブル パーティションの主キーの選択
   2. テーブルをパーティション分割した後のパーティション間データのクエリと統計
2. 履歴データのアーカイブ大規模なテーブル
   機能: フロントエンドとバックエンドのビジネスへの影響を軽減
   難易度:
   1. アーカイブ時点の選択
   2. アーカイブ操作の実行方法

2.6 大きなトランザクション

2.6.1 トランザクションとは

1. トランザクションは、他のすべてのファイル システムとは異なるデータベース システムであり、重要な特徴の 1 つです。

2. トランザクションは、アトミックな SQL ステートメントのセット、または独立した作業単位です。
   したがって、トランザクションは、原子性、一貫性、分離性、耐久性という 4 つの特性に準拠する必要があります。

2.6.2 トランザクションの原子性

定義: トランザクションは、分割できない最小の作業単位と見なす必要があります。トランザクション全体のすべての操作は、成功するかすべて失敗するかのどちらかです。トランザクションの場合、操作の一部だけを実行することはできません。
例:
A が B に 1,000 元を送金したいと考えています。A の口座から 1,000 元が引き出されるとき、データベース上の A の残高は 1,000 減算されますが、B の残高に追加されるときにサーバーがダウンします。 A 一緒に成功するか一緒に失敗するかのトランザクションの原子性を維持するには、1,000 元を A の口座に返金する必要があります。

2.6.3 トランザクションの一貫性 (CONSISTENCY)

定義: 一貫性とは、トランザクションがデータベースを 1 つの一貫性状態から別の一貫性状態 (データの整合性) に変換することを意味します。データベース内の情報は、トランザクションの開始前およびトランザクションの終了後に侵害されません。
例:
A の銀行の 1000 ブロックが B に転送され、A の残高は 0 で、B の口座残高は 0 から 1000 まで変化しますが、最初から最後まで A B = 1000 (A の残高) 0 ( B の口座残高)残高) = 0 (A の残高) 1000 (B の残高)、つまり、A と B の合計残高は変化せず、最初から最後まで 1,000 元のままです。

2.6.4 トランザクション分離 (ISOLATION)

定義: 分離では、トランザクションが他のトランザクションに送信されていないときにデータベース内のデータを変更する必要があります。
例:
銀行で、A が 1,000 元の残高から 500 元を引き出します。引き出しトランザクションが送信される前に、口座 A の残高を照会するトランザクションが実行されます。照会の結果は依然として出金トランザクションが送信される前は、そのトランザクションプロセスが他の企業から見えないため、残高は1,000元です。

• #SQL 標準で定義されている 4 つの分離レベル

  • READ UNCOMMITED
    Uncommittedトランザクションは外部から見えるようになっており、これは私たちがよくダーティ リーディングと呼ぶもので、クエリされたデータはダーティ データと呼ばれます。
  • READ COMMITED
    多くのデータのデフォルトの分離レベルは、トランザクションがコミットされた後にのみ読み取ることができます。トランザクションは外部からは見えません。
  • リピータブル リード (REPEATABLE READ)
    リピータブル リードの分離レベルのトランザクションで、コミットされたリードより上位のレベル。 アンコミット トランザクションでは、テーブル内のデータがクエリされ、別のトランザクションでデータの一部がテーブルに挿入されて送信されますが、コミットされていないトランザクションに戻ると、テーブル内のデータが再度クエリされ、テーブル内のデータがクエリされます。結果は同じで、挿入したばかりのデータはクエリされません。
    • ただし、現在、読み取りコミット分離レベルでデータを見つけることができます。
    • 現在のデータベースの分離レベル ステートメントを表示します:
    • 
      show variables like '% iso %'
    現在のデータベース分離レベル ステートメントを変更します:
    • 
      set session tx_isolation='read-committed'
  ##SERIALIZABLE

  • 最高の分離レベル。簡単に言うと、読み取られたすべてのデータがロックされるため、大量のロックタイムアウトやロック占有の問題が発生する可能性があるため、実際のビジネスではこの分離レベルを使用することはほとんどありません。データの一貫性が厳密に要求され、同時実行性が許容されない場合にのみ、この分離レベルの使用を検討します。
    分離:

  • #非コミット読み取り<コミット読み取り<反復可能読み取り<直列化可能

    • #同時実行性:
  • 非コミット読み取り>コミット読み取り>反復可能読み取り>シリアル化可能
  ##2.6.5 トランザクションの耐久性(耐久性)

定義: トランザクションがコミットされると、その変更はデータベースに永続的に保存されます。このときシステムがクラッシュしても、提出された改変データは失われることはありません（ディスク破損などの物理的要因を除く）。 例: 銀行のユーザー A が口座に 1,000 元を入金します。トランザクションが送信された後、銀行システムがクラッシュしても、復旧後、A が残高を操作しない限り、口座 A の 1,000 元はそのまま残ります。これはトランザクションの耐久性です。

2.6.7 大したこととは何ですか

ここまで述べてきましたが、大したこととは何でしょうか? 大規模トランザクションとは、実行に時間がかかり、大量のデータを操作するトランザクションを指します。たとえば、毎日各ユーザーの収入をカウントする金融商品がありますが、すべてのユーザーの収入をカウントしてユーザーの残高に更新する必要がある場合、数億件の更新に数時間かかります。途中で失敗すると、取引にかかる時間はさらに計り知れず、更新処理に含まれないため、ユーザーの残高がロックされ、全ユーザーが残高を利用できなくなるという問題が発生します。

大規模なトランザクションが引き起こすリスク

:

• 大量のデータをロックし、大量のブロックとロック タイムアウトを引き起こす

ロールバックにかかる時間が比較的長い

1. 実行時間が長く、マスタースレーブ間の遅延が発生しやすい
3. 回避方法大規模なトランザクション

?

• 一度に大量のデータを処理しないようにしてください。

トランザクション内の不要な SELECT 操作を削除します。

1. 上記2点ができれば、基本的に大きな事件の発生は回避できます。

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

以上がMYSQL 上級をマスターするの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。