九、MySQL高级—索引优化分析（2.3）

Daniel521-Spark

于 2024-09-07 19:07:05 发布

阅读量1k

点赞数 5

分类专栏： # 计算机（Java高级）高级阶段文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_42171159/article/details/141978820

版权

计算机（Java高级）高级阶段专栏收录该内容

10 篇文章 0 订阅

订阅专栏

🌻🌻 目录

一、查询优化
1.1 批量数据脚本
1.1.1 往表里插入50W数据
1.1.2 批量删除某个表上的所有索引

1.2 单表使用索引及常见索引失效
1.2.1 案例(索引失效)
1.2.2 一般性建议

1.3 关联查询优化
1.3.1 建表SQL
1.3.2 案例
1.3.3 建议

1.4 子查询优化
1.5 排序分组优化
1.6 最后使用索引的手段：覆盖索引

二、查询优化综合

文章大纲 👇👇

在这里插入图片描述

一、查询优化

1.1 批量数据脚本

1.1.1 往表里插入50W数据

建表

CREATE TABLE `dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 ceo INT NULL ,
 PRIMARY KEY (`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
 
CREATE TABLE `emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `empno` INT NOT NULL ,
 `name` VARCHAR(20) DEFAULT NULL,
 `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

在这里插入图片描述

设置参数log_bin_trust_function_creators

创建函数，假如报错：This function has none of DETERMINISTIC…
由于开启过慢查询日志，因为我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

SET GLOBAL log_bin_trust_function_creators=1;

在这里插入图片描述

这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：

windows下my.ini[mysqld]加上log_bin_trust_function_creators=1 

linux下 /etc/my.cnf下my.cnf[mysqld]加上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 $$

#假如要删除
#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$$

#假如要删除
#drop function rand_num;

在这里插入图片描述

创建存储过程

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

在这里插入图片描述

DELIMITER $$
CREATE PROCEDURE  insert_emp(  START INT ,  max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
#set autocommit =0 把autocommit设置成0  
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6)   , rand_num(30,50),rand_num(1,10000));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END$$

#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;

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

在这里插入图片描述

#执行存储过程，往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE `insert_dept`(  max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END$$

#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;

在这里插入图片描述

调用存储过程

dept
emp

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

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

select count(*) from dept;
select count(*) from emp;

在这里插入图片描述

1.1.2 批量删除某个表上的所有索引

1、查询索引名
2、取出索引名
3、怎么把字符串转换成sql

如何删除索引

① 查出该表有哪些索引，索引名–>集合

SHOW INDEX FROM t_emp;

SELECT index_name  FROM information_schema.STATISTICS WHERE table_name='t_emp' 
AND table_schema='mydb'
AND index_name <>'PRIMARY' AND seq_in_index = 1

在这里插入图片描述

② 存储过程

在这里插入图片描述

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$$

在这里插入图片描述

③ 执行存储过程

在这里插入图片描述

SELECT index_name  FROM information_schema.STATISTICS WHERE table_name='t_emp' 
AND table_schema='mydb'
AND index_name <>'PRIMARY' AND seq_in_index = 1

#CALL proc_drop_index("dbname","tablename"); #dbname 库名tablename 表名
CALL proc_drop_index("mydb","t_emp");

1.2 单表使用索引及常见索引失效

1.2.1 案例(索引失效)

全值匹配我最爱

(1) 需求

#系统中经常出现的sql语句如下：
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 
 
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND deptid=4

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND deptid=4 AND emp.name = 'abcd'  
#索引应该如何建立 ？

(2) 建立索引
① 没有建索引之前

在这里插入图片描述

② 建索引之后

在这里插入图片描述

CALL proc_drop_index("mydb","emp"); 
CALL proc_drop_index("mydb","dept"); 

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30

CREATE INDEX idx_age ON emp(age);

在这里插入图片描述


CALL proc_drop_index("mydb","emp"); 
CALL proc_drop_index("mydb","dept"); 

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30

CREATE INDEX idx_age ON emp(age); 

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND deptid=4

CREATE INDEX idx_age_deptid ON emp(age,deptid);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND deptid=4 AND emp.name = 'abcd'

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

在这里插入图片描述

最佳左前缀法则

(1) 需求

如果系统经常出现的sql如下：
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30   AND emp.name = 'abcd'   
或者
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1   AND emp.name = 'abcd'   
那原来的idx_age_deptid_name 还能否正常使用？

