索引
1.索引是什么?
索引(Index)是一种特殊的数据库结构,可以用来快速查询数据库表中的特定记录,是提高数据库性能的一种重要方式。
在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。索引是建立在表上的。
2.索引的分类
普通索引、唯一性索引、全文索引、单列索引、多列索引、空间索引。
3.创建索引
(1)创建普通索引
create table index1(id int,
-> name varchar(20),
-> sex boolean,
-> index(id)
-> );
(2)创建唯一性索引
创建唯一性索引时,需要使用UNIQUE参数进行约束;
create table index2(id int unique,
-> name varchar(20),
-> unique index index2_id(id desc)
-> );
这里在表中的id字段上建立了名为index2_id的唯一性索引,且以降序的形式排列。
(3)创建全文索引
注意:全文索引只能在char、varchar或text类型的字段上,并且只有MyISAM存储引擎支持全文索引。针对较大的数据,生成全文索引很耗时耗空间。
create table index3(
-> id int,
-> info varchar(20),
-> fulltext index index3_info(info)
-> )ENGINE=MYISAM;
(4)创建单列索引
所谓单列索引,就是在表的单个字段上创建索引。
create table index4(
-> id int,
-> subject varchar(30),
-> index index_st(subject(10))
-> );
(5)创建多列索引
创建多列索引就是在表的多个字段上创建一个索引;
create table index5(
-> id int,
-> name varchar(20),
-> sex char(4),
-> index index5_ns(name,sex)
-> );
这里可以看到,在表中的Name和sex字段上建立的多列索引。
而且,在多列索引中,只有查询条件中使用了这些字段中的第一个字段时
,索引才会被使用。
(6)创建空间索引
空间索引的创建必须使用参数SPATIAL参数来设置,而且表的存储引擎必须是MYISAM类型,索引字段必须有非空约束。
create table index6(
-> id int,
-> space GEOMETRY not null,
-> spatial index index6_sp(space)
-> )ENGINE=MyISAM;
可以看到,space字段上已经建立了一个名为index6_sp的空间索引,而且space的数据类型时GEOMETRY类型,它属于空间数据类型。
4.删除索引
删除索引就是指将表中已经存在的索引删除掉。
对于已经存在的索引,可以通过DROP语句来删除,基本形式如下:
drop index 索引名 on 表名;
下面删除index1的索引,执行代码如下:
drop index id on index1;
代码执行结果:
drop index id on index1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
使用查询语句show create table index1 \G
show create table index1\G;
*************************** 1. row ***************************
Table: index1
Create Table: CREATE TABLE `index1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
结果显示,表Index1中名为id的索引已经不存在了,则已经删除索引。
5.常见问题解答
(1)不要随意使用索引
索引可以提高查询速度,但是会降低更新表的速度。因为在更新表时,mysql不仅要更新数据,还要更新索引,保存索引。它会在一定程度上占用磁盘空间。
(2)表中建立了索引之后,导入大量数据会很慢。
对于已经建立索引的表中插入数据时,插入一条数据就要对该记录按照索引排序,索引速度会变得很慢,应该在没有任何索引的情况插入数据,然后再建立索引。