MySQL 8.0 ディクショナリテーブル拡張の詳細な説明

MySQL のデータディクショナリは、データベースの重要なコンポーネントの 1 つです。INFORMATION_SCHEMA は、実行中の MySQL サーバーからメタデータを取得するための標準準拠の方法として、MySQL 5.0 で初めて導入されました。 MySQL サーバーに関するデータメタデータ、統計情報、アクセス情報 (データベース名またはテーブル名、フィールドデータ型、アクセス権限など) を保存するために使用されます。

8.0 より前:

1. メタデータはファイルから取得されます

2. MEMORYテーブルエンジンを使用する

3. frmファイルはテーブル構造情報を保存します

4. opt ファイルには、ライブラリの文字セットなど、各ライブラリの基本情報が記録されます。

5. .TRNおよび.TRGファイルはトリガー情報を保存するために使用されます

5.6> information_schema.tables から TABLE_SCHEMA、ENGINE、COUNT(*) を選択します。ここで、table_schema in ('information_schema'、'mysql'、'performance_schema'、'sys') group by TABLE_SCHEMA、ENGINE;
+--------------------+--------------------+----------+
| TABLE_SCHEMA | エンジン | COUNT(*) |
+--------------------+--------------------+----------+
| 情報スキーマ | メモリ | 49 |
| 情報スキーマ | MyISAM | 10 |
| mysql | CSV | 2 |
| mysql | InnoDB | 6 |
| mysql | MyISAM | 21 |
| パフォーマンス スキーマ | パフォーマンス スキーマ | 52 |
+--------------------+--------------------+----------+

5.7> information_schema.tables から TABLE_SCHEMA、ENGINE、COUNT(*) を選択します。ここで、table_schema in ('information_schema'、'mysql'、'performance_schema'、'sys') group by TABLE_SCHEMA、ENGINE;
+--------------------+--------------------+----------+
| TABLE_SCHEMA | エンジン | COUNT(*) |
+--------------------+--------------------+----------+
| 情報スキーマ | InnoDB | 10 |
| 情報スキーマ | メモリ | 51 |
| mysql | CSV | 2 |
| MySQL | InnoDB | 19 |
| mysql | MyISAM | 10 |
| パフォーマンス スキーマ | パフォーマンス スキーマ | 87 |
| システム | NULL | 100 |
| sys | InnoDB | 1 |
+--------------------+--------------------+----------+

8.0 以降:

1. テーブルにメタデータが存在する

2. すべてMySQLデータベースに移動され、InnoDBテーブルエンジンに変更され、非表示になります

3. Information_schemaはviewを通じてのみ閲覧可能

4. すべてのNULLはビューである

5. 別のテーブルスペースmysql.ibdに保存されます

8.0> テーブルから TABLE_SCHEMA、ENGINE、count(*) を選択します。ここで、TABLE_SCHEMA は ('information_schema'、'mysql'、'performance_schema'、'sys') でグループ化されます。TABLE_SCHEMA、ENGINE;
+--------------------+--------------------+----------+
| TABLE_SCHEMA | ENGINE | カウント(*) |
+--------------------+--------------------+----------+
| 情報スキーマ | NULL | 65 |
| MySQL | InnoDB | 31 |
| mysql | CSV | 2 |
| パフォーマンス スキーマ | パフォーマンス スキーマ | 102 |
| システム | NULL | 100 |
| sys | InnoDB | 1 |
+--------------------+--------------------+----------+

5.7 ではいくつかの改善が行われました。しかし、INFORMATION_SCHEMA のパフォーマンスは、多くのユーザーにとって依然として大きな悩みの種となっています。現在の INFORMATION_SCHEMA 実装におけるパフォーマンスの問題の主な原因は、INFORMATION_SCHEMA テーブルに対するクエリが、クエリ実行中に一時テーブルが作成される方法で実装されていることです。

次のように、テーブルフラグメントをクエリします。

