[大数据]MySQL索引和锁实现原理及优化

存储引擎

先来了解一下存储引擎，因为不同存储引擎索引和锁的实现是不同的。

MySQL存储引擎其实就是对于数据库文件的一种存取机制，如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。因为他是开源的所以就出现了各种各样存储数据的方式因此就出现了很多种储存引擎，例如MyISAM，InnoDB、Memory等等。虽然存储引擎很多，不然常用的就两个分别是：MyISAM和InnoDB。默认MySQL的存储引擎为InnoDB。

• MyISAM：拥有很高的查询速度，但是不支持事务。所以当一张表基本只用于查询并且对事务要求不高，那么我们就可以将表的存储引擎改为MyISAM。在MyISAM中只支持表锁以及非聚簇索引，也就是数据和索引是分开存储的
  • MyISAM支持三种不同的存储结构：静态型、动态型、压缩型
    • 静态型：如果表中所有列的大小固定，也就是不包含像varchar类型的字段时，就会自动使用静态格式。使用静态格式的表的性能很高，因为在维护和访问数据时需要的开销很低。不过占用的空间很大
    • 动态型：如果表中存在变长类型的字段例如varchar，那么就会自动使用动态格式，虽然使用动态格式占用的空间比较小，但是性能会变低，而且更新、删除时会产生碎片，因此需要定期执行optimize table清理碎片
    • 压缩型：需要使用myisampack工具创建，可以对表进行压缩，减少占用的磁盘空间。如果是只读的表那么通常建议将表进行压缩。不过当表压缩后如果要修改数据，那么需要解压后在操作。
  • MyISAM的读法有很多，例如my-z[ei]m、mai-ai-sam
• InnoDB：他是MySQL默认的存储引擎，虽然他的查询性能没有MyISAM好，不过他支持事务、行级锁以及聚簇索引。所以他适用于增删改比较频繁且对事务的要求很高的情况。

索引

索引是一种数据结构，通过索引提升查询速度，，加速表之间的连接等等。类似于书的目录，通过目录，我们可以很快找到对应的内容。不过使用索引后索引也要占用一部分空间。并且当我们对数据进行增删改的时候也需要对索引进行维护。所以索引适用于经常查询但是改动不频繁的数据。

索引的类型

• 索引类型根据功能划分可以分为：

  • 唯一索引：使用唯一索引后索引列的值必须是唯一的，不过允许空值，一张表可有多个唯一索引，如果是组合唯一索引那么要求字段值组合后唯一
  • 主键索引：他是一种特殊的唯一索引，他和唯一索引的区别是，主键索引不允许有空值且一张表中只能有一个主键索引
  • 普通索引：不特殊指定的情况下都是普通索引，允许索引列插入空值或重复值。
  • 全文索引：用于在文本中查找关键词，只适用于文本类型的字段。因为普通索引在进行模糊匹配时效率很低，所以可以通过全文索引用于文本的模糊匹配

• 根据列划分可以分为

  • 单例索引：一个索引只包含一个列。
  • 组合索引：一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则，即使用 where 时条件的顺序要按照建立索引时字段顺序进行排列这样索引才会生效。

• 根据物理存储方式可以分为：

  • 聚簇索引：聚簇索引他是一种存储方式，他的数据和索引是存储在一起的这样找到了索引也就找到了数据，并且聚簇索引的顺序，就是数据在硬盘上的物理顺序。所以通常情况下主键就是默认的聚簇索引
  • 非聚簇索引：非聚簇索引也称为二级索引或辅助索引，非聚簇索引中数据和索引是分开的，索引的顺序和硬盘上的物理顺序没有必然联系，并且非聚簇索引需要通过子叶节点的数据去获取最终的数据，例如InnoDB中非聚簇索引子叶节点中存储的是主键的值，然后再用主键值到主键索引中查询数据（这一步称为回表查询），而MyISAM的子叶节点中存储的是数据的磁盘地址，然后根据磁盘地址获取最终数据

索引的数据结构

索引的数据结构有很多中，例如Hash、B-Tree、B+Tree 。在MySQL的InnoDB中索引默认使用的数据结构为B+Tree

B+Tree由B-Tree优化而来，而B-Tree由平衡二叉树优化而来，而Hash通常不会用来当做索引

• Hash是以键值对进行存储的。Hash当做索引存储时，Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。并且哈希索引不支持组合索引的最左匹配规则。如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

• 平衡二叉树是二叉树的优化版本，在普通二叉树中每个节点最多有2个分叉，左子树和右子树数据顺序为左小右大。但是普通二叉树却很容易出现子树节点向一边倾斜的问题。为了处理这个问题就出现出现了平衡二叉树，平衡二叉树在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，这样不会出现一边子树很高、一边子树很矮的情况了。使用平衡二叉树可以很好的兼容二分查找法。不过由于在平衡二叉树中每个节点只能存储一个元素，所以每个节点都相当于与一次IO操作，如果表中的数据越来越多那么树的高度就会越来越高。从而导致io的次数增加查询性能变差

• B-Tree是平衡二叉树的优化版本，由于在平衡二叉树中IO太频繁了，所以B-Tree主要的目的就是减少IO的次数，降低树的高度。所以在B-Tree中，每个节点会储存多个元素以及元素的子节点的地址并尽可能多的存储数据，这样二叉树就变成了多叉树。通过增加树杈降低树的高度，这样IO的次数就减少了。在B-Tree中每个节点中的元素会包含键和数据，并且元素之间会根据键值排序。这样在查找的过程中可以直接读取节点数据到内存中，然后再内存中对节点中的元素进行比较，虽然比较次数没有减少，但是IO次数却减少了很多。所以使用B-Tree构建索引可以很好的提升查询的效率。不过B-Tree并不支持快速范围查询，在范围查询的情况下需要多次从根节点进行查找，而且由于每个节点中都存储数据，所以当表的字段越多那么每条记录所占的空间就会越大，这样也会导致树变高，从而增加IO次数

  • 

