站长工具

MySQL索引高级进阶详解-玩转MySQL数据库

作者：qian-fen

前言

从今天开始本系列文章就带各位小伙伴学习数据库技术。数据库技术是Java开发中必不可少的一部分知识内容。也是非常重要的技术。本系列教程由浅入深, 全面讲解数据库体系。非常适合零基础的小伙伴来学习。

全文大约 【1957】字，不说废话，只讲可以让你学到技术、明白原理的纯干货！本文带有丰富案例及配图视频，让你更好的理解和运用文中的技术概念，并可以给你带来具有足够启迪的思考......

一. 索引

在上一章节中我们讲解了索引的基本入门和使用进阶。那么在这一节中我们来探讨下索引的深层原理。各位小伙伴准备好了吗，我们开始喽！

1. 索引的实现原理

索引是在MySQL的存储引擎中实现的，所以每种存储引擎的索引也就各不相同了，不同的存储引擎支持不同类型的索引。这里我们主要研究InnoDB引擎实现的B+树索引。

B+树是一种数据结构。通常使用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序，还能够加快查询速度，我们一起来看下吧。

2. 磁盘存储

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的。位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB。

InnoDB引擎将若干个地址连接磁盘块，以此来达到页的大小16KB，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

3. BTree

BTree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述BTree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。BTree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的BTree：

image.png

根据图中结构显示，每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

查找顺序,模拟查找15的过程 ：

● 根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】比较关键字15在区间（<17），找到磁盘块1的指针P1。

● P1指针找到磁盘块2，读入内存。【磁盘I/O操作第2次】比较关键字15在区间（>12），找到磁盘块2的指针P3。

● P3指针找到磁盘块7，读入内存。【磁盘I/O操作第3次】 在磁盘块7中找到关键字15。\

● 分析:
○ 发现需要3次磁盘I/O操作，和3次内存查找操作。

○ 由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个BTree查找效率的决定因素。BTree使用较少的节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

4. B+Tree

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

从上一节中的BTree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

image.png

B+Tree相对于BTree区别：

● 非叶子节点只存储键值信息。

● 所有叶子节点之间都有一个连接指针。

● 数据记录都存放在叶子节点中。

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

对B+Tree进行两种查找运算：

● 【有范围】对于主键的范围查找和分页查找。

● 【有顺序】从根节点开始，进行随机查找。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在24层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要13次磁盘I/O操作。

二. 结语

最后在这里对本文核心要点进行总结:

各位小伙伴需要了解B树和B+树的区别，这些数据结构是我们程序员的基本功。

三. 配套视频

如果你不习惯阅读技术文章，或是对文中的技术概念不能很好地理解，可以来看看视频教程。与本文配套的Java学习视频，链接如下：戳我一键直达

本文转载于网络 如有侵权请联系删除

