MySQL（二）——Explain详情与索引最佳实践

最新推荐文章于 2024-12-29 16:05:07 发布
最新推荐文章于 2024-12-29 16:05:07 发布
阅读量304 收藏 1
点赞数 1
CC 4.0 BY-SA版权
分类专栏： MySQL 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/yemuxiaweiliang/article/details/106044111
本文详细介绍了MySQL的索引类型，包括普通索引、唯一索引和主键索引，以及如何创建和使用它们。通过Explain分析查询性能，探讨了索引的最佳实践，包括单列索引、组合索引、全文索引和空间索引的使用场景。此外，还讨论了如何在已有的表上创建和删除索引，以及如何通过索引来优化查询效率。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

系列文章目录

MySQL（一）——索引底层数据结构与算法
MySQL（二）——Explain详情与索引最佳实践
MySQL（三）——MySQL的内部组件结构及bin-log归档
MySQL（四）——MySQL索引优化实战
MySQL（五）——MySQL索引优化实战（多表联查优化）
MySQL（六）——深入理解MySQL事务隔离级别与锁机制
MySQL（七）——深入理解MVCC与BufferPool缓存机制

Explain详情与索引最佳实践

MySQL的索引

使用索引是为了加快查询数据库表数据的速度。
索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引
MyISAM和InnoDB存储引擎：只支持BTREE索引
MEMORY/HEAP存储引擎：支持HASH和BTREE索引

索引分类

单列索引(普通索引，唯一索引，主键索引)

一个索引只包含单个列，但一个表中可以有多个单列索引。
普通索引：允许索引列中有重复值和空值
唯一索引：不允许重复，但是允许为空值，
主键索引：不允许重复和空值

索引的创建

CREATE TABLE book (
	bookid INT NOT NULL,
	bookname VARCHAR (255) NOT NULL,
	AUTHORS VARCHAR (255) NOT NULL,
	info VARCHAR (255) NULL,
	COMMENT VARCHAR (255) NULL,
	year_publication YEAR NOT NULL,
	INDEX (year_publication)
);

CREATE TABLE book1 (
	bookid INT NOT NULL,
	bookname VARCHAR (255) NOT NULL,
	AUTHORS VARCHAR (255) NOT NULL,
	info VARCHAR (255) NULL,
	COMMENT VARCHAR (255) NULL,
	year_publication YEAR NOT NULL,
	KEY (year_publication)
);

在这里插入图片描述

EXPLAIN SELECT * FROM book WHERE year_publication = 1990;

在这里插入图片描述

项目介绍
id:SELECT识别符。这是SELECT的查询序列号,也就是一条语句中，该select是第几次出现。在此语句中，select就只有一个，所以是1.
select_type:所使用的SELECT查询类型，SIMPLE表示为简单的SELECT，不实用UNION或子查询，就为简单的SELECT。也就是说在该SELECT查询时会使用索引。其他取值，PRIMARY：最外面的SELECT.在拥有子查询时，就会出现两个以上的SELECT。UNION：union(两张表连接)中的第二个或后面的select语句 SUBQUERY：在子查询中，第二SELECT。
table：数据表的名字。他们按被读取的先后顺序排列，这里因为只查询一张表，所以只显示book
type：指定本数据表和其他数据表之间的关联关系，该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref用于连接程序使用键的最左前缀或者是该键不是 primary key 或 unique索引（换句话说，就是连接程序无法根据键值只取得一条记录）的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。可能的取值有 system、const、eq_ref、index和All
possible_keys：MySQL在搜索数据记录时可以选用的各个索引，该表中就只有一个索引，year_publication
key：实际选用的索引
key_len：显示了mysql使用索引的长度(也就是使用的索引个数)，当 key 字段的值为 null时，索引的长度就是 null。注意，key_len的值可以告诉你在联合索引中mysql会真正使用了哪些索引。这里就使用了1个索引，所以为1，
ref:给出关联关系中另一个数据表中数据列的名字。常量（const），这里使用的是1990，就是常量。
rows：MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。
extra：提供了与关联操作有关的信息，没有则什么都不写。

