MySQL数据库在物联网中的应用及其InnoDB的加锁分析

MySQL数据库做为一款当今世界使用率最高的开源关系型数据库；因其体积小、速度快、总体拥有成本低、开源、稳定、兼容性强等优点，在各行各业都有它的身影。本文就和大家分享一下MySQL数据库在物联网行业中的应用，以及其InnoDB存储引擎的加锁分析。

物联网系统中有大量来源不同、结构不同、产生方式不同、用途不同的数据，这些数据包括设备状态、过程状态、订单状态等，而这些数据又会随着时间而不断变化，这就需要我们拥有及时获取、及时响应、及时展现、二次计算、数据管理的能力。所有的这一切都需要我们有一个强大的物联网数据库系统，首先，这个数据库系统需要存储从大量设备中获取的信息（这也是我们称为物联网原因）；其次，需要具备反应迅速、执行高效的特点。

在物联网底层数据库种类的选择上，根据需求不同，选择的数据库也不同。例如：设备信息的快速存储、查询数据，以及数量庞大的长链接需求，使用非关系型内存数据库redis是很好选择。而大量数据的存储和分析，这时又需要用到HBase。但最重要的业务数据（像订单数据，人员信息等），因这些数据往往涉及到非常复杂的逻辑关系，以及增删改查等逻辑运算，还有日后逻辑分析等，我们必须存储在关系型数据库中。而关系型数据库中MySQL又因核心线程完全多线程，支持常见SQL语句规范，可移植性高，安装简单小巧，运行效率高等特性，成为目前物联网关系性数据库的首选。但有一点需要我们特别注意的是：MySQL数据库在对表数据做增删改查等操作时可能会形成锁。而在生产环境中，数据库中的死锁是绝对禁止的。因为它对我们整个服务的影响是致命的。为避免在生产环境死锁的产生，就需要我们对MySQL及其锁机制有比较深入了解。

接下来就让我们详细了解MySQL及其锁机制：

  1. 一条简单SQL的加锁实现分析

先来看下面两条简单的SQL，他们加锁吗？加的什么锁？

SQL1：select* from t1 where id = 10;

SQL2：delete from t1 where id = 10;

针对这个问题，该怎么回答？我能想象到的答案是：

SQL1：不加锁。因为MySQL是使用多版本并发控制的，读不加锁。
SQL2：对id = 10的记录加写行锁。

判断一条sql是否加锁真的就那么简单吗？当然不是，请看下面的分析。

  1. 影响一条SQL是否加锁的因素有哪些

上边的答案对吗？说不上来。即可能是正确的，也有可能是错误的，已知条件不足，这个问题没有答案。如果让我来回答这个问题，我必须还要知道以下的一些前提，前提不同，我能给出的答案也就不同。要回答这个问题，还缺少哪些前提条件？

  • 前提一：当前系统的隔离级别是什么？
  • 前提二：id列是不是主键？
  • 前提三：id列如果不是主键，那么id列上有索引吗？
  • 前提四：id列上如果有二级索引，那么这个索引是唯一索引吗？
  • 前提五：两个SQL的执行计划是什么？索引扫描？全表扫描？
  • 前提六：数据库存储引擎是什么？支持什么类型的锁？

限篇幅问题，我们将前提六确定为InnoDB存储引擎，否则需要分析的组合数将是我们接要分析组合的N多倍。因为目前MySQL默认存储引擎为InnoDB，下面将为大家重点介绍一下InnoDB和其它几种存储引擎。

MySQL存储引擎InnoDB的优点

1、它遵循ACID模式设计，具有事务（Transactions），回滚和保护用户数据的崩溃恢复能力。

2、InnoDB 提供行级锁（Locking on row level），拥有与Oracle类似的不加锁读取（Non-locking read in SELECTs）。InnoDB锁定在行级，并在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户并发性和性能表现。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。

3、InnoDB表基于主键在磁盘上安排数据，有优化的常见查询功能。每一个InnoDB表都有一个叫做聚集索引的主键索引，能尽可能减少数据查询次数。

4、为了维护数据完整性，InnoDB还支持外键完整性约束。

