Mysql索引本质

最新推荐文章于 2022-04-29 22:31:27 发布

有机后浪

最新推荐文章于 2022-04-29 22:31:27 发布

阅读量365

点赞数

分类专栏： MySQL 文章标签：二叉树数据库数据结构 mysql

本文链接：https://blog.csdn.net/key_768/article/details/104895174

版权

MySQL 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

前言

对于Mysql的学习，不能仅仅是学会sql增删改查
还要对数据库优化有一定的认识
前面总结了一些常用的sql命令：Mysql常用命令总结
接下来学习优化

推荐一个数据结构演示网站：Data Structure Visualizations

索引的使用
1.1. 普通索引（NORMAL）
1.2. 唯一索引（UNIQUE）
1.3. 主键索引（PRIMARY KEY）
1.4. 全文索引（FULLTEXT）
1.5. 组合索引
1.6. 空间索引（SPATIAL）
索引的本质
2.1. 二叉树
2.2. 红黑树
2.3. B-Tree
2.4. B+Tree
存储引擎
3.1. MyISAM索引实现
3.2. InnoDB索引实现
Hash
查询优化的理解
5.1. InnoDB表必须要主键
5.2. 整型自增主键
5.3. 指针
总结

索引的使用

说到数据库查询优化，第一反应就是索引

索引就类似与字典的目录页，通过拼音、笔画等排序目录快速的查找到对应的字，通过索引可以快速的查找到相应的数据

实际上索引也是一张表:key-value，key就是索引列的值，value是该行所在磁盘位置的索引或者就是该行的数据

索引有四种类型：普通索引，唯一索引，全文索引，空间索引
在这里插入图片描述
还有索引的延伸：组合索引；特殊的唯一索引：主键索引

普通索引（NORMAL）

最基本的索引，没有任何的限制，加速查询

索引名的规范是index_tableName_name（普通索引以index或ind_开头），索引名的长度不能超过30个字符，可以缩写，当然可以随便设置索引名

建表时创建：

INDEX [index_tableName_name] (columnName(length));

在这里插入图片描述

创建一个index，名字叫index_student_selectId，索引列是id列

int，integer型可以不用设置长度，如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length

修改表结构(添加索引)：

ALTER table tableName ADD INDEX index_tableName_name(columnName);

CREATE INDEX index_tableName_name ON tableName(columnName(length));

在这里插入图片描述

可以看到已经添加了索引：
在这里插入图片描述
删除索引：

DROP INDEX indexName ON tableName;

在这里插入图片描述

唯一索引（UNIQUE）

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
关键字unique

unique [index_tableName_name](columnName(length))

在这里插入图片描述

其他操作都和普通索引类似，将index关键字改为unique关键字

主键索引（PRIMARY KEY）

主键索引是一种特殊的唯一索引，简称主键，一个表只能有一个主键，不允许有空值
由一个或多个列组成，用于唯一性标识数据表中的某一条记录

一般是在建表的时候指定了主键，就是创建主键索引，
在这里插入图片描述

CREATE INDEX不能用来创建主键索引，使用 ALTER TABLE来代替

添加主键索引：

alter table testPrimary add primary key(id);

当唯一索引没有null时，也就是主键索引

全文索引（FULLTEXT）

查找文本中的关键字，而不是直接与索引中的值相比较
通过关键字的匹配来进行查询过滤，类似与like+%，但是在数据量大的情况下全文索引快很多

创建：

#创建表时：
FULLTEXT [name](columnName)

#添加全文索引
create FULLTEXT index indexName on tableName(columnName);
alter table tableName add FULLTEXT index indexName(columnName);

#删除
drop index indexName on tableName;

注意：
MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引

因为版本是5.5,要设置一下存储引擎
在这里插入图片描述

版本不对会报错：
在这里插入图片描述
删除还是用index

两种添加：
在这里插入图片描述

使用全文索引

select * from tableName where match(columnName) against('xxx xxx');

搜索时还要关注最小搜索长度和最大搜索长度
详情可以看这位大佬的详解：

MySQL 之全文索引

组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

组合索引是一种延伸应用，操作都类似，例如创建表时创建组合索引，添加组合索引，删除组合索引等

在这里插入图片描述
student表就存在两个索引了

在这里插入图片描述

空间索引（SPATIAL）

