10-1 MySQL 索引优化与查询优化

文章目录

10-1 MySQL 索引优化与查询优化
1. 数据准备
2. 索引失效案例
- 2.1 索引字段：全值匹配最优
- 2.2 索引字段：最佳左前缀法则
- 2.3 主键插入顺序
- 2.4 索引字段进行了：计算、函数、类型转换(自动或手动)导致索引失效
- 2.5 索引字段类型转换导致索引失效
- 2.6 索引字段：使用了范围条件，右边的列索引失效
- 2.7 索引字段：不等于(!= 或者<>)索引失效
- 2.8 索引字段： is null可以使用索引，is not null无法使用索引
- 2.9 索引字段：使用了 like以通配符 % 开头索引失效
- 2.10 OR 前后存在非索引的列，索引失效
- 2.11 数据库和表的字符集统一使用utf8mb4
3. 关联查询优化
- 3.1 数据准备
- 3.2采用左外连接
- 3.3 采用内连接
4. join语句原理
5. 子查询优化
6. 排序优化
最后：

这篇文章是我蹲在《尚硅谷》-康师傅博主家的 WiFi 上(不是)，连夜 Ctrl+C / V 俩的镇站神文。

这篇转载只是为了，跟大家分享好内容，没有任何商业用途。如果你喜欢这篇文章，请一定要去原作者 B站《尚硅谷-MySQL从菜鸟到大牛》看看，说不定还能发现更多宝藏内容呢！

1. 数据准备

CREATE DATABASE dbtest4;

学员表 插 50万 条， 班级表 插 1万 条。

步骤1：建表


USE dbtest4;CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

步骤2：设置参数

命令开启：允许创建函数设置：

set global log_bin_trust_function_creators=1;    # 不加global只是当前窗口有效。

步骤3：创建函数

保证每条数据都不同。


#随机产生字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;#假如要删除
#drop function rand_string;

随机产生班级编号

#用于随机产生多少到多少的编号
DELIMITER //
CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;#假如要删除
#drop function rand_num;

步骤4：创建存储过程


#创建往stu表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_stu(startt INT ,  max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;SET autocommit =0;#设置手动提交事务REPEAT #循环SET i = i + 1; #赋值INSERT INTO student (stuno, NAME ,age ,classId ) VALUES ((startt+i),rand_string(6),rand_num(1,50),rand_num(1,1000));UNTIL i = max_num END REPEAT; COMMIT; #提交事务
END //
DELIMITER ;#假如要删除
# drop PROCEDURE insert_stu;

创建往 class 表中插入数据的存储过程

