MySQL 8.0——存储引擎

1、MySQL架构

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

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

1.1、MySQL物理文件的组成

1.1.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-slow_queries[=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选项来设置日志的数量。

1.1.2、数据文件

MySQL数据库会在data目录下面建立一个以数据库为名字的文件夹，用来存储数据库中的表文件数据。不同的数据库引擎，每个表的扩展名也不一样。例如，MyISAM引擎用“.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文件来存储。

共享表空间存储通过innodb_data_home_dir和innodb_data_file_path两个参数共同配置组成：innodb_data_home_dir参数配置数据存放的总目录，innodb_data_file_path参数配置每一个文件的路径及文件名称。如果需要添加新的ibdata文件，就需要在innodb_data_file_path参数后面配置，然后重新启动服务器才能生效。

1.1.3、其他文件

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

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

1.2、MySQL各逻辑块简介

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

1.2.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数据库启动的时候初始化数据库的各种环境变量、数据库初始化设置以及初始化数据存储引擎的设置。

1.2.2、核心API

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

1.2.3、网络交互模块

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

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

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

1.2.5、用户模块

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

1.2.6、访问控制模块

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

1.2.7、连接管理﹑连接线程和线程管理

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

1.2.8、转发模块

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

1.2.9、缓存模块

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

1.2.10、优化器模块

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

1.2.11、表变更管理模块

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

1.2.12、表维护模块

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

1.2.13、系统状态管理模块

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

1.2.14、表管理器

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

1.2.15、日志记录模块

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

1.2.16、复制模块

复制模块分为Master模块和Slave模块两部分。Master模块主要负责复制环境中读取Master端的binary日志以及Slave端的I/O线程交互等工作。

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

1.2.17、存储引擎接口模块

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

1.3、MySQL各逻辑块协调工作

MySQL各逻辑模块协调工作的过程如图所示。

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

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

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

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

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

2、数据库存储引擎

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

2.1、MySQL存储引擎简介

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

可以使用SHOW ENGINES语句查看系统所支持的引擎类型，结果如下：

show engines \G;

***************************[ 1. row ]***************************
Engine       | FEDERATED
Support      | NO
Comment      | Federated MySQL storage engine
Transactions | <null>
XA           | <null>
Savepoints   | <null>
***************************[ 2. row ]***************************
Engine       | MEMORY
Support      | YES
Comment      | Hash based, stored in memory, useful for temporary tables
Transactions | NO
XA           | NO
***************************[ 2. row ]***************************
Engine       | MEMORY
Support      | YES
Comment      | Hash based, stored in memory, useful for temporary tables
Transactions | NO
XA           | NO
Savepoints   | NO
***************************[ 3. row ]***************************
Engine       | InnoDB
Support      | DEFAULT
Comment      | Supports transactions, row-level locking, and foreign keys
Transactions | YES
Savepoints   | NO
***************************[ 3. row ]***************************
Engine       | InnoDB
Support      | DEFAULT
Comment      | Supports transactions, row-level locking, and foreign keys
Transactions | YES
XA           | YES
Savepoints   | YES
***************************[ 4. row ]***************************
Engine       | PERFORMANCE_SCHEMA
Support      | YES
Comment      | Performance Schema
Transactions | NO
XA           | NO
Savepoints   | NO
***************************[ 5. row ]***************************
Engine       | MyISAM
Support      | YES
Comment      | MyISAM storage engine
Transactions | NO
XA           | NO
Savepoints   | NO
***************************[ 6. row ]***************************
Engine       | CSTORE
Support      | YES
Comment      | Supports column store
Transactions | YES
XA           | NO
***************************[ 6. row ]***************************
Engine       | CSTORE
Support      | YES
Comment      | Supports column store
Transactions | YES
XA           | NO
Savepoints   | YES
***************************[ 7. row ]***************************
Engine       | MRG_MYISAM
Support      | YES
Comment      | Collection of identical MyISAM tables
Transactions | NO
Support      | YES
Comment      | Collection of identical MyISAM tables
Transactions | NO
XA           | NO
Savepoints   | NO
***************************[ 8. row ]***************************
Engine       | BLACKHOLE
Support      | YES
Comment      | /dev/null storage engine (anything you write to it disappears)
Transactions | NO
XA           | NO
Savepoints   | NO
***************************[ 9. row ]***************************
Engine       | CSV
Support      | YES
Comment      | CSV storage engine
Transactions | NO
XA           | NO
Savepoints   | NO
***************************[ 10. row ]***************************
Engine       | ARCHIVE
Support      | YES
Comment      | Archive storage engine
Transactions | NO
XA           | NO
Savepoints   | NO

Support列的值表示某种引擎是否能使用：YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认存储引擎。

2.2、InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID）​，支持行锁定和外键。MySQL 5.5.5之后，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大小的日志文件。

2.3、MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。MyISAM的主要特性如下：

1. 在支持大文件（达63位文件长度）的文件系统和操作系统上被支持。
2. 当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块以及若下一个块被删除则扩展到下一块来自动完成。
3. 每个MyISAM表最大的索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16个。
4. 最大的键长度是1000B，这也可以通过编译来改变。对于键长度超过250B的情况，一个超过1024B的键将被用上。
5. BLOB和TEXT列可以被索引。
6. NULL值被允许在索引的列中，这个值占每个键的0~1个字节。
7. 所有数字键值以高字节优先被存储，以允许一个更高的索引压缩。
8. 每个表一个AUTO_INCREMENT列的内部处理。MyISAM为INSERT和UPDATE操作自动更新这一列，这使得AUTO_INCREMENT列更快（至少10%）​。在序列顶的值被删除之后就不能再利用。
9. 可以把数据文件和索引文件放在不同目录。
10. 10.每个字符列可以有不同的字符集。
11. 有VARCHAR的表可以固定或动态记录长度。
12. VARCHAR和CHAR列可以多达64KB。

使用MyISAM引擎创建数据库，将生产3个文件。文件的名字以表的名字开始，扩展名指出文件类型：frm文件存储表定义，数据文件的扩展名为.MYD（MYData）​，索引文件的扩展名是.MYI （MYIndex）​。

2.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表在所有客户端之间共享（就像其他任何非TEMPORARY表）​。
8. MEMORY表内容被存在内存中，内存是MEMORY表和服务器在查询处理时的空闲中创建的内部表共享。
9. 当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROPTABLE）​。

2.5、MERGE存储引擎

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

下面给出一个创建和使用MERGE表的例子。

1. 创建3个表：table_myisam_1、table_myisam_2、table_merge_12（MERGE表）​。

create table table_myisam_1
(
	id int primary key,
	data datetime
)engine=MyISAM default charset=gbk;

create table table_myisam_2
(
	id int primary key,
	data datetime
)engine=MyISAM default charset=gbk;

create table table_merge_12
(
	id int primary key,
	data datetime
)engine=merge union=(table_myisam_1, table_myisam_2) insert_method=first;

2. 向前两个表table_myisam_1、table_myisam_2添加数据。

insert into table_myisam_1 values
(1, '2018-1-2'),
(2, '2018-1-3');

insert into table_myisam_2 values
(1, '2018-1-2'),
(2, '2018-1-3');

3. 查询MERGE表table_merge_12的数据。

select * from table_merge_12;

此时会发现insert_method=first起作用了，数据已经添加到table_myisam_1表中，table_merge_12表还是前两个表合并的结果。

通常，如果没有指定insert_method参数，任何尝试往MERGE表中INSERT数据的操作都会发生错误。

2.6、BerkeleyDB存储引擎

BerkeleyDB存储引擎不是MySQL开发的存储引擎，是由Sleepycat Software开发的事务性存储引擎，简称为BDB。

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

创建一个BDB表，可以用ENGINE或者TYPE表选项来创建，命令如下：

create table table_bdb_1
(
	id int primary key,
	data varchar(20)
) engine=BDB;

查看创建表的状态，命令如下：

由于BDB存储引擎实现了事务安全，因此BDB在每次启动的时候都会做一次检查操作，并且会将所有的redo日志清空。

3、认识MySQL的锁定机制

MySQL与其他数据库在锁定机制方面最大的不同之处在于，对于不同的存储引擎支持不同的锁定机制。

• InnoDB存储引擎支持行级锁（row-level locking）​，也支持表级锁，默认的情况下是采用行级锁；
• MyISAM和MEMORY存储引擎采用的是表级锁（table-levellocking）​。
• BDB存储引擎采用的是页面锁（page-level-locking）同时也支持表级锁。

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

3.1、行级锁定

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

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

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

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

上面这4种锁的共存逻辑关系如表所示。

意向锁是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。

3.2、表级锁定

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

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

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

• Current read lock queue (lock -> read)。
• Padding read lock queue (lock -> readwait)。
• Current write lock queue (lock ->write)。
• Padding write lock queue (lock -> writewait)。

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

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

对于MySQL数据库读锁和写锁的加锁方式，通常使用LOCK TABLE和UNLOCK TABLE实现对表的加锁和解锁。下表是一个获得表锁和释放表锁的详细过程。

3.3、页级锁定

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

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

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

下面通过一个简单的例子来说明读写操作，具体操作步骤如下：

1. 首先创建表content并插入数据，语句如下：

2. 向表content里面添加大量的数据，数据越多，效果越明显，重复多次执行以下语句即可：

3. 此时，准备工作已经完成，根据表21.4运行读写操作，理解MySQL读写队列运行的过程。

对于Session1，此时表没有加锁，所以对表加上一个读锁。对于Session2，此时表content已经加上了读锁，所以会将update请求放到锁定队列中。

从上述特点可见，很难笼统地说哪种锁定机制好，只能根据具体应用的特点来选择哪种锁定机制更合适。仅从锁的角度来看，表级锁更适合以查询为主、只有少量按索引条件更新数据的应用，而行级锁适应于有大量按索引条件并发更新少量不同数据同时又有并发查询的应用。

4、MyISAM的锁定机制

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

4.1、MyISAM表级锁的锁模式

MySQL的表级锁有两种模式：表共享读锁（table readlock）和表独占写锁（table write lock）​。

锁模式的兼容性如表所示。

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

下面通过一个例子来看一下MySQL表级锁的读锁过程，如表所示。


下面看一下MySQL表级锁的写锁测试的例子，如表所示。

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

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

show status like 'table%';

+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Table_locks_immediate      | 1465519 |
| Table_locks_waited         | 0       |
| Table_open_cache_hits      | 89      |
| Table_open_cache_misses    | 9       |
| Table_open_cache_overflows | 9       |
+----------------------------+---------+

这里有两个状态变量记录了MySQL内部表级锁定的情况，两个变量的含义如下：

• Table_locks_immediate：产生表级锁定的次数。
• Table_locks_waited：出现比较锁定争用而发生等待的次数。

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

MyISAM存储引擎表往往因为读表请求的增加，会出现比较严重的读写锁的问题，所以经常在实际应用中采用主从分离。主从服务器读写操作分离出来，主服务器执行写操作，而从服务器负责查询操作，此时往往因为主服务器执行完了写入的操作，但从服务器有大量的查询操作，会被这些来自主服务器和从服务器同步的UPDATE和INSERT操作严重堵塞，最后造成所有的MySQL从库负载迅速上升。在解决MyISAM读写互斥问题中，由于没有办法在短期内增加读的服务器，所以通过对MySQL进行一些配置，以牺牲数据实时性为代价来换取所有服务器的安全。具体配置如下。

1. 当对于同一个MyISAM表进行查询和插入操作时，为了降低锁竞争频率，可以将concurrent_insert的值设置为2，此时不管表有没有空洞，都允许数据文件结尾并发插入数据。至于产生的文件碎片，可以定期使用OPTIMIZETABLE语法进行优化。
2. 在默认情况下，写操作的优先级要高于读操作，即便先发送的是读操作请求，后发送的是写操作的请求，此时也会优先处理写请求，然后处理读请求。可以考虑设置max_write_lock_count=1，此时当系统处理一个写操作后，就会暂停写操作，给读操作执行的机会。
3. 降低写操作的优先级，给读操作更高的优先级别，可以将low-priority-updates设置为1。

4.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，必须拥有有关表的LOCKTABLE权限和SELECT权限。如果一个线程获得对一个表的读锁，那么该线程只能从该表中读取数据，如果一个线 程获得对一个表的写锁，那么该线程只可以对表进行写数据，此时其他线程会被阻塞，直到写被释放为止。

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

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

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

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

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

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

4.4、MyISAM Concurrent Insert的特性

MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别是0、1、2。

1. 当concurrent_insert的值为0时，不允许并发插入。
2. 当concurrent_insert的值为1时，如果表中没有被删除的行，MyISAM允许在一个进程读表的同时另一个进程从表尾插入记录。
3. 当concurrent_insert的值为2时，无论表中有没有被删除的行，都允许在表尾并发插入记录。

MySQL数据库默认的concurrent_insert的值为1，即MySQL允许一个进程读表的同时另一个进程从表尾插入记录。查询concurrent_insert值的命令如下：

在如下表所示的例子中，Session1获得表c_table的READ LOCAL锁，此时Session2可以插入数据记录，但是更新操作发生阻塞，Session1不能对表进行插入和更新数据操作。等到Session1释放锁资源后，Session2完成更新操作。

4.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将写的优先级别设置为比读的优先级低。

5、InnoDB的锁定机制

MySQL的MyISAM存储引擎只支持表级锁，随着应用对事务的完整性和并发性要求越来越高，MySQL才出现基于支持事务的存储引擎，后来慢慢出现了支持行级锁的InnoDB存储引擎。

MySQL数据库最常见的两种存储引擎是MyISAM和InnoDB两种存储引擎，这两种类型的存储引擎的表各有优缺点，MyISAM类型不支持事务处理，不过执行的效率比InnoDB类型的存储引擎更快，而InnoDB类型的存储引擎支持事务特性，并且InnoDB提供外键等数据库高级功能。在处理数据量上InnoDB可以处理海量数据，并且在具有良好索引的基础上，InnoDB的查询速度要比MyISAM快。另外，InnoDB存储引擎采用了行级锁，本节会详细介绍InnoDB存储引擎的行级锁。

5.1、InnoDB行级锁模式

InnoDB存储引擎支持行级锁，支持事务处理。事务是由一组SQL语句组成的逻辑处理单元，它的ACID特性如下：

• 原子性（Atomicity）​：事务具有原子不可分割的特性，要么一起执行，要么都不执行。
• 一致性（Consistency）​：在事务开始和事务结束时，数据都保持一致状态。
• 隔离性（Isolation）​：在事务开始和结束过程中，事务保持着一定的隔离特性，保证事务不受外部并发数据操作的影响。
• 持久性（Durability）​：事务完成后，数据将会被持久化到数据库中。

InnoDB存储引擎并发事务处理能力大大增加了数据库资源的利用率，提高了数据库系统的事务吞吐量，但并发事务同时也存在一些问题，主要包括更新丢失（LostUpdate）​、脏读（Dirty Reads）​、不可重复读（Non-Repeatable Reads）​、幻读（Phantom Reads）​。它们的具体含义如下：

• 更新丢失：两个事务更新同一行数据，但是第二个事务却中途失败退出了，导致对两个修改都失效了，这时系统没有执行任何锁操作，因此并发事务并没有被隔离。
• 脏读：一个事务读了某行数据，但是另一个事务已经更新了这行数据，这是非常危险的，很可能所有的操作被回滚。
• 不可重复读：一个事务对一行数据重复读取两次，可是得到了不同的结果。在两次读取数据的中途，有可能存在另外一个事务对数据进行了修改。
• 幻读：事务在操作过程中进行两次查询，第二次查询结果包含了第一次没有出现的数据。出现幻读的主要原因是，两次查询过程中另一个事务插入新的数据。

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

数据库隔离级别包括未提交读（Read uncommitted）​、已提交读（Read committed）​、可重复读（Repeatableread）​、可序列化（Serializable）​，事务的隔离级别越严格，并发副作用就越小，但付出的代价也越大。这4种数据库隔离级别的比较如表所示。

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

首先，使用共享锁和排他锁必须要满足以下几个条件：

• 设置autocommit的值是OFF或者0。
• 表的数据引擎是支持事务的，比如InnoDB数据引擎。
• 如果不管autocommit，手动在事务里执行操作，这时要使用begin或者start transaction开始事务。
• 不要在锁定事务规定的时间外使用共享锁和排他锁。

下面通过一个例子来理解。首先建立一个表，然后添加记录，命令如下：

create table s_table
(
	id int,
	data varchar(20)
)engine=innodb;

insert into s_table values(120, 'test');

使用InnoDB存储引擎共享锁的过程如表所示。

下面接着学习InnoDB存储引擎使用排他锁的例子，具体过程如表所示。

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

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

show status like '%innodb_row_lock%';

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 60091 |
| Innodb_row_lock_time_avg      | 15022 |
| Innodb_row_lock_time_max      | 34754 |
| Innodb_row_lock_waits         | 4     |
+-------------------------------+-------+

InnoDB的行级锁定状态不仅记录了锁定等待次数，还记录了锁定总时间长、每次平均时长以及最大的时长，各个状态变量的说明如下：

• Innodb_row_lock_current_waits：当前正在等待锁定的数量。
• Innodb_row_lock_time：从系统启动到现在锁定总时间长度。
• Innodb_row_lock_time_avg：每次等待的平均时间。
• Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间。
• Innodb_row_lock_waits：系统启动后到现在总共等待的次数。

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

此外，还可以通过如下方法来监控InnoDB行级锁并发争用的情况，具体操作步骤如下：

1. 创建Innodb_Monitor表，从而打开InnoDB的监控功能，命令如下：

create table innodb_monitor (a int)engine=innodb;

2. 使用“SHOW INNODB STATUS”语句查看InnoDB行级锁并发争用的情况，命令如下：

SHOW ENGINE INNODB STATUS\G


***************************[ 1. row ]***************************
Type   | InnoDB
Name   |
Status |
=====================================
2026-06-28 13:43:06 140507681888000 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 274972 srv_active, 0 srv_shutdown, 1084708 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1952
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: reservation count 2020
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: reservation count 2045
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: reservation count 2075
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: reservation count 2038
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: reservation count 2058
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: reservation count 2065
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: reservation count 2071
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: signal count 16162
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
OS WAIT ARRAY INFO: signal count 16162
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-06-28 13:30:36 140505650296576
*** (1) TRANSACTION:
TRANSACTION 337098, ACTIVE 487 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 285587, OS thread handle 140507682428672, query id 10136380 175.31.238.151 root updating
update s_table set data='test2' where id  = 120

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1269 n bits 72 index GEN_CLUST_INDEX of table `test_db`.`s_table` trx id 337098 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000013b4d; asc     ;M;;
 1: len 6; hex 0000000524c5; asc     $ ;;
 2: len 7; hex 82000001290151; asc     ) Q;;
 3: len 4; hex 80000078; asc    x;;
 4: len 4; hex 74657374; asc test;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1269 n bits 72 index GEN_CLUST_INDEX of table `test_db`.`s_table` trx id 337098 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000013b4d; asc     ;M;;
 1: len 6; hex 0000000524c5; asc     $ ;;
 2: len 7; hex 82000001290151; asc     ) Q;;
 3: len 4; hex 80000078; asc    x;;
 4: len 4; hex 74657374; asc test;;


