MySQL Database engine introduction and learning website

H Engine Introduction: ttp://www.jb51.net/article/38004.htm

Study site: http://www.w3school.com.cn/

MyISAM: MyISAM is the ISAM extended format of MySQL and the default database engine. In addition to providing a number of functions for index and field management not available in ISAM, MyISAM also uses a table-locking mechanism to optimize multiple concurrent read and write operations, at the expense of the need to run the Optimize Table command frequently to restore space wasted by the updated mechanism. MyISAM also has some useful extensions, such as the Myisamchk tool for repairing database files and the Myisampack tool for recovering wasted space. MyISAM emphasizes fast read operations, which may be the main reason why MySQL is so popular with Web development: in Web development, the bulk of your data operations are read operations. Therefore, most web hosting providers and internet platform providers only allow the use of the MyISAM format. an important flaw in the MyISAM format is the inability to recover data after a table is corrupted.

InnoDB: The InnoDB database engine is a direct product of the technology that makes MySQL flexible, and this technology is MYSQL+API. Every challenge you face when using MySQL comes from the ISAM and MyISAM database engine does not support transactional processing (transaction process) or foreign keys. Although much slower than ISAM and MyISAM engines, InnoDB includes support for transactional and foreign keys , which are not available in the top two engines for two points. As mentioned earlier, if your design requires accesses than either or both of these features, you will be forced to use one of the latter two engines.

The difference between MyISAM and InnoDB:

MyISAM types do not support advanced processing such as transaction processing, and InnoDB type support. The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides transactional support for advanced database functions such as external keys.

The following are some of the details and the specific implementation differences:
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 (*) fromtable, 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.
5.LOAD Table Frommaster operation does not work for InnoDB, the workaround is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table, but not for 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 scope to scan when executing an SQL statement, the InnoDB table also locks the full table, for example updatetable set num=1 where name like "a%"
The main difference between the two types is that InnoDB supports transactional and foreign key and row-level locks. MyISAM is not supported. So MyISAM tend to be considered only suitable for use in small projects.
I use MySQL as a user point of view, InnoDB and MyISAM are more like, but from my current operation of the database platform to meet the requirements: 99.9% stability, convenient scalability and high availability, MyISAM is definitely my first choice.

MyISAM indexes and data are separate, and the indexes are compressed, and memory usage increases a lot. Can load more indexes, and InnoDB is the index and the data is tightly bound, do not use compression which will cause innodb than MyISAM volume is large.

Generally speaking, MyISAM is suitable for:
(1) do a lot of count calculation;
(2) The insertion is not frequent, the query is very frequent;
(3) No business.

InnoDB Suitable for:
(1) Reliability requirements are relatively high, or require business;
(2) The table updates and queries are quite frequent,

MySQL Database engine introduction and learning website