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