*** (2) TRANSACTION:

*** (2) TRANSACTION:
TRANSACTION 337099, ACTIVE 192 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 285592, OS thread handle 140507681617664, query id 10136550 175.31.238.151 root updating
update s_table set data='test2' where id = 120

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1269 n bits 72 index GEN_CLUST_INDEX of table `test_db`.`s_table` trx id 337099 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000013b4d; asc     ;M;;
 1: len 6; hex 0000000524c5; asc     $ ;;
 2: len 7; hex 82000001290151; asc     ) Q;;
 3: len 4; hex 80000078; asc    x;;
 4: len 4; hex 74657374; asc test;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1269 n bits 72 index GEN_CLUST_INDEX of table `test_db`.`s_table` trx id 337099 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000013b4d; asc     ;M;;
 1: len 6; hex 0000000524c5; asc     $ ;;
 2: len 7; hex 82000001290151; asc     ) Q;;
 3: len 4; hex 80000078; asc    x;;
 4: len 4; hex 74657374; asc test;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 337115
Purge done for trx's n:o < 337114 undo n:o < 0 state: running
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421982196734056, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421982196737032, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421982196736040, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421982196735048, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421982196737032, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421982196736040, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421982196735048, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421982196733064, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421982196732072, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421982196735048, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421982196733064, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421982196732072, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
4 OS file reads, 822712 OS file writes, 1 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 99709, node heap has 0 buffer(s)
Hash table size 99709, node heap has 0 buffer(s)
Hash table size 99709, node heap has 0 buffer(s)
Hash table size 99709, node heap has 0 buffer(s)
Hash table size 99709, node heap has 0 buffer(s)
Hash table size 99709, node heap has 0 buffer(s)
Hash table size 99709, node heap has 0 buffer(s)
Hash table size 99709, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          145362627
Log buffer assigned up to    145362627
Log buffer completed up to   145362627
Log written up to            145362627
Log flushed up to            145362627
Added dirty pages up to      145362627
Pages flushed up to          145362627
Last checkpoint at           145362627
Log minimum file id is       0
Log maximum file id is       0
0 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 1950163
Buffer pool size   24574
Free buffers       22198
Database pages     2376
Old database pages 887
Independent database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Free buffers       22198
Database pages     2376
Old database pages 887
Independent database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1411, created 965, written 822994
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2376, unzip_LRU len: 0
CLOCK len: 0, unzip_CLOCK len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=123208, Main thread ID=140505616725760 , state=sleeping
Number of rows inserted 3005448, updated 35, deleted 562, read 4823268
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 4888, updated 4763, deleted 2624, read 1119241
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

