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

那么挑战来了,这条 sql 还能有更优化性能的写法吗?

  •  
  •   teemoer · 2016-06-15 17:04:10 +08:00 · 6380 次点击
    这是一个创建于 3084 天前的主题,其中的信息可能已经有所发展或是发生改变。

    http://ww3.sinaimg.cn/large/e38a7f8bgw1f4w0tyljcuj20kt0k1juw.jpg

    SELECT m.id, m.name, mc_diag.count_size AS mc_diag_count, mc_thers.count_size AS mc_thers_count, mc_me.count_size AS mc_me_count FROM medicine m LEFT JOIN medicine_count mc_diag ON mc_diag.medicine_id = m.id AND mc_diag.doctor_id = 47 AND mc_diag.diagosis_name = '急性上呼吸道感染' LEFT JOIN medicine_count mc_me ON mc_me.medicine_id = m.id AND mc_me.doctor_id = 47 LEFT JOIN medicine_count mc_thers ON mc_thers.medicine_id = m.id AND mc_thers.doctor_id <> 47 WHERE (m.name LIKE '%w%' OR m.help_code LIKE '%w%') AND m.type = 0 GROUP BY m.name ORDER BY mc_diag_count DESC, mc_me_count DESC, mc_thers_count DESC, m.id DESC LIMIT 0, 10;

    ########################################## 鄙人的智商也就这么多了,诸位 SQL 大神多多指教

    第 1 条附言  ·  2016-06-23 12:15:28 +08:00
    以下是 优化代替这条 sql 的写法......把数据库做的事儿交给了 java 做, 有可以优化的地方请 V 友指正.谢谢哈
    第 2 条附言  ·  2016-06-23 12:19:47 +08:00
    项目没用 java8 不能用流 唉 没办法 还有任何能够优化的地方吗,诸位 小伙伴 大伙伴 老伙伴?
    69 条回复    2016-06-24 11:13:56 +08:00
    teemoer
        1
    teemoer  
    OP
       2016-06-15 17:05:44 +08:00
    shit = = ! 格式乱了 我上图

    banksiae
        2
    banksiae  
       2016-06-15 17:45:59 +08:00
    like '%w%'要扫全表的
    est
        3
    est  
       2016-06-15 17:50:00 +08:00
    急性上呼吸道感染
    teemoer
        4
    teemoer  
    OP
       2016-06-15 17:52:28 +08:00
    @banksiae = = 先 and type=0 再 like 呢? 或者 有比 like 更好的办法吗
    teemoer
        5
    teemoer  
    OP
       2016-06-15 17:52:45 +08:00
    @est = = 看到你头像我忍不住笑出声
    Infernalzero
        6
    Infernalzero  
       2016-06-15 18:03:14 +08:00
    没有索引如何谈优化,况且看到那么多 join 而且还同时 group by order by 以及 like '%'的要想不是慢查询也难
    ayumilove
        7
    ayumilove  
       2016-06-15 18:05:27 +08:00
    medicine 表 有多少条数据
    500miles
        8
    500miles  
       2016-06-15 18:06:19 +08:00
    对同一张表 join 三次, 三次的条件还互补, 看不懂 看不懂
    ango
        9
    ango  
       2016-06-15 18:09:27 +08:00   ❤️ 1
    不要为一条 sql 而一条 sql ,注意 IO 瓶颈,
    应该在语言层来做一些逻辑处理,这样顶多消耗一点 CPU 性能。


    我们公司不允许 联表 查询。不到万不得已,不允许使用 联表 查询。
    magicdawn
        10
    magicdawn  
       2016-06-15 18:10:56 +08:00
    对啊...
    left join 同一张表啊
    ixiaozhi
        11
    ixiaozhi  
       2016-06-15 18:14:26 +08:00
    @ango 不允许 联表 查询的理由是什么,有点儿不理解
    omygod
        12
    omygod  
       2016-06-15 18:18:03 +08:00
    抛开数据量,谈 left join 的个数就是耍流氓
    ango
        13
    ango  
       2016-06-15 18:21:51 +08:00
    @ixiaozhi
    说是联表会产生临时表,导致 db 压力大。

    老人说是总监要求的,我做为新人也只能执行了。
    其实我也不能理解,简单的索引联表复合查询能有什么压力,现在搞得都得在 PHP 语言层做数据组合。
    tomczhen
        14
    tomczhen  
       2016-06-15 18:24:29 +08:00 via iPhone
    又是 jion ,又是 like ,又是 or ,数据量上来光靠修改 sql 语句想提高查询速度是不可能的。
    JiShuTui
        15
    JiShuTui  
       2016-06-15 18:25:47 +08:00
    分拆成多个查询,合理利用缓存
    murmur
        16
    murmur  
       2016-06-15 18:32:15 +08:00
    自己 join 自己是什么情况呢。。。看到 like 基本就确定没法优化了
    fireapp
        17
    fireapp  
       2016-06-15 18:33:07 +08:00
    这条 sql 有点毛病, 查询的结果集因为 group by 的存在所有很随机, group by 出来的结果集除了 group by 的对象 m.name 是明确的外, 其他字段都有不确定性
    Ouyangan
        18
    Ouyangan  
       2016-06-15 18:36:41 +08:00
    @ango 我自己写业务的时候也经常纠结是不是要联表查询 ,好纠结啊 , 各有个的好处 .
    petelin
        19
    petelin  
       2016-06-15 18:41:37 +08:00 via Android
    @ango 不对啊,单表查要是写 for 循环是更慢的,因为查 sql 的次数多了啊。
    welefen
        20
    welefen  
       2016-06-15 18:42:31 +08:00
    拆成多个语句执行吧
    teemoer
        21
    teemoer  
    OP
       2016-06-15 18:42:38 +08:00
    teemoer
        22
    teemoer  
    OP
       2016-06-15 18:43:25 +08:00
    @500miles == 1 2 3 三种情况来 计算出 每种条件的得出条数的 SIZE 进行 order by. ..
    teemoer
        23
    teemoer  
    OP
       2016-06-15 18:44:02 +08:00
    @ango 好的 也是打算这样处理 = = 暂时还没思路
    petelin
        24
    petelin  
       2016-06-15 18:44:08 +08:00 via Android
    联多表一条 sql 慢一点的也不超过 10ms ,拆成多个在语言里写 for 大多 30 、 50ms 。
    teemoer
        25
    teemoer  
    OP
       2016-06-15 18:44:56 +08:00
    @murmur join 1 2 3 三种情况来 计算出 每种条件的得出条数的 SIZE 进行 order by. ..
    teemoer
        26
    teemoer  
    OP
       2016-06-15 18:46:58 +08:00
    @fireapp 抱歉 没听多大懂哦 0.0
    howeroc
        27
    howeroc  
       2016-06-15 18:48:36 +08:00 via Android
    之前 group by 查出结果要 20 多秒,然后建了一个表,每 5 分钟跑一次定时任务。然后每次查询 0.02 秒。。。
    teemoer
        28
    teemoer  
    OP
       2016-06-15 18:48:53 +08:00
    @welefen 0.0 没思路 能稍微 提示提示吗
    teemoer
        29
    teemoer  
    OP
       2016-06-15 18:50:46 +08:00
    @howeroc 0.0 我这条语句里面 group by 之前都有筛选条件.... 无法在 查询之前就把某些数据提前提取出来哇 ...
    omengye
        30
    omengye  
       2016-06-15 18:51:30 +08:00 via Android
    话说没有用视图合并的么…
    kamushin
        31
    kamushin  
       2016-06-15 18:55:48 +08:00
    @petelin 10ms 是在 DB 上, 30ms 是在应用服务器上,这能一样么。一个 DB 服务器要接入多少应用服务器。
    petelin
        32
    petelin  
       2016-06-15 18:59:48 +08:00 via Android
    @kamushin 不是啊, django 有一个 debug toolbar ,可以看 一次请求处理过程中 mysql 消耗的时间,我说的 30 秒就是数据库查询的时间,
    代码当时这么写的
    For id in 一次查询:
    Sql 语句 where userid = id

    这样就会很慢
    petelin
        33
    petelin  
       2016-06-15 19:00:04 +08:00 via Android
    fireapp
        34
    fireapp  
       2016-06-15 19:01:55 +08:00   ❤️ 1
    @teemoer 如果有两条数据
    | id | name | other |
    | :-: | :-: | :-: |
    | 1 | abc | o1 |
    | 2 | abc | o2 |
    当 select id, name, other from table group by name
    可能会得到 id = 1 的那条数据
    也可能会得到 id = 2 的那条数据
    最坏的是得到 1, abc, o2 或 2, abc, o1 这种结果
    不知道这些结果是不是你业务所需要的
    teemoer
        35
    teemoer  
    OP
       2016-06-15 19:06:55 +08:00
    @fireapp 明白了 , 3Q
    zsx
        36
    zsx  
       2016-06-15 19:10:21 +08:00
    @petelin 你这种难道不应该一次查询取出所有 ID 然后合到一条查询里进行么……
    cxbig
        37
    cxbig  
       2016-06-15 19:14:53 +08:00
    看上去是统计医生开药算提成的代码,这个行业多半是灰色地带吧。。。提供技术支持算不算犯罪?
    strwei
        38
    strwei  
       2016-06-15 19:25:16 +08:00
    索引用 sphinx 优化试试
    teemoer
        39
    teemoer  
    OP
       2016-06-15 19:33:35 +08:00
    @cxbig = = ! 我去 你直接把我吓到了
    sampeng
        40
    sampeng  
       2016-06-15 19:37:32 +08:00
    不说数据量,谈优化都是耍流氓
    sampeng
        41
    sampeng  
       2016-06-15 19:39:45 +08:00
    还有你现在查询速度是多少。。。
    sampeng
        42
    sampeng  
       2016-06-15 19:42:08 +08:00
    数据量小于 10w 级别。。执行时间就 20-30ms 。优化他干嘛?
    反之,我倒觉得,如果能修改数据库结构,改表结构。降低复杂查询。(就是反范式拉~)。比优化 sql 要简单一点。。
    icaca
        43
    icaca  
       2016-06-15 19:46:48 +08:00
    跑个执行计划看下
    icaca
        44
    icaca  
       2016-06-15 19:48:44 +08:00
    恕我才疏学浅, group by name,为何还能 select id,name?
    Infernalzero
        45
    Infernalzero  
       2016-06-15 20:21:57 +08:00
    @ixiaozhi
    join 对数据库性能消耗巨大,一般不是搞那种访问量比较少的网站或者封闭系统的话都不推荐用
    现在基本都是靠宽表冗余字段,用空间换时间的,数据库只做简单的存储功能
    而且 join 对于后续数据库做水平分割是个大坑
    lecher
        46
    lecher  
       2016-06-15 20:33:24 +08:00 via Android   ❤️ 2
    反 SQL 范式是为了便于读几十倍于写入的业务模式下,分布式部署的多机性能。在互联网领域,一台 db 要承担很多台应用服务器的数据查询负载。

    所以尽量一次一个单表查询,便于控制索引和利用缓存。
    为了减少联表查询,还会做冗余字段。保证单表数据可以满足查询结果。
    应用服务器还会根据请求打包缓存,尽量做到一个请求过来直接在缓存上取完所有数据,避免查询打到数据库上。

    做报表是另一种业务模式,查询比写入少,大部分 SQL 语句一周也跑不了几次,如果在互联网公司,喜欢取数据出来,在程序里面算,但是交付给 IT 技能很弱的单位那种项目,没人维护代码,大部分查询计算都交给数据库处理,反正一周也没有几次,直接一条 sql 语句跑完计算,算一次十几秒也无所谓,反正同一条语句一天也跑不了几次。这种项目做 sql 优化,把让代码处理数据,也不会有人维护,下一次有新业务来,看数据库表结构去拼 sql 的工作量肯定要比看代码去调用接口的工作量低,看别人写的代码和文档多累,直接看数据库表结构, sql 写出来跑就是了 。别想太多优化的事情,非要优化,开汇总表,半夜啥的做任务队列慢慢跑,看汇总就好了,报表有延时看不到实时数据也是可以接受的。
    CinderellaCiCi
        47
    CinderellaCiCi  
       2016-06-15 20:48:15 +08:00 via Android   ❤️ 1
    路过…
    好多 left join 同一个表,还有两个 like ,(⊙o⊙)…
    还有你没有提供各表数据量信息和索引信息,可能也是大家给不了你切实建议的原因之一。
    neoblackcap
        48
    neoblackcap  
       2016-06-15 20:54:28 +08:00
    @icaca MySQL 乱合并,反正我没见 PostgreSQL 能这样写还不报错
    coolcfan
        49
    coolcfan  
       2016-06-15 21:00:15 +08:00
    曾经在压力测试里见过 SQL 改动导致数据库服务器 CPU 被占满……而且 sy 特别高
    msg7086
        50
    msg7086  
       2016-06-15 22:05:54 +08:00
    有些逻辑最好在程序里完成,而不是在数据库上。
    teemoer
        51
    teemoer  
    OP
       2016-06-15 23:22:13 +08:00
    @sampeng 谢谢 学习了哈 现在我电脑上面查是 27MS 服务器上面是 1S588MS
    teemoer
        52
    teemoer  
    OP
       2016-06-15 23:26:29 +08:00
    @lecher 0.0 感谢大叔你回复的 514 个字(加上标点和回车等...) 学习了 ありがとうございます.
    teemoer
        53
    teemoer  
    OP
       2016-06-15 23:27:10 +08:00
    @CinderellaCiCi 明白 谢谢 我打算听大家的 把一部分逻辑判断丢给程序来做
    cevincheung
        54
    cevincheung  
       2016-06-15 23:27:51 +08:00
    只有我觉得这种 SQL 该上第三方工具了么?
    ichou
        55
    ichou  
       2016-06-15 23:59:42 +08:00
    除了楼上各位讨论的把逻辑放到 业务代码中去 以及 反范式问题

    联表联到这么复杂,我会去探究是不是表设计本身就不够健壮
    如果这个查询的频率很高,直接导致系统性能瓶颈的话,可以试着建一个用来优化查询的冗余表。冗余表可以根据实时性要求的不同用定时脚本来更新

    如果只是一天跑几次,还是自己人用, 1s 、 2s 的, who care ?
    teemoer
        56
    teemoer  
    OP
       2016-06-16 01:32:02 +08:00
    @ichou = = 需求变动才 需要这样去做的. 我也是半路接手项目,之前 做这个的大神没考虑到后续的这些复杂需求, 我打算该表或者分表或者放到业务代码里面去.....

    这条代码经常都会用到 非常频繁的
    halden
        57
    halden  
       2016-06-16 06:22:52 +08:00
    其实我没看明白为什么 left join mc_me 之后还要 left join mc_others 。。第一个 join 的时候不是把 doctor_id = 47 拿了过来,然后呐? anyway 我是不明白 mc_me 和 mc_others 这两个表的意义何在。。
    will0404
        58
    will0404  
       2016-06-16 08:44:54 +08:00
    @fireapp 能解释下为什么吗? 确定会得到 1 abc o2 这样的数据?
    DRcoding
        59
    DRcoding  
       2016-06-16 09:04:10 +08:00
    看到医药业的 sql 甚是亲切啊,之前写了无数统计医院业务逻辑的存储过程....

    少年,你不先看下数据库的执行计划再谈优化嘛。
    dexterz
        60
    dexterz  
       2016-06-16 11:24:48 +08:00
    join on 后面的 and 移到 where 语句里面
    teemoer
        61
    teemoer  
    OP
       2016-06-16 13:37:16 +08:00
    @DRcoding @.@..... 已经考虑抛弃这条 SQL 修改表结构了 这条 sql 没多大 优化的可能性
    teemoer
        62
    teemoer  
    OP
       2016-06-16 13:39:01 +08:00
    @halden left join A left join B A 和 B 自己的 and 条件不一样 , 后面我排序需要用到 order by A 然后再 order by B 这个 and 是属于在 A 和 B 的 不是 属于 整条 sql 的 where 的 = = !
    sampeng
        63
    sampeng  
       2016-06-16 17:35:18 +08:00
    @teemoer 那你管他干嘛。。自己折腾自己。。。
    sampeng
        64
    sampeng  
       2016-06-16 17:36:39 +08:00
    另外,你自己电脑 27ms 。服务器 1s 多了。。这本身是个问题。
    数据量一样?如果一样。。。这 1s 花了你得知道。
    teemoer
        65
    teemoer  
    OP
       2016-06-16 18:11:35 +08:00
    @sampeng 我电脑配置 :


    服务器配置 :
    lecher
        66
    lecher  
       2016-06-16 20:53:25 +08:00 via Android   ❤️ 1
    别删表字段,这种历史遗留问题的事情,原开发组成员不在的情况下,重构的试错成本是非常高的。

    如果是要优化查询速度,逻辑写到代码里面,尽量做到一条 SQL 一个单表查询,中间计算结果要么用缓存 Redis memcached 之类的保存,要么再开汇总表,只要查询能利用到索引和缓存,一个请求过来拆成几十个 SQL 的速度也可以优化到一两秒之内完成。

    另外就是有的统计可以在写入的时候就计算完写到汇总字段里面。

    至于服务器数据处理的时间,还有很多优化的配置可以用,联表最忌讳的就是没有命中索引,一旦出现全表查询,联表的计算量是乘积提升的, A 、 B 表各一万的数据,联表没命中索引的话,计算量可是一个亿,再快的 CPU 也没必要浪费在这种无谓的联表计算上。
    teemoer
        67
    teemoer  
    OP
       2016-06-23 12:17:07 +08:00
    @lecher 已经把 sql 做的事 交给 java 了 但是 性能应该还是不好, 望指点,已附加代码到 帖子
    lecher
        68
    lecher  
       2016-06-24 05:58:13 +08:00 via Android
    全取出来自己计算关系没有完全发挥数据库的性能,循环主体是求四个集合的交集。
    属于某医生的药品
    不属于某医生的药品
    属于某疾病的药品
    不属于某疾病的药品

    这四个集合有没有办法通过单表查询从数据库取出来?
    如果按医生为主体循环,那么是否属于某疾病的药品这两个集合可以缓存在内存中。减少很多数据库查询请求和循环处理。
    预处理取出这些药品的分类集合并且按药品 ID 排序之后。
    主循环的业务就可以调整成求四个集合直接的交集,这个就可以通过归并排序的思路求交集,当然 Java 内置方法也有求交际的库可以用,只要预处理好,性能应该还可以优化一下。
    teemoer
        69
    teemoer  
    OP
       2016-06-24 11:13:56 +08:00
    @lecher 明白 了解了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   4703 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 31ms · UTC 01:12 · PVG 09:12 · LAX 17:12 · JFK 20:12
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.