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

文学随笔，生活点滴

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