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

This article introduces the relevant knowledge of "the memory structure and characteristics of InnoDB". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

As the saying goes, behind every successful man, there is a woman who pays silently for him, and for MySQL, this "person" is the InnoDB storage engine.

The most important feature that distinguishes   MySQL from other databases is its plug-in table storage engine. Among many storage engines, InnoDB is the most commonly used storage engine. Starting with the MySQL5.5.8 version, the InnoDB storage engine is the default storage engine.

The   InnoDB storage engine supports transactions, and its design goal is mainly for online transaction processing (OLTP) applications. It is characterized by the design of row locks, support for foreign keys, and support for unlocked reads, that is, the default read operation does not produce locks.

  InnoDB achieves high concurrency by using multi-version concurrency control (MVCC) and implements the isolation level in 4 of the SQL standard, which defaults to the REPEATABLE level. At the same time, a strategy called next-key-locking is used to avoid phantom reading. In addition, the InnoDB storage engine also provides high performance and high availability functions such as insert buffering (insert buffer), second write (double write), adaptive hash indexing (adaptive hash index), pre-read (read ahead) and so on.

The figure above   shows in detail the architecture of the InnoDB storage engine. As can be seen from the figure, the InnoDB storage engine consists of memory pools, background threads and disk files. Next, let's take a brief look at memory-related concepts and principles. Buffer pool

The   InnoDB storage engine is based on disk storage and manages the records in it as pages. However, because of the gap between CPU speed and disk speed, disk-based database systems usually use buffer pool records to improve the overall performance of the database.

  reads from the database, first puts the page read from the disk in the buffer pool, and the next time it reads the same page, it first determines whether the page is in the buffer pool. If the page is said to be hit in the buffer pool, the page is read directly. Otherwise, read the page on the disk.

When   modifies the pages in the database, it first modifies the pages in the buffer pool, and then refreshes them to disk at a certain frequency. The flushing of pages from the buffer pool to disk is not triggered every time the page is updated, but is flushed back to disk through a mechanism called CheckPoint.

  therefore, the size of the buffer pool directly affects the overall performance of the database, which can be set by configuring the parameter innodb_buffer_pool_size.

  specifically, the data page types cached in the buffer pool are: index page, data page, undo page, insert buffer (insert buffer), adaptive hash index (adaptive hash index), lock information stored by InnoDB (lock info) and data dictionary information (data dictionary).

  can see on the architecture diagram that the memory area of the InnoDB storage engine has redo log buffers and additional memory pools in addition to buffer pools. The InnoDB storage engine first puts the redo log information into this buffer, and then flushes it to the redo log file at a certain frequency. Redo log buffers generally do not need to be set to a large value, which can be controlled by the configuration parameter innodb_log_buffer_size.

Data page and index page

  Page is the most basic structure of Innodb storage and the smallest unit of Innodb disk management. Everything related to the database is stored in the Page structure. There are several types of Page, and data pages and index pages are the two most important types.

Insert buffer (Insert Buffer)

  We all know that when inserting on an InnoDB engine, it is generally necessary to insert in the primary key order in order to achieve higher insertion performance. When there are non-clustered and non-unique indexes in a table, when inserting, the data pages are stored in the order of the primary key, but the insertion of non-clustered index leaf nodes is no longer sequential. at this time, discrete access to non-clustered index pages is needed, and the performance of the insertion operation is degraded due to the existence of random reads.

  InnoDB designed Insert Buffer for this purpose for insertion optimization. For the insert or update operation of a non-clustered index, instead of inserting it directly into the index page every time, first determine whether the inserted nonclustered index is in the buffer pool, if so, insert it directly; if not, put it into an Insert Buffer first. It seems that the non-clustered index of the database has found the leaf node, but in fact it is not, so it is stored in another location. Then merge the child nodes of Insert Buffer and non-clustered index pages with a certain frequency and situation. At this point, it is usually possible to merge multiple inserts into one operation, which greatly improves the insert performance for non-clustered indexes.

Write twice (Double Write)

  if Insert Buffer brings performance improvement to InnoDB storage engine, then Double Write brings data page reliability to InnoDB storage engine.

