索引原理

最新推荐文章于 2022-10-31 11:52:36 发布

此男子淡漠

最新推荐文章于 2022-10-31 11:52:36 发布

阅读量950

点赞数 2

分类专栏： java开发记录文章标签：数据库mysql 数据库索引

本文链接：https://blog.csdn.net/weixin_44195647/article/details/95993432

版权

java开发记录专栏收录该内容

28 篇文章 0 订阅

订阅专栏

索引简述:

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

我们可以用大白话理解一下:

假如我们一般习惯于使用模糊查询,那么如果在数据量千万级别甚至以上的基础下我们如果不进行优化,那么查询速度会大大降低

举一个很鲜明的例子:

我们小时候都用过字典如果我们不使用目录去进行查询,我们想找到一个字在多少页 你需要从前向后翻,想想一下字典有多厚~但是我们如果利用目录去进行查询,那么就会很快的查询到具体字的位置.我们可以把字典中的目录理解为索引

那么如何深入的去了解索引呢?

我们先去了解一下如何给数据库表添加索引:
①在创建表的时候添加索引:

CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    INDEX [索引名称] (要设置索引的列名(列的长度))  
);

②在创建表之后添加索引:

ALTER TABLE 表名 ADD [UNIQUE] INDEX 索引名称(表中列名);
或者
CREATE INDEX 索引名称 ON 表名(列名);

注意点:

索引是需要占据磁盘空间的,在创建索引的同时需要注意是否空间够用.
创建索引时需要对表进行加锁,因此实际操作中需要在业务休闲时进行.

根据索引查询:

具体查询：
SELECT * FROM table_name WHERE column_1=column_2;(为column_1建立了索引)
 
或者模糊查询
SELECT * FROM table_name WHERE column_1 LIKE '%三'
SELECT * FROM table_name WHERE column_1 LIKE '三%'
SELECT * FROM table_name WHERE column_1 LIKE '%三%'
 
SELECT * FROM table_name WHERE column_1 LIKE '_好_'
 
如果要表示在字符串中既有A又有B，那么查询语句为：
SELECT * FROM table_name WHERE column_1 LIKE '%A%' AND column_1 LIKE '%B%';
 
SELECT * FROM table_name WHERE column_1 LIKE '[张李王]三';  //表示column_1中有匹配张三、李三、王三的都可以
SELECT * FROM table_name WHERE column_1 LIKE '[^张李王]三';  //表示column_1中有匹配除了张三、李三、王三的其他三都可以
 
//在模糊查询中，%表示任意0个或多个字符；_表示任意单个字符（有且仅有），通常用来限制字符串长度;[]表示其中的某一个字符；[^]表示除了其中的字符的所有字符
 
或者在全文索引中模糊查询
SELECT * FROM table_name WHERE MATCH(content) AGAINST('word1','word2',...);

删除索引

DROP INDEX my_index ON tablename；
或者
ALTER TABLE table_name DROP INDEX index_name;

查看表中的索引

SHOW INDEX FROM 表名

查看查询语句使用索引的情况

//explain 加查询语句
explain SELECT * FROM table_name WHERE column_1='123';

索引的分类

常见的索引类型:主键索引 唯一索引 普通索引 全文索引 组合索引

1、主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；

2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值

ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；

3、普通索引：用表中的普通列构建的索引，没有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('col')；

4、全文索引：用大文本对象的列构建的索引（下一部分会讲解）

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；

5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

针对于组合索引,遵循"最左前缀原则",把最常用的作为检索或排序的列放在最左,依次递减,组合索引相当于建立了col1,col2,col1col2col3 三个索引,而col2或者col3是不能使用索引的.
在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；

表中使用col1的前四个字符和col2的前三个字符作为索引.

索引的实现原理

mysql支持诸多存储引擎,而每种引擎所对索引的支持也各不相同,因此mysql数据库支持多种索引类型.

说到了索引实现原理,那么就不得不说索引实现原理所依赖的算法(二分查找法).

二分查找法(折半查找)

在这里插入图片描述
要实现此功能需要两个前提:

线性表必须采用顺序存储结构
表中元素按关键字有序排列

查找过程

首先，假设表中元素是按升序排列，将表中间位置记录的关键字与查找关键字比较，如果两者相等，则查找成功；否则利用中间位置记录将表分成前、后两个子表，如果中间位置记录的关键字大于查找关键字，则进一步查找前一子表，否则进一步查找后一子表。重复以上过程，直到找到满足条件的记录，使查找成功，或直到子表不存在为止，此时查找不成功。

比较次数

