Advantages and disadvantages of InnoDB and MyISAM for MySQL storage engine selection

The advantages and disadvantages of InnoDB and MyISAM for MySQL storage engine analysis bitsCN.com are as follows:

◆ Does your database have a foreign key?
◆ Do you need transaction support?
◆ Do you need full-text indexing?
◆ What query mode do you often use?
◆ How big is your data?

Thinking about the above questions can help you find the right direction, but that is not absolute. If you need transaction processing or foreign keys, InnoDB may be a good method. If you need full-text indexing, MyISAM is usually a good choice because it is built in the system. However, we do not often test the 2 million rows of records. Therefore, even if it is slower, we can use Sphinx to obtain full-text indexes from InnoDB.

The size of data is an important factor that affects your choice of storage engines. large-sized data sets tend to use InnoDB because they support transaction processing and fault recovery. The small size of the database determines the duration of fault recovery. InnoDB can use transaction logs to recover data, which is faster. However, MyISAM may take several hours or even a few days to do these tasks. InnoDB only needs a few minutes.

The habit of operating database tables may also be a factor that has a great impact on performance. For example, COUNT () can be very fast in the MyISAM table, but it may be very painful in the InnoDB table. Primary key query will be quite fast in InnoDB, but be careful that if our primary key is too long, it will also cause performance problems. A large number of inserts statements are faster in MyISAM, but the updates is faster in InnoDB-especially when the concurrency is large.

So which one do you use? Based on experience, if it is a small application or project, MyISAM may be more suitable. Of course, using MyISAM in a large environment can also be very successful, but this is not always the case. If you plan to use a project with a large amount of data and require transaction processing or foreign key support, you should use InnoDB directly. But remember that InnoDB tables require more memory and storage. converting a 100 GB MyISAM table to an InnoDB table may cause a bad experience.

MyISAM storage engine

MyISAM is the default storage engine. It is based on older ISAM code, but there are 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 requirement for binary portability is that the machine uses the complement code (as in the past 20 years) and the IEEE floating point format (also dominated by mainstream machines ). The only machine that does not support binary compatibility is an embedded system. These systems sometimes use special processors.
● Low data bytes are stored first, which does not seriously affect the speed. bytes in data rows are generally not combined, reading unassociated bytes from one direction does not occupy more resources than reading from the reverse direction. The code for getting the column value on the server is not time-consuming compared with other code.
● Large files (up to 63 characters in length) are supported on file systems and operating systems that support large files.
● When the deletion, update, and insertion are mixed, the dynamic size rows are less fragmented. This is automatically completed by merging adjacent deleted blocks and extending them to the next block if the next block is deleted.
● The maximum number of indexes in each MyISAM table is 64. This can be changed through recompilation. The maximum number of columns for each index is 16.
● The maximum key length is 1000 bytes. This can also be changed through compilation. When the key length exceeds 250 bytes, a key block exceeding 1024 bytes is used.
● BLOB and TEXT columns can be indexed.
● The NULL value is allowed in the indexed column. This occupies 0-1 bytes of each key.
● All numeric keys are stored in high byte first to allow compression of a higher index.
● When records are inserted in an ordered Order (like when you use an AUTO_INCREMENT column), the index tree is split so that the high node contains only one key. This improves the spatial utilization of the index tree.
● Internal processing of an AUTO_INCREMEN column in each table. MyISAM automatically updates this column for INSERT and UPDATE operations. This makes the AUTO_INCREMENT column faster (at least 10% ). After the value at the top of the sequence is deleted, it cannot be reused. (When the AUTO_INCREMENT column is defined as the last column of multi-column index, the deleted value from the top of the sequence can be reused ). The AUTO_INCREMENT value can be reset using alter table or myisamch.
● If the table in the middle of the data file does not have a free block, you can INSERT a new row into the table while other threads read from the table. (This is recognized as a concurrent operation ). The appearance of a free block is the result of deleting a row, or updating a dynamic length row with more data than the current content. When all free blocks are used up (filled up), future inserts will become concurrent.
● You can store DATA files and INDEX files in different directories and use the data directory and index directory options to create table for higher speed. For more information, see section 13.1.5 "create table syntax ".
● Each character column can have different character sets.
● Another identifier in the MyISAM index file indicates whether the table is properly disabled. If you use the -- myisam-recover option to start mysqld, the MyISAM table is automatically checked when it is opened. if it is disabled improperly, the table is repaired.
● If you use the -- update-state option to run myisamchk, it indicates that the table has been checked. Myisamchk -- fast only checks tables without this flag.
● Myisamchk -- analyze stores statistics for some keys, and stores statistics for the entire key.
● Myisampack can pack BLOB and VARCHAR columns.

MyISAM also supports the following features:

