Mysql的索引

  • 开发
  • 42

MySQL的索引是数据库性能优化的关键部分，它对于提高查询速度至关重要，索引在MySQL中扮演着类似于书籍目录中索引的角色。没有索引，数据库系统必须从头开始扫描整个表以找到相关的行，这称为全表扫描，对于大型表来说是非常低效的。而有了索引，数据库系统可以迅速定位到所需的数据，从而显著提高查询速度。

1 常见索引的分类

按照逻辑功能划分

  • 普通索引：普通的索引，没有什么限制
  • 唯一索引：不能重复，可以为null
  • 主键索引：不能为空，不能重复，主键索引同时也是聚簇索引
  • 联合索引： 和普通索引一样，只不过是多个字段建立一个索引，abc建立联合索引，底层其实建立索引a、索引ab、索引abc

按照储存方式划分

  • 聚簇索引： 索引数据和表数据存储在一起的，一张表中只能有一个聚簇索引，一般是主键索引，不存在会默认建立一个隐藏的字段作为聚簇索引。
  • 非聚簇索引： 索引数据和表数据是分开存放的，除了主键索引其他都是非聚簇索引

2 索引的使用

2.1 普通索引的创建

-- 创建表数据
CREATE TABLE users (
	id INT NULL,
	name varchar(100) NULL,
	age varchar(100) NULL
);
-- 方式1
create index idx_name on users(name);
-- 方式2 
alter table users add index idx_name(name);
-- 方式3, 创建表的时候创建索引
-- 先删除表
drop table users;
-- 创建表
CREATE TABLE users (
	id INT NULL,
	name varchar(100) NULL,
	age varchar(100) NULL,
    key idx_name(name)
);
-- 完成后删除表
drop table users;

在这里插入图片描述

2.2 普通索引的删除

-- 创建表
CREATE TABLE users (
	id INT NULL,
	name varchar(100) NULL,
	age varchar(100) NULL,
    key idx_name(name)
);
-- 删除普通话索引
alter table users drop index idx_name;
-- 完成后删除表
drop table users;

在这里插入图片描述

3 唯一索引的创建

-- 创建表数据
CREATE TABLE users (
	id INT NULL,
	name varchar(100) NULL,
	age varchar(100) NULL
);
-- 方式1
create unique index idx_uni_name on users(name);
-- 方式2 
alter table users add unique index idx_uni_name(name);
-- 方式3, 创建表的时候创建索引
-- 先删除表
drop table users;
-- 创建表
CREATE TABLE users (
	id INT NULL,
	name varchar(100) NULL,
	age varchar(100) NULL,
    unique key idx_uni_name(name)
);
-- 完成后删除表
drop table users;

在这里插入图片描述

2.4 唯一索引的删除

-- 创建表
CREATE TABLE users (
	id INT NULL,
	name varchar(100) NULL,
	age varchar(100) NULL,
    unique key idx_uni_name(name)
);
-- 删除索引
alter table users drop index idx_uni_name;
-- 完成后删除表
drop table users;

在这里插入图片描述

2.5 主键索引的创建

-- 创建表数据
CREATE TABLE users (
	id INT NULL,
	name varchar(100) NULL,
	age varchar(100) NULL
);
-- 方式1 
alter table users add primary key pk_id(id);
-- 方式2, 创建表的时候创建索引
-- 先删除表
drop table users;
-- 创建表
CREATE TABLE users (
	id INT NOT NULL,
	name varchar(100) NULL,
	age varchar(100) NULL,
    primary key pk_id(id)
);
-- 完成后删除表
drop table users;

在这里插入图片描述

2.6 主键索引的删除

-- 创建表
CREATE TABLE users (
	id INT NOT NULL,
	name varchar(100) NULL,
	age varchar(100) NULL,
    primary key pk_id(id)
);
-- 删除索引
alter table users drop primary key
-- 完成后删除表
drop table users;

在这里插入图片描述

2.7 联合索引的创建

-- 创建表数据
CREATE TABLE users (
	id INT NULL,
	name varchar(100) NULL,
	age varchar(100) NULL
);
-- 方式1
create index idx_name_age on users(name, age);
-- 方式2 
alter table users add index idx_name_age(name, age);
-- 方式3, 创建表的时候创建索引
-- 先删除表
drop table users;
-- 创建表
CREATE TABLE users (
	id INT NULL,
	name varchar(100) NULL,
	age varchar(100) NULL,
    key idx_name_age(name, age)
);
-- 完成后删除表
drop table users;

在这里插入图片描述

2.8 联合索引的删除

-- 创建表
CREATE TABLE users (
	id INT NOT NULL,
	name varchar(100) NULL,
	age varchar(100) NULL,
    index idx_name_age(name, age)
);
-- 删除索引
alter table users drop index idx_name_age;show 
-- 完成后删除表
drop table users;

在这里插入图片描述

3 索引失效的常见的情况

3.1 准备数据

-- 创建学生表
CREATE TABLE students (
	id INT auto_increment NOT NULL,
	stu_no varchar(100) NULL COMMENT '学号',
	name varchar(100) NULL COMMENT '学生名字',
	age INT NULL COMMENT '学生年龄',
	class_id INT NULL COMMENT '班级id',
	CONSTRAINT students_PK PRIMARY KEY (id)
);

--插入学生数据
INSERT INTO students (stu_no, name, age, class_id) VALUES
('1', '小红', 10, 1),
('2', '小蓝', 11, 2),
('3', '小黑', 13, 3),
('4', '小黑', 14, 1),
('5', '小白', 15, 2),
('6', '小青', 16, 3),
('1', '小红', 10, 1),
('2', '小蓝', 11, 2),
('3', '小黑', 13, 3),
('4', '小黑', 14, 1),
('5', '小白', 15, 2),
('6', '小青', 16, 3);

