MySQL性能调优——锁定机制与锁优化分析详解数据库

针对多线程的并发访问，任何一个数据库都有其锁定机制，它的优劣直接关系着数据的一致完整性与数据库系统的高并发处理性能。锁定机制也因此成了各种数据库的核心技术之一。不同数据库存储引擎的锁定机制是不同的，本文将从MySQL最常见的存储引擎MyISAM与InnoDB的锁定机制说起。

一、MyISAM的锁机制——表级锁定

MySQL表级锁定的常见类型主要分为两种，一种是读锁，一种是写锁。
谁持有读锁？谁持有写锁？谁在等待读锁资源？谁在等待写锁资源？数据库系统都是要记录的。MySQL中，主要通过如下4个队列来保存相关信息：

读锁持有队列：Current read-lock queue(lock->read)——存放所有正在锁定的读锁信息
写锁持有队列：Current write-lock queue(lock->write)——存放所有正在锁定的写锁信息
读锁等待队列：Pending read-lock queue(lock->read_wait)——存放所有等待对资源加读锁的线程信息
写锁等待队列：Pending write-lock queue(lock->write_wait)——存放所有等待对资源加写锁的线程信息

为保证数据一致完整性，多线程可以为同一份资源加多个读锁，而同一份资源只能加一个写锁，读锁与写锁也不能同时加在一份资源上。

1、读锁定

客户端请求获取读锁定资源时，如果满足如下两个要求，则请求通过，进入读锁持有队列；否则，请求失败，进入读锁等待队列。
（1）请求锁定的资源当前没有写锁定；
（2）写锁等待队列中没有优先级更高的写锁定在等待。

2、写锁定

客户端请求获取写锁定的时候：
（1）先通过写锁持有队列检查这份资源是否已经被加上写锁定，如果有，自然暂停自身线程进入写锁等待队列等待，如果没有，进行第（2）步
（2）检查写锁等待队列中是否有线程同样在等待获取这份资源的写锁定，如果有，则进入写锁等待队列等待，如果没有，进行第（3）步
（3）通过读锁持有队列检查这份资源是否已经被加上读锁定，如果有，则进行写锁等待队列等待，如果没有，可以获取写锁定，进入写锁持有队列中

请注意：对于MySQL使用者，展现出来的锁定类型只有读锁定与写锁定两种，但实际上，MySQL内部实现中却有11种枚举出来的锁定类型，因为表面与实现的差异，上述请求过程会有特例，在此不再赘述，如想深入了解，可参看简朝阳《MySQL性能调优与架构设计》。

那我们说，MyISAM在对表的操作上只能是串行处理，不能并行操作吗？并不是，MyISAM有一个很重要的机制就是并发插入（Concurrent Insert）特性，我们在下面第三部分MyISAM表级锁定优化建议再详细介绍。

二、InnoDB的锁机制——行级锁定

不光InnoDB存储引擎，MySQL的分布式存储引擎NDB Cluster都使用行级锁定。InnoDB的行级锁定同样分为两种，一种是共享锁，一种是排它锁。

1、当一个事务需要给某份资源加锁的时候，主要情况有如下

（1）如果遇到一个共享锁正锁定着资源，那么事务只能再加上一个共享锁，而不能加排它锁。
（2）如果遇到一个排他锁正锁定着资源，那么事务只能等待该锁定释放资源后他才能获得资源并添加自己的锁定。

2、InnoDB锁机制的实现与弊端

InnoDB锁机制是基于索引实现的，通过在指向数据记录的第一个索引键之前与最后一个索引键之后的空域空间（间隙或着说是范围）标记锁定信息实现，被称为间隙锁。
间隙锁的弊端：会在执行范围查询时，对范围内所有键值加锁，即使键值不存在，这会造成在加锁后无法插入锁定键值范围内的任何数据，影响性能。比如：

SELECT *  
FROM user  
WHERE user_id BETWEEM 1 AND 100 

执行这个查询时，会对1-100范围内所有索引键值（1-100）加间隙锁，即使并不存在user_id为10的用户信息，所以在加锁后，要想插入一条user_id为10的用户信息是不可行的，这对于行级锁来说并不符合常理。InnoDB给出的解释是：为了防止幻读的出现。
当没有索引时或无法利用索引时，InnoDB会弃用行级锁，改用表级锁，并发处理性能降低。
另外，因为InnoDB的行级锁与事务处理特性，一定会产生死锁现象，对于如何降低死锁产生概率，我在第四部分InnoDB行级锁定优化建议中详述。

三、MyISAM表级锁定优化建议

因为表级锁的锁定颗粒较大，其实现难度复杂性行都降低了，成本自然降低，但是付出了高并发处理性能较低的代价，所以表级锁的优化就从如何提高并发处理性能说起。

1、缩短锁定时间

（1）降低查询复杂度，将复杂的查询划分成几个简单的查询分步进行。
（2）建立合适的索引加快查询效率。
（3）优化表结构，只存放必要的信息，且控制字段类型与字段长度（等长最优）。

2、利用MyISAM并发插入特性（Concurrent Insert）,通过设置concurrent_insert参数实现

（1）concurrent_insert=2，无论MyISAM表数据文件的中间部分是否有因为删除数据留下的空闲空间，都允许在数据文件尾部进行并发插入。
（2）concurrent_insert=1，当MyISAM表数据文件中间不存在空闲空间时，才允许在数据文件尾部进行并发插入。
（3）concurrent_insert=0，无论MyISAM表数据文件的中间部分是否有因为删除数据留下的空闲空间，都不允许在数据文件尾部进行并发插入。
如果数据被删除的可能性比较小，而且对暂时性浪费并不在乎的话，可以尝试把concurrent_insert设置为2；但当删除量不是很小，查询时需要读取更多的空域空间时，推荐设置为1。

3、合理利用读写优先级

默认情况下，写优先级要高于读优先级。
（1）当数据库系统以读为主，要优先保证查询性能时，可通过low_priority_updates=1设置读优先级高于写优先级。
（2）当数据库系统需要保证写入性能，则不用设置low_priority_updates参数。

四、InnoDB行级锁定优化建议

InnoDB的行级锁最大的优势就是增强了高并发的处理能力，缺点就是复杂性较高、易死锁，且基于索引实现有一定弊端。我们要做的就是扬长避短，合理利用InnoDB行级锁定，为此我们就应该做的：
1、尽可能让所有的数据检索都通过索引实现，因为InnoDB行级锁是基于索引实现的，没有索引或无法使用索引系统会改为使用表级锁。
2、合理设计索引，以缩小加锁范围，避免“间隙锁”造成不该锁定的键值被锁定。
3、尽量控制事务的大小，因为行级锁的复杂性会加大资源量以及锁定时间。
4、使用较低级别的事务隔离，以减少因实现事务隔离而付出的成本。
5、避免死锁，可以通过如下方式实现：
（1）类似的业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁。
（2）同一个事务中，尽量做到一次性锁定需要的所有资源。
（3）对于易产生死锁的业务部分，增大处理颗粒度，升级为表级锁以降低死锁产生的概率。