参考文献

  • 高性能MySQL(第三版)

  • 极客时间–MySQL实战45讲

  • 数据库索引设计与优化

表和索引结构

索引页和表页

  • 表和索引行都被存储在页中.页的大小仅仅决定了一个页可以存储多少个索引行,表行,以及一共需要多少个页来存储表或者索引.
  • 当表和索引被加载或重组时,每个页都会预留出一定比例的空闲空间,以满足向其添加新的表行或索引行的需求.
  • 缓冲池和I/O活动都是基于页的.例如一次将一个完整的页读取到缓冲池.这意味着一次I/O会读入多条记录到缓冲池,而不仅仅是一条.

索引行

  • 索引行在评估访问路径的时候是一个非常拥有的概念.
  • 对于唯一索引,一个索引行等同于叶子页中的一个索引条目.字段的值从表中复制到索引上,并加上一个指向表中记录的指针.通常,表页的编号是这个指针的组成部分.
  • 对于非唯一索引,一个特定的索引值对应的索引行一个被想象成独立的索引条目,每一个都含有相同的值,但是却有不同的指针.大多数情况下,非唯一索引的实际存储方式是一个值后带着多个指针.

索引结构

  • 非叶子页通常包含着一个键值,以及一个指向下一层级的指针,该键值是下一层级页中的最大键值.

表行

  • 每一个索引行都指向表中相应的一行记录,指针通常标识了记录锁存放的页及它在页中的位置.
  • 当加载表或者向表中插入记录的时候,表中记录的顺序可以被定义成和它的某一个索引记录相同的顺序.在这种情况下,当索引行被按顺序处理时,对应的表行也将依照相同的顺序被逐个处理.索引和表都按相同的顺序被访问,这是一个效率很高的处理过程.
  • 表中记录的顺序只能按照表上某一个索引的顺序来组织.如果通过表上其他的索引来访问这张表,那么表中相应的记录将不会按照与索引条目相同的顺序存储.

缓冲池和磁盘I/O

从磁盘驱动器进行的随机I/O

  • 一个页包含多条记录,我们可能需要该页上的所有行或者其中一部分行,又或者只需要其中某一行,但是所花费的成本都是相同的,约为10ms

    行为 耗时
    排队(Q) 3ms
    寻道 4ms
    半圈旋转 2ms
    传输 1ms
    • Q=(u/(1−u))∗SQ=(u/(1-u))*S
      • Q: 平均排队时间
      • u: 平均驱动繁忙度
      • S: 平均服务时间
    • 每秒随机读取50次
      • u=50读取/s* 0.006s/读取=0.3
      • Q=(0.3/(1-0.3))*6ms=3ms

  • 假设在这10ms中磁盘的实际繁忙时间为6ms.1ms左右的传输时间是将页从磁盘服务器缓冲区移动至数据库缓冲池锁花费的.其余3ms是对于功能发生的排队时间的估计值,这是基于每秒50次读取的磁盘活动情况得出的.

从磁盘服务器缓存进行的读取

  • 若DBMS所需要的页不在数据库缓冲池中,继而会向磁盘服务器发起读请求,磁盘服务器会判断该页是否在服务器缓冲区中,只有当它发现页不在缓冲区中时才从磁盘驱动器上读取页.
  • 如果该页在磁盘服务器的读缓冲区中,那么花费的时间将从10ms大幅度降低至1ms.

从磁盘驱动器进行顺序读取

  • 全表扫描

  • 全索引扫描

  • 索引片扫描

  • 通过聚簇索引扫描表行

  • 顺序读取叶有两个非常重要的优势:

    • 同时读取多个页意味着平均读取每个页的时间将会减少.
    • 由于DBMS事先知道需要读取哪些页,所以可以在页被真正请求之前就提前把其读取进来,称这一过程为预读.

