MyISAM vs InnoDB 分析之一

需要理解MySQL中关于锁的一些概念，参考http://dev.mysql.com/doc/refman/5.1/en/locking-issues.html。

“MySQL manages contention for table contents using locking......”

这里contention应该是争端的意思。

内部锁机制，内部锁是由MySQL自己来处理为解决和管理争端。

内部锁和外部锁的区别：

“ This type of locking is internal because it is performed entirely by the server and involves no other programs. External locking occurs when the server and other programs lockMyISAM table files to coordinate among themselves which program can access the tables at which time.”

coordinate的名字意思是坐标，动词的意思，我本以为是定位（坐标），但有道词典上的意思是“vt 调整；整合 vi协调”，而且它的解释互相矛盾，及物的时候也解释做“协调”。

“MySQL uses row-level locking for InnoDB tables to support simultaneous write access by multiple sessions, making them suitable for multi-user, highly concurrent, and OLTP applications. MySQL uses table-level locking forMyISAM, MEMORY, and MERGE tables, allowing only one session to update those tables at a time, making them more suitable for read-only, read-mostly, or single-user applications.”

InnoDB使用行级锁，所以它适合多用户，高并发。

MyISAM表级锁，适合只读，多数读，但用户应用（这里的用户概念是偏向于单进程的意思）。

“MySQL grants table write locks as follows:

1. If there are no locks on the table, put a write lock on it.

2. Otherwise, put the lock request in the write lock queue.

MySQL grants table read locks as follows:

1. If there are no write locks on the table, put a read lock on it.

2. Otherwise, put the lock request in the read lock queue.

”

这点和《High Performance MySQL》（以下简称《High》）说的有些区别了，以下是摘抄自《High》第二版：

”Table locks

The most basic locking strategy available in MySQL, and the one with the lowest

overhead, is table locks. A table lock is analogous to the mailbox locks described ear-

lier: it locks the entire table. When a client wishes to write to a table (insert, delete,

update, etc.), it acquires a write lock. This keeps all other read and write operations

at bay. When nobody is writing, readers can obtain read locks, which don’t conflict

with other read locks.

Table locks have variations for good performance in specific situations. For exam-

ple, READ LOCAL table locks allow some types of concurrent write operations. Write

locks also have a higher priority than read locks, so a request for a write lock will

advance to the front of the lock queue even if readers are already in the queue (write

locks can advance past read locks in the queue, but read locks cannot advance past

write locks).

Although storage engines can manage their own locks, MySQL itself also uses a vari-

ety of locks that are effectively table-level for various purposes. For instance, the

server uses a table-level lock for statements such as ALTER TABLE , regardless of the

storage engine.“

这里只提到一个队列，其实应该是两个队列。

回到官网，“Table updates are given higher priority than table retrievals. Therefore, when a lock is released, the lock is made available to the requests in the write lock queue and then to the requests in the read lock queue. This ensures that updates to a table are not “starved” even if there is heavy SELECT activity for the table. However, if you have many updates for a table, SELECT statements wait until there are no more updates.”--在一个锁释放之后，会优先考虑写队锁队列，然后考虑读锁队列，总结就是表更新相较于表获取（读取数据）会具有更高的优先级。

MyISAM引擎支持并发插入，如果文件中间没有空洞的话，则插入和查询是可以并发的。

"The MyISAM storage engine supports concurrent inserts to reduce contention between readers and writers for a given table: If a MyISAM table has no free blocks in the middle of the data file, rows are always inserted at the end of the data file. In this case, you can freely mix concurrent INSERT and SELECT statements for a MyISAM table without locks. That is, you can insert rows into a MyISAM table at the same time other clients are reading from it. Holes can result from rows having been deleted from or updated in the middle of the table. If there are holes, concurrent inserts are disabled but are enabled again automatically when all holes have been filled with new data. This behavior is altered by the concurrent_insert system variable. See Section 8.11.3, “Concurrent Inserts”."

Considerations for Row Lockin

Advantages of row-level locking:

• Fewer lock conflicts when different sessions access different rows

• Fewer changes for rollbacks

• Possible to lock a single row for a long time

Disadvantages of row-level locking:

• Requires more memory than table-level locks

• Slower than table-level locks when used on a large part of the table because you must acquire many more locks

• Slower than other locks if you often do GROUP BY operations on a large part of the data or if you must scan the entire table frequently

Choosing the Type of Locking

Generally, table locks are superior to row-level locks in the following cases:

• Most statements for the table are reads

• Statements for the table are a mix of reads and writes, where writes are updates or deletes for a single row that can be fetched with one key read:

  UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
  DELETE FROM tbl_name WHERE unique_key_col=key_value;
  

• SELECT combined with concurrent INSERT statements, and very few UPDATE or DELETE statements

• Many scans or GROUP BY operations on the entire table without any writers

With higher-level locks, you can more easily tune applications by supporting locks of different types, because the lock overhead is less than for row-level locks.

Options other than row-level locking:

• Versioning (such as that used in MySQL for concurrent inserts) where it is possible to have one writer at the same time as many readers. This means that the database or table supports different views for the data depending on when access begins. Other common terms for this are “time travel,” “copy on write,” or “copy on demand.”

• Copy on demand is in many cases superior to row-level locking. However, in the worst case, it can use much more memory than using normal locks.

• Instead of using row-level locks, you can employ application-level locks, such as those provided by GET_LOCK()and RELEASE_LOCK() in MySQL. These are advisory locks, so they work only with applications that cooperate with each other. See Section 12.16, “Miscellaneous Functions”.

  
  

For large tables, table locking is often better than row locking, but there are some disadvantages:

• Table locking enables many sessions to read from a table at the same time, but if a session wants to write to a table, it must first get exclusive access. During the update, all other sessions that want to access this particular table must wait until the update is done.

• Table locking causes problems in cases such as when a session is waiting because the disk is full and free space needs to become available before the session can proceed. In this case, all sessions that want to access the problem table are also put in a waiting state until more disk space is made available.

总结一下：

MyISAM是表级锁，单就锁而言，速度快，但是在有并发发生时，锁住整个表，效率就不高了。

锁分两种，读锁和写锁，存在两个队列，请求读锁的队列和请求写锁的队列，而更新的优先级默认要高于获取数据的优先级，所以在同等条件下，更新的写锁会先获得处理，而获取数据的请求只能先等待。

读锁是共享锁，可以支持多人一起读；而写锁是排它锁，不支持大家一起处理，别人既不能写，也不能读（这点在后来MVCC得到了处理）。

InnoDB是行级锁，只锁一行，但是单就锁而言，需要更大的开销，但是在多用户、高并发并且需要写动作多的时候，效率会高很多。

InnoDB使用行级锁，所以它适合多用户，高并发。

MyISAM表级锁，适合只读，多数读，单用户应用（这里的用户概念是偏向于单进程的意思）。