文章/答案/技术大牛

发布

社区首页 >专栏 >MySQL-8.0 | 数据字典最强解读

MySQL-8.0 | 数据字典最强解读

数据和云

发布于 2019-05-13 10:17:21

4.3K0

文章被收录于专栏：数据和云数据和云

1. 引言

数据字典(Data Dictionary)中存储了诸多数据库的元数据信息如图1所示，包括基本Database, table, index, column, function, trigger, procedure，privilege等；以及与存储引擎相关的元数据，如InnoDB的tablespace, table_id, index_id等。MySQL-8.0在数据字典上进行了诸多优化，本文将对其进行逐一介绍。

图1

2. MySQL-8.0之前的数据字典

俗话说知己知彼，方能百战不殆。在介绍MySQL-8.0的数据字典前，我们先一起回顾一下MySQL-8.0之前的数据字典。

2.1 Data Dictionary 分布位置

图2

如图2所示，旧的数据字典信息分布在server层，mysql库下的系统表和InnoDB内部系统表三个地方，其中保存的信息分别如下所示：

server层文件
- .frm files: Table metadata files.
- .par files: Partition definition files. InnoDB stopped using partition definition files in MySQL 5.7 with the introduction of native partitioning support for InnoDB tables.
- .TRN files: Trigger namespace files.
- .TRG files: Trigger parameter files.
- .isl files: InnoDB Symbolic Link files containing the location of file-per-table tablespace files created outside of the data directory.
- .db.opt files: Database configuration files. These files, one per database directory, contained database default character set attributes.
mysql库下的系统表 mysql.user mysql.db mysql.proc mysql.event等 show tables in mysql；
InnoDB内部系统表
- SYS_DATAFILES
- SYS_FOREIGN
- SYS_FOREIGN_COLS
- SYS_TABLESPACES
- SYS_VIRTUAL

2.2 存在的问题

数据字典分散存储，维护管理没有统一接口
MyISAM系统表易损坏
DDL没有原子性，server层与innodb层数据字典容易不一致
文件存储数据字典扩展性不好
通过information_schema查询数据字典时生成临时表不友好

3. MySQL-8.0的数据字典

鉴于旧数据字典的种种缺点，MySQL-8.0对数据字典进行了较大的改动：把所有的元数据信息都存储在InnoDB dictionary table中，并且存储在单独的表空间mysql.ibd里，其架构如图3所示。下面逐一介绍各项改变的细节。

图3

3.1 存储结构

MySQL下的原有系统表由MyISAM转为了InnoDB表，没有了proc和event表，直接改存到了dictionary table中。在debug模式下，可用如下指令查看dictionary tables：

SET SESSION debug='+d,skip_dd_table_access_check';select name from mysql.tables where hidden='System' and type='BASE TABLE';
+------------------------------+
| name                         |
+------------------------------+
| dd_properties                |
| innodb_dynamic_metadata      |
| innodb_ddl_log               |
| catalogs                     |
| character_sets               |
| collations                   |
| column_statistics            |
| column_type_elements         |
| columns                      |
| events                       |
| foreign_key_column_usage     |
| foreign_keys                 |
| index_column_usage           |
| index_partitions             |
| index_stats                  |
| indexes                      |
| parameter_type_elements      |
| parameters                   |
| resource_groups              |
| routines                     |
| schemata                     |
| st_spatial_reference_systems |
| table_partition_values       |
| table_partitions             |
| table_stats                  |
| tables                       |
| tablespace_files             |
| tablespaces                  |
| triggers                     |
| view_routine_usage           |
| view_table_usage             |
+------------------------------+
31 rows in set (0.01 sec)

3.2 Dictionary Object Cache

数据字典表信息可以通过全局的cache进行缓存。

show variables like '%definition%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| schema_definition_cache         | 256   |
| stored_program_definition_cache | 256   |
| table_definition_cache          | 2000  |
| tablespace_definition_cache     | 256   |
+---------------------------------+-------+

table_definition_cache：存储表定义
schema_definition_cache：存储schema定义
stored_program_definition_cache：存储proc和func定义
tablespace_definition_cache：存储tablespace定义

另外还有character，collation，event，column_statistics也有cache，不过其大小硬编码不可配置：

