14 數據字典

發布時間：2023-06-13 19:08:31

參考官方文檔：https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-schema.html14.1 數據字典 schema數據字典表受保護，只能

參考官方文檔：

https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-schema.html

14.1 數據字典 schema

數據字典表受保護，只能在MySQL的調試版本中訪問。但是，MySQL支持通過INFORMATION_SCHEMA表和SHOW語句訪問存儲在數據字典表中的數據。

相關數據字典表如下:

catalogs：目錄信息。

character_sets：有關可用字符集的信息。

check_constraints：有關在表上定義的CHECK約束的信息。

collations：有關每個字符集的排序的信息。

column_statistics：列值的直方圖統計信息。

column_type_elements：有關列使用的類型的信息。

column：有關表中列的信息。

dd_properties：標識數據字典屬性（例如其版本）的表。服務器使用它來確定是否必須將數據字典升級到較新的版本。

events：有關事件計劃程序事件的信息。如果使用--skip-grant-tables選項啟動服務器，則將禁用事件調度程序，并且不會運行表中注冊的事件。

foreign_keys，foreign_key_column_usage：有關外鍵的信息。

index_column_usage：有關索引使用的列的信息。

index_partitions：有關索引使用的分區的信息。

index_stats：用于存儲執行ANALYZE TABLE時生成的動態索引統計信息。

indexes：有關表索引的信息。

innodb_ddl_log：存儲DDL日志以進行崩潰安全的DDL操作。

parameter_type_elements：有關存儲過程和函數參數的信息，以及有關存儲函數的返回值的信息。

parameters：有關存儲過程和函數的信息。

resource_groups：有關資源組的信息。

routines：有關存儲過程和功能的信息。

schemata：有關schema的信息。在MySQL中，模式是數據庫，因此此表提供有關數據庫的信息。

st_spatial_reference_systems：關于空間數據的可用空間參考系統的信息

table_partition_values：有關表分區使用的值的信息。

table_partitions：有關表使用的分區的信息。

table_stats：有關執行ANALYZE TABLE時生成的動態表統計信息。

table：有關數據庫中表的信息。

tablespace_files：有關表空間使用的文件的信息。

tablespaces：有關活動表空間的信息。

triggers：有關觸發器的信息。

view_routine_usage：關于視圖和視圖使用的存儲函數之間的依賴關系的信息。

view_table_usage：用于跟蹤視圖及其基礎表之間的依賴關系。

數據字典表是不可見的。它們不能用SELECT讀取，不會出現在SHOW TABLES的輸出中，也沒有在INFORMATION_SCHEMA.TABLES表中列出，依此類推。但是，在大多數情況下，可以查詢相應的INFORMATION_SCHEMA表。從概念上講，INFORMATION_SCHEMA提供了一個視圖，MySQL通過該視圖公開數據字典元數據。例如，您不能直接從mysql.schemata表中選擇：

mysql> SELECT * FROM mysql.schemata; ERROR 3554 (HY000): Access to data dictionary table 'mysql.schemata' is rejected.

而是從相應的INFORMATION_SCHEMA表中選擇該信息：

mysql> SELECT * FROM INFORMATION_SCHEMA.SCHEMATAG

沒有與mysql.indexes完全對應的INFORMATION_SCHEMA表，但是INFORMATION_SCHEMA.STATISTICS包含許多相同的信息。

到目前為止，還沒有與mysql.foreign_keys，mysql.foreign_key_column_usage完全對應的INFORMATION_SCHEMA表。 獲取外鍵信息的標準SQL方法是使用INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS和KEY_COLUMN_USAGE表。這些表現在被實現為foreign_keys，foreign_key_column_usage和其他數據字典表的視圖。

MySQL 8.0之前的某些系統表已被數據字典表替換，并且不再存在于mysql系統schema中：

events 數據字典表取代了MySQL 8.0之前的 event表。

parameters 和例 routines 表一起取代了MySQL 8.0之前的proc表。

MySQL 系統表仍存在于MySQL 8.0中，可以通過在MySQL系統數據庫上發出SHOW TABLES語句來查看。通常，MySQL數據字典表和系統表之間的區別在于，數據字典表包含執行SQL查詢所需的元數據，而系統表包含輔助數據，例如時區和幫助信息。 MySQL系統表和數據字典表在升級方式上也有所不同。 MySQL服務器管理數據字典升級。

--數據字典表看不到，但是一些其他的系統表可以看到，并能夠直接查詢。或者使用debug版本來進行直接訪問

數據字典如何升級

MySQL的新版本可能包括對數據字典表定義的更改。此類更改存在于新安裝的MySQL版本中，但是在執行MySQL二進制文件的就地升級時，使用新的二進制文件重新啟動MySQL服務器時將應用更改。在啟動時，將服務器的數據字典版本與存儲在數據字典中的版本信息進行比較，以確定是否應升級數據字典表。如果需要升級并支持升級，則服務器會創建具有更新定義的數據字典表，將持久化的元數據復制到新表中，用新表原子替換舊表，然后重新初始化數據字典。如果不需要升級，則繼續啟動而不更新數據字典表。

