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

[teemoer]

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 大神多多指教

以下是 优化代替这条 sql 的写法......把数据库做的事儿交给了 java 做, 有可以优化的地方请 V 友指正.谢谢哈

项目没用 java8 不能用流 唉 没办法 还有任何能够优化的地方吗,诸位 小伙伴 大伙伴 老伙伴?

[teemoer]

shit = = ! 格式乱了 我上图

[banksiae]

like '%w%'要扫全表的

[est]

急性上呼吸道感染

[teemoer]

@banksiae = = 先 and type=0 再 like 呢? 或者 有比 like 更好的办法吗

[teemoer]

@est = = 看到你头像我忍不住笑出声

[Infernalzero]

没有索引如何谈优化，况且看到那么多 join 而且还同时 group by order by 以及 like '%'的要想不是慢查询也难

[ayumilove]

medicine 表 有多少条数据

[500miles]

对同一张表 join 三次, 三次的条件还互补, 看不懂 看不懂

[ango]

不要为一条 sql 而一条 sql ，注意 IO 瓶颈，
应该在语言层来做一些逻辑处理，这样顶多消耗一点 CPU 性能。


我们公司不允许 联表 查询。不到万不得已，不允许使用 联表 查询。

[magicdawn]

对啊...
left join 同一张表啊

[ixiaozhi]

@ango 不允许 联表 查询的理由是什么，有点儿不理解

[omygod]

抛开数据量，谈 left join 的个数就是耍流氓

[ango]

@ixiaozhi
说是联表会产生临时表，导致 db 压力大。

老人说是总监要求的，我做为新人也只能执行了。
其实我也不能理解，简单的索引联表复合查询能有什么压力，现在搞得都得在 PHP 语言层做数据组合。

[tomczhen]

又是 jion ，又是 like ，又是 or ，数据量上来光靠修改 sql 语句想提高查询速度是不可能的。

[JiShuTui]

分拆成多个查询，合理利用缓存

[murmur]

自己 join 自己是什么情况呢。。。看到 like 基本就确定没法优化了

[fireapp]

这条 sql 有点毛病, 查询的结果集因为 group by 的存在所有很随机, group by 出来的结果集除了 group by 的对象 m.name 是明确的外, 其他字段都有不确定性

[Ouyangan]

@ango 我自己写业务的时候也经常纠结是不是要联表查询 ,好纠结啊 , 各有个的好处 .

[petelin]

@ango 不对啊，单表查要是写 for 循环是更慢的，因为查 sql 的次数多了啊。

[welefen]

拆成多个语句执行吧

[teemoer]

@ayumilove 1W+

[teemoer]

@500miles == 1 2 3 三种情况来 计算出 每种条件的得出条数的 SIZE 进行 order by. ..

[teemoer]

@ango 好的 也是打算这样处理 = = 暂时还没思路

[petelin]

联多表一条 sql 慢一点的也不超过 10ms ，拆成多个在语言里写 for 大多 30 、 50ms 。

[teemoer]

@murmur join 1 2 3 三种情况来 计算出 每种条件的得出条数的 SIZE 进行 order by. ..

[teemoer]

@fireapp 抱歉 没听多大懂哦 0.0

[howeroc]

之前 group by 查出结果要 20 多秒，然后建了一个表，每 5 分钟跑一次定时任务。然后每次查询 0.02 秒。。。

[teemoer]

@welefen 0.0 没思路 能稍微 提示提示吗

[teemoer]

@howeroc 0.0 我这条语句里面 group by 之前都有筛选条件.... 无法在 查询之前就把某些数据提前提取出来哇 ...

[omengye]

话说没有用视图合并的么…

[kamushin]

@petelin 10ms 是在 DB 上， 30ms 是在应用服务器上，这能一样么。一个 DB 服务器要接入多少应用服务器。

[petelin]

@kamushin 不是啊， django 有一个 debug toolbar ,可以看 一次请求处理过程中 mysql 消耗的时间，我说的 30 秒就是数据库查询的时间，
代码当时这么写的
For id in 一次查询:
Sql 语句 where userid = id

这样就会很慢

[petelin]

@petelin ms

[fireapp]

@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]

@fireapp 明白了 , 3Q

[zsx]

@petelin 你这种难道不应该一次查询取出所有 ID 然后合到一条查询里进行么……

[cxbig]

看上去是统计医生开药算提成的代码，这个行业多半是灰色地带吧。。。提供技术支持算不算犯罪？

[strwei]

索引用 sphinx 优化试试

[teemoer]

@cxbig = = ! 我去 你直接把我吓到了

[sampeng]

不说数据量，谈优化都是耍流氓

[sampeng]

还有你现在查询速度是多少。。。

[sampeng]

数据量小于 10w 级别。。执行时间就 20-30ms 。优化他干嘛？
反之，我倒觉得，如果能修改数据库结构，改表结构。降低复杂查询。（就是反范式拉~）。比优化 sql 要简单一点。。

[icaca]

跑个执行计划看下

[icaca]

恕我才疏学浅， group by name,为何还能 select id,name?

[Infernalzero]

@ixiaozhi
join 对数据库性能消耗巨大，一般不是搞那种访问量比较少的网站或者封闭系统的话都不推荐用
现在基本都是靠宽表冗余字段，用空间换时间的，数据库只做简单的存储功能
而且 join 对于后续数据库做水平分割是个大坑

