精华内容
下载资源
问答
  • 数据库索引设计与优化》提供了一种简单、高效、通用的关系型数据库索引设计方法。作者通过系统的讲解及大量的案例清晰地阐释了关系型数据库的访问路径选择原理,以及表和索引的扫描方式,详尽地讲解了如何快速地...
  • 数据库索引设计与优化》提供了一种简单、高效、通用的关系型数据库索引设计方法。作者通过系统的讲解及大量的案例清晰地阐释了关系型数据库的访问路径选择原理,以及表和索引的扫描方式,详尽地讲解了如何快速地...
  • 数据库索引底层原理

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

    转载:

    https://mp.weixin.qq.com/s/QzJUWqa51uAQJcXPAqoC9g

    二叉树到平衡二叉树,再到 B- 树,最后到 B+ 树来一步一步了解数据库索引底层的原理!

    二叉树(Binary Search Trees)

    二叉树是每个结点最多有两个子树的树结构。通常子树被称作“左子树”(Left Subtree)和“右子树”(Right Subtree)。二叉树常被用于实现二叉查找树和二叉堆。

    二叉树有如下特性:

    • 每个结点都包含一个元素以及 n 个子树,这里 0≤n≤2。 

    • 左子树和右子树是有顺序的,次序不能任意颠倒。左子树的值要小于父结点,右子树的值要大于父结点。

    光看概念有点枯燥,假设我们现在有这样一组数[35 27 48 12 29 38 55],顺序的插入到一个数的结构中,步骤如下 :

    好了,这就是一棵二叉树啦!我们能看到,经过一系列的插入操作之后,原本无序的一组数已经变成一个有序的结构了,并且这个树满足了上面提到的两个二叉树的特性!

     

    但是如果同样是上面那一组数,我们自己升序排列后再插入,也就是说按照[12 27 29 35 38 48 55]的顺序插入,会怎么样呢?

    由于是升序插入,新插入的数据总是比已存在的结点数据都要大,所以每次都会往结点的右边插入,最终导致这棵树严重偏科!

    上图就是最坏的情况,也就是一棵树退化为一个线性链表了,这样查找效率自然就低了,完全没有发挥树的优势了呢! 

    为了较大发挥二叉树的查找效率,让二叉树不再偏科,保持各科平衡,所以有了平衡二叉树!

    平衡二叉树 (AVL Trees)

    平衡二叉树是一种特殊的二叉树,所以他也满足前面说到的二叉树的两个特性,同时还有一个特性:它的左右两个子树的高度差的绝对值不超过 1,并且左右两个子树都是一棵平衡二叉树。

    大家也看到了前面[35 27 48 12 29 38 55]插入完成后的图,其实就已经是一棵平衡二叉树啦。

    那如果按照[12 27 29 35 38 48 55]的顺序插入一棵平衡二叉树,会怎么样呢?

    我们看看插入以及平衡的过程:

    这棵树始终满足平衡二叉树的几个特性而保持平衡!这样我们的树也不会退化为线性链表了!

    我们需要查找一个数的时候就能沿着树根一直往下找,这样的查找效率和二分法查找是一样的呢!

    一棵平衡二叉树能容纳多少的结点呢?这跟树的高度是有关系的,假设树的高度为 h,那每一层最多容纳的结点数量为 2^(n-1),整棵树最多容纳节点数为 2^0+2^1+2^2+...+2^(h-1)。

    这样计算,100w 数据树的高度大概在 20 左右,也就是说从有着 100w 条数据的平衡二叉树中找一个数据,最坏的情况下需要 20 次查找。

    如果是内存操作,效率也是很高的!但是我们数据库中的数据基本都是放在磁盘中的,每读取一个二叉树的结点就是一次磁盘 IO,这样我们找一条数据如果要经过 20 次磁盘的 IO?

    那性能就成了一个很大的问题了!那我们是不是可以把这棵树压缩一下,让每一层能够容纳更多的节点呢?虽然我矮,但是我胖啊...

     

    B-Tree

    这颗矮胖的树就是 B-Tree,注意中间是杠精的杠而不是减,所以也不要读成 B 减 Tree 了~

    那 B-Tree 有哪些特性呢?一棵 m 阶的 B-Tree 有如下特性:

    • 每个结点最多 m 个子结点。 

    • 除了根结点和叶子结点外,每个结点最少有 m/2(向上取整)个子结点。 

    • 如果根结点不是叶子结点,那根结点至少包含两个子结点。 

    • 所有的叶子结点都位于同一层。 

    • 每个结点都包含 k 个元素(关键字),这里 m/2≤k。

    • 每个节点中的元素(关键字)从小到大排列。 

    • 每个元素(关键字)字左结点的值,都小于或等于该元素(关键字)。右结点的值都大于或等于该元素(关键字)。

    是不是感觉跟丈母娘张口问你要彩礼一样,列一堆的条件,而且每一条都让你很懵逼!

    下面我们以一个[0,1,2,3,4,5,6,7]的数组插入一棵 3 阶的 B-Tree 为例,将所有的条件都串起来,你就明白了!

    那么,你是否对 B-Tree 的几点特性都清晰了呢?在二叉树中,每个结点只有一个元素。

    但是在 B-Tree 中,每个结点都可能包含多个元素,并且非叶子结点在元素的左右都有指向子结点的指针。

    如果需要查找一个元素,那流程是怎么样的呢?我们看下图,如果我们要在下面的 B-Tree 中找到关键字 24,那流程如下:

    从这个流程我们能看出,B-Tree 的查询效率好像也并不比平衡二叉树高。但是查询所经过的结点数量要少很多,也就意味着要少很多次的磁盘 IO,这对性能的提升是很大的。

    从前面对 B-Tree 操作的图,我们能看出来,元素就是类似 1、2、3 这样的数值。

    但是数据库的数据都是一条条的数据,如果某个数据库以 B-Tree 的数据结构存储数据,那数据怎么存放的呢?

    我们看下一张图:

    普通的 B-Tree 的结点中,元素就是一个个的数字。但是上图中,我们把元素部分拆分成了 key-data 的形式,Key 就是数据的主键,Data 就是具体的数据。

    这样我们在找一条数的时候,就沿着根结点往下找就 OK 了,效率是比较高的。

    B+Tree

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

    B+Tree 与 B-Tree 的结构很像,但是也有几个自己的特性:

    • 所有的非叶子节点只存储关键字信息。 

    • 所有卫星数据(具体数据)都存在叶子结点中。 

    • 所有的叶子结点中包含了全部元素的信息。 

    • 所有叶子节点之间都有一个链指针。

    如果上面 B-Tree 的图变成 B+Tree,那应该如下: 

    大家仔细对比于 B-Tree 的图能发现什么不同? 

    • 非叶子结点上已经只有 Key 信息了,满足上面第 1 点特性! 

    • 所有叶子结点下面都有一个 Data 区域,满足上面第 2 点特性! 

    • 非叶子结点的数据在叶子结点上都能找到,如根结点的元素 4、8 在最底层的叶子结点上也能找到,满足上面第 3 点特性! 

    • 注意图中叶子结点之间的箭头,满足上面第 4 点特性!

     

    B-Tree or B+Tree?

    在讲这两种数据结构在数据库中的选择之前,我们还需要了解的一个知识点是操作系统从磁盘读取数据到内存是以磁盘块(Block)为基本单位的,位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么。

    即使只需要一个字节,磁盘也会从这个位置开始,顺序向后读取一定长度的数据放入内存。

    这样做的理论依据是计算机科学中著名的局部性原理:当一个数据被用到时,其附近的数据也通常会马上被使用。 

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

    B-Tree 和 B+Tree 该如何选择呢?都有哪些优劣呢?

    ①B-Tree 因为非叶子结点也保存具体数据,所以在查找某个关键字的时候找到即可返回。

    而 B+Tree 所有的数据都在叶子结点,每次查找都得到叶子结点。所以在同样高度的 B-Tree 和 B+Tree 中,B-Tree 查找某个关键字的效率更高。 

    ②由于 B+Tree 所有的数据都在叶子结点,并且结点之间有指针连接,在找大于某个关键字或者小于某个关键字的数据的时候,B+Tree 只需要找到该关键字然后沿着链表遍历就可以了,而 B-Tree 还需要遍历该关键字结点的根结点去搜索。 

    ③由于 B-Tree 的每个结点(这里的结点可以理解为一个数据页)都存储主键+实际数据,而 B+Tree 非叶子结点只存储关键字信息,而每个页的大小是有限的,所以同一页能存储的 B-Tree 的数据会比 B+Tree 存储的更少。

    这样同样总量的数据,B-Tree 的深度会更大,增大查询时的磁盘 I/O 次数,进而影响查询效率。 

    鉴于以上的比较,所以在常用的关系型数据库中,都是选择 B+Tree 的数据结构来存储数据!

    下面我们以 MySQL 的 InnoDB 存储引擎为例讲解,其他类似 SQL Server、Oracle 的原理!

     

    InnoDB 引擎数据存储

    在 InnoDB 存储引擎中,也有页的概念,默认每个页的大小为 16K,也就是每次读取数据时都是读取 4*4K 的大小!

    假设我们现在有一个用户表,我们往里面写数据:

    这里需要注意的一点是,在某个页内插入新行时,为了减少数据的移动,通常是插入到当前行的后面或者是已删除行留下来的空间,所以在某一个页内的数据并不是完全有序的(后面页结构部分有细讲)。

    但是为了数据访问顺序性,在每个记录中都有一个指向下一条记录的指针,以此构成了一条单向有序链表,不过在这里为了方便演示我是按顺序排列的!

    由于数据还比较少,一个页就能容下,所以只有一个根结点,主键和数据也都是保存在根结点(左边的数字代表主键,右边名字、性别代表具体的数据)。

    假设我们写入 10 条数据之后,Page1 满了,再写入新的数据会怎么存放呢?

    我们继续看下图:

    有个叫“秦寿生”的朋友来了,但是 Page1 已经放不下数据了,这时候就需要进行页分裂,产生一个新的 Page。

    在 InnoDB 中的流程是怎么样的呢?

    • 产生新的 Page2,然后将 Page1 的内容复制到 Page2。 

    • 产生新的 Page3,“秦寿生”的数据放入 Page3。 

    • 原来的 Page1 依然作为根结点,但是变成了一个不存放数据只存放索引的页,并且有两个子结点 Page2、Page3。

     

    这里有两个问题需要注意的是:

    ①为什么要复制 Page1 为 Page2 而不是创建一个新的页作为根结点,这样就少了一步复制的开销了?

    如果是重新创建根结点,那根结点存储的物理地址可能经常会变,不利于查找。

    并且在 InnoDB 中根结点是会预读到内存中的,所以结点的物理地址固定会比较好!

    ②原来 Page1 有 10 条数据,在插入第 11 条数据的时候进行裂变,根据前面对 B-Tree、B+Tree 特性的了解,那这至少是一棵 11 阶的树,裂变之后每个结点的元素至少为 11/2=5 个。

    那是不是应该页裂变之后主键 1-5 的数据还是在原来的页,主键 6-11 的数据会放到新的页,根结点存放主键 6? 

    如果是这样的话,新的页空间利用率只有 50%,并且会导致更为频繁的页分裂。

    所以 InnoDB 对这一点做了优化,新的数据放入新创建的页,不移动原有页面的任何记录。

    随着数据的不断写入,这棵树也逐渐枝繁叶茂,如下图:

    每次新增数据,都是将一个页写满,然后新创建一个页继续写,这里其实是有个隐含条件的,那就是主键自增!

    主键自增写入时新插入的数据不会影响到原有页,插入效率高!且页的利用率高!

    但是如果主键是无序的或者随机的,那每次的插入可能会导致原有页频繁的分裂,影响插入效率!降低页的利用率!这也是为什么在 InnoDB 中建议设置主键自增的原因!

    这棵树的非叶子结点上存的都是主键,那如果一个表没有主键会怎么样?在 InnoDB 中,如果一个表没有主键,那默认会找建了唯一索引的列,如果也没有,则会生成一个隐形的字段作为主键!

    有数据插入那就有删除,如果这个用户表频繁的插入和删除,那会导致数据页产生碎片,页的空间利用率低,还会导致树变的“虚高”,降低查询效率!这可以通过索引重建来消除碎片提高查询效率!

     

    InnoDB 引擎数据查找

    数据插入了怎么查找呢?

    • 找到数据所在的页。这个查找过程就跟前面说到的 B+Tree 的搜索过程是一样的,从根结点开始查找一直到叶子结点。 

    • 在页内找具体的数据。读取第 1 步找到的叶子结点数据到内存中,然后通过分块查找的方法找到具体的数据。

    这跟我们在新华字典中找某个汉字是一样的,先通过字典的索引定位到该汉字拼音所在的页,然后到指定的页找到具体的汉字。

    InnoDB 中定位到页后用了哪种策略快速查找某个主键呢?这我们就需要从页结构开始了解。

    左边蓝色区域称为 Page Directory,这块区域由多个 Slot 组成,是一个稀疏索引结构,即一个槽中可能属于多个记录,最少属于 4 条记录,最多属于 8 条记录。

    槽内的数据是有序存放的,所以当我们寻找一条数据的时候可以先在槽中通过二分法查找到一个大致的位置。

    右边区域为数据区域,每一个数据页中都包含多条行数据。注意看图中最上面和最下面的两条特殊的行记录 Infimum 和 Supremum,这是两个虚拟的行记录。

    在没有其他用户数据的时候 Infimum 的下一条记录的指针指向 Supremum。

    当有用户数据的时候,Infimum 的下一条记录的指针指向当前页中最小的用户记录,当前页中最大的用户记录的下一条记录的指针指向 Supremum,至此整个页内的所有行记录形成一个单向链表。

    行记录被 Page Directory 逻辑的分成了多个块,块与块之间是有序的,也就是说“4”这个槽指向的数据块内最大的行记录的主键都要比“8”这个槽指向的数据块内最小的行记录的主键要小。但是块内部的行记录不一定有序。

    每个行记录的都有一个 n_owned 的区域(图中粉红色区域),n_owned 标识这个块有多少条数据。

    伪记录 Infimum 的 n_owned 值总是 1,记录 Supremum 的 n_owned 的取值范围为[1,8],其他用户记录 n_owned 的取值范围[4,8]。

    并且只有每个块中最大的那条记录的 n_owned 才会有值,其他的用户记录的 n_owned 为 0。

    所以当我们要找主键为 6 的记录时,先通过二分法在稀疏索引中找到对应的槽,也就是 Page Directory 中“8”这个槽。

    “8”这个槽指向的是该数据块中最大的记录,而数据是单向链表结构,所以无法逆向查找。

    所以需要找到上一个槽即“4”这个槽,然后通过“4”这个槽中最大的用户记录的指针沿着链表顺序查找到目标记录。

     

    聚集索引&非聚集索引

    前面关于数据存储的都是演示的聚集索引的实现,如果上面的用户表需要以“用户名字”建立一个非聚集索引,是怎么实现的呢?

    我们看下图:

    非聚集索引的存储结构与前面是一样的,不同的是在叶子结点的数据部分存的不再是具体的数据,而是数据的聚集索引的 Key。

    所以通过非聚集索引查找的过程是先找到该索引 Key 对应的聚集索引的 Key,然后再拿聚集索引的 Key 到主键索引树上查找对应的数据,这个过程称为回表!

    PS:图中的这些名字均来源于网络,希望没有误伤正在看这篇文章的你~^_^

     

    InnoDB 与 MyISAM 引擎对比

    上面包括存储和搜索都是拿的 InnoDB 引擎为例,那 MyISAM 与 InnoDB 在存储上有啥不同呢?憋缩话,看图:

    上图为 MyISAM 主键索引的存储结构,我们能看到的不同是:

    • 主键索引树的叶子结点的数据区域没有存放实际的数据,存放的是数据记录的地址。 

    • 数据的存储不是按主键顺序存放的,是按写入的顺序存放。

    也就是说 InnoDB 引擎数据在物理上是按主键顺序存放,而 MyISAM 引擎数据在物理上按插入的顺序存放。

    并且 MyISAM 的叶子结点不存放数据,所以非聚集索引的存储结构与聚集索引类似,在使用非聚集索引查找数据的时候通过非聚集索引树就能直接找到数据的地址了,不需要回表,这比 InnoDB 的搜索效率会更高呢!

     

    索引优化建议

    大家经常会在很多的文章或书中能看到一些索引的使用建议,比如说:

    • like 的模糊查询以 % 开头,会导致索引失效。 

    • 一个表建的索引尽量不要超过 5 个。 

    • 尽量使用覆盖索引。 

    • 尽量不要在重复数据多的列上建索引。 

    • ......

     

    很多这里就不一一列举了!那看完这篇文章,我们能否带着疑问去分析一下为什么要有这些建议?

    为什么 like 的模糊查询以 % 开头,会导致索引失效?为什么一个表建的索引尽量不要超过 5 个?

    为什么?为什么??为什么???相信看到这里的你再加上自己的一些思考应该有答案了吧?

     

     

    展开全文
  • 了解数据库索引及其原理

    万次阅读 多人点赞 2018-06-25 16:04:24
    索引这个词相信对于一个开发猿来说,就好比看到我们的代码一样低头不见抬头见,在一些日常优化我们查询效率的方案中,不光考虑优化我们的sql语句,另外就是使用索引。使用索引很简单,只要能写创建表的语句,就肯定...

    索引这个词相信对于一个开发猿来说,就好比看到我们的代码一样低头不见抬头见,在一些日常优化我们查询效率的方案中,不光考虑优化我们的sql语句,另外就是使用索引。使用索引很简单,只要能写创建表的语句,就肯定能写创建索引的语句,要知道这个世界上是不存在不会创建表的服务器端程序员的。然而, 会使用索引是一回事, 而深入理解索引原理又能恰到好处使用索引又是另一回事,这完全是两个天差地别的境界(我自己也还没有达到这层境界)。很大一部份程序员对索引的了解仅限于到“加索引能使查询变快”这个概念为止。

    下面就参考网上的一些文章,总结了一下对索引及其原理的理解。首先提几个问题:

    1.数据表为什么会使用主键?

    2.为什么使用索引后查询效率会变快?

    3.使用索引后会使插入、修改、删除变慢?

    4.什么时候需要在两个字段上加索引?

    这些问题他们可能不一定能说出答案。知道这些问题的答案有什么好处呢?如果开发的应用使用的数据库表中只有1万条数据,那么了解与不了解真的没有差别, 然而, 如果开发的应用有几百上千万甚至亿级别的数据,那么不深入了解索引的原理, 写出来程序就根本跑不动,就好比如果给货车装个轿车的引擎,这货车还能拉的动货吗?想要理解索引原理必须清楚一种数据结构「平衡树」(非二叉),也就是b tree或者 b+ tree,重要的事情说三遍:“平衡树,平衡树,平衡树”。当然, 有的数据库也使用哈希桶作用索引的数据结构 , 然而, 主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。

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

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

    答2: 假如我们执行一个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), 结果就是查找次数,这里的结果从亿降到了个位数。因此,利用索引会使数据库查询有惊人的性能提升。

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

    答4:讲完聚集索引 , 接下来聊一下非聚集索引, 也就是我们平时经常提起和使用的常规索引。其中涉及到了我们之前问题中的在索引中添加两个字段的多字段索引查询。

    非聚集索引和聚集索引一样, 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段, 假如给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的值返回即可。 通过这种覆盖索引直接查找的方式, 可以省略不使用覆盖索引查找的后面两个步骤, 大大的提高了查询性能,如下图

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

    本文主要参考:https://www.cnblogs.com/aspwebchh/p/6652855.html。在其基础上根据自己的理解总结了一下,如果哪里理解的不对,还望各位大佬指出来,互相学习。

    创建简单索引语句,可以参考一下w3school网站上的:点击打开链接



    展开全文
  • 本书提供了一种简单、高效、通用的关系型数据库索引设计方法。作者通过系统的讲解及大量的案例清晰地阐释了关系型数据库的访问路径选择原理,以及表和索引的扫描方式,详尽地讲解了如何快速地估算SQL运行的CPU时间及...
  • 数据库索引的体会

    2020-12-15 09:02:10
    数据库索引好比是一本书前面的目录,能加快数据库的查询速度。索引分为聚簇索引和非聚簇索引两种,聚簇索引 是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度,而非聚簇...
    1. 数据库索引好比是一本书前面的目录,能加快数据库的查询速度。索引分为聚簇索引和非聚簇索引两种,聚簇索引 是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快。 来自百度百科的讲解。
    2. 索引就好比目录,对于业务中经常需要作为筛选条件的字段,我们经常设置为索引,要知道索引采用的是树的遍历,远远比简单的循环快得多,但是其在增删改等操作中会多了索引维护的代价,会比普通的效率慢。
    3. 我们在数据库中创建的索引为聚集索引,聚集索引会根据当前索引列的逻辑顺序,使其在物理空间上有着一样的存储。就好比字典,将所有相同读音放在一起。而我们普通方式就好比杂乱的图书馆一样。
    展开全文
  • 通俗讲解数据库索引

    2020-05-07 14:05:31
    前言 提起索引我想你应该不陌生,当我们查阅一本大部头的时候我们应该如何...相同,数据库为了能快速的寻找到指定的数据必须要建立索引。对于少量的数据,没有合适的索引影响不是很大,但是,当随着数据量的增加...

    更多知识,请移步我的小破站:http://hellofriend.top

    前言

    提起索引我想你应该不陌生,当我们查阅一本大部头的时候我们应该如何快速的找到想要的内容呢?很简单,先找目录,通过目录我们就可以了解到我们要找的内容在书中的什么地方,而这个目录就担任着索引的功能。相同,数据库为了能快速的寻找到指定的数据必须要建立索引。对于少量的数据,没有合适的索引影响不是很大,但是,当随着数据量的增加,性能会急剧下降。

    几种常见的索引数据模型

    有序表

    最简单的方式就是有序线性表的存储方式,而这种方式包含了两种存储类型,分别是数组和链表有序数组(Array list)在等值查询和范围查询的情景下性能非常优秀,但是当我们需要在这之中插入数据的时候就需要将后方的数据全部进行移动,成本非常高,所以一般只适用于静态存储引擎。而另外一种有序链表(Linked list),在增加删除插入的场景中性能表现优秀,但是在查询的场景中就不太合适,但是我们知道对有序表的查询一般采用二分搜索,而这个二分搜索是通过分区的方式来提高查询效率,如果我们为有序链表建立分区索引,那有序链表的查询效率就能达到O(logN),这也是Redis的有序集实现方式跳表(Skip list),关于跳表的内容这里就不说了,有兴趣的请自行查阅。

    哈希表

    除了有序集的方式还能通过无序集的方式来作为索引,哈希表(Hash table),哈希表是通过计算key的散列值从而定位value在数组中的位置来进行查询的,不可避免地,多个key值经过哈希函数的换算,会出现同一个值的情况。处理这种情况的一种方法是,拉出一个链表。而哈希表由于是无序的所有只适用于等值查询的场景, 比如Memcached及其他一些NoSQL引擎。

    除了集合的方式还有二叉搜索树这种方式,二叉树是搜索效率最高的,但是实际上大多数的数据库存储却并不使用二叉树。其原因是,索引不止存在内存中,还要写到磁盘上。而磁盘的读取时间并不快,当树高20的时候数据库就有可能读取20个数据块,所以为了尽可能的减小磁盘的读取次数,则不应该使用二叉树,转而使用N叉树,N叉树由于在读写上的性能优点,以及适配磁盘的访问模式,已经被广泛应用在数据库引擎中了。如MySQL的InnoDB和MyISAM。

    数据库底层存储的核心就是基于这些数据模型的。每碰到一个新数据库,我们需要先关注它的数据模型,这样才能从理论上分析出这个数据库的适用场景。

    InnoDB的索引模型

    在MySQL中,索引是在存储引擎层实现的,所以有多种不同的索引,即使索引使用同一类型的的索引,工作方式也可能会不一样。而在MySQL中主要是使用InnoDB,所以这里就来分析InnoDB的索引模型

    在InnoDB中,索引使用的是B+树作为索引结构,根据叶子节点的内容,索引类型分为主键索引聚集索引 Clustered index)和非主键索引辅助索引 Secondary index)。主键索引的叶节点中存储的是表中的行数据,非主键索引中的叶节点存储的是主键的值而不是地址。当我们使用主键索引对表中的数据进行检索时,就可以直接得到行数据,而不用进行二次检索。而使用非主键索引进行检索时,我们得到的是主键的值,当我们查询的列不只有主键的时候,就需要通过搜索到的主键值再到主键索引中搜索一次,这种过程被称为回表

    通过上面的说明我们知道非主键索引存储的是主键的值,所以当主键的值占用的空间越小,非主键索引就能越小,需要读取的数据块就有可能越小,所以在创建数据表的时候应该选择一个合适的字段作为主键,或者使用自增主键。

    InnoDB索引策略

    覆盖索引

    举个例子说明比较容易理解,假设有一个people表,表中有id和name两列都是索引,id为主键,当我们执行以下查询SELECT id FROM people WHERE name=otstar;的时候,InnoDB会去name索引中寻找主键即id,而我们要查询的值id已经在name的索引中存储了,所以不需要回表,由于在非主键索引中的查询覆盖(满足了)查询请求,所以称为覆盖索引,由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段

    联合索引

    联合索引就是创建一个支持两种或以上字段比较的索引,比如name,age的联合索引,当我们查询SELECT id,name,age FROM people WHERE name=otstar AND age=18;的时候就只需要再name,age中直接查询,而不再需要回表。从而提高查询效率和减少开销。

    最左前缀原则

    从名称中就可以看出,最左优先,如当创建了一个name,age的联合索引,就相当于创建了name的单索引和name,age的多索引,这时我们就没必要再去创建name的单独索引,但是如果需要age也是索引则需要另外创建一个age的单索引

    那有这种特性的出现有要如何排序联合索引呢?当我们将一个索引放置到左边的时候可以减少创建一个索引的时候我们就应该优先考虑这种情况,还有一种是空间,name字段一般是比age字段大的,如果我们创建age,name的联合索引,当我们需要name的单独索引的时候,就需要创建name的单独索引,而创建一个name的索引对存储的开销比age大。简单的比喻下,如name的索引占用2空间,age占用1空间,则name,age+age的索引占用4空间,而age,name+name则需要占用5空间。

    索引下推

    假设有一个包含id,name,age,nikename的people表,id为主键索引,name,age为联合索引,当我们执行SELECT * FROM people WHERE name LIKE 'o%' AND age=18 AND nikename=otstar;,依照最左前缀原则,这句查询只能使用name单索引查询,而不能使用name,age的双索引查询,因为还有一个nikename字段需要匹配,所以当查询到符合name的查询的时候,在MySQL 5.6之前就只能拿id的值去回表看看其他字段是否匹配,而5.6之后引入了索引下推,也就是把age字段也加入索引,当name匹配后就可以一并对age进行判断,而不用连age字段都要回表比较,这时候就可以减少不少的回表次数。

    结语

    在满足语句需求的情况下, 尽量少地访问资源是提高数据库性能的一大关键,理解索引的原理,我们才能不浪费性能或资源,提高数据库的效率,从而提高程序的运行效率。

    展开全文
  • Mysql、Oracle、PostgreSql数据库索引失效场景详细讲解前言 前言 Mysql、Oracle、PostgreSql数据库索引失效场景详细讲解。废话不多说直接贴: 1、任何计算、函数、类型转换 2、!=、<> 3、IS NULL或者IS NOT ...
  • 数据库索引设计与优化》提供了一种简单、高效、通用的关系型数据库索引设计方法。 作者通过系统的讲解及大量的案例清晰地阐释了关系型数据库的访问路径选择原理, 以及表和索引的扫描方式,详尽地讲解了如何快速地...
  • 本书提供了一种简单、高效、通用的关系型数据库索引设计方法。作者通过系统的讲解及大量的案例清晰地阐释了关系型数据库的访问路径选择原理,以及表和索引的扫描方式,详尽地讲解了如何快速地估算SQL运行的CPU时间及...
  • 怎样建立最佳索引? 1、明确地创建索引 create index index_name on table_name(field_name) tablespace tablespace_name pctfree 5 initrans 2 maxtrans 255 storage ( minextents 1 maxextents 16382 pctincrease ...
  • 本书提供了一种简单、高效、通用的关系型数据库索引设计方法。作者通过系统的讲解及大量的案例清晰地阐释了关系型数据库的访问路径选择原理,以及表和索引的扫描方式,详尽地讲解了如何快速地估算SQL运行的CPU时间及...
  • Oracle数据库索引底层实现原理笔记

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

    万次阅读 多人点赞 2018-03-06 13:17:43
    转载深入浅出数据库索引原理,哪些字段适合建立索引 问题 为什么要给表加上主键? 为什么加索引后会使查询变快? 为什么加索引后会使写入、修改、删除变慢? 什么情况下要同时在两个字段上建索引? 这些问题...
  • 数据库 索引的优点和缺点

    千次阅读 2019-04-12 20:29:42
    第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。 第二,可以大大加快 数据的检索速度,这也是创建索引的最主要的原因。 第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面...
  • 数据库索引技术

    2012-11-21 01:23:47
    讲解数据库索引方面很不错的资料,看看吧!
  • 数据库--索引详解

    千次阅读 多人点赞 2018-08-08 22:34:52
    数据库索引是用于提高数据库表的数据访问速度的。想要理解索引原理必须清楚一种数据结构「平衡树」(非二叉),也就是b tree或者 b+ tree,重要的事情说三遍:“平衡树,平衡树,平衡树”。当然, 有的数据库也使用...
  • 数据库索引原理实例

    千次阅读 2021-01-17 10:52:00
    --未使用索引,不存在to_char(d1,'yyyymmdd’)的函数索引 解决办法:改为d1 between to_date('20200102 00:00:00','yyyymmdd hh24:mi:ss') and to_date('20200102 23:59:59','yyyymmdd hh24:mi:ss') 也可以执行create...
  • Oracle加数据库索引原理

    千次阅读 2019-03-06 16:48:19
    使用索引很简单,只要能写创建表的语句,就肯定能写创建索引的语句,要知道这个世界上是不存在不会创建表的服务器端程序员的。然而, 会使用索引是一回事, 而深入理解索引原理又能恰到好处使用索引又是另一回事,这...
  • 数据库索引有哪几种

    千次阅读 2021-01-30 05:02:22
    种类:1、按照索引列值的唯一性,索引可分为唯一索引和非唯一索引;非唯一索引:create index 索引名 on 表名(列名) tablespace表空间名;唯一索引:建立主键或者唯一约束时会自动在对应的列上建立唯一索引;2、索引...
  • 最近使用到Oracle数据库索引比较多,所以就想好好研究一下索引到底是什么。毕竟作为一个Application Developer,而不是DBA,所以这篇文字也是很通俗,特别浅显的描述了一下索引相关的概念。 为什么需要索引?数据...
  • mysql引擎及索引讲解

    2019-04-26 10:20:52
    数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问...这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象(如索引、视图和存储过程)。常用的两种引擎是MYISAM和INNODB。
  • 深入浅出数据库索引

    千次阅读 2020-02-07 10:00:54
    Mysql索引3.1 数据结构 - B-Tree和B+TreeB-Tree:B+Tree:带有顺序访问指针的B+Tree索引的物理存储与B-Tree相比,B+Tree有以下不同点:为什么B+Tree更适合做文件索引两种引擎的索引存储机制MyISAM索引实现InnoDB...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 55,623
精华内容 22,249
关键字:

数据库索引讲解