【MySQL】索引——索引的引入、认识磁盘、磁盘的组成、扇区、磁盘访问、磁盘和MySQL交互、索引的概念

MySQL

  

1. 索引的引入

  海量表在进行普通查询的时候，效率会非常的慢，但是索引可以解决这个问题。

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

  本机一个人来操作就要耗时接近5秒，所有如果放在公网中，假如同时有1000个人并发查询，那很可能就死机。

  
解决方法，创建索引

alter table EMP add index(empno);

  

换一个员工编号，测试看看查询时间

select * from EMP where empno=123456;

  

2. 认识磁盘

  磁盘（disk）是指利用磁记录技术存储数据的存储器。

  磁盘是计算机主要的存储介质，可以存储大量的二进制数据，并且断电后也能保持数据不丢失。早期计算机使用的磁盘是软磁盘（Floppy Disk，简称软盘），如今常用的磁盘是硬磁盘（Hard disk，简称硬盘）。

  

  

2.1 磁盘的组成

盘片：

  盘片：是存储数据的主要介质，通常由铝、玻璃或陶瓷等材料制成，表面涂有磁性物质，数据就记录在这些磁性涂层上。

  例如，一个磁盘可能有多个盘片，像多层蛋糕一样叠放。

  

磁道：

  磁道是磁盘表面上的一组同心圆。数据在磁盘上的存储就是分布在这些磁道上的。可以把磁道想象成一个环形的跑道，数据就如同运动员在跑道上的位置。每个磁道被划分成多个扇区，扇区是数据读写的基本单位。磁道的密度会影响磁盘的存储容量和数据传输速度。

  例如，磁盘外圈的磁道周长较长，能存储更多数据，而内圈磁道周长较短，存储的数据相对较少。在磁盘工作时，磁头会沿着磁道移动来读取或写入数据。

  

  

2.2 扇区

扇区：

  扇区是磁盘存储的基本单位它的大小一般是固定的，常见为 512 字节。比如要存数据到磁盘，就会按扇区来存放。扇区有编号，从 0 开始。

  相邻扇区组成磁道，多个磁道构成盘面。就算数据不满一个扇区，也会占一整个扇区的空间。扇区能让磁盘存储更高效、管理数据更方便。

  数据库文件，本质其实就是保存在磁盘的盘片当中。也就是上面的一个个小格子中，就是我们经常所说的扇区。当然，数据库文件很大，也很多，一定需要占据多个扇区。

  

定位扇区：

  通常存储着磁盘的重要信息，比如分区表，这能告诉系统磁盘如何划分区域来存储不同的数据。还可能存有引导记录，帮助计算机启动时找到操作系统的位置并加载。定位扇区就像是磁盘的“地图指南”和“启动钥匙”。

  例如，当计算机开机时，会首先读取定位扇区的信息，来知道如何找到并启动系统

  

  定位扇区：通常是指在磁盘操作中，为了特定目的而专门标识或指定的扇区。它可能具有特殊的用途或被系统用于特定的功能，例如存储磁盘的关键信息，如分区表、引导记录等。

  普通扇区：则是磁盘上用于一般数据存储和读写的扇区。

  

  我们现在已经能够在硬件层面定位，任何一个基本数据块了(扇区)。那么在系统软件上，就直接按照扇区(512字节，部分4096字节)，进行IO交互吗？不是

  如果操作系统直接使用硬件提供的数据大小进行交互，那么系统的IO代码，就和硬件强相关，换言之，如果硬件发生变化，系统必须跟着变化

  从目前来看，单次IO512字节，还是太小了。IO单位小，意味着读取同样的数据内容，需要进行多次磁盘访问，会带来效率的降低。

  之前文件系统，就是在磁盘的基本结构下建立的，文件系统读取基本单位，就不是扇区，而是数据块。所以，系统读取磁盘，是以块为单位的，基本单位是 4KB 。

  

2.3 磁盘访问

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

  随机访问：本次IO所给出的扇区地址和上次IO给出扇区地址不连续，这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。

  连续访问：如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的，那磁头就能很快的开始这次IO操作，这样的多个IO操作称为连续访问。

  因此尽管相邻的两次IO操作在同一时刻发出，但如果它们的请求的扇区地址相差很大的话也只能称为随机访问，而非连续访问。

  磁盘是通过机械运动进行寻址的，随机访问不需要过多的定位，故效率比较高。

  