class Shared_dictionary_cache
{
...
 Shared_multi_map<Abstract_table> m_abstract_table_map;
 Shared_multi_map<Charset> m_charset_map;
 Shared_multi_map<Collation> m_collation_map;
 Shared_multi_map<Column_statistics> m_column_stat_map;
 Shared_multi_map<Event> m_event_map;
 Shared_multi_map<Resource_group> m_resource_group_map;
 Shared_multi_map<Routine> m_routine_map;
 Shared_multi_map<Schema> m_schema_map;
 Shared_multi_map<Spatial_reference_system> m_spatial_reference_system_map;
 Shared_multi_map<Tablespace> m_tablespace_map;
...
}

3.3 Information_schema

图4

information_schema的变化如图4所示，主要包括以下几个方面：

1. information_schema部分表名变化

Old Name	New Name
INNODB_SYS_COLUMNS	INNODB_COLUMNS
INNODB_SYS_DATAFILES	INNODB_DATAFILES
INNODB_SYS_FIELDS	INNODB_FIELDS
INNODB_SYS_FOREIGN	INNODB_FOREIGN
INNODB_SYS_FOREIGN_COLS	INNODB_FOREIGN_COLS
INNODB_SYS_INDEXES	INNODB_INDEXES
INNODB_SYS_TABLES	INNODB_TABLES
INNODB_SYS_TABLESPACES	INNODB_TABLESPACES
INNODB_SYS_TABLESTATS	INNODB_TABLESTATS
INNODB_SYS_VIRTUAL	INNODB_VIRTUAL

2. 通过information_schema查询时不再需要生成临时表获取，而是直接从数据字典表获取

show create table SCHEMATA\G
*************************** 1. row ***************************
             View: SCHEMATA
      Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `SCHEMATA` AS select `cat`.`name` AS `CATALOG_NAME`,`sch`.`name` AS `SCHEMA_NAME`,`cs`.`name` AS `DEFAULT_CHARACTER_SET_NAME`,`col`.`name` AS `DEFAULT_COLLATION_NAME`,NULL AS `SQL_PATH` from (((`mysql`.`schemata` `sch` join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) join `mysql`.`collations` `col` on((`sch`.`default_collation_id` = `col`.`id`))) join `mysql`.`character_sets` `cs` on((`col`.`character_set_id` = `cs`.`id`))) where can_access_database(`sch`.`name`)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

3. 不需要像以前一样扫描文件夹获取数据库列表，不需要打开frm文件获取表信息，而是直接从数据字典表获取

4. information_schema查询以view的形式展现，更利于优化器优化查询

EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys      | key        | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
|  1 | SIMPLE      | cat   | NULL       | index  | PRIMARY            | name       | 194     | NULL                    |    1 |   100.00 | Using index |
|  1 | SIMPLE      | sch   | NULL       | eq_ref | PRIMARY,catalog_id | catalog_id | 202     | mysql.cat.id,const      |    1 |   100.00 | Using index |
|  1 | SIMPLE      | tbl   | NULL       | eq_ref | schema_id          | schema_id  | 202     | mysql.sch.id,const      |    1 |   100.00 | Using where |
|  1 | SIMPLE      | stat  | NULL       | const  | PRIMARY            | PRIMARY    | 388     | const,const             |    1 |   100.00 | Using index |
|  1 | SIMPLE      | ts    | NULL       | eq_ref | PRIMARY            | PRIMARY    | 8       | mysql.tbl.tablespace_id |    1 |   100.00 | Using index |
|  1 | SIMPLE      | col   | NULL       | eq_ref | PRIMARY            | PRIMARY    | 8       | mysql.tbl.collation_id  |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
6 rows in set, 1 warning (0.00 sec)

3.4 优点

去掉server层的元数据文件，元数据统一存储到InnoDB数据字典表，易于管理且crash-safe
支持原子DDL
information_schema查询更高效

4. Serialized Dictionary Information (SDI)

MySQL8.0不仅将元数据信息存储在数据字典表中，同时也冗余存储了一份在SDI中。对于非InnoDB表，SDI数据在后缀为.sdi的文件中，而对于innodb，SDI数据则直接存储与ibd中，如以下例子所示：

create table t1(c1 int) engine=InnoDB;
create table t2(c1 int) engine=MyISAM;

ll test/
-rw-r----- 1 root root 114688 2月  22 17:47 t1.ibd
-rw-r----- 1 root root   1495 2月  22 17:47 t2_337.sdi
-rw-r----- 1 root root      0 2月  22 17:47 t2.MYD
-rw-r----- 1 root root   1024 2月  22 17:47 t2.MYI

select id from mysql.tables where name='t2';
+-----+
| id  |
+-----+
| 337 |
+-----+

