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;
  REPEAT
  SET i = i + 1;
  INSERT INTO class ( classname,address,monitor ) VALUES (rand_string(8),rand_string(10),rand_num(1,100000));
  UNTIL i = max_num
  END 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))
BEGIN
      DECLARE 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 <>'' DO
              SET @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高级篇/MySQL索引及调优篇.md#3-关联查询优化

5. 子查询优化

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

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

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

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

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

在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 b
    WHERE 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 到磁盘进行排序的情况，效率较低。

优化建议：

1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描，在 ORDER BY 子句避免使用 FileSort 排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列； 如果不同就使用联合索引。
3. 无法使用 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;

最后：

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