【SQL】索引的使用及EXPLAIN关键字

原创于 2024-03-08 22:29:47 发布 · 1.2k 阅读

CC 4.0 BY-SA版权

文章标签：

5 篇文章

订阅专栏

本文详细介绍了SQL中主键索引、唯一索引、单值索引和复合索引的创建方法，以及如何删除索引。此外，还讲解了EXPLAIN关键字在查询优化中的作用，帮助理解数据库如何处理SQL语句。

索引的类型及创建

表中的列设定为主键之后会，数据库会为该列自动建立主SQL键索引
可随表创建时创建主键，如下：

CREATE TABLE 表名 (
	字段1 数据类型 PRIMARY KEY [AUTO_INCREMENT],
	...
);

也可以为已存在的表添加主键（但通常做法是在创建表时创建主键约束） ALTER TABLE 表名 ADD PRIMARY KEY(字段);

列表中的列创建唯一约束时，数据库会为该列自动建立唯一索引
可随表创建时创建唯一索引，如下两种方法

-- 不设置索引名，默认索引名为字段名
CREATE TABLE 表名 (
	字段1 数据类型 UNIQUE,
	...
);

-- 设置索引名
CREATE TABLE 表名 (
	字段1 数据类型,
	...
	UNIQUE  索引名(字段1)
);

也可以为已存在的表设置唯一约束同时建立唯一索引：ALTER TABLE 表名 ADD UNIQUE 索引名(字段名)

一个索引中只包含单个列，一张表中可以有多个单值索引
可随表创建时创建单值索引，如下

CREATE TABLE 表名 (
	字段1 数据类型,
	...
	KEY  索引名(字段1)
);

也可以为已存在的表创建单值索引：ALTER TABLE 表名 ADD INDEX 索引名(字段名)

一个索引中包含多个列
可随表创建时创建复合索引，如下

CREATE TABLE 表名 (
	字段1 数据类型,
	字段2 数据类型,
	字段3 数据类型,
	...
	KEY  索引名(字段1, 字段2, 字段3, ...)
);

也可以为已存在的表创建复合索引：ALTER TABLE 表名 ADD INDEX 索引名(字段1, 字段2, 字段3, ...)

对于主键索引的删除我们需要删除主键约束 ALTER TABLE 表名 DROP PRIMARY KEY
对于其余索引的删除我们可以执行 DROP INDEX 索引名 ON 表名

注意事项：
当我们删除唯一索引时，对应列的唯一约束也会被删除

用法：EXPLAIN SQL语句
使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道数据库是如何处理SQL语句的。可以用来分析查询语句或是表结构的性能瓶颈。

查询的序列号，表示查询中执行SELECT子句或操作表的顺序

当前操作关于那一张表

查询类型，常见类型有SIMPLE，DERIVED，PRIMARY，SUBQUERY

SIMPLE：简单的SELECT查询，查询中不包含任何子查询或者UNION
DERIVED：在FROM列表中包含的子查询会被标记为 DERIVED。数据库会递归这些子查询，把结果放在临时表里。例如 SELECT * FROM (SELECT t1.content FROM t1) s1; 其中的 SELECT t1.content FROM t1 段便会被标记为DERIVED
PRIMARY：查询中包含任何复杂的子部分，最外层的查询被标记为PRIMARY
SUBQUERY：在 SELECT或WHERE 列表中包含的子查询

访问类型排序

system：表只有一行记录（等于系统表），这是 CONST 类型的特例，平时基本不会出现
const：表示通过索引一次就找到了
eq_ref：唯一性索引扫描（字段设置了主键索引或唯一索引）例如：SELECT * FROM t1 JOIN t2 ON t1.id = t2.id 中首先会遍历找到t1中所有数据的id（type为ALL），并将该id分别与t2的id对照，因为t2的id是t2表的主键（每个值都唯一），因此对于t2表的操作会被标记为eq_ref
ref：非唯一性索引扫描
range：只检索给定范围的行，使用一个索引来选择行。例如 SELECT * FROM t1 WHERE t1.id > 0
index：当查询字段均建立过索引时，查询则会只遍历索引树，因为索引文件通常比数据文件小因此index会高效于ALL
ALL：将遍历全表以找到匹配的行