MySQL数据库应用总结(九)—MySQL数据库索引的操作

SQL语法预览:

创建表的时候创建索引:【createtable 表名(字段名称 数据类型,uniquefulltextspatialindex索引名(索引字段名(索引长度)…));】

在已有表上增加索引:【alter table表名add uniquefulltextspatial index索引名(索引字段名(索引长度)…);】

在已有表上创建索引:【createuniquefulltextspatial index索引名on表名(索引字段名(索引长度)…);】

删除索引:【altertable 表名drop index索引名;】

删除索引:【drop index索引名on表名;】

详解:

索引简介

索引是对数据库表中的一列或多列的值进行排序的一种结构,使用索引可以提高数据库中特定数据的查询速度。

索引的含义

索引是一个单独的、存储在磁盘上的数据库结构,包含对数据表里所有记录的引用指针。 使用索引可以快速找出在某个或多个列中有一定值的行,MySQL中所有的列类型都可以被索引,对相关列使用索引是提高查询造作速度的最佳途径。

例如:假设某数据库有50000条记录,现在要执行这样一个查询:select * from table where num=20000。如果没有索引,必须遍历整个表,直到num等于20000的行被找到为止;如果在num列上创建索引,MySQL不需要逐一进行查找,直接在索引里面寻找20000,就可以得知这一行的位置。因此,提高了查询的速度。

索引的优缺点

索引的分类

MySQL数据库中的索引分一下几类:

创建索引原则

(1)索引并非越多越好

多了站磁盘空间,也影响一些语句执行速度。

(2)避免对经常更新的表做过多索引

对于经常要查询的字段创建索引。

(3)数据量小的表最好不要使用索引

数据量小的时候,遍历数据的时间较短,创建索引与否对查询效率影响不大时,可以不建立索引。

(4)在不同值少的列上不要建立索引

枚举数量少的字段值,可以不建立索引。例如性别字段值只有两个,则无需建立索引。

(5)数据唯一时指定唯一索引

使用唯一索引能保证列的数据完整性。

(6)在频繁进行排序或分组的列上建立组合索引

即在频繁进行group by或 order by的操作时,如果待排列的列有多个,则建立组合索引。

创建索引

MySQL支持多种方法在单个或多个列上创建索引:在创建表的定义语句create table…index(字段名)中指定索引列,使用修改表的语句alter table在已经存在的表中用create index语句创建索引列。

1

创建表的时候创建索引

创建普通索引

语法结构:createtable 表名(字段名,数据类型 列级别约束条件…index(字段名));

案例

创建数据库db_demo3,在该数据库中定义数据表tb_book1,包含字段图书编号、图书名称、作者、出版社、出版时间和图书信息,创建索引为出版时间,SQL语句如下:

命令语句

create database db_demo3;

use db_demo3;

create table tb_book1(bookid int(11) primary key not null,bookname varchar(255) not null,authors varchar(255) not null,press varchar(255),year_publication year not null,info varchar(255),index (year_publication));

执行结果:

由结果看到book1表上的year_publication字段成功建立索引,其索引名称是MySQL自动添加的。使用explain语句可以查看索引是否正在使用:

Explain语句输出结果的各行解释如下:

select_type行:查询类型,简单查询。

table行:数据库读取数据表的名称。

type行:指本数据库表与其他数据表之间的关联关系,可取值有system、const、eq_ref、ref、range、index和all。

possible_keys行:给出MySQL在搜素数据记录时渴望选用的各个索引。

key行:MySQL实际选用的索引

key_len行:给出索引按字节计算的长度,值越小,则查询速度越快。

ref行:给出关联关系中另一个数据表里数据列的名称。

rows行:MySQL从表里读出数据行的个数。

extra行:提供与关联有关的信息。

可以看到,possible_keys和key的值都为year_publication,说明查询时使用了索引。

创建唯一索引

语法结构:createtable 表名(字段名,数据类型 列级别约束条件…unique index索引名(字段名));

案例

创建数据表tb_index1,包含字段name和id,在id字段上创建唯一索引,SQL语句如下:

命令语句

create table tb_index1(id int(11) primary key not null,name varchar(25) not null, unique index UniqIdx(id));

执行结果:

创建单列索引

语法结构:createtable 表名(字段名,数据类型 列级别约束条件…index索引名(字段名(索引长度)));

案例

创建数据表tb_index2,包含字段name和id,在name字段上创建单列索引,SQL语句如下:

命令语句

