0


子查询优化与排序优化

子查询优化

子查询介绍

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

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

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

在MySQL中,可以用连接(join)查询代替子查询,连接查询不需要建立临时表,其速度比子查询要快,如果查询中使用索引,则性能更好。

子查询优化技巧get

  • 保证被驱动表的join字段已经创建了索引
  • 需要join的字段,数据类型保持绝对一致
  • LEFT JOIN时,选择小表作为驱动表,大表作为被驱动表,减少外层循环的次数
  • INNER JOIN时,MySQL会自动将小结果集的表选为驱动表(前提是选择相信MySQL优化策略)
  • 能够直接多表关联的尽量直接关联,不用子查询(减少查询的趟数),或者将子查询sql拆开结合程序多次查询
  • 尽量不要使用not in/not exists/not null,用left join xxx where xx is null替代,测试样例代码如下(用的是上一篇文章创建的数据表)
#创建班级表中班长的索引
CREATE INDEX idx_monitor ON class(monitor);

#查询不为班长的学生信息(子查询)
EXPLAIN SELECT SQL_NO_CACHE a.* 
FROM student a 
WHERE  a.stuno  NOT  IN (
            SELECT monitor FROM class b 
            WHERE monitor IS NOT NULL) 

#查询不为班长的学生信息(join查询)
EXPLAIN SELECT SQL_NO_CACHE a.*
FROM  student a LEFT OUTER JOIN class b 
ON a.stuno =b.monitor
WHERE b.monitor IS NULL;

排序优化

问题:在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表中的非主键索引
CALL proc_drop_index('atguigudb2','student');
CALL proc_drop_index('atguigudb2','class');

SHOW INDEX FROM student;
SHOW INDEX FROM class;

过程一:order by时不limit,索引失效

#创建索引  
CREATE  INDEX idx_age_classid_name ON student (age,classid,NAME);

#不限制,索引失效(由于查询的数据未进行限制,且需要进行回表操作,为了节省开销,优化器会先将所有数据加载到内存中进行Filesort,然后再进行order by,导致索引失效)
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid; 

#这里查询的字段(对比上述查询所有字段而言)都存在于二级索引的叶子节点中,无需回表,因此使用上索引了(即直接order by)
EXPLAIN  SELECT SQL_NO_CACHE age,classid,NAME,id FROM student ORDER BY age,classid; 

#增加limit过滤条件,使用上索引了(由于数据量有限,因此先使用索引进行order by排序再进行回表操作更节省开销)。
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;  

技巧get:在已知查询数据量的情况下加上limit条件可以提高查询效率

过程二: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;

解析:很显然根据最左前缀原则前两条sql语句会出现索引失效,目前存在两个联合索引(分别是:idx_age_classid_name、idx_age_classid_stuno ),第三条语句很显然会选择匹配度高的联合索引idx_age_classid_stuno ,而后两条语句则选择了key_len为73的联合索引idx_age_classid_name来提升查询效率(一定条件下索引长度长的索引查询效率更高,可以理解为唯一性越高)

过程三: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;

执行结果:只有最后一条sql语句用上索引了 ,可见联合索引上的字段排序方式统一,也会使用上索引。要么都降序;要么都升序。

过程四:无过滤,失索引

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;

理解:根上还是优化器有一套自己计算开销的方式,具体问题具体优化,我们只能从一些Explain参数做一个大概的规律总结,那么这里根据执行结果我的理解是当选择在where或者order by上使用索引时,如果数据量大,就优先选择在where过滤条件上使用索引(参照前两条sql语句执行结果),当limit限制了数据量的情况则还是会优先选择在order by上使用索引(参照最后一条sql语句执行结果)

排序优化小结

INDEX a_b_c(a,b,c)

order by 能使用索引的情况(最左前缀原则)
- ORDER BY a
- ORDER BY a, b
- ORDER BY a,b,c
- ORDER BY a DESC,HY DESC,c DESC

如果WHERE使用索引的最左前缀定义为常量,则order by能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c

不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /*排序不一致*/
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in(…)ORDER BY b,c /*对于排序来说,多个相等条件也是范围查询*/

