精华内容
下载资源
问答
  • 本文介绍关于 Oracle 索引的结构。大概了解 Oracle 索引底层数据结构,从而更好地理解 Oracle 索引对增、删、改、查的性能
  • Oracle 索引数据结构

    2012-11-15 00:17:00
    本文内容 B-树(B-tree) 散列(Hash) k-d 树(k-d tree) 点四叉树(Point Quadtree) 本文介绍关于 Oracle 索引结构。大概了解 Oracle...

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28425/ext_idx_frmwork.htm#CHDDEEFG

    本文内容

    • B-树(B-tree)
    • 散列(Hash)
    • k-d 树(k-d tree)
    • 点四叉树(Point Quadtree)

    本文介绍关于 Oracle 索引的结构。大概了解 Oracle 索引底层的数据结构,从而更好地理解 Oracle 索引对增、删、改、查的性能。

    B-树(B-tree)


    非索引的结构能满足所有需要,但自平衡的 B-树索引结构更能优化在大数据集上检索的性能。每个 B-树节点拥有多个键和指针。特定 B-树支持的一个节点中键的最大数量是那颗树的顺序。每个节点都具有一个潜在的 order+1 指针,指向比它更低一级的节点。

    例如,如图 1 所示,order=2 的 B-树具有三个指针,分别指向:比它第一个键小的子节点(最左边的指针);比它第一个键大,比第二个键小的子节点(中间的指针);比它第二个键大的子节点(最右边的指针)。因此,B-树算法,最大限度地减少定位记录所需的读写,通过传递比二叉树算法更少的节点,二叉树对每个确定的节点,用一个键和最多两个子节点(二叉树的结构是一个键值,左右两个指针,B-树是二叉树的扩展)。下图描述的是克努特变换(Knuth variation),它的索引由两部分组成:一个顺序集(Sequence set),提供快速顺序的访问数据;一个索引集(Index set),提供直接访问顺序集。

    虽然,B-树的节点,一般不包含相同数量的数据值,并且他们通常包含一定量的未使用空间,B-树算法确保树保持平衡,和叶节点在同一级上。

     

    addci034

    图 1 B-树

     

    散列(Hash)


    散列根据一个给定字段值快速直接地访问一个特定的已存储的记录。每个记录被放置的位置是根据同一个函数,记录的一些字段域的函数计算的。并用相同的函数插入和更新。

    散列的问题是记录的物理顺序与它们的逻辑顺序没有任何关系。另外,散列会在磁盘上存在大量未使用的区域。

    addci035

    图 2 散列

     

    k-d 树(k-d tree)


    具有两维的数据,例如经度和纬度,可用通过使用 k-d树变换,称为 2-d 树,被有效地存储和检索。

    在这个结构,每个节点的数据类型,是字段信息,两个坐标,和指向两个子节点的左指针和右指针。

    addci036

    图 3 2-d 树

    这种结构利于范围查询。也就是说,如果用户指定一个点(xx, xx)和一个距离,那么,查询会返回在这个指定的原来点距离内的所有点集合。

    2-d 树很容易实现。但是因为,一个包含 k 个节点的 2-d 树具有 k 高度,因此,插入和查询复杂。

     

    点四叉树(Point Quadtree)


    点四叉树,在图 4 所示,也用来表示在一个两维空间中的点数据,但这些结构把区域划分为四个部分,而 2-d 树划分为两个。节点记录类型的字段由属性信息组成,包括两个坐标和指向四个子节点的方位点,按顺时针,如西北NW,西南SW,东北NE,东南SE。

    addci038

    图 4 Point Quadtree 索引结构

    点四叉树跟 2-d 树一样也很容易实现。一个包含 k 个节点的四叉树具有 k 高度,插入和查询复杂。每个比较都要求在至少两个坐标上进行。然而,实际中,从 root 到 leaf 的长度在点四叉树中往往较短。

    参看四叉树:http://zh.wikipedia.org/wiki/%E5%9B%9B%E5%8F%89%E6%A0%91

    参看四叉树的 Python 代码:http://zh.wikipedia.org/zh/File:Point_quadtree.svg

    复制上面第二个链接里边提供的 Python 代码,做适当修改。因为,网页提供的代码只能运行在较低版本 Python。Python 3 之后的版本跟之前的差异较大。因此,下载本文最后源代码,并在 Python 3.3 的 IDLE 运行。会得到如下输出:

    Python 3.3.0 (v3.3.0:bd8afb90ebf2, Sep 29 2012, 10:57:17) [MSC v.1600 64 bit (AMD64)] on win32
    Type "copyright", "credits" or "license()" for more information.
    >>> ================================ RESTART ================================
    >>> 
    <?xml version="1.0" encoding="iso-8859-1"?>
    <!DOCTYPE svg PUBLIC "-//W3C//DTD SVG 1.1//EN"
     "http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd">
    <svg xmlns="http://www.w3.org/2000/svg" version="1.1" width="400pt" height="400pt" viewBox="0 0 400 400">
        <g fill="none" stroke="blue">
            <line x1="1" y1="1" x2="1" y2="399" />
            <line x1="1" y1="399" x2="399" y2="399" />
            <line x1="399" y1="399" x2="399" y2="1" />
            <line x1="399" y1="1" x2="1" y2="1" />
            <line x1="200" y1="1" x2="200" y2="399" />
            <line x1="1" y1="200" x2="399" y2="200" />
            <line x1="100" y1="1" x2="100" y2="200" />
            <line x1="1" y1="100" x2="200" y2="100" />
            <line x1="50" y1="1" x2="50" y2="100" />
    ……

    复制输出的结果,命名为 .svg,.html 也行,用浏览器打开,会呈现下图:

    2012-11-15_000830

    图 5 一个 8*8 大小的点四叉树区域

    看这个图,从左上角开始,顺时针。你可以当做“根据需要,是否要点,不断按 4 个分裂其中一个方块”。

    o_r_%E7%BF%BB%E8%AF%91.jpgo_%E5%8E%9F%E5%88%9B.jpg

    下载 Point Qudatree Python 演示

    转载于:https://www.cnblogs.com/liuning8023/archive/2012/11/15/2728536.html

    展开全文
  • Mysql索引底层数据结构与算法

    千次阅读 2020-01-31 18:33:58
    目录 1. 索引概念 2. 索引结构 ...索引概念:索引是帮助MySQL高效获取数据的排好序的数据结构,更通俗的说数据库索引好比是一本书的目录,能加快数据库的查询速度 索引结构:二叉树, 红黑树, HASH, BTREE ...

    目录

    1. 索引概念

    2. 索引结构

    3. MyISAM和InnoDB的索引实现

    4. 联合索引

    5. 什么情况下应不建或少建索引

    6. MySql在建立索引优化时需要注意的问题


    1. 索引概念

    索引概念: 索引是帮助MySQL高效获取数据的排好序的数据结构, 更通俗的说数据库索引好比是一本书的目录,能加快数据库的查询速度

    索引结构: 二叉树, 红黑树, HASH, BTREE

    数据库索引为什么要用BTREE而不用红黑树, 二叉树, HASH呢?

    1. AVL树和红黑树这些二叉树结构的数据结构可以达到最高的查询效率这是毋庸置疑的。

        既然如此,那么数据库索引为什么不用 AVL树或者红黑树呢?

        这就牵扯到一个问题了, AVL数和红黑树基本都是存储在内存中才会使用的数据结构,那磁盘中会有什么不同呢?

        由于操作系统读写磁盘的基本单位是扇区,而文件系统的基本单位是簇(Cluster)。也就是说,磁盘读写有一个最少内容的限制,即使我们只需要这个簇上的一个字节的内容,也需要把一整个簇上的内容读完。

        那么,现在问题就来了

       一个父节点只有 2 个子节点,并不能填满一个簇上的所有内容啊?那多余的内容岂不是要浪费了?我们怎么才能把浪费的这部分内容利用起来呢?答案就是 B+ 树。

       由于 B+ 树分支比二叉树更多,所以相同数量的内容,B+ 树的深度更浅,深度更浅也就代表这磁盘 io 次数少!数据库设计的时候 B+ 树有多少个分支都是按照磁盘一个簇上最多能放多少节点设计的啊!

       所以,涉及到磁盘上查询的数据结构一般都用 B+ 树

    2. 二叉树就更不用说了, 有红黑数相同的缺陷外, 在一些极端的数据规则下, 二叉树的深度会变得很深, 使IO次数大大增加

    3. Hash的话是存在致命缺陷的, 也就是没有办法进行范围查询和Hash冲突,所以在一些不存在范围查询的字段上可以使用Hash来作为索引结构

    2. 索引结构

    2.1 B-Tree

    • 度(Degree)-节点的数据存储个数
    • 叶节点具有相同的深度
    • 叶节点的指针为空
    • 节点中的数据key从左到右递增排列

    2.2 B+Tree

    • 非叶子节点不存储data,只存储key,可以增大度
    • 叶子节点不存储指针
    • 顺序访问指针,提高区间访问的性能

    B+Tree索引的性能分析

    • 一般使用磁盘I/O次数评价索引结构的优劣
    • 预读:磁盘一般会顺序向后读取一定长度的数据(页的整数倍)放入内存
    • 局部性原理:当一个数据被用到时,其附近的数据也通常会马上被使用
    • B+Tree节点的大小设为等于一个页,每次新建节点直接申请一个页的空间,这样就保证一个节点物理上也存储在一个页里,就实现了一个节点的载入只需一次I/O
    • B+Tree的度d一般会超过100,因此h非常小(一般为3到5之间)

    3. MyISAM和InnoDB的索引实现

    3.1 MyISAM索引实现(非聚集)

    注意: MyISAM索引文件和数据文件是分离的

    3.2 InnoDB索引实现(聚集)

    • 数据文件本身就是索引文件
    • 表数据文件本身就是按B+Tree组织的一个索引结构文件
    • 聚集索引-叶节点包含了完整的数据记录
    • 为什么InnoDB表必须有主键,并且推荐使用整型的自增主键?
    • 为什么非主键索引结构叶子节点存储的是主键值?(一致性和节省存储空间)
       

    问题: InnoDB 的辅助索引叶子节点为什么不直接保存的记录地址而要存主键键值?

    4. 联合索引

    联合索引: 联合索引是指对表上的多个列进行索引,联合索引也是一棵B+树,不同的是联合索引的键值数量不是1,而是大于等于2

    ​CREATE TABLE `test` (
      `id` varchar(20) NOT NULL,
      `staff_id` varchar(20) NOT NULL,
      `staff_name` varchar(20) NOT NULL,
      `date` varchar(20) NOT NULL,
      `remark` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_test` (`staff_id`,`staff_name`,`date`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

     联合索引使用原则:

    1. 需要加索引的字段,要在where条件中
    2. 数据量少的字段不需要加索引。最窄的字段放在键的左边
    3. 如果where条件中是OR关系,必须所有的or条件都必须是独立索引,否则加索引不起作用。见:mysql关于or的索引问题
    4. 最左前缀原则
    5. 只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL

    最左前缀原则:

    1. b+ 树的数据项是复合的数据结构,比如 (name,age,sex) 的时候,b+ 树是按照从左到右的顺序来建立搜索树的,比如当 (张三,20,F) 这样的数据来检索的时候,b+ 树会优先比较 name 来确定下一步的所搜方向,如果 name 相同再依次比较 age 和 sex,最后得到检索的数据;但当 (20,F) 这样的没有 name 的数据来的时候,b+ 树就不知道第一步该查哪个节点,因为建立搜索树的时候 name 就是第一个比较因子,必须要先根据 name 来搜索才能知道下一步去哪里查询

    3. 比如当 (张三, F) 这样的数据来检索时,b+ 树可以用 name 来指定搜索方向,但下一个字段 age 的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是 F 的数据了, 这个是非常重要的性质,即索引的最左匹配特性。(这种情况无法用到联合索引)


    5. 什么情况下应不建或少建索引

    5.1 表记录太少

    如果一个表只有5条记录,采用索引去访问记录的话,那首先需访问索引表,再通过索引表访问数据表,一般索引表与数据表不在同一个数据块,这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出,处理速度显然会比用索引快。

    如表zl_sybm(使用部门)一般只有几条记录,除了主关键字外对任何一个字段建索引都不会产生性能优化,实际上如果对这个表进行了统计分析后ORACLE也不会用你建的索引,而是自动执行全表访问。如:

    select * from zl_sybm where sydw_bh=’5401’(对sydw_bh建立索引不会产生性能优化)

    5.2 经常插入、删除、修改的表

    对一些经常处理的业务表应在查询允许的情况下尽量减少索引,如zl_yhbm,gc_dfss,gc_dfys,gc_fpdy等业务表。

    5.3 数据重复且分布平均的表字段

    假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。

    5.4 经常和主字段一块查询但主字段索引值比较多的表字段

    如gc_dfss(电费实收)表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作 标志来具体查询某一笔收款的情况,如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间,从实际上分析一笔收款如果按收费序号索引就已 经将记录减少到只有几条,如果再按后面的几个字段索引查询将对性能不产生太大的影响。

    对千万级MySQL数据库建立索引的事项及提高性能的手段

    一、注意事项:

    首先,应当考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据,在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。

    其次,在对建立索引的时候要对表进行加锁,因此应当注意操作在业务空闲的时候进行。

    二、性能调整方面:

    首当其冲的考虑因素便是磁盘I/O。物理上,应当尽量把索引与数据分散到不同的磁盘上(不考虑阵列的情况)。逻辑上,数据表空间与索引表空间分开。这是在建索引时应当遵守的基本准则。

    其次,我们知道,在建立索引的时候要对表进行全表的扫描工作,因此,应当考虑调大初始化参数db_file_multiblock_read_count的值。一般设置为32或更大。

    再次,建立索引除了要进行全表扫描外同时还要对数据进行大量的排序操作,因此,应当调整排序区的大小。

        9i之前,可以在session级别上加大sort_area_size的大小,比如设置为100m或者更大。

        9i以后,如果初始化参数workarea_size_policy的值为TRUE,则排序区从pga_aggregate_target里自动分配获得。

    最后,建立索引的时候,可以加上nologging选项。以减少在建立索引过程中产生的大量redo,从而提高执行的速度。

    6. MySql在建立索引优化时需要注意的问题

    设计好MySql的索引可以让你的数据库飞起来,大大的提高数据库效率。设计MySql索引的时候有一下几点注意:

    6.1 创建索引

    对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加

    索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下

    降。但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过度索引。

    6.2 复合索引

    比如有一条语句是这样的:select * from users where area=’beijing’ and age=22;

    如果我们是在area和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效

    率,但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,

    salary)的复合索引,那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引,这被称为最佳左前缀

    特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减。

    6.3 索引不会包含有NULL值的列

    只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

    6.4 使用短索引

    对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

    6.5 排序的索引问题

    mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

    6.6 like语句操作

    一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

    6.7 不要在列上进行运算

    select * from users where

    YEAR(adddate)

    6.8 不使用NOT IN和 != 操作

    NOT IN和 != 操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替,id != 3则可使用id>3 or id < 3

    来替换。

    参考播客: 【推荐】mysql联合 索引(复合索引)的探讨

    展开全文
  • Oracle数据库索引底层实现原理笔记

    千次阅读 2020-08-19 16:49:20
    当然, 有的数据库也使用哈希桶作用索引数据结构 , 然而, 主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。 我们平时建表的时候都会为表加上主键, 在某些关系数据库中, 如果建表时不指定主键,...
    • 为什么要给表加上主键?

    • 为什么加索引后会使查询变快?

    • 为什么加索引后会使写入、修改、删除变慢?

    • 什么情况下要同时在两个字段上建索引?
      想要理解索引原理必须清楚一种数据结构「平衡树」(非二叉),也就是b tree或者 b+ tree,重要的事情说三遍:“平衡树,平衡树,平衡树”。当然, 有的数据库也使用哈希桶作用索引的数据结构 , 然而, 主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。

    我们平时建表的时候都会为表加上主键, 在某些关系数据库中, 如果建表时不指定主键,数据库会拒绝建表的语句执行。 事实上, 一个加了主键的表,并不能被称之为「表」。一个没加主键的表,它的数据无序的放置在磁盘存储器上,一行一行的排列的很整齐, 跟我认知中的「表」很接近。如果给表上了主键,那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构,也就是上面说的「平衡树」结构,换句话说,就是整个表就变成了一个索引。没错, 再说一遍, 整个表变成了一个索引,也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键, 一个表只能有一个「聚集索引」,因为主键的作用就是把「表」的数据格式转换成「索引(平衡树)」的格式放置。

    在这里插入图片描述

    上图就是带有主键的表(聚集索引)的结构图。图画的不是很好, 将就着看。其中树的所有结点(底部除外)的数据都是由主键字段中的数据构成,也就是通常我们指定主键的id字段。最下面部分是真正表中的数据。 假如我们执行一个SQL语句:

    select * from table where id = 1256;

    首先根据索引定位到1256这个值所在的叶结点,然后再通过叶结点取到id等于1256的数据行。 这里不讲解平衡树的运行细节, 但是从上图能看出,树一共有三层, 从根节点至叶节点只需要经过三次查找就能得到结果。如下图

    在这里插入图片描述

    假如一张表有一亿条数据 ,需要查找其中某一条数据,按照常规逻辑, 一条一条的去匹配的话, 最坏的情况下需要匹配一亿次才能得到结果,用大O标记法就是O(n)最坏时间复杂度,这是无法接受的,而且这一亿条数据显然不能一次性读入内存供程序使用, 因此, 这一亿次匹配在不经缓存优化的情况下就是一亿次IO开销,以现在磁盘的IO能力和CPU的运算能力, 有可能需要几个月才能得出结果 。如果把这张表转换成平衡树结构(一棵非常茂盛和节点非常多的树),假设这棵树有10层,那么只需要10次IO开销就能查找到所需要的数据, 速度以指数级别提升,用大O标记法就是O(log n),n是记录总树,底数是树的分叉数,结果就是树的层次数。换言之,查找次数是以树的分叉数为底,记录总数的对数,用公式来表示就是

    在这里插入图片描述

    用程序来表示就是Math.Log(100000000,10),100000000是记录数,10是树的分叉数(真实环境下分叉数远不止10), 结果就是查找次数,这里的结果从亿降到了个位数。因此,利用索引会使数据库查询有惊人的性能提升。

    然而, 事物都是有两面的, 索引能让数据库查询数据的速度上升, 而使写入数据的速度下降,原因很简单的, 因为平衡树这个结构必须一直维持在一个正确的状态, 增删改数据都会改变平衡树各节点中的索引数据内容,破坏树结构, 因此,在每次数据改变时, DBMS必须去重新梳理树(索引)的结构以确保它的正确,这会带来不小的性能开销,也就是为什么索引会给查询以外的操作带来副作用的原因。

    讲完聚集索引 , 接下来聊一下非聚集索引, 也就是我们平时经常提起和使用的常规索引。

    非聚集索引和聚集索引一样, 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段, 假如给user表的name字段加上索引 , 那么索引就是由name字段中的值构成,在数据改变时, DBMS需要一直维护索引结构的正确性。如果给表中多个字段加上索引 , 那么就会出现多个独立的索引结构,每个索引(非聚集索引)互相之间不存在关联。 如下图

    在这里插入图片描述

    每次给字段建一个新索引, 字段中的数据就会被复制一份出来, 用于生成索引。 因此, 给表添加索引,会增加表的体积, 占用磁盘存储空间。

    非聚集索引和聚集索引的区别在于, 通过聚集索引可以查到需要查找的数据, 而通过非聚集索引可以查到记录对应的主键值 , 再使用主键的值通过聚集索引查找到需要的数据,如下图

    在这里插入图片描述

    不管以任何方式查询表, 最终都会利用主键通过聚集索引来定位到数据, 聚集索引(主键)是通往真实数据所在的唯一路径。

    然而, 有一种例外可以不使用聚集索引就能查询出所需要的数据, 这种非主流的方法 称之为「覆盖索引」查询, 也就是平时所说的复合索引或者多字段索引查询。 文章上面的内容已经指出, 当为字段建立索引以后, 字段中的内容会被同步到索引之中, 如果为一个索引指定两个字段, 那么这个两个字段的内容都会被同步至索引之中。

    先看下面这个SQL语句

    //建立索引

    create index index_birthday on user_info(birthday);

    //查询生日在1991年11月1日出生用户的用户名

    select user_name from user_info where birthday = ‘1991-11-1’

    这句SQL语句的执行过程如下

    首先,通过非聚集索引index_birthday查找birthday等于1991-11-1的所有记录的主键ID值

    然后,通过得到的主键ID值执行聚集索引查找,找到主键ID值对就的真实数据(数据行)存储的位置

    最后, 从得到的真实数据中取得user_name字段的值返回, 也就是取得最终的结果

    我们把birthday字段上的索引改成双字段的覆盖索引

    create index index_birthday_and_user_name on user_info(birthday, user_name);

    这句SQL语句的执行过程就会变为

    通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容,然而, 叶节点中除了有user_name表主键ID的值以外, user_name字段的值也在里面, 因此不需要通过主键ID值的查找数据行的真实所在, 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式, 可以省略不使用覆盖索引查找的后面两个步骤, 大大的提高了查询性能,如下图

    在这里插入图片描述

    数据库索引的大致工作原理就是像文中所述, 然而细节方面可能会略有偏差,这但并不会对概念阐述的结果产生影响 。

    展开全文
  • MySQL索引底层数据结构

    千次阅读 2018-12-02 12:06:34
    首先,在讨论数据结构之前,先了解一下MySQL的存储引擎和数据存取原理。 这里有一篇关于存储引擎的文章:https://blog.csdn.net/qq_41618510/article/details/84680226 下图是分别用InnoDB和Myisam引擎存储数据的...

     首先,在讨论数据结构之前,先了解一下MySQL的存储引擎和数据存取原理。

    这里有一篇关于存储引擎的文章:https://blog.csdn.net/qq_41618510/article/details/84680226

    下图是分别用InnoDB和Myisam引擎存储数据的文件目录

    可以看出

    Myisam 的存储文件有三个,后缀名分别是 .frm、.MYD、MYI,其中 .frm 是表的定义文件,.MYD 是数据文件,.MYI 是索引文件。

    Myisam 只支持表锁,且不支持事务。Myisam 由于有单独的索引文件,在读取数据方面的性能很高 。

    InnoDB 的存储文件有两个,后缀名分别是 .frm 和 .idb,其中 .frm 是表的定义文件,而 idb 是数据文件。

    InnoDB 中存在表锁和行锁,不过行锁是在命中索引的情况下才会起作用。

    InnoDB 支持事务,且支持四种隔离级别(读未提交、读已提交、可重复读、串行化),默认的为可重复读;而在 Oracle 数据库中,只支持串行化级别和读已提交这两种级别,其中默认的为读已提交级别。

     

    数据存储的原理(硬盘)

    信息存储在硬盘里,硬盘是由很多的盘片组成,通过盘片表面的磁性物质来存储数据。

    把盘片放在显微镜下放大,可以看到盘片表面是凹凸不平的,凸起的地方被磁化,代表数字 1,凹的地方没有被磁化,代表数字 0,因此硬盘可以通过二进制的形式来存储表示文字、图片等的信息。

    硬盘有很多种,但是都是由盘片、磁头、盘片主轴、控制电机、磁头控制器、数据转换器、接口、缓存等几个部分组成。

    所有的盘片都固定在一个旋转轴上,这个轴即盘片主轴。

    所有的盘片之间是绝对平行的,在每个盘片的盘面上都有一个磁头,磁头与盘片之间的距离比头发丝的直径还小。

    所有的磁头连在一个磁头控制器上,由磁头控制器负责各个磁头的运动,磁头可沿盘片的半径方向移动,实际上是斜切运动,每个磁头同一时刻必须是同轴的,即从正上方往下看,所有磁头任何时候都是重叠的。

    由于技术的发展,目前已经有多磁头独立技术了,在此不考虑此种情况。

    盘片以每分钟数千转到上万转的速度在高速运转,这样磁头就能对盘片上的指定位置进行数据的读写操作。

    由于硬盘是高精密设备,尘埃是其大敌,所以必须完全密封。

    2. 数据读写的原理

    硬盘在逻辑上被划分为磁道、柱面以及扇区。

    磁头靠近主轴接触的表面,即线速度最小的地方,是一个特殊的区域,它不存放任何数据,称为启停区或者着陆区,启停区外就是数据区。

    在最外圈,离主轴最远的地方是 “0” 磁道,硬盘数据的存放就是从最外圈开始的。

    在硬盘中还有一个叫 “0” 磁道检测器的构件,它是用来完成硬盘的初始定位。

    盘面

    硬盘的盘片一般用铝合金材料做基片,硬盘的每一个盘片都有上下两个盘面,一般每个盘面都会得到利用,都可以存储数据,成为有效盘面,也有极个别的硬盘盘面数为单数。

    每一个这样的有效盘面都有一个盘面号,按顺序从上至下从 0 开始编号。

    在硬盘系统中,盘面号又叫磁头号,因为每一个有效盘面都有一个对应的读写磁头,硬盘的盘片组在 2-14 片不等,通常有 2-3 个盘片。

    磁道

    磁盘在格式化时被划分成许多同心圆,这些同心圆轨迹叫做磁道。

    磁道从外向内从 0 开始顺序编号,硬盘的每一个盘面有 300-1024 个磁道,新式大容量硬盘每面的磁道数更多,信息以脉冲串的形式记录在这些轨迹中,这些同心圆不是连续记录数据,而是被划分成一段段的圆弧。

    这些圆弧的角速度一样,由于径向长度不一样,所以线速度也不一样,外圈的线速度较内圈的线速度大,即同样的转速度下,外圈在同样时间段里,划过的圆弧长度要比内圈划过的圆弧长度大。

    每段圆弧叫做一个扇区,扇区从 1 开始编号,每个扇区中的数据作为一个单元同时读出或写入。

    磁道是看不见的,只是盘面上以特殊形式磁化了的一些磁化区,在磁盘格式化时就已规划完毕。

    柱面

    所有盘面上的同一磁道构成一个圆柱,通常称作柱面。

    每个圆柱上的磁头由上而下从 0 开始编号,数据的读 / 写按柱面进行,即磁头读 / 写数据时首先在同一柱面内从 0 磁头开始进行操作,依次向下在同一柱面的不同盘面即磁头上进行操作。

    只有在同一柱面所有的磁头全部读 / 写完毕后磁头才转移到下一柱面(同心圆再往里的柱面),因为选取磁头只需要通过电子切换即可,而选取柱面则必须机械切换,电子切换相当快,比在机械上的磁头向邻近磁道移动快得多。

    所以,数据的读 / 写按柱面进行,而不按盘面进行,也就是说,一个磁道写满数据后,就在同一柱面的下一个盘面来写,一个柱面写满后,才移到下一个扇区开始写数据,读数据也按照这种方式进行,这样就提高了硬盘的读 / 写效率。

    扇区

    操作系统以扇区形式将信息存储在硬盘上,每个扇区包括 512 个字节的数据和一些其他信息,一个扇区有两个主要部分:存储数据地点的标识符和存储数据的数据段。

    标识符就是扇区头标,包括组成扇区三维地址的三个数字:盘面号,柱面号,扇区号(块号)。

    数据段可分为数据和保护数据的纠错码(ECC)。在初始准备期间,计算机用 512 个虚拟信息字节(实际数据的存放地)和与这些虚拟信息字节相应的 ECC 数字填入这个部分。

    3. 访盘请求完成过程

    1)确定磁盘地址(柱面号,磁头号,扇区号),内存地址(源 / 目):

    当需要从磁盘读取数据的时候,系统会将数据的逻辑地址传递个磁盘,磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址,即确定要读的数据在哪个磁道,哪个扇区。

    2)为了读取这个扇区的数据,需要将磁头放到这个扇区上方,为了实现这一点:

    • A. 首先必须找到柱面,即磁头需要移动对准相应磁道,这个过程叫做寻道,所耗费时间叫做寻道时间。

    • B. 然后目标扇区旋转到磁头下,即磁盘旋转将目标扇区旋转到磁头下,这个过程耗费的时间叫做旋转时间。

    3)即一次访盘请求(读 / 写)完成过程由三个动作组成:

    • A. 寻道(时间):磁头移动定位到指定磁道。

    • B. 旋转延迟(时间):等待指定扇区从磁头下旋转经过。

    • C. 数据传输(时间):数据在磁盘与内存之间的实际传输。

    4. 磁盘的读写原理

    系统将文件存储到磁盘上时,按柱面、磁头、扇区的方式进行,即最先是第 1 磁道的第一磁头下的所有扇区,然后是同一柱面的下一个磁头……

    一个柱面存储满后就推进到下一个柱面,直到把文件内容全部写入磁盘。

    系统也以相同的顺序读出数据,读出数据时通过告诉磁盘控制器要读出扇区所在柱面号、磁头号和扇区号(物理地址的三个组成部分)进行。

    5. 减少 I/O 的预读原理

    由于存储介质的特性,磁盘本身存取就比主存慢很多,再加上机械运动耗费的时间,磁盘的存取速度往往是主存的几百分之一。

    因此,为了提高效率,要尽量减少磁盘的 I/O。

    磁盘往往不是严格地按需读取,而是每次都会预读,即使只需要一个字节,磁盘也会从这个位置开始,顺序向后读取一定长度的数据放入内存。

    这样做的理论依据是计算机科学中著名的局部性原理:

    1. 当一个数据被用到时,其附近的数据一般来说也会被马上使用。

    2. 程序运行期间所需要的数据通常比较集中。

    3. 由于磁盘顺序读取的效率很高(不需要寻道时间,只需要很少的旋转时间),因此对于具有局部性的程序来说,预读可以提高 I/O 效率。

    预读的长度一般为页(Page)的整数倍。页是计算机管理存储器的逻辑块,硬件及操作系统往往将主存和磁盘存储分割为连续的大小相等的块。

    每个存储块称为一页(在许多操作系统中,页的大小通常为 4k),主存和磁盘以页为单位交换数据,当程序要读取的数据不在主存中时,会触发一个缺页异常。

    此时系统会向磁盘发出读盘信息,磁盘会找到数据的起始位置并向后连续读取一页或几页的数据载入内存中,然后异常返回,程序继续运行。 

    MySQL 的 B+Tree

    目前大多数数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

    B+ 树索引是 B+ 树在数据库中的一种实现,是最常见也是数据库中使用最为频繁的一种索引。B+ 树中的 B 代表平衡,而不是二叉。

    因为 B+ 树是从最早的平衡二叉树演化而来的。B+ 树是由二叉查找树、平衡二叉树(AVLTree)和平衡多路查找树(B-Tree)逐步优化而来。

    二叉查找树:左子树的键值小于根的键值,右子树的键值大于根的键值。

    AVL 树:平衡二叉树(AVL 树)在符合二叉查找树的条件下,还满足任何节点的两个子树的高度最大差为 1。

    平衡多路查找树(B-Tree):为磁盘等外存储设备设计的一种平衡查找树。

    系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的,位于同一磁盘块中的数据会被一次性读取出来,而不是按需读取。

    InnoDB 存储引擎使用页作为数据读取单位,页是其磁盘管理的最小单位,默认 page 大小是 16k。

    系统的一个磁盘块的存储空间往往没有这么大,因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB。

    InnDB 在把磁盘数据读入到磁盘时会以页为基本单位,在查询数据时如果一个页中的每条数据都能助于定位数据记录的位置,这将会减少磁盘 I/O 的次数,提高查询效率。

    B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。

    为了描述 B-Tree,首先定义一条数据记录为一个二元组 [key, data],key 为记录的键值,对于不同数据记录,key 是互不相同的;data 为数据记录除 key 外的数据。

    那么 B-Tree 是满足下列条件的数据结构:

    1. d 为大于 1 的一个正整数,称为 B-Tree 的度。

    2. h 为一个正整数,称为 B-Tree 的高度。

    3. 每个非叶子节点由 n-1 个 key 和 n 个指针组成,其中 d<=n<=2d。

    4. 每个叶子节点最少包含一个 key 和两个指针,最多包含 2d-1 个 key 和 2d 个指针,叶节点的指针均为 null 。

    5. 所有叶节点具有相同的深度,等于树高 h。

    6. key 和指针互相间隔,节点两端是指针。

    7. 一个节点中的 key 从左到右非递减排列。

    8. 所有节点组成树结构。

    9. 每个指针要么为 null,要么指向另外一个节点。

    10. 如果某个指针在节点 node 最左边且不为 null,则其指向节点的所有 key 小于 v(key1),其中 v(key1) 为 node 的第一个 key 的值。

    11. 如果某个指针在节点 node 最右边且不为 null,则其指向节点的所有 key 大于 v(keym),其中 v(keym) 为 node 的最后一个 key 的值。

    12. 如果某个指针在节点 node 的左右相邻 key 分别是 keyi 和 keyi+1 且不为 null,则其指向节点的所有 key 小于 v(keyi+1) 且大于 v(keyi)。

    B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支,例:

    ?wx_fmt=png

    每个节点占用一个盘块的磁盘空间,一个节点上有两个升序排序的关键字和三个指向子树根节点的指针,指针存储的是子节点所在磁盘块的地址。

    两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。

    以根节点为例,关键字为 17 和 35,P1 指针指向的子树的数据范围为小于 17,P2 指针指向的子树的数据范围为 17~35,P3 指针指向的子树的数据范围为大于 35。

    模拟查找关键字 29 的过程:

    1. 根据根节点找到磁盘块 1,读入内存。【磁盘 I/O 操作第 1 次】

    2. 比较关键字 29 在区间(17,35),找到磁盘块 1 的指针 P2。

    3. 根据 P2 指针找到磁盘块 3,读入内存。【磁盘 I/O 操作第 2 次】

    4. 比较关键字 29 在区间(26,30),找到磁盘块 3 的指针 P2。

    5. 根据 P2 指针找到磁盘块 8,读入内存。【磁盘 I/O 操作第 3 次】

    6. 在磁盘块 8 中的关键字列表中找到关键字 29。

       

    MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的,因此力求达到树的深度不超过 3,也就是说 I/O 不需要超过 3 次。

    分析上面过程,发现需要 3 次磁盘 I/O 操作,和 3 次内存查找操作。由于内存中的关键字是一个有序表结构,可以利用二分法查找提高效率。

    而 3 次磁盘 I/O 操作是影响整个 B-Tree 查找效率的决定因素。

    B-Tree 相对于 AVLTree 缩减了节点个数,使每次磁盘 I/O 取到内存的数据都发挥了作用,从而提高了查询效率。

    B+Tree 是在 B-Tree 基础上的一种优化,使其更适合实现外存储索引结构,InnoDB 存储引擎就是用 B+Tree 实现其索引结构。

    在 B-Tree 中,每个节点中有 key,也有 data,而每一个页的存储空间是有限的,如果 data 数据较大时将会导致每个节点(即一个页)能存储的 key 的数量很小。

    当存储的数据量很大时同样会导致 B-Tree 的深度较大,增大查询时的磁盘 I/O 次数,进而影响查询效率。

    在 B+Tree 中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储 key 值信息,这样可以大大加大每个节点存储的 key 值数量,降低 B+Tree 的高度。

    B+Tree 在 B-Tree 的基础上有两点变化:

    1. 数据是存在叶子节点中的;

    2. 数据节点之间是有指针指向的。

       

    由于 B+Tree 的非叶子节点只存储键值信息,假设每个磁盘块能存储 4 个键值及指针信息,则变成 B+Tree 后其结构如下图所示:

    ?wx_fmt=png

    通常在 B+Tree 上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。

    因此可以对 B+Tree 进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找。

    8. Myisam 中的 B+Tree

    Myisam 引擎也是采用的 B+Tree 结构来作为索引结构。

    由于 Myisam 中的索引和数据分别存放在不同的文件,所以在索引树中的叶子节点中存的数据是该索引对应的数据记录的地址,由于数据与索引不在一起,所以 Myisam 是非聚簇索引。

    ?wx_fmt=png

    9. InnoDB 中的 B+Tree

    InnoDB 是以 ID 为索引的数据存储。

    采用 InnoDB 引擎的数据存储文件有两个,一个定义文件,一个是数据文件。

    InnoDB 通过 B+Tree 结构对 ID 建索引,然后在叶子节点中存储记录。

    ?wx_fmt=png

    若建索引的字段不是主键 ID,则对该字段建索引,然后在叶子节点中存储的是该记录的主键,然后通过主键索引找到对应的记录。

    联合索引底层存储结构

    单列索引其实也可以看做联合索引,索引列为1的联合索引,从下图就可以看出联合索引的底层存储跟单列索引时类似的,区别在于联合索引是每个树节点中包含多个索引值,在通过索引查找记录时,会先将联合索引中第一个索引列与节点中第一个索引值进行匹配,匹配成功接着匹配第二个索引列和索引值,直到联合索引的所有索引列都匹配完;如果过程中出现某一个索引列与节点相应位置的索引值不匹配的情况,则无需再匹配节点中剩余索引列,前往下一个节点。

     

     

    展开全文
  • Oracle索引原理

    千次阅读 2017-10-23 17:17:18
    索引原理 oracle中的索引与mysql中的索引不一样,oracle索引是存储了索引列的值以及rowid值。...oracle中的索引反应的是逻辑结构,不是物理结构索引创建的时候,是先创建叶子节点,然后再创建茎,最后创建根
  • Oracle 索引结构、内部管理

    千次阅读 2017-01-06 18:27:45
    摘要:本文对B树索引结构、内部管理等方面做了一个全面的介绍。同时深入探讨了一些与B树索引有关的广为流传的说法,比如删除记录对索引的影响,定期重建索引能解决许多性能问题等。   1.B树索引的相关概念 ...
  • Oracle索引结构

    2014-01-05 01:15:53
    其实现与二叉查找树类似,目标是减少oracle查找数据的时间。如果在一个数字列上有一个索引,那么理论上结构应该是这样的:  这个树最底层是叶子节点,包含索引键以及一个rowid(指向索引行)。叶子节点上面的称为...
  • Oracle 索引

    2014-09-10 09:42:37
    索引是由Oracle维护的可选结构,为数据提供快速的访问。准确地判断在什么地方需要使用索引是困难的,使用索引有利于调节检索速度。 当建立一个索引时,必须指定用于跟踪的表名以及一个或多个表列。一旦建立了索引,...
  • Oracle 索引详解

    万次阅读 2013-12-11 14:50:29
    Oracle 索引详解 一.索引介绍  1.1 索引的创建语法:  CREATE UNIUQE | BITMAP INDEX .  ON .  ( | ASC | DESC,  | ASC | DESC,...)  TABLESPACE   STORAGE   LOGGING | ...
  • oracle索引碎片

    万次阅读 2010-05-31 18:48:00
    oracle索引碎片
  • Oracle 索引 详解

    2013-03-15 11:48:09
    在关系数据库中,索引是一种与表有关的数据结构,它是除表以外的另一个重要模式对象。索引是建立在表的一列或多个列上的辅助对象,目的是提高表中数据的访问速度。  索引是表示数据的另一种方式,它提供的数据顺序...
  • Oracle索引——位图索引 位图索引(bitmap index)是从Oracle7.3版本开始引入的。目前Oracle企业版和个人版都支持位图索引,但标准版不支持。位图索引是为数据仓库/即席查询环境设计的,在此所有查询要求的数据在...
  • oracle 索引详解

    千次阅读 2012-07-23 16:33:04
    索引介绍  1.1 索引的创建语法:  CREATE UNIUQE | BITMAP INDEX .  ON .  ( | ASC | DESC,  | ASC | DESC,...)  TABLESPACE   STORAGE   LOGGING | NOLOGGING  COMPUTE
  • oracle索引介绍

    千次阅读 2015-10-14 09:58:09
    索引介绍  1.1 索引的创建语法:  CREATE UNIUQE | BITMAP INDEX .  ON .  ( | ASC | DESC,  | ASC | DESC,...)  TABLESPACE   STORAGE   LOGGING | NOLOGGING  
  • Oracle索引 详解

    万次阅读 多人点赞 2013-10-27 18:18:34
    索引介绍  1.1 索引的创建语法:  CREATE UNIUQE | BITMAP INDEX .  ON .  ( | ASC | DESC,  | ASC | DESC,...)  TABLESPACE   STORAGE   LOGGING | NOLOGGING  COMPUTE
  • Oracle数据库底层

    千次阅读 2017-08-08 06:51:40
    Oracle底层的架构对编写出高性能的SQL语句非常重要 实例系统全局内存局部区域(SGA)和一系列的后台进程组成(PGA) SGA的两个领域:共享池和数据库数据缓冲 SGA共享池: 地位: 关键部分之一; 作用:Oracle...
  • ORACLE 索引概述

    千次阅读 2013-08-23 14:04:47
    索引是数据库中一种可选的数据结构,她通常与表或簇相关。用户可以在表的一列或数列上建立索引,以提高在此表上执行 SQL 语句的性能。就像本文档的索引可以帮助读者快速定位所需信息一样,Oracle索引提供了更为...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 25,653
精华内容 10,261
关键字:

oracle索引底层数据结构

数据结构 订阅