mysql storage engine: the difference between myIsam and innodb

MySQL has multiple storage engines, MyISAM and InnoDB are two commonly used ones. Here are some basic concepts about these two engines (not an in-depth introduction).

MyISAM is the default storage engine for MySQL. It is based on the traditional ISAM type and supports full-text search, but it is not transaction-safe and does not support foreign keys. Each MyISAM table is stored in three files: the frm file stores the table definition; the data file is MYD (MYData); the index file is MYI (MYIndex).

InnoDB is a transactional engine that supports rollback, crash recovery, multi-version concurrency control, ACID transactions, and row-level locking (the row lock of the InnoDB table is not absolute. If MySQL is executing a SQL statement If the scope to be scanned cannot be determined, the InnoDB table will also lock the entire table (such as the SQL statement during the like operation), and provide a lock-free reading method consistent with the Oracle type. InnoDB stores its tables and indexes in a tablespace, which can contain several files.

Core difference

MyISAM is non-transactionally safe, while InnoDB is transactionally safe.

The granularity of MyISAM locks is table level, while InnoDB supports row-level locking.

MyISAM supports full-text type indexes, while InnoDB does not support full-text indexes.

MyISAM is relatively simple, so it is better than InnoDB in terms of efficiency. Small applications can consider using MyISAM.

MyISAM tables are saved in the form of files. Using MyISAM storage in cross-platform data transfer will save a lot of trouble.

InnoDB tables are more secure than MyISAM tables. You can switch non-transactional tables to transactional tables (alter table tablename type=innodb) while ensuring that data will not be lost.

Application Scenario

MyISAM manages non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. If your application needs to perform a large number of SELECT queries, MyISAM is a better choice.

InnoDB is used for transaction processing applications and has numerous features, including ACID transaction support. If your application needs to perform a large number of INSERT or UPDATE operations, you should use InnoDB, which can improve the performance of multi-user concurrent operations.

Mysql storage engine and index

The database must have an index. Without an index, the retrieval process becomes a sequential search. The time complexity of O(n) is almost Intolerable. It is very easy to imagine how a table consisting of only a single key can be indexed using a B-tree, as long as the key is stored in the node of the tree. When a database record contains multiple fields, a B-tree can only store the primary key. If a non-primary key field is retrieved, the primary key index loses its function and becomes a sequential search again. At this time, a second set of indexes should be established on the second column to be retrieved. The index is organized by independent B-trees. There are two common methods to solve the problem of multiple B-trees accessing the same set of table data, one is called clustered index (clustered index) and the other is called non-clustered index (secondary index). Although these two names are both called indexes, they are not a separate index type, but a data storage method. For clustered index storage, row data and primary key B-trees are stored together, and secondary key B-trees only store secondary keys and primary keys. Primary key and non-primary key B-trees are almost two types of trees. For non-clustered index storage, the primary key B-tree stores pointers to the actual data rows in the leaf nodes instead of the primary key.

InnoDB uses a clustered index to organize the primary key into a B-tree, and the row data is stored on the leaf nodes. If you use the condition "where id = 14" to find the primary key, follow the The B-tree retrieval algorithm can find the corresponding leaf node and then obtain the row data. If you perform a conditional search on the Name column, you need two steps: the first step is to retrieve the Name in the auxiliary index B tree, and reach its leaf node to obtain the corresponding primary key. The second step uses the primary key to perform another B-tree retrieval operation on the primary index B-tree, and finally reaches the leaf node to obtain the entire row of data.

MyISM uses a non-clustered index. The two B-trees of the non-clustered index look no different. The structure of the nodes is exactly the same but the stored content is different. The nodes of the primary key index B-tree store the primary key. Secondary key index B-tree stores secondary keys. The table data is stored in a separate place. The leaf nodes of the two B-trees use an address to point to the real table data. For the table data, there is no difference between the two keys. Because the index trees are independent, retrieval by secondary keys does not require access to the index tree for the primary key.

In order to more vividly illustrate the difference between these two indexes, we assume that a table stores 4 rows of data as shown below. Among them, Id serves as the primary index and Name serves as the secondary index. The diagram clearly shows the difference between clustered index and non-clustered index.

We focus on the clustered index. It seems that the efficiency of the clustered index is obviously lower than that of the non-clustered index, because every time you use the auxiliary index to retrieve, you have to go through it twice. B-tree search, isn't this unnecessary? What are the advantages of clustered index?

1 Since the row data and leaf nodes are stored together, the primary key and row data are loaded into the memory together. When the leaf node is found, the row data can be returned immediately. If the data is organized according to the primary key Id, the data can be updated. quick.

2 The auxiliary index uses the primary key as a "pointer" instead of using the address value as the pointer. The advantage is that it reduces the maintenance work of the auxiliary index when row movement or data page splitting occurs. The primary key value is used as a pointer. This will make the auxiliary index take up more space. The benefit is that InnoDB does not need to update the "pointer" in the auxiliary index when moving rows. That is to say, the position of the row (located through the 16K Page in the implementation, which will be covered later) will change with the modification of the data in the database (the previous B-tree node split and Page split), and you can use a clustered index. It is guaranteed that no matter how the nodes of the primary key B tree change, the auxiliary index tree will not be affected.

So in the case of millions of data and larger data, the index performance of mysql innoDB is even better!

The above is the detailed content of mysql storage engine: the difference between myIsam and innodb. For more information, please follow other related articles on the PHP Chinese website!