5.3、InnoDB行级锁的实现方法

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

在不通过索引条件查询的时候，InnoDB使用的是表锁，而不是行锁。例如，在下面的案例中，表t_innodb_no_index没有索引，此时使用的是表锁定。案例具体操作过程如表所示。

从结果可以看出，在表t_innodb_no_index没有索引的情况下，InnoDB使用的只是表级锁。如果使用的表包含索引，此时InnoDB将会锁定符合条件的行。下面通过例子来理解包含索引的表锁定行的例子，具体过程如表所示。

当表中锁定其中的某几行时，不同的事务可以使用不同的索引锁定不同的行。另外，不论使用主键索引、唯一索引还是普通索引，InnoDB都会使用行锁来对数据加锁。下面通过例子来理解，操作过程如表所示。


由此可以看出，Session2查询其他被Session1锁定的行，同样会发生阻塞等待锁资源。

5.4、间隙锁(Net-Key锁)

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

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

下面给出一个InnoDB存储引擎的间隙锁阻塞的例子，操作过程如表所示。

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

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

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

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

set session transaction isolation level repearable read;

set sesseion transaction isolation level serializable;

5.6、InnoDB存储引擎中的死锁

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

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

1. 创建测试表innodb_dead_lock，插入测试数据，然后添加索引dead_index_id，命令如下：

