MySql语句SUM、MAX优化：数据库版本5.7.26

MySql语句SUM、MAX优化：数据库版本5.7.26

为了避免大家误会，我用*直接代替了，也就是三个表查询出来的内容都是不能省略的。


 SELECT *  FROM khxx a 
  inner JOIN 
        (SELECT YXTKHH,HXJGBM,sum(jjye) AS jjye FROM dkxx GROUP BY YXTKHH,HXJGBM) c ON a.HXJGBM=c.HXJGBM AND a.YXTKHH=c.YXTKHH 
  left JOIN 
        (SELECT JKRXZ,update_time,SFNCZ, YXTKHH,HXJGBM FROM gqc WHERE id IN (SELECT max(id) FROM gqc GROUP BY YXTKHH,HXJGBM)) b ON a.YXTKHH=b.YXTKHH AND a.HXJGBM=b.HXJGBM

img

用到了 all 和 Using temporary; Using filesort，如何优化？

把a表放到后面？

题主给的语句是：

SELECT a.* FROM khxx a
inner JOIN  (SELECT KHXX_ID ,sum(jjye) AS jjye FROM dkxx GROUP BY KHXX_ID) c ON a.ID=c.KHXX_ID 
left JOIN   (SELECT ID,khxx_id FROM gqc WHERE id IN (SELECT max(id) FROM gqc GROUP BY KHXX_ID ) ) b ON a.ID=b.KHXX_ID

这里面select的字段只有 a.*，不包含b和c的信息，又由于left join b的时候没有带任何where条件，因此a表的数据会全部保留，那么join b就没有意义了，首先可以砍掉b表的关联，变成：

SELECT a.* FROM khxx a
inner JOIN  (SELECT KHXX_ID ,sum(jjye) AS jjye FROM dkxx GROUP BY KHXX_ID) c ON a.ID=c.KHXX_ID

再看这条语句里面， a inner join c 的条件是 c.KHXX_ID，再没有其他条件及where，所以c表的子查询中sum(jjye)没有意义，可以砍掉，再看看group by KHXX_ID并没有带任何where条件，因此dkxx表中的所有KHXX_ID在子查询中都会被保留下来，因此，子查询没有意义，可以直接用表dkxx进行关联，最终砍成：

SELECT a.* FROM khxx a
inner JOIN  dkxx c ON a.ID=c.KHXX_ID

如果题主给出的sql语句正确表达了题主的意思，那么最终优化的语句就是这个了。
望采纳，谢谢！

子查询改成视图, 然后再拿视图去关联, 比如

create view  c as SELECT YXTKHH,HXJGBM,sum(jjye) AS jjye FROM dkxx GROUP BY YXTKHH,HXJGBM

创建视图可参考: http://c.biancheng.net/view/2584.html


 
 SELECT *  FROM khxx a 
  left JOIN 
        (SELECT JKRXZ,update_time,SFNCZ, YXTKHH,HXJGBM FROM gqc WHERE id IN (SELECT max(id) FROM gqc GROUP BY YXTKHH,HXJGBM)) b ON a.YXTKHH=b.YXTKHH AND a.HXJGBM=b.HXJGBM 
 
JOIN 
        (SELECT YXTKHH,HXJGBM,sum(jjye) AS jjye FROM dkxx GROUP BY YXTKHH,HXJGBM) c ON a.HXJGBM=c.HXJGBM AND a.YXTKHH=c.YXTKHH

这么写没什么问题，你是觉得运行时间长了，还是运行的结果不是你想要的

提供参考实例思路【mysql查询优化--临时表和文件排序（Using temporary; Using filesort问题解决）】，链接：https://www.cnblogs.com/jpfss/p/9156422.html

语句中包含了一个内连接和一个左连接。内连接用于将 dkxx 表中的数据与 khxx 表中的数据进行连接，并将 dkxx 表中的 jjye 字段按 YXTKHH 和 HXJGBM 分组求和。左连接用于将 gqc 表中的数据与 khxx 表中的数据连接，并将 gqc 表中的 id 字段按 YXTKHH 和 HXJGBM 分组取最大值。

