十二、精通MySQL存储引擎

如何保证数据并发访问的一致性和有效性是所有数据库必须解决的一个问题。另外，锁冲突也是影响数据库并发性能的一个重要因素。应用程序在选择锁类型时，需要根据实际运行的需要选择最佳的锁类型。本章主要从MySQL的逻辑组成和数据库存储引擎方面介绍MySQL的整体架构，讨论MySQL锁机制的特点，以及解决MySQL锁问题的一些方法或建议，让读者能从整体上把握MySQL，理解MySQL的各个逻辑层是如何工作的。

一、MySQL架构

MySQL服务器由SQL层和存储引擎层构成。SQL层的主要功能包括权限判断、SQL解析功能和查询缓存处理等。存储引擎层完成数据库数据存储操作。MySQL整体架构的SQL层和存储引擎层实际上各自都包含了很多小模块。各个模块的工作方式如图所示。

从上图可以看出以下3点。
（1）客户端通过连接/线程处理层来连接MySQL数据库。连接线程处理层主要用来处理客户端的请求、身份验证和数据库安全验证等。
（2）查询缓存和查询分析器是SQL层的核心部分，其中主要涉及查询的解析、优化、缓存以及所有内置的函数、存储过程、触发器、视图等功能。
（3）优化器主要负责存储和获取所有存储在MySQL中的数据，可以把这3层统称为MySQL数据库的SQL层。

1、MySQL的物理文件的组成

MySQL的物理文件包括日志文件、数据文件和其他文件，下面将详细介绍这些文件的含义和作用。

1、日志文件

在MySQL数据库中，日志文件主要记录了数据库操作信息和错误信息。常用的日志文件包括错误日志、二进制日志、查询日志、慢查询日志和InnoDB引擎在线Redo日志等。
（1）错误日志：Error Log
错误日志文件记录了MySQL Server运行过程遇到的所有严重的错误信息，以及MySQL每次启动和关闭的详细信息。默认情况下，错误日志功能是关闭的，启动时要重新配置–log-error[=file_name]选项，修改错误日志存放的目录和文件名称。
（2）二进制日志：Binary Log
二进制日志文件就是常说的binlog。二进制日志记录了MySQL所有修改数据库的操作，然后以二进制的形式记录在日志文件中，其中还包括每条语句所执行的时间和所消耗的资源，以及相关的事务信息。
默认情况下，二进制日志功能是开启的，启动时可以重新配置log-bin[=file_name]选项，修改二进制日志存放的目录和文件名称。
（3）查询日志：Query Log
默认的查询日志文件是hostname.log。查询日志记录所有的查询操作，包括所有的select操作信息，体积比较大，开启后对性能有较大的影响，可以通过“–log[=file_name]”选项开启。如果需要跟踪某些特殊的SQL性能问题，可以短暂地打开该功能。
（4）慢查询日志：Slow Query Log
慢查询日志是指所有SQL执行地时间超过long_query_time变量地语句和达到min_examined_row_limit条件地语句。用户可以针对这部分语句进行性能调优。慢查询日志设置–log-show_querys[=file_name]选项开启后，将记录日志所在的路径和名称。MySQL系统默认的慢查询日志的文件名是hostname-slow.log,默认目录也是data目录。查看慢查询日志可以采用mysqldumpslow命令对慢查询日志进行分析。
（5）InnoDB引擎在线Redo日志：InnoDB redo log
InnoDB引擎在线Redo日志记录了InnoDB所做的所有物理变更和事务信息。通过Redo日志和Undo信息，InnoDB大大加强了事务的安全性。InnoDB在线Redo日志默认存放在data目录下面，可以通过设置innodb_log_group_home_dir选项来更改日志的存放位置、通过innodb_log_files_in_group选项来设置日志的数量。

2、数据文件

MySQL数据库会在data目录下面建立一个以数据库为名字的文件夹，用来存储数据库中的表文件数据。不同的数据库引擎，每个表的扩展名也不一样。例如MySQL引擎用“.MYD”作为扩展名，InnoDB引擎用“.ibd”，CSV使用“.csv”扩展名。
常见的数据文件如下：
（1）".frm"文件：无论是哪种存储引擎，创建表之后都会生成一个以表命名的“.frm”文件。frm文件主要存放与表相关的数据信息，主要包括表结构的定义信息。当数据库崩溃时，用户可以通过frm文件来恢复数据表结构。
（2）“.MYD”文件：MyISAM存储引擎创建表的时候，每一个MyISAM类型的表都会有一个“.MYD”文件与之对应。MYD文件主要用来存放数据表的数据文件。
（3）“.MYI”文件：每一个MyISAM类型的表都会有一个“.MYD”文件和一个“.MYI”文件，对于MyISAM存储引擎来说可以被缓存的内容主要源于".MYI"文件，用来存储表数据文件中任何索引的数据树。
（4）".ibd"文件和“.ibdata”文件：这两种文件主要是用来存储InnoDB存储引擎的数据，其中主要包括索引信息。InnoDB存储引擎采用这两种数据文件，主要是因为InnoDB存储引擎的存储方式能通过配置来决定是采用共享表空间还是独享表空间的存储方式存储数据。
如果采用共享表空间的方式存储数据，就会采用ibdata文件来存储，所有的表共同使用一个或者多个ibdata文件。如果采用独享表空间方式存储数据，就会采用ibd文件来存储。