空间索引是指依据空间对象的位置和形状或空间对象之间的某种空间关系按一定的顺序排列的一种数据结构，其中包含空间对象的概要信息，如对象的标识、外接矩形及指向空间对象实体的指针

用的较少，就不研究了

索引的本质

索引是帮助mysql高效获取数据排好序的数据结构

我们知道，数据库的数据都是存放在磁盘上的，而磁盘的IO操作都是很耗时的，如果不存在索引，一个select * 就要大量的磁盘IO，索引能仅通过2-4次磁盘IO就完成查找，所以说索引是高效的，当然索引也是存放在磁盘上的

索引是将数据排好序的数据结构：索引是一种数据结构，将数据排序

我们知道Mysql索引结构是：B+Tree
而索引结构有二叉树、红黑树、hash、BTree、B+Tree ，为什么用B+Tree呢

二叉树

如果是二叉树结构，数据库索引是这样的：
在这里插入图片描述
执行sql语句：

select * from table where Col2=23;

就需要：34 -》22 -》23
这才仅仅几个数据树的深度就为3
而且如果索引列为Col1，二叉树会退化成链表，查找操作就需要更多
在这里插入图片描述

红黑树

红黑树是自平衡的二叉树
它的性质：

每个节点要么是黑色，要么是红色。
根节点是黑色。
每个叶子节点（NIL）是黑色。
每个红色结点的两个子结点一定都是黑色。
任意一结点到每个叶子结点的路径都包含数量相同的黑结点

当某结点同一边存在3个结点时，它能通过自平衡-左旋、右旋、变色将二叉树平衡
在这里插入图片描述

0002结点左旋，变色

在这里插入图片描述

红黑树挺复杂的，可以看30张图带你彻底理解红黑树

红黑树解决了二叉树退化成链表的问题，但仍然是二叉树，对于多数据还是层数较大

B-Tree

B-Tree的改进是让一个结点可以存放多个存储多个数据
（Mysql中默认是16K，也可以自己设置）
在这里插入图片描述

这里度（Degree）设置的最大是3，当3个时自动会分裂成不同结点

所以B-Tree是这样的：
在这里插入图片描述
数据库查询操作的步骤：select * from table where key=49

将第一层结点放入内存，遍历查询
找到key=49就通过data里的数据存放地址找到磁盘中的数据
没找到key=49，通过大与key和小于key中间的指针查询（叶子结点指针为空），key=15和key=56中的指针找到下一个结点，并将结点放入内存，重复第一步

因为一个结点可以放置16K大小的key，下一层又是16K大小的Key，所以B-Tree一般也就2-4层，磁盘IO明显少了

结点放置到内存中遍历，消耗的时间可以忽略

所以B-Tree已经可以比较好的完成数据库查询

B+Tree

B+Tree是B-Tree的一种优化
性质：

非叶子结点不存放data，只存放key（增大degree）
叶子结点不存储指针
所有叶子节点之间都有一个链指针

在这里插入图片描述
每一层结点最大为16K：
通过show global status like "Innodb_page_size";可以查看

Mysql就是使用的B+Tree数据结构构造索引的

存储引擎

存储引擎是指表的存储引擎
在这里插入图片描述

有很多种，最常用的是MyISAM和InnoDB，现在一般都是InnoDB

这两种引擎底层结构：
可以去Mysql存储位置看看

Mysql安装目录下的data文件夹里可以看到都是我们数据库的文件夹
在这里插入图片描述
我们刚才那几个表是在mysqlindex数据库

可以看到这几个文件：

在这里插入图片描述前面把testfull表设置为MyISAM引擎
可以看到有3个testfull开头的文件

*.frm----描述了表的结构，是表结构定义
*.MYI----保存表的索引，I的index的简写
*.MYD----保存了表的数据，D是data的简写
db.opt----用文本编辑器打开，可以看到里面保存的是编码信息

而Innodb引擎则有两个文件
在这里插入图片描述
.ibd文件是.MYI和.MYD的结合，所以InnoDB引擎将索引和数据放一起

MyISAM索引实现

在这里插入图片描述

图中Col1是索引，可以看出Col1的值作为B+树的key，value是当前key所在数据行的地址

InnoDB索引实现

在这里插入图片描述

InnoDB将索引和数据放在一起，MyISAM引擎里data位置在InnoDB中是放置行数据
表数据文件本身就是按照B+Tree组织的一个索引结构文件

