mysql 8.0 Windows 版本。
tb_quote (
op_date ,
code ,
price ,
ratio ,
hsl ,
index unique idx_1 (op_date,code) ,
index idx_2(code)
)
每个交易日 6000 条,一年几百万条规模。
实现语句很简单:
方案一: 第一条语句在 1 秒内返回结果:(20 条记录)
select code from tb_quote where op_date='yyyy-mm-dd' and ratio >3 and ratio <5 and hsl>5 and hsl <10
嵌套第二条后:
select a.* from tb_quote a join (
select code from tb_quote b where op_date='yyyy-mm-d1' and ratio >3 and ratio <5 and hsl>5 and hsl <10 ) t
on a.code=t.code and a.op_date='yyyy-mm-d2'
执行时间就超过 1 分钟!
** 查看 mysql 解释: **
** 方案二: ** 但是如果把第一条语句的结果保存在一个表 tmp 里面,并且 code 建立普通索引以后,变成:
select a.* from tb_quote a join (
select code from tmp ) t
on a.code=t.codeand a.op_date='yyyy-mm-d3'
执行时间不超过 1 秒!!!
** 查看 mysql 解释:**
发现差别在:
不知道 mysql 是怎么优化的?如果不用中间表过渡的话,应该怎么写 sql ?
1
Oldletter 4 天前
试试
```sql SELECT a.* FROM tb_quote a WHERE a.op_date = 'yyyy-mm-d2' AND EXISTS ( SELECT 1 FROM tb_quote b WHERE b.op_date = 'yyyy-mm-d1' AND b.ratio > 3 AND b.ratio < 5 AND b.hsl > 5 AND b.hsl < 10 AND b.code = a.code ); ``` 或者写 cte ```sql WITH filtered_codes AS ( SELECT code FROM tb_quote WHERE op_date = 'yyyy-mm-d1' AND ratio > 3 AND ratio < 5 AND hsl > 5 AND hsl < 10 ) SELECT a.* FROM tb_quote a JOIN filtered_codes t ON a.code = t.code WHERE a.op_date = 'yyyy-mm-d2'; ``` 或者就只能改你的索引了 |
2
shfan 4 天前
如果使用 python ,我觉得可以用 dataframe ,而且也可以不用数据库,用 csv 文件本地加载数据更快,你这个数据量加载数据可能大概 1 秒,运算大概几十毫秒就能完成,如果运算条件多了,这种方式速度优势就明显了
|
3
LiaoMatt 4 天前
子查询的写法试过没
|
4
anonydmer 4 天前
这个场景为什么要用行数据库? 换个列数据库一下子问题都解决了
|
5
godall OP @Oldletter #1 谢谢,你的方案一、方案二查询速度大概 45 几秒,跟我的区别不大。还是我的方案二速度快。
根据 mysql 的执行计划最后发现大表的索引用错了(用了 code 索引)所以慢,强制指定索引(force index)后就飞快: ```` SELECT a.* FROM tb_quote a FORCE INDEX(idx_1) JOIN ( SELECT code FROM tb_quote b WHERE op_date='2025-01-14' AND ratio BETWEEN 3 AND 5 AND hsl BETWEEN 5 AND 10 ) t ON a.code=t.code AND a.op_date='2025-01-10' ; ```` |
6
LonelyNoodles 4 天前
换个 influxdb 试试吧 这种类型的数据 是时序数据库擅长的
|
7
encro 4 天前
duckdb ,Polars ,PyArrow
|
8
sagaxu 4 天前
CBO 有时会错判,可能是数据采样失真,也可能其它原因。可以尝试用 ANALYZE TABLE ...刷新下统计信息。
|
9
512357301 4 天前 via Android
换列式数据库吧,行式数据库数据量大了写 SQL 烧脑,各种加索引,索引也占空间啊,列存相当于全局索引,快多了
|
10
realpg 4 天前
MYSQL 稍微复杂一丁点的查询 一定要手动指定索引
不要让他自动分析,全是最垃圾索引 |
11
sagaxu 3 天前
@realpg Oracle 我都遇到过,之后大表查询我都手动指定索引,不再相信自动选的。Mysql 是我用过的数据库里最容易选错索引的,不但容易选错索引,它还容易选错执行顺序,还得强制指定连表顺序,连表索引,分组索引,排序索引等等。
|
12
realpg 3 天前
@sagaxu #11
mysql 你用非常容易选错索引都是抬举他了 只要是稍微复杂的查询,你库里有多条为每条复杂查询设计好的索引,且字段有重复的(就是有不同复合索引包含同一个字段),MYSQL 好像是故意的,他一定会选择错的索引 我们这边的一套系统的血泪经验 接近 99%的错误索引选择率 |
13
realpg 3 天前
@sagaxu #11
我这边的系统,每个表几乎都是亿行数据起步,这么大的数据规模,基本所有的 WHERE 多条件查询都设计了专用索引,要不根本操作不动 经常是表占 50GB 空间,其中 2/3 是索引占的 然后实际就是,只要你不 FORCE INDEX ,那么 MYSQL 一定会选择最垃圾的索引 几乎没有例外 我们全部测试过 |
15
1018ji 3 天前
嵌套,我又学了一招护城河,我感觉越来越没有人敢裁我了
|