How to choose between MyISAM and InnoDB

1. MyISAM does not support transactions. InnoDB is a transaction-type storage engine. When our tables need transaction support, MyISAM cannot be selected. 2. MyISAM only supports

1. MyISAM does not support transactions. InnoDB is a transaction-type storage engine. When our tables need transaction support, MyISAM cannot be selected. 2. MyISAM only supports

1. MyISAM does not support transactions. InnoDB is a storage engine for transaction types.

When transaction support is required for our tables, MyISAM cannot be selected.

2. MyISAM only supports table-level locks. BDB supports page-level locks and table-level locks. InnoDB supports row-level locks and table-level locks. Row-level locks are used by default.

Table-Level Lock: the entire table is locked directly. During the lock period, other processes cannot perform write operations on the table. If the write lock is set, other processes cannot read the table.

MyISAM is a table-level locking storage engine, which does not cause deadlock.

For write, the table locking principle is as follows:

If there is no lock on the table, place a write lock on it. Otherwise, put 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, put a read lock on it; otherwise, put the lock request in the read lock queue.

When a lock is released, the table can be written to lock the threads in the queue, and then read to lock the threads in the queue. This means that if you have many updates on a table, your SELECT statement will wait until all the write lock threads have finished executing.

Row-Level Lock: only the specified row is locked. Other processes can still operate on other rows in the table.

Row-level locks are the smallest type of locks in Mysql. They can greatly reduce database operation conflicts, but the smaller the granularity, the larger the implementation cost.

Row-level locks may lead to "deadlocks". How did they cause them? Cause: Mysql row-level locks do not directly lock records, but lock indexes. Indexes are classified into primary key indexes and non-primary key indexes. If an SQL statement operates on a primary key index, Mysql locks the primary key index. If an SQL statement operates on a non-primary key index, mysql will first lock this non-primary key index and then lock the primary key index.

During the UPDATE and DELETE operations, Mysql not only locks all the indexes that have been scanned by the WHERE condition, but also locks adjacent key values.

"Deadlock" Example Analysis:

Table Test :( ID, STATE, TIME) primary key index: ID non-primary key index: STATE

When the "update state = 1011 where state = 1000" statement is executed, the STATE index is locked. Because the STATE is a non-primary key index, Mysql also requests to lock the ID index.

When another SQL statement is executed almost simultaneously with statement 1: "UPDATE STATE = 1010 WHERE ID = 1", Mysql First locks the ID index for Statement 2 because Statement 2 operates the STATE field, mysql also requests to lock the STATE index. At this time. Lock each other's required indexes, and wait for the other to release the lock. So there is a deadlock.

Advantages of Row-Level Lock:

When many threads access different rows, there are only a small number of conflicts.

Only a few changes are allowed during rollback.

Can lock a single row for a long time

Row-Level Lock disadvantages:

It occupies more memory than page-level locks and table-level locks.

When most of the rows in a table are used, it is slower than page-level locks and table-level locks, because you must obtain more locks.

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

Page-level locks: Table-level locks are fast, but there are many conflicts. Row-level locks are slow, but there are few conflicts. Page-level locks are the ones they fold, locking adjacent records at a time.

3. MyISAM engine does not support foreign keys. InnoDB supports foreign keys.

4. Tables of MyISAM engine are often damaged when they are read and written in a large number of concurrent operations.

The current project encountered this problem. The INSERT and UPDATE operations on the table were frequent. The MyISAM engine was used, resulting in damage to the table every three minutes. Later, it was replaced with the InnoDB engine.

Other causes that may cause Table Corruption:

The data file is damaged due to a sudden power failure on the server, and the table is damaged due to forced shutdown (mysqld is not closed ).

The mysqld process is killed during write operations.

Disk fault

Table damage common symptoms:

The query table cannot return data or return some data.

Failed to open the table: Can't open file: '×××. myi' (errno: 145 ).

Error: Table 'P' is marked as crashed and shoshould be retried red.

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

Mysql table Restoration:

For MyISAM Table Restoration:

You can use the myisamchk tool that comes with Mysql: myisamchk-r tablename or myisamchk-o tablename (more secure than the previous one) to fix the table.

5. MyISAM is more advantageous for count () queries.

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

However, note that the execution process of the select count () statement with the WHERE condition is the same for the two engines. You must scan all the data to obtain the result.

6. InnoDB is designed for the maximum performance when processing a large amount of data. Its CPU efficiency may be unmatched by any other disk-based relational database engine.

7. MyISAM supports full-text indexing (FULLTEXT). InnoDB does not.

8. The efficiency of querying, updating, and inserting tables in the MyISAM engine is higher than that in InnoDB.

I did not perform a detailed test, so I intercepted the previous test conclusion on the Internet:

All performance tests are performed on: Micrisoft window xp sp2, Intel (R) Pentinum (R) M processor 1.6 oGHz 1 GB memory computer.

Test method: 10 queries are submitted consecutively. Total table records: 0.38 million, in seconds.

Engine type MyISAM InnoDB performance difference

Count 0.0008357 3.0163 3609

Query primary keys 0.005708 0.1574 27.57

Query non-primary key 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

Insert 0.004188 0.3694 88.21