The difference between MyISAM and InnoDB

2. MySQL is MyISAM by default.
4. MyISAM does not support transactions, while InnoDB supports. InnoDB autocommit By default is open, that is, each SQL statement will be encapsulated into a transaction by default, automatically commit, which will affect the speed, so it is best to put a number of SQL statements between Begin and commit, compose a transaction to commit.
6. InnoDB supports data row locking, MyISAM does not support row locking and only supports locking the entire table. That is, MyISAM read and write locks on the same table are mutually exclusive, MyISAM concurrent read and write if the queue is waiting for both read requests and write requests, the default write requests high priority, even if the read request first arrived, so MyISAM is not suitable for a large number of queries and modifications coexist, so the query process will be blocked for a long time. Because MyISAM is a lock table, a read operation can be time-consuming to starve other write processes.
8. InnoDB supports foreign keys, MyISAM not supported.
10. The InnoDB has a greater range of primary keys, up to twice times the maximum of MyISAM.
12. InnoDB does not support full-text indexing, and MyISAM supports it. Full-text indexing refers to the backward-sort index of each word in char, varchar, and text (except for the inactive word). MyISAM's full-text index is actually useless, because it does not support Chinese word segmentation, must be used by the user to add a space after the word and then write to the data table, and less than 4 Chinese characters will be ignored as the word stop.
14. MyISAM supports GIS data, INNODB not supported. That is, MyISAM supports the following spatial data objects: Point,line,polygon,surface and so on.
16. No where COUNT (*) uses MyISAM much faster than InnoDB. Because MyISAM has a built-in counter, COUNT (*) reads directly from the counter, and InnoDB must scan the entire table. Therefore, when you execute count (*) on InnoDB, you typically accompany where, and where you want to include an index column other than the primary key. Why is this special emphasis on "outside the primary key"? Because primary index in INNODB is stored with raw data, secondary index is stored separately and a pointer to primary key. So just count (*) uses secondary index scans faster, while primary key is primarily useful for scanning indexes while returning raw data.

17. MyISAM	InnoDB
    the difference in composition:	Each myisam is stored as three files on disk. The first file name begins with the name of the table, and the extension indicates the file type. The. frm file stores the table definition. The data file has an extension of. MYD (MYData). The extension of the index file is. MYI (Myindex).	A disk-based resource is a InnoDB tablespace data file and its log file, and the size of the InnoDB table is limited only by the size of the operating system file, typically 2GB
    transaction Processing Aspects :	Tables of the MyISAM type emphasize performance, which is performed more quickly than the InnoDB type, but does not provide transactional support	InnoDB provides transaction support transactions, external keys and other advanced database functions
    SELECT Update,insert , Delete Operation	If executing a lot of select,myisam is a better choice	1. If your data performs a large number of inserts or UPDATE, for performance reasons, you should use the InnoDB table 2.DELETE from table , InnoDB does not reestablish the table, but deletes one row at a time. 3.LOAD table from MASTER operation does not work for InnoDB, 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
    to auto_increment	the internal processing of one auto_incremen column per table.    MyISAM for INSERT and The update operation automatically updates this column . This makes the Auto_increment column faster (at least 10%). The value at the top of the sequence cannot be exploited after it has been deleted. (When the Auto_increment column is defined as the last column of a multicolumn index, the use of values removed from the top of the sequence can occur). The   auto_increment value can be used by ALTER TABLE or MYISAMCH to reset    for fields of type auto_increment, The InnoDB must contain only the index of the field, but in the MyISAM table, you can establish a federated index with other fields    better and faster auto_increment processing	If you specify a auto_increment column for a table, the InnoDB table handle in the data dictionary contains a counter called the autogrow counter, which is used to assign a new value to the column. The    autogrow counters are stored only in main memory, not on disk    algorithmic implementations of the calculator, see    Auto_increment column in InnoDB How to work
    the exact number of rows in the table	Select COUNT (*) from Table,myisam as long as you simply read the number of rows saved, note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same	The exact number of rows in a table is not saved in InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows
    Lock	Table lock	Provides row lock (locking on row level), providing no lock read consistent with Oracle type (non-locking read in Selects), in addition, the row lock of the InnoDB table is not absolute, and if MySQL cannot determine the range 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 "% aaa% "

The difference between MyISAM and InnoDB