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

What is the difference between mysql storage engines? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Differences between storage engines in MySQL: take Innodb and myisam as examples, the former supports transactions while the latter does not; the former emphasizes versatility and supports more extended functions, while the latter mainly focuses on performance; the former does not support full-text indexing, while the latter supports full-text indexing.

There are several storage engines supported by mysql, and here we mainly discuss several commonly used storage engines. Innodb,myisam

INNODB

INNODB index implementation

In common with MyISAM, InnoDB also uses B+Tree as a data structure to implement B-Tree indexes. The big difference is that the InnoDB storage engine uses the data storage method of "clustered index" to achieve B-Tree index. the so-called "aggregation" means that data rows and adjacent key values are stored compactly together. Note that InnoDB can only aggregate records of one leaf page (16K) (that is, records whose clustered index satisfies a certain range), so records containing adjacent key values may be far apart.

In InnoDB, a table is called an index organization table (index organized table). InnoDB constructs a B+Tree according to the primary key (if there is no primary key, a unique and non-empty index is selected instead, and if there is no such index, InnoDB implicitly defines a primary key as the clustered index). At the same time, the row record data of the whole table is stored in the leaf page, and the leaf node of the clustered index can also be called the data page. A non-leaf page can be regarded as a sparse index of a leaf page.

The following figure illustrates the implementation of InnoDB clustered index, but also reflects the structure of an innoDB table. You can see that in InnoDB, the primary key index and data are integrated and not separated.

This implementation gives InnoDB super high performance of searching by primary key. A clustered index can be selected purposefully, such as a mail table, and user ID can be selected to aggregate data, so that all emails of users of a certain id can be obtained by reading fewer and consecutive data pages from disk, avoiding the random IID O used to read scattered pages.

InnoDB is an iMago operation, and MVCC is used for Innodb read and write to support high concurrency.

Full table scan

It is not efficient when InnoDB does a full table scan, because InnoDB does not actually read sequentially, in most cases at random. When doing a full table scan, InnoDB scans pages and rows in primary key order. This applies to all InnoDB tables, including fragmented tables. If the primary key page table is not fragmented (the page table that stores the primary key and rows), the full table scan is fairly fast because the read order is close to the physical storage order. But when the primary key page is fragmented, the scan becomes very slow

Row level lock

Provide row locks (locking on row level), provide unlocked reads (non-locking read in SELECTs) consistent with Oracle types, and row locks for InnoDB tables are not absolute. If MySQL cannot determine the range to scan when executing a SQL statement, the InnoDB table will also lock the entire table, such as

Update table set num=1 where name like "aaa%"

MYISAM

Implementation of MyISAM Index

Each MyISAM is stored as three files on disk. The name of the first file starts with the name of the table, and the extension indicates the file type. The MyISAM index file [.MYI (MYIndex)] and the data file [.MYD (MYData)] are separated. The index file only holds the pointer (physical location) of the page where the record is located, reads the page through these addresses, and then reads the indexed rows. Let's take a look at the structure diagram first.

The image above is a good illustration of the physical location where the leaves in the tree hold the corresponding rows. With this value, the storage engine can query back to the table smoothly and get a row of complete records. At the same time, each leaf page also holds a pointer to the next leaf page. Thus it is convenient to traverse the range of leaf nodes. As for the secondary index, it is implemented in the same way as the figure above in the MyISAM storage engine, which also shows that the index mode of MyISAM is "non-clustered", which is compared with the "clustered index" of Innodb.

By default, MyISAM reads the index into memory and operates directly in memory.

Table level lock

Summary: Innodb emphasizes versatility and supports more extended functions, while myisam mainly focuses on performance

Difference

1. InnoDB supports transactions, but MyISAM does not. For InnoDB, each SQL language is encapsulated as a transaction by default, which will automatically commit, which will affect the speed, so it is best to put multiple SQL languages between begin and commit to form a transaction.

2. InnoDB is a clustered index, and the data file is tied to the index. There must be a primary key. It is very efficient to index through the primary key. But the secondary index needs two queries, first query the primary key, and then query the data through the primary key. Therefore, the primary key should not be too large, because the primary key is too large, other indexes will also be very large. While MyISAM is a nonclustered index, the data file is separated, and the index holds the pointer to the data file. The primary key index and secondary index are independent.

3. InnoDB does not save the specific number of rows of the table, and a full table scan is required when performing select count (*) from table. On the other hand, MyISAM uses a variable to save the number of rows of the entire table. When executing the above statement, you only need to read out the variable, which is very fast.

4. Innodb does not support full-text indexing, while MyISAM supports full-text indexing. MyISAM is more efficient in query.

Thank you for reading! After reading the above, do you have a general idea of the difference between mysql storage engines? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

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.