MYSQL Left Join优化(10秒优化到20毫秒内)

目录
  • 【功能背景】
  • 【原始的SQL】
  • 【原始的SQL分析】
  • 【分析步骤】
  • 【优化后的SQL】
  • 【优化的SQL分析】

结合工作中的内容和大家分享一次Left Jon优化的过程,希望能给同学们新的思路。

【功能背景】

    我们需要按照用户订单号和商户号统计出购买的商品数量和售后的商品数量。涉及到的表和关系见下图:

很不幸工程师在起初进行表结构设计的时候没有在商户订单表中记录下购买的商品总数,在商户订单的售后单中也没记录下售后的商品数量。

【原始的SQL】

select
  o.no,s_order.no,sum(s_item.count),sum(after_sale_item.count)
  from
  buyer_order o
  left join seller_order s_order on o.id = s_order.buyer_order_id
  left join seller_order_item s_item on s_order.id = s_item.seller_order_id
  left join seller_order_after_sale after_sale on s_order.id = after_sale.seller_order_id
  left join seller_order_after_sale_item after_sale_item on after_sale.id = after_sale_item.after_sale_id
where o.add_time >='2019-05-01'
group by
  o.id,s_order.id
order by
  o.id
limit 0,10

以上SQL几个关键字段都使用了索引。  

【原始的SQL分析】

这是一条很常规的SQL,逻辑上也没什么毛病

这条SQL中有较多的连接查询,如果随着售后单的增加,连接的数据就会更多

将符合条件的数据都加载到内存后按照 order.id,s_order.id 进行分组统计,如果有100W的数据会怎样?如果你用代码去实现这么一段统计你会怎么做?

将统计完的数据再按照 order.id 进行排序,取出前10条数据。

从以上的SQL发现需要将符合条件的所有的数据加载到内存后要进行分组,统计,排序,最后再进行分页。我们能不能减少数据的加载数量呢?能不能减少数据库CPU的使用量,能不能先取少量的数据再统计呢?

基于以上的问题,我们进行了优化

【分析步骤】

作为旁观者一开始不了解我们功能需要输出什么样的数据,所以我们一开始要了解每张表存储的是什么样的数据,彼此之间的关系是什么。

我们忘记原来的SQL是什么样的,按照我们需要的数据,再次重新的思考,不要再陷入原来的SQL的漩涡中。

针对上面提出的问题,如何减少数据的加载?能不能先分页数据,再对分页的数据进行单独的统计呢?

那么我们是不是需要对group by进行优化,我们要想办法先分页

大家是否想到了一些方法?

【优化后的SQL】

select
  o.id,o.no,s_order.no,
  (select sum(sot.count) from seller_order so
    left join seller_order_item sot on so.id = sot.seller_order_id
        where so.id =s_order.id ),
  (select sum(osat.count) from seller_order_after_sale osa
    left join seller_order_after_sale_item osat on osa.id = osat.after_sale_id
        where osa.seller_order_id = s_order.id )
  from
  buyer_order o
  left join seller_order s_order on o.id = s_order.buyer_order_id
where o.addTime >='2019-05-01'
order by
  o.id
limit 0,10

【优化的SQL分析】

  1. 很直观的发现,我们把group by去掉了,因为按照 order.id,s_order.id 分组,实际只对 buyer_order和seller_order表进行连接,逻辑上是一样的进行了分组。
  2. group by不使用的话我们就减少了CPU对数据分组的处理,而且我们只连接主要的表数据,减少了加载到内存中的数据。
  3. 以上的操作就完成了我们之前说的先对数据分页。我们取出了10条数据。
  4. 接着我们再对10条数据的销售出去的商品数量和售后的数量进行统计
  5. 这时候大家发现,我们其实只对分页出来的10条数据进行统计,原来是将所有的数据分组统计后取10条。可以发现我们这样操作大大减少了对数据的统计处理。我们只需要统计我们需要的数据。

以上优化的效果可能远远超出大家的想象。

实际工作中连表的数比我们例子中的要多,未优化的SQL在执行未分页的时候发现一共有70万的数据,我们分页取出10条数据花了10+秒以上的时间,数据量不大但是大部分的时间都消耗在了分组和数据统计,大家可以试着写一段代码对这些数据进行分组和统计,就能明白其中的复杂性。

而实际上无论取出10条和全部取出,时间基本上一样的(不考虑IO),因为先进行了统计。

