这是一个创建于 114 天前的主题，其中的信息可能已经有所发展或是发生改变。
#### 配置参数前提
目前数据库是 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 也异常慢
所以这种查询到底如何去处理，因为做的是大屏幕展示的，很多的数据都是时间条件查询。
那到底要去如何优化才能处理好这类查询。
看到别个百万千万上亿查询都洒洒水。实在想不通。
至于数据库配置内存已经是升级过一轮了。加不了一点
目前数据库是 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 也异常慢
所以这种查询到底如何去处理，因为做的是大屏幕展示的，很多的数据都是时间条件查询。
那到底要去如何优化才能处理好这类查询。
看到别个百万千万上亿查询都洒洒水。实在想不通。
至于数据库配置内存已经是升级过一轮了。加不了一点
第 1 条附言 · 113 天前
谢谢大家的回复。按照老哥的说法感觉也是。
200w的数据一年就要捞50w数据，那要这么多时间也正常。
那我在琢磨琢磨。配置升级申请换其他软件来搞也不太现实(占用内存小可以尝试)。数据库内存才从8G->16G 申请了无数次。
总之谢谢大家了。我先试下查询需要的所有字段都加到索引里。
41 条回复 • 2024-09-12 12:12:03 +08:00
 |
1
Baloneo 114 天前
为什么不加时间索引?
|
 |
2
opengps 114 天前
为什么不加时间 pro_removal_time 索引?
|
 |
3
showB1 114 天前
mysql 单表 200W ？不考虑换种数据库么？业务 db 和 olap 的要分开才好。
非要用 mysql ，时间都是 yyyy-MM-dd HH:mm:ss 吗 /抽出来 yyyy-MM-dd 、yyyy-MM 的加上索引？然后不要用 between 了，我记得 between 大范围捞不走索引了。 |
 |
4
Xrall OP |
 |
6
lbprivateacc 114 天前 via Android
如果只是统计一年内的信息，能预生成一张统计表嘛
|
|
7
Xrall OP @lbprivateacc 应该也是可以的，现在的表其实已经拆过一次了，也是根据统计需求拆的。但是拆分后查询全都得改吧。不知道还有其他方案没
|
 |
8
t3zb2xzvjm4yvmn 113 天前
可以试试用时间字段做分区
|
 |
9
restkhz 113 天前
7 秒是太离谱了。
t_order_comprehensive_coverage_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 是关了的，对吧...？ |
 |
10
yor1g 113 天前 via Android
区间数据太多不能走索引的 把区间缩小用 union 查
|
 |
11
sagaxu 113 天前  2
200W 行里捞 50W 行，加什么索引都没用，按每秒 10 万行算，DB 端消耗 5 秒，加上传输和程序解析，也要 6 秒朝上了。
这种管理后台跑分析的事情，同步到其它库，不要在业务库里面搞。你可以搞个从库使劲儿造，也可以按天搞个汇总表(只有当日需要从原始表读)，或者力大砖飞上大数据那套分析工具。 |
 |
12
yh7gdiaYW 113 天前
我们做过类似的需求，不过数量级比这个更大一点，初期大约是几亿数据里捞几百万（现在是万亿数据里捞几千万）。
最终结论是，mysql 就不适合做几百万以上数据集上的统计查询，建议切到专门的 OLAP 数据库，可以试试 StarRocks 或者 TiDB （开 TiFlash ）。 如果一定要在 MySQL 上做，建议升级硬件配置，换上你们速度最快的 SSD 和单核性能最强的 CPU ，这两个是我们发现的最大卡点，楼上说索引的我想应该没做过这种业务 |
 |
14
chenqh 113 天前
你把 is_collect=0 去掉,需要多长时间?
|
 |
15
moult 113 天前
按小时汇总数据，然后在这个汇总表里面查。不加索引都可以。
|
 |
16
pinerge 113 天前
explain analyze 看看结果
|
 |
