The difference and choice between MyISAM and InnoDB, detailed summary, performance comparison

1. MyISAM: The default table type, which is based on the traditional ISAM type, ISAM is an abbreviation for indexed sequential access method (indexed sequential access methods), which is the standard way to store records and files. It is not transaction-safe, and foreign keys are not supported, and if a large number of Select,insert MyISAM are performed, the comparison is appropriate.

2, InnoDB: Support the engine of transaction security, support foreign key, row lock, transaction is his biggest characteristic. If you have a large number of update and insert, it is recommended to use InnoDB, especially for multiple concurrency and high QPS scenarios.

One, table lock differences

MyISAM:

MyISAM only supports table-level locks, and when the user operates the MyISAM table, the Select,update,delete,insert statement automatically locks the table, and if the locking table satisfies insert concurrency, new data can be inserted at the end of the table. You can also lock the table by using the Lock Table command, which basically mimics the transaction, but consumes very much and is typically used only in experimental demonstrations.

InnoDB:

InnoDB supports transactional and row-level locks, and is the most characteristic of InnoDB.

ACID properties of the transaction: Atomicity,consistent,isolation,durable.

Several problems caused by concurrent transactions: Update lost, Dirty read, non-repeatable read, Phantom read.

Transaction ISOLATION LEVEL: uncommitted read (READ UNCOMMITTED), Read committed (read), REPEATABLE read (Repeatable read), Serializable (Serializable)

Comparison of four isolation levels

Read data consistency and concurrency side effects Isolation level	Read Data consistency	Dirty Read	Non-REPEATABLE READ	Phantom reading
READ UNCOMMITTED for submission	Lowest level, no reading of physically smooth data	Is	Is	Is
Read Committed	Statement-level	Whether	Is	Is
REPEATABLE READ (repeatable red)	Transaction-level	Whether	Whether	Is
Serializable (Serializable)	Highest level, transaction level	Whether	Whether	Whether

View MySQL's default transaction isolation LEVEL "show global variables like ' tx_isolation '; ”

InnoDB's Row lock mode has the following types: Shared lock, exclusive lock, Intent shared lock (table lock), intent exclusive lock (table lock), Gap lock.

NOTE: When the statement does not use an index, INNODB cannot determine the line of action, and this time uses the intent lock, which is the table lock

About Deadlocks:

What is a deadlock? When two transactions need to obtain an exclusive lock held by the other party to complete the transaction, this results in a cyclic lock wait, which is the common deadlock type.

Ways to resolve deadlocks:

1. Database parameters

2, the application as far as possible to agree that the program read the Order of the table

3, when processing a table in the application, try to sort the order of processing

4, adjust the transaction isolation level (avoid two transactions at the same time a row of non-existent data, deadlock is prone to occur)

Second, the database file differences

MyISAM:

MyISAM belongs to the heap table

MyISAM has three files on disk storage, each file name begins with a table name, and the extension indicates the file type.

. frm is used to store the definition of a table

. MYD for storing data

. MYI for storing table indexes

The MyISAM table also supports three different storage formats:

Static table (default, but note that there can be no space at the end of the data, it will be removed)

Dynamic tables

Compression table

InnoDB:

InnoDB belong to index organization table

InnoDB has two storage methods, shared tablespace storage and multi-table space storage

The table structure of the two storage methods, like MyISAM, begins with the table name and the extension is. frm.

If a shared tablespace is used, all data and index files for all tables are saved in a table space, and a tablespace can have multiple files, with the Innodb_data_file_path and Innodb_data_home_dir parameters setting the location and name of the shared table space. The name of the general shared tablespace is called Ibdata1-n.

If you use multiple table spaces, each table has a tablespace file that stores the data and indexes for each table, with the file name beginning with the table name and. ibd as the extension.

Third, the index difference

1, about automatic growth

The auto-grow column of the MyISAM engine must be an index, and if it is a composite index, autogrow may not be the first column, and he can be incremented based on the previous columns.

The auto-growth of the InnoDB engine must be an index, and if it is a combined index, it must be the first column of the combined index.

2, about the primary key

MyISAM allows tables without any indexes and primary keys to exist,

The MyISAM index is the address where the row is saved.

InnoDB engine if no primary key or non-null unique index is set, a 6-byte primary key is automatically generated (the user is not visible)

The InnoDB data is part of the primary index, and the attached index holds the value of the primary index.

3. About the count () function

