Features of MySQL data sheet engine InnoDB and MyISAM

1. MyISAM Watch engine Features

(1) MyISAM is the MySQL default storage engine

(2) Most tools with inspection and repair forms.

(3) The table can be compressed

(4) Support full-text search.

(5) It is not a matter of security. If the rollback of a thing causes incomplete rollback, it does not have atomicity.

(6) Foreign keys are not supported.

(7) If the execution of a large number of select,myisam is a better choice.

(8) Each MyISAM table is stored in three files: the frm file is stored as a table definition, the data file is MyD (MYData), and the index file is myi (myindex) extension.

(9) tables are saved as files, and using MyISAM storage in cross-platform data transfer saves a lot of hassle
Summarize:

Read operations are more efficient than INNODB. Small applications using MyISAM is a good choice. Concurrency is weaker than InnoDB.

2. InnoDB table engine Features (1) provides transaction-safe tables with transactional commit, rollback, and crash-repair capabilities.

(2) A row lock is provided, providing a non-lock read that is consistent with the Oracle type.

(3) There is no need to widen the lock in the table because the InnoDB column lock is suitable for very small space.

(4) A FOREIGN key constraint is provided.

(5) The design goal is to deal with the large-capacity database system, and its CPU utilization is not comparable to other disk-based relational database engines.

(6) Establish its dedicated buffer pool in main memory for caching data and indexes.

(7) storing data and indexes in a tablespace, which may contain multiple files, is not the same as it is, for example, in MyISAM, a table is stored in a separate file.

(8) The size of the table is limited only by the file size of the operating system, typically 2 GB.
(9) All the tables are stored in the same data file Ibdata1 (it may also be multiple files using a separate tablespace file, it is not good to back up a separate table when using shared tablespace), the free scheme can be copying data files, backing up Binlog, or using mysqldump.

Summarize:

These features improve the performance of multi-user concurrency operations.

Attention:

For INNODB types of tables 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 to start a transaction, resulting in an automatic commit for each insert, which seriously affects the speed. Even if autocommit is open, you can call begin before you execute SQL, multiple SQL forms a transaction, or do not open the autocommit configuration, which will greatly improve performance.

3. MyISAM table and InnoDB table difference

(1) InnoDB does not support indexes of fulltext types.
(2) The exact number of rows in the table is not saved in InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the saved rows. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.
(3) For fields of type auto_increment, the InnoDB must contain only the index of the field, but in the MyISAM table, you can establish a federated index with other fields.
(4) When you delete from table, InnoDB does not reestablish the table, but deletes one row at a time.
(5) The LOAD table from master operation has no effect on InnoDB, the workaround is to first change the InnoDB table to MyISAM table, import the data and then change it to the InnoDB table, but not for tables that use additional InnoDB features such as foreign keys.
(6) The row lock of the InnoDB table is also not absolute, if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the full table, such as the Update table set num=1 where name like "%aaa%"

4. Transaction Testing

(1) Transaction table creation

Use the start MySQL command as follows, affecting only the CREATE statement
Mysqld-max-nt--standalone--default-table-type=innodb
The CREATE TABLE command is as follows:
Use test;
drop table if exists TN;
Create TABLE TN (a varchar (10));

To view the type of a table
Show CREATE TABLE TN;

(2) Transaction table switching

Temporarily changing the default table type can be used:
Set Table_type=innodb;
Show variables like ' table_type ';

You can execute the following command to toggle a non-transactional table to a transaction table (data is not lost):

ALTER TABLE TableName TYPE=INNODB;

(3) Transaction use

Creating a table By default is the MyISAM table type.

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 TM;
Create TABLE TN (a varchar (ten)) Type=myisam;
drop table if exists TN;
Create table Ty (a varchar (ten)) Type=innodb;
Begin
INSERT INTO TM values (' a ');
Insert into TN values (' a ');
SELECT * FROM TM;
SELECT * from TN;
Can see a record of a TM table.

To perform another MySQL:
Use test;
SELECT * FROM TM;
SELECT * from TN;
Only a TM can see a record.
Only in the original There commit TN table;
can only see the records of the TN table.

Features of MySQL data sheet engine InnoDB and MyISAM