Introduction to the difference between Engine=innodb and Engine=myisam in MySQL

MyISAM type does not support advanced processing such as transaction processing, and InnoDB type support, this article for you to explain the difference between Engine=innodb and Engine=myisam in MySQL, do not understand the friends can learn, I hope to help you

When you first build a database with MySQL administrator, the table defaults to the InnoDB type, and you don't care. Later, when using Access2mysql to guide the data found can only lead to myisam type of table, do not know what the difference between the two types, went to check. The MyISAM type does not support advanced processing, such as transaction processing, but InnoDB type support. Tables of the MyISAM type emphasize performance, which executes more than INNODB types faster, but does not provide transactional support, while InnoDB provides transactional support for advanced database features such as foreign keys. This makes it possible to use different storage types for different uses of the datasheet.





In addition, the binary data files of the MyISAM type can be migrated in different operating systems. That is, it can be used directly from the Windows system to the Linux system.





Modification:





ALTER TABLE tablename ENGINE = MyISAM;





MyISAM: This is the default type, based on the traditional ISAM type, ISAM is the abbreviation for indexed sequential access method (indexed sequential access methods), which 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 something is rolled back it will cause an incomplete rollback and not be atomic. It is a better choice if you perform a lot of select,myisam.





InnoDB: This type is transaction-safe. It has the same characteristics as the BDB type, and they also support foreign keys. The InnoDB table is very fast. Features that are richer than bdb, so if you need a transaction-safe storage engine, It is recommended to use it. If your data performs a large number of inserts or updates, you should use InnoDB tables for performance reasons,





For INNODB types that support things, the main reason for the speed is that the AUTOCOMMIT default setting is open, and the program does not explicitly call begin transactions, causing each insert to automatically commit, seriously affecting the speed. You can call begin before executing SQL, and multiple SQL forms one 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. When you create a table, the default is MyISAM if you do not specify a type, and transactions are not supported.


can look at the type of the table with the Show CREATE TABLE tablename command.





2.1 does not have any effect on the start/commit of tables that do not support transactions, submitted before the execution of a commit, testing:


executes a msyql:


use test;


drop table if exists TN;


CREATE TABLE tn (a varchar) type=myisam;


drop table if exists Ty;


CREATE table Ty (a varchar) type=innodb;





begin;


insert into TN values (' a ');


insert into Ty values (' a ');


select * from TN;


select * from Ty;


can see a record.





execute another mysql:


use test;


select * from TN;


select * from Ty;


only TN can see a record


then on the other side


commit;


can only see the record.





3. You can switch non-transaction tables to transactions (data is not lost) by executing the following command, InnoDB table is more secure than MyISAM table:


ALTER TABLE TableName TYPE=INNODB;





3.1 InnoDB table cannot be used with Repair Table command and MYISAMCHK-R table_name


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





4. The following parameters are added to the command line that starts the MySQL database to make the newly published MySQL data table default to use transactions (


only affects the CREATE statement. ）


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