4.1 非事务表

上述例子中MyISAM表t2的SDI为test/t2_337.sdi，其中337为table_id, t2_337.sdi可以直接打开，数据是json格式(cat test/t2_337.sdi)：

{
    "mysqld_version_id": 80015,
    "dd_version": 80014,
    "sdi_version": 1,
    "dd_object_type": "Table",
    "dd_object": {
        "name": "t2",
        "mysql_version_id": 80015,
        "created": 20190222094723,
        "last_altered": 20190222094723,
        "hidden": 1,
        "options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;",
        "columns": [{
            "name": "c1",
            "type": 4,
            "is_nullable": true,
            "is_zerofill": false,
            "is_unsigned": false,
            "is_auto_increment": false,
            "is_virtual": false,
            "hidden": 1,
            "ordinal_position": 1,
            "char_length": 11,
            "numeric_precision": 10,
            "numeric_scale": 0,
            "numeric_scale_null": false,
            "datetime_precision": 0,
            "datetime_precision_null": 1,
            "has_no_default": false,
            "default_value_null": true,
            "srs_id_null": true,
            "srs_id": 0,
            "default_value": "",
            "default_value_utf8_null": true,
            "default_value_utf8": "",
            "default_option": "",
            "update_option": "",
            "comment": "",
            "generation_expression": "",
            "generation_expression_utf8": "",
            "options": "interval_count=0;",
            "se_private_data": "",
            "column_key": 1,
            "column_type_utf8": "int(11)",
            "elements": [],
            "collation_id": 255,
            "is_explicit_collation": false
        }],
        "schema_ref": "test",
        "se_private_id": 18446744073709551615,
        "engine": "MyISAM",
        "last_checked_for_upgrade_version_id": 0,
        "comment": "",
        "se_private_data": "",
        "row_format": 1,
        "partition_type": 0,
        "partition_expression": "",
        "partition_expression_utf8": "",
        "default_partitioning": 0,
        "subpartition_type": 0,
        "subpartition_expression": "",
        "subpartition_expression_utf8": "",
        "default_subpartitioning": 0,
        "indexes": [],
        "foreign_keys": [],
        "partitions": [],
        "collation_id": 255
    }
}

4.2 InnoDB事务表

上述例子中的InnoDB表t1的SDI则可以通过工具ibd2sdi可以解析出来(ibd2sdi test/t1.ibd)：

