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

Preface

There is only one storage engine in databases such as Oracle and SQL Server, 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 needs, and users can also write their own storage engines according to their own needs.

Differences between MySQL's main storage engines

The default storage engine for MySQL is MyISAM, and other common ones are InnoDB, as well as MERGE, MEMORY (HEAP), and so on.

Major storage engines

MyISAM manages non-transactional tables and provides 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, MyISAM is used by default when the storage engine for the new table is not specified. Each MyISAM is stored as three files on disk. The file name is the same as the table name, with .frm (storage table definition),. MYD (MYData, storage data),. MYI (MYIndex, storage index). Data files and index files can be placed in different directories, evenly distributed io, for faster speed.

The InnoDB storage engine is used in transaction processing applications and has many features, including ACID transaction support, providing transaction security with commit, rollback, and crash recovery capabilities. But compared to the MyISAM storage engine, InnoDB writes are less efficient and take up more disk space to retain data and indexes.

Memory keeps all the data in memory and can be applied to temporary tables to provide extremely fast access in environments that require quick lookup of references and other similar data. Memory uses a hash index, so data access is very fast.

Merge allows MySQL DBA or developers to logically group a series of equivalent MyISAM tables together and reference them as one object. Ideal for VLDB environments such as data warehousing.

Horizontal comparison of different storage engines

Features MyISAMBDBMemoryInnoDB storage restrictions without 64TB transaction security support lock mechanism table lock table lock row lock B-tree index support hash index support full-text index support clustered index support data cache support data cache support data compressibility support space use low Nash A high memory use low low medium high batch insert speed high level support foreign key support

View and configure the operation of the storage engine

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

two。 To view information such as the definition structure of a table, you can use the following commands:

Desc [ribe] tablename; / / View the structure of the data table Show create table tablename; / / display the creation statement of the table, you can view the ENGINEshow table status like 'tablename'\ G specified when the table was created to display the current state value of the table

3. Set or modify the storage engine for the table

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

Create table tableName (columnName (column name 1) type (data type) attri (property setting), columnName (column name 2) type (data type) attri (property setting),... .) Engine = engineName

Modify the storage engine with the command

Alter table tableName engine = engineName

For the entire server or solution, you don't have to use the same storage engine, you can use a different storage engine for each table in the scheme.

Storage structure of InnoDB

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

The storage format of the Page page is as follows:

The storage of a page consists of the following parts:

Page Header: records the control information of the page, accounting for a total of 150 bytes, including the left and right sibling page pointers, page space usage, and so on. The detailed description of the page header will be described in the next article. Minimum virtual record and maximum virtual record: virtual records stored in two fixed locations that do not store data themselves. The minimum virtual record is smaller than any record, and the maximum virtual record is larger than any record. Record heap: refers to the orange part of the image above. Represents the record space allocated by the page and is the real storage area for indexed data. There are two types of record heaps, namely, valid records and deleted records. A valid record is a record that is normally used by the index, while a deleted record indicates that the index has been deleted and is not in use, such as the dark blue part of the image above. As records are updated and deleted more frequently, the more deleted records will be in the record heap, that is, more and more holes (fragments) will appear. When these deleted records are joined together, they become a free-space linked list of the page. Unallocated space: unused storage space on a page, which will become smaller and smaller as the page continues to use. When inserting a new record, first try to get the appropriate storage location (enough space) from the free space linked list, and if it is not satisfied, it will apply in the unallocated space. Slot area: the slot is the pointer to some valid records on the page, each slot occupies two bytes, and stores the offset of the record from the first address of the page. If the page has n valid records, then the number of slot is between n/8+2~n/4+2. The next section details the slot area, which is the key to the orderly and binary search of the record page. Footer (Page Tailer): the last part of the page, accounting for 8 bytes, mainly stores the check information of the page.

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

Index structure of InnoDB

InnoDB stores indexes in the same way as B+Tree.

A table in Innodb may contain multiple indexes, each of which is stored using a B+ tree. The index includes the clustered index and the secondary index. The clustered index uses the primary key of the table as the index key and contains all the fields of the table. The 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 each page size is generally 16K. From the organizational structure of the B + tree, the pages of the B tree can be divided into:

Leaf node: a page with a B-tree hierarchy of 0 that stores all the contents of the record.

Non-leaf nodes: pages with a B-tree hierarchy greater than 0 store only index keys and page pointers.

A typical B+ tree structure:

As can be seen from the image above, pages at the same level are connected by a two-way linked list.

In general, 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 on the page is sorted by index key.

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

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

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.