索引知识概念

一、索引的概念：
1、普通索引
普通索引是由KEY或者INDEX 定义的索引，是mysql 中的基本索引类型，可以创建在任何数据类型中，其值是否唯一和非空由字段本身的约束条件所决定。

2，唯一性索引

唯一性索引是由 UNIQUE 定义的索引，该索引所在字段的值必须是唯一的。例在 grade 表的 id 字段上建立唯一性索引，那么， id 字段的值就必须是唯一的。

3．全文索引

全文索引是由 FULLTEXT 定义的索引，它只能创建在 CHAR 、 VARCHAR或者TEXT 类型的字段上，而且，现在只有 MyISAM 存储引擎支持全文索引。

4．单列索引

半列索引指的是在表中单个字段上创建索引，它可以是普通索引、唯一索引或者全文索引，只要保证该索引只对应表中一个字段即可。

5．多列索引

多列索引指的是在表中多个字段上创建索引，只有在查询条件中使用了这些子段中的弟一个字段时，该索引才会被使用。例如，在 grade 表的 id 、 name 和 score 子段上创建一个多列索引，那么，只有查询条件中使用了 id 字段时，该索引才会被使用。

6．空间索引

空间索引是由 SPATIAL 定义的索引，它只能创建在空间数据类型的学段上。 MySQL 中的空间数据类型有4种，分别是 GEOMETRY 、 POINT 、 LINESTRING 和 POLYGON 。需要注意的是，创建空间索引的字段，必须将其声明为 NOT NULL ，并且空间索引只能在存储引擎为 MyISAM 的表中创建。

需要注意的是，虽然索引可以提高数据的查询速度，但索引会占用一定的磁盘空间，并且在创建和维护索引时，其消耗的时间是随着数据量的增加而增加的。因此，使用索引时，应该综合考虑索引的优点和缺点。

二、创建索引

要想使用索引提高数据表的访问速度，首先要创建一个索引。创建索引的方式有＝种，具体如下。

1．创建表的时候创建索引

所示： 创建表的时候可以直接创建索引，这种方式最简单、方便，其基本的语法格式 ：

CREATE TABLE 表名 (字段名 数据类型[完整性约束条件],

字段名 数据类型[完整性约束条件],

字段名 数据类型

[UNIQUE|FULLTEXT|SPATIAL) INDEX|KEY

[别名] (字段名1[(长度)]  [ASC|DESC])

关于上述语法的相关解释具体如下。

(1)UNIQUE: 可选参数,表示唯一索引。
(2)FULLTEXT:可选参数,表示全文索引。
(3)SPATIAL:可选参数,表示空间索引。

(4)INDEX和KEY:用来表示字段的索引，二者选一即可
(5)别名:可选参数,表示创建的索引的名称。
(6)字段名1:指定索引对应字段的名称.

(7)长度:可选参数,用于表示索引的长度。
(8)ASC和DESC:可选参数，其中，ASC表示升序排列，DESC表示降序排列。

通过具体的案例别对MySQL中的6种索引类型进行讲解，具体如下;
1)创建普通索引

【例】在t1表中id字段上建立索引，SQL语句如下:

CREATE TABLE tI (id INT

name VARCHAR(20), score FLOAT, INDEX (id)

2、使用CREATE INDEX 语句在已经存在的表上创建索引
语法：

CREATE [UNIQUE|FULLTEXT|SPATIAL) INDEX 索引名  ON 表名 （字段名[（长度）]） [ASC|DESC] );

举例：`test_yyp_0012` 表中没有任何索引
CREATE TABLE `test_yyp_0012` (
  `ID1` int(90) NOT NULL,
  `ID2` bigint(190) NOT NULL,
  `BIRTHDATE` date NOT NULL,
  `BIRTHTIME` time DEFAULT NULL COMMENT '出生时间',
  `LONGTEXT21` longtext COMMENT 'string',
  PRIMARY KEY (`ID1`,`ID2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试字段类型转换准确性'

给`test_yyp_0012` 表创建普通索引：

create index index_id on  test_yyp_0012(ID1);

查看表结构显示如下：
  KEY `index_id` (`ID1`)

3、使用ALTER TABLE 语句在已经存在的表上创建索引
语法：

ALTER TABLE　表名　ADD　 [UNIQUE|FULLTEXT|SPATIAL) INDEX 索引名  　（字段名[（长度）]） [ASC|DESC] );

alter table  test_yyp_0012  add  index index_id2(ID2 asc);

alter table  test_yyp_0012  add  unique  iuniquendex_id3(ID3 asc);

表中已存在索引可以创建索引，表中不存在索引也可以执行命令创建索引。

三、删除索引

由于索引会占用一定的磁盘空间，为了避免影响数据库性能，应该及时删除不再使用的索引。

删除索引的方式有2种：

1、使用 ALTER TABLE 删除索引
语法：
ALTER TABLE　表名　DROP  INDEX 索引名

alter table  test_yyp_0012  drop index index_id2 ;

2、使用DROP INDEX 删除索引
语法：
DROP INDEX 索引名 ON 表名

drop index iuniquendex_id3 on test_yyp_0012;