SQL 如何建立索引来加快数据库的查询

1. 什么是索引

索引是一个单独存储在磁盘上的数据库结构,它们包含着对数据表里所有记录的引用指针

在数据库中,索引用来加速对表的查询,索引技术通过使用快速路径访问的方法快速定位数据,减少了磁盘的I/O

可以把索引比作书的目录,每一张表都要有索引,就像每一本书都应该有目录一样。有了索引,数据库的检索速度可以大大提高
在这里插入图片描述

2. 索引的特点

索引是作用于表查询的一项技术,因此创建索引时,需要确保该索引是应用在SQL查询语句的条件,一般作为where子句的条件。

在表现形式上,索引表现为一张表,该表保存了主键与索引字段,并指向实体表的记录。索引独立于数据表存放,但不能独立存在,必须属于某个表。索引由数据库自动维护,数据表被删除时,该表上的索引自动被删除。

不过需要注意的是,虽然索引极大地提高了查询速度,同时却会降低更新表的速度,如对表进行insert、update和delete的速度会被降低。 因为更新表时,数据库不仅需要更新表数据,还要更新索
引文件,此外由于索引是独立于表存在的“表”,因此建立索引会占用一定的磁盘空间。

3. 索引的分类

  1. 普通索引
    最基本的索引,它没有任何限制,用于加速查询
  2. 唯一索引(unique)
    索引列的值必须唯一,但允许有空值。如果是组合索列值的组合必须唯一
  3. 主键索引(primary key)
    是一种特殊的唯 一索引,一个表只能有一个主键,不允许有空值,主键索引一般是在表建立的同时自动创建
  4. 单列索引
    指单个字段上创建索引
  5. 组合索引
    指条个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合的规则
  6. 全文索引(fulltext)
    该索引不直接与索引中的值相比较,而是查找文本中的关键字
  7. 空间索引(spatial)
    MySQL 5.7.4实验室版本中,InnoDB存储引擎新增了对于几何数据空间索引的支持

4. 索引的设计原则

设计索引应遵循如下原则:

  1. 只建立适量的索引。 索引并非越多越好,过多的索引会降低表的修改速度以及占用额外的存储空间。
  2. 只在需要大量查询的表中建立大量索引。 应该避免对经常更新的表进行过多的索引,并且索引中的列要尽可能少。
  3. 不在数据量小的表建立索引。 数据量小的表没有必要使用索引
  4. 不在不同值少的列上建立索引。 如果表的某一列中大部分数据都是重复的,则没有必要建立索引
  5. 指定唯一索引。 当表的某一列数据被指定为unique是,可以建立唯一索引
  6. 在频繁操作的列上建立索引。 若想要建立索引,则应选择频繁排序(order by)或分组(group by)的列建立索引

5. 创建索引

5.1. 自动创建索引

当在表上定义primary key或者unique约束条件时,数据库会自动创建一个对应的索引,也就是创建主键索引和唯一索引。

5.2. 手动创建索引

5.2.1. 创建表时创建索引

在建表的同时创建索引的语法为:

create table [表名](
	[属性名1] [数据类型1] [约束1],
	...
	[属性名n] [数据类型n] [约束n],

	[unique | fulltext | spatial] index|key [索引名](
		[属性名1] [(长度)] [asc | desc],
		...
		[属性名n] [(长度)] [asc | desc]
	)
);

例如
建立普通索引

create table normal_table(
	id int(20) primary key,
	username varchar(20),
	email varchar(20),
	-- 在id列建立普通索引
	index normal_index(id)
);

建立唯一索引

create table unique_table(
	id int(20) primary key,
	username varchar(20),
	email varchar(20),
	-- 在email列建立唯一索引,并升序排列
	unique index uniquel_index(email asc)
);

建立全文索引

create table fulltext_table(
	id int(20) primary key,
	username varchar(20),
	email varchar(20),
	-- 在username列建立全文索引
	fulltext index fulltext_index(username)
);

建立单列索引

create table single_table(
	id int(20) primary key,
	username varchar(20),
	email varchar(20),
	-- 在username列建立索引,索引长度为9
	index single_index(username(9))
);

建立多列索引

create table multi_table(
	id int(20) primary key,
	username varchar(20),
	email varchar(20),
	-- 在username列和email列建立索引,索引长度分别为9和10
	index multi_index(
		username(9),
		email(10)
	)
);
5.2.2. 在已经存在的表上创建索引
5.2.2.1. alter语句创建索引

语法:

alter table [表名] add [unique|fulltext|spatial] [index|key] [索引名](
		[属性名1] [(长度)] [asc | desc],
		...
		[属性名n] [(长度)] [asc | desc]
	);

例如:
建立普通索引

-- 在normal_table表的id列建立名为normal_index的普通索引
alter table normal_table add index normal_index(id);

建立唯一索引

-- 在unique_table表的username列建立名为unique_index的唯一索引
alter table unique_table add unique index unique_index(username);

建立多列索引

-- 在multi_table表的username和email列建立名为multi_index的多列索引
alter table multi_table add index multi_index(username, email);

其他类型的索引同理

5.2.2.2. create语句创建索引

语法:

create unique [unique|fulltext|spatial] index [索引名] ON [表名](
		[属性名1] [(长度)] [asc | desc],
		...
		[属性名n] [(长度)] [asc | desc]
	);

例如
建立普通索引

-- 在normal_table表的id列建立名为normal_index的普通索引
create index normal_index on normal_table(id); 

建立唯一索引

-- 在unique_table表的username列建立名为unique_index的唯一索引
create unique index unique_index on unique_table(username); 

建立多列索引

-- 在multi_table表的username和email列建立名为multi_index的多列索引
create index multi_table on multi_table(username, email); 

其他类型的索引同理

6. 查看索引

6.1. explain关键字查看索引

explain关键字用于查看和分析SQL索引使用情况
语法:

explain select * from [表名];

使用里explain语句后,会返回一张表:

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra

其中对表中字段的说明可以参考这篇博客:mysql中explain查看sql语句索引使用情况

例如:

-- 查看multi_table表中的所有使用情况
explain select * from multi_table;

结果为:

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEmulti_tableindexmulti_table21661100.00Using index

6.2. show index关键字查看索引

show index关键字用于查看某一表中的所有索引
语法:

show index from [表名];

使用里show index语句后,会返回一张表:

TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpression

其中对表中字段的说明可以参考这篇博客:MySQL SHOW INDEX 的语法解析

例如:

-- 查看multi_table表中的全部索引
show index from multi_table;

结果为:

TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpression
multi_table0PRIMARY1idA0BTREEYES
multi_table1multi_index1usernameA09YESBTREEYES
multi_table1multi_index2emailA010YESBTREEYES
multi_table1multi_table21usernameA0YESBTREEYES
multi_table1multi_table22emailA0YESBTREEYES

7. 删除索引

7.1. alter关键字删除索引

语法:

alter table [表名] drop index [索引名];

例如

-- 从multi_table表中删除multi_index索引
alter table multi_table drop index multi_index;

7.2. drop关键字删除索引

语法:

drop index [索引名] on [表名];

例如

-- 从multi_table表中删除multi_index索引
drop index multi_index on multi_table;
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 撸撸猫 设计师:设计师小姐姐 返回首页