● The true VARCHAR type is supported. the VARCHAR column starts with the length of the two bytes.
● Tables with VARCHAR can have fixed or dynamic record lengths.
● VARCHAR and CHAR columns can be up to 64 KB.
● A messy computed index pair can be used for UNIQUE. This allows you to merge any columns in the table with UNIQUE. (Even so, you cannot search on a UNIQUE indexed index ).

InnoDB storage engine

InnoDB provides MySQL with a transaction security (ACID-compatible) storage engine with the capabilities of commit, rollback, and crash recovery. InnoDB locks row-level and also provides an Oracle-style non-locked read in the SELECT statement. These features increase the deployment and performance of multiple users. There is no need to expand locking in InnoDB, because row-level locking in InnoDB is suitable for very small space. InnoDB also supports foreign key forcing. In SQL queries, you can freely mix InnoDB tables with other MySQL tables, or even in the same query.
InnoDB is designed for maximum performance when processing massive data volumes. Its CPU efficiency may be unmatched by any other disk-based relational database engine.
The InnoDB storage engine is fully integrated with the MySQL server. The InnoDB storage engine maintains its own buffer pool to cache data and indexes in the main memory. InnoDB stores its tables and indexes in a tablespace. the tablespace can contain several files (or original disk partitions ). This is different from the MyISAM table. for example, in the MyISAM table, each table is in a separate file. InnoDB tables can be of any size, even on an operating system with a file size limited to 2 GB.
InnoDB is included in MySQL binary distribution by default. Windows Essentials installer makes InnoDB the default MySQL table on Windows.
InnoDB is used to generate large databases that require high performance. The famous Internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. stores more than 1 TB of data on InnoDB, and some other sites process an average of 800 inserts/updates per second on InnoDB.

Differences between InnoDB and MyISAM

Differences:

MyISAM is the default storage engine in MySQL. generally, not many people care about this. It is a tricky thing to decide what storage engine to use, but let's take a look at it. here we only consider MyISAM and InnoDB, because these two are the most common.
Next let's answer some questions:

Does your database have a foreign key?
Do you need transaction support?
Do you need full-text indexing?
What query mode do you often use?
How big is your data?

Thinking about the above questions can help you find the right direction, but that is not absolute. If you need transaction processing or foreign keys, InnoDB may be a good method. If you need full-text indexing, MyISAM is usually a good choice because it is built in the system. However, we do not often test the 2 million rows of records. Therefore, even if it is slower, we can use Sphinx to obtain full-text indexes from InnoDB.
The size of data is an important factor that affects your choice of storage engines. large-sized data sets tend to use InnoDB because they support transaction processing and fault recovery. The small size of the database determines the duration of fault recovery. InnoDB can use transaction logs to recover data, which is faster. However, MyISAM may take several hours or even a few days to do these tasks. InnoDB only needs a few minutes.
The habit of operating database tables may also be a factor that has a great impact on performance. For example, COUNT () can be very fast in the MyISAM table, but it may be very painful in the InnoDB table. Primary key query will be quite fast in InnoDB, but be careful that if our primary key is too long, it will also cause performance problems. A large number of inserts statements are faster in MyISAM, but the updates is faster in InnoDB-especially when the concurrency is large.
So which one do you use? Based on experience, if it is a small application or project, MyISAM may be more suitable. Of course, using MyISAM in a large environment can also be very successful, but this is not always the case. If you are planning to use a project with a large amount of data and require transaction processing or foreign key support, you should use InnoDB directly. But remember that InnoDB tables require more memory and storage. converting a 100 GB MyISAM table to an InnoDB table may cause a bad experience.

Differences:

1. InnoDB does not support FULLTEXT indexes.
2. innoDB does not store the specific number of rows in the table. that is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same.
3. for fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.
4. when deleting FROM table, InnoDB does not create a new table, but deletes a row.
5. the load table from master operation does not work for InnoDB. the solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then change the imported data to the InnoDB TABLE, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys.
In addition, the row lock of the InnoDB table is not absolute. if MySQL cannot determine the scope to be scanned when executing an SQL statement, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "% aaa %"

How to improve InnoDB performance:
The performance of MyISAM and InnoDB storage engines is not very different. for InnoDB, innodb_flush_log_at_trx_commit is the main option that affects performance. if it is set to 1, therefore, data is automatically submitted each time it is inserted, resulting in a sharp decline in performance. it should be related to refreshing logs. setting it to 0 can significantly improve the efficiency. of course, similarly, you can submit "set autocommit = 0" in SQL to SET the performance. In addition, I also heard that setting innodb_buffer_pool_size can improve the performance of InnoDB, but I did not find it significantly improved.
Basically, we can consider using InnoDB to replace our MyISAM engine, because InnoDB has many good features, such as transaction support, stored procedures, views, row-level locking, etc, in the case of a lot of concurrency, I believe that InnoDB must be much better than MyISAM. the configuration in cnf is also critical. a good configuration can effectively accelerate your application.
Any type of table is not omnipotent. you only need to select a proper table type for the business type to maximize the performance advantage of MySQL. BitsCN.com