文章目录
1.需求分析
有这样一个需求做一个类似于资产的查询统计,并且可以通过条件进行查询。这里面涉及到的一共有两张表一个是委托表我们称之为WeiTuo,一个表是成交表我们称之为ChengJiao。
我们看一下这个两张表的字段
create table WeiTuo
(
wei_tuo_id bigint auto_increment
primary key,
link_account varchar(255) not null ,
average_dealt_price decimal(65, 30) not null ,
bs varchar(255) not null ,
Transtype varchar(255) not null ,
commodity varchar(255) null ,
pricing_commodity varchar(255) null ,
dealt_amount decimal(65, 30) not null ,
status varchar(255) not null ,
last_update_time bigint null ,
create table ChengJiao
(
chengjiao_id bigint auto_increment
primary key,
link_account varchar(100) not null ,
amount decimal(65, 30) not null ,
bs varchar(20) not null ,
Transtype varchar(255) not null ,
dealt_time bigint null ,
price decimal(65, 30) not null,
commodity varchar(255) null ,
pricing_commodity varchar(255) null
);
我们需要将同一个账户号(linkAccount),同一个交易类型(commodity,pricing_commodity都相同),买入委托总量(orderBuyInAmount),成交买入总量(transBuyInAmount),买入成交差值(diffBuyInAmount),买入委托总价格(orderBuyInPrice),买入成交总价格(transBuyInPrice),买入价格差值(diffBuyInPrice),卖出委托总量(orderSellOutAmount),卖出买入总量(transSellOutAmount),卖出成交差值(diffSellOutAmount),卖出委托总价格(orderSellOutPrice),卖出成交总价格(transSellOutPrice),卖出价格差值(diffSellOutPrice)统计出来返回一个list给前端进行展示。
2.思路分析
我们要将这些展现到前端,最初的思路就是来一个sql语句封装到一个实体类,然后就行了,但是由于这是两张表我即得统计买也得统计卖,所以这就成了先将买入的东西查出来作为左表,卖出的东西拼成右表两个表连起来就行了。
3.解决方法1——sql直接上(两张表各有200多条数据)
SELECT ac1.link_account,
ac1.commodity as accOrderBuyCommodity,
t1.commodity as transBuyCommodity,
SUM(ac1.dealt_amount) as accorder_buy_in,
SUM(t1.amount) as trans_buy_in,
SUM(ac1.dealt_amount - t1.amount) as buy_diff_amount,
SUM(t1.price * t1.amount) as trans_buy_in_price,
ac1.pricing_commodity as accOrderPricingCommodity,
t1.pricing_commodity as transPricingCommodity,
SUM(ac1.average_dealt_price * ac1.dealt_amount) as order_buy_in_price,
SUM(ac1.average_dealt_price * ac1.dealt_amount - t1.price * t1.amount) as buy_diff_price
FROM WeiTuo ac1,
ChengJiao t1
LEFT JOIN(SELECT ac2.link_account,
ac2.commodity as accOrderBuyCommodity,
t2.commodity as transBuyCommodity,
SUM(ac2.dealt_amount) as accorder_sell_out,
SUM(t2.amount) as trans_sell_out,
SUM(ac2.dealt_amount - t2.amount) as sell_diff_amount,
ac2.pricing_commodity as accOrderPricingCommodity
t2.pricing_commodity as transPricingCommodity,
SUM(ac2.average_dealt_price * ac2.dealt_amount) as order_sell_out_price,
SUM(ac2.average_dealt_price * ac2.dealt_amount - t2.price * t2.amount) as sell_diff_price
FROM WeiTuo ac2,
ChengJiao t2
WHERE ac2.link_account = t2.link_account
AND ac2.bs = 's'
AND t2.bs = 's'
AND ac2.status = 'dealt'
GROUP BY ac2.link_account,
ac2.commodity,
t2.commodity,
ac2.pricing_commodity,
t2.pricing_commodity
HAVING ac2.commodity = t2.commodity
AND ac2.pricing_commodity = t2.pricing_commodity) as selltable
ON selltable.link_account = t1.link_account
WHERE ac1.bs = 'b'
AND ac1.link_account = t1.link_account
AND t1.bs = 'b'
AND ac1.status = 'dealt'
GROUP BY ac1.link_account,
ac1.commodity,
ac1.pricing_commodity,
t1.pricing_commodity,
t1.commodity
HAVING ac1.commodity = t1.commodity
AND ac1.pricing_commodity = t1.pricing_commodity;
这个查询的时候1min50s都没好,截完图之后也没好,等了一会还没好,估计没个好。。。主要是因为这个来来回回查了4张表,第一个买入的需要两张表的数据,第二个卖出的需要两张表加一块四张表,一张表200多条200200200*200全表扫描那这个数据量挺恐怖还要进行分组求和等运算。对数据库的压力非常大。
这时候我们第一时间想到的是加索引观察他的查询条件有link_account,bs,status我们建一个联合索引看看行不行。
CREATE INDEX `link_bs_status` ON `Weituo` (link_account,bs,status);
CREATE INDEX `link_bs` ON `Chengjiao` (link_account,bs);
然后执行一下
还是不行基本没戏,看一下SQL执行计划他走没走索引
这个忘了之前建过索引,但是他走的还是全表扫描。所以这种sql直接上的方法可以被废弃了。
4.改进优化1.0——之前数据库的复杂操作放到内存中做
刚才得出的结论是我们不能把复杂的聚合操作放在数据库里,同时我们要尽量减少联表查询,这样对数据库的压力特别大,我们尽量把大量繁琐复杂的计算放在内存里。 内存速度远远大于在数据库中的速度,一旦数据量上涨到上万条那就完了。注意到我们有一个用户表里面记录了用户的ID我们可以把这个拉出来然后根据这些ID通过多线程去查询每个线程只查一个ID的数据这样既利用了多核CPU的优势也减少了单次查询的数据量。
@RestController
@Slf4j
@RequestMapping("/trading/Statistic")
public class StatisticController {
@Autowired
private IAccountService iAccountService;
@Autowired
private IChengjiaoService iChengjiaoService;
@Autowired
private IWeituoService iWeituoService;
@Autowired
private IStatisticService iStatisticService;
private List<Statistic> StatisticsList;
@GetMapping(value = "/list")
public Result<?> queryPageList(Statistic statistic,
@RequestParam(value = "dealtime_begin",required = false)String begin,
@RequestParam(value = "dealtime_end",required = false)String end){
StatisticsList = new ArrayList<>();
String linkAccount = statistic.getLinkAccount();//拿到查询条件账户号
String commodity = statistic.getCommodity