InnoDB and MyISAM differences

InnoDB and MyISAM are the two most common table types used by many people when using MySQL, both of which have pros and cons, depending on the application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, and InnoDB type support. The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides transactional support for advanced database functions such as external keys.

?
The following are some of the details and the specific implementation differences:


1. InnoDB does not support indexes of type Fulltext.


2. The exact number of rows in a table is not saved in InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the saved rows. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.


3. For a field of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, you can establish a federated index with other fields.


4. Delete from table, InnoDB does not reestablish the table, but deletes one row at a time.


5. The LOAD table from master operation has no effect on InnoDB, and the workaround is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table, but not for 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 scan when executing an SQL statement, the InnoDB table also locks the full table, such as the Update table set num=1 where name like "? a%"


The main difference between the two types is that InnoDB supports transactional and foreign key and row-level locks. MyISAM is not supported. So MyISAM tend to be considered only suitable for use in small projects.


I use MySQL as a user point of view, InnoDB and MyISAM are more like, but from my current operation of the database platform to meet the requirements: 99.9% stability, convenient scalability and high availability, MyISAM is definitely my first choice.


The reasons are as follows:


1, first of all, I am currently on the platform of the majority of projects are read more write less projects, and MyISAM reading performance is stronger than InnoDB.


2, MyISAM index and data are separate, and the index is compressed, the memory usage of the corresponding improved a lot. Can load more indexes, and InnoDB is the index and the data is tightly bound, do not use compression which will cause innodb than MyISAM volume is large.


3, from the platform point of view, often 1, 2 months will occur application developers accidentally update a table where the scope of the wrong, resulting in this table can not be normal use, this time MyISAM the superiority of the embodiment, casually from the day copy of the compressed package out of the corresponding table file, Put it in a database directory, then dump into SQL and back to the main library, and binlog the corresponding. If it's InnoDB, I'm afraid it can't be so fast, don't tell me to let InnoDB regularly back up with an export xxx.sql mechanism, because the smallest database instance on my platform has a size of dozens of g of data.


4, from my contact with the application logic, select COUNT (*) and order BY is the most frequent, probably can account for the entire SQL total statement of more than 60% of the operation, and this operation InnoDB actually will lock the table, many people think InnoDB is a row-level lock, the only Where the primary key is valid, the non-primary key will lock the full table.


5, there is often a lot of application departments need me to give them regular data on some tables, MyISAM words are very convenient, as long as they correspond to the list of the frm. myd,myi files, let them in the corresponding version of the database to start the line, and InnoDB need to export xxx.sql, because the light to other people's files, by the dictionary data file, the other side is not available.


6, if and myisam than insert write operation, InnoDB also not up to MyISAM write performance, if is for index-based update operation, although MyISAM may be inferior innodb, but so high concurrency of write, from the library can chase is also a problem, It might as well be solved by a multi-instance sub-Library table architecture.


7, if it is used MyISAM, the merge engine can greatly speed up the development of the application department, they just do some select count (*) operation on this merge table, it is very suitable for a large project total of about hundreds of millions of rows of a type (such as log, survey statistics) business table.


Of course, InnoDB is not absolutely not, with business projects such as simulation stocks, I am using InnoDB, active users more than 200,000, is also very easy to cope with, so I personally also like InnoDB, but if from the database platform application, I would prefer MyISAM.


In addition, some people may say that you myisam can not resist too much write operation, but I can make up by the structure, say my existing database platform capacity: The total number of master and slave data in more than hundreds of T, more than 1 billion PV dynamic page per day, there are several large items are called by the data interface method is not counted into PV total, ( This includes a large project because the initial memcached was not deployed, resulting in a single database processing 90 million queries per day). My overall database server load averaged around 0.5-1.





Http://www.cnblogs.com/villion/archive/2009/07/09/1893762.html


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 (as the machine has in the last 20 years) and the IEEE floating-point format (which is completely dominant in the mainstream machine). The only machines that do not support binary compatibility are embedded systems. These systems sometimes use a special processor.


· Storing data in low bytes does not seriously affect speed; The bytes in the data row are generally not federated, and reading the unbound bytes from One Direction is no more resource-intensive than reading from reverse. The code that gets the column values on the server does not appear to be time-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 deleting and updating and inserting a mix, the rows of dynamic dimensions are less fragmented. This is done automatically by merging adjacent deleted blocks, and if the next block is deleted, it expands to the next one.


· 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 cases where the key length exceeds 250 bytes, a key block of more than 1024 bytes is used.


· The Blob and text columns can be indexed.


· A null value is allowed in the column of the index. This accounts for 0-1 bytes per key.


· All numeric key values are stored first in high bytes to allow a higher index compression.


· When the records are inserted in a sequential order (as if you were using 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.


· The 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 exploited after it has been deleted. (When the Auto_increment column is defined as the last column of a multicolumn index, the use of values removed from the top of the sequence can occur). 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 known as concurrent operations). The appearance of a free block is the result of deleting rows, or updating a dynamic length row with more data than the current content. When all free blocks are exhausted (filled), future insertions become concurrent.


