mysql高效查询left join和group by(加索引)

mysql高效查询

mysql牺牲了group by来增加left join的速度（前提是加了索引）。

user表：10万数据

实例1： 200秒左右

SELECT U.id, A.favorite_count FROM (SELECT id from user) U
LEFT JOIN (
  -- 点赞数
  SELECT favorite_by AS user_id, SUM(favorite_count) AS favorite_count
  FROM favorite
  GROUP BY favorite_by
)  A ON U.id=A.user_id
LEFT JOIN (
  -- 评论数量
  SELECT user_id, COUNT(*) AS comment_count
  FROM photo_comment
  GROUP BY user_id
) B ON U.id=B.user_id

实例2：1秒多

select uf.user_id , uf.favorite_count, COUNT(pc.id) as comment_count from (
select u.id as user_id , SUM(f.favorite_count) as favorite_count from (SELECT id from user) u
LEFT JOIN favorite f on f.favorite_by = u.id
GROUP BY u.id
) uf
LEFT JOIN photo_comment pc on pc.user_id = uf.user_id
GROUP BY uf.user_id

附：如何在MySQL高效的join3个表

对于下面这个三个表的join语句

select *
from t1
join t2 on(t1.a=t2.a)
join t3 on (t2.b=t3.b)
where t1.c>=X and t2.c>=Y and t3.c>=Z;

若改写成straight_join，要怎么指定连接顺序，以及怎么给三个表创建索引？

尽量使用BKA算法

使用BKA，并非“先计算两个表join的结果，再跟第三个表join”，而是直接嵌套查询的。具体实现：在t1.c>=X、t2.c>=Y、t3.c>=Z这三个条件里，选择一个经过过滤以后，数据最少的那个表，作为第一个驱动表。此时，可能会出现如下两种情况。

如果选出来是表t1或者t3，那剩下的部分就固定了：

  • 如果驱动表是t1，则连接顺序是t1->t2->t3，要在被驱动表字段创建上索引，也就是t2.a 和 t3.b上创建索引
  • 如果驱动表是t3，则连接顺序是t3->t2->t1，需要在t2.b 和 t1.a上创建索引。

同时，我们还需要在第一个驱动表的字段c上创建索引。

第二种情况是，若选出来的第一个驱动表是表t2，则需要评估另外两个条件的过滤效果。

思路就是，尽量让每一次参与join的驱动表的数据集，越小越好，因为这样我们的驱动表就会越小。

总结

到此这篇关于mysql高效查询left join和group by的文章就介绍到这了,更多相关mysql高效查询内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL中（JOIN/ORDER BY）语句的查询过程及优化方法

    MySQL中（JOIN/ORDER BY）语句的查询过程及优化方法

    在MySQL查询语句过程和EXPLAIN语句基本概念及其优化中介绍了EXPLAIN语句,并举了一个慢查询例子: 可以看到上述的查询需要检查1万多记录,并且使用了临时表和filesort排序,这样的查询在用户数快速增长后将成为噩梦. 在优化这个语句之前,我们先了解下SQL查询的基本执行过程: 1.应用通过MySQL API把查询命令发送给MySQL服务器,然后被解析 2.检查权限.MySQL optimizer进行优化,经过解析和优化后的查询命令被编译为CPU可运行的二进制形式的查询计划(quer

  • mysql中各种常见join连表查询实例总结

    mysql中各种常见join连表查询实例总结

    本文实例讲述了mysql中各种常见join连表查询.分享给大家供大家参考,具体如下: 通常我们需要连接多个表查询数据,以获取想要的结果. 一.连接可以分为三类: (1) 内连接:join,inner join (2) 外连接:left join,left outer join,right join,right outer join,union,union all (3) 交叉连接:cross join 二.准备需要演示的表: CREATE TABLE `a` ( `id` int(11) uns

  • 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利用profile分析慢sql详解（group left join效率高于子查询）

    使用profile来分析慢sql mysql 的 sql 性能分析器主要用途是显示 sql 执行的整个过程中各项资源的使用情况.分析器可以更好的展示出不良 SQL 的性能问题所在. 最近遇到一个查询比较慢的sql语句,用了子查询,大概需要0.8秒左右,这个消耗时间比较长,严重影响了性能,所以需要进行优化.单独查询单表或者子查询记录都很快,下面来看看详细的介绍. 开启profile mysql> show profiles; -- 查看是否开启 Empty set, 1 warning (0.00

  • MYSQL使用inner join 进行 查询/删除/修改示例

    复制代码 代码如下: --查询 SELECT tp.tp_id, tp.tpmc, tp.leveid, tp.tpdz, tp.jgm, tp.scsj, tp.pbzyid, tp.ksbfsj, tp.jsbfsj, tp.status, tp.tpbz FROM qdgl_tupian tp INNER JOIN qdgl_pqb pq ON tp.tp_id=pq.tpid WHERE pq.bfjgm='27010825' AND ps_bfsj >= '2013-01' AND p

  • Mysql连接join查询原理知识点

    Mysql连接(join)查询 1.基本概念 将两个表的每一行,以"两两横向对接"的方式,所得到的所有行的结果. 假设: 表A有n1行,m1列: 表B有n2行,m2列: 则表A和表B"对接"之后,就会有: n1*n2行: m1+m2列. 2.则他们对接(连接)之后的结果类似这样: 3.连接查询基本形式: from  表1  [连接方式] join  表2  [on连接条件]连接查询基本形式: from  表1  [连接方式] join  表2  [on连接条件] 1

  • MySQL优化之使用连接(join)代替子查询

    使用连接(JOIN)来代替子查询(Sub-Queries) MySQL从4.1开始支持SQL的子查询.这个技术可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中.例如,我们要将客户基本信息表中没有任何订单的客户删除掉,就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来,然后将结果传递给主查询,如下所示: DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FR

  • 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查询优化:连接查询排序limit(join、order by、limit语句)介绍

    不知道有没有人碰到过这样恶心的问题:两张表连接查询并limit,SQL效率很高,但是加上order by以后,语句的执行时间变的巨长,效率巨低. 情况是这么一个情况:现在有两张表,team表和people表,每个people属于一个team,people中有个字段team_id. 下面给出建表语句: 复制代码 代码如下: create table t_team ( id int primary key, tname varchar(100) ); create table t_people (

  • mysql使用from与join两表查询的区别总结

    mysql使用from与join两表查询的区别总结

    前言 在mysql中,多表连接查询是很常见的需求,在使用多表查询时,可以from多个表,也可以使用join连接连个表 这两种查询有什么区别?哪种查询的效率更高呢? 带着这些疑问,决定动手试试 1.先在本地的mysql上先建两个表one和two one表 CREATE TABLE `one` ( `id` int(0) NOT NULL AUTO_INCREMENT, `one` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = InnoDB

随机推荐