当前位置：首页 > news >正文

Mysql系列--8、索引

news 2025/8/26 18:36:05

一、演示索引的强大

二、索引的目的和分类

2.1目的

2.2分类

三、理解Mysql与磁盘交互

3.1Mysql与存储的关系

3.2Mysql与磁盘交互的基本单位

3.3共识

四、影响查询效率的因素

4.1组织数据的形式--目录项

4.1.1理解单Page结构

4.2算法本身--B+树

4.2.1理解多Page结构

该图就是传说中类似B+树的结构，至此，我们已经给我们的表user构建完了主键索引。

4.3总结

4.4B+与B树

五、聚簇索引与非聚簇索引

六、索引的操作

5.1查看索引信息

5.2主键索引

5.3唯一键索引

5.4普通索引

5.5认识全文索引

5.6删除索引

七、索引的创建原则

一、演示索引的强大

以一个海量数据的查询速度的比较看到索引的强大

--构建一个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);

查询员工编号998877的员工select * from emp where empno=998877;结果显示耗时4.93秒，这还是在本机一个人来操作，在实际项目中，如果放在公网中，假如同时有
1000个人并发查询，那很可能就死机。创建索引alter table emp add index(empno);换一个员工编号进行查询select * from emp where empno=123456;结果显示为0秒

索引的存在使查询效率呈指数级减少！！！

二、索引的目的和分类

2.1目的

优化查询效率，提高数据库性能

索引不用加内存，不用改程序，不用调sql，只要执行正确的 create index ，查询速度就可能提高成百上千倍。

但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。

所以它的价值，在于提高一个海量数据的检索速度。

2.2分类

主键索引(primary key)

唯一索引(unique)

普通索引(index)

全文索引(fulltext)--解决中子文索引问题

三、理解Mysql与磁盘交互

3.1Mysql与存储的关系

MySQL 给用户提供存储服务，而存储的都是数据，数据在磁盘这个外设当中。

磁盘是计算机中的一个机械设备，相比于计算机其他电子元件，磁盘效率是比较低的，在加上IO本身的特征，可以知道：

如何提交效率，是 MySQL 的一个重要话题。

3.2Mysql与磁盘交互的基本单位

MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL 进行IO的基本单位是 16KB (后面统一使用 InnoDB 存储引擎讲解)

3.3共识

（1）与磁盘存储的基本单位类似，Mysql中的数据文件是以page为单位保存在磁盘中的

（2）Mysql的CURD操作，都需要通过计算，找到相应的插入位置，或者找到对应修改或者查询的数据

（3）只要涉及计算就需要CPU参与，那么一定要先将数据移动到内存中

（4）在特定时间内数据一定是一部分在内存，一部分在磁盘中，这时就涉及到磁盘和内存数据的交互，俗称IO，而此时IO的基本单位就是Page

（5）为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。

（6）一定要尽可能的减少系统和磁盘IO的次数

四、影响查询效率的因素

4.1组织数据的形式--目录项

4.1.1理解单Page结构

MySQL 中要管理很多数据表文件，而要管理好这些文件，就需要先描述在组织 ,我们目前可以简单理解成一个个独立文件是有一个或者多个Page构成的

不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 构成双向链表

因为有主键的问题， MySQL 会默认按照主键给我们的数据进行排序，从上面的Page内数据记录可以看出，数据是有序且彼此关联的。

但为了提高查询的效率，往往每个Page节点均含有目录项

目录，是一种“空间换时间的做法”

4.2算法本身--B+树

4.2.1理解多Page结构

MySQL 中每一页的大小只有 16KB ，单个Page大小固定，所以随着数据量不断增大， 16KB 不可能存下所有的数据，那么必定会有多个页来存储数据。

我们可以通过相互连接的多个Page进行线性查询，但这样时间复杂度还是O(n)，而且随着Page的增多，也存不下这么多数据

所以我们可以仿照单Page引入目录只对Page进行存储，这样对于一个只存Page目录的Page来说可以存许多目录，进而对应更多的数据，且查询时，先查目录进而快速舍去不需要的Page目录，是存储和查询效率指数级优化

最终形成目录页和普通页连接的树状结构

目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址

该图就是传说中类似B+树的结构，至此，我们已经给我们的表user构建完了主键索引。

4.3总结

1.叶子节点全部用链表连接的原因：
（1）B+树的特点

（2）我们希望进行范围查找

2.矮胖型的树状结构，使找到目标数据只需要更少的Page，IO次数更少！

3.其他数据结构为什么不是最优的优化结构？

（1）链表--线性遍历，O(n)

（2）二叉搜索树--退化问题，可能退化成为线性结构

（3）AVL &&红黑树--虽然是平衡或者近似平衡，但是毕竟是二叉结构，相比较多阶B+，意味着树整体过高，大家都是自顶向下找，层高越低，意味着系统与硬盘更少的IO Page交互

（4）Hash--官方的索引实现方式中， MySQL 是支持HASH的，不过 InnoDB 和 MyISAM 并不支持.Hash跟进其算法特征，决定了虽然有时候也很快(O(1))，不过，在面对范围查找就明显不行，

4.4B+与B树

B树：

B+树：

五、聚簇索引与非聚簇索引

针对搜索引擎的不同，索引的存储也会有不同的存储特点，但总体还是B+结构！

前文的InnoDB 索引， InnoDB 是将索引和数据放在一起的。称为聚簇索引

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

MyISAM 最大的特点是，将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据的地址。