举个例子:若将数据库比作一本书,那么索引就是书的目录,用来提高查询的速度。通过索引,查询数据时可以不必读完记录的所有信息,而只是查询索引列。
表的不同的存储引擎定义了每个表的最大索引数和最大索引长度。所有存储引擎对每个表至少支持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:创建唯一性索引时,字段可以没有进行唯一性约束,也可以在该字段上成功建立唯一性索引。但是,这样可能达不到提高查询速度的目的。