MySQL索引根底ITeye - 凯发娱乐

MySQL索引根底ITeye

2019年04月17日14时42分08秒 | 作者: 凡儿 | 标签: 索引,运用,哈希 | 浏览: 2350

    索引用于加速数据拜访的速度。把计算机的磁盘比作一本字典,索引便是字段的目录,当咱们想快速查到某个词语的时分只需求经过查询目录找到词语地点的页数,然后直接翻开某页就能够。MySQL最常用的索引是B+树索引,为什么运用B+作为MySQL的索引,这是许多面试官必问的问题。

为什么B+树 硬件相关常识

    计算机的磁盘是一个圆盘的接口,圆盘上有一个个的圆圈,数据便是记载在这些圆圈的扇区上。如下图所示

当计算机体系读取数据的时分要经过以下几个进程:
1、首要移动臂依据柱面号使磁头移动到所需求的柱面上,这一进程被称为寻道。所消耗的时刻叫寻道时刻(ts)。
2、方针扇区旋转到磁头下,这个进程消耗的时刻叫旋转时刻。
    因而拜访磁盘的时刻由三部分构成: 寻道时刻+旋转时刻+数据传输时刻 
榜首部分寻道时刻延迟最高,最大可到达100ms,旋转时刻取决于磁盘的转速,转速在7200转/分钟的磁盘均匀旋转时刻在5ms左右。磁盘的读取是以block(盘块)为单位的,坐落同一个盘块的数据能够一次性读取出来。在读写数据的时分尽量削减磁头来回移动的次数,防止过多的查找时刻。假设每次从磁盘上读取数据的时分都要阅历上面的几个进程那么功率上无疑是极低的。

为什么B+树

    从上面能够看到,假设随机拜访磁盘的速度是很慢的,因而需求规划一个合理的数据结构来削减随机拜访磁盘的次数。B树便是这样一种数据结构。

B树、B+树介绍 B树

    B树是为存储设备而规划的一种多叉平衡查找树。它与红黑树相似,可是在下降IO操作方面B树的体现要更好一些,B树与红黑树最大的差异在于B树能够有多个子节点,红黑树最多是有两个子节点,这就决议了大多数状况下B树的高度要比红黑树低许多,因而在查找的时分能够下降IO次数。下图是一棵B树:

B 树又名平衡多路查找树。一棵m阶的B树的特性如下:
    a.树中每个结点最多含有m个孩子(m =2);
    b.除根结点和叶子结点外,其它每个结点至少有[ceil(m / 2)]个孩子(其间ceil(x)是一个取上限的函数);
    c.若根结点不是叶子结点,则至少有2个孩子(特别状况:没有孩子的根结点,即根结点为叶子结点,整棵树只要一个根节点);
    d.一切叶子结点都出现在同一层,叶子结点不包括任何关键字信息
    e.每个非终端结点中包括有n个关键字信息: (n,P0,K1,P1,K2,P2,......,Kn,Pn)。其间:
        a) Ki (i=1…n)为关键字,且关键字按次序升序排序K(i-1) Ki。 
        b) Pi为指向子树根的接点,且指针P(i-1)指向子树种一切结点的关键字均小于Ki,但都大于K(i-1)。 
        c) 关键字的个数n有必要满意: [ceil(m / 2)-1] = n = m-1。
    B树中的每个节点都尽可能存储多的关键字信息和分支信息,可是不会超越磁盘块的巨细。这样在有用下降了树的高度,在查找的时分能够快速定位在指定的磁盘块。假设要从上图中找到79这个数字,首要从根节点开端扫描,79大于35所以挑选P3指针,指向磁盘块4,在磁盘块4中79在65和87之间,因而挑选P2指针,挑选磁盘块10,这时分就能够从磁盘块10中找到79。整个进程只需求3次IO,假设这棵树被缓存在内存中,那么只需求一次IO就能够读到79这个数字。

B+树

    B+树是B的变种,一颗m阶B+树和m阶B树的异同点在于:
    1、有n棵子树的节点中有n-1个关键字(与B树n棵子树有n-1个关键字,保持一致)
    2、一切的叶子节点中包括了悉数的关键字的信息,以及指向含有这些关键字记载的指针,且叶子节点自身依关键字的巨细而自小而大次序链接(而B树的叶子节点并没有包括悉数需求查找的信息)
    3、一切的非终端节点能够当作索引部分,节点中仅含有其子树根节点中最大或许最小的关键字(而B树的非终节点也要包括需求查找的有用信息)
    
