V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 1914 天前的主题，其中的信息可能已经有所发展或是发生改变。

划重点：单表 3500w

单表查
表 [用户]
字段 [用户名，金额，日期时间]
查每天同一时间用户 A 和用户 B 金额之和的最大值
如果不用

	
SELECT
	max( a.金额 + b.金额 ),
	a.日期时间 
FROM
	( SELECT 金额,日期时间 FROM 用户表 WHERE 用户名 = 'a' ) a
	LEFT JOIN ( SELECT 金额,日期时间 FROM 用户表 WHERE 用户名 = 'b' ) b 
    	ON a.日期时间 = b.日期时间

该怎么写，不能加索引
上面这个 sql 查得特别慢，近 1 分钟了

第 1 条附言 · 2019-08-29 09:26:53 +08:00

少年们，已经采用了每日计算前一天加和最大值的定时任务来解决，勉强找了一张表，把记录塞了进去。。。
谢谢米娜～

单表

select

日期

金额

48 条回复 • 2019-08-30 18:34:34 +08:00

vus520

2019-08-28 14:37:51 +08:00

拆 SQL....

php01

2019-08-28 14:40:41 +08:00

select 金额,日期时间 from 用户表用户名 in ('a','b');
用程序计算。
此后，加表，作为统计表，统计表同日累加金额。

hosaos

2019-08-28 14:40:54 +08:00

代码里做啊，同一时间如果不长的话，先定个变量 MaxMoney=0，取 A 的数据(可分页)，然后取 B，值大了就覆盖 MaxMoney

php01

2019-08-28 14:43:19 +08:00

@php01 单表 3500w 数据，光取出来就时间话费很长了。所以你先整体全部算一遍，重新做一张统计表，把统计结果写入，以后每次增改，同步这张统计表。

Breadykid

2019-08-28 14:53:05 +08:00

@php01 目前有类似的统计表，但是没有能放这个数值的表，要是建新表的话里面只有这一个字段，但是直接在 3500w 单表里取，怎么取都要很久，光
```SELECT 金额,日期时间 FROM 用户表 WHERE 用户名 = 'a'```
就花 46s

glacer

2019-08-28 14:53:14 +08:00

为什么不能加索引？ join 如果是用临时表的话是无法使用索引的

no1xsyzy

2019-08-28 15:10:01 +08:00

`SELECT 金额,日期时间 FROM 用户表 WHERE 用户名 = 'a'` 取出来有多少个？

Breadykid

2019-08-28 15:16:29 +08:00

@no1xsyzy 288

Immortal

2019-08-28 16:05:18 +08:00

拿到上一次查询结果的 id 号作为这一次查询的 where 条件:>id
虽然上一次 id 不一定是昨天的最后一条数据但肯定小于今天的数据
如果在两天内的数据量下执行 max 可能会好点你可以试试

shyrock

2019-08-28 16:08:31 +08:00

其他需求都普普通通，但是这个‘不能加索引’就很骚了。。。能问下为什么？

npe

2019-08-28 16:23:07 +08:00

where 用户=a,b
group by 时间
max 金额

----

数据量过大建议分表，可以按月进行拆分。

Aresxue

2019-08-28 16:24:24 +08:00

拆呗，不过也不知道你想咋样啊，你是怕单条 sql 超时了还是怕数据库 IO 时间过长压力过大，亦或是要求必须一条 sql
搞定啊，不知道你想干啥这工作也没法开展不是。如果只是怕数据库长时间 IO 压力太大，就拆呗，搞成几条单表查询，在代码里做计算。如果必须要一条 sql 搞定，你还不准在任何列上用索引，那几乎没啥太大优化空间了，语义上的优化对你这种简单 sql 帮助不大。

jziwenchen

2019-08-28 16:25:35 +08:00

```SELECT 金额,日期时间 FROM 用户表 WHERE 用户名 = 'a'```
这条语句花了 46s ?? 4000w 数据并不多啊怎么会这么慢...

shadow88sky

2019-08-28 16:27:09 +08:00

将表同步到 elasticsearch 中，学一些简单的 es 语法，就搞定了

jziwenchen

2019-08-28 16:33:08 +08:00

这样可以吗？

select max(a.金额 + b.金额) , 日期
from 表 as a
left join 表 as b on b. 日期 = a.日期
where a.用户='a' and a.用户='b'

arrow8899

2019-08-28 16:37:28 +08:00

这样做的目的是啥

1762628386

2019-08-28 16:40:41 +08:00

建议收购公司，夺得话语权，然后加上索引就行了

liuhuansir

2019-08-28 16:44:01 +08:00

SELECT 金额,日期时间 FROM 用户表 WHERE 用户名 = 'a' 这个都花了 46s，那单纯优化 sql 已经不可能了，纯粹是数据量过大，只能拆了

onepunch

2019-08-28 16:44:20 +08:00

如果是阿里云建议了解一下分析型数据，最近接触的业务使用了下非常好用。跟生产数据一致，而且查询都是秒级的。
场景跟你很像，生产环境无法追加索引。

https://cn.aliyun.com/product/ads

ylsc633

2019-08-28 17:03:10 +08:00

