V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
Dlin
V2EX  ›  MySQL

求取一个 sql 优化建议

  •  
  •   Dlin · 237 天前 · 2531 次点击
    这是一个创建于 237 天前的主题,其中的信息可能已经有所发展或是发生改变。

    先祖留下的 sql 如下:

    SELECT
    	COUNT(*) 
    FROM
    	(
    	SELECT
    		t2.create_time createTime,
    		t2.platform,
    		t2.id taskId,
    		t2.order_id orderId,
    		t1.business_contract_no businessContractNo,
    		t3.contract_no,
    		t4.contract_name 
    	FROM
    		esign_task_item t1
    		INNER JOIN esign_task t2 ON t1.task_id = t2.id
    		LEFT JOIN esign_contract t3 ON t3.task_item_id = t1.id
    		LEFT JOIN esign_contract_template t4 ON t4.contract_no = t1.contract_template_no 
    	WHERE
    		t1.business_contract_no IS NOT NULL 
    	GROUP BY
    		t1.business_contract_no,
    		t2.order_id,
    		t2.id 
    	ORDER BY
    	t1.id DESC 
    	) TOTAL
    

    这段 sql 其实也是分页插件生成的,先不说 count 很慢的问题。 问题是里面 group by 非常慢,得查 20 秒左右 执行计划如下: pC2Rn2V.png

    21 条回复    2023-07-10 19:07:40 +08:00
    lyusantu
        1
    lyusantu  
       237 天前   ❤️ 2
    都要查 count 了,left join 和 order by 的意义是什么呢
    wangritian
        2
    wangritian  
       237 天前   ❤️ 1
    槽点太多,说你不懂吧,还知道执行计划,说懂吧,写成这样的 SQL 你居然在认真分析[捂脸]
    Corolin
        3
    Corolin  
       237 天前
    MyBatis 吧... 不太理解这个里面的 group by 的用意... 去重?

    select count(0) from (
    SELECT 1
    FROM
    esign_task t2
    INNER JOIN esign_task_item t1 ON t1.task_id = t2.id
    WHERE
    t1.business_contract_no IS NOT NULL
    GROUP BY
    t1.business_contract_no,
    t2.order_id,
    t2.id
    ) a
    5aSx5Lia
        4
    5aSx5Lia  
       237 天前
    这里应该是主要分页查询 t1 ,t2 表的数据吧,t3 ,t4 只是查询信息,感觉可以拆分成两次查询,先分页查询出前 N 条数据,然后再去查补充数据, 或者不用分页插件去查 count
    play78
        5
    play78  
       237 天前
    不知道你具体是什么数据,但是看后面的 group ,至少可以把 where 和 group 放到 t1 那里,减少后面关联后,数据过大吧? t2,t3, t4, 这些数据都可以先 group 后,再进行 left join 关联。
    SELECT
    ...
    FROM (
    select ... from esign_task_item where business_contract_no IS NOT NULL group by task_id, business_contract_no) as t1
    INNER JOIN esign_task t2 ON t1.task_id = t2.id
    LEFT JOIN esign_contract t3 ON t3.task_item_id = t1.id
    LEFT JOIN esign_contract_template t4 ON t4.contract_no = t1.contract_template_no
    GROUP BY
    t2.order_id,
    t2.id
    wengyanbin
        6
    wengyanbin  
       237 天前
    自己写 sql 去查询总数,明显 mybatis 生成的 sql 很慢。
    buffzty
        7
    buffzty  
       237 天前
    @lyusantu 基本就是懒 直接把查询语句加个 count
    xuanbg
        8
    xuanbg  
       237 天前
    WHERE
    t1.business_contract_no IS NOT NULL

    这就约等于取全部数据了吧,怎么都不可能快的。
    yhtbiy
        9
    yhtbiy  
       237 天前
    优化建议:
    1. 在子查询中,可以将不需要的字段去除,只保留需要进行 COUNT 操作的字段,以减少内存和 CPU 的消耗。
    2. 可以考虑将子查询中的 LEFT JOIN 改为 INNER JOIN ,以减少查询的数据量。
    3. 可以为子查询中的表添加索引,以提高查询性能。
    4. 可以考虑将子查询中的 GROUP BY 和 ORDER BY 操作移至外层查询,以减少子查询的数据量。
    5. 可以根据实际情况,调整查询语句的顺序,以提高查询性能。
    imokkkk
        10
    imokkkk  
       237 天前
    优化建议如下:

    1. 索引优化:为表 esign_task_item 的列 business_contract_no 创建索引,以加快 WHERE 子句中的条件判断。

    2. 子查询优化:将子查询中的 GROUP BY 子句中的 t2.order_id 和 t2.id 移至外层查询中,以避免在子查询中进行分组操作。

    3. 子查询优化:将子查询中的 ORDER BY 子句中的 t1.id 改为 t2.id ,以避免在子查询中进行排序操作。

    优化后的 SQL 如下:

    SELECT
    COUNT(*)
    FROM
    (
    SELECT
    t2.create_time createTime,
    t2.platform,
    t2.id taskId,
    t2.order_id orderId,
    t1.business_contract_no businessContractNo,
    t3.contract_no,
    t4.contract_name
    FROM
    esign_task_item t1
    INNER JOIN esign_task t2 ON t1.task_id = t2.id
    LEFT JOIN esign_contract t3 ON t3.task_item_id = t1.id
    LEFT JOIN esign_contract_template t4 ON t4.contract_no = t1.contract_template_no
    WHERE
    t1.business_contract_no IS NOT NULL
    GROUP BY
    t1.business_contract_no,
    t2.id,
    t2.order_id
    ORDER BY
    t2.id DESC
    ) TOTAL
    sujin190
        11
    sujin190  
       237 天前
    看你这个似乎改成 t2 left join t1 就不用 group by 了吧,因为你 t2 其实 group by 了主键 id 了,t2 left join t1 的时候就算 t1 里有重复的也恰好会展开,所以结果和你的这个用 group by 是一样的
    hcbb
        12
    hcbb  
       237 天前
    1.你确定这条 sql 能跑?都 group 了还能取出 t2.create_time createTime,t2.platform,不聚合搞个鸡儿 group ,不如直接 distinct
    2.business_contract_no,id 这些都该有索引
    3.由于你最终目的是 count(*),所有很多表没必要 join 进去,(默认你 id 是 unique index),esign_task 没必要 join 进去,where 加
    t1.task_id is not null 即可,t3,t4 也可以想一下是不是满足 1.n 情况,进行去除
    4.别用分页插件了
    hhjswf
        13
    hhjswf  
       237 天前 via Android
    @lyusantu 都说是分页插件了,一条查分页数据,一条查总页数,count 就是查总页数
    iosyyy
        14
    iosyyy  
       237 天前
    在这个查询中,表 t1 很大,而表 t2 、t3 和 t4 相对较小。为了优化查询性能,可以考虑以下几点:

    索引优化:确保每个表上的相关列具有适当的索引。在表 t1 上,确保 business_contract_no 列上有索引,这将加速条件 t1.business_contract_no IS NOT NULL 的筛选。在其他表上,根据连接条件和筛选条件创建索引,以提高连接和过滤操作的效率。

    重新排列连接顺序:根据表的大小和连接条件选择合适的连接顺序。将较小的表放在前面,以减少连接操作的数据量。在这个查询中,将 t2 表放在前面,t1 表放在后面可能更合适。

    子查询优化:将子查询的结果存储到一个临时表中,并在最外层的 SELECT 语句中使用该临时表。这样可以避免在子查询中重复执行相同的操作,提高查询效率。

    下面是优化后的查询代码:

    sql
    Copy code
    -- 创建临时表存储子查询结果
    ```sql
    CREATE TEMPORARY TABLE temp_result
    SELECT
    t2.create_time createTime,
    t2.platform,
    t2.id taskId,
    t2.order_id orderId,
    t1.business_contract_no businessContractNo,
    t3.contract_no,
    t4.contract_name
    FROM
    esign_task_item t1
    INNER JOIN esign_task t2 ON t1.task_id = t2.id
    LEFT JOIN esign_contract t3 ON t3.task_item_id = t1.id
    LEFT JOIN esign_contract_template t4 ON t4.contract_no = t1.contract_template_no
    WHERE
    t1.business_contract_no IS NOT NULL
    GROUP BY
    t1.business_contract_no,
    t2.order_id,
    t2.id
    ORDER BY
    t1.id DESC;
    ```

    -- 查询临时表中的行数
    SELECT COUNT(*) FROM temp_result;
    请注意,根据实际情况,你可能需要调整索引、连接顺序和其他查询优化技术来进一步提升性能。这些优化建议提供了一般性的指导,具体的优化策略应根据数据库引擎和数据量等因素进行调整。

    chatgpt 生成的我输入的条件就是 t1 是大表其他都是小表相连 如果其他也都是大表我建议你更改数据库结构减少笛卡尔积
    另外这里的
    ```sql
    GROUP BY
    t1.business_contract_no,
    t2.order_id,
    t2.id
    ```
    应该被删除掉
    iosyyy
        15
    iosyyy  
       237 天前
    @iosyyy 再补充一点 t2.create_time createTime,
    t2.platform,
    t2.id taskId,
    t2.order_id orderId,
    t1.business_contract_no businessContractNo,
    t3.contract_no,
    t4.contract_name
    这些实际上都应该直接 select 1 这些数据我看你的逻辑应该是不需要查的
    iosyyy
        16
    iosyyy  
       237 天前
    @iosyyy #15 这样可以减少数据库缓存.. 对数据库见解比较浅 而且你这个需要具体问题具体分析 你给出的东西并不够多 建议补充下再说
    jack4536251
        17
    jack4536251  
       237 天前 via Android
    现在还有手写原生 sql 的?不都是用 orm 吗
    8355
        18
    8355  
       237 天前
    很明显 最大的性能问题在这里
    WHERE
    t1.business_contract_no IS NOT NULL
    GROUP BY
    t1.business_contract_no,

    GROUP BY t2.order_id,t2.id 意义也没有吧。。。

    之后 但是并没有查询相关字段链表干嘛呢。。 直接删不就得了
    INNER JOIN esign_task t2 ON t1.task_id = t2.id 可以子查询 不用连表 如果数据量是 1 对 1 的话可以完全不用查。。

    完全删掉 屁用没有
    LEFT JOIN esign_contract t3 ON t3.task_item_id = t1.id
    LEFT JOIN esign_contract_template t4 ON t4.contract_no = t1.contract_template_no

    这就是查 list 懒得单独写 count 的结果吧。
    cnoder
        19
    cnoder  
       237 天前
    我想问下 op esign 是什么意思🙋
    huigeer
        20
    huigeer  
       237 天前
    看不到执行计划的图片,再上一遍看看?
    另外 group by 查总数试试
    SQL_CALC_FOUND_ROWS & FOUND_ROWS()
    chendl111
        21
    chendl111  
       237 天前
    若在执行上述 SQL 语句时 GROUP BY 操作过慢,您可以尝试进行以下优化:

    添加索引:对于涉及到 GROUP BY 操作的字段,添加合适的索引可以显著提高查询性能。您可以考虑为 t1.business_contract_no 、t2.order_id 和 t2.id 添加索引。

    使用子查询替代 GROUP BY:有时候,使用子查询来替代 GROUP BY 操作可以提高查询性能。您可以尝试将 GROUP BY 子句替换为子查询,然后使用 DISTINCT 关键字来去重。例如:

    sql
    SELECT COUNT(*)
    FROM (
    SELECT DISTINCT
    t1.business_contract_no,
    t2.order_id,
    t2.id
    FROM
    esign_task_item t1
    INNER JOIN esign_task t2 ON t1.task_id = t2.id
    LEFT JOIN esign_contract t3 ON t3.task_item_id = t1.id
    LEFT JOIN esign_contract_template t4 ON t4.contract_no = t1.contract_template_no
    WHERE
    t1.business_contract_no IS NOT NULL
    ) AS TOTAL;
    这种方式可能会更快,因为 DISTINCT 操作消除了重复行,并且不需要进行全局的 GROUP BY 操作。

    请注意,具体的优化策略可能因数据库的结构、数据量和查询需求而异。建议您根据实际情况评估和测试不同的优化方法以获得最佳性能。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1605 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 16:39 · PVG 00:39 · LAX 08:39 · JFK 11:39
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.