数据量不断增大的几种简单的优化方案

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.