MySql索引底层原理

一、索引

       说到索引,大家理解的肯定是把它当做字典的偏旁或者一本书的目录页,更好的指引我们找到内容。可以去这样理解,但这样太表面了。 其实,索引是帮助MySql高效获取数据的排好序数据结构。(如果没有索引则需要一条一条挨个磁盘IO查找)。我们先讲讲数据结构。

二、索引数据结构

2.1 Hash表

       Hash索引(hash index)基于哈希表实现,对于每一行数据,存储引擎都会对所有的索引列计算一个hash码(hash code),哈希码是一个较小的值,哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。如果hash码一样则会采用链表的形式存储,类似于HashMap,Hash索引适用于精准查询。

假如有以下表:


如果我们在name列建立索引,name数据库会使用哈希算法计算name列每一行数据的hash值并进行存储。因为Hash值是随机计算的,所以可能存在冲突,假如计算结果如下:

我们有一条SELECT id,name,age FROM t_user WHERE name=‘石小添’; 这样的一条SQL可以直接对石小添 按哈希算法算出来一个Hash值,通过该值找到对应的记录指针,通过记录指针找到表中的哪一行数据,最后比较name是否为石小添,以保证就是要查找的行。
但是如果我们有 SELECT id,name,age FROM t_user WHERE name>‘石小添’; 这样的一条SQL则无能为力,因为Hash表支持快速的精确查询,但是不支持范围查询。
 

2.1 二叉树

二叉树基本特点:逐条插入数据时,左边的子节点小于父节点,右边的子节点大于父节点,这就是排序。

假设有一张表如上图有两个字段,当前这张表没有索引。如果我们执行一条Sql select * from table where Col2 = 89;它是要从上往下依次查询6次才能找到结果。如果在Col2字段建立索引,将该字段值用二叉树存储,那我们只需要查询2次即可得到结果。每个节点存的值类似于(key,value),key:该索引字段的值。value:该字段所在行数据存在磁盘上文件地址指针。 但是,如果我们拿字段Col1(单边增长)作为索引,一个一个逐渐插入数据(如果有索引,先维护好索引结构再插入数据)时,其得到的结果是这样的:

这时你就会发现成了链表结构,该索引就毫无意义了,跟未建立索引查询次数是一样的。所以,Mysql索引是没有使用二叉树这种数据结构的。

2.2 红黑树(二叉平衡树)

红黑数又叫二叉平衡树。红黑树就很好解决了上面二叉树这种存储单边增长数据出现的情况,当逐条插入时底层的算法会自动平衡,上述Col1字段逐条插入后结果是这样的:

这时候执行 select * from table where Col1 = 6 就只要执行3次,这种情况下的索引,查询性能上比二叉树更加优化了。但是,如果数据量非常庞大,有五百万条,2^n=五百万。n为红黑树高度,而你需要查找的数据刚好在最下面的叶子节点,那效率可想而知。所以MySql索引也没有用这种结构存储索引。

2.3 B-Tree(B树)

data可理解为:该索引字段所在行数据存在磁盘上文件地址指针(Myisam引擎) 或 该索引字段所在行所有字段数据。

B-Tree特点:

1,叶节点具有相同深度,叶节点指针为空

2,所有索引元素不重复

3,节点中的数据索引从左到右递增排列

4,也满足左边的子节点小于父节点,右边的子节点大于父节点(排序)

B-Tree结构就解决了红黑树问题,横向扩展,能存储更多的数据,就能控制好树的高度。Mysql索引就是利用B-Tree数据结构,稍微改造了下成为B+Tree。

2.4 B+Tree(Mysql索引所用的数据结构)

B+Tree是由B-Tree改变而来,其结构是这样的:

B+Tree特点:

1,非叶子节点不存储data,只存储索引(冗余),这样可以放更多的索引

2,叶子节点包含所有索引字段

3,叶子节点用指针连接,提高区间访问的性能

4,也满足左边的子节点小于父节点,右边的子节点大于父节点(排序)

总结:相对于B-Tree。B+Tree,叶子节点存储了完整的索引,而非叶子节点只存储了索引(没存data),而且索引还是有重复的,这样做的目的是为了一个节点上存储更多索引。