聚集索引：叶子结点包含完整的数据记录
像InnoDB结构中，主键索引与数据结合在一起，就是聚集索引
而MyISAM中，索引保存在.MYI中，数据保存在.MYD中，通过索引得到的data是数据地址，就是非聚集索引

Hash

在这里插入图片描述

前面说的B+Tree是最常用的索引方法，但还有一种也会有：Hash

Hash索引方法是通过hash算法将主键转换成一个hashcode，而hashcode与磁盘文件指针有映射关系

在这里插入图片描述
Hash算法看似很方便，但是有很多局限

hash算法产生的HashCode不唯一，数据量越大越容易相同，如果HashCode相同，需要解决hash碰撞问题
处理不了范围查找，HashCode是无序的，当有范围查找时又需要回到最初的办法：扫描磁盘

所以数据库默认的是B+Tree数据结构

查询优化的理解

前面对B+Tree以及InnoDB实现有了一定了解，对一些优化的方式更加明白了

InnoDB表必须要主键

InnoDB表是按B+Tree存储的，必须要有一个主键当做索引

其实我们刚用Mysql时不会使用主键，其实Mysql在底层会帮我们找一个可以唯一标识这个表的数据段rowid

当表中存在一个数字类型的单列主键时， _rowid 其实就是指的是这个主键列
当表中不存在主键但存在一个数字类型的非空唯一列时， _rowid 其实就是指的是对应非空唯一列

InnoDB表中在没有默认主键的情况下会生成一个6字节空间的自动增长主键，可以用select _rowid from tableName来查询

注意以下情况是不存在 _rowid 的

主键列或者非空唯一列的类型不是数字类型
主键是联合主键
唯一列不是非空的

所以说InnoDB表一定要有主键，这是一种优化

整型自增主键

为什么推荐使用整形自增主键？

整型需要的存储空间小：整型和无符号整型:2字节，长整型:4字节；而如果是字符串例如UUID，占的空间明显更大，而一个结点只能存放16K，所以推荐整型
B+Tree结构是以主键大小规定这些结点的位置，整型比较大小方便，如果是UUID采用32位数字组成，编码采用16进制，比大小太麻烦了
使用自增就是为了不用分开大结点，例如上面的一个结点由主键{20,30}组成，自增的话就是在后面增加结点，不自增例如加一个主键为25的结点，就需要将{20,30}分成{20,25}，{30}，有一定开销

指针

在这里插入图片描述
B+Tree叶子结点不同结点之间有一个双向箭头，用于指向前后结点
作用是：用于范围查找
select * from table where col >20;
这个sql语句查找col大于20的数据，找到主键为20的数据，然后通过指针将后续的数据取出，这就是我们需要的结果集

总结

对于查询优化，第一个想到的总是索引，索引是帮助mysql高效获取数据排好序的数据结构
高效在于仅需几次磁盘IO即可完成查询，数据结构是B+Tree
索引有四种：普通索引；唯一索引；全文索引；空间索引；主键索引是特殊的唯一索引（键值不可为空）；组合索引是一种应用，可以将多个列设置为索引
复习了二叉树、红黑树、B-Tree、B+Tree，了解了为什么mysql底层是B+Tree
常用的存储引擎表是：MyISAM表和InnoDB表；MyISAM引擎是非聚集索引，它将索引存放在.MYI中，将数据存放在.MYD中；InnoDB引擎是聚集索引，将索引和数据存放在.ibd文件中，表现在数据结构是data段为整个数据段
Hash索引方法和B+Tree有区别：Hash索引方法是将主键通过hash算法算出hashcode，该hashcode与磁盘文件指针有映射关系，即通过hashcode可以直接找到磁盘数据地址，缺陷在与数据量大会存在碰撞（hashcode相同），且无法范围查找
通过B+Tree和InnoDB引擎的学习，对一些优化措施有一定的理解

有机后浪

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql索引本质

前言对于Mysql的学习，不能仅仅是学会sql增删改查还要对数据库优化有一定的认识前面总结了一些常用的sql命令：Mysql常用命令总结接下来学习优化推荐一个数据结构演示网站：Data Structure Visualizations目录索引的使用1.1. 普通索引（NORMAL）1.2. 唯一索引（UNIQUE）1.3. 主键索引（PRIMARY KEY）1.4. 全文索...
复制链接

扫一扫

专栏目录