MySQL运维实战之元数据和数据字典

俊达11个月前技术文章437

什么是元数据

假设我们执行一个简单的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 |
+------------------------------+----------+----------+------------------+-----------+------------------------------+--------+


相关文章

flume性能调优

1.Source性能调优1.1 Spooldir Source使用Spooldir Source采集日志数据时,若每行日志数据<100bp,可以通过将多行合并传输来提升传输性能建议合并时根据数据...

PG体系结构(一)

PG体系结构(一)

一、进程结构PG数据库启动时会先启动一个主进程(9.3之前称为postmaster,9.3以后称为postgres server process),然后fork出一些辅助子进程(backend、bac...

NameNode和SecondaryNameNode工作详解

NameNode和SecondaryNameNode工作详解

一、NN和SNN工作机制我们可以思考一个问题:NameNode中的元数据是存储在哪里的首先假设,如果存储在NameNode节点的磁盘中,因为经常需要进行随机访问NameNode的元数据,响应客户请求,...

Flume使用案例之实时读取目录到HDFS

目标:使用flume监听整个目录的文件分步实现:1. 创建配置文件flume-dir.conf#1 Agenta3.sources = r3a3.sinks = k3a3.channels = c3 ...

Kubernetes源码解读(四)--Lister&Watcher源码分析

Kubernetes源码解读(四)--Lister&Watcher源码分析

Lister&&Watcher是Reflector的一个主要能力提供者,我们来看看Lister&&Watcher是如何实现List()和watch()的过程的。List...

Clickhouse MergeTree原理(二)—— 表和分区的维护

MergeTree是Clickhouse中最核心的存储引擎。上一篇文章中,我们介绍了MergeTree的基本结构。1、MergeTree由分区(partiton)和part组成。2、Part是Merg...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。