自动跳跃式顺序读

  • 从定义上看,如果一系列不连续的行按照同一个方向扫描,那么访问模式将会是跳跃式顺序的.每行的平均I/O时间自然就比随机访问时间短,跳跃的距离越短则节省的时间越多.
    • 比如,当表行是通过一个聚簇索引读取时,访问模式即为跳跃式顺序的.

优化器及访问路径

索引片以及匹配列

  • 索引的一个窄的片段会将会被顺序扫描,其上索引行的值在一定的区间内,相应的表行将通过同步读从表中读取,除非该页已在缓冲池中.所以访问路径的成本很大程度上取决于索引片的厚度,即谓词表达式确定的值域范围.
  • 索引片越厚,需要顺序扫描的索引页就越多,需要处理的索引记录也越多,而最大开销还是来自于增加的对表的同步操作,每次表页读取需要10ms.如果索引片比较窄,就会显著减少索引访问的那部分开销,但是主要从成本节省还是在更少的对表的同步读取上.
  • 索引片就是SQL查询语句在执行中需要扫描的一个索引片段,根据索引片中包含的匹配列的数量不同,将索引分成窄索引(比如包含索引列数为1或2)和宽索引(包含的索引列数大于2)
    • 若索引片越宽,则需要顺序扫描的索引页就越多;
    • 若索引片越窄,则会减少索引访问的开销;
    • 每个非聚簇索引保存的数据都会存储主键值,然后通过主键值,来回表查找对应的数据,因此每个说因都相当与包括了主键.
    • 宽索引能够避免二次的随机 IO,而窄索引就需要在对索引进行顺序读取之后再根据主键 id 从主键索引中查找对应的数据
    • 对于窄索引,每一个在索引中匹配到的记录行最终都需要执行另外的随机读取从聚集索引中获得剩余的数据,如果结果集非常大,那么就会导致随机读取的次数过多进而影响性能.

索引过滤及过滤列

  • 有时候,列可能既存在于WHERE子句中,也存在于索引中,但这个列却不能参与索引片的定义.不过这些列仍然能够减少回表进行同步读的次数,这些列被称为过滤列
  • 假设一张表中有索引(A,B,C,D),查询语句中的WHERE子句为三个谓词组成的,具体为WHERE A=:A AND B>:B AND C=:C
    • 在WHERE子句,该列是否至少拥有一个足够简单的谓词与之对应?
      • 如果有,那么这个列就是匹配列.
      • 如果没有,那么这个列及其后面的索引列都是非匹配列.
    • 如果该谓词是一个范围谓词,那么剩余的索引列都是非匹配.
    • 对于最后一个匹配列之后的索引列,如果拥有一个足够简单的谓词与其对应,那么该列为过滤列.
    • 具体说明: 在索引的顺序为(A,B,C,D)
      • 第一个索引列A,这个列出现在一个等值谓词中,这也是所有谓词中最简单的一个,即列A是匹配列,将被用于定义索引片;
      • 第二个索引列B,由于列B是一个范围谓词,所以剩下的列C将不能参与匹配过程,即它不能定义索引片.但列C能够避免不必要的表访问,因为它参与了索引片的过滤过程.从效果上而言,列C的作用于列A和列B几乎同等重要,列C无法参与匹配过程只是使得索引片更厚一点而已.
      • 即索引A,B为匹配列,C为过滤列.

访问路径术语

  • 匹配谓词有时也称作范围限定谓词.当有合适的索引存在时,如果优化器能够识别到某谓词为匹配谓词,那就称这个谓词是可索引的或者可搜索的.
  • 消除表访问的最显而易见的方式就是将缺失的列添加到索引上.
  • 这种能够避免某个SELECT调用的表访问的索引称为覆盖索引
  • 使用覆盖索引的SELECT语句有时会被称为覆盖SELECT