Doublewrite schematic diagram

  as shown in the figure above, the Double Write consists of two parts, one is the in-memory double write buffer with the size of 2MB, and the other is the continuous 128pages of shared table space on the physical disk, which is also the size of 2MB. When refreshing the dirty pages of the buffer pool, we do not write directly to the disk, but copy the dirty pages to this area of memory first through the memcpy function, and then write them sequentially to the physical disk of the shared tablespace twice through doublewrite buffer, and then immediately call the fsync function to synchronize the disk to avoid the problems caused by the operating system buffered write. After completing the writing of the doublewrite page, the pages in doublewirite buffer are written to each tablespace file.

  if the operating system crashes while writing a page to disk, during recovery, the InnoDB storage engine can find a copy of the page from the doublewrite in the shared tablespace, copy it to the tablespace file, and apply the redo log.

Redo log (Redo Log Buffer)

  when the version of the page in the buffer pool is newer than the disk, the database needs to flush the new version of the page from the buffer pool to the disk. But if you refresh every time a page sends a change, then performance development is very large, so InnoDB adopts the Write Ahead Log strategy, that is, when a transaction commits, it writes the redo log first, and then writes dirty pages to disk when the transaction commits. If an outage results in data loss, data recovery is performed through a redo log.

Schematic diagram for writing InnoDB data

The   InnoDB storage engine first puts the redo log information into the redo log buffer, and then flushes it to the redo log file at a certain frequency. Redo log buffers generally do not need to be set too large, because redo log buffers are generally flushed to the log file every second. It can be controlled by the configuration parameter innodb_log_buffer_size. The default is 8MB.

In addition to the per-second refresh mechanism,   flushes the redo log buffer to the log every time a transaction commits. InnoDB is the storage engine of transactions, which realizes transaction persistence through Force Log at Commit mechanism, that is, when a transaction commits, all logs of the transaction must be written to the redo log file for persistence, and then the commit operation of the transaction is completed. The writing mechanism of InnoDB is roughly shown in the following figure.

In order to ensure that each log is written to the redo log file,   must invoke a fsync operation to actually write the buffered file from the file system cache to disk after each redo log buffer write.

  can control the policy of flushing redo logs to disk through innodb_flush_log_at_trx_commit. The default value of this parameter is 1, which means that the transaction commit must have a fsync operation, and can also be set to 0 and 2. 0 means that no write redo log operation occurs when the transaction is committed, which is done only in the main thread, and 2 means that the redo log is written on commit, but only to the file system cache without fsync operations. Thus, when set to 0, the performance is the highest, but transaction consistency is lost.

Adaptive hash indexing (Adaptive Hash Index)

  InnoDB builds a hash index for hot pages according to the frequency and pattern of access to improve query efficiency. The InnoDB storage engine monitors queries on each index page on the table, and builds a hash index if it is observed that the establishment of a hash index can lead to a speed increase, so it is called an adaptive hash index.

  adaptive hash indexing is built from the B+ tree pages of the buffer pool, so it is fast to build, and there is no need to hash the entire data table. It has a requirement that the continuous access mode to the page must be the same, that is, the WHERE of its query must be exactly the same and must be contiguous.

Lock Information (lock info)

  as we all know, the InnoDB storage engine locks table data at the row level. However, InnoDB also uses locks in many other places within the database, allowing concurrent access to many different resources. Database systems use locks to support concurrent access to shared resources and provide data integrity and consistency. We will learn more about the lock later.

Data dictionary information (Data Dictionary)

  InnoDB has its own table cache, which can be called a table definition cache or a data dictionary. When InnoDB opens a table, it adds a corresponding object to the data dictionary.

  data dictionary is a collection of meta-information about data, library objects, table objects and so on in a database. In MySQL, the content of data dictionary information includes table structure, database name or table name, data type of field, view, index, table field information, stored procedure, trigger and so on. The MySQL INFORMATION_ schema library provides access to data Bureau metadata, statistics, and access information about MySQL server (for example, database or table names, data types and access permissions for fields, and so on). The information stored in this library can also be called MySQL's data dictionary.

This is the end of the content of "memory structure and characteristics of InnoDB". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.