What is the difference between MySQL MyISAM and InnoDB? How to optimize MySQL database?

The basic difference between MyISAM and InnoDB

1.InnoDB does not support indexes of type Fulltext.

The exact number of rows in the table is not saved in 2.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 a field of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, you can establish a federated index with other fields.

4.DELETE from table, InnoDB does not reestablish the table, but deletes one row at a time. MyISAM is the table InnoDB is the row lock

5.LOAD table from Master operation on InnoDB is not working, the solution is to first change the InnoDB table to MyISAM table, import data and then change to InnoDB table, However, tables that use additional InnoDB attributes, such as foreign keys, do not apply.

In addition, the row lock of the InnoDB table is not absolute, and if MySQL cannot determine the scope 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 "2%"

6.InnoDB Support Things

Select the storage engine to select according to the actual situation.

In general, if the query is more recommended to use MyISAM.

If you need transaction processing or foreign keys, then InnoDB may be a good way.

Ways to optimize MySQL database:

1, choose the most applicable field properties, minimize the definition of field length, as far as possible to set the field not NULL, such as ' Province, gender ', preferably set to enum

2, use join instead of subquery

3, use Union (union) instead of manually created temporary table

4, transaction processing (ensure data integrity, such as add and modify, both are executed, accesses than either failed)

5, proper indexing (how to index?) What are the pros and cons of indexing? ）

6, Optimizing SQL statements

7,explain can see the MySQL execution plan

8, sub-table (Vertical sub-table, horizontal sub-table?) ）

Related reading

Http://www.phpernote.com/mysql/500.html

http://www.nowamagic.net/librarys/veda/detail/1899

The above describes the difference between MySQL MyISAM and InnoDB? How to optimize MySQL database? , including the aspects of the content, want to be interested in PHP tutorial friends helpful.