优化后的SQL,加载到内存中只有2万左右的数据,而且不进行统计,先取出10条数据,然后再对10条数据进行统计,逻辑上比之前的简单多了。优化后的SQL执行时间在20毫秒以内。

其实如果在订单表和售后表都记录了对应的数量,连表数还要少,还不需要进行子查询。有时候设计表的时候还是需要考虑一下统计的需要。

到此这篇关于MYSQL Left Join优化(10秒优化到20毫秒内)的文章就介绍到这了,更多相关MYSQL Left Join优化内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • Mysql巧用join优化sql的方法详解

    0. 准备相关表来进行接下来的测试 相关建表语句请看:https://github.com/YangBaohust/my_sql user1表,取经组 +----+-----------+-----------------+---------------------------------+ | id | user_name | comment | mobile | +----+-----------+-----------------+-----------------------------

  • 解决Mysql的left join无效及使用的注意事项说明

    Mysql的left join无效及使用 今天写sql发现使用left join 没有把左边表的数据全部查询出来,让我郁闷了一会,后来仔细研究了一会才知道自己犯了个常识性的错误(我是菜鸟) 这是原sql 这样的查询并不能将tb_line这张表的数据都查询出来,好尴尬... 后面我才知道原来当我们进行多表查询,在执行到where之前,会先形成一个临时表 而on就是临时表中的条件筛选,使用left join则不管条件是否为真,都会查询出左边表的数据,条件为假的,则显示为null where则是在临时

  • MySQL联表查询基本操作之left-join常见的坑

    概述 对于中小体量的项目而言,联表查询是再常见不过的操作了,尤其是在做报表的时候.然而校对数据的时候,您发现坑了吗?本篇文章就 mysql 常用联表查询复现常见的坑. 基础环境 建表语句 DROP TABLE IF EXISTS `role`; CREATE TABLE `role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `role_name` VARCHAR(50) DEFAULT NULL COMMENT '角色名', PRIMARY KEY (`i

  • mysql多个left join连接查询用法分析

    本文实例讲述了mysql多个left join连接查询用法.分享给大家供大家参考,具体如下: mysql查询时需要连接多个表时,比如查询订单的商品表,需要查询商品的其他信息,其他信息不在订单的商品表,需要连接其他库的表,但是连接的条件基本都是商品ID就可以了,先给一个错误语句(查询之间的嵌套,效率很低): SELECT A.order_id, A.wid, A.work_name, A.supply_price, A.sell_price, A.total_num, A.sell_profit,

  • 浅析Mysql Join语法以及性能优化

    一.Join语法概述 join 用于多表中字段之间的联系,语法如下: 复制代码 代码如下: ... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona table1:左表:table2:右表. JOIN 按照功能大致分为如下三类: INNER JOIN(内连接,或等值连接):取得两个表中存在连接匹配关系的记录. LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录. RIGHT JOIN

  • MySQL中一些优化straight_join技巧

    在oracle中可以指定的表连接的hint有很多:ordered hint 指示oracle按照from关键字后的表顺序来进行连接:leading hint 指示查询优化器使用指定的表作为连接的首表,即驱动表:use_nl hint指示查询优化器使用nested loops方式连接指定表和其他行源,并且将强制指定表作为inner表. 在mysql中就有之对应的straight_join,由于mysql只支持nested loops的连接方式,所以这里的straight_join类似oracle中

  • MYSQL Left Join优化(10秒优化到20毫秒内)

    目录 [功能背景] [原始的SQL] [原始的SQL分析] [分析步骤] [优化后的SQL] [优化的SQL分析] 结合工作中的内容和大家分享一次Left Jon优化的过程,希望能给同学们新的思路. [功能背景]     我们需要按照用户订单号和商户号统计出购买的商品数量和售后的商品数量.涉及到的表和关系见下图: 很不幸工程师在起初进行表结构设计的时候没有在商户订单表中记录下购买的商品总数,在商户订单的售后单中也没记录下售后的商品数量. [原始的SQL] select o.no,s_order.

  • sql优化实战 把full join改为left join +union all(从5分钟降为10秒)

    今天收到一个需求,要改写一个报表的逻辑,当改完之后,再次运行,发现运行超时. 因为特殊原因,无法访问客户的服务器,没办法查看sql的执行计划.没办法知道表中的索引情况,所以,尝试从语句的改写上来优化. 一.原始语句如下: select isnull(vv.customer_id,v.customer_id) as customer_id, isnull(vv.business_date,replace(v.business_date,'-','')) as business_date, v.pr

  • MySQL中join语句怎么优化

    目录 Simple Nested-Loop Join Block Nested-Loop Join Index Nested-Loop Join 如何选择驱动表? Simple Nested-Loop Join 我们来看一下当进行 join 操作时,mysql是如何工作的.常见的 join 方式有哪些? 如图,当我们进行连接操作时,左边的表是驱动表,右边的表是被驱动表 Simple Nested-Loop Join 这种连接操作是从驱动表中取出一条记录然后逐条匹配被驱动表的记录,如果条件匹配则将

  • MySQL多表链接查询核心优化

    概述 在一般的项目开发中,对数据表的多表查询是必不可少的.而对于存在大量数据量的情况时(例如百万级数据量),我们就需要从数据库的各个方面来进行优化,本文就先从多表查询开始.其他优化操作,后续另外更新,敬请关注. 数据背景 现假设有一个中学学校,学校中的年级有一年级.二年级.三年级,每个年级有两个班级.分别为101.102.201.202.301.302. 现在我们要为这个学校建立一个考试成绩统计系统.为此,我们对数据库的设计画了如下ER图: 根据ER图,我们设计了数据表,结构如下: class

  • 浅谈MySQL 亿级数据分页的优化

    背景 下班后愉快的坐在在回家的地铁上,心里想着周末的生活怎么安排. 突然电话响了起来,一看是我们的一个开发同学,顿时紧张了起来,本周的版本已经发布过了,这时候打电话一般来说是线上出问题了. 果然,沟通的情况是线上的一个查询数据的接口被疯狂的失去理智般的调用,这个操作直接导致线上的MySql集群被拖慢了. 好吧,这问题算是严重了,下了地铁匆匆赶到家,开电脑,跟同事把Pinpoint上的慢查询日志捞出来.看到一个很奇怪的查询,如下 POST domain/v1.0/module/method?ord

  • 浅谈mysql 树形结构表设计与优化

    前言 在诸多的管理类,办公类等系统中,树形结构展示随处可见,以"部门"或"机构"来说,接触过的同学应该都知道,最终展示到页面的效果就是层级结构的那种,下图随机列举了一个部门的树型结构展示图 设计考虑因素 1.表结构设计 稍稍有点开发和表结构设计经验的同学,设计出这样一张表,应该很容易,只需要在depart表中,添加一个pid/字段即可满足要求,参考下表: CREATE TABLE `depart` ( `depart_id` varchar(32) NOT NULL

  • MySQL优化之连接优化

    上文MySQL优化之缓存优化 这篇文章中提到了一个很重要的概念,就是show variables是用来表示系统编译或者配置在my.cnf中的变量值.而show status则称之为状态值,显示的是当前服务实例运行所具有的状态信息,是一个动态改变的值.因此常用来观测当前MySQl的运行是否正常,如果不正常那么依靠调整静态参数来提高MySQL的性能.所以明白这两个概念的不同,是后面调优的基础. MySQL 连接优化 记得有一次在公司内部连接MySQL的时候,总是连接不上.找到DBA后,经过排查原因,

  • Mysql根据时间查询日期的优化技巧

    例如查询昨日新注册用户,写法有如下两种: EXPLAIN select * from chess_user u where DATE_FORMAT(u.register_time,'%Y-%m-%d')='2018-01-25'; EXPLAIN select * from chess_user u where u.register_time BETWEEN '2018-01-25 00:00:00' and '2018-01-25 23:59:59'; register_time字段是date

  • Mysql数据库中数据表的优化、外键与三范式用法实例分析

    本文实例讲述了Mysql数据库中数据表的优化.外键与三范式用法.分享给大家供大家参考,具体如下: 数据表优化 将商品信息表进行优化 1.创建商品种类表: create table if not exists goods_cates( id int unsigned primary key auto_increment, name varchar(40) not null ); 2.将商品种类写入商品种类表中: 注意:插入另一个表的查询结果不需要加values insert into goods_

  • 实现MySQL定时批量检查表repair和优化表optimize table的shell脚本

    本文介绍mysql定时批量检查表repair和优化表optimize table的shell脚本,对于MySQL数据库的定期维护相当有用!如下所示: #!/bin/bash host_name=192.168.0.123 user_name=jincon.com user_pwd=jincon.com database=my_db_name need_optmize_table=true tables=$(mysql -h$host_name -u$user_name -p$user_pwd $

随机推荐