MySQL 8.0 New features New data dictionary early adopters-Alibaba Cloud Developer Community

as we all know, MySQL adopts the architecture mode of unified Server layer and different underlying engine plug-ins. frm files are created for each table at the Server layer to store metadata information related to table definitions. However, some engines (such as InnoDB) also store metadata, which not only generates metadata redundancy, but also manages the Server layer and the engine layer separately. When performing DDL operations, it is difficult to implement crash-safe, let alone make DDL transactional.

To solve these problems (especially DDL cannot implement atomic), FRM files and other server-layer metadata files (frm, par, trn, trg, isl,db) have been canceled since MySQL8.0. opt), all metadata is stored by the InnoDB engine, and other system tables such as permission tables are also used by the InnoDB engine.

This article is the first time that I have learned about this content, so I won't go too deep. Because there are too many changes involved, I will expand them one by one later.

All testing and code related parts in this article are based on MySQL8.0.0. Since this is the first development version of version 8.0, future behavior changes are not excluded.

Test

first, create a new database and create two tables under the database to start our test.

mysql> CREATE DATABASE sbtest;
Query OK, 1 row affected (0.00 sec)

mysql> USE sbtest
Database changed
mysql> CREATE TABLE t1 (a int primary key);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2 (a int primary key, b int);
Query OK, 0 rows affected (0.00 sec)

$ls -lh /u01/my80/data/sbtest
total 256K
-rw-r----- 1 yinfeng.zwx users 128K Oct  5 19:44 t1.ibd
-rw-r----- 1 yinfeng.zwx users 128K Oct  5 19:44 t2.ibd

$ls  /u01/my80/data/sbtest_9.SDI
/u01/my80/data/sbtest_9.SDI

$cat /u01/my80/data/sbtest_9.SDI
{
    "sdi_version": 1,
    "dd_version": 1,
    "dd_object_type": "Schema",
    "dd_object": {
        "name": "sbtest",
        "default_collation_id": 33,
        "created": 0,
        "last_altered": 0
    }
}

You can see that there are only ibd files and no frm files in the Library Directory. However, an SDI file is generated in the data directory to describe the information of the sbtest Library.

Let's take a look at creating a MYISAM engine table:

mysql> create database my;
Query OK, 1 row affected (0.00 sec)

