Selection of table type (storage engine)

Selection of table type (storage engine)

Plug-in storage engine is one of the most important features of MySQL database, users can choose how to store and index data, use transactions and so on according to the needs of the application.

The storage engines supported by MySQL5.0 include MyISAM, InnoDB, BDB, Memory, MERGE, EXAMPLE, NDB Cluster, ARCHIVE, CSV, blackhole, federated, etc. where InnoDB and BDB provide transaction security tables, other storage engines provide non-transactional security tables.

By default, creating a new table does not specify the storage engine for the table, the new table is the default storage engine, and if necessary, set Default-table-type.

Eg:create Table AI (

I bigint () not null auto_increatement,

Primary KEY (i)

) Engine=myisam DEFAULT CHARSET=GBK;

Alter table Ai Engine=innodb;

MyISAM

MyISAM is the default storage engine for MySQL. MyISAM does not support transactions, and foreign keys are not supported. The advantage is that access is fast, there is no requirement for the integrity of things, or a SELECT, insert-based application can basically use this engine to create tables. Each myisam is stored on disk as 3 files, with the same file name as the table name, with the following extensions:

. frm (save table definition)

MYD (MYData, storing data)

MYI (myindex, storage index)

Locking : locks the entire table instead of rows.

Concurrency: Shared locks (read locks) are available on all tables when reading data, and each connection does not interfere with each other. when writing data, obtaining an exclusive lock will lock the entire table, while the other connection requests ( Read, write request ) are in the waiting list .

InnoDB

The InnoDB storage Engine provides transactional security with the ability to commit, rollback, and crash resilience. As with MyISAM, InnoDB writes less efficiently and consumes more disk space to preserve data and indexes.

Auto-Grow columns must be indexed.

MySQL the storage engine that supports foreign keys is only InnoDB , When creating foreign keys, it is required that the parent table must have a corresponding index, and the Word table will automatically create the corresponding index when the foreign key is created.

InnoDB stores tables and indexes in two ways: 1, using shared tablespace storage, 2, using multi-table space storage

MEMORY

The memory storage engine creates a table using the contents of the existing memories. Each memory meter only actually corresponds to one disk file, in the format. frm. Fast access (because the data is in memory, the hash index is used by default, but once the service is closed, the data in the table is lost)

MERGE

The merge storage engine is a set of myisam that must be structurally identical, the merge table itself has no data, and queries, updates, deletions, and so on, of the merge type's tables are actually performed on the internal MyISAM table.

Selection of table type (storage engine)