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

1/isam 
 ISAM is a well-defined and time-tested method of data table management that is designed to take into account that the number of times the database is queried is much larger than the number of updates. As a result, ISAM performs read operations quickly and does not consume large amounts of memory and storage resources. The two main disadvantages of ISAM are that it does not support transactional processing or fault tolerance: If your hard drive crashes, the data file cannot be recovered. If you are using ISAM in mission-critical applications, you must always back up all of your real-time data, and with its replication features, MySQL can support such a backup application. The 
 
 3/myisam 
 MyISAM is the default type in MySQL and is based on the traditional ISAM type, and 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.  The 
 
 Myiasm is a new version of the Iasm table, with the following extensions: 
 Binary level portability.  
    NULL column index. 
    supports large files.  
  better index compression. 
   better key? Statistical distribution 。 
      better and faster auto_increment processing. 

3/innodb 
 It provides transaction control capabilities that ensure that a set of commands are executed successfully, or that the results of all commands are rolled back when any one of the commands fails. It can be imagined that the ability to control transactions in electronic banking is very important. Supports commit, rollback, and other transactional features. The latest version of MySQL is already planning to remove support for BDB and to develop InnoDB in full. If you need a transaction-safe storage engine, it is recommended. If your data performs a large number of inserts or update, for performance reasons, you should use InnoDB. 
 for the InnoDB type of support, the main reason for the speed is that the AUTOCOMMIT default setting is open, and the program does not explicitly call begin to start a transaction, resulting in an automatic commit for each insert, which seriously affects the speed. You can call begin before you execute SQL, and multiple SQL forms a thing (even if the autocommit is open), which will greatly improve performance. 

　　
The following are some of the details and the specific implementation differences:

1.InnoDB does not support indexes of type Fulltext.



Where condition, the operation of the two tables is the same.
3. For a field of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, you can establish a federated index with other fields.

From table, InnoDB does not reestablish the table, but deletes one row at a time.

The master operation has no effect on InnoDB, and the workaround is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table, but not for tables that use additional InnoDB features, such as foreign keys.


Table set num=1 where name like "? a%"

7. 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.

8. Doing a start/commit operation on a table that does not support transactions has no effect and has been committed before committing a commit, testing:
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.

9. You can execute the following command to toggle non-transactional tables to transactions (data is not lost), and the InnoDB table is more secure than the MyISAM table:
ALTER TABLE TableName TYPE=INNODB;

InnoDB tables cannot be used with Repair Table command and MYISAMCHK-R table_name
But you can use check table, and Mysqlcheck [OPTIONS] database [tables]

11. The following parameters have been added to the command line starting the MySQL database to 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;

12. 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