Key points of Mysql lock technology

The difference between MyISAM and InnoDB MySQL is MyISAM by default. 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. 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. InnoDB supports foreign keys, MyISAM not supported. The InnoDB has a greater range of primary keys, up to twice times the maximum of MyISAM.

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. MyISAM supports GIS data, INNODB not supported. That is, MyISAM supports the following spatial data objects: Point,line,polygon,surface and so on. 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. 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.

InnoDB implements the following two types of row locks. Shared Lock (S): Allows a transaction to read one line, preventing other transactions from acquiring an exclusive lock on the same data set. Exclusive Lock (X): A transaction that allows an exclusive lock to update data, preventing other transactions from acquiring shared read and exclusive write locks of the same data set.

Key points of Mysql lock technology