【MySQL 一 数据库基础】深入解析 MySQL 的索引(3)

news/2025/2/24 7:33:53

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

索引

索引操作

自动创建

  • 当我们为一张表加主键约束(Primary key)，外键约束(Foreign Key)，唯一约束(Unique)时，MySQL会为对应的的列自动创建一个索引；
  • 如果表不指定任何约束时，MySQL会自动为每一列生成一个索引并用ROW_ID 进行标识，这个标识我们无法使用，是数据库内部生成的标识；
  • 建议为每一张表都定义一个主键，如果没有明确可以使用主键的列，我们可以在表多定义一个 bigint 类型的字段，然后使用自增主键来约束这个字段。

手动创建

(1) 主键索引
# 方式一，创建表时创建主键

create table t_test_pk (
	id bigint primary key auto_increment,
	name varchar(20)
);

# 方式二，创建表时单独指定主键列

create table t_test_pk1 (
	id bigint auto_increment,
	name varchar(20),
	primary key (id)
);

# 方式三，修改表中的列为主键索引

create table t_test_pk2(
	id bigint,
	name varchar(20)
);

# 修改表结构，为 id 列添加主键约束

alter table t_test_pk2 add primary key (id);

# 修改表结构，为 id 列修改为 bigint 类型且自增

alter table t_test_pk2 modify id bigint auto_increment;
(2) 唯一索引
# 方式一，创建表时创建唯一键

create table t_test_uk (
	id bigint primary key auto_increment,
	name varchar(20) unique
);

# 方式二，创建表时单独指定唯一列

create table t_test_uk1 (
	id bigint primary key auto_increment,
	name varchar(20),
	unique (name)
);

# 方式三，修改表中的列为唯一索引

create table t_test_uk2 (
	id bigint primary key auto_increment,
	name varchar(20)
);
alter table t_test_uk2 add unique (name);
(3) 普通索引

在这里插入图片描述

# 方式一，创建表时指定索引列

create table t_test_index (
	id bigint primary key auto_increment,
	name varchar(20) unique,
	sno varchar(10),
	index(sno)
);

# 方式二，修改表中的列为普通索引

create table t_test_index1 (
	id bigint primary key auto_increment,
	name varchar(20),
	sno varchar(10)
);
alter table t_test_index1 add index (sno);

# 方式三，单独创建索引并指定索引名(必须要指定名字)，index_name 推荐使用表名+列名

create table t_test_index2(
	id bigint primary key auto_increment,
	name varchar(20),
	sno varchar(10)
);
create index index_name on t_test_index2(sno);

在这里插入图片描述

创建复合索引

# 方式一，创建表时指定索引列

create table t_test_index4(
	id bigint primary key auto_increment,
	name varchar(20),
	sno varchar(10),
	class_id bigint,
	index (sno,class_id)
);

在这里插入图片描述

# 方式二，修改表中的列为复合索引

create table t_test_index5(
	id bigint primary key auto_increment,
	name varchar(20),
	sno varchar(10),
	class_id bigint
);
alter table t_test_index5 add index (sno,class_id);

# 方式三，单独创建索引并指定索引名

create table t_test_index6 (
	id bigint primary key auto_increment,
	name varchar(20),
	sno varchar(10),
	class_id bigint
);
create index index_name on t_test_index6 (sno,class_id);

在这里插入图片描述

查看索引

# 方式一

show keys from table_name ;

# 方式二

show index from table_name ;

# 方式三，简要信息:desc 表名;

desc table_name ;

删除索引

主键索引
# 语法
alter table 表名 drop primary key;

# 示例，删除t_test_index6表中的主键

alter table t_test_index6 drop primary key;   # 删除主键不用指定列，因为一张表只有一个主键

在这里插入图片描述

# 如查提示由于自增列的错误，先删除自增属性，把自增列修改为非自增，再删除主键

alter table t_test_index6 modify id bigint;

alter table t_test_index6 drop primary key;

show keys from t_test_index6 ;  # 查看结果
其他索引
#语法

alter table 表名 drop index 索引名;

# 示例，删除t_test_index6表中名为index_name的索引

alter table t_test_index6 drop index index_name;

show keys from t_test_index6 ;

创建索引的注意事项

  • 索引应该创建在高频查询的列上

  • 索引需要占用额外的存储空间

  • 对表进行插入、更新和删除操作时，同时也会修索引，可能会影响性能

  • 创建过多或不合理的索引会导致性能下降，需要谨慎选择和规划索引

怎么查看自己写的SQL走没走索引

可以查看执行计划

# 构造测试表

create table student_index(
	student_id bigint primary key auto_increment ,
	sn varchar(6) unique ,
	name varchar(50) not null,
	mail varchar(50) ,
	class_id bigint ,
	index(class_id) 
);

insert into student_index values(1,1,1,1,1);

insert into student_index values(2,2,2,2,2);

# 创建 sn 和 name 的复合索引 

create index idx_student_sn_name on student_index(sn, name);

show index from student_index;

不加条件，查询所有

# 接下来，我们要判断下面这条 SQL 语句走不走索引

select* from student_index;

explain select* from student_index;

在这里插入图片描述

使用主键查询

# 加上查询条件

select* from student_index where student_id = 1;

explain select* from student_index where student_id = 1 ;

在这里插入图片描述

子查询中使用索引

select * from student_index where 
	student_id = (select student_id from student_index where student_id = 1);
	
