MySQL 数据库索引教程（超详细）

作者：飞向星的客机

2022 年 7 月 05 日
本文字数：1296 字
阅读完需：约 4 分钟

索引初步

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引、唯一索引、组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引；唯一索引，即索引列的值必须唯一，但允许有空值。创建主键的时候回自动创建唯一索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

索引结构

实验准备（以下实验都在 VMware 中进行）

1 台 MySQL 主机（5.7）

准备写一个 2 千万左右的数据表（当然不是手写）

创建索引：Navicat 15 for MySQL (图形化工具)

1、新建一个数据库

create database test default character set utf8;

复制代码

2、新建一个数据表

CREATE TABLE testsql (    id int NOT NULL AUTO_INCREMENT PRIMARY KEY comment '自增主键',    dept tinyint not null comment '部门id',    name varchar(30) comment '用户名称',    create_time datetime not null comment '注册时间',    last_login_time datetime comment '最后登录时间' ) comment '测试表';

复制代码

3、写入数据

insert into testsql values(1,1,'user_1', '2018-01-01 00:00:00', '2018-03-01 12:00:00');

复制代码

4、赋值

set @i=1;

复制代码

5、做一个表的倍数增加（不然几千万的数据写一年都写不完）

insert into testsql(dept, name, create_time, last_login_time) select left(rand()*10,1) as dept,   #随机生成1~10的整数        concat('user_',@i:=@i+1),   #按序列生成不同的name        date_add(create_time,interval +@i*cast(rand()*100 as signed) SECOND), #生成有时间大顺序随机注册时间        date_add(date_add(create_time,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND) #生成有时间大顺序的随机的最后登录时间from testsql;

复制代码

一直重复运行上面的命令

到后面的速度会越来越慢，可能要几分钟，这是正常的

6、查看

select count(*) from testsql;

复制代码

这行命令可以查看数据表已经达到了多少列

直到数字出现了几千万行就可以停止了

（实几百万行已经可以看到效果了）

查看日志

show variables like "%_buffer%";

复制代码

7、实验

这个时候数据表已经达到了几千万可以查询以下几千万数据中的某一行

select COUNT(*) from t where name='user_500000';

复制代码

可以看到搜索速度很慢

8、创建索引

选中数据表，单机右键

找到索引名字可以随便取字段我选择的是 name（其他也可以）

类型我选的是 NORMAL,方法用的是 BTREE

保存，可能要等几分钟，这时候不要乱动

9、实验效果

这个时候重新搜索，可以看到延迟已经没有了

select COUNT(*) from t where name='user_500000';

复制代码

以上就是 MySQL 数据库索引的教程

发布于: 刚刚阅读数: 3

原文链接:【http://xie.infoq.cn/article/b18d877fa4866bd1800be3c2e】。文章转载请联系作者。

飞向星的客机

关注

🏅️ InfoQ 签约作者 2022.03.08 加入

CSDN：飞向星的客机

发布

暂无评论

创作场景

MySQL 数据库索引教程（超详细）

索引初步

缺点

索引结构

实验准备（以下实验都在 VMware 中进行）

1、新建一个数据库

2、新建一个数据表

3、写入数据

4、赋值

5、做一个表的倍数增加（不然几千万的数据写一年都写不完）

6、查看

7、实验

8、创建索引

9、实验效果

飞向星的客机

评论