Shulou(Shulou.com)06/01 Report--

This article mainly tells you some knowledge of innodb storage engine in mysql5.7. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on some knowledge of innodb storage engine in mysql5.7 can bring you some practical help.

First, a preliminary study of innodb:

1. MySQL log file:

①: the slow.log file records the slow query log. When the execution time of a statement exceeds the value specified in the configuration parameter long_query_time, this statement is recorded in this file.

②: the error.log file records some errors or warnings when the system is started or running, which is set by configuring the parameter log_error

③: the general.log file records all statements executed on the database, and is often used to track problems, but can affect performance a little, so it is generally not opened, only occasionally when debugging. (if the QPS is very high, this file may be very large and difficult to handle. It is generally not recommended to open it.)

2. Under the datadir directory of the MySQL system, there is a directory called MySQL:

This directory is actually some system tables of the MySQL database, such as permissions, users, etc., but these tables are all tables of the MyISAM storage engine

3. Default 3 database instances: information_schema performance_schema sys

①: information_schema:

Information_schema database, which comes with MySQL, is an information database that stores information about all other databases maintained by MySQL CVM, such as database names, database tables, data types and access permissions of table columns, etc. There are several read-only tables in the database, which are actually views, not basic tables

②: performance_schema:

Performance_schema database is added in MySQL5.5 and named performance_schema. It is designed for performance and is mainly used to collect performance parameters of database cloud servers. The database has the following functions:

Provide details of the process waiting, including locks, mutexes, and file information

Save the summary information of historical events to provide a detailed basis for judging the performance of MySQL CVM.

It is not easy to add or delete monitoring event points, and you can change the monitoring cycle of MySQL CVM at will.

③: sys:

Sys database is the first system information database added in MySQL5.7, which is similar to the dynamic view in Oracle. Through this database, we can quickly understand the metadata information of the system, and it is very convenient to make a lot of information in DBA hairstyle database, which is of great help in solving performance bottlenecks and automatic operation and maintenance. The library is the default in MySQL5.7 and can be imported manually in 5.6,

Note: this library combines information_schema and performance_schema in the form of views to query for easier-to-understand results, but only if the information source of the sys library itself depends on information_schema.

4. The innoDB storage engine includes two default log files

The log file size is set by the parameter innodb_log_file_size, and the number is set by the parameter innodb_log_files_in_group. After the size of these log files is set, they cannot be changed during operation. If you want to modify them, you need to close the database and modify them.

-- Note: the innodb storage engine also has a small file. The db.opt file stores some configuration information of the MySQL database, such as encoding and sorting information, which will be stored in this file if you specify some non-default parameters when creating the database.

2. Innodb data dictionary:

1. You can't see the data dictionary in MySQL. The reason is that MySQL is a plug-in database management system. Its structure is divided into two layers: server layer and storage engine layer. These two layers need to cooperate with each other to work amicably. But as a plug-in database management system, the storage engine layer can have multiple storage engine plug-ins, but there is only one server layer. The earliest default storage engine is MyISAM, which does not have a data dictionary, and all it has about table structure is a .frm file, so it is necessary for innodb to have this file for the server layer to recognize and manage it.

2. Innodb has four basic system tables

Used to store user-defined tables, columns, indexes, and index columns. These tables are: sys_tables sys_columns sys_indexes sys_fields

①: sys_ tables: used to store all tables with innodb as the storage engine, with each record corresponding to a defined table

Name: represents a table name

ID: ID number of the table

N_COLSL: indicates the number of columns in this table, and the number of columns specified in the table (4 bytes)

Type: indicates the storage type of this table, including record format, compression, and other information (4 bytes)

Space: indicates the ID number of the table space in which the table is located

(among them, mix_id mix_len cluster_name is not available for the time being.)

②: sys_columns: used to store information about all columns in all tables defined in innodb, each column corresponding to a record in this table

Table_id: indicates the ID number of the table to which this column belongs

Pos: indicates which column this column is in the table

Name: represents the column name of this column

Mtype: represents the primary data type of this column

Prtype: represents some of the exact data types of this column, which is a combined value, including the null flag, whether there is a symbol number flag, whether it is a binary string flag and indicates that the column is true varchar

Len: represents the data length of this column, but does not include the varchar type because this type stores the data length in the record

Prec: indicates the precision of this column data, but it doesn't seem to be used at the moment

③: sys_indexes: used to store index information for all tables in innodb. Each record corresponds to an index.

Table_id: indicates the ID number of the table to which this index belongs

ID: represents the index ID number of this index

Name: represents the index name of this index

N_fields: indicates the number of columns contained in this index

Type: indicates the type of index, including clustered index, unique index, etc.

Space: indicates the tablespace ID number in which the index data resides

Page_no: represents the root page of the B+ tree corresponding to this index

④: sys_fields: used to store index columns defined in all indexes, with one index column for each record

Index_id: the index in which this column is located

Pos: how many index columns is this column in an index?

Col_name: the column name of this index column

3. Dictionary table loading:

When innodb starts, if you are creating a new database, you need to initialize the library, so the first thing you need to do is to create a dictionary-managed B + tree and other information. After these initialization operations, load the four system tables in memory through the function dict_boot and read some other information.

4. Rowid management:

In innodb, records in a user table do not necessarily have a rowid column, and rowid is assigned to a table only if there is no primary key defined, that is, when rowid is needed as a clustered index column. On the other hand, the administrative allocation of rowid does not mean that a table has an exclusive ID space, but is global. All tables share this ID number.

3. Innodb data storage structure

1. The structure of tablespace files

Innodb storage engine imitates the storage structure of Oracle in storage design, and its data is managed according to table space. When you create a new database, the innodb storage engine initializes a tablespace file called ibdata1, which by default stores data for all tables, as well as system tables that we are familiar with but cannot see, such as sys_tables, sys_columns, sys_indexes, sys_fields, and so on. In addition, the rollback segment data used to ensure data integrity will be stored. Of course, in the new version of MySQL, the storage location of the rollback segment can be set by parameters.

The design of the innodb storage engine is flexible and can be set by the parameter innodb_file_per_table so that each table corresponds to its own separate tablespace file instead of being stored in a public ibdata1 file. Independent tablespace files store information such as Btree data, indexes and insert buffers of tables, while the rest of the information is stored in the default tablespace.

The content stored in this file is mainly B+ tree (index), a table can have multiple indexes, that is, in a file, multiple indexes can be stored, and if a table does not have an index, the one used to store data is called clustered index, that is to say, it is also an index. The final conclusion is that the ibd file stores all the index data of a table.

2. Paragraph:

Segment is the main organizational structure in tablespace files. It is a logical concept used to manage physical files and is the basic element of indexes, tables, and rollback segments. When creating an index (B+ tree), two segments are created at the same time, namely, the inner node segment and the leaf segment, the inner node segment is used to manage (store) the non-leaf (page) data of the B+ tree, and the leaf segment is used to manage (store) the data of the leaf node of the B+ tree; that is to say, in the process of increasing the amount of index data, all new storage space applications are applied from the concept of "segment". An index, including two segments, then the number of segments in a table is multiplied by 2. (a more vivid explanation: ibd files are made up of multiple segments, and no other space is separated from the management of segments. )

3. Clusters:

Innodb introduced the concept of cluster, which is called extent in the code.

A cluster is the basic element of a segment, and a segment is composed of several clusters. A cluster is a physically continuously allocated segment space, each segment will have at least one cluster, and a default cluster will be created when a segment is created. If one cluster is not enough to put down more data when storing data, a new cluster needs to be allocated from this segment to store the new data. The size of the space managed by a segment is unlimited and can be extended all the time, but the smallest unit of expansion is the cluster. The space size of the cluster is fixed, usually 64 pages; (the cluster is the actual physical storage space)

4. Page:

"Page" is the product of cluster subdivision, which is not only the component unit of the cluster, but also the smallest unit managed by the segment, the smallest unit of data file management, and of course, the smallest unit of space allocation in the file.

A cluster can include multiple pages (64 pages by default, each 16KB), and this number of pages is often referred to as "cluster size". These pages are managed by this cluster and are logically (page numbers are continuous from small to large) and physically continuous. When inserting data into the table, if a page has been written, the system allocates a new free page from the current cluster for processing, and if all 64 pages in the current cluster are allocated, the system allocates a new cluster from the segment of the current page, and then allocates a new page from the cluster to use. (to put it more simply: a tablespace file is divided into blocks of equal length, each block is a page, a page defaults to 16KB, and no space in a file exists without segment management.)

-Note: there is no space that does not exist in the form of a page.

5. Organizational structure of segments, clusters and pages:

A tablespace can have multiple files, each with its own number, and when you create a tablespace, there is at least one file, which is called "file 0". A file is cut into a block of equal length "default 16KB", which is usually called a page, so the entry managed by all the segment clusters in this table space is stored in the first page of "file 0" (page_no is 0). Then the data stored on this page is 16KB, but there is usually header information that takes up some space. The real management information data starts at the location where the page is offset to fil_page_data (38), which stores the description of the tablespace.

-Note:

①: table space control information includes: full cluster linked list, semi-full cluster linked list, free cluster linked list, and segment inode information also has these information; linked list in table space manages all clusters in the whole table space, including full cluster, semi-full cluster and free cluster, while segment iNode information manages full cluster, semi-full cluster and free cluster in its own segment.

②: in innodb, a cluster description page manages 16384 pages, the cluster size defaults to (fsp_extent_size) 64, and the size of the cluster descriptor is 40B, so the number of clusters that can be described in a cluster description page is (univ_page_size- header length) / 40, where univ_page_size=16384B represents the page size. In a table space, the storage of the cluster description page is that there is a cluster description page every 16384 pages, so the cluster description page only needs to describe 16384 pages.

③: the so-called btr, in innodb, represents the processing of the B+ tree. Whether it begins with a function or a file, it is related processing, which is short for Btree.

This is the end of some knowledge of innodb storage engine in mysql5.7. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.