17
NowTime 113 天前 via Android
我们业务有张表差不多几百万行数据，也是基于时间范围查询比如过去 90d 数据，对于某个用户数据量大的插座就比较慢。
偶然一次实验发现，先定位到 90d 前最早的 id 值，查询时带上 id >= {查询值}，速度就变得很快。 |
|
18
yh7gdiaYW 113 天前 1
另外这就是 MySQL 做的稀烂的地方，这个场景如果是 MongoDB ，自带的分析工具很容易能看出瓶颈在 fetch （根据索引把需要的行从文件系统读取出来）。加 limit 1 、加索引都没啥用，因为数据量大。
除了楼上说的提前汇总数据、切 OLAP 数据库这些，我们之前调研时也发现了一个野路子，即把查询需要的所有字段都加到索引里，然后就是见证奇迹的时刻 |
 |
19
liuhuan475 113 天前
分区表试试
|
|
20
Xrall OP @liuhuan475 后面都来试试看什么方案最合适。
|
 |
21
oneisall8955 113 天前
所以，这个表 50w 行统计出来，会有人看吗？
|
|
22
Xrall OP @oneisall8955 只是数据查询出来 50W 行，代码里面还得分组，他就是要查看一年到头，每个区域有多少个用户 分别各种类型的重量有多少，去了哪里，收的地方又有多少个，收了然后处理的有多少这些数据，所以选择查询出来处理。
|
 |
23
aylsss 113 天前 via iPhone
1.建个联合索引：CREATE INDEX idx_pro_removal_order ON t_order_stats (pro_removal_time, order_id);
2.查询时把 is_collect=0 放到最后面 |
 |
24
threeBoy 113 天前
没格式看的头皮发麻， 最简单有效的方式就是再弄一个报表专用数据库 clickhouse 之类的
|
 |
25
xuanbg 113 天前
纯粹是捞的数据太多造成的，啥都不好使。再怎么优化，也就 5 秒的水平。
|
 |
27
lancelotfh 113 天前
先把数据捞出来，建张临时表去跑
|
 |
29
redog 113 天前
表里有主键吗？把主键去掉，建立一个，按 is_collect ，pro_removal_time ，id 的 UNIQUE 索引，这样应该会形成聚簇索引，第一次创建时因为你已经有 200W 的记录了，会对这 200W 记录重新物理排序，所以会很慢，另外就是插入和更改 is_collect 时会慢一点，但这个数据量来看应该不会有太大感知。
好处是会按上面的顺序物理排序，这样你前面的条件一出，回表取值的速度会大大加快，捞数据是顺序读取的。 另外一种就是建一个覆盖索引，按你之前的条件，前面必须是 is_collect ，pro_removal_time ，id ，然后继续跟所需要的业务字段，那个求和的也要算进来。 |
 |
30
SDYY 113 天前
一个月前的数据还会修改吗，先缓存历史数据，最新数据再计算后汇总
|
 |
31
wxf666 113 天前
@xuanbg #25 现在千元消费级固态，不是都支持 100W 随机查找/秒 了吗？
换句话说，即使服务器内存只有几百 MB ，MySQL 完全用不了缓存，所有读写都走固态 IO ， 随机查找 50W 行，也应该 0.5 秒就能搞定？   |
|
33
wxf666 112 天前
|
|
36
Xrall OP @showB1 #35 已经选择放弃 MySQL 干这种事情了。拿 ES 应付应付
 只不过 es 查询也有其他问题，哎最主要还是没办法升级设备用更专业的来处理这个了 |
|
37
wxf666 112 天前
@showB1 #35 你的意思是，虽然现在消费级固态，能做到 100W 随机读写 / 秒，
但 MySQL 没能完全利用好这个资源，所以做不到？ 请教一下，主要是什么方面耗时过多了呢？ - 网络 IO ？ - 等待锁？ - ……？ |
|
39
Xrall OP @showB1 #38
谢谢提出这些方案。可惜 es 有现成的，其他的都没内存上了。真的就是申请设备异常难，我也去查询过文档最低都要 4G 内存吧。实在没有余粮了。。 |
 |
40
dode 106 天前
如果没有固态硬盘，换成固态硬盘基本可以解决问题
|
Select Language