粗略看了下你这是不是可以改成

where name in ('a','b') group by time

wlkq

2019-08-28 17:33:31 +08:00

2 楼的方法算最优解了吧，又不能加索引

IamUNICODE

2019-08-28 17:45:13 +08:00

想知道为什么不能加索引，把理由说出来，我这好早做准备。。

onepunch

2019-08-28 17:50:50 +08:00

@IamUNICODE 加索引会锁表，数据越多时间越长，生产环境不允许停机那么长时间，如果是自己的电脑那无所谓了

hhhzccc

2019-08-28 18:10:04 +08:00

再加一张临时中间表，

nthhdy

2019-08-28 18:22:15 +08:00

为啥不能加索引

arthas2234

2019-08-28 18:32:12 +08:00

一般这种大表，不会涉及修改数据的话，做归档处理。你现在加也不迟，先实现个功能拆表，后面再实现需求

jowenzzzzz

2019-08-28 18:33:42 +08:00 via Android

看不懂这个代码，同一时间求最大值，没 group by，同一时间是天？同一时间是否有多条记录？

luozic

2019-08-28 19:42:06 +08:00

mysql 还是别的，mysql 拆表，别的 oracle postgresql 倒是有不少骚操作。

jzmws

2019-08-28 20:44:31 +08:00

4000w 的数据不多呀 ! 增加硬件陪着

lilongn1

2019-08-28 21:09:21 +08:00 via iPhone

Select max(sum(金额) )
from user
where 用户 in (‘ a ’,’ b ’)
group by 日期

reus

2019-08-28 22:13:50 +08:00

@onepunch 建从库，加了索引，再切换主从……

Breadykid

2019-08-29 09:21:26 +08:00

@shyrock 生产环境不让随便加索引 [无话语权的小兵开发的功能不该涉及索引

Breadykid

2019-08-29 09:23:03 +08:00

@jziwenchen 你这个和我的有什么本质的区别吗？

Breadykid

2019-08-29 09:24:46 +08:00

@Aresxue 产品希望这个查询接口执行时间不超过 3s，无论用一条 sql 还是代码里操作

Breadykid

2019-08-29 09:27:32 +08:00

@shadow88sky 木有 es，哭哭

Breadykid

2019-08-29 09:28:06 +08:00

@1762628386 梦做得不错

Breadykid

2019-08-29 09:31:54 +08:00

@luozic 用的 mysql，不过我很喜欢 pg 的哇，想问 pg 有什么骚操作哇

aliipay

2019-08-29 10:05:23 +08:00

每日计算前一天加和最大值的定时任务
---------------------
是所有 ab 用户组合都事先算好？

IamUNICODE

2019-08-29 10:08:04 +08:00

@onepunch 我懂了，原来是这原因，那我得先做好准备了。。

hjq98765

2019-08-29 10:16:48 +08:00

在做 a 表和 b 表的子查询的时候，应该是各做了一次用户表的全表扫描，如果以后只看 a 和 b 两个用户的话，可以先生成一个临时表 where 用户名 in ('a','b')，然后再在这个 ab 表上分别查 a 和 b，可以少一次用户表的全表扫描

netnr

2019-08-29 10:28:48 +08:00

给出一个彩票方案：两张表分别排序取前 100 条最大值，在比较时间，有相同的，恭喜你中奖了，机率很渺茫

Aresxue

2019-08-29 10:48:07 +08:00

@Breadykid 那二楼方案是最佳的，先把 a 和 b 的数据从数据库里取出来，然后在程序里做计算，计算量大的话大可以优化，应该能控制在比较短的时间，这个方案的可行性主要看你从数据库里取 a 和 b 的数据要多久，要是这一步都超过 3s 那就没得玩。
PS：合理的方案还是加索引，不让加索引是因为加索引的时候会锁表，会影响表的正常使用(涉及到该表的业务全都没无法使用)，时间过长的情况下可能导致更严重的问题，整个数据库崩了都有可能（几率不大），加了索引后还要清理内存碎片对表进行分析优化，不然 CBO 可能不会及时得知变更，计算执行计划的成本的时候出现失误。和 DBA 商量商量趁哪天服务升级或夜深人静了服务没什么人使用的时候把索引给加上吧。

LuciferGo

2019-08-29 15:14:05 +08:00

每天抽对应日期的 AB 用户数据到中间表再做处理,相对直接查更快。这种大表没索引最终要么归档要么还是要停一下加索引

luozic

2019-08-29 15:14:47 +08:00

视图里面可以得到汇总金额，后面就用代码算，计算机结果塞进去临时表来存。

jziwenchen

2019-08-29 16:05:26 +08:00

@Breadykid

你用 explain 工具可以看到啊你的 SQL 用到临时表 , join 就没有啊；明显会快；并且你单表查询都要 46s 怎么优化 SQL 都慢.

Breadykid

2019-08-30 17:41:05 +08:00

@aliipay 是的哇

aliipay

2019-08-30 17:55:23 +08:00

@netnr 有相同的日期也不一定和是最大

netnr

2019-08-30 18:34:34 +08:00

@aliipay 是的，考虑不周

千万级单表 sql 查询问题

划重点：单表 3500w