• B+Tree是B-Tree的优化版本。B-Tree和B+Tree的区别是，在B-Tree中所有节点都会存储数据，而在B+Tree中只有子叶节点才会存储数据，并且最底层的子叶节点之间会形成一个双向链表。由于在B+Tree中只有子叶节点才会存储数据，所以非子叶节点可以存储更多的键从而拥有更多的子叶节点，因此B+Tree的树会更矮并且基本不会被表中的列的数量影响。由于子叶节点之间会形成一个双向链表并且只有子叶节点会存储所有的数据，所以他可以很好的支持范围查询，也就是在范围查询时当我们通过B+Tree查找到范围的最大值或最下值之后，我们只需要根据子叶节点的链表向前或先后进行遍历查找就可以了，这样就不需要从根节点遍历了。

  • 

索引实现

MyISAM和InnoDB的索引实现是不同的，并且他们的存储方式也不同

MyISAM

MyISAM的索引都是非聚簇索引，索引和数据文件是分开存储的，.MYD为数据文件 .MYI为索引文件。默认使用B+Tree作为索引结构，在索引中叶子节点中存储的键为索引列的值，数据为索引所在行的磁盘地址。他的主键索引和其他的索引实现也基本相同，只是不同索引的约束不同而已。

InnoDB

在InnoDB中会存在一个聚簇索引和多个非聚簇索引（也可以称为辅助索引或二级索引）。通常聚簇索引就是主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID隐式字段来构建聚簇索引。

在InnoDB中索引和数据文件是存储在一起的，聚簇索引的子叶节点中会存储对应的数据，而其他的非聚簇索引中子叶节点存储的是数据的主键值。所以非聚簇索引访问数据时需要二次查找，也就是需要先根据索引查找到对应的主键值，然后再通过主键值到聚簇索引中查找数据（通过主键值到聚簇索引中查找称为回表查询）

组合索引

索引分为普通的单列索引和组合索引（也可以称为联合索引），单列索引就是把单个字段的值作为索引的键，而组合索引就是把多个字段的值的组合作为索引的键。

在组合索引中会使用最左前缀匹配原则，例如创建组合索引a,b,c，在查找时他首先会根据a字段进行查找，如果a字段相同在根据b字段进行查找，如果a和b都相同才根据c字段进行查找。也就是说创建组合索引(a,b,c)相对于创建了(a)、(a,b)、(a,b,c)三个索引。所以组合索引遵循最左前缀匹配原则。

根据最左匹配原则会出现下面情况，如果表中有主键索引i，组合索引为a,b,c，单列索引d，非索引字段e

• 当where条件中有a字段（不受编写时的顺序影响）那么就会使用组合索引，例如a=1、b=1 and a=2、a=1 and c=3等等
• 当where条件中没有a字段，且查询字段中包含除了主键或组合索引以外的字段，那么就不会使用组合索引。
  • 例如select * from table where b=3 and c=4或select a,d,e from table where b=3 and c=4
  • 这种查询中查询字段包含除了主键和组合索引以外的字段，并且where条件中没有a字段（不符合最左匹配）那么是不会使用组合索引的
• 当where条件中没有a字段，不过查询字段中只有主键或组合索引字段，那么是可以使用组合索引的，并且会使用覆盖索引的优化
  • 例如select i,a,b from table where b=3 and c=4
  • 由于查询字段只包含主键和组合索引字段，即使where条件中没有a字段，那么也会使用组合索引。由于在这种情况下可以使用覆盖索引优化，所以mysql默认会在查询时将where条件的组合索引补全，相当于加了一个条件a=任何值

覆盖索引

覆盖索引并不是一种索引，而是一种优化手段。因为在使用非聚簇索引进行查询的时候，我们只可以获取主键值，之后还需要通过主键值进行回表查询最后获取数据。如果当我们只需要获取索引列的数据，那么通过非聚簇索引查询后就可以拿到结果了，这时就没必要在进行回表查询。这种情况就是覆盖索引。通过覆盖索引避免回表操作能够有效提高查询效率。

为了更有效的使用覆盖索引，在创建索引的时候我们可以根据情况创建更多的组合索引。因为组合索引中可以包含很多字段，如果我们需要频繁的查询某些字段，那么我们可以通过这些字段创建组合索引，那么在查询这些字段的时候就可以使用覆盖索引，避免回表操作，从而提高效率。并且使用组合索引相比于使用一个个的单列索引更节省空间。

创建组合索引后相当于创建了多个索引，所以我们使用的时候并不一定要每次都使用到所有的组合索引列，只需要使用组合索引的时候符合最左匹配原则就可以了。所以通常情况下优先使用组合索引

索引操作

• 查看表中有那些索引

  • show index from  table_name;
    

• 查看某个查询是否使用索引：

  • 格式为EXPLAIN SQL...

  • 

  • 其中主要的字段包括

    • possible_keys：表示查询时，可能使用的索引
    • key：表示实际使用的索引
    • rows：扫描出的行数(估算的行数)
    • Extra：执行情况的描述和说明，需要注意当出现Using index时表示使用了索引覆盖

索引的使用

创建索引的情况

创建索引应该优先创建组合索引，因为组合所以相比于一个个创建索引占用的空间更小，并且更有利于使用覆盖索引优化。其中应该创建索引的情况包括

