索引设计与优化-读书笔记-1

2021/7/26 6:08:02

本文主要是介绍索引设计与优化-读书笔记-1,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

最近读了《数据库索引设计与优化》一书。
直呼内行,以下是读书笔记。

聚簇索引

新插入的表行所在的表页的索引是聚簇索引。
若索引行的顺序和表行的索引具有强关联性可说这个索引是聚集的。但是不一定是聚簇索引。
一个表只允许有一个聚簇索引。在某个特定时间可能会有多个索引是聚集的。

索引页和表页

表和索引行都被存储在页中。页的大小一般为4/8 KB. 缓冲池和IO的活动都是基于页的。
页的大小决定了一页可以存储多少个索引行,表行。

索引页和表页都是在页内存储数据,差异是一个存储的是data数据,一个存储的是索引数据。若索引页存储的是非聚簇索引,那么连续的索引页指向的是非连续的data数据页。此时全索引扫描是顺序IO,对指向的数据的扫描是随机Io.

聚簇索引页是连续的所指向的data数据页也是连续的。通过聚簇索引扫描表行是顺序IO.

缓冲区

内存缓存区,通常非常大,可以存储成千上万的页。MySQL缓冲区的目的是将常用的数据缓存起来避免频繁的磁盘IO带来的性能损耗。每一个DBMS会根据对象类型(表和索引)及页的情况拥有多个缓冲区。

磁盘IO

可以分为顺序IO和随机IO

顺序IO: 指读写操作的访问地址连续。在顺序IO访问中,HDD所需的磁道搜索时间显着减少,因为读/写磁头可以以最小的移动访问下一个块。数据备份和日志记录等业务是顺序IO业务。
随机IO:指读写操作时间连续,但访问地址不连续,随机分布在磁盘的地址空间中。
mysql在处理IO的过程中通常都会伴随着预读取,局部预读原理告诉我们,当计算机访问一个地址的数据时候,与他相邻的地址的数据也有较大几率访问到所以一次io会把相邻页的数据也加载到缓存区当中去。

随机IO耗时估算

对一页或者多个连续页一次数据读取我们认为是一次IO. 一次随机IO的耗时大概是10ms。
每次读取数据的时间大致可分为

  1. 排队等待时间:可能发生的排队时间
  2. 寻道时间: 是指磁盘 磁臂震动到指定磁道所需要的时间,一般在5ms内
  3. 半圈旋转:找到指定磁道后还需要旋转到达目标页数据。
  4. 数据传输耗时: ,将数据从磁盘传输到数据库缓冲区的时间,耗时相对较小 一般在1ms内。

其中寻道时间和旋转时间称为服务时间。同数据库缓冲区一样,磁盘也有缓冲区,若数据存在于磁盘缓冲区,寻道时间和旋转时间均可省略,IO时间将会降低在1ms左右。

顺序IO耗时估算

以下都是顺序IO

  1. 全表扫描:一般是按顺序读取数据页
  2. 全索引扫描: 按顺序扫描存储索引数据的页。但是索引页存储的数据地址指针,指向的页可能不连续,索引后对应的数据扫描是随机IO
  3. 索引片扫描:同2
  4. 通过聚簇索引扫描表行: 聚簇索引后直接指向数据,且聚簇索引的增长一般是连续的,聚簇索引所指向的地址页也是连续的,是顺序扫描。

一般DBMS会知道哪些索引和表页需要被顺序的读取,且能识别出不在缓冲区的页,然后发出多页的一次IO请求。对于平均4K的表页来说在40MB/s的读取速度下,顺序IO的耗时可能为0.1ms.
且通常伴随着预读,在需要所需数据前将一部分数据读取到缓冲区当中去。

增加索引的代价

响应耗时分析

前提:
假设在一个索引上添加一行需要耗时10ms当前情况不考虑异步写 那么索引的新增需要找到对应的索引页的插入位置,对于非聚簇索引这个位置通常不是最后一个索引页末尾,寻找对于的索引页是一个随机IO过程,可以认为是估算值10ms
问题:

  1. 在一个事务中向一张有10条索引的表中插入1行数据。
  2. 在一个事务中向一张有10条索引的表中插入20行数据。

分析以上情况下随机IO次数:

  1. 10条索引包含9非聚簇索引和1聚簇索引。需要10次随机读。顺序读的第一次也是随机读,需要寻道时间 旋转才能在后续开启顺序读。
  2. 9个非聚簇索引需要9*20=180次随机读。聚簇第一次随机后续都是顺序读。以供需要181次随机读。

磁盘负载分析

被修改的叶子页最终都会落到磁盘上去,由于数据库的写是异步的,所以写不会影响事务时间,但是写会增加磁盘的负载,如果一张表的插入较高的话,磁盘负载可能会变成限制索引数量的主要问题。

磁盘空间限制

如果一个表中有千万行以上的数据,索引磁盘空间的成本可能会成为一个限制因素,每一次数据的写入都需要增加对应索引的空间。



这篇关于索引设计与优化-读书笔记-1的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程