【宋红康 MySQL数据库 】【高级篇】【13】索引优化与查询优化

最新推荐文章于 2024-08-20 13:24:05 发布
最新推荐文章于 2024-08-20 13:24:05 发布
阅读量1.1k 收藏 6
点赞数 4
分类专栏： 数据库 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44018671/article/details/121762723
版权

持续学习&持续更新中…

学习态度：守破离

【宋红康 MySQL数据库 】【高级篇】【13】索引优化与查询优化

索引优化不必死记硬背，一切都是优化器基于成本的考虑。

哪些维度可以进行数据库调优

在这里插入图片描述

数据准备

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


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

# 执行存储过程
CALL proc_drop_index("dbname","tablename");

索引失效案例

在这里插入图片描述

全值匹配我最爱

在这里插入图片描述

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

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

最佳左前缀法则

在这里插入图片描述

在这里插入图片描述

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd' ;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.name = 'abcd';
 
EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE classid=4 AND student.age=30 AND student.name = 'abcd'; 

DROP INDEX idx_age ON student;
DROP INDEX idx_age_classid ON student;

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.name = 'abcd';

主键插入顺序

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

类型转换索引失效

计算、函数、类型转换(自动或手动)导致索引失效

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

#此语句比下一条要好！（能够使用上索引）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

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

CREATE INDEX idx_name ON student(NAME);

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;

EXPLAIN SELECT id, stuno, NAME FROM student WHERE SUBSTRING(NAME, 1,3)='abc';

类型转换导致索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123; 

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = '123';

在这里插入图片描述

范围条件右边的列索引失效

在这里插入图片描述

在这里插入图片描述

SHOW INDEX FROM student;

CALL proc_drop_index('atguigudb2','student');


CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);

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


# 解决方案

CREATE INDEX idx_age_name_cid ON student(age,NAME,classId);

不等于索引失效

不等于(!= 或者<>)索引失效

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

# 用到了索引
EXPLAIN SELECT SQL_NO_CACHE NAME FROM student WHERE student.name != 'abc' ;

is null可以使用索引，is not null无法使用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL; 

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

在这里插入图片描述

like以通配符%开头索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%'; 

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';


EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';

EXPLAIN SELECT id,age FROM student WHERE NAME LIKE '%abc';

在这里插入图片描述

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

在这里插入图片描述
在这里插入图片描述

SHOW INDEX FROM student;

CALL proc_drop_index('atguigudb2','student');

CREATE INDEX idx_age ON student(age);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

CREATE INDEX idx_cid ON student(classid);

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

在这里插入图片描述

练习及一般性建议

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

关联查询优化

数据准备

#分类
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)));

代码测试

# 情况1：左外连接

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

#添加索引
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;

# 情况2：内连接

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;

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

简单嵌套循环连接（Simple Nested-Loop Join）

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

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

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

块嵌套循环连接（Block Nested-Loop Join）

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

Hash JOIN

在这里插入图片描述

在这里插入图片描述

JOIN小结

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

子查询优化

在这里插入图片描述

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

#查询不为班长的学生信息

# 子查询
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;

在这里插入图片描述

排序优化

排序优化

在这里插入图片描述

代码测试

#删除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);
#不限制,索引失效
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;

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

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

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

EXPLAIN  SELECT * FROM student ORDER BY age LIMIT 10;

#过程四：order by时规则不一致, 索引失效 （顺序错，不索引；方向反，不索引）

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

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

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

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; 

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;

在这里插入图片描述

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

在这里插入图片描述

filesort算法：双路排序和单路排序

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

Group By优化

在这里插入图片描述

分页查询优化

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

优先考虑覆盖索引

什么是覆盖索引

在这里插入图片描述

#删除之前的索引
#举例1：
DROP INDEX idx_age_stuno ON student;

CREATE INDEX idx_age_name ON student (age,NAME);

EXPLAIN SELECT * FROM student WHERE age <> 20;


EXPLAIN SELECT age,NAME FROM student WHERE age <> 20;

#举例2：
EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';