5、可以与其它MySQL存储引擎混合使用InnoDB表。例如，能用一个结合操作在一个单独查询中综合源自MyISAM和InnoDB表的数据。

6、InnoDB是针对提高CPU效率而设计的，并且在处理大数据时表现最佳。

MySQL其它存储引擎的特点和局限性

1、MyISAM不支持事务，不支持行级锁，支持表锁，支持全文索引，最大的缺陷是崩溃后无法安全恢复。

2、Memory存储引擎将表中数据放在内存中，因此速度非常快，但因其支持表锁，所以并发性能较差，最糟糕的是这个存储引擎在数据库重启或崩溃之后表中的数据将全部丢失，它只适用于存储临时数据的临时表，MySQL中一般使用这个存储引擎来存放查询的中间结果集，如MySQL自带的默认的information_schema库中就存在较多使用Memory存储引擎的表

3、Archive存储引擎置只支持INSERT和SELECT操作，支持行锁，但本身并不是事务安全的存储引擎，其最大的优点是其具有较好的压缩比，压缩比一般可达到1:10，可以将同样的数据以更小的磁盘空间来存储。

MySQL/InnoDB定义的4种隔离级别

•Read Uncommited
可以读取未提交记录。此隔离级别，不会使用。

•ReadCommitted (RC)
针对当前读，RC隔离级别保证对读取到的记录加锁(记录锁)，存在幻读现象。