· You can put data files and index files in different directories, create table for a higher speed with Data directory and index directory options, see 13.1.5, "CREATE table Syntax".


· Each character column can have a different character set.


· Another flag in the MyISAM index file indicates whether the table is closed correctly. If you start the Mysqld,myisam table with the--myisam-recover option, it is automatically checked when it is opened, and the table is repaired if the table is improperly closed.


· If you run Myisamchk with the--update-state option, it labels the table as checked. Myisamchk--fast Check only those tables that do not have this flag.


· Myisamchk--analyze stores statistics for some keys, and also stores statistics for the entire key.


· Myisampack can package blobs and varchar columns.


MyISAM also supports the following features:


· True varchar types are supported, and varchar columns begin with a 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 for unique. This allows you to have unique on the merge of any column within the table. (However, you cannot search on a unique computed index).


InnoDB Storage Engine


InnoDB provides MySQL with a transaction-safe (acid-compatible) storage engine with Commit, rollback, and crash resiliency. InnoDB locks the row-level and also provides an Oracle-style, non-locking read in the SELECT statement. These features add to multi-user deployment and performance. There is no need to widen the lock in the InnoDB because a row-level lock in InnoDB is suitable for very small spaces. InnoDB also supports foreign key coercion. In SQL queries, you are free to mix tables of the InnoDB type with other MySQL table types, even in the same query.


The InnoDB is designed for maximum performance when dealing with large amounts of data. 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, and the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB stores its table and index in a table space, a tablespace can contain several files (or raw disk partitions). This is different from the MyISAM table, such as in the MyISAM table where each table is in a separate file. The InnoDB table can be any size, even if the file size is limited to 2GB on the operating system.


The InnoDB is included in the MySQL binary distribution by default. Windows Essentials Installer makes InnoDB a 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 some other sites handle an average of 800 insertions/updates per second on InnoDB.




The difference between InnoDB and MyISAM


Overview of differences:


MyISAM is the default storage engine in MySQL, and generally not too many people are concerned about this thing. Deciding what kind of storage engine to use is a very tricky thing to do, but it's worth it to study, the article here only considers the two MyISAM and InnoDB, because these two are the most common.


Let's answer some questions first:


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



Thinking about these questions can help you find the right direction, but that's not absolute. If you need transaction processing or foreign keys, then InnoDB may be a good way. If you need full-text indexing, then generally speaking, MyISAM is a good choice because it is built in the system, however, we do not actually test 2 million rows of records in a regular manner. So, even slower, we can get full-text indexing from InnoDB by using Sphinx.


The size of the data is an important factor in what kind of storage engine you choose, and large datasets tend to choose the INNODB approach because they support transactional processing and failback. The small database determines the length of time to recover, and InnoDB can use the transaction log for data recovery, which is faster. While MyISAM may take hours or even days to do these things, InnoDB only takes a few minutes.


Your habit of manipulating database tables can also be a factor that has a significant impact on performance. For example, COUNT () can be very fast in the MyISAM table, and it can be painful under the InnoDB table. While the primary key query will be quite fast under InnoDB, it is important to be careful that if our primary key is too long it can cause performance problems. A large number of inserts statements will be faster under MyISAM, but updates will be faster under innodb-especially when concurrency is large.


So, which one do you use to check? From experience, if it is a small application or project, then MyISAM may be more appropriate. Of course, the use of MyISAM in large-scale environments 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 transactional or foreign key support, then you should really use the InnoDB method directly. But it is important to remember that InnoDB tables require more memory and storage, and converting 100GB MyISAM tables to InnoDB tables may make you have a very bad experience.


Summary of differences:


1.InnoDB does not support indexes of type Fulltext.
The exact number of rows in the table is not saved in 2.InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the saved rows. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.
3. For a field of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, you can establish a federated index with other fields.
4.DELETE from table, InnoDB does not reestablish the table, but deletes one row at a time.
The 5.LOAD table from master operation has no effect on InnoDB, and the workaround is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table, but not for tables that use additional InnoDB features, such as foreign keys.


Also, the row lock of the InnoDB table is not absolute, and if MySQL cannot determine the scope to scan when executing an SQL statement, the InnoDB table also locks the full table, such as the Update table set num=1 where name like "? a%"





Ways to improve InnoDB performance:


MyISAM and InnoDB Storage engine performance difference is not very large, for InnoDB, the main impact of performance is innodb_flush_log_at_trx_commit this option, if set to 1, then each time you insert data is automatically submitted, resulting in a sharp decline in performance, should be related to the refresh log, set to 0 efficiency can see a significant increase, of course, you can also submit "Set autocommit = 0" in SQL to set up to achieve good performance. In addition, I have heard that setting up Innodb_buffer_pool_size can improve the performance of InnoDB, but I have found no particularly significant improvement in my test.


Basically we can consider using InnoDB to replace our MyISAM engine, because InnoDB itself a lot of good features, such as transaction support, stored procedures, views, row-level locking and so on, in the case of a lot of concurrency, I believe 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, to maximize the performance advantage of MySQL.


