一个看似简单的 sql 优化问题，但是把我给难住了

[EchoUtopia]

在我看来，这是个很合理的需求，我在项目中没做优化，对于 ToB 项目的数据量实测完全没问题，但是对于数据量大的情况下，确实需要优化，可是我想不出解决方案。

以下例子基于 postgres，但是这个问题应该不限于 postgres 。

有一张货币表 c： create table currency (id bigserial not null primary key, rate double precision not null);

造数据： insert into currency (rate) select random()*10 from generate_serial(1, 100);

有一张薪资表 s： create table salary(id bigserial not null primary key, salary double precision not null, currency_id bigint not null references currency(id));

造 1000w 条数据： insert into salary(salary, currency_id) select random()*1000, random()*99::int+1 from generate_series(1, 10000000);

薪资表通过 currency_id 外键引用 c，c 里面记录了每个货币的汇率，这个汇率会定期更新，所以不方便将汇率直接写入 salary 表。

当过滤薪资条件时，会使用 SQL： explain analyze select * from salary s left join currency c on s.currency_id = c.id where salary/rate > 1000;

这个查询不会使用索引，会很慢，怎么优化？

有大佬有思路的吗？

[Pipecraft]

@urnoob 跟我想的一样。
```
select
*
from
salary s
left join
(
select
id,
rate * 1000 as min_salary
from
currency
)
as c
on s.currency_id = c.id
where
salary > min_salary;
```

[EchoUtopia]

@urnoob @freelancher @ccde8259 薪资表里不同数据的货币不一样
@ccde8259 currency left join salary ?, s.salary > c.id ?

[masterclock]

总感觉怪怪的，用 当时的金额 和 现在的汇率 来计算

[EchoUtopia]

@masterclock 实际需求并不是薪资，我只是用薪资举例而已

[EchoUtopia]

@masterclock 另外我想了下，薪资也没问题吧，比如公司总部在美国，公司有的部门在成都，有的在东京，那总部发给成都的工资应该是以人民币计的固定薪资，东京类似，但是总部那边各种统计、计算啥的都是以美元计的，所以总部想统计以美元计的所有工作人员的薪资范围的人，这个时候应该是要把不同国家的薪资换算成美元。

[debuggerx]

薪资表加个字段，更新汇率的时候开启异步任务分批更新相应币种薪资的基准货币值呢？

[machtimes]

试试这个:
explain analyze
select t.salary,t.currency_id,t.rate,t.new_salary from
(
select s.salary,s.currency_id,coalesce(c.rate,1) as rate,s.salary/coalesce(c.rate,1) as new_salary
from salary s
left join currency c
on s.currency_id = c.id
) t
where t.new_salary > 1000
;

[EchoUtopia]

@machtimes 耗时是原始查询 1 倍多
@debuggerx 应该可以，但是我想最好是把这个字段单独拆一个表出来，只有两个字段，salary_id, converted_salary，这样更新的时候需要写的数据页就少了，实际薪资表会有很多其他字段。另外一个问题就是更新数据的时候会上排他锁，所有数据都要更新一遍，不知道对查询有多大影响。

[nekoneko]

建个视图?

[nekoneko]

@nekoneko #29
因为汇率是定期更新,所以不会更新得很频繁
可以给工资表再冗余一个字段,更新汇率表的时候同时更新那个冗余的字段

[a719031256]

计算那一步拆开应该比较好

[cs419]

批量更新数据估计比较耗时

很久之前用 sprak 跑批 几千万的数据 分分钟就跑完了
数据直接写到新的表里 再登记下新表名
查数据前 先查表名

你说的数据量大是多大 几十亿 ？

[kisick]

用货币表里的最大 rate*1000 和最小 rate*1000 来过滤一下

[xuanbg]

salary/rate > 1000 是啥意思？汇率不一样，这个标准完全没有啊，作为查询条件的意义何在？

[abccccabc]

explain analyze select * from salary s left join currency c on s.currency_id = c.id where salary/rate > 1000;　

改为

explain analyze select * from salary s left join currency c on s.currency_id = c.id where salary > 1000 　* rate;

这样应该会走 salary 索引吧？

[ccde8259]

@EchoUtopia currency 是小表，用 currency 对应的 rate x 10000 换算成 salary 的计算量也小。算出来 left join 回去可以用跑索引 currency_id 等值再走 salary 的范围扫描完成。

[pengtdyd]

mysql 500w 性能就明显下降，我觉的可以考虑把数据同步到其他数据库中做，比如：es

[zlowly]

直觉上，传统关系型数据库对这种 sql 是无法应对实时查询的。
当然如果货币种类十分有限，通过分区的思路是可以改善查询，比较麻烦而且仍然不足以应付查询性能。
因为可以看出，currency 的 rate 变化，足以导致整个结果完全不同，极端情况下，全表扫描是无法避免的，这不是什么简单优化可以应对的。这种需求，毕竟 rate 变动应该也是不频繁的，通常都只是转为定期报表加工，并不需要特别考虑 sql 和结构优化。

如果类似需求有实时性要求而且 rate 时刻动态彼变化，请考虑传统关系型数据库以外的方案，例如分布式数据库、分布式计算等。

[risky]

感觉表结构可以改一下, 存上合同上定下的工资金额与币种, 对合同币种汇率, 实发金额 /币种
对合同币种汇率可以放在 currency 表的连表里单独管理带有历史记录的汇率关联进 salary

[hrn961110]

方案一，用小表驱动大表，left join 改成 right join 试试。
方案二，对 salary 数据分段。缓存到内存里，在内存里做操作。