["ibd2sdi"
,
{
    "type": 1,
    "id": 336,
    "object":
        {
    "mysqld_version_id": 80015,
    "dd_version": 80014,
    "sdi_version": 1,
    "dd_object_type": "Table",
    "dd_object": {
        "name": "t1",
        "mysql_version_id": 80015,
        "created": 20190222094723,
        "last_altered": 20190222094723,
        "hidden": 1,
        "options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;",
        "columns": [
            {
                "name": "c1",
                "type": 4,
                "is_nullable": true,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 1,
                "ordinal_position": 1,
                "char_length": 11,
                "numeric_precision": 10,
                "numeric_scale": 0,
                "numeric_scale_null": false,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": true,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "",
                "default_value_utf8_null": true,
                "default_value_utf8": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "interval_count=0;",
                "se_private_data": "table_id=1059;",
                "column_key": 1,
                "column_type_utf8": "int(11)",
                "elements": [],
                "collation_id": 255,
                "is_explicit_collation": false
            },
            {
                "name": "DB_ROW_ID",
                "type": 10,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 2,
                "ordinal_position": 2,
                "char_length": 6,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "numeric_scale_null": true,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": true,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "",
                "default_value_utf8_null": true,
                "default_value_utf8": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "",
                "se_private_data": "table_id=1059;",
                "column_key": 1,
                "column_type_utf8": "",
                "elements": [],
                "collation_id": 63,
                "is_explicit_collation": false
            },
            {
                "name": "DB_TRX_ID",
                "type": 10,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 2,
                "ordinal_position": 3,
                "char_length": 6,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "numeric_scale_null": true,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": true,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "",
                "default_value_utf8_null": true,
                "default_value_utf8": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "",
                "se_private_data": "table_id=1059;",
                "column_key": 1,
                "column_type_utf8": "",
                "elements": [],
                "collation_id": 63,
                "is_explicit_collation": false
            },
            {
                "name": "DB_ROLL_PTR",
                "type": 9,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 2,
                "ordinal_position": 4,
                "char_length": 7,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "numeric_scale_null": true,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": true,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "",
                "default_value_utf8_null": true,
                "default_value_utf8": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "",
                "se_private_data": "table_id=1059;",
                "column_key": 1,
                "column_type_utf8": "",
                "elements": [],
                "collation_id": 63,
                "is_explicit_collation": false
            }
        ],
        "schema_ref": "test",
        "se_private_id": 1059,
        "engine": "InnoDB",
        "last_checked_for_upgrade_version_id": 0,
        "comment": "",
        "se_private_data": "",
        "row_format": 2,
        "partition_type": 0,
        "partition_expression": "",
        "partition_expression_utf8": "",
        "default_partitioning": 0,
        "subpartition_type": 0,
        "subpartition_expression": "",
        "subpartition_expression_utf8": "",
        "default_subpartitioning": 0,
        "indexes": [
            {
                "name": "PRIMARY",
                "hidden": true,
                "is_generated": false,
                "ordinal_position": 1,
                "comment": "",
                "options": "",
                "se_private_data": "id=140;root=4;space_id=2;table_id=1059;trx_id=2569;",
                "type": 2,
                "algorithm": 2,
                "is_algorithm_explicit": false,
                "is_visible": true,
                "engine": "InnoDB",
                "elements": [
                    {
                        "ordinal_position": 1,
                        "length": 4294967295,
                        "order": 2,
                        "column_opx": 1
                    },
                    {
                        "ordinal_position": 2,
                        "length": 4294967295,
                        "order": 2,
                        "column_opx": 2
                    },
                    {
                        "ordinal_position": 3,
                        "length": 4294967295,
                        "order": 2,
                        "column_opx": 3
                    },
                    {
                        "ordinal_position": 4,
                        "length": 4294967295,
                        "order": 2,
                        "column_opx": 0
                    }
                ],
                "tablespace_ref": "test/t1"
            }
        ],
        "foreign_keys": [],
        "partitions": [],
        "collation_id": 255
    }
}
}
,
{
    "type": 2,
    "id": 7,
    "object":
        {
    "mysqld_version_id": 80015,
    "dd_version": 80014,
    "sdi_version": 1,
    "dd_object_type": "Tablespace",
    "dd_object": {
        "name": "test/t1",
        "comment": "",
        "options": "",
        "se_private_data": "flags=16417;id=2;server_version=80015;space_version=1;state=normal;",
        "engine": "InnoDB",
        "files": [
            {
                "ordinal_position": 1,
                "filename": "./test/t1.ibd",
                "se_private_data": "id=2;"
            }
        ]
    }
}
}
]

SDI在ibd中实际是以表（BTree)的形式存储的。建表时会通过btr_sdi_create_index建立SDI的BTree，同时会向BTree插入table和tablespace的SDI信息，表的结构如下：


create table SDI_$TABLESPACEID(type int,
                               id int,
                               compressed_len int,
                               uncompressed_len int,
                               data blob not null,
                               primary key(type,id));

dd::sdi::store
     -->dd::sdi_tablespace::store_tsp_sdi // store tablespace SDI
          -->dict_sdi_set
               -->ib_sdi_set
                    -->ib_cursor_insert_row
    -->dd::sdi_tablespace::store_tbl_sdi // store table SDI
          -->ib_sdi_set
               -->ib_sdi_set
                    -->ib_cursor_insert_row

4.3 其他表空间的SDI

ibd2sdi mysql.ibd，可以查看所以mysql下的表，包括new dictionary和mysql下的普通表。需要注意的是ibdata1中不存放SDI信息，使用ibd2sdi解析它会出现以下提示：

[INFO] ibd2sdi: SDI is empty.

4.4 import

import (import table *.sdi)只支持MyISAM表，InnoDB不支持。由于SDI不包含trigger信息，所以import也不会导入trigger信息，trigger需额外处理。

5. Data Dictionary存取实现


class Storage_adapter {

 /**
   Drop a dictionary object from persistent storage.
 */

 template <typename T>
 static bool drop(THD *thd, const T *object);

/**
  Store a dictionary object to persistent storage.
*/

template <typename T>
static bool store(THD *thd, T *object);
}

例如create table 会涉及到mysql.tablespaces,mysql.tablespace_files, mysql.tables, mysql.indexes, mysql.columns,mysql.index_column_usage等。create table的过程如图5所示：

图5

