MySQL之索引（附带习题）

索引介绍

定义：一个单独的、存储在磁盘上的数据结构，它包含着对数据库里所有记录的引用指针

优点：

1. 通过创建唯一索引可以保证数据库表中的每一行数据是唯一性的
2. 加快数据的查询速度
3. 实现数据的参考完整性方面，可以加速表与表之间的连接
4. 在使用分组和排序的子句进行数据查询时，也可以减少查询中分组和排序的时间

不利：

1. 创建和维护索引需要时间，随着数据量的增加所消耗的时间也会增加
2. 索引需要占磁盘空间
3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，降低了数据的维护速度

 分类：

创建索引语法：

（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

writers表结构

字段名	数据类型	主键	外键	非空	唯一	自增
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）索引并非越多越好