BLOG MyBatis MySQL

以前的SQL

以前写的blog-ssm项目，现在看看挺多地方可以优化的。
比如之前实现这个查询前七天每天的日志数，我是用一个mapper传入间隔数，传出每天的日志量，然后通过7次调用放到一个List里。

<select id="selectApartDaysPv" parameterType="int" resultType="int">
    select count(0) FROM log WHERE TO_DAYS(NOW())-#{value} = TO_DAYS(access_time)
</select>

@Override
public List<Integer> selectLastWeekPvList() {
    List<Integer> l = new ArrayList<>();
    int r7 = logMapper.selectApartDaysPv(7);
    int r6 = logMapper.selectApartDaysPv(6);
    int r5 = logMapper.selectApartDaysPv(5);
    int r4 = logMapper.selectApartDaysPv(4);
    int r3 = logMapper.selectApartDaysPv(3);
    int r2 = logMapper.selectApartDaysPv(2);
    int r1 = logMapper.selectApartDaysPv(1);
    l.add(r7);
    l.add(r6);
    l.add(r5);
    l.add(r4);
    l.add(r3);
    l.add(r2);
    l.add(r1);
    return l;
}

现在看看很是一个奇葩。

MySQL优化

首先看看问题，要查询前七天每天的数据量，明显要分组。

SELECT
    DATE_FORMAT( access_time, '%Y-%m-%d' ) days,
    COUNT(*) COUNT 
FROM
    ( SELECT * FROM log WHERE DATE_SUB( CURDATE(), INTERVAL 7 DAY ) <= DATE( access_time ) ) AS test 
GROUP BY
    days;

所以第一次写了上面这个，今天是 2021.10.22，且前七天日志删到只留了19号和22号的，但是问题出现了。这样写如果七天中某一天没有数据，那么这一天也就没有数据行了。

所以需要用到前七天的时间虚表，然后通过left join左连接上面SQL的查询结果，并注意如果为空的时候应该使用IFNULL(a.COUNT,0)去处理某天统计为空设为默认0。

其中我的表为log，log表中access_time即为时间字段。
要查询出来的数据表头因为封装了DTO所以设为access_date，access_value。
其它都是派生表必须要有的自己的别名。最后通过access_date从后到前的时间排下序。

SELECT 
  b.access_date,IFNULL(a.count,0) AS access_value
FROM (
  SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS access_date
  UNION ALL
  SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY)
  UNION ALL
  SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY)
  UNION ALL
  SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY)
  UNION ALL
  SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY)
  UNION ALL
  SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY)
    UNION ALL
  SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY)
)  b
LEFT JOIN
(
  SELECT DATE_FORMAT(access_time,'%Y-%m-%d') days, COUNT(0) AS count 
  FROM (SELECT * FROM log WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(access_time)) AS t
  GROUP BY days
) a
ON (b.access_date = a.days)
ORDER BY access_date

@Override
public List<Integer> selectSevenDaysPv() {
    List<Integer> l = new ArrayList<>();
    List<SevenDayLog> logs = logMapper.selectSevenDaysPv();
    for (SevenDayLog log : logs) {
        l.add(log.getAccessValue());
    }
    return l;
}

之后我再把之前几天的日志先恢复，然后重新查询，可以看到实现了该操作。
如果需要UV数则在COUNT(0)位置换成COUNT(DISTINCT ip)，不过前提你要在log中有这个ip字段且真实统计。
其中FROM (SELECT * FROM log WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(access_time)) AS t子查询用来优化速度的，范围先缩小到全表中前7天的log数据，以至于后面分组不会从全表查一遍。