MYSQL数据库开发之路-----索引

使用fulltext参数可以设置索引为全文索引。全文索引只能创建在char、varchar或者text类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询熟读。例如，查询带有文章回复内容的字段，可以应用全文索引方式。在默认情况下，应用全文搜索大小写不敏感，如果索引的列使用二进制排序后，可以执行大小写敏感的全文索引。

单列索引

顾名思义，单列索引只对应一个字段的索引。可以包括上述的三种索引方式。应用该索引的条件只需要保证该索引值对应一个字段即可。

多列索引

在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，用户可以通过这几个字段进行查询。想要应用该索引，用户必须使用这些字段中的第一个字段。

空间索引

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

创建索引

创建索引是指在某个表中至少一列中创建索引，以便提高数据库性能。其中，创建索引可以提高表的访问速度。

在建立数据表时创建索引

在建立数据表时可以直接创建索引。语法格式如下：

create table table(

属性名数据类型[约束条件],

属性名数据类型[约束条件]

......

属性名数据类型

[ unique | fulltext | spatial ] {index|key}

[ 别名 ] （属性名1 [ (长度) ] [ asc | desc ]）

);

参数说明如下：

unique：可选项，表明索引为唯一性索引。

fulltext：可选项，表明索引为全文索引。

spatial：可选项，表明索引为空间索引。

index和key参数：用于指定字段索引，用户在选择时，只需要选择其中一种即可；另外别名为可选项，作用是给创建的索引取新名称；别名的参数如下：
属性名1：指定索引对应的字段名，该字段必须预先被定义

长度：可选项，指定索引的长度，必须是字符串类型才可以使用。

asc/desc:可选项，asc表示升序，desc表示降序。

普通索引创建

创建普通索引，即不添加unique、fulltext等任何参数。

#创建表名为score的数据表,并在该表的id字段上创建索引
CREATE TABLE score(
id INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
NAME VARCHAR(50) NOT NULL,
math INT(5) NOT NULL,
english INT(5) NOT NULL,
chinese INT(5) NOT NULL,
INDEX(id)
);
#查看该表的结构
SHOW CREATE TABLE score;

创建唯一性索引

创建唯一性索引与创建普通索引的语法结构大体相同，但是在创建唯一性索引的时候，需要使用unique参数进行约束。

#创建一个表名为address 的数据表并指定该表的id字段上建立唯一索引
CREATE TABLE address(
id INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
NAME VARCHAR(50),
addresss VARCHAR(200), #address为索引名 ，是一个别名
UNIQUE INDEX address(id ASC)
);

创建全文索引

与创建普通索引和唯一索引不同，全文索引只能作用在char，varchar、text类型的字段上。创建全文索引需要使用fulltext参数进行越是。

#创建一个名称为cards的数据表，并在该表的number字段上创建全文索引
CREATE TABLE cards(
id INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
NAME VARCHAR(50),
number BIGINT(11),
info VARCHAR(50),
FULLTEXT KEY cards_info(info)
);

创建单列索引

创建单列索引，即在数据表的单个字段上创建索引，创建该类型索引不需要引入约束参数，用户在创建时只需要指定单列字段名，即可创建单列索引。

#创建名称为telephone的数据表，并指定在tel字段上创建名称为tel_num的单列索引
CREATE TABLE telephone(
id INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
NAME VARCHAR(50) NOT NULL,
tel VARCHAR(50) NOT NULL,
INDEX tel_num(tel(20))
);

创建多列索引

与创建单列索引相仿，创建多列索引即指定表的多个字段即可实现。

#创建名称为information的数据表，并指定name和sex为多列索引
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)
);

创建空间索引

创建空间索引，需要设置spatial参数，同时只有MyISAM类型的存储引擎支持该类型的索引。而且，索引字段必须有非空约束。

#创建一个名称为list的数据表，并创建一个名为listinfo的空间索引
CREATE TABLE LIST(
id INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
goods GEOMETRY NOT NULL,
SPATIAL INDEX listinfo(goods)
) ENGINE=MYISAM;

空间类型：geometry、point、linestring、polygon等类型。

在已经建立的数据表中创建索引

在MySQL中，不但可以在用户创建数据表时创建索引，用户也可以在已经创建的表中，在已经存在的一个或几个字段上创建索引，语法格式如下：

create table [ unique | fulltext | spatial ] index index_name

on table_name(字段名 [ (length)] [ asc | desc ] );

参数说明：

index_name :索引名称，该参数作用是给用户创建索引赋予新的名称

table_name :表名，即指定创建索引的表名称

字段名：指定索引对应的字段名。该字段必须存在数据表中

创建普通索引

#普通索引，在tb_admin创建名为stu_info的索引
CREATE INDEX stu_info ON tb_admin(USER);

创建唯一性索引

在已经存在的数据表中创建唯一性索引的语法格式如下：

create unique index 索引名 on 数据表名称（字段名称）

create unique index index1_id on index1(cid);

创建全文索引

在已经存在的数据表中创建全文索引的语法格式如下：

create fulltext index 索引名 on 数据表名称（字段名称）

create fulltext index index2_info on index2(info);

创建单列索引

在已经存在的数据表中创建单列索引的语法格式如下：

create index 索引名 on 数据表名称（字段名称（长度））

create index index3_addr on index3(address(4));

创建多列索引

在已经存在的数据表中创建多列索引的语法格式如下：

create index 索引名 on 数据表名称（字段名称1，字段名称2，......）

create table index4_na on index4(name,address);

创建空间索引

在已经存在的数据表中创建空间索引的语法格式如下：

create spatial index 索引名 on 数据表名称（字段名称）

注意：spatial用来设置索引为空间索引。用户要操作的数据表类型必须为MyISAM类型。并且字段名称必须存在非空约束，否则将不能正常创建空间索引。

修改数据表结构添加索引

修改已经存在表上的索引，可以通过alter table 语句为数据表添加索引，其语法格式如下：

alter table table_name add

[ unique | fulltext | spatial ] index index_name(字段名 [ (length)] [ asc | desc ] )；

添加普通索引

alter table studentinfo ADD INDEX timer (time(20));

添加唯一索引

语法格式如下：

ALTER TABLE 表名 ADD UNIQUE INDEX 索引名称(字段名称);

添加全文索引

语法格式如下：

ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名称(字段名称);

添加单列索引

语法格式如下：

ALTER TABLE 表名 ADD INDEX 索引名称(字段名称(长度));

添加多列索引

语法格式如下：

ALTER TABLE 表名 ADD INDEX 索引名称(字段名称1，字段名称2，…);

添加空间索引

语法格式如下：

ALTER TABLE 表名 ADD SPATIAL INDEX 索引名称(字段名称);

删除索引

在MySQL中，创建索引后如果用户不再需要索引，则可以删除指定表中的索引。删除索引可以通过drop语句来实现，语法格式如下：
drop index index_name on table_name;

#删除address01表中的addr索引
DROP INDEX addr ON address01;

小怡的编程之路

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MYSQL数据库开发之路-----索引

索引是由数据表中一列或多列组合而成，创建索引的目的：优化数据库的查询速度。其中，用户创建的索引指向数据库中具体数据所在位置。当用户通过索引查询数据库中的数据时，不需要遍历所有数据库中的所有数据，大幅度提高了查询效率。用户通过索引查询数据，不但可以提高查询速度同时也能降低服务器的负载。在查询数据的过程中，系统不必遍历数据表中的所有记录，而是查询索引列。缺点：使用索引可以提高检索数据的速度，队友依赖关系的字表和父表之间联合查询时，可以提高查询速度，并且可以提高整体的系统性能。...
复制链接

扫一扫