[lecher]

反 SQL 范式是为了便于读几十倍于写入的业务模式下，分布式部署的多机性能。在互联网领域，一台 db 要承担很多台应用服务器的数据查询负载。

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

做报表是另一种业务模式，查询比写入少，大部分 SQL 语句一周也跑不了几次，如果在互联网公司，喜欢取数据出来，在程序里面算，但是交付给 IT 技能很弱的单位那种项目，没人维护代码，大部分查询计算都交给数据库处理，反正一周也没有几次，直接一条 sql 语句跑完计算，算一次十几秒也无所谓，反正同一条语句一天也跑不了几次。这种项目做 sql 优化，把让代码处理数据，也不会有人维护，下一次有新业务来，看数据库表结构去拼 sql 的工作量肯定要比看代码去调用接口的工作量低，看别人写的代码和文档多累，直接看数据库表结构， sql 写出来跑就是了 。别想太多优化的事情，非要优化，开汇总表，半夜啥的做任务队列慢慢跑，看汇总就好了，报表有延时看不到实时数据也是可以接受的。

[CinderellaCiCi]

路过…
好多 left join 同一个表，还有两个 like ，(⊙o⊙)…
还有你没有提供各表数据量信息和索引信息，可能也是大家给不了你切实建议的原因之一。

[neoblackcap]

@icaca MySQL 乱合并，反正我没见 PostgreSQL 能这样写还不报错

[coolcfan]

曾经在压力测试里见过 SQL 改动导致数据库服务器 CPU 被占满……而且 sy 特别高

[msg7086]

有些逻辑最好在程序里完成，而不是在数据库上。

[teemoer]

@sampeng 谢谢 学习了哈 现在我电脑上面查是 27MS 服务器上面是 1S588MS

[teemoer]

@lecher 0.0 感谢大叔你回复的 514 个字(加上标点和回车等...) 学习了 ありがとうございます.

[teemoer]

@CinderellaCiCi 明白 谢谢 我打算听大家的 把一部分逻辑判断丢给程序来做

[cevincheung]

只有我觉得这种 SQL 该上第三方工具了么？

[ichou]

除了楼上各位讨论的把逻辑放到 业务代码中去 以及 反范式问题

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

如果只是一天跑几次，还是自己人用， 1s 、 2s 的， who care ？

[teemoer]

@ichou = = 需求变动才 需要这样去做的. 我也是半路接手项目,之前 做这个的大神没考虑到后续的这些复杂需求, 我打算该表或者分表或者放到业务代码里面去.....

这条代码经常都会用到 非常频繁的

[halden]

其实我没看明白为什么 left join mc_me 之后还要 left join mc_others 。。第一个 join 的时候不是把 doctor_id = 47 拿了过来，然后呐？ anyway 我是不明白 mc_me 和 mc_others 这两个表的意义何在。。

[will0404]

@fireapp 能解释下为什么吗？ 确定会得到 1 abc o2 这样的数据？

[DRcoding]

看到医药业的 sql 甚是亲切啊，之前写了无数统计医院业务逻辑的存储过程....

少年，你不先看下数据库的执行计划再谈优化嘛。

[dexterz]

join on 后面的 and 移到 where 语句里面

[teemoer]

@DRcoding @.@..... 已经考虑抛弃这条 SQL 修改表结构了 这条 sql 没多大 优化的可能性

[teemoer]

@halden left join A left join B A 和 B 自己的 and 条件不一样 , 后面我排序需要用到 order by A 然后再 order by B 这个 and 是属于在 A 和 B 的 不是 属于 整条 sql 的 where 的 = = !

[sampeng]

@teemoer 那你管他干嘛。。自己折腾自己。。。

[sampeng]

另外，你自己电脑 27ms 。服务器 1s 多了。。这本身是个问题。
数据量一样？如果一样。。。这 1s 花了你得知道。

[teemoer]

@sampeng 我电脑配置 :


服务器配置 :

[lecher]

别删表字段，这种历史遗留问题的事情，原开发组成员不在的情况下，重构的试错成本是非常高的。

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

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

至于服务器数据处理的时间，还有很多优化的配置可以用，联表最忌讳的就是没有命中索引，一旦出现全表查询，联表的计算量是乘积提升的， A 、 B 表各一万的数据，联表没命中索引的话，计算量可是一个亿，再快的 CPU 也没必要浪费在这种无谓的联表计算上。

[teemoer]

@lecher 已经把 sql 做的事 交给 java 了 但是 性能应该还是不好, 望指点,已附加代码到 帖子

[lecher]

全取出来自己计算关系没有完全发挥数据库的性能，循环主体是求四个集合的交集。
属于某医生的药品
不属于某医生的药品
属于某疾病的药品
不属于某疾病的药品

这四个集合有没有办法通过单表查询从数据库取出来？
如果按医生为主体循环，那么是否属于某疾病的药品这两个集合可以缓存在内存中。减少很多数据库查询请求和循环处理。
预处理取出这些药品的分类集合并且按药品 ID 排序之后。
主循环的业务就可以调整成求四个集合直接的交集，这个就可以通过归并排序的思路求交集，当然 Java 内置方法也有求交际的库可以用，只要预处理好，性能应该还可以优化一下。

[teemoer]

@lecher 明白 了解了