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

#### 配置参数前提

目前数据库是 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_coferage_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 也异常慢

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

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

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

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

Baloneo

为什么不加时间索引?

opengps

为什么不加时间 pro_removal_time 索引?

showB1

mysql 单表 200W ？不考虑换种数据库么？业务 db 和 olap 的要分开才好。
非要用 mysql ，时间都是 yyyy-MM-dd HH:mm:ss 吗 /抽出来 yyyy-MM-dd 、yyyy-MM 的加上索引？然后不要用 between 了，我记得 between 大范围捞不走索引了。

lbprivateacc

如果只是统计一年内的信息，能预生成一张统计表嘛

t3zb2xzvjm4yvmn

可以试试用时间字段做分区

restkhz

7 秒是太离谱了。
t_order_comprehensive_coferage_IDX 组合了哪些字段？ is_collect ，pro_removal_time ？
看 explain 只用了 t_order_stats_order_id_IDX. 如果我没理解错，这基本也是在扫全表，只不过用了 order_id 的索引然后一条一条根据 where 的条件查。
顺便一问，pro_removal_time 有单独索引吗？或者作为组合索引的第一个？

没有的话试着创建一个组合索引，只索引这两个字段。pro_removal_time 放第一位,is_collect 放第二位试试。如果 explain 看他还不用这个，force index 试试。
我怕你那个优化器直接把 is_collect 当第一个筛选条件，如果绝大多数都是 1 那会不稳定。

这个内存大小应该不存在什么问题。swap 是关了的，对吧...？

yor1g

区间数据太多不能走索引的把区间缩小用 union 查

sagaxu

200W 行里捞 50W 行，加什么索引都没用，按每秒 10 万行算，DB 端消耗 5 秒，加上传输和程序解析，也要 6 秒朝上了。

这种管理后台跑分析的事情，同步到其它库，不要在业务库里面搞。你可以搞个从库使劲儿造，也可以按天搞个汇总表(只有当日需要从原始表读)，或者力大砖飞上大数据那套分析工具。

yh7gdiaYW

我们做过类似的需求，不过数量级比这个更大一点，初期大约是几亿数据里捞几百万（现在是万亿数据里捞几千万）。
最终结论是，mysql 就不适合做几百万以上数据集上的统计查询，建议切到专门的 OLAP 数据库，可以试试 StarRocks 或者 TiDB （开 TiFlash ）。
如果一定要在 MySQL 上做，建议升级硬件配置，换上你们速度最快的 SSD 和单核性能最强的 CPU ，这两个是我们发现的最大卡点，楼上说索引的我想应该没做过这种业务