數據字典表的升級是一項原子操作，這意味著所有數據字典表都將根據需要進行升級，否則操作將失敗。如果升級操作失敗，則服務器啟動失敗并顯示錯誤。在這種情況下，舊服務器二進制文件可與舊數據目錄一起使用以啟動服務器。當新的服務器二進制文件再次用于啟動服務器時，將重新嘗試升級數據字典。

通常，成功升級數據字典表后，將無法使用舊的服務器二進制文件重新啟動服務器。因此，升級數據字典表后，不支持將MySQL服務器二進制文件降級到以前的MySQL版本。

mysqld --no-dd-upgrade選項可用于防止啟動時自動升級數據字典表。如果指定了--no-dd-upgrade，并且服務器發現服務器的數據字典版本與存儲在數據字典中的版本不同，則啟動失敗，并顯示一條錯誤指出數據字典升級被禁止。

14.2 刪除基于文件的元數據存儲

在以前的MySQL版本中，字典數據部分存儲在元數據文件中。基于文件的元數據存儲的問題包括昂貴的文件掃描，對文件系統相關錯誤的敏感性，用于處理復制和崩潰恢復失敗狀態的復雜代碼以及缺乏可擴展性，使得難以為新功能和關系對象添加元數據。

下面列出的元數據文件已從MySQL中刪除。除非另有說明，否則以前存儲在元數據文件中的數據現在將存儲在數據字典表中。

.frm文件：表元數據文件。刪除.frm文件后：

刪除了由.frm文件結構施加的64KB表定義大小限制。
INFORMATION_SCHEMA.TABLES VERSION列報告的硬編碼值為10，這是MySQL 5.7中使用的最后一個.frm文件版本。

.par文件：分區定義文件。隨著對InnoDB表的本機分區支持的引入，InnoDB停止在MySQL 5.7中使用分區定義文件。
.TRN文件：觸發器名稱空間文件。
.TRG文件：觸器發參數文件。
.isl文件：InnoDB符號鏈接文件，包含在數據目錄外部創建的每表文件表空間文件的位置。
db.opt文件：原先在mysql 目錄下數據庫配置文件。這些文件（每個數據庫目錄一個）包含數據庫默認字符集屬性。
ddl_log.log文件：該文件包含由數據定義語句（例如DROP TABLE和ALTER TABLE）生成的元數據操作的記錄。

數據字典schema將字典數據存儲在事務（InnoDB）表中。數據字典表與非數據字典系統表一起位于mysql數據庫中。

數據字典表是在一個名為mysql.ibd的InnoDB表空間中創建的，該表空間位于MySQL數據目錄中。 mysql.ibd表空間文件必須位于MySQL數據目錄中，并且其名稱不能被其他表空間修改或使用。

字典數據受相同的提交，回滾和崩潰恢復功能保護，這些功能可保護存儲在InnoDB表中的用戶數據。

--以前是myisam 引擎，現在使用innodb 以支持事務性（general_log和slow_log 除外還是CSV引擎存儲）。

14.4 數據字典對象緩存

字典對象高速緩存是一個共享的全局高速緩存，該高速緩存將以前訪問的數據字典對象存儲在內存中，以實現對象重用并最小化磁盤I / O。類似于MySQL使用的其他緩存機制，字典對象緩存使用基于LRU的逐出策略從內存中逐出最近最少使用的對象。

字典對象緩存包括存儲不同對象類型的緩存分區。一些緩存分區大小限制是可配置的，而其他則是硬編碼的。

表空間定義緩存分區：存儲表空間定義對象。 tablespace_definition_cache選項為可以存儲在字典對象高速緩存中的表空間定義對象的數量設置限制。默認值為256。
模式定義高速緩存分區：存儲模式定義對象。 schema_definition_cache選項為可以存儲在字典對象高速緩存中的模式定義對象的數量設置限制。默認值為256。
表定義緩存分區：存儲表定義對象。對象限制設置為max_connections的值，其默認值為151。

表定義高速緩存分區與使用table_definition_cache配置選項配置的表定義高速緩存并行存在。這兩個緩存均存儲表定義，但服務于MySQL服務器的不同部分。一個緩存中的對象不依賴于另一個緩存中對象的存在。

存儲過程定義高速緩存分區：存儲存儲過程定義對象。 stored_program_definition_cache選項為可以存儲在字典對象高速緩存中的已存儲程序定義對象的數量設置限制。默認值為256。

存儲過程定義高速緩存分區與使用stored_program_cache選項配置的存儲過程和存儲函數高速緩存并行存在。