MyISAM the total number of rows that have a table, if select COUNT (*) from table, the value is taken out directly

InnoDB does not save the total number of rows in the table, and if you use SELECT COUNT (*) from table, the entire table is traversed, consuming considerable amounts, but the MyISAM and InnoDB are handled the same way when the wehre condition is added.

4. Full-Text Indexing

MyISAM supports full-text indexing of fulltext types

InnoDB does not support full-text indexing of fulltext types, but InnoDB can support full-text indexing using Sphinx plug-ins, and it works better. (Sphinx is an open source software that provides API interfaces in multiple languages that can optimize MySQL's various queries)

5. Delete from table

With this command, InnoDB does not create a new table, but rather a single piece of deleted data, and on InnoDB it is best not to use this command if you want to empty a table that holds a large amount of data. (Truncate TABLE is recommended, but the user has permission to drop this table)

6. Index Save location

The index of the MyISAM is in table name +. Myi files are saved separately.

InnoDB's index and data are stored in the tablespace.

Iv. Considerations for Development

1, you can use the Show CREATE TABLE tablename command to see the engine type of the table.

2. Doing Start/commit operations on tables that do not support transactions has no effect and has been committed before committing a commit.

3. You can execute the following command to switch non-transactional tables to transactions (data is not lost), the InnoDB table is more secure than the MyISAM table: ALTER TABLE TableName TYPE=INNODB, or use ALTER TABLE tablename engine = InnoDB

4, the default InnoDB is to turn on auto-commit, if you follow the MyISAM method to write code page there is no error, but performance will be very low. How do you improve database performance when writing code?

A, try to bind more than one statement to a transaction, commit, avoid the database overhead caused by multiple commits.

b, after a transaction acquires an exclusive lock or an intent exclusive lock, if there is a SQL statement that needs to be processed, the program should do as few logical operations and processing as possible between the two or more SQL statements, reducing the lock time.

C, try to avoid deadlocks

D, SQL statement if there is a WHERE clause must use the index, try to avoid obtaining intent exclusive lock.

F, for our own database environment, the log system is directly inserted, not modified, so we use the hybrid engine approach, zion_log_db still use MyISAM storage engine, only zion_game_db,zion_login_db,daum_ Billing uses the InnoDB engine.

Five, exactly how to choose

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?

MyISAM only Index cache
InnoDB index file data file InnoDB buffer
MyISAM can only manage indexes, which are used by the operating system to cache when index data is greater than allocated resources, and data files depend on the operating system's cache. InnoDB, whether indexed or data, is managed by itself

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.

The 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.

For tables with InnoDB types that support transactions, the main reason for the speed is that the AUTOCOMMIT default setting is open, and the program does not explicitly call begin to start a transaction, resulting in an automatic commit for each insert, which seriously affects the speed. You can call begin before you execute SQL, and multiple SQL forms a transaction (even if autocommit is open), which greatly improves performance.

InnoDB

InnoDB provides MySQL with transaction security with transactional (commit), rollback (rollback), and crash-repair capabilities (crash recovery capabilities) (Transaction-safe (ACID compliant )) Type table. The InnoDB provides a row lock (locking on row level) that provides an unlocked read (non-locking read in selects) that is consistent with the Oracle type. These features improve the performance of multi-user concurrency operations. There is no need to widen the lock (lock escalation) in the InnoDB table because the InnoDB column lock (Row level locks) is suitable for very small space. InnoDB is the first table engine on MySQL to provide a foreign key constraint (FOREIGN key constraints).

InnoDB's design goal is to handle a large-capacity database system, which is not comparable to other disk-based relational database engines. Technically, InnoDB is a complete database system placed in the background of MySQL, InnoDB in the main memory to establish its dedicated buffer pool for caching data and indexes. InnoDB the data and index in the table space, may contain multiple files, which is different from other, for example, in MyISAM, the table is stored in a separate file. The size of the InnoDB table is limited only by the size of the operating system file, typically 2 GB.
InnoDB All tables are stored in the same data file Ibdata1 (or possibly multiple files, or stand-alone tablespace files), relatively poorly backed up, the free scheme can be copy data files, backup Binlog, or mysqldump.

MyISAM
MyISAM is the MySQL default storage engine.
Each of the MyISAM tables is stored in three files. The frm file holds the table definition. The data file is MyD (MYData). The index file is an MYI (myindex) extension.
Because MyISAM is relatively simple, it is better than innodb in efficiency. Using MyISAM for small applications is a good choice.
MyISAM tables are saved as files, and using MyISAM storage in cross-platform data transfer saves a lot of hassle

The following are some of the details and the specific implementation 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.

In addition, the row lock of the InnoDB table is not absolute, and if MySQL cannot determine the range 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 "%aaa%"

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.

Six, repeat to summarize again

1, MyISAM does not support transactions, InnoDB is a transaction type of storage engine, when our table needs to use the transaction support, it must not choose MyISAM.

2, MyISAM only support table-level lock, BDB support page-level lock and table-level lock default to page-level lock, and INNODB support row-level lock and table-level lock default to row-level lock

Table-level Lock: locks the entire table directly, during lock-up, other processes cannot write to the table, and if a write lock is set, the other process is not allowed to read

MyISAM is a table-level locked storage engine that does not have a deadlock problem

For write, the table locking principle is as follows:

If there is no lock on the table, place a write lock on it, otherwise, place the lock request in the write lock queue.

For read, the table locking principle is as follows:

If there is no write lock on the table, place a read lock on top of it, or place the lock request in the Read lock queue

When a lock is released, the table can be obtained by a thread in the write lock queue, and then the thread in the lock queue is read. This means that if you have a lot of updates on a table, then your SELECT statement waits until all the write lock threads have finished executing.

Row-level locks: locks only on the specified row, and other processes can operate on other rows in the table.

Row-level locks are the least granular type of MySQL lock, which can greatly reduce the conflict of database operations, but the smaller the granularity, the greater the cost of implementation.

Row-level locks can cause "deadlocks", and that is how it is caused, the reason for the analysis: MySQL row-level locks are not direct lock records, but lock index. Indexes are primary key index and non-primary key index Two, if a SQL statement operation of the primary key index, then MySQL will lock the primary key index, if the SQL statement operation is non-primary key index, then MySQL will first lock the non-primary key index, then lock the primary key index.

In the update and delete operations, MySQL not only locks all the where conditions scanned indexes, but also locks adjacent key values.

"Deadlock" Example analysis:

Table test: (id,state,time) primary key index: ID non-primary key index: state

The state index is locked when the "UPDATE State =1011 WHERE state=1000" statement is executed, so MySQL also requests the Lock ID index because the state is not a primary key index

When another SQL statement is executed almost simultaneously with statement 1: "UPDATE state=1010 WHERE id=1" for statement 2 MySQL locks the ID index first, and because statement 2 operates the State field, MySQL also requests that the state index be locked. Then. Each other is locked in the desired index, and is waiting for each other to release the lock. So there's a "deadlock" situation.

Advantages of row-level locks:

There are many threads that have access to different rows, and there are only a few conflicts.

There are only a few changes when rolling back

A single row can be locked for a long time

Row-level Lock disadvantages:

More memory is consumed relative to page-level and table-level locks

When most rows of a table are in use, it is slower than page-level and table-level locks because you have to get more locks

When a group by operation is frequently used on most data, it is certainly slower than table-level and page-level locks.

Page-level Locks: table-level locks are fast, but conflict is high; row-level locks are slow, but conflict is low; page-level locks are the two of them, locking together a contiguous set of records at a time.

3, MyISAM engine does not support foreign keys, INNODB support foreign keys

4, the MyISAM engine table in a large number of high concurrent read and write will often appear table corruption situation

Our previous projects encountered this problem, the table insert and update operations are very frequent, the original MyISAM engine, resulting in the table go out damage, and later replaced by the InnoDB engine.

Other causes of table corruption are:

Damage to data files due to abrupt server power outage, forced shutdown (mysqld not closed) causes table corruption

The mysqld process was killed during the write operation.

Disk failure

Table Damage Common symptoms:

Query tables cannot return data or return partial data

Failed to open table: Can ' t open file: ' xxx. MYI ' (errno:145).

Error:table ' P ' is marked as crashed and should be repaired.

Incorrect key file for table: ' ... '. Try to repair it

MySQL Table recovery:

For recovery of MyISAM tables:

You can use the Myisamchk tool that comes with MySQL: myisamchk-r tablename or Myisamchk-o tablename (more insured than the previous) to fix the table

5. MyISAM is more advantageous for count () query

Because MyISAM stores the number of rows in a table, the results can be obtained directly when the select count () is executed, and InnoDB needs to scan all the data to get the results.

But note that the table execution process for the SELECT COUNT () statement with the Where condition is the same for both engines, and all the data needs to be scanned for results

6. InnoDB is designed for maximum performance when dealing with huge amounts of data, and its CPU efficiency can be unmatched by any other disk-based relational database engine.

7. MyISAM supports full-text indexing (fulltext), InnoDB does not support

8, MyISAM engine table query, UPDATE, insert efficiency is higher than innodb

The online interception of the predecessors Test conclusion:

Test method: Submit 10 consecutive query, table records total: 380,000, time unit s

Engine Type MyISAM InnoDB performance difference

Count 0.0008357 3.0163 3609

Query primary key 0.005708 0.1574 27.57

Querying for non-primary keys 24.01 80.37 3.348

Update primary key 0.008124 0.8183 100.7

Update non-primary key 0.004141 0.02625 6.338

Insertion 0.004188 0.3694 88.21

(1) After the index, for the MyISAM query can be accelerated: 4 206.09733 times times, InnoDB query 510.72921 times times faster, while the MyISAM update speed slowed down to the original 1/2,innodb more
The new speed slows down to the original 1/30. To see if the situation determines whether to index, such as the log table without querying, do not do any index.

(2) If your data volume is millions, and there is no transaction processing, then using MyISAM is the best choice for performance.

(3) The size of the InnoDB table is more large, with MyISAM can save a lot of hard disk space.

In this 38w table we tested, the table takes up space as follows:
Engine Type MyISAM InnoDB
Data 53,924 KB 58,976 KB
Index 13,640 KB 21,072 KB
Total space occupied 67,564 KB 80,048 KB

Another 176W record table, where the table occupies space, is as follows:

Engine Type MyIsam Innordb
Data 56,166 KB 90,736 KB
Index 67,103 KB 88,848 KB
Total space occupied 123,269 KB 179,584 KB

Seven, performance comparison

The beta version is MySQL Ver 14.14 distrib 5.1.49, for Debian-linux-gnu (i686), using InnoDB plugin 1.0.8 (officially known as better than built-in version performance) and the default MyISAM.

The test machine is a notebook, configured as follows: Intel Core 2 Duo P8600,2g*2 DDR3 1066 Ram, 320G HDD 5400 rpm.

Test one: Data insertion performance test, here I separately on the Innodb_flush_log_at_trx_commit parameter open and close are measured, each test is run 40s, the number of the table is the actual number of inserted bars.

MyISAM Innodb (Open) Innodb (off)

Single thread, insert 120000 60000 60000

4 threads, inserting 40000*4 20000*4 40000*4

Single thread, batch 100/time insert 3600*100 800*100 3000*100

Single thread, batch 200/time insert 1800*200 400*200 1600*200

You can see that bulk insert performance is much higher than a single insert, but the size of one batch has little effect on performance. The parameters of whether each record refreshes the log have a significant impact on InnoDB performance. Overall, MyISAM performance is a bit more superior. One thing to note here is that during the insertion test, I monitored the system resources and found that MyISAM is very low on system resources, but InnoDB is very high on disk, and should be a lot more logs to log for transaction control.

Test two: Data read performance test. Reads 1000 records at random and reads them repeatedly.

MyISAM Innodb

Single thread, 200 reads 5.7s 16.7s

4 threads, 200 reads 12s 40.8s

It can be seen that the read performance of MyISAM is very scary, the performance gap is 3 times times the appearance.

The above two tests found that MyISAM almost outright win-win without the need for business, but to know that it is a table lock, InnoDB is a row lock, then the concurrent read and write simultaneous existence, the result will be what?!

Test three: Two threads concurrent write, 2 threads concurrently read.

MyISAM Innodb

Insert Write 40s:10000*2 One at a time read 200 *2:14s write 40s:60000*2 read 200 times *2:50s

Batch 100/Time Insert write 40s:1000*100*2 read 200 *2:10s write 40s:1500*100*2 read 200 times *2:50s

This immediately shows the InnoDB's strong performance in concurrency, with little performance degradation. The MyISAM single insertion speed has become very slow, and bulk insertion has also dropped 40% performance.

Summing up, in the application of writing more read less or InnoDB insert performance more stable, in the case of concurrency can also be basic, if the reading speed requires a relatively fast application or select MyISAM.

Transferred from: http://blog.csdn.net/wjtlht928/article/details/46641865

The difference and choice between MyISAM and InnoDB, detailed summary, performance comparison