mysql storage engine and applicable scenarios

mysql storage engine and applicable scenarios

MySQL storage engines include MyISAM, InnoDB, MEMORY, CVS, MRG_MyISAM, BLACKHOLE, SEQUENCE, ARCHIVE, etc. The commonly used ones are InnoDB, MyISAM and MEMORY. You can use the command:

show vaiables like'storage_engine'

To get the engine used by the current database.

The following part mainly comes from:

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 the Web, data warehousing, and other application environments. MyISAM has high insertion and query speed, but does not support transactions .

The main features of MyISAM are:

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

2. When deleting and updating and inserting operations are mixed, dynamically sized rows produce less fragmentation . This is done automatically by merging adjacent deleted blocks, and if the next block is deleted, extending to the next block is done automatically.

3. The maximum number of indexes per 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 of each key

5. Data files and index files can be placed 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.

MyISAM's indexing method is also called "non-clustered". The reason for this is to distinguish it from InnoDB's clustered index.

InnoDB storage engine

InnoDB is the preferred engine for transactional databases . It supports transaction security 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 security (ACID compatible) 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 the SELECT statement. These features increase multi-user deployment and performance. In SQL query, you can freely mix InnoDB type 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 may be 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 puts its tables and indexes in a logical table space, which can contain several files (or raw disk files). This is different from MyISAM tables, for example, each table in the MyISAM table is stored in a separate file. InnoDB tables can be of any size, even on operating systems where the file size is limited to 2GB

4. InnoDB supports foreign key integrity constraints. When storing data in a table, the storage of each table is stored in the order of the primary key. If the primary key is not displayed in the table definition, InnoDB will generate a 6-byte ROWID for each row , And use this as the primary key.

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

The first major difference is that InnoDB's data files are themselves index files. From the above, the MyISAM index file and the data file are separated, and the index file only saves the address of the data record. In InnoDB, the table data file itself is an index structure organized by B+Tree, and the leaf node data field of this tree saves 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 second difference from MyISAM index is that InnoDB's secondary index data field stores the value of the primary key of the corresponding record instead of the address. In other words, all InnoDB secondary indexes refer to the primary key as the data field.

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 too long fields as the primary key, because all secondary indexes refer to the primary key. Index, a long primary index will make the secondary index too large. For another example, using non-monotonic fields as the primary key is not a good idea in InnoDB, because the InnoDB data file itself is a B+Tree, and the non-monotonic primary key will cause the data file to maintain the characteristics of B+Tree when inserting new records. Frequent split adjustments are very inefficient, and using auto-increment fields as primary keys is a good choice.

MEMORY storage engine

The MEMORY storage engine stores the data in the table in memory, and provides fast access without querying and referencing other table data.

The main features of MEMORY are:

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

2. MEMORY storage engine executes the epitome of HASH and BTREE

3. There can be 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. The MEMORY table is shared among all clients (just like any other non-TEMPORARY table)

8. The memory of the MEMORY table is stored in the memory. The memory is shared between the MEMORY table and the internal table created when the server is idle during query processing.

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)

The choice of storage engine

InnoDB: If you want to provide transaction security (ACID compatible) capabilities for commit, rollback, and crash recovery capabilities, and require concurrency control, InnoDB is a good choice

The difference between InnoDB and MyISAM:

1. InnoDB supports things, but MyISAM does not support things

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

3. InnoDB supports MVCC, but MyISAM does not

4. InnoDB supports foreign keys, but 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 (but does not support transactions) engine can provide higher processing efficiency

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

Archive: If you only have INSERT and SELECT operations, you can choose Archive. Archive supports highly concurrent insert operations, but it is not transaction safe. Archive is very suitable for storing archived data, such as recording log information can use Archive

Copyright statement: This article is the original article of the CSDN blogger "zgrgfr". It follows the CC 4.0 BY-SA copyright agreement. Please attach the original source link and this statement for reprinting.

Original link:

Reference: mysql storage engine and applicable scenarios-Cloud + Community-Tencent Cloud