您可以使用以下方法来优化这条 MySQL 语句：

  1. 建立索引：在查询中使用的字段（例如 YXTKHH 和 HXJGBM）上建立索引，可以提高查询效率。
  2. 使用子查询替换查询：可以将子查询中的查询替换为外连接或内连接，以减少查询的复杂度。
  3. 使用内存表优化：如果需要多次使用的数据量较大，可以将数据加载到内存表中，以便提高查询效率。
  4. 避免使用不必要的字段：使用 SELECT * 返回所有字段可能会使查询变慢，应该只返回实际需要的字段。

另外，您也可以尝试使用 EXPLAIN 命令来分析 MySQL 语句的执行计划，以了解 MySQL 如何执行语句，并找出可以优化的地方。

EXPLAIN SELECT * FROM khxx a
inner JOIN
(SELECT YXTKHH,HXJGBM,sum(jjye) AS jjye FROM dkxx GROUP BY YXTKHH,HXJGBM) c ON a.HXJGBM=c.HXJGBM AND a.YXTKHH=c.YXTKHH
left JOIN
(SELECT JKRXZ,update_time,SFNCZ, YXTKHH,HXJGBM FROM gqc WHERE id IN (SELECT max(id) FROM gqc GROUP BY YXTKHH,HXJGBM)) b ON a.YXTKHH=b.YXTKHH AND a.HXJGBM=b.HXJGBM;

不要一条语句查询出所有的，跟据业务需求把sql拆开分几次查询。

这是一个使用内连接和左连接的 SQL 查询语句，它将 khxx、dkxx、gqc 三个表进行了连接。

为了优化这个查询，可以考虑以下几点：

使用索引来提升查询性能。在 khxx、dkxx 和 gqc 表中，可以建立 YXTKHH 和 HXJGBM 列的索引，这样可以更快地对这些列进行排序和过滤。

对于 dkxx 表，在进行 group by 操作时可以考虑使用索引来代替临时表。例如，可以在 dkxx 表上建立 (YXTKHH, HXJGBM, jjye) 的联合索引，然后使用索引上的 jjye 列计算 sum。

对于 gqc 表，在查询最大 id 时可以使用 limit 子句来限制返回的行数。例如：

SELECT * FROM gqc WHERE id IN (SELECT max(id) FROM gqc GROUP BY YXTKHH,HXJGBM LIMIT 100)

这样可以避免对整张表进行 group by 操作，从而提升查询性能。

对于 khxx 和 gqc 表，在使用左连接时，可以将它们放在连接的右侧，这样可以避免在左表中的 null 值对结果的影响。例如：

SELECT * FROM 
    (SELECT YXTKHH,HXJGBM,sum(jjye) AS jjye FROM dkxx GROUP BY YXTKHH,HXJGBM) c 
    INNER JOIN khxx a ON a.HXJGBM=c.HXJGBM AND a.YXTKHH=c.YXTKHH 
    LEFT JOIN

使用 EXPLAIN 分析查询的执行计划可以帮助优化查询。这两个标识表示 MySQL 需要使用临时表来执行查询，或者在查询结束时对结果进行排序。这可能会导致查询运行得更慢。

要优化查询，你可以尝试以下方法：

1.尽量避免使用 SELECT *，而是只选择需要的列。

2.为使用的列创建索引。

3.尽量使用带有谓词的查询，例如 WHERE 和 HAVING 子句。

4.尝试使用不同的查询方法，例如使用 JOIN 代替子查询。

5.如果你的查询在执行时需要排序，请尝试使用 ORDER BY 子句而不是 GROUP BY。

6.使用 LIMIT 子句可以帮助你限制查询返回的行数。

7.如果你的数据量非常大，可以考虑使用分区表来分割数据。

此外，还可以使用 MySQL 的性能分析工具，例如 PERFORMANCE_SCHEMA 或 EXPLAIN FORMAT=JSON，来更深入地了解查询的执行情况。