MySQL の information_schema の詳細な紹介

information_schema データベースは、MySQL システムに付属するデータベースであり、データベース メタデータへのアクセスを提供します。 information_schema は MySQL インスタンスの百科事典のようなもので、文字セット、権限関連、データベース エンティティオブジェクト情報、外部検査制約、パーティションなど、データベース内で解決する必要がある情報のほとんどを記録しているように感じます。 、圧縮テーブル、テーブル情報、インデックス情報、パラメータ、最適化、ロックなど。 information_schema を通じて、MySQL インスタンス全体の実行状況を把握したり、MySQL インスタンスの基本情報を学習したり、データベースの最適化や保守を行うこともできます。まさに百科事典と言えます (笑)。

以下は、私が学んだいくつかの小さな経験に基づいたこれらの表の一般的な分類です。不備がある場合は、ご指摘ください。できるだけ早く修正します。

1: 文字セットと照合順序に関連するシステムテーブル

CHARACTER_SETS: データベース関連の文字セット情報を格納します(メモリストレージエンジン)

COLLATIONS: 文字セットに対応する照合規則

COLLATION_CHARACTER_SET_APPLICABILITY: それは文字セットと接続校正の間の単なる対応です

文字セットと照合順序の違いについて話しましょう:

文字セット (文字セット) は人間の言語を参照する 文字列 を格納します 最小の表意文字シンボル。たとえば、「A」、「B」など。

照合規則は、同じ文字セット内の文字間の比較規則を指します。

各文字シーケンスは 1 つの文字セットに一意に対応します。 set は複数の文字シーケンスに対応できます。その 1 つはデフォルトの文字シーケンス (デフォルト照合順序) です

MySQL の文字シーケンス名は、文字シーケンスに対応する文字セット名で始まります (_ci で始まることを示します)。大文字と小文字) 区別しない)、_cs (大文字と小文字の区別を示す)、または _bin (エンコード値による比較を示す)。例: 文字シーケンス "utf8_general_ci" では、文字 "a" と "A" は同等です

文字セットと照合順序に関連する MySQL 変数 を見てください:

character_set_server: デフォルトの内部動作文字セット

Character_set_client: クライアントソースデータで使用される文字セット

character_set_connection: 接続層の文字セット

character_set_results: query結果の文字セット

character_set_database: 現在選択されているデータベースのデフォルトの文字セット

character_set_system: システムメタデータ (フィールド名)など) 文字セット

MySQL の文字セット変換プロセスをもう一度見てみましょう:

(1)。MySQL サーバーはリクエストを受信すると、character_set_client からcharacter_set_connection に変換します。内部操作を実行する前のリクエスト データはcharacter_set_connectionから内部操作文字セットに変換されます。 決定方法は次のとおりです。

各データフィールドのCHARACTER SET設定値を使用します。

上記の値が存在しない場合は、を使用します。対応するデータ テーブルの DEFAULT CHARACTER SET 設定値 (MySQL 拡張機能、非 SQL 標準)。

上記の値が存在しない場合は、対応するデータベースの DEFAULT CHARACTER SET 設定値を使用します。存在しない場合は、character_set_server 設定値を使用します。

(3). 内部演算文字セットから演算結果をcharacter_set_resultsに変換します。

2: 権限に関連するいくつかのテーブル:

SCHEMA_PRIVILEGES: データベースに関連する権限を提供します。このテーブルは、mysql.db から取得されたメモリ テーブルです。

TABLE_PRIVILEGES: テーブルの権限に関連する情報を提供します。情報は mysql.tables_priv テーブルからロードされます。

COLUMN_PRIVILEGES: このテーブルは、どのテーブルとどのライブラリかを明確に確認できます。どのような権限が付与されるか。認可時に付与オプションが追加された場合、PRIVILEGE_TYPE の値は YES である必要があることがわかります。

USER_PRIVILEGES: テーブルの権限に関連する情報を提供します。この情報は mysql.user テーブルからロードされます。もちろん、テーブルを通じて、MySQL 権限、SCHEMA、TABLE、および COLUMN レベルを明確に確認できます。 、これらはユーザーに基づいて付与されます。 MySQL の承認も非常に詳細であり、列に固有のものであることがわかりますが、これは監査などの特定のアプリケーション シナリオで非常に役立ちます。

3: データベース システムのエンティティ オブジェクトを格納するいくつかのテーブル:

COLUMNS: すべてのストレージ エンジンのテーブルのフィールド情報を格納します。

INNODB_SYS_COLUMNS: INNODB のメタデータを格納し、SYS_COLUMNS 統計テーブルの存在に依存します。

ENGINES: エンジンのタイプ、このエンジンをサポートするかどうか、説明、サポートするかどうか、分散トランザクションをサポートするかどうか、ロールバックポイントをサポートできるかどうか

EVENTS: MySQL の イベント を記録します。スケジュールされた Job

FILES に似ています: このテーブルは、MySQL テーブル スペースに格納されているファイル、ファイル ストレージの場所、このテーブルのデータはメモリ内の InnoDB からプルされる、という情報を提供します。取り出されるので、このテーブル自体もメモリテーブルであり、再起動するたびに再度取り出されます。それが、以下で説明する INNODB_SYS_DATAFILES テーブルです。もう 1 つ注意すべき点は、このテーブルには一時テーブル情報が含まれているため、SYS_DATAFILES テーブルと同等ではないということです。それでも INNODB_SYS_DATAFILES を確認する必要があります。 UNDO テーブルスペースも InnoDB で構成されている場合は、それも記録されます。

PARAMETERS: パラメーター テーブルには、ストアド プロシージャ とメソッドの一部のパラメーター、およびストアド プロシージャの戻り値情報が保存されます。ストレージとメソッドは ROUTINES に保存されます。

PLUGINS: 基本的に MySQL プラグイン情報、アクティブかどうか、ステータス、その他の情報。実際、SHOW PLUGINS 自体はこのテーブルを使用して道徳データ

ROUTINES: ストアド プロシージャとメソッド関数に関する一部の情報を取得しますが、この情報にはユーザー定義は含まれず、一部のシステム情報のみが含まれます。

SCHEMATA: このテーブルは、インスタンスの下に存在するデータベースの数とデータベースのデフォルトの文字セットを提供します。

TRIGGERS: このテーブルは、すべての関連情報を含む トリガー の情報を記録します。システムおよびユーザー自身が作成したトリガー。

VIEWS: Views 情報。これは、システムとユーザーの基本的なビュー情報でもあります。

これらのテーブルには一部のデータベースのエンティティ オブジェクトが格納されており、DBA にとってこれらのテーブルは作業を大幅に容易にし、データベースの関連情報の解決とクエリをより迅速かつ便利にします。

4 制約情報

INNODB_SYS_FOREIGN_COLS

: このテーブルには、INNODB の外部キーに関するメタデータ情報と SYS_FOREIGN_COLS に格納されている情報も格納されます。

INNODB_SYS_FOREIGN

: INNODB に格納されている外部キーに関するメタデータ情報は、その情報と一致しています。 SYS_FOREIGN_COLS に保存されます。情報は一貫していますが、INNODB のみに限ります

KEY

_COLUMN_USAGE: データベース内のすべての制約された列が保存され、制約の名前とカテゴリも記録されます

キーと制約は独立したものであるように見えるため、別々にリストされています。パフォーマンスが低下するため、ほとんどの実稼働環境では外部キーを使用しませんが、一意の制約などの制約を合理的に使用することは依然として良い選択です。 。

5 誰もが DBA に精通していると思いますが、これらのテーブルはシステムの再起動時にもリロードされます。つまり、メモリテーブルです。

PARTITIONS: MySQL パーティション テーブルに関連する情報。このテーブルを通じて、パーティションの関連情報 (データベース内のパーティション テーブル、パーティション テーブルのパーティションとそれぞれのデータ情報) をクエリできます。パーティション) パーティション関連の詳細については、「MySQL パーティション管理」を参照してください。

PROCESSLIST: show processlist は実際にこのテーブルからデータを取得し、PROCESSLIST のデータがその基礎となります。これはメモリ テーブルであるため、メモリ内でのクエリと同等であり、これらの操作は非常に高速です。

INNODB_CMP_PER_INDEX、INNODB_CMP_PER_INDEX_RESET: これら 2 つのテーブルには、テーブル全体とインデックスに関する情報を含む、INNODB 情報テーブルの圧縮に関する関連情報が格納されます。データ自体もクラスター化インデックスと見なすことができるため、インデックスは圧縮されます。圧縮テーブルについては、information_schema シリーズ 11 に簡単に紹介します。

