一个看似简单的 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;

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

有大佬有思路的吗？

[murmur]

为什么要在 sql 里用除法呢 汇率这么几条数据存到内存就够了 到时候换算后薪资就是固定值 这不少百分百走索引

[jenlors]

如果要存到数据量可以试试创建一个虚拟列然后建索引

[jenlors]

量 -> 库

[EchoUtopia]

@murmur 汇率会变动，所以薪资也会变动

[murmur]

@EchoUtopia 但是查询得瞬间是一定的，就算是 100 条记录每次都查出来也不会太慢，更何况这东西需要实时刷新吗？

[stach]

1. 查询前, 把需要查询到的 currency_id, rate 存一份拷贝到内存中
2. 查询时, 按照一个 currency_id 一次查询, 分批查询 salary 表, 索引为 salary 字段
(根据汇率, 在程序中计算好了工资, 不要用 mysql 计算)
3. 查询完毕, 汇总所有的批次结果

[maplecai]

可以拆开查两次吧？先捞出所有的汇率，然后再构造 sql 去获取薪资，where (currency_id = 1 and salary > rate*1000) or (currency_id = 2 and salary > rate*1000)，currency_id 和 rate 替换成第一次查询拿到的结果

[nonoyang]

我看查询条件有 salary/rate > 1000，那是不是薪水可以冗余一个统一货币对应的金额？

[EchoUtopia]

@long2ice 谢谢，我去看下虚拟列，虚拟列可以引用其他表的字段吗，感觉不行呢
@murmur 货币只有 100 条，但是薪资数据很多，需求是 根据汇率转换后的薪资范围过滤，可能我没看懂你的意思

[nonoyang]

@nonoyang 哦不行，理解错了

[EchoUtopia]

@stach @maplecai @nonoyang 你们的解决方案都差不多，确实不错，我去试下，感谢。

@maplecai 的考虑更周全点，可能所有薪资的汇率就那几种，在这种情况下，确实能做到比较好的优化

[EchoUtopia]

@nonoyang 好吧，我看错你的意思了，薪水冗余货币汇率确实不合适，因为数据量大了，汇率更新的时候所有数据都要更新

[flyingfz]

没用到索引的原因， 是 where 里先运算 再比较 ， 要想办法把运算过程排除掉.

在代码里 根据 currency 的值 ，计算出一个目标值，按你的例子应该是 1000 * rate ,
把这个东西 要么插入临时表(或者某个字段)， 要么通过构造 CASE when then SQL 语句，
然后你的查询性能应该就能上来了。

[nonoyang]

@EchoUtopia 我一开始以为你薪水最初是一个统一的货币，后来一想应该不是，你可以参考下 7 楼的思路，如果用的 mybatis,<foreach>很容易构造出对应的 sql,只不过需要实际测试下效率

[cs419]

感觉没必要用实时计算呀 对效率要求不是很高

如果是实发工资 自然是以某个时刻的汇率为准
这种必然是先算好 存起来

而如果是预估的话 批处理加缓存也 ok 吧
再慢也慢不到哪去

[MoYi123]

你需要全量查吗? 你这个 sql 加上 limit 100 就没问题了吧.

INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES ('Limit (cost=3.25..8.96 rows=100 width=40) (actual time=0.049..1.219 rows=100 loops=1)');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' -> Hash Join (cost=3.25..190485.74 rows=3333333 width=40) (actual time=0.048..1.210 rows=100 loops=1)');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' Hash Cond: (s.currency_id = c.id)');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' Join Filter: ((s.salary / c.rate) > ''1000''::double precision)');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' Rows Removed by Join Filter: 2301');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' -> Seq Scan on salary s (cost=0.00..163695.00 rows=10000000 width=24) (actual time=0.005..0.599 rows=2401 loops=1)');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' -> Hash (cost=2.00..2.00 rows=100 width=16) (actual time=0.015..0.016 rows=100 loops=1)');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' Buckets: 1024 Batches: 1 Memory Usage: 13kB');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES (' -> Seq Scan on currency c (cost=0.00..2.00 rows=100 width=16) (actual time=0.002..0.006 rows=100 loops=1)');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES ('Planning Time: 0.120 ms');
INSERT INTO "MY_TABLE"("QUERY PLAN") VALUES ('Execution Time: 1.238 ms');

[EchoUtopia]

@cs419 不是实时更新，但是会定期更新，如果算好了存起来，后面更新的时候，就要更新 salary 表了，这个表数据量大了，全部更新肯定不行的。

@MoYi123 加 limit 肯定不行的，如果请求最后几页，会遍历大部分数据，或者满足数据的不到 100 条，就需要把所有数据都遍历一遍了。

[freelancher]

一个疑问：把计算放在程序里不就完了。非要在 SQL 语句里面计算么？

[ccde8259]

构筑索引(currency_id,salary)
EXPLAIN SELECT s.* from currency c left join salary s on c.id=s.currency_id and s.salary> c.id * 10000

[urnoob]

oracle 的话用 with table 构造一张临时表 内容是 select rate*1000 from c 然后和薪水表 join 查, 货币种类再多也就百来条数据
或者 select * from salary s left join (select rate*1000 newrate, id from c )c on s.currency_id = c.id where salary > newrate