3、其他文件

MySQL数据库系统除了日志文件、数据文件外，还包括其他一些文件，例如系统配置文件、pid文件、socket文件等。

MySQL系统配置文件一般都在“etc/my.cnf”中。pid文件类似于UNIX/Linux操作系统下面的进程文件，MySQL服务器的pid文件用来存放自己的进程ID。MySQL服务器启动后，socket文件自动生成，主要用来连接客户端。

MySQL各逻辑块简介

MySQL逻辑架构采用SQL层和存储引擎分离的方式，真正实现了数据存储和逻辑业务的分离。MySQL的SQL层从宏观上可以分为3层，事实上SQL层包含了很多子模块，下面就详细介绍SQL层各个子模块的功能。

1、初始化模块

初始化模块就是在数据库启动的时候对整个数据库做的一些初始化操作，例如各种系统环境变量的初始化、各种缓存、存储引擎初始化设置等。
在MySQL初始化过程中，部分系统参数是通过MySQL数据库系统文件设置的。MySQL系统参数可以通过“mysqld --verbose --help”命令来查看当前系统所有参数的设置。在Linux平台上，MySQL数据库读取文件首先会读取/etc/my.cnf（主要用来设置MySQL全局选项）。许多初学者在Linux平台上安装MySQL失败就是因为/etc/my.cnf的设置是系统默认的错误路径，可以将$MySQL_HOME/support_files/目录下面的配置文件复制到/etc/my.cnf中，命令如下：

cp  ./support_files/my_medium.cnf  /etc/my.cnf

MySQL数据库读取完/etc/my.cnf之后，接下来会解析$MySQL_HOME/my.cnf。在这个过程中，服务器会到MySQL安装目录下面解析数据库的相关配置。MySQL启动初始化，接着会解析defaults-extra-file附带选项，修改该参数可以指定系统配置文件，接下来数据库会解析有关用户的选项。

初始化模块是在MySQL数据库启动的时候初始化数据库的各种环境变量、数据库初始化设置以及初始化数据库存储引擎的设置。

2、核心API

MySQL数据库核心API主要实现了数据库底层操作的优化功能，其中主要包括IO操作、格式化输出、高性能存储数据结果算法的优化、字符串的处理，其中最重要的是内存管理。

3、网络交互模块

MySQL底层相互交互的模块抽象出接口，对外提供可以接收和发送数据的API接口，其他模块需要交互的时候，可以通过API接口调用。

4、服务器客户端交互协议模块

MySQL服务器采用C/S客户端的形式访问数据库，数据连接使用MySQL C/S客户端交互协议模块，实现了客户端与服务器交互过程中所需要的一些独特协议，这些协议都是建立在现有的网络协议之上的。

5、用户模块

用户模块的主要功能是用于控制用户登录连接的权限和用户的授权管理。

6、访问控制模块

访问控制模块主要用于监控用户的每一个操作。访问控制模块实现的功能就是根据用户模块中不同的用户授权以及数据库的各种约束来控制用户对数据的访问。用户模块和访问控制模块结合起来就组成了MySQL数据库的权限管理功能。

7、连接管理、连接线程和线程管理

连接管理模块负责监听MySQL Server的各种请求，根据不同的请求转发到线程管理模块，每个客户请求都会被数据库自动分配一个独立的线程，为其单独服务，而连接线程的主要工作就是负责MySQL Server与客户端通信。线程管理模块负责管理这些生成的线程。

8、转发模块

客户端连接MySQL之后会发送一些查询语句，在MySQL Server里面，连接线程接收到客户端的一个请求会直接将查询转发到各个对应的处理模块。转发模块主要就是根据查询语句语法分析，然后转发给不同的模块处理。

9、缓存模块

查询缓存模块的主要功能是将客户端查询的请求返回的结果集到缓存中与查询的一个HASH值做对应。在查询的基表发生任何数据变化后，MySQL会自动让查询的缓存失效。在读写比例非常高的应用系统中，查询缓存对性能的提高是非常显著的。

10、优化器模块

这个模块主要是将客户端发送的查询请求在之前算法的基础上分析，计算出一个最优的查询策略，优化之后会提高查询访问的速度，最后根据其最优策略返回查询语句。

11、表变更管理模块

表变更管理模块主要负责完成DML和DDL的查询，例如insert、update、delete、create table、alter table等语句的处理。

12、表维护模块

表维护模块主要用于检测表的状态、分析、优化表结构以及修复表。

13、系统状态管理模块

在客户端请求系统状态的时候，系统状态模块主要负责将各种状态的数据返回给用户。最常用的一些查询状态命令包括show status、show variables等，都是通过这个模块返回的。

14、表管理器

表管理器主要就是维护系统生成的表文件。例如，MyISAM存储引擎类型表生成的是frm文件、MYD文件以及MYI文件，表管理器的工作就是维护这些表，将各个表结构的信息缓存起来，而且还管理表级别的锁。