Http://www.enet.com.cn/article/2008/0311/A20080311178988.shtml




InnoDB and MyISAM are the two most common table types used by many people when using MySQL, both of which have pros and cons, depending on the application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, and InnoDB type support. The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides transactional support for advanced database functions such as external keys.


Myiasm is a new version of the Iasm table, with the following extensions:


• Portability at the binary level.


· NULL column index.


• Less fragmentation of the variable-length line than the ISAM table.


• Support for large files.


• Better index compression.


• Better keys for statistical distributions.


• Better and faster auto_increment processing.


The following are some of the details and the specific implementation differences:


1. InnoDB does not support indexes of type Fulltext.


2. The exact number of rows in a table is not saved in InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the saved rows. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.


3. For a field of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, you can establish a federated index with other fields.


4. Delete from table, InnoDB does not reestablish the table, but deletes one row at a time.


5. The LOAD table from master operation has no effect on InnoDB, and the workaround is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table, but not for 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 scan when executing an SQL statement, the InnoDB table also locks the full table, such as the Update table set num=1 where name like "? a%"


In summary, any kind of table is not omnipotent, only appropriate for the business type to choose the appropriate table type, to maximize the performance advantage of MySQL.


Http://www.ccvita.com/409.html


key_buffer_size– This is very important for the MyISAM table. If you use only the MyISAM table, you can set it to the 30-40% of available memory. A reasonable value depends on the index size, the amount of data, and the load.
Remember, the MyISAM table caches data using the operating system's cache, so you need to set aside some memory for them, and in many cases the data peso is mostly. Nonetheless, it is always necessary to check that all key_buffer are being exploited.
. MYI files are only 1GB, while Key_buffer is set to 4GB is very rare. This is a waste of time. If you rarely use the MyISAM table, you also keep the key_buffer_size below 16-32MB to accommodate the temporary table index that is given to the disk.


innodb_buffer_pool_size– This is very important for the InnoDB table. InnoDB is more sensitive to buffering than the MyISAM table. MyISAM can be run under the default Key_buffer_size settings, but InnoDB is snail-like under the default Innodb_buffer_pool_size settings. Since InnoDB caches both data and indexes, there is no need to leave too much memory on the operating system, so if you only need to use InnoDB, you can set it up to 70-80% of usable memory. If you have a small amount of data and do not burst, you do not need to set the innodb_buffer_pool_size too large.


innodb_additional_pool_size– This option has little impact on performance, at least on operating systems that have almost enough memory to allocate. But if you still want to set it to 20MB (or larger), you need to look at how much memory InnoDB other needs to allocate.


Innodb_log_file_size is important in the case of high write loads, especially large data sets. The larger the value, the higher the performance, but note that the recovery time may be increased. I often set it to 64-512MB, depending on the size of the server. Innodb_log_buffer_size default settings in the case of moderate-strength write loads and shorter transactions, server performance is also possible. If there is a peak update operation or a large load, you should consider increasing its value. If its value is set too high, memory may be wasted. It refreshes every second, so there is no need to set the required memory space for more than 1 seconds. Usually the 8-16MB is enough. The smaller the system, the less its value.


Innodb_flush_logs_at_trx_commit is InnoDB 1000 times times slower than MyISAM and head big? You may have forgotten to modify this parameter. The default value is 1, which means that each committed update transaction (or statements outside of each transaction) is flushed to disk, which is quite resource intensive, especially when there is no battery backup cache. Many applications, especially those transformed from MyISAM, set its value to 2, which is to not flush the log to disk, but only to the operating system's cache. Logs are still flushed to disk every second, so there is usually no loss of 1-2 updates per second. If you set it to 0, it's a lot faster, but it's relatively unsafe. When the MySQL server crashes, some transactions are lost. Set to 2 command to lose the part of the transaction that was flushed to the operating system cache.


table_cache– the cost of opening a table can be significant. For example MyISAM the Myi file header flag that the table is in use. You certainly don't want this to happen too often, so it's often necessary to increase the number of caches to maximize the cache of open tables. It needs to use the operating system's resources and memory, which is certainly not a problem for the current hardware configuration. If you have more than 200 tables, it might be appropriate to set it to 1024 (each thread needs to open the table) and increase its value if the number of connections is larger. I've seen cases set to 100,000.


The cost of creating and destroying thread_cache– threads can be significant, as each thread is required to connect/disconnect. I usually set it at least to 16. If there is a large number of hops concurrent connections in the application and the value of threads_created is larger, then I will increase its value. It is intended to create no new threads in the usual operation.


query_cache– This is useful if your application has a lot of read and no application-level caching. Do not set it too big, because it also requires a lot of overhead to maintain it, which can cause MySQL to become slower. Typically set to 32-512MB. It's a good idea to track a period of time to see if it's working well. Under a certain load pressure, if the cache hit rate is too low, enable it.

Excerpt from-----csdn (pick up the stars from the sky)

InnoDB and MyISAM differences