第26章【高级篇】索引的创建与删除

【宋红康 MySQL数据库】【高级篇】索引的创建与删除_MySQL8.0的索引新特性

# 索引的声明与使用

# 索引的分类

在这里插入图片描述

# 创建索引

在这里插入图片描述

# 创建表时创建索引

在这里插入图片描述

隐式创建索引：

#隐式的方式创建索引：在声明有主键约束、唯一性约束、外键约束的字段上，会自动的添加相关的索引
CREATE DATABASE dbtest2;

USE dbtest2;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

在这里插入图片描述

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

1
2
3
4
5
6
7
8
9
10
11

在这里插入图片描述

#性能分析工具：EXPLAIN
EXPLAIN SELECT * FROM book WHERE book_name = 'mysql高级';

1
2

通过命令查看索引：

#方式1：
SHOW CREATE TABLE book;

#方式2：
SHOW INDEX FROM book;
SHOW INDEX FROM book\G

1
2
3
4
5
6

在这里插入图片描述

2.创建唯一索引：

在这里插入图片描述

#② 创建唯一索引
# 声明有唯一索引的字段，在添加数据时，要保证唯一性，但是可以添加null
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;

INSERT INTO book1(book_id,book_name,COMMENT)
VALUES(1,'Mysql高级','适合有数据库开发经验的人员学习');

INSERT INTO book1(book_id,book_name,COMMENT)
VALUES(2,'Mysql高级',NULL);

SELECT * FROM book1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

3.主键索引：

在这里插入图片描述

#通过定义主键约束的方式定义主键索引
CREATE TABLE book2(
book_id INT PRIMARY KEY ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

SHOW INDEX FROM book2;

#通过删除主键约束的方式删除主键索引
ALTER TABLE book2 DROP PRIMARY KEY;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

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;

1
2
3
4
5
6
7
8
9
10
11
12

5.创建联合索引（组合索引）：

CREATE TABLE book4(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引【先按照book_id排序，其次按照book_name排序，再然后按照info排序】
INDEX mul_bid_bname_info(book_id,book_name,info)
);

SHOW INDEX FROM book4;


#分析【最左前缀原则可以使用索引】
EXPLAIN SELECT * FROM book4 WHERE book_id = 1001 AND book_name = 'mysql';

EXPLAIN SELECT * FROM book4 WHERE book_name = 'mysql';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

在这里插入图片描述

6.创建全文索引：

在这里插入图片描述

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(50))
)

SHOW INDEX FROM test4;

1
2
3
4
5
6
7
8
9

在这里插入图片描述

# 创建表后（已经存在的表上）创建索引

在这里插入图片描述

#① ALTER TABLE ... ADD ...

CREATE TABLE book5(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

SHOW INDEX FROM book5;

ALTER TABLE book5 ADD INDEX idx_cmt(COMMENT);

ALTER TABLE book5 ADD UNIQUE uk_idx_bname(book_name);

ALTER TABLE book5 ADD INDEX mul_bid_bname_info(book_id,book_name,info);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

在这里插入图片描述

#② CREATE INDEX ... ON ...

CREATE TABLE book6(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

SHOW INDEX FROM book6;

CREATE INDEX idx_cmt ON book6(COMMENT);

CREATE UNIQUE INDEX  uk_idx_bname ON book6(book_name);

CREATE INDEX mul_bid_bname_info ON book6(book_id,book_name,info);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 删除索引

在这里插入图片描述

SHOW INDEX FROM book5;

#方式1：ALTER TABLE .... DROP INDEX ....
ALTER TABLE book5 
DROP INDEX idx_cmt;

SHOW INDEX FROM book5;

1
2
3
4
5
6
7

在这里插入图片描述

#方式2：DROP INDEX ... ON ...
DROP INDEX uk_idx_bname ON book5;

1
2

测试：删除联合索引中的相关字段，索引的变化

SHOW INDEX FROM book5;

ALTER TABLE book5
DROP COLUMN book_name;

ALTER TABLE book5
DROP COLUMN book_id;

ALTER TABLE book5
DROP COLUMN info;

SHOW INDEX FROM book5;

1
2
3
4
5
6
7
8
9
10
11
12

在这里插入图片描述

# MySQL8 (opens new window).0索引新特性

# 支持降序索引

在这里插入图片描述

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

SHOW CREATE TABLE ts1;

DELIMITER //
CREATE PROCEDURE ts_insert()
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i < 800
	DO
		INSERT INTO ts1 SELECT RAND()*80000,RAND()*80000;
		SET i = i + 1;
	END WHILE;
	COMMIT;
END //
DELIMITER ;

#调用
CALL ts_insert();

SELECT COUNT(*) FROM ts1;

#优化测试
EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;

#不推荐
EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b DESC LIMIT 5;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

# 隐藏索引

在这里插入图片描述

#① 创建表时，隐藏索引
CREATE TABLE book7(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#创建不可见的索引
INDEX idx_cmt(COMMENT) invisible
);

SHOW INDEX FROM book7;

EXPLAIN SELECT * FROM book7 WHERE COMMENT = 'mysql....';

#② 创建表以后
ALTER TABLE book7
ADD UNIQUE INDEX uk_idx_bname(book_name) invisible;

CREATE INDEX idx_year_pub ON book7(year_publication);

EXPLAIN SELECT * FROM book7 WHERE year_publication = '2022';

#修改索引的可见性
ALTER TABLE book7 ALTER INDEX idx_year_pub invisible; #可见--->不可见

ALTER TABLE book7 ALTER INDEX idx_cmt visible; #不可见 ---> 可见

#了解：使隐藏索引对查询优化器可见

SELECT @@optimizer_switch \G

SET SESSION optimizer_switch="use_invisible_indexes=on";

EXPLAIN SELECT * FROM book7 WHERE year_publication = '2022';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

编辑

上次更新: 2024/01/26, 05:03:22

← 第25章【高级篇】InnoDB的数据存储结构第27章【高级篇】索引的设计原则→