15、日志记录模块

日志记录模块主要负责整个数据库逻辑层的日志文件，其中包含错误日志、二进制日志以及慢查询日志等。

16、复制模块

复制模块分为Master模块和Slave模块两部分。Master模块主要负责复制环境中读取Master端的binary日志以及Slave端的I/O线程交互等工作。
Slave模块主要有两个线程：一个负责从Master请求和接收binary日志，并写入本地I/O线程；另一个从relay log读取日志事件，然后解析成可以在Slave端执行的命令，再交给Slave端的SQL线程。

17、存储引擎接口模块

MySQL实现了其数据库底层存储引擎的插件式管理，将各种数据处理高度抽象化。

3、MySQL各逻辑块协调工作

MySQL启动后，初始化模块就从系统配置文件中读取系统参数和命令参数，并按照参数初始化整个系统，同时存储引擎也会启动。当初始化工作结束后，连接管理模块会监听并接收客户端的程序，将连接请求转发给线程管理模块进行授权检查。通过检查后，会检测线程池里是否有空间连接线程：如果有，就取出跟客户端连接上；如果没有，就建立一个新的线程与客户端建立连接。

MySQL数据库中的请求有两种：一种是需要命令解析和分发模块解析才能执行请求的操作；另一种是不需要转发就可以直接执行的命令。此时，如果初始阶段开启日志功能，日志模块将请求记入日志。

Query类型的请求会将控制权交给Query解析器。Query解析器会检查是否是select类型的查询，如果是，就启动查询缓存。此时会查询缓存中是否已经存在结果集，如果存在，就将缓存中的数据返回给连接线程模块，之后连接线程会将数据传递到客户端。如果没有被缓存，或者不是一个可以缓存的查询，此时查询解析器进行相应的处理，通过查询分发器传给相关的处理模块。

如果解析器结果是DML/DDL，就交给变更管理模块；如果是一些检查、修复类的查询，就交给表维护模块去处理；如果是一条没有被缓存的查询语句，就交给查询优化器模块。实际上表变更管理器又分为若干小的模块、例如insert处理器、delete处理器、update处理器、create处理器以及alter处理器来负责不同的DML和DDL。总之，查询优化器、表变更模块、表维护模块、复制模块、状态模块都是根据命令解析器的结果不同而分发给不同类型的模块。

当一条命令执行完成之后，控制权都会还给连接线程模块。在上面各个模块的处理过程中，各个模块都依赖于整个MySQL的核心API模块，比如内存管理、文件I/O、字符串处理等。

二、数据库存储引擎

数据库存储引擎是数据库底层软件组件，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。

1、MySQL存储引擎简介

MySQL提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。MySQL8.0支持的存储引擎有InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE等。

可以使用SHOW ENGINES语句查看系统所支持的引擎类型。

2、InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。MySQL8及之后，InnoDB作为默认存储引擎，主要特性如下：
（1）InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表与其他MySQL表的类型混合起来，甚至在同一个查询中也可以混合。
（2）InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。
（3）InnoDB存储引擎完全与MySQL服务器整合，为在主内存中缓存数据和索引而维护它自己的缓冲池。InnoDB将它的表和索引存在一个逻辑表空间中，表空间可以包含数个文件。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。
（4）InnoDB支持外键完整性约束（FOREIGN KEY）。存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显式在表定义时指定主键，InnoDB会为每一行生成一个6B的ROWID，并以此作为主键。
（5）InnoDB被用在众多需要高性能的大型数据库站点上。InnoDB不创建目录，使用InnoDB时，MySQL将在数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。

3、MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓库和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。MyISAM的主要特性如下：
（1）在支持大文件（达63位文件长度）的文件系统和操作系统上被支持。
（2）当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少的碎片。这要通过合并相邻被删除的块以及若下一个块被删除则扩展到下一个块来自动完成。
（3）每个MyISAM表最大的索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16个。
（4）最大的键长度是1000B，这也可以通过编译来改变。
（5）BLOB和TEXT列可以被索引。
（6）NULL值被允许在索引的列中，这个值占每个键的0~1个字节。
（7）所有数字键值以高字节优先被存储，以允许一个更高的索引压缩。
（8）每个表一个AUTO_INCREMENT列的内部处理。MyISAM为INSERT和UPDATE操作自动更新这一列，这使得AUTO_INCREMENT列更快。在序列顶的值被删除之后就不能再利用。
（9）可以把数据文件和索引文件放在不同目录。
（10）每个字符列可以有不同的字符集。
（11）有VARCHAR的表可以固定或动态记录长度。
（12）VARCHAR和CHAR列可以多达64KB。
使用MyISAM引擎创建数据库，将生产3个文件。文件的名字以表的名字开始，扩展名指出文件类型：frm文件存储表定义，数据文件的扩展名为.MYD（MYDATA），索引文件的扩展名是.MYI（MYIndex）。

