Difference between MyISAM and InnoDB in mysql table type (mysql does not support transaction resolution)

Differences between MyISAM and InnoDB in mysql tables: This is the default type, which is based on the traditional ISAM type, and ISAM is the abbreviation of IndexedSequentialAccessMethod (sequential access method with indexes, it is a standard method for storing records and files. compared with other storage engines, MyISAM provides most tools for checking and repairing tables. myISAM table

Differences between MyISAM and InnoDB in mysql table types: MyISAM is the default type, which is based on the traditional ISAM type, and ISAM is the abbreviation of Indexed Sequential Access Method (Sequential Access Method with indexes, it is a standard method for storing records and files. compared with other storage engines, MyISAM provides most tools for checking and repairing tables. myISAM table

Differences between mysql table types MyISAM and InnoDB

MyISAM: This is the default type. It is based on the traditional ISAM type, and ISAM is the abbreviation of Indexed Sequential Access Method (Sequential Access Method with indexes, it is a standard method for storing records and files. compared with other storage engines, MyISAM has most tools for checking and repairing tables. myISAM tables can be compressed and support full-text search. they are not transaction-safe and do not support foreign keys. If a transaction is rolled back, incomplete rollback is not atomic. If you execute a large number of SELECT statements, MyISAM is a better choice.

InnoDB: This type is transaction-safe. it has the same features as the BDB type and supports foreign keys. the InnoDB table is fast. it has more features than BDB. Therefore, if you need a transaction-safe storage engine, we recommend that you use it. if your data executes a large number of INSERT or UPDATE operations, InnoDB tables should be used for performance considerations,

For the InnoDB type labels that support transactions, AUTOCOMMIT is enabled by default, and the program does not explicitly call BEGIN to start the transaction. As a result, each inserted entry is automatically Commit, the speed is seriously affected. You can call begin before executing the SQL statement. Multiple SQL statements form a transaction (even if autocommit is enabled), which greatly improves the performance.
1. MySQL 4.0 and above support transactions, including non-max versions. 3.23 requires the max version mysqld to support transactions.

2. If no type is specified during table creation, the default value is myisam. transactions are not supported.
You can run the show create table tablename command to view the table type.

2.1 The start/commit operation on a table that does not support transactions has no effect. It has been submitted before the execution of the commit operation. test:
Execute an msyql:
Use test;
Drop table if exists tn;
Create table tn (a varchar (10) type = myisam;
Drop table if exists ty;
Create table ty (a varchar (10) type = innodb;

Begin;
Insert into tn values ('A ');
Insert into ty values ('A ');
Select * from tn;
Select * from ty;
You can see a record.

Execute another mysql:
Use test;
Select * from tn;
Select * from ty;
Only tn can see one record
Then on the other side
Commit;
You can see the record.

3. You can run the following command to switch the non-transaction table to the transaction (data will not be lost). The innodb table is safer than the myisam table:
Alter table tablename type = innodb;

3.1 innodb tables cannot use the repair table command and myisamchk-r table_name
However, you can use check table and mysqlcheck [OPTIONS] database [tables]

4. The following parameters are added to the command line for starting the mysql database to enable the newly released mysql data tables to use transactions by default (
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. You can temporarily change the default table Type:
Set table_type = InnoDB;
Show variables like 'table _ type ';
Or:
C: mysqlbinmysqld-max-nt-standalone-default-table-type = InnoDB

For mysql, the most representative table storage engine is InnoDB and MyISAM. The main difference between the two is whether to perform the data integrity check of Foreign keys.

The InnoDB Storage engine supports checking the external key constraints, including delete, update, and cascade ).

The advantage or advantage of the foreign key constraint check (foreign key enhancement) is:

1 If the association design is appropriate, it will make it more difficult for programmers to introduce data inconsistency into the database.

2. If the database server has the centralized constraints check function, you do not have to perform this type of check on the data on the application side, thus avoiding different reference programs using different methods to check constraints.

3. Use cascading update and deletion to simplify application code

4. Designing an overdue Association helps you to record the relationship between tables in the form of documents

However, the above benefits require the database server to spend additional costs to perform such checks, increasing the burden on the database server and thus affecting the database performance.

If you do not need a foreign key in the table and want to avoid reference integrity check, you can use MyISAM storage.

Engine. For applications that only have insert and select, selecting MyISAM will provide better performance. However, when using the MyISAM engine, if there is a foreign key Association, you need to process this association at the application level to ensure data integrity.

DBAs (Database Administrators) need to create topological associations, which may lead to difficulties in restoring a single table (MySQL allows you to temporarily disable foreign key checks when loading tables dependent on other tables, this reduces the difficulty ). For tables with foreign keys, pay attention to this problem.