过滤因子

  • 过滤因子描述了谓词的选择性,即表中满足谓词条件的记录行数所占的比例,它主要依赖于列值的分布情况.

  • 过滤因子:它描述了谓词的选择性.在WHERE条件语句中,每个条件都称为一个谓词,谓词的选择性也等于满足这个条件列的记录数除以总记录数的比例.

    • 过滤因子决定了索引片的大小,过滤因子的条件过滤能力越强,满足条件的记录数就越少,SQL查询需要扫描的索引片也就越小.
    • MySQL使用SHOW INDEX FROM tab_name

    过滤因子(FF)=结果集的数量表行的数量过滤因子(FF)=\cfrac{结果集的数量}{表行的数量}

  • 过滤因子的计算算法,就是满足谓词条件的记录行数除以表总行数。

    • 简单谓词的过滤因子 = 谓词结果集的数量 / 表总行数
    • 组合谓词的过滤因子 = 谓词1的过滤因子 × 谓词2的过滤因子

过滤因子对索引设计的影响

  • 需要扫描的索引片的大小对访问路径的性能影响至关重要.在当前的硬性条件下,索引片大小的最重要的量度就是需要扫描的索引记录数,即匹配组合谓词的过滤因子与总行数的乘积.
  • 按照定义,匹配谓词能够参与定义索引片的大小,即从哪开始,到哪结束.通常索引行的记录数等同于表的记录数.

磁盘以及CPU时间的基础假设

I/O时间
随机读 10ms(4KB或8KB)
顺序读 40MB/s
顺序扫描的CPU时间
检查一行记录 5μs
FETCH 100μs

三星索引★★★★★

  • 三星索引,即对于一个查询语句可能的最好索引.如果使用三星索引的话,一次查询通常只需要进行一次磁盘随机读及一次窄索引片的扫描.因此用一个普通索引的响应时间少几个数量级.

星级的评定

  • 如果与一个查询相关的索引行是相邻的,或者至少相距足够靠近的话,那这个索引就可以被标记上第一个星,这最小化了必须扫描的索引片的宽度.
    • 在WHERE子句中,找到所有等值谓词中的条件列,将它们作为索引中的开始列;
    • 它最小化了必须扫描的索引片的宽度。
  • 如果索引行的顺序与查询语句的需求一致,则索引可以被标记上第二颗星,这排除了排序操作.
    • 将GROUP BY和ORDER BY中的列加入到索引中.
    • 避免排序
  • 如果索引行包含查询语句中的所有列,那么索引就可以被标记上第三颗星.这避免了访问表的操作:仅访问索引就可以了.
    • 将SELECT子句中剩余的列加入到索引片中.
    • 避免回表查询

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

  • 理想的索引是一个三星索引,但当存在范围谓词时,这是不可能实现的.也许不得不牺牲第二颗星来满足一个更窄的索引片(第一颗星),这样最佳索引就只拥有两颗星.
  • 首先设计一个索引片尽可能窄(第一颗星)的宽索引(第三颗星).如果查询使用这个索引是不需要排序(第二颗星),那么这个索引就是三星索引.否则这个索引只能是二星索引,牺牲第二颗星.或者采用另外一种选择,避免排序,牺牲第一颗星保留第二颗星.这两种二星索引中将会是相应查询语句的最佳索引.
候选A
  1. 取出对于优化器来说不过分复杂的等值谓词列.将这些列作为索引的前导列–以任意顺序皆可.
  2. 将选择性最好的范围谓词作为索引的下一个列,如果存在的话.最好的选择性是指对于最差的输入值有最低的过滤因子.只考虑对于优化器来说不过分复杂的范围谓词即可.
  3. 以正确的顺序添加ORDER BY列(如果ORDER BY列有DESC的话,加上DESC),忽略在第1步或者第2步中已经添加的列.
  4. 以任意顺序将SELECT语句中其余的列添加至索引中(但是需要以不易变的列开始)
候选B
  • 如果候选A引起了所给的查询语句的一次排序操作,那么还可以设计候选B.根据定义,对于B来说第二颗星比第一颗星更重要.
  1. 取出对于优化器来说不过分复杂的等值谓词列.将这些列作为索引的前导列–以任意顺序皆可.
  2. 以正确的顺序添加ORDER BY列(如果ORDER BY列有DESC的话,加上DESC),忽略在第1步中已经添加的列.
  3. 以任意顺序将SELECT语句中其余的列添加至索引中(但是需要以不易变的列开始)

