目录
引言
索引是一种特殊的数据库结构,是提高数据库性能的重要方式用来快速查询数据库表中特定的记录,mysql中所有的数据类型都可以被索引
分类:mysql中的索引包括 普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引
索引概述
索引是由数据表中一列或多列组合而成,创建索引的目的:优化数据库的查询速度。
其中,用户创建的索引指向数据库中具体数据所在位置。当用户通过索引查询数据库中的数据时,不需要遍历所有数据库中的所有数据,大幅度提高了查询效率。
MySQL索引概述
用户通过索引查询数据,不但可以提高查询速度同时也能降低服务器的负载。在查询数据的过程中,系统不必遍历数据表中的所有记录,而是查询索引列。
缺点:使用索引可以提高检索数据的速度,队友依赖关系的字表和父表之间联合查询时,可以提高查询速度,并且可以提高整体的系统性能。但是,创建索引和维护需要消耗时间,并且该消耗时间与数据量成正比;另外,索引需要占用物理空间,给数据的维护造成很多麻烦
※注意
索引会影响用户对数据库的插入操作。因为,向有索引的表中插入记录时,数据库系统会按照索引进行排序。所以,用户可以索引删除后,插入数据,当数据插入操作完成后,用户可以重新创建索引。
不同存储引擎定义的每个表最大索引数和最大索引长度不同。所有存储引擎对每个表至少支持16个索引。总长度至少为256字节。索引有两种存储类型:B树(BTREE)索引和哈希(hash)索引,B树为系统默认索引方法。
MySQL索引分类
MySQL的索引包括6类:普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引。
普通索引
即不应用任何限制条件的索引,该索引可以在任何数据类型中创建。字段本身的约束条件可以判断其值是否为空或唯一。创建该类型索引后,用户在查询时,便可以通过索引进行查询。在某数据表的某一字段中创建普通索引后,用户需要查询数据时只需要根据该索引进行查询即可。
唯一性索引
使用unique参数可以设置唯一性索引。创建该索引时,索引的值必须唯一,通过唯一索引,用户可以快速定位某条记录,主键是一种特殊唯一性索引。
全文索引
使用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;