2. 执行过程如表所示。

5.7、InnoDB行级锁优化建议

InnoDB存储引擎实现了行级锁，很显然在锁定方面行级锁的颗粒更小，实现更为复杂，所带来的性能损耗也比表级锁更高，但是InnoDB行级锁在并发性能上远远要高于表级锁。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比优势就比较明显了，所以说在选择使用哪种锁的时候，应该考虑应用是否有很大的并发量。想要合理使用InnoDB的行级锁，应该做到扬长避短，尽量做到以下几点：

1. 尽量控制事务的大小，减少锁定的资源量和锁定时间长度。
2. 尽可能让所有的数据检索都通过索引来完成，从而避免因为无法通过索引加锁而升级为表级锁定。
3. 尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录。
4. 在业务环节允许的情况下，尽量使用较低级别的事务隔离，以减少因为事务隔离级别锁带来的附加成本。
5. 合理使用索引，让InnoDB在索引上面加锁的时候更加准确。
6. 在应用中，尽可能按照相同的访问顺序来访问，防止产生死锁。
7. 在同一个事务中，尽可能做到一次锁定所需的所有资源，减少产生死锁的概率。
8. 对于容易产生死锁的业务，可以放弃使用InnoDB行级锁，尝试使用表级锁来减少死锁产生的概率。
9. 不要申请超过实际需要的锁级别。

