InnoDB data dictionary -- Shi Hongbao

Research on lnmpr source code 2020-11-09 22:19:32
innodb data dictionary shi hongbao


One . Basic knowledge of

  1. This article uses Mysql edition : 8.0.12-debug
  2. This article uses Linux command : xxd
  3. This paper needs to use the knowledge points : Mysql Data page storage , You can see https://segmentfault.com/a/1190000037436803

1.1 problem

  1. Data Dictironary What is it? ?
  • Data Dictironary(DD, The data dictionary ) It's a collection of database objects , Such as table 、 View 、 Index, etc. , It can be regarded as meta information of database . let me put it another way , The data dictionary stores information about the table structure , The columns that each table has , Index of the table, etc .

2. What is the system table ? How is it different from the table you created ?

  • There are many system tables , Common are mysql.schemata,mysql.tables, mysql.indexes
  • The meta information of the table we created is put into the system table
  • In memory , This meta information is provided to external users in the form of objects , for instance , Create a table , The data dictionary object of this table will be created in memory , System tables and the tables we create will have their own data dictionary objects
  • We can think of the meta information of the system table stored in its own data dictionary object , This information will be serialized to disk mysql.ibd In file

3.DD Where to store it ?

This is for Mysql 8 The data dictionary ,

  • Data dictionary information needs to be persistent , Stored in mysql.ibd In file , Using dedicated table spaces id
  • In each separate table space , Also backup a copy of this table space related dd Object serialization information
  • SYSTEM tablespace , That is to say ibdata1 In file , There's no storage dd Information about the object

1.2 DD Storage

Mysql 8 Before , The storage structure of data dictionary is shown in the figure below ,

 The data dictionary

As you can see from the diagram , DD Information is stored in multiple places ,

  • Part of the data is stored in a file
  • Part of the data is stored in mysql In the system table
  • InnoDB The system table also stores part of the data

This storage method has the following problems :

  1. Data is stored in multiple places , Difficult to maintain management
  2. MyISAM System tables are vulnerable to damage
  3. Atomic operations are not supported

Mysql 8 The data dictionary has been redesigned , And use InnoDB Storage ,

 The data dictionary -Mysql 8

  • Delete all the files that used to store the data dictionary , Put it in the data dictionary table space
  • For the original use MyISAM Stored system table , Replace all with InnoDB Storage , To realize atoms DDL Provides the possibility of

1.3 How to view the data dictionary

  1. Connect through the command line Mysql Server view
SET SESSION debug='+d,skip_dd_table_access_check';
SELECT name, schema_id, hidden, type FROM mysql.tables where schema_id=1 AND hidden='System';

You can see DD A lot of system tables are stored in ,

+------------------------------+-----------+--------+------------+
| name | schema_id | hidden | type |
+------------------------------+-----------+--------+------------+
| catalogs | 1 | System | BASE TABLE |
| character_sets | 1 | System | BASE TABLE |
| collations | 1 | System | BASE TABLE |
| columns | 1 | System | BASE TABLE |
| dd_properties | 1 | System | BASE TABLE |
| events | 1 | System | BASE TABLE |
| index_stats | 1 | System | BASE TABLE |
| indexes | 1 | System | BASE TABLE |
| schemata | 1 | System | BASE TABLE |
| tables | 1 | System | BASE TABLE |
| tablespace_files | 1 | System | BASE TABLE |
| tablespaces | 1 | System | BASE TABLE |
+------------------------------+-----------+--------+------------+
  • Here we list only a few , InnoDB There are many other system tables stored
  • These system tables store various metadata , columns The column information of the table is stored , indexes The index information of the table is stored , schemata It stores information from the database .

2. adopt ibd2sdi Tool View

ibd2sdi test.ibd
  • tips: Can pass utilities/ibd2sdi.cc file , See the deserialization process , And then we can see that sdi Page storage structure

1.4 Serialized Dictionary Information(SDI)

  1. SDI What is it? ?
  • SDI, Dictionary serialization information , That is, the data after serializing the objects in the data dictionary

2.SDI How to organize ?

  • DD Contains a lot of data dictionary objects , Of these objects SDI The data to B+ The way trees are organized
  • SDI Data is basically stored after compression , Compression uses zlib

3.SDI give an example

{
"mysqld_version_id":80012,
"dd_version":80012,
"sdi_version":1,
"dd_object_type":"Table",
"dd_object":{
"name":"x",
"columns":[
{
"name":"id",
"type":4,
...
},
{
"name":"DB_TRX_ID",
"type":10,
...
},
{
"name":"DB_ROLL_PTR",
"type":9,
...
}
],
...
"indexes":[
{
"name":"PRIMARY",
"hidden":false,
...
}
],
...
}
}
  • You can see , SDI The meta information of this table is stored in , For example, the column composition of this table , Index information, etc .

Two . SDI Storage page example

In this paper , We said that in a separate table space for each table , Store the table related DD Object serialization information . In this section , We learn how to view SDI Information .

  1. Let's start with a table
CREATE TABLE `x` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
  • The library where the table is located is hbshi

2. View the separate table space file for this table , adopt xxd x.ibd x.txt Look at the data page of this table space , Here we mainly look at chapter 4 page , this 1 Page stores the data dictionary serialization information related to this table ,

