当前位置： 首页 > article >正文

MySQL数据库入门到大蛇尚硅谷宋红康老师笔记 高级篇 part10

article 2025/3/28 12:41:30

第10章_索引优化与查询优化

虽然SQL查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。
·物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。
·逻辑查询优化就是通过SQL等价变换提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高。

1.数据准备

#1. 数据准备

CREATE DATABASE atguigudb2;

USE atguigudb2;

#建表
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;


SET GLOBAL log_bin_trust_function_creators=1; 
#随机产生字符串
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 ;

#用于随机产生多少到多少的编号
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 ;

#创建往stu表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE  insert_stu(  START 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 ((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  #提交事务
END //
DELIMITER ;

 1.建表-----2.设置参数----3.创建参数-------5.调用存储过程----6.删除某表的索引

#执行存储过程，往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 ;


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

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

SELECT COUNT(*) FROM class;

SELECT COUNT(*) FROM student;
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=1来控制游标的结束
       DECLARE  CONTINUE HANDLER FOR NOT FOUND SET done=2 ;      
#若没有数据返回,程序继续,并将变量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 ;

2.索引失效案例

141-数据准备与索引失效的11种情况1_哔哩哔哩_bilibili

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

#我们已经写了一个简单的左外连接
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

假设我们type有20条数据 , 而boook有30条数据 , 类似于嵌套循环一样 , type是驱动表而book是被驱动表,先从驱动表type中以"type.card = book.card"这样的条件, 一遍一遍的ALL遍历30次,显然要优化:

#添加索引
CREATE INDEX Y ON book(card);

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

这下主外链接,如果只能添加一个索引,请为被驱动表添加索引

CREATE INDEX X ON `type`(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;
DROP INDEX X ON `type`;

#内连接
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;
#添加索引
CREATE INDEX Y ON book(card);

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

CREATE INDEX X ON `type`(card);

内连接中驱动表和被驱动表可以互换, 不会影响最后的查询结果; 而 左/右外连接就不能轻易互换了
#结论：对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;


#删除索引
DROP INDEX Y ON book;
#结论：对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现。
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;
CREATE INDEX Y ON book(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

#向type表中添加数据（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)));

#结论：对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。“小表驱动大表”
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

现在type就变成ref了,小表驱动大表

3.4 JOIN语句的原理

 join方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL5.5版本之前，MySQL只支持一种表间关联方式，就是嵌套循环（NestedLoopJoin)。如果关联表的数据量很大，则join关联的执行时间会非常长。在MySQL5.5以后的版本中，MySQL通达引入BNLJ算法来优化嵌套执行。

1.驱动表和被驱动表

驱动表就是主表，被驱动表就是从表、非驱动表。

 对于内连接来说：
     SELECT * FROM A JOIN B ON..
A一定是驱动表吗？不一定，优化器会根据你查询语句做优化，决定先查哪张表。先查询的那张表就是驱动表，反之就是被驱动表。通过explain关键字可以查看。

 对于外连接来说
SELECT * FROM A LEFT JOIN B ON...
#或
SELECT * FROM B RIGHT JOIN A ON..

通常，大家会认为A就是驱动表，B就是被驱动表。但也未必。测试如下：

#JOIN的底层原理

CREATE TABLE a(f1 INT, f2 INT, INDEX(f1))ENGINE=INNODB;

CREATE TABLE b(f1 INT, f2 INT)ENGINE=INNODB;


INSERT INTO a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);

INSERT INTO b VALUES(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);


#测试1
EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) WHERE (a.f2=b.f2);
#此时已经变成了一个外连接

#测试2
EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) AND (a.f2=b.f2);

#测试3
EXPLAIN SELECT * FROM a JOIN b ON(a.f1=b.f1) WHERE (a.f2=b.f2);


SHOW VARIABLES LIKE '%optimizer_switch%';

SHOW VARIABLES LIKE '%join_buffer%';

外连接本来on后面加的连接条件，where后加普通过滤条件。如果where后还加连接条件就成内连接了

外表扫描次数：1,内表扫描次数：A,读取记录数：A+B*A,JOIN比较次数：B*A,回表读取记录次数：0
当然mysql肯定不会这么粗暴的去进行表的连接，所以就出现了后面的两种对Nested-LoopJoin优化算法。

我们从A表中取一个数据出来，然后B中出来数据一个个的到内存中，A那个就一个个对应的在内存中走走走，走完内存就清掉了；然后A取第二个数据，B里面再加载一遍，再清掉。

 3.Index Nested-Loop Join（索引嵌套循环连接）

IndexNested-LoopJoin其优化的思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数。

 开销统计SNLJINLJ
