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

In this issue, Xiaobian will bring you the difference between innodb and myisam in mysql. The article is rich in content and analyzed and described from a professional perspective. After reading this article, I hope you can gain something.

MyISAM does not support transactions, while InnoDB does. InnoDB supports data row locking, MyISAM does not support row locking, only supports locking the entire table. InnoDB supports foreign keys, MyISAM does not. InnoDB has a larger primary key range, up to twice that of MyISAM.

Difference between MyISAM and InnoDB

MySQL defaults to MyISAM.

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

InnoDB supports data row locking, MyISAM does not support row locking, only supports locking the entire table. That is, MyISAM read locks and write locks on the same table are mutually exclusive. When MyISAM reads and writes concurrently, if there are both read requests and write requests in the waiting queue, the priority of the default write request is high, even if the read request arrives first, so MyISAM is not suitable for a large number of queries and modifications. The query process will block for a long time. Because MyISAM is a lock table, a read operation that takes longer can starve other write processes.

InnoDB supports foreign keys, MyISAM does not.

InnoDB has a larger primary key range, up to twice as large as MyISAM.

InnoDB does not support full-text indexing, while MyISAM does. Full-text indexing is an inverse-sorted index of every word in char, varchar, and text except stop words. MyISAM's full-text index is useless because it doesn't support Chinese word segmentation. It must be written to the data table by the user after word segmentation, and words with less than 4 Chinese characters will be ignored like stop words.

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

Count(*) without where MyISAM is much faster than InnoDB. Because MyISAM has a built-in counter, count(*) reads directly from the counter, while InnoDB must scan the entire table. Therefore, when executing count(*) on InnoDB, it is generally accompanied by where, and where should contain index columns other than the primary key. Why is there a special emphasis on "outside the primary key"? Because primary index is stored with raw data in InnoDB, secondary index is stored separately, and then there is a pointer to primary key. Therefore, if only count(*) is used, it is faster to scan with secondary index, while primary key is mainly used when scanning index and returning raw data.

The above is what is the difference between innodb and myisam in mysql shared by Xiaobian. If there is a similar doubt, please refer to the above analysis for understanding. If you want to know more about it, please pay attention to 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.