--------------------------------------------------------- 小路原创 ------------------------------------------------
索引简介:数据库对象索引与书本的目录相似,创建索引主要是提高从表中检索数据的速度,还可以保障字段的唯一性,从而实现数据库表的完整性。
过多地创建索引会占据许多的磁盘空间。
根据索引的存储类型,可以将索引分为B型索引(BTREE)和哈希索引(HASH)。
注意:InnoDB和MyISAM存储引擎支持BTREE类型索引,MEMORY存储引擎支持HASH类型索引,默认为BTREE索引。
MySQL支持的存储引擎对每个表最少支持16个索引,总索引长度至少为256个字节。
MySQL支持6种索引:
普通索引、全文索引、单列索引、多列索引和空间索引
。
根据创建索引方式可分为
自动索引
和
手动索引
两种,表中主键约束和唯一性约束列都会自动创建索引。
自动索引:在表中设置完整性约束时该表会被系统自动创建索引。
手动索引:手动在表上创建索引。
普通索引
:创建索引时不附件任何限制条件,可以创建在任何数据类型的字段上。
唯一索引
:创建索引时限制索引的值必须是唯一的。
全文索引
:主要是关联在数据类型为char,varchar和text字段上,以便能够更加快速地查询数据量较大的字符串类型的字段。
多列索引
:在创建索引时,所关联的字段不是一个字段,而是多个字段,查询语句中只有使用了所关联字段的第一个字段,多列索引才会被使用。
适合创建索引的情况:
1、经常被查询的字段,即where子句中常出现的字段。
2、在分组的字段,即在group子句中常出现的字段。
3、存在依赖关系的字表和父表之间的联合查询,即主键或外键字段。
4、设置唯一完整性约束的字段。
不适合创建索引的情况:
1、在查询中很少使用的字段。
2、拥有许多重复值的字段。
一、创建和查看普通索引
1、创建表时创建索引
普通索引:
create table table_name(
属性名 数据类型,
……
i
ndex|key [索引名](属性名1[(长度)] [ASC|DESC])
);
唯一索引:
create table table_name(
属性名 数据类型,
……
unique index|key [index_name](属性名 [(长度)] [ASC|DESC])
);
全文索引:
create table table_name(
属性名 数据类型,
……
fulltext index|key [index_name](属性名 [(长度)] [ASC|DESC])
);
多列索引:
create table table_name(
属性名1 数据类型,
……
属性名n 数据类型,
index|key [index_name](属性名1 [(长度)] [ASC|DESC],
……
属性名n [(长度)] [ASC|DESC])
);
example:
use school;
drop table if exists buidings
create table buildings(
Id middleint not null primary key,
Name varchar(20),
Dept varchar(30),
index index_id(Id)
);
insert into buildings
values(1,'1号教学楼','计科院'),
(2,'2号办公楼','行政楼'),
(3,'3号教学楼','地化学院');
--查看索引是否已被使用
explain
select * from buildings where Id = 1;
2、在已有的表上创建索引
普通索引:
create index index_name
on table_name (属性名 [(长度) [ASC|DESC]]);
唯一索引:
create unique index index_name
on table_name(属性名 [(长度)] [ASC|DESC]);
example:
create index index_Name
on buildings(Name);
全文索引:
create fulltext index|key index_name
on table_name(属性名 [(长度)] [ASC|DESC]);
多列索引:
create index index_name
on table_name(属性名1 [(长度)] [ASC|DESC],
……
属性名n [(长度)] [ASC|DESC])
explain
select * from buildings where Name = 3;
3、通过修改表来创建索引
普通索引:
alter table table_name
add index|key index_name(属性名 [(长度)] [ASC|DESC]);
唯一索引:
alter table table_name
add unique index|key index_name(属性名 [(长度)] [ASC|DESC]);
全文索引:
alter table table_name
add fulltext index|key index_name(属性名 [(长度)] [ASC|DESC]);
多列索引:
alter table table_name
add index|key index_name(属性名1 [(长度)] [ASC|DESC],
……
属性名n [(长度)] [ASC|DESC])
example:
alter table buildings
add key index_Name(Name DESC);
二、删除索引
drop index index_name
on table_name;
example:
drop index index_Dept on buildings
--------------------------------------------------------- 小路原创 ------------------------------------------------