索引的创建

索引的创建

创建主键索引PRIMARY KEY：

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

创建唯一索引UNIQUE ：

ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

创建普通索引INDEX：

ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 

创建全文索引FULLTEXT ：

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

创建多列索引 ：

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

每种索引的区别：

普通索引：没有任何限制，只需加入表的名称，和表的字段名称即可。普通索引没有唯一性要求。

主键索引：它是一种特殊的唯一索引，不允许有空值。主键索引也可以为多个字段组合

 唯一索引：它与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。组合索引也就是由数据表中的几个字段组合，组合成的值是唯一。

分析索引查询效率：

现在我们已经知道了一些如何选择索引列的知识，但还无法判断哪一个最有效。MySQL提供了一个内建的SQL命令帮助我们完成这个任务，这就是EXPLAIN命令。EXPLAIN命令的一般语法是：EXPLAIN 。你可以在MySQL文档找到有关该命令的更多说明。下面是一个例子：

EXPLAIN SELECT peopleid FROM people WHERE firstname=’Mike’ AND lastname=’Sullivan’ AND age=’17′;

这个命令将返回下面这种分析结果：

下面我们就来看看这个EXPLAIN分析结果的含义。

table：这是表的名字。
type：连接操作的类型。下面是MySQL文档关于ref连接类型的说明：

“对于每一种与另一个表中记录的组合，MySQL将从当前的表读取所有带有匹配索引值的记录。如果连接操作只使用键的最左前缀，或者如果键不是UNIQUE或PRIMARY KEY类型（换句话说，如果连接操作不能根据键值选择出唯一行），则MySQL使用ref连接类型。如果连接操作所用的键只匹配少量的记录，则ref是一种好的连接类型。”

在本例中，由于索引不是UNIQUE类型，ref是我们能够得到的最好连接类型。

如果EXPLAIN显示连接类型是“ALL”，而且你并不想从表里面选择出大多数记录，那么MySQL的操作效率将非常低，因为它要扫描整个表。你可以加入更多的索引来解决这个问题。预知更多信息，请参见MySQL的手册说明。

possible_keys：
可能可以利用的索引的名字。这里的索引名字是创建索引时指定的索引昵称；如果索引没有昵称，则默认显示的是索引中第一个列的名字（在本例中，它是“firstname”）。默认索引名字的含义往往不是很明显。

Key：
它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。

key_len：
索引中被使用部分的长度，以字节计。在本例中，key_len是102，其中firstname占50字节，lastname占50字节，age占2字节。如果MySQL只使用索引中的firstname部分，则key_len将是50。

ref：
它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行。在本例中，MySQL根据三个常量选择行。

rows：
MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1。

Extra：
这里可能出现许多不同的选项，其中大多数将对查询产生负面影响。在本例中，MySQL只是提醒我们它将用WHERE子句限制搜索结果集。

索引的弊端

索引并不是百利无害的，索引可以加快查询速度但是缺点也有以下两点

1）索引是十分占用磁盘空间的，如果创建很多索引很可能索引占用的磁盘空间很可能已经超过你数据所占用的磁盘空间。

2）索引的创建，在加快了查询速度的同时也使更新、插入的速度变慢。因为在更新和插入的同时，我们不仅仅要对原来的数据进行操作，同时也会更新和插入新的索引数据。所以创建合理的索引才能增强数据库的性能。