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

What is the difference between MyISAM and InnoDB in MySQL? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

The difference between MyISAM and InnoDB:

MyISAM is used by default in 1.MySQL.

2.MyISAM does not support transactions, while InnoDB does. The AUTOCOMMIT of InnoDB is turned on by default, that is, each SQL statement will be encapsulated as a transaction by default and commit automatically, which will affect the speed, so it is best to put multiple SQL statements between begin and commit to form a transaction to commit.

3.InnoDB supports row locking, while MyISAM does not support row locking, only locking the entire table. That is to say, 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, so MyISAM is not suitable for the situation where a large number of queries and modifications coexist, so the query process will be blocked for a long time. Because MyISAM is a locked table, one read operation is time-consuming and makes other write processes starve to death.

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

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

6.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, it must be segmented by the user and then added a space to the data table, and words with less than 4 Chinese characters will be ignored as well as discontinued words.

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

8. Count (*) without where uses MyISAM much faster than InnoDB. Because there is a counter built in for MyISAM, it reads directly from the counter when count (*), while InnoDB must scan the entire table. Therefore, the execution of count (*) on InnoDB is generally accompanied by where, and the where contains 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.

Thank you for reading! After reading the above, do you have a general idea of the difference between MyISAM and InnoDB in MySQL? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

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.