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


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

@

目录

这篇文章是我蹲在《尚硅谷》-康师傅博主家的 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;

最后:

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