what is mysql innodb

InnoDB is one of the database engines of MySQL. It is now the default storage engine of MySQL and one of the standards for binary releases by MySQL AB. InnoDB adopts a dual-track authorization system, one is GPL authorization and the other is proprietary software authorization. . InnoDB is the preferred engine for transactional databases and supports transaction security tables (ACID); InnoDB supports row-level locks, which can support concurrency to the greatest extent. Row-level locks are implemented by the storage engine layer.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

If you want to see the storage engine used by your database by default, you can use the command SHOW VARIABLES LIKE 'storage_engine';

1. InnoDB storage engine

InnoDB is one of the database engines of MySQL. It is now the default storage engine of MySQL and one of the standards for binary releases by MySQL AB. InnoDB was developed by Innobase Oy and acquired by Oracle in May 2006. Compared with traditional ISAM and MyISAM, the biggest feature of InnoDB is that it supports ACID-compatible transaction (Transaction) function, similar to PostgreSQL.

InnoDB adopts a dual-track licensing system, one is GPL licensing and the other is proprietary software licensing.

1. InnoDB is the preferred engine for transactional databases and supports transaction security tables (ACID)

ACID attributes of transactions: That is, atomicity and consistency , Isolation, durability

                                                                                                                                                                                                                                                              been To roll back to the point where the transaction started.

This is implemented: is mainly based on the Redo and UNDO mechanism of MySQ log system. A transaction is a set of SQL statements that have functions such as selection, query, and deletion. There will be one node for each statement execution. For example, after the delete statement is executed, a record is saved in the transaction. This record stores when and what we did. If something goes wrong, it will be rolled back to the original position. What I have done has been stored in the redo, and then it can be executed in reverse.

##                                                                                                                                                                                                                                                                                           (eg: For example, if A transfers money to B, it is impossible that A deducts the money but B does not receive it)

## There is no interference between different transactions for the same data;

#                                                                                                                                                                                                                                                        A transaction is modifying a certain data multiple times, and the multiple modifications in this transaction have not yet been committed. At this time, a concurrent transaction accesses the data, which will cause the data obtained by the two transactions to be inconsistent); (read Fetched uncommitted dirty data from another transaction)