外表扫描次数：1                        1
内表扫描次数：A                      0
读取记录数：A+B*A            A+B(match)
JOIN比较次数：B*A                    A*Index(Height)
回表读取记录次数：0                  B(match) (if possible)

4.Bldck Nested-Loop Join（块嵌套循环连接）

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引l，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了10的次数。为了减少被驱动表的io次数，就出现了BlockNested-LoopJoin的方式。

不再是逐条获取驱动表的数据，而是一块一块的获取，引l入了joinbuffer缓冲区，将驱动表join相关的部分数据列（大小受joinbuffer的限制）缓存到joinbufer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和joinbuffer中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次降低了被驱动表的访问频率。

开销统计         SNLJ          INLJ            BNLJ
外表扫描次数：1              1                   1
内表扫描次数：A                 0              A *used_column_size/join_buffer_size+1
读取记录数：A+B*A      A+B(match)        A+B*(A *used_column_size/join_buffer_size)
JOIN比较次数：B*A             A *Index(Height)         B*A
回表读取记录次数：0        B(match) (if possible)          0

 A表全表扫面一边,再把需要用的平均分给join buffer,有几份就是几次+1次扫描

参数设置：
          block_nested_loop
通过show variables like '%optimizer_switch%'查看block_nested_loop状态。默认是开启的。
          join_buffer_size
驱动表能不能一次加载完，要看joinbuffer能不能存储所有的数据，默认情况下join_buffer_size=256k。join_buffer_size的最大值在32位系统可以申请4G，而在64位操做系统下可以申请大于4G的JoinBuffer空间（64位Windows除外，其大值会被截断为4GB并发出警告）。

1、**整体效率比较：INLJ>BNLJ>SNLJ
2、永远用小结果集驱动大结果集（其本质就是减少外层循环的数据数量）（小的度量单位指的是表行数每行大小)  过滤之后才是要扫描读取的部分

3、为被驱动表匹配的条件增加索引（(减少内层表的循环匹配次数)
4、增大joinbuffersize的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）
5、减少驱动表不必要的字段查询（字段越少，joinbuffer所缓存的数据就越多）

 6.Hash Join

从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hashjoin默认都会使用hashjoin

4.子查询优化

一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。
子查询是MySQL的一项重要的功能，可以帮助我们通过一个SQL语句实现比较复杂的查询。但是，子查询的执行效率不高。原因：

①执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
②子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
③对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用JOIN来代替子查询。

#4. 子查询的优化

#创建班级表中班长的索引
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
);

--is not null 不一定会导致索引失效，是is not null 的数据太多了回表的次数太多，
--数据量太大，成本太高的情况，查询优化器会强制执行全表扫描

EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c 
ON stu1.`stuno` = c.`monitor`
WHERE c.`monitor` IS NOT NULL;
#查询不为班长的学生信息
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里面，那就is null
尽量不要使用NOTIN或者NOTEXISTS，用LEIT JOIN xXX ON xx WHERE xx IS NULL替代

5.排序优化

问题：在WHERE条件字段上加索引，但是为什么在ORDERBY字段上还要加索引呢？
回答：
在MySQL中，支持两种排序方式，分别是FileSort 和Index排序。
·Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
·FileSort排序则一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。

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

#5. 排序优化
#删除student和class表中的非主键索引
CALL proc_drop_index('atguigudb2','student');
CALL proc_drop_index('atguigudb2','class');

SHOW INDEX FROM student;
SHOW INDEX FROM class;
#过程一：
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid; 

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

#过程二：order by时不limit，索引失效
#创建索引  
CREATE  INDEX idx_age_classid_name ON student (age,classid,NAME);--这时候我们
在student表中针对于age，classid和name建立了一个联合索引，

#不限制,索引失效
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid; 
到底有没有用索引？不好说，如果用了，就是上面那个联合索引，当数据量大的时候，他这样一个
二级索引，一直后还要回表找找主表中的聚簇索引，每一条数据还得找到叶子节点，
结果就是数据量太大，回表操作太多，于是干脆直接把数据加载进来，直接看时间反而少一些

#EXPLAIN  SELECT SQL_NO_CACHE age,classid,name,id FROM student ORDER BY age,classid; 
此时反而用了索引，这是因为我们没有回表，引出覆盖索引
#增加limit过滤条件，使用上索引了。
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;
由于你只要十条数据，那么直接排序取前十条即可  
#过程三：order by时顺序错误，索引失效

#创建索引age,classid,stuno
CREATE  INDEX idx_age_classid_stuno ON student (age,classid,stuno); 

#以下哪些索引失效?
EXPLAIN  SELECT * FROM student ORDER BY classid LIMIT 10;          --NO