INNODB_CMPMEM、INNODB_CMPMEM_RESET: これら 2 つのテーブルには、MySQL INNODB の圧縮ページに関するバッファ プール情報が保存されますが、注意すべき点は、これら 2 つのテーブルを使用してすべての情報を収集する場合、パフォーマンスに影響を与える可能性があることです。重大な影響があるため、デフォルトではオフになっています。この機能を有効にしたい場合は、innodb_cmp_per_index_enabled パラメータを ON に設定する必要があります。

INNODB_BUFFER_POOL_STATS: このテーブルは、INNODB のバッファー プールに関する情報を提供します。これは、show Engine innodb status によって提供される情報と同じです。これは、show エンジンの innodb ステータスの情報源でもあります。

INNODB_BUFFER_PAGE_LRU、INNODB_BUFFER_PAGE: INNODB LRU LIST に関連する情報を保持します。詳細については、編集者のメモ innodb バッファー プールの説明を参照してください。

INNODB_BUFFER_PAGE: このテーブルは比較的単純で、バッファーにバッファリングされたページ データを格納します。 。このテーブルをクエリすると、サービスの短い一時停止を許容できない限り、このステートメントを独自の実稼働データベースで実行しないでください。詳細については、innodb バッファ プール

INNODB_SYS_DATAFILES に関する編集者のメモを参照してください。このテーブルは、記録されたテーブルのファイル保存場所とテーブルスペース (INNODB) の対応です

INNODB_TEMP_TABLE_INFO: このテーブルは、INNODB のすべてのユーザーが使用するすべての情報を記録しますが、インメモリにのみ記録できますそして永続的な情報はありません。

INNODB_METRICS: INFORMATION_SCHEMA を補足する INNODB のさまざまなパフォーマンス インデックスを提供します。MySQL システム統計を収集します。これらの統計は手動でオンまたはオフに設定できます。次のパラメータが制御可能です: innodb_monitor_enable、innodb_monitor_disable、innodb_monitor_reset、innodb_monitor_reset_all。

INNODB_SYS_VIRTUAL: テーブルには、INNODB テーブルの仮想カラムに関する情報が格納されます。これは、MySQL 5.7 では、仮想生成カラムとストアド生成カラムの 2 つのタイプがサポートされています。生成された列のみを生成します。データ ディクショナリ (テーブルのメタデータ) に保存すると、このデータ列はディスクに保存されません。後者は、生成された列を読み取られるたびに計算するのではなく、ディスクに保存します。明らかに、後者は既存のデータから計算できるデータを保存し、より多くのディスク領域を必要とし、実際にデータの列を保存することに比べて利点がありません。そのため、MySQL 5.7 では、生成された列のタイプは指定されておらず、デフォルトは です。仮想列です。

INNODB_CMP、INNODB_CMP_RESET: INNODB 情報テーブルの圧縮に関する関連情報を保存します。詳細については、推奨されるメモを参照してください。

これらのテーブルが管理関連のテーブルとしてリストされているのはなぜですか? これらのテーブルは、接続、パーティション、圧縮テーブル、innodb バッファー プールなどのテーブルと同様であり、データベースの関連機能のステータスを明確に確認できるからです。特に、いくつかの変数を通じて MySQL の実行ステータスを確認しやすくなり、管理が容易になります。関連するメモには、推奨される innodb バッファー プールの説明、MySQL パーティション管理、および information_schema シリーズ 11 が含まれます。これらはすべて私自身のメモです。

6: テーブル情報とインデックス情報に関するいくつかのテーブル

TABLES、TABLESPACES、INNODB_SYS_TABLES、INNODB_SYS_TABLESPACES:

TABLES このテーブルは間違いなく、システム データベースやユーザー作成データベースを含むデータベースに記録されたテーブル情報です。 。 'test1'G のような show table status のソースはこのテーブルです。

TABLESPACES はマークされたアクティブなテーブルスペースです。 このテーブルは innodb テーブルスペースに関する情報を提供しません。また、本番ライブラリは INNODB を使用する必要があるため、あまり役に立ちません。

INNODB_SYS_TABLES このテーブルは、SYS_TABLES データ ディクショナリからの取得に依存しています。このテーブルは、行フォーマット、圧縮ページ サイズのビットレベル情報 (該当する場合) を含む、テーブル のフォーマットとストレージ特性を提供します。

