【详解】MySQL 索引

最新推荐文章于 2022-05-24 17:52:40 发布
最新推荐文章于 2022-05-24 17:52:40 发布
阅读量196 收藏
点赞数
分类专栏： # Mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43040688/article/details/105419053
版权

目录

1. InnoDB存储引擎索引概述

主要有两种：

  • B+树索引（不能找到具体行，找到的是具体列）
  • 哈希索引

2. 二分查找法

在这里插入图片描述

3. 平衡二叉树

3.1 二叉查找树

在这里插入图片描述

  • 左子树小于根的值，右子树大于根的值
  • 可以通过中序遍历输出：2、3、5、6、7、8
  • 查找某个值，先跟根元素判断，如果根小，去右子树；如果根大，去左子树
  • 二叉查找树可以任意构造，如下面：
    在这里插入图片描述

3.2 平衡二叉树

  • 任何节点左右两个子树的高度，最大差为1
  • 性能是比较高的，如果需要最好的性能，需要最优二叉树，但是建立和维护需要很大的操作
  • 插入数据后，需要左旋或者右旋维护平衡性
    在这里插入图片描述

左旋

在这里插入图片描述
右旋

在这里插入图片描述

3.3 B+树

  • 为磁盘或其他直接存取的辅助设备而设计的平衡二叉树
  • 所有的记录节点都是按照键值的大小顺序存放在同一层的叶节点
    *

3.3.1 B+树插入算法

  • index Page上面存放了，子页的第一个节点
    在这里插入图片描述
    实例1
    在这里插入图片描述实例2

  • 插入70时，发现left page已满，index page没有满
    在这里插入图片描述

  • 拆分left page，将其中间节点放在index page，小于中间节点的放在左边，大于中间节点的，放在右边

在这里插入图片描述

实例3

  • 再插入95时，发现left page和index page已满，需要进行两次拆分

在这里插入图片描述
旋转

  • 如果兄弟没有满，可以优先将数据移到兄弟节点，减少分页
    在这里插入图片描述

3.3.2 B+树删除操作

  • 使用填充因子控制树的删除变化，50%是其最小值
    在这里插入图片描述

实例1
在这里插入图片描述

实例2

  • 删除25时，由于25在index page，删除后小于50%，将25的兄弟28补充到原来25的位置
    在这里插入图片描述实例3
  • 删除60时，此时leftpage仅剩65，所以需要将65和并到兄弟节点，同时合并index page
    在这里插入图片描述

3.5 B树和B+树的区别

B树

  • 所有的节点都会存储数据
  • 父节点当中的索引不会出现在子节点中
    在这里插入图片描述

B+树

  • 只在叶子节点存储数据
  • 父节点当中的索引会出现在子节点中
  • 叶子节点中的最后一个索引会指向下一个叶子节点的第一个索引

在这里插入图片描述

B+树相对于B树有一些自己的优势，可以归结为下面几点。

  • 单一节点存储的元素更多，使得查询的IO次数更少，所以也就使得它更适合做为数据库MySQL的底层数据结构了。
  • 所有的查询都要查找到叶子节点，查询性能是稳定的，而B树，每个节点都可以查找到数据，所以不稳定。
  • 所有的叶子节点形成了一个有序链表，更加便于查找。

4. B+树索引

  • 分为聚集索引和辅助聚集索引
  • 不需要再进行全表查找，对树进行查找即可，速度很快
  • 是有序的，可以用于排序和分组

4.1 聚集索引(主键索引)

  • 按照主键构造B+数，页节点放的是完整行数据
  • 一个表只能有一个（如果没有主键，就会自动创建一个rowid作为主键索引）
  • 对主键的排序查找和范围查询速度特别快，如查询前十条数据和查询大于5的数据

为什么建议使用主键索引？

  • 表的存储结构： 表空间 段（索引 = 索引段 + 数据段） 区 = 1M = 64 页 页（16K，叶子节点）
  • 非自增的情况下，插入数据其他需要移动位置、页分裂、B+树结构的调整的、由于无法判断数据放在哪里，磁盘利用率很低，如插入一条ID为250的数据
    在这里插入图片描述
  • 自增的好处是：磁盘利用率高，不会导致其他索引的移动

