1.1 索引的分类
MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
- 从 功能逻辑 按照上说,索引主要有 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 等索引;
1.2 创建索引
1、创建表的时候创建索引
隐式的索引创建:
# 1.隐式的添加索引(在添加有主键约束、唯一性约束或者外键约束的字段会自动的创建索引)
CREATE TABLE dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,# 创建主键索引
dept_name VARCHAR(20)
);
CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,# 主键索引
emp_name VARCHAR(20) UNIQUE,# 唯一索引
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
); # 外键索引
显式的索引创建:
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 指定升序或者降序的索引值存储。
- 特例:主键索引使用主键约束的方式来创建。
1、创建普通索引
在book表中的year_publication字段上建立普通索引,SQL语句如下:
# ①创建普通的索引
CREATE TABLE book(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
# 声明索引
INDEX idx_bname(book_name)
);
通过命令查看索引有没有创建成功
# 方式1:
SHOW CREATE TABLE book; # Linux下添加\G参数
# 方式2:
SHOW INDEX FROM book;
#性能分析工具:EXPLAIN,查看索引是否正在使用
EXPLAIN SELECT * from book where book_name = 'mysql高级';
EXPLAIN语句输出结果的各个行我们在下一章讲解,这里主要关注两个字段
- possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引
- key行时MySQL实际选用的索引
可以看到,possible_keys和key值都为idx_bname,查询时使用了索引
2、创建唯一索引
# ②创建唯一索引
CREATE TABLE book1 (
book_id INT,
book_name VARCHAR (100),
AUTHORS VARCHAR (100),
info VARCHAR (100),
COMMENT VARCHAR (100),
year_publication YEAR,
#声明索引
UNIQUE INDEX uk_idx_cmt (COMMENT)
);
show INDEX from book1;# 查看索引
3、主键索引
设定为主键后数据库会自动建立索引,innodb为聚簇索引,语法:
随表一起建索引:
# ③主键索引
# 通过定义主键约束的方式定义主键索引
create table book2(
book_id int primary key,
book_name varchar(100),
AUTHORS VARCHAR (100),
info VARCHAR (100),
COMMENT VARCHAR (100),
year_publication YEAR
) ;
删除主键索引:
# 通过删除主键约束的方式删除主键索引
alter table book2
drop primary key;
修改主键索引:必须先删除掉(drop)原索引,再新建(add)索引
4、创建单列索引
#④ 创建单列索引
CREATE TABLE book3(
book_id INT,
book_name VARCHAR(100),
AUTHORS VARCHAR (100),
info VARCHAR (100),
COMMENT VARCHAR (100),
year_publication YEAR,
UNIQUE INDEX idx_bname(book_name)
);
show index from book3;
5、创建组合索引
举例:创建表 book4,在表中的 book_id、book_name和 info字段上建立组合索引,SQL 语句如下:
# ⑤ 创建联合索引
create table book4(
book_id INT,
book_name VARCHAR(100),
AUTHORS VARCHAR (100),
info VARCHAR (100),
COMMENT VARCHAR (100),
year_publication YEAR,
index mul_bid_bname_info(book_id,book_name,info)
)
SHOW INDEX FROM book4;
注意上面三行依次是book_id,book_name,info,与我们创建索引时指定的顺序是严格对应的。在查询时会遵守最左索引原则,先进行book_id条件的比较,然后再进行book_name比较,最后才是info。因此注意把最常用的查询字段放在索引的最左边。
# 分析
explain select * from book4 where book_id = 1001 and book_name = 'mysql'; # 会使用到mul_bid_bname_info索引
explain select * from book4 where book_name = 'mysql';# 不会使用到mul_bid_bname_info索引
6、 创建全文索引
FULLTEXT全文索引可以用于全文搜索,并且只为CHAR
、VARCHAR
和TEXT
列创建索引。索引总是对整个列进行,不支持局部(前缀)索引。
举例1:创建表test4,在表中的 info 字段上建立全文索引,SQL 语句如下:
CREATE TABLE test4(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX futxt_idx_info(info)
) ENGINE=MyISAM;
在 MySQL 5.7 及之后版本中可以不指定最后的 ENGINE 了,因为在此版本中 InnoDB 支持全文索引。
语句执行完毕后,用SHOW CREATE TABLE查看表结构:
SHOW INDEX FROM test4\G;
由结果可以看到,info字段上已经成功建立了一个名为futxt_idx_info的FULLTEXT索引。
举例2:
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR (200),
body TEXT,
FULLTEXT index (title, body)
) ENGINE = INNODB ;
创建了一个给 title 和 body 字段添加全文索引的表。
举例3:
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 (‘查询字符串’);
明显的提高查询效率
注意点
- 使用全文索引前,搞清楚版本支持情况;
- 全文索引比like + % 快 N倍,但是可能存在精度问题
- 如果需要全文索引的是大量数据,建议先添加数据,再创建索引。
2、在已经存在的表上创建索引
在已经存在的表中创建索引可以使用 ALTER TABLE 语句或者 CREATE INDEX 语句。
1. 使用 ALTER TABLE 语句创建索引
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]
2. 使用 CREATE INDEX 创建索引
CREATE INDEX 语句可以在已经存在的表上添加索引,在 MySQL 中, CREATE INDEX 被映射到一个 ALTER TABLE 语句上,基本语法结构为:
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]
举例1:在book表的comment字段上建立名为 的普通索引
create index idx_cmt on book(comment);
举例2:在book表中的book_id字段上建立名为uk_idx_bid的唯一索引,SQL语句如下:
CREATE UNIQUE INDEX uk_idx_bid ON book(book_id);
举例3:在book表的book_id、book_name、info字段上建立联合索引,SQL语句如下:
CREATE INDEX mul_bid_bname_info ON book(book_id,book_name,info);
1.3 删除索引
MySQL中删除索引使用ALTER TABLE或DROP INDEX语句,两者可实现相同的功能,DROP INDEX语句在内部被映射到一个ALTER TABLE语句中
1. 使用 ALTER TABLE 删除索引:
ALTER TABLE删除索引的基本语法格式如下:
ALTER TABLE table_name DROP INDEX index_name;
练习:删除book表中名称为idx_bk_id的唯一索引
首先查看book表中是否名称为idx_bk_id的索引,输入SHOW语句如下:
SHOW INDEX FROM book\G;
下面删除该索引,输入删除语句如下:
ALTER TABLE book DROP INDEX idx_bk_id;
提示
添加AUTO_INCREMENT约束字段的唯一索引不能被删除()
2. 使用 DROP INDEX 语句删除索引:
DROP INDEX删除索引的基本语法格式如下:
DROP INDEX index_name ON table_name;
练习:删除book表中名称为idx_aut_info的组合索引,SQL语句如下:
DROP INDEX idx_aut_info ON book;
语句执行完毕,使用SHOW查看索引是否删除:
SHOW CREATE TABLE book\G;
可以看到,book表中已经没有名称为idx_aut_info的组合索引,删除索引成功。
提示 删除表中的列时,如果要删除的列为索引的组成部分,则该列也会从索引中删除。如果组成索引的所有列都被删除,则整个索引将被删除。