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
jwh199588
V2EX  ›  MySQL

前辈写的 sql 语句如何优化

  •  
  •   jwh199588 · 2022-05-24 11:23:54 +08:00 · 2241 次点击
    这是一个创建于 926 天前的主题,其中的信息可能已经有所发展或是发生改变。
     select
            p.pr_id,  p.depot_id, p.depot_name, p.manager_user_id, p.manager_user_name,p.brand_id,p.brand_name,p.desc_id,p.desc_name,p.model_id,p.model_name,p.standard_repertory,
            p.prewarning_value,count(case when s.quality_id=1 then 1 end) as real_repertory,p.remark,p.create_emp,p.create_tm,p.update_emp,p.update_tm,p.apply_tm,p.notice_value, p.standard_mean_value, p.standard_adjust
           FROM
              `table1` p
            LEFT JOIN
            (select desc_id,model_id,brand_id,quality_id,depot_id from table2 where  sp_status_id in (1,2,8)) s
            ON  p.depot_id = s.depot_id
            and p.desc_id = s.desc_id
            and s.model_id in (SELECT g.model_id from table3 g where g.general_code=(SELECT o.general_code FROM table3 o where o.model_id=p.model_id))
            where 1=1
            <if test="depotId != null">
                    and p.depot_id = #{depotId, jdbcType=INTEGER}
                </if>
                <if test="managerUserId != null">
                    and p.manager_user_id = #{managerUserId, jdbcType=INTEGER}
                </if>
                <if test="descId != null">
                    and p.desc_id = #{descId, jdbcType=INTEGER}
                </if>
                <if test="modelId != null">
                    and p.model_id = #{modelId, jdbcType=INTEGER}
                </if>
                <if test="brandId != null">
                    and p.brand_id = #{brandId, jdbcType=INTEGER}
                </if>
                <if test="managerUserName != null and managerUserName != ''">
                    and p.manager_user_name = #{managerUserName, jdbcType=VARCHAR}
                </if>
                <if test="startTime != null">
                    and p.apply_tm &gt;= #{startTime, jdbcType=TIMESTAMP}
                </if>
                <if test="endTime != null">
                    and p.apply_tm &lt;= #{endTime, jdbcType=TIMESTAMP}
                </if>
    
               GROUP by p.pr_id having 1=1
               <if test="noticeType != null and noticeType ==1">
                   and real_repertory &lt;= p.prewarning_value
               </if>
                <if test="realRepertory != null">
                    and real_repertory=#{realRepertory, jdbcType=INTEGER}
                </if>
    

    上一任开发者写了一个 sql 语句,我想优化一下,但是发现自己能力有限,谁可以帮个忙;
    说说情况:

    1. table1 表大概数据量只有 1000 条
    2. table2 表的数据量大概有 100 万
    3. table3 表大概只有 1000 条
      现在一次查询大概需要 2 分钟
    6 条回复    2022-05-24 19:39:16 +08:00
    lixikei
        1
    lixikei  
       2022-05-24 11:31:20 +08:00
    分三次查 都比这效率高
    T0m008
        2
    T0m008  
       2022-05-24 11:35:48 +08:00
    100 万的 table2 明显可以生成一个小的表啊
    where sp_status_id in (1,2,8)
    eason1874
        3
    eason1874  
       2022-05-24 11:49:16 +08:00
    用 explain 命令分析这语句,不出意外的话 table2 那里数据量爆炸,正常应该 join 再 where 而不是 select where 再联表(除非 select 出来的数据量极小)

    最后两个 select 也很奇怪,看着是想查询 1 、3 两个表共同的字段。正常人写不成这鸟样,目测是通过某些 SQL 管理界面操作,自动生成的
    linglin0924
        4
    linglin0924  
       2022-05-24 11:52:51 +08:00
    我终于理解为什么有的人看到代码就头晕,因为我看到这坨 sql 也晕了。

    日常写 sql 都是配合 orm 框架,最多几行,不然多写几个。

    这一坨。
    fxxkgw
        5
    fxxkgw  
       2022-05-24 12:06:06 +08:00
    谷歌搜:sql 优化 美团
    PopRain
        6
    PopRain  
       2022-05-24 19:39:16 +08:00
    select
    p.pr_id, p.depot_id, p.depot_name, p.manager_user_id, p.manager_user_name,p.brand_id,p.brand_name,p.desc_id,p.desc_name,p.model_id,p.model_name,p.standard_repertory,
    p.prewarning_value,count(case when s.quality_id=1 then 1 end) as real_repertory,p.remark,p.create_emp,p.create_tm,p.update_emp,p.update_tm,p.apply_tm,p.notice_value, p.standard_mean_value, p.standard_adjust
    FROM
    `table1` p
    LEFT JOIN table3 o on o.model_id=p.model_id -- 得到 o.general_code
    LEFT JOIN table3 g on g.general_code=o.general_code -- 得到 g.model_id
    LEFT JOIN table2 s on s.depot_id=p.depot_id and s.desc_id=p.desc_id and s.sp_status_id in (1,2,8) and s.model_id=g.model_id

    -- 这样应该就可以了,原来那种写法 mysql 优化不了。。。。。 现在这种写法也要看执行计划,添加必要索引,MySql 处理复杂 join 能力很差 (和 postgresql , oracle, sql server 比)
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   4995 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 03:55 · PVG 11:55 · LAX 19:55 · JFK 22:55
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.