MySQL——优化嵌套查询和分页查询

优化嵌套查询

嵌套查询(子查询)可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。嵌套查询写起来简单,也容易理解。但是,有时候可以被更有效率的连接(JOIN)替代。

现在假如要找出从来没有在网站中消费的客户,也就是查询在客户customer表中但是不在支付payment表中的客户信息。

嵌套查询:

explainselect*fromcustomerwherecustomer_idnotin(selectcustomer_idfrompayment);

连接改写:

explainselect*fromcustomer a left join payment b on a.customer_id=b.customer_idwhereb.customer_idisnull;

画外音:连接查询效率更高的原因,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作;并且Not exists表示MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行, 就不再搜索了。

优化分页查询

在MySQL中做分页查询,MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回 N 行,那当 offset 特别大的时候,效率就非常的低下。例如“limit 1000,20”,此时MySQL排序出前1020条数据后仅仅需要第1001到1020条记录,前1000条数据都会被抛弃,查询和排序的代价非常高。由此可见MySQL的分页处理并不是十分完美,需要我们在分页SQL上做一些优化,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL 改写

画外音:控制返回的总页数并不是那么靠谱,毕竟每页的数据量也不能过大,数据多起来之后,控制返回的总页数就变的不现实了。所以还是要对超过特定阈值的页数进行 SQL 改写

现在假设要对电影表film排序后取某一页数据

explainselect*fromfilm orderbytitle limit50,5;

可以看到优化器实际上做了全表扫描,处理效率不高。

第一种优化思路

在索引上完成排序分页的操作,最后根据主键关联回表查询所需要的其他列内容。

画外音:此处涉及到了SQL优化的两个重要概念,索引覆盖和回表,我在前面的文章中详细介绍过这两个概念。通过索引覆盖在索引上完成扫描和排序(索引有序),最后通过主键(InnoDB引擎索引会通过主键回表)回表查询,最大限度减少回表查询的I/O次数。

explainselect*fromfilm a inner join(selectfilm_idfromfilm orderbytitle limit50,5)b on a.film_id=b.film_id;

第二种优化思路

把LIMIT查询转换成某个位置的查询,减少分页翻页的压力。

假设现在每页10条数据,要取第42页的数据。

explainselect*fromfilm orderbytitle limit410,10;

现在需要多传一个参数,就是上一页(第41页)的最后一条数据的主题title,

SQL可以改写为:

explainselect*fromfilmwheretitle>'HOLES BRANNIGAN'orderbytitle limit10;

这样就把LIMIT m,n 转换成了LIMIT n的查询,但是这种方案只适合在不会出现重复值的特定环境,否则分页结果可能会丢失数据。

总结

对于嵌套查询和分页查询的优化,归根结底就是遵循SQL优化原则之一——减少回表查询的I/O次数。对于分页查询优化,更建议使用第一种优化方案,性能更好,稳定性更高。

参考

《深入浅出MySQL》

原文发布于微信公众号 - 撸码那些事(lumanxs)

原文发表时间:2018-08-31

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏运维

zabbix2.4.5迁移到zabbix3.0

http://qicheng0211.blog.51cto.com/3958621/1744603

2173
来自专栏Ken的杂谈

CentOS 7下InfluxDB部署与使用入门

InfluxDB里存储的数据被称为时间序列数据,InfluxDB存储方式跟传统关系型数据库不同的是:传统关系型数据库通过数据库+表+字段组织数据,InfluxD...

4854
来自专栏运维技术迷

连仕彤博客Mysql数据库归档利器之pt-archiver

2186
来自专栏维C果糖

史上最简单的 MySQL 教程(十四)「列属性 之 主键」

主键:primary key,表中主要的键,每张表只能有一个字段(复合主键,可以多个字段)使用此属性,用来唯一的约束该字段里面的数据,不能重复。

43311
来自专栏王磊的博客

Microsoft SQL Server 2005 提供了一些工具来监控数据库

--WL 09-07-03 /*Microsoft SQL Server 2005 提供了一些工具来监控数据库。方法之一是动态管理视图。动态管理视图 (DMV)...

3455
来自专栏农夫安全

注入学习之sqli-labs-3(第二关)

前言 本来是想一个个关卡讲下去,后来自己测试了一下,发现第二、三、四这三关跟第一关,起始原理是一样的,只不过是单引号,双引号,带不带括号的区别,只要我们带入的语...

3386
来自专栏Aloys的开发之路

Oracle系统表整理+常用SQL语句收集

-- DBA/ALL/USER/V_$/GV_$/SESSION/INDEX开头的绝大部分都是视图 -- DBA_TABLES意为DBA拥有的或可以访问的所有...

23910
来自专栏乐沙弥的世界

ORA-00942: table or view does not exist

      在过程,包,函数,触发器中调用Oracle相关动态性能视图时,需要授予适当的权限,否则会收到表和视图不存在的错误提示。即使你可以单独查询这些视图。因...

1422
来自专栏cloudskyme

oracle10g分区的几种类型

为了简化数据库大表的管理,例如在数据仓库中一般都是TB级的数量级.ORACLE8以后推出了分区选项.分区将表分离在若于不同的表空间上,用分而治之的方法来支撑元限...

34911
来自专栏Ryan Miao

mysql创建定时执行存储过程任务

sql语法很多,是一门完整语言。这里仅仅实现一个功能,不做深入研究。 目标:定时更新表或者清空表。 案例:曾经做过定时清空位置信息表的任务。(然而,当时并未考虑...

3967

扫码关注云+社区

领取腾讯云代金券