6、InnoDB存储引擎的在线操作

在MySQL 8.0中，InnoDB存储引擎增加了一些新功能。其中，在线操作非常重要，包括在线添加主键、在线增大VARCHAR列长和在线重命名索引。

“时刻在线”的特性对于所有完美的Web解决方案而言都是必需的。对于数据库管理员或者网站的开发运维人员来说，在不影响前台服务的条件下调试和拓展其后台数据库系统是极其重要的。MySQL 8.0提供了确保全局事务始终在线的方式。因此，读写的操作在此过程中是可行的，并且服务器不需要同步或者重启。在此次更新之前，用户不得不停止所有的更新操作，同步所有的服务器，并且同时重启服务器。这就意味着，为了确保全局事务，必须提前规划好服务器的宕机时间。MySQL 8.0支持在线操作功能，可以满足需要在生产环境中管理MySQL的开发者和数据库管理在线操作的需求。

6.1、在线添加主键

在MySQL 5.6版本中，当主键列为NULL字段的时候，添加主键需要复制数据表。而在MySQL 8.0版本中，可以在线直接添加主键。下面通过案例来对比不同版本中的操作区别。

在MySQL 5.6版本中，添加主键的操作过程如下：

MySQL 8.0的在线操作功能主要是通过加入在线更改InnoDB Bufferf Pool特性来实现的。通过动态修改innodb_buffer_pool_size值，可以在无宕机的情况下修改缓冲池的大小。

