MySQL 学习<八> MySQL索引

如果表不用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，显然，表越大，查询数据所花费的时间越多；而索引是用于快速找出某个列中有一特定值的行。

索引简介

含义和特点

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某列或多列中有一特征值的行；MySQL中索引的存储类型有两种:BTREE和HASH，具体和表的存储引擎相关，MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

其优点主要有以下几条：

1.创建唯一索引，可以保证数据库表中每一行数据的唯一性。
2.可以加快查询数据的查询速度。
3.实现数据的参考完整性方面，可以加快表和表之间的连接。
4.在使用分组和排序子句进行数据查询的时候，也可以显著减少查询中分组和排序时间。
缺点：
1.创建和维护索引需要时间，如果数据量增多，那么创建索引和维护索引的时间也就会消耗很多
2.当对于很多数据进行更新的时候，索引也是需要进行相应的更新的，其中删除和增加都是同样的道理，所以也是特别耗时
3.创建索引也需要占用磁盘空间，除了数据表占数据空间之外，每个索引还要占据一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快到达最大文件尺寸。

索引分类

1.普通索引：允许在定义索引的列中插入重复值和空值。
2.唯一索引：索引列的值必须唯一，但是允许有空值。
3.主键索引：索引列的值必须唯一，且不允许有空值存在
4.单列索引：一个索引只包含单个列，一个表可以有多个单列索引。
5.组合索引：在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的额左边字段时，索引才会被使用，使用组合索引时遵循最左前缀索引。
6.全文索引：类型为FULLTEXT，在定义索引的裂伤支持值的全文查找，允许在这些索引列中插入空值和重复值。全文索引可以在CHAR,VARCHAR,TEXT类型的列上创建，MySQL中只有MyISAM存储引擎支持全文索引。
7.空间索引：对于空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是：GEOMETRY,POINT,LINESTRING,POLYGON，MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引，创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

创建索引

创建表时创建索引

语法：

CREATE TABLE table_name [col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][index_name](col_name [length])
[ASC|DESC];

其中：

[UNIQUE|FULLTEXT|SPATIAL]:可选参数，表示唯一索引，全文索引，空间索引；
[INDEX|KEY]：作用相同，用来创建索引；
col_name：为需要创建索引的字段列，该列必须从数据表中定义的多列中选择。；
index_name:指定索引的名称，可选参数，如果不指定，mysql默认col_name为索引值；

length：为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
ASC|DESC：指定升序或者降序的索引值存储。

创建普通索引:

CREATE TABLE book
(
bookid             INT NOT NULL,
bookname           VARCHAR(255) NOT NULL,
authors             VARCHAR(255) NOT NULL,
info               VARCHAR(255) NULL,
comment           VARCHAR(255) NULL,
year_publication   YEAR NOT NULL,
INDEX(year_publication)
);

使用SHOW CREATE TABLE查看表的结构：

可以看出在book表的year_publication字段上面成功创建了索引，索引名称是mysql自动添加的year_publication
使用EXPLAIN语句来查看索引是否正在使用：

 

1.id:SELECT识别符。这是SELECT的查询序列号
2.select_type:指定所使用的SELECT查询类型，这里值为SIMPLE,表示是简单查询，不使用UNION或者子查询，其他可能取值有：PRIMARY,UNION,SUBQUERY等
3.table：表示数据库读取的数据表的名字，按被读取的先后顺序排列。 

4.type表示本数据表与其它数据表之间的关联关系，可能的取值有system,const,eq_ref,ref,range,index,all
5.possible_keys:表示mysql在搜索数据记录时可选用的各个索引。
6.key表示MySQL实际选用的索引
7.key_len:表示给出索引按照字节计算的长度，key_len数值越小，表示越快。
8.ref：表示关联关系中另一个数据表里的数据列名字
9.rows:mysql在执行查询时会预计从这个数据表里读出的数据行的个数 

10.extra:表示提供了与关联操作相关的信息

创建唯一索引：

CREATE TABLE t1
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);

查看表结构：

id字段上已经成功建立了一个名为Uniqldx的唯一索引。

创建单列索引：

为简化，直接给出关键语句：INDEX SingleIdx(name(20)) ；

name字段上已经创建了一个名为SingleIdx的单列索引，索引长度为20.

 创建组合索引：

创建全文索引：
CREATE TABLE t4
(
id    INT NOT NULL,
name CHAR(30) NOT NULL,
age  INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info)
) ENGINE=MyISAM;

由于MySQL的默认存储引擎是InnoDB，所以这里创建表时要修改表的存储引擎为MyISAM；

查看表结构：

创建空间索引：

CREATE TABLE t5
( g GEOMETRY NOT NULL, SPATIAL INDEX spatIdx(g) ) ENGINE=MyISAM;

注意：指定空间类型字段值的非空约束，并且表的存储引擎为MyISAM。

在已经存在的表上创建索引

使用ALTER TABLE语句创建索引，基本语法：

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL]  

[INDEX|KEY][index_name](col_name[length],...)[ASC|DESC];

例如，要在刚才创建的book表中在bookname字段上添加索引：

ALTER TABLE book ADD INDEX BkNameIdx ( bookname(30) );

再查看表中索引， SHOW INDEX FROM book \G：

在book表的bookId字段上建立名称为UniqidIdx 的唯一索引：

ALTER TABLE book ADD UNIQUE INDEX UniqidIdx ( bookId );

同理，查看表中索引：

在表中创建单列索引，语句如下所示：

ALTER TABLE book ADD INDEX BkcmtIdx ( comment(50) );

在表中author和info字段上创建组合索引：

ALTER TABLE book ADD INDEX BkAuAndInfoIdx ( authors(20),info(50) );

查看表中索引：

创建全文索引（注意ENGINE为MyISAM）：

ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx ( info );

查看表中索引：

创建空间索引：

ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g);

查看表中索引：

另外，还可以使用CREATE INDEX 创建索引，基本语法为：

CREATE  [UNIQUE|FULLTEXT|SPATIAL]   INDEX  index_name  

ON table_name (col_name[length],...) [ASC|DESC];

再次创建一个book表，假设没有任何索引值：

创建普通索引，语法如下：

CREATE INDEX BkNameIdx ON book(bookname);

创建唯一索引，语法如下：

CREATE UNIQUE INDEX UniqidIdx  ON book ( bookId );

创建单列索引，语法如下：

CREATE INDEX BkcmtIdx ON book(comment(50) );

创建组合索引，语法如下：

CREATE INDEX BkAuAndInfoIdx ON book ( authors(20),info(50) );

创建全文索引，语法如下：

CREATE FULLTEXT INDEX ON t6(info);

创建空间索引，语法如下：

CREATE SPATIAL INDEX spatIdx ON t7 (g);

注意一点的是：全文索引和空间索引都是drop 对应表后重新建立表再创建索引的。

删除索引

使用ALTER

使用ALTER TABLE删除索引，基本语法如下：

ALTER TABLE table_name DROP INDEX index_name;

例如，删除book表中名称为UniqidIdx的唯一索引，语法如下：

 ALTER TABLE book DROP INDEX UniqidIdx;

使用DROP

使用DROP TABLE删除索引，基本语法如下：

DROP INDEX index_name ON table_name;

例如，删除book表中名称为BkAuAndInfoIdx的组合索引，语句如下：
 DROP INDEX BkAuAndInfoIdx ON book;