explain select * from student_index where 
	student_id = (select student_id from student_index where student_id = 1);

在这里插入图片描述

使用普通索引

select * from student_index where sn = '1' ;

explain select * from student_index where sn = '1' ;  # sn 被 unique 约束

在这里插入图片描述

使用复合索引

# 删除 sn 列的单列索引 sn ，来演示复合索引 

alter table student_index drop index sn ;

select * from student_index where sn = '1' and name = '1';

explain select * from student_index where sn = '1' and name = '1';

在这里插入图片描述

select sn , name from student_index where sn = '1' and name = '1';

explain select sn , name from student_index where sn = '1' and name = '1';

在这里插入图片描述

如果条件包含了复合索引中的所有列，那么都会走索引，条件中的先后顺序不影响结果

explain select sn , name from student_index where  name = '1' and sn = '1';

在这里插入图片描述

我们创建的复合索引是 sn 在前，name 在后的；如果在查询中，先查 name ，再查 sn ，就类似于先查韵母，再查声母；
这样虽然是不合理的，但是开发数据库的大佬为了保证健壮性，只要查询条件中，使用了索引包含的所有列，就会走索引，和顺序没有关系；

以 sn 为条件查询 name，这样的查询方法是合理的，走索引树查询（索引覆盖）

select name from student_index where sn = '1' ;

explain select name from student_index where sn = '1' ;

在这里插入图片描述

以 name 为条件查询 sn，这样的查询方法是不合理的，索引失去了意义，可能就会走全表查询

select sn from student_index where name = '1' ;

explain select sn from student_index where name = '1' ;

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述


http://www.niftyadmin.cn/n/5864088.html

相关文章

使用大语言模型（Deepseek）构建一个基于 SQL 数据的问答系统

使用大语言模型（Deepseek）构建一个基于 SQL 数据的问答系统

GitHub代码仓库 架构 从高层次来看，这些系统的步骤如下： 将问题转换为SQL查询：模型将用户输入转换为SQL查询。 执行SQL查询：执行查询。 回答问题：模型根据查询结果响应用户输入。 样本数据 下载样本数据&#xf…
阅读更多...
OutOfMemoryError unable to create new native thread

OutOfMemoryError unable to create new native thread

现象 生产环境大量的报OutOfMemoryError: unable to create new native thread Caused by: java.lang.OutOfMemoryError: unable to create new native threadat java.lang.Thread.start0(Native Method) [na:1.8.0_291]at java.lang.Thread.start(Thread.java:717) [na:1.8.…
阅读更多...
w803|联盛德|WM IoT SDK2.X测试|window11|TOML 文件|外设|TFT_LCD|测试任务|（5）：TFT_LCD_LVGL示例

w803|联盛德|WM IoT SDK2.X测试|window11|TOML 文件|外设|TFT_LCD|测试任务|（5）：TFT_LCD_LVGL示例

TFT_LCD_LVGL 功能概述 此应用程序是使用 WM IoT SDK 进行 LVGL 功能的示例。它演示了如何初始化 TFT LCD 设备，并创建 LVGL DEMO Task 进行 LVGL 模块的初始化，并展示 LVGL 原生的不同 Demo 场景, 例如： Widgets, Music Player, Benchmark…
阅读更多...
跟着 Lua 5.1 官方参考文档学习 Lua (8)

跟着 Lua 5.1 官方参考文档学习 Lua (8)

文章目录 4 – The Auxiliary Library4.1 – Functions and Types字符串缓冲区luaL_BufferluaL_buffinitluaL_addcharluaL_addstringluaL_addlstringluaL_addvalueluaL_prepbufferluaL_addsizeluaL_pushresult例子：luaL_Buffer 的使用 函数参数检查luaL_argchecklua…
阅读更多...
SQL ①-数据库 || MySQL

SQL ①-数据库 || MySQL

这里是Themberfue 数据库 ✨进入到目前的学习阶段中，数据库相信大家并不陌生，但是如果要说出概念的话那可能还是有点困难的，这里我就简单罗列出数据的一些相关概念，虽然这部分内容只是理论知识，但是非常重要。 &…
阅读更多...
AI探索笔记：浅谈人工智能算法分类

AI探索笔记：浅谈人工智能算法分类

人工智能算法分类 这是一张经典的图片，基本概况了人工智能算法的现状。这张图片通过三个同心圆展示了人工智能、机器学习和深度学习之间的包含关系，其中人工智能是最广泛的范畴，机器学习是其子集，专注于数据驱动的算法改进&#…
阅读更多...
C++关键字之mutable

C++关键字之mutable

1.介绍 在C中，mutable是一个关键字，用于修饰类的成员变量。它的主要作用是允许在常量成员函数或常量对象中修改被标记为mutable的成员变量。通常情况下，常量成员函数不能修改类的成员变量，但有些情况下，某些成员变量的…
阅读更多...
QT闲记-状态栏，模态对话框，非模态对话框

QT闲记-状态栏，模态对话框，非模态对话框

1、创建状态栏 跟菜单栏一样，如果是继承于QMainWindow类，那么可以获取窗口的状态栏，否则就要创建一个状态栏。通过statusBar()获取窗口的状态栏。 2、添加组件 通常添加Label 来显示相关信息，当然也可以添加其他的组件。通过addWidget（）添加组件 3、设置状态栏样式 …
阅读更多...
最新文章

Copyright @ 2022~2023