MySQL中索引的定义及操作

举报
1+1=王 发表于 2022/12/16 10:42:29 2022/12/16
【摘要】 MySQL中索引的定义及操作

索引的定义

数据库中的索引就像一本书的目录,可以据此快速定位数据库中相关数据的所在位置。

在数据库中,索引被定义为一种特殊的数据结构,由数据库中的一列或多列组合而成,可以用来快速查询数据表中某一特定值的记录。

索引是在表的字段的基础上建立的一种数据库对象,它由DBA或者表的拥有者创建或撤销,他是创建表与表之间关联关系的基础。

索引的类型

普通、唯一、主键和全文索引

普通索引(INDEX)

普通索引是MySQL中的基本索引类型,允许在定义索引的列中插入空值和重复值。

创建普通索引的几种方式:

# 1.直接创建索引
create index index_name on table(column(length));

# 2. 以修改表结构创建
alter table table_name add index index_name on column(length);

# 3. 创建表的同时创建索引
create table user(
	id CHAR(6) not null,
	name CHAR(255) not null,
	primary key(id),
	index user_name(name(length));
)

唯一索引(UNIQUE INDEX)

唯一索引指索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。

创建唯一索引的几种方式:

# 1.直接创建索引
create unique index index_name on table(column(length));

# 2. 以修改表结构创建
alter table table_name add unique index index_name on column(length);

# 3. 创建表的同时创建索引
create table user(
	id CHAR(6) not null,
	name CHAR(255) not null,
	primary key(id),
	unique index user_name(name(length));
)

主键索引(PRIMARY KEY)

主键索引是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。

全文索引(FULLTEXT)

全文索引的类型为FULLTEXT,表示在定义索引的列上支持值的全文查找,允许插入重复值或空值。

全文索引主要用来查找文本中的关键字,而不是直接与索引中的值相比较。

MySQL中只有MyISAM存储引擎支持全文索引。

全文索引可以在进行创建表、更新表和创建索引时使用,目前只支持CHAR、VARCHAR、TEXT列创建全文索引。

单列索引和组合索引

  • 单列索引
    单列索引即一个索引只包含单个列,一个表可以有多个单列索引。
  • 组合索引
    组合索引是指组合表的多个字段创建的索引,,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。遵循最左前缀匹配原则

聚集索引和非聚集索引

  • 聚集索引
    聚集索引是索引结构和数据一起存放的索引,索引结构的叶子节点上包含着该行的所有信息,找到索引也就找到了数据。
  • 非聚集索引
    非聚集索引是索引结构和数据分开存放的索引,索引结构的叶子节点指向了数据的对应行,当需要访问数据时,在内存中先搜索索引,然后通过索引找到磁盘相应数据。
聚集索引 非聚集索引
一个表只能有一个 一个表可以有多个
物理连续 逻辑连续,物理不连续
查询快,插入慢(需要移动物理位置) 回表查询
索引的叶节点就是数据节点 索引的叶节点仍然是索引节点,通过一个指针指向对应的数据块。

索引的创建原则

1. 创建索引由专人完成

  • 索引由DBA或表的拥有者负责创建和撤销,其他用户不能随意操作。
  • 索引由系统自动选择,或由用户打开,用户可执行重建索引操作。

2.是否创建索引取决于表的数据量

  • 基本表中记录的数量越多,记录越长,越有必要创建索引。创建索引后,查询速度的提升效果会很明显。要避免对经常更新的表创建过多的索引,索引中的列也要尽可能少。

  • 数据量小的表最好不要使用索引。由于数据较少,查询花费的时间可能比遍历索引的时间还要短,因此,创建索引可能不会产生优化效果。对经常用于查询的字段应该创建索引,但要避免添加不必要的字段。

  • 索引要根据数据查询或数据处理的要求确定是否创建。对于查询频度高、实时性要求高的数据一定要建立索引。

3.索引数量要适度

  • 索引文件占用文件目录和存储空间,因此索引过多会加重系统负担。

  • 索引需要自身维护。当基本表的数据增加、删除或修改时,索引也会进行调整和更新,索引文件也要随之变化,以保持与基本表一致。

  • 索引过多会影响数据增、删、改的速度。索引并非越多越好,-张表中如果有大量的索引,不仅占用磁盘空间,而且还会影响INSERT、DELETE、UPDATE等操作的性能。

4.避免使用索引的情形

(1)包含太多重复值的字段。

(2)查询中很少被引用的字段。

(3)值特别长的字段。

(4)查询返回率很高的字段。

(5)具有很多NULL值的字段。

(6)需要经常增、删、改的字段。

(7)记录较少的基本表。

(8)需要频繁、大批量进行数据更新的基本表。

索引操作

创建索引

create table <table_name> [<fields_name> type]
[UNIQUE|FULLTEXT][INDEX|KEY] 
<index_name> (<column_name > [length]) [ASC|DESC]

查看索引

show index from <table_name> [from <database_name>]

删除索引

  1. 删除索引
drop index <index_name> from <table_name>
  1. 通过修改表结构删除
alter table <table_name> drop index <index_name>
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。