4、MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。MEMORY的主要特性如下：
（1）MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500B的最大键长度。
（2）MEMORY存储引擎执行HASH和BTREE索引。
（3）可以在一个MEMORY表中有非唯一键。
（4）MEMORY表使用一个固定的记录长度格式。
（5）MEMORY不支持BLOB或TEXT列。
（6）MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引。
（7）MEMORY表在所有客户端之间共享。
（8）MEMORY表内容被存在内存中，内存是MEMORY表和服务器在查询处理时的空闲中创建的内部表共享。
（9）当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）。

5、MERGE存储引擎

MERGE存储引擎是一组MyISAM表组合，将一组结构相同的MyISAM表组合成一个逻辑单元，通常也叫作MRG_MYISAM存储引擎。MERGE表本身没有数据，对于MERGE类型表的插入操作是通过INSERT_METHOD子句完成的，可以使用FIRST或者LAST值，使其数据增加到第一个表或者最后一个表上。其实，上述操作是对内部MyISAM表进行的操作，所以在创建MERGE表时，MySQL只会生成两个较小的文件：一个是.frm文件，用于存放数据；还有一个.MRG文件，用于存放MERGE表的名称，包括MERGE表由哪些表组成。

6、BerkeleyDB存储引擎

BerkeleyDB存储引擎不是MySQL开发的存储引擎，是由Sleepycat Software开发的事务性存储引擎，简称为BDB。
创建BDB表会生成两个数据文件，文件的名字用表名来开头，扩展名表示其不同的文件类型：一个.frm文件，存储表元数据；一个.db文件，包含数据和索引内容。另外，为了实现事务安全，BDB有自己的redo日志，可以通过参数设置日志存放的位置。在锁定机制方面，BDB和MEMORY存储引擎一样，实现页级锁定。

三、认识MySQL的锁定机制

MySQL与其他数据库在锁定机制方面最大的不同之处在于，对于不同的存储引擎支持不同的锁定机制。例如，InnoDB存储引擎支持行级锁，也支持表级锁，默认的情况下是采用行级锁；MyISAM和MEMORY存储引擎采用的是表级锁（table-level-locking）。BDB存储引擎采用的是页面锁（page-level-locking）同时也支持表级锁。

总的来说，MySQL各存储引擎使用了3种级别的锁定机制：行级锁定，页级锁定和表级锁定。

下面分析一下这3种级别的锁机制的特点。

1、行级锁定

行级锁最大的特点是锁定对象的颗粒度很小，发生锁定资源争用的概率也很小，能够给予应用程序尽可能大的并发处理能力，从而提高一些需要高并发应用系统的整体性能。

虽然能够在并发处理能力上有较大的优势，但是行级锁也存在不少弊端。由于行级锁的颗粒度比较小，每次获取锁和释放锁会消耗比较大，因此加锁比较慢，很容易发生死锁。

行级锁不是MySQL自己实现的锁定方式，而是由其他存储引擎所实现的，比如InnoDB存储引擎。InnoDB实现了两种类型的行级锁，包括共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB使用了两种内部使用的意向锁，也就是意向共享锁和意向排他锁。各个锁的含义如下：

• 共享锁（S）：允许一个事务读一行数据时阻止其他事务取得相同数据的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据的共享锁和排他锁。
• 意向共享锁（IS）：事务打算给数据行加行共享锁。事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁。事务在给一个数据行加排他锁前必须先取得该表的IX锁。

4种锁的共存逻辑关系表

锁模式	共享锁（S）	排他锁（X）	意向共享锁（IS）	意向排他锁（IX）
共享锁（S）	兼容	冲突	兼容	冲突
排他锁（X）	冲突	冲突	冲突	冲突
意向共享锁（IS）	兼容	冲突	兼容	兼容
意向排他锁（IX）	冲突	冲突	兼容	兼容

如果一个事务请求的锁模式与当前的锁模式兼容，InnoDB就将请求的锁授予该事务；如果两者不兼容，那么事务要等待锁释放。

意向锁是InnoDB存储引擎自动加的。对于普通SELECT语句，InnoDB不会加任何锁，对于INSERT、UPDATE、DELETE语句，InnoDB会自动给涉及的数据加排他锁，InnoDB可以通过以下语句添加共享锁和排他锁。

（1）添加共享锁（S）

 SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;

(2) 添加排他锁（X）

SELECT * FROM table_name WHERE ... FOR UPDATE;

2、表级锁定

与行级锁不同的是，表级锁的锁定机制的颗粒度最大，该锁定机制的最大特点是系统开销比较小，由于实现逻辑非常简单，因此带来的系统负面影响最小。由于表级锁一次性将整个表锁定，因此可以很好地避免死锁的问题。

表级锁定机制也存在一定的缺陷。由于表级锁的锁定机制颗粒度很大。所以发生锁冲突的概率最高、并发度最低。

MySQL数据库的表级锁定主要分为两种类型：一种是读锁定，另一种是写锁定。MySQL数据库提供了以下4种队列来维护这两种锁，间接地说明了数据库表级锁地4种状态：

• Current read lock queue (lock->read)
• Padding read Lock queue(lock->read wait)
• Current write lock queue(lock -> write)
• Padding write lock queue(lock->write wait)