• 当数据量很大并且经常用于条件匹配的字段（包括查询和修改，修改的时候可以避免将表中所有的记录锁住）或者是经常需要用于表连接的字段应该创建索引。注意普通索引在排序的时候不会被使用，只有主键索引会被使用
• 如果查询的时候只是查询其中的某些字段并且查询很频繁，而且大部分查询字段在查询条件中，这时可以将这些需要查询的字段和查询条件一起创建组合索引。这样就可以有效利用覆盖索引的优化

不应该创建索引的情况

• 如果数据增删改非常频繁，并且不要求查询性能的时候不建议创建索引
• 如果字段的值只有几个特定的值那么不建议创建索引

索引失效的情况

• 索引列采用模糊查询时以%开头
• 在使用组合索引时添加的条件不符合最左原则
• 对索引列进行运算
• 查询条件中含有or操作
• 条件匹配时数据类型不一致，因为隐式类型转换会导致索引失效
• 普通索引使用!=、is null、is not null操作时索引会失效，主键索引没影响。

锁

锁的分类

数据库中锁可以分为很多类型

• 通过锁的模式可以分为
  • 乐观锁
    • 他认为读多写少，遇到并发写的可能性低。每次获取数据的时候都不会加锁，而是在写回数据的时候去判断数据是否被其他线程修改。如果没有修改那么就直接写回，如果修改了那么就重新执行整个流程。
  • 悲观锁
    • 他认为读少写多，遇到并发写的可能性很高，所以每次读写数据都会上锁，这样在读写的这段时间内其他线程无法获取到锁，所以就会被阻塞。在数据库中大部分锁都是悲观锁
• 通过锁的范围可以分为
  • 表锁
    • 他会将整个表锁住。所以可以很好的避免死锁问题，并且开销小、获取锁和释放锁的速度很快。但是锁的粒度太大会导致锁冲突概率高、并发度低
  • 行锁
    • 他只会锁住表中的记录。由于锁的锁的粒度小，所以锁冲突概率低、并发度高。但是加锁慢、开销大，并且会发生死锁
• 从读写角度锁可以分为
  • 共享锁
    • 共享锁也称为读锁，用于对数据进行读取，当事务对数据添加读锁后，其他事务只能对该数据添加读锁。只有等到读锁被全部释放后才能对该数据添加写锁。所以共享锁支持并发读，但是读期间不允许修改
  • 排他锁
    • 排他锁也称为写锁，用于对数据进行修改，当事务对数据添加写锁后，其他事务不能对该数据添加任何其他的锁，只有等到写锁释放后其他事务才可以对该数据进行操作。写锁主要是为了解决在修改数据时，避免其他事务对数据进行操作。
  • 意向锁
    • 意向锁又分为意向共享锁和意向排他锁，之所以出现意向锁的原因是因为，当一个事务对表中的某些记录加了行级排他锁后，那么当前表就无法在添加其他的锁了，不管是表级锁或行级锁。在没有意向锁的情况下如果这个时候我们想判断是否可以给表加锁，那么我们得对表中所有的数据进行判断看是否添加了行级排他锁，这样效率是很低的。而使用意向锁之后我们在添加行级排他锁时就可以在表上把意向排他锁加上，这样判断当前表中是否存在锁时只需要判断表上是否有意向锁就可以了。所以意向锁都是表锁。通常当一个事务需要获取资源的锁定时数据库会自动给这个事务添加对应的意向锁。

其中这些锁之间的兼容关系为，其中第一行为已有锁，第一列为要加的锁：

注意这里的共享锁和排他锁包括行级和表级的共享锁、排他锁

简单概括就是

• 共享锁可以和共享锁兼容，包括意向锁共享锁
• 排他锁不和其他锁任何兼容
• 意向锁和意向锁兼容，不管是意向共享锁还是意向排他锁

在mysql中行锁是基于索引实现的。并且mysql中行锁还可以进一步细分为：记录锁（Record Lock）、间隙锁（Gap Lock）、 Next-Key锁、插入意向锁

• 记录锁
  • 记录锁他会将匹配的记录锁住，使用记录锁的条件是操作必须命中索引并且索引是唯一索引，那么就会使用记录锁
• 间隙锁
  • 顾名思义间隙锁锁住的是索引之间的间隙也就是一个范围，当我们的操作需要加行锁，并且操作中使用了索引不过索引为非唯一索引。以及操作中没有匹配到符合条件的数据。这个时候就会使用间隙锁将这个操作的查找范围的前后两个索引之间的区域锁起来。例如某个字段的数据为1,5,9。我们的操作需要匹配2，这时候肯定匹配不到所以就会将2所在的区域的两个前后两个索引间的范围锁起来也就是1-5这个区域。使用间隙锁可以防止其他事务在这个范围内插入或修改记录，保证记录不会变，从而不会出现幻读现象。并且间隙锁只在可重复读（Repeatable Read 简称 RR）的事务隔离级别下才会使用，MySql默认的事务隔离级别就是可重复读
    • 注意如果匹配的值并不在两个索引之间而在前面或后面（也就是最大索引和最小索引之外），那么就会锁住最大或最小索引到正无穷或负无穷的区域，例如字段数据为1,5,9。我们匹配10，那么就会锁住9-正无穷的区域。同理如果匹配0，那么就会锁住负无穷-1的区域
• Next-Key锁
  • Next-Key锁其实就是记录锁和间隙锁的组合。当我们的操作需要加行锁，并且操作中使用了索引不过索引为非唯一索引。以及操作中查到符合条件的数据。这个时候就会使用Next-Key将匹配到的索引记录以及前后两个索引之间的区域锁起来。例如某个字段的数据为1,5,9，我们的操作需要匹配5，那么他会将1-5，5，5-9的区域锁起来，其中1-5和5-9为间隙锁而5为记录锁，所以他是记录锁和间隙锁的组合。这样做仍然是为了解决幻读的问题，因为字段值为5的数据可能有多条，为了防止在新增一条字段值为5的数据就只能将前后两个索引间的范围锁住。并且Next-Key锁只在可重复读（Repeatable Read 简称 RR）的事务隔离级别下才会使用
• 插入意向锁
  • 插入意向锁其实是一种特殊的间隙锁，用于表示插入的意向，只有在insert操作的时候会用到。这个意向锁和和表级的意向锁完全是两个不同的概念。插入意向锁和间隙锁以及Next-Key锁会有冲突只能使用其中一个。间隙锁是防止其他事务在某个范围插入数据，而在执行insert时会添加插入意向锁。由于他们冲突所以间隙锁就能够阻止数据的插入

这些锁的兼容关系为，其中第一行为已有锁，第一列为要加的锁：

简单概括就是

• 插入意向锁不影响其他事务加其他任何锁。也就是说，一个事务已经获取了插入意向锁，对其他事务是没有任何影响的；但是已经存在间隙锁或Next-Key锁时无法添加插入意向锁
• 间隙锁除了和插入意向锁冲突外，不和其他任何锁冲突
• 记录锁和Next-Key锁相互冲突

加锁场景

一个操作是否需要加锁以及加什么锁，主要根据以下几个条件判断，分别是：

• 数据库的存储引擎

  • mysql中InnoDB 存储引擎支持表锁和行锁，而MyISAM只支持表锁，下面说的都是InnoDB存储引擎

• sql语句

  • SELECT ... 语句正常情况下为快照读，不加锁；
  • SELECT ... LOCK IN SHARE MODE 语句为当前读，加共享行锁；
  • SELECT ... FOR UPDATE 语句为当前读，加排他行锁；
  • 常见的 DML 语句（如 INSERT、DELETE、UPDATE）为当前读，加排他行锁；
  • 常见的 DDL 语句（如 ALTER、CREATE 等）加排他表级锁，且这些语句为隐式提交，不能回滚；

• 数据库的隔离级别，需要加锁进行操作的情况下Mysql的隔离级别和正常的隔离级别有些不同

  • 读未提交（Read Uncommitted）
    • 在这个隔离级别下可以读到未提交的数据。所以这个隔离级别基本不会使用。
  • 读已提交（Read Committed）
    • 只能读到以提交的数据，可以解决脏读，并且在这个隔离级别下他会对当前匹配的数据加记录锁，但是不会添加间隙锁和Next-Key锁。所以可以在事务期间防止数据修改，因此还可以解决不可重复读问题，但是无法解决幻读问题
  • 可重复读（Repeatable Read）
    • 这是mysql默认的隔离级别，在这个隔离级别下他会对当前匹配的数据加记录锁、间隙锁以及Next-Key锁，所以可以在事务期间防止数据修改以及当前数据范围的增删，因此可以解决不可重复读和幻读的问题。
  • 串行化（Serialization）
    • 使用这个隔离级别会使数据库从 MVCC 并发控制退化到基于锁的并发控制，不存在快照读，都是当前读。因此查询的并发性能会很差，通常不建议使用。当然他也可以同时解决脏读、不可重复读和幻读的问题

• where从句中的条件是否使用索引以及索引的类型

  • 如果where条件中的字段存在唯一索引，那么他会添加记录锁
  • 如果where条件中的字段存在索引但不是唯一索引，并且有匹配的记录，那么在读已提交的隔离级别下会对匹配记录添加记录锁，如果在可重复读的隔离级别下会对记录添加Next-Key锁，也就是记录锁和间隙锁的组合
  • 如果where条件中的字段存在索引但不是唯一索引，并且没有匹配的记录，那么在读已提交的隔离级别下不会加锁，而在可重复读的隔离级别下会在匹配条件的前后两个索引之间添加间隙锁
  • 如果where条件中的字段不存在索引，那么在读已提交的隔离级别下会对表中的所有数据加记录锁，在可重复读的隔离级别下会对所有数据加记录锁以及在记录之间加间隙锁。所以在mysql中通过无索引字段操作时，相当于对整个表加表锁，因此对数据的并发操作性能影响是很大的。

测试锁的时候只需要启用两个连接（例如在SQLyog中启动两次，注意在SQLyog中一次连接启动两个窗口是不行的），然后为了不让他自动提交事务我们可以在sql语句前后添加begin和commit，这样只要不执行commit事务就不会提交。

注意请不要搞混了，我们平常执行的查询语句是快照读哦，是不需要加锁的，所以平常写sql的时候不需要关心锁的问题。但如果是SELECT ... LOCK IN SHARE MODE、SELECT ... FOR UPDATE 和增删改操作则需要进行加锁

MVCC

mvcc称为多版本并发控制，在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

在mysql中读操作分为两种

• 当前读
  • 当我们使用SELECT ... LOCK IN SHARE MODE或SELECT ... FOR UPDATE这种语句进行数据读取时采用的就是当前读。他读取的是记录的最新版本。并且读取时还要保证其他并发事务不能修改当前记录，因此会对读取的记录进行加锁。
• 快照读
  • 当我们使用普通的SELECT...语句进行读取操作时，使用的就是快照读，当然前提是事务隔离级别不为串行化，在串行化中都是当前读，所以串行化对数据的并发操作性能影响是很大的。快照读是基于多版本并发控制实现的，多版本并发控制指的就是维护一个数据的多个版本，使得读写操作没有冲突。这样读取数据就不需要加锁了，并且由于快照读其实读取的是数据的某个版本所以还可以解决脏读，幻读，不可重复读等问题，但不能解决更新丢失问题，因为读取的版本可能不是最新版本

MVCC在mysql中的实现主要通过隐式字段，undo日志以及Read View来实现

• 隐式字段
  • 在每行记录中除了我们自定义的字段外，还包括数据定义的隐式字段。其中涉及MVCC的隐式字段有2个分别是：DB_TRX_ID（事务id）、DB_ROLL_PTR（回滚指针）
    • 事务id就是创建这条记录或最后一次修改该记录的事务的ID
    • 回滚指针用于指向这条记录的上一个版本，也就是undo日志中的历史记录
• undo日志
  • undo日志就是重做日志，对于数据的插入、修改或删除都会产生undo日志，在这些日志中记录了数据的变更历史，这些日志在事务回滚或快照读时会被用到
• Read View
  • Read View主要是用来做可见性判断的, 当我们某个事务执行快照读的时候，会根据当前数据库的环境创建一个Read View读视图，把它当做条件用来判断当前事务能够看到哪个版本的数据。简单理解在Read View的数据主要就是执行快照读时当前数据库中活动的事务的事务id集合
• 整体实现流程
  • 由于事务id是有规律的也就是递增的，所以在快照读中获取数据的时候我们可以先获取当前匹配记录的事务id，然后拿着这个id跟Read View中维护的id进行比较
  • 如果这个当前记录的事务id大于Read View中的所有事务id，那么就说明我们进行快照读后数据被修改了，所以这时我们就可以通过记录的回滚指针到undo日志中找上一个版本的数据，然后再进行判断，当然如果上一个版本还是大于Read View中的所有事务id那么就继续向上找
  • 如果当前记录的事务id小于Read View中的所有事务id，那么就说明我们进行快照读后数据没有被修改过，所以直接读取最新的数据就行
  • 如果当前记录的事务id在Read View中事务id的区间，也就是小于Read View中事务id的最大值，大于Read View中事务id的最小值。那么就判断Read View中事务id中是否包含当前记录的事务id。如果包含则说明我们快照读的时候当前记录还在被其他事务修改并且没有提交，所以我们应该获取当前记录的上一个版本，而如果不包含则说明在进行快照读时，数据已经被修改且提交了，直接读取最新数据就行。

在不同事务隔离级别的影响下，快照读的结果是不同的

• 在可重复读（Repeatable Read）的隔离级别下，一个事务中Read View的创建时机是当事务中第一次进行快照读的时候创建Read View，后面的快照读都使用第一次快照读创建的Read View。所以在可重复读中，读取的数据不会被其他事务修改，因此可以避免脏读，幻读，不可重复读的问题。但是会出现读取到的不是最新的数据
• 在读已提交（Read Committed）的隔离级别下，在一个事务中每次进行快照读都会创建一个新的Read View。所以在读已提交的隔离级别下可以看到其他事务提交数据，因此只能避免脏读。

MyISAM的锁

上面说的锁基本都是InnoDB的锁，因为MyISAM是不支持事务的。不支持事务意味着无法回滚数据，并且会出现事务安全中的一系列问题。由于一份数据只允许同时由一个进程操作，所以MyISAM的表也有锁，不过MyISAM只支持表锁，在执行增删改的时候默认会加表级排他锁，而查时会加表级共享锁。但是操作完成后就会立即释放锁，相当于每个操作都是一个事务，而在InnoDB中一个事务里面可以进行多次操作。在MyISAM中如果要测试锁得手动加锁才能测试或在查询时通过sleep测试，因为没有事务所以不能使用begin和commit测试

例如：

• 进程1：

  • -- 给test_myisam表加读锁，如果要加写锁可以将READ改为WRITE
    LOCK TABLE test_myisam READ ;
    
    SELECT * FROM test_myisam ;
    
    --释放当前进程的所有锁
    UNLOCK TABLES ;
    
    -- 或通过sleep测试，注意sleep得在查询字段之后
    SELECT *,SLEEP(10) FROM test_myisam ;
    

• 进程2：

  • -- 由于增删改会加写锁所以会阻塞，不过查询是可以的
    UPDATE test_myisam SET age=30 WHERE id=1;
    

并发插入

MyISAM在加共享锁之后,理论上是不允许同时插入数据的,但是mysql有个系统变量concurrent_insert 用于控制其插入行为(只允许插入,不允许更新等)，可以通过select @@concurrent_insert;查询，他的值包括

• concurrent_insert设置为NEVER (or 0)时,不允许并发插入
• concurrent_insert设置为AUTO(or 1)时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是MySQL 的默认设置
  • 删除表中记录而产生的中间空洞，需要定期使用optimize table table_name[,table_name]来收回空洞
• concurrent_insert设置为ALWAYS (or 2)时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。

在查询时如果需要支持并发插入，那么加锁时得添加READ LOCAL，而不是READ。例如LOCK TABLE test_myisam READ LOCAL;

锁的调度

由于读写操作分别加读锁和写锁，所以如果同时执行读写操作那么他们会以串行的方式进行。并且在MyISAM存储引擎中，写锁的优先级会高于读锁的优先级。也就是当有读请求和写请求时，写请求会优先获得锁（即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁请求之前）。这样当有大量的写请求就会导致查询操作很难获取到锁从而可能一直在阻塞中。这也正是有大量修改操作的表不适合用MyISAM存储引擎的原因。

不过我们可以设置MyISAM的调度行为：

• 通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求优先的权利。
• 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。例如：UPDATE [LOW_PRIORITY] tabel_name SET col1=expr1,col2=expr2,...
• 给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL便暂时将写请求的优先级降低，给读进程一定获得锁的机会，例如SET max_write_lock_count=100

优化

如果一张表有一亿条数据那么优化可以通过这几个方面进行优化

• sql优化和索引：给经常用做条件的列创建索引，如果是InnoDB存储引擎优先使用组合索引，这样可以使用覆盖索引避免回表查询从而提交效率，然后优化sql，在操作时使用到索引。之后在优化操作，查询时可以添加一个范围避免一下查太多数据，例如日志查询我们可以要求一次只允许查询某段时间内的日志。
• 缓存：能使用缓存的地方就使用缓存
• 垂直分表：如果表中有些字段经常查询，有些不怎么查询，可以考虑将冷门字段和热门字段放在不同的表中。或将某些大数据的字段拆分出来单独放在一张表中
• 表分区：默认MySQL中一张表的数据都是存在一个文件里面的，我们可以通过分区让他将表数据存储在不同的分区中。其实就是将一个数据文件拆分成了多个。在分区之后表能够存储的数据会变大很多，而且在分区后我们通过分区列查询只需要扫描某些分区就可以了，这样也可以提高查询效率
• 主从复制：为了提高数据库性能我们可以做主从复制实现读写分离，这样既可以备份数据，提高可用性，充分利用存储引擎的特性还可以对请求进行负载
• 服务化：如果应用是微服务的，那么可以让每个服务连接当前业务的数据库，如需其他数据可以通过服务调用获取其他数据库中的数据，相当于实现垂直分库操作
• 分库分表：如果经过前面的处理优化后依旧无法满足要求，那么就可以对数据进行分库和水平分表。将数据分散到多个表以及多个库中。

当数据量很大的时候，如果创建了索引那么查询的时候查询的效率取决于查询数据的多少，如果查询的数据很少那么也是很快的，如果查询的数据多，哪自然就很慢，所以如果数据很多我们查询的时候通常都需要指定一个范围，例如查询时必须指定一个时间范围。当然当数据量很大那么索引也会很大，而插入的时候由于需要创建索引所以插入时会很慢

当查询的数据很多，分页的起点会影响查询的效率，例如

select * from table_name limit 0,10
-- 耗时0.003秒
select * from table_name limit 1000000,10
-- 耗时7.28秒

这种情况我们可以对分页进行改写

select * from table_name 
where id >=  (select id from table_name limit 1000000, 1)
limit 0,10
-- 耗时0.365秒

改写后子查询是直接获取主键数据所以会很快（使用了主键索引并且会使用覆盖索引优化），而外部查询是根据主键筛选数据，筛选后数据量很小，所以分页就很快了

表分区

MySQL中InnoDB类型的表会生成一个.idb用于存储表数据，和一个.frm用于存储表定义。而MyISAM类型的表会生成.myd文件用于存储数据，.myi用于存储索引和.frm用于存储表定义。当表中的数据越来越多，那么存储数据的文件就会越来越大。这个时候我们可以通过分区partition，将数据存储到多个分区文件中，其实就是将数据文件分为多个文件。

使用分区的好处是，在分区之后如果我们通过分区字段进行数据筛选那么只需要筛选部分分区就可以了这样可以加快筛选速度，同时在涉及sum()和count()这类聚合函数的查询时，可以在每个分区上面并行处理，最终只需要汇总所有分区得到的结果。而且还可以让表存储更多的数据。

不过如果在创建分区的时候表中有主键，那么分区字段中必须包含主键，所以想要使用分区筛选就必须包含主键。如果数据不是特别多使用分区和使用索引的性能基本一致。并且分区并不能替代分表分库，如果想快速提高性能还是得分表分库

分区可以支持多种类型：

• range：基于一个给定的连续区间范围进行分区，主要是基于整数的分区，对于非整形的字段需要利用表达式将其转换成整形
• list：是基于列出的枚举值列表进行分区。
• hash：基于给定的分区个数，将数据分配到不同的分区，hash分区只支持根据整数分区，对于非整数的可以通过表达式进行转换
• key：跟hash分区类似，区别是key分区中分区键不能使用表达式，不过可以支持其他类型（除了blob和text类型之外）。他们都可以有效的分散数据
• columns：他分为range column和list column。跟range分区和list分区类似，只不过他们支持多个字段作为分区键，并且支持多种类型（不支持小数和blob、text）

主从复制

通过主从复制可以实现读写分离，对请求进行负载、对数据进行备份提高可用性等等，并且主从复制是MySQL自带的功能，直接配置就行

主从复制是基于binlog（二进制日志）实现的。开启binlog后数据库他会将所有的操作都记录到binlog中。并且开启主从复制后会额外开启三个线程，分别是：主节点的IO线程以及从节点的IO线程和SQL线程。

• 进行主从复制时从节点会通过IO线程连接主节点，然后请求主节点中binlog文件的内容。当然如果不是首次同步还会发送binlog的名称以及binlog中的位置也就是position。
• 主节点收到请求后就会通过IO线程将binlog对应的内容返回给从节点。并且返回的信息中除了binlog的内容还包括当前同步的binlog的名称以及位置
• 从节点的IO线程收到数据后就会将日志数据写入到relaylog(中继日志)中。当然也会将binlog的文件名和位置记录到master-info文件中以便知道后续从什么地方开始同步
• 之后从节点中的SQL线程会实时监控中继日志内容是否有更新，并解析文件中的SQL语句，在从节点中执行。从而实现数据的同步

主从复制的方式

• 基于SQL语句的复制（statement-based replication，SBR）
  • 基于SQL语句的复制，主节点会把SQL语句写入到binlog中，然后从节点会执行该日志中的SQL
  • 优点：日志文件很小，因此占用空间少，并且可以提高网络传输效率
  • 缺点：某些函数不能被正确复制，例如利用时间戳函数调用当前时间作为时间值，因为由于主从之间的延迟会导致时间值不一致
• 基于行的复制（row-based replication，RBR)
  • 基于行的复制会将行变化的数据记录在日志中，也就是binlog中记录了实际变更的数据
  • 优点：所有的数据变化都是被复制，这是最安全的复制方式。这样就不会出现某些函数无法被复制的问题
  • 缺点：由于记录的是实际变更的数据所以日志文件会很大，从而导致占用空间变大网络传输效率降低。并且不能通过binlog解析获取执行过的sql语句
