Discussion on the advantages and disadvantages of MySQL storage engine selection InnoDB and MyISAM
- 2020-05-19 06:03:18
- OfStack
Let's answer a few questions:
◆ does your database have foreign keys?
◆ do you need transaction support?
◆ do you need a full-text index?
◆ what query mode do you often use?
How big is your data?
Thinking through these questions will help you find the right direction, but it's not absolute. If you need a transaction or a foreign key, then InnoDB might be a good way to do it. If you need a full-text index, MyISAM is usually a good choice because it's built into the system, however, we don't really test two million line records very often. So, even a little bit slower, we can get a full-text index from InnoDB by using Sphinx.
The size of the data is an important factor in which storage engine you choose. Large data sets tend to be InnoDB because they support transaction processing and failover. The database determines the amount of time it takes to recover from a failure. InnoDB can use transaction logs for data recovery, which is faster. Whereas MyISAM can take hours or even days to do this, InnoDB can take minutes.
Your habit of manipulating database tables can also be a significant performance concern. For example, COUNT() can be very fast in MyISAM, and painful in InnoDB. Primary key queries can be quite, quite fast under InnoDB, but be careful if our primary key is too long it can also cause performance problems. Large Numbers of inserts statements are 1 faster under MyISAM, but updates is 1 faster under InnoDB -- especially when concurrency is high.
So, which one do you use? As a rule of thumb, if it is a small application or project, then MyISAM may be more suitable. Of course, MyISAM will have great success in large environments, but not always. If you're planning to use a very large data volume project and need transaction processing or foreign key support, then you really should go straight to InnoDB. But remember that InnoDB tables require more memory and storage, and converting 100GB MyISAM tables to InnoDB tables can be a very bad experience.
MyISAM storage engine
MyISAM is the default storage engine. It is based on older ISAM code, but has many useful extensions. Some features of the MyISAM storage engine:
● all data values are stored in low bytes first. This separates the data machine from the operating system. The only 1 requirement for the portability of base 2 is that the machine use a complement (as has been the case with machines in the last 20 years) and the IEEE floating point format (which is completely dominant in mainstream machines). The only machine that does not support base 2 compatibility is an embedded system. These systems sometimes use special processors.
● low bytes of data stored first does not seriously affect the speed; The byte 1 in a data row is generally unfederated, and reading the unfederated byte from one direction does not require more resources than reading it from the other direction. The code on the server that gets the column values is not as time-intensive as the other code.
● large files (up to 63-bit file length) are supported on file systems and operating systems that support large files.
● dynamically sized rows are less fragmented when deletes and updates are mixed with inserts. This is done automatically by merging adjacent deleted blocks and extending to the next block if the next block is deleted.
● the maximum number of indexes per MyISAM table is 64. This can be changed by recompiling. The maximum number of columns per index is 16.
● the maximum key length is 1000 bytes. This can also be changed by compiling. In cases where the key length exceeds 250 bytes, a key block exceeding 1024 bytes is used.
● the BLOB and TEXT columns can be indexed.
●NULL values are allowed in the indexed columns. This takes 0-1 bytes per key.
● all numeric key values are stored in high bytes first to allow 1 higher index compression.
● when records are inserted in sorted order (as you would with an AUTO_INCREMENT column), the index tree is split so that the high nodes contain only one key. This improves the space utilization of the index tree.
● internal processing of 1 AUTO_INCREMEN column per table. MyISAM automatically updates this column for INSERT and UPDATE operations. This makes the AUTO_INCREMENT column faster (at least 10%). It cannot be reused after the value at the top of the sequence has been deleted. When the AUTO_INCREMENT column is defined as the last column of a multicolumn call, a reusing of the value removed from the top of the sequence can occur. The AUTO_INCREMENT value can be reset using either ALTER TABLE or myisamch.
● if there is no free block in the middle of the data file, you can INSERT new rows into the table while other threads read from the table. (this is known as concurrent operation). Free blocks appear as a result of deleting rows or updating dynamically long rows with more data than the current content. When all free blocks are used up, future inserts become concurrent.
● you can put data files and index files in different directories for higher speeds using DATA DIRECTORY and INDEX DIRECTORY options CREATE TABLE. See section 13.1.5, "CREATE TABLE syntax".
Each character column can have a different character set.
● another flag in the MyISAM index file that indicates whether the table was closed correctly. If mysqld is started with the -- myisam-recover option, the MyISAM table is automatically checked when opened, and if the table is improperly closed, the table is fixed.
● if you run myisamchk with the -- update-state option, it marks the table as checked. myisamchk --fast checks only those tables that do not have this flag.
●myisamchk --analyze stores statistics for partial keys as well as for the entire key.
●myisampack can package the BLOB and VARCHAR columns.
MyISAM also supports the following features:
● support true VARCHAR type; The VARCHAR column starts with a length stored in 2 bytes.
● tables with VARCHAR can have fixed or dynamic record lengths.
● the VARCHAR and CHAR columns can be up to 64KB.
● a scrambled calculated index pair can be used on UNIQUE. This allows you to have UNIQUE on any column merge in the table. (however, you cannot search on an UNIQUE calculated index).
InnoDB storage engine
InnoDB provides a transaction-safe (ACID compatible) storage engine with commit, rollback, and crash recovery capabilities to MySQL. InnoDB locks at the row level and also provides a non-locked read to Oracle style 1 in SELECT statements. These features increase multi-user deployment and performance. There is no need to extend locking in InnoDB, because row-level locking in InnoDB is suitable for very small Spaces. InnoDB also supports FOREIGN KEY coercion. In the SQL query, you are free to mix InnoDB table types with other MySQL table types, even in the same query.
InnoDB is designed for maximum performance when handling large amounts of data. Its CPU efficiency is probably unmatched by any other disk-based relational database engine.
The InnoDB storage engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its table & index in one table space, which can contain several files (or raw disk partitions). This is different from MyISAM tables, where each table is kept in a separate file, for example. The InnoDB table can be any size, even on operating systems where the file size is limited to 2GB.
InnoDB is included by default in the MySQL2 base distribution. Windows Essentials installer makes InnoDB the default table for MySQL on Windows.
InnoDB is used to generate large database sites that require high performance. The famous Internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. Stores more than 1TB of data on InnoDB, and one other site handles an average of 800 inserts/updates per second on InnoDB.
The difference between InnoDB and MyISAM
Summary of the differences:
MyISAM is the default storage engine in MySQL, 1 generally not too many people care about this stuff. Deciding what kind of storage engine to use is a very tricky thing, but it's still worth investigating. The article here only considers MyISAM and InnoDB, because these two are the most common.
Let's answer a few questions:
Does your database have foreign keys?
Do you need transaction support?
Do you need a full-text index?
What query patterns do you often use?
How big is your data?
Thinking through these questions will help you find the right direction, but it's not absolute. If you need a transaction or a foreign key, then InnoDB might be a good way to do it. If you need a full-text index, MyISAM is usually a good choice because it's built into the system, however, we don't really test two million line records very often. So, even a little bit slower, we can get a full-text index from InnoDB using Sphinx.
The size of the data is an important factor in which storage engine you choose. Large data sets tend to be InnoDB because they support transaction processing and failover. The database determines the amount of time it takes to recover from a failure. InnoDB can use transaction logs for data recovery, which is faster. While MyISAM may take hours or even days to do this, InnoDB only takes a few minutes.
Your habit of manipulating database tables can also be a significant performance concern. For example, COUNT() can be very fast in MyISAM, but it can be painful under InnoDB. Primary key queries under InnoDB are quite, quite fast, but be careful if our primary key is too long it can cause performance problems. Large Numbers of inserts statements are 1 faster under MyISAM, but updates is 1 faster under InnoDB -- especially when concurrency is high.
So, which one do you use? As a rule of thumb, if it is a small application or project, then MyISAM may be more suitable. Of course, MyISAM can be very successful in large environments, but not always. If you are planning to use a very large data volume project and need transaction processing or foreign key support, then you really should go straight to InnoDB. Keep in mind that InnoDB tables require more memory and storage, and converting 100GB MyISAM tables to InnoDB tables can be a very bad experience.
Summary of differences:
1.InnoDB does not support indexes of type FULLTEXT.
2.InnoDB does not store the exact number of rows in the table, that is, select count(*) from table scans the entire table once to calculate the number of rows, but MyISAM simply reads out the number of rows saved. Note that when the count(*) statement contains the where condition, the operations for both tables are identical.
3. For fields of type AUTO_INCREMENT, InnoDB must contain an index for that field only, but in the MyISAM table, it is possible to set up a joint index with other fields 1.
4. When DELETE FROM table, InnoDB will not re-establish the table, but will delete 1 row and 1 row.
5. The LOAD TABLE FROM MASTER operation does not work for InnoDB. The solution is to first change the InnoDB table to MyISAM table and then to InnoDB table after importing the data, but not for tables that use additional InnoDB features (such as foreign keys).
In addition, the row lock of InnoDB table is not absolute. If MySQL cannot determine the scope to scan when executing an SQL statement, InnoDB table will also lock the full table, for example, update table set num=1 where name like "%aaa%".
Ways to improve InnoDB performance:
MyISAM and InnoDB storage engine performance difference is not very great, for InnoDB, impact performance mainly by innodb_flush_log_at_trx_commit this option, if set to 1, so every time insert data automatically submit, lead to sharp decline in performance, should be related to refresh the logs, is set to 0 to see efficiency improved significantly, of course, Similarly, you can submit "SET AUTOCOMMIT = 0" in SQL to achieve good performance. In addition, I've heard that setting innodb_buffer_pool_size can improve the performance of InnoDB, but my tests found no significant improvement.
Basically we can consider to use InnoDB to replace our MyISAM engine, InnoDB itself because of the many good characteristics, such as transaction support, stored procedures, views, row-level locking, etc., in the case of concurrent many, believe a lot of performance must be better than MyISAM InnoDB, of course, the corresponding in my. cnf configuration is the key of good configuration, can effectively speed up your application.
Any table is not a panacea. Only by selecting the appropriate table type according to the appropriate business type can MySQL maximize its performance advantages.
◆ does your database have foreign keys?
◆ do you need transaction support?
◆ do you need a full-text index?
◆ what query mode do you often use?
How big is your data?
Thinking through these questions will help you find the right direction, but it's not absolute. If you need a transaction or a foreign key, then InnoDB might be a good way to do it. If you need a full-text index, MyISAM is usually a good choice because it's built into the system, however, we don't really test two million line records very often. So, even a little bit slower, we can get a full-text index from InnoDB by using Sphinx.
The size of the data is an important factor in which storage engine you choose. Large data sets tend to be InnoDB because they support transaction processing and failover. The database determines the amount of time it takes to recover from a failure. InnoDB can use transaction logs for data recovery, which is faster. Whereas MyISAM can take hours or even days to do this, InnoDB can take minutes.
Your habit of manipulating database tables can also be a significant performance concern. For example, COUNT() can be very fast in MyISAM, and painful in InnoDB. Primary key queries can be quite, quite fast under InnoDB, but be careful if our primary key is too long it can also cause performance problems. Large Numbers of inserts statements are 1 faster under MyISAM, but updates is 1 faster under InnoDB -- especially when concurrency is high.
So, which one do you use? As a rule of thumb, if it is a small application or project, then MyISAM may be more suitable. Of course, MyISAM will have great success in large environments, but not always. If you're planning to use a very large data volume project and need transaction processing or foreign key support, then you really should go straight to InnoDB. But remember that InnoDB tables require more memory and storage, and converting 100GB MyISAM tables to InnoDB tables can be a very bad experience.
MyISAM storage engine
MyISAM is the default storage engine. It is based on older ISAM code, but has many useful extensions. Some features of the MyISAM storage engine:
● all data values are stored in low bytes first. This separates the data machine from the operating system. The only 1 requirement for the portability of base 2 is that the machine use a complement (as has been the case with machines in the last 20 years) and the IEEE floating point format (which is completely dominant in mainstream machines). The only machine that does not support base 2 compatibility is an embedded system. These systems sometimes use special processors.
● low bytes of data stored first does not seriously affect the speed; The byte 1 in a data row is generally unfederated, and reading the unfederated byte from one direction does not require more resources than reading it from the other direction. The code on the server that gets the column values is not as time-intensive as the other code.
● large files (up to 63-bit file length) are supported on file systems and operating systems that support large files.
● dynamically sized rows are less fragmented when deletes and updates are mixed with inserts. This is done automatically by merging adjacent deleted blocks and extending to the next block if the next block is deleted.
● the maximum number of indexes per MyISAM table is 64. This can be changed by recompiling. The maximum number of columns per index is 16.
● the maximum key length is 1000 bytes. This can also be changed by compiling. In cases where the key length exceeds 250 bytes, a key block exceeding 1024 bytes is used.
● the BLOB and TEXT columns can be indexed.
●NULL values are allowed in the indexed columns. This takes 0-1 bytes per key.
● all numeric key values are stored in high bytes first to allow 1 higher index compression.
● when records are inserted in sorted order (as you would with an AUTO_INCREMENT column), the index tree is split so that the high nodes contain only one key. This improves the space utilization of the index tree.
● internal processing of 1 AUTO_INCREMEN column per table. MyISAM automatically updates this column for INSERT and UPDATE operations. This makes the AUTO_INCREMENT column faster (at least 10%). It cannot be reused after the value at the top of the sequence has been deleted. When the AUTO_INCREMENT column is defined as the last column of a multicolumn call, a reusing of the value removed from the top of the sequence can occur. The AUTO_INCREMENT value can be reset using either ALTER TABLE or myisamch.
● if there is no free block in the middle of the data file, you can INSERT new rows into the table while other threads read from the table. (this is known as concurrent operation). Free blocks appear as a result of deleting rows or updating dynamically long rows with more data than the current content. When all free blocks are used up, future inserts become concurrent.
● you can put data files and index files in different directories for higher speeds using DATA DIRECTORY and INDEX DIRECTORY options CREATE TABLE. See section 13.1.5, "CREATE TABLE syntax".
Each character column can have a different character set.
● another flag in the MyISAM index file that indicates whether the table was closed correctly. If mysqld is started with the -- myisam-recover option, the MyISAM table is automatically checked when opened, and if the table is improperly closed, the table is fixed.
● if you run myisamchk with the -- update-state option, it marks the table as checked. myisamchk --fast checks only those tables that do not have this flag.
●myisamchk --analyze stores statistics for partial keys as well as for the entire key.
●myisampack can package the BLOB and VARCHAR columns.
MyISAM also supports the following features:
● support true VARCHAR type; The VARCHAR column starts with a length stored in 2 bytes.
● tables with VARCHAR can have fixed or dynamic record lengths.
● the VARCHAR and CHAR columns can be up to 64KB.
● a scrambled calculated index pair can be used on UNIQUE. This allows you to have UNIQUE on any column merge in the table. (however, you cannot search on an UNIQUE calculated index).
InnoDB storage engine
InnoDB provides a transaction-safe (ACID compatible) storage engine with commit, rollback, and crash recovery capabilities to MySQL. InnoDB locks at the row level and also provides a non-locked read to Oracle style 1 in SELECT statements. These features increase multi-user deployment and performance. There is no need to extend locking in InnoDB, because row-level locking in InnoDB is suitable for very small Spaces. InnoDB also supports FOREIGN KEY coercion. In the SQL query, you are free to mix InnoDB table types with other MySQL table types, even in the same query.
InnoDB is designed for maximum performance when handling large amounts of data. Its CPU efficiency is probably unmatched by any other disk-based relational database engine.
The InnoDB storage engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its table & index in one table space, which can contain several files (or raw disk partitions). This is different from MyISAM tables, where each table is kept in a separate file, for example. The InnoDB table can be any size, even on operating systems where the file size is limited to 2GB.
InnoDB is included by default in the MySQL2 base distribution. Windows Essentials installer makes InnoDB the default table for MySQL on Windows.
InnoDB is used to generate large database sites that require high performance. The famous Internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. Stores more than 1TB of data on InnoDB, and one other site handles an average of 800 inserts/updates per second on InnoDB.
The difference between InnoDB and MyISAM
Summary of the differences:
MyISAM is the default storage engine in MySQL, 1 generally not too many people care about this stuff. Deciding what kind of storage engine to use is a very tricky thing, but it's still worth investigating. The article here only considers MyISAM and InnoDB, because these two are the most common.
Let's answer a few questions:
Does your database have foreign keys?
Do you need transaction support?
Do you need a full-text index?
What query patterns do you often use?
How big is your data?
Thinking through these questions will help you find the right direction, but it's not absolute. If you need a transaction or a foreign key, then InnoDB might be a good way to do it. If you need a full-text index, MyISAM is usually a good choice because it's built into the system, however, we don't really test two million line records very often. So, even a little bit slower, we can get a full-text index from InnoDB using Sphinx.
The size of the data is an important factor in which storage engine you choose. Large data sets tend to be InnoDB because they support transaction processing and failover. The database determines the amount of time it takes to recover from a failure. InnoDB can use transaction logs for data recovery, which is faster. While MyISAM may take hours or even days to do this, InnoDB only takes a few minutes.
Your habit of manipulating database tables can also be a significant performance concern. For example, COUNT() can be very fast in MyISAM, but it can be painful under InnoDB. Primary key queries under InnoDB are quite, quite fast, but be careful if our primary key is too long it can cause performance problems. Large Numbers of inserts statements are 1 faster under MyISAM, but updates is 1 faster under InnoDB -- especially when concurrency is high.
So, which one do you use? As a rule of thumb, if it is a small application or project, then MyISAM may be more suitable. Of course, MyISAM can be very successful in large environments, but not always. If you are planning to use a very large data volume project and need transaction processing or foreign key support, then you really should go straight to InnoDB. Keep in mind that InnoDB tables require more memory and storage, and converting 100GB MyISAM tables to InnoDB tables can be a very bad experience.
Summary of differences:
1.InnoDB does not support indexes of type FULLTEXT.
2.InnoDB does not store the exact number of rows in the table, that is, select count(*) from table scans the entire table once to calculate the number of rows, but MyISAM simply reads out the number of rows saved. Note that when the count(*) statement contains the where condition, the operations for both tables are identical.
3. For fields of type AUTO_INCREMENT, InnoDB must contain an index for that field only, but in the MyISAM table, it is possible to set up a joint index with other fields 1.
4. When DELETE FROM table, InnoDB will not re-establish the table, but will delete 1 row and 1 row.
5. The LOAD TABLE FROM MASTER operation does not work for InnoDB. The solution is to first change the InnoDB table to MyISAM table and then to InnoDB table after importing the data, but not for tables that use additional InnoDB features (such as foreign keys).
In addition, the row lock of InnoDB table is not absolute. If MySQL cannot determine the scope to scan when executing an SQL statement, InnoDB table will also lock the full table, for example, update table set num=1 where name like "%aaa%".
Ways to improve InnoDB performance:
MyISAM and InnoDB storage engine performance difference is not very great, for InnoDB, impact performance mainly by innodb_flush_log_at_trx_commit this option, if set to 1, so every time insert data automatically submit, lead to sharp decline in performance, should be related to refresh the logs, is set to 0 to see efficiency improved significantly, of course, Similarly, you can submit "SET AUTOCOMMIT = 0" in SQL to achieve good performance. In addition, I've heard that setting innodb_buffer_pool_size can improve the performance of InnoDB, but my tests found no significant improvement.
Basically we can consider to use InnoDB to replace our MyISAM engine, InnoDB itself because of the many good characteristics, such as transaction support, stored procedures, views, row-level locking, etc., in the case of concurrent many, believe a lot of performance must be better than MyISAM InnoDB, of course, the corresponding in my. cnf configuration is the key of good configuration, can effectively speed up your application.
Any table is not a panacea. Only by selecting the appropriate table type according to the appropriate business type can MySQL maximize its performance advantages.