一、创建表时创建索引
1. 创建普通索引
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)
);
# 查看索引
show index from book;
#查看某个查询语句中是否使用到了索引
explain select * from book b where b.book_name = 'mysql高级';
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)
);
3. 创建联合索引
create table book2 (
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)
);
二、在已存在的表上创建索引
(1)方式一:
create table book3 (
book_id int,
book_name varchar(100),
AUTHORS varchar(100),
info varchar(100),
comment varchar(100),
year_publication year
);
1. 创建普通索引
alter table book3 add index idx_cmt(comment);
2. 创建唯一性索引
alter table book3 add unique index uk_idx_bname(book_name);
3. 创建联合索引
alter table book3 add index mul_bid_bname_info(book_id, book_name, info);
(2)方式二:
create table book4 (
book_id int,
book_name varchar(100),
AUTHORS varchar(100),
info varchar(100),
comment varchar(100),
year_publication year
);
1. 创建普通索引
create index idx_cmt on book4(comment);
2. 创建唯一性索引
create unique index uk_idx_bname on book4(book_name);
3. 创建联合索引
create index mul_bid_bname_info on book4(book_id, book_name, info);