精华内容
下载资源
问答
  • Oracle支持的全部索引MySQL支持的全部索引

    Oracle支持的全部索引

    MySQL支持的全部索引

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

    千次阅读 2020-01-31 18:33:58
    3. MyISAMInnoDB的索引实现 4. 联合索引 5. 什么情况下应不建或少建索引 6. MySql在建立索引优化时需要注意的问题 1. 索引概念 索引概念:索引是帮助MySQL高效获取数据的排好序的数据结构,更通俗的说数据库...

    目录

    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联合 索引(复合索引)的探讨

    展开全文
  • 一文读懂mysql索引底层原理

    千次阅读 2019-03-19 10:59:00
    数据库的底层索引是用B树B+树实现的,但是为什么使用的是它们,为什么不用红黑树? 红黑树等数据结构也可以用来实现索引,但是文件系统以及数据库系统普遍采用B-Tree/B+Tree作为索引结构.这是因为:索引本身也很大,...

      Mysql官方对索引的定义为:索引是帮助Mysql高效获取数据的数据结构,简单来说:索引就是数据结构.

     

    数据库的底层索引是用B树和B+树实现的,但是为什么使用的是它们,为什么不用红黑树?

        红黑树等数据结构也可以用来实现索引,但是文件系统以及数据库系统普遍采用B-Tree/B+Tree作为索引结构.这是因为:索引本身也很大,因此索引往往是以索引文件的形式存储在磁盘上.所以,索引查找的过程就会产生磁盘的I/O操作,相比于内存存取,I/O存取消耗要高几个数量级,所以索引的优劣最重要的指标就是在查找过程中的磁盘I/O存取次数.

     

    B-/+的性能分析:

        一般使用磁盘I/O次数评价索引结构的优劣.

        B树将一个节点的大小设为一个页,每次新建节点时,直接申请一个页的空间,这样就保证一个节点物理上也存储在一个页里,计算机存储分配是按页对齐的,就实现了一个node只需要磁盘访问一次,我们可以获得最大数量的数据.所以,B树的深度决定了要访问磁盘I/O的次数.

        而红黑树这种结构,h要大的多.由于逻辑上很近的节点物理上可能很远,无法利用其局部性.

    为什么B+树更适合外存索引?

        因为B+树的内节点去掉了data域,因此有更大的出度,而且它是根据索引关键字进行查找,不经过分支结点,而是沿着下一叶子的指针就可遍历所有的关键字.

     

    mysql简介:

    mysql是目前非常流行的开源关系型的数据库,不仅是免费得,可靠性高,速度也比较快,而且拥有灵活的插件式存储引擎.

        每个插件式的存储引擎都有各自的特点,能够根据具体的应用建立不同的存储引擎表.每一种存储引擎都使用不同的存储机制,索引技巧,锁定水平并且最终提供广泛的不同的功能和能力.

    注意:存储引擎是基于表的,而不是基于数据库的.

     

    MYSQL索引:

    MyISAM存储引擎不支持事务,表锁设计,支持全文索引.它的另一个与众不同的地方是它的缓冲池只缓冲索引文件,而不缓冲数据文件,数据文件的缓冲交由操作系统本身来完成.

    它的索引结构采用B+树作为索引结构,叶子节点的data域存放的是数据记录的地址.所以其数据文件和索引文件是分离的.

    搜索过程:

    按照B+搜索算法搜索算法搜索索引,如果指定的key存在,取出data的值,然后以data的值作为地址,读取相应的数据记录.

     

    这种索引方式也叫做"非聚集索引".

     

     

    InnoDB索引:

    InnoDB存储引擎支持事务,其设计主要面向在线事务处理的应用,其特点是行锁设计,支持外键,并支持类似Oracle的非锁定读,即默认读取操作不会产生锁.从mysql的5.5.8版本开始,InnoDB就是其默认的存储引擎.

     

    InnoDB索引方式与MyISAM的区别:

    InnoDB的数据文件本身就是索引文件.MyISAM数据文件和索引文件分离,但InnoDB的表数据文件本身就是按B+树的组织形成的索引结构,这颗树的叶节点data域保存了完整的数据记录.这个索引的key就是数据表的主键,因此InnoDB表数据文件本身就是主索引.

    其叶子节点包含了完整的数据记录,这种索引叫做"聚集索引".

     

     

     

     

     

     

     

     

     

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

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

     首先,在讨论数据结构之前,先了解一下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和mysql的区别(底层代码)

    千次阅读 2017-06-28 14:33:57
    一、并发性 并发性是oltp数据库最重要的特性,但并发涉及到...虽然InnoDB引擎的表可以用行级锁,但这个行级锁的机制依赖于表的索引,如果表没有索引,或者sql语句没有使用索引,那么仍然使用表级锁。 oracle:oracle使用
  • MySQL官方对于索引的定义为:索引是帮助MySQL高效获取数据的数据结构。即:索引是数据结构。 我们知道,数据库查询是数据库最主要的功能之一,我们都希望查询数据的速度尽可能的快,因此数据库系统的设计者会从...
  • MySql索引底层实现

    2017-09-17 20:58:25
    MySQL官方对于索引的定义为:索引是帮助MySQL高效获取数据的数据结构。即可以理解为:索引是数据结构。   我们知道,数据库查询是数据库最主要的功能之一,我们都希望查询数据的速度尽可能的快,因此数据库...
  • 今天我们来说一下我们的mysql,个人认为现在的mysql能做到很好的优化处理,不比收费的oracle差,而且mysql确实好用。 当我们查询慢的时候,我会做一系列的优化处理,例如分库分表,加索引。那么我们底层索引到底...
  • mysql体系结构 如下图所示 正确的创建合适的索引是提升数据库查询性能的基础 二索引是什么? 索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构 为什么要使用索引? 1.索引能极大的...
  • Oraclemysql产品性能优化总结 李万鸿2017-6-23 数据库是产品的一大性能瓶颈,oraclemysql等数据库的查询速度直接影响...
  • 搞定MySQL索引

    千次阅读 2021-02-15 17:45:22
    1. 索引(index) 1.1 索引 官方定义: 一种帮助MySQL提高查询效率的数据结构; 这里就得到,索引其实就是一种数据结构,选用的为B+树 1.2 优缺点 优点:加快数据查询速度; 缺点: 1、维护索引需要耗费数据库资源; ...
  • Oraclemysql的区别

    2021-03-06 22:30:29
    Oraclemysql的区别 一、外部区别 原文地址 https://www.sohu.com/a/280090529_120047065 1、本质的区别 Oracle数据库是一个对象关系数据库管理系统(ORDBMS)。它通常被称为Oracle RDBMS或简称为Oracle,是一个...
  • Oracle索引原理

    千次阅读 2017-10-23 17:17:18
    mysql索引分为聚集索引,非聚集索引。其中聚集索引只能有一个。 oracle中索引的主要分为根,茎,叶子三部分。索引列值都是存放在叶子节点上,茎只是存放了叶子节点的相关信息。 oracle中的索引反应的是逻辑结构,...
  • MySQL索引学习整理

    千次阅读 2015-07-16 13:33:38
    MySQL索引创建及相应知识学习整理
  • MySQL 索引及优化实战

    千次阅读 2018-07-07 23:30:53
    MySQL 索引类型从物理存储角度上,索引可以分为聚集索引和非聚集索引。1. 聚集索引(Clustered Index)聚集索引决定数据在磁盘上的物理排序,一个表只能有一个聚集索引。2. 非聚集索引(Non-...
  • 数据库索引底层实现

    千次阅读 2016-04-14 12:57:28
    由浅入深理解数据库中索引底层实现 2014年11月22日 ⁄ 综合 ⁄ 共 7081字 ⁄ 字号 小 中 大 ⁄ 评论关闭 ...Oracle和DB2数据库索引的实现基本上也是大同小异的。文章写得很通俗易懂,就转在这了
  • Oracle索引游标学习

    2020-08-02 23:04:24
    了解索引的分类以及基本功能,学会分析复杂SQL的执行过程和索引使用情况,分析查询的表是走了全表扫描还是索引 1索引 **背景:**在关系型数据库如MySQLOracle中,由于数据库表数据是直接存放在硬盘中,受限于硬盘...
  • 背景: 为了提高数据库效率,建索引是家常便饭;那么...
  • MySQL索引简介

    千次阅读 2015-07-26 19:45:47
    为了避免混乱,本文将只关注于B-Tree索引,因为这是平常使用MySQL时主要打交道的索引,至于哈希索引和全文索引本文暂不讨论。 文章主要内容分为三个部分。 第一部分主要从数据结构及算法理论层面讨论MySQL数据库索引...
  • 数据库索引底层原理

    2019-07-23 15:37:19
    二叉树到平衡二叉树,再到 B- 树,最后到 B+ 树来一步一步了解数据库索引底层的原理! 二叉树(Binary Search Trees) 二叉树是每个结点最多有两个子树的树结构。通常子树被称作“左子树”(Left Subtree)“右...
  • SQL Server和Oracle数据库索引介绍

    千次阅读 2016-05-25 16:27:38
    SQL Server和Oracle数据库索引介绍 1 SQL Server中的索引   索引是与表或视图关联的磁盘上结构,可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。这些键存储在一个结构(B 树)中...
  • MYSQLORACLE程序迁移的注意事项 有很多应用项目, 刚起步的时候用MYSQL数据库基本上能实现各种功能需求,随着应用用户的增多,数据量的增加,MYSQL渐渐地出现不堪重负的情况:连接很慢甚至宕机, 于是就有把...
  • mysql数据库底层原理

    千次阅读 2020-05-18 21:47:19
    一、MySQL 的基本定义: ...关系数据库管理系统,由瑞典MySQL AB公司开发,目前属于Oracle公司。 是建立在关系模型基础上的数据库,借助于集合代数等数学概念方法来处理数据库中的数据。 特点: 数据以表格
  • ORACLE VS MYSQL

    千次阅读 2014-01-20 15:29:04
    Oracle成立于1977年,早期的理论基础,反而来自于一篇IBM的论文《A Relational Model of Data for Large Shared Data Banks》【1】。作者CODD选取了关系代数的五种运算,并基于运算,架构了一种新型的数据存储模型。...
  • mysql和oracle的区别

    2019-05-12 18:32:50
    MySQL和Oracle都是流行的关系数据库管理系统(RDBMS),在世界各地广泛使用;大多数数据库以类似的方式工作,但MySQL和Oracle的这里那里总是存在一些差异的。本篇文章就给大家比较Oracle和MySQL,介绍Oracle和...
  • oracle B树索引

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

    2019-04-24 14:13:09
    前些天,又回顾了一下数据库相关的知识,也看了一些数据库中索引的一些文章,发现其实索引底层的一些东西还是有深度的,看的我想睡觉,今天先写一些索引的稍微基础一点的知识,索引复杂的东西,我暂时还没弄得很明白...
  • Mysql底层数据结构 B+Tree (B-Tree变种) 非叶子节点不存data,只存储索引(冗余),可以放更多的索引。 非叶子节点包含所有索引字段 叶子节点用指针连接,提高区间访问的性能。 B-Tree 特点 叶结点具有相同的...
  • mysql索引调优

    千次阅读 2021-03-12 14:34:09
    mysql高级 数据库的基本结构理解: 数据库名词 描述 database(数据库) 用于保存有类型,有分类的数据,一般是一个文件,或是一组文件 table(数据库表) 某种数据类型的结构化清单,数据库表存在数据库...
  • 数据库索引底层原理及优化

    千次阅读 多人点赞 2018-09-11 14:11:18
    一、摘要 本文以MySQL数据库为研究对象,讨论与数据库...为了避免混乱,本文将只关注于BTree索引,因为这是平常使用MySQL时主要打交道的索引,至于哈希索引和全文索引本文暂不讨论。 二、常见的查询算法及数据结构...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 19,722
精华内容 7,888
热门标签
关键字:

oracle和mysql索引底层

mysql 订阅