(2) 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);  

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

在这里插入图片描述

虽然可以正常使用，但是只有部分被使用到了。

在这里插入图片描述

完全没有使用上索引。

结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

在这里插入图片描述

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描，如下：LEFT(emp.name,3) = 'abc'

需求

#这两条sql哪种写法更好
EXPLAIN  SELECT SQL_NO_CACHE * FROM emp WHERE   emp.name  LIKE 'abc%' 
 
EXPLAIN   SELECT SQL_NO_CACHE * FROM emp WHERE   LEFT(emp.name,3)  = 'abc'

第一种

在这里插入图片描述

第二种

在这里插入图片描述

#这两条sql哪种写法更好
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name LIKE 'abc%' 
 
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(emp.name,3) = 'abc'

CREATE INDEX idx_name ON emp(NAME);

CALL proc_drop_index("mydb","emp"); 
CALL proc_drop_index("mydb","dept");

存储引擎不能使用索引中范围条件右边的列

需求

#如果系统经常出现的sql如下：
EXPLAIN SELECT  SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc' ; 

#那么索引 idx_age_deptid_name这个索引还能正常使用么？

在这里插入图片描述

#如果系统经常出现的sql如下：
EXPLAIN SELECT  SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc'; 

#那么索引 idx_age_deptid_name这个索引还能正常使用么？

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

CREATE INDEX idx_age_name_deptid ON emp(age,NAME,deptid);


CALL proc_drop_index("mydb","emp"); 
CALL proc_drop_index("mydb","dept");

在这里插入图片描述

如果这种sql 出现较多
应该建立：

create index idx_age_name_deptid on emp(age,name,deptid)

效果

在这里插入图片描述

mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

在这里插入图片描述

CREATE INDEX idx_name ON emp(NAME)
  
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> 'abc' 

CALL proc_drop_index("mydb","emp"); 
CALL proc_drop_index("mydb","dept");

在这里插入图片描述

is not null 也无法使用索引,但是is null是可以使用索引的

在这里插入图片描述

#下列哪个sql语句可以用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NULL
  
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NOT NULL

CREATE INDEX idx_age ON emp(age)

CALL proc_drop_index("mydb","emp"); 
CALL proc_drop_index("mydb","dept");

在这里插入图片描述

like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作
字符串不加单引号索引失效

在这里插入图片描述

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name LIKE '%ab%';

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 123;  

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name LIKE 'ab%';

CREATE INDEX idx_age_name ON emp(age,NAME)

CALL proc_drop_index("mydb","emp"); 
CALL proc_drop_index("mydb","dept");

在这里插入图片描述

小总结

假设index(a,b,c)

Where语句	索引是否被使用
where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a，b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N
where a = 3 and c = 5	使用到a，但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b， c不能用在范围之后，b断了
where a is null and b is not null	is null 支持索引但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用
where a <> 3	不能使用索引
where abs(a) =3	不能使用索引
where a = 3 and b like ‘kk%’ and c = 4	Y,使用到a,b,c
where a = 3 and b like ‘%kk’ and c = 4	Y,只用到a
where a = 3 and b like ‘%kk%’ and c = 4	Y,只用到a
where a = 3 and b like ‘k%kk%’ and c = 4	Y,使用到a,b,c

1.2.2 一般性建议

对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面
书写sql语句时，尽量避免造成索引失效的情况

1.3 关联查询优化

1.3.1 建表SQL

在这里插入图片描述

CREATE TABLE IF NOT EXISTS `class` (
`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`)
);
 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(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)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

1.3.2 案例

#下面开始explain分析
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;
#结论:type 有All
 
#添加索引优化
ALTER TABLE `book` ADD INDEX Y ( `card`);
 
换成INNER JOIN
 
DELETE FROM class WHERE id<5;
 
# 第2次explain
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
#可以看到第二行的 type 变为了 ref,rows 也变成了优化比较明显。
#这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,
#所以右边是我们的关键点,一定需要建立索引。
 
# 删除旧索引 + 新建 + 第3次explain
DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX X (card);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

① 没有使用索引

在这里插入图片描述

② 使用了索引

在这里插入图片描述

③ book 使用了索引 class没有使用索引

在这里插入图片描述

③ class 使用了索引 book 没有使用索引