因为B+树的叶子节点是衔接在一起的,因而相关于运用B树作为索引,关于MySQL的规模查询愈加优化。一起因为叶子节点包括一切关键字信息,因而有的查询句子就不需求回表,只需求查询索引就能够查到需求的数据。

索引类型 B树索引

    虽然是叫B树索引,可是数据库实际上运用的是B+树来安排数据。B树索引意味着一切值都是依照次序存储的,而且每个叶子节点到根节点的间隔是相同的。
假设有如下数据表:

CREATE TABLE `people` (
 `last_name` varchar(50) DEFAULT NULL,
 `first_name` varchar(50) DEFAULT NULL,
 `dob` date DEFAULT NULL,
 `gender` enum(m,f) DEFAULT NULL,
 KEY `last_name` (`last_name`,`first_name`,`dob`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

该表对last_name,first_name,dob三列建立了索引,索引的安排方法如下:

当一起对多列进行索引的时分,索引的次序是非常重要的,上面的索引首要是依照last_name进行索引,在last_name相同的状况下在对first_name进行排序,最终是dob字段。
    B树索引适用于全键值、键值规模和最左前缀查找:
全键值
    查找名字为Allen Kim,出生日期为1930-07-12的人,这样的查找方法匹配了索引中的一切字段,顺次扫描索引中的last_name、first_name和dob字段,找到对应的数据。
键值规模
    查找名字在Allen和Barrymore之间的人,这种查找方法也会运用到索引。需求留意的是这儿只能是索引中的榜首列,也便是last_name的规模查找。
前缀匹配
    查找last_name是以Al最初的人,这种查询会以此扫描索引中的节点,然后选出来对应的复合条件的行。也是只能运用榜首列的前缀查询。假设是说想查first_name的前缀匹配,那么是无法运用到索引的,意味着要进行全表扫描。
准确匹配某一列,规模批量别的一列
    准确匹配的列有必要是所以中的榜首列,规模匹配的列是第二列,这样才干运用到上面的索引。

 

B树索引的运用约束:
1、不是依照最左列开端查询的,无法运用索引。
2、不能越过索引的列进行查询。
3、假设运用到了规模匹配,那么规模匹配右边的列都无法运用索引查询。

哈希索引

    哈希索引运用哈希表来完成,只要是准确匹配的时分才会收效。存储引擎会对索引列计算出一个哈希值,然后保存一个哈希值到行数据的指针。哈希索引因为其特别的安排方法,约束了其运用场景。哈希索引只合适值比较少的状况,例如枚举类型。在以下几种方法中是不合适运用哈希索引的:
1、哈希索引只包括哈希值和指针,不存储字段值,因而运用哈希索引防止不了要进行回表查询。
2、哈希索引数据并不是依照值的次序进行排序的,因而哈希索引无法用来排序
3、哈希索引不支撑部分索引列匹配。比如说在(A,B)两列上简历哈希索引,那么只要在一起运用A、B两列查询的时分才会运用哈希索引,只运用A列查询无法运用哈希索引。
4、哈希索引只支撑等值比较,不支撑像between and这种规模查询。
5、运用哈希索引的时分应该尽量防止哈希抵触。

跋文

    数据库的索引机制处理的问题是在拜访内存数据与磁盘数据的速度不同很大的状况下,怎么快速拜访数据的问题。只要了解了索引的原理才干够更好的规划表的索引字段以及写出功能更优的查询句子。在咱们写SQL句子的时分头脑中应该大体上能规划出查询数据以及怎么运用索引的进程。下一篇会介绍一下高功能索引的战略,带你规划出更优的索引。

 

欢迎重视我的微信大众号:yunxi-talk,共享Java干货,进阶Java程序员必备。

​​

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表凯发娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1

    MySQL索引根底ITeye

    索引,运用,哈希
  • 2

    数据库拜访优化规律ITeye

    数据,索引,运用
  • 3

    导入导出与字符集ITeye

    字符集,导入,导出
  • 4

    游标笔记ITeye

    游标,数据,读取
  • 5

    SQLSERVER分页查询ITeye

    查询,代码,计划
  • 6
  • 7

    oracle暗码修正办法ITeye

    暗码,修正,用户
  • 8
  • 9
  • 10