) The insert efficiency of MySQL isam is higher than that of InnoDB.

We can see the concurrent_insert parameter today and explain it as follows:

The MyISAM storage engine has a system variable concurrent_insert, which is used to control its Concurrent Insertion behavior. The values can be 0, 1, or 2, respectively.

Concurrent inserts are not allowed when concurrent_insert is set to 0.

When concurrent_insert is set to 1, if the MyISAM table does not have holes (that is, the rows in the middle of the table are not deleted), MyISAM allows a process to read the table at the same time, another process inserts records from the end of the table. This is also the default setting of MySQL. If there is a hole, although it cannot be good at concurrency, MySQL can still use insert delayed to improve the insert performance (only applicable to MyISAM, memory, and archive engines ).

When concurrent_insert is set to 2, records can be inserted concurrently at the end of the table regardless of whether there are holes in the MyISAM table, mySQL allows the insert and select statements to run concurrently in the MyISAM table without empty data blocks.

Note: The holes-empty concept is exclusive to the MyISAM engine. The data files are stored in sequence during MyISAM storage. When a delete operation is performed, the data file is empty.

When new data is inserted, first check whether the empty space can insert this data (when concurrent_insert is 2, you do not need to judge). Otherwise, the data is inserted to the end. This setting is only valid for the MyISAM engine, the InnoDB Storage method has its own unique method and does not produce data holes.

Can InnoDB achieve insert and select concurrency? Here is a case to explain that when the InnoDB table has an index, it can certainly be concurrent (Row-Level Lock, as long as it is not tired, because the implementation of InnoDB row locks is implemented by locking the index item, unlike Oracle's locking the row in the data block, otherwise, unrelated rows will be locked .), But no?

Create a test table currenttable first (note that there is no index ):

Insert 999 data records.

Start transaction 1 explicitly: Execute the insert operation

It is acceptable to select the table on another terminal, because it is only the S lock.

But select... For update, hanging will appear.

Hanging

The above example can also be quite clear (the implementation of InnoDB row locks is implemented on the index item.

For the same table, after I create an ID-based primary key clustered index, in the same scenario, select... For update can be executed smoothly.

When this table uses the MyISAM engine, not only can select be executed, select... For update is also acceptable (separate concurrent execution of newly inserted rows and changing existing rows at the data file level ).

From the above few words, I should be able to see if MyISAM is better than InnoDB in terms of insertion performance, although the insert and select concurrency and locking mechanisms are discussed here.

Indeed, MyISAM's insertion performance is better than InnoDB. For details, refer to the realzyy blog.

(Http://www.realzyy.com /? P = 20)