Differences and comparisons of storage engines in MySQL

MyISAM storage engine

MyISAM is based on the ISAM storage engine and extends it. It is one of the most commonly used storage engines in Web, data warehousing and other application environments. MyISAM has higher insertion and query speeds, but does not support transactions .

The main features of MyISAM are:

1. Large files (up to 63 bits in length) are supported on file systems and operating systems that support large files.

2. Dynamically sized rows produce less fragmentation when delete, update, and insert operations are mixed. This is done automatically by merging adjacent deleted blocks and extending to the next block if the next block is deleted.

3. The maximum number of indexes for each MyISAM table is 64, which can be changed by recompiling. The maximum number of columns per index is 16

4. NULL is allowed in the index column, this value occupies 0~1 bytes for each key

5. You can put data files and index files in different directories (InnoDB is placed in one directory)

The MyISAM engine uses B+Tree as the index structure, and the data field of the leaf node stores the address of the data record .

The following figure is a schematic diagram of the MyISAM index:

Here, we assume that the table has three columns. Assuming that Col1 is the primary key, the above figure is a diagram of the primary key of a MyISAM table.

It can be seen that the MyISAM index file only saves the address of the data record .

In MyISAM, there is no structural difference between the primary index and the secondary index (Secondary key), except that the primary index requires the key to be unique, while the secondary index key can be repeated.

If we create a secondary index on Col2, the structure of this index is as shown below:

It is also a B+Tree , and the data field stores the address of the data record.

Therefore, the index retrieval algorithm in MyISAM is to first search the index according to the B+Tree search algorithm. If the specified Key exists, the value of its data field is taken out, and then the corresponding data record is read using the value of the data field as the address.

The MyISAM index method is also called "non-clustered", which is called this to distinguish it from the clustered index of InnoDB.

InnoDB Storage Engine

InnoDB is the preferred engine for transactional databases. It supports transaction-safe tables (ACID), row locking , and foreign keys . As you can see in the figure above, InnoDB is the default MySQL engine .

The main features of InnoDB are:

1. InnoDB provides MySQL with a transaction-safe (ACID-compliant) storage engine with commit, rollback, and crash recovery capabilities. InnoDB locks at the row level and also provides an Oracle-like non-locking read in SELECT statements. These features increase multi-user deployment and performance. In SQL queries, you can freely mix InnoDB tables with other MySQL table types, even in the same query.

2. InnoDB is designed for maximum performance in processing huge amounts of data . Its CPU efficiency is probably unmatched by any other disk-based relational database engine lock.

3. The InnoDB storage engine is fully integrated with the MySQL server. The InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a logical tablespace , which can contain several files (or raw disk files). This is different from MyISAM tables, where each table is stored in a separate file . InnoDB tables can be of any size, even on operating systems that limit file sizes to 2GB.

4. InnoDB supports foreign key integrity constraints . When storing data in a table, each table is stored in the order of the primary key. If the primary key is not specified when defining the table, InnoDB will generate a 6-byte ROWID for each row and use it as the primary key.

Although InnoDB also uses B+Tree as the index structure, its specific implementation is completely different from MyISAM.

The first major difference is that InnoDB's data files are themselves index files .

From the above, we know that MyISAM index files and data files are separate , and the index files only save the addresses of data records.

In InnoDB, the table data file itself is an index structure organized as B+Tree, and the leaf node data field of this tree stores complete data records .

The key of this index is the primary key of the data table, so the InnoDB table data file itself is the primary index.

The above figure is a schematic diagram of the InnoDB primary index (which is also a data file). You can see that the leaf node contains complete data records.

This type of index is called a clustered index . Because InnoDB's data files themselves are clustered by primary keys, InnoDB requires that the table must have a primary key (MyISAM may not have one). If not explicitly specified, the MySQL system will automatically select a column that can uniquely identify the data record as the primary key. If such a column does not exist, MySQL will automatically generate an implicit field for the InnoDB table as the primary key. This field is 6 bytes long and of long integer type.

The second difference from the MyISAM index is that the InnoDB auxiliary index data field stores the value of the corresponding record primary key instead of the address. In other words, all secondary indexes of InnoDB reference the primary key as the data field.

For example, the following figure shows an auxiliary index defined on Col3:

Here, the ASCII code of English characters is used as the comparison criterion. The implementation of clustered index makes the search by primary key very efficient, but the auxiliary index search requires two index searches: first search the auxiliary index to obtain the primary key, and then use the primary key to retrieve the record from the primary index .

Understanding the index implementation methods of different storage engines is very helpful for the correct use and optimization of indexes . For example, after knowing the index implementation of InnoDB, it is easy to understand why it is not recommended to use a field that is too long as the primary key, because all secondary indexes refer to the primary index, and a primary index that is too long will make the secondary index too large.

For another example, it is not a good idea to use a non-monotonic field as the primary key in InnoDB, because the InnoDB data file itself is a B+Tree. A non-monotonic primary key will cause the data file to be frequently split and adjusted when inserting new records in order to maintain the characteristics of the B+Tree, which is very inefficient. Using an auto-increment field as the primary key is a good choice.

MEMORY storage engine

The MEMORY storage engine stores data in a table in memory, providing fast access without querying or referencing data in other tables.

The main features of MEMORY are:

1. Each MEMORY table can have up to 32 indexes, 16 columns per index, and a maximum key length of 500 bytes

2. MEMORY storage engine performs HASH and BTREE microcosm

3. You can have non-unique key values ​​in a MEMORY table

4. The MEMORY table uses a fixed record length format

5. MEMORY does not support BLOB or TEXT columns

6. MEMORY supports AUTO_INCREMENT columns and indexes on columns that can contain NULL values

7. MEMORY tables are shared among all clients (just like any other non-TEMPORARY table)

8. MEMORY table memory is stored in memory. The memory is shared by the MEMORY table and the internal table created by the server when the query is processed.

9. When the contents of the MEMORY table are no longer needed, to release the memory used by the MEMORY table, you should execute DELETE FROM or TRUNCATE TABLE, or delete the entire table (using DROP TABLE)

Archive storage engine Storage engine selection

Different storage engines have their own characteristics to meet different needs, as shown in the following table:

InnoDB : If you want to provide transactional safety (ACID compatibility) capabilities with commit, rollback, and crash recovery capabilities, and require concurrency control, InnoDB is a good choice

Differences between InnoDB and MyISAM:

1>. InnoDB supports transactions, while MyISAM does not support transactions

2>.InnoDB supports row-level locks, while MyISAM supports table-level locks

3>.InnoDB supports MVCC, while MyISAM does not

4>.InnoDB supports foreign keys, while MyISAM does not

5>.InnoDB does not support full-text indexing, but MyISAM does. (X)

MyISAM : If the data table is mainly used to insert and query records, the MyISAM engine (but does not support transactions) can provide higher processing efficiency.

Memory : If you only need to temporarily store data, the amount of data is not large, and high data security is not required, you can choose to store the data in the memory engine. MySQL uses this engine as a temporary table to store the intermediate results of the query. The data is processed very quickly but the security is not high.

Archive : If there are only INSERT and SELECT operations, you can choose Archive. Archive supports high-concurrency insert operations, but it is not transaction-safe. Archive is very suitable for storing archived data, such as recording log information.

You need to be flexible in choosing which engine to use. Multiple tables in a database can use different engines to meet various performance and practical needs. Using a suitable storage engine will improve the performance of the entire database.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.