#执行存储过程，往class表添加随机数据
DELIMITER //
CREATE PROCEDURE `insert_class`(  max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i + 1;INSERT INTO class ( classname,address,monitor ) VALUES (rand_string(8),rand_string(10),rand_num(1,100000));UNTIL i = max_numEND REPEAT;COMMIT;
END //
DELIMITER ;#假如要删除
#drop PROCEDURE insert_class;

步骤5：调用存储过程

class

#执行存储过程，往class表添加1万条数据
CALL insert_class(10000);

在这里插入图片描述

stu

#执行存储过程，往stu表添加50万条数据
CALL insert_stu(100000,500000);

在这里插入图片描述

步骤6：删除某表上的索引

创建存储过程


DELIMITER //
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGINDECLARE done INT DEFAULT 0;DECLARE ct INT DEFAULT 0;DECLARE _index VARCHAR(200) DEFAULT '';DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema = dbname AND table_name = tablename AND seq_in_index = 1 AND index_name <> 'PRIMARY';DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 2; OPEN _cur;FETCH _cur INTO _index;WHILE _index <>'' DOSET @str = CONCAT("drop index", _index,"on" , tablename);PREPARE sql_str FROM @str ;EXECUTE sql_str;DEALLOCATE PREPARE sql_str;SET _index='';FETCH _cur INTO _index;END WHILE;CLOSE _cur;
END //
DELIMITER ;

执行存储过程

CALL proc_drop_index("dbname","tablename");

2. 索引失效案例

2.1 索引字段：全值匹配最优

全值匹配最优：指的是我们查询的内容，过滤上都走了我们的索引，都和我们创建的索引完全匹配上了。

在这里插入图片描述

# 1. 全值匹配我最爱
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30;EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classid = 4;EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classid = 4 AND `name` = 'abcd';# 添加上索引
CREATE INDEX idx_age ON student(age);
CREATE INDEX idx_age_classid ON student(age,classId);
CREATE INDEX idx_age_classid_name ON student(age,classId,`name`);

2.2 索引字段：最佳左前缀法则

拓展：Alibaba《Java开发手册》
索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

在这里插入图片描述

SHOW INDEX FROM student;
# 最佳左前缀法则
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND `name` = 'abcd';EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid = 1 AND `name` = 'abcd';EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid = 1 AND `name` = 'abcd' AND age = 30;

2.3 主键插入顺序

在这里插入图片描述

如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图：

在这里插入图片描述

可这个数据页已经满了，再插进来咋办呢？我们需要把当前 页面分裂 成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着：性能损耗 ！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增 ，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入，比如： person_info 表：

CREATE TABLE person_info(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

我们自定义的主键列 id 拥有AUTO_INCREMENT属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。

2.4 索引字段进行了：计算、函数、类型转换(自动或手动)导致索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

在这里插入图片描述

# 计算、函数、类型转换(自动或手动)导致索引失效EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE '%abc';

在这里插入图片描述

type为“ALL”，表示没有使用到索引，查询时间为 3.62 秒，查询效率较之前低很多。

student表的字段stuno上设置有索引

CREATE INDEX idx_sno ON student(stuno);

# (索引字段)计算导致索引失效
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

在这里插入图片描述

# student 表的字段 stuno上设置有索引
CREATE INDEX idx_sno ON student(stuno);
# (索引字段)计算导致索引失效
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;# 索引优化：对于这种索引简单运算的，我们可以优先将运算结果计算出来，再进行查询，
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

在这里插入图片描述

2.5 索引字段类型转换导致索引失效


# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME=123;# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME='123';

在这里插入图片描述

2.6 索引字段：使用了范围条件，右边的列索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age = 30 AND student.classId > 20 AND student.name = 'abc';

在这里插入图片描述

将范围查询条件放置语句最后：

CREATE INDEX idx_age_classId_name ON student(age,NAME,classId);EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age = 30 AND student.classId > 20 AND student.name = 'abc';

在这里插入图片描述

应用开发中范围查询，例如：金额查询，日期查询等等一些范围查询，在创建索引时，需将这些范围查询的字段放到(索引字段的最后面)。

2.7 索引字段：不等于(!= 或者<>)索引失效

为 name 字段创建索引

# 不等于 (!= 或者 <>) 索引失效
CREATE INDEX idx_name ON student(`name`);EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name` <> 'abc';EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name` != 'abc';

在这里插入图片描述

2.8 索引字段： is null可以使用索引，is not null无法使用索引

is null 可以使用索引：

# is null 可以使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name` IS NULL;

在这里插入图片描述

is not null ：无法使用索引，索引失效

# is not null ：无法使用索引，索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name` IS NOT NULL;

在这里插入图片描述

结论：最好在设计数据表的时候就将 字段设置为 NOT NULL 约束 ，比如可以将 INT 类型的字段，默认值设置为 0，将字符类型的默认值设置为空字符串"" 。

拓展：同理，在查询中使用 no like 也无法使用索引，导致全表扫描。

2.9 索引字段：使用了 like以通配符 % 开头索引失效

# 索引字段当中使用了 like 以通配符 '%' 开头索引失效
# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name`LIKE '%ab';

在这里插入图片描述

# 索引字段当中使用了 like 以通配符 '%' 开头索引失效
# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name`LIKE 'ab%';

在这里插入图片描述

2.10 OR 前后存在非索引的列，索引失效

在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而 OR后的条件没有进行索引，那么索引会失效，也就是说，OR 前后的两个条件中的列都时索引时，查询中才使用索引 。

因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件进行了索引是没有意义的，只要有条件列没有索引，就会进行全表扫描 ，因此索引的条件列也会失效。

# 创建 age 的索引
CREATE INDEX idx_age ON student(age);EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`age` = 10 OR student.`name` = 'abc';

在这里插入图片描述

2.11 数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行转换会造成索引失效。

3. 关联查询优化

3.1 数据准备

# 分类
CREATE TABLE IF NOT EXISTS `type` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
#图书
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);#向分类表中添加20条记录
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));#向图书表中添加20条记录
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

3.2采用左外连接

下面开始 EXPLAIN 分析

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

在这里插入图片描述

添加索引优化

# 添加索引
ALTER TABLE book ADD INDEX Y ( card);   #【被驱动表】，可以避免全表扫描EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

在这里插入图片描述

可以看到第二行的 type 变为了 ref，rows 也变成了优化比较明显。这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行，左边一定都有，所以 右边是我们的关键点,一定需要建立索引。

ALTER TABLE `type` ADD INDEX X (card); #【驱动表】，无法避免全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

在这里插入图片描述

接着：

DROP INDEX Y ON book;
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

在这里插入图片描述

3.3 采用内连接

drop index X on type;
drop index Y on book;（如果已经删除了可以不用再执行该操作）

换成 inner join（MySQL自动选择驱动表）

EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;

在这里插入图片描述

添加索引优化

# 添加索引优化
ALTER TABLE book ADD INDEX Y ( card);
EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;

在这里插入图片描述

ALTER TABLE TYPE ADD INDEX X (card);
EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;

在这里插入图片描述

接着：

DROP INDEX X ON `type`;
EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;

在这里插入图片描述

对于内连接来说，如果表的连接条件中只有一个字段有索引，则有索引的字段所在的表会被作为驱动表。

接着


#向图书表中添加20条记录
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

DROP INDEX `Y` ON book; # 删除索引
SHOW INDEX FROM book;EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON `type`.card = book.card;

在这里插入图片描述