在MySQL 8.0版本中，添加主键的操作过程如下：

6.2、在线增大VARCHAR列长

在MySQL 8.0中，数据库管理员可以在线增大varchar字段的长度。不过需要注意的是，目前只支持255字节内的VARCHAR字段增大列长，因为超过255字节，InnoDB层存储数据表示列长度的部分就得多扩展一个字节。下面举例说明。

创建数据表tm，SQL语句如下：

增大VARCHAR的列长为80，SQL语句如下：

从结果可以看出，没有影响记录，实现了在线更改VARCHAR列长的效果。

增大VARCHAR的列长为180，SQL语句如下：

增大VARCHAR的列长为255，SQL语句如下：

增大VARCHAR的列长为256，SQL语句如下：

从结果可以看出，已经影响记录，无法实现在线更改VARCHAR列长的效果。

减小VARCHAR的列长为25，SQL语句如下：

从上述结果可以看出，在线增大VARCHAR的列长必须控制在255以内，而且不支持在线减少VARCHAR的列长。

6.3、在线重命名索引

MySQL 8.0直线在线重命名索引功能，通过ALTERTABLE、RENAME INDEX操作来实现。下面举例说明。在bb表中的bs字段上建立普通索引，SQL语句如下：

查看创建的索引，SQL语句如下：

