高度な MYSQL を紹介する 2 番目の記事-mysql チュートリアル-php.cn

高度な MYSQL を紹介する 2 番目の記事

5 MySQL アーキテクチャ

高度な MYSQL を紹介する 2 番目の記事
以下では、簡単に説明するために、より一般的に使用されるストレージエンジンのいくつかを選択します。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 は MySQL5 のデフォルトのストレージエンジンです。 .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 システム表スペースと独立表スペースのどちらを選択するか

##比較:

システムテーブルスペース独立したテーブルスペースファイルサイズを単純に縮小することはできませんoptimize table同時に複数のファイルにデータを更新できます

システムファイルを圧縮するコマンドを渡すと IO ボトルネックが発生します

推奨事項:

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

元々システムテーブルスペースに存在していたテーブルを独立テーブルに転送します。スペースメソッドで。
手順:

mysqldump を使用してすべてのデータベーステーブルデータをエクスポートします
MySQL サービスを停止し、パラメータを変更し、Innodb 関連ファイルを削除します
MySQL サービスを再起動し、Innodb システムテーブルスペースを再構築します
データを再インポートします

##5.2.2 Innodb ストレージエンジンの機能

# #Innodb はトランザクションストレージエンジンです

トランザクションの ACID 特性 (前に紹介したアトミック性、一貫性など) を完全にサポートします
Redo ログと Undo ログ
Innodb は行レベルのロックをサポートしています

5.2.3 Innodb ステータスのチェック

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

show Engine innodb status
5.2.4 該当するシナリオ

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

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

5.2.3.1 ロックとは何ですか?

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

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

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

排他ロック (書き込みロックとも呼ばれます)
5.2.3.3 書き込みロックと読み取りの互換性ロック関係 (行の互換性状況)

読み取りロック書き込みロック非互換非互換読み取りロック互換性なし# ＃＃＃＃＃互換性がある＃＃＃＃＃＃＃＃＃＃＃

実際の状況では、結果は上の表の結果と異なる場合があります。主な理由は、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 値として保存することはできません。
大規模なテーブルやオンライン処理には適していません
テキストファイルの内容を保存する

5.3.2 CSV ストレージエンジンの適用可能なシナリオ

CSV ストレージエンジンはデータ交換に適しています中間テーブル

高度な MYSQL を紹介する 2 番目の記事

##5.4 アーカイブストレージエンジン

5.4.1 ファイルシステムストレージの特性

アーカイブストレージエンジンはすべての書き込みをキャッシュし、zlib を使用して挿入された行を圧縮します。そのため、アーカイブストレージエンジンは、MyISAM ストレージエンジンのテーブルと比較してディスク I/O を節約します。同じ桁のデータの場合、アーカイブストレージエンジンは、MyISAM と比較してストレージスペースを節約します。そしてInnodb。アーカイブストレージエンジンに保存される数テラバイトの Innodb テーブルには、数百メガバイトのストレージスペースしか必要としない場合があります。

アーカイブストレージエンジンのテーブルデータは、ARZ というサフィックスを持つファイルですが、他のエンジンと同様に、テーブルの構造情報を格納するために使用される frm というサフィックスを持つシステムファイルもあります。

5.4.2 アーカイブストレージエンジンの機能

および 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 サーバー上のテーブル

データはローカルに保存されず、すべてのデータはリモートサーバーに配置されます
テーブル構造とリモートサーバーの接続情報はローカルに保存する必要があります

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 に適用できるシナリオ

時々の統計分析と手動クエリ

6 正しいストレージエンジンの選択方法

参考条件: