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来强制使用某个索引

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

B+树研究1 - 2-3树实现

最近在看MySQL的书,经常提到B+树,总是感觉有一层面纱蒙在上面,好神秘。。。
于是决定研究下B+树,在B站上面搜了下B+树的视频,只找到了将B树的视频,配合在网上找的一些资料,得到了一个结论
2-3树和2-3-4树是一种特殊的B树,而B+树是B树的优化版,有点类似于亚古兽-》暴龙兽-》机械暴龙兽-》战斗暴龙兽的意思。

于是决定先实现下简单点的2-3树
2-3树定义
2-3 树是一种多路查找树 :2和3的意思就是2-3树包含两种节点
1). 2节点

  1. 节点包含一个元素和两个孩子(或者没有孩子)
  2. 左子树包含的元素值小于该节点的元素值,右子树包含的节点的元素值大于该节点的元素值
  3. 2节点要不有两个孩子,要不就没有孩子,不允许有一个孩子
    2). 3节点
  4. 包含一大一小两个元素(两个元素按大小顺序排列好)和三个孩子(或者没有孩子)
  5. 左子树包含的节点的元素值小于该节点较小的元素值,右子树包含的节点的元素值大于该节点较大的元素值,中间子树包含的节点的元素值介于这两个元素之间
  6. 3节点要么有三个孩子,要么就没有孩子,不允许有一个或两个孩子
    3). 所有叶子节点都在同一个层次

要点

  1. 每次新增或删除节点时可能会触发节点的分裂与合并
  2. 在分裂与合并发生时,有可能会发生连锁的分裂与合并
  3. 2节点有可能会转换为3节点

实现思路

每次在新增或者删除节点时,判断一次当前节点的元素节点有没有超过2(3节点的元素最多为2),有则触发分裂,把当前节点3个值中中间的值向父节点传递,这个动作可能会触发父节点从2节点变为3节点,或者触发从3节点转换为2节点(分裂新节点)

MySQL技术内幕(InnoDB存储引擎概述)学习笔记01-MySQL基本概念

MySQL组成部分

  • 连接池组件
  • 管理服务和工具组件
  • SQL接口组件
  • 查询分析器组件
  • 优化器组件
  • 缓冲(Cache)组件
  • 插件式存储引擎
  • 物理文件

MySQL是单进程多线程

插件式存储引擎是MySQL的一大特点

InnoDB体系架构

  • 内存块
  • 后台线程

内存块

  • 维护所有进程/线程需要访问的多个内部数据结果
  • 缓存磁盘上的数据,方便快速的读取,同时对磁盘文件的数据修改之前在这里花村
  • 重做日志(redo log)缓冲

后台线程

作用

  • 刷新内存池中的数据,保证缓冲池中的内存缓存的是最近的数据。
  • 将已修改的数据文件刷新到磁盘文件
  • 保证在数据库发生异常的情况下InnoDB能恢复到正常运行状态

不同的线程

InnoDB是多线程的模型,其后台有多个不同的线程,负责处理不同的任务

  • Master Thread
    是一个核心线程,主要将缓冲池中的数据异步刷新到磁盘,保证数据的一致性,包括脏页的刷新,合并插入缓冲(INSERT BUFFER),UNDO页的回收
  • IO Thread
    在InnoDB中大量使用了AIO(Async IO)来处理写请求,这样可以极大的提高数据库的性能。而IO Thread的工作主要是负责这些IO请求的回调(call back)处理。InnoDB 1.0版本之前共有4个IO Thread,分别是write,read,insert buffer和log IO Thread。在Linux平台下,IO Thread的数量不能进行调整,但是在Windows下可以通过innodb_file_io_threads来增大IO Thread。从InnoDB 1.0.x版本开始,read thread和write thread分别增大到4个,并且不再使用innodb_file_io_threads参数,而是分别使用innodb_read_io_threads和innodb_write_io_threads参数进行设置。
  • Purge Thread
    事务被提交后,其所使用的undolog可能不再需要,因此需要Purge Thread来回收已经使用并分配的undo页。在InnoDB 1.1版本之前,purge操作仅在InnoDB存储引擎的Master Thread中完成。而从1.1版本开始,purge操作独立到单独的线程中进行,以此来减轻Master Thread的工作,从而提高CPU的使用率以及提升存储引擎的性能
  • Page Cleaner Thread
    在1.2.x版本引入。作用是将之前版本中脏页的刷新操作都放到单独的线程来完成。其目的是为了减轻原Master Thread的工作及对于用户查询线程的阻塞,进一步提高InnoDB存储引擎的性能。

内存

  • 缓冲池

InnoDB存储引擎是基于磁盘存储的,并将其中的记录按照页的方式进行管理。因此可将其视为基于磁盘的数据库系统。在数据库系统中,由于CPU速度与磁盘速度之间的鸿沟,基于磁盘的数据库系统通常使用缓冲池极速来提高数据库的整体性能。
缓冲池简单说就是一块内存区域,通过内存的速度来弥补磁盘速度相对较慢的速度。在数据库中进行读取页的操作,首先将从磁盘读取到的页放在缓冲池中,这个过程称为将页FIX在缓冲池中。下一次再读相同的页时,首先判断该页是否在缓冲池中。如在缓冲池中,称该页在缓冲池中被命中,直接读取该页。否则,读取磁盘上的页。
对于数据库中页的修改操作,则首先修改在缓冲池中的页,然后再以移动的频率刷新到磁盘上。这里需要注意的是,页从缓冲池刷新回磁盘的操作并不是在每次更新时触发,而是通过一种称为Checkpoint的机制刷新回磁盘。同样,这也是为了提高数据库的整体性能。
综上所述,缓冲池的大小直接影响着数据库的整体性能。由于32位操作系统的限制(最多将该值设置成3G,也可以通过操作系统的PAE选项来获得最大64GB的支持),建议数据库服务器采用64位的系统。

对于InnoDB而言,其缓冲池的配置通过参数innodb_buffer_pool_size来设置。

具体来看,缓冲池重的数据页类型有 : 索引页,数据页,undo页,插入缓冲(insert buffer),自适应哈希索引(adaptive hash index),InnoDB存储的锁信息(lock info),数据字典信息(data dictionary)等。不能简单的认为缓冲池只是缓存索引页和数据页,他们只是占缓冲池的很大一部分而已。

从InnoDB 1.0.x开始,允许有多个缓冲池实例。每个页根据hash值平均分配带不同缓冲池实例中,这样做的好处是减少数据库内存的资源竞争,增加数据库的并发处理能力。可以通过innodb_buffer_pool_instances来进行设置,该值默认为1。

在配置文件中将innodb_buffer_pool_instances来进行配置,该值默认为1。将该值设置为大于1就能得到多个缓冲池实例。再通过SHOW ENGINE INNODB STATUS可以查看

  • LRU List,Free List和Flush List

通常来说,数据库中的缓冲池是通过LRU(Last Recent Used,最近最少使用)算法来进行管理的。即最频繁使用的在LRU列表前面,最少使用的在LRU列表的尾端。当缓冲池不能存放新读取到的页的时候,将首先释放LRU列表尾端的页。
InnoDB中对传统的LRU算法做了优化。在InnoDB中加入了midpoint位置。新读取到的页,虽然是最新访问的页,但是不是直接放到LRU列表的首部,而是加入到LRU列表的midpoint位置。这个算法在InnoDB存储引擎下成为midpoint insertion strategy。默认配置下,该位置在列表长度的5/8处。midpoint可由参数innodb_old_blocks_pct控制。

那为什么不采用朴素的LRU算法,直接将读取的页放入到LRU列表的首部呢?这是因为若直接将读取到的页放入到LRU的首部,那么某些SQL操作可能会使3缓冲池中的页被刷新出,从而影响缓冲池的效率。常见的这类操作为索引或者数据的扫描操作。这类操作需要访问表中的很多页,甚至是全部的页,而这些页通常来说又仅在这次查询操作中需要,并不是活跃的热点数据。如果页被放入LRU列表的首部,那么非常可能将所需要的热点数据页从LRU列表中移除,在下一次需要该页时,InnoDB存储引擎需要再次访问磁盘。

为了解决这个问题,InnoDB存储引擎引入了另一个参数来进一步管理LRU列表,这个参数是innodb_old_block_time,用于表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。因此当需要执行上说所说的SQL操作时,可以通过下面的方法尽可能使LRU列表中的热点数据不被刷出。

set global innodb_old_blocks_time = 1000;
# data or index scan operation
set global innodb_old_blocks_time = 0;

如果预估到活跃的热点数据不止63%,那么在执行SQL语句之前,还可以通过下面的语句来减少热点页可能被刷出的概率。

set global innodb_old_blocks_pct = 20;

LRU列表用来管理以及读取的页,但当数据库启动时,LRU列表是空的,即没有任何页,这时页都存放在Free列表中。当需要从缓存池中分页时,首先从Free列中查找是否有可用的空闲页,若有则将该页从Free列表中删除,放入到LRU列表中。否则,根据LRU算法,淘汰LRU列表末尾的页,将该内存空间分配给新的页。当页从LRU列表的old部分加入到new部分时,称此时发生的操作为page made young,而因为innodb_old_blocks_time的设置导致页没有从old部分移动到new部分的操作称为page not made young。可以通过命令show engine innodb status来观察LRU列表及Free列表的使用情况及运行状态。注意这个命令显示的不是当前的状态,而是过去的某个时间范围内的InnoDB存储引擎的状态,具体的时间范围可以在命令执行后的结果中看到。

InnoDB从1.0版本开始支持压缩叶的功能,即将原本16KB的页压缩为1KB,2KB,4KB和8KB。而由于页的大小发生了变化,LRU列表也有了些许改变。对于非16KB的页,是通过unzip_LRU列表进行管理的。通过命令SHOW ENGINE INNODB STATUS可以观察到如下内容

...
LRU len: 266, unzip_LRU len: 0
...

可以看到LRU列表一共有266个页,而unzip_LRU列表中有0个页。这里需要注意的是,LRU中包含了unzip_LRU列表中的页。

对于压缩页的表,每个表的压缩比率可能各有不同。可能存在有的表页大小为8KB,有表页大小为2KB的情况。unzip_LRU是怎么样从缓冲池中分配内存的呢?

首先,在unzip_LRU列表中对不同压缩页大小的页进行分别管理。其次,通过伙伴算法进行内存分配。例如需要从缓冲池中申请页为4KB的大小,其过程为

  1. 检查4KB的unzip_LRU列表,检查是否有可用的空闲页
  2. 若有则直接使用
  3. 否则,检查8KB的unzip_LRU列表
  4. 如果能得到空闲页,将页分为2个4KB页,存放到4KB的unzip_LRU列表
  5. 若不能得到空闲页,从LRU列表中申请一个16KB的页,将页分为1个8KB的页,2个4KB的页,分别存放到对应的unzip_LRU列表中

同样可以使用infomation_schema架构下的表 INNODB_BUFFER_PAGE_LRU 来观察unzip_LRU列表中的页

SELECT TABLE_NAME,SPACE,PAGE_NUMBER,COMPRESSED_SIZE FROM INNODB_BUFFER_PAGE_LRU WHERE COMPRESSED_SIZE <> 0;

LRU列表中的页被修改后,该页称为脏页(dirty page),即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过CHECKPOINT机制将脏页刷新回磁盘,而Flush列表中的页即为脏页。需要注意的是,脏页既存在于LRU列表中,也存在于Flush列表中国。LRU列表用来管理缓冲池中页的可用性,Flush列表用来管理将页刷新会磁盘,二者互不影响。

同LRU列表一样,Flush列表也可以通过命令SHOW ENGINE INNODB STATUS来查看,Modify db pages显示的就是脏页的数量。information_schema中可以用下面的语句查看

SELECT TABLE_NAME,SPACE,PAGE_NUMBER,PAGE_TYPE FROM INNODB_BUFFER_PAGE_LRU WHERE OLDEST_MODIFICATION > 0;
  • 重做日志缓冲

InnoDB存储引擎会首先将重做日志信息放入到这个缓冲区,然后按一定频率将其刷新到重做日志文件。重做日志缓冲一版不需要设置的很大,因为一般情况下每一秒钟会将重做日志刷新到日志文件,因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。该值可由配置参数innodb_log_buffer_size控制,默认为8MB

在通常情况下,8MB的重做日志缓冲池足以满足绝大部分的应用,因为重做日志在下列三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中。

  1. Master Thread每一秒将重做日志刷新到重做日志文件
  2. 每个事务提交时会将重做日志刷新到重做日志文件
  3. 当重做日志缓冲池空间小于1/2时,重做日志缓冲刷新到重做日志文件
  • 额外的内存池

在InnoDB存储引擎中,对内存的管理是通过一种称为内存堆(heap)的方式进行的。在对一些数据结构本身的内存进行分配时,需要从额外的内存池中进行申请,当该区域的内存不够时,会从缓冲池中进行申请。例如分配了缓冲池(innodb_buffer_pool),但是每个缓冲池中的帧缓冲(frame buffer)还有对应的缓冲控制对象(buffer control block),这些对象记录了一些诸如LRU,锁,等待等信息,而这个对象的内存则需要从额外内存池中申请。因此在申请了很大的InnoDB缓冲池时,也应该考虑相应的增加这个值。

Checkpoint

Checkpoint(检查点)技术的目的主要是解决以下几个问题

  • 缩短数据库的回复时间
  • 缓冲池不够用时,将脏页刷新到磁盘
  • 重做日志不可用时,刷新脏页

在InnoDB存储引擎中,Checkpoint发生的时间,条件及脏页的选择等都非常复杂。而Checkpoint所做的事情无外乎是将缓冲池中的脏页刷新到磁盘。不同之处在于每次刷新多少页到磁盘,每次从哪里取脏页,以及什么时间出发Checkpoint。在InnoDB存储引擎内部,有两种Checkpoint,分别为

  • Sharp Checkpoint
  • Fuzzy Checkpoint

Sharp Checkpoint发生在数据库关闭时将所有的脏页都刷新回磁盘,这是默认的工作方式,即参数innodb_fast_shutdown=1

但是数据库如果再运行时也使用Sharp Checkpoint,那么数据库的可用性就会收到很大的影响。故在InnoDB引擎内部视同Fuzzy Checkpoint进行页的刷新,即只刷新一部分脏页,而不是刷新所有的脏页回磁盘。

Innodb存储引擎可能发生如下几种情况的Fuzzy Checkpoint

  • Master Thread Checkpoint
  • FLUSH_LRU_LIST Checkpoint
  • Async/Sync Flush Checkpoint
  • Dirty Page too much Checkpoint

对于Master Thread中发生的Checkpoint,差不多以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的,即此时InnoDB存储引擎可以进行其他操作,用户查询线程不会阻塞。

FLUSH_LRU_LIST Checkpoint是因为InnoDB存储引擎需要保证LRU列表中需要有差不多100个空闲页可供使用。在InnoDB 1.0.x版本之前,需要检查LRU列表中是否有足够的可用空间操作发生在用户查询线程中,显然这会阻塞用户的查询操作。倘若没有100个可用空闲页,那么InnoDB存储引擎会将LRU列表尾端的页移除。如果这些页中有脏页,那么需要进行Checkpoint,而这些页是来自LRU列表的,因此成为FLUSH_LRU_LIST Checkpoint。

而从MySQL 5.6版本,也就是InnoDB1.2.x版本开始,这个检查被放在了一个单独的Page Cleaner线程中进行,并且用户可以通过参数innodb_lru_scan_depth控制LRU列中可用页的数量,该值默认为1024。

Async/Sync Flush Checkpoint指的是重做日志不可用的情况,这时需要强制将一些页刷新回磁盘,而此时脏页是从脏页列表选取的。

Dirty page too much即脏页数量太多。导致InnoDB存储引擎强制进行Checkpoint。其目的总得来说还是为了保证缓冲池中有足够可用的页。其可由参数innodb_max_dirty_pages_pct控制。
innodb_max_dirty_pages_pct的值为百分比,如果是90则代表当缓冲池脏页数量超过90%时,强制进行Checkpoint,刷新一部分脏页到磁盘。

InnoDB关键特性

  • 插入缓冲(Insert Buffer)
  • 两次写(Double Write)
  • 自适应哈希索引(Adaptive Hash Index)
  • 异步IO(Async IO)
  • 刷新邻接页(Flush Neighbor Page)

插入缓冲

Insert Buffer可能是InnoDB存储引擎关键特性中最令人激动与兴奋的一个功能。不过这个名字可能会让人认为插入缓冲是缓冲池的一个组成部分。其实不然,InnoDB缓冲池中有Insert Buffer信息固然不错,但是Insert Buffer和数据页一样,也是物理页的一个组成部分。

在InnoDB存储引擎中,主键是行的唯一标识符。通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此,插入聚集索引(Primary Key)一般是顺序的,不需要磁盘的随机读取。比如按照下列SQL定义表:

CREATE TABLE T(
a INT AUTO_INCREMENT,
b VARCHAR(30),
PRIMARY KEY(a)
)

其中a列是自增长的,若对a列插入NULL值,则由于其具有AUTO_INCREMENT属性,其值会自动增长。同时页中的行记录按照a的值进行顺序存放。在一般情况下,不需要随机读取另一个页中的记录。因此对于这类插入操作,速度是非常快的。

注意并不是所有的主键插入都是顺序的。若主键是类似UUID这样的,那么插入和辅助索引一样,同样是随机的。即使主键是自增类型,但是插入的是指定的值,而不是NULL值,那么同样可能导致插入并非连续的情况。

但是不可能每张表上只有一个聚集索引,更多的情况下,一张表上有多个非聚集的辅助索引(secondary index)。比如,用户需要按照b这个字段进行查找,并且b这个字段不是唯一的,即表是按如下的SQL语句定义的:

CREATE TABLE T(
a INT AUTO_INCREMENT,
b VARCHAR(30),
PRIMARY KEY(a),
KEY(b)
)

在这样的情况下产生了一个非聚集的且不是唯一的索引。在进行插入操作时,数据页的存放还是按主键a进行顺序存放的,但是对于非聚集索引叶子节点的插入不再是顺序的了,这时就需要离散的访问非聚集索引页,由于随机读取的存在而导致了插入操作性能下降。当然这并不是这个b字段上索引的错误,而是因为B+树的特性决定了非聚集索引插入的离散型。

需要注意的是,在某些情况下,辅助索引的插入依然是顺序的,或者说是比较顺序的,比如用户购买表中的时间字段。在通常情况下,用户购买时间是一个辅助索引,用来根据时间进行查询。但是在插入时却是根据时间的递增而插入的,因此插入也是较为顺序的。

InnoDB存储引擎开创性的设计了Insert Buffer,对于非聚集索引的插入或更新操作,不是每一次直接插入到索引页中,而是先判断插入的非聚集索引页是否在缓冲池中,若在则直接插入;若不在则先放入到一个Insert Buffer对象中,好似欺骗。数据库这个非聚集的索引已经插到叶子节点,而实际没有,只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge(合并)操作,这时通常能将多个插入合并到一个操作中(因为在一个索引页),这就大大提高了对于非聚集索引插入的性能。

然后Insert Buffer的使用需要同时满足两个条件

  • 索引是辅助索引(secondary index)
  • 索引不是唯一的(unique)

当满足以上两个条件时,InnoDB会使用Insert Buffer,这样就能提高插入操作的性能了,不过考虑到这样一种情况:应用程序进行大量的插入操作,这些都涉及了不唯一的非聚集索引,也就是使用了Insert Buffer。若此时MySQL数据库发生了宕机,这时势必有大量的Insert Buffer并没有合并到实际的非聚集索引中去。因此这时恢复可能需要花很长的时间,在极端条件下甚至需要几个小时。

辅助索引不能是唯一的,因为在插入缓冲时,数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生,从而导致Insert Buffer失去了意义。

目前Insert Buffer存在一个问题 :在写密集的情况下,插入缓冲会占用过多的缓冲池内存(innodb_buffer_pool),默认最大可以占用到1/2的缓冲池内存。

这对于其他的操作可能会带来一定的影响。

Change Buffer

InnoDB从1.0.x版本开始引入了Change Buffer,可将其视为Insert Buffer的升级。从这个版本开始,InnoDB存储引擎可以对DML操作--INSERT,DELETE,UPDATE都进行缓冲,他们分别是:Insert Buffer,Delete Buffer,Purge Buffer。

当然和之前Insert Buffer一样,Change Buffer使用的对象依然是非唯一的辅助索引。
对一条记录进行UPDATE操作可能分为两个过程

  • 将记录标记为已删除。
  • 真正将记录删除。

因此Delete Buffer对应UPDATE操作的第一个过程,即将记录标记为已删除。Purge Buffer对应UPDATE操作的第二个过程,即将记录真正的删除。同时,InnoDB存储引擎提供了参数innodb_change_buffering,用来开启各种Buffer的选项。该参数可选的值为 :inserts,deletes,purges,changes,all,none。inserts,deletes,purges就是前面讨论过的三种情况。changes表示启用inserts和deletes,all表示启用所有,none表示都不启用。该参数默认值为all。

从InnoDB 1.2.x版本开始,可以通过参数innodb_change_buffer_max_size来控制Change Buffer最大内存使用量

mysql> show variables like "%innodb_change_buffer_max_size%";
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
+-------------------------------+-------+
1 row in set, 1 warning (0.00 sec)

该值默认为25,表示最多使用1/4的缓冲池内存空间。需要注意的是改值的最大有效值为50.

...
Ibuf: size 1, free list len 0, seg size 2, 470 merges
merged operations:
 insert 585, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
...

seg size :显示了当前Change Buffer的大小为1 * 16KB = 16KB
free list len :代表了空闲页的数量
size : 代表已经合并记录页的数量。seg size - (1 + free list len)。原文如下 :The number of pages used within the change buffer. Change buffer size is equal to seg size - (1 + free list len). The 1 + value represents the change buffer header page.
merges :代表合并的次数,也就是实际读取页的次数
merged operations - insert: 表示操作Insert Buffer的次数(插入的次数)
merged operations - delete mark: 表示操作Delete Buffer的次数
merged operations - delete: 表示操作Purge Buffer的次数;
discarded operations :表示当Change Buffer发生merge时,表已经被删除,此时就无需再将记录合并到辅助索引中了。

merged operations - insert/merges = 585/470 与等于 1.3 / 1
从这个值就可以看出性能的提升

Insert Buffer的内部实现http://jinblog.com/admin/write-post.php?cid=905#wmd-preview

Insert Buffer的使用场景,即非唯一辅助索引的插入操作。但是对于Insert Buffer具体是什么,以及内部是怎么实现可能依然模糊。

Insert Buffer的数据结构是一颗B+树。在MySQL 4.1之前的版本中每张表都有一颗Insert Buffer B+树。而在现今的版本中,全局只有一颗Insert Buffer B+树,负责对所有表的辅助索引进行Insert Buffer。而这棵B+树存放在共享表空间中,默认也就是ibdata1中。因此,试图通过独立表空间ibd文件恢复表中数据时,往往会导致CHECK TABLE失败。这是因为ibd文件进行恢复后,还需要进行REPAIR TABLE操作来重建表上所有的辅助索引。

Insert Buffer是一颗B+树,因此其也由叶节点和非叶节点组成。非叶节点存放的是查询的search key(键值)

当一个辅助索引要插入到页时,如果这个页不在缓冲池中,那么InnoDB引起会构造一个search key,接下来查询Insert Buffer这棵B+树,然后将这条记录插入到Insert Buffer B+树的页子节点中。

Merge Insert Buffer

Merge Insert Buffer的操作可能发生在以下几种情况

  • 辅助索引页被读取到缓冲池时
  • Insert Buffer Bitmap页追踪到改辅助索引已无可用空间时
  • Master Thread

两次写

如果写失效,可以通过重做日志进行恢复。但是需要认识到,重做日志中记录的是对页的物理操作,如偏移量800,写“aaa”记录。如果这个页本身就已经损坏了,再对其进行重做是没有意义的。这就是说,在应用重做日志前,需要一个页的副本,放写入失效时,先通过页的副本还原该页,再进行重做,这就是doublewrite。

doublewrite由两部分组成,一部分是内存中的doublewrite buffer,大小为2MB,另一部分是物理磁盘上共享表空间中连续的128个页,即2个区(extent),大小同样为2MB。在对缓冲池的脏页进行刷新时,并不直接写磁盘,而是通过memcpy函数将脏页先复制到内存中的doublewrite buffer,之后通过doublewrite buffer再分两次,每次1MB顺序的写入共享表空间的物理磁盘上,然后马上调用fsync函数同步磁盘,避免缓冲写带来的问题。在这个过程中,因为doublewrite页是连续的,因此这个过程是顺序写的,开启并不是很大。在完成doublewrite页的写入后,再将doublewrite buffer中的页写入各个表空间文件中,此时的写入是离散的。

可以用show global status like "innodb_dblwr%";观察到doublewrite的运行情况,如果Innodb_dblwr_pages_written(doublewrite一共写的次数):Innodb_dblwr_writes(实际写入的次数)远小于64:1,那么可以说明系统写入压力并不是很高。

如果操作系统在将页写入到磁盘的过程中发生了崩溃,在恢复过程中,InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本,将其复制到表空间文件,再应用重做日志。

有些文件系统本身就提供了部分写失效的防范机制,在这种情况下,可以使用skip_innodb_doublewrite禁用doublewrite功能以提高性能。

自适应哈希索引

InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升,则建立哈希索引,称之为自适应哈希索引(Adaptive Hash Index, AHI)。AHI是通过缓冲池的B+树页构造而来,因此构建速度很快,而且不需要对整张表建立索引。

AHI有一个要求,即对这个页的连续访问模式必须是一样的。例如对于(a,b)这样的联合索引页,其访问模式可以是以下情况

  • where a=xxx
  • where a=xxx and b=xxx

访问模式一样是指的查询的条件是一样的,若交替进行上述两种查询,那么InnoDB存储引擎不会对该页构造AHI,此外还有如下要求

  • 以该模式访问了100次
  • 页通过该模式访问了N次,其中N=页中记录*1/16

根据InnoDB存储引擎官方的文档显示,启用AHI后,读取和写入速度可以提高2倍,辅助索引的链接操作性能可以提升5倍。毫无疑问,AHI是非常好的优化模式,其设计思想是数据库自优化(self-runing),则无需DBA对数据库进行人为调整。

可以使用show engine innodb status;查看哈希索引的使用情况

show engine innodb status;

......

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 125156, seg size 125158, 122194 merges
merged operations:
 insert 367572, delete mark 25630980, delete 1001186
discarded operations:
 insert 9209, delete mark 0, delete 0
0.00 hash searches/s, 0.56 non-hash searches/s
---
LOG
---
...

可以使用innodb_adaptive_hash_index来禁用或启用此特性,默认为开启

异步IO

异步IO的优势

  1. 提高数据库的性能
  2. 可以进行IO Merge操作

异步IO需要系统支持才能开启,可以用innodb_use_native_aio开控制。

官方的测试,开启Native IO,恢复速度可以提升75%

在InnoDB存储引擎中,read ahead方式的读取都是通过AIO完成,脏页的刷新,即磁盘的写入操作则全部由AIO完成。

刷新邻接页

InnoDB提供了Flush Neigbor Page(刷新邻接页特性)。其工作的原理为:在刷新一个脏页时,InnoDB存储引擎会检测该页所在区(entent)的所有页,如果是脏页,那么一起刷新。这样做可以通过AIO将多个IO写入操作合并为一个IO操作。改工作机制在传统的机械磁盘下有显著优势。但是需要考虑两个问题

  • 是不是可能将不怎么脏的页进行了写入,则该页之后又会很快变成脏页?
  • 固态硬盘有较高的IOPS,是否还需要这个特性?

为此。InnoDB在1.2.x版本之后提供了innodb_flush_neighbors,用来控制是否启用该特性,对于固态硬盘有着超高IOPS的磁盘,可以将该参数设置为0,即关闭此特性。

启动,关闭与恢复

可以使用innodb_fast_shutdown控制MySQL数据库关闭时的行为(刷新脏页和merge数据到磁盘),innodb_force_recovery控制启动时的行为(启动时有数据需要恢复的情况下,恢复的行为)

数据库索引设计与优化-01

磁盘与CPU时间的基础假设

I/O时间
随机读 10ms (4KB或8KB的页)
顺序读 40MB/s

顺序扫描的CPU时间
检查一行记录 5µs
FETCH 100µs

不合适的索引

对于下面这个简单的SQL,仅有两个合理的访问路径

  1. 索引扫描(LNAME, FNAME)
  2. 全表扫描
select cno,fname from cust where lname = :lname and city = :city order by fname

假设表中总共有 1000000 条数据

对于第一种选择来说,数据库会根据谓词条件lname = :lname扫描索引片。对于索引片中的每一个索引行,数据库系统都必须回到表中校验city字段的值。由于表中的行是根据cno字段而不是lname字段来聚簇的,所以校验操作需要做一次磁盘的随机读。对于最普遍的姓氏来说,在不考虑city字段的过滤因子的情况下,获取完整的结果意味着,需比对10000个索引行和10000个表行。那么,这个过程会持续多久?

假设索引(lname,fname)的大小是1000000 100 byte ≈ 100MB,包括数据及分散的空闲空间。另外,再假设顺序读的速度是40MB/s。读取一个宽度为1%的索引片的,即1MB,需花费10ms + 1MB / 40MB/s,这显然是没有问题的,但是10000次随机读将花费10000 10ms,这使得这种方式太慢了。

对于第二种选择来说,只有第一个页需要随机读。如果表的大小为1000000 * 600 byte ≈ 600MB,包括分散的空闲空间,那么花费的I/O时间将会是10ms + 600MB / 40M/s ≈ 15s,仍旧很慢

第二种方案的CPU时间将会比第一种方案的CPU时间长的多,因为数据库管理系统必须对比1000000行而不是20000行,并且还需要对这些行进行排序。从另一个方面来说,由于是顺序读,CPU的时间可以与I/O时间交叠。在这个场景下,全表扫描比在不合适的索引上扫描要快,但是这还不够,需要一个更好的索引。

三星索引

前面讨论了一个非常不合适的索引,这里来讨论另一个极端,三星索引,即对于一个查询语句可能是最好的索引。如果使用了三星索引,一次查询通常只需要进行磁盘随机读及一次窄索引片的扫描。因此,其响应时间通常会比使用一个普通索引的响应时间少几个数量级。

  • 如果与一个查询相关的的索引行是相邻的,或者至少相距足够靠近的话,那么这个索引就可以被标记上第一颗星。这最小化了必须扫描的索引片的宽度
  • 如果索引行的顺序与查询语句的需求一致,则索引可以被标记上第二颗星。这排除了排序操作
  • 如果索引行包含查询语句的所有列,那么索引就可以被标记上第三颗星。这避免了访问表的操作:仅访问索引就可以了。

对于这三颗星,第三颗通常是最重要的。将一个列排除在索引之外可能会导致许多速度较慢的随机读。我们把包含第三颗星的索引名为对应查询语句的宽索引。

示例

DECLARE CURSOR41 CURSOR FOR
SELECT CNO,FNAME
FROM CUST
WHERE LNAME = :LNAME AND CITY = :CITY
ORDER BY FNAME

为了满足第一颗星(减少索引片的大小以减少需要扫描的数据行)

取出所有等值为此的列(WHERE COL = ...)。把这些列作为索引最开头的列,以任意顺序都可以。对于CURSOR41来说,三星索引可以以LNAME,CITY或者以CITY,LNAME开头。在这两种情况下,必须扫描的索引片宽度将被缩减至最窄。

为了满足第二颗星(避免排序,较少磁盘IO和内存使用)

将ORDER BY列加入到索引中。不要改变这些列的顺序,但是忽略那些在第一步中已经加入索引的列。例如如果CURSOR41在ORDER BY中有重复的列,如ORDER BY LNAME, FNAME或者是ORDER BY FNAME, CITY,只有FNAME需要在这步中被加入到索引中去。当FNAME时索引的第三列时,结果集中的记录无需排序就已经是以正确的顺序排列了。第一次读取操作将返回FNAME值最小的那一行。

为了满足第三颗星(避免没个索引对于的数据行都需要进行一次随机IO从聚集索引中读取剩余数据)

将查询语句中剩余的列加到索引中去,列在索引中添加的顺序对查询语句的性能没有影响,但是将易变的列放在最后能够降低更新的成本。现在索引中已包含了满足无需回表的访问路径所需的所有列。
最终三星索引将会是:
(LNAME,CITY,FNAME,CNO)或(CITY,LNAME,FNAME,CNO)
CURSOR41在以下三个方面是较为挑剔的:

  • WHERE条件不包含范围谓词(BETWEEN,>,>=等)
  • FROM语句只涉及单表
  • 所有谓词对于优化器来说都足够简单

范围谓词和三星索引

下面的SQL与之前的相同,只是显现顾客是在一个范围内

DECLARE CURSOR43 CURSOR FOR
SELECT CNO,FNAME
FROM CUST
WHERE LNAME BETWEEN :LNAME1 AND :LNAME2
AND
CITY = :CITY
ORDER BY FNAME

尝试为这个CURSOR设计一个三星索引。大部分的推论与CURSOR41相同,但是BETWEEN谓词=谓词替代后将会有很大的影响。我们将会以相反的顺序依次考虑三颗星。
首先最简单的行(虽然非常中烟),第三颗星。按照之前的描述,确保查询的所有列都在索引中就能满足第三颗星。这样就不需要访问表,那么同步读就不会造成问题。
添加ORDER BY列能使索引满足第二颗星,但是这个仅在将其放在BETWEEN谓词列LNAME之前才成立,如索引(CITY,FNAME,LNAME)。由于CITY的值只有1个(=谓词),所以使用这个索引可以使结果集以FNAME的顺序排序,而不需要额外的排序。但是如果ORDER BY字段加在BETWEEN谓词列LNAME后,如索引(CITY,LNAME,FNAME),那么索引行不是按FNAME顺序排列的,因而就需要进行排序操作。因此为了满足第二颗星,FNAME必须在BETWEEN谓词列LNAME的前面,若索引(FNAME)或索引(CITY,FNAME,...)。
再考虑第一颗星,如果CITY是索引的第一列,那么我们将会有一个相对窄的索引片需要扫描(MC=1),这取决于CITY的过滤因子。但是如果用索引(CITY,LNAME,...)的话,索引片会更窄,这样在有两个匹配列的情况下我们只需要访问真正需要的索引行。但是,为了做到这样,并从一个很窄的索引片中获益,其他列(如LNAME)就不能放在这两列之间。
所以我们的理想索引会有几颗星呢?首先他一定能有第三颗星,但是,正如我们刚才所说,我们只能有第一颗星或者第二颗星,而不能同时拥有两颗星。换句话说,我们只能二选一

  • 避免排序--拥有第二颗星
  • 拥有可能的最窄索引片,不仅将需要处理的索引行数降至最低,而且将后续处理量,特别是表中数据行的同步读较小到最少--拥有第一颗星

在这个例子中,BETWEEN谓词或者任何其他范围谓词的出现,意味着我们不能同时拥有第一颗星和第二颗星。也就是说我们不能拥有一个三星索引。
这就意味着需要在第一颗星和第二颗星中作出选择。通常这不是一个困难的选择,第一颗星一版比第二颗星重要,虽然并不总是这样。

让我们考虑一下索引(LNAME,CITY,...),LNAME是范围谓词,如前面看到的,这意味着LNAME是参与索引匹配过程的最后一个列。等值谓词CITY不会在匹配过程中被使用。这样做将会导致只有一个匹配列---索引片将会比使用索引(CITY,LNAME,...)更宽。

为查询语句设计最佳索引的算法

根据以上的讨论,理想的索引是一个三星索引。正如我们看到的,当存在范围谓词时,这是不可能实现的。我们不得不牺牲第二颗星来满足一个更窄的索引片(第一颗星),这样,最佳索引就只拥有两颗星。在这个例子中理想索引是不可实现的。将这层因素考虑在内,我们可以对所有情况创建最佳索引(也许不是理想索引)的过程公式化。创建出的索引将拥有三颗星或者两颗星。

首先设计一个索引片尽可能窄(第一颗星)的宽索引(第三颗星)。如果查询使用这个索引时不需要排序(第二颗星),那么这个索引就是三星索引。否则这个索引只能是二星索引,牺牲第二颗星。或者采用另一种选择,避免排序,牺牲第一颗星保留第二颗星。这两种二星索引中的一个将会是相应查询语句的最佳索引。

下面的内容阐述了为查询语句创建最佳索引的算法

候选A

  1. 取出对于优化器来说不过分复杂的等值谓词列。将这些列作为索引的前导列--以任意顺序指定皆可。
  2. 将选择性最好的范围谓词作为索引的下一个列,如果存在的话。最好的选择性是指对于最差的输入值有最低的过滤因子。只考虑对于优化器来说不过分复杂的范围谓词即可
  3. 以正确的顺序添加ORDER BY列(如果ORDER BY列有DESC的话,加上DESC)。忽略在第一步或第二步中已经添加的列。
  4. 以任意顺序将SELECT语句中其余的列添加至索引中(但是需要以不易变的列开始)

举例 : CURSOR43
候选A为(CITY,LNAME,FNAME,CNO)
由于FNAME在范围谓词列的后面,候选A引起了CURSOR43的一次排序操作。

候选B

如果候选A引起了所给查询语句的一次排序操作,那么还可以设计候选B。根据定义,对于候选B来说第二颗星比第一颗星更重要。

  1. 取出对于优化器来说不过分复杂的等值谓词列。将这些列作为索引的前导列--以任意顺序指定皆可。
  2. 以正确的顺序添加ORDER BY列(如果ORDER BY列有DESC的话,加上DESC)。忽略在第一步或第二步中已经添加的列。
  3. 以任意顺序将SELECT语句中其余的列添加至索引中(但是需要以不易变的列开始)

举例 : CURSOR43
候选B为(CITY,FNAME,LNAME,CNO)
现在我们有两个最佳索引的候选对象,一个有第一颗星,一个有第二颗星。为了判断哪一个是最佳索引

需要注意,到目前为止,我们所做的只是设计理想索引或是最佳索引。但是这是否是实际可行的,我们在这个阶段还不好说

SQL 4.4

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

OPEN CURSOR CURSOR4
FETCH CURSOR CURSOR4 --- 最多20次
CLOSE CURSOR CURSOR4

现今排序速度很快--我们为什么还需要候选B

近几年来,排序速度已经提升了很多。现在大多数的排序过程都在内存中进行,用当前最快的处理器每排序50000行记录所耗费的时间只有0.5s,这对于一次事务操作来说也许是可接受的,但这对于CPU时间来说已经是一个比较大的开销了。
由于现在硬件条件下排序速度很快,所以如果一个程序取出结果集的所有行,那么候选A可能和候选B一样快,甚至比候选B更快。
然而,如果一个程序只需获取能够填满一个屏幕的数据量,如CURSOR44,那么候选B可能会比候选A快很多。正如第三章中讨论的,如果访问路径中没有排序,数据库管理系统只要一次一次地读取数据行就能对结果集进行物化。这也是为什么有些时候避免排序非常重要(通过采用候选B)。如果结果集很大,为了产生第一屏数据,二星索引候选A(需要进行排序)可能会话费非常长的时间。我们需要时刻记着,终端用户的一次错误输入可能会使结果集变得非常大。
如果访问路径中没有排序,使用CURSOR44的程序将会非常快(假设LNAME和CITY两列是索引中的前两列---不管顺序如何),即使结果集包含数以百万级的数据行。每个事务永远都不会使数据库管理系统物化大于20行的数据。

需要为所有查询语句都设计理想索引吗?

为每一个查询设计最佳索引的过程是简单的。这个设计过程就是上文所说的两种候选方案算法,是机械式的,只要给出下面这些内容就可以自动完成整个过程

  1. 查询语句
  2. 数据库统计信息(行数,页数,列值分布等)
  3. 对于一个简单谓词或组合谓词最差情况下的过滤因子
  4. 已经存在的索引

在这种简单的过程中,当前存在的索引信息只是用来避免生成重复的索引。有时一个表的索引可以删除掉一些多余的索引以提高插入速度,前提是删除后不会使查询速度明显下降。

在为一个查询语句设计了一个最佳索引后,去看一下已存在的索引是很有必要的。有可能某一个已经存在的索引几乎和理想索引差不多好用,特别是打算在这个已有索引的最后添加一些列的情况下。

当分析一个已经存在的索引对一个新查询语句由多大用处时,需要记住,多余的索引分为三种:完全多余索引,近乎多余索引,以及可能多余的索引。

完全多余索引

如果一个查询包含WHERE A= :A AND B= :B而另一个查询包含WHERE B = :B AND A = :A,数据库管理系统就会创建两个索引:(A,B)和(B,A)。如果没有查询包含A列或者B列上的范围谓词的话,那么这两个索引中的一个就是完全多余的。我们不需要两本电话簿,一个根据LNAME,FNAME排序而另一本根据FNAME,LNAME排序(如果姓氏和名字都已知)。这个时候需要规范SQL语句。

近乎多余的索引

假设索引(LNAME,CITY,FNAME,CNO)已经存在。为一个新的查询语句设计的理想索引包含了以这个索引的4列开头的14个列。那么,在创建了新的索引之后,原来的索引是不是应该删除呢?一些DBA可能会犹豫要不要这样做,因为这个已经存在的索引是唯一索引。但是,这个索引并不是主键索引也不是候选索引,只是恰好这个索引包含了主键列CNO。把其他列加到这个索引上不会有完整性问题。如果数据库管理系统支持非键值索引列,或者有约束来保证唯一性,数据列甚至可以加到主键索引或者任何键值必须唯一的索引上。这样一来,问题就成了一个纯粹的性能问题:一个原本使用4列索引的查询现在使用新的14列索引,速度是否会明显变慢?

假设索引行的大小从原先的50字节增长为200字节,那么扫描10000行索引片并从中取出1000个索引项会花费多少时间?CPU时间增长不多,但是I/O时间是和需要访问的页数成比例的。
CPU时间 = 1000 0.1ms + 10000 0.005ms = 150ms (两种情况下都是1000次FETCH调用和10000个索引行)
4KB大小的叶子页的数量(4列) 1.5 10000 50 / 4000 约等于 200
4KB大小的叶子页的数量(14列) 1.5 10000 200 / 4000 约等于 800
1.5位空闲空间系数

顺序读时间(4列) = 200 * 0.1ms = 20ms
顺序读时间(14列) = 800 * 0.1ms = 80ms

由于顺序读的处理过程是的响应时间还是受CPU时间的限制,所以查询语句使用这两个索引的响应时间没有明显不同。在新的14列索引创建之后,现存的4列索引就变成多余的了

可能多余的索引

一个普遍的常客是这样的 : 一个新的查询语句的理想索引是(A,B,C,D,E,F),而表上已经存在的索引是(A,B,F,C)。那么如果把已经存在的索引替换成(A,B,F,C,D,E),新的索引是不是就多余了?换句话说如果把D和E两列加到现有的索引上是的访问路径仅限于索引,这样对于新的查询语句是否就已经足够了?
理想索引可能在两方面比索引(A,B,F,C,D,E)要好

  1. 可能使得查询有更多的匹配列
  2. 可能可以避免排序

这两个优势都受需要在索引片上扫描的行数的影响。两个索引的差异可以入本章所述转换成毫秒值进行比较,或者更简单一些,通过后面讨论的快速上线估算法(QUBE)进行估算。估算结果往往会显示,新的索引是不需要的,在现有的索引后面加上新的列对于新的SELECT语句就已经足够了

新增一个索引的代价

如果表上有100个不同的查询,且为每一个查询语句都设计了最佳索引的话,那么即使没有重复的索引,该表上最终也可能有非常多的索引,这样一来表的插入,更新和删除操作就会变得很慢。

响应时间

当数据库管理系统向表中添加一行时,他必须在每一个索引上都添加响应的行。在当前的硬件条件下,在一个索引上添加一行,插入操作所花费的时间就增加10ms,因为必须从磁盘上读取一个叶子页。当一个事务向一张有10个索引的表里插入1行数据时,索引的维护就会使响应时间增加10*10ms = 100ms,这可能是可以接受的。然而,如果一个事务向一张有10个索引的表里插入20行数据的话,索引的维护就会需要181次随机读,即耗费1.8s。这个估算基于的前提假设是,新的索引行会把表上其中一个索引(一直增大的键值上的索引)添加到同一个叶子页上,而会把其余9个索引添加到20个不同的叶子页上。从响应时间的角度来看,在一个有10个索引的大表上进行大的事务操作(每个事务中有许多插入或删除操作)可能是无法忍受的。另外,从磁盘负载的角度来看,要在一个大表上进行每秒多余于10行的插入操作可能不容许表上有10个索引。

磁盘负载

被修改过的叶子页是迟早会被写到磁盘上去的。由于数据库的写是异步的,所以这些写不会影响到事务的响应时间。但是,这些会在增加磁盘负载。RAID 5会放大这种影响,因为每一次页的随机更新都会引来两个磁盘的访问。每一次访问都会耗费12ms,因为整个Raid条带都需要被读取和写回:一次寻道(4ms)和两次旋转(24ms)。因此,向磁盘写一个被修改的也带来的整体磁盘繁忙度的增加为24ms。RAID 10(条带化和镜像)相应的增量为26ms = 12ms。

如果一张表的插入频率较高的话,磁盘负载可能会变成主要的问题,限制了表上索引的数量。由于删除操作和插入操作锁带来的磁盘负载是相同的,所以大量的删除任务是另外一个重要的考虑事项。更新操作只会影响到列值被修改了的索引。

假设一个RAID 5磁盘服务器有128块盘,16块空闲盘。数据库(表和索引或者他们的分区)被条带化到这些活动盘上。读缓存为64GB,写缓存为2GB。

在TRANS表中,新插入的行保存在表及其聚簇索引的末尾。在页被写到磁盘之前,许多行会被写到这个页上,所以这些操作不会造成大量的磁盘读和写。会带来问题的是4个索引上的随机插入操作,每一个新的索引行可能都会导致一次磁盘读和磁盘写,每秒插入20行,即每秒一共80次随机写(4*20)。

先忽略读缓存和写缓存。在最差的情况下,4个索引造成的磁盘繁忙度为80*(6ms + 24ms)=2400ms,相应的次磁盘负载为2400ms/s=2.4=240%,如果这4个索引是在112块磁盘上做条带,他们对于平均磁盘负载的贡献是240%/120≈2%。根据平均排队时间将会是3ms。由此,增加2%的磁盘复杂度是可以忍受且不明显的。

使用读缓存和写缓存能多大程度上减轻磁盘负载?
64GB的读缓存和4个索引的大小(1.2GB)相比较似乎很大,但是如果访问模式是完全随机的,当插入频率是20行每秒时,对于每一个索引上75000个叶子页中的任何一个叶子页,对同一个叶子页的相邻两次访问的时间间隔是基本平均的,间隔时间为75000*50ms=3750s≈1h,如果读缓存的的平均时间是30分钟,那么就不会有很多的读命中缓存。如果写缓存的保留时间比读缓存的保留时间短,比如10分钟,那读缓存的作用会更小:只有当一个叶子页在10分钟内被更新1次以上,才能通过写缓存节省一次磁盘写。因此这个4个索引导致的平均磁盘繁忙度几乎依然是2%。如果访问模式不是随机的,缓存将会节省更多磁盘负载上的开销。每小时更新次数多余10次的叶子页能在读缓存和写缓存中长时间保留。

RAID 10,镜像并条带但是没有校验位,能将每个被修改也锁带来的次破案繁忙度从24ms降至12ms,但是这样需要增加磁盘数量。有256块盘的RAID 5基本上也能带来相同的效果。

从这个例子中可以引申出一个经验法则,其中指示符L表示一个表上的索引对RAID 5下磁盘平均负载平均负载的贡献 : L = N * I / D
其中 N = 随机插入设计的索引数量
I = 插入频率(表每秒插入的行数)
D = 磁盘数量(空闲盘除外)

如果L < 1,那么磁盘负载的增加不构成问题;负载增量很可能低于2%
如果L再1和10之间,那么负载的增加可能会比较明显
如果L>10,那么磁盘负载很可能会构成问题,除非缓存命中率很高。

在上面例子中, L = 4 * 20 / 112 = 0.7

如果磁盘负载是一个问题,较明显的解决办法是尝试合并索引。一个有10个列的索引比两个各有6个列的索引锁引起的磁盘负载要小。

磁盘空间

如果表中有一千万行以上的数据,索引磁盘空间成本可能会成为一个问题。外购硬件的价格主要取决于两个因素 : 花费的CPU时间和分配的磁盘空间。

举个例子,有人建议表上创建一个每行包含400字节用户数据的索引,我们是否需要考虑磁盘空间?
这个索引(去除RAID带来的额外开销)需要1.5 10000000 400byte ≈ 6GB 左右的磁盘空间。不过如果将这些列添加到现有索引上并不能提供可接受的响应时间,磁盘空间可能不是问题的关键。

随着索引变大,缓冲池或磁盘缓存也应该随之增大,否则非子叶的I/O量会增加,这一点也会增加开销。

一些建议

即使在目前磁盘空间成本较低的情况下,机械式的为每一个查询设计最佳索引也是不明智的,因为索引的维护可能会使得一些程序太慢或者使磁盘负载超负荷(这会影响所有程序)。最佳索引(根据两个候选索引的方法设计或者使用索引工具设计)是一个好的开端,但是,在决定为一个新的查询创建理想索引前需要先考虑下三种多余索引。

即使可能为每一个查询设计最佳索引,但在实际中更常见的情况是,只对那些由于不合适的索引而导致速度太慢(通过估算或通过测量)的查询语句进行索引设计。

练习

为4.5中的查询语句设计候选索引A和候选索引B。

SELECT A,B,D,E
FROM ORDERITEM
WHERE B BETWEEN :B1 AND :B2 (FF = 1...10%)
AND
C = 1 (FF = 2%)
AND
E > 0 (FF = 50%)
AND 
F = :F (FF = 0.1...1%)
ORDER BY A,B,C,F
WE WANT 20 ROWS PLEASE

候选索引A

对每一个候选索引,计算在最差情况下一个事务必须访问的索引行数。ORDERITEM表有100000000行。
候选索引A

  1. 提取出简单谓词(C, F)
  2. 从范围谓词中提取出过滤因子最低的(C, F, B)
  3. 把select中的字段放到索引中(C, F, B, A, D, E)
    扫描的索引行 100000000 (2% 1% * 10) = 2000

候选索引B
没有想到...