2019年5月

MySQL技术内幕(InnoDB存储引擎概述)学习笔记05-性能调优

选择合适的CPU

首先需要弄清楚当前数据库的应用类型。一般而言,可分为两大类 : OLTP(Online Transaction Processing)和OLAP(Online Analytical Processing,在线分析处理)。这是两种截然不同的数据库应用。OLAP多用在数据仓库或数据集市中,一般需要执行复杂的SQL语句来进行查询;OLTP多用在日常的事务处理应用中国,如银行交易,在线商品交易,Blog,网络游戏等。相对于OLAP,数据库的容量较小。

InnoDB一般应用于OLTP的数据库应用,这种应用的特点如下

  • 用户操作的并发量大
  • 事务处理的时间一般比较短
  • 查询的语句较为简单,一般走索引
  • 复杂的查询少

可以看出,OLTP应用本身对CPU的要求不是很高,因为复杂的查询可能需要执行比较,排序,连接等非常耗CPU的操作,这些操作在OLTP的数据库应用中较少发生。因此,可以说OLAP是CPU密集型的操作,而OLTP是IO密集型的操作,建议在采购设备时,将更多的注意力放在提高IO的配置上。

此外,为了获得更多内存的支持,用户采购的CPU必须支持64位,否则无法支持64位操作系统的安装.

从InnoDB的设计架构上看,其主要的后台操作都是在一个单独的master thread中完成的,因此并不能很好地支持多核应用。当然,开源社区已经通过多种办法来改变这种局面,而InnoDB 1.0版本在各种测试环境下已经显示出对多核CPU的处理性能有了极大的提高,而InnoDB 1.2版本又支持多个purge线程,以及将刷新操作从master thread中分离出来。因此,如果用户的CPU支持多核,InnoDB的版本应该选择1.1或更高版本。另外,如果CPU是多核的,可以通过修改参数innodb_read_io_threads和innodb_write_io_threads来增大IO的线程,这样也能更充分有效地利用CPU的多核性能。

在当前的MySQL数据库版本中,一条SQL查询语句只能在一个CPU中工作,并不支持多核CPU的处理。OLTP的数据库应用一般操作都很简单,因此对于OLTP的应用影响不是很大。但是,多个CPU或多核CPU对处理大并发量的请求还是会有帮助。

内存的重要性

内存的大小最能直接反映数据库的性能。通过之前的学习,已经了解到InnDB即缓存数据,又缓存索引,并且将他们缓存于一个很大的缓冲池中,即InnoDB Buffer Pool。因此,内存的大小直接影响了数据库的性能。

判断内存是否达到了瓶颈

参数说明
innodb_buffer_pool_reads标识从物理磁盘读取页的次数
innodb_buffer_pool_read_ahead预读的次数
innodb_buffer_pool_read_ahead_evivted预读的页,但是没有被读取就从缓冲池中被替换的页的数量,一般用来判断预读的效率
innodb_buffer_pool_read_requests从缓冲池中读取页的次数
innodb_data_read总共读入的字节数
innodb_data_reads发起读取请求的次数,每次可能需要读取多个页

缓冲命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_ahead + innodb_buffer_pool_reads)
命中率越高,性能越高

平均每次读取的字节数 = innodb_data_read / innodb_data_reads

注意,即使缓冲池的大小已经大于数据库文件的大小,也并不意味着没有磁盘操作。数据库的缓冲池只是用来存放热点的区域,后台的线程还负责将脏页异步的写入到磁盘。此外,每次事务提交时还需要将日志写入到重做日志中。

硬盘对数据库性能的影响

传统机械硬盘

机械硬盘有两个重要的指标 : 寻道时间和转速。传统机械硬盘最大的问题在于读写磁头,读写磁头的设计使硬盘可以不再像磁带一样,只能进行顺序访问,而是可以随机访问。但是,机械硬盘的访问需要耗费长时间的磁头旋转和定位来查找,因此顺序访问的速度远远高于随机访问。传统关系型数据库的很多设计也是在尽量充分地利用顺序访问的特性。

通常来说,可以将多块机械硬盘组成RAID来提高数据库的性能,也可以将数据文件分布在不同硬盘上来达到访问负载的均衡。

固态硬盘

固态硬盘,更准确地说是基于闪存的固态硬盘,是近几年出现的一种新的存储设备,其内部由闪存(Flash Memory)组成。因为闪存的低延迟性,低功耗,以及防震性,闪存设备已经在移动设备上得到了广泛的应用。

不同于传统的机械硬盘,闪存是一个完全的的电子设备,没有传统机械硬盘的读写磁头。因此,固态硬盘不需要像传统机械硬盘一样,需要耗费大量时间的磁头旋转和定位来查找数据,所以固态硬盘可以一致的随机访问时间。

另一方面,闪存中的数据是不可以更新的,只能通过扇区(sctor)的覆盖重写,而在覆盖重写之前,需要执行非常耗时的擦除操作(erase)操作。擦除操作不能在所含数据的扇区上完成,而需要在删除整个被称为删除块的基础上完成,这个擦除块的尺寸大于扇区的大小,通常为128KB或256KB。此外,每个擦除块有擦写次数的限制。已经有一些算法来解决这个问题。但是对于数据库应用,需要认真考虑固态硬盘在写入方面存在的问题。

因为存在上述写入方面的问题,闪存的读写速度是非对称的。读取速度要远快于写入的速度,因此对于固态硬盘在数据库中的应用,应该好好利用其读取的性能,避免过度的写入操作。

由于闪存是一个完全的电子设备,没有读写磁头等移动部件,因此固态硬盘有着较低的访问延时。当主机发布一个读写请求时,固态硬盘的控制机会把I/O命令从逻辑地址映射成实际的物理地址,写操作还需要修改相应的映射表信息。算上这些额外的开销,固态硬盘的访问延时一般小于0.1ms左右。

对于固态硬盘在InnoDB中的优化,可以增加innodb_io_capacity变量的值达到充分利用固态硬盘带来的高IOPS特效。不过这需要用户根据自己的应用进行针对性的调整。在InnoDB 1.2版本中,可以选择关闭邻接页刷新,同样会为数据库的性能带来一定效果的提升。

合理的设置RAID

RAID类型

RAID(Redundant Array of Independent Disks,独立磁盘冗余数组)的基本思想就是把多个相对便宜的硬盘组合起来,成为一个磁盘数组,使性能达到升值超过一个价格昂贵,容量巨大的硬盘。由于将多个硬盘组合成为一个逻辑扇区,RAI看起来就像一个单独的硬盘或逻辑存储单元,因此操作系统只会把他当做一个硬盘。

RAID的作用是 :

  • 增加数据集成度
  • 增加容错功能
  • 增加处理量或容量

根据不同磁盘的组合方式,常见的RAID组合方式可分为RAID 0,RAID 1,RAID 5,RAID 10和RAID 50等。

RAID 0: 将多个磁盘合并成一个大的磁盘,不会有冗余,并行I/O,速度最快。 RAID 0亦称为带区集,他将多个磁盘并列起来,使之成为一个大磁盘。在存放数据时,其将数据按磁盘的个数进行分段,同时将这些数据写进这些盘中。所以,在所有级别中,RAID 0的速度是最快的。但是RAID 0没有冗余功能,如果一个磁盘(物理)损坏,则所有的数据都将丢失。理论上,多磁盘的性能等于 : 单一磁盘效能 * 磁盘数,但实际上受限于总线I/O瓶颈及其他因素的影响,RAID效能会随边际递减。也就是说,假设一个磁盘的效能是50MB/s,两个磁盘的RAID 0效能约为96MB/s,三个磁盘的RAID 0也许就是130MB/s而不是150MB/s了。

RAID 1 : 两组以上的N个磁盘相互作为镜像,在一些多线程操作系统中能有很好的读取速度,但写入速度略有降低。除非拥有相同数据的主磁盘和镜像同时损坏,否则只要有一个磁盘正常即可维持运作,可靠性最高。RAID 1就是镜像,其原理为在主硬盘上存放数据的同时也在镜像硬盘上写相同的数据。当主硬盘(物理)损坏时,镜像硬盘则代替主硬盘的工作。因为镜像硬盘做数据备份,所以RAID 1在所有的RAID级别上来说是最好的。但是,无论使用多少磁盘作为RAID 1,仅算·一个磁盘容量,是所有RAID级别中使用率最低的级别。

RAID 5 : 是一种存储性能,数据安全和存储成本兼顾的存储解决方案。它使用的是Disk Striping(硬盘分区)技术。RAID 5至少需要三个硬盘,RAID 5不对存储的数据进行备份,而是将数据和相对应的奇偶校验信息存储于不同的磁盘上。当RAID 5的一个磁盘数据发生损坏后,利用剩下的数据和相应的奇偶校验信息去恢复被损坏的数据。RAID 5可以理解为是RAID 0和RAID 1的折中方案。RAID 5可以为系统提供数据安全保障,但保障程度比镜像低而磁盘利用率比镜像高。RAID 5具有和RAID 0相近似的数据读取速度,只是多了一个奇偶校验信息,写入数据的速度相当慢,若使用Write Back可以让性能改善不少,同时,由于多个数据对应一个奇偶校验信息,RAID 5的磁盘空间利用率要比RAID 1高,存储成本相对较低。

RAID 10和RAID 01 : RAID 10是先镜像再分区数据,将所有硬盘分为两组,视为RAID 0的最低组合,然后将这两组各自视为RAID 1运作。RAID 1-有着不错的读取速度,而且拥有比RAID 0更高的数据保护性。RAID 01则与RAID 10的程序相反,先分区再将数据镜射到两组硬盘。RAID 01将所有的硬盘分为两组,变成RAID 1的最低组合,而将两组硬盘各自视为RAID 0运作。RAID 01比RAID 10有着更快的读写速度,不过也多了一些会让整个磁盘组停止运转的几率,因为只要同一组的硬盘全部损毁,RAID 01就会停止运作,而RAID 10可以在牺牲RAID 0的优势下正常运作。RAID 10巧妙的利用了RAID 0的速度及RAID 1的安全(保护)两种特性,他的缺点的是需要更多的磁盘,因为至少必须有四个以上的偶数磁盘才能使用。

RAID 50 : RAID 50也被称为镜像阵列条带,由至少六块磁盘组成,像RAID 0一样,数据被分区成条带,在同一时间内向多块磁盘写入;像RAID 5一样,也是以数据的校验位来保证数据的安全,且检验条带分布在各个磁盘上,其目的在于提高RAID 50的读写性能。

对于数据库应用来说,RAID 10是最好的选择,它同时兼顾了RAID 1和RAID 0的特性,但是当一个磁盘失效时,性能可能会受到很大的影响,因为条带(strip)会成为瓶颈

RAID Write Back功能

RAID Write Back功能是指RAID控制器能够将写入的数据放入自身的缓存中,并把他们安排到后面再执行。这样做的好处是,不用等待物理磁盘实际写入的完成,因此写入变得更快了。对于数据库来说,这显得十分重要。例如,对重做日志的写入,在将sync_binlog设为1的情况下二进制日志的写入,脏页的刷新等性能都能得到明显的提升。

但是,当操作系统或数据库宕机时,Write Back功能可能会破坏数据库的数据,这是由于已经写入的数据可能还在RAID卡的缓存中,数据可能没有完全写入磁盘,而这时故障发生了。为了解决这个问题,目前大部分的硬件RAID卡都提供了电池备份单元(BBU,Battery Backup Unit),因此可以放心的开启Write Back的功能

操作系统的选择

Linux是MySQL数据库服务器最常使用的操作系统。与其他操作系统不同的是Linux拥有众多的发行版本,每个用户的偏好可能不尽相同。然而在将Linux作为数据库服务器时需要考虑更多的是操作系统的稳定性,而不是新特性。

除了Linux操作系统外,FreeBSD也是另一个常见的优秀操作系统。之前版本的FreeBSD对MySQL数据库支持不是很好,需要选择单独的线程库进行手动编译。新版的直接安全即可。

Solaris也是非常不错的操作系统,之前是基于SPARC硬件的操作系统,现在已经移植到了X86平台上。Solaris是高性能,高可靠性的操作系统,
。。。略。。。

后面的内容不是这次的目标,就略过了。。。

MySQL技术内幕(InnoDB存储引擎概述)学习笔记04-事务

概述

事务(Transaction)是数据库区别于文件系统的重要特性之一。在文件系统中,如果正在写文件,但是操作系统突然崩溃了,这个文件就很可能被破坏。当然有一些机制可以把文件恢复到某个时间点。不过,如果需要保证两个文件同步,这些文件系统可能就无能为力了。例如,在需要更新两个文件时,更新完一个文件后,在更新完第二个文件之前系统重启了,就会有两个不同的文件。

这正是数据库系统引入事务的主要目的 : 事务会把数据库从一种一致状态转换成另一种一致状态。在数据库提交工作时,可以确保要么所有修改都已经保存了,要么所有的修改都不保存。

InnoDB中的事务完全符合ACID特性。

  • 原子性(atomicity)
  • 一致性(consistency)
  • 隔离性(isolation)
  • 持久性(durability)

本章主要关注事务的原子性这一概念,并说明正确使用事务即编写正确的事务应用程序,避免在事务方面养成一些不好的习惯。

认识事务

事务可由一条简单的SQL语句组成,也可以由一组复杂的SQL语句组成。事务是访问并更新数据库中数据项的一个程序执行单元。在事务中的操作,要么都成功,要么都不成功,这就是事务的目的,也是事务模型区别于文件系统模型的重要特征之一。

理论上说,事务有着极其严格的定义,他必须同时满足四个特性,即通常所说的事务的ACID特性。值得注意的是,虽然理论上定义了严格的事务要求,但是数据库厂商处于各种目的,并没有严格的去满足ACID标准。例如,对于MySQL的NDB Cluster引擎来说,虽然其支持事务,但是不满足D的要求,即持久性的要求。对于Oracle数据库来说,其默认的事务隔离级别为READ COMMITTED,不满足I的要求,即隔离性的要求。虽然在大多数情况下,这并不会导致严重的后果,甚至可能带来性能的提升,但是用户首先需要知道严谨的事务标准,并在实际的生产应用中避免可能存在的潜在问题。对于InnoDB存储引擎而言,其默认的事务隔离级别为READ REPEATABLE,完全遵循和满足ACID特性。下面具体介绍下事务的ACID特性,并给出相关概念。

A(Atomicity),原子性。在计算机系统中,每个人都将原子性视为理所当然。例如在C语言中调用SQRT函数,要么返回正确的平凡根值,要么返回错误的代码,而不会在不可预知的情况下改变任何的数据结构和参数。如果SQRT函数被许多个程序调用,一个程序的返回值也不会是其他程序要计算的平方根。

然而在数据的事务中实现调用操作的原子性,就不是那么理所当然了。例如用户在ATM机前取款,假设取款的流程为

  1. 登录ATM机,验证密码
  2. 从远程银行的数据库中取得账户的信息
  3. 用户在ATM机上输入欲取出的金额
  4. 从远程银行的数据库中更新账户信息
  5. ATM机出款
  6. 用户取钱

整个取款的操作应该视为原子操作,即要么都做,要么都不做。不能用户钱未从ATM机上获得,但是银行卡上的钱已经被扣除了。

原子性是整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功,才算成功。事务中任何一个SQL语句失败,已经执行成功的SQL语句也必须撤销,数据库状态应该退回到执行事务前的状态。

如果事务中的操作都是只读的,要保持原子性其实是很简单的。一旦发生任何错误,要么重试,要么返回错误代码。因为只读操作不会改变系统中的任何相关部分。但是当事务中的操作需要改变系统中的状态时,例如插入或更新记录,那么情况就不一样了。如果操作失败,很有可能会引起状态变化,因此必须保护系统中并发用户访问受影响的部分数据。

C(Consistency),一致性。一致性是指事务将数据库从一种状态转换为下一种一致的状态。在事务开始之前和事务结束之后,数据的完整性约束没有被破坏。例如表中有一个字段为姓名,为唯一约束。如果一个事务对姓名字段进行了修改,但是事务在提交或事务操作发生回滚后,表中的姓名变得非唯一了,这就破坏了事务的唯一性要求,即事务将数据库从一种状态变为了另一种不一致的状态。因此,事务是一致性的单位,如果是事务中某个动作失败了,系统可以自动撤销事务,返回初始化的状态。

I(isolation),隔离性。隔离性还有其他的称呼,如并发控制(concurrency control),可串行化(serializability),锁(locking)等。事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离,即该事务提交前对其他事务都不可见,通常这使用锁来实现。当前数据库系统中都提供了一种粒度锁(granular lock)的策略,允许事务仅锁住一个实体对象的子集,以此来提高事务之前的并发度。

D(durability),持久性。事务一旦提交,其结果就是永久性的。即使发生宕机等故障,数据库也能将数据恢复。需要注意的是,只能从事务本身的角度来保证结果的永久性。例如,事务提交后,所有的变化都是永久的。即使当数据库 因为崩溃而需要恢复时,也能保证恢复后提交的数据都不会丢失。但若不是数据库本身发生故障,而是一些外部原因,如RAID卡损坏,自然灾害等原因导致数据库发生问题,那么所有提交的数据可能都会丢失。因此持久性保证事务系统的高可靠性(High Reliability),而不是高可用性(High Availability)。对于高可用性的实现,事务本身并不能保证,需要一些系统共同配合完整。

分类

从事务理论的角度来说,可以把事务分为以下几种类型

  • 扁平事务(Flat Transactions)
  • 带有保存点的扁平事务(Flat Transaction with Savepoints)
  • 链事务(Chained Transactions)
  • 嵌套事务(Nested Transactions)
  • 分布式事务(Distributed Transactions)

扁平事务是事务中最简单的一种,但是在实际生产环境中可能是最频繁的事务。在扁平事务中,所有操作都处于统一层次,由begin work开始,由commit work或rollback work结束,其间的操作是原子的,要么都执行,要么都回滚。因此扁平事务是应用程序称为院子操作的基本组成模块。

因为其简单,故基本每个数据库系统都实现了对扁平事务的支持。

扁平事务的主要限制是不能提交或回滚事务的某一部分,或分几个步骤提交。

带有保存点的扁平事务,除了支持扁平事务的操作外,允许在事务执行过程中回滚到同一事务中较早的一个状态。这是因为某些事务可能在执行过程中出现的错误并不会导致所有的操作都失效,当其整个事务不合乎要求,开销也太大。保存点(Savepoints)用来通知系统应该记住事务当前的状态以便之后发生错误时,事务能回到保存点当时的状态。

对于扁平的事务来说,其隐式的设置了一个保存点。然后再整个事务中,只有这一个保存点,因此,回滚只能回滚到事务开始的状态。保存点用save work函数来建立通知系统记录当前的处理状态。当出现问题时,保存点能用作内部的重启动点,根据应用逻辑,决定是回滚到最近一个保存点还是其他更早的保存点。

链事务可视为保存点模式的一种变种。带有保存点的扁平事务,当发生系统崩溃时,所有保存点都将消失,因为其保存点是易失(volatile)的而非持久的(persistent)。这意味着当进行恢复时,事务需要从开始处重新执行,而不能从最近的一个保存点继续执行。

链事务的思想是 : 在提交一个事务时,释放不需要的数据对象,将必要的处理上下文隐式地传给下一个要开始的事务。注意,提交事务操作和开始下一个事务操作将合并为一个原子操作。这意味着下一个事务将看到上一个事务的结果,就好像在一个事务中进行一样。

链事务与带有保存点的扁平事务不同的是,带有保存点的扁平事务能回滚到任意正确的保存点。而链事务中的回滚仅限于当前事务,即只能恢复到最近的一个保存点。对于锁的处理,两者也不同。链事务在执行commit后即释放了当前事务所持有的锁,而带有保存点的扁平事务不影响迄今为止所持有的锁。

嵌套事务是一个层次结构框架。由一个顶层事务(top level transaction)控制着每个层次的事务。顶层事务之下前台的事务被称为子事务(substransaction),其控制每一个局部的变换

下面给出Moss对前台事务的定义

  1. 嵌套事务是由若干事务组成的一棵树,子树既可以是前台事务,也可以是扁平事务。
  2. 处在叶节点的事务是扁平事务。但是每个子事务从根到叶节点的距离可以是不同的。
  3. 位于根节点的事务称为顶层事务,其他事务称为子事务。事务的前驱(predecessor)称为父事务(parent),事务的下一层称为儿子事务(child)。
  4. 子事务既可以提交也可以回滚。但是他的提交操作并不马上生效,除非其父事务已经提交。因此可以推论出,任何子事务都在顶层事务提交后才真正提交。
  5. 树中的任意一个事务的回滚会引起他的所有子事务一同回滚,故子事务仅保留A,C,I特性,不具有D特性。

在Moss的理论中,实际的工作是交由叶子节点来完成的,即只有叶子节点的事务才能访问数据库,发送消息,获取其他类型资源。而高层的事务仅负责逻辑控制,决定何时调用相关的子事务。即使一个系统不支持嵌套事务,用户也可以通过保存点技术来模拟嵌套事务。

使用保存点技术模拟的嵌套事务在锁的持有方便还是与嵌套事务有所区别。当通过保存点即使来模拟事务时,用户无法选择哪些需要被子事务几次,哪些需要被父事务保留。这就是说,无论有多少个保存点,所有被锁住的对象都可以被得到和访问。而在嵌套事务中,不同子事务在数据库对象上持有的锁是不同的。

然而,如果系统支持在嵌套事务中并行的执行各个子事务,在这种情况下,采用保存点的扁平事务来模拟嵌套事务就不切实际了。这从另一个方面反映出,想要实现事务间的并行性,需要真正的支持嵌套事务。

分布式事务通常是一个在分布式环境下运行的扁平事务,因此需要根据数据所在位置访问网络中的不同节点。例如跨行转账

InnoDB支持扁平事务,带有保存点的事务,链事务,分布式事务。对于嵌套事务,其原生不支持,因此,对有并行事务需求的用户来说,MySQL数据库或InonoDB引擎就显得无能为力了。

事务的实现

事务的隔离性由锁来实现。原子性,一致性,持久性通过数据库的redo log和undo log来完成,redo log称为重做日志,用来保证事务的原子性和持久性。undo log用来保证事务的一致性

redo和undo的作用都可以视为一种恢复操作,redo恢复提交事务修改的页操作,而undo回滚行记录到某个特定版本。因此两者记录的内容不同,redo通常是物理日志,记录的页的物理修改操作。undo是逻辑日志,根据每行记录进行记录

redo

基本概念

重做日志用来实现事务的持久性,即事务ACID中的D。其由两部分组成 : 一是内存中的重做日志缓冲(redo log buffer),其是易失的;二是重做日志文件(redo log file),其是持久的.

InnoDB是事务的存储引擎,其通过Force Log at Commit机制实现事务的持久性,即当事务提交时(COMMIT)时,必须先将该事务的所有日志写入到重做日志文件进行持久化,事务的COMMIT操作才算完成。这里的日志指的是重做日志,在InnoDB中,由两部分组成,即redo log和undo log。redo log用来保证事务的持久性,undo log用来帮助事务回滚及MVCC的功能。redo log基本上是需要进行随机读写的。

为了确保每次日志都写入重做日志文件,在每次将重做日志缓冲写入重做日志文件后,InnoDB都需要调用一个fsync操作。由于重做日志文件打开并没有使用O_DIRECT选项,因此重做日志缓冲先写入文件系统缓存。为了确保重做日志写入磁盘,必须进行一次fsync操作。由于fsync的效率取决于磁盘的性能,因此磁盘的性能能决定事务提交的性能,也就是数据库的性能。

InnoDB允许用户手动设置成非持久性,以此提高数据库的性能,即当事务提交时,日志不写入重做日志文件,而是等待一个时间周期后再执行fsync操作。由于并非强制在事务提交时进行一次fsync操作,显然这可以显著提高数据的性能。但是当数据库宕机时,由于部分日志未刷新到磁盘,因此会丢失最后一段时间的事务。

参数innodb_flush_log_at_trx_commit可以用来控制重做日志刷新到磁盘的策略。具体的可以百度。

在MySQL数据库中海油一种二进制日志(binlog),其用来进行POINT-TIME(PIT)的回复及主从复制(Relication)环境的简历。从表面上看其和重做日志非常相似,都是记录了对于数据库的操作的日志。然而,从本质上来说,两者有着非常大的不同。

首先,重做日志是InnoDB存储引擎层产生的,而二进制日志是在MySQL数据库的上层产生的,并且二进制日志不仅针对于InnoDB存储引擎,MySQL数据库中的任何存储引擎都会产生二进制日志。

其次,两种日志记录的内容形式不同。MySQL数据库上层的二进制日志是一种逻辑日志,其记录对应的SQL语句。而InnoDB的重做日志是物理格式日志,其记录的是对每个页的修改。

此外,两种日志记录写入磁盘的时间不同,二进制日志只在事务提交完成后进行一次写入。而InnoDB的重做日志在事务进行中不断地被写入,这表现为日志并不是随事务提交的顺序进行写入的。

二进制日志仅在事务提交时记录,并且对于每一个事务,仅包含对应事务的一个日志。而对于InnoDB的重做日志,由于其记录的是物理操作日志,因此每个事务对于多个日志条目,并且事务的重做日志是并发写入的,并非在事务提交时写入的,故其在文件中记录的顺序并非是事务开始的顺序。

log block

在InnoDB中,重做日志都是以512字节进行存储的。这意味着重做日志缓存,重做日志文件都是以块(block)的方式进行保存的,称之为重做日志块(redo log block),每块的大小为512字节。

若一个页中产生的日志数量大于512字节,那么需要分隔为多个重做日志块进行存储。此外,由于重做日志块的大小和磁盘扇区大小一样,都是512字节,因此重做日志的写入可以保证原子性,不需要doublewrite技术。

...略...

undo

基本概念

重做日志记录了事务的行为,可以很好地通过其对页进行重做操作。但是数据有时还需要回滚,这时就需要undo。因此在对数据库进行修改时,InnoDB存储引擎不但会产生redo,还会产生一定量的undo。这样如果用户只需的事务或语句由于某种原因失败了,又或者用户用一条rollback语句请求回滚,这样就可以利用这些undo信息将数据回滚到修改之前的样子。

redo存档在重做日志文件中,与redo不同,undo存放在数据库内部的一个特殊段(segment)中,这个段称为undo段(undo segment)。undo段位于共享表空间内。

用户通常对undo有这样的误解 : undo用于将数据库物理地恢复到执行语句或事务之前的样子,但事实并非如此。undo是逻辑日志,因此只是将数据库的逻辑的回复到原来的样子。所有的修改都没逻辑的取消了,但是数据结构和页本身在回滚后可能大不相同。这是因为在多用户并发的系统中,可能会有数十上百甚至上千个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如,一个事务在修改当前一个页中某几个记录,同时还有别的事务在对同一个页中的 另几条记录进行修改。因此不能将一个页回滚到事务开始时的样子,因为这会影响到其他事务正在进行的工作。

例如,用户执行了一个insert 10w条记录的事务,这个事务会导致分配一个新的段,即表空间增大。在用户执行rollback时,会将插入的事务进行回滚,但是表空间的大小并不会因此缩减。因此,当InnoDB存储引擎回滚时,它实际上做的是与之前相反的操作。对于每个insert操作,都会去完成一个delete;对于每个delete,都会完成一个insert;对于每个update,都对完成一个相反的update。

除了回滚操作,undo的另一个作用是MVCC,即在InnoDB存储引擎中MVCC的实现是通过undo来实现的。当用户读取一行记录时,若该记录已经被其他事务占用,当前事务可以通过undo读取到之前的行版本信息,以此实现非锁定读。

最后很重要的一点,undo log会产生redo log,也就是undo log的产生会伴随着redo log的产生,这是因为undo log也需要持久性的保护。

... 略 ...

事务控制语句

在MySQL的命令行的默认设置下,事务都是自动提交(auto commit)的,即执行SQL语句就会马上执行COMMIT操作。因此要显式地开启一个事务需使用命令BEGIN,START TRANSACTION,或者执行命令SET AUTOCOMMIT=0,禁用当前会话的自动提交。注意,每个数据库厂商自动提交的设置都不相同。下面看看有哪些事务控制语句

  • START TRANSACTION : 显式的开启一个事务
  • COMMIT : 这个语句的最简形式就是COMMIT。也可以详细一些,用COMMIT WORK,不过二者几乎是等价的。COMMIT会提交事务,并使已对数据做的所有修改称为永久性的。
  • ROLLBACK : 这个语句的最简形式就是ROLLBACK。也可以详细一些,用ROLLBACK,不过二者几乎是等价的。回滚并结束用户的事务,并撤销事务中所有未提交的修改。
  • SAVEPOINT identifier : 在事务中创建一个保存点,一个事务可以有多个SAVEPOINT.
  • RELEASE SAVEPOINT identifier : 删除一个事务的保存点。
  • ROLLBACK TO [SAVEPOINT] identifier : 这个语句和SAVEPOINT命令一起使用。可以把事务回滚到标记点,而不回滚在此标记点之前的任何操作
  • SET TRANSACTION : 用来设置事务的隔离级别。InnoDB提供的事务级别有 : READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE。

START TRANSACTION,BEGIN语句都可以在命令行下显式的开启一个事务。但是在存储过程中,MySQL数据库的分析器会将BEGIN识别成BEGIN...END,因此在存储过程中只能使用START TRANSACTION语句开启一个事务。

COMMIT和COMMIT WORK语句基本是一致的,都是用来提交事务的。不同之处在于COMMIT WORK用来控制事务结束后的行为是CHAIN还是RELEASE的,如果是CHAIN方式,那就变成了链事务。

可以通过参数completion_type来进行控制,该参数默认为0,标识没有任何操作,在这种设置下COMMIT和COMMIT WORK是完全等价的。当参数为1时,COMMIT WORK等同于COMMIT AND CHAIN,表示马上自动开启一个相同隔离级别的事务。如下

create table t(`id` int);
set @@completion_type=1;
start transaction;
insert into t value (1);
commit work;
insert into t value (2);
rollback;
select * from t;
# 只能查询出id为1的一条记录

当completion_type为2时,COMMIT WORK等同于COMMIT AND RELEASE。在事务提交后会自动断开与服务器的连接。

ROLLBACK和ROLLBACK WORK与COMMIT和COMMIT WORK的用法一致。

SAVEPOINT记录了一个保存点,可以通过ROLLBACK TO SAVEPOINT来回滚到某个保存点,但是如果回滚到一个不存在的保存点,则会抛出异常

InnoDB存储引擎的中的事务都是原子性的,这说明下述两种情况 : 构成事务的每条语句都会提交(称为永久)或者全部回滚。这种保护还延伸到每个语句。一条语句要么完全成功,要么完全回滚(注意,这里是针对单条语句的回滚)。因此一条语句执行失败并抛出异常时,并不会导致先前事务中已经执行的语句回滚。所有的执行都会被保留,必须由用户自己来决定是否对其进行提交或回滚操作。如下:

drop table if exists `t`;
create table t(`id` int,primary key(`id`));
start transaction;
insert into t value (1);
insert into t value (1);
# 抛出主键不能重复的异常
select * from t;
# 可以查询出id为1的一条记录

另一个容易犯的错误是ROLLBACK TO SAVEPOINT,虽然有ROLLBACK,但其并不是真正的结束一个事务,因此即使只需了ROLLBACK TO SAVEPOINT,之后也需要显式的执行COMMIT或ROLLBACK命令。如下 :

drop table if exists `t`;
create table t(`id` int,primary key(`id`));
start transaction;
insert into t value (1);
savepoint t1;
insert into t value (2);
savepoint t2;
release savepoint t1;
insert into t value (2);
# 抛出主键重复的异常
rollback to savepoint t2;
select * from t;
# 依然可以查询到两条记录
rollback;
# 全部回滚
select * from t;
# 查询不到记录了

隐式提交的SQL语句

以下SQL语句会产生一个隐式的提交操作,就是不能进行回滚

  • DDL语句: ALTER DATABASEUPGRADE DATA DIRECTORY NAME,ALTER EVENT, ALTER PROCEDURE, ALTER TABLE, ALTER VIEW,CREATE DATABASE, CREATE EVENT, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, CREATE TRIGGER, CREATE VIEW,DROP DATABASE, DROP EVENT, DROP INDEX, DROP PROCEDURE,DROP TABLE, DROP TRIGGER, DROP VIEW, RENAME TABLE,TRUNCATE TABLE。
  • 用来隐式地修改 MySQL架构的操作: CREATE USER、 DROP USER、 GRANT 、RENAME USER、 REVOKE、 SET PASSWORD。
  • 管理语句: ANALYZE TABLE、 CACHE INDEX、 CHECK TABLE、 LOAD INDEX INTO CACHE、 OPTIMIZE TABLE、 REPAIR TABLE。

InnoDB的应用需要在考虑每秒请求数(Question Per Second,QPS)的同事,应该关注每秒事务的处理能力(Transaction Per Second, TPS)。

计算TPS的方法(com_commit+com_collback)/time。但是利用这种方法进行计算的前提是 : 所有事务必须是显式提交的,隐式存在的提交和回滚(默认autocommit=1),不会计算到这两个变量中。

事务的隔离级别

...略...

分布式事务

MySQL数据库的分布式事务

InnoDB提供了对XA事务的支持,并通过XA事务来支持分布式事务的实现。分布式事务是指允许多个独立的事务资源(transactional resources)参与到一个全局的事务中。事务资源通常是关系型数据库系统,但也可以是其他类型的资源。全局事务要求其中的所有参与的事务要么都提交,要么都回滚,这对于事务原有的ACID要求又有了提高。在使用分布式事务时,InnoDB存储引擎的事务隔离级别必须设置为SERIALIZABLE。

XA事务允许不同数据库之前的分布式事务,如一台服务器是MySQL数据库,另一台是Oracle(或者其他的),只要参与在全局事务中的每个节点都支持XA事务。

内部XA事务

之前说的分布式事务是外部事务,即资源管理器是MySQL数据库本身。在MySQL数据库中还存在另外一种分布式事务,其在存储引擎与插件之前,又或者在存储引擎与存储引擎之间,称之为内部XA事务。

最常见的内部XA事务存在于binlog与InnoDB存储引擎之间。
...略...

不好的事务习惯

在循环中提交

提交事务时会写一次重做日志,如果是循环一千次,那么就会写一千次重做日志。当是整个循环结束之后再提交时,那么就只会写一次。

使用自动提交

...略...

使用自动回滚

...略...

长事务

顾名思义就是执行时间长的事务。有的事务执行可能需要非常长的时间。可能1个小时,可能4,5个小时,这取决于数据硬件的配置。DBA和开发人员本身能做的事情非常少。然而,由于ACID的特性,这个操作被封装在一个事务中完成。这就产生了一个问题,在执行过程中,当数据库或操作系统,硬件发生问题时,重新开始事务变得不可接受。数据库需要回滚所有已经发生的变化,而这个过程可能比产生这些变化的时间还长。因此对于长事务的问题,有时可以转化为小批量的事务进行处理。当发生错误时,只需要从发生错误的位置继续执行就行。

MySQL技术内幕(InnoDB存储引擎概述)学习笔记03-锁

什么是锁

锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。InnoDB会在行级别上对表数据上锁,这固然不错。不过InnoDB也会在数据库内部其他多个地方使用锁,从而允许对多种不同资源提供并发访问。例如,操作缓冲池中的LRU列表,删除,添加,移动LRU列表中的元素,为了保证一致性,必须有锁的介入。数据库系统使用锁是为了支持对共享资源的并发访问,提供数据的完整性和一致性。

另一点需要理解的是,虽然现在数据库系统做得越来越类似,但是有多少种数据库就有多少种锁的实现方法。在SQL语法层面,因为有SQL标准的存在,要熟悉多个数据库系统并不是一件难事。而对于锁,用户可能对某个特定的关系数据库系统的锁定模型有一定的经验,但这并不意味着知道其他数据库。不同数据库(如Microsoft SQL Server, Oracle等),甚至不同的存储引擎(如MyISAM,NDB Cluster)对于锁的实现完全不同。

对于MyISAM,其锁是表锁设计。并发情况下读没有问题,但是并发插入的性能就要差一些了,如果插入是在“底部”,MyISAM存储引擎还是可以有一点的并发写入操作。对于Microsoft SQL Server,在2005版本之前其都是页锁,相对于表锁并发性能有所提升。页锁容易实现,但是对于热点数据页依然无能为力。到2005版本开始支持乐观并发和悲观并发,在乐观并发下开始支持行级锁,但是其实现方式与InnoDB不同。用户会发现在Microsoft SQL Server下,锁是一种稀有的稀缺资源,锁越多开销越大,因此他会有锁升级,在这种情况下,行锁会升级为表锁,这时并发性能就又回到了以前。

InnoDB存储引擎锁的实现和Oracle数据库非常类似,提供一致性非锁定读,行级锁支持,行锁没有相关额外的开销,并可以同时得到并发性和一致性。

lock与latch

在数据库中,lock与latch都可以称为锁。但是两者有着截然不同的含义。

latch一版称为,闩锁(轻量级的锁),因为其要求锁定的时间必须非常短。若持续的时间长,则应用的性能会非常差。在InnoDB存储引擎中,latch又可以分为mutex(互斥锁)和rwlock(读写锁)。其目的是用来保证并发线程操作临界资源的正确性,并且通常没有死锁检测的机制。

lock的对象是事务,用来锁定的是数据库中的对象,如表,页,行。并且一般lock的对象在事务commit或rollback后进行释放(不同的事务隔离级别释放的时间可能不同)。此外,lock,正如在大多数数据库中一样,是有死锁机制的。

locklatch
对象事务线程
保护数据库内容内存数据结构
持续时间整个事务过程临界资源
模式行锁,表锁,意向锁读写锁,互斥量
死锁通过waits-for graph,time out等机制进行死锁检测与处理无死锁检测与处理机制。仅通过应用程序加锁的顺序(lock leveling)保证无死锁的情况发生
存在于Lock Manager的哈希表中每个数据结果的对象中

对于InnoDB中的latch,可以通过show engine innodb mutex来进行查看,输出结果说明

名称说明
countmutex被请求次数
spin_waitsspin lock(自旋锁)的次数,InnoDB存储引擎latch在不能获得锁时首先进行自旋,若自旋后还不能获得锁,则进入等待状态
spin_rounds自旋内部循环的总次数,每次自旋的内部循环是一个随机数。spin_rounds/spin_waits表示每次自旋所需的内部循环次数
os_waits表示操作系统等待的次数。当spin_lock通过自旋还是不能获得latch时,则会进入操作系统等待状态,等待被唤醒
os_yields进行os_thread_yield唤醒操作的次数
os_wait_times操作系统等待的时间,单位是ms

上述信息比较底层,一版仅供开发人员参考。

InnoDB存储引擎中的锁

锁的类型

InnoDB存储引擎实现如下两种标准的行级锁

  • 共享锁(S LOCK) : 允许事务读一行数据
  • 排它锁(X LOCK) : 允许事务删除或更新一行数据

如果一个事务T1已经获得了行r的共享锁,那么另外的事务T2可以立即获得行r的共享锁,因为读取并没有改变行r的数据,成这种情况为锁兼容(Lock Compatible)。但若有其他事务T3想要获得行r的排它锁,则必须等待事务T1,T2释放行r上的共享锁,这种情况称为锁不兼容。如下表

 XS
X不兼容不兼容
S不兼容兼容

从上表可以发现X锁与任何锁都不兼容,而S锁仅和S锁兼容。需要特备注意的是,S锁和X锁都是行锁,兼容是指对同一记录锁的兼容情况。

此外,InnoDB支持多粒度(granular)锁定,这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作,InnoDB存储引擎支持一种额外的锁方式,称之为意向锁(Intention Lock)。意向锁是将锁定的对象分为多个层次,意向锁意味着事务希望在更细粒度上进行加锁。

若将上锁对象看成一棵树,那么对最下层的对象上锁,也就是对最细粒度(fine granularity)的对象进行上锁,那么首先需要对粗粒度的对象上锁。如果需要对页上的记录r上X锁,那么分别需要对数据库A,表,页上意向锁IX,最后对记录r上X锁。若其中任何一个部分导致等待,那么改操作需要等待粗粒度锁的完成。举例来说,在对记录r加X锁之前,已经有事务对表1进行了S表锁,那么表1上已存在S锁,之后事务需要对记录r在表1上加上IX,由于不兼容,所以该事务需要等待表锁操作的完成。

InnoDB支持意向锁设计比较简练,其意向锁即为表级别的锁,设计目的主要是为了在一个事务中揭示下一行将被请求的锁的类型。其支持两种意向锁。

  • 意向共享锁(IS Lock) : 事务想要获得一张表中某几行的共享锁
  • 意向共享锁(IX Lock) : 事务想要获得一张表中某几行的排它锁

由于InnoDB支持的是行级别的锁,因此意向锁其实不会阻塞除全表扫变外的任何请求。意向锁与行级锁的兼容性入校

 ISIXSX
IS兼容兼容兼容不兼容
IX兼容兼容不兼容不兼容
S兼容不兼容兼容不兼容
X不兼容不兼容不兼容不兼容

可以在show engine innodb status的输出结果中搜索lock来查看当前锁的情况

在InnoDB 1.0版本之后,可以在INFORMATION_SCHEMA数据库中通过表INNODB_TRX,INNODB_LOCKS,INNODB_LOCK_WAITS来监控锁的使用情况。

INNODB_TRX结构

该表显示了当前运行的InnoDB事务的信息

字段名说明
trx_idInnoDB内部唯一的事务ID
trx_state当前事务的状态
trx_started事务的开始时间
trx_request_lock_id等待事务的锁ID。如trx_state的状态为LOCK WAIT,那么改值代表当前的事务等待之前事务占用锁资源的ID。若trx_state不是LOCK WAIT,则该值为NULL
trx_weight事务的权重,反映了一个事务修改和锁住的行数。在InnoDB中,当发生死锁需要回滚时,会选择该值最小的的进行回滚
trx_mysql_thread_idMySQL中的线程ID,SHOW PROCESSLIST显示的结果
trx_query事务运行的SQL语句

INNODB_LOCKS结构

该表显示了当前运行的InnoDB事务锁的信息

字段名说明
lock_id锁的ID
lock_trx_id事务ID
lock_mode锁的模式
lock_type锁的类型,表锁还是行锁
lock_table要加锁的表
lock_index锁住的索引
lock_space锁对象的space id
lock_page事务锁定的页的数量。若是表锁,则该值为NULL
lock_rec事务锁定的行的数量,若是表锁,则该值为NULL
lock_data事务锁定的记录的主键值,若是表锁,则该值为NULL

需要注意的是,lock_data这个值并非是“可信”的值。例如用户运行了一个范围查找时,lock_data可能只返回第一行的主键值。与此同时,如果当前资源被锁住了,若锁住的页因为InnoDB缓冲池的容量,导致该页从缓冲池中被刷出,则查看INNODB_LOCKS表时,该值同样会显示为NULL,即InnoDB不会从磁盘再进行一次查找。

INNODB_LOCK_WAITS结构

该表显示了当前运行的InnoDB事务等待的情况

字段名说明
requesting_trx_id申请锁资源的事务ID
requesting_lock_id申请的锁的ID
blocking_trx_id阻塞的事务ID
blocking_lock_id阻塞的锁ID

这个表可以清楚的看到那个事务阻塞了另一个事务

一致性非锁定读

一致性的非锁定读(consistent nonlocking read)是指InnoDB通过行多版本控制(muti versioning)的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作,这时读取操作不会因此去等待行上锁的释放。相反的,会取读取行的一个快照数据。

之所以称为非锁定读,是因为不需要等待访问的行上X锁的释放。快照数据是指之前版本的数据,该实现是通过undo端来完成。而undo段用来在事务中回滚数据,因此快照数据本身是没有额外的开销。此外,读取快照数据是无需上锁的,因为没有事务会对历史数据进行修改操作。

非锁定读机制极大提高了数据库的并发性。在InnoDB的默认设置下,这是默认的读取方式,即读取不会占用和等待表上的锁。但是在不同事务隔离级别下,读取的方式不同,并不是在每个事务隔离级别下都是采用非锁定一致性读。此外,即使都是使用非锁定一致性读,但是对于快照数据的定义也各不同。

快照数据其实就是当前行数据之前的历史版本,每行记录可能有多个版本。一行数据可能有不止一个快照数据,一般称这种技术为行多版本技术。由此带来的并发控制,称之为多版本并发控制(Mutil Version Concurrency Control, MVCC)。

在事务隔离级别READ COMMITTED和REPEATABLE READ(InnoDB存储引擎的默认事务隔离级别)下,InnoDB使用非锁定一致性读。然而,对于快照数据的定义确不同。在READ COMMITTED事务隔离级别下,对于快照数据,非一致性读总是读取被锁定行的最新的一份快照数据。而在REPEATABLE READ事务隔离级别下,对于快照数据,非一致性读总是读取事务开始时的行数据版本。

一致性锁定读

在某些情况下,用户需要显式的对读取操作加锁以保证数据逻辑的一致性。而要求数据库支持加锁语句,即使是对于select的只读操作。InnoDB对于select语句支持两种一致性锁定读(locking read)操作

  • SELECT ... FOR UPDATE
  • SELECT ... LOCK IN SHARE MODE

SELECT ... FOR UPDATE对读取的行记录加一个X锁,其他事务不能对已锁定的行加上任何锁。SELECT ... LOCK IN SHARE MODE对读取的行记录加一个S锁,其他事务可以对被锁定的行加S锁,但是如果加X锁,则会被阻塞。

对于一致性锁定读,即使读取的行已经被执行了SELECT ... FOR UPDATE,也是可以进行读取的,这和之前讨论的情况一样。此外,这两个语句必须在事务中执行,当事务提交了,锁也就释放了。因此在使用这两个SQL语句时,务必加上begin,start transaction或者set autocommit=0。

自增长与锁

自增长在数据库中是一种非常常见的属性,是一种首选的主键方式。在InnoDB的内存结构中,对每个含有自增长值的表都有一个自增长计数器(auto-increment counter)。当对含有自增长的计数器的表进行插入操作时,这个计数器会被初始化,使用下面的语句可以得到计数器的值。
SELECT MAX(auto_inc_col) from t for update

插入操作会根据这个自增长的计数器值加1赋予自增长列。这个实现方式成为AUTO-INC Locking。这种锁采用一种特殊的表锁机制,为了提高插入性能,锁不是在一个事务完成后才释放,而是在完成对自增长值插入的SQL语句后立即释放。

虽然AUTO-INC Locking从一定程度上提高了并发插入的效率,但还是存在一些性能上的问题。首先,对于有自增长值的列的并发插入性能较差,事务必须等待前一个插入的完成(虽然不用等事务的完成)。其次,对于INSERT ... SELECT的大数据量的插入会影响插入的性能,因为另外一个事务中的插入会被阻塞。

从5.1.22版本开始,InnoDB提供了一种轻量级互斥量的自增长实现机制,这种机制能大大提高自增长值的性能。

在InnoDB存储引擎中,自增长的列必须是索引,同时必须是索引的第一个列。

外键和锁

外键用于引用完整性的约束检查。在InnoDB中,对于一个外键列,如果没有显示的对这个列加索引,InnoDB会自动对其加一个索引,因为这样可以避免表锁。

对于外键的插入或更新,首先需要查找父表中的记录,即select父表。但是对于父表的select操作,不是使用一致性非锁定读的方式,因为这样会产生数据不一致的问题,因此使用的是SELECT ... LOCK IN SHARE MODE方式,即主动对表加一个S锁。如果这是父表上已经加了X锁,子表上的操作会被阻塞。

锁的算法

锁的3中算法

  • Record Lock : 单个行记录上的锁
  • Gap Lock : 间隙锁,锁定一个范围,单不包含记录本身
  • Next-Key Lock : Gap Lock + Record Lock,锁定一个范围,并且锁定记录本身

Record Lock总是会去锁住索引记录,如果InnoDB存储引擎表在建立的时候没有设置任何索引,那么InnoDB会使用隐式的主键来进行锁定。

Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法,在Next Lock算法下,InnoDB对于行的查询都是采用这种锁定算法

略略略。。。

MySQL技术内幕(InnoDB存储引擎索引)学习笔记02-索引

概述

InnoDB存储引擎支持以下几种常见的索引 :

  • B+树索引
  • 全文索引
  • 哈希索引

InnoDB存储引擎支持的哈希索引是自适应的,InnoDB会跟根据表的使用情况自动为表生成哈希索引,不能人为干预是否在一张表中生成哈希索引。

B+树索引就是传统意义上的索引。类似于二叉树。
B+树索引中的B不是代表的二叉(binary),而是代表的平衡(balance),因为B+树是从最早的平衡二叉树演化而来,但是B+树不是一个二叉树。

数据算法与结构

介绍B+树之前先介绍下相关的数据结构

二分查找

将记录有序化排列,在查找过程中采用跳跃式的方式查找,先以有序数列的重点位置为比较对象,如果要找的元素值小于该重点元素,则将待查序列缩小为左半部分,否则为右半部分。通过一次查找将查找区间缩小一半

二叉查找树和平衡二叉树

二叉查找树 :左子树的键值总是小于节点的键值,右子树的键值总是大于节点的键值
平衡二叉树 :首先要符合二叉查找树的条件,然后必须满足任何子节点的两个子树的高度最大差为1
平衡二叉树在插入,更新和删除节点时,需要做旋转操作,会有额外的开销

B+树

B+树是为磁盘或其他存取辅助设备设计的一种平衡查找树。在B+树中,所有记录节点都是按照键值的大小顺序存放在同一层的子节点上,由各子节点指针进行连接。

B+树的插入操作

为了保持B+树的平衡,对于新插入的键值可能需要做大量的拆分页(split)操作。因为B+树结构主要用于磁盘,页的拆分意味着磁盘操作,所以应该在尽可能的情况下尽量减少页的拆分操作。因此B+树同样提供了类似于平衡二叉树的旋转功能。

B+树的删除

B+树使用填充因子(fill factor)来控制树的删除变化,50%是填充因子的最小值。B+树的删除操作必须保证删除后叶子节点中的记录依然排序。

B+树索引

前面讨论的都是B+树的数据结构及其一般操作,B+树索引的本质就是B+树在数据库中的实现。在数据库中,B+树的高度一般都是在2~4层,这也就是说查找某一键值的行记录时最多只需要2到4次IO。一般的机械硬盘每秒至少可以做100次IO,2~4次的IO意味着查询时间只需要0.02~0.04秒。

数据库中的B+树可以分为聚集索引(clustered index)和辅助索引(secondary index),但是不管是聚集索引还是辅助索引,其内部都是B+树的,即高度平衡的,叶子节点存放所有的数据。聚集索引与辅助索引不同的是,叶子节点存放的是否是一整行的数据。

聚集索引

聚集索引就是按照每张表的的主键构建一颗B+树,同时叶子节点中存放的即为整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中的数据也是索引的一部分。同B+树数据结构一样,每个数据页都通过一个双向链表来进行链接。

由于实际的数据页只能按照一颗B+树进行排序,因此每张表只能拥有一个聚集索引。在多数情况下,查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外,由于定义了数据的逻辑顺序,聚集索引能特别快的访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。

数据页上存放的是完整的每行的记录,而在非数据页的索引页中,存放的是键值和指向数据页的偏移量,而不是一个完整的行的记录。

如果聚集索引必须按照特定的顺序存放物理记录,则维护成本会相当高,所以聚集索引的存储并不是物理上连续的,而是逻辑上连续的。这其中有两点:一是页通过双向链表链接,页按照主键的顺序排序;另一点是每个页中的记录也是通过双向链表进行维护的,物理存储上可以同样不按照主键存储。

聚集索引的另一个好处是,他对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户索要查询的数据

辅助索引

辅助索引(Secondary Index,也称为非聚集索引),叶子节点并不包含行记录的全部数据。叶子节点除了包含键值外,每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉InnoDB存储引擎哪里可以找到索引相应的行数据。由于InnoDB存储引擎表是索引组织表,因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

辅助索引并不影响数据在聚集索引中的组织,因此在每张表上可以有多个辅助索引。当通过辅助索引来寻找数据是,InnoDB存储引擎会遍历索引并通过叶级别的指针获得指向主键索引的主键,然后再通过主键索引来找到一个完整的行记录。举例来说,如果在一颗高度为3的辅助索引树中查找数据,那需要对这棵索引树遍历3次找到指定主键,如果聚集索引树的高度同样为3,那么还需要对聚集索引树进行3次查找,最终找到一个完整的行数据所在的页,因此一共需要6次逻辑IO访问以得到最终的数据页。

B+树索引的管理

添加删除索引

不赘述了,可以百度

可以通过show index from观察表上的索引,show index展现结果中每列的含义
Table : 索引所在的表名
Non_unique : 非唯一的索引,可以看到primary key是0,因此必须是唯一的
Key_name : 索引的名字,用户可以通过这个名字来执行drop index
Seq_in_index : 索引中该列的位置
Column_name : 索引列的名称
Collation : 列以什么方式存储在索引中,可以是A或NULL。B+树索引总是A,即排序的。如果使用了Heap存储引擎,并建立了Hash索引,这里就会显示NULL了。因为Hash是根据Hash桶存放索引数据,而不是对数据进行排序。
Cardinality : 非常关键的值,标识索引中唯一值的数目的估计值。Cardinality表的行数应尽可能接近1,如果非常小,如果非常小,那么可以考虑删除此索引
Sub_part : 是否是列的部分被索引。字段的类型为字符串时,只会以字符串的前面一部分字符作为索引
Packed : 关键词如何被压缩
Null : 是否索引的列含有NULL值。
Index_type : 索引的类型,InnoDB存储引擎只支持B+树索引,所以这里显示的都是BTREE。
Comment : 注释

Cardinality值非常关键,优化器会根据这个值来判断是否使用该索引。但是这个值并不是实时更新的,即并非每次索引的更新都会更新该值,因为这样代价太大了。因此这个值是不太准确的,只是一个大概的值。如果需要更新Cardinality的值,可以使用ANALYZE TABLE

Fast Index Creation

MySQL5.5版本之前存在的一个普遍被人诟病的问题是MySQL数据库对于索引的添加或删除的这类DDL操作,MySQL的操作过程为

  1. 创建一张临时表,表结果通过alter table新定义结果
  2. 把原表中数据导入到临时表中
  3. 删除原表
  4. 把临时表重命名为原来的表名

如果是一张很大的表,会需要很长的时间,并且在这段时间内,是不能对这张表进行操作的。

InnoDB从1.0.x版本开始支持一种称为Fast Index Creation(快速索引创建)的索引创建方式--检查FIC。

Cardinality值

什么是Cardinality

并不是所有的查询条件中出现的列都需要添加索引。对于什么时候添加B+树索引,一般经验是,在访问表中很少一部分时使用B+树索引才有意义。对于性别字段,地区字段,类型字段,他们可取值的范围很小,称为低选择性,这种时候添加索引其实是没有必要的。高选择性的字段,比如说用户邮箱,比如用户有邮箱。

InnoDB的Cardinality统计

InnoDB内部更新Cardinality的策略为

  • 表中1/16的数据已发生变化
  • stat_modified_counter > 2000000000

B+树索引的使用

联合索引

从本质上说,联合索引也是一颗B+树,不同的是联合索引的键值数量不是1,而是大于等于2。
和单个键值的B+树一样,键值都是排序的,通过叶子节点可以逻辑上顺序地读出所有数据。比如一个索引idx(a,b)(1,1),(1,2),(2,1),(2,4),(3,1),(3,2)。数据按(a,b)的顺序进行了存放

因此对于查询select * from t where a = 1 and b = 2,显示是可以使用这个联合索引的。对于单个的a列的查询select * from t where a = 1,也可以使用这个索引。但是对于单个b列的查询select * from t where b = 2则是不能使用这个索引的。可以看到b的顺序为1,2,1,4,1,2,显然是不排序的,因此对于b列的查询使用不到(a,b)的索引。

联合索引的第二个好处是已经对第二个键值进行了排序处理,这样可以避免一次排序操作。

联合索引(a,b)其实是根据列a,b进行排序,因此下列语句可以直接使用联合索引得到结果

然而对于联合索引(a,b,c)来说,下列语句同样可以直接通过连接索引拿到结果
select ... from t where a = 1 order by b
select ... from t where a = 1 and b = 2 order by c
但是对于下面的语句,联合索引不能直接得到结果,其还需要执行一次filesort操作,因为(a,c)并未排序
select ... from t where a = 1 order by c

覆盖索引

InnoDB支持覆盖索引(covering index,或称覆盖索引),则从辅助索引中就可以得到查询的记录,而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息,故其大小远小于聚集索引,因此可以减少大量的IO操作。InnoDB版本小于1.0或MySQL版本为5.0或以下的,不支持此特性。

对于辅助索引而言,由于其包含了主键信息,因此其叶子节点存放的数据为(primary key1,primary key2,...,key1,key2,key3...)。例如,下面的语句都可仅适用一次辅助联合索引完成查询
select key2 from t where key1 = xxx
select primary key2, key2 from t where key1 = xxx
select primary key1, key2 from t where key1 = xxx
select primary key1, primary key2, key2 from t where key1 = xxx

覆盖索引还有一个好处是对某些统计问题而言的。当InnoDB选择适用辅助索引来进行统计时,由于辅助索引远小于聚集索引,选择辅助索引可以减少IO操作。如下面的SQL
select COUNT(*) from t
通常情况下InnoDB引擎不会查询聚集索引来进行统计。但是当表上有辅助索引时,辅助索引远小于聚集索引,选择辅助索引可以减少IO操作,故优化器会选择辅助索引进行统计。

表bug_log有(user_id, buy_date)的联合索引,这里只根据列b进行条件查询,一般情况下是不可以选择b中所谓的查询条件。但是如果是统计操作,并且是覆盖索引,则优化器会进行选择。
select count(*) from buy_log where buy_date >= '2011-01-01'
表buy_log有联合索引,这里只根据列b进行条件查询,一般情况下是不能使用该联合索引的,但是这句SQL查询是统计操作,并且可以利用到覆盖索引的信息,因此优化器会选择改联合索引。

优化器选择不使用索引的情况

当用户要查询的数据是整行信息,而辅助索引不能覆盖到我们要查询的信息,因此在对辅助索引查询到指定的数据后,还需要进行一次书签访问来查找到整行的信息。虽然辅助索引数据是顺序存放的,但是再一次进行书签查找的数据则是无序的,因此变成了磁盘上的离散读操作。如果要求访问的数据量很小,则优化器还是会选择辅助索引,但是当访问量的数据占整个表的蛮大一部分时(一般是20%左右),优化器会选择通过聚集索引来查找数据。因为之前提到过,顺序读要远远快于离散读。

因此对于不能进行索引覆盖的情况,优化器选择辅助索引的情况是,通过辅助索引查找的数据是少量的。这是由于当前传统机械硬盘的特性决定的,即利用顺序读来替代离散读。若使用的磁盘是固态硬盘,随机操作足够快,同时有足够的自信确认使用辅助索引可以带来更好的性能,那么使用关键词force index来强制使用某个索引

哈希表和全文索引就不记录了。。。