mysql> use my
Database changed
mysql> create table t1 (a int, b varchar(320)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

$ls my/
t1_435.SDI  t1.MYD  t1.MYI

{
    "sdi_version": 1,
    "dd_version": 1,
    "dd_object_type": "Table",
    "dd_object": {
        "name": "t1",
        "mysql_version_id": 80000,
        "created": 20161005201935,
        "last_altered": 20161005201935,
        "options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
        "columns": [
            {
                "name": "a",
                "type": 4,
                "is_nullable": true,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": false,
                "ordinal_position": 1,
                "char_length": 11,
                "numeric_precision": 10,
                "numeric_scale": 0,
                "datetime_precision": 0,
                "has_no_default": false,
                "default_value_null": true,
                "default_value": "",
                "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": 33
            },
            {
                "name": "b",
                "type": 16,
                "is_nullable": true,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": false,
                "ordinal_position": 2,
                "char_length": 960,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "datetime_precision": 0,
                "has_no_default": false,
                "default_value_null": true,
                "default_value": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "interval_count=0;",
                "se_private_data": "",
                "column_key": 1,
                "column_type_utf8": "varchar(320)",
                "elements": [],
                "collation_id": 33
            }
        ],
        "schema_ref": "my",
        "hidden": false,
        "se_private_id": 18446744073709551615,
        "engine": "MyISAM",
        "comment": "",
        "se_private_data": "",
        "row_format": 2,
        "partition_type": 0,
        "partition_expression": "",
        "default_partitioning": 0,
        "subpartition_type": 0,
        "subpartition_expression": "",
        "default_subpartitioning": 0,
        "indexes": [],
        "foreign_keys": [],
        "partitions": [],
        "collation_id": 33
    }
}

in this example, a MyISAM table t1 is created, and the corresponding SDI file is created. The detailed information of the table is recorded in JSON format. According to the description of the official document, this document exists for a function that has not been fully implemented.

New Information Schema definitions

some new tables are redesigned using View, mainly including these tables:

CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
KEY_COLUMN_USAGE
SCHEMATA
STATISTICS
TABLES
TABLE_CONSTRAINTS
VIEWS

#例如SCHEMATA

mysql> show create table information_schema.schemata\G
*************************** 1. row ***************************
                View: SCHEMATA
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`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.01 sec)

in other words, although the DD system table is hidden and invisible, you can still obtain most of the information through the view. In fact, this method greatly speeds up the query of the IS table. After the query IS converted into a physical table, you do not need to create a temporary table for the query of each table (the operations of the temporary table include creating frm at the server layer, obtaining data at the engine layer, or global data that needs lock protection). In addition, the optimizer can also select a better execution plan for querying tables (for example, using indexes on system tables).

The official test shows that the query Performance of the IS table IS greatly improved. The official blog portal IS MySQL 8.0: Improvements to Information_schemaMySQL 8.0: Scaling and Performance of INFORMATION_SCHEMA

new option: information_schema_stats: CACHED | LATEST

目前表的元数据信息缓存在statistics及tables表中以加速对IS表的查询性能。你可以通过参数information_schema_stats来直接读取已经缓存到内存的数据(cached)，还是从存储引擎中获取最新的数据(latest). 很显然后者要慢一点。

而从is库下，可以看到对应两种表:TABLES及TABLES_DYNAMIC, 以及STATISTICS及STATISTICS_DYNAMIC。当被设置为LATEST时，就会去从**_DYNAMIC表中去读取数据。

该选项也会影响到SHOW TABLES等语句的行为。

Data Dictionary Cache

after the structure of the data dictionary changes greatly, the memory data dictionary Cache is also changed accordingly,

mysql> show variables like '%defin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| schema_definition_cache         | 256   |
| stored_program_definition_cache | 256   |
| table_definition_cache          | 1400  |
| tablespace_definition_cache     | 256   |
+---------------------------------+-------+
4 rows in set (0.00 sec)

tablespace_defintion_cache: the size of the tablespace cache, which stores the definition of tablespace. A tablespace may contain multiple tables.

Table_defintion_cache:

stored_program_defintion_cache: the definition cache of stored procedure & & function.

Schema_defintion_cache: the cache defined by the storage schema.

hardcode character set cache:

character set definition cache partition: Stores character set definition objects and has a hardcoded object limit of 256.
collation definition cache partition: Stores collation definition objects and has a hardcoded object limit of 256.

system table changes

• convert permission-related tables to InnoDB engine

// Include user, db, tables_priv, columns_priv, procs_priv, and proxies_priv.

// Official blog introduction

• convert a func table to an InnoDB transaction table

// Based on this change, the function of the operation (for example CREATE FUNCTION OR DROP FUNCTION, OR user-defined UDF) MAY cause a implicit commit

• the routine and event tables in the mysql database are no longer used. These information are stored in the new DD table and are invisible to the mysql database.
• Foreign key system table

// Use two invisible system tables foreign_keys and foreign_key_column_usage to store foreign key information // because these two system tables are invisible, you need to obtain foreign key information by using the REFERENTIAL_CONSTRAINTS and KEY_COLUMN_USAGE tables in the IS database // The imported foreign key cannot exceed 64 characters in name.

Source code overview

back to the source code directory, a large number of New code files are introduced to manage New DD from the server layer. A series of unified APIs are defined. The code is stored in the SQL/dd directory, functions and classes are defined under namespace dd

different classes and their inheritance relationships are defined for different metadata:

namespace dd {
    Weak_object
        Entity_object   
            Dictionary_object
                Tablespace
                Schema
                Event
                Routine
                    Function
                    Procedure
                Charset
                Collation
                Abstract_table
                    Table
                Spatial_reference_system
                Index_stat
                    View
                Table_stat
            Partition
            Trigger
            Index
            Foreign_key
            Parameter
            Column
        Partition_index 
        Partition_value
        View_routine
        View_table
        Tablespace_file
        Foreign_key_element
        Index_element
        Column_type_element
        Parameter_type_element
    Object_table
        Dictionary_object_table
    Object_type
    Object_table_definition   
    
}

data dictionary Cache management class:

dd::cache {
    dd::cache::Dictionary_client
    Object_registry
    Element_map
    Multi_map_base
        Local_multi_map
        Shared_multi_map
    
    Cache_element
    Free_list
    Shared_dictionary_cache
    
    Storage_adapter
}

the mysql database stores system tables. However, by using the show tables command, we can only see 37 tables, while the number of ibd files in the mysql directory far exceeds 37, this means that some system tables are invisible to users. These tables are also used to manage core data dictionary information. The reason why they are invisible is to avoid improper operations by users. (Of course, this behavior changes in the future are not excluded), about the access to these tables, in the directory sql/dd/impl/tables/in the interface definition, these hidden table include:

$grep 'std::string s_table_name' sql/dd/impl/tables/* | awk '{ print $4}'
s_table_name("catalogs");
s_table_name("character_sets");
s_table_name("collations");
s_table_name("columns");
s_table_name("column_type_elements");
s_table_name("events");
s_table_name("foreign_key_column_usage");
s_table_name("foreign_keys");
s_table_name("index_column_usage");
s_table_name("indexes");
s_table_name("index_partitions");
s_table_name("index_stats");
s_table_name("parameters");
s_table_name("parameter_type_elements");
s_table_name("routines");
s_table_name("schemata");
s_table_name("st_spatial_reference_systems");
s_table_name("table_partitions");
s_table_name("table_partition_values");
s_table_name("tables");
s_table_name("tablespace_files");
s_table_name("tablespaces");
s_table_name("table_stats");
s_table_name("triggers");
s_table_name("version");
s_table_name("view_routine_usage");
s_table_name("view_table_usage");

take common operations on a table as an example to see how some of the code is called. (Due to New DD code changed related worklog there are dozens, the author through the test + code debug way the first step is familiar with the code, record comparison messy)

database-level operations

1. create database

mysql> create database db1;
Query OK, 1 row affected (2.87 sec)

mysql> create database db2;
Query OK, 1 row affected (3.05 sec)

entry function: mysql_create_db

-- Create a database directory -- Build a binlog and write it to a file -- call the DD API interface: dd::create_schema

* 构建对象dd::Schema
* 存储到数据词典中mysql.schemata表中，相关堆栈:

dd::create_schema
|--> dd::cache::Dictionary_client::store<dd::Schema>
    |--> dd::cache::Storage_adapter::store<dd::Schema>
        |--> dd::Weak_object_impl::store
            |--> dd::Raw_new_record::insert
            

Note: schemata表对用户是不可见的
    mysql> desc schemata;
    ERROR 3554 (HY000): Access to system table 'mysql.schemata' is rejected.

* 创建并存储当前库的信息到SDI文件中,sdi文件命名以库名为前缀，堆栈如下
    

dd::create_schema
|--> dd::store_sdi
    |--> dd::sdi_file::store
        |--> write_sdi_file

    
* 成功则commit，失败则rollback

1. modify database

mysql> alter database db1 default charset gbk;
Query OK, 1 row affected (2 min 17.54 sec)

entry function: mysql_alter_db

-- Call the DD API API: dd::alter_schema

* 更新数据词典信息，相关堆栈:

dd::alter_schema
|--> dd::cache::Dictionary_client::update<dd::Schema>
    |--> dd::cache::Dictionary_client::store<dd::Schema>
        |--> dd::cache::Storage_adapter::store<dd::Schema> 
            |--> dd::Weak_object_impl::store
                |--> dd::Raw_record::update

*更新sdi文件, 相关堆栈

dd::alter_schema
|--> dd::Sdi_updater::operator()
    |--> dd::update_sdi
        |--> dd::sdi_file::store
            |--> write_sdi_file

*但奇怪的是，更新后很快就删除了 ?? (8.0.0版本，why ??)
看起来sdi文件的序列号没有递增，导致文件被快速删除了，实际上的目的是创建一个新的文件，写入新的数据，然后将老的SDI删掉
ref: http://bugs.mysql.com/bug.php?id=83281

-- Write Binlog

1. show databases

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (1.40 sec)

when this command is executed, an SQL conversion is actually performed to convert it into a standard query statement. The stack is as follows:

dispatch_command
|-->mysql_parse
    |-->parse_sql
        |-->MYSQLparse
            |--> dd::info_schema::build_show_databases_query

the SQL statement after conversion is similar:

       SELECT SCHEMA_NAME as `Database`,
          FROM information_schema.schemata;

because it is directly read from the system table, this means that creating a folder under the Data Directory will not be treated as a new database directory.

1. Delete database

mysql> drop database db2;
Query OK, 0 rows affected (1 min 1.86 sec)

-- Delete related files

-- Delete the records in the system table mysql/schemata

mysql_rm_db
|--> dd::drop_schema
    |--> dd::cache::Dictionary_client::drop<dd::Schema>
        |-->dd::cache::Storage_adapter::drop<dd::Schema>
            |--> dd::Weak_object_impl::drop
                |--> dd::Raw_record::drop
                    |--> handler::ha_delete_row

table-level operations

1. create a table

mysql> create table t1 (a int primary key, b int, c int, key(b));
Query OK, 0 rows affected (7 min 12.29 sec)

entry function:

mysql_create_table_no_lock
|--> create_table_impl
    |--> rea_create_table

-- Insert a new record in dd ( dd::create_table--> dd::create_dd_user_table)

// 根据建表语句初始化`dd::Table` 对象，包括表的列定义，各个属性和选项，索引定义
// 存到系统表中

dd::create_dd_user_table
|-->  dd::cache::Dictionary_client::store<dd::Table> 
    |-->dd::cache::Storage_adapter::store<dd::Table>
        |-->dd::Weak_object_impl::store
            // 先插入到mysql/tables系统表中
            
            // 再插入到其他系统表中，如"mysql/columns", 
            |-->dd::Table_impl::store_children
                |--> dd::Abstract_table_impl::store_children      // mysql/columns
                    |--> dd::Collection<dd::Column*>::store_items
                        |--> Weak_object_impl::store
            |-->dd::Collection<dd::Index*>::store_items           // mysql/indexes
                |--> dd::Weak_object_impl::store
                    |-->dd::Index_impl::store_children
                        |--> dd::Collection<dd::Index_element*>::store_items    // mysql/index_column_usage

-- Then create an engine file

1. Open table

-- Restart the instance and then open the table. The Table definition is loaded into the memory for the first time. You need to access the system table to obtain the table definition:

open_and_process_table
|-->open_table
    |-->get_table_share_with_discover
        |-->get_table_share
            |-->open_table_def
                // 先看schema是否存在，并从系统表`mysql/schemata`载入内存cache中
                |-->dd::schema_exists   
                    |--> dd::cache::Dictionary_client::acquire<dd::Schema> 
                        |-->dd::cache::Dictionary_client::acquire<dd::Item_name_key, dd::Schema>
                            |-->dd::cache::Shared_dictionary_cache::get<dd::Item_name_key, dd::Schema>
                                |-->dd::cache::Shared_dictionary_cache::get_uncached<dd::Item_name_key, dd::Schema>
                                    |-->dd::cache::Storage_adapter::get<dd::Item_name_key, dd::Schema>
                                        |-->dd::Raw_table::find_record
                // 再获取表的定义并从系统表mysql/tables载入
                |-->dd::abstract_table_type
                    |-->dd::cache::Dictionary_client::acquire<dd::Abstract_table>
                        |-->dd::cache::Dictionary_client::acquire<dd::Item_name_key, dd::Abstract_table>
                            |-->dd::cache::Shared_dictionary_cache::get<dd::Item_name_key, dd::Abstract_table>
                                |-->dd::cache::Shared_dictionary_cache::get_uncached<dd::Item_name_key, dd::Abstract_table>
                                    |-->dd::cache::Storage_adapter::get<dd::Item_name_key, dd::Abstract_table>
                                        |--> dd::Raw_table::find_record
                                        // 获取表上的属性信息
                                        |-->Dictionary_object_table_impl::restore_object_from_record
                                            |-->dd::Table_impl::restore_children
                                                |-->dd::Abstract_table_impl::restore_children
                                                    // 从mysql/columns系统表获得列信息
                                                    |-->dd::Collection<dd::Column*>::restore_items<dd::Abstract_table_impl>
                                                    // 从mysql/indexs系统表获得索引信息
                                                    |-->dd::Collection<dd::Index*>::restore_items<dd::Table_impl>
                                                    //从mysql/index_column_usage获取索引信息
                                                    |-->dd::Collection<dd::Index_element*>::restore_items<dd::Index_impl>
                                                    // 从mysql/foreign_keys获得外键信息
                                                    |-->dd::Collection<dd::Foreign_key*>::restore_items<dd::Table_impl>
                                                    // 从mysql/table_partitions获得分区信息
                                                    |-->dd::Collection<dd::Partition*>::restore_items<dd::Table_impl>
                                                    //从"mysql/triggers获得触发器信息
                                                    |-->dd::Collection<dd::Trigger*>::restore_items<dd::Table_impl>

related WorkLog

WL#6379: Schema definitions for new DDWL#6380: Formulate framework for API for DDWL#6381: Handler API changes for new dictionaryWL#6382: Define and Implement API for Table objectsWL#6383: Define and Implement API for TriggersWL#6384: Define and Implement API for Stored RoutinesWL#6385: Define and Implement API for SchemaWL#6387: Define and Implement API for TablespacesWL#6388: Define and Implement API for EventsWL#6389: Define and Implement API for ViewsWL#6390: Use new DD API for handling non-partitioned tablesWL#6391: Protect Data Dictionary tablesWL#6392: Upgrade to Transactional Data DictionaryWL#6394: Bootstrap code for new DDWL#6416: InnoDB: Remove the use of *.isl filesWL#6599: New Data Dictionary and I _S integrationWL#6929: Move FOREIGN KEY constraints to the global data dictionaryWL#7053: InnoDB: Provide storage for tablespace dictionaryWL#7066: External tool to extract InnoDB tablespace dictionary informationWL#7069: Provide data dictionary information in serialized formWL#7167: Change DDL to update rows for view columns in DD.COLUMNS and other dependent values.WL#7284: Implement common code for different DD APIsWL#7464: InnoDB: provide a way to do non-locking readsWL#7488: InnoDB startup refactoringWL#7630: Define and Implement API for Table Partition InfoWL#7771: Make sure errors are properly handled in DD APIWL#7784: Store temporary table metadata in memoryWL#7836: Use new DD API for handling partitioned tablesWL#7896: Use DD API to work with triggersWL#7897: Use DD API to work with stored routinesWL#7898: Use DD API to work with eventsWL#7907: Runtime: Use non-locking reads for DD tables under I _S view.WL#8150: Dictionary object cacheWL#8433: Separate DD commands from regular SQL queries in the parser grammarWL#8980: Move UDF table from MyISAM to Transactional StorageWL#9045: Make user management DDLs atomic

Official Blog: https://mysqlserverteam.com/mysql-server-bootstrapping-and-dictionary-initialization/https://mysqlserverteam.com/bootstrapping-the-transactional-data-dictionary/