ビッグデータ学習パート 2 MYSQL 上級

#記事ディレクトリ

5 MySQL アーキテクチャ
• 5.1 MyISAM
  5.2 Innodb
  • 5.2.1 システム テーブル スペースと独立テーブル スペースのどちらを選択するか
    • 5.2.2 Innodb ストレージ エンジンの機能
    • 5.2.3 Innodb ステータス チェック
    • 5.2.4 該当するシナリオ
    • 5.2.4 (拡張) ロックとは
    • 5.2.3.1 とはロック？
      • 5.2.3.2 ロックの種類:
      • 5.2.3.3 書き込みロックと読み取りロックの互換関係(1行の互換性)
      • 5.2.3.4 ロック粒度 :
      • 5.2.3.5 ブロッキングとデッドロック
    ##5.3 CSV ストレージ エンジン
  • ##5.3. 1 CSV ストレージ エンジンの特徴
  • 5.3.2 CSV ストレージ エンジンの適用可能なシナリオ
    • ##5.4 アーカイブ ストレージ エンジン
  ##5.4 .1 ファイル システム ストレージ機能
  • 5.4.2 アーカイブ ストレージ エンジンの機能
  • 5.4.3 アーカイブ ストレージ エンジンの使用シナリオ
    • ##5.5 メモリ ストレージ エンジン
    5.5.1 ファイル システム ストレージの特性
  • 5.5.2 メモリの機能特性
  • 5.5.3 混同されやすいメモリの概念
    • 5.5.4 メモリ使用シナリオ
    • 5.6 Federated Storage Engine
    5.6.1 Federated 機能
  • 5.6.2 Federated の使用方法
  • 5.6.3 Federated の適用可能なシナリオ
    • 6 正しいストレージ エンジンの選択方法
  前の記事からの続き

5 MySQL アーキテクチャ

次に、より一般的に使用されるストレージをいくつか選択します エンジンの簡単な説明 ストレージ エンジンmysql で使用されるデータはデータベースのパフォーマンスに直接影響しますので、ストレージ エンジンを使用する前にストレージ エンジンの特性をよく理解していただければ幸いです。

5.1 MyISAM

MyISAM は、MySQL5.5 より前のデフォルトのストレージ エンジンでした。このため、MyISAM ストレージ エンジンを使用するサーバーは依然として多数存在します。同時に、MyISAM は現在、多くのシステム テーブルと一時テーブルで使用されるストレージ エンジンです。ここで言及されている一時テーブルは、

create table

を通じて作成されるテーブルではなく、作成中に使用されるデータの量を指します。ソートやグループ化などの操作一定のサイズを超えると、クエリ オプティマイザーによって一時テーブルが作成されます。 MyISAM ストレージ エンジンは、MYD と MYI で構成されます。MYD はデータ ファイルの拡張子で、MYI はインデックス ファイルの拡張子です。このストレージ エンジンは、これら 2 つの拡張子を持つデータ ファイルとインデックス ファイルにテーブルを格納します。

機能:

同時実行性とロック レベル
MyISAM は、行レベルのロックではなくテーブル レベルのロックを使用します。つまり、テーブル内のデータは変更時に、テーブル全体をロックする必要があり、テーブルを読み取るときに共有ロックもすべてのテーブルに追加されます。ここから、MyISAM をエンジンとして使用するテーブルの読み取りおよび書き込み操作が相互に排他的であることがわかります。 MyISAM は同時読み取りおよび書き込み操作があまり得意ではないことがわかります。読み取り専用操作のみの場合、共有ロックは共有ロックをブロックしないため、同時実行性の点ではパフォーマンスは悪くありません。

テーブル損傷の修復

MyISAM は、予期せぬシャットダウンによって破損した MyISAM テーブルのチェックと修復をサポートしていますが、ここで説明する修復はデータの回復ではありません。MyISAM はトランザクション ストレージ エンジンではないため、実行できません。トランザクションの回復には関連ログが必要となるため、MyISAM テーブルの回復によりデータ損失が発生する可能性があることに注意してください。