对于内连接来说：在两个表的连接条件都存在索引(都不存在索引的)的情况下，会选择小表作为驱动表，“小表驱动大表”

4. join语句原理

https://github.com/codinglin/StudyNotes/blob/main/MySQL%E9%AB%98%E7%BA%A7%E7%AF%87/MySQL%E7%B4%A2%E5%BC%95%E5%8F%8A%E8%B0%83%E4%BC%98%E7%AF%87.md#3-%E5%85%B3%E8%81%94%E6%9F%A5%E8%AF%A2%E4%BC%98%E5%8C%96

在这里插入图片描述

5. 子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。 子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作 。

**子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。**原因：

执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。

子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。

对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

**在MySQL中，可以使用连接（JOIN）查询来替代子查询。**连接查询 不需要建立临时表 ，其 速度比子查询 要快，如果查询中使用索引的话，性能就会更好。

举例1：查询学生表中是班长的学生信息

# 创建班级表中班长的索引
CREATE INDEX idx_monitor ON class(monitor);EXPLAIN SELECT * FROM student stu1
WHERE stu1.`stuno` IN (
SELECT monitor
FROM class c
WHERE monitor IS NOT NULL
)

推荐使用多表查询

EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c
ON stu1.`stuno` = c.`monitor`
WHERE c.`monitor` is NOT NULL;

在这里插入图片描述

举例2：取所有不为班长的同学

不推荐

EXPLAIN SELECT SQL_NO_CACHE a.*
FROM student a
WHERE a.stuno NOT IN (SELECT monitor FROM class bWHERE monitor IS NOT NULL
);

在这里插入图片描述

推荐：

EXPLAIN SELECT SQL_NO_CACHE a.*
FROM student a LEFT OUTER JOIN class b
ON a.stuno = b.monitor
WHERE b.monitor IS NULL;

在这里插入图片描述

结论：尽量不要使用 NOT IN或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

6. 排序优化

问题：在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？

回答：

在MySQL中，支持两种排序方式，分别是 FileSort 和 Index 排序。

Index 排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
FileSort 排序则一般在 内存中 进行排序，占用CPU较多。如果待排结果较大，会产生临时文件 I/O 到磁盘进行排序的情况，效率较低。

优化建议：

SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
无法使用 Index 时，需要对 FileSort 方式进行调优。

测试：

删除student表和class表中已创建的索引。

# 方式1
DROP INDEX idx_monitor ON class;
DROP INDEX idx_cid ON student;
DROP INDEX idx_age ON student;
DROP INDEX idx_name ON student;
DROP INDEX idx_age_name_classId ON student;
DROP INDEX idx_age_classId_name ON student;

以下是否能使用到索引，能否去掉using filesort

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;

在这里插入图片描述

测试2： order by 时不使用 limit ，索引失效

# 过程二: order by 时 不使用 limit ，索引失效
# 创建索引
CREATE INDEX idx_age_classid_name ON student (age,classid,`name`);
# 不使用 limit 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid ;

在这里插入图片描述

# order by  使用 limit 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 100;

在这里插入图片描述

测试三：order by 排序当中字段，不满足索引最左匹配原则，顺序错误，索引失效

# 测试三：order by 排序当中，顺序错误，索引失效 EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY classid,`name` LIMIT 100;

在这里插入图片描述

测试四： order by 时，规则不一致(索引排序不一致(反序可以走索引，mysql 8.0 支持)，)，索引失效

# 测试： order by 时，规则不一致(索引排序不一致(反序可以走索引，mysql 8.0 支持)，)，索引失效EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age DESC, classid DESC LIMIT 100;

在这里插入图片描述

# 测试： order by 时，规则不一致(索引排序不一致(反序可以走索引，mysql 8.0 支持)，)，索引失效EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age ASC, classid DESC LIMIT 100;

在这里插入图片描述

最后：

“在这个最后的篇章中，我要表达我对每一位读者的感激之情。你们的关注和回复是我创作的动力源泉，我从你们身上吸取了无尽的灵感与勇气。我会将你们的鼓励留在心底，继续在其他的领域奋斗。感谢你们，我们总会在某个时刻再次相遇。”

10-1 MySQL 索引优化与查询优化

文章目录

1. 数据准备

2. 索引失效案例

2.1 索引字段：全值匹配最优

2.2 索引字段：最佳左前缀法则

2.3 主键插入顺序

2.4 索引字段进行了：计算、函数、类型转换(自动或手动)导致索引失效

2.5 索引字段类型转换导致索引失效

2.6 索引字段：使用了范围条件，右边的列索引失效

2.7 索引字段：不等于(!= 或者<>)索引失效

2.8 索引字段： is null可以使用索引，is not null无法使用索引

2.9 索引字段：使用了 like以通配符 % 开头索引失效

2.10 OR 前后存在非索引的列，索引失效

2.11 数据库和表的字符集统一使用utf8mb4

3. 关联查询优化

3.1 数据准备

3.2采用左外连接

3.3 采用内连接

4. join语句原理

5. 子查询优化

6. 排序优化

最后：

相关文章