MySQL Technology insider InnoDB Storage Engine Learning Notes

1th MySQL architecture and storage engine 1.3 MySQL storage engine

The biggest difference between a database and a file system is that the database is a transaction-enabled

InnoDB Storage Engine:

The default storage engine after MySQL5.5.8, primarily for OLTP (online transaction processing, for basic, daily transaction processing)

Supports transactions, supports foreign keys, supports row locks (in some cases, locks entire tables), non-locking reads (default read operation does not generate locks)

By using MVCC to obtain high concurrency, and to implement 4 isolation levels of the SQL standard, the default is repeatable read level

Use a strategy called Next-key locking to avoid phantom reading (phantom)

High-performance technologies such as insert buffer, two write (double write), Adaptive Hash Index (Adaptive hash indexes), pre-read (read ahead) are also available.

Table data is clustered, and each table's storage is stored in the order of the primary key.

MyISAM Storage Engine:

Does not support transactions, support full-text indexing, table lock design, mainly for some OLAP (online analysis processing, the main application of Data Warehouse).

Its buffer pool buffers only the index files, not the data files.

The Storage engine table consists of myd and myi, MyD is used to hold the data file, myi to hold the index file.

NDB:

is a clustered storage engine characterized by the fact that the data is all in memory.

Therefore, the primary key lookup is very fast, and by adding the NDB database storage node can improve the database performance linearly, is a high-availability, high-performance cluster system.

Memory:

The data in the table is stored in memory, and if the database restarts or crashes, the data in the table disappears.

It is ideal for staging tables that store temporary data. A hash index is used by default.

Only table locks are supported and concurrency is poor.

5th Chapter Index and algorithm 5.1 InnoDB storage Engine Index Overview

The InnoDB storage engine supports several common indexes:

B + Tree Index

Full-Text Indexing

Hash index

Adaptive Hash Index attribute: The InnoDB storage engine automatically generates a hash index for the table based on the usage of the table, and does not artificially intervene to generate a hash index in the table.

The B + Tree index does not find a specific row for a given key value. The B + Tree index can find only the page where the data row is located. The database then reads the page into memory, finds it in memory, and finally gets the data to be looked up.

5.3 B + Tree

A balanced lookup tree designed for disk or other access assistive devices.

All record points are placed on leaf nodes of the same layer in order of size.

Each leaf node is connected by a pointer.

5.4 B + Tree Index

B + index in the database has a feature is the high fan out of

The height of B + trees is generally in the four tiers, which means that a maximum of 2 to 4 IO is required to find a row record of a key value.

Fan in: Refers to the number of ancestor modules that call the module directly.

Fan out: Refers to the number of subordinate modules that the module calls directly.

The B + Tree index can be divided into a clustered index (clustered index) and a secondary index (secondary index), but whether it is a clustered or secondary index, its interior is a B + tree, which is highly balanced, and the leaf node holds all the data.

A clustered index and a secondary index are different, whether the leaf node is storing a whole line of information.

Clustered index:

A clustered index is a B + tree constructed according to the primary key of each table.

The leaf node is the row record data of the whole table, and the leaf node is also called the data page.

The data in the Index organization table is also part of the index. As with the B + Tree data structure, each data page is linked by a doubly linked list.

A clustered index provides very fast access to queries against range values.

Many documents write: The clustered index stores the data physically in order.

But the book says that the storage of clustered indexes is not physically contiguous, but logically contiguous. (I don't know which one is right.)

Here are two points: first, the page that was mentioned above is connected by a two-way link, and the page is sorted in the order of the primary key;

Another point is that the records in each page are also maintained through a doubly linked list, which can be stored on the physical storage as well as in the primary key.

Another benefit of a clustered index is that it has a very fast sort lookup and range lookup for primary keys.

Secondary index (nonclustered index):

The leaf node does not contain all the data for the row record.

In addition to the key values, the leaf node contains a bookmark (bookmark) in the index row in each leaf node.

This bookmark is used to tell the INNODB where the storage engine can find the row data relative to the index.

The presence of secondary indexes does not affect the organization of the data in the clustered index, so there can be multiple secondary indexes on each table.

When looking for data through a secondary index, the InnoDB storage engine traverses the secondary index and obtains a primary key to the primary key index through the page's basic pointer, and then finds a complete row record through the primary key index.

MySQL Technology insider InnoDB Storage Engine Learning Notes