数据库索引设计与优化04-被动式索引设计

到目前为止,我们讨论的都是相对简单的SQL语句。在我们讨论如关联查询,子查询,合并查询,星型查询和多索引访问时,有必要先讨论一下如何监视SQL,并从被动式响应的角度考虑索引设计中需要考虑的问题。

被动式的方法与莱特兄弟创造守架飞机的经历非常相似。本质上就是把查询放在一起,推下悬崖,然后看他能否起飞。换句话说,就是为应用设计一个没有索引的原型,然后开始运行一些查询。又或者,创建原始索引集,然后通过运行应用来看那些索引被用到,那些没有被用到。即使是一个小型的数据库系统,运行速度慢的查询也会被很快凸显出来。

被动式调优的方法也被用来理解和调优一个性能没有满足预期的已有应用。

在产品投入大规模使用后才对应用的大部分索引进行调优工作,就像是将一个载满乘客的大型客机推下悬崖。这可能会有一个好的结果,但是数据库员工必须做好快速响应的准备。如果没有人在应用开发阶段关注索引设计的话,一些程序可能会在应用投入后变得非常慢。

我们将会讨论在应用投入生产环境后的最初几天需要用到的性能工具和技术。这些工具和技术对于之后能够轻松地调优也是有用的,以在用户觉察之前发现性能问题,或者至少在问题变得令人无法忍受之前发现他们。在这种场景下,我们实际上是主动的而非被动的。

explain描述了所选择的访问路径

识别可疑的访问路径是相当容易的,尤其是当explain的输出结果被存储在表中的情况下,因为这使得获取解决过变得容易。这就是为什么对于优化器选择了可以访问路径的SQL,分析过程通常从索引优化开始。通过explain能够快读发现以下这些性能警示信号。

全表扫描或全索引扫描

对结果集排序

除了全表扫描和全索引扫描,结果集的排序就是最有用的警示信号了。引起排序的原因可能有以下两种

  1. 没有可使查询语句避免排序的索引
  2. 优化器所选择的访问路径包含了一次多余的排序

对于第一种情况,可以通过优化索引来避免,第二种后面再讨论。

通常,排序是没有害处的。例如,某应用可能有以前个不同的查询语句,其中几百个在他们的访问路径中都有一次排序,这几百个语句中的90%可能都有非常出色的性能,排序对其响应时间值贡献了很不明显的一部分。因此,大量错误的警告可能会使这种检查方法有些无用。

有很多数据库顾问将排序视为敌人。我们认为,哪些强调随机I/O带来致命影响的顾问更值得信任。

成本估算

一些数据库管理系统的explain功能显示了优化器对所选访问路径的本地响应时间的估算,或至少显示了对CPU时间的估算。有些产品,如SQL Server 2000,显示了对访问路径中每一步的CPU及I/O时间估值。在一个简单的场景中,可能会有如下几步 :

  1. 检索并读取索引行
  2. 对指针进行排序
  3. 检索并读取表行
  4. 对结果进行排序

经验显示,过分的依赖优化器的成本估算是危险的。毕竟优化器生成成本估算仅仅是为了选择最快的访问路径。优化器生成的估值有时会给出错误的警示信号,而且还不会显示所有的警示信号,但这是一个用起来简单且快速的工具,他使得早期检查称为可能。因此,哪些成本估值异常高的SQL语句--可能是一个新应用中估值最高的前20个语句,应该一一检查一下,很可能不合适的索引使用或优化器问题就能被发现。

不幸的是,以下两个严重问题限制了使用成本估算方法的价值

  1. 优化器所做出的的本地响应时间估算可能与实际相差很大
  2. 当谓词使用绑定变量时(显然这是很普遍的),优化器对过滤因子的估算是基于平均输入值的,或更差情况下,基于默认值。为了获取更有价值的最差情况估值,explain中的绑定变量必须用最差情况下的输入值来代替。这是一个需要应用知识的累人操作。

explain是分析优化器问题的一个不可或缺的工具。因为这正是存在的理由,而不仅仅被用于索引设计。就像机场安检人员不仅是在机场检查可疑乘客。

问题制造者和问题受害者

一个涵盖一周中高峰时段的尖刺报告可能包含100多个不同领域模块的数千个尖刺。在应用开发者的些许帮助下,一个数据库专家也许每周分析并修复5-10个模块。由于程序变更或不同用户输入,下一周的尖刺报告可能会显示新的问题模块。为了高效地利用有限的时间,仔细地对所分析的模块排定优先级是很重要的,事务的响应时间及执行频率并不是唯一的评定标准。

我们首先要区分的是问题制造者和受害者。如果一个事务独占了资源(也许是因为使用了不合适的索引),那么会对其他事务造成明显的负面影响,进而导致这些事务也与独占资源的事务一同出现在异常报告中。

问题制造者所使用的资源会被包含进服务时间,而问题受害者会因为需要等待这些资源而受到影响,即排队时间受影响。着手优化的合理方式是考虑如何解决问题制造者导致的问题,而非受害者的问题

有优化空间的问题制造者和无优化空间的问题制造者

在决定将注意力从受害者转移到问题制造者上之后,我们需要区分的第二项就是有优化空间的问题制造者和无优化空间的问题制造者。不过我们可能没有时间去分析所有的问题制造者。有优化空间的问题制造者是指哪些能够通过改进所有来获得大幅性能提升的事务。

有优化空间的问题制造者有两个特征

  1. 磁盘服务时间长。
  2. 磁盘度大多是是对表页的读取。

若在同步读上耗费的时间占了本地响应时间的绝大部分,且有超过100个表页是从磁盘服务器上读取的,那么索引改进很可能会带来不错的效果。同样,如果对于索引页的同步读大于100次也不是一个好消息。

为了减少对索引的随机读次数,可以减少所需访问的索引数量,也可以减少所需访问的表的数量,这可以通过向表添加冗余字段的方式实现。这些改变并不比创建一个新的索引或像现有索引列上添加列更容易或更可控。

关机随机访问有一个例外,就是对一个厚索引片扫描可能会由于叶子页的分裂而变慢。这个文件很容易解决也相对容易避免,将在后面介绍。

有优化空间的问题制造者

分析有优化空间的问题制造者通常会读取许多表页。我们知道有三种读取表页的方式 :

  1. 事务中的随机读(同步读 : SR)
  2. 跳跃式顺序读
  3. 顺序读(顺序预读)

通过分析见此报告,我们能够腿短出有优化空间的问题制造者主要是有用这三种方式中的那种方式读取表页的。

同步读(方式1)不与CPU的时间重叠,应用程序会停下来,等待锁清秋的页从磁盘服务器返回。尖刺报告会将该事务同步读取的页数连同这些同步读的平均等待时间一起显示出来。

异步读(方式2和方式3)是预读取,他的I/O时间与程序所花费的CPU时间是重叠的。在数据库管理系统向磁盘服务器请求后几页的同时,程序任然在处理数据库缓冲池中的页。程序可能会遇到没有页可供处理的情况,也可能不会遇到。如果发生了这种情况,那么这个等待时间会被记录进预读等待的计数器中。如果程序从未与发哦需要等待预读取页的情况,那么CPU时间便决定了程序的性能。于是,在这种情况下,预读等待的值为0.顺序扫描所花费的时间一部分被记录进SQL的CPU时间,一部分被记录进其他等待(对于任何CPU排队来说)。

在当前硬件下,以跳跃式顺序读的方式访问表页时,响应时间可能还是取决于本地响应时间中最主要的组成部分,因为这意味着包含满足条件的页彼此相隔很远。

而以顺序读方式访问表页时,在当前硬件条件下,CPU或I/O时间的主导地位基本各占一半,有时访问每页所花费的CPU时间比I/O时间长一些,有时又短一些。QUBE算法假设,I/O时间与CPU时间中较大者的上线为0.01ms每行。如果I/O时间决定了顺序读的时间,那么顺序扫描所花费的时间就等于尖刺报告中CPU时间与预读等待部分的总和。

许多程序使用上述方式中的两种或三种来访问表页。此时理解尖刺报告就没有那么简单了,不过主要的方法通常也不是那么难推理。访问的表页总数也许能够帮助我们理解报告。访问的表页总数除了同步读次数外,还包括了通过预读读取的页数(方式2和方式3)和缓冲池的命中数(在数据库缓存终止找到的表页数量)。

调优的潜在空间

在投入大量时间分析一个异常事务之前,值得花时间先去评估一下在索引优化之后本地响应时间能降低多少。调优的潜在空间就是指可实现的降低值的上限。

随机读

只读事务的调优潜在空间等同于读取的表页数和同步读的平均时长的乘积。如果通过使用宽索引避免全部表页的读取,那么本地响应时间就能减少以这个量。

跳跃式顺序读

在上述例子中,有些优化器可能会选择跳跃式顺序读。那么数据库管理系统将首先从所有满足条件的索引行中获取指针,然后再根据表页号对指针进行排序。此时,数据库管理系统就有了所有表页排序后的列表,每个表页都至少包含一条符合条件的记录。只有到这时,数据库管理系统才会开始访问表。在这种方式下,访问每个表页的I/O时间自然相对较短,尤其是当一些满足条件的行恰好都在同一个磁轨上时。另外,I/O时间将会与CPU时间重叠,因为数据库管理系统有条件进行预读了。相比同步读,这种方式所节省的时间是非常不确定的,具体取决于读取的页之间的平均距离以及条带的实现方式。这种方式的调优潜在空间即为当前预读等待的值。

顺序读

顺序扫描的响应时间通常取决于CPU时间。

这类问题,我们需要找到一个更窄的索引段或表段的访问路径,以达到调优的目的。

无优化空间的问题制造者

通常来说,对有大量SQL调用(如10000多次)的事务来说,如果CPU时间是本地响应时间的主要组成部分,那么这些事务是很难被优化的。

对于此类事务,调优潜在空间即为能够去除的SQL调用次数乘以每次SQL调用的基本CPU时间(如0.1ms)。

受害者

数据库索引设计与优化03-影响索引设计过程的因素

I/O时间估算的验证

在前面我们引入了以下I/O时间 :
随机读取 10ms(页的大小为4KB或8KB)
顺序读取 40MB/s

这些数据是假定系统使用当前硬件并在一个合理的负载下运行时的值。一些系统可能运行的更慢或处理超负荷状态,所以,进行下面这些检验可能是有用的。为此,我们需要建一张表,该表有100万行记录,每行记录的平均长度为400字节。根据假定的顺序I/O时间计算,以顺序I/O读取400字节长度的行所花费的时间为 400byte / 40MB/s = 0.01ms。

于是,进行如下扫描所消耗的时长便是确定的(括号中的数据库是根据在前面使用的估算数据来预测的)

  • 由一个所有行都不满足条件的select查询引起一次全表扫描(1 10ms + 1000000 0.01ms ≈ 10s)
  • 一次涉及1000次fetch调用的索引扫描,使用如(lname,fname)这样的索引,导致1000行索引片扫描和1000次对表的同步读(1000 0.01ms + 1000 10ms = 10ms + 10s ≈ 10s)。

我们有必要以这样的方式进行测量,即保证在程序刚开始的时候,查询所需要的页不在内存或者读缓存中。如果系统运行的相当繁忙,那么两次测量之间间隔几个小时可能就达到这一目的了。

如果测量出的时间比预计的时间长很多,那么便需要明确造成这一后果的主要原因 : 磁盘设备或路径较慢?还是磁盘超负载运行?

无论这些测出的数字是乐观或悲观,作为相对性能指标,他们是有用的。

多个窄索引片

通常情况下,我们为一个select语句设计一个索引(而不是反过来),但有时候为了让数据库管理系统能够以最高效的方式使用一个已经存在的索引,对一个SQL语句进行重新设计也是可取的。因为优化器并不知道一切!

在where子句包含两个范围谓词时,一个完美的优化器能够构造出一个由多个窄索引片组成的访问路径。如下

select cno, fname
from cust
where lname between :lname1 and :lname2
      and
      city between :city1 and :city2
order by fname

因为一个范围谓词是索引匹配过程中的最后一个匹配字段,所以让数据库管理系统读取多个窄索引片(有lname和city共同定义)需要额外的程序逻辑。谓词条件lname between :lname1 and :lname2必须被修改为lname = :lname。这可以通过一个触发器维护的辅助表来完成,该表包含客户表中lname列的所有不同的值。当用户输入字符串JO时,程序便从辅助表中每次读取一个满足条件的lname,并用每个lname值打开下面的sql。

select cno, fname
from cust
where lname = :lname
      and
      city between :city1 and :city2
order by fname

现在,即使是一个没有完美优化器的数据库管理系统也将会从索引(lname, city, fname, cno)中读取多个窄片。响应时间将取决于索引片的数量以及每一个索引片中的行数。

使用如下假设对比这两种选择

  • cust表中有100万行记录
  • lname最坏情况下的过滤因子为1%
  • city最坏情况下的过滤因子为10%

被第一条sql扫描的索引片由10000行索引记录组成(MC = 1;1000000的1%为10000),其中只有10%的city值(1000行)是满足需要的。

同时假设此索引片中有50个不同的lname值,用第二个sql优化过的程序将会读取50个索引片(平均每个索引片由20行记录),这1000行中的每一行都是满足查询条件的city。

根据QUBE(鉴于只是单纯的做比较,我们忽略fetch所带来的时间开销,两个游标的时间开销为)
cursor 1 1 * 10ms + 10000 * 0.01 ≈ 0.1s
cursor 2 50 * (1 * 10ms + 20 * 0.01) ≈ 0.5s

这有些让人吃惊,但却是合理的。顺序处理的确有时比跳跃处理要来得高效,原因是每一次跳跃都可能以为着一次随机访问。当数字不同时,用优化后的sql有可能会快很多。

为了证明最后说的观点,我们来考虑一个场景。该场景有一张更大的表(100000000行记录),且第一个索引列拥有较高的过滤因子。如下

select cno, fname
from cust
where lname between :lname1 and :lname2      FF max 10%
and
city between :city1 and city2      FF max 0.01%
order by fname

结果集的大小将会是0.01% 10% 100000000 = 1000行。假设有 一个索引(lname, city, fname, cno)且lname在1000行结果集中有50个不同的值。现在,简单程序(两个between)的时间花费估算结果(一个宽索引片)为
1 * 10ms + 10000000 * 0.01ms ≈ 100s
然而复杂程序(50个索引片)的估算结果仍旧为
50 * (1 * 10ms + 20 * 0.01ms) ≈ 0.5s

简单就是没(和安全)

使用辅助表来保存lname不同值的解决方案是一个典型的将数据完整性和性能进行折中的例子。理论上,使用一个触发器来维护辅助表是没有数据完整性的风险的。然而,触发器是应用程序,是程序就不会是完美的。当 alan jones的记录从客户端删除且表中还有另一个jones的记录时,是否能完全确定触发器不会同时把jones的记录从辅助表中删除呢?只有当量化估算不这样做性能就无法接受时,我们才考虑采用这样的调整手段。有时,这样的调整方式反而会对性能产生负面影响。

困难谓词

定义 : 如果一个谓词字段不能参与定义索引片,即它无法成为一个匹配谓词,那么我们就说它对优化器而言太困难了。

like谓词

数据库管理系统可能因为like谓词不参与匹配过程而扫描整个索引,那么访问路径将会变得很慢。

所以,只有在已知优化器不会造成问题的前提下,才应该使用谓词like

or操作符和布尔谓词

即使是简单的谓词,如果他们与其他谓词之间为or操作,那么对于优化器而言他们也可能是困难的。这样的谓词只有在多索引访问下才有可能参与定义一个索引片。

下面的例子将明确这一点。假设在table上有一个索引(A, B)
SQL 6.4 A

select A, B, C
FROM TABLE
WHERE A > :A
      AND
      B > :B

SQL 6.4 B

select A, B, C
FROM TABLE
WHERE A > :A
      OR
      B > :B

在SQL 6.4 A中,单个索引片将会别扫描,由谓词A > :A定义,因为他是个范围谓词,所以他是唯一的一个匹配字段。B字段因为索引筛选将会在此次扫描中被检查,如果谓词B > :B不满足条件,则索引条目将会被忽略。

在SQL 6.4 B中,由于OR操作符的存在,数据库管理系统不能只读这一个索引片,因为即使不满足谓词A > :A,谓词B > :B的条件也可能会满足。可行的替代方案有全索引扫描,全表扫描,以及多索引访问(如果存在另一个以字段B开始的索引的话)。

在这个例子中,我们不能责怪优化器。有时候一个拥有复杂where子句的游标可能被拆分为多个带有简单where子句的游标来避免此类问题。大体上,假设一个谓词的判定结果为false,而这时如果不检查其他谓词就不能确定地将一行记录排除在外,那么这类谓词对优化器而言就是太过困难的。在SQL 6.4 B中,仅仅因为不满足谓词A > :A或仅仅因为不满足谓词B >:B是无法排除一行记录的。在SQL 6.4 A中,如果一行记录不满足谓词A > :A,那么该条记录就可以被排除而不必检查它是否满足谓词B > :B。

这样的谓词被称为非布尔谓词(non-Boolean term)或被称为非BT谓词(non-BT)。如果像SQL 6.4 A那样在where子句中只有AND操作符,那么所有简单谓词都是BT谓词,就是好的谓词,因为只要任何一个简单谓词被判定为不满足条件,那么这一行就可以被排除。

总结

除非访问路径是一个多索引扫描,否则只有BT谓词可以参与定义索引片。

我们来考虑一个定义身材高大的男人的where子句
SQL 6.5

where sex = M
      and
      (weight > 90 or height > 190)

只有一个简单谓词sex='M'是BT谓词。如果weight谓词判定为false,hright谓词的判定结果可能为true也可能相反。这两个谓词都无法仅通过自己的判定结果来排除一行记录。

这就是为什么我们在用理想索引设计方法来设计候选索引A和B时提出了这样的结论 : 只将那些对优化器而言足够简单的谓词添加至索引上。对于SQL 6.5而言,在设计方法的第一步中,只有SEX字段可以被用到。

in谓词

谓词col in (:hv1, :hv2 ...)可能会给优化器制造一些麻烦。例如,在DB2 for z/OS V7中,只有一个IN谓词能够被用来做匹配,即能够参与定义索引片,不过参与匹配的IN谓词中的字段不一定是最后一个匹配字段。因此,当理想索引A被选中时,选择性最高的IN谓词的字段应当在第一步就包含进索引。SQL 6.6提供了一个例子。
SQL 6.6

SELECT A, B, C, D, E
FROM TX
WHERE A = :A
      AND
      B IN (:B1, :B2, :B3)      FF = 0...10%
      AND
      C > 1
      AND
      E IN (:E1, :E2, :E3)      FF = 0...1%

下面的候选A中的数字对应于我们在前面描述的步骤

候选A

  1. 挑选出字段A和E作为索引的起始字段
  2. 加上字段C
  3. 无字段
  4. 加上字段B和D

候选索引A为(A, E, C, B, D)或者以下变化形式中的任何一个

  • (A, E, C, D, B)
  • (E, A, C, D, B)
  • (E, A, C, B, D)

对于SQL 6.6而言,所有这些候选索引都满足MC = 3。数据库管理系统读三个索引片(A, E1, C),(A, :E2, C)和(A, :E3, C),而无需使用其他任何特殊的方式。字段B的IN谓词将参与筛选过程。如果需要有4个匹配字段(即9个索引片),那么我们很可能需要把SELECT语句拆分成三个,每个拆分出的语句只有IN谓词字段列表中的一个字段。第一个SELECT语句包含谓词B = :B1,第二个为B = :B2,第三个为B = :B3。这三个select语句可以通过一个union all再结合为一个游标。对这个游标进行explain将显示三次MC = 4。

候选B

IN谓词的字段不应该在第一步就被选择出来,否则这将破坏结果集的顺序要求,从而必须进行一次排序操作了。

过滤因子隐患

贯穿本书,我们关注于最糟糕的场景,这些场景都是在输入值对应于最大过滤因子时出现的。然而,情况并不总是如此,如下
SQL 6.7

SELECT B
FROM TABLE
WHERE A = :A      FF = 1%
      AND 
      C > :C      FF = 0...10%
ORDER BY B
WE WANT 20 ROWS PLEASE

在这个语句中,只要程序获取了20行记录,屏幕将会把数据展示给用户,而不管整个结果集的大小是多少。

为了解释过滤因子对性能的隐式影响,我们来比较一下过滤因此处理两种极端情况下的QUBE结果。第一种极端情况下提供可能的最小结果集,即0行记录;第二种则提供很大的结果集(1000行记录)。这两种场景下使用都使用索引(A,B,C)。两种场景下,匹配字段A都拥有相同的过滤因子,结果集的大小完全取决于字段C。

先考虑下第二种场景,通常我们认为这将带来最糟糕的性能。一次索引匹配扫描(1 MC,只需要扫描索引而无需访问表)将访问一个包含20 * 10行记录的索引片,因为必须扫描10个索引行才能提供出结果集中的一行记录(字段C的FF为10%)

索引      (A, B, C)            TR = 1      TS = 200
表        TABLE                TR = 0      TS = 0
提取      20 * 0.1ms
LRT                            TR = 1      TS = 200
                               1 * 10ms    200 * 0.01ms
                               10ms + 2ms + 20 * 0.1ms ≈ 14ms

在第一种场景下,没有记录满足字段C的谓词条件,所以过滤因子为0%。当然,数据库管理系统必须扫描索引行中的每一行,即1000000的1%才会知道这种情况。

索引      (A, B, C)            TR = 1      TS = 10000
表        TABLE                TR = 0      TS = 0
提取      0 * 0.1ms
LRT                            TR = 1      TS = 10000
                               1 * 10ms    10000 * 0.01ms
                               10ms + 100ms + 0 * 0.1ms ≈ 110ms

很明显,对于整个事务而言,最差场景是结果集为空的场景,或者额需要扫描整个索引片才能得出结果集的场景。更糟糕的是,如果这个案例不能做到值扫描索引或者对应的索引不是聚簇索引,那么就需要进行额外的10000次随机读取,而这将消耗近两分钟时间,结果却是发现没有一行满足条件的数据。

如果索引匹配扫描过程中扫描的是一个更厚的索引片,比如字段A的过滤因子为20%,那么即便只需要扫描一个索引,LRT也将达到2s(200000 * 0.01ms)!当查询结果只需要一屏幕的数据时,满足第一颗星(最小化待扫描的索引片厚度)比满足第二颗星(避免排序)更为重要。

当以下三个条件同时满足时,这种过滤因子隐患可能会产生 :

  1. 访问路径中没有排序
  2. 第一屏结果一建立就回应
  3. 不是所有的谓词字段都参与定义带扫描的索引片--换句话说就是,不是所有的字段都是匹配字段。

这个例子满足了所有这三个条件

  • 访问路径中没有排序,因为字段B(Order By字段)紧接在等值谓词字段A后面,并且字段A是索引的第一个字段。
  • 第一屏结果一建立就响应
  • 一个谓词字段(字段C)不参与定义待扫描的索引片

我们应该充分理解这三个条件背后的逻辑

  • 如果条件1和条件2不为真,那么完整的结果集表总是会被数据库管理系统或应用程序(通过进行FETCH操作来物化)。
  • 如果条件3不为真,就不存在索引过滤(因为所有的谓词字段都是匹配字段),索引片中每一个索引行都是满足条件的。即使结果集为空,数据库管理系统也只需要通过一次访问就能判断出这一情况。

过滤因子隐患的例子

在这个例子中,用户在开始查询前首先在一个弹出窗口中选择姓(lname)和城市(city),随后打开SQL 6.8。我们之前分析这个例子时需要的是整个结果集,而在这个例子中,程序发起的FETCH调用不会超过20次,也许刚好够填满一个屏幕。下一个查询事务将显示接下来的20条结果。以此类推。这种方式使程序变得复杂,但当结果集很大时,这种方式提升了第一屏结果的响应速度。和之前一样,我们将最大的过滤因子假设为1%(lname)及10%(city)。
SQL 6.8

SELECT CNO, FNAME
FROM CUST
WHERE LNAME = :LNAME
      AND
      CITY = :CITY
ORDER BY FNAME
WE WANT 20 ROWS PLEASE

表上有三个索引 : (CNO)(聚簇索引),(PNO),(LNAME, FNAME)

是否有一个已经存在的或者计划创建的索引,包含了where子句中涉及的字段(一个半宽索引)?

如果我们决定使用索引(LNAME, FNAME),那么问题将严重到何种程度?最糟情况下的QUBE结果将回答这个问题。

当数据库管理系统选择索引(LNAME, FNAME)时,结果行是按照所要求的顺序获取的(ORDER BY FNAME),也就不需要进行排序操作。因此,数据库管理系统不会做早期的物化 : OPEN CURSOR不会引起表访问,不过FETCH操作将会同时对索引和表进行访问,其中的一些FETCH操作会返回结果行,而另外大部分的FETCH操作则不会。

最先发生的两次访问(一次对索引,一次对表)产生了一行结果行记录,因为第一个JONES生活在LONDON。在第一次FETCH调用满足后程序便发起了第二次FETCH调用。下一个JONES并不生活在LONDON,所以没有产生结果行,于是会继续进行更多的对索引及表的访问,知道生活在LONDON的JONES被找到以满足FETCH调用。因为只有10%的用户生活在LONDON,所以平均一个FETCH调用需要检查10个JONES条目--10次索引访问及10次表访问才能将一行记录加入到结果表中。

索引      LNAME, FNAME            TR = 1      TS = 199
表        CUST                    TR = 200    TS = 0
提取      20 * 0.1ms
LRT                               TR = 200      TS = 199
                                  200 * 10ms    199 * 0.01ms
                                  2s + 1.99ms + 20 * 0.1ms ≈ 2s

根据QUBE得出的本地响应时间是2s,也许还是可以接受的。但这就是最糟糕的情况了么?假定过滤因子CITY = :CITY为10%,LANME = :LNAME为1%已经是最大值了。然而,在这个例子中,产生最大结果集的过滤因子值并不是最糟糕的情况,本例是过滤因子陷阱的另一个例子。过滤因子陷阱的三个条件都适用于索引(LNAME, FNAME)进行处理这个游标

  • 访问路径上没有排序
  • 第一屏结果一建立就返回结果
  • 不是所有谓词都参与了定义待扫描的索引片。

因此,为了创建一条“不存在符合条件的用户”的消息,所花费的时间比返回一个大结果集的第一页数据所花费的时间更长。

让我们考虑一种极端的情况,数据库管理系统必须扫描一个最大的索引片,但最终只产生了一个空的结果集。一个城市中,没有任何一个客户的姓是常见的姓,此时数据库管理系统仍旧必须扫描整个索引片,并对每一个索引进行相应表行的检查。

索引      LNAME, FNAME            TR = 1          TS = 1000000 * 1%
表        CUST                    TR = 10000      TS = 0
提取      0 * 0.1ms
LRT                               TR = 10001      TS = 10000
                                  10001 * 10ms    10000 * 0.01ms
                                  100s + 100ms + 0 * 0.1ms ≈ 100s

将近2分钟而不是2秒钟!索引必须要改善,此时我们将再一次面对那个难题 : 是选择刚好够用的最低成本的改造,还是设计一个最佳索引,又或是设计一个折中的方案?

最佳索引

(LNAME, CITY, FNAME, CNO)

索引      LNAME, CITY, FNAME, CNO            TR = 1          TS = 20
表        CUST                               TR = 0          TS = 0
提取      20 * 0.1ms
LRT                                          TR = 1          TS = 20
                                             1 * 10ms        20 * 0.01ms
                                             10ms + 0.2ms + 20 * 0.1ms ≈ 12ms

以上对应的是在最糟糕场景下使用候选A索引的情况 : 至少有20行结果记录。在使用理想索引的情况下,当整个结果集无法显示在一个屏幕时,建立一个有20行记录的结果表需要21次索引访问。使用索引(LNAME, FNAME)与使用索引(LNAME, CITY, FNAME, CNO)的最糟输入相应时间相差约4个数量级。

如果结果集表为空,那么使用这个三星索引只需要一次索引访问就可以判定出来。从定义上能看出,三星索引不满足陷阱发生条件列表中的第三条。

然而不幸的是,一个三星索引意味着额外增加一个索引,因为在现有的索引中,LNAME和FNAME之间增加一个CITY字段可能会对现有程序造成负面影响。

半宽索引(最大化索引过滤)

在现有索引(LNAME, FNAME)的最后增加谓词字段CITY可以消除绝大多数的表访问,因为在这个简单的场景中,优化器可以进行索引过滤,仅当CITY值符合条件时才读取表行。

索引(LNAME, FNAME, CITY)能够通过BQ测试 : 所有的谓词都在索引中。然而,他仅仅是一星索引。符合条件的的索引行在物理上并不挨在一起(MC仅为1),并且索引不是宽索引。那这个廉价的解决方案是否能提供可接受的响应时间呢?

现在是会用这个索引的游标落入了过滤因子缺陷的范畴。因此我们必须估算使用索引(LNAME, FNAME, CITY)时过滤因子的极限值。其中一个过滤因子将导致这种访问路径下最糟糕的性能。

最大结果集(例如1000行)

FF (LANE = :LNAME) = 1%
FF (CITY = :CITY) = 10%
FF (LANE = :LNAME and CITY = :CITY) = 0.1%

因为CITY的过滤因子为10%,所以每10个索引行满足一次谓词判定CITY = :CITY。所以为了找出一行满足条件的结果记录,平均需要访问10次索引。

索引      LNAME, FNAME, CITY                 TR = 1          TS = 200
表        CUST                               TR = 20          TS = 0
提取      20 * 0.1ms
LRT                                          TR = 1          TS = 200
                                             21 * 10ms        200 * 0.01ms
                                             210ms + 2ms + 20 * 0.1ms ≈ 214ms

空结果集

FF (LANE = :LNAME) = 1%
FF (CITY = :CITY) = 0%
FF (LANE = :LNAME and CITY = :CITY) = 1%

索引      LNAME, FNAME, CITY                 TR = 1           TS = 10000
表        CUST                               TR = 0           TS = 0
提取      0 * 0.1ms
LRT                                          TR = 1          TS = 10000
                                             1 * 10ms        10000 * 0.01ms
                                             10ms + 100ms + 0 * 0.1ms ≈ 110ms

宽索引(只需访问索引)

向上述半宽再添加一个字段,使得索引变宽 : (LNAME, FNAME, CITY, CNO)

最大结果集(例如1000行)

索引      LNAME, FNAME, CITY, CNO                 TR = 1          TS = 200
表        CUST                                    TR = 0          TS = 0
提取      20 * 0.1ms
LRT                                               TR = 1          TS = 200
                                                  1 * 10ms        200 * 0.01ms
                                                  10ms + 2ms + 20 * 0.1ms ≈ 14ms

空结果集

索引      LNAME, FNAME, CITY, CNO                 TR = 1           TS = 10000
表        CUST                                    TR = 0           TS = 0
提取      0 * 0.1ms
LRT                                               TR = 1          TS = 10000
                                                  1 * 10ms        10000 * 0.01ms
                                                  10ms + 100ms + 0 * 0.1ms ≈ 110ms

在空的结果集的场景下,本地响应时间保持在0.1s。从一个多屏结果集中产生一个屏幕结果集的事务现在变得非常快,因为不再需要对表进行20次TR了。

总结

  1. 性能最差的场景取决于使用的索引

    • 对于原始索引而言,在结果集为空时性能最差(最常见的LNAME减伤最不常见的CITY)
    • 对于半宽索引而言,虽然我们的例子中是结果集表最大时性能最差,但性能最差的场景可能是这两者(结果集为空和结果集表最大)中的任何一个,这取决于表访问和索引片扫描的相对成本。(根据QUEB)20次TR所耗费的是时间(200ms)等于20000次TS所耗费的时间(200ms)。若每屏的行数减少,或索引片更厚,那么结果集为空就可能称为性能最差的场景。
    • 对于宽索引而言,结果集为空时性能最差。
    • 对于三星索引而言,结果集最大时性能最差(结果集为空时只需要进行一次索引访问)。
  2. 原始索引在结果集为空时(而不是结果集最大时)称为一个灾难。这是因为它需要进行大量的表访问来校验CITY字段。
  3. 半宽索引及宽索引都能提供还不错的性能,即使是在结果集为空的情况。两个索引都避免了不必要的表访问,并且索引片扫描的代价也相对较小。随着由字段LNAME定义的索引片的数据增长,本地响应时间的增长也相对缓慢。如果一个公司拥有很多来自South Korea的顾客,那么有一天LEE的索引片可能包含100000索引行。在结果集这么大的情况下,这两个索引依旧能够提供比较不错的响应时间;最差的输入值所带来的响应时间将会是1s(100000*0.01ms)。
  4. 相比使用半宽索引,宽索引的优势在于避免了前20行结果的表访问。
  5. 尽管使用三星索引可能会得到一些好处,尤其是在结果集为空的情况下,但这一好处并不明显,并且会引入由于添加了一个新索引而产生的额外负载。
  6. 如果在LEE的索引片变得非常厚,一个三星索引可能更适合。不过存在一个上限,大约是100000次TS,这时扫描索引片将耗费太长时间以致我们需要创建一个新的索引。对于本例而言这个新的索引就是三星索引。

对于本次分析中我们所考量的各种索引的总结如下所示。

类型索引LRT维护
现有索引LNAME,FNAME100s-
半宽索引LNAME, FNAME, CITY0.2sU CITY + 10-20ms
宽索引LNAME, FNAME, CITY, CNO0.1sU CITY + 10-20ms
三星索引LNAME, CITY, FNAME, CNO0.01sI/D + 10ms U + 10-20ms

在之前的例子中,我们一直心照不宣地假定结果集为空的情况是导致性能最糟糕的场景。然而,我们应该意识到,一个几乎为空的结果集将导致更糟糕的性能,因为在使用半宽的索引的情况下,这将引起不超过20次的表访问。

练习

架设一张表,表中有10000000条记录,表上有索引(A)和(C),(A)为聚集索引
SQL 语句如下,在当前的索引条件下,这个sql执行需要花费一分钟。

SELECT C,E,F
FROM TABLE
WHERE B = :B
      AND
      C > :C
      AND
      D = 1
ORDER BY C

练习 6.1

用两种方案设计最佳的可能索引 : (1)不增加额外的第三个索引,(2)增加第三个索引
假设C的过滤因子为X,结果的行数为Y,则QUBE结果如下,可以看出,主要是回表产生的随机读很多,想办法把这部分消除掉,就能有很大提升

索引      C                 TR = 1                         TS = 10000000 * x
表        CUST              TR = 10000000 * x + 1          TS = 0
提取      Y * 0.1ms
LRT                         TR = 10000000 * x + 2          TS = 10000000 * x
                            (10000000 * x + 2) * 10ms        10000000 * x * 0.01ms
                            (10000000 * x + 2) * 10ms + 10000000 * x * 0.01ms + Y * 0.1ms ≈ 60000ms

(1)不增加额外的第三个索引

增加一个宽索引,相对于原索引,将会减少 100000000 * x + 10 毫秒,随着X(C的过滤因子)的不同,减少的时间会变化,过滤因子越大,优化的百分比越高。

索引      C, B, D, E, F                 TR = 1          TS = 10000000 * x
表        CUST                          TR = 0          TS = 0
提取      Y * 0.1ms
LRT                                     TR = 1          TS = 10000000 * x
                                        1 * 10ms        10000000 * x * 0.01ms
                                        1 * 10ms + 10000000 * x * 0.01ms + Y * 0.1ms ≈ ?
(2)增加第三个索引

增加一个三星索引, 假设B和D的过滤因子分别为Z,H,相对于原索引,随着B,D,C字段的过滤因子的不同,较少的时间会变化,过滤因子越大,优化的百分比越高

索引      B, D, C, E, F                 TR = 1          TS = 10000000 * x * Z * H
表        CUST                          TR = 0          TS = 0
提取      Y * 0.1ms
LRT                                     TR = 1          TS = 10000000 * x * Z * H
                                        1 * 10ms        10000000 * x * Z * H * 0.01ms
                                        1 * 10ms + 10000000 * x * Z * H * 0.01ms + Y * 0.1ms ≈ ?

练习 6.2

假设1s是我们可接受的执行时间,并且我们不愿增加一个新索引。对于上一题中的第一个方案,你需要了解哪些信息来语句它所能带来的性能提升?
需要了解C的过滤因子和符合筛选条件的记录的总条数

数据库索引设计与优化02-BE与QUBE

前瞻性的索引设计

发现不合适的索引

一旦一个应用的明细方案确定下来,就应该确认当前的额索引对新的应用来说是否合适。为了完成这个工作,可以使用两种简单,快速并且可行的方法

  1. 基本问题法(Basic Question, BQ)
  2. 快速上线估算法(Quick Upper-Bound Estimate, QUBE)

基本问题法(BQ)

是否已有一个已存在的或者计划中的索引包含了where子句所引用的所有列(一个半宽索引)?

  • 如果答案是否,那么我们应该首先考虑将缺少的谓词列加到一个现有的索引上去。这将产生一个半宽索引,尽管索引的等值匹配过程并不令人满意(一星),但是索引过滤可以保证回表访问只发生在所有查询条件都满足的时候。
  • 如果这还没有达到足够的性能,那么下一个选择就是将所有涉及的列都加到索引上,以使访问路径只需要访问索引。这将产生一个避免所有表访问的宽索引。
  • 如果select仍然很慢,就应该用前面的候选索引算法来涉及一个新的索引。根据定义,这将是所能实现的最佳索引。

如何确认第一个方案(半宽索引)或第二个(宽索引)方案能否让select在最差输入下仍然运行的足够快?

如果可以访问生产库或者类似生产库的测试库,我们可以每次创建一个索引,用最差输入来测试响应时间。为了确保测得的响应时间接近于在正常生产库上允许的性能表现,我们必须把缓冲池考虑进来,并观察每个事务的缓冲池命中率。测试的第一个事务很可能在缓冲池中没有发现相应的缓存页,所以在最差输入下的磁盘读的指标会比正常环境要高。此外第一个访问索引的事务必须要等待文件被打开。而另一方面,如果变量的输入值保持不变,那么第二个访问改索引的事务将很可能会获得100%的缓冲池命中率(没有磁盘读)。为了获得具有代表性的响应时间,每个索引方案都应在进行过有预热事务后再开始测试,可以通过传入一些典型值(但不是最差情况的值)来打开文件,并将大部分非页子索引页加载到数据库的缓冲池中。这样,使用最差输入值的的事务就会有一个比较有代表性的响应时间了,至少我们已经把CPU和磁盘读的服务时间考虑进来了。

实际上,使用第二种方法,QUBE来评估索引方案将不会那么乏味。

如果模拟测试和QUBE方法都没有实施,那么就应当选择使用宽索引方案,并在应用切换到生成环境后立即启用异常报告来发现那些连宽索引都无法满足的性能要求的场景。如果必要的话,我们需要为那些运行缓慢的查询设计我们所能达到的最佳索引。

注意

对于BQ的一个肯定的回答并不能保证足够的性能。记住,BQ的目的是确保我们至少可以通过索引过滤来最小化对表的访问,除此之外没有其他作用。

举个例子,假设有一个select,谓词是where b = :b and c = :c,唯一有用的索引是(A, B, C)。这个索引包含了该select的所有谓词列,因此BQ方法检查这个select并不会产生告警。然而这个查询的访问路径将会是全表扫描。如果该表有超过100000条记录,那么查询将运行的非常慢。索引过滤本身并不意味着这个索引有三颗星中的任何一颗。

快速上限估算法(QUBE)

这个快速估算法输出的结果是本地响应时间(LRT),即在数据库服务器中的耗时。在单层环境(指发出SQL调用的应用与数据库部署在同一台机器上)中,一个传统事务的LRT是指用户和数据库服务器之间一次交互的响应时间,不包括所有的网络延迟。在多层环境(客户端/服务器)中。各层之间的通信时间也被排除在外。批量任务的LRT是指执行任务耗时。任何任务队列中的排队时间也被排除在外。

服务时间

在简单的场景下(I/O时间和CPU时间不重叠),服务时间等于CPU时间加上排除了磁盘驱动排队的随机读时间。如果没有资源竞争,则本地响应时间等于服务时间。

排队时间

在一个常规的多用户环境中,程序的并发会导致对所需资源的各种竞争,因此这些并发的程序不得不排队开获取这些资源。以下资源在LRT的范畴内 :

  • CPU排队时间(所有处理器都忙着处理更高优先级的任务)
  • 磁盘驱动器排队(包含请求页的驱动器处于繁忙状态)
  • 锁等待(请求的表或行被锁定在一个不兼容的级别)
  • 多道编程的级别,线程数或其他方面已经达到上限(这些都是为了防止资源过载而设计的系统值)

QUBE会忽略除磁盘驱动排队以外的其他所有类型的排队,以提供一个简单的评估过程,用于评估那些对性能影响特别大的方面。

在排除上述因素后,我们得到的是一个非常简单的估算过程,仅需两个输入变量,TR和TS,必要时还有第三个输入变量,F。这些就可以将SQL的处理及I/O成本考虑进来了,并且他们是影响索引设计的主要因素。如果是使用QUBE来比较多个可选访问路径之间的性能差异,由于FETCH调用的次数是相同的,所以可以忽略FETCH调用次数带来的影响。如果是使用QUBE来确定响应时间,那就需要包含FETCH调用的次数。

比较值
LRT = TR * 10ms + TS * 0.01ms

绝对值
LRT = TR * 10ms + TS * 0.01ms + F * 0.1ms

LRT = 本地响应时间
TR = 随机访问的数量
TS = 顺序访问的数量
F = 有效FETCH的数量

基本概念

访问

根据定义,DBMS读取一个索引行或一个表行的成本称为一次访问 : 索引访问或表访问。如果DBMS扫描索引或表的一个片段(被读取的行在物理上是彼此相邻的),那么第一行的读取即为一次随机访问。对于后续行的读取,每行都是一次顺序访问。在当前的硬件条件下,顺序访问的成本比随机访问的成本低得多。一次索引访问的成本与一次表访问的成本基本上是相同的。

读取一组连续的索引行

物理上彼此相邻是什么意思?

索引上的所有行都通过指针链接在一起,链接的先后顺序由索引的键值严格定义。当几个索引行的键值相同时,就根据索引行存储的指针值进行链接。在传统的索引设计(从某个角度看,是理想化的)中,链表从LP1(叶子页1)开始,随后链接LP2,以此类推。这样(假设每个磁道可以放12个叶子页,当前的硬件通常可以容纳更多),叶子页就组成了一个连续的文件,LP1至LP12存储在磁盘柱面的第一个磁道,LP13至LP24存储在下一个磁道,如此继续,当第一个柱面存满后,下一组LP就会被存储在下一个柱面的首个磁道上。换句话说,就是叶子页之间没有其他页。

现在,读取一个连续的索引行(即一个索引片,或者包含了单个键值或者一个范围的键值所对应的索引行)就非常快了。一次磁盘旋转会将多个叶子页读取进内存中,而且只有在磁盘指针移到下一个柱面时才需要进行一次短暂的寻址、

不过,这个完美的顺序还是会被打破的,至少有以下三个影响因素 :

  1. 如果一个叶子页没有足够的空间存储新插入的索引行,那么叶子页就必须被分裂。之后链表仍会按照正确的顺序链接索引行,但是这与底层的物理存储顺序就不再一致了,一些按道理应该是顺序的访问就变成随机访问了。不过索引的充足可以再次恢复最理想的顺序。
  2. 意向不到的数据增长可能会填满原本连续的空间(区或类似的概念)。操作系统于是就会寻找另外有一个连续的空间,并将它连接到原来空间的后面。这时候从第一个区跨到第二个区访问就会产生一次随机访问,不过这种情况影响不大。
  3. RAID 5条带会将前几个叶子页存储在一个驱动器上,将后面的叶子页存放在另外的驱动器上。这就会产生额外的随机读,但实际上条带的积极作用要大过随机读带来的性能恶化,一个智能的磁盘服务器可以将后续的叶子页并行的从多个驱动器上读取至磁盘缓存中,从而大大降低了单个叶子页的I/O时间。此外,在RAID 5条带策略下,一个被频繁访问的索引的不太可能导致某一个磁盘负载过高,因为I/O请求会被均匀低分布到RAID 5阵列内的多个磁盘驱动器。

忽略上述情况,我们仍然假设,如果两个索引行在链表上彼此相邻(或者在唯一索引中,相同键值的行指针意味着彼此相邻),那么我们就认为这两行在物理上也相邻。这就意味着QUBE认为所有的索引都有最理想的顺序。

读取一组连续的表行

读取一组连续的表行有如下两种情况。

  1. 全部扫描
    从TP1(表页1)开始,读取该页上所有的记录,然后再访问TP2,一次类推。按照记录在表页中存储的顺序进行读取,没有其他特殊的顺序。
  2. 聚簇索引扫描
    读取索引片上第一个索引行,然后获取相应的表行,再访问第二个索引行,以此类推。如果索引行与对应的表行记录顺序完全一致(聚簇率为100%),那么除了第一次之外的所有表访问就都是顺序访问。表记录的链接方式跟索引不一样。单个表页中记录的顺序无关紧要,只要访问的下一个表记录在同一个表页或者相邻的下一个表页内就可以了。

同索引一样,存储表的传统方式也是将所有表页保留在一个连续的空间内。引起顺序杂乱或碎片化的因素也和索引中的相似,但又两个地方不同

  1. 如果往表中插入的记录在聚簇索引所定义的主页中装不下,则通常不会移动现有的行,而是会将新插入的记录存储到离主页尽可能近的表页中。对第二个页的随机I/O会使聚簇索引扫描变得更慢,但是如果这条记录离主页很近,这些额外的开销就可以被避免,因为顺预读功能会一次性将多个表页装载到数据库缓存中。即使顺序预读功能没有使用,也只有当该页在数据库缓存被覆盖的情况下才会发生额外的随机I/O。
  2. 一条记录被更新后,可能因为表行过长导致其无法再存储于当前的表页中。这是DBMA就必须将该行记录迁移至另外一个表页中,同时在原有的表页中存储指向新表页的指针。当该行被访问时,会引入额外的随机访问。

表可以通过重组来还原行记录的顺序,从而减少不必要的随机访问。因此,如果行记录存储在同一个页或者相邻的页当中,QUBE就认为他们在物理上彼此相邻。换句话说,QUBE假设所有的表,所有都是以最理想的顺序组织的。

之前我们做过一些最差场景的的假设,在这种假设下,QUBE足够简单,能够快速且方便的使用。同样的原因,我们也有必要做一些乐观的假设。尽管存在上述问题,根据QUBE的定义,扫描索引或表的一个片段只需进行一次随机访问。数据库专家们需要对重组的必要性进行监控,以保证我们所做的这些乐观假设都是合理的。

计算访问次数

既然访问对于QUBE而言如此重要,我们现在就解释下如何确定索引及表的访问次数,包括随机访问和顺序访问。

随机访问

我们首先思考一下磁盘读与访问的区别。一次磁盘读所访问的对象是一个页,而一次访问的访问对象则是一行。一次随机磁盘读会将一整页(通常会包含很多行)读取至数据库的缓冲池中,但是根据定义,前后两次随机读不太可能会访问到同一个页。因此,QUBE中单次随机访问所消耗的时间与一次磁盘随机读的平均耗时是一样的,都是10ms。虽然随机读是同步的,但是由于现在的处理器速度非常快,所以在估算单次随机访问开销时可以忽略CPU时间,这一CPU时间通常小于0.1ms。

顺序访问

一次顺序访问是指读取物理上连续的下一行,这一行要么存储在同一页中,要么在下一页中。由于顺序读的CPU时间与I/O时间是重叠的(DBMS和磁盘控制器通常会预读一些页),因此顺序访问的消耗时间就是两者中较大的那个。在QUBE中,一次顺序读所消耗的时间是0.01ms。

当计算访问次数时,为了简单起见,我们会遵循以下规则:

  1. 忽略索引的非叶子节点。我们假定他们都在数据库的缓冲池中,或者至少在磁盘服务器的读缓存中。
  2. 假设DBMS能够直接定位到索引片的第一行(忽略为了定位索引行的位置而使用二分查找或者其他技术所耗费的时间)。
  3. 忽略跳跃使顺序读所节省的任何时间。这可能是一个很悲观的假设。
  4. 假设所有的索引和表都处于理想的组织顺序。如上所述,这可能是个很乐观的假设,除非表和索引都被很好的监控着。

当计算索引访问次数时,可以将索引看成一个微表,他的行数与其指向的表包含的行数相同,且按照索引键值排列。

当计算表访问次数时,我们假设表行在表页中是按理想顺序排序的,这个顺序依赖于表的组织方式。我们可以假设一次全表扫描(N行数据)将需要一次随机访问和N-1次顺序访问。

FETCH处理

被接受的行的数量可以通过FETCH调用的次数来确定(除非多行FETCH可用),这些行将经历更多的处理程序。TS不包含这个额外的处理过程。现在,我们需要将第三个输入变量,LRT组成中的F考虑进来。F的成本比TS大一个数量级,而另一方面,他比TR的成本要小很多。

如果QUBE被用来比较可选的访问路径,比如比较全表扫描与使用特定索引,那么F参数是无关紧要的,因为他的值在两种情况下相等,我们只需要考虑TR和TS。但如果使用QUBE来确定LRT,F参数就可能会比较重要,这取决于FETCH调用的次数。

在处理被接受了的行时,可能会涉及排序操作,排序的整体开销通常与FETCH的行数成正比。大多数情况下,相对于在所接受的行上进行其他处理过程,排序的开销非常小,所以他被隐藏在F的开销内。然后有些场景却不是这样,这个将会在后面讨论。除了这些例外场景外,我们都假定排序开销包含在F参数中。

请注意,在计算FETCH调用次数时,为了避免混淆,我们将忽略“判断没有更多符合条件的行”的那次调用,这纯粹是为了简化计算。例如,一张有10000条计算的表上的一个过滤因子为1%,期望的返回行数是100,那么我们就假设F为100而不是101.

主要访问路径的QUBE示例

示例5.1 : 主键索引访问

SELECT CNO,LNAME,FNAME
FROM
WHERE CNO=:CNO

通过主键索引读取一个表行需要随机访问一次表和随机访问一次索引。

索引 CNO         TR = 1
表   CUST        TR = 1
提取 1 * 0.1ms
LRT        TR = 2
           2 * 10ms + 0.1ms ≈ 20ms

示例5.2 : 聚簇索引访问

SELECT CNO,LNAME,FNAME
FROM CUST
WHERE ZIP = :ZIP AND LNAME = :LNAME
ORDER BY FNAME

假设区号为(ZIP)30103的地区内有1000位名为Joneses的客户,通过这个二星索引的两个匹配列访问了一个薄的索引片。这里不需要排序,因为索引已经提供了所需的顺序。

扫描1000行的索引片需要多长时间?首先,需要进行一次随机访问来找到索引片上的第一条符合条件的索引行,然后DBMS继续向前读取,知道找到列ZIP和LNAME与输入值不匹配的行为止。包括访问的最后一个不匹配的行,索引的访问数是1000。因此,根据QUBE,扫描索引片需要1 10ms + 1000 0.01ms = 20ms。

因为列CNO不在索引中,所以必须从表中读取该列。由于索引是聚簇的,而且我们假设表中的1000行是相邻的,于是扫描表片段的成本将会是1 10ms + 999 0.01ms ≈ 20ms。此外,还有FETCH调用的时间,根据QUBE,总的响应时间为140ms。由于顺序访问的成本非常低,因此表的访问成本是非常低的。最大的开销来自DETCH调用的过程。

索引 ZIP,LNAME,FNAME TR = 1      TS = 1000
表   CUST = 1                    TS = 999
提取 1000 * 0.1ms

LRT     TR = 2      TS = 1999
        2 * 10ms    1999 * 0.01ms
        20ms + 20ms + 100ms = 140ms
        20ms + 20ms + 1000 * 0.1ms
        20ms + 20ms + 100ms = 140ms

示例5.3 : 非聚簇的索引访问

SELECT CNO,LNAME,FNAME
FROM CUST
WHERE ZIP = :ZIP AND LNAME = :LNAME
ORDER BY FNAME

如果表的记录不是通过聚簇索引访问的,那么表的1000次访问就会变成随机访问。

索引      ZIP,LNAME,FNAME      TR = 1      TS = 1000
表        CUST                 TR = 1000   TS = 0
提取      1000 * 0.1ms
LRT                            TR = 1001   TS = 1000
                               1001 * 10ms 1000 * 0.1ms
                               10s + 10ms + 1009 * 0.1ms ≈ 10s

在这种情况下,将列CNO加到索引中让索引称为三星索引会是个好方案,因为这样能使响应时间从10s降低至0.1s

索引      ZIP,LNAME,FNAME      TR = 1      TS = 1000
表        CUST                 TR = 0      TS = 0
提取      1000 * 0.1ms
LRT                            TR = 11   TS = 1000
                               1 * 10ms 1000 * 0.1ms
                               10ms + 10ms + 1009 * 0.1ms ≈ 120ms

示例 5.4 : 使用聚簇索引进行跳跃式顺序表访问

SELECT STREET, NUMBER, ZIP, BORN
FROM CUST
WHERE LNAME = 'JONES' AND FNAME = 'ADAM' AND CITY = 'LONDON'
ORDER BY BORN

5.4的查询将使用聚簇索引(LNAME,FNAME,BORN,CITY)。在这一索引条件下,要找到住在伦敦的所有名为Adam Joneses的客户,需要多少次随机访问和顺序访问呢?
假设LNAME和FNAME匹配之后的索引片大小为5

索引      LNAME,FNAME,BORN,CITY      TR = 1      TS = 4
表        CUST                       TR = 2      TS = 0
提取      2 * 0.1ms
LRT                                  TR = 3      TS = 4
                                     3 * 10ms    4 * 0.1ms
                                     40ms + 0.4ms + 2 * 0.1ms ≈ 30.4ms

使用满足需求的成本最低的索引还是所能达到的最有索引

实例1

我们已经通过一些例子展示了如何运用QUBE来方便的评估一些比较重要的访问方式,现在我们将展示如何使用这两种技术(BQ和QUBE)来完成整个索引设计过程。

假设有一张表,结构如下

create table `cust`(
`cno` varchar(8) primary key,
`pno` varchar(8) not null default '',
`fname` varchar(8) not null default '',
`lname` varchar(8) not null default '',
`city` varchar(8) not null default '',
key `idx_pno`(`pno`),
key `idx_lname_fname`(`lname`, `fname`)
)

再假设下其他的条件

  1. 表中有100万记录
  2. idx_lname_fname是唯一合适的索引
  3. 谓词lname = :lname的最大过滤因子是1%
  4. 谓词city = :city的最大过滤因子是10%
  5. 结果集最多包含10000条记录(1000000 1% 10%)
  6. 表中的记录按照cno列的大小顺序存储,主键索引cno是聚簇索引,或者表经常按照cno进行排序重组。

sql语句如下

select cno,fname
from cust
where lname = :lname and city = :city
order by fname
该事务的基本问题

是否有一个现有的或者计划中的索引包含了where子句中所涉及的所有列?

换句话说,我们是否现在或者即将有一个半宽索引?答案显示是没有,至少从现有的索引情况看是这样。这并不一定意味着存在问题,性能还是有可能令人满意的。但至少这是一个警示信号,我们应该去思考是否真的存在问题,如果是并且我们决定忽略它,那么它会变得多严重?我们是否需要将那些不在索引中的谓词列加到索引中?这样做将给BQ一个肯定的答案,但我们应该牢记这仍然不能确保良好的性能。

对事务上限的快速估算

当DBMS选择使用索引idx_lname_fname时,结果行是按照所请求的顺序(order by fname)被获取的,既不需要排序。因此,DBMS将不会进行早期物化 : OPEN CURSOR动作不会产生对表或索引的访问,而是在每次FETCH的时候访问索引和表,其中的一些访问会将记录行返回给FETCH,另一些(大部分)则不会。整个过程将引起一次所有扫描,匹配列只有一个,该列的过滤因子是1%。根据QUBE

索引      LNAME,FNAME      TR = 1      TS = 9999
表        CUST             TR = 10000   TS = 0
提取      1000 * 0.1ms
LRT                        TR = 10001  TS = 9999
                           10001 * 10ms    9999 * 0.1ms
                           100s + 1s + 1000 * 0.1ms ≈ 101.1s

很明显,这里有一个很明显的问题,即数据库必须读取10000条不相邻的表记录。这需要耗费很长时间,将近2分钟。

QUBE是一个对上限的估算法 : 结果100s意味着本地响应时间最多会达到100s。真实的LRT可能比这个值小,特别是当内存和缓冲池的大小对数据库来说非常充足时。这种情况下,许多随机访问将不再需要从磁盘驱动器上读取,随机访问的平均响应时间也可能比10ms低。不管怎么说,这不是一个错误的告警,对表的访问次数太高了。

由于QUBE是一个非常粗略的工时,因此在结果中显示多于一位的有效数字是具有误导性的,尽管为了清除起见我们会这么做。在真实的报告中,应该这样阐述结论 : 根据快速估算,被提议的索引能将最差情况的响应时间从2min降至1s。

使用满足需求的成本最低的索引还是所能达到的最有索引

设计索引并不只是单纯的为了最小化磁盘I/O的总量,而是为了设法让所有程序都运行的足够快,同时还能做到不使用过量的磁盘存储,内存和读缓存,且不使磁盘超载。

我们甚至可以将这种决策以数字的方式来表达,比如这样问 : 将一个每月运行50000次的事务的响应时间从2s~10s降低至0.1s~0.5s,同时节省1000秒的CPU运算时间,为达到这样的目的每个月支付10美元是否值得?

通过BQ,QUBE(或者其他方法),我们已经发现了这样一个事实 : 改SQL会由于没有合适的索引执行的非常慢。现在,我们面临一个困难的问题 : 我们应该给这个语句设计一个所能达到的最优索引,还是为期设计一个成本最低的满足性能要求的索引?又或是介于两者之间的某种索引?我们并没有一个硬性公式去解答这个问题。

该事务的最佳索引

使用前面描述的算法可以得到两个三星索引
(lname, city, fname, cno)或(city, lname, fname, cno)
这两个索引都有三颗星,因为他们会扫描一个非常薄的索引片,order by列跟在匹配列之后,从而避免了排序;此外,此查询只需要访问索引而无需回表。

索引      lname, city, fname, cno      TR = 1      TS = 999
表        CUST                         TR = 0      TS = 0
提取      1000 * 0.1ms
LRT                                    TR = 1      TS = 999
                                       1 * 10ms    999 * 0.01ms
                                       10ms + 10ms + 1000 * 0.1ms ≈ 120ms

在性能方面,这两个索引并没有什么区别,他们都极大的降低了成本。不幸的实际,三星索引将意味着额外增加一个索引,因为列(city)添加到现有索引的lname和fname之间可能会对现有的程序造成不利的影响。这就是为什么我们可以考虑一下开销更低的可选方案。

半宽索引(最大化索引过滤)

将谓词列city加至现有索引(lname,fname)的末端,可以消除大部分的表访问,因为会引入索引过滤过程。表中的行只有在确定包含所请求的city值的情况下才会被访问。

该索引(lname, fname, city)是满足BQ的,所有谓词列都在索引中。然而,他只有一颗星,所请求的索引行不是连续的,当然,他也不是宽索引。但是这种开销很低的方案是否满足需求了呢?

索引      lname, fname, city      TR = 1      TS = 9999
表        CUST                    TR = 1000   TS = 0
提取      1000 * 0.1ms
LRT                               TR = 1001   TS = 9999
                                  1001 * 10ms 9999 * 0.01ms
                                  10s + 100ms + 1000 * 0.1ms ≈ 10.2s

借助于这个半宽索引,LRT由原来的近2min降低到了10s,这是一个巨大的进步,但还不够(别忘了最佳索引下的运行时间仅为120ms)。我们仍有太多成本很高的TR,我们需要一个宽索引,已取得更好的性能。

宽索引(只需访问索引)

将一个列添加到半宽索引上,将使改索引变为一个宽索引 : (lname, fname, city, cno)。现在,我们有了两颗星,第二颗和第三颗,同时LRT变为了1s。

索引      lname, fname, city, cno      TR = 1      TS = 9999
表        CUST                         TR = 0      TS = 0
提取      1000 * 0.1ms
LRT                                    TR = 1      TS = 9999
                                       1 * 10ms 9999 * 0.01ms
                                       10ms + 100ms + 1000 * 0.1ms ≈ 210ms

下图提供了各种索引的性能比较,最后一列显示的是由此引起的额外的维护成本。需要注意的是,三星所以将是一个新的索引。对于这些成本提醒如下两点事项 :

  1. 插入和删除意味着修改一个叶子页。如果该子叶不在缓冲池或者读缓存中,那么整个叶子页就必须从磁盘驱动器上读取,无论锁添加或删除的索引行的长度是多少,这都将耗费10ms的时间
  2. 更新city列会导致响应时间增加10ms或20ms,具体的值取决于是否需要将索引行迁移至另一个叶子页。通过将cust列作为最后一个索引列的方式可以避免这一移动。
类型索引LRT维护成本
现有索引lname,fname100s-
半宽索引lname,fname,city10sU city + 10 - 20ms
现有索引lname,fname,city,cno0.2sU city + 10 - 20ms
现有索引lname,city,fname,cno0.1sI/D + 10ms U + 10 - 20ms

LRT是最差输入下的QUBE值,I = 插入,D = 删除,U = 更新

重要说明

在这个阶段,我们需要将所有变更的维护成本也考虑进来。比如,在将索引升级为宽索引时,理论上是将cno添加至索引的末尾,但实际上,将它放在city列的前面会更好,即(lname, fname, cno, city)。对于这个select而言,由于city列参与的是索引过滤过程,所以他的位置并不会对运行结果造成影响。

同样,当有多个等值谓词作为匹配列时,我们需要考虑这些列在索引上的先后顺序。经常变化的列应当尽可能的排在后面(相对于lname,用户更改地址的可能性更大,因此lname,city可能是比较适合的顺序)。另一方面,我们需要考虑新索引对于其他select的帮助有多大。在本例中,我们已经有一个lname开头的索引了,因此从这个角度看,city,lanme可能更合适。显然,我们需要在这两者之间进行权衡,对于这个案例而言。后者可能更重要,因为city并不是一个频繁更新的列

更改现有索引列的顺序和在现有索引列之间添加新列同样危险。在这两种情况下,现有的select的执行速度都可能会急剧下降,因为匹配列减少了,或者引入了排序(导致过早产生结果集)

实例2

范围事务的BQ即QUBE

假设有一张表,结构如下

create table `cust`(
`cno` varchar(8) primary key,
`pno` varchar(8) not null default '',
`fname` varchar(8) not null default '',
`lname` varchar(8) not null default '',
`city` varchar(8) not null default '',
key `idx_pno`(`pno`),
key `idx_city`(`city`),
key `idx_lname_fname`(`lname`, `fname`)
)

再假设下其他的条件

  1. 表中有100万记录
  2. idx_lname_fname是唯一合适的索引
  3. 谓词lname = :lname的最大过滤因子是10%
  4. 谓词city = :city的最大过滤因子是10%
  5. 结果集最多包含10000条记录(1000000 10% 10%)
  6. 表中的记录按照cno列的大小顺序存储,主键索引cno是聚簇索引,或者表经常按照cno进行排序重组。

sql语句如下

select cno,fname
from cust
where city = :city and lname between :lname1 and :lname2
order by fname

idx_city与idx_lname_fname都不是半宽索引,所以两者都不满足BQ。QUBE的结果显示出二者的性能都非常差,无论使用哪个索引其结果都是相同的 : 两个索引的匹配扫描过程都只能使用1个MC,都需要进行排序

索引      lname, fname或city            TR = 1      TS = 99999
表        CUST                         TR = 100000      TS = 0
提取      10000 * 0.1ms
LRT                                    TR = 100001      TS = 9999
                                       100001 * 10ms 99999 * 0.01ms
                                       1000s + 1s + 10000 * 0.1ms ≈ 1002s
该事务的最佳索引

最佳索引很容易得到,候选方案A是
(city, lname, fname, cno)
该索引只有两颗星,因为需要排序(order by跟在范围谓词列的后面)。因此,候选方案B一定是(city,fname,lname,cno),他也只有两颗星,不过他具有的是第二颗星而不是第一颗。通过QUBE很容易判断出哪一个才是最佳索引。

候选索引A
索引      city, lname, fname, cno            TR = 1      TS = 9999
表        CUST                               TR = 0      TS = 0
提取      10000 * 0.1ms
LRT                                          TR = 1      TS = 9999
                                             1 * 10ms 9999 * 0.01ms
                                             10ms + 100ms + 10000 * 0.1ms ≈ 1110ms
候选索引B
索引      city, fname, lname, cno            TR = 1      TS = 99999
表        CUST                               TR = 0      TS = 0
提取      10000 * 0.1ms
LRT                                          TR = 1      TS = 99999
                                             1 * 10ms    99999 * 0.01ms
                                             10ms + 1000ms + 10000 * 0.1ms ≈ 2010ms

最佳索引显然是A,(city, lname, fname, cno),LRT为1s,但即使是最佳索引,事务的性能也一般。

我们早先提出过,如果只是为了比较不同的访问路径,fetch的处理可以被忽略,因为它在所有的情况下均是相同的。但是这么做之后,在决定选取哪个索引时要小心一点。例如,在这个案例中,如果忽略fetch的处理,那么候选方案A的成本仅为候选方案B成本的1/10,而实际的优势比例要小得多。

在这个例子中,候选方案A所需要的排序成本与候选方案B所节省的1秒相比是微不足道的,如前所述,事实上排序成本已经被包含在fetch的开销中了。候选方案B的问题在于我们使用的索引片更厚(10%),从而产生了大量的TS。

半宽索引(最大化索引过滤)

在两个现有索引的末端添加缺少的谓词列可以消除大量的随机访问,因为这样能引入索引过滤过程。只有在确定索引行中同时包含所需city和lname值时,表中的行才会被访问。

在现有索引的基础上,我们可以使用两个半宽索引
(city, lname)或(lname, fname, city)
我们再一次使用QUBE来判断那个是最好的。

索引      city, lname            TR = 1      TS = 9999
表        CUST                   TR = 10000      TS = 0
提取      10000 * 0.1ms
LRT                              TR = 10001      TS = 999
                                 10001 * 10ms 9999 * 0.01ms
                                 100s + 100ms + 10000 * 0.1ms ≈ 101s
索引      lname, fname, city            TR = 1      TS = 99999
表        CUST                          TR = 10000  TS = 0
提取      10000 * 0.1ms
LRT                                    TR = 10001   TS = 99999
                                       10001 * 10ms 99999 * 0.01ms
                                       100s + 1s + 10000 * 0.1ms ≈ 1002s

虽然第二个索引会有一个更厚的索引片,但这个因素在很大程度上被大量的表访问掩盖了(尽管已经通过过滤的方式将表访问的次数大大减少了)。看来我们需要设计一个宽索引来消除对表的10000次TR了。

宽索引(只需访问索引)

我们在上面评估的第一个宽索引上再多加两个列,在第二个索引上再多加一个列,两者就都变成了宽索引 : (city, lname, fname, cno)或(lname, fname, city, cno)

索引      city, lname, fname, cno            TR = 1      TS = 9999
表        CUST                               TR = 0      TS = 0
提取      10000 * 0.1ms
LRT                                          TR = 10001  TS = 999
                                             1 * 10ms 9999 * 0.01ms
                                             10ms + 100ms + 10000 * 0.1ms ≈ 1.11s
索引      lname, fname, city, cno            TR = 1      TS = 99999
表        CUST                               TR = 0      TS = 0
提取      10000 * 0.1ms
LRT                                          TR = 11     TS = 99999
                                             10001 * 10ms 99999 * 0.01ms
                                             10ms + 1s + 10000 * 0.1ms ≈ 2.01s

现在表的访问已经消除了,两个索引的LRT之间的区别就变得很明显了。第一个索引满足了第一颗星,即提供了一个薄的索引片,因为它所基于的原始索引只包含city列,这是在select中唯一的等值条件。正因为如此,我们才能将它升级为最佳索引,首先添加between谓词列(使其变为半宽索引,但仍不够),然后再添加select中引用的其他列(使其变为宽索引)。

类型索引LRT维护成本
现有索引lname或city17min-
半宽索引city, lname101sU lname + 10 - 20ms
半宽索引lname, fname, city102sU city + 10 - 20ms
宽索引city, lname, fname, cno1sU L & FNAME + 10 - 20ms
宽索引lname, fname, city, cno2sU city + 10 - 20ms
三星索引city, lname, fname, cno17minU L & FNAME + 10 - 20ms
三星索引city, fname, lname, cno17minU L & FNAME + 10 - 20ms

LRT是最差输入下的QUBE值,I = 插入,D = 删除,U = 更新

何时使用QUBE

理想情况下,QUBE应当在新方案的设计过程中使用。如果在当前或者计划设计的索引条件下最差输入的本地响应时间过长,比如大于2s,那么就应当考虑进行索引优化了。

尽管批处理任务的告警阈值视具体的任务而定,但检查一下所有批处理程序相邻提交点之间的最大时间间隔是很重要的,这个值也许应当小于1s或2s,否则该批处理任务有可能会导致事务池和其他批处理任务中的大量锁等待。

QUBE甚至可以在设计程序之前就开始使用,只需要简单地知道数据库一定会处理的最差输入场景即可。例如,读取表中10行不相邻的记录,像B表重增加2行相邻的记录,等等。事实上,直到估算结果令人满意时再开始设计程序是非常明智的做法。有时,我们可能还必须设计较为复杂的程序结构以满足性能的要求。

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的特性,这个操作被封装在一个事务中完成。这就产生了一个问题,在执行过程中,当数据库或操作系统,硬件发生问题时,重新开始事务变得不可接受。数据库需要回滚所有已经发生的变化,而这个过程可能比产生这些变化的时间还长。因此对于长事务的问题,有时可以转化为小批量的事务进行处理。当发生错误时,只需要从发生错误的位置继续执行就行。