Java --- MySQL8之索引的创建与设计原则

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)
);

2.1.1、创建普通索引

使用INDEX 索引名(字段名)或KEY来进行声明

CREATE TABLE book(
book_id INT,
book_name VARCHAR(20),
AUTHORS VARCHAR(20),
#声明索引
INDEX idx_bname(book_name)
);

2.1.2、创建唯一索引

CREATE TABLE book1(
book_id INT,
book_name VARCHAR(20),
AUTHORS VARCHAR(20),
#声明索引
UNIQUE INDEX uk_idx_aut(AUTHORS)
);

2.1.3、创建主键索引

CREATE TABLE book2(
#声明索引
book_id INT PRIMARY KEY ,
book_name VARCHAR(20),
`AUTHORS` VARCHAR(20)
);

#删除主键索引
ALTER TABLE book2 DROP PRIMARY KEY;

2.1.4、创建单列索引

CREATE TABLE book1(
book_id INT,
book_name VARCHAR(20),
AUTHORS VARCHAR(20),
#声明索引
UNIQUE INDEX uk_idx_aut(AUTHORS)
);

2.1.5、创建联合索引

CREATE TABLE book3(
book_id INT,
book_name VARCHAR(20),
`AUTHORS` VARCHAR(20),
#声明索引
INDEX mul_bid_bname_info(book_id,book_name,`AUTHORS`)
);

2.1.6、创建全文索引

CREATE TABLE book4(
book_id INT,
book_name VARCHAR(20),
`AUTHORS` VARCHAR(20),
#声明索引
FULLTEXT INDEX ftxt_idx_bname(book_name(10))
);

2.2、给已存在的表创建索引

方式1：

CREATE TABLE book5(
book_id INT,
book_name VARCHAR(20),
`AUTHORS` VARCHAR(20),
info VARCHAR(20)
);


#ALTER TABLE 表名 ADD INDEX 索引名(字段名)
#添加普通索引
ALTER TABLE book5 ADD INDEX idx_info(info);
#添加唯一索引
ALTER TABLE book5 ADD UNIQUE uk_idx_bname(book_name);
#添加联合索引
ALTER TABLE book5 ADD INDEX mul_bid_bname_binfo(book_id,book_name,info);

SHOW INDEX FROM book5;

方式2：

CREATE TABLE book6(
book_id INT,
book_name VARCHAR(20),
`AUTHORS` VARCHAR(20),
info VARCHAR(20)
);
#CREATE INDEX 索引名 ON 表名(字段名)
#添加普通索引
CREATE INDEX idx_bid ON book6(book_id);
#添加唯一索引
CREATE UNIQUE INDEX uk_idx_bname ON book6(book_name);
#添加联合索引
CREATE INDEX mul_bid_bname_binfo ON book6(book_id,book_name,info);

三、查看索引

方式1：

SHOW CREATE TABLE book\G;

方式2：

SHOW INDEX FROM book;

四、删除索引

方式1：

#ALTER TABLE 表名 DROP INDEX 索引名;
ALTER TABLE book6 DROP INDEX idx_bid;

方式2：

#DROP INDEX 索引名 ON 表名;
DROP INDEX uk_idx_bname ON book6;

#联合索引中，删除表的字段，索引也会跟着变化
ALTER TABLE book6 DROP COLUMN book_name;

五、MySQL8索引新特性

5.1、支持降序索引

在MySQL8.0之前创建的仍然是升序索引，使用时使用反向扫描，大大降低了数据库效率。

CREATE TABLE test1(
a INT,
b INT,
INDEX idx_a_b(a ASC,b DESC)
);

5.2、隐藏索引

主要

最低0.47元/天解锁文章

鸭鸭老板

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
Java --- MySQL8之索引的创建与设计原则

目录一、索引的分类1.1、普通索引1.2、唯一索引 1.3、主键索引1.4、单列索引1.5、多列(联合)索引1.6、全文索引二、创建索引2.1、使用 CREATE TABLE创建2.1.1、创建普通索引 2.1.2、创建唯一索引 2.1.3、创建主键索引2.1.4、创建单列索引2.1.5、创建联合索引 2.1.6、创建全文索引2.2、给已存在的表创建索引三、查看索引四、删除索引五、MySQL8索引新特性5.1、支持降序索引 5.2、隐藏索引5.2.1、创建隐藏索引5.2.2、修改索引的可见性六、创建索
复制链接

扫一扫