stored_program_cache選項為每個連接設置緩存的存儲過程或函數的數量的軟上限，并且每次連接執行存儲過程或函數時都會檢查該上限。另一方面，存儲過程定義高速緩存分區是共享的高速緩存，該共享的高速緩存存儲的存儲過程定義對象用于其他目的。存儲過程定義高速緩存分區中對象的存在與存儲過程高速緩存或存儲函數高速緩存中對象的存在無關，反之亦然。

字符集定義緩存分區：存儲字符集定義對象，并且硬編碼對象限制為256。

排序定義緩存分區：存儲排序定義對象，并且硬編碼對象限制為256。

14.5 INFORMATION_SCHEMA和數據字典集成

隨著數據字典的引入，以下INFORMATION_SCHEMA表被實現為數據字典表的視圖：

CHARACTER_SETS
CHECK_CONSTRAINTS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
COLUMN_STATISTICS
EVENTS
FILES
INNODB_COLUMNS
INNODB_DATAFILES
INNODB_FIELDS
INNODB_FOREIGN
INNODB_FOREIGN_COLS
INNODB_INDEXES
INNODB_TABLES
INNODB_TABLESPACES
INNODB_TABLESPACES_BRIEF
INNODB_TABLESTATS
KEY_COLUMN_USAGE
KEYWORDS
PARAMETERS
PARTITIONS
REFERENTIAL_CONSTRAINTS
RESOURCE_GROUPS
ROUTINES
SCHEMATA
STATISTICS
ST_GEOMETRY_COLUMNS
ST_SPATIAL_REFERENCE_SYSTEMS
TABLES
TABLE_CONSTRAINTS
TRIGGERS
VIEWS
VIEW_ROUTINE_USAGE
VIEW_TABLE_USAGE

這些表上的查詢現在更加高效，因為它們從數據字典表中獲取信息，而不是通過其他較慢的方式獲取信息。特別是，對于作為數據字典表視圖的每個INFORMATION_SCHEMA表：

服務器不再必須為INFORMATION_SCHEMA表的每個查詢創建一個臨時表。

當基礎數據字典表存儲之前通過目錄掃描（例如，枚舉數據庫名或數據庫中的表名）或文件打開操作（例如，從.frm文件中讀取信息）獲得的值時，INFORMATION_SCHEMA將查詢這些值現在改為使用表查找。（此外，即使對于非視圖INFORMATION_SCHEMA表，數據庫和表名之類的值也可以通過從數據字典中的查找來檢索，并且不需要目錄或文件掃描。）

基礎數據字典表上的索引允許優化器構造有效的查詢執行計劃，對于以前使用每個查詢使用臨時表來處理INFORMATION_SCHEMA表的以前的實現而言，而非如此。

前面的改進還適用于SHOW語句，該語句顯示與作為數據字典表視圖的INFORMATION_SCHEMA表相對應的信息。例如，SHOW DATABASES顯示與SCHEMATA表相同的信息。

除了引入有關數據字典表的視圖之外，現在還緩存了STATISTICS和TABLES表中包含的表統計信息，以提高INFORMATION_SCHEMA查詢性能。 information_schema_stats_expiry系統變量定義了高速緩存的表統計信息到期之前的時間段。默認值為86400秒（24小時）。如果沒有緩存的統計信息或統計信息已過期，則在查詢表統計信息列時將從存儲引擎中檢索統計信息。要隨時更新給定表的緩存值，請使用ANALYZE TABLE

information_schema_stats_expiry可以設置為0，以使INFORMATION_SCHEMA查詢直接從存儲引擎檢索最新的統計信息，其速度比檢索緩存的統計信息慢。

MySQL 8.0中的INFORMATION_SCHEMA表與數據字典緊密相關，因此在用法上存在一些差異。

14.6序列化字典信息（SDI）

除了將有關數據庫對象的元數據存儲在數據字典中之外，MySQL還以序列化形式存儲它。該數據稱為序列化字典信息（SDI）。 InnoDB將SDI數據存儲在其表空間文件中。 NDBCLUSTER將SDI數據存儲在NDB詞典中。其他存儲引擎將SDI數據存儲在為表的數據庫目錄中的給定表創建的.sdi文件中。 SDI數據以緊湊的JSON格式生成。

除臨時表空間和undo表空間文件外，所有InnoDB表空間文件中都存在序列化字典信息（SDI）。 InnoDB表空間文件中的SDI記錄僅描述表空間中包含的表和表空間對象。

SDI數據通過表或CHECK TABLE FOR UPGRADE上的DDL操作來更新。將MySQL服務器升級到新版本時，不會更新SDI數據。

SDI數據的存在提供了元數據冗余。例如，如果數據字典不可用，則可以使用ibd2sdi工具直接從InnoDB表空間文件中提取對象元數據。

