The difference between engine=innodb and engine=myisam in mysql
- 2020-05-30 21:12:29
- OfStack
When you first started building your database with MySQL Administrator, the default table was of type InnoDB, so you didn't care. Later, when using the derivative data of Access2MySQL, I found that it could only be derived into the table of MyISAM type. I didn't know the difference between the two types, so I checked. Previously, the MyISAM type did not support advanced processing such as transaction processing, while the InnoDB type did. Tables of type MyISAM emphasize performance, and the number of executions is faster than those of type InnoDB, but do not provide transaction support, whereas InnoDB provides transaction support and advanced database functions such as external keys. This can be used for different storage types depending on the data table.
In addition, MyISAM type binary data files can be migrated between different operating systems. That is, you can copy it directly from the Windows system to the linux system.
Modification:
ALTER TABLE tablename ENGINE = MyISAM;
MyISAM: this is the default type, it is based on the traditional ISAM type, ISAM is Indexed Sequential Access Method (indexed sequential access method), it is a normal method of storing records and files. Compared with other storage engines, MyISAM have a check and repair form most of the tools. MyISAM form can be compressed, and they support full-text search. They are not the transaction security, but also does not support foreign keys. If something rolls back it will cause an incomplete rollback, no atomicity. If you do a lot of SELECT, MyISAM is a better choice.
InnoDB: this type is the transaction security. It has the same properties and BDB type, they also support foreign keys. InnoDB form quickly. With rich than BDB properties, so if you need a transaction safe storage engine, it is recommended to use it. If you do a lot of data of INSERT or UPDATE, for performance reasons, should use InnoDB table,
For the standard of InnoDB type that supports things, the main reason that affects the speed is that the default setting of AUTOCOMMIT is turned on, and the program does not explicitly call BEGIN to start the transaction, resulting in automatic Commit for every insert, which seriously affects the speed. begin can be called before sql is executed, and multiple sql form one thing (even if autocommit is opened), which will greatly improve performance.
===============================================================
Transactions are supported on mysqld above 4.0, including non-max versions. 3.23 requires max version mysqld to support transactions.
2. If you do not specify type when creating a table, myisam will be the default. Transactions are not supported.
You can see the type of table with the show create table tablename command.
2.1 the start/commit operation does not have any effect on tables that do not support transactions. It has been submitted before commit is executed. Test:
Execute 1 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 one record
Execute another mysql:
use test;
select * from tn;
select * from ty;
Only tn can see 1 record
And then on the other side
commit;
You can see the records.
3. You can switch the non-transaction table to the transaction by executing the following command (data will not be lost). innodb table is more secure than myisam table:
alter table tablename type=innodb;
3.1 the innodb table cannot use the 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 to start mysql database to make the newly published mysql data tables use transactions by default (
Only create statements are 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 done by:
set table_type=InnoDB;
show variables like 'table_type';
Or:
c:/mysql/bin/mysqld-max-nt --standalone --default-table-type=InnoDB
In addition, MyISAM type binary data files can be migrated between different operating systems. That is, you can copy it directly from the Windows system to the linux system.
Modification:
ALTER TABLE tablename ENGINE = MyISAM;
MyISAM: this is the default type, it is based on the traditional ISAM type, ISAM is Indexed Sequential Access Method (indexed sequential access method), it is a normal method of storing records and files. Compared with other storage engines, MyISAM have a check and repair form most of the tools. MyISAM form can be compressed, and they support full-text search. They are not the transaction security, but also does not support foreign keys. If something rolls back it will cause an incomplete rollback, no atomicity. If you do a lot of SELECT, MyISAM is a better choice.
InnoDB: this type is the transaction security. It has the same properties and BDB type, they also support foreign keys. InnoDB form quickly. With rich than BDB properties, so if you need a transaction safe storage engine, it is recommended to use it. If you do a lot of data of INSERT or UPDATE, for performance reasons, should use InnoDB table,
For the standard of InnoDB type that supports things, the main reason that affects the speed is that the default setting of AUTOCOMMIT is turned on, and the program does not explicitly call BEGIN to start the transaction, resulting in automatic Commit for every insert, which seriously affects the speed. begin can be called before sql is executed, and multiple sql form one thing (even if autocommit is opened), which will greatly improve performance.
===============================================================
Transactions are supported on mysqld above 4.0, including non-max versions. 3.23 requires max version mysqld to support transactions.
2. If you do not specify type when creating a table, myisam will be the default. Transactions are not supported.
You can see the type of table with the show create table tablename command.
2.1 the start/commit operation does not have any effect on tables that do not support transactions. It has been submitted before commit is executed. Test:
Execute 1 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 one record
Execute another mysql:
use test;
select * from tn;
select * from ty;
Only tn can see 1 record
And then on the other side
commit;
You can see the records.
3. You can switch the non-transaction table to the transaction by executing the following command (data will not be lost). innodb table is more secure than myisam table:
alter table tablename type=innodb;
3.1 the innodb table cannot use the 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 to start mysql database to make the newly published mysql data tables use transactions by default (
Only create statements are 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 done by:
set table_type=InnoDB;
show variables like 'table_type';
Or:
c:/mysql/bin/mysqld-max-nt --standalone --default-table-type=InnoDB