mysql_create_table
    -->mysql_create_table_no_lock
          -->create_table_impl
               -->rea_create_base_table
                    -->dd::cache::Dictionary_client::store<dd::Table> // mysql.tables
                         -->dd::cache::Storage_adapter::store<dd::Table>
                              -->dd::Weak_object_impl::store
                                   -->dd::Raw_new_record::insert // store mysql.tables
                                         -->handler::ha_write_row
                                              -->ha_innobase::write_row
                                  -->dd::Table_impl::store_children
                                       -->dd::Abstract_table_impl::store_children // store  mysql.columns
                                            -->dd::Collection<dd::Column*>::store_items
                                       -->dd::Collection<dd::Index*>::store_items // store mysql.indexes
                                            -->dd::Weak_object_impl::store
                                                  -->dd::Index_impl::store_children
                                                       -->dd::Collection<dd::Index_element*>::store_items // store mysql.index_column_usage
                              -->dd::sdi::store //store table SDI
                    -->ha_create_table
                         -->handler::ha_create
                               -->ha_innobase::create
                                    -->innobase_basic_ddl::create_impl
                                         -->create_table_info_t::create_table_update_global_dd
                                              -->dd_set_autoinck
                                              -->dd_create_implicit_tablespace
                                                   -->dd_create_tablespace
                                                        -->dd::cache::Dictionary_client::store<dd::Tablespace>
                                                             -->dd::cache::Storage_adapter::store<dd::Tablespace>
                                                                   -->dd::Weak_object_impl::store // store mysql.tablespace
                                                                       -->dd::Tablespace_impl::store_children
                                                                            -->dd::Collection<dd::Tablespace_file*>::store_items
                                                                                -->dd::Tablespace_file_impl::store // store tablespace_files
                                                                   -->dd::sdi::store     // store tablespace SDI
                         -->dd::cache::Dictionary_client::update<dd::Table> // 更新innodb引擎相关元数据
                              -->dd::cache::Storage_adapter::store<dd::Table>
                              -->dd::Weak_object_impl::store

create table t1(c1 int primary key, c2 int) engine=innodb;

select s.name as schema_name, t.* from mysql.tables t, mysql.schemata s where s.id=t.schema_id and s.name='test' and t.name='t1'\G
*************************** 1. row ***************************
                        schema_name: test
                                 id: 374
                          schema_id: 5
                               name: t1
                               type: BASE TABLE
                             engine: InnoDB
                   mysql_version_id: 80015
                         row_format: Dynamic
                       collation_id: 255
                            comment:
                             hidden: Visible
                            options: avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;
                    se_private_data: NULL
                      se_private_id: 1096
                      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: 2019-03-12 19:30:46
                       last_altered: 2019-03-12 19:30:46
                    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

select * from mysql.indexes where table_id= 374\G
*************************** 1. row ***************************
                   id: 299
             table_id: 374
                 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=177;root=4;space_id=39;table_id=1096;trx_id=9996;
        tablespace_id: 43
               engine: InnoDB

select id, name, type from mysql.columns where table_id= 374;
+------+-------------+---------------------+
| id   | name        | type                |
+------+-------------+---------------------+
| 4025 | c1          | MYSQL_TYPE_LONG     |
| 4026 | c2          | MYSQL_TYPE_LONG     |
| 4028 | DB_ROLL_PTR | MYSQL_TYPE_LONGLONG |
| 4027 | DB_TRX_ID   | MYSQL_TYPE_INT24    |
+------+-------------+---------------------+

select * from mysql.index_column_usage where index_id=299;
+----------+------------------+-----------+--------+-------+--------+
| index_id | ordinal_position | column_id | length | order | hidden |
+----------+------------------+-----------+--------+-------+--------+
|      299 |                1 |      4025 |      4 | ASC   |      0 |
|      299 |                2 |      4027 |   NULL | ASC   |      1 |
|      299 |                3 |      4028 |   NULL | ASC   |      1 |
|      299 |                4 |      4026 |   NULL | ASC   |      1 |
+----------+------------------+-----------+--------+-------+--------+

select * from mysql.tablespaces where name='test/t1';
+----+---------+---------+----------------------------------------------------------------------+---------+--------+
| id | name    | options | se_private_data                                                      | comment | engine |
+----+---------+---------+----------------------------------------------------------------------+---------+--------+
| 43 | test/t1 | NULL    | flags=16417;id=39;server_version=80015;space_version=1;state=normal; |         | InnoDB |
+----+---------+---------+----------------------------------------------------------------------+---------+--------+

