索引特性.

最新推荐文章于 2024-11-06 10:59:12 发布

~小火苗

最新推荐文章于 2024-11-06 10:59:12 发布

阅读量1.1k

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_54183294/article/details/130918388

版权

MySQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

文章探讨了MySQL如何与磁盘交互，特别是在没有索引时的效率问题。介绍了磁盘结构，如扇区和定位，以及随机和连续访问的概念。接着，深入讨论了InnoDB和MyISAM存储引擎的聚簇索引与非聚簇索引的区别，以及B+树作为索引结构的原因。文章还涉及了不同类型的索引创建和管理。

摘要由CSDN通过智能技术生成

没有索引，可能会有什么问题

在这里插入图片描述

先整一个海量表，在查询的时候，看看没有索引时有什么问题？

--构建一个8000000条记录的数据
--构建的海量表数据需要有差异性，所以使用存储过程来创建， 拷贝下面代码就可以了，暂时不用理解

-- 产生随机字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;

--产生随机数字
delimiter $$
create function rand_num()
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
delimiter ;

--创建存储过程，向雇员表添加海量数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into EMP values ((start+i)
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;

-- 执行存储过程，添加8000000条记录
call insert_emp(100001, 8000000);

在这里插入图片描述

认识磁盘

MySQL与存储

MySQL 给用户提供存储服务，而存储的都是数据，数据在磁盘这个外设当中。磁盘是计算机中的一个机
械设备，相比于计算机其他电子元件，磁盘效率是比较低的，在加上IO本身的特征，可以知道，如何提
交效率，是 MySQL 的一个重要话题。

先来研究一下磁盘

在这里插入图片描述

在看看磁盘中一个盘片

在这里插入图片描述

扇区

在这里插入图片描述

定位扇区

结论

在这里插入图片描述

磁盘随机访问(Random Access)与连续访问(Sequential Access)

在这里插入图片描述

MySQL 与磁盘交互基本单位

在这里插入图片描述

建立共识

在这里插入图片描述

索引的理解

建立测试表

在这里插入图片描述

插入多条记录

在这里插入图片描述

查看插入结果

在这里插入图片描述

中断一下—为何IO交互要是 Page

在这里插入图片描述

理解单个Page

在这里插入图片描述

理解多个Page

在这里插入图片描述

页目录

在这里插入图片描述

单页情况

在这里插入图片描述

多页情况

在这里插入图片描述

复盘一下

在这里插入图片描述

InnoDB 在建立索引结构来管理数据的时候，其他数据结构为何不行？

在这里插入图片描述

数据结构演示链接：添加链接描述

B+ vs B

B树:
在这里插入图片描述
B+树:

聚簇索引 VS 非聚簇索引

MyISAM 存储引擎-主键索引
MyISAM 引擎同样使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。下图为 MyISAM
表的主索引， Col1 为主键。

在这里插入图片描述
其中， MyISAM 最大的特点是，将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据的地址。
相较于 InnoDB 索引， InnoDB 是将索引和数据放在一起的。

其中， InnoDB 这种用户数据与索引数据在一起索引方案，叫做聚簇索引

当然， MySQL 除了默认会建立主键索引外，我们用户也有可能建立按照其他列信息建立的索引，一般这种索引可以叫做辅助（普通）索引。

对于 MyISAM ,建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复。

下图就是基于 MyISAM 的 Col2 建立的索引，和主键索引没有差别
在这里插入图片描述
同样， InnoDB 除了主键索引，用户也会建立辅助（普通）索引，我们以上表中的 Col3 建立对应的辅助索引如下图：

可以看到， InnoDB 的非主键索引中叶子节点并没有数据，而只有对应记录的key值。
所以通过辅助（普通）索引，找到目标记录，需要两遍索引：首先检索辅助索引获得主键，然后用主键
到主索引中检索获得记录。这种过程，就叫做回表查询
为何 InnoDB 针对这种辅助（普通）索引的场景，不给叶子节点也附上数据呢？原因就是太浪费空间
了。
总结：