mysql索引理解

要理解的问题

  • 1.什么是索引
  • 2.为什么要用索引
  • 3.索引实现的方式
  • 4.BTree理解
  • 5.B+ Tree理解
  • 6.B+ Tree脱颖而出的原因
  • 7.聚合索引实现的原则:最左前缀原则的理解
  • 8.索引sql分类以及创建
  • 9.索引失效的情况
  • 10.后记

1.什么是索引

在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构.是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的,它是由数据页面以外的索引页面组成的,每个索引页面中的行都会含有逻辑指针,以便加速检索物理数据。

重点:排好序的存储结构
注:(图暂时都没画,因为突然自己画图软件不会用)

2. 为什么要用索引

col1col2
1020
820
1120
620

既然提到为什么需要索引,那么我们就先来说下没有索引的查找方式把。
没有索引的时候,数据库表查找数据的时候采用的是顺序查找,至到找到正确的位置,这个时间复杂度也就是O(n)。
而加入了索引之后,就会根据索引去查找,然后找到对应索引的地址值,去找到信息。以二叉树为例子进行举例。
算法结构可视化网址
在这里插入图片描述
你之前查找6的时候第四次才能查到,而现在第三次就可以查到。使用二叉树的话时间复杂度就是O(log n)
注:
二叉查找树(binary search tree , BST)或者是一棵空树;或者是具有以下性质的二叉树:
⑴ 若它的左子树不空,则其左子树中所有结点的值不大于根结点的值;
⑵ 若它的右子树不空,则其右子树中所有结点的值不小于根结点的值;
⑶ 它的左、右子树都是二叉查找树。
对于大数据量的表使用索引之后,查询效率会有显著的提升。

3.索引实现的方式

可以打开navicat,然后找到一个表,鼠标右键点击,然后选择设计表,点击索引
在这里插入图片描述
查看索引方法,可以看到有HASH和BTREE,那么又有一个问题出现了。
为什么不使用二叉树呢?
我们知道二叉树有一些性质:
1.在二叉树的第i层上最多有 2i个结点。
2.高度为h的二叉树至多有 2 h+1-1 个结点。
3.对任何一棵二叉树T,如果其终端结点数为n0,度为 2 的结点数为n2,则n0 = n2
4. n 个结点的完全二叉树的高度为 logn 。
5. 含有 n≥1 个结点的二叉树的高度至多为 n-1;高度至少为 log n。
6. 如果对一棵有 n 个结点的完全二叉树的结点进行编号,则对任一结点 i(1≤i ≤n), 有 ⑴ 如果 i=1,则结点 i 是二叉树的根,无双亲;如果 i>1,则其双亲结点 PARENT(i)是
结点 ⎣ 2/i ⎦。 ⑵ 如果 2i>n,则结点 i 无左孩子;否则其左孩子是结点 2i。 ⑶ 如果 2i+1>n,则结点 i 无右孩子;否则其右孩子是结点 2i+1。
在这里我们就看一下有n个节点的二叉树,其存储高度为log n,每个节点要存放一块内存,然后我们看一下每块内存的大小,这个在mysql 中都有定义的
在这里插入图片描述
我们可以看到就是16KB,如果我们一个节点一个16kb的话,这样对于千万数量级的数据来说用二叉树的话有两个不便之处
1.所需内存过大
2.树的高度太大,查找次数太多,速度变慢。
因为这些原因就忽略掉了二叉树这种存储结构,使用B Tree、B+Tree、HASH表方式。
注:
系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的,位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么。
InnoDB存储引擎中有页(Page)的概念,页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB,可通过参数innodb_page_size将页的大小设置为4K、8K、16K
而系统一个磁盘块的存储空间往往没有这么大,因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位,在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置,这将会减少磁盘I/O次数,提高查询效率。
B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree,首先定义一条记录为一个二元组[key, data] ,key为记录的键值,对应表中的主键值,data为一行记录中除主键外的数据。对于不同的记录,key值互不相同。
一棵m阶的B-Tree有如下特性:

  1. 每个节点最多有m个孩子。
  2. 除了根节点和叶子节点外,其它每个节点至少有Ceil(m/2)个孩子。
  3. 若根节点不是叶子节点,则至少有2个孩子
  4. 所有叶子节点都在同一层,且不包含其它关键字信息
  5. 每个非终端节点包含n个关键字信息(P0,P1,…Pn, k1,…kn)
  6. 关键字的个数n满足:ceil(m/2)-1 <= n <= m-1
  7. ki(i=1,…n)为关键字,且关键字升序排序。
  8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki,但都大于k(i-1)

4.BTree理解

