What is the difference between mySQL's MyISAM and InnoDB? How to optimize MYSQL database? , myisaminnodb

What is the difference between mySQL's MyISAM and InnoDB? How to optimize MYSQL database? , myisaminnodb_PHP tutorial

WBOY

Release： 2016-07-13 09:46:26

Original

1112 people have browsed it

What is the difference between MyISAM and InnoDB of mysql? How to optimize MYSQL database? , myisaminnodb

The basic difference between MyISAM and InnoDB

1.InnoDB does not support FULLTEXT type indexes.

2. InnoDB does not save the specific number of rows in the table. That is to say, when executing select count(*) from table, InnoDB has to scan the entire table to calculate how many rows there are, but MyISAM only needs to simply read Just print out the number of saved rows. Note that when the count(*) statement contains a where condition, the operations of the two tables are the same.

3. For fields of type AUTO_INCREMENT, InnoDB must contain an index with only this field, but in the MyISAM table, a joint index can be established with other fields.

4. When DELETE FROM table, InnoDB will not re-create the table, but will delete it row by row. MyISAM is a table, innodb is a row lock

5. LOAD TABLE FROM MASTER (from the main load table) operation does not work for InnoDB. The solution is to first change the InnoDB table to a MyISAM table, import the data and then change it to an InnoDB table, but for This does not apply to tables that use additional InnoDB features (such as foreign keys).

In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the range to be scanned when executing a SQL statement, the InnoDB table will also lock the entire table, for example, update table set num=1 where name like “"2%”

6.InnoDB supports things

Select storage engine according to actual situation.

Generally, it is recommended to use myIsam if there are many queries.

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

Methods to optimize MYSQL database:

1. Select the most applicable field attribute, reduce the length of the defined field as much as possible, and try to set the field to NOT NULL, such as 'province, gender', it is best to set it to ENUM

2, use join instead of subquery

3. Use UNION to replace manually created temporary tables

4. Transaction processing (to ensure data integrity, such as adding and modifying at the same time, if both are true, both will be executed, and if one fails, it will fail)

5. Build an index appropriately (how to build an index? What are the pros and cons of indexing?)

6, optimize sql statement

7, explain can see the mysql execution plan

8, sub-table (vertical sub-table, horizontal sub-table?)