在这里插入图片描述

1.3.3 建议

1、保证被驱动表的join字段已经被索引
2、left join 时，选择小表作为驱动表，大表作为被驱动表。
3、inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
4、子查询尽量不要放在被驱动表，有可能使用不到索引。
5、能够直接多表关联的尽量直接关联，不用子查询。

1.4 子查询优化

尽量不要使用 not in 或者 not exists

#取所有不为掌门人的员工，按年龄分组 ，每个年龄段多少人
SELECT SQL_NO_CACHE age,COUNT(*) FROM emp a 
	WHERE id NOT IN(SELECT ceo FROM dept b2 WHERE ceo IS NOT NULL)
	GROUP BY age 
	HAVING COUNT(*)<10000

在这里插入图片描述

用left outer join on xxx is null 替代

EXPLAIN SELECT SQL_NO_CACHE age,COUNT(*) FROM emp a 
	LEFT OUTER JOIN dept b ON a.id =b.ceo
	WHERE b.ceo IS NULL
	GROUP BY age 
	HAVING COUNT(*)<10000

在这里插入图片描述

1.5 排序分组优化

case

CREATE INDEX idx_age_deptid_name ON emp (age,deptid,NAME)
 
#以下是否能使用到索引，能否去掉using filesort
 
1、EXPLAIN  SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid; 
 
2、EXPLAIN  SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid LIMIT 10; 
 
#无过滤 不索引

在这里插入图片描述

3、EXPLAIN  SELECT * FROM emp WHERE age=45 ORDER BY deptid;
 
4、EXPLAIN  SELECT * FROM emp WHERE age=45 ORDER BY   deptid,NAME; 
 
5、EXPLAIN  SELECT * FROM emp WHERE age=45 ORDER BY  deptid,empno;
 
6、EXPLAIN  SELECT * FROM emp WHERE age=45 ORDER BY  NAME,deptid;
 
7、 EXPLAIN SELECT * FROM emp WHERE deptid=45 ORDER BY age;
 
#顺序错，必排序
 
8、EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY  deptid DESC, NAME DESC ;
 
9、EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY  deptid ASC, NAME DESC ;
 
#方向反 必排序

ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序

索引的选择

执行案例前先清除emp上的索引，只留主键
索引的选择

CALL proc_drop_index('mydb','emp');
CALL proc_drop_index('mydb','dept');

#查询 年龄为30岁的，且员工编号小于101000的用户，按用户名称排序
 
SELECT SQL_NO_CACHE * FROM emp WHERE age = 30 AND empno <101000 ORDER BY NAME;

在这里插入图片描述

#结论：很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
 
#开始优化：

思路:尽量让WHERE的过滤条件和排序使用上索引
但是一共两个字段(deptno,empno)上有过滤条件，一个字段(ename)有索引

1、我们建一个三个字段的组合索引可否？

在这里插入图片描述

CREATE INDEX idx_age_empno_name ON emp(age,empno,NAME);
#我们发现using filesort 依然存在，所以name 并没有用到索引。
#原因是因为empno是一个范围过滤，所以索引后面的字段不会再使用索引了。
 
#所以我们建一个3值索引是没有意义的 那么我们先删掉这个索引，
DROP INDEX idx_age_empno_name ON emp
 
#为了去掉filesort我们可以把索引建成
CREATE INDEX idx_age_name ON emp(age,NAME);

在这里插入图片描述

也就是说empno 和name这个两个字段我只能二选其一。
这样我们优化掉了 using filesort。
执行一下sql

在这里插入图片描述

速度果然提高了4倍。…

但是,如果我们选择那个范围过滤，而放弃排序上的索引呢,建立

DROP INDEX idx_age_name ON emp
CREATE INDEX idx_age_empno ON emp(age,empno);

在这里插入图片描述

果然出现了filesort，而且type还是range光看字面其实并不美好。
我们来执行以下sql

在这里插入图片描述

结果竟然有 filesort的 sql 运行速度，超过了已经优化掉 filesort的 sql ，而且快了好多倍。何故？

原因是所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。相对的 empno<101000 这个条件如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。

结论：当范围条件和group by 或者 order by 的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

① 双路排序
MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，
读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

② 取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

③ 单路排序
从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。

④ 结论及引申出的问题
由于单路是后出的，总体而言好过双路
但是用单路有问题
在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。
本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