BTree(平衡多路查找树),是一种常见的数据结构。
对于一棵m阶B-tree,每个结点至多可以拥有m个子结点。各结点的关键字和可以拥有的子结点数都有限制,规定m阶B-tree中,根结点至少有2个子结点,除非根结点为叶子节点,相应的,根结点中关键字的个数为1m–m-1;非根结点至少有[m/2]([],向上取整)个子结点,相应的,关键字个数为[m/2]-1~m-1。
B-Tree有如下特性:
1、关键字集合分布在整棵树中;
2、任何一个关键字出现且只出现在一个结点中;
3、搜索有可能在非叶子结点结束;
4、其搜索性能等价于在关键字全集内做一次二分查找;
5、自动层次控制;
B-tree中,每个结点包含:
1、本结点所含关键字的个数;
2、指向父结点的指针;
3、关键字;
4、指向子结点的指针;
在这里插入图片描述

B+ Tree理解

在这里插入图片描述
BTREE和B+Tree的区别:
(1)所有关键字存储在叶子节点,非叶子节点不存储真正的data
(2)为所有叶子节点增加了一个链指针
B+Tree是Btree的进阶,顺序指针的好处就在于方便我们进行范围查找。

6.B+ Tree脱颖而出的原因

1.二分查找
2.顺序指针
B+ Tree树我们可以举一个例子,存储引擎中一个页是14kB,这也就是我们B+Tree树上每个节点的大小,假设一个索引的key加上指针为14B,那么一个页里面可以存放大概1170个索引,然后每个结点又有1170个指针指向子节点,每个子节点又同样包含1170个索引,这样在B+ Tree树的第二层就已经存储了11701170=1368900个索引,假设第三层是叶节点,也就是存放的为索引+指针+data,就按1KB来算,这样也能存储了大概136890016=21902400,这也就是说一千万的数据量我就需要查找三次就可以查找到对应的值,当然这个只是理论上的计算,具体的设计还要看怎么配置。那么就会有疑问了,如果我们使用HASH表的话不是更快吗?
HASH表,会计算出来每个key的hash值,然后存放到链表里面,这个我们查找的时候根据hsah值一下就可以查找到对应的链表,但是为什么我们不使用HASH表呢?这个时候就要看顺序指针,因为我们在查找的过程中除了精确查找之外,还存在范围查找,hash表这个时候就比不上B+TREE了,因为B+Tree存在顺序指针,直接可以顺序的查找下去

7.聚合索引实现的原则:最左前缀原则的理解

前面我们说的都是单个索引的情况,但是在实际使用中,我们会存在多个索引也就是聚合索引,那么聚合索引就要理解最左前缀原则。单索引可以理解为聚合索引为1的情况,mysql建立多列索引(联合索引)有最左前缀的原则,即最左优先,如:
如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引;
如果有一个3列索引(col1,col2,col3),则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引;
1、b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,b+树是按照从左到右的顺序来建立搜索树的,比如当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex,最后得到检索的数据;但当(20,F)这样的没有name的数据来的时候,b+树就不知道第一步该查哪个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪里查询。
2、比如当(张三,F)这样的数据来检索时,b+树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是非常重要的性质,即索引的最左匹配特性。(这种情况无法用到联合索引)
关于最左前缀的使用,有下面两条说明:
0.最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
2.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式

限时福利1:原价 129 元,最后2天仅需 69 元!后天涨价至98元 限时福利2:购课进答疑群专享柳峰(刘运强)老师答疑服务 限时福利3:购课添加助教领取价值 800 元的编程大礼包 为什么需要掌握高性能的MySQL实战? 由于互联网产品用户量大、高并发请求场景多,因此对MySQL的性能、可用性、扩展性都提出了很高的要求。使用MySQL解决大量数据以及高并发请求已经是程序员的必备技能,也是衡量一个程序员能力和薪资的标准之一。 为了让大家快速系统了解高性能MySQL核心知识全貌,我为你总结了「高性能 MySQL 知识框架图」,帮你梳理学习重点,建议收藏! 【课程设计】 课程分为四大篇章,将为你建立完整的 MySQL 知识体系,同时将重点讲解 MySQL 底层运行原理、数据库的性能调优、高并发、海量业务处理、面试解析等。 一、性能优化篇: 主要包括经典 MySQL 问题剖析、索引底层原理和事务与锁机制。通过深入理解 MySQL索引结构 B+Tree ,学员能够从根本上弄懂为什么有些 SQL 走索引、有些不走索引,从而彻底掌握索引的使用和优化技巧,能够避开很多实战中遇到的“坑”。 二、MySQL 8.0新特性篇: 主要包括窗口函数和通用表表达式。企业中的许多报表统计需求,如果不采用窗口函数,用普通的 SQL 语句是很难实现的。 三、高性能架构篇: 主要包括主从复制和读写分离。在企业的生产环境中,很少采用单台MySQL节点的情况,因为一旦单个节点发生故障,整个系统都不可用,后果往往不堪设想,因此掌握高可用架构的实现是非常有必要的。 四、面试篇: 程序员获得工作的第一步,就是高效的准备面试,面试篇主要从知识点回顾总结的角度出发,结合程序员面试高频MySQL问题精讲精练,帮助程序员吊打面试官,获得心仪的工作机会。
©️2020 CSDN 皮肤主题: 技术工厂 设计师:CSDN官方博客 返回首页