Differences between InnoDB and MyISAM

MySQL, as the most popular free database service engine, has been popular for a long time. However, some people may not know much about the internal environment of MySQL, especially those mechanisms for concurrent processing. Today, let's take a look at the classification of data tables in MySQL and their simple properties.

Up to now, MySQL has provided users with seven tables, including DBD, heap, isam, merge, myias, InnoDB, and gemeni. Among them, DBD and InnoDB are transaction security tables, while others are transaction non-security tables.

 
DBD
The Berkeley dB (DBD) Table supports transaction processing and is developed by sleepycat. It provides the long-awaited MySQL feature-transaction control. Transaction control is a very valuable feature in any database system, because they ensure that a group of commands can be successfully executed or rolled back.

Heap
The heap table is the fastest table to access data in MySQL. This is because they use a hash index stored in the dynamic memory, but if MySQL or the server crashes, the memory data will be lost.

Isam
Isam tables are the default table types of earlier MySQL versions until myiasm is developed. We recommend that you do not use it any more.

Merge
Merge is an interesting new type that appears after 3.23.25. A merge table is actually a set of MyISAM tables that are merged into a table mainly for efficiency consideration, this not only improves the speed, search efficiency, and repair efficiency, but also saves disk space.

Myiasm
Myiasm is based on the iasm code and should be considered a derivative of iasm, but it adds a lot of useful extensions. It is the default data table Type of MySQL. Based on the traditional isam type, isam is the abbreviation of indexed sequential access method (sequential access method with indexes). Generally, it is a standard method for storing records and files. Compared with other storage engines, MyISAM provides most tools for checking and repairing tables. Isam tables can be compressed and support full-text search. However, they are insecure and do not support foreign keys. If the transaction is rolled back, incomplete rollback will be caused and it is not atomic. Therefore, if you ignore transactions and access concurrency and need to execute a large number of select search statements, MyISAM will be the best choice.

