Discussion on the advantages and disadvantages of choosing InnoDB and MyISAM of Mysql storage engine _mysql

Let us answer some of the following questions:

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

Thinking about these issues can help you find the right direction, but that's not absolute. If you need a transaction or a foreign key, then InnoDB may be a better way. If you need Full-text indexing, then MyISAM is usually a good choice because it's built into the system, but we don't actually test 2 million of lines of records regularly. So, even slowly, we can get full-text indexing from InnoDB by using Sphinx.

The size of the data is an important factor that affects what storage engine you choose, and large data sets tend to choose InnoDB, because they support transaction processing and failback. The size of the database determines the length of the recovery, InnoDB can use the transaction log for data recovery, which is faster. And MyISAM may take hours or even days to do these things, InnoDB only take a few minutes.

Your habit of manipulating database tables may also be a significant factor in performance impact. For example, COUNT () can be very fast in the MyISAM table, and it can be painful under the InnoDB table. The primary key query is pretty fast under InnoDB, but it's important to be careful if our primary key is too long to cause performance problems. A large number of inserts statements would be faster under MyISAM, but updates would be quicker under innodb-especially when the volume of concurrency was high.

So, in the end, which one do you use? According to experience, if it is a small application or project, then MyISAM may be more appropriate. Of course, using MyISAM in a large environment can be a great success, but it's 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 really use the INNODB approach directly. But remember that InnoDB tables require more memory and storage, and converting 100GB MyISAM tables to InnoDB tables can be a very bad experience for you.

MyISAM Storage Engine

MyISAM is the default storage engine. It's based on older ISAM code, but there are a lot of useful extensions. Some features of the MyISAM storage engine:

All data values store the low byte first. This separates the data machine from the operating system. The only requirement for binary portability is that the machine uses the complement (as it has in the last 20 years) and the IEEE floating-point format (which is also completely dominant in the mainstream machine). The only machine that does not support binary compatibility is the embedded system. These systems sometimes use a special processor.
Storing data in a low byte does not seriously affect speed; The bytes in the data row are generally not federated, and the uncommitted bytes in one Direction are no more resource-intensive than the reverse read. The code that gets the column value on the server does not appear to be tight compared to other code.
Large files (up to 63-bit file lengths) are supported on file systems and operating systems that support large files.
When the deletion and the update and insert are mixed, the rows of the dynamic dimension are less fragmented. This is done automatically by merging adjacent blocks that are deleted, and 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. For a key length exceeding 250 bytes, a key block of more than 1024 bytes is used.
BLOBs 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 key values are stored first in high byte to allow for a higher index compression.
When the records are inserted in a sorted order (as you would use a 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 one 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%). The value at the top of the sequence cannot be used again after it has been deleted. (When the Auto_increment column is defined as the last column of a multiple-column index, you can have a situation where the value deleted from the top of the sequence is reused). The auto_increment value can be reset using ALTER TABLE or MYISAMCH.
If the table in the middle of the data file has no free blocks, you can insert new rows into the table while other threads are reading from the table. (This is recognized as concurrent operations). The free block appears as a result of deleting a row, or as a result of updating a dynamic length row with more data than the current content. When all free blocks are exhausted (filled), future inserts become concurrent.
You can put the data files and index files in a different directory, and create a table with the Data directory and index directory options to get a higher speed, see 13.1.5, "CREATE TABLE syntax."
Each character column can be a different character set.
Another flag in the MyISAM index file indicates whether the table was properly closed. If the--myisam-recover option starts the Mysqld,myisam table is automatically checked when it is turned on, the table is repaired if the table is improperly closed.
If you run Myisamchk with the--update-state option, it is labeled as checked. Myisamchk--fast Only check those tables that do not have this flag.
Myisamchk--analyze stores statistics for a partial key, as well as for the entire key.
Myisampack can pack blobs and varchar columns.

MyISAM also supports the following features:

True varchar type is supported; The varchar column starts with the length stored in 2 bytes.
A table with varchar can have a fixed or dynamic record length.
varchar and char columns can be up to 64KB.
A messed up computed index pair can be used with unique. This allows you to have a unique combination of any of the columns in the table. (However, you cannot search on a unique computed index).

InnoDB Storage Engine

