当前位置:   article > 正文

Mysql高级篇学习总结14:子查询优化、排序优化、GROUP BY优化、分页查询优化_mysql如何优化子查询

mysql如何优化子查询

1、子查询优化

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

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

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

尽量不要使用NOT IN 或者NOT EXISTS,用LEFT JOIN TABLEX ON FIELDY WHERE FIELDY IS NULL进行替代

2、排序优化

2.1 排序优化

在mysql中,支持2种排序方式,分别是FileSortIndex排序:

  • FileSort排序一般在内存中进行排序展示,占用CPU较多。如果待排结果较大,会产生临时文件I/O到磁盘进行排序的情况,效率较低。
  • Index排序中,索引可以保证数据的有序性,不需要再进行排序,效率更高

优化建议:

  1. 可以在WHERE子句和ORDER BY子句中使用索引,目的是在WHERE子句中避免全表扫描,在ORDER BY子句中避免使用FileSort排序。当然,某些情况下全表扫描,或者FileSort排序不一定比索引慢。
  2. 尽量使用INDEX 完成ORDER BY排序。如果WHERE和ORDER BY后面是相同的列就使用但索引列,如果不同就使用联合索引。
  3. 无法使用idex时,需要对FileSort方式进行调优。

以联合索引IDEX a_b_c(a,b,c)进行举例说明:
1)order by 能使用最左前缀

ORDER BY a
ORDER BY a,b
ORDER BY a,b,c
ORDER BY a DESC, b DESC, c DESC
  • 1
  • 2
  • 3
  • 4

2)如果WHERE使用索引的最左前缀定义常量,则order by能使用索引

WHERE a = const ORDER BY b, c
WHERE a = const AND b = const ORDER BY c
WHERE a = const AND b > const ORDER BY b, c
  • 1
  • 2
  • 3

3)不能使用索引进行排序的情况

ORDER BY a ASC, b DESC     -- 排序不一致
WHERE g = const ORDER BY b, c     -- b字段前面丢失最左前缀索引a
WHERE a = const ORDER BY c     -- c字段前面丢失最左前缀索引b
WHERE a = const ORDER BY a, d     -- d不是索引
WHERE a IN (...) ORDER BY b, c     -- 对于排序来说,多个相等条件也是范围查找
  • 1
  • 2
  • 3
  • 4
  • 5

2.2 filesort算法:双路排序和单路排序

排序的字段如果不在索引列上,则filesort会有2种算法:双路排序、单路排序。
1)双路排序(慢)

  • mysql4.1之前使用的是双路排序。字面意思是两次扫描磁盘得到最终结果。首先读取行指针和order by列,对他们进行排序。然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出。
  • 从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段

取一批数据,要对磁盘进行两次扫描。众所周知,IO是非常耗时的,所以在mysql4.1之后,出现了第二种改进的算法,即单路排序。

2)单路排序(快)
从磁盘读取查询需要的所有列,按照order by列在buffer中进行排序,然后扫描排序后的列表进行输出。

优化策略:
1)尝试提高 sort_buffer_size
不管使用哪种算法,提高这个参数都会提高效率。要根据系统的能力去提高,因为这个参数针对每个进程的1M-8M之间调整。Mysql 5.7中,Innodb存储引擎默认的是1MB

mysql> show variables like '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_sort_buffer_size | 1048576 |
| myisam_sort_buffer_size | 8388608 |
| sort_buffer_size        | 262144  |
+-------------------------+---------+
3 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

2)order by 时使用 select * 是一个大忌,最好只查询需要的字段

3、GROUP BY 优化

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

4、分页查询优化

一般在分页查询时,通过创建覆盖索引能够比较好的提高性能。
比如:

SELECT * FROM student LIMIT 2000000, 10;
  • 1

可以考虑在索引上先完成排序分页操作,然后再根据主键关联回原表来查询所需内容:

SELECT * FROM student t, (SELECT id FROM student ORDER BY id LIMIT 2000000, 10) a
WHERE t.id = a.id;
  • 1
  • 2
声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:【wpsshop博客】
推荐阅读
相关标签
  

闽ICP备14008679号