处理已经存在的索引

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

完全多余的索引

  • 如果一个查询包含WHERE A=:A AND B=:B而另一个查询包含WHERE B=:B AND A=:A的话,数据库管理系统就会创建两个索引:(A,B)和(B,A).如果没有查询包含A列或者B列上的范围谓词的话,那么这两个索引中的一个就是完全多余的.

近乎多余的索引

  • 假设索引(A,B,C,D)已经存在,为了一个新的查询语句设计的理想索引包含了以这个索引的4列为开头的14列.就会出现一个问题:一个原来使用4列的索引的查询现在使用新的14列索引,速度是否明显变慢?
  • 假设索引行的大小从原先的50字节增长为200字节,那么扫描10000行索引片并从中取出1000个索引项会花费多长时间?CPU时间增长不多,但是I/O时间是和需要访问的页数成比例的.
    • CPU时间 = 1000 x 0.1ms + 10000 x 0.005ms = 150ms (两种情况下都是1000次FETCH调用和10000个索引行)
    • 4KB大小的叶子页的数量(4列) 1.5 x 10000 x 50 / 4000 ~=200 (1.5为空闲空间系数)
    • 4KB大小的叶子页的数量(14列) 1.5 x 10000 x 200 / 4000 ~=800
    • 顺序读时间(4列) = 200 x 0.1ms = 20ms
    • 顺序读时间(14列) = 800 x 0.1ms = 80ms
  • 由于顺序读的处理过程使得响应时间还受CPU时间的限制,所以查询语句使用这两个索引的响应时间没有明显的不同.因此在新的14列索引创建之后,现存的4列就变化层多余的了.

可能多余的索引

  • 可能出现的情况: 一个新的查询语句的理想索引是(A,B,C,D,E,F),而表上已经存在的索引(A,B,F,C).那么如果把已经存在索引替换成(A,C,F,C,D,E)的话,新的索引是否就多余了?即如果把D和E列加到现有的索引上使得访问路径仅限于索引,这样对于新的查询语句是否就已经足够了?
  • 理想索引可能在两个方面比索引(A,B,C,D,E)要好:
    • 可能是的查询有更好的匹配列
    • 可能可以避免排序

使用pt-duplicate-key-checker

1
pt-duplicate-key-checker -h <hostname> -P <port> -u <user> -p <pass> --database=<db_name>

新增一个索引的代价

  • 影响事项:

    • 响应时间

    • 磁盘负载

    • 磁盘空间

  • 插入和删除意味着修改一个叶子页.如果该叶子页不在缓冲池或者读缓存中,那么整个叶子页就必须从磁盘上读取.无论所添加或删除的索引行的长度是多少,这都将耗费10ms的时间.

  • 更新索引列会导致响应时间增加10ms或20ms,具体的值取决于是否需要将索引行迁移至另外一个叶子页.

索引评估方法

基本问题法(Basic Question,BQ)

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

  • 如果答案是否,那么我们应当首先考虑将缺少的谓词列加到一个现有的索引上去.这将产生一个半宽索引,尽管索引的等值匹配过程并不令人满意(一星),但是索引过滤可以确保回表访问只发生在所有查询条件都满足的时候.

    • 半宽索引(最大化索引过滤): 索引包含了WHERE子句所引用的所有列

  • 如果这还没有达到足够的性能,那么下一个选择就是将所有涉及的列都加到索引上,以使访问路径只需访问索引.这将产生一个避免所有表访问的宽索引.

    • 宽索引(只需访问索引): 索引包含了WHERE子句所引用的所有列+SELECT子句中所有列

  • 如果SELECT仍然很慢,就应当使用两个候选索引算法来设计新的索引.根据定义,这就是所能实现的最佳索引.

    • 三星索引/二星索引