• check table tablename
  を通じてテーブルをチェックし、
repair table tablename
• を通じてテーブルを復元できます。
  
  MyISAM テーブルでサポートされるインデックス タイプ MyISAM はフルテキスト インデックス作成をサポートしており、mysql5.7 より前にフルテキスト インデックス作成をネイティブにサポートする唯一の公式ストレージ エンジンでした。 MyISAM テーブルはデータ圧縮をサポートしています MyISAM が大きな読み取り専用テーブルを表す場合、つまり、テーブルが作成されてデータがインポートされた後、テーブルに変更が加えられない場合、テーブルを圧縮してディスク I/O を削減できます。
myisampack
• コマンドを使用してテーブルを圧縮できます。圧縮ではテーブルが個別に圧縮されるため、データ行を読み取るときにテーブル全体を解凍する必要はありません。
  
• 制限:
  バージョン

大きなテーブルを保存する場合は、変更する必要があります。 MAX_Rows と AVG_ROW_LENGTH

バージョン> mysql5.0 のデフォルトのサポートは 256TBです

• #適用可能なシナリオ:
• 非トランザクション アプリケーション
• 読み取り専用アプリケーション (レポートなど)

空間アプリケーション

• 5.2 Innodb

• Innodb は、MySQL 5.5 以降のバージョンのデフォルトのストレージ エンジンです。Innodb は、トランザクション ストレージ用のストレージ エンジンであり、トランザクション処理をサポートします。
  Innodb には独自のテーブル スペースの概念があり、データは innodb_file_per_table パラメータによって決定されるテーブル スペースに保存されます。このパラメータが ON の場合、システム拡張子 ibd を持つファイルが Innodb テーブルごとに作成されます。このパラメータが OFF の場合、データはシステムの共有テーブル スペース (つまり ibdataX#) に保存されます。 ##、#XX は数値を表し、デフォルトでは 1 から始まります。 このパラメータを表示するコマンドは次のとおりです:
  show variables like 'innodb_file_per_table';
  このパラメータを変更するコマンドは次のとおりです:
  set global innodb_file_per_table=off;
  

  5.2.1 システム表スペースと独立表スペースのどちらを選択するか

  ##比較:

  システム テーブル スペース独立したテーブル スペースファイル サイズを単純に縮小することはできませんシステム ファイルを圧縮するコマンドを渡すと IO ボトルネックが発生します同時に複数のファイルにデータを更新できます推奨事項:

  optimize table

  Innodb に独立したテーブル スペースを使用する

  • 元のテーブルを転送しますシステム表スペースに存在するメソッドを独立した表スペースに追加します。
  手順:

  
  mysqldump を使用してすべてのデータベース テーブル データをエクスポートします

MySQL サービスを停止し、パラメータを変更し、Innodb 関連ファイルを削除します
MySQL サービスを再起動し、Innodb システム テーブル スペースを再構築します
2. データを再インポートします
4. ##5.2.2 Innodb ストレージ エンジンの機能

# #Innodb はトランザクション ストレージ エンジンです トランザクションの ACID 特性 (前に紹介したアトミック性、一貫性など) を完全にサポートします

Redo ログと Undo ログ
• Redoログの実装 トランザクションの持続性は 2 つの部分で構成されます。1 つはメモリ内の作業ログ永続バッファで、そのサイズは innodb_log_buffer_size によって決まります。もう 1 つは再構築されたログ ファイルで、ファイル内に表示される ib_logflie です。システム関連の書類。 Undo Log はトランザクションのアトミック性を実現し、トランザクションが失敗した場合にロールバック操作を実行します。 REDO ログはシーケンシャルに読み書きされ、Undo ログはランダムに読み書きされます。可能であれば、データをソリッド ステート ドライブに保存すると、パフォーマンスが向上します。
• Innodb は行レベルのロックをサポートしています
行レベルのロックとテーブルレベルのロックは異なります。行レベルのロックの特徴は、同時実行性を最大限にサポートできることです。行レベルのロックは次のとおりです。ストレージ エンジン層によって実装されます。
• 
  
• 5.2.3 Innodb ステータスのチェック
  

次のコマンドを使用して Innodb ステータスを確認できます:

show Engine innodb status

5.2.4 該当するシナリオ

mysql5.7 バージョン以降、Innodb はすでにフルテキスト インデックスと空間関数をサポートしているため、ほとんどの OLTP アプリケーションに適しています。 。

5.2.4 (拡張) ロックとは何ですか

5.2.3.1 ロックとは何ですか?

#ロックの主な機能は、共有リソースへの同時アクセスを管理することです

#ロックはトランザクションの分離を実現するために使用されます

##5.2 .3.2 ロックの種類:

• 共有ロック (読み取りロックとも呼ばれます)

排他ロック (書き込みロックとも呼ばれます)

5.2.3.3 書き込みロックと読み取りロックの互換関係（1行の互換性）

書き込みロック

読み取りロック非互換

	書き込みロック	非互換
読み取りロック	非互換	互換性のある
実際の状況では、結果は上の表の結果と異なる場合があります。主な理由は、Innodb のロック メカニズムが非常に複雑なものであり、最終結果に影響を与えるロックが多数あるためです。 5.2.3.4 ロックの粒度: テーブルレベルのロック 行レベルのロック 5.2.3.5 ブロッキングとデッドロック ブロッキング: ブロッキングは、異なるロック間の互換性関係が原因で発生します。ある時点で、あるトランザクションのロックは、別のトランザクションのロックが完了するまで待機する必要があります。占有しているリソースが障害を形成します。 デッドロック: デッドロックとは、トランザクションの実行中に相手が待機しているリソースを 2 つ以上のトランザクションが占有した場合に発生する例外を指します。定義からわかるように、ブロックされた複数のトランザクションがブロックされたトランザクションが待機しているリソースを占有します。デッドロックは、ブロックされた複数のトランザクションが互いに待機しているリソースを占有する場合です。 5.3 CSV ストレージ エンジン CSV ストレージ エンジンは、csv ファイルを mysql テーブル ファイルとして処理できます。このストレージ エンジンのストレージ形式は通常の csv ファイルです。 CSV ストレージ エンジンの保存方法は非常に独特です。テーブルを MyISAM または Innodb に保存すると、これら 2 つのファイルはバイナリ形式で保存されるため、データ ファイルを直接参照できません。CSV ストレージ エンジンは異なります。CSV データは次の形式で保存されます。テキスト形式のファイル。つまり、more などのファイルを表示するコマンドを使用して表示することも、vi コマンドを使用して CSV ストレージ エンジン内のテーブルを表示および編集することもできます。形式と要件が満たされている限り、 CSV ファイルの要件を満たしている場合、データが破損することを心配する必要はありません。 mysql で CSV ストレージ エンジン テーブルを作成すると、3 つのファイル システムのファイルを確認できるようになります。これら 3 つのファイル名はすべてテーブル名に基づいていますが、接尾辞としてそれぞれ csv、csm、および frm が付いています。csv ファイルは、CSV ストレージ エンジン内のデータ ファイルです。 csm ファイルには、テーブルのメタデータ、テーブルのステータス、データ量が保存されます。 frm ファイルにはテーブル構造情報が格納されます。 5.3.1 CSV ストレージ エンジンの特徴 最大の特徴は、データが CSV 形式で保存されることです。 CSV の各列は ##次の図に示すように、#, で区切られ、テキストの内容は二重引用符で囲まれます。 すべての列は NULL であってはなりません テーブルの作成時、すべての列は空であってはならず、NULL 値として保存することはできません。 インデックスはサポートされません 大規模なテーブルやオンライン処理には適していません データ ファイルは直接編集できます テキスト ファイルの内容を保存する 5.3.2 CSV ストレージ エンジンの適用可能なシナリオ CSV ストレージ エンジンはデータ交換に適しています中間テーブル ##5.4 アーカイブ ストレージ エンジン 5.4.1 ファイル システム ストレージの特性 アーカイブストレージ エンジンはすべての書き込みをキャッシュし、zlib を使用して挿入された行を圧縮します。そのため、アーカイブ ストレージ エンジンは、MyISAM ストレージ エンジンのテーブルと比較してディスク I/O を節約します。同じ桁のデータの場合、アーカイブ ストレージ エンジンは、MyISAM と比較してストレージ スペースを節約します。そしてInnodb。アーカイブ ストレージ エンジンに保存される数テラバイトの Innodb テーブルには、数百メガバイトのストレージ スペースしか必要としない場合があります。 アーカイブ ストレージ エンジンのテーブル データは、ARZ というサフィックスを持つファイルですが、他のエンジンと同様に、テーブルの構造情報を格納するために使用される frm というサフィックスを持つシステム ファイルもあります。 5.4.2 アーカイブ ストレージ エンジンの機能 insert および select 操作のみをサポート 自動インクリメント ID 列ではインデックスのみが許可されます ##5.4.3 アーカイブ ストレージ エンジンの使用シナリオ シナリオ 1: ログおよびデータ収集クラス Data アーカイブは変更と削除をサポートしていないため、ORDB は間違いなくデータを変更しますが、一部のウェアハウス タイプのアプリケーションや、ログ テーブルやデータ収集テーブルなどの特殊なテーブルには依然として役立ちます。大量のデータを収集する必要があるため、アーカイブ ストレージ エンジンを使用します。アーカイブ ストレージ エンジンのストレージ容量はすべてのエンジンの中で最も小さいため、データ収集またはログ アプリケーションであっても、アーカイブ ストレージ エンジンはこれらのデータを更新できないことに注意してください。そのため、ログを記録するとき、またはデータ コレクション内のデータを変更する場合は、アプリケーションを使用すると、アーカイブ ストレージ エンジンを使用できない場合があります。 5.5 メモリ ストレージ エンジン 5.5.1 ファイル システム ストレージの特性 メモリ ストレージ エンジンは HEAP ストレージ エンジンとも呼ばれ、データはメモリに保存されます。これは、データ テーブルが使い捨てであることを意味します。MySQL サービスが再起動されると、すべてのメモリ ストレージ エンジンのデータが消去されます。ただし、テーブル構造はメモリ ストレージ エンジンの下でテーブルを作成すると、テーブル構造を保存するために使用される frm システム ファイルのみが生成されるため、このファイルは保持されます。これが、MySQL サーバーを再起動するとデータは失われますが、テーブル構造は失われない理由です。 ファイル ストレージの特性から、MyISAM のインデックスのみがメモリに保存され、データは MyISAM によってキャッシュされるため、メモリ ストレージ エンジンの I/O 効率は MyISAM の I/O 効率よりもはるかに高いことがわかります。オペレーティング システムとメモリ ストレージ エンジン エンジンのすべてのデータとインデックスはメモリに保存されます。メモリ ストレージ エンジンの機能的特徴を見てみましょう。 5.5.2 メモリの機能特徴 機能特徴: HASH インデックス (デフォルト) とBTree Index HASH インデックスであれば同等のクエリを実行する場合は非常に高速ですが、範囲クエリを実行する場合は HASH インデックスが使用できないため、注意が必要ですテーブルに多数の同等のクエリが必要な場合は、HASH インデックスを使用し、範囲クエリには BTree インデックスを使用します。インデックスの種類が異なると、パフォーマンスに大きな影響を与える可能性があります。 すべてのフィールドは固定長です varchar(10) = char(10) これには、テーブル構造を定義するときに最小フィールド長要件を満たす必要があります。満たさないと、大量のメモリが無駄になります。 BLOG や TEXT などの大きなフィールドはサポートされません メモリ ストレージ エンジンはテーブル レベルのロックを使用します 最大サイズは max_heap_table_size パラメータによって決まります デフォルトこのパラメータの値は 16 MB のみで、大量のデータをメモリ ストレージ エンジン テーブルに保存したい場合は、このパラメータを変更する必要があり、このパラメータの変更は既存のメモリ ストレージ エンジン テーブルには反映されません。既存のテーブルを変更する必要があり、既存のテーブルは再構築されます。 5.5.3 メモリの紛らわしい概念 メモリ ストレージ エンジン テーブル: すべてのシステムで使用できますが、同じではありません。一時テーブル。 一時テーブル: 一時テーブルには 2 つのタイプがあります。1 つは、クエリ オプティマイザーがクエリを最適化するときにシステムによって使用される一時テーブルであり、内部一時テーブルです。システムは、制限が次の場合に一時テーブルを使用します。制限を超えていない場合は (BLOB または TEXT ラージ フィールドを使用)、MyISAM 一時テーブルを使用し、制限を超えていない場合はメモリ テーブルを使用します。 もう 1 つは、コマンド createtemporary table によって作成された一時テーブルです。作成されたテーブルは、任意のストレージ エンジンを使用できます。 一時テーブルの種類に関係なく、内部的にのみ表示されます。 5.5.4 メモリ使用シナリオ は、郵便番号や地域対応テーブルなどのテーブルの検索またはマッピングに使用されます データ分析中に生成される中間テーブルの保存に使用されます。 定期的な集計データをキャッシュするために使用される結果テーブル メモリ データは失われやすいため、データは再現可能である必要があります。 5.6 Federated Storage Engine 5.6.1 Federated の機能 リモート MySQL サーバーへのアクセスを提供します。メソッド Federated Storage エンジンはリモート サーバーへのローカル接続のみを確立するため、アクセスしたいすべてのテーブルは引き続きリモート サーバー上に配置され、データはローカルに保存されないと言えます。 Federated Storage Engine テーブルにアクセスするたびに、クエリがリモート サーバーに送信されて実行され、関連データがリモート MySQL サーバーから取得されます。 データはローカルに保存されず、すべてのデータはリモート サーバーに配置されます テーブル構造とリモート サーバーの接続情報はローカルに保存する必要があります したがって、システム内の frm ファイルである場合は、リモート情報の保存とリモート テーブルへの接続方法に関する情報を使用します。 5.6.2 Federated の使用方法 Federated Storage Engine はサーバーに接続する SQL Server の機能を実現しますが、独自のパフォーマンスにより、あまり良くありません。通常、レプリケーションなどを通じて同じ目的を達成できるため、現在の MySQL バージョンでは、フェデレーション ストレージ エンジンはデフォルトで無効になっています。 Federated Storage Engine を使用する必要がある場合は、federated=1 を /usr/local/mysql/my.cnf に追加して、MySQL サーバーを再起動する必要があります。 show Engine を使用して、現在の MySQL サーバーが Federated Storage エンジンをサポートしているかどうかを確認します。 そして、create table ステートメントで次の接続文字列を使用します。 mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name リモート サーバー バインディング接続: grant select,update,insert,delete on remote.remote_fet to fred_link@'127.0.0.1'identified by '123456' クエリ情報を決定できますリモート サーバーに関する情報と、関連するデータベース テーブルに関する情報。 5.6.3 Federated に適用できるシナリオ 時々の統計分析と手動クエリ Federated はパフォーマンスが遅いため、次の場合にのみ適しています。時々統計分析と手動クエリ。 6 正しいストレージ エンジンの選択方法 参考条件: トランザクション バックアップ クラッシュ回復 ストレージ エンジンの独自の機能 ストレージ エンジンを混合しないようにしてください。 #その他の関連する無料学習の推奨事項: mysql チュートリアル(ビデオ)

以上がビッグデータ学習パート 2 MYSQL 上級の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。