The difference between engine=innodb and engine=myisam in MySQL
first to build a database with MySQL Administrator, the table default is a InnoDB type, and it doesn't care. Later, when using Access2MySQL to guide data, it was found that the tables that could only be MyISAM types did not know the difference between the two types. It turns out that the MyISAM type does not support advanced processing such as transaction processing, while InnoDB type support. The MyISAM type table emphasizes performance, whose number of executes is faster than the InnoDB type, but does not provide transaction support, and InnoDB provides transaction support for advanced database functions such as external keys. This allows different storage types to be used according to the data table.
, in addition, MyISAM type binary data files can be migrated in different operating systems. That is, it can be copied directly from the Windows system to the Linux system.
modification:
[code]ALTER TABLE tablename ENGINE = MyISAM; [/code]
MyISAM: this is the default type. It is the abbreviation based on the traditional type, which is the standard method for storing records and files. Storage engine comparison, MyISAM has most of the tools to check and repair tables. MyISAM forms can be compressed and they support full text search. They are not transaction safe, and they do not support foreign keys. If things roll back, they will cause incomplete rollback and no atomicity. If you execute a lot of SELECT, MyISAM is a better choice. The type of
InnoDB: is transaction safe. It has the same characteristics as the BDB type, and they also support the foreign key.InnoDB table fast. It is more abundant than BDB, so if you need a secure storage engine for a transaction, it is suggested to use it. If your numbers execute a large number of INSERT or UPDATE, out of performance square The InnoDB table,
for the InnoDB type that supports things, the main reason for the impact of the speed is that the AUTOCOMMIT default setting is open, and the program does not explicitly call the BEGIN start transaction, causing each insert to automatically Commit, which seriously affects the speed. You can call begin before executing SQL, and multiple SQL forms a thing (even if autocommit is open), which will greatly improve the performance.
===============================================================
1. 4 or more mysqld supports transactions, including non Max versions. 3.23 needs Max version mysqld to support transactions. When
2. creates tables, if type is not specified, it is MyISAM by default and does not support transactions.
can use the show create table tablename command to see the type of the table.
2.1 does not have any effect on the start/commit operation that does not support the transaction. It has been submitted before the execution of commit, and the test:
execute a msyql:
[code]use test;
drop table. Ty (a varchar (10)) type=innodb;
begin;
insert into TN values. Y, [/code]
only TN can see a record
and then on the other side
commit;
can see records.
3. can perform the following commands to switch the non transaction table to the transaction (the data will not be lost), the InnoDB table is more secure than the MyISAM table:
[code] alter table tablename type=innodb; The following parameters added to the command line of the mysqlcheck [OPTIONS] database [tables]
4. to start the MySQL database can make the newly released MySQL data tables default to use transactions (
only to the create statement).
[code]--default-table-type=InnoDB[/code]
test command:
[code]use test;
drop table if exists TN; Ables like'table_type'; [/code]
or:
[code]c:/mysql/bin/mysqld-max-nt --standalone --default-table-type=InnoDB[/code]