快速上限估算发(Quick Upper-Bound Estimate,QUBE)

  • 快速估算法的输出结果是本地响应时间(LRT),即在数据库服务器中的耗时.
    • 在单层环境(指发出SQL调用的应用与数据库部署在同一台机器上)中,一个传统事务的LRT是指用户和数据库之间一次交互的响应时间,不包括所有网络延迟.
    • 在多层环境(客户端/服务器)中,各层之间的通信时间也被排除在外.
    • 批量任务的LRT是指执行任务的耗时,任何任务队列中排队时间也被排除在外.
  • LRT包含服务时间和排队时间.
    • 服务时间: 在简单的场景下(I/O时间和CPU时间不重叠),服务时间等于CPU时间加上排除了磁盘驱动排队时间的随机读时间.如果没有资源竞争,则本地响应等于服务时间.
    • 排队时间
      • CPU排队时间(所有处理器都忙着处理更高优先级的任务)
      • 磁盘驱动器排队(包含请求页的驱动器处于繁忙状态)
      • 锁等待(请求的表或行被锁定在一个不兼容的级别)
      • 多道编程的级别,线程数或其他方面已经达到了上限
  • QUBE会忽略除磁盘驱动排队以外的其他所有类型的排队,以提供一个简单的评估过程,用于评估那些对性能影响特别大的方面.
  • QUBE的评估过程仅需要两个输入变量,TR和TS,必要时还有第三输入变量F
    • 比较值: LRT = TR x 10ms + TS x 0.01ms
    • 绝对值: LRT = TR x 10ms + TS x 0.01ms + F x 0.1ms
    • TR: 随机访问的数量
    • TS: 顺序访问的数量
      • 0.01ms是指数据库在判断是否接受一行时所做的必要处理的时间,不管是在索引中还是表中,所有的行都必须进行这一步检测.被拒绝的行将不再需要进一步处理
    • F: 有效FETCH的数量(被接受的行的数量)

基本概念:访问

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

随机访问

  • 磁盘读与访问的区别:一次磁盘读所访问的对象是一个页,而一次访问的访问对象则是一行.一次随机磁盘读写会将一整页(通常包含很多行)读取至数据库的缓冲池中,但是根据定义,前后两次连续随机读不太可能会访问到同一个页.因此,QUBE中单次随机访问所消耗的时间与一次磁盘随机读的平均耗时是一样的,都是10ms.

顺序访问

  • 一次顺序访问是指读取物理上连续的下一行,这一行要么存储在同一页中,要么在下一页中.由于顺序读的CPU时间与I/O时间是重叠的,因此顺序访问的消耗时间就是两者中较大的那个.在QUBE中,一次顺序读所消耗的时间是0.01ms.

计算访问次数

  • 在计算索引访问次数的时候,可以将索引看成一个微表,它的行数与其指向的表包含的行数相同,且按照索引键值排列.
  • 当计算表访问次数的时候,假设表行在表页中是按理想顺序排序的,这个顺序依赖于表的组织方式.这样就可以假设一次全表扫描(N行数据)将需要一次随机访问和N-1次顺序访问.

FETCH处理

  • 被接受的行的数量可以通过FETCH调用的次数来确定(除非多行FETCH可用),这些行将经历更多的处理程序.但TS不包含这个额外的处理过程.
  • F的成本比TS打一个数量级,而另外一方面,它比TR的成本小很多.
  • 在计算FETCH调用次数时,为了避免混淆,将忽略"判断没有更多符合条件的行"的那次调用.

QUBE示例

主键索引访问
1
2
3
4
5
6
7
8
9
10
-- 主键索引CNO
SELECT CNO,LNAME,FNAME
FROM   CUST
WHERE  CNO = :CNO

索引	CNO 			TR = 1
表		CUST		  TR = 1
提取  1 x 0.1ms
LRT							TR = 2
								2 x 10ms + 0.1ms ~= 20ms
  • 通过主键索引读取一个表行需要随机访问一次表和随机访问一次索引.