从结果可以看出，创建了名称为bs的索引。

下面修改bs的索引名称为newbs，SQL语句如下：

alter table bb rename index bs to newbs;

7、MySQL 8.0的新特性——支持JSON类型

MySQL是一个关系型数据库，在MySQL 8.0之前，没有提供对非结构化数据的支持，但是如果用户有这样的需求，也可以通过MySQL的BLOB来存储非结构化的数据。

create table tp
(
	json_data blob
);

insert into tp values
(
	'{
		"key1": "data1", 
		"key2":2, 
		"key3": 
			{
				"sub_key1": "sub_vall"
			}
		}'
);

select * from tp;

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| json_data                                                                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 0x7b0a0909226b657931223a20226461746131222c200a0909226b657932223a322c200a0909226b657933223a200a0909097b0a09090909227375625f6b657931223a20227375625f76616c6c220a0909097d0a09097d |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

在本例中，使用BLOB来存储JSON数据，需要用户保证插入的数据是一个能够转换成JSON格式的字符串，因为MySQL并不保证任何正确性。在MySQL看来，这就是一个普通的字符串，并不会进行任何有效性检查。此外，提取JSON中的字段也需要在用户的代码中完成。

在MySQL 8.0中，已经实现了对JSON类型的支持。MySQL本身已经是一个比较完备的数据库系统，对于底层存储并不适合有太大的改动，那么MySQL是如何支持JSON格式的呢？

MySQL 8.0对支持JSON的做法是在Server层提供一些便于操作JSON的函数，简单地将JSON编码成BLOB，然后交由存储引擎层进行处理。MySQL 8.0的JSON支持与存储引擎没有关系，MyISAM存储引擎也支持JSON格式。

create table tb
(
	data json
)engine=innodb;

insert into tb values('{"key": "val"}');

create table ts 
(
	data json
)engine=myisam;

insert into ts values('{"key": "val"}');

MySQL 8.0提供了很多操作JSON的函数，都是为了提高易用性。

MySQL编码成BLOB对象，首先存放的是JSON的元素个数，然后存放的是转换成BLOB以后的字节数，接下来存放的是key pointers和value pointers。为了加快查找速度，MySQL内部会对key进行排序，以提高处理速度。

在MySQL 8.0中，key的长度只用2个字节（65535）保存，如果超过这个长度，MySQL将报错，如下所示：

8、MySQL 8.0的新特性——全文索引的加强

MySQL 8.0支持更加灵活、更加优化的全文搜索。例如，全本索引支持外部的分析器，就像MyISAM。插件可以替代内置分析器，也可以作为一个前端来使用。MySQL 8.0实现了标记优化器，这个优化器可以将查询结果传递到InnoDB，因此InnoDB可以跳过全文检索部分。

在InnoDB上实现了支持CJK（中文、日文和韩文）的全文检索。MySQL 8.0为CJK提供了一个默认的全文分析器（N-GRAM分析器）​。

在全文索引中，n-gram就是一段文字里面连续的n个字的序列。例如，用n-gram来对“春花秋月”进行分词，得到的结果如表21.18所示。其中，n由参数ngram_token_size控制，即分词的大小，默认是2。

