MySQL Engine explained

MySQLsupports three engines: ISAM,MyISAM , and HEAP To select different engines depending on the requirements.

The engine of the database depends on how MySQL was compiled at the time of installation, and to add a new engine, it must be recompiled

--with-plugins=partition,innobase

ISAM:

Read operations are fast and do not consume large amounts of memory and storage resources

Do not support the handling of things

Cannot be fault tolerant

The data cannot be recovered if the hard drive crashes

MyISAM: (default table Type)

MyISAM is MySQL's ISAM extended format and the default database engine, like the following extensions:

1), the portability of the binary hierarchy.

2), NULL column index.

3), less fragmentation of the variable-length line than the ISAM table.

4), supporting large files.

5), better index compression.

6), better key code statistical distribution.

7), better and faster auto_increment processing.

MyISAM Suitable for: (1) do a lot of count calculation, (2) Insert infrequently, query very frequently; (3) No transaction

InnoDB Berkley DB (BDB): (Supports security of things)

Support for foreign keys

Support Row lock

Support Transactions

Dual License: GPL and commercial license

Ideal for environments with a high number of update and insert operations, higher concurrent QPS ( query rate per second )

To view the database engine:

View now available storage engine: mysql> show engines;

View current default storage engine: mysql> show variables like '%storage_engine% ';

See what engine is used for a table (the parameters behind the parameter engine in the display result):

Mysql> Show create table table name;

such as: mysql> show Create test_db;

To modify the MySQL table space engine:

set InnoDB as the default engine:

Under [mysqld] in configuration file my.cnf, add Default-storage-engine=innodb

To Modify the table engine:

ALTER TABLE test_db Engine=myisam;

ALTER TABLE test_db ENGINE=INNODB;

Note: If you add the InnoDB engine error, you need to delete the Ib_logfile0,ib_logfile1 file in the/mysql/data directory to

MySQL Engine explained