MySQL Storage engine

Definition of the storage engine

Database storage engine is the database underlying software organization, database management System (DBMS) through the storage engine to create, delete, modify, query data. Different storage engines provide different storage mechanisms, indexing techniques, locking levels, and other features, using different storage engines, and you can also get specific features.

Add : The core of MySQL is the storage engine

Viewing the storage engine

command : show Engines (I use the navicat)

InnoDB Storage Engine

The default storage engine after mysql5.5 is the preferred engine for transactional databases. Support for transaction security tables (ACID: atomicity, consistency, isolation, persistence)

Features of the InnoDB:

2. The MySQL table provides transaction handling, rollback, crash resiliency, transactional security for multiple versions of concurrency control, InnoDB locking at the row level and also providing a non-locking read similar to Oracle in SELECT statements, which adds multiuser deployment and performance
4. 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
6. Supports Auto_increment, supports foreign key integrity constraints, stores data in tables, stores each table in primary key order, and if no primary key is specified when the table definition is not displayed, InnoDB generates a 6-byte rowid for each row, which is used as the primary key
8. InnoDB, the table structure of the created table is stored in a file with a. frm suffix, a 10MB size auto-extended data file named Ibdata1, and two log files with a 5MB size named Ib_logfile0 and Ib_logfile1. stored in table spaces defined by Innodb_data_home_dir and Innodb_data_file_path
10. support for B + Tree index, Adaptive Hash Index, FULLTEXT index (now supported), R-tree index (a spatial index data structure for creating spatial indexes on GIS data types, now supported) Supplemental: Adaptive Hash Index: The InnoDB engine monitors the usage of indexes on the table in real time, if it is considered Build a hash index to increase efficiency, and automatically build a hash index in an in-memory "adaptive Hash Index buffer" (a good look)

Summary: InnoDB's advantages lie in good transaction processing power, crash resilience and concurrency control, with poor read and write efficiency (not including concurrent writes) and a relatively large data space footprint

MyISAM Storage Engine

MyISAM is developed based on the ISAM engine, with high insertion, query speed, but no transaction support

Features of the MyISAM:

1. Dynamic size lines are less fragmented when deleting and updating and inserting mixes
2. 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
3. BLOBs and text columns can be indexed
4. Null is allowed in the indexed column, which accounts for 0~1 bytes per key
5. All numeric key values are stored first in high bytes to allow a higher index compression
6. Each MyISAM-type table has an internal auto_increment column that 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 (at least 10%)
7. Data files and index files can be placed in different directories
8. varchar and char columns can be up to 64KB
9. Tables based on the MyISAM storage engine support 3 different storage formats. Includes static, dynamic and compression types. The static type is the default storage format of MyISAM, its fields are fixed length, the dynamic type contains variable length fields, the length of the record is not fixed, and the compression type needs to use the Myisampack tool, which takes up less disk space.
10. 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)
11. Supports B + Tree index (different from InnoDB), fulltext Index, R-tree index

Summary: The advantage of MyISAM is the small footprint, fast processing speed, the disadvantage is that transaction integrity and concurrency are not supported

Memory Storage Engine

The memory storage engine stores the data in a table in memory, providing quick access to querying and referencing other table data

Characteristics of Memory:

1. Each memory storage engine-based table actually corresponds to a disk file with the same file name as the table name, type frm type, which stores only the structure of the table
2. Data files are stored in memory, which facilitates the fast processing of data and improves the efficiency of the entire table.
3. Memory does not support BLOB or text columns
4. MEMORY supports auto_increment columns and indexes on columns that can contain null values
5. The memory table is shared among all clients (like any other non-temporary table)
6. When you no longer need the contents of the memory table, to release the memories used by the storage table, you should execute the delete from or TRUNCATEtable, or delete the entire table (using the DROP table )
7. The memory table uses a fixed record length format
8. Supports hash and btree indexes

Summary: Memory used very little, because it is to put the data into memory, if there is an abnormality in the RAM will affect the data, if the restart or shutdown, all data will disappear, therefore, memory-based table life cycle is very short, is generally a one-time

/tr> tr>

features	innodb	myisan	memory	arch Ive
Storage Restrictions	 64TB	256TB	ram	none
Support transaction	yes	no	no	no
Support full-text indexing	yes	yes	no	no
Support B-Tree index	yes	yes	yes	no
Hash index support	Adaptive Hash index	no	yes	no
Support data cache	yes	no	n/a	no
Support foreign key	yes	no	no	no

Summary:

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