##                                                                                                Data, multiple queries within a transaction range returned different data values. This is because it was modified and submitted by another transaction during the query interval; (the data submitted by the previous transaction was read, and the same data values ​​were queried. A data item)

                                                                                                        Virtual read (phantom read) : It is a phenomenon that occurs when transactions are not executed independently (eg: transaction T1 reads all rows in a table A data item was modified from "1" to "2". At this time, transaction T2 inserted a row of data items into the table, and the value of this data item was still "1" and submitted to the database. If the user operating transaction T1 looks at the data that was just modified, he will find that there is still one row that has not been modified. In fact, this row was added from transaction T2, as if he was hallucinating. ; : After the transaction is completed, all updates to the database by the transaction will be saved to the database and cannot be rolled back 2. InnoDB is the default storage engine of mySQL. The default isolation level is RR, and in RR Taking the isolation level a step further, multi-version concurrency control (MVCC) is used to solve the non-repeatable read problem, and gap locks (that is, concurrency control) are added to solve the phantom read problem. Therefore, InnoDB's RR isolation level actually achieves the effect of serialization level while retaining better concurrency performance. MySQL database provides us with four isolation levels: a, Serializable (serialization): can avoid dirty reads, non-repeatable reads, and phantom reads occurs; b, Repeatable read (repeatable read): can avoid the occurrence of dirty reads and non-repeatable reads;

c, Read committed (read committed): can avoid the occurrence of dirty reads Occurrence;

d, Read uncommitted (read uncommitted): the lowest level, no guarantee in any situation;

from a----d isolation level from high to low, the higher the level , the lower the execution efficiency

3. InnoDB supports row-level locks. Row-level locks can support concurrency to the greatest extent, and row-level locks are implemented by the storage engine layer.

Lock

: The main function of the lock is to manage concurrent access to shared resources and is used to achieve transaction isolation

        

Type:

Shared lock (read lock), exclusive lock (write lock)

## MySQL Lock strength

: table-level locks (low overhead, low concurrency), usually implemented at the server layer                                                                                                                                                                                                 but      , will only be implemented at the storage engine level4. InnoDB is designed for maximum performance in processing huge amounts of data. Its CPU efficiency may be unmatched by any disk-based relational database engine

5. The InnoDB storage engine is fully integrated with the MySQL server. The InnoDB storage engine is cached in the main memory. It maintains its own buffer pool for data and indexes. InnoDB places its tables and indexes in a logical table space. The table space can contain several files (or original disk files); 6. InnoDB supports foreign key integrity constraints. When storing data in a table, each table is stored in the order of the primary key. If the primary key is not specified when the table is defined. InnoDB will generate a 6-byte ROWID for each row and use it as the primary key

7. InnoDB is used in many large database sites that require high performance

8. InnoDB does not save the number of rows in the table (eg: when selecting count(*) from table, InnoDB needs to scan the entire table to calculate how many rows there are); when clearing the entire table, InnoDB stores one row Deletion of one row is very slow;

InnoDB does not create a directory. When using InnoDB, MySQL will create a 10MB automatically extended data file named ibdata1 in the MySQL data directory. And two 5MB log files named ib_logfile0 and ib_logfile1

2. The underlying implementation of the InnoDB engine

InnoDB has two storage files, the suffixes are .frm and .idb; .frm is the definition file of the table, and .idb is the data file of the table.

1. The InnoDB engine uses the B Tree structure as the index structure

B-Tree (balanced multi-path search tree): for disks, etc. A balanced search tree designed for external storage devices

When the system reads data from disk to memory, the basic unit is disk block bits. Data located in the same disk block will be read once read out on a regular basis, rather than on demand.

InnoDB storage engine uses pages as data reading units. Pages are the smallest unit of disk management. The default page size is 16k.

The storage space of a disk block in the system is often not that large, so every time InnoDB applies for disk space, it will use several consecutive disk blocks with addresses to reach the page size of 16KB.

InnoDB will use pages as the basic unit when reading disk data into the disk. When querying data, if each piece of data in a page can help locate the data record location, which will reduce the number of disk I/O and improve query efficiency.

The data in the B-Tree structure allows the system to efficiently find the disk block where the data is located

Each node in the B-Tree is based on The actual situation can contain a large amount of keyword information and branches, for example:

Each node occupies one disk block Space, there are two ascending-order keys on a node and three pointers to the root node of the subtree. The pointers store the address of the disk block where the child node is located.

Take the root node as an example. The keywords are 17 and 35. The data range of the subtree pointed to by the P1 pointer is less than 17. The P2 pointerThe data range of the subtree pointed to is 17----35, and the data range of the subtree pointed to by the P3 pointer is greater than 35;

Simulated searchKeywords Process 29:

a. Find disk block 1 based on the root node and read it into memory. [The first disk I/O operation]

b. Compare keyword 29 in the interval (17,35) and find the pointer P2 of disk block 1;

c. Find disk block 3 according to the P2 pointer and read it into the memory. [Disk I/O operation for the second time]

d. Compare keyword 29 in the interval (26, 30) and find pointer P2 of disk block 3;

e. Find disk block 8 according to the P2 pointer and read it into the memory. [Disk I/O operation third time]

f. Keyword 29 was found in the keyword list in disk block 8.

MySQL's InnoDB storage engine is designed with the root node resident in memory, so it strives to achieve a tree depth of no more than 3, that is, I/O does not need to exceed three times;

Analyzing the above results, we found that three disk I/O operations and three memory search operations are required. Since the keywords in the memory are an ordered list structure, binary search can be used to improve efficiency; three disk I/O operations are the decisive factor affecting the entire B-Tree search efficiency.

B Tree

B Tree is an optimization based on B-Tree, making it more suitable Implement the external storage index structure. Each node in B-Tree has key and data, and the storage space of each page is limited. If the data data is large, each node (i.e. one page) will be able to store The number of keys is very small. When the amount of data stored is large, the depth of the B-Tree will also be larger, which will increase the number of disk I/Os during query, thus affecting query efficiency.

In B Tree, all data record nodes are stored on leaf nodes of the same layer in order of key value. Only key value information is stored on non-leaf nodes. This can greatly Increase the number of key values ​​stored in each node and reduce the height of B Tree;

B Tree has two changes based on B-Tree: ( 1) The data is stored in leaf nodes

Since the non-leaf nodes of B Tree only store key value information, assuming that each disk block can store 4 key values ​​and pointer information, the structure after becoming B Tree is as shown below:

Usually there are two head pointers on the B Tree, one points to the root node, the other points to the leaf node with the smallest keyword, and There is a chain ring structure between all leaf nodes (ie data nodes).

Therefore, two search operations can be performed on B Tree, one is a range search and paging search for the primary key, and the other is a random search starting from the root node.

B Tree in InnoDB

InnoDB is a data storage indexed by ID

There are two data storage files using the InnoDB engine, one is a definition file and the other is a data file.

InnoDB builds an index on the ID through the B Tree structure, and then stores the records in the leaf nodes

##If the indexed field is not the primary key ID, create an index for the field, then store the primary key of the record in the leaf node, and then find the corresponding record through the primary key index

[Related recommendations:

mysql video tutorial]

The above is the detailed content of what is mysql innodb. For more information, please follow other related articles on the PHP Chinese website!