聚簇索引访问
  • MySQL中聚簇索引就是主键,并且不允许你设置非主键列为聚簇索引.就算你不手动设置主键,mysql也会自动建一个隐藏的列做为主键
1
2
3
4
5
6
7
8
9
10
11
12
-- 索引(ZIP,LNAME,FNAME) 假设满足条件的数据有1000条
SELECT CNO,LNAME,FNAME
FROM   CUST
WHERE  ZIP = :ZIP AND LNAME = :LNAME
ORDER BY FNAME

索引 ZIP,LNAME,FNAME		TR = 1		TS = 1000
表		CUST							 TR = 1		 TS = 1000
提取 1000 x 0.1ms	
LRT = 2 x 10ms + 2000 x 0.01ms + 1000 x 0.1ms
		= 20ms + 20ms + 100ms
		= 140ms

在InnoDB表中按主键顺序插入行

  • 如果正在使用InnoDB表并且没有什么数据需要聚集,那么可以定义一个代理键(surrogate key)作为主键,这种主键的数据应该和应用无关,最简单的方法是使用AUTO_INCREMENT自增列.这样可以保证数据行是按顺序写入,对于根据主键做关联操作的性能也会更好.
  • 最好避免随机的(不连续且值的分布范围非常大)聚簇索引,特别是对于I/O密集型的应用.
  • 使用自增的整数ID作为主键,因为主键的值是顺序的,所以InnoDB把每一条记录都存储在上一条记录的后面.当达到页的最大填充因子时(InnoDB默认的最大填充因子时页大小的15/16,留出部分空间用于修改),下一条记录就会写入新的页中.一旦数据按照这种顺序的方式加载,主键页就会近似于被顺序的记录填满.
  • 使用UUID聚簇索引的作为主键,因为新行的主键值不一定比之前插入的大,所以InnoDB无法简单地总是把新行插入到索引的最后,而是需要为新的行寻找合适的位置(通常是已有数据的中间位置),并且分配空间.这样会增加很多额外工作,并导致数据分布不够优化.包括以下缺点:
    • 写入的目标页可能已经刷到磁盘上并从缓存中移除,或者是还没有被夹在到缓存中,InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中.这样导致大量的随机I/O
    • 因为写入是乱序的,InnoDB不得不频繁地做页分裂操作,以便为新的行分配空间.页分裂会导致移动大量数据,一次插入最少需要修改三个也不是一个页.
    • 由于频繁的页分裂,页会变得稀疏并被不规则填充,所以最终数据会有碎片.
  • 所以在使用InnoDB时应该尽可能地按主键顺序插入数据,并且尽可能使用单调增加的聚簇键的值来插入新行.

减少索引和数据碎片

  • B-Tree索引可能会碎片化,这会降低查询的效率,碎片化的索引可能会以很差或者无序的方式存储在磁盘上.根据设计,B-Tree需要随机磁盘访问才能定位到叶子页,所以随机访问是不可避免的.然而,如果叶子在物理分布上是顺序且紧凑,那么查询的性能就会更好,否则,对于范围查询,索引覆盖扫描等操作来说,速度可能会降低很多倍.
  • 表的数据存储也可能碎片化,然而,数据存储的碎片化比索引更加复制.有三种类型

行碎片(Row fragmentation)

  • 这种碎片指的是数据行被存储在多个地方的多个片段中.即使查询只从索引中访问一行记录,行碎片也会导致性能下降.

行间碎片(Intra-row fragmentation)

  • 行间碎片是指逻辑上顺序的页,或者行在磁盘上不是顺序存储的.行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响,因为这些操作原本能够从磁盘上顺序存储的数据获益.

剩余空间碎片(Free space fragmentation)

  • 剩余空间碎片是指数据页中有大量的空余空间.这会导致服务器读取大量不需要的数据,从而造成浪费.

  • 对于MyISAM表,这三类碎片化都可能发生,但InnoDB不会出现短小的行碎片,InnoDB会移动短小的行并重写到一个片段.

  • 可以使用OPTIMIZE TABLE或者导出再导入的方式重新整理数据.

