mysql索引入门简介

举个例子：若将数据库比作一本书，那么索引就是书的目录，用来提高查询的速度。通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列。

 

表的不同的存储引擎定义了每个表的最大索引数和最大索引长度。所有存储引擎对每个表至少支持16个索引，总索引长度至少为256B，有些存储引擎支持更多的索引数和更大的索引长度。

 

索引有两种存储类型，包括B型树(BTREE)索引和哈希(HASH)索引。InnoDB和MyISAM存储引擎支持BTREE索引；MEMORY存储引擎支持HASH索引和BTREE索引，默认为前者。

 

索引的分类：

1、普通索引：在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空由字段本身的完整性约束条件决定。

 

2、唯一性索引：使用UNIQUE参数可以设置索引为唯一性索引。在创建唯一性索引时，限制该索引的值必须是唯一的。通过唯一性索引，可以更快的确定某条记录。主键就是一种特殊的唯一性索引。

 

3、全文索引：使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR、或TEXT类型的字段上。

 

4、单列索引：在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

 

5、多列索引：多列索引是在表的多个字段上创建一个索引。但是，只有查询条件中使用了这些字段中的第一个字段时，索引才会被使用。

 

6、使用SPATIAL参数可以设置索引为空间索引。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。

 

索引的设计原则：

1、选择唯一性索引：唯一性索引的值是唯一的，可以更快地通过改索引来确定某条记录。

 

2、为经常需要排序、分组和联合操作的字段建立索引。经常需要使用ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

 

3、为经常作为查询条件的字段建立索引。

 

4、限制索引的数目：索引的数目不是越多越好，每个索引都需要占用一定的磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

 

5、尽量使用数据量少的索引：如果索引的值很长，那么查询的速度会受到影响。

 

6、尽量使用前缀来索引：如果索引字段的值很长，最好使用值的前缀来索引。如果值检索字段的前脸的若干个字符，那么可以提高检索速度。

 

7、删除不再使用很少使用的索引：表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。所以应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

 

索引的操作：

创建表的时候创建索引：

mysql> CREATE TABLE 表名 ( 
属性名1 数据类型 [完整性约束条件]
......
[ UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [别名] (属性名1 ([长度]) [ASC|DESC])
);

对上述代码解释：

UNIQUE是可选参数，表示索引为唯一性索引，另外两个同理；

INDEX和KEY参数用来指定字段为索引的，两者选择其中之一就可以了，作用时一样的。

"别名"是可选参数，用来给创建的索引取新的名字。

"属性名1"参数指定索引对应的字段和的名称，该字段必须为前面定义好的字段。

"长度"是可选参数，其指索引的长度，必须是字符串类型才可以使用。

"ASC" 和 "DESC" 都是可选参数，前者表示升序排列，后者表示降序排列。

 

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

mysql> CREATE  [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名
ON 表名 (属性名1 [(长度)] [ASC | DESC]);

需要注意：此时索引名必须设置。其它参考创建表时创建索引。例如：

mysql> CREATE INDEX wid ON school.student(id);
mysql> CREATE INDEX index_addr ON school.student(address(4));

上述第二个例子中，查询时可以只查询address字段的前4个字符，而不需要全部查询。

 

使用ALTER TABLE语句来创建索引：

mysql> ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX
索引名 (属性名1 [(长度)] [ASC | DESC]);

用法参考上面的两种方式。例如：

mysql> ALTER TABLE school.student ADD INDEX sid (id);
mysql> ALTER TABLE school.student ADD INDEX sname (name(20));

 

删除索引：

mysql> DROP INDEX 索引名 ON 表名；

删除索引很容易理解，索引名我们可以通过下面的方式获取：

mysql> SHOW CREATE TABLE 表名;
mysql> SHOW CREATE TABLE school.student;

 

使用EXPLAIN语句可以查看索引的使用情况，例如：

mysql> EXPLAIN SELECT * FROM school.student WHERE sid = 1 \G

 

 

ps：创建唯一性索引时，字段可以没有进行唯一性约束，也可以在该字段上成功建立唯一性索引。但是，这样可能达不到提高查询速度的目的。