为什么使用整型做索引而不是字符串？

  • 字符串的空间太大，浪费了大量空间，导致B+树出度变小
  • 整型的空间占的小
  • ·方便排序·

4.2 辅助索引（非主键索引）

  • 子节点不包括全部行数据
  • 包含的是一个书签，告诉InnoDB引擎去哪里找数据，对应的就是聚集索引的主键

为什么只存储主键？

  • 空间上：存储多余的数据浪费磁盘空间
  • 时间上：对索引的更新更慢

MyISAM存储的是磁盘地址

  • MyISAM生成三个文件：user.myi 索引文件、user.myd数据文件 、user.frm数据结构类型。
  • 默认没用生成主键的索引树，所以就没有在索引中存储地址

4.3 索引的管理

  • ALTER TABLE
alter table  表名  add [index/key]  索引名 (列名)
alter table  表名   drop   [index/key]  索引名

  • CREATE /DROP INDEX

  • 对于非聚集索引，创建索引时会对表加一个S锁，此时只能读数据

4.3 B+树索引的使用

  • 如果数据选择性很高，且只取其中一部分时，即身份证号，电话号等，可以加索引，但是像性别，地区等，不建议加索引
  • 如果高选择性，但是要取其中大部分数据时，不建议使用
  • 优化器在判断是否返回的行数很多，如果很多，自动不使用索引
  • 如果查询经常使用 where xxx = xxx and xxx =xxx 时，可以使用联合索引
  • 联合索引会自动对每个列排好序

4.4 B+树索引和红黑树的比较

更少的查找次数
查找速度跟树h有关

  • 红黑树的兄弟节点最多一个，所以H很高
  • B+树兄弟节点可以有多个，多以H很低

可以利用磁盘的预读特性
磁盘读取数据是，会把相邻节点数据提前载入，B+树每个节点存储着不止一个数据，磁盘读取速度快

5 哈希索引

  • InnoDB的自适应哈希索引使用的散列表

  • 当某些索引值被使用的比较频繁，会在B+树索引之上建立一个哈希索引

特点：

  • 查询速度是O(1)的，但是失去了有序性
  • 无法用于排序和分组
  • 只能进行精确查找（等值查找），不能用于部分查找和范围查找

6. 全文索引

  • MySIAM索引支持全文索引，用于查找关键词，同时不单单进行等值匹配，还会根据出现的频率进行匹配

  • InnoDB在MySQL 5.6.4以后的版本支持

7. 空间数据索引

  • MyISAM支持的索引
  • 用来保存地理位置数据，如经纬度
