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
teemoer OP shit = = ! 格式乱了 我上图
|
2
banksiae 2016-06-15 17:45:59 +08:00
like '%w%'要扫全表的
|
3
est 2016-06-15 17:50:00 +08:00
急性上呼吸道感染
|
6
Infernalzero 2016-06-15 18:03:14 +08:00
没有索引如何谈优化,况且看到那么多 join 而且还同时 group by order by 以及 like '%'的要想不是慢查询也难
|
7
ayumilove 2016-06-15 18:05:27 +08:00
medicine 表 有多少条数据
|
8
500miles 2016-06-15 18:06:19 +08:00
对同一张表 join 三次, 三次的条件还互补, 看不懂 看不懂
|
9
ango 2016-06-15 18:09:27 +08:00 1
不要为一条 sql 而一条 sql ,注意 IO 瓶颈,
应该在语言层来做一些逻辑处理,这样顶多消耗一点 CPU 性能。 我们公司不允许 联表 查询。不到万不得已,不允许使用 联表 查询。 |
10
magicdawn 2016-06-15 18:10:56 +08:00
对啊...
left join 同一张表啊 |
12
omygod 2016-06-15 18:18:03 +08:00
抛开数据量,谈 left join 的个数就是耍流氓
|
13
ango 2016-06-15 18:21:51 +08:00
@ixiaozhi
说是联表会产生临时表,导致 db 压力大。 老人说是总监要求的,我做为新人也只能执行了。 其实我也不能理解,简单的索引联表复合查询能有什么压力,现在搞得都得在 PHP 语言层做数据组合。 |
14
tomczhen 2016-06-15 18:24:29 +08:00 via iPhone
又是 jion ,又是 like ,又是 or ,数据量上来光靠修改 sql 语句想提高查询速度是不可能的。
|
15
JiShuTui 2016-06-15 18:25:47 +08:00
分拆成多个查询,合理利用缓存
|
16
murmur 2016-06-15 18:32:15 +08:00
自己 join 自己是什么情况呢。。。看到 like 基本就确定没法优化了
|
17
fireapp 2016-06-15 18:33:07 +08:00
这条 sql 有点毛病, 查询的结果集因为 group by 的存在所有很随机, group by 出来的结果集除了 group by 的对象 m.name 是明确的外, 其他字段都有不确定性
|
20
welefen 2016-06-15 18:42:31 +08:00
拆成多个语句执行吧
|
22
teemoer OP @500miles == 1 2 3 三种情况来 计算出 每种条件的得出条数的 SIZE 进行 order by. ..
|
24
petelin 2016-06-15 18:44:08 +08:00 via Android
联多表一条 sql 慢一点的也不超过 10ms ,拆成多个在语言里写 for 大多 30 、 50ms 。
|
25
teemoer OP @murmur join 1 2 3 三种情况来 计算出 每种条件的得出条数的 SIZE 进行 order by. ..
|
27
howeroc 2016-06-15 18:48:36 +08:00 via Android
之前 group by 查出结果要 20 多秒,然后建了一个表,每 5 分钟跑一次定时任务。然后每次查询 0.02 秒。。。
|
29
teemoer OP @howeroc 0.0 我这条语句里面 group by 之前都有筛选条件.... 无法在 查询之前就把某些数据提前提取出来哇 ...
|
30
omengye 2016-06-15 18:51:30 +08:00 via Android
话说没有用视图合并的么…
|
31
kamushin 2016-06-15 18:55:48 +08:00
@petelin 10ms 是在 DB 上, 30ms 是在应用服务器上,这能一样么。一个 DB 服务器要接入多少应用服务器。
|
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 这样就会很慢 |
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 这种结果 不知道这些结果是不是你业务所需要的 |
37
cxbig 2016-06-15 19:14:53 +08:00
看上去是统计医生开药算提成的代码,这个行业多半是灰色地带吧。。。提供技术支持算不算犯罪?
|
38
strwei 2016-06-15 19:25:16 +08:00
索引用 sphinx 优化试试
|
40
sampeng 2016-06-15 19:37:32 +08:00
不说数据量,谈优化都是耍流氓
|
41
sampeng 2016-06-15 19:39:45 +08:00
还有你现在查询速度是多少。。。
|
42
sampeng 2016-06-15 19:42:08 +08:00
数据量小于 10w 级别。。执行时间就 20-30ms 。优化他干嘛?
反之,我倒觉得,如果能修改数据库结构,改表结构。降低复杂查询。(就是反范式拉~)。比优化 sql 要简单一点。。 |
43
icaca 2016-06-15 19:46:48 +08:00
跑个执行计划看下
|
44
icaca 2016-06-15 19:48:44 +08:00
恕我才疏学浅, group by name,为何还能 select id,name?
|
45
Infernalzero 2016-06-15 20:21:57 +08:00
@ixiaozhi
join 对数据库性能消耗巨大,一般不是搞那种访问量比较少的网站或者封闭系统的话都不推荐用 现在基本都是靠宽表冗余字段,用空间换时间的,数据库只做简单的存储功能 而且 join 对于后续数据库做水平分割是个大坑 |
46
lecher 2016-06-15 20:33:24 +08:00 via Android 2
反 SQL 范式是为了便于读几十倍于写入的业务模式下,分布式部署的多机性能。在互联网领域,一台 db 要承担很多台应用服务器的数据查询负载。
所以尽量一次一个单表查询,便于控制索引和利用缓存。 为了减少联表查询,还会做冗余字段。保证单表数据可以满足查询结果。 应用服务器还会根据请求打包缓存,尽量做到一个请求过来直接在缓存上取完所有数据,避免查询打到数据库上。 做报表是另一种业务模式,查询比写入少,大部分 SQL 语句一周也跑不了几次,如果在互联网公司,喜欢取数据出来,在程序里面算,但是交付给 IT 技能很弱的单位那种项目,没人维护代码,大部分查询计算都交给数据库处理,反正一周也没有几次,直接一条 sql 语句跑完计算,算一次十几秒也无所谓,反正同一条语句一天也跑不了几次。这种项目做 sql 优化,把让代码处理数据,也不会有人维护,下一次有新业务来,看数据库表结构去拼 sql 的工作量肯定要比看代码去调用接口的工作量低,看别人写的代码和文档多累,直接看数据库表结构, sql 写出来跑就是了 。别想太多优化的事情,非要优化,开汇总表,半夜啥的做任务队列慢慢跑,看汇总就好了,报表有延时看不到实时数据也是可以接受的。 |
47
CinderellaCiCi 2016-06-15 20:48:15 +08:00 via Android 1
路过…
好多 left join 同一个表,还有两个 like ,(⊙o⊙)… 还有你没有提供各表数据量信息和索引信息,可能也是大家给不了你切实建议的原因之一。 |
48
neoblackcap 2016-06-15 20:54:28 +08:00
@icaca MySQL 乱合并,反正我没见 PostgreSQL 能这样写还不报错
|
49
coolcfan 2016-06-15 21:00:15 +08:00
曾经在压力测试里见过 SQL 改动导致数据库服务器 CPU 被占满……而且 sy 特别高
|
50
msg7086 2016-06-15 22:05:54 +08:00
有些逻辑最好在程序里完成,而不是在数据库上。
|
53
teemoer OP @CinderellaCiCi 明白 谢谢 我打算听大家的 把一部分逻辑判断丢给程序来做
|
54
cevincheung 2016-06-15 23:27:51 +08:00
只有我觉得这种 SQL 该上第三方工具了么?
|
55
ichou 2016-06-15 23:59:42 +08:00
除了楼上各位讨论的把逻辑放到 业务代码中去 以及 反范式问题
联表联到这么复杂,我会去探究是不是表设计本身就不够健壮 如果这个查询的频率很高,直接导致系统性能瓶颈的话,可以试着建一个用来优化查询的冗余表。冗余表可以根据实时性要求的不同用定时脚本来更新 如果只是一天跑几次,还是自己人用, 1s 、 2s 的, who care ? |
56
teemoer OP @ichou = = 需求变动才 需要这样去做的. 我也是半路接手项目,之前 做这个的大神没考虑到后续的这些复杂需求, 我打算该表或者分表或者放到业务代码里面去.....
这条代码经常都会用到 非常频繁的 |
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 这两个表的意义何在。。
|
59
DRcoding 2016-06-16 09:04:10 +08:00
看到医药业的 sql 甚是亲切啊,之前写了无数统计医院业务逻辑的存储过程....
少年,你不先看下数据库的执行计划再谈优化嘛。 |
60
dexterz 2016-06-16 11:24:48 +08:00
join on 后面的 and 移到 where 语句里面
|
62
teemoer OP @halden left join A left join B A 和 B 自己的 and 条件不一样 , 后面我排序需要用到 order by A 然后再 order by B 这个 and 是属于在 A 和 B 的 不是 属于 整条 sql 的 where 的 = = !
|
64
sampeng 2016-06-16 17:36:39 +08:00
另外,你自己电脑 27ms 。服务器 1s 多了。。这本身是个问题。
数据量一样?如果一样。。。这 1s 花了你得知道。 |
66
lecher 2016-06-16 20:53:25 +08:00 via Android 1
别删表字段,这种历史遗留问题的事情,原开发组成员不在的情况下,重构的试错成本是非常高的。
如果是要优化查询速度,逻辑写到代码里面,尽量做到一条 SQL 一个单表查询,中间计算结果要么用缓存 Redis memcached 之类的保存,要么再开汇总表,只要查询能利用到索引和缓存,一个请求过来拆成几十个 SQL 的速度也可以优化到一两秒之内完成。 另外就是有的统计可以在写入的时候就计算完写到汇总字段里面。 至于服务器数据处理的时间,还有很多优化的配置可以用,联表最忌讳的就是没有命中索引,一旦出现全表查询,联表的计算量是乘积提升的, A 、 B 表各一万的数据,联表没命中索引的话,计算量可是一个亿,再快的 CPU 也没必要浪费在这种无谓的联表计算上。 |
68
lecher 2016-06-24 05:58:13 +08:00 via Android
全取出来自己计算关系没有完全发挥数据库的性能,循环主体是求四个集合的交集。
属于某医生的药品 不属于某医生的药品 属于某疾病的药品 不属于某疾病的药品 这四个集合有没有办法通过单表查询从数据库取出来? 如果按医生为主体循环,那么是否属于某疾病的药品这两个集合可以缓存在内存中。减少很多数据库查询请求和循环处理。 预处理取出这些药品的分类集合并且按药品 ID 排序之后。 主循环的业务就可以调整成求四个集合直接的交集,这个就可以通过归并排序的思路求交集,当然 Java 内置方法也有求交际的库可以用,只要预处理好,性能应该还可以优化一下。 |