Why is myiasm better:
Myiasm tables are smaller than iasm tables, so fewer resources are used.
Myiasm tables can be transplanted at the binary layer on different platforms.
Myiasm has a larger key size and a larger key size limit.
For the MyISAM storage engine, its read/write locks and read/write locks are mutually exclusive, so that read/write operations are sequential. So, one process requests the read lock of a MyISAM table, and the other process also requests the write lock of the same table. How does MySQL handle this? The answer is that the write process obtains the lock first. Not only that, even if the Read Request first goes to the lock wait queue, after the write request arrives, the write lock will be inserted before the read lock request! This is because MySQL considers that write requests are generally more important than read requests. This is precisely the reason why MyISAM tables are not suitable for applications with a large number of update operations and query operations, because a large number of update operations will make it difficult for query operations to obtain read locks, which may be blocked forever. This situation may sometimes become very bad! Fortunately, we can adjust MyISAM through some settings.
. By specifying the startup parameter low-priority-updates, the MyISAM engine gives the Read Request priority by default. Run the set low_priority_updates = 1 command to lower the priority of the update request sent by the connection. You can specify the low_priority attribute of an insert, update, or delete statement to reduce the priority of the statement. Although the above three methods are both update-first or query-first methods, they can still be used to query applications that are relatively important (such as user logon to the system, read lock wait is a serious problem. In addition, MySQL also provides a compromise to adjust read/write conflicts, that is, to set an appropriate value for the system parameter max_write_lock_count. When the read lock of a table reaches this value, mySQL temporarily lowers the priority of write requests, giving the read process a certain chance to get the lock.

The Problems and Solutions brought about by the write priority scheduling mechanism have been discussed above. It should also be emphasized that some query operations that require a long time to run will also starve the write process "! Therefore, the application should try to avoid long-running query operations. Do not always want to use a select clause to solve the problem, because this seemingly clever SQL statement is often complicated, the execution takes a long time. If possible, you can use an intermediate table or other measures to "break down" the SQL statement, so that each step of the query can be completed in a short time, this reduces lock conflicts. If complex queries are unavoidable, they should be executed during idle time periods. For example, some regular statistics can be executed at night.

InnoDB
InnoDB is a relatively new data table Type launched after MySQL 4.0, Which is transaction security. It has the same features as the bdb type and supports foreign keys. InnoDB tables are faster than bdb tables. Therefore, if you need a transaction-safe storage engine, we recommend that you use it. If you execute a large number of insert or update operations on your data, InnoDB tables should also be used for performance considerations. For InnoDB tables that support transactions, the main cause of the impact on the speed is that autocommit is enabled by default, and the program does not explicitly call begin to start the transaction, as a result, each inserted entry is automatically submitted, seriously affecting the speed. You can call begin before executing the SQL statement. Multiple SQL statements form a transaction (even if autocommit is enabled), which greatly improves the performance.

View autocommit: Select @ autocommit;

Set autocommit: Set autocommit = 0;

In mysql5.1, how does one disable autocommit at startup?

Add init_connect = 'set autocommit = 0' to the configuration'

Gemeni
Gemeni table has been released since MySQL 4.0. However, up to now, there have been few introductions to it, and even fewer applications. We will not introduce it for the time being.

MySQL has many data table types, including MyISAM and InnoDB.
The two types have their own advantages and disadvantages. You need to select a suitable one based on the actual situation. MySQL supports setting different types for different tables. The following is a simple comparison:
MyISAM Table type is a mature and stable table type, but MyISAM does not support some features.

MyISAM InnoDB

Transactions are not supported.
Data row locking is not supported, only table locking is supported.
Foreign key constraints are not supported
The tablespace is relatively small and relatively large. The maximum size is 2 times.
Full-text indexing is not supported
GIS data is not supported
The query speed is slow when count (*) is not executed.

 

 

 

 

SQL example:
[Create Table tb_test type = heap (name char (10) Not null, numb char (8) Not null, primary key (name ))]

Bytes ---------------------------------------------------------------------------------------------

1. You can run the following command to switch the non-transaction table to the transaction (data will not be lost). The InnoDB table is safer than the MyISAM table:
Alter table tablename type = InnoDB;
2. InnoDB tables cannot use the repair table command and myisamchk-r table_name
However, you can use check table and mysqlcheck [Options] database [Tables]
3. The following parameters are added to the command line for starting the MySQL database so that the newly released MySQL Data Tables use transactions by default (only the create statement is affected .)
-Default-table-type = InnoDB
4. temporarily changing the default table type can be used:
Set table_type = InnoDB;
Show variables like 'table _ type ';
Or:
C:/MySQL/bin/mysqld-max-NT-standalone-default-table-type = InnoDB

MySQL, as the most popular free database service engine, has been popular for a long time. However, some people may not know much about the internal environment of MySQL, especially those mechanisms for concurrent processing. Today, let's take a look at the classification of data tables in MySQL and their simple properties.

Up to now, MySQL has provided users with seven tables, including DBD, heap, isam, merge, myias, InnoDB, and gemeni. Among them, DBD and InnoDB are transaction security tables, while others are transaction non-security tables.

 
DBD
The Berkeley dB (DBD) Table supports transaction processing and is developed by sleepycat. It provides the long-awaited MySQL feature-transaction control. Transaction control is a very valuable feature in any database system, because they ensure that a group of commands can be successfully executed or rolled back.

Heap
The heap table is the fastest table to access data in MySQL. This is because they use a hash index stored in the dynamic memory, but if MySQL or the server crashes, the memory data will be lost.

Isam
Isam tables are the default table types of earlier MySQL versions until myiasm is developed. We recommend that you do not use it any more.

Merge
Merge is an interesting new type that appears after 3.23.25. A merge table is actually a set of MyISAM tables that are merged into a table mainly for efficiency consideration, this not only improves the speed, search efficiency, and repair efficiency, but also saves disk space.

Myiasm
Myiasm is based on the iasm code and should be considered a derivative of iasm, but it adds a lot of useful extensions. It is the default data table Type of MySQL. Based on the traditional isam type, isam is the abbreviation of indexed sequential access method (sequential access method with indexes). Generally, it is a standard method for storing records and files. Compared with other storage engines, MyISAM provides most tools for checking and repairing tables. Isam tables can be compressed and support full-text search. However, they are insecure and do not support foreign keys. If the transaction is rolled back, incomplete rollback will be caused and it is not atomic. Therefore, if you ignore transactions and access concurrency and need to execute a large number of select search statements, MyISAM will be the best choice.

Why is myiasm better:
Myiasm tables are smaller than iasm tables, so fewer resources are used.
Myiasm tables can be transplanted at the binary layer on different platforms.
Myiasm has a larger key size and a larger key size limit.
For the MyISAM storage engine, its read/write locks and read/write locks are mutually exclusive, so that read/write operations are sequential. So, one process requests the read lock of a MyISAM table, and the other process also requests the write lock of the same table. How does MySQL handle this? The answer is that the write process obtains the lock first. Not only that, even if the Read Request first goes to the lock wait queue, after the write request arrives, the write lock will be inserted before the read lock request! This is because MySQL considers that write requests are generally more important than read requests. This is precisely the reason why MyISAM tables are not suitable for applications with a large number of update operations and query operations, because a large number of update operations will make it difficult for query operations to obtain read locks, which may be blocked forever. This situation may sometimes become very bad! Fortunately, we can adjust MyISAM through some settings.
. By specifying the startup parameter low-priority-updates, the MyISAM engine gives the Read Request priority by default. Run the set low_priority_updates = 1 command to lower the priority of the update request sent by the connection. You can specify the low_priority attribute of an insert, update, or delete statement to reduce the priority of the statement. Although the above three methods are both update-first or query-first methods, they can still be used to query applications that are relatively important (such as user logon to the system, read lock wait is a serious problem. In addition, MySQL also provides a compromise to adjust read/write conflicts, that is, to set an appropriate value for the system parameter max_write_lock_count. When the read lock of a table reaches this value, mySQL temporarily lowers the priority of write requests, giving the read process a certain chance to get the lock.

The Problems and Solutions brought about by the write priority scheduling mechanism have been discussed above. It should also be emphasized that some query operations that require a long time to run will also starve the write process "! Therefore, the application should try to avoid long-running query operations. Do not always want to use a select clause to solve the problem, because this seemingly clever SQL statement is often complicated, the execution takes a long time. If possible, you can use an intermediate table or other measures to "break down" the SQL statement, so that each step of the query can be completed in a short time, this reduces lock conflicts. If complex queries are unavoidable, they should be executed during idle time periods. For example, some regular statistics can be executed at night.

InnoDB
InnoDB is a relatively new data table Type launched after MySQL 4.0, Which is transaction security. It has the same features as the bdb type and supports foreign keys. InnoDB tables are faster than bdb tables. Therefore, if you need a transaction-safe storage engine, we recommend that you use it. If you execute a large number of insert or update operations on your data, InnoDB tables should also be used for performance considerations. For InnoDB tables that support transactions, the main cause of the impact on the speed is that autocommit is enabled by default, and the program does not explicitly call begin to start the transaction, as a result, each inserted entry is automatically submitted, seriously affecting the speed. You can call begin before executing the SQL statement. Multiple SQL statements form a transaction (even if autocommit is enabled), which greatly improves the performance.

View autocommit: Select @ autocommit;

Set autocommit: Set autocommit = 0;

In mysql5.1, how does one disable autocommit at startup?

Add init_connect = 'set autocommit = 0' to the configuration'

Gemeni
Gemeni table has been released since MySQL 4.0. However, up to now, there have been few introductions to it, and even fewer applications. We will not introduce it for the time being.

MySQL has many data table types, including MyISAM and InnoDB.
The two types have their own advantages and disadvantages. You need to select a suitable one based on the actual situation. MySQL supports setting different types for different tables. The following is a simple comparison:
MyISAM Table type is a mature and stable table type, but MyISAM does not support some features.

MyISAM InnoDB

Transactions are not supported.
Data row locking is not supported, only table locking is supported.
Foreign key constraints are not supported
The tablespace is relatively small and relatively large. The maximum size is 2 times.
Full-text indexing is not supported
GIS data is not supported
The query speed is slow when count (*) is not executed.

 

 

 

 

SQL example:
[Create Table tb_test type = heap (name char (10) Not null, numb char (8) Not null, primary key (name ))]

Bytes ---------------------------------------------------------------------------------------------

1. You can run the following command to switch the non-transaction table to the transaction (data will not be lost). The InnoDB table is safer than the MyISAM table:
Alter table tablename type = InnoDB;
2. InnoDB tables cannot use the repair table command and myisamchk-r table_name
However, you can use check table and mysqlcheck [Options] database [Tables]
3. The following parameters are added to the command line for starting the MySQL database so that the newly released MySQL Data Tables use transactions by default (only the create statement is affected .)
-Default-table-type = InnoDB
4. temporarily changing the default table type can be used:
Set table_type = InnoDB;
Show variables like 'table _ type ';
Or:
C:/MySQL/bin/mysqld-max-NT-standalone-default-table-type = InnoDB