Xzzz2020
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
详解MySQL索引原理以及优化
09-09
在本篇文章中小编给大家整理了关于MySQL索引原理以及优化的相关知识点以及实例代码，需要的朋友们参考下。
几种索引算法
最新发布
weixin_45517239的博客
01-07 397
*平衡二叉树是一种特殊的二叉树，左右子树高度差不超过1，且每个子树都是平衡二叉树。**平衡二叉树的常用算法有红黑树、AVL、TREAP、伸展树、左偏树等。它在插入和删除的时候会通过特定算法自旋转来保持平衡，这种旋转比较耗时，因此比较适合插入和删除不是很多但查找多的情景。如上图，像上面索引值为30的行记录，mysql先把根节点加载到内存中通过折半查找法等算法快速定位到30所在的二级节点，然后把该二级节点的数据加载到内存中快速定位到30所在的叶子节点，进而再次通过折半查找法找到30的行记录。
经典索引算法
Leiroy的博客
04-11 615
索引算法 树索引 1.kd-tree KNN算法的核心是寻找待测样本在训练样本集中的k个近邻，如果训练样本集过大，则传统的遍历全样本寻找k近邻的方式将导致性能的急剧下降。 kd-tree以空间换时间，**利用训练样本集中的样本点，沿各维度依次对k维空间进行划分，建立二叉树，**利用分治思想大大提高算法搜索效率。我们知道，二分查找的算法复杂度是[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uGjdwiNq-1649642189635)(https://math.ji
MySQL各种索引算法
洱涷zZ的博客
05-24 4028
Mysql各种索引算法 ​ --洱涷 ​ 首先梳理一下当我们在数据库中输入一个SQL进行查询时，DBMS（数据库管理系统）所经历的流程： 对sql语法进行校验，看齐是否符合sql语法规则 对合法的语句进行语义检查，即根据数据字典中有关模式定义检查数据库对应是否有效 查询优化，在DBMS中每一个查询都有许多可以选择的执行策略和操作算法，查询优化即选择其中一个较高效的去执行 根据上一步优化器得到的执行策略生成查询执行计划，有代码生成器生成执行这个查询计划的代码，
关于数据库索引设计的几个常用算法
热门推荐
云计算、分布式架构、K8S、大数据、机器学习、搜索、推荐、广告
12-23 1万+
B+、B- Tree(mysql,oracle,mongodb)      主要用在关系数据库的索引中，如oracle，mysql innodb；mongodb中的索引也是B-树实现的；还有HBase中HFile中的DataBlock的索引等等。      动态查找树主要有：二叉查找树（Binary Search Tree），平衡二叉查找树（Balanced Binary Search Tre
74. 索引的算法
zhu_fangyuan的博客
12-07 981
索引的算法： 索引的算法有 BTree 算法和 Hash 算法 BTree 算法 BTree 是最常用的 mysql 数据库索引算法，也是 mysql 默认的算法。因为它不仅可以被用在=，>，>=，<= 和 between 这些比较操作符上而且还可以用于 like 操作符。只要它的查询条件是一个不以通配符开头的变量 Hash 算法 Hash 索引只能用于对等比较，例如=，<=>（相当于 = ）操作符。由于一次定位数据，不像BTree 索引需要从根节点到枝节点，最后才能访问
MySQL索引详解大全
02-01
索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度索引的优点是可以提高检索数据的速度索引的缺点是创建和维护索引需要耗费时间索引可以提高查询速度，会减慢写入速度1.普通索引2.唯一索引3.全文...
详解mysql索引总结----mysql索引类型以及创建
09-09
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。这篇文章主要介绍了详解mysql索引总结----mysql索引类型以及创建，有兴趣的可以了解一下。
详解mysql索引总结—-mysql索引类型以及创建
01-19
关于MySQL索引的好处，如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能...
MySQL底层索引与算法
Navigator_H的博客
05-24 179
MySQL底层索引与算法 索引数据结构 索引是帮助MySQL高效获取数据的排好序的数据结构 二叉树： 如果索引的结构使用二叉树对col2lie建立索引，索引结构如上图右侧所示。 比如select * from table where col2=89； 此时如果不适用索引查询则会逐行查询遍历6次，才能查到对应的值，如果使用索引，则查询2次，便可查询到对应的数据；效率明显提高 但是如果对于col1之类逐行递增的数据，二叉树形成的数据结构如图所示： 此时如果执行SQL select * from table
索引几种算法
friends99的博客
04-20 5470
数据库mysql索引：使用B树索引下面介绍一下B树索引：Lucene全文检索使用：二分查找下面介绍一下二分查找：二分查找也称折半查找（Binary Search），它是一种效率较高的查找方法。但是，折半查找要求线性表必须采用顺序存储结构，而且表中元素按关键字有序排列；1、时间复杂度：因为每次的查找都会比上一次少一半的范围，时间复杂度为O(log n).2、二分法必须事先经过排序，且数据量必须直接在...
索引算法
lucyxu107的博客
07-25 2883
 关于B+树数据结构 ①InnoDB存储引擎支持两种常见的索引。 一种是B+树，一种是哈希。B+树中的B代表的意思不是二叉（binary），而是平衡（balance），因为B+树最早是从平衡二叉树演化来的，但是B+树又不是一个平衡二叉树。 同时，B+树索引并不能找到一个给定键值的具体行。B+树索引只能找到的是被查找数据行所在的页。然后数据库通过把页读入内存，再在内存中进行查找，最后得到
mysql idataparameter_MySQL技术内幕——InnoDB存储引擎笔记
weixin_39625782的博客
01-27 185
第一章 MySQL体系结构和存储引擎1.1 定义数据库和实例数据库 ：物理操作系统文件或其他形式类型文件的集合实例 ： MySQL数据库由后台线程以及一个共享内存区组成。Mysql数据库实例在系统上的表现就是一个进程1.2 Mysql体系接口连接池组件管理服务和工具组件SQL接口组件查询分析器组件优化器组件缓冲组件插件时存储引擎物理文件1.3 InnoBD存储引擎InnoDB存储引擎支持事务，其设...
索引算法概览
CHIERYU的专栏
03-29 930
索引的定义索引技术是通过有结构、无结构的数据以一定规则存储在相应介质上，并同时提供一种访问方式的技术。从定义中可以看出，索引技术的核心是关于如何存储与访问信息，当在实际问题中面临选择何种索引技术或工具时，首先需要了解数据量的大小、数据的格式，是即时访问还是批量查询等等，有时还需要结合存储介质，比如是否使用缓存或中间缓存文件。分类本文给出的分类参照文献[1]，并补充了自己的理解，分类层次结构如图所示。
MySQL索引的数据结构及算法原理（BTree索引）
u013514816的博客
07-07 5120
    MySQL支持多种存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，哈希索引，全文索引等等。1 数据结构及算法基础1.1 索引的本质    MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。也就是说索引是数据结构    数据库查询是数据库的最主要功能之一。数据本身的组织结构不可能完全满足各种数据结构...
数据库索引算法的优缺点
xusanyao的博客
01-10 1354
当前测试的版本是Mysql 5.5.25只有BTree和Hash两种索引类型，默认为BTree。Oracle或其他类型数据库中会有Bitmap索引（位图索引），这里作为比较也一起提供。  BTree索引 BTree（多路搜索树，并不是二叉的）是一种常见的数据结构。使用BTree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。按照翻译，B 通常认为是Balance的简称。这
《Mysql必知必会》笔记---重要部分
xzzz2020的博客
04-07 717
目录1.使用数据处理函数2. 汇总数据 1.使用数据处理函数 文本处理函数 soundex用来匹配发音 日期和时间处理函数 使用where语句进行匹配时，日期默认为"yyyy-mm-dd"，即2015-09-01 若数据中包含具体的时间值，为了可以匹配某一天，可以使用Date函数，只获取日期部分 select cust_id, order_num from order where Da...
【详解】MySQL高级特性
xzzz2020的博客
04-12 635
目录1. 分区表1.1 原理1.2 分区表类型1.3 分区的作用1.4 分区的问题2. 查询缓存 1. 分区表 分区表是一个独立的表，由多个子表组成 分区表是对底层表的封装，索引也只是每个分区的索引而不是全局索引 作用 表特别大的时候，其中只有一部分是热点数据，其他的都是历史数据 分区表的数据更容易维护，可以单独对一个分区执行一些操作，如删除、优化、检查、修复、备份等。 分区表分布在不同物理...
【详解】MySQL的InnoDB存储引擎
xzzz2020的博客
04-09 565
目录1. InnoDB存储引擎体系架构内存2 master thread2.1 源码分析2.1.1 主循环（loop）2.1.2 background loop2.2 master thread潜在的问题3. 关键特性3.1 插入缓存3.2 两次写3.3 自适应哈希索引4. InnoDB Plugin 1. InnoDB存储引擎体系架构 内存块： 维护所有进程/线程需要访问的多个内部数据结构 ...
mysql 索引下推详解
09-16
MySQL索引下推是一种优化查询性能的技术。传统的索引使用方法是首先通过索引找到满足条件的记录，然后再对这些记录进行进一步的过滤。而索引下推则是在进行索引查询时，将过滤条件应用于索引的过程中，减少了需要...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值