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

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

2016-06-15 17:05:44 +08:00

shit = = ! 格式乱了我上图

banksiae

2016-06-15 17:45:59 +08:00

like '%w%'要扫全表的

est

2016-06-15 17:50:00 +08:00

急性上呼吸道感染

teemoer

2016-06-15 17:52:28 +08:00

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

teemoer

2016-06-15 17:52:45 +08:00

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

Infernalzero

2016-06-15 18:03:14 +08:00

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

ayumilove

2016-06-15 18:05:27 +08:00

medicine 表有多少条数据

500miles

2016-06-15 18:06:19 +08:00

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

ango

2016-06-15 18:09:27 +08:00

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

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

magicdawn

2016-06-15 18:10:56 +08:00

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

ixiaozhi

2016-06-15 18:14:26 +08:00

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

omygod

2016-06-15 18:18:03 +08:00

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

ango

2016-06-15 18:21:51 +08:00

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

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

tomczhen

2016-06-15 18:24:29 +08:00 via iPhone

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

JiShuTui

2016-06-15 18:25:47 +08:00

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

murmur

2016-06-15 18:32:15 +08:00

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

fireapp

2016-06-15 18:33:07 +08:00

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

Ouyangan

2016-06-15 18:36:41 +08:00

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

petelin

2016-06-15 18:41:37 +08:00 via Android

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

welefen

2016-06-15 18:42:31 +08:00

拆成多个语句执行吧

teemoer

2016-06-15 18:42:38 +08:00

@ayumilove 1W+

teemoer

2016-06-15 18:43:25 +08:00

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

teemoer

2016-06-15 18:44:02 +08:00

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

petelin

2016-06-15 18:44:08 +08:00 via Android

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

teemoer

2016-06-15 18:44:56 +08:00

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

teemoer

2016-06-15 18:46:58 +08:00

@fireapp 抱歉没听多大懂哦 0.0

howeroc

2016-06-15 18:48:36 +08:00 via Android

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

teemoer

2016-06-15 18:48:53 +08:00

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

teemoer

2016-06-15 18:50:46 +08:00

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

omengye

2016-06-15 18:51:30 +08:00 via Android

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

kamushin

2016-06-15 18:55:48 +08:00

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

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

2016-06-15 19:00:04 +08:00 via Android

@petelin ms

fireapp

2016-06-15 19:01:55 +08:00

@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

2016-06-15 19:06:55 +08:00

@fireapp 明白了 , 3Q

zsx

2016-06-15 19:10:21 +08:00

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

cxbig

2016-06-15 19:14:53 +08:00

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

strwei

2016-06-15 19:25:16 +08:00

索引用 sphinx 优化试试

teemoer

2016-06-15 19:33:35 +08:00

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

sampeng

2016-06-15 19:37:32 +08:00

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

sampeng

2016-06-15 19:39:45 +08:00

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

sampeng

2016-06-15 19:42:08 +08:00

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

icaca

2016-06-15 19:46:48 +08:00

跑个执行计划看下

icaca

2016-06-15 19:48:44 +08:00

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

Infernalzero

2016-06-15 20:21:57 +08:00

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

lecher

2016-06-15 20:33:24 +08:00 via Android

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

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

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

CinderellaCiCi

2016-06-15 20:48:15 +08:00 via Android

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

neoblackcap

2016-06-15 20:54:28 +08:00

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

coolcfan

2016-06-15 21:00:15 +08:00

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

msg7086

2016-06-15 22:05:54 +08:00

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

teemoer

2016-06-15 23:22:13 +08:00

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

teemoer

2016-06-15 23:26:29 +08:00

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

teemoer

2016-06-15 23:27:10 +08:00

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

cevincheung

2016-06-15 23:27:51 +08:00

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

ichou

2016-06-15 23:59:42 +08:00

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

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

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

teemoer

2016-06-16 01:32:02 +08:00

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

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

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

2016-06-16 08:44:54 +08:00

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

DRcoding

2016-06-16 09:04:10 +08:00

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

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

dexterz

2016-06-16 11:24:48 +08:00

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

teemoer

2016-06-16 13:37:16 +08:00

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

teemoer

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

2016-06-16 17:35:18 +08:00

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

sampeng

2016-06-16 17:36:39 +08:00

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

teemoer

2016-06-16 18:11:35 +08:00

@sampeng 我电脑配置 :

服务器配置 :

lecher

2016-06-16 20:53:25 +08:00 via Android

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

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

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

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

teemoer

2016-06-23 12:17:07 +08:00

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

lecher

2016-06-24 05:58:13 +08:00 via Android

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

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

teemoer

2016-06-24 11:13:56 +08:00

@lecher 明白了解了