【MySQL】：索引

最新推荐文章于 2026-03-19 10:51:11 发布

原创

最新推荐文章于 2026-03-19 10:51:11 发布 · 1k 阅读

标签

#mysql #数据库

朋友们、伙计们，我们又见面了，本期来给大家带来数据库表的索引知识点，如果看完之后对你有一定的启发，那么请留下你的三连，祝大家心想事成！

C 语言专栏：C语言：从入门到精通

数据结构专栏：数据结构

个人主页：stackY、

C + + 专栏：C++

Linux 专栏：Linux

1. 为什么要有索引

索引：提高数据库的性能，在于提高一个海量数据的检索速度。索引是物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要创建正确的索引，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。

MySQL的服务器本质是在内存中运行的，所有对于数据库的增删查改的操作全部都是在内存中进行的，索引也是如此！

常见索引分为：

主键索引(primary key)
唯一索引(unique)
普通索引(index)
全文索引(fulltext)--解决中子文索引问题。

下面我们用一个例子来演示索引的对于检索速度的影响：

案例：

先创建一个海量表，在查询的时候，看看没有索引时查询所需时间；

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

将上面的代码使用vim拷贝进一个.sql文件中，然后在MySQL中使用source + 文件路径即可完成创建。

-- 导入文件
mysql> source /root/index_data.sql
Query OK, 1 row affected (0.29 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (8 min 9.30 sec)

-- 查看测试库
mysql> use bit_index;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_bit_index |
+---------------------+
| EMP                 |
+---------------------+
1 row in set (0.00 sec)

这个创建海量表的时间有点长，所以需要耐心等待！

① 查询员工编号为998877的员工

mysql> select * from EMP where empno=998877;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 998877 | eyebTo | SALESMAN | 0001 | 2024-11-30 00:00:00 | 2000.00 | 400.00 |    411 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (4.85 sec)

可以看到没有设置索引时查询时间是4.58秒，这还是在本机一个人来操作，在实际项目中，如果放在公网中，假如同时有1000个人并发查询，那很可能就死机。

② 创建索引

-- 给指定表的指定字段添加索引
-- alter table 表名 add index(字段);

mysql> alter table EMP add index(empno);
Query OK, 0 rows affected (26.85 sec)
Records: 0  Duplicates: 0  Warnings: 0

③ 继续查询，看看查询时间如何

mysql> select * from EMP where empno=998877;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 998877 | eyebTo | SALESMAN | 0001 | 2024-11-30 00:00:00 | 2000.00 | 400.00 |    411 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (0.02 sec)

可以看出，加上索引之后的查询时间有了很明显的下降，提高了检索效率。

2. MySQL与储存

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

磁盘是计算机中的一个机械设备，相比于计算机其他电子元件，磁盘效率是比较低的，在加上IO本身的特征，可以知道，如何提高效率，是 MySQL 的一个重要话题。

2.1 磁盘

关于介磁盘的具体绍以及定位扇区在文件系统这一章节详细介绍过，这里直接对磁盘的扇区进行分析：

扇区：

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

我们在使用Linux，所看到的大部分目录或者文件，其实就是保存在硬盘（/var/lib/mysql ）当中的。(当然，有一些内存文件系统，如： proc ， sys 之类，我们不考虑)
#数据库文件，本质其实就是保存在磁盘的盘片当中，就是一个一个的文件
[root@VM-0-3-centos ~]# ls /var/lib/mysql -l #我们目前MySQL中的文件
total 319592
drwxr-x--- 2 mysql mysql 4096 Apr 15 21:46 57test
-rw-r----- 1 mysql mysql 56 Apr 12 15:27 auto.cnf
drwxr-x--- 2 mysql mysql 4096 May 17 13:52 bit_index
-rw------- 1 mysql mysql 1676 Apr 12 15:27 ca-key.pem
.......
所以，最基本的，找到一个文件的全部，本质，就是在磁盘找到所有保存文件的扇区。
而我们能够定位任何一个扇区，那么便能找到所有扇区，因为查找方式是一样的。

结论：

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

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

从目前来看，单次IO 512字节，还是太小了。IO单位小，意味着读