其中，Current read lock queue中存放的是当前持有读锁的所有线程，而正在等待资源的信息则存放在Padding read lock queue中；Current write lock queue中存放的是当前持有写锁的所有线程，而正在等待对资源写操作的信息则存放在Padding write lock queue中。

MySQL内部实现读锁和写锁有多达11种具体的锁类型，由系统中一个枚举类型变量（thr_lock_type）定义。具体各种锁定类型如下表。

锁定类型	含义
IGNORE	当发生锁请求的时候内部交互使用，在锁定结构和队列中并不会有任何信息存储
UNLOCK	释放预定请求的交互用所类型
READ	普通读锁定
WRITE	普通写锁定
READ_WITH_SHARED_LOCKS	在InnoDB中使用到，语法如下：SELECT … LOCK IN SHARE MODE
READ_HIGH_PRIORITY	高优先级读锁定
READ_NO_INSERT	不允许Concurrent的锁定
WRITE_ALLOW_WRITE	这个类型实际上就是由存储引擎自行处理锁定的时候，MySQL允许其他的线程再获取读或者写锁定，因为即使资源冲突，存储引擎也能自行处理
WRITE_ALLOW_READ	这种锁定发生在对表DDL操作的时候，MySQL可以允许其他线程获取读锁定，因为MySQL是通过重建整个表再RENAME而实现的该功能，所以整个过程中表依然可以提供读服务
WRITE_CONCURRENT_INSERT	正在进行Concurrent Insert时使用的锁定方式。该锁定进行的时候，除了READ_NO_INSERT之外的其他任何读锁定请求都不会被堵塞
WRITE_DELAYED	在使用INSERT DELAYED时的锁定类型
WRITE_LOW_PRIORITY	声明的低级别锁定方式，通过设置LOW_PRIORITY_UPDATE=1 而产生
WRITE_ONLY	当在操作过程中某个锁定异常中断之后系统内部需要进行CLOSE TABLE操作，在这个过程中出现的锁定类型就是WRITE_ONLY

对于MySQL数据库读锁和写锁的加锁方式，通常使用LOCK TABLE和UNLOCK TABLE实现对表的加锁和解锁。

LOCK TABLE lock_table_test read;

UNLOCK TABLES;

3、页级锁定

页级锁定在MySQL中是比较特殊的一种锁定方式，颗粒度介于行级锁定与表级锁定之间，所以获取锁定所需要的资源开销以及锁提供的并发处理的能力也介于表级锁定和行级锁定之间。

在数据库实现资源锁定的过程中，锁定机制的粒度越小，数据库实现的算法越复杂，数据库所消耗的内存越大。不过，随着锁机制粒度越来越小，应用的并发发生锁等待的概率也越来越小，系统整体性能会随之增高。

MySQL是用写队列和读队列来完成对数据库的读和写操作的，所以说MySQL数据库存在读锁和写锁的概念。对于写锁而言，如果表没有加锁，就对其表加写锁；如果表已经加了写锁，此时会将写操作的请求放入写锁的队列中。对于读锁而言，如果没有加入读锁，那么请求会加入一个读操作的锁，其他读操作的请求会放到读锁的队列中。

四、MyISAM的锁定机制

MyISAM存储引擎基本上可以说是对MySQL提供的锁定机制所实现的表级锁定依赖最大的一种存储引擎。MyISAM的表锁是使用最为广泛的锁类型。

1、MyISAM表级锁的锁模式

MySQL的表级锁有两种模式：表共享读锁（table read lock）和表独占写锁（table write lock）。
锁模式的兼容性如表

	None	读锁	写锁
读锁	兼容	兼容	冲突
写锁	兼容	冲突	冲突

对于MyISAM表的读操作不会因为不同进程访问资源而发生阻塞，而对于MyISAM表的写操作会阻塞其他用户对同一表的读和写操作。

lock table read_lock read;
unlock tables;

lock table read_lock write;
unlock tables;

2、获取MyISAM表级锁的争用情况

MyISAM存储引擎只支持表锁，MySQL数据库可以通过检查table_locks_waited和tables_locks_immediate状态变量来分析系统上的表锁的争夺情况，命令如下：

show status like 'table%';

这里有两个状态变量记录了MySQL内部表级锁定的情况，两个变量的含义如下：
（1）Table_locks_immediate：产生表级锁定的次数。
（2）Table_locks_waited：出现表锁定争用而发生等待的次数。

如果Table_locks_waited的值比较高，那么说明存在着比较严重的表级锁争用情况。MyISAM在读操作占主导的情况下是高效的，可一旦出现大量读写操作并发，同InnoDB相比，MyISAM的执行效率就会直线下降，对于MyISAM存储引擎表，新的数据会被附加到文件的结尾，可如果经常做一些UPDATE和DELETE操作，数据将不会是连续的，数据文件中就会出现很多空洞，此时再插入新的数据时，默认情况下会先看这些空洞是否可以容纳新的数据，如果可以容纳新的数据，那么会将数据保存到空洞里面去，反之，会将新的数据保存到数据文件的结尾。这样做是为了减少文件的大小，降低产生文件碎片。