select * from mysql.tablespace_files where tablespace_id=43;
+---------------+------------------+---------------+-----------------+
| tablespace_id | ordinal_position | file_name     | se_private_data |
+---------------+------------------+---------------+-----------------+
|            43 |                1 | ./test/t1.ibd | id=39;          |
+---------------+------------------+---------------+-----------------+

select * from mysql.tablespaces a, mysql.tablespace_files b where a.id=b.tablespace_id and a.name='test/t1';
+----+---------+---------+----------------------------------------------------------------------+---------+--------+---------------+------------------+---------------+-----------------+
| id | name    | options | se_private_data                                                      | comment | engine | tablespace_id | ordinal_position | file_name     | se_private_data |
+----+---------+---------+----------------------------------------------------------------------+---------+--------+---------------+------------------+---------------+-----------------+
| 43 | test/t1 | NULL    | flags=16417;id=39;server_version=80015;space_version=1;state=normal; |         | InnoDB |            43 |                1 | ./test/t1.ibd | id=39;          |
+----+---------+---------+------------------------------------------------------

6. Initialize

图6

mysqld --initialize的源码流程如图6所示。具体过程为：

dd::Dictionary_impl::init
     -->bootstrap::initialize
            -->DDSE_dict_init
                 -->innobase_ddse_dict_init
                      -->innobase_init_files //创建mysql.ibd
            -->initialize_dictionary
                 -->create_dd_schema/initialize_dd_properties/create_tables
                 -->DDSE_dict_recover // 创建mysql/innodb_system tablespace
                 -->flush_meta_data
                      -->dd::cache::Storage_adapter::store()   //
                           -->dd::Weak_object_impl::store
                                -->dd::Table_impl::store_attributes
                      -->dd::cache::Storage_adapter::core_store  // 存储到Object_registry m_core_registry;
                      -->dd::sdi::store
                 -->populate_tables
                      -->get_dml
                 -->update_properties
                 -->update_versions

7. Atomic DDL

7.1 Atomic DDL

定义：DDL所涉及的以下更改操作是原子的，这些更改操作要么都提交，要么都回滚。

data dictionary
storage engine
binary log

只有InnoDB engine支持Atomic DDL，以下操作不支持：

Table-related DDL statements that involve a storage engine other than InnoDB.
INSTALL PLUGIN and UNINSTALL PLUGIN statements.
INSTALL COMPONENT and UNINSTALL COMPONENT statements.
CREATE SERVER, ALTER SERVER, and DROP SERVER statements.

7.2 DDL log

DDL过程中操作DD事物表是原子的，而DDL过程中也会操作文件，创建和释放BTree以及修改DD cache，这些操作不是原子的。为了实现atomic DDL， DDL过程中对文件操作和Btree操作等记录日志，这些日志会记录到DD表mysql.innodb_ddl_log中。日志有以下几个类型：

enum class Log_Type : uint32_t {
/** Smallest log type */
SMALLEST_LOG = 1,

/** Drop an index tree */
FREE_TREE_LOG = 1,

/** Delete a file */
DELETE_SPACE_LOG,

/** Rename a file */
RENAME_SPACE_LOG,

/** Drop the entry in innodb_dynamic_metadata */
DROP_LOG,

/** Rename table in dict cache. */
RENAME_TABLE_LOG,

/** Remove a table from dict cache */
REMOVE_CACHE_LOG,

/** Alter Encrypt a tablespace */
ALTER_ENCRYPT_TABLESPACE_LOG,

/** Biggest log type */
BIGGEST_LOG = ALTER_ENCRYPT_TABLESPACE_LOG
};

mysql.innodb_ddl_log 表结构如下：

CREATE TABLE `innodb_ddl_log` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`thread_id` bigint(20) unsigned NOT NULL,
`type` int(10) unsigned NOT NULL,
`space_id` int(10) unsigned DEFAULT NULL,
`page_no` int(10) unsigned DEFAULT NULL,
`index_id` bigint(20) unsigned DEFAULT NULL,
`table_id` bigint(20) unsigned DEFAULT NULL,
`old_file_path` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`new_file_path` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `thread_id` (`thread_id`);

将DDL分为以下几个阶段, Prepare记录DDL log，Post-DDL会replay log来提交或回滚DDL操作，同时也并清理DDL log。

Prepare: Create the required objects and write the DDL logs to the mysql.innodb_ddl_log table. The DDL logs define how to roll forward and roll back the DDL operation.