计算公式： a<log2(下)n(上)<b(a,b,n属于Z+(上))
当顺序表有n个关键字时：
查找失败时，至少比较a次关键字；查找成功时，最多比较关键字次数是b。
注意：a,b,n均为正整数。

代码提示

public static int binarySearch(Integer[] srcArray, int des) {
    //定义初始最小、最大索引
    int start = 0;
    int end = srcArray.length - 1;
    //确保不会出现重复查找，越界
    while (start <= end) {
        //计算出中间索引值
        int middle = (end + start)>>>1 ;//防止溢出
        if (des == srcArray[middle]) {
            return middle;
        //判断下限
        } else if (des < srcArray[middle]) {
            end = middle - 1;
        //判断上限
        } else {
            start = middle + 1;
        }
    }
    //若没有，则返回-1
    return -1;
}

了解二分查找法后你会觉得B树索引以及其扩展很像二分查找~

二叉树示意图

在这里插入图片描述

所有节点最多拥有两个子节点
左边节点的键值小于根的键值,右边节点的键值大于根的键值

注意:

查看上面可以看到数据排列很均匀,但是假如数据本身排列并不是这样均匀呢?
那么我们就会看到不均匀的排列样式.
在这里插入图片描述

那么查找速度跟什么有关系呢?

从二叉树的结构上看,从顶点开始进行查询,如果数据很多,那么二叉树的高度就会很高,加入我们想要查询的那个数据在最下面,那么就会导致查询效率的减小,所以二叉树的查询速度跟二叉树的高度成反比~

由于二叉树当数据排列并不均匀时两边节点高度极度不一致导致出现不稳定的二叉树导致了树结构的检索效率低下,为了解决这个问题引入增加了平衡算法的树结构(Treap、红黑树)

平衡二叉树

在这里插入图片描述
特点:

所有节点最多拥有两个子节点；
左边的子节点要小于当前节点、右边子节点大于当前节点；
树的左右两边的层级数相差不会大于1;

思考:

现在平衡二叉树虽然看似很平衡,但是我们能否继续进行优化进而提升查询效率呢?

引入B树

B树

和平衡二叉树稍有不同的是B树属于多叉树又名平衡多路查找树

在这里插入图片描述
下面提供一个网址用来进行B树的简单模拟

https://www.cs.usfca.edu/~galles/visualization/BST.html

B树思考:是否一个树的节点挂的节点越多越好?

此刻我们需要了解一下磁盘读取数据的过程~

外存储器-磁盘

计算机存储设备一般分为两种：内存储器(main memory)和外存储器(external memory)。内存存取速度快，但容量小，价格昂贵，而且不能长期保存数据(在不通电情况下数据会消失)。

外存储器—磁盘是一种直接存取的存储设备(DASD)。它是以存取时间变化不大为特征的。可以直接存取任何字符组，且容量大、速度较其它外存设备更快。

磁盘的构造

磁盘是一个扁平的圆盘(与电唱机的唱片类似)。盘面上有许多称为磁道的圆圈，数据就记录在这些磁道上。磁盘可以是单片的，也可以是由若干盘片组成的盘组，每一盘片上有两个面。如下图11.3中所示的6片盘组为例，除去最顶端和最底端的外侧面不存储数据之外，一共有10个面可以用来保存信息。
在这里插入图片描述
当磁盘驱动器执行读/写功能时。盘片装在一个主轴上，并绕主轴高速旋转，当磁道在读/写头(又叫磁头) 下通过时，就可以进行数据的读 / 写了。

一般磁盘分为固定头盘(磁头固定)和活动头盘。固定头盘的每一个磁道上都有独立的磁头，它是固定不动的，专门负责这一磁道上数据的读/写。

活动头盘 (如上图)的磁头是可移动的。每一个盘面上只有一个磁头(磁头是双向的，因此正反盘面都能读写)。它可以从该面的一个磁道移动到另一个磁道。所有磁头都装在同一个动臂上，因此不同盘面上的所有磁头都是同时移动的(行动整齐划一)。当盘片绕主轴旋转的时候，磁头与旋转的盘片形成一个圆柱体。各个盘面上半径相同的磁道组成了一个圆柱面，我们称为柱面。因此，柱面的个数也就是盘面上的磁道数。

磁盘的读/写原理和效率

磁盘上数据必须用一个三维地址唯一标示：柱面号、盘面号、块号(磁道上的盘块)。

读/写磁盘上某一指定数据需要下面3个步骤：

(1) 首先移动臂根据柱面号使磁头移动到所需要的柱面上，这一过程被称为定位或查找。