创建数据表，并设置全文检索。SQL语句如下：

create table tft
(
	id int(11) default null,
	name varchar(512) default null,
	content text,
	fulltext key idx_name(name),
	fulltext key idx_content(content) with parser ngram
);

插入演示数据，SQL语句如下：

insert into tft (id, name, content) values
(1, '春花秋月', '经典古诗');

普通检索必须要是整个词才能检索到，SQL语句如下：

select * from tft
where match(name) against ('春花秋月');

部分词是不能检索出信息的，SQL语句如下：

　　mysql>SELECT * FROM tft WHERE MATCH (name) AGAINST ('秋月');
　　Empty set (0.00 sec)

新的全文检索功能检索任意两个组合的记录，SQL语句如下：

再次使用全文检索功能检索任意两个组合记录，SQL语句如下：

9、MySQL 8.0的新特性——动态修改InnoDB缓冲池的大小

从MySQL 5.7.5版本起，MySQL支持在不重启系统的情况下动态调整innodb_buffer_pool_size。调整大小的过程是以innodb_buffer_pool_chunk_size为单位迁移pages到新的内存空间，迁移进度可以通过Innodb_buffer_pool_resize_status查看。当在线修改缓冲池大小的时候，以chunk为单位进行增长或收缩。缓冲池大小是innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数（128MB）​，如果不是，将会适当调大innodb_buffer_pool_size，以满足要求。因此，可能会出现缓冲池大小的实际分配比配置文件中指定的size要大的情况。

下面举例说明如何在线调整缓冲池的大小。

查看当前缓冲池的大小，SQL语句如下：

show variables like '%innodb_buffer_pool_size%';

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 402653184 |
+-------------------------+-----------+

查看缓冲池中实例的个数，SQL语句如下：

show variables like 'innodb_buffer_pool_instances';

+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1     |
+------------------------------+-------+

动态修改缓冲池的大小为1000MB，SQL语句如下：

set global innodb_buffer_pool_size=1048576000;

查看警告信息，SQL语句如下：

出现上述警告信息的原因是，设置1000MB不是innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances的倍数，即128MB的倍数。

查看设置缓冲池的进度，SQL语句如下：

查看当前缓冲池的大小，SQL语句如下：

从结果可以看出，缓冲池的大小被设置成了1056964608字节（约1024MB）​，因为1024是128的整数倍，出现了缓冲池大小比配置文件里指定的size还大。

10、MySQL 8.0的新特性——表空间数据加密

在MySQL 8.0中，InnoDB Tablespace Encryption支持对独享表空间的InnoDB数据文件加密，其依赖keyringplugin来进行秘钥的管理。开启加密功能需要启动参数–early-plugin-load。

在PHP的配置文件my.ini中开启–early-plugin-load参数。

　　[mysqld]
　　early-plugin-load=keyring_file.so

启动参数后，查看服务器是否支持加密功能：

创建加密表空间：

11、MySQL 8.0的新特性——跳过锁等待

在MySQL 5.7版本中，SELECT…FOR UPDATE语句在执行的时候，如果获取不到锁，会一直等待，直到innodb_lock_wait_timeout超时。

在MySQL 8.0版本中，通过添加NOWAIT和SKIPLOCKED语法，能够立即返回。如果查询的行已经加锁，那么NOWAIT会立即报错返回，而SKIP LOCKED也会立即返回，只是返回的结果中不包含被锁定的行。

下面通过案例来理解MySQL 8.0版本中如何跳过锁等待，如表所示。

12、常见问题

12.1、如何选择符合要求的存储引擎？

不同存储引擎都有各自的特点，以适应不同的需求，如表所示。为了做出选择，首先需要考虑每一个存储引擎提供了哪些不同的功能。

如果要提供提交、回滚和崩溃恢复能力的事务安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个很好的选择。如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率；如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果。如果只有INSERT和SELECT操作，可以选择Archive引擎，Archive存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive存储引擎非常适合存储归档数据，如记录日志信息可以使用Archive引擎。

使用哪一种引擎要根据需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求。使用合适的存储引擎，将会提高整个数据库的性能。

12.2、如何查看默认存储引擎？

在前面介绍了如何使用SHOW ENGINES语句查看系统中所有的存储引擎，其中包括默认的存储引擎，还可以使用一种直接的方法查看默认存储引擎，输入语句如下：

执行结果直接显示了当前默认的存储引擎为InnoDB。