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

最新推荐文章于 2024-04-29 23:47:34 发布

lpruoyu

最新推荐文章于 2024-04-29 23:47:34 发布

阅读量1k

点赞数 6

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_44018671/article/details/126343898

版权

数据库专栏收录该内容

47 篇文章 79 订阅

订阅专栏

持续学习&持续更新中…

学习态度：守破离

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

索引的声明与使用
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.创建普通索引：

#① 创建普通的索引
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)
);

在这里插入图片描述

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

通过命令查看索引：

#方式1：
SHOW CREATE TABLE book;

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

在这里插入图片描述

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;

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;

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.创建联合索引（组合索引）：

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

在这里插入图片描述

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;

在这里插入图片描述

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

在这里插入图片描述

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

在这里插入图片描述

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

删除索引

在这里插入图片描述

SHOW INDEX FROM book5;

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

SHOW INDEX FROM book5;

在这里插入图片描述

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

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

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;

在这里插入图片描述

MySQL8.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;

隐藏索引

在这里插入图片描述

#① 创建表时，隐藏索引
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';