Shulou(Shulou.com)06/01 Report--

1.MyISAM does not support transactions, while InnoDB does. InnoDB's AUTOCOMMIT is turned on by default, that is, each SQL statement is encapsulated into a transaction by default and automatically committed, which will affect the speed, so it is best to display multiple SQL statements between begin and commit.

Form a transaction to commit.

2.InnoDB supports row locking, while MyISAM does not support row locking, only locking the entire table. That is, the read lock and write lock on the same table of MyISAM are mutually exclusive. If there are both read and write requests in the queue for MyISAM concurrent read and write, the default write request has a high priority.

Even if the read request arrives first, MyISAM is not suitable for situations where there are a large number of queries and modifications, so that the query process will block for a long time. Because MyISAM is a locked table, one read operation is time-consuming and makes other write processes starve to death.

3.InnoDB supports foreign keys, but MyISAM does not.

4.InnoDB has a larger range of primary keys, up to twice the size of MyISAM.

5.InnoDB does not support full-text indexing, while MyISAM does. Full-text indexing refers to inverted indexing of every word (except deprecated words) in char, varchar, and text. MyISAM's full-text index is actually useless because it does not support Chinese word segmentation and must be segmented by the user.

Then add a space and write it to the data table, and words with less than 4 Chinese characters will be ignored as well as discontinued words.

6.MyISAM supports GIS data, but InnoDB does not. That is, MyISAM supports the following spatial data objects: Point,Line,Polygon,Surface, etc.

7. Count (*) without where uses MyISAM much faster than InnoDB. Because MyISAM has a built-in counter, it reads directly from the counter when count (*), while InnoDB must scan the entire table. So the execution of count (*) on InnoDB is usually accompanied by where, and

The where should include index columns other than the primary key. Why is there a special emphasis on "outside the primary key"? Because in InnoDB, primary index is stored with raw data, while secondary index is stored separately, and then there is a pointer to primary key.

So it's faster to scan with secondary index if only count (*), while primary key is more useful when scanning the index and returning raw data.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.