第7章 索引
索引是一种特殊的数据库结构,可以用来快速查询数据库表中的特定记录。
索引是提高数据库性能的重要方式。
MySQL中,所有的数据类型都可以被索引。
包括:
普通索引;
唯一性索引;
全文索引;
单列索引;
多列索引;
空间索引等。
7.1 索引简介
索引由数据库表中一列或多列组合而成,其作用是提高对表中数据的查询速度。
7.1.1 索引的含义和特点
索引是创建在表上的,是对数据库表中一列或多列的值进行排序的一种结构。
索引可以提高查询的速度,有效地提高了数据库系统的性能。
通过索引,查询数据时,可以不必读完记录的索引信息,而只是查询索引列。否则,数据库系统将读取每条记录的所有信息进行匹配。
不同的存储引擎定义了每个表的最大索引数和最大索引长度。
索引有两种存储类型,包括B型树(BTREE)索引和哈希(HASH)索引。
优点:
可以提高检索数据的速度;
对于有依赖关系的子表和父表之间的联合查询时,可以提高查询速度;
使用分组和排序子句进行数据查询时,同样可以显著节省查询中分组和排序的时间。
缺点:
创建和维护索引需要耗费时间,耗费时间的数量随着数据量的增加而增加;
索引需要占用物理空间,每一个索引要占一定的物理空间;
增加、删除、修改数据时,要动态的维护索引,造成数据的维护速度降低了。
技巧:
索引可以提高查询的速度,但是会影响插入记录的速度。这是因为,向有索引的表中插入记录时,数据库系统会按照索引进行排序,这样就降低了插入记录的速度。
最好的办法是,先删除表中的索引,然后插入数据,插入完成后,再创建索引。
7.1.2 索引的分类
1.普通索引
在创建普通索引时,不附加任何限制条件。
详细解释见书上。
2.唯一性索引
使用UNIQUE可以设置索引为唯一性索引。
详细解释见书上。
3.全文索引
使用FULLTEXT参数可以设置索引为全文索引。
全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。
详细解释见书上。
4.单列索引
在表中的单个字段上创建索引。
详细解释见书上。
5.多列索引
多列索引是在表的多个字段上创建一个索引。
详细解释见书上。
6.空间索引
使用SPALTIAL参数可以设置索引为空间索引。
详细解释见书上。
7.1.3 索引的设计原则
1.选择唯一性索引
唯一性索引的值是唯一的,可以更快的通过该索引来确定某条记录。
例如:
学生表中学号是具有唯一性的字段。
详细解释见书上。
2.为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、DISTINCT、UNION等操作的字段,排序操作会浪费很多时间。如果为其建立索引,可以有效地避免排序操作。
详细解释见书上。
3.为常作为查询条件的字段建立索引
详细解释见书上。
4.限制索引的数目
索引的数目不是越多越好。
每个索引都需要占用磁盘空间,所以越多,需要的磁盘空间就越大。
修改表时,对索引的重构和更新很麻烦。
越多的索引,会使更新表变得很浪费时间。
详细解释见书上。
5.尽量使用数据量少的索引
如果索引的值很长,那么查询的速度会受到影响。
详细解释见书上。
7.尽量使用前缀来索引
详细解释见书上。
8.删除不再使用或者很少使用的索引
详细解释见书上。
7.2 创建索引
创建索引是指在某个表的一列或多列上建立一个索引,以便提高对表的访问速度。
创建索引有3种方式:
创建表的时候创建索引;
在已经存在的表上创建索引;
使用ALTER TABLE语句来创建索引。
7.2.1 创建表的时候创建索引
CREATE TABLE 表名 (属性名 数据类型 [完整性约束条件],
属性名 数据类型 [完整性约束条件],
......
属性名 数据类型
[ UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[别名] (属性名1 [(长度)] [ASC | DESC])
);
1.创建普通索引
创建一个普通索引时,不需要加任何UNIQUE、FULLTEXT、SPATIAL参数。
详细解释、例子见书上。
2.创建唯一性索引
创建唯一性索引时,需要使用UNIQUE参数进行约束。
详细解释、例子见书上。
3.创建全文索引
只能创建在CHAR、VARCHAR、TEXT类型的字段上。
现在只有MyISAM存储引擎支持全文索引。
详细解释、例子见书上。
4.创建单列索引
单列索引是在表的单个字段上创建索引。
对于字符型的数据,可以不用查询全部信息,而只查询其前面的若干字符信息。
详细解释、例子见书上。
5.创建多列索引
创建多列索引是在表的多个字段上创建一个索引。
详细解释、例子见书上。
6.创建空间索引
创建空间索引时,必须使用SPATIAL参数来设置,表的存储引擎必须是MyISAM类型。
创建空间索引时,表的存储引擎必须使用MyISAM类型。
而且,索引字段必须有非空约束。
详细解释、例子见书上。
7.2.2 在已经存在的表上创建索引
在已经存在的表中,可以直接为表上的一个或几个字段创建索引。
基本形式:
CREATE [UNIQUE | FULLTEXT |SPATIAL] INDEX 索引名
ON 表名 (属性名 [(长度)] [ASC | DESC]);
说明:
UNIQUE、FULLTEXT、SPATIAL都是可选参数;
UNIQUE表示索引为唯一性索引;
FULLTEXT表示索引为全文索引;
SPATIAL表示索引为空间索引;
“INDEX”参数是指需要创建索引的表的名称,该表必须是已存在的,如果不存在,需要先创建;
“属性名”参数指定索引对应的字段的名称,该字段必须为前面定义好的字段;
“长度”是可选参数,其指索引的长度,必须是字符串类才可以使用;
ASC和DESC都是可选参数,ASC表示升序排列,DESC表示降序排列。
1.创建普通索引
2.创建唯一性索引
3.创建全文索引
4.创建单列索引
5.创建多列索引
6.创建空间索引
7.2.3 用ALTER TABLE语句来创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX
索引名 (属性名 [(长度)] [ASC | DESC]);
1.创建普通索引
例如:
ALTER TABLE example0 ADD INDEX index13_name(name(20));
2.创建唯一性索引
例如:
ALTER TABLE index14 ADD UNIQUE INDEX index14_id(courser_id);
3.创建全文索引
4.创建单列索引
5.创建单列索引
6.创建空间索引
7.3 删除索引
一些不再使用的索引会降低表的更新速度,影响数据库的性能。对于这样的索引,应该将其删除。
基本形式:
DROP INDEX 索引名 ON 表名;
7.6 常见问题及解答
1.MySQL中索引、主键和唯一性的区别是什么?
2.表中建立了索引之后,导入大量数据为什么很慢?
参考文献:
1.《MySQL入门很简单》。