⑤ 优化策略
增大sort_buffer_size参数的设置
增大max_length_for_sort_data参数的设置
减少select 后面的查询的字段。
Why：
提高ORDER BY的速度：

ORDER BY时SELECT * 是一个大忌只QUERY需要的字段，这点非常重要。在这里的影响是：

1.1 当QUERY的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。
1.2 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。

尝试提高 sort_buffer_size
不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M之间调整
尝试提高 max_length_for_sort_data
提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率.1024-8192之间调整

GROUP BY关键字优化

group by 使用索引的原则几乎跟order by一致，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。

1.6 最后使用索引的手段：覆盖索引

什么是覆盖索引？
简单说就是，select 到 from 之间查询的列 <=使用的索引列+主键

在这里插入图片描述

explain select * from emp where name like '%abc';

在这里插入图片描述

使用覆盖索引后

在这里插入图片描述

二、查询优化综合

子查询优化强训:

CALL proc_drop_index('mydb','t_emp');
CALL proc_drop_index('mydb','t_dept');

#1、列出自己的掌门比自己年龄小的人员

SELECT a.`name`,a.`age`,c.`name` ceoname,c.`age` ceoage FROM 
t_emp a 
LEFT JOIN t_dept b ON a.`deptId`= b.`id` 
LEFT JOIN t_emp c ON b.`CEO`= c.`id`
WHERE c.`age`< a.`age`

#优化  
EXPLAIN SELECT SQL_NO_CACHE a.`name`,a.`age`,c.`name` ceoname,c.`age` ceoage FROM 
emp a 
LEFT JOIN dept b ON a.`deptId`= b.`id` 
LEFT JOIN emp c ON b.`CEO`= c.`id`
WHERE c.`age`<a.`age`
    
#2、列出所有年龄低于自己门派平均年龄的人员

SELECT c.`name`,c.`age`,aa.age FROM t_emp c INNER JOIN
(
    SELECT a.`deptId`,AVG(a.`age`)age FROM t_emp a
    WHERE a.`deptId` IS NOT NULL
    GROUP BY a.`deptId`
 )aa ON c.`deptId`=aa.deptid 
 WHERE c.`age`< aa.age

#优化 

EXPLAIN SELECT SQL_NO_CACHE c.`name`,c.`age`,aa.age FROM emp c INNER JOIN
(
    SELECT a.`deptId`,AVG(a.`age`)age FROM emp a
    WHERE a.`deptId` IS NOT NULL
    GROUP BY a.`deptId`
 )aa ON c.`deptId`=aa.deptid 
 WHERE c.`age`< aa.age
 
CREATE INDEX idx_deptid ON emp(deptid)
 
CREATE INDEX idx_deptid_age ON emp(deptid,age)

#3、列出至少有2个年龄大于40岁的成员的门派

SELECT b.`deptName`,COUNT(*) FROM t_emp a 
INNER JOIN t_dept b ON b.`id` = a.`deptId`
WHERE a.age >40
GROUP BY b.`deptName`,b.`id` 
HAVING COUNT(*)>=2
 
 #优化 
 EXPLAIN SELECT SQL_NO_CACHE b.`deptName`,COUNT(*) FROM  
dept b STRAIGHT_JOIN emp a  ON b.`id` = a.`deptId`
 WHERE a.age >40
 GROUP BY b.`deptName`,b.`id` 
 HAVING COUNT(*)>=2
 
CREATE INDEX  idx_deptid_age ON emp(deptid,age)
CREATE INDEX  idx_deptname ON dept(deptname)

#STRAIGHT_JOIN 强制确定驱动表和被驱动表 1、概念非常明确 2、对数据量的比例非常明确

#4、至少有2位非掌门人成员的门派
SELECT * FROM t_emp a WHERE a.id NOT IN
{
 SELECT b.`ceo` FROM t_dept b WHERE b.`ceo`IS NOT NULL
}  

NOT IN -->LEFT JOIN xxx ON xx WHERE xx IS NULL

SELECT c.deptname,  c.id,COUNT(*) FROM t_emp a 
INNER JOIN t_dept c ON a.`deptId` =c.`id`
LEFT JOIN t_dept b ON a.`id`=b.`ceo`
WHERE b.`id` IS NULL
GROUP BY c.`id` ,c.deptname
HAVING COUNT(*)>=2

