MySQL高级-InnoDB存储引擎

本文使用MySQL版本为8.0.11

1.概述

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

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

本文主要讲解InnoDB存储引擎。

2.InnoDB存储引擎简介

        InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁和外键。MySQL5.5之后，InnoDB作为默认存储引擎，主要特性如下：

        1.InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在select语句中提供了一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表与其他的MySQL表的类型混合起来，甚至在同一个查询中也可以混合。

        2.InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。

        3.InnoDB存储引擎完全与MySQL服务器整合，为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引存在一个逻辑表空间中，表空间可以包含数个文件。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。

        4.InnoDB支持外键完整性约束。存储表的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6B的rowid，并以此作为主键。

        5.InnoDB被用在众多需要高性能的大型数据库站点上。InnoDB不创建目录，使用InnoDB时，MySQL将在数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile和ib_longfile1的5MB大小的日志文件。

3.MySQL的锁机制

        MySQL与其他数据库在锁机制方面最大的不同之处在于，对于不同的存储引擎支持不同的锁机制。例如InnoDB存储引擎支持行级锁，也支持表级锁，默认采用行级锁。总的来说，MySQL存储引擎使用了3种级别的锁机制，行级锁、页级锁、表级锁。

        3.1 行级锁

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

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

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

        （1）共享锁（S）:允许一个事务读一行数据时阻止其他的事务读取相同数据的排他锁。

        （2）排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务取得相同数据的共享锁和排他锁。

        （3）意向共享锁（IS）：事务打算给数据行加行共享锁。事务再给一个数据行加共享锁前必须先取得该表的IS锁。

        （4）意向排他锁（IX）：事务打算给数据行加行排他锁。事务在给一个数据行加排他锁前必须先取得该表的IX锁。

        上面这4种锁的共存逻辑关系如下图：

        如果一个事务请求的锁模式与当前的锁模式兼容，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

  3.2 表级锁

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

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

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

        （1）Current read lock queue(lock -> read)。

        （2）Padding read lock queue(lock -> read wait)。

        （3）Current write lock queue(lock -> write)。

        （4）Padding write lock queue(lock -> write wait)。

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

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

 

3.3 页级锁

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

        在数据库实现资源锁定的过程中，锁机制的粒度越小，数据库实现的算法越复杂，数据库消耗的内存越大。不过，随着锁机制粒度越来越小，应用的并发发生锁等待的概率也越来越小，系统整体性能会随之提高。MySQL是用写队列和读队列来完成对数据库的读写操作，所以说MySQL数据库存在读锁和写锁的概念。对于写锁而言，如果表没有加锁，就对其表加写锁；如果表已经加锁，此时会将写操作的请求放入写锁的队列中。对于读锁而言，如果没有加入读锁，那么请求会加入一个读操作的锁，其他读操作的请求会放到读锁的队列中。

        下面通过简单的例子来说明读写操作。

【例3.3.1】1、创建表content并插入数据：

create table content(
id int,
content VARCHAR(20)
);
insert into content VALUES(1,'wangfei');

        2、向content里面插入大量的数据，数据越多，效果越明显：

insert into content select * fron content;

         3、此时，准备工作已经完成，根据下图运行读写操作，理解MySQL读写队列运行的过程：

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

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

4.InnoDB锁机制

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

4.1 InnoDB行级锁模式

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

        （1）原子性（Atomicity）：事务具有原子不可分割的特性，要么一起执行，要么都不执行。

        （2）一致性（Consistency）：在事务开始和事务结束时，数据都保持一致状态。

        （3）隔离性（Isolation）：在事务开始和结束过程中，事务保持一定的隔离特性，保证事务不受外部并发数据操作的影响。

        （4）持久性（Durabilitu）：事务完成后，数据将会被持久化到数据库中。

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

        （1）更新丢失：两个事务更新同一行数据，但是第二个事务却中途失败推出了，导致对两个修改都失效，这时系统没有执行任何锁操作，因此并发事务并没有被隔离。

        （2）脏读：一个事务读取了某行数据，但是另一个事务已经更新了这行数据，这是非常危险的，很可能所有的曹组被回滚。

        （3）不可重复读：一个事务对一行数据重复读取两次，可是得到不同的结果。在两次读取数据的中途，有可能存在另外一个事务对数据进行了修改。

        （4）幻读：事务在操作过程中进行了两次查询，第二次查询结果包含了第一次没有出现的数据。出现幻读的主要原因是，两次查询过程中另一个事务插入新的数据。

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

        数据库隔离级别包括未提交读、已提交读、可重复读、可序列化、事务的隔离级别越严格，并发副作用越小，但是付出的代价越大。这4中隔离级别比较如下：

        InnoDB存储 引擎实现了4 种锁，分别是共享锁、排他锁、意向共享锁、意向排他锁。

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

        （1）设置autocommit的值是OFF或0.

        （2）表的数据引擎是支持事务的，比如InnoDB数据引擎。

        （3）如果不管autocommit，手动在事务里执行操作，这时要使用begin或者start  transaciton开始事务。

        （4）不要在锁定事务规定的时间外使用共享锁和排他锁。

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

        

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

        使用InnoDB存储引擎共享锁的过程如下图：

        下面是InnoDB使用排他锁的例子：

4.2 获取InnoDB行级锁的争用情况

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

show status like '%innodb_row_lock%';

        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提供的这些系统状态的分析，制定相应的优化计划，尤其是当等待次数比较高，而且每次等待时间比较长的时候，需要分析系统出现这种情况的原因。

       

4.3 InnoDB行级锁的实现方法

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

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

        

         从结果可以看出，在表t_innodb_no_index没有索引的情况下，InnoDB使用的只是表级锁。下面通过案例来理解包含索引的表锁定的例子，具体过程如下：

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

 

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

 4.4 间隙锁（Net_Key锁）

        在更新InnoDB存储引擎表中的某个区间数据时，将会锁定这个区间的所有记录。例如:

update xxx where id between 1 and 100

        就会锁住id从1到100之间所有的记录。值得注意的是，在这个区间中假设某条记录并不存在，该条记录也会被锁定，这时，如果另外一个Session往这个表中添加了一条记录，就必须要啊等到上一个事务释放锁资源。

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

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

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

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

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

4.6 InnoDB存储引擎中的死锁

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

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

步骤1：创建测试表innodb_dead_lock，插入测试数据，然后添加索引dead_index_id:

create table innodb_dead_lock(
	id int,
	data varchar(20)
)ENGINE=INNODB;
insert into innodb_dead_lock values(1,'data1');
insert into innodb_dead_lock values(2,'data2');

create index dead_index_id on innodb_dead_lock(id);

步骤2：执行过程如下：

 

4.7 InnoDB行级锁优化建议

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

（1）尽量控制事务大小，减少锁定的资源量和锁定的时间长度。

（2）尽可能让所有的数据检索都通过索引来完成，从而避免因为无法通过索引加锁而升级为表级锁。

（3）尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁的记录。

（4）在业务允许的情况下，尽量使用较低级别的事务隔离，以减少因为事务隔离级别锁带来的附加成本。

（5）合理使用索引，让InnoDB在索引上面加锁的时候更加准确。

（6）在应用中，尽可能按照相同的访问顺序来访问，防止产生死锁。

（7）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少产生死锁的概率。

（8）对于容易产生死锁的业务，可以放弃使用InnoDB行级锁，尝试使用表级锁来减少死锁产生的概率。

（9）不要申请超过实际需要的锁级别。