MySQL-----索引

最新推荐文章于 2024-05-19 03:18:57 发布

「PlanA」

最新推荐文章于 2024-05-19 03:18:57 发布

阅读量1.2k

点赞数 31

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2301_77599154/article/details/137380829

版权

一概述

索引(index)是帮助MysQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，

这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

假如表中没有索引的查询情况：

select * from user where age = 45

执行select语句之后,它会从第一条记录开始依次向下查询匹配,当找到符合条件的记录7号时，它会继续向下查找直到将整个表的记录查询匹配完毕，这种查询称为全表扫描，性能十分的低。

假如有索引的查询情况：

数据结构二叉树，查询age=45的记录，查询的方法，首先将age=45与结点36比对，发现比36大,走右边，然后与48比较，比48小，走左侧，最后找到age=4的记录。找到45以后通过索引建立的二叉树，找到指向的数据。显然，有索引的只需要匹配3次就定位到了数据，搜索效率十分高效。

二索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种:

在不同存储引擎中对索引的支持

我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

2-1 Btree索引结构

首先我们现了解一下二叉树

上图二叉树:一个结点最后只能有两个子节点，左侧的结点比根结点小，右侧的结点比根结点大。比如我们查找数据20：与36比较比36小走左侧，在与22比较比22小，走左侧，在于19大走右侧，最终找到数据记录20.

如果维护二叉树时是顺序插入，最终形成一个单向链表，如果我们要查找17，那么就会把整个链表走一次。

弊端：二叉树缺点:顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。

解决链表的顺序插入我们可以使用红黑树，但是红黑树也是一个二叉树，大数据量的情况下，层级较深，检索熟读仍然慢。

B-Tree树

上面的红黑树虽然解决了，顺序插入的问题，但是仍然不能满足大量的数据，如果我们一个结点下面，跟多个结点，那么就可以提升查询的效率。

以一颗最大度数（max-degree)为5(5阶)的b-tree为例(每个节点最多存储4个key，5个指针):

模拟B-Tree树的形成：

Data Structure Visualization

插入100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250数据为例。

2-2 B+Tree树

以一颗最大度数(max-degree)为4(4阶)的b+tree为例:

观察可以发现所有的元素都会出现在叶子结点，上面的非叶子结点主要起到索引作用。
在B+树中所有的叶子结点形成了一个单向链表。

模拟B+树的形成：

插入100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250数据为例。

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

2-3 Hash结构

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

比如上面的我们通过每一个人名name,通过我们的hash算法得到槽位值，然后在这个槽位中存放这个key（name）以及地址值。当有多个key指向一个槽位时，会产生hash冲突，我们可以通过链表来解决，在一个槽位后面增加一个链表。

特点：

hash索引只能用于对等比较(=,in),不支持范围查询(between,>,<,...)
无法利用索引完成排序操作
查询效率高，通常只需要一次检索就可以了，效率高于B+tree索引

在MysQL中，支持hash索引的是Memory引擎，而innoDB中具有自适应hash功能,hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

为什么InnoDB存储引擎选择使用B+tree索引结构？

相对于二叉树，层级更少，搜索效率高
对于B-tree，无论叶子结点还是非叶子结点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
相对Hash索引，B+tree支持范围收索以及排序操作。

三索引分类

在InnoDB存储索引中，更据索引的存储形式，又可以分为以下两种：

聚集索引的选取规则：
如果存在主键，主键索引就是聚集索引

如果不存在主键，将使用第一个唯一索引作为聚集索引

如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

执行SQL语句，首先我们会通过二级索引，查找到Arm的id为10,然后通过聚集索引查找到id为10对应的行的数据内容。这种查询我们称为回表查询。

四索引的语法

-- 创建索引
create [UNIQUE | FULLTEXT ] INDEX index_name ON table_name(index_col_name,..)
-- 查看索引
SHOW INDEX FROM table_name;
-- 删除索引
Drop INDEX index_name ON table_name

首先我们已经有了一个tb_user表,在此基础上我们来实现创建索引的操作。

创建索引

五 SQL性能分析

5-1 查看执行频率

SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;