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

The following mainly brings you how to distinguish the storage engine myIsam and innodb. I hope these words can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

MySQL has a variety of storage engines, of which MyISAM and InnoDB are commonly used. Here are some basic concepts about these two engines (not in depth).

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

InnoDB is a transactional engine that supports rollback, crash resilience, multi-version concurrency control, ACID transactions, row-level locking (row locks on InnoDB tables are not absolute, and if MySQL cannot determine the scope to scan when executing a SQL statement, InnoDB tables will also lock the whole table, such as SQL statements during like operations), and provide unlocked reads consistent with Oracle types. InnoDB stores its tables and indexes in one tablespace, which can contain several files.

Core distinction

MyISAM is non-transactional secure, while InnoDB is transactional secure.

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

MyISAM supports full-text indexing while InnoDB does not support full-text indexing.

MyISAM is relatively simple, so it is better than InnoDB in efficiency, and small applications can consider using MyISAM.

MyISAM table is saved as a file, so using MyISAM storage in cross-platform data transfer will save a lot of trouble.

InnoDB tables are more secure than MyISAM tables and can switch from non-transactional tables to transactional tables (alter table tablename type=innodb) without losing data.

Application scenario

MyISAM manages non-transactional tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. If you need to execute a large number of SELECT queries in your application, then MyISAM is a better choice.

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

Storage engine and index of Mysql

The database must have an index, and without an index, the retrieval process becomes a sequential search, and the time complexity of O (n) is almost unbearable. It is easy to imagine how a table consisting of only a single keyword can be indexed using a B+ tree, as long as the keyword is stored in the node of the tree. When a record in a database contains multiple fields, a B+ tree can only store the primary key. If the non-primary key field is retrieved, the primary key index loses its function and becomes a sequential search. At this point, you should create a second set of indexes on the second column to be retrieved. This index is organized by a separate B+ tree. There are two common ways 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 both of these names are called indexes, they are not a separate type of index, but a way to store data. For clustered index storage, row data and primary key B + tree are stored together, secondary key B + tree only stores secondary key and primary key, primary key and non-primary key B + tree are almost two types of trees. For non-clustered index storage, the primary key B + tree stores pointers to real rows of data at the leaf node, not 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 node. If you use the condition such as "where id = 14" to find the primary key, you can find the corresponding leaf node according to the B+ tree retrieval algorithm, and then get the row data. If you do a conditional search on the Name column, you need two steps: the first step is to retrieve the Name in the secondary index B + tree and reach its leaf node to get the corresponding primary key. The second step is to use the primary key to perform another B+ tree retrieval operation in the primary index B+ tree species, and finally reach the leaf node to get the whole row of data.

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

To better illustrate the difference between the two indexes, let's imagine that a table stores four rows of data as shown in the following figure. Id is the primary index and Name is the secondary index. The figure clearly shows the difference between clustered index and non-clustered index.

We focus on clustered indexes, and it seems that the efficiency of clustered indexes is significantly lower than that of non-clustered indexes, because every time we use secondary index retrieval, we have to go through two B+ tree lookups, isn't that unnecessary? What is the advantage of clustering index?

1 because the row data and the leaf node are stored together, so the primary key and the row data are loaded into memory together. If you find the leaf node, you can immediately return the row data. If you organize the data according to the primary key Id, it is faster to get the data.

2 the advantage of using the primary key as the "pointer" instead of using the address value as the pointer is that it reduces the maintenance of the secondary index when the row moves or the data page is split, using the primary key value as a pointer will make the secondary index take up more space, in exchange for the advantage that InnoDB does not have to update this "pointer" in the secondary index when moving rows. That is to say, the position of the row (which is located by 16K Page in the implementation, which will be discussed later) will change with the modification of the data in the database (the previous Btree node split and the Page split). The use of clustering index can ensure that no matter how the node of the primary key B+ tree changes, the secondary index tree will not be affected.

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

For the above about how to distinguish the storage engine myIsam and innodb, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.