mysql 遇到最愚蠢的查询性能问题，求解决

[godall]

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. 筛选某一天涨幅在（ 3%，5%）区间的股票，且换手率在（ 5%-10%)区间之间
2. 查看后一天的股票涨跌情况。

实现语句很简单：

方案一： 第一条语句在 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 解释： **

1. simple b,key idx_1 ,key_len=3,ref=const ,rows=s5373,filterd 0.05, Extra:using where
2. simple a, key idx_2, key_len=30, ref=tb_quote.code, rows=465 ,filtered 0.21, Extra:using where

** 方案二： ** 但是如果把第一条语句的结果保存在一个表 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 解释：**

1. simple a, key idx_1, key_len=3, ref=const, rows=5365 ,filtered 100, Extra:useing where
2. simple tmp, key idx_code ,key_len=29,ref=a.f12 ,rows=1, filterd 100, Extra:using where

发现差别在：

1. 次序不同，方案二子表 tmp 在前，方案一子表 b 在后；
2. filtered 值不同，方案二是 100 ，方案一只有 0.05 ；

不知道 mysql 是怎么优化的？如果不用中间表过渡的话，应该怎么写 sql ？

[Oldletter]

试试
```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';
```
或者就只能改你的索引了

[shfan]

如果使用 python ，我觉得可以用 dataframe ，而且也可以不用数据库，用 csv 文件本地加载数据更快，你这个数据量加载数据可能大概 1 秒，运算大概几十毫秒就能完成，如果运算条件多了，这种方式速度优势就明显了

[LiaoMatt]

子查询的写法试过没

[anonydmer]

这个场景为什么要用行数据库？ 换个列数据库一下子问题都解决了

[godall]

@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' ;
````

[LonelyNoodles]

换个 influxdb 试试吧 这种类型的数据 是时序数据库擅长的

[encro]

duckdb ，Polars ，PyArrow

[sagaxu]

CBO 有时会错判，可能是数据采样失真，也可能其它原因。可以尝试用 ANALYZE TABLE ...刷新下统计信息。

[512357301]

换列式数据库吧，行式数据库数据量大了写 SQL 烧脑，各种加索引，索引也占空间啊，列存相当于全局索引，快多了

[realpg]

MYSQL 稍微复杂一丁点的查询 一定要手动指定索引
不要让他自动分析，全是最垃圾索引

[sagaxu]

@realpg Oracle 我都遇到过，之后大表查询我都手动指定索引，不再相信自动选的。Mysql 是我用过的数据库里最容易选错索引的，不但容易选错索引，它还容易选错执行顺序，还得强制指定连表顺序，连表索引，分组索引，排序索引等等。

[realpg]

@sagaxu #11
mysql 你用非常容易选错索引都是抬举他了

只要是稍微复杂的查询，你库里有多条为每条复杂查询设计好的索引，且字段有重复的（就是有不同复合索引包含同一个字段），MYSQL 好像是故意的，他一定会选择错的索引

我们这边的一套系统的血泪经验 接近 99%的错误索引选择率

[realpg]

@sagaxu #11
我这边的系统，每个表几乎都是亿行数据起步，这么大的数据规模，基本所有的 WHERE 多条件查询都设计了专用索引，要不根本操作不动
经常是表占 50GB 空间，其中 2/3 是索引占的
然后实际就是，只要你不 FORCE INDEX ，那么 MYSQL 一定会选择最垃圾的索引
几乎没有例外 我们全部测试过

[godall]

@sagaxu #11 确实如此，谢谢！

[1018ji]

嵌套，我又学了一招护城河，我感觉越来越没有人敢裁我了