EXPLAIN SELECT id,age FROM student WHERE NAME LIKE '%abc';

###
SELECT CRC32('hello')
FROM DUAL;

覆盖索引的利弊

在这里插入图片描述

索引下推（索引条件下推）

使用前后对比

在这里插入图片描述

ICP的开启/关闭

在这里插入图片描述

SET optimizer_switch = 'index_condition_pushdown=on';

代码测试

#举例1：
USE atguigudb1;

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

#举例2：
CREATE TABLE `people` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `zipcode` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
  `firstname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
  `lastname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
  `address` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `zip_last_first` (`zipcode`,`lastname`,`firstname`)
) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;


INSERT INTO `people` VALUES 
('1', '000001', '三', '张', '北京市'), 
('2', '000002', '四', '李', '南京市'), 
('3', '000003', '五', '王', '上海市'), 
('4', '000001', '六', '赵', '天津市');


EXPLAIN SELECT * FROM people
WHERE zipcode='000001'
AND lastname LIKE '%张%'
AND address LIKE '%北京市%';

EXPLAIN SELECT * FROM people
WHERE zipcode='000001'
AND lastname LIKE '张%'
AND firstname LIKE '三%';

开启和关闭ICP的性能对比

#创建存储过程，向people表中添加1000000条数据，测试ICP开启和关闭状态下的性能
DELIMITER //
CREATE PROCEDURE  insert_people( max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO people ( zipcode,firstname,lastname,address ) VALUES ('000001', '六', '赵', '天津市');  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT; 
END //

DELIMITER ;

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

ICP的使用条件

不需要回表的话，ICP就用不上了。

在这里插入图片描述

其他查询优化策略

EXISTS和IN的区分

在这里插入图片描述

在这里插入图片描述

COUNT(*)与COUNT(具体字段)效率

在这里插入图片描述

关于SELECT(*)

在这里插入图片描述

LIMIT 1对优化的影响

在这里插入图片描述

多使用COMMIT

在这里插入图片描述

淘宝数据库主键是如何设计的？

在这里插入图片描述

自增ID的问题

在这里插入图片描述

在这里插入图片描述

业务字段作主键

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

淘宝的主键设计

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

推荐的主键设计

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

SELECT UUID() FROM DUAL;

SET @uuid = UUID();

SELECT @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

参考

尚硅谷宋红康: MySQL数据库（入门到高级，菜鸟到大牛）.

本文完，感谢您的关注支持！

lpruoyu
关注
  • 4
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 4
    评论
专栏目录
数据库 MySQL 学习笔记高级篇.md
09-01
数据库 MySQL 学习笔记高级篇.md
mysql高级参考课件（供实操参考）.zip
01-18
5. **索引与优化**： - 理解B-Tree、Hash、R-Tree等不同类型的索引，以及它们的优缺点。 - 如何创建和管理索引，包括唯一索引、主键、外键和全文索引。 - 查询优化：使用EXPLAIN分析查询执行计划，避免全表扫描，...
MySQL高级特性_06_索引的数据结构_尚硅谷_宋红康
Java全栈开发者
03-12 771
索引（index)是帮助MySQL高效获取数据的数据结构。索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构“，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的技术上实现高级查找算法。索引是在存储引擎中实现的因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引。总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。
【宋红康 MySQL数据库 】【高级篇】【10】索引的创建与删除_MySQL8.0的索引新特性
Keep Reading Keep Writing Keep Thinking
08-16 1122
索引的创建与删除_MySQL8.0的索引新特性
【宋红康 MySQL数据库 】【高级篇】【14】数据库的设计规范
Keep Reading Keep Writing Keep Thinking
09-01 1461
数据库的设计规范
尚硅谷-宋红康-MySQL高级性能篇
ctflq的博客
01-30 1342
mysql高级性能优化
【宋红康 MySQL数据库 】【高级篇】【15】数据库其它调优策略
Keep Reading Keep Writing Keep Thinking
09-02 998
数据库其它调优策略
尚硅谷宋红康MySQL笔记 10-13
qq_45418837的博客
08-21 743
尚硅谷宋红康MySQL笔记 10~18
【宋红康 MySQL数据库 】【高级篇】【09】InnoDB的数据存储结构
Keep Reading Keep Writing Keep Thinking
07-16 1560
InnoDB的数据存储结构
通俗易懂的MySQL高级教程（含配套资料）
06-14
本教程为授权出品 MySQL是目前流行的关系型...索引的优化策略，了解innodb和myisam存储引擎，熟悉MySQL锁机制，能熟练配置MySQL主从复制，熟练掌握explain、show profile、慢查询日志等日常SQL诊断和性能分析策略。
康师傅mysql基础pdf
01-28
10. **优化**: MySQL的性能可以通过优化查询、使用合适的数据类型、合理设计索引、调整配置参数等方式提升。 康师傅mysql基础pdf应该详细介绍了这些内容，并可能还包括了实践案例和常见问题解答。通过学习这份资料...
MySQL从入门到高级系列视频.zip
02-21
目录网盘文件永久链接 1.Mysql数据库入门及安装.mp4 ...14.MySQL数据库索引及慢查询讲解.mp4 15.MySQL数据库高效优化解析.mp4 16.构建MySQL+keepalived高可用自动切换.mp4 17.构建MySQL+DRBD+Keepalived高可用集群.mp4
【达梦数据库】-导入导出过程中get DDL复现过程
weixin_47686079的博客
08-20 183
在处理问题的过程中，客户反馈在达梦数据库中导入导出分区表，get 表的DDL，不会出现表的所有分区信息，ORACLE数据库却会出现所有分区表，事实真的如此吗？
linux安装达梦数据库
HAN_789的博客
08-19 202
如果无法创建dmdba 文件夹，需要进入root用户下 在管理员用户下，这种安装方式一般只用于验证，开发，测试，生产最好还是安装传统的方式安装。在 home/dmdba/dmdbms/bin 目录下执行。（1）将文件上传到 /home/dmdba/ 目录下。（2）加压：tar -xzvf dmdb.tar.gz。进入到root就可以创建dmdba 文件夹了。1、后台启动，一般建议用后台启动。
分布式事务：基本概念
最新发布
玉汝于成
08-20 198
事务可以看做事一次大的活动，它由不同的小的活动组成，这些活动要么全部成功，要么全部失败。
网上商品订单转手系统bootpf
weixin_43213064的博客
08-18 495
【代码】springboot网上商品订单转手系统bootpf
旅游网站
weixin_43213064的博客
08-16 1143
【代码】springboot旅游网站
pikachu综合靶场漏洞讲解
YhMjQx的博客
08-20 864
本篇文章主要讲解pikachu靶场中大部分题目，同样的，其余部分题目我将会放在专题中讲解，作为新知识的练习题
宋红康 mysql数据库
08-15
您好！对于MySQL数据库，我可以提供一些基本的信息。MySQL是一个开源的关系型数据库管理系统，广泛应用于各种网站和应用程序中。它具有高性能、稳定可靠以及丰富的功能。 MySQL支持多种操作系统，并提供了多种编程语言的接口，如Java、Python、PHP等。它使用标准的SQL语言进行数据管理和查询，可以处理大规模的数据集。 MySQL的特点包括： 1. 可扩展性：可以在单个服务器上运行多个数据库实例，支持主从复制和分布式数据库架构。 2. 安全性：提供了各种安全特性，如用户认证、数据加密和访问控制等。 3. 高性能：通过索引、查询优化和缓存等技术，提升数据库的查询和写入性能。 4. 数据完整性：支持事务处理和数据约束，保证数据的一致性和完整性。 5. 可靠性：提供了数据备份、恢复和故障转移等机制，确保数据的可靠性和可用性。 除了基本的数据库操作外，MySQL还支持存储过程、触发器、视图等高级功能，可以实现复杂的业务逻辑和数据处理需求。 希望这些信息对您有所帮助！如果您有更具体的问题，可以继续提问。
评论 4
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值