Perform: Perform the DDL operation. For example, perform a create routine for a CREATE TABLE operation.

Commit: Update the data dictionary and commit the data dictionary transaction.

Post-DDL: Replay and remove DDL logs from the mysql.innodb_ddl_log table. To ensure that rollback can be performed safely without introducing inconsistencies, file operations such as renaming or removing data files are performed in this final phase. This phase also removes dynamic metadata from the mysql.innodb_dynamic_metadata data dictionary table for DROP TABLE, TRUNCATE TABLE, and other DDL operations that rebuild the table.

7.3.1 drop table

以drop table为例，drop 过程中会删除ibd文件，也会从mysql.innodb_dynamic_metadata 中删除相应记录。

在preppare阶段只是记录日志，没有真正删除。如果drop过程成功, innobase_post_ddl才从mysql.innodb_ddl_log中读取记录去replay，replay_delete_space_log/replay_drop_log会真正执行删除, replay完也会清理ddl log；如果drop过程失败，rollback时mysql.innodb_ddl_log的记录也回滚了，innobase_post_ddl时没有记录需要replay。

mysql_rm_table
     -->mysql_rm_table_no_locks
        -->drop_base_table
            -->ha_delete_table
               -—>handler::ha_delete_table
                  -->ha_innobase::delete_table
                    -->innobase_basic_ddl::delete_impl
                       -->row_drop_table_for_mysql
                          -->Log_DDL::write_drop_log               // 记录删innodb_dynamic_metadata日志
                          -—>Log_DDL::write_delete_space_log       // 记录删ibd日志
            -->dd::drop_table
                -->dd::cache::Dictionary_client::drop<dd::Table>
                    -->dd::cache::Storage_adapter::drop<dd::Table>
                         -->dd::sdi::drop
        -->innobase_post_ddl
            -->Log_DDL::post_ddl
               -->Log_DDL::replay_by_thread_id
                    -->Log_DDL::replay
                       —>Log_DDL::replay_delete_space_log // post-ddl 真正删除innodb_dynamic_metadata
                       —>Log_DDL::replay_drop_log         // post-ddl 真正删除ibd
                    -->delete_by_ids
                       -->DDL_Log_Table::remove

7.3.2 create table

drop table post_ddl阶段执行的redo操作，而create table post ddl执行的是rollback操作。create table prepare阶段会真正的创建ibd，BTree，修改DD share cache，同时记录相应的log到mysql.innodb_ddl_log中。

create_table
        -->Log_DDL::write_delete_space_log
        -->Log_DDL::write_remove_cache_log
        -->Log_DDL::write_free_tree_log

如果DDL成功commit，在post-DDL阶段，DDL log记录被清理了，不需要replay。如果DDL失败rollback，在post-DDL阶段，DDL log清理操作也回滚了，需要replay， relay会rollback前面的创建ibd，BTree，以及修改DD share cache。

如果create table过程中发生crash，重启后会读取ddl log完成ddl的回滚。

init_server_components
    -->ha_post_recover
         -->post_recover_handlerton
              -->innobase_post_recover
                   -->Log_DDL::recover
                        -->Log_DDL::replay_all
                             -->Log_DDL::replay
                                  -->replay_delete_space_log/replay_remove_cache_log/replay_free_tree_log
                        -->delete_by_ids
                             -->DDL_Log_Table::remove

7.3.3 truncate table

truncate 先rename 为临时ibd，然后drop临时ibd，再重建表。rename会记录ddl log, 参考write_rename_space_log函数，删除重建也会记录ddl log, 同前面介绍的create/drop table, 可以做到原子。rollback时通过日志将临时ibd重命名为原ibd，参考replay_rename_space_log函数。


Sql_cmd_truncate_table::truncate_table
    -->ha_create_table
         -->handler::ha_create
              -->ha_innobase::create
                   -->ha_innobase::truncate_impl
                        -->innobase_truncate<dd::Table>::exec
                             -->innobase_truncate<Table>::truncate
                                  -->rename_tablespace . // t1.ibd rename to #sql-ib1084-513656514.ibd
                                       -->fil_rename_tablespace
                                            -->Fil_shard::space_rename
                                                 -->Log_DDL::write_rename_space_log
                                  -->innobase_basic_ddl::delete_impl
                                       -->row_drop_table_for_mysql
                                            -->Log_DDL::write_delete_space_log// drop #sql-ib1084-513656514.ibd
                                  -->innobase_basic_ddl::create_impl
                                       -->create_table_info_t::create_table
                                            -->create_table_info_t::create_table_def
                                                 -->row_create_table_for_mysql
                                                      -->dict_build_table_def
                                                           -->dict_build_tablespace_for_table
                                                                -->Log_DDL::write_delete_space_log // drop t1.ibd
                                                       -->Log_DDL::write_remove_cache_log
                                                       -->dict_create_index_tree_in_mem
                                                            -->Log_DDL::write_free_tree_log
      -->innobase_post_ddl
           -->Log_DDL::post_ddl
                -->Log_DDL::replay_by_thread_id
                      -->Log_DDL::replay
                          -->Log_DDL::replay_delete_space_log // drop #sql-ib1084-513656514.ibd
                          --> Log_DDL::replay_drop_log
                -->delete_by_ids
                      -->DDL_Log_Table::remove

