The difference between Engine=innodb and Engine=myisam in MySQL

MyISAM types do not support advanced processing such as transaction processing, and InnoDB type support.

Tables of the MyISAM type emphasize performance, which is performed more quickly than the InnoDB type, but does not provide transactional support.

InnoDB provides transactional support and advanced database features such as foreign keys.

This makes it possible to use different storage types depending on the data table.

In addition, MyISAM types of binary data files can be migrated in different operating systems.

That is, it can be copied directly from the Windows system to a Linux system.

Modify:

ALTER TABLE tablename ENGINE = MyISAM;

MyISAM:

This is the default type, which is based on the traditional ISAM type,

ISAM

is an abbreviation for indexed sequential access method, which has indexed sequential access methods,

It is the standard way to store records and files.

Compared to other storage engines,

MyISAM has most of the tools for checking and repairing tables.

MyISAM tables can be compressed, and they support full-text search.

They are not transaction-safe and do not support foreign keys.

If the rollback of a thing causes incomplete rollback, it does not have atomicity.

If executing a lot of select,myisam is a better choice.!!!!!!!!!!!

InnoDB:

This type is transaction-safe. It has the same characteristics as the BDB type, and they also support foreign keys.

InnoDB tables are fast, with features richer than BDB,

Therefore, it is recommended to use a transaction-safe storage engine if one is required.

If your data performs a large number of inserts or UPDATE,

For performance reasons, you should use the InnoDB table.!!!!!!!!!!!!!

For a table of InnoDB types that support things,

The main factors that affect speed are:

Autocommit The default setting is open,

And the program does not explicitly invoke BEGIN TRANSACTION,

Causes each insert to automatically commit, which seriously affects the speed.

You can call begin before you execute SQL,

Multiple SQL forms a thing (even if the autocommit is open), which can greatly improve performance.

===============================================================

1. More than 4.0 mysqld support transactions, including non-max versions. 3.23 requires Max version mysqld to support transactions.

2. If you do not specify a type when creating a table, the default is MyISAM, and transactions are not supported.!!!!!!!!!!!!!

You can use the Show CREATE TABLE tablename command to see the type of the table.

2.1 Doing a start/commit operation on a table that does not support transactions has no effect and has been committed before committing a commit.

Test:

Perform a msyql:

Use test;

drop table if exists TN;

Create TABLE TN (a varchar (ten)) Type=myisam;

drop table if exists Ty;

Create table Ty (a varchar (ten)) Type=innodb;

Begin

Insert into TN values (' a ');

Insert into Ty values (' a ');

SELECT * from TN;

select * from Ty;

Can see a record.

To perform another MySQL:

Use test;

SELECT * from TN;

select * from Ty;

Only TN can see a record

And then on the other side

Commit

Can only see the record.

3. You can execute the following command to toggle non-transactional tables to transactions (data is not lost),

The InnoDB table is more secure than the MyISAM table:

ALTER TABLE TableName TYPE=INNODB;

3.1 InnoDB table cannot use Repair Table command and MYISAMCHK-R table_name

But you can use check table, and Mysqlcheck [OPTIONS] database [tables]

4. The following parameters were added to the command line that started the MySQL database.

You can make the newly published MySQL data table default to use transactions (only the Create statement is affected).

--default-table-type=innodb

Test command:

Use test;

drop table if exists TN;

Create TABLE TN (a varchar (10));

Show CREATE TABLE TN;

5. Temporarily changing the default table type can be used:

Set Table_type=innodb;

Show variables like ' table_type ';

Or:

C:/mysql/bin/mysqld-max-nt--standalone--default-table-type=innodb

?

The difference between Engine=innodb and Engine=myisam in MySQL