(2) 如上图11.3中所示的6盘组示意图中，所有磁头都定位到了10个盘面的10条磁道上(磁头都是双向的)。这时根据盘面号来确定指定盘面上的磁道。

(3) 盘面确定以后，盘片开始旋转，将指定块号的磁道段移动至磁头下。

经过上面三个步骤，指定数据的存储位置就被找到。这时就可以开始读/写操作了。

访问某一具体信息，由3部分时间组成：

● 查找时间(seek time) Ts: 完成上述步骤(1)所需要的时间。这部分时间代价最高，最大可达到0.1s左右。

● 等待时间(latency time) Tl: 完成上述步骤(3)所需要的时间。由于盘片绕主轴旋转速度很快，一般为7200转/分(电脑硬盘的性能指标之一, 家用的普通硬盘的转速一般有5400rpm(笔记本)、7200rpm几种)。因此一般旋转一圈大约0.0083s。

● 传输时间(transmission time) Tt: 数据通过系统总线传送到内存的时间，一般传输一个字节(byte)大概0.02us=2*10^(-8)s

磁盘读取数据是以盘块(block)为基本单位的。位于同一盘块中的所有数据都能被一次性全部读取出来。而磁盘IO代价主要花费在查找时间Ts上。因此我们应该尽量将相关信息存放在同一盘块，同一磁道中。或者至少放在同一柱面或相邻柱面上，以求在读/写信息时尽量减少磁头来回移动的次数，避免过多的查找时间Ts。

所以，在大规模数据存储方面，大量数据存储在外存磁盘中，而在外存磁盘中读取/写入块(block)中某数据时，首先需要定位到磁盘中的某块，如何有效地查找磁盘中的数据，需要一种合理高效的外存数据结构，就是下面所要重点阐述的B-tree结构，以及相关的变种结构：B±tree结构和B*-tree结构。

针对于磁盘或者其他存储设备而设计的一种多叉平衡查找树成为B树,相比于红黑树来说很相似但是在降低磁盘I/O操作方面更好一些,许多数据库系统一般使用B树或者B树的各种变形结构.
B树与红黑树最大的不同在于，B树的结点可以有许多子女，从几个到几千个。那为什么又说B树与红黑树很相似呢?因为与红黑树一样，一棵含n个结点的B树的高度也为O（lgn），但可能比一棵红黑树的高度小许多，应为它的分支因子比较大。所以，B树可以在O（logn）时间内，实现各种如插入（insert），删除（delete）等动态集合操作。

如下图所示，即是一棵B树，一棵关键字为英语中辅音字母的B树，现在要从树种查找字母R（包含n[x]个关键字的内结点x，x有n[x]+1]个子女（也就是说，一个内结点x若含有n[x]个关键字，那么x将含有n[x]+1个子女）。所有的叶结点都处于相同的深度，带阴影的结点为查找字母R时要检查的结点）：
在这里插入图片描述
相信，从上图你能轻易的看到，一个内结点x若含有n[x]个关键字，那么x将含有n[x]+1个子女。如含有2个关键字D H的内结点有3个子女，而含有3个关键字Q T X的内结点有4个子女。

用度定义的B树

  针对上面的5点，再阐述下：B树中每一个结点能包含的关键字（如之前上面的D H和Q T X）数有一个上界和下界。这个下界可以用一个称作B树的最小度数（算法导论中文版上译作度数，最小度数即内节点中节点最小孩子数目）m（m>=2）表示。

每个非根的内结点至多有m个子女，每个非根的结点必须至少含有m-1个关键字，如果树是非空的，则根结点至少包含一个关键字；

每个结点可包含至多2m-1个关键字。所以一个内结点至多可有2m个子女。如果一个结点恰好有2m-1个关键字，我们就说这个结点是满的（而稍后介绍的B树作为B树的一种常用变形，B树中要求每个内结点至少为2/3满，而不是像这里的B树所要求的至少半满）；

当关键字数m=2（t=2的意思是，mmin=2，m可以>=2）时的B树是最简单的（有很多人会因此误认为B树就是二叉查找树，但二叉查找树就是二叉查找树，B树就是B树，B树是一棵含有m（m>=2）个关键字的平衡多路查找树），此时，每个内结点可能因此而含有2个、3个或4个子女，亦即一棵2-3-4树，然而在实际中，通常采用大得多的t值。

B树中的每个结点根据实际情况可以包含大量的关键字信息和分支(当然是不能超过磁盘块的大小，根据磁盘驱动(disk drives)的不同，一般块的大小在1k~4k左右)；这样树的深度降低了，这就意味着查找一个元素只要很少结点从外存磁盘中读入内存，很快访问到要查找的数据。如果你看完上面关于B树定义的介绍，思维感觉不够清晰，请继续参阅下文第6小节、B树的插入、删除操作部分。