7.4 Atomic DDL带来的变化

drop 多表或多用户时，如果个别失败，整个DDL都会回滚，且不会记录binlog；而在MySQL8.0以前, 部分DDL会成功且整个DDL会记录binlog。

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+

mysql> drop table t1,tt;
ERROR 1051 (42S02): Unknown table 'test.tt'
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+

show binlog events;
+------------------+-----+----------------+-----------+-------------+-----------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                    |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         124 | Server ver: 8.0.15-debug, Binlog ver: 4 |
| mysql-bin.000001 | 124 | Previous_gtids |         1 |         155 |                                         |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------+
2 rows in set (0.00 sec)

8. Persistent Autoinc

MySQL8.0以前自增值没有持久化，重启时通过select MAX(id)的方式获取当前自增值，这种方式自增值会重复利用。MySQL8.0开始支持自增值持久化，通过增加redo日志和Data Dictonary 表mysql.innodb_dynamic_metadata来实现持久化。

每次insert/update更新自增值时会将自增值写到redo日志中，参考dict_table_autoinc_log函数，日志格式如下：

MLOG_TABLE_DYNAMIC_META
id,
version,
PM_TABLE_AUTO_INC,
autoinc

同时dict_table_t增加了新的变量autoinc_persisted, 在每次checkpoint时会将autoinc_persisted存储到表mysql.innodb_dynamic_metadata中。

dict_table从dictionary cache淘汰时也会将autoinc_persisted持久化到mysql.innodb_dynamic_metadata中。



log_checkpointer
     -->log_consider_checkpoint
         -->dict_persist_to_dd_table_buffer
              -->dict_table_persist_to_dd_table_buffer_low
                   -->Persisters::write
                        -->AutoIncPersister::write
                        -->DDTableBuffer::replace

dict_table从dictionary cache淘汰时也会将autoinc_persisted持久化到mysql.innodb_dynamic_metadata中。

crash重启时，先从mysql.innodb_dynamic_metadata获取持久化的自增值，再从redo日志中读取最新的自增值, 参考MetadataRecover::parseMetadataLog，并通过MetadataRecover::apply更新到table->autoinc。

dict_table_remove_from_cache_low
    -->dict_table_persist_to_dd_table_buffer_low
        -->Persisters::write
            -->AutoIncPersister::write
            -->DDTableBuffer::replace

9. Upgrade

MySQL-8.0不支持跨版本升级，只能从5.7升级到8.0，不支持5.5,5.6直接升级到8.0。升级需要注意的问题：

原mysql5.7 mysql库下不能存在dictinary table同名的表
不支持老版本(5.6之前）的数据类型decimal，varchar, data/datetime/timestamp，通过check table xxx for upgrade可以检测
non-native 分区表不支持
不支持5.0之前的trigger，5.0之前的trigger没有definer
foreign key constraint name 不能超过64字节
view的column不能超过255 chars
enum 类型不能超过255 chars.
frm需与InnoDB系统表一致
一些空间函数如PointFromText需修改为ST_POINTFROMTEXT

10. 参考信息

https://github.com/mysql/mysql-server
https://dev.mysql.com/doc/refman/8.0/en/data-dictionary.html
https://dev.mysql.com/doc/refman/8.0/en/system-schema.html
https://mysqlserverteam.com/upgrading-to-mysql-8-0-here-is-what-you-need-to-know/
http://mysqlserverteam.com/mysql-8-0-improvements-to-information_schema/
https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html
https://mysqlserverteam.com/bootstrapping-the-transactional-data-dictionary/
https://www.slideshare.net/StleDeraas/dd-and-atomic-ddl-pl17-dublin

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-04-25，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server