3. 磁盘和MySQL交互

  而 MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL 进行IO的基本单位是 16KB。

  也就是说，磁盘这个硬件设备的基本单位是 512 字节，而 MySQL InnoDB引擎 使用 16KB 进行IO交互。MySQL 和磁盘进行数据交互的基本单位是 16KB 。 这个基本数据单元，在 MySQL 这里叫做page。

mysql> SHOW GLOBAL STATUS LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 | -- 16*1024=16384
+------------------+-------+
1 row in set (0.01 sec)

  

总结：

  MySQL 中的数据文件，是以page为单位保存在磁盘当中的。

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

  而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。

  所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。而此时IO的基本单位就是Page。

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

  为何更高的效率，一定要尽可能的减少系统和磁盘IO的次数。

  

4. 索引的概念

  MySQL 索引是一种用于提高数据库查询和操作性能的数据结构。

  它就像是一本书的目录，通过索引，MySQL 能够更快地定位和获取所需的数据，而不必遍历整个数据表。

  所以索引能够显著提高数据库的查询速度，可能让速度提升数百甚至数千倍，只需执行正确的 create index 操作，无需对内存、程序或 SQL 语句进行大的改动。

  同时也强调了索引并非毫无代价。虽然能加快查询，但会降低插入、更新和删除操作的速度，因为这些写操作会产生大量的 I/O 开销。这意味着在使用索引时需要权衡查询性能和写操作性能之间的平衡。

  

  关于常见索引的分类：

  主键索引（primary key）：用于唯一标识表中的每一行记录，确保其值的唯一性和非空性。例如，在学生表中，学号可以作为主键索引。

  唯一索引（unique）：确保某一列的值不重复，但允许为 NULL。比如，在用户表中，身份证号可以设置为唯一索引。

  普通索引（index）：用于加快数据的查询速度，但不保证列值的唯一性。比如，在商品表中，商品名称可以创建普通索引。

  全文索引（fulltext）：主要用于解决中文文本的索引问题，能够高效地在大量文本数据中进行搜索。例如，在文章表中，文章内容可以创建全文索引来快速搜索特定的关键词或短语。

  

4.1 索引测试

建立测试表：

mysql> create table if not exists user (-> id int primary key,-> age int not null,-> name varchar(16) not null-> );
Query OK, 0 rows affected (0.01 sec)

  

插入多条记录：

mysql> insert into user (id, age, name) values(3, 18, '杨过');
Query OK, 1 row affected (0.01 sec)mysql> insert into user (id, age, name) values(4, 16, '小龙女');
Query OK, 1 row affected (0.00 sec)mysql> insert into user (id, age, name) values(2, 26, '黄蓉');
Query OK, 1 row affected (0.01 sec)mysql> insert into user (id, age, name) values(5, 36, '郭靖');
Query OK, 1 row affected (0.01 sec)mysql> insert into user (id, age, name) values(1, 56, '欧阳锋');
Query OK, 1 row affected (0.00 sec)

  

查看插入结果：

mysql> select * from user;

  

  我们会发现排序竟然默认是有序的。

  

4.2 Page

单个Page：

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

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

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

  

  

多个Page：

  在上面页模式中，只有一个功能，就是在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能。但是，我们也可以看到，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。

  如果有1千万条数据，一定需要多个Page来保存1千万条数据，多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找。这效率也太低了。

  

4.3 单页和多页情况

单页情况：

  针对上面的单页Page，我们能否也引入目录呢？当然可以。

  那么当前，在一个Page内部，我们引入了目录。比如，我们要查找id=4记录，之前必须线性遍历4次，才能拿到结果。现在直接通过目录2[3]，直接进行定位新的起始位置，提高了效率。现在我们可以再次正式回答上面的问题了，为何通过键值 MySQL 会自动排序？可以很方便引入目录。

  

多页情况：

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

  在单表数据不断被插入的情况下， MySQL 会在容量不足的时候，自动开辟新的Page来保存新的数据，然后通过指针的方式，将所有的Page组织起来。

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

… …