【MySQL】索引原理及操作

目录

索引原理

初识索引

磁盘原理 

磁盘与系统之间的关系 

MySQL、系统、磁盘之间的关系 

理解索引

页目录 

页目录设计的数据结构问题

聚簇索引与非聚簇索引 

遗留问题

索引操作

创建索引

查询索引 

删除索引 

其他索引概念与操作 

---

索引原理

索引（Index）是数据库管理系统中一种非常重要的数据结构，它主要用于帮助数据库系统高效获取数据。

---

初识索引

实验准备

在正式开始观察索引对搜索数据的效率影响之前，我们得先拥有一张海量数据表

drop database if exists `bit_index`;
create database if not exists `bit_index` default character set utf8;
use `bit_index`;

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

-- 雇员表
CREATE TABLE `EMP` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);


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

 把上述SQL添加到文件中，使用source语句可以直接导入一张800万数据的数据库

注意：导入时长可能需要10分钟左右

---

实验现象

使用select查找empno为998877的一行数据，看看用时

 

可以看到，大概用时为4.5秒左右，这还是在本机一个人来操作，在实际项目中，如果放在公网中，假如同时有1000个人并发查询，那很可能就死机。

接下来我们为该表的empno列创建索引，既然说索引能提高数据库查找的效率，那么查找效率有多快呢？sad

创建索引语句：

alter table EMP add index(empno);

 创建索引并进行查找数据：

可以看到，从4.5秒左右到0.01秒，索引对于数据库查找效率的提升是数量级的！

索引是如何做到的呢？接下来正式开始索引原理方面的学习！

---

 MySQL与磁盘的关系 

MySQL为我们创建一个数据库，本质就是在系统中为我们创建一个目录，在Centos7.6中，该目录被保存在/var/lib/mysql路径下

MySQL为我们创建表，本质就是数据库目录下，创建一个普通文件。

所以总的来说，MySQL的一切都与文件息息相关，要弄懂索引原理，理解MySQL，我们必须从文件入手，而文件在硬件上是被写入到磁盘中的，所以变相的MySQL与磁盘的关系也是非常紧密的！所以接下来我们从磁盘角度入手，一步一步弄清楚索引的原理

---

磁盘原理 

对于磁盘，在文件系统时已经详细介绍过，若感兴趣可以去看看，而接下来我将对磁盘进行简要介绍 

---

结构

上图就是一个磁盘结构，其中较为关键的部位就是磁盘(盘片)和磁头

盘片：通过主轴的带动，盘片会进行高速旋转

磁头：磁头会进行左右摇摆

通过盘片和磁头的运动规律，我们可以让磁头定位到盘片中的任意区域！

---

盘片

从逻辑上，一块盘片是由若干个扇区组成

扇区如何构成？

如图中，盘片上划分出了多个不同半径的同心圆，两个同心圆之间的区域，我们称之为磁道

如图中，盘片上的以半径划分出的一条一条的线，两条线之间的区域我们称之为扇面

磁道和扇面的交集区域，我们称之为扇区

扇区是磁盘IO的基本单位，一个扇区通常能存储512字节的数据！

注意：尽管越靠近外侧，扇区的物理面积好像越大，但不管它多大，在数据存储方面始终都是只能存储512字节的数据，尽管在最新的磁盘技术中，为了更大的容量，外侧的扇区能存储的数据越多，但我们暂时不考虑！

由磁盘的硬件结构可以得知，我们要打开一个文件，首先是要找到一个文件，找到一个文件，也就是找到这个文件对应的扇区

---

磁盘的连续访问与随机访问

通过磁盘的结构我们可以得知，磁盘要定位一个扇区首先是要通过盘片的高速旋转，磁头的高速摆动来定位的

尽管我们说它是高速运动，盘片的转动甚至可以到达几千转，但相对于计算机来说，物理高速运动是很慢的，所以磁盘I/O是很慢的！

磁盘要访问扇区是通过物理运动实现的，这也就决定了，磁盘在访问了A扇区继续访问B扇区，若B扇区在A扇区附近，那么磁盘可以很快定位到B扇区。若A扇区与B扇区相隔很远，磁盘要进行大幅度运动，那么它的下一次访问相对而言就较慢了！

其中，AB扇区相隔很近，磁盘连续访问AB扇区时，我们称磁盘在进行连续访问

 AB扇区相隔很远，磁盘大幅度运动定位不同扇区的方式，我们称磁盘在进行随机访问

注意：一般来说，好的软件设计应该是让磁盘尽量进行连续访问，减少随机访问！

---

磁盘与系统之间的关系 

磁盘I/O的基本单位是扇区(512字节)，那么系统和磁盘I/O的基本单位也是扇区(512字节)吗？

并不是，系统与磁盘进行交互的基本单位一般是4096字节，即4KB

换句话来说，对于系统来说，哪怕我只访问1个字节的空间，磁盘也必须把完整4KB的空间给我

---

为什么系统与磁盘I/O交互要设计成4KB

系统I/O基本单位为4KB，主要是因为如下原因：

• 可移植性
• 效率问题 

可移植性： 如果操作系统直接使用硬件提供的数据大小进行交互，那么系统的IO代码，就和硬件强相关，换言之，如果硬件发生变化，系统必须跟着变化，这显然是不合理的！

效率问题：512字节对于I/O来说还是太小了。例如对于一个4KB的连续空间，若采取512字节为单位，那么需要进行8次I/O。若采取4KB为单位，那么只需要进行1次I/O。众所周知I/O是非常耗时的！

---

为什么磁盘I/O的基本单位是512字节

前面，我们说了，若以512字节为单位，此时I/O的效率是很低的，那么磁盘I/O的基本单位为什么是512字节呢？

事实上，现如今的不少磁盘I/O的基本单位是4KB，但尽管如此，还是有以512字节为I/O基本单位的磁盘存在的&