相关文章

  • 多线程笔记（七）JUC 这个包下有很多的类，其中CopyOnWriteArrayList是一个安全的集合类

    背景ArrayList这个集合本来就是线程不安全的，当我们多线程跑数据，往ArrayList里面添加数据的时候，前面的数据有可能被覆盖，为了解决这个问题，我们使用 synchronized关键字。现在我们还可以在不使用关键字的情况下往集合里面添加数据，并且数据是安全的，我们就可以使用CopyOnWriteArrayList这个数组JDK1.5引入的J.U.C包中，又实现了一个线程安全版的ArrayList——CopyOnWriteArrayList。代码实现publicclassJUC{ publicstaticvoidmain(String[]args){ CopyOnWriteArrayList<String>strings=newCopyOnWriteArrayList<>(); for(inti=0;i<10000;i++){ newThread(()->{ strings.add(Thread.currentThread().getName()); }).start(); } try{ Thread.sleep(Long.parse

  • 渗透测试中常见的那些编码和加密

    声明：该公众号大部分文章来自作者日常学习笔记，也有少部分文章是经过原作者授权和其他公众号白名单转载，未经授权，严禁转载，如需转载，联系开白。请勿利用文章内的相关技术从事非法测试，如因此产生的一切不良后果与文章作者和本公众号无关。0x01前言我们在渗透测试中经常会遇到各种各样的编码和加密，笔者在这里将以前遇到过的一些编码和加密整理了下，但肯定远不止这些，特别是加密的方式太多了，我能目测出来的也就最为常见的MD5密文和遇到过的，还有一些自写加密算法，这种还得先找到加密算法后才能解密。0x02 密文识别以前用的HashIdentifier和hashID都停止更新了，这里另外给大家推荐几个，使用这几款工具能够帮助我们快速识别密文加密类型，特别是一些不常见的加密，可识别加密类型多达300+。 https://github.com/noraj/haitihttps://nth.skerritt.blog/（在线识别）https://github.com/HashPals/Name-That-Hash0x03常见编码(1)Unix时间戳1218124800复制(2)KEYCODE键码65666796

  • 接入层限流之ngx_http_limit_req_module

    【转载请注明出处】：https://cloud.tencent.com/developer/article/1626336ngx_http_limit_req_module模块是Nginx提供的基于漏桶算法实现的请求限流模块，用于对指定KEY对应的请求进行限流，比如按照IP维度限制请求速率。ngx_http_limit_req_module官方文档配置示例http{ limit_req_zone$binary_remote_addrzone=one:10mrate=1r/s; limit_conn_log_levelerror; limit_conn_status503; ... server{ ... location/limit{ limit_reqzone=oneburst=5nodelay; }复制limit_req：配置限流区域、桶容量（突发容量，默认0）、是否延迟模式（默认延迟）；limit_req_zone：配置限流KEY、及存放KEY对应信息的共享内存区域大小、固定请求速率；此处指定的KEY是“$binary_remote_addr”表示IP地址；固定请求速率使用rat

  • 一文领略链接与装载

    引言链接与装载是一个比较晦涩的话题，大家往往容易陷入复杂的细节中而难以看清问题的本来面目。从本质上讲各个系统的编译、链接、装载过程都是大同小异的，或许可以用一种更抽象的形式来理解这些过程，梳理清楚宏观的来龙去脉有利于对特定系统进行深入学习。本文主要根据《程序员的自我修养——链接、装载与库》和自己的理解总结而来，书的内容是基于GCC的，不过笔者尽量以更抽象、更简洁的方式把问题讲清楚，避开那些恼人的细节。一、源代码是如何运行起来的不直接使用机器语言进行应用程序开发是为了提高开发效率，但程序终究是机器运行的，所以才有了复杂的编译链接过程，将源代码转换为机器指令。程序员一般使用IDE进行应用程序开发，对于需要先编译成机器语言再运行的程序，在执行运行指令后时常会陷入漫长的等待才能运行起来，这期间计算机做了大量工作：预编译：主要处理以“#”开始的预编译指令。编译：词法分析：将字符序列分割成一系列的记号。语法分析：根据产生的记号进行语法分析生成语法树。语义分析：分析语法树的语义，进行类型的匹配、转换、标识等。中间代码生成：源码级优化器将语法树转换成中间代码，然后进行源码级优化，比如把1+2优化为3。

  • hand first python 选读（3）

    数据处理掌握了前面4章，这就是比较简单的一章。先看一个需求：有一个跑步教练kelly，他最好的选手一直在刻苦训练，每次跑步成绩，kelly都会及时的把事件记录在计算机的一个文本文件中。总共有四个文件，分别记录james，sarah，julie和mikey的时间数据。 james:2-34,3:21,2.34,2.45,3.01,2:01,2:01,3:10,2-22 julie:2.59,2.11,2:11,2:23,3-10,2-23,3:10,3.21,3-21 sarah:2:58,2.58,2:39,2-25,2-55,2:54,2.18,2:55,2:55 mikey:2:22,3.01,3:01,3.02,3:02,3.02,3:22,2.49,2:38 首先，这个教练需要一种快捷的方法能够很快了解每个选手跑得最快的3个时间分析数据，有的是以.连接，有些以-连接，还有的是:。需作分类讨论转化为小数。排序接下来介绍列表排序方法。sort：用法如arr.sort()，不会返回新的列表，且修改原有列表sorted：用法如sorted(arr)；返回一个新数组，不修改旧列表都是升序

  • 挑战程序竞赛系列（31）：4.5剪枝

    版权声明：本文为博主原创文章，未经博主允许不得转载。https://blog.csdn.net/u014688145/article/details/76550373挑战程序竞赛系列（31）：4.5剪枝详细代码可以fork下Github上leetcode项目，不定期更新。 练习题如下：POJ1011:SticksPOJ2046:GapPOJ3134:PowerCalculusPOJ1011:Sticks变态的DFS搜索，需要剪枝否则TLE，初始版本如下：voidsolve(){ while(true){ intn=ni(); if(n==0)break; int[]sticks=newint[n]; intmin=0; intsum=0; intmax=0; for(inti=0;i<n;++i){ intlen=ni(); max=Math.max(max,len); sum+=len; sticks[i]=len; } Arrays.sort(sticks); Set<Integer>mem=newHashSet<Integer>(); for(int

  • Remove Duplicates from Sorted List

    问题：将有序链表中的重复元素删除 分析：由于有序，所以p结点是否重复只需要和它的前一节点比较是否相等就可以了，我们可以定义一个helper新头结点链表     将p结点与新链表的尾结点比较，若不相等则加入新链表中。classSolution { public: ListNode*deleteDuplicates(ListNode*head) { if(head==NULL||head->next==NULL)returnhead; ListNode*helper=newListNode(-100000); ListNode*ret=head; while(ret) { ListNode*next=ret->next; if(ret->val!=helper->val) { helper->next=ret; helper=ret;//将helper指新链表的尾结点 helper->next=NULL;//尾指向空，因为后面的结点有可能被删去了，它不知道下一个指向谁 } elsedeleteret; ret=next; } returnhead; }

  • Adaboost从原理到实现（Python）

    首先来回答AdaBoosting的基本思想通俗地讲就是综合某些专家的判断，往往要比一个专家单独的判断要好（三个臭皮匠顶过诸葛亮-周志华《机器学习第八章》）。在”强可学习”和”弱可学习”的概念上来说就是我们通过对多个弱可学习的算法进行”组合提升或者说是强化”得到一个性能赶超强可学习算法的算法。其次回答Boosting的思路 1.找到一个弱分类器，分类器简单，快捷，易操作(如果它本身就很复杂，而且效果还不错，那么进行提升无疑是锦上添花，增加复杂度，甚至上性能并没有得到提升，具体情况具体而论)。2.迭代寻找N个最优的分类器(最优的分类器，就是说这N个分类器分别是每一轮迭代中分类误差最小的分类器，并且这N个分类器组合之后是分类效果最优的。)。在迭代求解最优的过程中我们需要不断地修改数据的权重(AdaBoost中是每一轮迭代得到一个分类结果与正确分类作比较，修改那些错误分类数据的权重，减小正确分类数据的权重),后一个分类器根据前一个分类器的结果修改权重在进行分类，因此可以看出，迭代的过程中分类器的效果越来越好，所以需要给每个分类器赋予不同的权重。最终我们得到了N个分类器和每个分类器的权重，那么最

  • VBA: 通过Dir函数查找指定文件

    文章背景：通过VBA编写代码时，有时需要判断某个文件是否存在；或者判断在文件夹内是否存在指定类型的文件。此时，就会涉及到Dir函数。下面就来介绍Dir函数的语法和应用场景。1Dir函数的语法2应用示例2.1获取指定路径文件的名称2.2判断指定路径的文件夹是否存在（不存在则创建它） 2.3获取指定路径文件夹内所有文件和子文件夹的名称2.4获取指定路径文件夹内的所有文件名称2.5获取指定路径文件夹内所有子文件夹的名称2.6获取指定路径文件夹内第一个txt文件的名称 2.7获取指定路径文件夹内所有txt文件的名称 1Dir函数的语法Dir[(pathname[,attributes])]返回一个字符串，该字符串表示与指定模式或文件属性或驱动器卷标匹配的文件、目录或文件夹的名称。pathname可选参数。用来指定文件名的字符串表达式，可能包含目录或文件夹、以及驱动器。如果没有找到pathname，则会返回零长度字符串("")。attributes可选参数。常数或数值表达式，其总和用来指定文件属性。如果省略，则会返回匹配pathname但不包含属性的文件。attribute

  • 《Spring Boot编程思想》--运维篇

    《SpringBoot编程思想》--运维篇

  • MTU总结

            MTU：MaximumTransmissionUnit最大传输单元，以IP协议为例，MTU定义了网络层所能通过的最大IP报文。 1、数据传输过程中MTU遵循木桶原理 2、MTU是包括IP头的（不包括以太网14字节的头），IP头通常为20字节，所能通过的最大数据载荷（payload）计算方法为：MTU-IP头-上层协议头，这就是为何在不修改MTU时，网络设备互ping在不分片的情况下最大只能携带1472字节载荷MTU(1500)-IP头(20)-ICMP头(8)=1472 3、分片：若发送数据报文大于接口MTU，则发送者的IP层会将数据报文分片，在接收端进行重组 重组涉及到的IP字段： Identification：标识分片报文来自同一个数据包 Flag字段中的MF(MoreFragment)位：标识此报文为分片报文 FragmentOffset（偏移量）来进行重组 4、MSS：MaximumSegmentSize：最大分段大小，由TCP协议协商       为什么流通当时没匹配上？这个目前原因

  • Go 标准库 http.FileServer 实现静态文件服务

    http.FileServer方法属于标准库net/http，返回一个使用FileSystem接口root提供文件访问服务的HTTP处理器。可以方便的实现静态文件服务器。 http.ListenAndServe(":8080",http.FileServer(http.Dir("/files/path"))) 复制 访问http://127.0.0.1:8080，即可看到类似Nginx中autoindex目录浏览功能。 源码解析 我们现在开始将上述的那仅有的一行代码进行剖析，看看到底是如何实现的。源码中英文注释也比较详细，可以参考。 我们先看http.Dir()，再看http.FileServer()，而http.ListenAndServe()监听TCP端口并提供路由服务，此处不赘述。 http.Dir() 从以下源码我们可以看出，typeDirstring实现了typeFileSysteminterface的接口函数Open，http.Dir("/")实际返回的是http.Dir类型，将字符串路径转换成文件系统。 //所属文件:src/net/http/fs.go,26-87行

  • 再探haproxy

    一设置haproxy输出log 1.1调整配置文件 默认haproxy是不会输出log到文件的，这样很大程度在查询问题时会很不方便，haproxy是可以输出日志到文件的，配置文档类似于如下: ]#cathttp_haproxy.conf global maxconn100000 statssocket/var/run/haproxy.statmode600leveladmin log127.0.0.1local3debug userhaproxy grouphaproxy chroot/usr/local/haproxy/var daemon defaults logglobal modehttp retries3 timeoutconnect10s timeoutclient20s timeoutserver30s timeoutcheck5s frontendhttp-in bind:80 modehttp logglobal optionhttplog optionforwardfor optiondontlognull optionhttpclose default_ba

  • Web - 工具 、 库 和 兼容

    一，工具   ├ 1， vue-awesome-swiper  基于 Swiper4、适用于Vue的轮播组件，支持服务端渲染和单页应用。 2,  http://www.spritecow.com/   雪碧图背景定位工具 3, uuid (npm) 自动生成id 4，Animate.css 一款强大的预设css3动画库 , animate.css是一个来自国外的CSS3动画库，它预设了抖动（shake）、闪烁（flash）、弹跳（bounce）、翻转（flip）、旋转（rotateIn/rotateOut）、淡入淡出（fadeIn/fadeOut）等多达60多种动画效果，几乎包含了所有常见的动画效果。 5，jQuery插件  http://www.jq22.com/jqueryUI-1-jq 6, Vant   https://youzan.gith

  • 第二次软件工程基础作业

    熟悉使用工具 git地址  https://github.com/npcccc1/achaodnm.git git用户名  npcccc1 博客链接  https://www.cnblogs.com/npc1158947015/ 学号后5位  92324 作业链接  https://edu.cnblogs.com/campus/xnsy/Autumn2019SoftwareEngineeringFoundation/homework/7590                   一、环境配置过程 之前就用过github注册挺容易，之前也下好了visualstdio2017，选好了c++组件不过都没留截图，git的安装也没遇到太大的问题。 克隆项目：在文件类型转化的尝试上花了大量时间还没啥用 因为对于git了解太少的原因，在网上搜了几天也没看懂分支的知识，用git指令解决一个错又出来一个，所以把克隆过来的项目总是转化不成c

  • ACM——快速排序法

    快速排序 时间限制(普通/Java):1000MS/3000MS     运行内存限制:65536KByte总提交:653      测试通过:297 描述   给定输入排序元素数目n和相应的n个元素，写出程序，利用内排序算法中快速排序算法进行排序，并输出排序最后结果的相应序列。   输入   共两行，第一行给出排序元素数目n，第二行给出n个元素，1≤n≤100000，每个元素值范围为 [0，100000)   输出   一行，输出排序结果。   样例输入 748 36 68 72 12 48 2 样例输出 2 12 36 48 48 68 72 提示  数据结构A实验四 题目来源 CHENZ   //快速排序 #include<iostream> usi

  • php大力力 [042节] 今天做了一个删除功能

    php大力力[042节]今天做了一个删除功能   if(isset($_GET['action'])){ if($_GET['action']=="del"){ $sql="deletefromtbl_mediawhereMadiaName='{$_GET['MadiaName']}'"; $result=mysql_query($sql); if($result&&mysql_affected_rows()>0){ echo"删除成功<br>"; }else{ echo"删除失败<br>"; } } } 还有这里： echo'<td><ahref=""><fontcolor="#0050FF">修改</font"></a>/<ahref=""><ahref="admin_Price.php?action=del&MadiaName='.$MadiaName.'"><fontcolor="#0050FF

  • 【解决】MATLAB导出矢量图(例如SVG格式)模糊

    【参考】：Matlab导出矢量图模糊的解决办法 Plot，文件→导出设置→渲染→选择“Painters(矢量格式)”，再导出即可。 当图片元素复杂时，导出的矢量图大小太大，还不如导出位图。  

  • 第2章 C语言基础知识

    1C语言发展和特点 C语言源于ALGOL60语言，于20世纪60年代初提出。 1963年，英国剑桥大学将ALGOL60语言发展成为组合程设计语言（CPL）。1970年英国剑桥大学的MartinRichards对CPL进行简化，开发出基本组合程序设计语言（BCPL）。 1970年，美国贝尔实验室的KenThompson以BCPL语言为基础，设计出很简单且很接近硬件的B语言（取BCPL的首字母）。 1972年，美国贝尔实验室的D.M.Ritchie在B语言的基础上最终设计出了一种新的语言，他取了BCPL的第二个字母作为这种语言的名字，即C语言。 1.1C语言发展 1975年UNIX第六版发布后，C语言终于获得了计算机专业人士的广泛支持。 1978年，美国贝尔实验室正式推出了C语言。 1983年美国国家标准协会（ANSI）根据C语言问世以来的的各种版本，对C语言发展和扩充制定了第一个C语言标准草案，称为83ANSIC。 1989年ANSI发布了一个完整的C语言标准ANSIX3.159-1989，称为ANSIC或C89。 1990年，国际标准化组织（ISO）接受C89为ISO国际标准，也称为C

  • Nginx教程---01.Nginx入门

    createby三七二十一 LZ参考视频(年代久远，但万变不离其宗)：链接：https://pan.baidu.com/s/1O_MmN0c3ckM6vbk08n8Qkg密码：z9zr 01_Nginx入门 nginx-高性能Web服务器 复制 一、基础篇 1.Nginx介绍<br> 2.Nginx编译安装<br> 3.Nginx信号控制<br> 复制 1、Nginx介绍 Nginx("enginex")是一个高性能的HTTP和反向代理服务器，也是一个IMAP/POP3/SMTP代理服务器。Nginx是由IgorSysoev为俄罗斯访问量第二的Rambler.ru站点开发的 2、Nginx编译安装 Nginx官网：http://www.nginx.org/ 下载： 1.LZ喜欢下载stable(稳定版) 2.LZ将下载的nginx放在src下 3.复制stable版本的链接地址，使用wget命令下载 4.解压 5.安装     5.1在安装nginx之前，要安装pcre库，即正则表达式库 &n

  • 极客编程小挑战＃26：实现日期级联下拉选择框

      详细内容请参见原文：http://www.gbtags.com/gb/share/5823.htm  本期挑战：  初始的html代码如下： <selectname="year"id="year"> <optionvalue="0">--</option> <optionvalue="1999">1999</option> <optionvalue="2000">2000</option> <optionvalue="2001">2001</option> </select>年 <selectname="month"id="month"> <optionvalue="0">--</option> <optionvalue="1">1</option> <optionvalue="2">2</option> <optionvalue="3

相关推荐

推荐阅读