The difference between MySQL storage engine MyISAM and InnoDB

Write in front

MySQL , Oracle,sqlserver are all in the relational database, MySQL database is also known as one of the most widely used open source databases in the industry, and the plug-in storage engine is one of its most important features, Learn about the features of the MySQL database that its storage engine can use more easily.

This program is marked

• Learn about the MySQL plug-in storage engine

• Mastering the difference between MyISAM and InnoDB storage engines

What is a plug-in storage engine?

MySQL Storage engine is a plug-in storage engine that allows MySQL to provide the official, and allows third parties to develop their own storage via the MySQL API interface The . MySQL uses a variety of different techniques to store data in different file systems, such as the memory engine, which stores data directly in memory. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately offers a wide range of capabilities and capabilities, and by selecting different technologies, users can gain additional speed or functionality to improve the overall functionality of the application. For example, if you want to study a large amount of temporary data, you might need to use a memory storage engine that can store all of the tabular data in memory and help users complete the application. Alternatively, a user might need a database that supports transactional processing to ensure that the data is backed up when the transaction is unsuccessful, which requires the use of a transaction-enabled storage engine (InnoDB engine). These different techniques and associated functions are called storage engines (also known as table types) in MySQL. MySQL has a number of different storage engines configured by default and can be pre-set or enabled in MySQL server. You can choose the storage engine for servers, databases, and tables to provide maximum flexibility when choosing how to store information, how to retrieve that information, and what performance and functionality you need to combine with your data. MYSQL5.5 provides 10 storage engines, Memory,csv,performance_schema,blackhole,myisam,mrg_myisam,archive,federated,innodb,aria. Inodb and MyISAM are the two most used storage engines in MySQL's many storage engines. So this article also focuses on the differences between these two storage engines.

MyISAM the difference between the InnoDB storage engine and the

before talking about the difference between the two, it is necessary to first understand the concept that the storage engine is a table-level concept, so it becomes a table type, and each creation of a table can specify the storage engine used, but it is not recommended that each table cross-use different engines.

1 Data Files

MyISAM Storage engine creates three files per table

Table_name.frm	Structure of the storage table
tabl E_name. MYD	Storing data
table_name. MYI	Storage Index

InnoDB There are two ways to store data in a table space: data and indexes for all INONODB tables are placed in the same tablespace and each table uses a table space to store the data and indexes of the table alone.

When all tables are in the same tablespace, their data files are stored in the data directory under ibddata[#] to name them. This approach is not conducive to management and backup recovery. Therefore, the second way is generally used, each table creates a separate table space to implement the advanced features supported by InnoDB, such as single-table import and export, single-label backup, and so on. Data file is

Span style= "font-size:16px;font-family: ' Courier New ';" > table_name.ibd	store data and index
Table_ name.frm	Storage table Structure Definition

2 Transactional

MyISAM the types of tables emphasize performance, perform faster than the InnoDB type but do not provide transactional support, and do not support foreign keys for scenarios where a large number of select operations are applied. InnoDB supports transactions and divides transactions into four isolation levels, read-uncommitted: READ UNCOMMITTED is prone to dirty reading; read-committed: Read Commit, this level is not repeatable read, Repeatable-read: Repeatable read, this level is prone to phantom reading ; SERIALIZABLE: serialization; The default level is Repeatable-read. Suitable for scenarios where transaction requirements are high, it is more appropriate to handle a large number of short-term transactions.

3 Lock particle size

MyISAM the lock granularity is coarser and is a table-level lock. The InnoDB is a row-level lock, and a gap lock is used to prevent phantom reads. Of course, the InnoDB row-level lock is not absolute, when the SQL statement in the execution process can not determine the scope of the scan will also lock the entire table, such as Updatestudents set sex= "M" where name like "%aaa%";

4 Index Type

InnoDB is a clustered index, secondary index, MyISAM is a nonclustered index, and full-text index (fulltext index) is supported.

5 Backup Policy

InnoDB Support Hot Spares, Win Bei, cold. Can be recovered safely after a crash. MyISAM most support Win Bei and cold. Unable to recover safely after a crash.

6 other

InnoDB supports adaptive hash indexes and uses MVCC to support high concurrency. In terms of performance, it supports pre-read operations, memory data buffering, memory index caching, insert operation buffers, and so on. The InnoDB engine supports features such as deferred index updates, and table compression.

In recent years, InnoDB has replaced the myisam trend. The default storage engine for MySQL and mariadb has been using InnoDB.

This article is from the "Keep Simple Keep Stupid" blog, make sure to keep this source http://yangzhiheng.blog.51cto.com/11586378/1980892

The difference between MySQL storage engine MyISAM and InnoDB