000c000: 723e 3b3c 0000 0003 ffff ffff ffff ffff r>;<............
000c010: 0000 0000 0128 06fc 45bd 0000 0000 0000 .....(..E.......
000c020: 0000 0000 0004 0002 04d2 8004 0000 0000 ................
000c030: 0172 0001 0001 0002 0000 0000 0000 0000 .r..............
000c040: 0000 ffff ffff ffff ffff 0000 0004 0000 ................
000c050: 0002 00f2 0000 0004 0000 0002 0032 0100 .............2..
000c060: 0201 0f69 6e66 696d 756d 0003 000b 0000 ...infimum......
000c070: 7375 7072 656d 756d cb80 0000 10ff f100 supremum........
000c080: 0000 0200 0000 0000 0000 0900 0000 0016 ................
........
........
000fff0: 0000 0000 0070 0063 723e 3b3c 0128 06fc .....p.cr>;<.(..

For a table space , There may be multiple data dictionary objects , These objects will be serialized with B+ The way trees are organized , Let's take a look at the specific storage structure ,

Overall page storage structure :

  • Minimum record : Recording head [00c05e, 00c062], Specific data [00c063, 00c06a]
  • Maximum record : Recording head [00c06b, 00c06f], Specific data [00c070, 00c077]
  • The first 1 Bar record : 2 The content length of bytes [00c078, 00c079]; 5 The header of the byte [00c07a, 00c07e]; Record the content [00c07f, 00c16a].
  • The first 2 Bar record : 2 The content length of bytes [00c16b, 00c16c], 5 The header of the byte [00c16d, 00c171], Recorded data [00c172, 00c4d1]

Let's look at the specific storage of each record :

  • length : 1 perhaps 2 Byte length field , explain The length of the content
  • 5 Byte record header
  • 33 Byte record description
  • The details of the record , Record the front of the head 1 perhaps 2 A byte is the length of the record

Note that :

  • 4 Bytes of type information
  • 8 Bytes of id
  • 6 Byte transactions id
  • 7 Byte rollback pointer
  • 4 Uncompressed length of bytes
  • 4 Compressed length of bytes

The storage structure is shown in the figure below ,
SDI Storage page

3.SDI Record the traversal process

  • Calculate the number from the minimum record 1 Bar record , 00c063 + 010f = 00c172
  • Computation first 2 Bar record , 00c172 + ff0d = 00c07f
  • Computation first 3 Bar record : 00c07f + fff1 = 00c070

Be careful : The cursor starts with the contents of each record , For example, the starting position of the minimum record is 00c063, instead of 00c05e. Calculate the next record position , It's also where the content starts .

4. How to view SDI Compressed data

  • First, through bin/ibd2sdi Tools , take ibd In the document sdi Information printing
  • From the information in print , Choose one of them
  • Extract from the selected item object Field , Compress it , Here we use python The script is compressed , Compression mode uses zlib
#!/usr/bin/env python
# -*- coding=utf-8 -*-
#
import os
import sys
import json
import time
import datetime
import hashlib
import zlib
reload(sys)
sys.setdefaultencoding("utf-8")
def help():
f = open("./test.data")
l = f.readline()
d = zlib.compress(l)
f2 = open("./tmp.data", 'w+')
f2.write(d)
help()
  • Compare the compressed data with the data stored in the table space

3、 ... and . DD Table space

  1. InnoDB Common table spaces
  • Table space occupation 4 Bytes , The maximum value is 0xffff ffff, This table space is invalid , Not used
  • 0xffff fffe yes data dicitonary Table space , That is to say mysql.ibd
  • 0xffff fffd It's a temporary table space , That is to say ibtmp1.ibd
  • [0xffff fffc, 0xffff fff0] It's for the log
  • [0xffff ffef, 0xffff ff70] yes undo log
  • [0x0000 0002, 0xffff ff6f] Is a normal table space
  • 1 yes sys/sys_config Table space
  • 0 It's the system table space , That is to say ibdata1

2.DD Table space storage

image

  • DD The storage structure of a table space is the same as that of a general independent table space
  • The first 4 page (page 3) yes SDI Stored root page, SDI With B+ Tree organization
  • except SDI Out of information , Specific metadata tables are also stored in this table space , The storage method is the same as the general table .

Four . Summary and reflection

  1. This paper mainly introduces Mysql The concept of data dictionary and the storage of data dictionary , Here's a brief summary of ,
  • System tables and the tables we created , Meta information is stored in a data dictionary
  • The meta information of the tables we created ourselves is stored in the system tables , It will also be serialized into its own table space
  • Meta information of system tables , Stored in the data dictionary object of the system table , This information will be serialized to mysql.ibd In file
  • SDI Information to B+ The way trees are organized

2. reflection

  • DD Is a collection of database objects , Mysql server Layers and InnoDB Layers need this information , How they operate ?
  • DDL How is atomicity realized ?

5、 ... and . Reference resources

  1. https://dev.mysql.com/doc/ref...
版权声明
本文为[Research on lnmpr source code]所创,转载请带上原文链接,感谢

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云