INNODB provides MySQL with transaction security (acid-compatible) storage engine with Commit, rollback, and crash recovery capabilities. InnoDB locks the row level and also provides an oracle-consistent, unlocked read in the SELECT statement. These features add to multi-user deployment and performance. There is no need to enlarge the lock in InnoDB because the InnoDB row-level locking fits very small spaces. InnoDB also supports foreign key coercion. In SQL queries, you can freely mix innodb types of tables with other types of MySQL tables, even in the same query.
InnoDB is the maximum performance design for processing large amounts of data. Its CPU efficiency may be unmatched by any other disk-based relational database engine.
The InnoDB storage engine is fully consolidated 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 a tablespace, a tablespace can contain several files (or a raw disk partition). This is different from the MyISAM table, for example in the MyISAM table where each table is separated from the file. The InnoDB table can be any size, even on an operating system that has a file size limited to 2GB.
InnoDB is included in the MySQL binary distribution by default. Windows Essentials Installer makes InnoDB the default table for MySQL on Windows.
InnoDB is used in many large database sites that require high performance. The famous Internet news site slashdot.org runs on the InnoDB. Mytrix, Inc. stores more than 1TB of data on InnoDB, and some other sites handle an average of 800 inserts/updates per second on the InnoDB.

the difference between InnoDB and MyISAM

Difference Overview:

MyISAM is the default storage engine in MySQL, and it's not usually a lot of people who care about this stuff. Deciding what kind of storage engine to use is a very tricky thing to do, but it's worth studying, and the article here only considers the two MyISAM and InnoDB, because these are the two most common.
Let us answer some of the following questions:

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

Thinking about these issues can help you find the right direction, but that's not absolute. If you need a transaction or a foreign key, then InnoDB may be a better way. If you need Full-text indexing, then MyISAM is usually a good choice because it's built into the system, but we don't actually test 2 million of lines of records regularly. So, even slowly, we can get full-text indexing from InnoDB by using Sphinx.
The size of the data is an important factor that affects what storage engine you choose, and large data sets tend to choose InnoDB, because they support transaction processing and failback. The size of the database determines the length of the recovery, InnoDB can use the transaction log for data recovery, which is faster. And MyISAM may take hours or even days to do these things, InnoDB only take a few minutes.
Your habit of manipulating database tables may also be a significant factor in performance impact. For example, COUNT () can be very fast in the MyISAM table, and it can be painful under the InnoDB table. The primary key query is pretty fast under InnoDB, but it's important to be careful if our primary key is too long to cause performance problems. A large number of inserts statements would be faster under MyISAM, but updates would be quicker under innodb-especially when the volume of concurrency was high.
So, exactly which one do you use? From experience, if it is a small application or project, then MyISAM may be more appropriate. Of course, using MyISAM in a large environment can be a great success, but it's 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 really use the INNODB approach directly. But remember that InnoDB tables require more memory and storage, and converting 100GB MyISAM tables to InnoDB tables can be a very bad experience for you.

Difference Summary:

The fulltext type index is not supported for 1.InnoDB.
The number of rows in the table is not saved in 2.InnoDB, that is, when the select count (*) from table is executed, InnoDB scans the entire table to calculate the number of rows, but MyISAM simply reads out the saved rows. Note that when the COUNT (*) statement contains the Where condition, the operations of the two tables are the same.
3. For Auto_increment type fields, the InnoDB must contain only the index of the field, but in the MyISAM table, you can establish a federated index with the other fields.
4.DELETE from table, InnoDB does not re-establish the table, but deletes one row at a time.
5.LOAD table from Master does not work for InnoDB, the solution is to first change the InnoDB table to MyISAM table, import the data and then change to InnoDB table, but for the use of additional InnoDB features (such as foreign key) of the table does not apply.
In addition, row locks on innodb tables are not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the entire table, such as Update table set num=1 where name like "%aaa%"

Ways to improve InnoDB performance:
MyISAM and InnoDB Storage engine performance difference is not very large, for the InnoDB, the impact of performance is mainly innodb_flush_log_at_trx_commit this option, if set to 1, then each time you insert the data will be automatically submitted, resulting in a sharp drop in performance, should be related to the refresh log, set to 0 efficiency can see a significant increase, of course, you can also submit in SQL "Set autocommit = 0" To set to achieve good performance. In addition, it is also heard that by setting up innodb_buffer_pool_size can improve the performance of InnoDB, but I test found no particularly noticeable improvement.
Basically we can consider using InnoDB to replace our MyISAM engine, because InnoDB own many good characteristics, such as transaction support, stored procedure, view, row level lock and so on, in many cases, believe that InnoDB performance is certainly much stronger than MyISAM, of course , the corresponding configuration in the MY.CNF is also more critical, good configuration, can effectively accelerate your application.
Any kind of table is not omnipotent, only appropriate for the business type to choose the appropriate table type, in order to maximize the performance of MySQL advantage.