继续浏览精彩内容
慕课网APP
程序员的梦工厂
打开
继续
感谢您的支持,我会继续努力的
赞赏金额会直接到老师账户
将二维码发送给自己后长按识别
微信支付
支付宝支付

细说一下Mysql的索引的作用和使用?

慕仔3251853
关注TA
已关注
手记 17
粉丝 0
获赞 2



  运用mysql最多的便是查询,咱们火燎的期望mysql能查询的更快一些,咱们常常用到的查询有:


  依照id查询唯一一条记载

  依照某些个字段查询对应的记载

  查找某个规划的悉数记载(between and)

  对查询出来的作用排序

  mysql的索引的意图是使上面的各种查询能够更快。


  预备常识


  什么是索引?


  上一篇中有详细的介绍,能够以前看一下:什么是索引?


  索引的本质:经过不断地缩小想要获取数据的规划来筛选出终究想要的作用,一同把随机的作业变成次第的作业,也便是说,有了这种索引机制,咱们能够总是用同一种查找办法来确认数据。


  磁盘中数据的存取


  以机械硬盘来说,先了解几个概念。


  扇区:磁盘存储的最小单位,扇区一般大小为512Byte。


  磁盘块:文件系统与磁盘交互的的最小单位(计算机系统读写磁盘的最小单位),一个磁盘块由接连几个(2^n)扇区组成,块一般大小一般为4KB。


  磁盘读取数据:磁盘读取数据靠的是机械运动,每次读取数据花费的时刻能够分为寻道时刻、旋转推迟、传输时刻三个部分,寻道时刻指的是磁臂移动到指定磁道所需求的时刻,干流磁盘一般在5ms以下;旋转推迟便是咱们常常风闻的磁盘转速,比方一个磁盘7200转,标明每分钟能转7200次,也便是说1秒钟能转120次,旋转推迟便是1/120/2 = 4.17ms;传输时刻指的是从磁盘读出或将数据写入磁盘的时刻,一般在零点几毫秒,相关于前两个时刻能够忽略不计。那么拜访一次磁盘的时刻,即一次磁盘IO的时刻约等于5+4.17 = 9ms左右,听起来还挺不错的,但要知道一台500 -MIPS的机器每秒能够实施5亿条指令,由于指令依托的是电的性质,换句话说实施一次IO的时刻能够实施40万条指令,数据库动辄十万百万甚至千万级数据,每次9毫秒的时刻,明显是个灾祸。


  mysql中的页


  mysql中和磁盘交互的最小单位称为页,页是mysql内部界说的一种数据结构,默许为16kb,恰当于4个磁盘块,也便是说mysql每次从磁盘中读取一次数据是16KB,要么不读取,要读取便是16KB,此值能够修改的。


  数据检索进程


  咱们对数据存储办法不做任何优化,直接将数据库中表的记载存储在磁盘中,假定某个表只需一个字段,为int类型,int占用4个byte,每个磁盘块能够存储1000条记载,100万的记载需求1000个磁盘块,假定咱们需求从这100万记载中检索所需求的记载,需求读取1000个磁盘块的数据(需求1000次io),每次io需求9ms,那么1000次需求9000ms=9s,100条数据随意一个查询便是9秒,这种状况咱们是无法承受的,明显是不可的。


  咱们火燎的需求是什么?


  咱们火燎需求这样的数据结构和算法:


  需求一种数据存储结构:当从磁盘中检索数据的时分能,够减少磁盘的io次数,最好能够降低到一个安稳的常量值

  需求一种检索算法:当从磁盘中读取磁盘块的数据之后,这些块中或许包括多条记载,这些记载被加载到内存中,那么需求一种算法能够快速从内存多条记载中快速检索出方针数据

  咱们来找找,看是否能够找到这样的算法和数据结构。


  咱们看一下常见的检索算法和数据结构。


  循环遍历查找


  从一组无序的数据中查找方针数据,常见的办法是遍历查询,n条数据,时刻复杂度为O(n),最快需求1次,最坏的状况需求n次,查询功率不安稳。


  二分法查找


  二分法查找也称为减半查找,用于在一个有序数组中快速界说某一个需求查找的数据。


  原理是:


  先将一组无序的数据排序(升序或许降序)之后放在数组中,此处用升序来举例说明:用数组中心方位的数据A和需求查找的数据F比照,假定A=F,则完毕查找;假定AF,则将查找规划缩小至数组中A数据左边的部分,持续依照上面的办法直到找到F中止。


  示例:


  从下列有序数字中查找数字9,进程如下

  [1,2,3,4,5,6,7,8,9]


  第1次查找:[1,2,3,4,5,6,7,8,9]中心方位值为5,9>5,将查找规划缩小至5右边的部分:[6、7、8、9]


  第2次查找:[6、7、8、9]中心值为8,9>8 ,将规划缩小至8右边部分:[9]


  第3次查找:在[9]中查找9,找到了。


  能够看到查找速度是恰当快的,每次查找都会使规划减半,假定咱们选用次第查找,上面数据最快需求1次,最多需求9次,而二分法查找最多只需求3次,耗时时刻也比较安稳。


  二分法查找时刻复杂度是:O(logN)(N为数据量),100万数据查找最多只需求20次(2^20=1048576‬)


  二分法查找数据的利益:定位数据十分快,条件是:方针数组是有序的。


  有序数组


  假定咱们将mysql中表的数据以有序数组的办法存储在磁盘中,那么咱们定位数据进程是:


  取出方针表的悉数数据,存放在一个有序数组中

  假定方针表的数据量十分大,从磁盘中加载到内存中需求的内存也十分大

  进程取出悉数数据耗费的io次数太多,进程2耗费的内存空间太大,还有新增数据的时分,为了保证数组有序,刺进数据会涉及到数组内部数据的移动(365myworLd),也是比较耗时的,明显用这种办法存储数据是不可取的。


  链表


  链表恰当于在每个节点上添加一些指针,能够和前面或许后边的节点联接起来,就像一列火车相同,每节车厢恰当于一个节点,车厢内部能够存储数据,每个车厢和下一节车厢相连。


  链表分为单链表和双向链表。


  单链表


  每个节点中有持有指向下一个节点的指针,只能依照一个方向遍历链表,结构如下:

  //单项链表

  class Node1{

  private Object data;//存储数据

  private Node1 nextNode;//指向下一个节点

  }

  双向链表


  每个节点中两个指针,别离指向其时节点的上一个节点和下一个节点,结构如下:

  //双向链表

  class Node2{

  private Object data;//存储数据

  private Node1 prevNode;//指向上一个节点

  private Node1 nextNode;//指向下一个节点

  }

  链表的利益:


  能够快速定位到上一个或许下一个节点

  能够快速删去数据,只需改动指针的指向即可,这点比数组好

  链表的缺点:


  无法向数组那样,经过下标随机拜访数据

  查找数据需从第一个节点开端遍历,不利于数据的查找,查找时刻和无需数据类似,需求全遍历,最差时刻是O(N)

  二叉查找树


  二叉树是每个结点最多有两个子树的树结构,一般子树被称作“左子树”(left subtree)和“右子树”(right subtree)。二叉树常被用于完毕二叉查找树和二叉堆。二叉树有如下特性:


  1、每个结点都包括一个元素以及n个子树,这儿0≤n≤2。2、左子树和右子树是有次第的,次第不能任意倒置,左子树的值要小于父结点,右子树的值要大于父结点。

  数组[20,10,5,15,30,25,35]运用二叉查找树存储如下:




  Mysql索引的详解常识送给咱们



  每个节点上面有两个指针(left,rigth),能够经过这2个指针快速拜访左右子节点,检索任何一个数据最多只需求拜访3个节点,恰当于拜访了3次数据,时刻为O(logN),和二分法查找功率相同,查询数据仍是比较快的。


  可是假定咱们刺进数据是有序的,如[5,10,15,20,30,25,35],那么结构就变成下面这样:




  Mysql索引的详解常识送给咱们



  二叉树退化为了一个链表结构,查询数据最差就变为了O(N)。


  二叉树的优缺点:


  查询数据的功率不安稳,若树左右比较平衡的时,最差状况为O(logN),假定刺进数据是有序的,退化为了链表,查询时刻变成了O(N)

  数据量大的状况下,会导致树的高度变高,假定每个节点对应磁盘的一个块来存储一条数据,需io次数大幅添加,明显用此结构来存储数据是不可取的

  平衡二叉树(AVL树)


  平衡二叉树是一种特别的二叉树,所以他也满意前面说到的二叉查找树的两个特性,一同还有一个特性:


  它的左右两个子树的高度差的绝对值不跨越1,并且左右两个子树都是一棵平衡二叉树。

  平衡二叉树相关于二叉树来说,树的左右比较平衡,不会呈现二叉树那样退化成链表的状况,不管怎样刺进数据,终究经过一些调整,都能够保证树左右高度相差不大于1。


  这样能够让查询速度比较安稳,查询中遍历节点控制在O(logN)规划内


  假定数据都存储在内存中,选用AVL树来存储,仍是能够的,查询功率十分高。不过咱们的数据是存在磁盘中,用过选用这种结构,每个节点对应一个磁盘块,数据量大的时分,也会和二叉树相同,会导致树的高度变高,添加了io次数,明显用这种结构存储数据也是不可取的。


  B-树


  B杠树,千万不要读作B减树了,B-树在是平衡二叉树上进化来的,前面介绍的几种树,每个节点上面只需一个元素,而B-树节点中能够放多个元素,首要是为了降低树的高度。


  一棵m阶的B-Tree有如下特性【特征描绘的有点绕,看不懂的能够越过,看后边的图】:


  每个节点最多有m个孩子,m称为b树的阶

  除了根节点和叶子节点外,其它每个节点至少有Ceil(m/2)个孩子

  若根节点不是叶子节点,则至少有2个孩子

  悉数叶子节点都在同一层,且不包括其它关键字信息

  每个非终端节点包括n个关键字(健值)信息

  关键字的个数n满意:ceil(m/2)-1 <= n <= m-1

  ki(i=1,…n)为关键字,且关键字升序排序

  Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的悉数节点关键字均小于ki,但都大于k(i-1)

  B-Tree结构的数据能够让系统高效的找到数据地址的磁盘块。为了描绘B-Tree,首要界说一条记载为一个二元组[key, data] ,key为记载的键值,对应表中的主键值,data为一行记载中除主键外的数据。关于不同的记载,key值互不相同。


  B-Tree中的每个节点根据实践状况能够包括许多的关键字信息和分支,如下图所示为一个3阶的B-Tree:




  Mysql索引的详解常识送给咱们



  每个节点占用一个盘块的磁盘空间,一个节点上有两个升序排序的关键字和三个指向子树根节点的指针,指针存储的是子节点地址磁盘块的地址。两个键将数据划分红的三个规划域,对应三个指针指向的子树的数据的规划域。以根节点为例,关键字为17和35,P1指针指向的子树的数据规划为小于17,P2指针指向的子树的数据规划为17~35,P3指针指向的子树的数据规划为大于35。


  仿照查找关键字29的进程:


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

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

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

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

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

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

  分析上面进程,发现需求3次磁盘I/O操作,和3次内存查找操作,由于内存中的关键字是一个有序表结构,能够运用二分法快速定位到方针数据,而3次磁盘I/O操作是影响整个B-Tree查找功率的决定因素。


  B-树相关于avl树,经过在节点中添加节点内部数据的个数来减少磁盘的io操作。


  上面咱们说过mysql是选用页办法来读写数据,每页是16KB,咱们用B-树来存储mysql的记载,每个节点对应mysql中的一页(16KB),假定每行记载加上树节点中的1个指针占160Byte,那么每个节点能够存储1000(16KB/160byte)条数据,树的高度为3的节点大约能够存储(第一层1000+第二层1000^2+第三层1000^3)10亿条记载,是不是十分惊讶,一个高度为3个B-树大约能够存储10亿条记载,咱们从10亿记载中查找数据只需求3次io操作能够定位到方针数据地址的页,而页内部的数据又是有序的,然后将其加载到内存顶用二分法查找,是十分快的。


  能够看出运用B-树定位某个值仍是很快的(10亿数据中3次io操作+内存中二分法),可是也是有缺点的:B-不利于规划查找,比方上图中咱们需求查找[15,36]区间的数据,需求拜访7个磁盘块(1/2/7/3/8/4/9),io次数又上去了,规划查找也是咱们常常用到的,所以b-树也不太适合在磁盘中存储需求检索的数据。


  b+树


  先看个b+树结构图:




  Mysql索引的详解常识送给咱们



  b+树的特征


  每个结点至多有m个子女

  除根结点外,每个结点至少有[m/2]个子女,根结点至少有两个子女

  有k个子女的结点必有k个关键字

  父节点中持有拜访子节点的指针

  父节点的关键字在子节点中都存在(如上面的1/20/35在每层都存在),要么是最小值,要么是最大值,假定节点中关键字是升序的办法,父节点的关键字是子节点的最小值

  最底层的节点是叶子节点

  除叶子节点之外,其他节点不保存数据,只保存关键字和指针

  叶子节点包括了悉数数据的关键字以及data,叶子节点之间用链表联接起来,能够十分便利的支撑规划查找

  b+树与b-树的几点不同


  b+树中一个节点假定有k个关键字,最多能够包括k个子节点(k个关键字对应k个指针);而b-树对应k+1个子节点(多了一个指向子节点的指针)

  b+树除叶子节点之外其他节点值存储关键字和指向子节点的指针,而b-树还存储了数据,这样相同大小状况下,b+树能够存储更多的关键字

  b+树叶子节点中存储了悉数关键字及data,并且多个节点用链表联接,从上图中看子节点中数据从左向右是有序的,这样快速能够支撑规划查找(先定位规划的最大值和最小值,然后子节点中依托链表遍历规划数据)

  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次数,然后影响查询功率。

  Mysql的存储引擎和索引


  mysql内部索引是由不同的引擎完毕的,首要说一下InnoDB和MyISAM这两种引擎中的索引,这两种引擎中的索引都是运用b+树的结构来存储的。


  InnoDB中的索引


  Innodb中有2种索引:主键索引(集结索引)、辅佐索引(非集结索引)。


  主键索引:每个表只需一个主键索引,叶子节点一同保存了主键的值也数据记载。


  辅佐索引:叶子节点保存了索引字段的值以及主键的值。


  MyISAM引擎中的索引


  不管是主键索引仍是辅佐索引结构都是相同的,叶子节点保存了索引字段的值以及数据记载的地址。


  如下图:


  有一张表,Id作为主索引,Name作为辅佐索引。



  Mysql索引的详解常识送给咱们



  InnoDB数据检索进程


  假定需求查询id=14的数据,只需求在左边的主键索引中检索就能够了。


  假定需求查找name='Ellison'的数据,需求2步:


  先在辅佐索引中检索到name='Ellison'的数据,获取id为14

  再到主键索引中检索id为14的记载

  辅佐索引这个查询进程在mysql中叫做回表。


  MyISAM数据检索进程


  在索引中找到对应的关键字,获取关键字对应的记载的地址

  经过记载的地址查找到对应的数据记载

  咱们用的最多的是innodb存储引擎,所以此处首要说一下innodb索引的状况,innodb中最好是选用主键查询,这样只需求一次索引,假定运用辅佐索引检索,涉及到回表操作,比主键查询要耗时一些。


  innodb中辅佐索引为什么不像myisam那样存储记载的地址?


  表中的数据发生改动的时分,会影响其他记载地址的改动,假定辅佐索引中记载数据的地址,此时会受影响,而主键的值一般是很少更新的,当页中的记载发生地址改动的时分,对辅佐索引是没有影响的。


  咱们来看一下mysql中页的结构,页是实在存储记载的当地,对应B+树中的一个节点,也是mysql中读写数据的最小单位,页的结构设计也是恰当有水平的,能够加速数据的查询。


  页结构


  mysql中页是innodb中存储数据的基本单位,也是mysql中处理数据的最小单位,和磁盘交互的时分都是以页来进行的,默许是16kb,mysql中选用b+树存储数据(cameL2005),页恰当于b+树中的一个节点。


  页的结构如下图:




  Mysql索引的详解常识送给咱们



  每个Page都有通用的头和尾,可是中部的内容根据Page的类型不同而发生改动。Page的头部里有咱们关心的一些数据,下图把Page的头部详细信息显示出来:




  Mysql索引的详解常识送给咱们



  咱们要点注重和数据组织结构相关的字段:Page的头部保存了两个指针,别离指向前一个Page和后一个Page,根据这两个指针咱们很简单幻想出Page链接起来便是一个双向链表的结构,如下图:




  Mysql索引的详解常识送给咱们



  再看看Page的主体内容,咱们首要注重行数据和索引的存储,他们都位于Page的User Records部分,User Records占有Page的大部分空间,User Records由一条一条的Record组成。在一个Page内部,单链表的头尾由固定内容的两条记载来标明,字符串办法的"Infimum"代表开端,"Supremum"代表完毕,这两个用来代表开端完毕的Record存储在System Records的,Infinum、Supremum和User Records组成了一个单向链表结构。开端数据是依照刺进的先后次第摆放的,可是跟着新数据的刺进和旧数据的删去,数据物理次第会变得紊乱,但他们依然经过链表的办法保持着逻辑上的先后次第,如下图:




  Mysql索引的详解常识送给咱们



  把User Record的组织办法和若干Page组合起来,就看到了稍微完好的办法。




  Mysql索引的详解常识送给咱们



  innodb为了快速查找记载,在页中界说了一个称之为page directory的目录槽(slots),每个槽位占用两个字节(用于保存指向记载的地址),page directory中的多个slot组成了一个有序数组(可用于二分法快速定位记载,向下看),行记载被Page Directory逻辑的分红了多个块,块与块之间是有序的,能够加速记载的查找,如下图:




  Mysql索引的详解常识送给咱们



  看上图,每个行记载的都有一个n_owned的区域(图中粉色区域),n_owned标识所属的slot这个这个块有多少条数据,伪记载Infimum的n_owned值总是1,记载Supremum的n_owned的取值规划为[1,8],其他用户记载n_owned的取值规划[4,8],并且只需每个块中最大的那条记载的n_owned才会有值,其他的用户记载的n_owned为0。


  数据检索进程


  在page中查询数据的时分,先经过b+树中查询办法定位到数据地址的页,然后将页内整体加载到内存中,经过二分法在page directory中检索数据,缩小规划,比方需求检索7,经过二分法查找到7位于slot2和slot3所指向的记载中心,然后从slot3指向的记载5开端向后向后一个个找,能够找到记载7,假定里边没有7,走到slot2向的记载8完毕。


  n_owned规划控制在[4,8]内,能保证每个slot统辖的规划内数据量控制在[4,8]个,能够加速方针数据的查找,当有数据刺进的时分,page directory为了控制每个slot对应块中记载的个数([4,8]),此时page directory中会对slot的数量进行调整。


打开App,阅读手记
0人推荐
发表评论
随时随地看视频慕课网APP