create table tb_index2(id int(11) primary key not null,name varchar(25) not null, index SingleIdx(name(20)));

执行结果:

创建组合索引

语法结构:createtable 表名(字段名,数据类型 列级别约束条件…index 索引名(字段名1,字段名2,…,字段名n))

案例

创建数据表tb_index3,包含字段id、name、info和age字段,在id、name和age字段上创建组合索引,SQL语句如下:

命令语句

create table tb_index3(id int(11) not null,name char(30) not null,age int not null,info varchar(255), index MultiIdx(id,name,age));

执行结果:

创建全文索引

FULLTEXT全文索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引,并且只能在数据类型为char、varchar和text的列上建立全文索引。

语法结构:createtable 表名(字段名,数据类型 列级别约束条件…fulltext index索引名(字段名)) ENGINE=MyISAM;

案例

创建数据表tb_index4,包含字段id、name、info和age字段,在info字段上创建全文索引,SQL语句如下:

命令语句

create table tb_index4(id int(11) not null,name char(30) not null,age int(11) not null,info varchar(255), fulltext index MultiIdx(info)) ENGINE =MyISAM;

执行结果:

创建空间索引

空间索引必须在MyISAM类型的表中创建,且空间类型的字段不能为空。

语法结构:createtable 表名(字段名,数据类型 列级别约束条件…spatial index 索引名(字段名)) ENGINE=MyISAM;

案例

创建数据表tb_index5,包含字段id、name、info和age字段,在info字段上创建空间索引,SQL语句如下:

命令语句

create table tb_index5(jh geometry not null, spatial index SpatIdx(jh)) ENGINE=MyISAM;

执行结果:

2

在已有表上创建索引

在已有字段的表中创建索引,可以使用alter table语句或者create index语句。

使用alter table … add index…语法创建索引

创建普通索引

与创建表时创建索引的语法不同的是,这里使用了alter table 语句和add关键字,add表示向表中增加索引。

结构:altertable 表名addindex 索引名(字段名(索引长度));

增加索引之前,先用show index语句查看指定表中已经创建的索引:

结构:showindexfrom表名 \G;

执行结果:

上面主要参数各行解释如下:

table行:查看的当前数据表的名称。

Non_unique行:索引非唯一。1代表真表示不是唯一索引,0表示假表示是唯一索引。

Key_name行:主键,也是索引。

Seq_in_index行:字段在索引中的位置,单列索引该值为1,组合字段为每个字段在索引定义中的顺序。

Column_name行:当前索引的字段名。

Cardinality行:索引基数。

Sub_part行:表示索引长度。

Packed行:索引包。

Null行:表示该字段是否能为空值。

Index_type行:表示索引类型。

Comment行:表示注释。

可以看到Key_name的值都为PRIMARY和year_publication(未定义索引名默认为索引字段名),说明查有两个索引。

案例

在tb_book1表中的字段bookname字段上增加索引名为BKNameIdx的普通索引,SQL语句如下:

命令语句

alter table tb_book1 add index BKNameIdx(bookname(30));

执行结果:

可以看到,现在表中又新增一个索引,即通过alter table语句添加的名称为BKNameIdx的索引,该索引为非唯一索引,长度为30。

创建唯一索引

语法结构:altertable 表名addunique index索引名(字段名);

案例

在数据表tb_book1字段bookid上建立名为UniqidIdx的唯一索引,SQL语句如下:

命令语句

alter table tb_book1 add unique index UniqidIdx(bookid);

执行结果:

创建单列索引

语法结构:altertable 表名add index 索引名(字段名(索引长度));

案例

在数据表tb_book1字段press上建立名为SigBKIdx的单列索引,SQL语句如下:

命令语句

alter table tb_book1 add index SigBKIdx(press);

执行结果:

创建组合索引

语法结构:altertable 表名add index索引名(字段名1(索引长度),字段名2(索引长度)…);

案例

在数据表tb_book1字段authors和info上建立名为ZHIdx的组合索引,SQL语句如下:

命令语句

alter table tb_book1 add index ZHIdx(authors(20),info(50));

执行结果:

创建全文索引

FULLTEXT全文索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引,并且只能在数据类型为char、varchar和text的列上建立全文索引。

语法结构:createtable 表名(字段名,数据类型 列级别约束条件…fulltext index索引名(字段名)) ENGINE=MyISAM;

案例

创建表tb_index6,在数据表tb_index6字段info上建立名为infoFTIdx的全文索引,SQL语句如下:先创建表

命令语句

