Differences between InnoDB and MyISAM tables in Mysql

As we all know, InnoDB and MyISAM are the two most commonly used table types in mysql, and MyISAM is the default type. There are many differences between them, mainly focusing on different ones. InnoDB supports some advanced processing, while MyISAM emphasizes performance, and of course it is necessary to sacrifice some things while emphasizing performance.

Now we can compare the statement (select count (*) | (1) | (Primary Key) for counting the number of rows, and create two tables in Mysql: MyISAM and InnoDB:

Code create table 'table1 '(
'Id' int (11) not null,
'Name' char (50) character set ucs2 default NULL,
Primary key ('id ')
) ENGINE = MyISAM default charset = latin1

Create table 'table2 '(
'Id' int (11) not null,
'Name' char (50) character set ucs2 default NULL,
Primary key ('id ')
) ENGINE = InnoDB default charset = latin1

Then insert some records randomly into the two tables, for example, insert two records.

Run the following statement:

Select count (*) from Table1
Select count (*) from Table2

Note: here the count (*) and count (id) and count (1) are no different, because we have already created a primary key when creating this table, like most database engines, mysql performs some optimizations, which are calculated based on primary key indexes.

The above statement results are of course 2. The following describes the execution process of these two statements:

Explain select count (*) from Table1
Explain select count (*) from Table2

The execution plans are as follows:

First sentence:

+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + -------------------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + -------------------------------- +
| 1 | SIMPLE | NULL | Select tables optimized away |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + -------------------------------- +

Second sentence:

+ ---- + ------------- + -------- + ------- + --------------- + --------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + -------- + ------- + --------------- + --------- + ------ + ------------- +
| 1 | SIMPLE | Table2 | index | NULL | PRIMARY | 4 | NULL | 3 | Using index |
+ ---- + ------------- + -------- + ------- + --------------- + --------- + ------ + ------------- +

As a result, we can clearly see that Table1 is a MyISAM table. When counting the number of rows, it is already the best statement (Select tables optimized away, indicating that no optimization can be performed, in some documents, tables of the MyISAM type store Rows and can be directly used for query .). Table2 is an InnoDB table. When counting the number of rows, it uses clustered indexes for statistics.