MyISAM and InnoDB differences in MySQL "detailed"

This article mainly introduces the difference between MyISAM and InnoDB in MySQL, hoping to help the needy friends!

The difference between MyISAM and InnoDB:

1.MySQL by default is MyISAM.

2.MyISAM does not support transactions, while InnoDB support. 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.

The 3.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.

4.InnoDB supports foreign keys, MyISAM not supported.

The 5.InnoDB has a larger primary key range and is twice times the maximum of MyISAM.

6.InnoDB does not support full-text indexing, and MyISAM support. 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.

7.MyISAM supports GIS data, INNODB not supported. That is, MyISAM supports the following spatial data objects: Point,line,polygon,surface and so on.

8. 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.

For more MySQL knowledge, you can refer to learning MySQL video tutorial or mysql manual!