索引是一种特殊的数据库结构,用来快速查询数据库表中的特定记录,是提高数据库性能的重要方式。所有数据类型都可被索引。它创建在表上,对表中的一列或多列的值进行排序的一种结构。InnoDB和MyISAM支持B型树(BTREE)索引,MEMORY存储引擎支持HASH索引和BTREE索引。
优点是提高查询速度,缺点是创建和维护索引需要耗费时间。在插入大量记录时为了降低插入速度的影响(插入的同时数据库系统会按照索引进行排序)应该先删除表中的索引,然后插入数据,插入完成后再创建索引。
1.创建表的时候创建索引
create table 表名(属性名 数据类型 [完整性约束条件],
属性名 数据类型 [完整性约束条件]
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
[别名] (属性名1 [(长度)]) [ASC|DESC]
);
(1).创建普通索引
create table index1(id int,
name varchar(20),
index(id));
explain select * from index1 where id=1 \G;//若possible_keys和key值都为id说明id索引已经存在并作用
(2).创建唯一性索引
create table index2(id int unique,name varchar(20),unique index index2_id(id ASC));
(3).创建全文索引,只能在char,varchar,text类型的字段。
create table index3(id int,info varchar(20),fulltext index index3_info(info));
(4).创建单列索引
create table index4(id int,subject varchar(30),index index4_sub(subject(10)));
对应字符型数据,可以不查询全部信息,以提高查询速度。
(5).创建多列索引
create table index6(id int,name varchar(30),sex char(4),index index_ns(name,sex));
多列索引只有查询条件中使用了这些字段的第一个字段时,索引才会被使用。在优化查询速度时,可以考虑优化多列索引。
explain select * from index6 where name='ms'\G;//possible_keys和key值都为index_ns
explain select * from index6 where sex='n' \G; //值为null
2.在已存在的表上创建索引
create [unique|fulltext|spatial] index 索引名 on 表名 (属性名 [(长度)] [ASC|DESC]);
(1).创建普通索引
create index index7_id on index6(id);
(2).创建唯一性索引
create unique index index8_id on index7(course_id);
(3).创建全文索引
create fulltext index index9_id on index8(info);
(4).创建单列索引
create index index10_addr on index10(address(4));
(5).创建多列索引
create index index11_na on index11(name,address));
3. 用alter table创建索引
alter table 表名 add [unique|fulltext|spaital] index 索引名 (属性名 [(长度)] [ASC|DESC)];
eg: alter table index12 add unique index index12_id(course_id);
4.删除索引
drop index 索引名 on 表名;