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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 1147 天前的主题，其中的信息可能已经有所发展或是发生改变。

在我看来，这是个很合理的需求，我在项目中没做优化，对于 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;

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

有大佬有思路的吗？

第 1 条附言 · 2021-10-09 16:12:47 +08:00

可能有些人从表结构没看出来一个隐含的需求，薪资表里每条数据都有个货币 id 外键，意味着每条薪资数据的货币可能不一样

第 2 条附言 · 2021-10-09 16:52:05 +08:00

@maplecai
@stach

我试了下，实际效果不行，比最初始的查询慢很多。

select count(*) from salary s join (

with recursive tmp(n, converted_salary) as (
(select currency_id, 1000*rate from salary s left join currency c on s.currency_id = c.id order by currency_id asc limit 1)
union all
(select currency_id, 1000*rate from tmp, lateral (select currency_id, rate from salary s left join currency c on c.id = s.currency_id where currency_id > n order by currency_id asc limit 1) sub)
)select * from tmp) as t on s.currency_id = t.n where s.salary > t.converted_salary;

这是我用的 sql，中间的 tmp recursive cte 表是用来查询 1000 块对应不同货币转换后的值，用来模拟 mysql 的 loose index scan，这个查询很快，几 ms，但是再和 salary join 就很慢了，explain 了下，主要慢在 salary 表会走顺序扫描和 has join aggregate 。

我尝试了给 salary 建索引，想让`where s.salary > t.converted_salary` 这个条件走索引，但是 postgres 还是会走顺序扫描。

强制使用索引后，postgres 会使用 nested loop，tmp 表为 inner loop，但是这样只会更慢

第 3 条附言 · 2021-10-11 10:50:54 +08:00

currency 里的 rate 是基于一个基础货币算出来的，假如采用 usd 作为基础货币，那人民币的 rate 就是 6.45 左右。

第 4 条附言 · 325 天前

两年后再看到这篇文章，非常的陌生，乍一看我都怀疑是不是我写的了。

慢慢梳理，往事又上心头。

再一看的时候，我觉得这个问题应该换一个思路解决，非常简单。

那就是每个人的薪资以一个特定的币种计算，例如美元。这个也符合实际，查询也高效。

币种只用来展示给不同的人时计算使用

47 条回复 • 2021-10-11 17:53:32 +08:00

murmur

2021-10-09 14:36:23 +08:00

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

jenlors

2021-10-09 14:37:49 +08:00

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

jenlors

2021-10-09 14:38:11 +08:00

量 -> 库

EchoUtopia

2021-10-09 14:39:42 +08:00

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

murmur

2021-10-09 14:43:21 +08:00

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

stach

2021-10-09 14:47:52 +08:00

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

maplecai

2021-10-09 14:48:45 +08:00

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

nonoyang

2021-10-09 14:49:07 +08:00

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

EchoUtopia

2021-10-09 14:50:14 +08:00

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

nonoyang

2021-10-09 14:52:10 +08:00

@nonoyang 哦不行，理解错了

EchoUtopia

2021-10-09 14:55:24 +08:00

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

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

EchoUtopia

2021-10-09 14:56:24 +08:00

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

flyingfz

2021-10-09 14:57:59 +08:00

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

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

nonoyang

2021-10-09 15:05:45 +08:00

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

cs419

2021-10-09 15:10:55 +08:00

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

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

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

MoYi123

2021-10-09 15:14:08 +08:00

你需要全量查吗? 你这个 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

2021-10-09 15:23:11 +08:00

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

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

freelancher

2021-10-09 15:38:23 +08:00

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

ccde8259

2021-10-09 15:48:46 +08:00 via iPhone

构筑索引(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

2021-10-09 15:50:48 +08:00

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

Pipecraft

2021-10-09 15:58:42 +08:00

@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

2021-10-09 16:01:17 +08:00

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

masterclock

2021-10-09 16:26:04 +08:00

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

EchoUtopia

2021-10-09 16:30:16 +08:00

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

EchoUtopia

2021-10-09 16:36:03 +08:00

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

debuggerx

2021-10-09 16:36:35 +08:00

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

machtimes

2021-10-09 16:44:51 +08:00

试试这个:
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

2021-10-09 17:01:58 +08:00

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

nekoneko

2021-10-09 17:11:28 +08:00

建个视图?

nekoneko

2021-10-09 17:15:57 +08:00

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

a719031256

2021-10-09 17:25:08 +08:00

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

cs419

2021-10-09 18:20:51 +08:00

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

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

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

kisick

2021-10-09 19:16:13 +08:00 via iPhone

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

xuanbg

2021-10-09 19:22:51 +08:00

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

abccccabc

2021-10-09 19:45:24 +08:00

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

2021-10-09 19:47:21 +08:00 via iPhone

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

pengtdyd

2021-10-09 21:44:09 +08:00

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

zlowly

2021-10-10 00:38:13 +08:00

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

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

risky

2021-10-10 11:34:30 +08:00

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

hrn961110

2021-10-10 13:40:20 +08:00

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

dingyaguang117

2021-10-10 15:43:42 +08:00 via iPhone

@maplecai 的方案靠谱

zbinlin

2021-10-10 21:02:22 +08:00

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

liuxu

2021-10-10 22:37:07 +08:00

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

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

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

第三是用其他数据库

liuxu

2021-10-10 22:49:17 +08:00

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

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

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

EchoUtopia

2021-10-11 10:50:05 +08:00

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

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

liuxu

2021-10-11 11:23:31 +08:00

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

disk

2021-10-11 17:53:32 +08:00

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