往期:
六、索引的创建和删除
索引的分类:
- 从
功能逻辑
上说,索引主要有 4 种,分别是普通索引、唯一索引、主键索引、全文索引 - 按照
物理实现方式
,索引可以分为 2 种:聚簇索引和非聚簇索引 - 按照
作用字段个数
进行划分,分成单列索引和联合索引
不同的存储引擎支持的索引类型也不一样
- InnoDB :支持 B-tree、Full-text 等索引,不支持 Hash 索引;
- MyISAM : 支持 B-tree、Full-text 等索引,不支持 Hash 索引;
- Memory :支持 B-tree、Hash 等索引,不支持 Full-text 索引;
- NDB :支持 Hash 索引,不支持 B-tree、Full-text 等索引;
- Archive :不支持 B-tree、Hash、Full-text 等索引;
6.1 索引的创建
1.创建表的时候创建
CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]
UNIQUE 唯一索引
、FULLTEXT 全文索引
、SPATIAL 空间索引
INDEX
与KEY
是同义词,两者作用相同index_name
指定索引的名称,为可选参数,如果不指定,那么MySQL默认col_name为索引名col_name
为需要创建索引的字段列,该列必须从数据表中定义的多个列中选择,可以选多个列组成联合索引length
为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度ASC
或DESC
指定升序或者降序的索引值存储
CREATE TABLE book(
book_id INT,
book_name VARCHAR(100),
year_publication YEAR,
INDEX(year_publication) #普通索引,也是单列索引
INDEX multi_idx(book_id,book_name) #多列索引
UNIQUE INDEX uk_idx_id(book_id) #唯一索引
);
CREATE TABLE student (
id INT(10) UNSIGNED AUTO_INCREMENT,
student_no VARCHAR(200),
student_name VARCHAR(200),
PRIMARY KEY(id) #主键索引
);
CREATE TABLE `papers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
`content` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `title` (`title`,`content`) #全文索引
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
#不同于like方式查询
SELECT * FROM papers WHERE content LIKE ‘%查询字符串%’;
#全文索引可以用match+against方式查询:
SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);
CREATE TABLE test5(
geo GEOMETRY NOT NULL,
SPATIAL INDEX spa_idx_geo(geo) #空间索引
) ENGINE=MyISAM;
- 使用全文索引前,搞清楚版本支持情况;
- 全文索引比 like + % 快 N 倍,但是可能存在精度问题;
- 如果需要全文索引的是大量数据,建议先添加数据,再创建索引。
2.在已存在的表上创建
1.使用ALTER TABLE语句创建索引
ALTER TABLE table_name ADD
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]
2.使用CREATE INDEX创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]
6.2 索引的删除
1.使用ALTER TABLE删除
ALTER TABLE table_name DROP INDEX index_name;
2.使用DROP INDEX删除
DROP INDEX index_name ON table_name;
七、索引设计原则
7.1 哪些情况适合索引
- 字段的数值有
唯一性
的限制- 唯一性索引的值是唯一的,可以更快通过索引
确定
某条记录
- 唯一性索引的值是唯一的,可以更快通过索引
- 频繁作为
WHERE 查询条件
的字段- 某个字段在SELECT语句的 WHERE 条件中经常被使用到,那么就需要给这个字段创建索引了
- 经常
GROUP BY
和ORDER BY
的列- 排序操作会浪费很多时间,如果为其建立索引,可以有效的避免排序操作,相当于事先用索引为其排序
- UPDATE、DELETE 的 WHERE 条件列
- 在UPDATE或DELETE时,首先要用WHERE查询出来,如果添加了索引,找到这个要修改或删除的记录的效率就会提高
- 但是后续的更新操作,更新的字段是非索引字段效率反而会高,因为非索引字段不需要维护索引
- 所以,WHERE条件加索引,尽量不要修改索引字段
DISTINCT
字段需要创建索引- 如果需要去重,对该字段创建索引也会提