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

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

有大佬有思路的吗？

[dingyaguang117]

@maplecai 的方案靠谱

[zbinlin]

这里能用到索引的地方不多，建个 MATERIALIZED VIEW 试试？

[liuxu]

where 条件用了>最好的情况就是 range 类型查询，要是结果基数太大优化器就直接让全表扫描了，也就是不会用你的索引，想用的话可以添加 FORCE INDEX(idx)

能优化的方案就 3 个，首先是改写 sql，where 里面不要有计算，不然没法用索引（当然这个是 sql 预处理问题，未来或某些再发行版本可能优化为可以使用）。然后建覆盖索引，内存给够，扫内存而不是扫磁盘，包括 tmp 表内存限制给够，不要用到磁盘，参见 tmp_table_size 相关参数

第二是分表，这个是对于你这个需求是建议做的，简单合理又有效，我也常用，不过要选好 hash key，你这个表其实缺 user_id，一般 hash key 选它就行。你拆成 100 个表，每个表也就 10 万行，然后代码用异步框架 100 条 sql 一起执行，最后业务里面合并就好了。当然如果这个表有其他业务在用的话，建议同步到从表然后做这件事情

第三是用其他数据库

[liuxu]

@liuxu 这个表业务常用还有查询某个用户的工资，而不只是>1000，这样就会出现 where 条件是 user_id=xxx

我知道有些公司甚至不让用 join，我老东家曾经就有这个要求，所以你这个查询一个用户的工资会分成 2 个简单 sql 语句
1. 查询工资表
2. 从工资表拿到汇率 id，然后去汇率表汇率
最后业务代码里面合并计算

所以你最后的优化方案还是得看公司具体规定，能不能分表，换数据库

[EchoUtopia]

@cs419 谢谢，数据写到新表这个思路不错。我做的 tob 项目数据量不大，我只是想到数据量大了的解决方案。

@kisick 这个没啥用，最大的上亿，最小的小数点后 4 位
@xuanbg 这里的汇率是基于一个基础货币算出来的，假如采用 usd 作为基础货币，那人民币的 rate 就是 6.45 左右。
@abccccabc 一样的，对于每条 salary 数据都要计算，走不了索引
@ccde8259 我在 append 已经这样做了，性能更差
@zlowly 谢谢分享
@zbinlin materialized view 应该和直接更新 salary 全部数据是一样的
@liuxu 谢谢分享，where 里有计算也是可以走索引的，postgres 有表达式索引（ mysql 好像叫函数索引），但是要求是计算结果是固定的。分表这个建议不错，是不是可以按币种分表。

[liuxu]

@EchoUtopia 哦看到了，mysql 8 支持，学习了，还活在 mysql5.7 里
安币种也可以，不过效果不好，像人民币和韩币的 2 个工资表大小肯定不一样，人民币的还是有你这个性能问题

[disk]

千万级感觉原来的性能也可以了，上亿可以考虑列式数据库。