我们最主要的是看possible_keys和key 这两个属性，上面显示了key为year_publication。说明使用了索引。

创建唯一索引

CREATE TABLE t1 (
	id INT NOT NULL,
	NAME CHAR (30) NOT NULL,
	UNIQUE INDEX UniqIdx (id)
);

在这里插入图片描述

SHOW CREATE TABLE t1

INSERT INTO t1 VALUES(1,'xxx');

EXPLAIN SELECT * FROM t1 WHERE id = 1

在这里插入图片描述

EXPLAIN SELECT * FROM t1 WHERE id = 5

在这里插入图片描述

创建主键索引

CREATE TABLE t2 (
	id INT NOT NULL,
	NAME CHAR (10),
	PRIMARY KEY (id)
);

INSERT INTO t2 VALUES(1,'QQQ');

EXPLAIN SELECT * FROM t2 WHERE id = 1　

在这里插入图片描述

组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
创建一个表t3，在表中的id、name和age字段上建立组合索引

CREATE TABLE t3 (
	id INT NOT NULL,
	NAME CHAR (30) NOT NULL,
	age INT NOT NULL,
	info VARCHAR (255),
	INDEX MultiIdx (id, NAME, age)
);

在这里插入图片描述

解释最左前缀

组合索引就是遵从了最左前缀，利用索引中最左边的列集来匹配行，这样的列集称为最左前缀，例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会使用索引，比如，age或者（name，age）组合就不会使用索引查询。

EXPLAIN SELECT * FROM t3 WHERE age = 3 AND name = 'bob'

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

全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。索引总是对整个列进行，不支持前缀索引，
就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行

CREATE TABLE t4 (
	id INT NOT NULL,
	NAME CHAR (30) NOT NULL,
	age INT NOT NULL,
	info VARCHAR (255),
	FULLTEXT INDEX FullTxtIdx (info)
) ENGINE = MyISAM;

在这里插入图片描述

SHOW CREATE TABLE t4

INSERT INTO t4 VALUES(8,'AAA',3,'text is so good，hei，my name is bob'),(9,'BBB',4,'my name is gorlr');

SELECT * FROM t4 WHERE MATCH(info) AGAINST('name' IN BOOLEAN MODE);

EXPLAIN SELECT * FROM t4 WHERE MATCH(info) AGAINST('gorlr');

show variables like 'ft%'
repair table t4 quick

在这里插入图片描述

空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。
在创建空间索引时，使用SPATIAL关键字。
要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

CREATE TABLE t5 (
	g GEOMETRY NOT NULL,
	SPATIAL INDEX spatIdx (g)
) ENGINE = MyISAM;

SHOW CREATE TABLE t5

在这里插入图片描述

在已经存在的表上创建索引

ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]

查看表中创建的索引

SHOW INDEX FROM book

在这里插入图片描述

字段内容
Table:创建索引的表
Non_unique：表示索引非唯一，1代表 非唯一索引， 0代表 唯一索引，意思就是该索引是不是唯一索引
Key_name：索引名称
Seq_in_index表示该字段在索引中的位置，单列索引的话该值为1，组合索引为每个字段在索引定义中的顺序(这个只需要知道单列索引该值就为1，组合索引为别的)
Column_name：表示定义索引的列字段
Sub_part：表示索引的长度
Null：表示该字段是否能为空值
Index_type：表示索引类型
ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));

使用CREATE INDEX创建索引。

CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] 索引名称 ON 表名(创建索引的字段名[length])

CREATE INDEX BkBookNameIdx ON book(bookname);

在这里插入图片描述

删除索引

格式一：ALTER TABLE 表名 DROP INDEX 索引名。
ALTER TABLE book DROP INDEX BkBookNameIdx;

在这里插入图片描述

格式二：DROP INDEX 索引名 ON 表名；
DROP INDEX BkNameIdx ON book;

在这里插入图片描述

