Features of mysql Data Table engine InnoDB and MyISAM, innodbmyisam

Features of mysql Data Table engine InnoDB and MyISAM, innodbmyisam
1. MyISAM Table Engine Features

(1) MyISAM is the default storage engine of MySQL.

(2) Most tools with table checking and repair.

(3) tables can be compressed.

(4) full-text search is supported.

(5) transactions are not secure. If a transaction is rolled back, incomplete rollback is not atomic.

(6) Foreign keys are not supported.

(7) If you execute a large number of SELECT statements, MyISAM is a better choice.

(8) Each MyISAM table is stored in three files: the frm file stores the table definition, the data file is MYD (MYData), and the index file is MYI (MYIndex) extension.

(9) tables are saved as files. Using MyISAM for cross-platform data transfer saves a lot of trouble.
Summary:

Read operations are more efficient than InnoDB. Using MyISAM for small applications is a good choice. Concurrency is weaker than InnoDB.

2. Features of the innodb table engine (1) provides transaction security tables with transaction commit, rollback, and crash recovery capabilities.

(2) provides row locks and non-lock reads consistent with Oracle.

(3) The table does not need to expand the lock because InnoDB column locking is suitable for a very small space.

(4) provide foreign key constraints.

(5) The design goal is to deal with large-capacity database systems. Its CPU utilization is incomparable to other disk-based relational database engines.

(6) establish a dedicated buffer pool in the primary memory for high-speed data buffering and indexing.

(7) store data and indexes in tablespaces and may contain multiple files. This is different from other types. For example, in MyISAM, tables are stored in separate files.

(8) The table size is limited by the file size of the operating system, generally 2 GB.
(9) All Tables are stored in the same data file ibdata1 (or multiple files using independent tablespace files, when using shared tablespaces, it is difficult to back up separate tables. The free solution can be copying data files, backing up binlogs, or using mysqldump.

Summary:

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

Note:

For InnoDB tables that support transactions, AUTOCOMMIT is enabled by default, and the program does not explicitly call BEGIN to start the transaction. As a result, every insert operation automatically Commit, the speed is seriously affected. Even if autocommit is enabled, you can call begin before executing the SQL statement. Multiple SQL statements form a transaction, or do not enable the AUTOCOMMIT configuration, which will greatly improve the performance.

3. Differences between MyISAM and InnoDB tables

(1) InnoDB does not support FULLTEXT indexes.
(2) InnoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same.
(3) For fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.
(4) When deleting FROM table, InnoDB does not create a new table but deletes a row.
(5) the load table from master operation does not work for InnoDB. The solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then import the data to the InnoDB TABLE, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys.
(6) The row lock of the InnoDB table is not absolute. If MySQL cannot determine the scope to be scanned when executing an SQL statement, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "% aaa %"

4. Transaction Test

(1) create a transaction table

Run the following command to start mysql:
Mysqld-max-nt -- standalone -- default-table-type = InnoDB
The command for creating a table is as follows:
Use test;
Drop table if exists tn;
Create table tn (a varchar (10 ));

View the table Type
Show create table tn;

(2) transaction table Switch

You can temporarily change the default table Type:
Set table_type = InnoDB;
Show variables like 'table _ type ';
 
Run the following command to switch the non-transaction table to the transaction table (data will not be lost ):

Alter table tablename type = innodb;

(3) Transaction usage

The default myisam Table type is used to create a table.

The start/commit operation on tables that do not support transactions has no effect. It has been submitted before the execution of the commit operation. test:

Execute an msyql:
Use test;
Drop table if exists tm;
Create table tn (a varchar (10) type = myisam;
Drop table if exists tn;
Create table ty (a varchar (10) type = innodb;
Begin;
Insert into tm values ('A ');
Insert into tn values ('A ');
Select * from tm;
Select * from tn;
A tm table record is displayed.

Execute another mysql:
Use test;
Select * from tm;
Select * from tn;
Only tm can see one record
Only the commit tn table in the original region;
To view the records of the tn table.