索引的操作
数据库对象索引是一种有效组合数据的方式,通过索引对象,可以快速查询到数据库表中的特定记录,是一种提高性能的常用方式。
简介
为了提高数据的检索速度,由于数据存储在数据库表中,所以索引是创建在数据库表对象上面的,由表中的一个或多个字段生成的键组成,这些键存储在数据结构(B-树或哈希表)中,通过MySQL可以快速查找与键值相关联的字段,根据索引的存储类型,可以分为B型树索引(BTREE)和哈希索引(HASH)。
InnoDB和MyISAM存储类型支持BTREE类型索引,MEMORY存储引擎支持HASH类型索引,默认为前者索引。
MySQL支持6中索引类型:普通索引,唯一索引,全文索引,单例索引,多列索引,空间索引
以下情况适合创建索引:
- 经常被查询的字段,即在WHERE语句中经常出现的字段
- 在分组的字段,在ORDER BY字句中出现的字段
- 存在依赖关系的父表与子表之间的联合查询,即主键和外键字段
- 设置唯一性约束的字段
以下情况不适合:
- 在查询中很少被使用的字段
- 拥有许多重复值的字段
创建普通索引
创建表的时候创建索引
CREATE TABLE student(
id INT,
name varchar(20),
……
INDEX index_id(id)
);
查看:
SHOW CREATE TABLE STUDENT \G
在已经存在的表上创建索引
CREATE INDEX index_id ON student (id);
查看同上
通过SQL语句的ALTER TABLE 创建普通索引
ALTER TABLE student ADD INDEX index_id(id);
通过DESC查看该数据库中已经存在的表student的信息。
DESC student;
执行:
alter table student add index index_name(name);
查看索引是否被创建:
创建和查看唯一索引
根据创建索引的方式,可以分为自动索引和手动索引,所谓自动索引,是指在数据库中设置完整性约束时,该表会被系统自动创建索引。所谓手动索引,是指在手动在表上创建索引,当设置表中的某个字段设置为主键或唯一完整性约束时,系统自动创建关联该字段的唯一索引。
CREATE TABLE student(
id INT UNIQUE,
……
UNIQUE INDEX index_id(id)
);
在已经存在的表上创建索引
CREATE UNIQUE INDEX 索引名 ON 表名(属性名 [长度] [ASC|DESC])
创建和查看全文索引
全文索引主要关联在数据类型为CHAR,VARCHAR,和TEXT字段上,以便可以更加快速的查询数据量较大的字符串类型的字段。
MySQL只能在MyISAM的数据库上创建全文索引,在默认情况下,全文索引的搜索执行方式为不区分大小写,如果全文索引所关联的字段为二进制数据类型,则以区分大小写的搜索方式执行。
CREATE TABLE table_name(
FULLTEXT INDEX|KEY [索引名] (属性名1 [长度] [ASC|DESC])
);
在已经存在的表上创建索引:
CREATE FULLTEXT INDEX 索引名 ON 表名(属性名 [长度] [ASC|DESC])
删除索引
DROP INDEX index_name ON table_name;