数据库操作1——普通索引

1. 索引介绍

- 索引类型

索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。下文中将详细介绍~

- 索引存储

数据库底层索引实现主要有两种存储类型，B树(BTREE)和哈希(HASH)索引，InnoDB和MyISAM 使用BTREE索引；而MEMORY 存储引擎可以使用BTREE 和HASH 索引，默认用BTREE.在没有指定的情况下，数据库使用的引擎是 InnoDB。

- 索引优点

可以提高检索数据的速度。

- 索引缺点

创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态地维护索引，造成数据的维护速度降低了。

- 使用建议

索引可以提高查询的速度，但是会影响插入记录的速度，因为向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。

2. 普通索引

所谓普通索引，就是在创建索引时，不附加任何限制条件（唯一、非空等限制）。该类型的索引可以创建在任何数据类型的字段上。

创建普通索引的方法常见的有三种：

（1）在创建表的时候创建索引：

CREATE TABLE tablename(

    propname1 type1,

    propname2 type2,

    ……

    propnamen type..n,

     INDEX | KEY

    [indexname] (propnamen [(length)] [ ASC | DESC ] ) );

其中，参数INDEX和KEY是用来指定字段为索引的，两者选择其中之一就可以了，作用是一样的；参数indexname是索引名字，可省略；参数propnamen是索引对应的字段的名称，该字段必须为前面定义好的字段；参数length是可选参数，其指索引的长度，必须是字符串类型才可以使用；参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列，如果不指定，则为升序。

例子：

# 创建表class1，里面有三条属性（id, name和teacher）,以id单列创建普通索引
create table class1(id int, name varchar(128), teacher varchar(64), index index_id(id desc));

（2）已存在的表上创建索引

1）使用create语句创建索引

CREATE INDEX indexname  

    ON tablename (propname [(length)] [ASC|DESC]);  

参数INDEX是用来指定字段为索引，此处不能为KEY；参数indexname是新创建的索引的名字；参数tablename是指需要创建索引的表的名称，该表必须是已经存在的，如果不存在，需要先创建；参数propname指定索引对应的字段的名称，该字段必须为前面定义好的字段；参数length是可选参数，表示索引的长度，必须是字符串类型才可以使用；参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列,默认升序。

例如：

# 创建表class2
create table class2(id int, name varchar(128), teacher varchar(64));
# 以id列创建索引
create index index_id on class2(id desc);

2）使用alter语句创建索引

ALTER TABLE tablename ADD INDEX | KEY indexname  

     (propname [(length)] [ASC|DESC]);

在上述语句中，参数tablename是需要创建索引的表；关键字IDNEX或KEY用来指定创建普通索引；参数indexname用来指定所创建的索引名；参数propname用来指定索引所关联的字段的名称；参数length用来指定索引的长度；参数ASC用来指定升序排序；参数DESC用来指定降序排序。

例如：

# 创建表class3
create table class3(id int, name varchar(128), teacher varchar(64));
# 以id列创建普通索引
alter table class3 add index index_id(id desc);