• 混合模式复制（mixed-based replication，MBR)
  • 他是SQL语句复制和行复制的结合。对于一般的复制使用SQL语句的复制模式，对于SQL语句模式无法复制的操作则使用行复制模式来保存，MySQL会根据执行的SQL语句选择日志保存方式

主从复制方式可以通过binlog_format来进行设置，例如：binlog_format=mixed

GTID复制模式

GTID复制模式是MySQL5.6新增的。在原来基于二进制日志的复制中，从库需要告知主库要从日志的哪个偏移位置开始进行同步，如果指定错误会造成数据的遗漏，从而造成数据的不一致。并且当发生故障，需要主从切换，那么需要找到binlog的偏移位置也就是position，然后将主节点指向新的主节点，相对来说比较麻烦。而通过GTID复制模式就不用再找binlog的position了，他会基于事务自动找到同步的位置

GTID的工作原理为：

• 当一个事务在主节点提交时会产生GTID，并一同记录到binlog日志中
• binlog传输到从节点并存储到从节点的relaylog后会读取这个GTID的值，然后跟本地binlog对比看是否有对应记录。所以MySQL的从节点也需要开启binlog
• 如果有记录，说明该GTID的事务已经执行，从节点会忽略
• 如果没有记录，从节点就会从relaylog中执行该GTID的事务，并记录到binlog中

当然GTID也有缺点，例如：

• 主从库的表的存储引擎必须一致
  • 例如主节点在一个事务中对InnoDB类型的表进行了多次操作，效果是产生一个GTID。当从节点中的表为MyISAM，那么执行这个GTID的第一个语句后就会报错，因为非事务引擎一条sql就是一个事务。
• 不支持create table….select 语句复制
  • 因为这个语句会被拆成create语句和insert语句，实际上会出现两个事务，但是只对应一个GTID
• 对于create temporary table 和 drop temporary table语句不支持

读写分离

启用主从复制之后我们可以让主库用于写操作，从库用于读操作，这样就可以使用读写分离了。在我们程序中使用读写分离，我们可以使用应用层的实现方案，使用应用层的实现方案我们在程序中直接导入相关依赖就可以了，例如sharding-jdbc就支持读写分离，当然也可以使用代理层的实现方案，使用代理层的实现方案后我们连接数据库直接连接代理就可以了，例如Mycat和sharding-proxy。

主从复制的问题

• 处理从库的延迟问题

  • 由于主从复制中主节点会将所有的操作写入binlog，然后binlog更新的内容会被同步到从节点的relaylog中，最后从节点在从relaylog中读取对应的数据进行操作从而实现同步。所以延迟是一定会存在的。无法做到实时的强一致。我们能做的是减少延迟的时间。如果非要强一致可以直接读主库并添加缓存

  • 导致延迟的情况可能包括：

    • 主节点并发太高了，导致从节点处理数据的速度跟不上。因为主节点将操作写入binlog日志是很快的，他只是对一个文件写数据嘛。当然将操作记录写入relaylog也会很快。不过从节点的sql线程执行relaylog中的sql进行操作并非是对一个文件写数据，他需要对表中的数据进行操作，所以这个操作是需要一定时间的。而且从节点的sql线程还是单线程的。所以但主节点并发太高就会出现从节点同步数据跟不上的问题。当然这也是为什么要弄一个relaylog的原因，不然同步的数据就无法快速的传递给从节点，这样就可能出现大量数据的丢失
    • 从节点某个操作出现阻塞从而出现延迟。或者由于网络的原因也会出现延迟

  • 查看主从复制是否出现延迟可以通过show slave status 命令输出的Seconds_Behind_Master参数的值来判断

    • 0，表示主从复制良好；
    • 正值，表示主从已经出现延时，数字越大表示从库延迟越严重

  • 解决办法

    • 通过配置开启从节点sql线程的多线程并行复制。通过并行复制可以让从节点中的SQL线程变为多线程，并行读取 relaylog 中的数据并执行，从而提高执行效率。不过并行复制需要MySQL 5.7.22之后的版本才支持
    • 添加多个从节点，分散读的压力， 从而降低服务器负载
• 主节点宕机后，如何保证主从数据的一致性
  • 采用半同步复制进行主从数据的同步。默认情况下mysql使用的是异步复制的方式，主节点处理事务过程中，将操作写入binlog后就会通知IO线程处理，然后完成事务的提交，不会关心日志记录是否成功发送到任意一个从节点中。所以如果主节点宕机了那么就可能会出现主节点已经提交的事务并没有传递到从节点中，因此就会出现主从数据不一致的情况，此时如果强行将从节点提升为主节点就可能会导致新的主节点上数据不完整。
  • 不过我们可以通过半同步复制解决整个问题。在半同步复制中主节点写入 binlog 日志之后，就会强制立即将数据同步到从节点。必须等至少一个从节点将收到的binlog写入relaylog并返回ack消息后才会认为写操作完成了。虽然半同步复制可以解决从库无法及时同步的问题，但他会对主库的性能造成影响

分库分表

介绍

关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多，即使添加从库、优化索引，做很多操作时性能仍下降严重。此时就要考虑对其进行切分了，切分的目的就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库 ，将数据大表拆分成若干数据表，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

数据切分主要可以分为两种，分别是：分表和分库。而在进行分表和分库的时候我们可以根据需求进行水平拆分或垂直拆分。

