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

from: Alibaba Cloud Database 2016-10-07 7558

introduction: 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.
+ Follow to continue viewing

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.


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

$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:



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). 很显然后者要慢一点。


该选项也会影响到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.


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

// 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 {

data dictionary Cache management class:

dd::cache {

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}'

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::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::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::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::Sdi_updater::operator()
    |--> dd::update_sdi
        |--> dd::sdi_file::store
            |--> write_sdi_file
*但奇怪的是,更新后很快就删除了 ?? (8.0.0版本,why ??)

-- 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:

            |--> 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

|--> dd::drop_schema
    |--> dd::cache::Dictionary_client::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:

|--> create_table_impl
    |--> rea_create_table

-- Insert a new record in dd ( dd::create_table--&gt; dd::create_dd_user_table)

// 根据建表语句初始化`dd::Table` 对象,包括表的列定义,各个属性和选项,索引定义
// 存到系统表中
|-->  dd::cache::Dictionary_client::store<dd::Table> 
            // 先插入到mysql/tables系统表中
            // 再插入到其他系统表中,如"mysql/columns", 
                |--> 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::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:

                // 先看schema是否存在,并从系统表`mysql/schemata`载入内存cache中
                    |--> 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>
                // 再获取表的定义并从系统表mysql/tables载入
                        |-->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
                                        // 获取表上的属性信息
                                                    // 从mysql/columns系统表获得列信息
                                                    // 从mysql/indexs系统表获得索引信息
                                                    // 从mysql/foreign_keys获得外键信息
                                                    // 从mysql/table_partitions获得分区信息

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:

SQL storage relational database MySQL API
mysql error socket var official data storage new mysql trigger data storage support data storage and computing
developer Community&gt; apsaradb
Please read this disclaimer carefully before you start to use the service. By using the service, you acknowledge that you have agreed to and accepted the content of this disclaimer in full. You may choose not to use the service if you do not agree to this disclaimer. This document is automatically generated based on public content on the Internet captured by Machine Learning Platform for AI. The copyright of the information in this document, such as web pages, images, and data, belongs to their respective author and publisher. Such automatically generated content does not reflect the views or opinions of Alibaba Cloud. It is your responsibility to determine the legality, accuracy, authenticity, practicality, and completeness of the content. We recommend that you consult a professional if you have any doubt in this regard. Alibaba Cloud accepts no responsibility for any consequences on account of your use of the content without verification. If you have feedback or you find that this document uses some content in which you have rights and interests, please contact us through this link: We will handle the matter according to relevant regulations.
Selected, One-Stop Store for Enterprise Applications
Support various scenarios to meet companies' needs at different stages of development

Start Building Today with a Free Trial to 50+ Products

Learn and experience the power of Alibaba Cloud.

Sign Up Now