测试filesort和index排序

  • 测试前的准备
CALL proc_drop_index('atguigudb2','student');

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

这里清除了之前建立的所有索引,没有索引的条件下执行select查询显然选择了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 ;

解析:这里建立age、name字段的索引,就去掉了FileSort排序方式,key_len = 5 说明只用上了age这个字段的索引就进行回表了,执行速度还没有FileSort的快,数据量大的情况下越明显,原因是因为所有的排序都是金国过滤后执行,但是age字段过滤数据量并不是很大因此性能提升比较有限,相对于stuno<101000这个条件而言的话,索引放在stuno字段上性能开销更小。

方案二

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

解析:这里创建的索引idx_age_stuno_name 使得性能提升更加地明显,而新建索引idx_age_stuno 时,速度则更快,说明冗余的索引段也会影响查询性能。

结论:

  1. 两个索引同时存在,MySQL自动选择最优的方案。对于这个例子,MySQL选择idx_age_stuno_name ,但是随着数据量的变化,选择的索引也会随之变化。
  2. 当【范围条件】和【group by或者order by】的字段出现二选一时,优先观察条件字段的过滤数量,如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段上。反之,亦然。

FileSort算法:双路排序和单路排序

  • 当排序的字段若不在索引列上,则filesort会有两种算法:双路排序算法和单路排序算法
  • 双路排序:即先对order by或者group by的字段排好序后,再根据该字段的顺序读取对应的query字段数据;若从磁盘取排序字段,会先在buffer进行排序,再从磁盘取其他字段,即会对磁盘进行两次扫描,极其耗时
  • 单路排序:从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据,并且把随机io变成了顺序io,但是它会使用更多的空间,因为它把每一行需要的列都保存在内存中
  • 单路排序存在的问题:在sort_buffer中,单路比多路要占用更多的空间,因为单路是把所有字段取出,所以有可能取出的数据的总大小超出了sort_buffer的容量,导致每次只能取sort_buffer容量大小的数据进行排序(创建tmp文件,多路合并),排完再取sort_buffer容量大小,再排……从而多次I/O,单路本来想省一次I/O操作,反而导致大量的I/O操作,反而得不偿失。

order by优化策略

  1. 尝试提高Sort_buffer_size,两种算法均可提升效率(InnoDB默认大小是1M,该参数针对每个进程connection的1M-8M之间的调整)
  2. 尝试提高max_length_for_sort_data,通常返回的列的总长度大于max_length_for_sort_data,使用双路算法,否则使用单路算法,也是1M-8M之间调整
  3. order by 时select * 是一个大忌,最好只query需要的字段
  4. 减少使用order by,和业务沟通能不排序就不排序,或将排序放到程序端去做。order by、group by、distinct这些语句较为耗费CPU,而数据库CPU资源极其宝贵
  5. 包含了order by、group by、distinct这些查询的语句,where条件过滤出来的结果集请保持再1000行以内,否则SQL会很慢

附:

group by优化:

  • group by 使用索引的原则几乎跟order by 一致,group by即使没有过滤条件用到索引,也可以直接使用索引
  • group by 先排序再分组,遵照索引建的最佳左前缀法则
  • 当无法使用索引列,增大max_length_for_sort_data和sort_buffer_size参数设置
  • where效率高于having,能写where限定条件尽量不要写having中

分页查询优化:

#场景描述:排序前2000010记录,仅仅返回2000000-2000010的记录,其他记录丢弃(代价极大)
Explain select * from student limit 2000000,10;
  • 优化思路一:在索引上完成排序分页操作,最后根据主键关联回表查询所需要的其他列内容 Explain select * from student t,(select id from student order by id limit 20000000,10) a where t.id = a.id;
  • 优化思路二:可以把limit查询准换成某个位置的查询 Explain select * from student where id>2000000 limit 10;
  • 宗旨就是尽量往聚簇索引上靠
标签: mysql 数据库 sql

本文转载自: https://blog.csdn.net/m0_69766497/article/details/127526128
版权归原作者 哆啦咪嗦 所有, 如有侵权,请联系我们删除。

“子查询优化与排序优化”的评论:

还没有评论