#优化 
EXPLAIN SELECT SQL_NO_CACHE c.deptname,  c.id,COUNT(*) 
FROM  dept c STRAIGHT_JOIN emp a 
  ON a.`deptId` =c.`id`
LEFT JOIN dept b ON a.`id`=b.`ceo`
WHERE b.`id` IS NULL
GROUP BY c.deptname,c.`id` 
HAVING COUNT(*)>=2

CREATE INDEX idx_ceo_deptnam ON dept(ceo,deptname)
CREATE INDEX idx_deptnam ON dept(deptname)
CREATE INDEX idx_deptid ON emp(deptid)

SELECT b.`id`,b.`deptName` ,COUNT(*) FROM t_emp a INNER JOIN  t_dept b ON a.`deptId`= b.`id`
GROUP BY b.`deptName`,b.`id`

SELECT b.`id`,b.`deptName`, COUNT(*) FROM emp a INNER JOIN  dept b ON a.`deptId`= b.`id`
GROUP BY b.`deptName`,b.`id`

UPDATE t_dept SET deptname='明教' WHERE id=5

#5、列出全部人员，并增加一列备注“是否为掌门”，如果是掌门人显示是，不是掌门人显示否
#CASE WHEN
#IF
 
SELECT  a.`name`, CASE WHEN b.`id` IS NULL THEN '否' ELSE '是' END '是否为掌门'
FROM  t_emp a 
LEFT JOIN t_dept b ON a.`id`=b.`ceo`  
 

#6、列出全部门派，并增加一列备注“老鸟or菜鸟”，若门派的平均值年龄>50显示“老鸟”，否则显示“菜鸟”

SELECT b.`deptName`,
IF (AVG(a.age)>50,'老鸟','菜鸟')'老鸟or菜鸟'
 FROM t_emp a
INNER JOIN t_dept b ON a.`deptId`= b.`id`
 GROUP BY b.`id` ,b.`deptName`

#7、显示每个门派年龄最大的人

SELECT NAME,age FROM t_emp a
INNER JOIN
(
SELECT deptid,MAX(age) maxage
FROM t_emp
WHERE deptid IS NOT NULL
GROUP BY deptid
) aa ON a.`age`= aa.maxage AND a.`deptId`=aa.deptid

#优化 
EXPLAIN SELECT SQL_NO_CACHE NAME,age FROM emp a
INNER JOIN
(
SELECT deptid,MAX(age) maxage
FROM emp
WHERE deptid IS NOT NULL
GROUP BY deptid
) aa ON a.`age`= aa.maxage AND a.`deptId`=aa.deptid

CREATE INDEX idx_deptid_age ON emp(deptid,age)

#错例
SELECT b.`deptName`,a.`name`,MAX(a.`age`)FROM t_dept b
   LEFT JOIN t_emp a ON b.`id`=a.`deptId`
   WHERE a.name IS NOT NULL
   GROUP BY b.`deptName`


UPDATE t_emp SET age=100 WHERE id =2

#8、显示每个门派年龄第二大的人（涉及sql编程）
SET @rank=0;   #@rank 自定义变量
SET @last_deptid=0;
SELECT a.deptid,a.name,a.age
 FROM(    
    SELECT t.*,
     IF(@last_deptid=deptid,@rank:=@rank+1,@rank:=1) AS rk,
     @last_deptid:=deptid AS last_deptid
    FROM t_emp t
    ORDER BY deptid,age DESC
    
 )a WHERE a.rk=2;

#分组排序
SET @rank=0;
SET @last_deptid=0;
SELECT * FROM
(
 SELECT t.*,
     IF(@last_deptid=deptid,@rank:=@rank+1,@rank:=1) AS rk,
     @last_deptid:=deptid AS last_deptid
    FROM t_emp t
    ORDER BY deptid,age DESC
) a WHERE a.rk <=1


#oracle rank() over()

UPDATE t_emp SET age=100 WHERE id =1

SET @rank=0;
SET @last_deptid=0;
SET @last_age=0;

 SELECT t.*,
     IF(@last_deptid=deptid,
     IF(@last_age = age,@rank,@rank:=@rank+1)
     ,@rank:=1) AS rk,
     @last_deptid:=deptid AS last_deptid,
     @last_age :=age AS last_age
    FROM t_emp t
    ORDER BY deptid,age DESC