EXPLAIN  SELECT * FROM student ORDER BY classid,NAME LIMIT 10;     --no

EXPLAIN  SELECT * FROM student ORDER BY age,classid,stuno LIMIT 10; --YES
首先age是允许为null的，因此占用5字节，classid也是5个，stuno不能null是4个字节，
因此key_len就是14

EXPLAIN  SELECT * FROM student ORDER BY age,classid LIMIT 10;      --yes
idx_age_classid_name        因此len是73

EXPLAIN  SELECT * FROM student ORDER BY age LIMIT 10;              --yes
#过程四：order by时规则不一致, 索引失效 （顺序错，不索引；方向反，不索引）

EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;
主要原因是age是降序排的,

EXPLAIN  SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10;--no

EXPLAIN  SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10; --no
主要是age asc是升序的,但是classid又变成降序了

EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;--
#过程五：无过滤，不索引

EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid;

EXPLAIN  SELECT * FROM student WHERE  age=45 ORDER BY classid,NAME; 
感觉这里是5是因为age限制后，classid已经有序了，直接就回表了，要不然排序的话依然会
有extra信息的

EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age;

EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age LIMIT 10;

CREATE INDEX idx_cid ON student(classid);
EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age;
#前面说先过滤再排序，性能肯定好一定，毕竟避免了无效的数据都排序了一个遍

 

145-子查询优化与排序优化_哔哩哔哩_bilibili 真听不下去了,云里雾里的

#实战：测试filesort和index排序
CALL proc_drop_index('atguigudb2','student');

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

#方案一: 为了去掉filesort我们可以把索引建成

CREATE INDEX idx_age_name ON student(age,NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

#方案二：

CREATE INDEX idx_age_stuno_name ON student(age,stuno,NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

DROP INDEX idx_age_stuno_name ON student;

CREATE INDEX idx_age_stuno ON student(age,stuno);

6.GROUP BY优化

  1. groupby使用索引的原则几乎跟orderby一致，groupby即使没有过滤条件用到索引，也可以直接使用索引。
  2. groupby先排序再分组，遵照索引建的最佳左前缀法则
  3. 当无法使用索引l列，增大max_length_for_sort_data和sort_buffer_size参数的设置
  4. where效率高于having，能写在where限定的条件就不要写在having中了
  5. 减少使用orderby，和业务沟通能不排序就不排序，或将排序放到程序端去做。Orderby、group by、distinct.这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
  6. 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1ooo行以内，否则SQL会很慢。

 7.优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。

EXPLAIN SELECT + FROM student LIMIT 2000000,10;

 8.优先考虑覆盖索引

8.1什么是覆盖索引？

理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。

理解方式二：非聚簇复合索引 的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。

简单说就是，索引列+主键包含SELECT到FROM之间查询的列。

 比如说上面过程二中增加limit过滤条件，这一句
EXPLAINNSELECT SQL_NO_CACHE * FROM Student QRDER BY age,claSSid LIMIT 10;

也有索引idx_age_classid_name 

我们现在C1C2C3三个字段,C1是主键,为C2建立一个普通的二级索引,覆盖索引叶子节点存得是数据，非覆盖索引叶子节点存的是索引字段和主键

C1字段 对应的页码,到下面叶子节点会保留他索引的字段,以及主键的字段值 , 147-覆盖索引的使用_哔哩哔哩_bilibili----6.07

 148,149,150好漫长不想看

 


http://www.kler.cn/a/594194.html

相关文章：

  • redis的key是如何找到对应存储的数据原理
  • 微软产品的专有名词和官方视频教程
  • labview与西门子1500plc进行S7通讯（仿真效果）
  • 在网页跑3D多人互动之渲染效能瓶颈
  • 连续出现的字符（信息学奥赛一本通-1148）
  • 从零开始使用C++完成串口助手(一)
  • mapbox基础，加载marker点位，测试大数据量加载性能问题
  • 分布式中间件：RabbitMQ死信队列和延迟队列
  • Android Opengl（九）FBO帧缓冲示例
  • UI设计中的对比与统一：构建和谐界面的原则
  • PyTorch模型转ONNX例子
  • Unity URP 实现场景和UI添加后处理
  • 知识库--Milvus
  • WordPress靶场攻略
  • php 要达到go的性能，应该如何优化php
  • 【蓝桥杯python研究生组备赛】005 数学与简单DP
  • 【CXX-Qt】2.1 构建系统
  • Python 编程题 第十一节：选择排序、插入排序、删除字符、目标移动、尾部的0
  • 如何通过 SQLyog 连接远程 MySQL 数据库？（附工具下载）
  • pdf文件分页按需查看