Detailed explanation of the index and storage structure of the MySQL InnoDB engine

Preface

In databases such as Oracle and SQL Server, there is only one storage engine, and all data storage management mechanisms are the same.

The MySQL database provides a variety of storage engines. Users can choose different storage engines for data tables according to different requirements, or they can write their own storage engines according to their needs.

Differences between MySQL's main storage engines

The default storage engine of MySQL is MyISAM. Other commonly used ones are InnoDB, MERGE, MEMORY (HEAP), etc.

Several major storage engines

MyISAM manages non-transactional tables, providing high-speed storage and retrieval, as well as full-text search capabilities.

MyISAM is the default storage engine for MySQL. When create creates a new table, if the storage engine of the new table is not specified, MyISAM is used by default. Each MyISAM is stored in three files on disk. The file names are the same as the table names, with extensions of .frm (to store table definitions), .MYD (MYData, to store data), and .MYI (MYIndex, to store indexes). Data files and index files can be placed in different directories to evenly distribute IO and achieve faster speed.

The InnoDB storage engine is used for transaction processing applications and has many features, including ACID transaction support, which provides transaction safety with commit, rollback, and crash recovery capabilities. However, compared to the MyISAM storage engine, InnoDB writes less efficiently and takes up more disk space to retain data and indexes.

Memory stores all data in memory and can be used in temporary tables to provide extremely fast access in environments where quick lookup of references and other similar data is required. Memory uses hash indexes, so data access speed is very fast.

Merge allows a MySQL DBA or developer to logically group together a series of equivalent MyISAM tables and reference them as 1 object. It is very suitable for VLDB environments such as data warehousing.

Horizontal comparison of different storage engines

View and configure storage engine operations

1. Use the show engines; command to display the storage engines supported by the current database;

2. To view the table definition structure and other information, you can use the following commands:

Desc[ribe] tablename; //View the structure of the data table Show create table tablename; //Show the table creation statement to view the ENGINE specified when creating the table
show table status like 'tablename'\G displays the current status value of the table

3. Set or modify the storage engine of the table

The basic syntax for setting the storage engine when creating a database table is:

Create table tableName(
columnName (column name 1) type (data type) attri (attribute setting),
columnName (column name 2) type (data type) attri (attribute setting),
……..) engine = engineName

To modify the storage engine, you can use the command

Alter table tableName engine =engineName

You do not have to use the same storage engine for the entire server or solution; you can use a different storage engine for each table in the solution.

InnoDB storage structure

InnoDB uses a page storage structure. The following is the table space structure diagram of InnoDB:

The page storage format is shown in the following figure:

The storage of a page consists of the following parts:

1. Page Header: records the control information of the page, occupying a total of 150 bytes, including the left and right sibling page pointers of the page, page space usage, etc. The detailed description of the page header will be described in the next article.
2. Minimum virtual record and maximum virtual record: two virtual records stored in fixed locations, which do not store data themselves. The smallest virtual record is smaller than any record, and the largest virtual record is larger than any record.
3. Record heap: refers to the orange-yellow part of the above figure. Indicates the record space where the page has been allocated, and is also the actual storage area for index data. There are two types of record heaps: valid records and deleted records. Valid records are records that are normally used by the index, while deleted records are records that have been deleted from the index and are no longer in use, as shown in the dark blue part of the figure above. As records are updated and deleted more frequently, there will be more deleted records in the record pile, that is, more and more holes (fragments) will appear. These deleted records are connected to form a free space list of the page.
4. Unallocated space: refers to the storage space that is not used by pages. As pages are continuously used, the unallocated space will become smaller and smaller. When a new record is inserted, it first tries to obtain a suitable storage location from the free space list (with enough space). If there is no suitable storage location, it will apply for it in the unallocated space.
5. Slot area: Slot is a pointer to some valid records of the page. Each slot occupies two bytes and stores the offset of the record relative to the first address of the page. If a page has n valid records, the number of slots is between n/8+2 and n/4+2. The next section introduces the slot area in detail, which is the key to recording page order and binary search.
6. Page Tailer: The last part of the page, which occupies 8 bytes and mainly stores the verification information of the page.

The page header, maximum/minimum virtual record, and page footer all have fixed storage locations in the page.

InnoDB index structure

InnoDB uses B+Tree to store indexes.

An Innodb table may contain multiple indexes, each of which is stored using a B+ tree. The index includes clustered index and secondary index. The clustered index uses the primary key of the table as the index key and contains all the fields of the table. A secondary index contains only the contents of the index key and the clustered index key (primary key), and does not include other fields. Each index is a B+ tree. Each B+ tree consists of many pages, and the size of each page is generally 16K. From the organizational structure of the B+ tree, the pages of the B-tree can be divided into:

Leaf node: The page at level 0 of the B-tree, which stores all the recorded contents.
Non-leaf nodes: Pages with a B-tree level greater than 0 that only store index keys and page pointers.

A typical B+ tree structure:

As can be seen from the above figure, pages at the same level are connected by a doubly linked list.

Generally speaking, starting from the leftmost leaf node of the B+ tree and scanning to the right, you can get all the data of the B+ tree from small to large. Therefore, for leaf nodes, there are the following characteristics:

The data within a page is sorted by index key.

The index key value of any record in a page is not less than any record in its left sibling page.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.