Mysql的高级特性之索引、B-TREE、B+TREE

最新推荐文章于 2022-11-10 18:16:07 发布

你看星星很亮

最新推荐文章于 2022-11-10 18:16:07 发布

阅读量222

点赞数

分类专栏： mysql高级文章标签： mysql 索引数据结构

本文链接：https://blog.csdn.net/weixin_56320090/article/details/116998557

版权

mysql高级专栏收录该内容

4 篇文章 0 订阅

订阅专栏

mysql高级特性：索引

1.索引的定义：

mysql官方对索引的定义为：索引（index）是帮助Mysql高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种放肆引用（指向数据），这样就可以在这些数据结构上实现高级的查找算法，这种数据结构就是索引。如下的示意图所示

左边是数据表，一共有两列七条数据，最左边的是数据记录的物理地址如“0x07”就是物理地址。（逻辑上相邻的两条数据在磁盘上不一定就是相邻的）。为了加快Col2列的查找，可以维护一个如右图所示的二叉查找数<–这边是打个比喻。每个节点分别包含索引键值和一个指向对应的数据记录物理地址值的指针，这样就库运用二叉查找快速的获取对应的数据。

注意：一般来说索引本身也很大，不可能全部存储到内存中，因此索引往往以索引文件的形式存储到磁盘上。索引是数据库中用来提高性能的最佳工具。

2.索引优势劣势

优势

类似于书籍的目录索引，提高数据的检索效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

1.实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占空间的。
2.虽然索引大大的提高了查询效率，同时也降低了更新表的速度，如对表进行Inster、uodate、delete。因为更新表时，Mysql不仅要保存数据，还要保存索引文件每次更新添加了索引列的字段，都会调整因为更新带来的键值变化的索引信息。

3.索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

对查询频次较高，且数据量比较大的表建立索引。
索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
使用唯一索引，区分度越高，使用索引的效率越高。
索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

4.索引的结构

索引实在Mysql的存储引擎中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都是不一定相同到额，也不是所有的存储引擎都支持所有的索引类型的。Mysql目前提供了一下四种索引：

BTREE索引（B-TREE）：最常见的索引类型，大部分都支持B树索引。
HASH索引：只有Memory引擎支持，使用简单
R-TREE索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常很少使用。
FULL-text（全文索引）：全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持
我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为索引。

引擎是基于表实现的
在数据库中可视化也是可以看的，这边我使用的是NAV，打开一个数据库–>打开表–>右击–>设计表–>索引就可以查看此表的索引。也可以在–>选项中查看引擎类型
在这里插入图片描述

5.索引分类

这边以以下库为例：
创建名为 demo_01的库，在库中创建 city、country两张表。

create database demo_01 default charset=utf8mb4;

use demo_01;

CREATE TABLE `city` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `city_name` varchar(50) NOT NULL,
  `country_id` int(11) NOT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `country` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(100) NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);
insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);

insert into `country` (`country_id`, `country_name`) values(1,'China');
insert into `country` (`country_id`, `country_name`) values(2,'America');
insert into `country` (`country_id`, `country_name`) values(3,'Japan');
insert into `country` (`country_id`, `country_name`) values(4,'UK');

创建索引

**创建索引语句：

create index<create index: 创建索引> index_city_name<index_city_name : 索引名> on <on 在哪里创建> city(city_name)<city(city_name) 为city表中的city_name字段创建>**

1）单值索引：即一个索引只包含单个列，一个表可以有多个单列索引：这里为city的city_name字段创建名为index_city_name单值索引

create index index_city_name on city(city_name)

2）唯一索引：索引列的值必须唯一A（就像表中的主键，自动就为主见列创建唯一索引），但是可以有空值

3)复合索引：即一个索引包含多个列。这里为city的city_id和city_name字段创建名为index_city_id_name的复合索引。

create index index_city_id_name on city(city_id，city_name)
就相当于
对city_id创建索引 ;
对city_id, city_name创建了索引 ;

这里值得注意的就是我们定义的如果是复合索引，那么我们在查找的时候就要遵循我们定义索引的顺序，要不然会失效。
在上边语句中，我们定义的顺序是先city_id 在city_name 就像是上楼一样， city_id是第一层楼，我们只能先上c了ity_id这第一层楼才能去尝试上第二层楼city_name。

查看索引

查看city表中的所有索引

show index from city<表名>

删除索引

删除city表中的名为 index_city_name的索引。

drop index index_city_name on city ;

B+TREE 结构

在前文我们说过，Mysql默认就是采用InnoDB引擎，默认采用B+TREE数据结构存储。那么B+TREE到底是什么呢？
B+TREE是一种特殊的B-TREE（B 杠 TREE ，可以不是 B 减树哦）。

B-TREE

所以在这里我们要先介绍一下B-TREE

BTree又叫多路平衡搜索树

一颗m叉（就是一个节点下有几个分支）的BTREE的特征如下。

1.树中每个节点最多包含m个孩子（此B-TREE是几叉树那么他们的节点下最多只有几个节点\分支）
2.除根节点与叶子节点外，每个节点至少有【celi(m/2)】（celi方法就是想上取整）个孩子
3.若根节点不是叶子节点，则至少有两个孩子。
4.所以的叶子节点都在同一层
5.（最重要的）每个非叶子节点由n个key（元素）与n+1个指针组成，其中【celi(m/2)-1<=n<=m-1】

看的懵逼，很正常，下面我们举一个案例看一下

以3叉BTree为例，key的数量：公式推导[ceil(m/2)-1] <= n <= m-1。所以 1 <= n <=2 。当n>2时，中间节点分裂到父节点，两边节点分裂。

插入1-5这5个数据。在树中，比根节点大的去右边，小的去左边

插入前两个数据1、2 。此时套用第五条特性，每个非叶子节点由1-2个key与2-3个指针组成，如图1-1所示所以此时是满足的
：图1-1
插入第三个元素 3，如图1-2所示，插入第三个数据后此节点中的元素超过最大个数，也就是我们使用公式算出的n的最大值2，所以中间的元素向上分裂成为父节点，左右两边的值向两边分裂，成为子节点
：图1-2
插入第四个元素4，此时套用第五条特性，每个非叶子节点由1-2个key与2-3个指针组成，如图1-3所示所以此时是满足的，不用进行改变直接插入。
：图1-3
插入第五个元素5，此时3-4节点增加了一个元素，超出了最大个数，所以中间的元素（004）向上分裂，003和005向两边分裂成为2-4下边的子节点。如图1-4
：图1-4
插入第六个元素6，此时套用第五条特性，每个非叶子节点由1-2个key与2-3个指针组成，如图1-5所示所以此时是满足的，不用进行改变直接插入。
：图1-5
插入第7个元素7，此时5-6节点增加了一个元素，超出了最大个数，所以中间的元素（6）向上分裂，005和007向左右分裂，变成002-004-006下的字节点，如图1-6所示，同时父节点002-004-006节点的元素也超出了最大个数，所以004向上分裂，成为父节点，004和006向左右两边分裂变成006的子字节，至此，变化完成，如图1-7所示
：图1-6
：图1-7

我这边有个网站，测试B-TREE的新增等变化
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B+TREE

一个m阶的B+树具有如下几个特征：
1.有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
3.所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。
B+Tree为BTree的变种，B+Tree与BTree的区别为：
1). n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。
2). B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。
3). 所有的非叶子节点都可以看作是key的索引部分。