InnoDB的索引模型

在InnoDB中,表都是根据主键顺序以索引的形式存储,这种存储方式的表称为索引组织表.InnoDB使用B+树索引模型,所以数据都是存储在B+树中的;

每个索引在InnoDB里面对应一个B+树;

InnoDB选择聚集索引遵循以下原则

  • 在创建表时，如果指定了主键，则将其作为聚集索引。
  • 如果没有指定主键，则选择第一个NOT NULL的唯一索引作为聚集索引。
  • 如果没有唯一索引，则内部会生成一个6字节的rowid作为主键

索引类型

  • 主键索引
    • 主键索引的叶子节点存在是整行数据.在InnoDB里,主键索引也称为聚簇索引(clustered index);
  • 非主键索引
    • 非主键索引的叶子节点的内容是主键值.在InnoDB里,非主键索引也被称为二级索引(secondary index);

img

基于主键索引和普通索引的查询有什么区别?

  • 若语句SELECT * FROM T WHERE ID = 50,即主键查询方式,则只需要搜索ID这颗B+树;
  • 若语句SELECT * FROM T WHERE k = 5,即普通索引查询方式,则需要先搜索k索引树,得ID的值为500,再到ID索引树搜索一次,这个过程称为回表.也就是说,基于非主键索引的查询需要多扫描一颗索引树.
  • 主键长度越小,普通索引的叶子节点就越小,普通索引占用空间越小

索引维护

  • B+树为了维护索引有序性,在插入新值的时候需要做必要维护.若在上图插入新的ID值为700,则只需要在R5的记录后面插入一个新记录.若新插入的ID值为400,就相对麻烦,需要逻辑上挪动后面的数据,空出位置.

  • 若R5所在的数据页已经满了,根据B+树的算法,这时候需要申请一个新的数据页,然后挪动部分数据过去,这过程称为页分裂;

    • 除了性能外,页分裂操作还影响数据页的利用率.原本放在一个页的数据,现在分到两个页中,整体空间利用率降低了大约50%.
    • 有分裂就有合并.当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并.合并的过程,可以认为分裂过程中的逆过程.
自增主键的使用场景
  • 自增主键是指自增列上定义的主键,在建表语句中一般定义为:NOT NULL PRIMARY KEY AUTO_INCREMENT
  • 插入新记录的时候可以不指定ID的值,系统会获取当前ID最大值加1作为下一条的ID值.即自增主键的插入数据模式,符合递增插入的场景,每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点分裂.
    • 而有些业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高.
      • 适合业务字段直接做主键的场景?
        • 要求只有一个索引,该索引必须是唯一索引.
  • 主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小.

Change buffer

  • 当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,InnoDB会将这些更新操作换成在change buffer中,这样就不需要从磁盘中读入这个数据页了.在下次查询需要访问这个数据也的时候,将数据页读入内存,然后执行change buffer中与这个页有关的操作.通过这种方式就能保证这个数据逻辑的正确性.
  • 需要说明的是,虽然名字叫做change buffer,实际上它是可以持久化的数据.也就是说change buffer在内存中有拷贝,也会被写入到磁盘上.
  • 将change buffer中的操作应用到原数据页,得到最新结果的过程称为merge.除了访问这个数据页会触发merge外,系统有后台现场会定期merge.在数据库正常关闭(shutdown)的过程中,也会执行merge操作.
    • merge的执行流程
      • 从磁盘读入数据页到内存(老版本的数据页);
      • 从change buffer里找出这个数据页的change buffer 记录(可能有多个),依次应用得到新版数据页;
      • 写redo log.这个redo log包含了数据的变更和change buffer的变更;
    • 到这里merge过程就结束了,这时候,数据页和内存中change buffer对应的磁盘位置都还没有修改,属于脏页,之后各自刷回自己的物理数据,就是另外一个过程了.
  • 显然,如果能够将更新操作先记录在change buffer,减少随机磁盘访问,语句的执行速度会得到明显的提示.而且数据读入内存是需要占用buffer pool的,所以这种方式还能够避免占用内存,提高内存利用率.