3.2 查询中OR没有全值匹配

-- 名字和学号建立索引
alter table students add index idx_name(name);
alter table students add index idx_no(stu_no);
-- 查询数据
explain select * from students where name="小红" or stu_no="2" or age=16;
-- 删除索引
alter table students drop index idx_name;
alter table students drop index idx_no;

age没有建立索引导致未能走索引
在这里插入图片描述

3.4 模糊查询中%开头

-- 名字建立索引
alter table students add index idx_name(name);
-- 查询数据
explain select * from students where name like "%红";
-- 删除索引
alter table students drop index idx_name;

当模式以通配符 % 开头时（如 "%xxx"），MySQL 通常无法有效地使用索引来加速查询，因为这样的模式表示“以任何字符序列开始，然后跟着 ‘xxx’”。MySQL 无法预测前面的字符序列是什么，因此它必须扫描表中的每一行来检查是否匹配。因此，对于以 % 开头的 LIKE 查询，即使存在索引，MySQL 也可能不会使用它，这通常会导致全表扫描，从而降低查询性能。
在这里插入图片描述

3.5 数据类型不一致

-- 名字建立索引
alter table students add index idx_no(stu_no);
-- 查询数据
explain select * from students where stu_no = 1;
-- 删除索引
alter table students drop index idx_no;

stu_no和查询条件的数据类型不一致，MySQL可能会尝试进行隐式类型转换来匹配条件。这种隐式转换可能导致索引不被使用。所以查询数据类型一致，1要写成"1"
在这里插入图片描述

3.6 字段参与计算

-- 名字建立索引
alter table students add index indx_age(age);
-- 查询数据
explain select * from students where age -1 = 11;
-- 删除索引
alter table students drop index indx_age;

在这里插入图片描述

3.7 字段使用系统函数

-- 名字建立索引
alter table students add index indx_name(name);
-- 查询数据
explain select * from students where LTRIM(name) = "小红";
-- 删除索引
alter table students drop index indx_name;

在这里插入图片描述

3.8 联合索引违背最左匹配原则

-- 名字建立索引
alter table students add index indx_name_age_no(name, age, stu_no);
-- 查询数据
explain select * from students where age= 11;
-- 删除索引
alter table students drop index indx_name_age_no;

建立联合索引name, age, stu_no时，实际上建立了三个索引(name)、(name, age)、(name, age, stu_no)，如果查询的条件不在这个范围内就不走索引
在这里插入图片描述

3.9 不同字段对比

-- 名字建立索引
alter table students add index indx_age(age);
alter table students add index indx_class(class_id);
-- 查询数据
explain select * from students where stu_no=class_id;
-- 删除索引
alter table students drop index indx_age;
alter table students drop index indx_class;

当比较同一张表中的两个不同字段时（即使这两个字段都各自有索引），数据库通常不会使用这两个索引来加速查询，因为索引通常用于单个字段的快速查找。当你执行类似 WHERE stu_no = class_id 的查询时，数据库引擎需要逐行比较这两个字段的值，这实际上是一个全表扫描的过程，因为它必须读取每一行来确定这两个字段是否匹配。
在这里插入图片描述

3.10 反向操作

-- 名字建立索引
alter table students add index indx_name(name);
-- 查询数据
explain select * from students where name is not null;
-- 删除索引
alter table students drop index indx_name;
alter table students drop index indx_class;

如果SQL属于正向范围查询，例如>、<、between、like、in...等操作时，索引是可以正常生效的，但如果SQL执行的是反向范围操作，例如NOT IN、NOT LIKE、IS NOT NULL、!=、<>...等操作时，就会出现问题

在这里插入图片描述

上一章：Mysql的库函数
下一章：Mysql的事务

阅读全部

相关推荐

  5. MySQL的索引类型

    2024-04-03 11:28:01       42 阅读

  6. MySQL中的索引

    2024-04-03 11:28:01       39 阅读

  10. 谈谈 MySQL 的索引

    2024-04-03 11:28:01       34 阅读

最近更新

  3. docker php8.1+nginx base 镜像 dockerfile 配置

    2024-04-03 11:28:01       98 阅读

  4. Could not load dynamic library ‘cudart64_100.dll‘

    2024-04-03 11:28:01       106 阅读

  9. 在Django里面运行非项目文件

    2024-04-03 11:28:01       87 阅读

  19. Python语言-面向对象

    2024-04-03 11:28:01       96 阅读

  20. 如何分清楚常见的 Git 分支管理策略Git Flow、GitHub Flow 和 GitLab Flow

    2024-04-03 11:28:01       91 阅读

热门阅读

  5. 【C#】特殊字符（$、@、{}、[]）和转义字符Escape character（\字符）

    2024-04-03 11:28:01       29 阅读

  6. android 所有音量默认最大

    2024-04-03 11:28:01       36 阅读

  9. 小程序页面滚动？

    2024-04-03 11:28:01       31 阅读

  12. Flink总结

    2024-04-03 11:28:01       41 阅读

  14. 【m122】webrtc的比较

    2024-04-03 11:28:01       35 阅读

  15. 一线大厂面试真题——强引用、软引用、弱引用、虚引用有什么区别

    2024-04-03 11:28:01       41 阅读

  19. IDEA 宝贝插件

    2024-04-03 11:28:01       38 阅读

  20. IntelliJ IDEA - 快捷键 Win & Mac 对照表

    2024-04-03 11:28:01       37 阅读

  22. openshift和k8s的差别

    2024-04-03 11:28:01       34 阅读

  26. 算法训练day56leetcode300.最长递增子序列674最长连续递增序列

    2024-04-03 11:28:01       32 阅读

  27. RuoYi单体版Table行内编辑

    2024-04-03 11:28:01       32 阅读