daily_payment.md

# 每日报表

数据分析组需要经常执行以下查询生成从昨天到之前某一天的交易量统计

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

现在这个查询很慢，payment 表的信息如下：

```text
mysql> desc payment;
+---------------+---------------+------+-----+---------+----------------+
| Field         | Type          | Null | Key | Default | Extra          |
+---------------+---------------+------+-----+---------+----------------+
| payment_id    | int           | NO   | PRI | NULL    | auto_increment |
| customer_id   | int           | YES  |     | NULL    |                |
| staff_id      | int           | YES  |     | NULL    |                |
| rental_id     | int           | YES  |     | NULL    |                |
| amount        | decimal(12,4) | YES  |     | NULL    |                |
| pathment_date | timestamp     | YES  |     | NULL    |                |
+---------------+---------------+------+-----+---------+----------------+
6 rows in set (0.12 sec)
```

应该如何优化这个查询？

<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;` 列出所有表

## 答案

建立表达式索引

```sql
alter table payment add index idx_payment_date((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_SUB(CURDATE(), INTERVAL 1 DAY)
group by day 
```

### 对该查询帮助不大

在 payment_date 列上建立索引

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

### 可以简化语句，但对优化没有帮助

建立计算列

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

然后使用它改写查询

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