# 每日报表优化

# 每日报表

分析过去一段时间的查询数据，Joe 发现 payment 表
```sql
create table payment(
    payment_id int primary key auto_increment,
    customer_id int,
    staff_id int,
    rental_id int,
    amount decimal(12, 4),
    payment_date timestamp
)
```
每天的订单量很大，下面这个查询的统计过程占用了大多数数据库资源，查询不会
早于当天，总是在历史上某一个日期段内查询

```sql
select date(payment_date) as day, sum(amount)
from payment
where date(payment_date) between $1 and $2
group by date(payment_date);
```


怎样优化最有效？

<hr/>

点击进入[MySQL实战练习环境](https://mydev.csdn.net/product/pod/new?image=cimg-centos7-skilltreemysql&connect=auto&create=auto&utm_source=skill){target="_blank"}。

* `show databases;` 列出所有数据库
* `show tables;` 列出所有表

##  答案

建立中间表并建立索引。

```postgresql
create table daily_payment(
    day date primary key , 
    amount decimal(12, 4));

insert into daily_payment(day, amount) 
select payment_date::date as day, 
       sum(amount) as amount 
from payment group by day;

```

使用

```sql
select day, amount 
from view_daily_payment 
where day between $1 and $2;
```

进行查询。并且每天定时执行一次刷新命令

```sql
insert into daily_payment(day, amount)
select date(payment_date) as day, 
       sum(amount) as amount 
from payment
where date(payment_date) 
    between DATE_SUB(CURDATE(), INTERVAL 2 DAY) 
    and DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
group by day;

```

## 选项

### 不会优化 sum 和 group by

在 payment_date 列上建立索引

```sql
create index idx_payment_date on 
    payment(payment_date);
```

### 不会优化 sum

建立计算列

```sql
alter table payment add day date 
    generated always as ( payment_date::date ) stored 
```

然后使用它改写查询

```sql
select day as day, sum(amount)
from payment
where day between $1 and DATE_SUB(CURDATE(), INTERVAL 1 DAY)
group by day;
```

### 优化了日期查询，但是不会优化统计过程

建立表达式索引

```sql
create index idx_payment_day 
    on payment((date(payment_date)));
```

### 不做物化，对查询速度不会有显著改善

建立视图

```sql
create view view_daily_payment as 
    select date(payment_date) as day, amount from payment;
```

然后在视图 view_daily_payment 上执行

```sql
select day, sum(amount) 
from view_daily_payment
where day between $1 and date('yesterday')
group by day 
```