文件查找的具体过程(涉及磁盘IO操作)

为了简单，这里用少量数据构造一棵3叉树的形式，实际应用中的B树结点中关键字很多的。上面的图中比如根结点，其中17表示一个磁盘文件的文件名；小红方块表示这个17文件内容在硬盘中的存储位置；p1表示指向17左子树的指针。

其结构可以简单定义为：

typedef struct {

/*文件数*/

int  file_num;

/*文件名(key)*/

char * file_name[max_file_num];

/*指向子节点的指针*/

 BTNode * BTptr[max_file_num+1];

 /*文件在硬盘中的存储位置*/

 FILE_HARD_ADDR offset[max_file_num];

}BTNode;

假如每个盘块可以正好存放一个B树的结点（正好存放2个文件名）。那么一个BTNODE结点就代表一个盘块，而子树指针就是存放另外一个盘块的地址。

下面，咱们来模拟下查找文件29的过程：

根据根结点指针找到文件目录的根磁盘块1，将其中的信息导入内存。【磁盘IO操作 1次】

此时内存中有两个文件名17、35和三个存储其他磁盘页面地址的数据。根据算法我们发现：17<29<35，因此我们找到指针p2。

根据p2指针，我们定位到磁盘块3，并将其中的信息导入内存。【磁盘IO操作 2次】

此时内存中有两个文件名26，30和三个存储其他磁盘页面地址的数据。根据算法我们发现：26<29<30，因此我们找到指针p2。

根据p2指针，我们定位到磁盘块8，并将其中的信息导入内存。【磁盘IO操作 3次】

此时内存中有两个文件名28，29。根据算法我们查找到文件名29，并定位了该文件内存的磁盘地址。

分析上面的过程，发现需要3次磁盘IO操作和3次内存查找操作。关于内存中的文件名查找，由于是一个有序表结构，可以利用折半查找提高效率。至于IO操作是影响整个B树查找效率的决定因素。

当然，如果我们使用平衡二叉树的磁盘存储结构来进行查找，磁盘4次，最多5次，而且文件越多，B树比平衡二叉树所用的磁盘IO操作次数将越少，效率也越高。

B树的高度

根据上面的例子我们可以看出，对于辅存做IO读的次数取决于B树的高度。而B树的高度由什么决定的呢？

若B树某一非叶子节点包含N个关键字，则此非叶子节点含有N+1个孩子结点，而所有的叶子结点都在第I层，我们可以得出：

因为根至少有两个孩子，因此第2层至少有两个结点。
除根和叶子外，其它结点至少有┌m/2┐个孩子，
因此在第3层至少有2┌m/2┐个结点，
在第4层至少有2(┌m/2┐^2)个结点，
在第 I 层至少有2*(┌m/2┐^(l-2) )个结点，于是有： N+1 ≥ 2┌m/2┐I-2；
考虑第L层的结点个数为N+1，那么2(┌m/2┐^(l-2)）≤N+1，也就是L层的最少结点数刚好达到N+1个，即： I≤ log┌m/2┐((N+1)/2 )+2；
　　所以

当B树包含N个关键字时，B树的最大高度为l-1（因为计算B树高度时，叶结点所在层不计算在内），即：l - 1 = log┌m/2┐((N+1)/2 )+1。
　　这个B树的高度公式从侧面显示了B树的查找效率是相当高的。

曾在一次面试中被问到，一棵含有N个总关键字数的m阶的B树的最大高度是多少?答曰：log_ceil（m/2）(N+1)/2 + 1 （上面中关于m阶B树的第1点特性已经提到：树中每个结点含有最多含有m个孩子，即m满足：ceil(m/2)<=m<=m。而树中每个结点含孩子数越少，树的高度则越大，故如此）。在2012微软4月份的笔试中也问到了此问题。

此外，还有读者反馈，说上面的B树的高度计算公式与算法导论一书上的不同，而后我特意翻看了算法导论第18章关于B树的高度一节的内容，如下图所示：
在这里插入图片描述

上面内容取自下面文章:https://blog.csdn.net/v_JULY_v/article/details/6530142/

未完待续…

此男子淡漠

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
索引原理

索引简述:索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。我们可以用大白话理解一下:假如我们一般习惯于使用模糊查询,那么如果在数据量千万级别甚至以上的基础下我们如果不进行优化,那么查询速度会大...
复制链接

扫一扫