MySQL Storage engine

Overview

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 .

Use the show ENGINES command to view the engine used by MySQL:

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.

InnoDB Storage Engine

InnoDB is the preferred engine for transactional databases, supports transaction-safe tables (ACID), supports row-level locking and foreign key constraints , and sees thatInnoDB 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. MySQL supports the foreign key storage engine only InnoDB.

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

In general, InnoDB is a good choice if transaction support is required and there is a high frequency of concurrent reads . The InnoDB storage engine is ideal for handling multiple concurrent update requests.

MyISAM Storage Engine

The MyISAM is based on the ISAM storage engine and extends it independently of the operating system. A Tb_demo table for the MyISAM engine is created and the following three files are generated:

1. tb_demo.frm, storage table definition; 2. Tb_demo. MYD, storing data; 3. Tb_demo. MYI, stores the index.

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 .

The MyISAM storage engine is ideal for use in the following situations:

1. Select the intensive table. The MyISAM storage engine is very fast in filtering large amounts of data, which is its most prominent advantage.
2. Insert intensive tables. The concurrent Insert feature of MyISAM allows data to be selected and inserted at the same time. For example, the MyISAM storage engine is ideal for managing mail or Web server log data.

Memory Storage Engine

The starting point for using the MySQL memory storage engine is speed. To get the fastest response time, the logical storage medium used is system memory. Although storing table data in memory does provide high performance, all memory data will be lost when the mysqld daemon crashes. The speed of the acquisition also brings some drawbacks. It requires that the data stored in the Memory data table use a constant length format, which means that a variable length data type such as BLOB and text cannot be used, and varchar is a variable-length type, but because it is a fixed-length char type within MySQL, So it can be used.

Memory storage engines are typically used in the following situations:

1. The target data is small and is accessed very frequently. Storing the data in memory, so it will cause the use of memory, can be controlled by the parameter max_heap_table_size the memory table size, set this parameter, you can limit the memory table maximum size.

2. If the data is temporary and required to be immediately available, it can be stored in the memory table.

3. Data stored in the memory table, if suddenly lost, will not have a substantial negative impact on the app service.

memory supports both hash index and B-Tree index . B-Tree indexes are better than hash indexes, you can use partial queries and wildcard queries, or you can use operators such as <, >, and >= to facilitate data mining. Hash indexes are very fast for equality comparisons, but are much slower for range comparison, so the hash index values are suitable for use in operators of = and <>, not in the < or > operators, nor in the ORDER BY clause.

MERGE

The merge storage engine is a combination of a set of MyISAM tables that must be identical in structure, although their use is not as prominent as other engines, but is useful in some cases. To be blunt, the merge table is just a few aggregators of the same MyISAM table, and there is no data in the merge table, and the merge type table can be queried, updated, deleted, and actually operated on the internal MyISAM table. The usage scenario for the merge storage engine.

For server logs, the most common storage strategy is to divide the data into tables, each associated with a specific time-end. For example, you can use 12 identical tables to store server log data, each named by the name of each month. When it is necessary to generate a report based on data from all 12 log tables, this means that multiple table queries need to be written and updated to reflect the information in those tables. Instead of writing these queries that may have errors, instead of merging the tables with a single query and then deleting the merge table without affecting the original data, deleting the merge table simply removes the definition of the merge table and has no effect on the internal table.

ARCHIVE

Archive is archived, many of the advanced features are no longer supported after archiving, supporting only the most basic insert and query functions. Prior to MySQL version 5.5, archive did not support indexing, but it began to support indexing in MySQL 5.5. Archive has a good compression mechanism, which uses the Zlib compression library, which is compressed in real time when the record is requested, so it is often used as a repository.

selection of storage engines

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

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

MySQL Storage engine