MySQL运维实战之元数据和数据字典
什么是元数据
假设我们执行一个简单的SQL：
select * from tab where col = 'value';
执行这个语句，MySQL需要知道：
这个表是否存在，表的存储引擎是什么
这个表有哪些字段，这些字段的数据类型是什么
这个表有哪些索引，表的主键是什么
对于InnoDB存储引擎，还需要知道主键和各个索引的根节点在哪个表空间的那个Page。
上述的这些数据就是数据库元数据的一部分。这些数据也存储在InnoDB的系统表中，我们也把这些系统表称为数据字典。
在MySQL 5.7以及更早的版本中，每个表的表结构信息存储在frm文件（以.frm为后缀的文件）中，MySQL 8.0取消了frm文件，所有表的元数据都存储在InnoDB中。
访问innodb元数据
InnoDB元数据位于schema mysql中，默认情况下，无法访问这些表：
mysql> select * from mysql.tables limit 1; ERROR 3554 (HY000): Access to data dictionary table 'mysql.tables' is rejected.
需要在编译MySQL时加上Debug选项，
1、Build mysql
build mysql时，加上Debug选项。
cmake -DDebug=1
2、设置debug变量：
设置session变量debug之后，就可以访问元数据表了。
元数据表
查询元数据表列表：
mysql> SET SESSION debug='+d,skip_dd_table_access_check'; mysql> SELECT name, schema_id, hidden, type FROM mysql.tables where schema_id=1 AND hidden='System'; +------------------------------+-----------+--------+------------+ | name | schema_id | hidden | type | +------------------------------+-----------+--------+------------+ | catalogs | 1 | System | BASE TABLE | | character_sets | 1 | System | BASE TABLE | | check_constraints | 1 | System | BASE TABLE | | collations | 1 | System | BASE TABLE | | column_statistics | 1 | System | BASE TABLE | | column_type_elements | 1 | System | BASE TABLE | | columns | 1 | System | BASE TABLE | | dd_properties | 1 | System | BASE TABLE | | events | 1 | System | BASE TABLE | | foreign_key_column_usage | 1 | System | BASE TABLE | | foreign_keys | 1 | System | BASE TABLE | | index_column_usage | 1 | System | BASE TABLE | | index_partitions | 1 | System | BASE TABLE | | index_stats | 1 | System | BASE TABLE | | indexes | 1 | System | BASE TABLE | | innodb_ddl_log | 1 | System | BASE TABLE | | innodb_dynamic_metadata | 1 | System | BASE TABLE | | parameter_type_elements | 1 | System | BASE TABLE | | parameters | 1 | System | BASE TABLE | | resource_groups | 1 | System | BASE TABLE | | routines | 1 | System | BASE TABLE | | schemata | 1 | System | BASE TABLE | | st_spatial_reference_systems | 1 | System | BASE TABLE | | table_partition_values | 1 | System | BASE TABLE | | table_partitions | 1 | System | BASE TABLE | | table_stats | 1 | System | BASE TABLE | | tables | 1 | System | BASE TABLE | | tablespace_files | 1 | System | BASE TABLE | | tablespaces | 1 | System | BASE TABLE | | triggers | 1 | System | BASE TABLE | | view_routine_usage | 1 | System | BASE TABLE | | view_table_usage | 1 | System | BASE TABLE |
部分元数据表存储的数据：
tables
tables表中存储了数据库里每一个表的表结构相关信息，如存储引擎，行格式，字符集等。
mysql> select * from tables where name = 'phy2' limit 1\G *************************** 1. row *************************** id: 385 schema_id: 5 name: phy2 type: BASE TABLE engine: InnoDB mysql_version_id: 80032 row_format: Dynamic collation_id: 255 comment: hidden: Visible options: avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0; se_private_data: NULL se_private_id: 1078 tablespace_id: NULL partition_type: NULL partition_expression: NULL partition_expression_utf8: NULL default_partitioning: NULL subpartition_type: NULL subpartition_expression: NULL subpartition_expression_utf8: NULL default_subpartitioning: NULL created: 2023-03-01 09:26:56 last_altered: 2023-03-01 09:26:56 view_definition: NULL view_definition_utf8: NULL view_check_option: NULL view_is_updatable: NULL view_algorithm: NULL view_security_type: NULL view_definer: NULL view_client_collation_id: NULL view_connection_collation_id: NULL view_column_names: NULL last_checked_for_upgrade_version_id: 0 engine_attribute: NULL secondary_engine_attribute: NULL 1 row in set (0.03 sec)
indexes
indexes表存储表的索引信息。对于innodb存储引擎的表，表自身就是一个类型为PRIMARY的索引。indexes表的se_private_data字段存储着存储引擎相关信息，对于innodb表，se_private_data信息中包含以下信息：
root: 索引根节点的Page号
space_id: 表空间ID
table_id: 表的id，对应tables表的se_private_id字段。
mysql> select * from indexes where table_id = 385\G *************************** 1. row *************************** id: 307 table_id: 385 name: idx_a type: MULTIPLE algorithm: BTREE is_algorithm_explicit: 0 is_visible: 1 is_generated: 0 hidden: 0 ordinal_position: 2 comment: options: flags=0; se_private_data: id=176;root=5;space_id=17;table_id=1078;trx_id=2786; tablespace_id: 22 engine: InnoDB engine_attribute: NULL secondary_engine_attribute: NULL *************************** 2. row *************************** id: 306 table_id: 385 name: PRIMARY type: PRIMARY algorithm: BTREE is_algorithm_explicit: 0 is_visible: 1 is_generated: 0 hidden: 0 ordinal_position: 1 comment: options: flags=0; se_private_data: id=175;root=4;space_id=17;table_id=1078;trx_id=2786; tablespace_id: 22 engine: InnoDB engine_attribute: NULL secondary_engine_attribute: NULL 2 rows in set (0.00 sec)
tablespace
tablespace存储了表空间信息，se_private_data中，id是表空间ID，对应indexes表se_private_data字段中的space_id
mysql> select * from tablespaces where id = 22\G *************************** 1. row *************************** id: 22 name: test/phy2 options: autoextend_size=0;encryption=N; se_private_data: flags=16417;id=17;server_version=80032;space_version=1;state=normal; comment: engine: InnoDB engine_attribute: NULL 1 row in set (0.00 sec)
元数据表的元数据
元数据表本身也是表，所以也需要元数据来描述表结构、字段、索引等相关信息，元数据表的元数据信息也存储在元数据表中。
可以看到，元数据表都存在系统表空间中，space_id为4294967294（0xFFFFFFFE），元数据表的索引的根页面在系统表空间以固定的顺序分配。
## 表
mysql> select id, schema_id, name, type, engine
from tables where name = 'tables';
+----+-----------+--------+------------+--------+
| id | schema_id | name | type | engine |
+----+-----------+--------+------------+--------+
| 29 | 1 | tables | BASE TABLE | InnoDB |
+----+-----------+--------+------------+--------+
1 row in set (0.01 sec)
## 表的字段
mysql> select id, name, ordinal_position, column_type_utf8
from columns where table_id = 29 order by ordinal_position limit 10;
+-----+------------------+------------------+--------------------------------------------------------------------+
| id | name | ordinal_position | column_type_utf8 |
+-----+------------------+------------------+--------------------------------------------------------------------+
| 330 | id | 1 | bigint unsigned |
| 331 | schema_id | 2 | bigint unsigned |
| 332 | name | 3 | varchar(64) |
| 333 | type | 4 | enum('BASE TABLE','VIEW','SYSTEM VIEW') |
| 334 | engine | 5 | varchar(64) |
| 335 | mysql_version_id | 6 | int unsigned |
| 336 | row_format | 7 | enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged') |
| 337 | collation_id | 8 | bigint unsigned |
| 338 | comment | 9 | varchar(2048) |
| 339 | hidden | 10 | enum('Visible','System','SE','DDL') |
+-----+------------------+------------------+--------------------------------------------------------------------+
## 索引
mysql> select id, table_id, name, type, se_private_data
from indexes where table_id = 29 order by id;
+----+----------+------------------------------+----------+---------------------------------------------------------+
| id | table_id | name | type | se_private_data |
+----+----------+------------------------------+----------+---------------------------------------------------------+
| 78 | 29 | PRIMARY | PRIMARY | id=78;root=82;space_id=4294967294;table_id=29;trx_id=0; |
| 79 | 29 | schema_id | UNIQUE | id=79;root=83;space_id=4294967294;table_id=29;trx_id=0; |
| 80 | 29 | engine | UNIQUE | id=80;root=84;space_id=4294967294;table_id=29;trx_id=0; |
| 81 | 29 | engine_2 | MULTIPLE | id=81;root=85;space_id=4294967294;table_id=29;trx_id=0; |
| 82 | 29 | collation_id | MULTIPLE | id=82;root=86;space_id=4294967294;table_id=29;trx_id=0; |
| 83 | 29 | tablespace_id | MULTIPLE | id=83;root=87;space_id=4294967294;table_id=29;trx_id=0; |
| 84 | 29 | type | MULTIPLE | id=84;root=88;space_id=4294967294;table_id=29;trx_id=0; |
| 85 | 29 | view_client_collation_id | MULTIPLE | id=85;root=90;space_id=4294967294;table_id=29;trx_id=0; |
| 86 | 29 | view_connection_collation_id | MULTIPLE | id=86;root=91;space_id=4294967294;table_id=29;trx_id=0; |
| 87 | 29 | type_2 | MULTIPLE | id=87;root=92;space_id=4294967294;table_id=29;trx_id=0; |
+----+----------+------------------------------+----------+---------------------------------------------------------+
10 rows in set (0.01 sec)
## 索引字段
select b.name, b.type, a.index_id, a.ordinal_position, a.column_id, c.name, a.hidden
from index_column_usage a, indexes b, columns c
where a.index_id = b.id
and b.table_id = c.table_id
and a.column_id = c.id
and b.table_id = 29
and a.index_id > 78
order by a.index_id, a.ordinal_position
+------------------------------+----------+----------+------------------+-----------+------------------------------+--------+
| name | type | index_id | ordinal_position | column_id | name | hidden |
+------------------------------+----------+----------+------------------+-----------+------------------------------+--------+
| schema_id | UNIQUE | 79 | 1 | 331 | schema_id | 0 |
| schema_id | UNIQUE | 79 | 2 | 332 | name | 0 |
| schema_id | UNIQUE | 79 | 3 | 330 | id | 1 |
| engine | UNIQUE | 80 | 1 | 334 | engine | 0 |
| engine | UNIQUE | 80 | 2 | 342 | se_private_id | 0 |
| engine | UNIQUE | 80 | 3 | 330 | id | 1 |
| engine_2 | MULTIPLE | 81 | 1 | 334 | engine | 0 |
| engine_2 | MULTIPLE | 81 | 2 | 330 | id | 1 |
| collation_id | MULTIPLE | 82 | 1 | 337 | collation_id | 0 |
| collation_id | MULTIPLE | 82 | 2 | 330 | id | 1 |
| tablespace_id | MULTIPLE | 83 | 1 | 343 | tablespace_id | 0 |
| tablespace_id | MULTIPLE | 83 | 2 | 330 | id | 1 |
| type | MULTIPLE | 84 | 1 | 333 | type | 0 |
| type | MULTIPLE | 84 | 2 | 330 | id | 1 |
| view_client_collation_id | MULTIPLE | 85 | 1 | 361 | view_client_collation_id | 0 |
| view_client_collation_id | MULTIPLE | 85 | 2 | 330 | id | 1 |
| view_connection_collation_id | MULTIPLE | 86 | 1 | 362 | view_connection_collation_id | 0 |
| view_connection_collation_id | MULTIPLE | 86 | 2 | 330 | id | 1 |
| type_2 | MULTIPLE | 87 | 1 | 333 | type | 0 |
| type_2 | MULTIPLE | 87 | 2 | 360 | view_definer | 0 |
| type_2 | MULTIPLE | 87 | 3 | 330 | id | 1 |
+------------------------------+----------+----------+------------------+-----------+------------------------------+--------+