•RepeatableRead (RR)
针对当前读，RR隔离级别保证对读取到的记录加锁(记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入(间隙锁)，不存在幻读现象。

•Serializable
所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。

MySQL/InnoDB索引的类别

MySQL索引分类:

FULLTEXT
即为全文索引，目前只有MyISAM引擎支持。（MySQL的全文索引建立的文件还是比较大的，而且效率不是很高，即便是使用了中文分词插件，对中文分词支持也只是一般。）

hash索引
目前就只有MEMORY引擎显式地支持这种索引，不能使用范围查询。在处理较小数据量时具有无可比拟的速度优势，所以hash索引很适合做缓存（内存数据库）

BTREE索引
是一种将索引值按一定的算法，存入一个树形的数据结构中。（BTREE这种MySQL默认的索引类型，具有普遍的适用性）

RTREE
RTREE在MySQL很少使用，仅支持geometry数据类型。
相对于BTREE，RTREE的优势在于范围查找.

InnoDB里只支持BTREE索引，有两种形态：

一是primary key形态，其leafnode里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。

二是secondary index（包括唯一索引和普通索引），其leafnode和普通的BTREE差不多，只是还存放了指向主键的信息.

查询组合及分析

  • 组合一：id列是主键，RC隔离级别
  • 组合二：id列是二级唯一索引，RC隔离级别
  • 组合三：id列是二级非唯一索引，RC隔离级别
  • 组合四：id列上没有索引，RC隔离级别
  • 组合五：id列是主键，RR隔离级别
  • 组合六：id列是二级唯一索引，RR隔离级别
  • 组合七：id列是二级非唯一索引，RR隔离级别
  • 组合八：id列上没有索引，RR隔离级别
  • 组合九：Serializable隔离级别

注：上面的这些组合，我做了一个前提假设，也就是有索引时，执行计划一定会选择使用索引进行过滤(索引扫描)。但实际情况会复杂很多，真正的执行计划，还是需要根据MySQL输出的为准。

排列组合还没有列举完全，但是看起来，已经很多了。真的有必要这么复杂吗？事实上，要分析加锁，就是需要这么复杂。但是从另一个角度来说，只要你选定了一种组合，SQL需要加哪些锁，其实也就确定了。接下来，就让我们来逐个分析这9种组合下的SQL加锁策略。

组合一：id主键+RC

这个组合，是最简单，最容易分析的组合。id是主键，Read Committed隔离级别，给定SQL：delete from t1 where id = 10; 只需要将主键上，id= 10的记录加上X锁即可。如下图所示：

结论：id是主键时，此SQL只需要在id=10这条记录上加X锁即可。

组合二：id唯一索引+RC

这个组合，id不是主键，而是一个Unique的二级索引键值。那么在RC隔离级别下，delete from t1 where id = 10; 需要加什么锁呢？见下图：

此组合中，id是unique索引，而主键是name列。此时，加锁的情况由于组合一有所不同。由于id是unique索引，因此delete语句会选择走id列的索引进行where条件的过滤，在找到id=10的记录后，首先会将unique索引上的id=10索引记录加上X锁，同时，会根据读取到的name列，回主键索引(聚簇索引)，然后将聚簇索引上的name= ‘d’ 对应的主键索引项加X锁。为什么聚簇索引上的记录也要加锁？试想一下，如果并发的一个SQL，是通过主键索引来更新：update t1 set id = 100 wherename = ‘d’; 此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

组合三：id非唯一索引+RC

相对于组合一、二，组合三又发生了变化，隔离级别仍旧是RC不变，但是id列上的约束又降低了，id列不再唯一，只有一个普通的索引。假设delete from t1 where id = 10; 语句，仍旧选择id列上的索引进行过滤where条件，那么此时会持有哪些锁？同样见下图：

根据此图，可以看到，首先，id列索引上，满足id= 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。

组合四：id无索引+RC

相对于前面三个组合，这是一个比较特殊的情况。id列上没有索引，whereid = 10;这个过滤条件，没法通过索引进行过滤，那么只能走全表扫描做过滤。对应于这个组合，SQL会加什么锁？或者是换句话说，全表扫描时，会加什么锁？这个答案也有很多：有人说会在表上加X锁；有人说会将聚簇索引上，选择出来的id= 10;的记录加上X锁。那么实际情况呢？请看下图：

若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQLServer层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

组合五：id主键+RR& 组合六：id唯一索引+RR

组合五，id列是主键列，Repeatable Read隔离级别，针对delete from t1 where id = 10; 这条SQL，加锁与组合一：[id主键，Read Committed]一致。

与组合五类似，组合六的加锁，与组合二：[id唯一索引，Read Committed]一致。两个X锁，id唯一索引满足条件的记录上一个，对应的聚簇索引上的记录一个。

组合七：id非唯一索引+RR

还记得前面提到的MySQL的四种隔离级别的区别吗？RC隔离级别允许幻读，而RR隔离级别，不允许存在幻读。但是在组合五、组合六中，加锁行为又是与RC下的加锁行为完全一致。那么RR隔离级别下，如何防止幻读呢？问题的答案，就在组合七中揭晓。

组合七，RepeatableRead隔离级别，id上有一个非唯一索引，执行deletefrom t1 where id = 10; 假设选择id列上的索引进行条件过滤，最后的加锁行为，是怎么样的呢？同样看下面这幅图：

RepeatableRead隔离级别下，id列上有一个非唯一索引，对应SQL：deletefrom t1 where id = 10; 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。

组合八：id无索引+RR

组合八，RepeatableRead隔离级别下的最后一种情况，id列上没有索引。此时SQL：deletefrom t1 where id = 10; 没有其他的路径可以选择，只能进行全表扫描。最终的加锁情况，如下图所示：

如图，这是一个很恐怖的现象。首先，聚簇索引上的所有记录，都被加上了X锁。其次，聚簇索引每条记录间的间隙(GAP，也同时被加上了GAP锁。这个示例表，只有6条记录，一共需要6个记录锁，7个GAP锁。试想，如果表上有1000万条记录呢？

在RepeatableRead隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发更新/删除/插入操作。

组合九：Serializable

针对前面提到的简单的SQL，最后一个情况：

Serializable隔离级别。对于SQL2：deletefrom t1 where id = 10; 来说，Serializable隔离级别与RepeatableRead隔离级别完全一致，因此不做介绍。

Serializable隔离级别，影响的是SQL1：select* from t1 where id = 10; 这条SQL，在RC，RR隔离级别下，都是快照读，不加锁。但是在Serializable隔离级别，SQL1会加读锁。

结论：在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

总结：

  • 前面的部分，基本上已经涵盖了MySQL/InnoDB所有的加锁规则。深入理解MySQL如何加锁，有两个比较重要的作用：
  • 可以根据MySQL的加锁规则，写出不会发生死锁的SQL；
  • 可以根据MySQL的加锁规则，定位出线上产生死锁的原因；
  • 死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。而使用本文上面提到的，分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因。