跳至主要內容

MySQL索引

大约 7 分钟约 1955 字

索引是一种特殊的数据库结构,是提高数据库性能的重要方式,可以用来快速查询数据库表中的特定记录,MySQL中所有的数据类型都可以被索引。

我们来看一个数据表有索引和无索引时检索相同数据的时间。

首先看一下表的结构:

运行结果
运行结果

我们为num1添加了索引,num2没有。

然后我们查看里面的数据。

运行结果
运行结果

在此表中添加了两个字段在这两个字段中num1添加了索引,num2没有添加索引,还添加了2432358条数据行,0~2432357,我们分别使用where语句设置查询条件为字段1和字段2的某个值,分别看一下查询速度。

运行结果
运行结果

可以看到使用索引后查询几乎可以秒出,但是不使用索引就会遍历整个表,非常耗费时间。

索引概述

索引分类

  • 普通索引

普通索引不应用任何限制条件的索引,该索引可以在任何数据类型中创建。字段本身的约束条件可以判断其值是否为空或唯一。在某数据表的某一字段中创建该类型索引后,用户便可通过索引进行查询。

  • 唯一索引

使用UNIQUE参数可以设置索引为唯一索引。创建该索引时,索引的值必须唯一,通过唯一索引,用户可以快速定位某条记录,主键是一种特殊的唯一索引。

  • 全文索引

使用FULLTEXT参数可以设置索引为全文索引。全文索引只能在CHAR、VARCHAR、TEXT类型的字段上创建。查询数据量较大的字符串类型的字段时,使用全文索引可以提高查询速度。如,查询带有回复内容的字段,可以应用全文索引方式。需要注意的是,在默认情况下,应用全文搜索大小写不敏感。如果索引的列使用二进制排序,可以使用对大小写敏感的全文索引。

  • 单列索引

顾名思义,单列索引即只对一个字段生效的索引,其可以包含上述3种索引方式。应用该索引时必须保证该索引的值对应一个字段。在添加单列索引时,若被索引的列为varchar、text等类型,则还可以为索引添加长度。

  • 多列索引

多列索引实在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段,用户可以通过这几个字段进行查询。要想应用该索引,用户必须使用这些字段的第一个字段。多列索引在使用联合查询时会起到优化作用,但是它不能优化单独使用被索引的列满足特定条件的查询。多列建索引比对每个列分别建索引更有优势,因为索引建立得越多就越占磁盘空间,在更新数据的时候速度会更慢。另外建立多列索引时,顺序也是需要注意的,应该将严格的索引放在前面,这样筛选的力度会更大,效率更高。

  • 空间索引

使用SPATIAL参数可以设置该索引为空间索引。空间索引只能建立在空间数据类型上,这样可以提高系统获取空间型数据的速度。MySQL中只有MyISAM存储引擎支持空间检索,而且索引的字段不能为空值。

创建索引

在创建数据表时创建索引

语法如下:

create table table_name(
属性名 数据类型[约束条件],
...,
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
[别名] (属性1[(长度)][ASC|DESC]
);

INDEX和KEY参数用于指定字段索引,选择其中一个即可。

创建普通索引

create table score(
    id int(11) not null auto_increment primary key,
    name varchar(50) not null ,
    math int(5) not null ,
    chinese int(5) not null ,
    english int(5) not null ,
    index(id)
);
image-20221020190902387

在最后一行可以看到,id已经被索引。

创建唯一索引

create table address(
id int(11) auto_increment primary key not null,
    name varchar(50),
    detail_address varchar(200),
    unique index address(id)
);
image-20221020191251199

在数据完整性约束里,若为字段设置主键约束,则MySQL会自动为该字段添加主键索引;若为字段设置候选键约束,则MySQL会自动为该字段设置唯一索引。

设置主键约束的部分语法为constraint index_col_name primary key (col_name),其中constaint关键字表示下面的语句代表添加完整性约束,index_col_name代表主键的名称,col_name代表字段,如下面为表a中的字段a添加主键约束,由于MySQL会自动为该字段添加主键索引,因而运行结果如下图。运行结果

下面是为字段b添加候选键约束的演示。

运行结果
运行结果

创建全文索引

create table cards(
id int(11) auto_increment primary key not null,
    name varchar(50),
    number bigint(11),
    info varchar(50),
    fulltext key card_info(info)
)
运行结果
运行结果

创建单列索引

create table telephone(
id int(11) primary key auto_increment not null,
    name varchar(50) not null,
    tel varchar(50) not null,
    index tel_num(tel(20))
);
运行结果
运行结果

创建多列索引

create table information(
id int(11) auto_increment primary key not null,
    name varchar(50) not null,
    sex varchar(5) not null,
    birthday varchar(50) not null,
    index info(name,sex)
);
运行结果
运行结果

创建空间索引

create table list(
id int(11) primary key auto_increment not null,
    goods geometry not null,
    spatial index listinfo(goods)
);
运行结果
运行结果

在已建立的数据表上创建索引

语法结构如下:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(属性 [(length)])[ASC|DESC]);

创建普通索引

在创建普通索引之前,先来看一下student的表的结构。

运行结果
运行结果
create index id_index on student(id);
运行结果
运行结果

创建唯一索引

语法:

CREATE UNIQUE INDEX 索引名 ON 数据表名称(字段名称);

我们在删除前面创建的索引之后为id添加一个唯一索引(删除索引的语句没有包含在下面的截图中):

create unique index id_uindex on student(id);
运行结果
运行结果

创建全文索引

语法如下:

CREATE FULLTEXT INDEX 索引名 ON 数据表名称(字段名称);

我们在演示之前先创建一个如下的数据表:

运行结果
运行结果
create fulltext index info_index on information(info);
运行结果
运行结果

创建单列索引

语法如下:

CREATE INDEX 索引名 ON 数据表名称(字段名称(长度));

设置字段名称长度,可以优化查询,提高查询效率。

我们为info中的information添加单列索引(先删除):

create index info_index on information(info(10));
运行结果
运行结果

创建多列索引

语法如下:

CREATE INDEX 索引名 ON 数据表名称(字段1,...);

如我们为数据表student添加一个多列索引:

create index multi_index on student(name,age);
运行结果
运行结果

创建空间索引

语法如下:

CREATE SPATIAL INDEX 索引名 ON 数据表名称(字段名称);

删除索引

若索引不使用也不删除,会既占用系统资源也降低表更新的速度。删除索引的语法如下:

DROP INDEX 索引名 ON 数据表名;

我们可以看到demo表中的字段num1存在我们之前创建过的索引,我们删除此索引。

运行结果
运行结果
drop index num on demo;
运行结果
运行结果

可以看到成功删除了索引。

上次编辑于:
贡献者: 棋.