【MySQL】Explain详解与索引最佳实践
金鳞踏雨
10-01 1476
使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句 或 是结构的性能瓶颈。在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL。注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中。
MySQL —— explain 查看执行计划与 MySQL 优化
longool的博客
11-17 2217
可能存在的问题：在 sort_buffer 中，因为是把所有字段都取出，所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能去 sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多路合并），排完再取 sort_buffer 容量大小，再排……索引嵌套循环连接是基于索引进行连接的算法，索引是基于内层表的，通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录进行比较， 从而利用索引的查询减少了对内层表的匹配次数，优势极大的提升了 join的性能。
MySQL索引操作：创建、添加和删除索引
NullPointerException的博客
11-24 504
如果对索引的作用不太了解的建议先阅读上一篇博文：MySQL索引的作用和分类介绍 创建索引 创建表的时候创建索引 格式： CREATE TABLE 表名[字段名 字段类型] [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [索引名字] (字段名[length]) 　　[ASC|DESC] 普通创建表语句　　　　　　　　 设置什么样的索引(唯一、...
mysql删除索引 增加索引
易的博客
07-31 320
使用ALTER TABLE语句创建索引 alter table table_name add index index_name (column_list) ; alter table table_name add unique (column_list) ; alter table table_name add primary key (column_list) ; 删除索引 drop ...
MySQL删除自增索引_mysql-添加删除索引
weixin_35019131的博客
01-27 436
1.添加PRIMARY KEY(主键索引)语法：ALTER TABLE `表名` ADD PRIMARY KEY ( `列名称` )mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column_name` )添加自增属性的主键索引：语法：alter table 表名 change 列名称 新的列名称 类型 primary key auto_...
添加索引和删除索引
weixin_43537239的博客
06-30 553
1.添加PRIMARY KEY（主键索引） ALTER TABLE table_name ADD PRIMARY KEY ( column ) 2.添加UNIQUE(唯一索引) ALTER TABLE table_name ADD UNIQUE ( column ) 3.添加INDEX(普通索引) ALTER TABLE table_name ADD INDEX index_name ( column ) 4.添加FULLTEXT(全文索引) ALTER TABLE table_name ADD FULLT
mysql添加删除索引
weixin_37460672的博客
05-16 217
1.主键 添加： alter table table_name add primary key（`column`） 删除： alter table table_name drop primary key
MySQL——索引与EXPLAIN
等一杯咖啡的博客
12-05 1417
前言 本文内容主要参考自《高性能MySQL》第5章以及《MySQL DBA 修炼之道》书中的第三章，算是原书的实践与补充。 上次主要讲了MySQL的基本操作，这次来谈谈索引与EXPLAIN。 I. 什么是索引？ 想要深入的学习MySQL相关技术，而不仅仅停留在简单CURD，能够写出百万数据中分分钟查出需要数据的SQL，首先就需要掌握索引技术。那么什么是索引呢？ 要理解MySQL中索引是如何工作的，...
掌握MySQL索引：策略与最佳实践——索引使用（三）
zhangruibo_code的博客
08-07 1001
表中存在联合索引 idx_user_pro_age_sta (关联了三个字段profession, age, status)，该索引也是一个二级索引，该叶子节点下面的是这一行的主键id。虽然是根据name字段查询，查询二级索引，但是由于查询返回在字段为 id，name，在name的二级索 引中，这两个值都是可以直接获取到的，因为覆盖索引，所以不需要回表查询，性能高。由于在name的二级索引中，不包含gender，所以，需要两次索引扫描，也就是需要回表查询，性能相对较差一点。
MySQL —— 索引
最新发布
m0_74824123的博客
12-29 877
既然红黑树不行，那我们可以考虑B树，这时一颗多路平衡树，由于是多路，所以可以降低书高，但是我们的MySQL还是不满意，觉得效率还不是不够高，于是MySQL 就使用B树的变形也就是B+树，我们在前面就知道B+树有一些特点：真实的数据都是保存在叶子节点上的，非叶子结点只是起到一个导航的作用，并且叶子结点是使用双向链表进行连接的，所以在数据库进行范围查找的时候十分方便。，这两行并不存储任何真实数据，而是最为数据行链表的头和尾【你可以理解为链表的两个空节点，一个是空的头节点，另一个是空的尾节点】
Mysql索引的添加与删除总结
t_jindao的博客
02-15 329
对索引的操作做了点总结,基本手打,没有去验证, 如果发现错误,帮忙验证一下, 或者使用时发现错误的地方可以指出一下,我好改正!谢谢   索引     主键索引     外键索引     唯一索引     普通索引     全文索引     多列索引,联合索引      主键索引:     添加主键索引         1 :              CREATE TABLE `user...
mysql 索引的使用 之 创建 、 删除
天下无双
03-17 1516
创建数据表 创建索引 create index username_index on users(username); 删除索引 drop index username_index on users select count(*) from users where username like 'f%'; // 在没有创建索引 与 创建索引的情况下 二者查询所耗时分别为 -- 0.037
MySQL索引的添加修改以及删除
weixin_53850894的博客
11-22 3417
在 MySQL 中，可以通过CREATEALTER, 和DROP语句来添加、修改和删除索引。
Mysql创建索引、删除索引、索引对查询的影响
weixin_58504010的博客
06-25 1570
删除索引的操作不会影响到表中的数据，但会释放与索引相关的存储空间，并可能提高某些写操作的性能（如INSERT、UPDATE和DELETE）。索引可以加快数据的检索速度，但也会增加插入、更新和删除操作的时间，因为索引本身也需要被维护。了解索引的用途：在删除索引之前，确保你了解该索引的用途。测试：在删除索引之前，最好在生产环境的副本或测试环境中测试删除索引对性能的影响。这种索引允许你在查询中使用这些列的任意组合进行高效的搜索。定期审查和优化你的索引策略，因为随着数据的变化，最佳的索引策略也可能会发生变化。
mysql索引的创建，删除
SFNES的博客
08-03 282
一：索引的创建可以在CREATE TABLE语句中进行， 也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。 删除索引可以利用ALTER TABLE或DROP INDEX语句来实现。 （1）使用ALTER TABLE语句创 alter table table_name add index index_name (column_list) ; alter table...
mysql创建索引
the_yiMI的博客
11-21 381
索引的创建 1创建表的时候创建 Create table  example( Id int, Gread float, [unique|fulltext|spatial] index|key [别名] （属性名1[(长度)]  [asc |desc]） );   结构：[unique|fulltext|spatial] index|key [别名] （属性名1[(长度)
MySQL（一）索引的创建和删除
dlx29376的博客
07-30 198
索引是存储引擎用于快速找到记录的一种数据结构，这是索引的基本功能。 索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。接下来将讲述如何创建、查看和删除索引。 索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。 目录 一、索引类别 二、创建索引 1．A...
mysql全文索引去除_定义MySQL全文搜索FULLTEXT索引
weixin_39782709的博客
01-21 543
在本教程中，您将学习如何定义全文索引，以便在MySQL中执行各种全文搜索。在表的列中执行全文搜索之前，必须对其数据进行索引。 每当列的数据更改时，MySQL将重新创建全文索引。在MySQL中，全文索引是一种名称为FULLTEXT的索引。MySQL支持对全文搜索启用列自动建立索引和重新索引数据。 MySQL 5.6或更高版本允许您为数据类型为MyISAM中的CHAR，VARCHAR或TEXT或Inn...
mysql-索引的创建和删除
power_to_go
04-27 185
MySQL InnoDB 存储引擎会为 primary key 和 unique 列自动创建 B+ 树索引。这两个列无须手动创建索引。 索引的添加 创建表时指定 create table test_demo( id int, name varchar(10), age int, address varchar(50), primary key(id...
MySQL查询优化实战：详解EXPLAIN命令与索引应用
MySQL提供了一个强大的工具——EXPLAIN命令，用于分析SELECT语句的执行计划，帮助开发者找出瓶颈。通过在SQL语句前添加"EXPLAIN"关键字，例如： ```sql EXPLAIN SELECT * FROM customer WHERE id ; ``` 该命令会...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

yemuxiaweiliang

你的鼓励将是我创作的最大动力

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

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

打赏作者

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

抵扣说明：

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

余额充值