Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the difference between InnoDB and MyISAM in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

The connection and difference between InnoDB and MyISAM

Database storage engine

The database storage engine is the underlying software organization of the database. The database management system (DBMS) uses the data engine to create, query, update and delete data. Different storage engines provide different storage mechanisms, indexing skills, locking levels and other functions, using different storage engines, you can also get specific functions.

How do you know what engine your database uses?

SHOW ENGINES

We can see that the database provides us with a lot of storage engines. From the table, we can see that the Support column of InnoDB is DEFAULT, indicating that on my database server, InnoDB is the default database engine, but MySQL is very compatible with multiple engines. Different databases on a database server can use different data engines, and even multiple tables in a database can use different engines.

We can summarize some of the differences between the two engines from some documentation:

InnoDB supports transactions, but MyISAM does not. For InnoDB, every SQL statement is encapsulated as a transaction to commit by default, which will affect the speed. The way to optimize the speed is to put multiple SQL statements between begin and commit to form a transaction.

InnoDB supports foreign keys, but MyISAM does not.

So if a table modification requires a more demanding transaction, you can choose InnoDB. In this database, the tables with high query requirements can be selected for MyISAM storage. If the database needs a temporary table for query, you can even consider choosing the MEMORY storage engine.

But why are there these differences between InnoDB and MyISAM? We need to understand the underlying principles of the corresponding storage engine.

Principle of storage engine

First, give an answer to the question that may be asked in the interview, "what is the data structure of the index used by the MyISAM and InnoDB engines?"

They are all B + trees, but the difference is:

The data structure of the B + tree in MyISAM stores the address value of the actual data, and its index is separate from the actual data, but the index points to the actual data. The pattern of this index is called a nonclustered index.

The actual data is stored in the data structure of the B+ tree in InnoDB, and this kind of index is called clustered index.

B tree and B + tree

So what is a B+ tree?

The B + tree is a variant of the B tree. For the B tree:

B-tree belongs to multifork tree, also known as balanced multipath lookup tree, and its rules are as follows:

All node keywords are arranged in incremental order and follow the principle of small on the left and big on the right.

Number of sub-nodes: the number of sub-nodes of non-leaf nodes is > 1 and = 2, except for empty trees. (note: M order represents the maximum number of search paths for a tree node, M paths, when Mtree 2 is a 2-fork tree, and M-tree 3 is 3-fork).

Number of keywords: the number of keywords in branch nodes is greater than or equal to ceil (m _ px2)-1 and less than or equal to 1 (note: ceil () is a function rounded in a positive infinite direction, such as ceil (1.1), the result is 2)

The pointer of the leaf node is empty and the leaf node has the same depth

For B+ trees:

The B + tree is an upgraded version of the B tree. Compared with the B tree, the B + tree makes full use of the node space and makes the query speed more stable, and its speed is completely close to the dichotomy search.

The C++ definition of a B+ tree is similar to the following:

Class BPTree; / / self explanatory classes

Class Node

{

Bool IS_LEAF

Int * key, size

Node** ptr

Friend class BPTree

Public:

Node ()

}

Class BPTree

{

Node * root

Void insertInternal (int,Node*,Node*)

Void removeInternal (int,Node*,Node*)

Node* findParent (Node*,Node*)

Public:

BPTree ()

Void search (int)

Void insert (int)

Void remove (int)

Void display (Node*)

Node* getRoot ()

Void cleanUp (Node*)

~ BPTree ()

}

What is an index?

Because the data structure implemented above is related to the index in the database, you have the following knowledge about the index:

Unique index: unique index does not allow two rows to have the same index value

Primary key index: defining a primary key for a table automatically creates a primary key index, which is a special type of unique index. The primary key index requires that each value in the primary key is unique and cannot be empty.

Clustered index (Clustered): the physical order of rows in a table is the same as the logical (index) order of key values, and there can be only one table.

Nonclustered index (Non-clustered): a nonclustered index specifies the logical order of the tables. The data is stored in one location, the index is stored in another, and the index contains a pointer to the data storage location. There can be multiple, less than 249

MyISAM

Back at MyISAM, the index structure is shown in the following figure, because MyISAM's index file holds only the address of the data record. In MyISAM, there is no structural difference between the primary index and the secondary index (Secondary key):

The index retrieval algorithm in MyISAM is to first search the index according to the B+Tree search algorithm, if the specified Key exists, take out the value of its data domain, and then take the value of the data domain as the address to read the corresponding data record.

InnoDB

For InnoDB, the table data file itself is an index structure organized by B+Tree, and the data domain of the leaf node of this tree holds the complete data record.

Because InnoDB uses the database primary key as the index Key, the InnoDB data table file itself is the primary index, and because InnoDB data files need to be aggregated according to the primary key, tables using InnoDB as the data engine need to have a primary key. If it is not explicitly specified, MySQL will try to automatically select a column that uniquely identifies the data as the primary key, and if it cannot be found, it will generate an implied field as the primary key The length of this field is 6 bytes and the type is long shaping.

After reading the above, do you have any further understanding of the difference between InnoDB and MyISAM in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, 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.