MySQL storage Engine "InnoDB, MyISAM, Memory"

Database, MySQL such as the existence of multi-storage engine database software, clear common storage engine differences, using the appropriate storage engine, make the project run smoother, sometimes for a project, even more important than the project itself. This article aims to talk about the differences between common three kinds of storage engines, pros and cons. It is learning and sharing.

1: View the storage engines supported by MySQL:

1.1 Show engines;

1.2 Show variables like ' have% ';

In this way, we know what the storage engine supported by MySQL is.

Below, analyze a few common storage engines. Innodb,myisam and memory.

2:INNODB Storage Engine

2.1:INNODB transaction security with transaction, rollback, crash repair capability and multiple versions concurrency

2.2: Auto_increment column on InnoDB:

2.2.1:INNODB supports auto-grow columns, this column cannot be empty, and the value must be unique

2.2.2: This column must be the primary key. When inserted, the value is not specified, and the default is the value of the self-increment. When you specify 0 or NULL, it is also the self-increment value. Specifies a value that is valid and does not exist, and then automatically starts from that value

Foreign Key in 2.3:innodb:

InnoDB supports foreign keys. The table in which the foreign key resides is often called a child table. The dependent table is called the parent table. In the parent table, the fields associated with the quilt table must be the primary key of the parent table.

(Foreign key: can be simply understood as: when the data of the parent table is updated, deleted, added, the child table data will also change.) Examples are many, here is not an example)

2.4:innodb's storage engine and its pros and cons:

The InnoDB storage engine has a storage format of three files:

The. FRM table structure file, which holds information such as the field definition when the table was created.

Table data files, stored in the Innodb_data_home_dir directory,

The index file of the table, stored in the Innodb_data_file_path directory

The InnoDB storage engine has good acid properties.

InnoDB disadvantage: Reading and writing efficiency is relatively myisam. The amount of disk space that is occupied is relatively large.

Ideal for use in 2.5:INNODB applications:

High concurrency, with more tables for update operations. A table that needs to use transactions. Tables that are required for automatic disaster recovery.

3:myisam Storage Engine:

The 3.1:myisam Storage engine table is stored with three files:

. frm file, storing the structure of the table

. myd files, storing data for tables

. myi file, index of the stored table

Storage format for the 3.2:myisam storage engine

3.2.1: Static type

All columns of the table are static (fixed length). In this case, maintaining and accessing data stored in a predefined format requires very little overhead. However, more space is needed because the data is stored with the maximum space for each column and there is a wasted space. Of course, the disk is not a problem.

3.2.2: Dynamic type

The column of the indicator has a dynamic column (indefinite length). The advantage is that there is less space to use. The disadvantage is that when you update data, you need to move the data, generate internal fragmentation, and reduce the efficiency of maintenance and access.

For this problem, we can: (1) Try to use the fixed Length field (2) Use the Optimize table statement to organize the fragmentation in the table

3.2.3: Compression type:

For the application's declaration cycle, read-only data tables can be converted to MyISAM compressed tables by: Myisampack tool to reduce the disk space used

Advantages and disadvantages of the 3.4:myisam storage engine:

Advantages: Small footprint, fast processing speed (relative InnoDB)

Cons: Transactional integrity and concurrency not supported

4:memory Storage Engine

4.1:memory Storage engine file Storage form

The memory storage engine also forms a. FRM table structure file on the disk, except that the data pieces of the table are not stored as files on disk. Because the data is stored in memory, access is faster. However, it is necessary to consider the persistence of data on memory.

4.2:memory the index type of the storage engine

The default is the hash index, and the Btree index is also supported

4.3:memory storage Engine data storage cycle

Data is stored in memory and once the server shuts down, the data will no longer exist.

Advantages and disadvantages of the 4.4:memory storage engine:

The memory storage engine does not support variable-length table columns

Auto_increment column not supported prior to MySQL4.1.0

4.5:memory Storage Engine Usage Scenarios

Fast speed requirements, temporary data

After the loss, the whole project has no or negative impact on the time.

From for notes (Wiz)

MySQL storage Engine "InnoDB, MyISAM, Memory"