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

This article is mainly to tell you that by using commands to view the mysql storage engine, you can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope that using commands to view the mysql storage engine article can bring you some practical help.

SHOW VARIABLES LIKE 'storage_engine'

1. InnoDB storage engine

1.InnoDB is the preferred engine for transactional databases

Support for transaction security tables (ACID)

ACID attributes of transactions: atomicity, consistency, isolation, persistence

a. Atomicity: atomicity means that either all or none of this set of statements are executed, and if an error occurs in the middle of the transaction execution, the database will be rolled back to the place where the transaction started.

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

b. Consistency: before and after the transaction starts and ends, the integrity constraints of the database are not broken. (eg: for example, if A transfers money to B, it is impossible that A withholds the money but B does not receive it.)

c. Isolation: only one transaction is allowed to request the same data at a time, and there is no interference between different transactions.

If isolation is not taken into account, several problems arise:

I, dirty reading: refers to reading data in another uncommitted transaction during one transaction (when a transaction is modifying a data several times, and the modifications in this transaction have not been committed, then a concurrent transaction accesses the data, which will cause the data obtained by the two transactions to be inconsistent); (read the dirty data uncommitted by another transaction)

Ii, non-repeatable: for a certain data in the database, multiple queries within a transaction range return different data values, because during the query interval, it is modified and committed by another transaction; (the data committed by the previous transaction is read, and the same data item is queried)

Iii, virtual reading (illusory reading): a phenomenon that occurs when a transaction is not executed independently (eg: transaction T1 changes a data item in all rows of a table from "1" to "2", and transaction T2 inserts a row of data items into the table, and the value of this data item is still "1" and submitted to the database. On the other hand, if the user operating transaction T1 looks at the data that has just been modified, he will find that there is still one row that has not been modified. In fact, this line is added from transaction T2, which is like an illusion. (read the data committed by the previous transaction, for a batch of data as a whole)

d. Persistence: 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 for mySQL

The default isolation level is RR, and under the isolation level of RR, the unrepeatable read problem is solved through multi-version concurrency control (MVCC), and the phantom reading problem is solved by gap lock (that is, concurrency control). Therefore, the RR isolation level of InnoDB actually achieves the effect of serialization level while retaining good concurrency performance.

The MySQL database provides us with four levels of isolation:

A, Serializable (serialization): it can avoid dirty reading, unrepeatable reading and phantom reading.

B, Repeatable read (repeatable): can avoid dirty reading and non-repeatable reading

C, Read committed (read submitted): can avoid dirty reading

D, Read uncommitted (read unsubmitted): lowest level, no guarantee under any circumstances

The isolation level from aMuthMutel / Mustco is from high to low, and the higher the level is, the lower the execution efficiency is.

3.InnoDB supports row-level locks.

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

Locks: the main function of locks is to manage concurrent access to shared resources and to achieve transaction isolation.

Type: shared lock (read lock), exclusive lock (write lock)

Strength of MySQL lock: table-level lock (low overhead, low concurrency), usually implemented in the cloud server layer

Row-level locks (high overhead and high concurrency) are only implemented at the storage engine level

4. InnoDB is designed to handle the maximum performance of a large amount of data.

Its CPU efficiency may not be matched by any disk-based relational database engine.

5. InnoDB storage engine is fully integrated with MySQL CVM.

The InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB puts its tables and indexes in a logical tablespace, which can contain several files (or original disk files).

6. InnoDB supports foreign key integrity constraints.

When storing data in a table, the storage of each table is stored in the order of the primary key, and if it is not displayed, the primary key is specified when the table is defined. InnoDB generates a 6-byte ROWID for each line and uses it as the primary key

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

8. The number of rows of the table is not saved in InnoDB (when eg:select count (*) from table, InnoDB needs to scan the whole table to calculate the number of rows); when emptying the whole table, InnoDB is deleted row by row, which is very slow.

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

Second, the underlying implementation of the InnoDB engine

There are two storage files for InnoDB, with suffixes .frm and .idb, where .frm is the definition file for the table and .idb is the data file for the table.

1. InnoDB engine uses B+Tree structure as index structure.

B-Tree (balanced Multiplex search Tree): a balanced search tree designed for external storage devices such as disks

When the system reads data from disk to memory, it is in basic units of disk block bits, and the data located in the same disk block will be read out at once rather than on demand.

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

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

InnoDB will take the page as the basic unit when reading disk data to disk. When querying data, if each piece of data in a page can help to locate the location of data records, it will reduce the number of disk ID O and improve the query efficiency.

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

Each node in B-Tree can contain a lot of keyword information and branches according to the actual situation, for example

Each node occupies the disk space of a disk block. On one node, there are two keywords sorted in ascending order and three pointers to the root node of the subtree. The pointer stores the address of the disk block where the child node resides.

Take the root node as an example, the data range of the subtree pointed to by the P1 pointer with keywords 17 and 35 is smaller than that pointed to by the pointer P2, and the data range of the subtree pointed by the pointer to P3 is greater than 35.

Simulate the process of finding keyword 29:

a. Find disk block 1 according to the root node and read it into memory. [disk Icano operation for the first time]

b. The comparison keyword 29 is in the interval (172.35), find the pointer P2 of disk block 1.

c. Locate disk block 3 according to the P2 pointer and read it into memory. [disk Icano operation for the second time]

d. The comparison keyword 29 is in the interval (261.30), find the pointer P2 of disk block 3.

e. Locate disk block 8 according to the P2 pointer and read it into memory. [disk Icano operation for the third time]

f. Find the keyword 29 in the keyword list in Block 8.

MySQL's InnoDB storage engine is designed to keep the root node in memory, so it strives to achieve a tree depth of no more than 3, that is, no more than three times

After analyzing the above results, it is found that three disk Iwhite O operations and three memory lookup operations are required. Because the keyword in memory is an ordered table structure, dichotomy search can be used to improve the efficiency, and the determinants that affect the efficiency of the whole B-Tree search during the three disk IBO operations.

B+Tree

B+Tree is an optimization based on B-Tree to make it more suitable for the implementation of external storage index structure. There are key and data in each node in B-Tree, and the storage space of each page is limited. If the data data is large, it will lead to a small number of key that can be stored in each node (that is, a page). When the amount of data stored is very large, it will also lead to a large depth of B-Tree, and increase the number of disk Icano when querying, thus affecting the efficiency of the query.

In B+Tree, all data recording nodes are stored on leaf nodes of the same layer according to the order of key values, while only key value information is stored on non-leaf nodes, which can greatly increase the number of key values stored by each node and reduce the height of B+Tree.

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

Therefore, there are two kinds of lookup operations for B+Tree, one is range lookup and paging lookup for primary keys, and the other is random lookup starting from the root node.

B+Tree in InnoDB

InnoDB is a data store indexed by ID

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

InnoDB indexes ID through the B+Tree structure, and then stores records in the leaf node

If the indexed field is not the primary key ID, the field is indexed, then the primary key of the record is stored in the leaf node, and then the corresponding record is found through the primary key index.

By using the command to view the mysql storage engine, let's stop here. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.