为什么B+Tree非叶子结点不存data,只存索引(冗余)?

因为MySql在设计的时候,每个节点存储大小为16KB,可以通过语句 SHOW GLOBAL STATUS LIKE 'InnoDb_page_size 查询

假设我们用一个bigint类型的作为索引,接下来我们分析下:

一个bigint类型占8个字节,旁边的为指向下一个节点的指针地址值,MySql底层设计为6B左右以内,这一个索引占14B,

根节点就能存储16KB/14B=1170个索引,假设我们树的高度为3,则一共能存储 1170*1170*16条数据,这相对于B-Tree来说,在相同高度下,明显存储的数据多得多,这样做性能也明显提高了,mysql中用的B+Tree结构叶子节点是双向链表且头尾也存有互相指向对方的指针,这些指针的存在大大增加了查找性能(比如范围查找,排序查找)。

三、常用MySqly存储引擎

3.1 Myisam存储引擎索引实现

3.1.1 创建一个 Myisam存储引擎 表

CREATE TABLE `tb_myisam` (
  `id` int(11) NOT NULL,
  `col1` varchar(255) DEFAULT NULL,
  `col2` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

大家都知道,数据库中数据是存在磁盘上的,tb_myisam表在磁盘上对应了三个文件:

一个myisam表创建之后在磁盘上会有三个文件frmMYDMYI维护:
frm:存储表结构
MYD:存储表数据
MYI:存储表中索引

非聚集索引:索引和数据分开两个文件存储

3.1.2 Myisam索引维护:

主键索引

Col1是主键,在Col1上建立索引。如果现在执行语句:select * from table where Col1=49;从根节点开始找,右子节点>=父节点。Myisam引擎data存储的是 该索引字段所在行数据存在磁盘上文件地址指针,就这样快速找到数据。

非主键索引和主键索引差不多

3.2 InnoDB存储引擎索引实现(MySql默认)

3.2.1 创建一个 InnoDB存储引擎 表

CREATE TABLE `tb_innodb` (
  `id` int(11) NOT NULL,
  `col1` varchar(255) DEFAULT NULL,
  `col2` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

InnoDB是MySql默认存储引擎,tb_InnoDB表在磁盘上对应了两个文件:

一个InnoDB表创建之后在磁盘上会有两个文件frmibd维护:
frm:存储表结构
ibd:存储表索引和数据

聚集索引:索引和数据存在同一个文件

3.2.2 InnoDB索引维护(主键索引):我们在插入数据之前需要对索引维护之后才能插入成功

InnoDB存储引擎索引特点:

1,表数据文件本身就是按B+Tree组织的一个索引结构文件

2,InnoDB索引也叫聚集索引(索引和数据存在一个文件),都存在存在tb_innodb.ibd文件,查询数据是只要过滤tb_innodb.ibd一个文件,其效率高于Myisam存储引擎。

3,InnoDB索引叶节点包含了完整的数据记录

4,为什么InnoDB表必须有主键,并且推荐使用整型的自增主键?

因为Mysql在设计时 表数据文件本身就是按B+Tree组织的一个索引结构文件,如果没有主键这个数据文件是组织不起来的。如果在建InnoDB表时没给主键,系统会自动从表字段选择一个唯一值的作为主键,如果没有,系统默认加一列(Rowid)作为主键,但我们是看不到的。那为什么推荐使用整型的自增主键呢?因为我们在用索引查数据时,会从根节点往下找,要比较左右节点的大小,很明显整型的比较效率要高于字符串(因为字符串在比较时还要先转换成ACSii码),这样效率高得多。而且整型所占空间较小,节约空间。自增主键是为了在插入数据时更好的维护索引,不用大范围改变索引数据结构(B+Tree节点都是逐渐递增的,如果当前节点已经存满16KB,则会结构发生很大改变,会有性能开销)。如果索引是逐渐递增的插入,则不会发生这种情况。

3.2.3  InnoDB非主键索引

可以看到叶子节点存的是表主键的值,为什么这样设计呢?一致性(如果也跟主键索引一样存储整行字段的值的话我们就需要考虑到数据的一致性了)和节省空间(没必要存两份浪费空间)

四、联合索引(非主键索引)

我们在开发项目时一般不创建单列索引,而是多个键创建联合索引.

假设我们联合索引为(col1,col2,col3),分别为上图绿色方格中的三行数据,分别根据col1,col2,col3三列排序,紫色为其他非索引字段。

原理:先根据col1排序从左往右逐渐自增。col1相同拿col2排序,依次类推。

如果有三条语句如下:

(1) select * from table where col1=10003 and col2=Staff;

(2) select * from table where col2=Engineer and col3=1996-08-03;

(3) select * from table where  col3=1996-08-03;

根据上面讲述的排序原理我们可以知道,只有第(1)才会走索引查询,(2)(3)是不会的,这样(1)的效率明显高于(2)(3)。这就是我们常常在网上看到的最左前缀法则,建立的索引会失效。

 

 

  • 0
    点赞
  • 0
    评论
  • 2
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

打赏
文章很值,打赏犒劳作者一下
相关推荐
<p> <span style="font-size:14px;color:#337FE5;">【为什么学爬虫?】</span> </p> <p> <span style="font-size:14px;">       1、爬虫入手容易,但是深入较难,如何写出高效率的爬虫,如何写出灵活性高可扩展的爬虫都是一项技术活。另外在爬虫过程中,经常容易遇到被反爬虫,比如字体反爬、IP识别、验证码等,如何层层攻克难点拿到想要的数据,这门课程,你都能学到!</span> </p> <p> <span style="font-size:14px;">       2、如果是作为一个其他行业的开发者,比如app开发,web开发,学习爬虫能让你加强对技术的认知,能够开发出更加安全的软件和网站</span> </p> <p> <br /> </p> <span style="font-size:14px;color:#337FE5;">【课程设计】</span> <p class="ql-long-10663260"> <span> </span> </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> 一个完整的爬虫程序,无论大小,总体来说可以分成三个步骤,分别是: </p> <ol> <li class="" style="font-size:11pt;color:#494949;"> 网络请求:模拟浏览器的行为从网上抓取数据。 </li> <li class="" style="font-size:11pt;color:#494949;"> 数据解析:将请求下来的数据进行过滤,提取我们想要的数据。 </li> <li class="" style="font-size:11pt;color:#494949;"> 数据存储:将提取到的数据存储到硬盘或者内存中。比如用mysql数据库或者redis等。 </li> </ol> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> 那么本课程也是按照这几个步骤循序渐进的进行讲解,带领学生完整的掌握每个步骤的技术。另外,因为爬虫的多样性,在爬取的过程中可能会发生被反爬、效率低下等。因此我们又增加了两个章节用来提高爬虫程序的灵活性,分别是: </p> <ol> <li class="" style="font-size:11pt;color:#494949;"> 爬虫进阶:包括IP代理,多线程爬虫,图形验证码识别、JS加密解密、动态网页爬虫、字体反爬识别等。 </li> <li class="" style="font-size:11pt;color:#494949;"> Scrapy和分布式爬虫:Scrapy框架、Scrapy-redis组件、分布式爬虫等。 </li> </ol> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> 通过爬虫进阶的知识点我们能应付大量的反爬网站,而Scrapy框架作为一个专业的爬虫框架,使用他可以快速提高我们编写爬虫程序的效率和速度。另外如果一台机器不能满足你的需求,我们可以用分布式爬虫让多台机器帮助你快速爬取数据。 </p> <p style="font-size:11pt;color:#494949;">   </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> 从基础爬虫到商业化应用爬虫,本套课程满足您的所有需求! </p> <p class="ql-long-26664262" style="font-size:11pt;color:#494949;"> <br /> </p> <p> <br /> </p> <p> <span style="font-size:14px;background-color:#FFFFFF;color:#337FE5;">【课程服务】</span> </p> <p> <span style="font-size:14px;">专属付费社群+定期答疑</span> </p> <p> <br /> </p> <p class="ql-long-24357476"> <span style="font-size:16px;"><br /> </span> </p> <p> <br /> </p> <p class="ql-long-24357476"> <span style="font-size:16px;"></span> </p>
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页

打赏

manba123456

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值