什么条件下可以使用change buffer呢?

  • 对于唯一索引来说,所有的更新操作都要先判断这个操作是否违反唯一性约束.比如,要对插入的记录,就要先判断现在表中是否已经存在了这条记录,而这必须要将数据页读入内存才能判断.如果都已经读入到内存中了,那直接更新内存会更快,就没必要使用change buffer了.
  • 因此,唯一索引的更新就不能使用change buffer,实际上也只有普通索引可以使用.
  • change buffer用的是buffer pool里的内存,因此不能无限增大.change buffer的大小,可以通过参数innodb_change_buffer_max_size来动态设置.这个参数设置为50的时候,表示change buffer的大小最多只能占用buffer pool的50%.

在一张表中插入一条新记录,InnoDB的处理流程是怎样的?

  • 第一种情况是,这个记录是要更新的目标也在内存中.

    • 对于唯一索引来说,找到要插入的位置,判断到没有冲突后,插入这个值,语句执行结束.
    • 对于普通索引来说,找到要插入的位置,插入这个值,语句执行结束.
    • 普通索引和唯一索引对更新语句的性能影响的差别,只是一个判断,只会耗费微小的CPU的时间.

  • 第二种情况是,这个记录要更新的目标页不在内存中.

    • 对于唯一索引来说,需要将数据页读入内存,判断到没有冲突后,插入这个值,语句执行结束;

    • 对于普通索引来说,则是将更新记录在change buffer,语句执行就结束了.

change buffer的使用场景

  • Change buffer只限于用在普通索引的场景下,而不适用于唯一索引.
普通索引的所有场景,使用change buffer都可以起到加速作用吗?
  • 因为merge的时候是真正进行数据更新的时刻,而change buffer的主要目的就是将记录的变更动作缓存下来,所以在一个数据页做merge之前,change buffer记录的变更越多(也就是这个页面上要更新的次数越多),受益就越大.
    • 因此,对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时change buffer的使用效果最好.这种业务模型常见的就是账单类,日志类的系统.
    • 反之,假设一个业务的更新模式是写入之后马上会做查询,那么即使满足了条件,将更新先记录在change buffer,但之后由于马上要访问这个数据页,会立即触发merge过程.这样随机访问I/O的次数不会减少,反而增加了change buffer的维护代价.所以,对于这种业务模式来说,change buffer反而起到了副作用.

索引的选择

  • 普通索引和唯一索引应该怎么选择,其实这两类索引在查询能力上是没差别的,主要考虑的是对更新性能的影响.所以建议尽量选择普通索引.
  • 若所有的更新后面,都马上伴随着对这个记录的查询,那么应该关闭change buffer.而其他情况下,change buffer都能提升更新性能.

change buffer 和 redo log的区别

  • redo log主要节省的是随机写磁盘的IO消耗(转成顺序写)
  • change buffer主要节省的则是随机读磁盘的IO消耗.

索引查看

  • 在MySQL中可以通过系统表innodb_index_stats来查看索引选择性如何，并且可以看到组合索引中每一个字段的选择性如何，还可以计算索引的大小.
1
2
3
4
5
6
7
8
9
mysql> select stat_value as pages,index_name,stat_value*@@innodb_page_size /1024/1024 as size from mysql.innodb_index_stats where (table_name = '表名' and database_name='数据库' and stat_description =
'Number of pages in the index' and stat_name = 'size') group by index_name;
+-------+------------+--------------+
| pages | index_name | size         |
+-------+------------+--------------+
| 13741 | PRIMARY    | 214.70312500 |
|   993 | k_1        |  15.51562500 |
+-------+------------+--------------+
2 rows in set (0.00 sec)