Four MySQL storage engines

Objective

The database storage engine is the database's underlying software organization, and the Database management system (DBMS) uses the data engine to create, query, update, and delete data. Different storage engines provide different storage mechanisms, indexing techniques, lock levels, and other features that use different storage engines and can also get specific functionality. Many different database management systems now support a number of different data engines. the core of MySQL is the storage engine .

Storage Engine View

MySQL provides developers with the ability to query the storage engine, and I use MySQL5.1, which can be used:

SHOW ENGINES

command to view the engine used by MySQL, the output of the command is (I use the Navicat Premium):

Seeing MySQL gives the user so many storage engines, including the engine that handles the transaction security table and the engine that comes out of the non-Thing security table.

If you want to see which engine the database uses by default, you can use the command:

SHOW VARIABLES like ' storage_engine ';

To view, the query results are:

In MySQL, there is no need to use the same storage engine throughout the server, and for specific requirements, you can use a different storage engine for each table. The value of the support column indicates whether an engine can be used: Yes indicates that it can be used, no indicates that it cannot be used, and default indicates that the engine is the current default storage engine. Here's a look at some of the most commonly used engines.

InnoDB Storage Engine

InnoDB is the preferred engine for transactional databases, supports transaction security tables (ACID), supports row locking and foreign keys, and sees that InnoDB is the default MySQL engine. InnoDB Main features are:

1. InnoDB provides MySQL with a thing-safe (acid-compatible) storage engine with Commit, rollback, and crash resilience. InnoDB locks the row-level and also provides a non-locking read similar to Oracle in the SELECT statement. These features increase multi-user deployment and performance. In SQL queries, you are free to mix tables of the InnoDB type with other MySQL table types, even in the same query

2. InnoDB is designed to handle the maximum performance of large amounts of data. Its CPU efficiency may be any other disk-based relational database engine lock unmatched

3. The InnoDB storage engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB its tables and indexes in a logical table space, the tablespace can contain several files (or raw disk files). This differs from the MyISAM table, such as in the MyISAM table where each table is stored in a detached file. The InnoDB table can be any size, even if the file size is limited to 2GB on the operating system

4. INNODB supports foreign key integrity constraints, when storing data in a table, each table's storage is stored in the primary key order, and if no primary key is specified when the table definition is displayed, InnoDB generates a 6-byte rowid for each row, which is used as the primary key

5. InnoDB is used in many large database sites that require high performance

InnoDB do not create a directory, when using InnoDB, MySQL will create a 10MB size auto-extended data file named Ibdata1 in MySQL Data directory, and two log files of 5MB size named Ib_logfile0 and Ib_logfile1

MyISAM Storage Engine

The 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, query speed, but does not support things . MyISAM Main features are:

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

2, when the deletion and update and insert operation mixed use, the dynamic size of the line to produce less fragmentation. This is done by merging adjacent deleted blocks, and if the next block is deleted, it expands to the next piece of auto-completion

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, the maximum key length is 1000 bytes, which can also be compiled to change, for a key length of more than 250 bytes, a key more than 1024 bytes will be used

5. BLOBs and text columns can be indexed

6, NULL is allowed in the column of the index, this value occupies the 0~1 bytes of each key

7. All numeric key values are stored with high bytes first to allow a higher index compression

8. Each MyISAM type table has a auto_increment internal column, which is updated when the insert and update operations, and the Auto_increment column is refreshed. So, the Auto_increment column update for the MyISAM type table is faster than the InnoDB type auto_increment

9, you can put the data files and index files in different directories

10. Each character column can have a different character set

11, the table with varchar can be fixed or dynamic record length

12, varchar and char columns can be up to 64KB

Creating a database using the MyISAM engine will result in 3 files. The name of the file begins with the name of the table, the file type of the extension: frm file store table definition, data file extension. MYD (MYData), the extension of the index file. MYI (Myindex)

Memory Storage Engine

The memory storage engine stores the data in the table in the RAM, providing quick access without querying and referencing other table data. The main features of memory are:

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

2, memory storage engine execution hash and btree miniature

3, you can have a non-unique key value in a memory table

4, Memory table using 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 between the clients (like any other non-temporary table)

8. Memory table is stored in memory, memory table and server in the Idle query processing, the creation of internal table sharing

9. When the contents of the memory table are no longer needed, to release the RAM used by the memories table, you should execute delete from or TRUNCATE TABLE, or delete the entire table (using drop table)

Selection of storage engines

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

function	MYISAM	Memory	InnoDB	Archive
Storage limits	256TB	Ram	64TB	None
Supporting Things	No	No	Yes	No
Support Full-Text indexing	Yes	No	No	No
Support Number Index	Yes	Yes	Yes	No
Support for Hash indexes	No	Yes	No	No
Support for data caching	No	N/A	Yes	No
Support for foreign keys	No	No	Yes	No

InnoDB is a good choice if you want to provide security for things (acid-compatible) capabilities for commit, rollback, crash resiliency, and require concurrency control

If the data table is used primarily for inserting and querying records, the MyISAM engine can provide high processing efficiency

If the data is only temporarily stored, the amount of data is small, and does not require a high level of data security, you can choose to save the data in memory of the storage engine, MySQL use the engine as a temporary table, the intermediate results of the query stored

If you have only insert and select operations, you can choose Archive,archive to support high concurrency inserts, but it is not transaction-safe by itself. Archive is ideal for storing archived data, such as logging information can be used archive

Which engine to use requires flexibility, multiple tables in a database can use different engines to meet a variety of performance and real needs , and using the right storage engine will improve the performance of the entire database

Four MySQL storage engines