Mysql的索引

已于 2024-04-03 10:40:44 修改
阅读量1.2k 收藏 19
点赞数 34
分类专栏： MySQL 文章标签： mysql 数据库
于 2024-04-02 19:25:59 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43413871/article/details/137284170
版权
本文详细介绍了MySQL中的各种索引类型（普通、唯一、主键和联合），创建和删除方法，以及索引失效的常见原因，强调了索引对数据库性能优化的重要性。
摘要由CSDN通过智能技术生成

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的事务

平时不搬砖
关注
专栏目录
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
查看mySQL数据库索引
04-11
mySQL索引查看 select * from information_schema.statistics where table_schema='数据库名称' and table_name = '表名称'
MySQL 索引
weixin_67470255的博客
06-16 862
索引的副作用：​●索引需要占用额外的磁盘空间。​​对于 MyISAM 引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。​​而 InnoDB 引擎的表数据文件本身就是索引文件。​​●在插入和修改数据时要花费更多的时间，因为索引也要随之变动。 表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是子表的主键，查询时可以快速定位。 记录数超过300行的表应该有索引。如果没有索引，需要把表遍历一遍，会严重影响数据库的性能。 经常与其他表进行连接的表，在连接字段上应该建立索引。
MYSQL索引【index】
最新发布
m0_70003285的博客
09-19 1145
类型、创建方法，还是性能优化？表结构---设计表---SQL预览表结构---设计表---SQL预览---索引索引是数据库中提高查询性能的重要工具。创建索引可以通过旋转：发生在增加、删除数据--绝对的平衡，每增加一个旋转一次--不追求严格的平衡，而是大致的平衡。红黑树的平衡性相对较弱，可能会导致树的高度较高，这可能会导致一些数据需要进行多次磁盘 IO 操作才能查询到，这也是 MySQL 没有选择红黑树的主要原因。
数据库索引
qq_51106567的博客
07-05 420
数据库索引，索引数据结构，常见索引失效关键字
mysql索引有哪些类型？
热门推荐
Iam8600的博客
08-20 3万+
MySQL目前主要有的索引类型为：普通索引、唯一索引、主键索引、组合索引、全文索引。 通过给字段添加索引可以提高数据的读取速度，提高项目的并发能力和抗压能力。索引优化时mysql中的一种优化方式。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。 下面我们来看看MySQL目前主要有的索引类型： 1、普通索引 普通索引是最基本的索引，它没有任何限制，值可以为空；仅加速查询。可以通过以下几种方式来创建或删除： 1）、直接创建索引 CREATE INDEX index_name ON ta
mysql的索引可以分为哪些类型
JustinMars的博客
06-13 479
MySQL的索引是用于提高查询性能的重要数据结构。不同类型的索引在不同的使用场景中具有不同的优势和适用性。
分享几道关于MySQL索引的重点面试题
01-19
但MySQL 索引你真的懂吗？这几道题带你了解索引的几个重要知识点 1. 什么是最左前缀原则？ 以下回答全部是基于MySQL的InnoDB引擎 例如对于下面这一张表 如果我们按照 name 字段来建立索引的话，采用B+树的结构，...
04-VIP-Mysql索引优化实战一.pdf
11-30
MySQL索引优化是数据库性能提升的关键环节，本篇主要探讨了几个关于MySQL索引使用和优化的重要知识点。 首先，创建了一个名为`employees`的员工记录表，其中包含`id`（主键）、`name`、`age`、`position`和`hire_...
快速了解MySQL 索引
12-14
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。 打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。 索引分...
MySQL索引
12-14
MySQL索引是数据库管理系统中的一种重要工具，它创建了一个排序的数据结构，目的是为了加快数据库表中数据的查询速度。在没有索引的情况下，查询数据时，数据库需要从第一条记录开始逐条检查，直到找到匹配的行，...
MySQL的索引类型有哪些
常备不懈
04-17 2089
MySQL数据库系统中的索引是用来加快搜索和检索数据库记录的数据结构。这些索引类似于书籍的目录，帮助MySQL快速定位数据表中的数据，而无需逐行扫描整个表。
MySQL有哪些索引？
一名后端程序猿
10-15 1万+
mysql中有主键索引、唯一索引、普通索引、组合索引、全文索引
数据库查询优化 --- 索引 Index
HelloWorld
01-23 1091
数据库查询优化 --- 索引 Index什么是索引索引的实现方法聚集索引 和 非聚簇索引聚集索引非聚集索引组合索引索引的使用适合使用索引的场景不适合使用索引的场景SQL创建索引 什么是索引 没有加索引的数据，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐 因为数据是无序的, 查找时需要一行一行查找, 非常慢 如果可以使用数据结构对数据进行储存, 可以大大减少查找时间 索引的实现方法 主流的实现方法是使用B+树, B+是一个平衡二叉树 将一个属性标为index, 则B+每个节点是属性的
MySQL系列：索引都有哪些类型
NIO4444
09-20 460
目录 主键索引 唯一索引 组合索引 普通索引 全文索引 主键索引 是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引 唯一索引 与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。 组合索引 指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。 普通索引 是最基本的索引，它没有任何限制。 ...
MySQL索引有哪些分类，你真的清楚吗？
wamp0001的博客
03-08 4120
按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引。 按物理存储分类可分为：聚簇索引、二级索引（辅助索引）。 按字段特性分类可分为：主键索引、普通索引、前缀索引。 按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）。
MySql数据库的索引
qq_15145603的博客
08-01 1038
索引（Index）是指在某种数据结构中用于快速定位和访问数据的一种数据结构。它可以看作是对数据的某个属性进行排序和组织的一种方式，以便于高效地搜索和检索数据。在数据库中，索引是用于提高查询效率的重要手段。通过创建索引，数据库系统可以按照指定的属性值进行排序，并将索引存储在内存或磁盘上，以快速定位特定的数据行。常见的索引类型包括B树索引、哈希索引、全文索引等。在计算机科学中，索引也可以指代一种数据结构，例如数组、链表等，用于存储和访问数据元素。总之，索引是一种用于加速数据查找和访问的数据结构或技术。
MySQL中的索引
qq_64064246的博客
08-05 2656
MySQL中删除索引使用ALTER TABLE或DROP INDEX语句，两者可实现相同的功能，DROP INDEX语句在内部被映射到一个ALTER TABLE语句中。提示 删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。可以看到，book表中已经没有名称为idx_aut_info的组合索引，删除索引成功。MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。先进行book_id条件的比较，然后再进行book_name比较，最后才是info。
Mysql数据库：索引管理
十七拾的博客
03-21 952
本文主要介绍如何查询、创建、删除mysql数据库的索引，希望对你有帮助！
mysql索引
05-29
MySQL索引是一种数据结构，可以帮助MySQL快速定位和访问表中的数据。使用索引可以提高查询效率，降低数据库的负载。下面是MySQL索引的一些基本概念和使用方法： 1. 索引类型 MySQL支持多种类型的索引，包括B树索引...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

平时不搬砖

创造不易，请动动你发财的小手

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值