MyISAM存储引擎往往因为读表请求的增加，会出现比较严重的读写锁的问题，所以经常在实际应用中采用主从分离。主从服务器读写操作分离出来，主服务器执行写操作，而从服务器负责查询操作，此时往往因为主服务器执行完了写入的操作，但从服务器有大量的查询操作，会被这些来自主服务器和从服务器同步的UPDATE和INSERT操作严重堵塞，最后造成所有的MySQL从库负载迅速上升。在解决MyISAM读写互斥问题中，由于没有办法在短期内增加读的服务器，所以通过对MySQL进行一些配置，以牺牲数据实时性为代价来换取所有服务器的安全。具体配置如下。
（1）当对于同一个MyISAM表进行查询和插入操作时，为了降低锁竞争频率，可以将concurrent_insert的值设置为2，此时不管表有没有空洞，都允许数据文件结尾并发插入数据。至于产生的文件碎片，可以使用OPTIMIZE TABLE语法进行优化。
（2）在默认情况下，写操作的优先级要高于读操作，即便先发送的是读操作请求，后发送的是写操作的请求，此时也会优先处理写请求，然后处理读请求。可以考虑设置max_write_lock_count=1,此时当系统处理一个写操作后，就会暂停写操作，给读操作执行的机会。
（3）降低写操作的优先级，给读操作更高的优先级别，可以将low-priority-updates设置为1。

3、MyISAM表级锁加锁方法

MyISAM在执行查询语句的时候会自动给查询语句涉及的数据库表记录添加读锁，而在执行数据更新操作（比如UPDATE、INSERT、DELETE等语句）时MySQL数据库会自动给涉及的表记录添加写锁。

对于MySQL数据库读锁和写锁的加锁方式，通常使用命令LOCK TABLE和UNLOCK TABLE实现手动加表级锁。LOCK TABLE、UNLOCK TABLE可以用来锁定和释放当前线程的表，具体语法如下：