對于InnoDB，SDI記錄需要一個索引頁，默認情況下大小為16KB。但是，SDI數據被壓縮以減少存儲空間。

對于由多個表空間組成的分區InnoDB表，SDI數據存儲在第一個分區的表空間文件中。

MySQL服務器使用在DDL操作期間訪問的內部API來創建和維護SDI記錄。

IMPORT TABLE語句根據.sdi文件中包含的信息導入MyISAM表。

14.7數據字典用法差異

與沒有數據字典的服務器相比，使用啟用了數據字典的MySQL服務器在操作上存在一些差異：

以前，啟用innodb_read_only系統變量會阻止僅為InnoDB存儲引擎創建和刪除表。從MySQL 8.0開始，啟用innodb_read_only可以防止所有存儲引擎執行這些操作。任何存儲引擎的表創建和刪除操作都會修改mysql系統數據庫中的數據字典表，但是這些表使用InnoDB存儲引擎，并且在啟用innodb_read_only時無法進行修改。相同的原理適用于需要修改數據字典表的其他表操作。例子：

ANALYZE TABLE失敗，因為它更新了存儲在數據字典中的表統計信息。

ALTER TABLE tbl_name ENGINE = engine_name失敗，因為它更新了存儲在數據字典中的存儲引擎名稱。

注意：啟用innodb_read_only對mysql系統數據庫中的非數據字典表也具有重要意義。

以前，mysql系統數據庫中的表對DML和DDL語句可見。從MySQL 8.0開始，數據字典表是不可見的，不能直接修改或查詢。但是，在大多數情況下，可以查詢對應的INFORMATION_SCHEMA表。這樣可以在服務器開發過程中更改基礎數據字典表，同時保持穩定的INFORMATION_SCHEMA接口供應用程序使用

MySQL 8.0中的INFORMATION_SCHEMA表與數據字典緊密相關，因此在用法上存在一些差異：

以前，INFORMATION_SCHEMA查詢STATISTICS和TABLES表中的表統計信息是直接從存儲引擎檢索統計信息。從MySQL 8.0開始，默認情況下使用緩存的表統計信息。 information_schema_stats_expiry系統變量定義緩存的表統計信息到期之前的時間段。默認值為86400秒（24小時）。（要在任何時候更新給定表的緩存值，請使用ANALYZE TABLE。）如果沒有緩存的統計信息或統計信息已過期，則在查詢表統計信息列時將從存儲引擎中檢索統計信息。要始終直接從存儲引擎檢索最新統計信息，請將information_schema_stats_expiry設置為0。

一些INFORMATION_SCHEMA表是數據字典表的視圖，這使優化程序可以在這些基礎表上使用索引。因此，根據優化程序的選擇，INFORMATION_SCHEMA查詢的結果行順序可能與以前的結果不同。如果查詢結果必須具有特定的行順序特征，請包括ORDER BY子句。

與早期的MySQL系列不同，對INFORMATION_SCHEMA表的查詢可能以不同的字母大小寫返回列名稱。應用程序應以不區分大小寫的方式測試結果集的列名。如果這不可行，則解決方法是在選擇列表中使用列別名，該別名以必需的字母大小寫形式返回列名稱。例如：

SELECT TABLE_SCHEMA AS table_schema, TABLE_NAME AS table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users';

即使在命令行上明確命名，mysqldump和mysqlpump也不再dump INFORMATION_SCHEMA數據庫。

CREATE TABLE dst_tbl like src_tbl，要求src_tbl是基表，如果它是一個INFORMATION_SCHEMA表，則該表是數據字典表的視圖，則失敗。

以前，從INFORMATION_SCHEMA表中選擇的列的結果集標題使用查詢中指定的大寫字母。該查詢產生一個帶有table_name頭的結果集：

SELECT table_name FROM INFORMATION_SCHEMA.TABLES;

從MySQL 8.0開始，這些標頭大寫；前面的查詢產生一個帶有TABLE_NAME頭的結果集。如有必要，可以使用列別名來實現不同的字母大小寫。例如：

SELECT table_name AS 'table_name' FROM INFORMATION_SCHEMA.TABLES;

數據目錄會影響mysqldump和mysqlpump從mysql系統數據庫中轉儲信息的方式：

以前，可以dump mysql系統數據庫中的所有表。從MySQL 8.0開始，mysqldump和mysqlpump僅轉儲該數據庫中的非數據字典表。

以前，使用--all-databases選項時，不需要--routines和--events選項包括存儲的例程和事件：轉儲包括mysql系統數據庫，因為 proc和event表也包含存儲過程和事件的定義。從MySQL 8.0開始，不使用event和proc表。對應對象的定義存儲在數據字典表中，但不轉儲這些表。要在使用--all-databases創建的轉儲中包含存儲的例程和事件，請顯式使用--routines和--events選項。