は、実際に SYS_TABLESPACES の INNODB 情報と一致する INNODB に関するテーブルスペース情報を提供します。 。

統計: このテーブルは、テーブルに関するインデックス情報とすべてのインデックスに関する関連情報を提供します。

INNODB_SYS_INDEXES: INNODB テーブルのインデックスに関連する情報を提供します。 SYS_INDEXES テーブルに格納される情報は、後者がすべてのストレージ エンジンのインデックス情報を提供するのに対し、後者はストレージ エンジンのインデックスのみを提供する点を除いて、基本的に同じです。 INNODB テーブルの情報。

INNODB_SYS_TABLESTATS:

このテーブルは MySQL の INNODB テーブル情報を記録し、MySQL オプティマイザーは実際には MySQL データベース

の統計情報を推定します。

このテーブル レコードはメモリに記録され、再起動するたびに再記録されるため、最後の再起動以降のデータベース統計のみが記録されます。このテーブルを使用すると、インデックスの使用回数をクエリしたり、頻繁に使用されないインデックスの削除や削除を容易にしたり、テーブルの更新や挿入の効率を向上させたり、ディスク領域を節約したりすることがより便利になります。 INNODB_SYS_FIELDS: このテーブルは INNODB テーブルのインデックスフィールド情報とフィールドランキングを記録します

INNODB_FT_CONFIG: このテーブルはフルテキストインデックス情報を保存します

INNODB_FT_DEFAULT_STOP

W

ORD : このテーブルにはストップワード情報が格納されます。これは innodb の INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD と同じです。この STOPWORD はインデックスを作成する前に作成する必要があり、フィールドは varchar として指定する必要があります。 stopword は、いわゆるストップ ワードです。異なる文字セットや並べ替え方法では、ストップ ワード リストが読み取られ、取得されないことがあります。これは、ストップ ワードによって異なります。言葉の並べ方。この機能を使用して、不要なフィールドを除外できます。 INNODB_FT_INDEX_TABLE: このテーブルは、フルテキスト インデックスを持つ INNODB テーブルのインデックス使用情報を保存します。同様に、このテーブルは innodb_ft_aux_table を設定した後にのみ使用できます。挿入前にレコード情報を保存し、DML 中の高価なインデックスの再編成を回避するためでもあります。

OPTIMIZER_TRACE: MySQL の最適化に関連するいくつかのテーブルについて: 最適化追跡関数によって生成された情報を提供します。また、小規模なテストである MySQL Tracking Optimizer Test も実行しました

PROFILING: SHOW PROFILE は、サーバー実行ステートメントの動作ステータスを詳細に表示できます。また、ステートメントの実行にかかる時間を理解するのにも役立ちます。いくつかの制限としては、機能が実装されていないこと、他の接続からのステートメントを表示および解析できないこと、解析中に発生するコストが挙げられます。 SHOW PROFILES は、最近サーバーに送信された複数のステートメントを表示します。ステートメントの数は、セッション変数 profiling_history_size に従って定義されます。デフォルトは 15 で、最大値は 100 です。 0 に設定すると、分析機能がオフになるのと同じです。詳細については、MySQL プロファイルを参照してください

INNODB_FT_BEING_DELETED、INNODB_FT_DELETED:

INNODB_FT_BEING_DELETED

このテーブルは INNODB_FT_DELETED のスナップショットであり、OPTIMIZE TABLE 中にのみ使用されます。詳細については、私の OPTIMIZE TABLE の説明を参照してください。

8: MySQL トランザクションとロックに関連するいくつかのテーブル

INNODB_LOCKS

: 現在取得されているロックですが、未取得のロックではなく、 INNODB のみに適用されます。

INNODB_LOCK_WAITS: システム ロック待機関連情報。1 つ以上のブロックされた行のレコードや、ロック リクエストやブロックされた変更リクエストなどのロック情報が含まれます。 INNODB_TRX

: 実行中のすべてのトランザクション (INNODB) に関連する情報が含まれており、トランザクションがブロックされているか、ロックが必要かどうかも含まれます。

これらのテーブルを通じて、未完了のものやブロックされたプロセスを簡単にクエリできます。詳細については、information_schema シリーズ 8 (モノ、ロック) を参照してください。

以上がMySQL の information_schema の詳細な紹介の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。