LOCK TABLE 
tbl_name [as alias] {READ[LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name [as alias] {READ[LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES

要想使用LOCK TABLE，必须拥有有关表的LOCK TABLE权限和SELECT权限。如果一个线程获得对一个表的读锁，那么该线程只能从该表读取数据，如果一个线程获得对一个表的写锁，那么该线程只能对表进行写数据，此时其他线程会被阻塞，直到写被释放为止。

下面详细讲解LOCK TABLE的用法。

当对表以别名的形式锁定时，不能在一次查询中多次使用一个已锁定的表使用别名代替，在此情况下，必须分别获得对每个别名的锁定，举例如下：

lock table c_table write,c_table as c write;

#下面语句出错
insert into c_table select * from c_table;

#下面语句执行成功
insert into c_table select * from c_table as c;

如果查询一个别名引用一个表，那么必须使用相同的别名锁定该表，如果没有别名，则不会锁定该表，举例如下：

unlocd tables;

lock table c_table read;

#下面语句出错
select * from c_table as c;

如果使用一个别名锁定一个表，那么必须使用该别名在查询中引用该表，举例如下：

unlock tables;

lock table c_table as c read;

#下面语句出错
select * from c_table;

#下面语句执行成功
select * from c_table as c;

READ LOCAL和READ之间的区别是，READ LOCAL允许在锁定被保持时执行非冲突性INSERT语句同时插入，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录。

如果对一个表使用LOW_PRIORITY WRITE锁定，就意味着MySQL等待特定的锁定，直到没有申请READ锁定的线程为止。当线程已经获得WRITE锁定，并在等待得到锁定清单中用于下一个表的锁定时，所有其他线程会等待WRITE锁定被释放。

4、 MyISAM Concurrent Insert的特性

MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别是0、1、2。
（1）当concurrent_insert的值为0时，不允许并发插入。
（2）当concurrent_insert的值为1时，如果表中没有被删除的行，MyISAM允许在一个进程读表的同时另一个进程从表位插入记录。
（3）当conrurrent_insert的值为2时，无论表中有没有被删除的行，都允许在表尾并发插入记录。

5、MyISAM表级锁优化建议

对于MyISAM存储引擎，虽然使用表级锁在实现过程中比行级锁和页面锁带来的附加成本要小，所消耗的资源也小，不过MyISAM表级锁的颗粒度比较大，在数据库并发处理过程中产生的数据资源争用的情况也会比其他的锁定级别要多，从而在较大程度上会降低并发处理能力。MyISAM表级锁的优化建议如下：
（1）MyISAM表级锁的锁定级别是固定的，所以在考虑MyISAM表级锁优化时，重点考虑如何提高并发的效率。
（2）减少锁定的时间，让查询的时间尽可能短。减少比较复杂的查询语句，可以考虑将复杂的查询分解成多个小的查询。尽可能建立足够高效的索引，让数据检索更迅速。尽量让MyISAM存储引擎的表控制字段类型。利用合理的机会优化MyISAM表数据文件。
（3）MyISAM表级锁可以考虑分离能并行的操作，对于读写互相阻塞的表级锁，可能会觉得在存储引擎的表上就只能是完全的串行化，没法再并行了，可是MyISAM的存储引擎还有个非常有用的特性，就是Concurrent Insert的特性。可以考虑设置Concurrent Insert值为2，此时无论MyISAM存储引擎的数据文件中间部分是否存在空洞（因为删除数据而留下的空闲空间），都允许由数据文件尾部进行插入操作。
（4）MyISAM的表级锁定对于读和写是由不同优先级别设定的，默认情况下写操作的优先级别高于读操作的优先级别，可以考虑根据应用的实际情况来设置读锁和写锁的优先级别，此时可以通过设置系统参数low_priority_updates=1将写的优先级别设置为比读的优先级低。

五、InnoDB的锁定机制

MySQL的MyISAM存储引擎只支持表级锁，随着应用对事务的完整性和并发性要求越来越高。MySQL才出现基于支持事务的存储引擎，后来慢慢出现了支持行级锁的InnoDB存储引擎。
MySQL数据库最常用的两种存储引擎是MyISAM和InnoDB两种存储引擎，这两种类型的存储引擎的表各有优缺点，MyISAM类型不支持事务，不过执行的效率比InnoDB类型的存储引擎更快，而InnoDB类型的存储引擎支持事务特性。并且InnoDB提供外键等数据库高级功能。在处理数据量上InnoDB可以处理海量数据，并且在具有良好索引的基础上，InnoDB的查询速度要比MyISAM块。另外，InnoDB存储引擎采用了行级锁。

1、InnoDB行级锁模式

InnoDB存储引擎支持行级锁，支持事务处理。事务是由一组SQL语句组成的逻辑处理单元，它的ACID特性如下：
（1）原子性（Atomicity）：事务具有原子不可分割的特性，要么一起执行，要么不执行。
（2）一致性（Consistency）：在事务开始和事务结束时，数据都保持一致状态。
（3）隔离性（Isolation）：在事务开始和结束过程中，事务保持着一定的隔离特性，保证事务不受外部并发数据操作的影响。
（4）持久性（Durability）：事务完成后，数据将会被持久化到数据库中。

InnoDB存储引擎并发事务处理能力大大增加了数据库资源的利用率，提高了数据库系统的事务吞吐量，但并发事务同时也存在一些问题，主要包括更新丢失（Lost Update）、脏读（Dirty Reads）、不可重复读（Non-Repeatable Reads）、幻读（Phantom Reads）。它们的具体含义如下：
（1）更新丢失：两个事务更新同一行数据，但是第二个事务却中途失败退出了，导致对两个修改都失效了，这时系统没有执行任何锁操作，因此并发事务并没有被隔离。
（2）脏读：一个事务读了某行数据，但是另一个事务已经更新了这行数据，这是非常危险的，很可能所有的操作被回滚。
（3）不可重复读：一个事务对一行数据重复读取两次，可是得到了不同的结果。在两次读取数据的中途，有可能存在另一个事务对数据进行了修改。
（4）幻读：事务在操作过程中进行两次查询第二次查询结果包含了第一次没有出现的数据。出现幻读的主要原因是，两次查询过程中另一个事务插入新的数据。

数据库并发中的“更新丢失”通常是应该完全避免的，但防止更新丢失数据，并不能单靠数据库事务控制来解决，需要应用程序对要更新的数据加必要的锁来解决，而以上出现的数据库“脏读”“不可重复读”“幻读”都必须由数据库提供一定的事务隔离机制来解决。为了避免数据库事务带来的问题，在标准SQL规范中定义了4个事务的隔离级别，不同的隔离级别对事务的处理不一样。

数据库隔离级别包括未提交读（Read uncommitted）、已提交读（Read committed）、可重复读（Repeatable read）、可序列化（Serializable），事务的隔离级别越严格，并发副作用就越小，但付出的代价也越大。

隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读	最低级别，只保证不读取物理上损坏的数据	是	是	是
已提交读	语句级	否	是	是
可重复读	事务级	否	否	是
可序列化	最高级别，事务级	否	否	否

InnoDB存储引擎实现了4种行锁，分别是共享锁（S）、排他锁（X）、意向共享锁（IS）、意向排他锁（IX）。下面进一步学习这4种行锁的知识。

首先，使用共享锁和排他锁必须要满足以下几个条件：
（1）设置autocommit的值是OFF或者0.
（2）表的数据引擎是支持事务的，比如InnoDB数据引擎。
（3）如果不管autocommit，手动在事务里执行操作，这时要使用begin或者start transaction开始事务。
（4）不要再锁定事务规定的时间外使用共享锁和排他锁。

2、获取InnoDB行级锁的争用情况

InnoDB所使用的行级锁，在系统中是通过另一组更为详细的状态来记录的，查看命令如下：

show status like '%innodb_row_lock%';

InnoDB行级锁定状态不仅记录了锁定等待次数，还记录了锁定总时间长、每次平均时长以及最大时长，各个状态变量的说明如下：
（1）Innodb_row_lock_current_waits:当前正在等待锁定的数量。
（2）Innodb_row_lock_time：从系统启动到现在锁定总时间长度。
（3）Innodb_row_lock_time_avg：每次等待的平均时间。
（4）Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间。
（5）Innodb_row_lock_waits：系统启动后到现在总共等待的次数。

根据InnoDB提供的这些系统状态的分析，制定相应的优化计划，尤其是当等待次数比较高，并且每次等待时间比较大的时候，需要分析系统出现这种情况的原因。

3、InnoDB行级锁的实现方法

InnoDB行级锁是通过给索引上的索引项加锁来实现的。只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁。

在不通过索引条件查询的时候，InnoDB使用的是表锁，而不是行锁。

当表中锁定其中的某几行时，不同的事务可以使用不同的索引锁定不同的行。另外，不论使用主键索引、唯一索引还是普通索引，InnoDB都会使用行锁来对数据加锁。

4、间隙锁（Net-Key锁）

在更新InnoDB存储引擎表中的某个区间数据时，将会锁定这个区间的所有记录。例如，update xxx where id between 1and 100,就会锁住id从1到100之间的所有记录。值得注意的是，在这个区间中假设某条记录并不存在，该条记录也会被锁住，这时，如果另外一个Session往这个表中添加一条记录，就必须要等到上一个事务释放锁资源。

InnoDB使用间隙锁的目的有两方面：一方面是为了防止幻读，如果没有添加间隙锁，其他事务要添加id在1到100之间的某条记录就会发生幻读；另一方面是为了满足其恢复和赋值的需求。

5、InnoDB在不同隔离级别下加锁的差异

在不同的隔离级别下，InnoDB处理SQL语句时所采用的一致性和需要的锁是不同的。

对于SQL语句而言，隔离级别越高，InnoDB存储引擎给记录添加的锁就越严格，产生锁冲突的可能性就越高，对并发的性能影响就越大。因此，应该尽量使用较低的隔离级别，以降低并发中锁争用的概率。

对于一些需要使用较高级别的隔离级别的情况，可以通过如下操作更换隔离级别，命令如下：

set session transaction isolation level repeatable read;

set session transaction isolation level serializable;

6、InnoDB存储引擎中的死锁

一般情况下，如果InnoDB存储引擎发生了死锁状况，通常是一个事务释放锁并回滚，另一个事务获得锁，继续完成事务。但在涉及外部锁或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，此时需要通过设置锁等待时间（innodb_lock_wait_timeout）来解决。通常情况下，死锁都是应用设计的问题，通过调整业务流程、事务大小、数据库访问的SQL语句，绝大多数死锁都可以避免。下面来看一个InnoDB存储引擎发生死锁的例子。

Session1	Session2
set autocommit=0;	set autocommit=0;
更新数据，命令如下：update t_innodb_lock set b=‘test’ where a=1;
	更新数据，命令如下：update t_innodb_lock set b=‘test’ where a=2;
等待Session2释放资源，被阻塞，命令如下：update t_innodb_lock set b=‘test’ where a=2;
	更新a=1的记录，此时发生死锁，结果如下：update t_innodb_lock set b=‘test’ where a=1;
获得锁资源，更新操作成功执行：update t_innodb_lock set b=‘test’ where a=2;

通常在应用中，在REPEATABLE-READ隔离级别下，如果两个线程同时以相同的概率使用的排他锁，在没有符合该条件记录的情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会发生死锁。这种情况下，将隔离级别改成READ COMMIT，就可以避免死锁。

Session1	Session2
set autocommit=0;	set autocommit=0
select @@tx_isolation;	select @@tx_isolation
start transaction;	start transaction;
查询数据命令如下：select * from innodb_dead_lock where id=102 for update;
	查询数据命令如下：select * from innodb_dead_lock where id=102 for update;
插入数据命令如下：insert into innodb_dead_lock values(102,‘data102’)；等待
	其他Session已经对记录进行了更新，此时在插入此条记录就会发生死锁并退出 insert into innodb_dead_lock values(102,‘data102’)；
其他Session已经退出，当前Session可以获得锁并成功插入记录：insert into innodb_dead_lock values(102,‘data102’)；

7、InnoDB行级锁优化建议

InnoDB存储引擎实现了行级锁，很显然在锁定方面行级锁的颗粒更小，实现更为复杂，所带来的性能损耗比表级锁更高，但是InnoDB在并发性能上远远要高于表级锁。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比优势就比较明显了，所以说在选择使用哪种锁的时候，应该考虑应用是否有很大的并发量。想要合理使用InnoDB的行级锁，应该做到扬长避短，尽量做到以下几点：
（1）尽量控制事务的大小，减少锁定的资源量和锁定时间长度。
（2）尽可能让所有的数据检索都通过索引来完成，从而避免因无法通过索引加锁而升级为表级锁定。
（3）尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录。
（4）在业务环节允许的情况下，尽量使用较低级别的事务隔离，以减少因为事务隔离级别所带来的附加成本。
（5）合理使用索引，让InnoDB在索引上面加锁的时候更加准确。
（6）在应用中，尽可能按照相同的访问顺序来访问，防止产生死锁。
（7）在同一个事务中，尽可能做到一次锁定所需的所有资源，减少产生死锁的概率。
（8）对于容易产生死锁的业务，可以放弃使用InnoDB行级锁，尝试使用表级锁来减少死锁产生的概率。
（9）不要申请超过实际需要的锁级别