索引介绍
定义:一个单独的、存储在磁盘上的数据结构,它包含着对数据库里所有记录的引用指针
优点:
- 通过创建唯一索引可以保证数据库表中的每一行数据是唯一性的
- 加快数据的查询速度
- 实现数据的参考完整性方面,可以加速表与表之间的连接
- 在使用分组和排序的子句进行数据查询时,也可以减少查询中分组和排序的时间
不利:
- 创建和维护索引需要时间,随着数据量的增加所消耗的时间也会增加
- 索引需要占磁盘空间
- 当对表中的数据进行增加、删除和修改的时候,索引也要动态地维护,降低了数据的维护速度
分类:
创建索引语法:
(1)在创建表的时候建立索引
CREATE table table_name(
clo_name data_type,
......
[UNIQUE | FULTEXT |SPATIAL] [INDEX|KEY] [index_name](col_name[length]) [ASC|DESC]
)
UNIQUE代表唯一索引,FULLTEXT代表全文索引,SPATIAL代表空间索引;
index和key为同义词,用于指定创建索引;
index_name为索引名,如果没有指定索引名,默认为建立在索引的列名为索引名
col_name为字段列,length可选项,表示索引的长度。只有字符串类型的字段才能指定索引长度。
(2)在已存在的表上建立索引
(a)使用ALTER TABLE语句建立索引
ALTER TABLE table_name add [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY]
[index_name] (col_name[length],...)[ASC|DESC]
(b)使用CREATE INDEX创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(col_name[length],..) [ASC|DESC]
查看索引:
SHOW INDEX FROM table_name;
删除索引
ALTER TABLE table_name DROP INDEX index_name;
DROP INDEX index_name on table_name;
习题(MySQL5.7从入门到精通 第9章习题)
表结构如表1
字段名 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
w_id | SMALLINT(11) | 是 | 否 | 是 | 是 | 是 |
w_name | VARCHAR(255) | 否 | 否 | 是 | 否 | 否 |
w_address | VARCHAR(255) | 否 | 否 | 否 | 否 | 否 |
w_age | CHAR(2) | 否 | 否 | 是 | 否 | 否 |
w_note | VARCHAR(255) | 否 | 否 | 否 | 否 | 否 |
(1)在数据库里创建表writers,存储引擎为MyISAM,创建表的同时在w_id字段上添加名称为UniqIdx的唯一索引
create table writers(
w_id smallint(11) primary key not null auto_increment,
w_name varchar(255) not null ,
w_address varchar(255) ,
w_age char(2) not null,
w_note varchar(255),
unique index UniqIdx(w_id)
)engine=MyIsAM;
(2)使用alter table语句在w_name字段上建立nameIdx的普通索引
ALTER TABLE writers add Index nameIdx(w_name);
(3)使用CREATE INDEX 语句在w_address和w_age字段上面建立名称为MultiIdx的组合索引
CREATE INDEX MultiIdx on writers(w_address,w_age);
(4)使用create index语句在w_note字段上建立名称为FTIdex的全文索引
CREATE FULLTEXT INDEX FTIdex on writers(w_note);
(5)删除名为FTIdx的全文索引
DROP INDEX FTIdex on writers;
-- 或者选用下面这个
ALTER TABLE writers drop INDEX FTIdex;
小结:
(1)注意建立全文索引和空间索引时,表的存储引擎要为MyISAM
(2)尽量使用短索引
(3)对于数据量小的表最好不要建立索引
(4)在频繁进行排的序和分组的列上建立索引
(5)索引并非越多越好