1. 索引的含义和特点
索引:创建在表上,是对数据库表中一列或多列的值进行排序的一种结构。
存储类型: B性树(BTREE)索引和哈希(HASH)索引; InnoDB和MyISAM支持BTREE索引, MEMORY支持BTREE索引和HASH索引
优缺点: 优点——提高检索数据的速度; 缺点: 创建和维护索引需要耗费时间
2. 索引分类
1)普通索引:不加任何限制条件
2)唯一性索引:使用UNIQUE参数(主键就是一种特殊唯一性索引)
3)全文索引: 使用FULLTEXT参数,只能创建在CHAR VARCHER或TEXT类型的字段上
4)单列索引: 在表中的单个字段上创建索引,唯一性索引或全文索引
5)多列索引: 在表的多个字段上创建一个索引;唯一性索引或全文索引
6)空间索引:使用SPATIAL参数,只有MyISAM存储引擎支持空间索引,必须建立在空间数据类型上,切必须非空
3. 如何设计索引
4. 创建索引
三种方式:创建表时创建索引, 在已经存在的表上创建索引,使用ALTER TABLE语句创建
4.1 创建表的时候创建索引
1)创建普通索引
在表的id字段建立索引CREATE TABLE index1(id INT,
nameVARCHAR(20),
sex BOOLEAN,INDEX(id)
);
2)创建唯一性索引
CREATE TABLE index2(id INT UNIQUE,
nameVARCHAR(20),UNIQUE INDEX index2_id(id ASC)
);
3)创建全文索引
CREATE TABLE index3(id INT,
infoVARCHAR(20),
FULLTEXTINDEXindex3_info(info)
)ENGINE=MyISAM;
4)创建单列索引
CREATE TABLE index4(id INT,
subjectVARCHAR(20),INDEX index4_st(subject(10))
);
5)创建多列索引
CREATE TABLE index5(id INT,
nameVARCHAR(20),
sexCHAR(4),INDEXindex5_ns(name, sex)
);
6)创建空间索引
CREATE TABLE index6(id INT,space GEOMETRY NOT NULL,
SPATIALINDEX index6_sp(space)
)ENGINE=MyISAM;
4.2 在已经存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX索引表ON 表名 (属性名 [(长度)] [ASC | DESC]
1)创建普通索引
在examp0表的id字段建立名为index7_id的索引CREATE INDEX index7_id ON example0(id)
2)创建唯一性索引
在index8表中的course_id字段建立名为index8_id的唯一性索引CREATE UNIQUE INDEX index8_id ON index8(course_id);
3)创建全文索引
在index9表中的info字段建立名为index9_info的全文索引CREATE FULLTEXT INDEX index9_info ON index9(info);
4)创建单列索引
在index10表中的address字段上建立名为index10_addr的单列索引CREATE INDEX index10_addr ON index10(address(4));
5)创建多列索引
在index11表中的name和address字段上建立名为index11_na的多列索引CREATE INDEX index11_na ON index11(name,address);
6)创建空间索引
在index12表中的line字段上建立名为index12_line的多列索引CREATE SPATIAL INDEX index12_line ON index12(line);
4.3 用ALTER TABLE语句创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULL TEXT | SPATIAL] INDEX索引名(属性名[(长度)] [ASC | DESC]);
1)创建普通索引
在example0表中的name字段建立名为index13_name的索引ALTER TABLE example0 ADD INDEX index13_name(name(20));
2)创建唯一性索引
ALTER TABLE index14 ADD UNIQUE INDEX index14_id(course_id);
3)创建全文索引
ALTER TABLE index15 ADD FULLTEXT INDEX index15_info(info);
4)创建单列索引
ALTER TABLE index16 ADD INDEX index16_addr(address(4));
5)创建多列索引
ALTER TABLE index17 ADD INDEX index17_na(name, address);
6)创建空间索引
ALTER TABLE index18 ADD SPATIAL INDEX index18_line(line);
5. 删除索引
DROP INDEX 索引名 ON 表名;