• 分表
  • 垂直分表
    • 垂直分表就是将表的字段分成多个表，每个表存储其中一部分字段。这样可以避免查询时查出不需要的数据，以及查询时由于数据太大的从而导致查询效率降低的问题
    • 当一张表中，某些字段经常查询，而某些字段不怎么使用，这个时候我们可以将热门字段和冷门字段拆分成两个表
    • 当表中的大字段查询不是特别频繁时，我们可以将大字段拆分到附表中
  • 水平分表
    • 水平分表就是将表中的数据，根据一定规则拆分到多个表中。这样可以避免因为单表数据量太大导致的性能问题
    • 当一张表的数据量非常大，那么索引数据也会很大此时B+Tree会变高导致IO次数增加索引变慢，而且数据量太大会导致锁冲突的等待时间变长。并且由于插入时需要维护索引当索引很大会严重影响插入的效率。这时候我们就可以考虑对表进行水平分表
• 分库
  • 分表只是对同一个库中的表进行拆分，每个表竞争的还是同一台服务器的资源。而一台服务器的资源是有限的，当数据库中的数据太多导致服务器出现瓶颈时，我们就需要对数据进行分库处理。分库后我们就可以将压力分散到多个服务器中，从而提升整体的负载能力
  • 垂直分库
    • 垂直分库就是根据一定规则（通常根据业务拆分）将不同的表，分布到不同的数据库中，每个数据库放在不同的服务器上。这样就可以避免因为单个服务器导致的性能瓶颈
    • 当进行垂直分库后，我们可以结合微服务使用，每个服务只需连接当前业务的数据库，如需其他数据可以通过服务调用获取其他数据库中的数据
  • 水平分库
    • 水平分库就是将表中的数据，根据一定规则拆分到不同的数据库中，每个数据库放在不同的服务器上。当一张表的数据非常大而且查询非常的频繁，以至于在单个数据库中进行水平分表后还是会因为服务器的性能出现了瓶颈。这个时候我们就可以将数据拆分到不同的数据库中（然后还可以在各个数据库中在进行水平分表）。这样就可以避免因为单个服务器导致的性能瓶颈
    • 其实水平分库和垂直分库类似，只不过垂直分库分散的是不同表，而水平分库分散的是同一张表（水平分表后一张表会被拆分成多张表，只是数据不同而已，然后将这些拆分后的表分散到不同的库中。当然也可以理解为他们分散的都是数据）

使用优先级：

在使用的时候应该优先考虑垂直分表，这个我们在设计表结构的时候就可以做到。然后是水平分表。水平分表后如果是由于服务器导致的瓶颈，我们可以考虑进行垂直分库，当然在垂直分库后每个库中可以进行垂直分表（一般不会在进行垂直分表，在第一次垂直分表后应该就不能在继续分了）和水平分表的操作。如果垂直分库后，在库中某张表的数据非常大而且查询非常的频繁，以至于在单个数据库中进行水平分表后还是会因为服务器的性能出现了瓶颈，那么我们就可以对这个库中的表进行水平分库，将表的数据拆分到其他的库中，其实相当于将水平分表后的表放到其他库中，当然一般不会有怎么夸张的表和数据量。

实现

分库分表的实现大致可以分为两种，分别是：

• 应用层实现
  • 应用层实现的主要思路是重新实现JDBC的API，通过sql解析、sql重写、sql路由等一系列的准备工作获取真正可执行的sql，然后底层再按照传统的方法（比如数据库连接池）获取物理连接来执行sql，最后把数据结果合并起来返回给应用层。这样就可以在不改变业务代码的情况下透明地实现分库分表。因为他是跟应用层绑定的所以无法跨语言
  • 其中sharding-JDBC（新版叫ShardingSphere-JDBC）就是应用层的实现
• 代理层实现
  • 代理层实现的主要思路是在应用和数据库的连接之间搭起一个代理层，应用直接连接代理层，然后代理层负责转发请求到底层的数据库。由于没有跟应用层绑定，所以他可以跨语言
  • 其中Mycat、sharding-proxy就是代理层的实现

其实在应用层面我们也可以自己实现简单的分表和分库，例如我们可以通过微服务并且每个服务连接自己的数据库这样就实现了分库，而分表我们可以自己实现存储过程来进行插入和查询，从而将数据分散在不同的表中。当然如果目前已有的实现满足需求那也就没必要自己去实现了

分库分表后的问题

在进行分库分表后会引入一些新的问题，其中包括：

• 分库分表后自增主键重复问题，解决方法是：

  • 不用自增主键，例如使用uuid
  • 使用分布式id，例如采用雪花算法来生成分布式id
    • 雪花算法是64位的Long型数字，他由1位符号位、41位时间戳（最大可以表示69年）、10位机器节点id（最多支持1024个节点）和12位序列号（用来记录同毫秒内产生的不同id，最大为4095）组成。它能够在分布式环境中保持自增顺序，不过由于使用到了时间戳所以生成的数值会跟系统的时间相关。如果时间回拨可能会出现重复

• 跨库事务问题：当更新内容同时分布在不同库中，就会出现跨库事务问题

  • 解决方案是：
    • 拆分时规划好尽量避免跨库操作
    • 使用MySQL自带的针对跨库的事务一致性方案(XA)，不过性能要比单库的慢10倍左右。他采用2PC协议将提交分成两个阶段。阶段一为准备（prepare）阶段。即所有的参与者准备执行事务并锁住需要的资源。阶段二为提交阶段（commit）。当事务管理器确认所有参与者都准备好了之后，向所有参与者发送commit命令。

• 很多分库分表的实现不支持不同库的表进行关联操作。所以在分库的时候应该尽可能的将有关联的表放在同一个库中，或弄一些全局表每个库都存一份。当然也可以在应用层查询两次然后再把数据进行拼装

• 当然还有很多限制，不同分库分表的实现有不同的限制，例如某些操作符不支持（sharding-JDBC中不支持case when、having、union等等）