create table tb_index6(id int not null,info char(255)) ENGINE=MyISAM;

alter table tb_index6 add fulltext index infoFTIdx(info);

执行结果:

创建空间索引

语法结构:altertable 表名add spatial index 索引名(字段名);

案例

创建表tb_index7,在数据表tb_index7字段jh上建立名为spatIdx的空间索引,SQL语句如下:先创建表:

命令语句

create table tb_index7(jh geometry not null) ENGINE=MyISAM;

alter table tb_index7 add spatial index spatIdx(jh);

执行结果:

使用create index …语法创建索引

创建普通索引

这里使用了create index语句向表中增加索引。先创建一个新表tb_book2,方便案例师范,因为同一字段不能同时创建多个索引。

命令语句

create table tb_book2(bookid int(11) not null,bookname varchar(255) not null,authors varchar(255) not null,press varchar(255),year_publication year not null,info varchar(255));

语法结构:createindex索引名on表名(字段名(索引长度));

案例

在tb_book2表中的字段bookname字段上增加索引名为BKNameIdx的普通索引,SQL语句如下:

命令语句

create index BKNameIdx on tb_book2(bookname(30));

执行结果:

可以看到,create index语句成功创建名称为BKNameIdx的索引,该索引为非唯一索引,长度为30。

创建唯一索引

语法结构:create unique index索引名on表名(字段名);

案例

在数据表tb_book2字段bookid上建立名为UniqidIdx的唯一索引,SQL语句如下:

命令语句

create unique index UniqidIdx on tb_book2(bookid);

执行结果:

创建单列索引

语法结构:createindex索引名on表名(字段名(索引长度));

案例

在数据表tb_book2字段press上建立名为SigIdx的单列索引,SQL语句如下:

命令语句

create index SigIdx on tb_book2(press);

执行结果:

创建组合索引

语法结构:create index索引名on表名(字段名1(索引长度),字段名2(索引长度)…);

案例

在数据表tb_book2字段authors和info上建立名为ZHIdx的组合索引,SQL语句如下:

命令语句

create index ZHIdx on tb_book2(authors(20),info(50));

执行结果:

创建全文索引

语法结构:createfulltext index索引名on表明(字段名);

案例

创建表tb_index8,在数据表tb_index8字段info上建立名为infoFTIdx的全文索引,SQL语句如下:先创建表:

命令语句

create table tb_index8(id int not null,info char(255)) ENGINE=MyISAM;

create fulltext index infoFTIdx on tb_index8(info);

执行结果:

创建空间索引

语法结构:create spatial index索引名on表名(字段名);

案例

创建表tb_index9,在数据表tb_index9字段jh上建立名为spatIdx的空间索引,SQL语句如下:

命令语句

create table tb_index9(jh geometry not null) ENGINE=MyISAM;

create spatial index spatIdx on tb_index9(jh);

执行结果:

删除索引

1

使用alter table语句删除索引

语法结构:alter table表名drop index索引名;

案例

删除数据表tb_book2字段bookid上名为UniqidIdx的唯一索引,首先查看tb_book2表是否有名为UniqiIdx的索引,SQL语句如下:

命令语句

show create table tb_book2 /G;

alter table tb_book2 drop index UniqidIdx;

执行结果:

2

使用drop index语句删除索引

语法结构:drop index索引名on表名;

案例

删除数据表tb_book2字段bookname上名为的普通索引,SQL语句如下:

首先查看tb_book2表是否有名为BKNameIdx的索引。

命令语句

show create table tb_book2 /G;

drop index BKNameIdx on tb_book2;

执行结果:

以上是关于表的所有操作,希望对你有所帮助。

SQL语法总结:

创建表的时候创建索引:【createtable 表名(字段名称 数据类型,uniquefulltextspatialindex索引名(索引字段名(索引长度)…));】

在已有表上增加索引:【alter table表名add uniquefulltextspatial index索引名(索引字段名(索引长度)…);】

在已有表上创建索引:【createuniquefulltextspatial index索引名on表名(索引字段名(索引长度)…);】

删除索引:【altertable 表名drop index索引名;】

删除索引:【drop index索引名on表名;】

end

欢迎关注互动|未来科技008

欢迎关注互动|十年之前diary

十年之前diary

文学随笔,生活点滴

广读胸中有本,勤写笔下生辉

  • 发表于:
  • 原文链接:http://kuaibao.qq.com/s/20180125G0XRX000?refer=cp_1026

同媒体快讯

相关快讯

扫码关注云+社区