5.7> explain select round(DATA_FREE/1024/1024) as DATA_FREE from information_schema.TABLES where DATA_FREE/1024/1024 > 1024 and TABLE_SCHEMA not in ('information_schema', 'mysql', 'performance_schema', 'sys');
+----+-------------+--------+-------+---------------+-------+-------+------+------+-----------------------------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+--------+-------+---------------+-------+-------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | TABLES | ALL | NULL | NULL | NULL | NULL | NULL | where を使用します。Open_full_table; すべてのデータベースをスキャンしました |
+----+-------------+--------+-------+---------------+-------+-------+------+------+-----------------------------------------------------+

追加情報には、Open_full_table、すべてのデータベースのスキャンが含まれます。
Skip_open_table、Open_frm_only、Open_full_table これらの値は、INFORMATION_SCHEMA テーブルクエリのファイルオープンの最適化を示します。

Skip_open_table: テーブルファイルを開く必要はありません。データベースカタログをスキャンすることにより、クエリで情報が利用可能になりました。
Open_frm_only: テーブルの .frm ファイルのみを開く必要があります。
Open_full_table: 最適化されていない情報検索。 .frm、.MYD、および .MYI ファイルを開く必要があります。
スキャンされた N 個のデータベース: information_schema クエリを処理するときにスキャンする必要があるディレクトリの数を示します。

MySQL インスタンスに数百のデータベースがあり、それぞれに数百のテーブルがある場合、INFORMATION_SCHEMA クエリは最終的にファイルシステムから個々の frm ファイルを読み取ることになり、大量の I/O 読み取りが発生します。また、テーブルを開いて関連するメモリ内データ構造を準備するために、より多くの CPU を消費することになります。 MySQL サーバーレベルのテーブルキャッシュ (table_definition_cache システム変数) の使用を試みますが、大規模なインスタンスでは、すべてのテーブルを保持するのに十分な大きさのテーブルキャッシュが存在することはほとんどありません。そのため、メモリ使用量が大幅に増加し、OOM が発生する可能性もあります。

通常、この問題を解決するには次の方法を使用します。

1. ライブラリテーブルを分割して、1つのインスタンスで開かれるファイルの数を減らす

2. table_definition_cacheとtable_open_cacheの数を調整する

3. 物理メモリを追加する

MySQL 8.0 のリリース後、別のオプションが提供されました。ディクショナリテーブルは InnoDB エンジンを使用するため、ディクショナリテーブルでインデックスを使用できます。

次の図は、MySQL 5.7 と 8.0 の設計上の違いを説明しています。

8.0> explain select table_name,table_rows,concat(round(DATA_LENGTH/1024/1024, 2), 'MB') as size,concat(round(INDEX_LENGTH/1024/1024, 2), 'MB') as index_size,DATA_FREE/1024/1024 AS data_free_MB from information_schema.TABLES where table_schema not in ('information_schema','performance_schema','test') order by data_free_MB desc limit 10;
+----+-------------+-----------+-----------+----------+--------------------+----------+----------+-------------------------------+------+----------+----------------------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+-----------+-----------+----------+--------------------+----------+----------+-------------------------------+------+----------+----------------------------------------------+
| 1 | SIMPLE | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | インデックスの使用; 一時の使用; ファイルソートの使用 |
| 1 | SIMPLE | sch | NULL | ref | PRIMARY,catalog_id | catalog_id | 8 | mysql.cat.id | 6 | 50.00 | where の使用; インデックスの使用 |
| 1 | SIMPLE | tbl | NULL | ref | schema_id | schema_id | 8 | mysql.sch.id | 52 | 100.00 | where の使用 |
| 1 | SIMPLE | ts | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.tbl.tablespace_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | stat | NULL | eq_ref | PRIMARY | PRIMARY | 388 | mysql.sch.name、mysql.tbl.name | 1 | 100.00 | NULL |
| 1 | SIMPLE | col | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.tbl.collation_id | 1 | 100.00 | インデックスを使用 |
+----+-------------+-----------+-----------+----------+--------------------+----------+----------+-------------------------------+------+----------+----------------------------------------------+

以上がMySQL 8.0 ディクショナリテーブル機能強化の詳細な説明です。MySQL 8.0 ディクショナリテーブル機能強化の詳細については、123WORDPRESS.COM の他の関連記事にも注目してください。

以下もご興味があるかもしれません: