Turn!! Storage engine explained in MySQL (Innodb,myisam,memory and other storage engine comparison)

The storage engine in MySQL:

1, the concept of storage engine

2. View the storage engines supported by MySQL

3. Features of several common storage engines in MySQL

4. Mutual transformation between storage engines

First, the storage engine:

1, storage engine is how to realize the storage of data, how to index the stored data and how to update, query data and other technology implementation methods.

2. Data in MySQL is stored in files (or memory) in a variety of different technologies, each of which uses different storage mechanisms, indexing techniques, locking levels and ultimately providing a wide range of different functions and capabilities. In MySQL, these different technologies and associated functions are called storage engines.

Second, the MySQL view engine

1, show engines; Look at the storage engines supported by MySQL and get the default storage engine from MySQL.

2, show variables like '% storage_engine '; View the default storage engine for MySQL

3, show create TABLE tablename; To view the storage engine used by a particular table, this default storage engine has been modified!

4. Show table status from database where Name= "tablename"

An accurate view of the storage engine used by a table in a database

Three, MySQL commonly used several kinds of storage engine: InnoDB, BDB, MyISAM, memory and the explanation of these several engines;

InnoDB Storage Engine:

(1) InnoDB storage Engine The MySQL table provides the security of transactions, rollbacks, and system crash repair capabilities and multi-version burst-controlled transactions.

(2) InnoDB support Self-growth column (auto_increment), the value of the self-growth column cannot be empty, if the use of the time is empty, how to automatically save the existing value starts to add value, if there is but larger than the present, then save this value.

(3) The InnoDB storage engine supports foreign keys (foreign key), and the table on which the foreign key resides is called a child table and depends on the table called the parent table.

(4) The InnoDB storage engine is most important to support transactions, as well as transaction-related functions.

(5) The InnoDB storage engine supports MVCC row-level locks.

MyISAM Storage Engine:

1, MyISAM This storage engine does not support transactions, row-level locks are not supported, only concurrent inserted table locks are supported, mainly for high-load SELECT.

2. MyISAM Type table supports three different storage structures: Static type, dynamic type, compression type.

(1) Static type: The size of the defined table column is fixed (that is, does not contain: Xblob, xtext, varchar and other variable length data types), so that MySQL will automatically use the static MyISAM format.

Tables that use a static format have a high performance because the overhead of storing data in a predetermined format during maintenance and access is low. But this high-performance has a space in exchange for, because at the time of the definition is fixed, so regardless of the value in the column is how large, will be the maximum value, occupy the entire space.

(2) Dynamic type: If the column (even if only one column) is defined as dynamic (Xblob, xtext, varchar and other data types), then MyISAM automatically use the dynamic type, although the dynamic table occupies less space than the static table, but brings a performance reduction, Because if the content of a field changes, its position is likely to need to be moved, which results in fragmentation. As the data changes, the fragmentation increases and data access performance decreases accordingly.

There are two workarounds for reducing data access for fragmentation reasons:

@1, use static data types whenever possible

@2, often using the Optimize table statement, will defragment the table and restore space lost due to table updates and deletions.

(If the storage engine does not support optimize table, you can dump and reload the data, which can also reduce fragmentation)

(3) Compression type: If you create a table in this database that is read-only throughout the life cycle, this is the case with MyISAM compressed tables to reduce space usage.

Memory Storage Engine:

(1) Memory storage engine is a bit different than some of the previous storage engines, it uses the data stored inside it to create the table, and all the data is stored in memory.

(2) Each memory storage engine-based table actually corresponds to a disk file with the same file name and table name, and the type is. frm. The file only stores the structure of the table, and its data files are stored in memory, which facilitates the fast processing of data and improves the processing capacity of the whole table.

(3) The memory storage engine uses the hash index by default, which is faster than using the B-+tree type, and can be referenced at creation time if the reader wishes to use the B-tree type.

(4) Memory storage engine file data is stored in the RAM, if the MYSQLD process exception, restart or shut down the machine this data will disappear. So the life cycle of the tables in the memory storage engine is very short and is typically used only once.

Blackhole Storage Engine:

(1) Support transactions, and support MVCC row-level lock, mainly used for logging or synchronous archiving, this storage engine unless there is a special purpose, otherwise not suitable for use!

Four, each storage engine transforms each other:

1. ALTER TABLE TableName engine = innodb/myisam/memory//Modify the storage engine for this table

Advantages: simple, and suitable for all engines.

Cons: (1), this conversion method takes a lot of time and I/o,mysql to perform a copy of the row from the old table to the new table, so the efficiency is lower.

(2), during the conversion period, the source table was read lock

(3), from one engine to another engine to do table conversion, all the proprietary features belonging to the original engine will be lost, such as from InnoDB to MyISAM InnoDB index will be lost!

2. Using dump (dump) import (import)

Pros: Using mysqldump this tool to export modified data will be saved in. sql file, you can operate on this file, so you have more and better control, such as modify the table name, modify the storage engine and so on!

Take a look at the exported table:

The above is the use of mysqldump guide out of the table is a. sql file you can write this file as required, and import this file into the ok!

3, the first way is simple, the second way of security, this third way is considered the first two ways of compromise, create select:

(1), create table newtable like oldtable;

(2), ALTER TABLE newtable engine= innodb/myisam/memory

(3), insert into newtable select * from Oldtable;

If the amount of data is small, this way is still good!

There is also a more efficient way to do this is to increment the population, commit a transaction after populating each incremental block, and not cause the undo log file to be too large;

(1) Start transaction

(2) INSERT INTO newtable select * from oldtable where ID (primary key) between x and Y;

(3) Commit

Such data after the fill with the need for a new table, the old table also exists, do not need to haunt can be deleted, very convenient!

Turn!! Storage engine explained in MySQL (Innodb,myisam,memory and other storage engine comparison)