数据库查询优化到底该怎么做？

[Xrall]

#### 配置参数前提

目前数据库是 MySQL5.7
服务器内存 16g
单表数据 1G
数据行数 200W 的数据
当年的数据截止目前为止大概在 50W
表数据是 订单和订单详情 清洗后的数据。
业务要求统计一年内产生的订单相关信息

#### SQL

```sql
select [指定的一些业务数据需要字段，含一个字段 sum 求和并除以 1000000 单位转换] from t_order_stats where is_collec = 0
where is_collect=0 and pro_removal_time between '2024-01-01 00:00:00' and '2024-09-05 23:59:59' group by order_id
-- 就这样的查询在没有缓存的情况下就需要 6818ms
-- 至于为什么没带其他条件是想的直接拿出来 代码里面全部去分类分组去求。
```

### explain 计划

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| ---- | ----------- | ------------- | ----- | ------------------------------------------------------------ | -------------------------- | ------- | ------- | ---- | ----------- |
| 1 | SIMPLE | t_order_stats | index | t_order_comprehensive_coverage_IDX, t_order_stats_order_id_IDX, t_order_trans_idx | t_order_stats_order_id_IDX | 8 | 2007971 | 50 | Using where |

`t_order_stats_order_id_IDX` 索引只有 `order_id`

#### 疑惑

is_collect 没有辨识度 只有 0/1
哪怕是加上 limit 1 也异常慢

所以这种查询到底如何去处理，因为做的是大屏幕展示的，很多的数据都是时间条件查询。

那到底要去如何优化才能处理好这类查询。

看到别个百万千万上亿查询都洒洒水。实在想不通。

至于数据库配置内存已经是升级过一轮了。加不了一点

[oneisall8955]

所以，这个表 50w 行统计出来，会有人看吗？

[Xrall]

@oneisall8955 只是数据查询出来 50W 行，代码里面还得分组，他就是要查看一年到头，每个区域有多少个用户 分别各种类型的重量有多少，去了哪里，收的地方又有多少个，收了然后处理的有多少这些数据，所以选择查询出来处理。

[aylsss]

1.建个联合索引：CREATE INDEX idx_pro_removal_order ON t_order_stats (pro_removal_time, order_id);

2.查询时把 is_collect=0 放到最后面

[threeBoy]

没格式看的头皮发麻， 最简单有效的方式就是再弄一个报表专用数据库 clickhouse 之类的

[xuanbg]

纯粹是捞的数据太多造成的，啥都不好使。再怎么优化，也就 5 秒的水平。

[Xrall]

@xuanbg 😰是的最后改了 SQL 那 id 去划分查询 十条已经正常速度了，但是捞一年还是捞不动

[lancelotfh]

先把数据捞出来，建张临时表去跑

[yh7gdiaYW]

@Xrall 这个改法是分页查询的跳 id 优化，取的数量大了就没用，解决不了你的需求

[redog]

表里有主键吗？把主键去掉，建立一个，按 is_collect ，pro_removal_time ，id 的 UNIQUE 索引，这样应该会形成聚簇索引，第一次创建时因为你已经有 200W 的记录了，会对这 200W 记录重新物理排序，所以会很慢，另外就是插入和更改 is_collect 时会慢一点，但这个数据量来看应该不会有太大感知。
好处是会按上面的顺序物理排序，这样你前面的条件一出，回表取值的速度会大大加快，捞数据是顺序读取的。
另外一种就是建一个覆盖索引，按你之前的条件，前面必须是 is_collect ，pro_removal_time ，id ，然后继续跟所需要的业务字段，那个求和的也要算进来。

[SDYY]

一个月前的数据还会修改吗，先缓存历史数据，最新数据再计算后汇总

[wxf666]

@xuanbg #25 现在千元消费级固态，不是都支持 100W 随机查找/秒 了吗？

换句话说，即使服务器内存只有几百 MB ，MySQL 完全用不了缓存，所有读写都走固态 IO ，

随机查找 50W 行，也应该 0.5 秒就能搞定？

[xuanbg]

@wxf666 读 5000 条可以百十毫秒搞定，50w 不是简单 x100 就够了。

[wxf666]

@xuanbg #32 按理说，现在平民级固态完全做得到呀？

5000 条就算全走固态 IO ，应该 0.005 秒就好？


耗时在什么地方呢？网络 IO 吗？还是 MySQL 的锁啥的？

[showB1]

@Xrall between 大范围捞数据，引擎判断大量的回表很慢就自己优化不走索引了

[showB1]

@wxf666 还是你牛逼，楼主快把数据存固态硬盘里。。。。。。。

[Xrall]

@showB1 #35 已经选择放弃 MySQL 干这种事情了。拿 ES 应付应付 只不过 es 查询也有其他问题，哎最主要还是没办法升级设备用更专业的来处理这个了

[wxf666]

@showB1 #35 你的意思是，虽然现在消费级固态，能做到 100W 随机读写 / 秒，

但 MySQL 没能完全利用好这个资源，所以做不到？


请教一下，主要是什么方面耗时过多了呢？

- 网络 IO ？
- 等待锁？
- ……？

[showB1]

@Xrall 用 ck 、doris ，数仓随便找一个也比 es 对口啊

[Xrall]

@showB1 #38 谢谢提出这些方案。可惜 es 有现成的，其他的都没内存上了。真的就是申请设备异常难，我也去查询过文档最低都要 4G 内存吧。实在没有余粮了。。

[dode]

如果没有固态硬盘，换成固态硬盘基本可以解决问题