【MySQL】mysql | 记一次SQL慢查询优化

一、来龙去买

        1、MySQL5.7

        2、单表数据60万

        3、有个字段point是text类型，存的是大文本（60W条数据，7个G，自己脑补point存的啥）

        4、基础表，有用户ID，时间（yyyy-MM-dd HH:mm:ss），业务数据（两类）

        5、业务分为两种类型

                1）业务结果字段，金额；用于累加统计

                2）point就是用于渲染展示

        6、业务场景需求

                1）对同一天的进行归类汇总

                2）按天分页

        7、优化前

                1）未分页，查询整个表的全部字段（10多个），但业务只用部分字段

                2）实时计算，利用SQL排序，直接把内存搞炸了

                3）根本查不动，完全没反应（等不下去）

        8、优化后

                1）增加扩展字段：date（yyyy-MM-dd），用于归类查询

                2）增肌视图，业务场景数据抽离（关注哪些用哪些）

                3）创建组合索引（user+date），group by 的时候走索引

                4）优化后：2761.36ms

二、处理方案

1、给表增加字段date

1）增加字段

alter table record add date varchar(20) comment '格式化datetime';

2）对date赋值

update record set date = DATE_FORMAT(start_time,'%Y-%m-%d')

start_time，即yyyy-MM-dd HH:mm:ss

2、给记录表创建组合索引

1）组合索引

create index gdx_user_date on record(user, date);

说明：组合索引为了group by 时候走索引

3、创建视图

1）sql

create view v_record_user
as
select distinct user, date, count(user) as num
from record
group by user, date

此视图，为了拿到用户在某一天有多少条记录

目的：后续按天分页要用

4、索引验证

1）sql

explain SELECT  date,num,user  FROM v_record_user
WHERE user = '152xxxxxxxx"'

2）结果

 

5、按天分页

1）需求

按天分页，每页10条；当当天记录超过10条时，全部显示；即1页可能包括1天数据，也可能是多天数据

2）思路

将天换算为开始日期（yyyy-MM-dd）与结束日期，每次查询时，按每天的记录数进行分页。

3）java源代码

   /**
     * 明细列表
     * @param param
     * @return
     */
    void recordWithPage(Param param) {
        // 所有有数据的日期
        List<Record> list = QueryWrapperUtils.listByUser(recordService, getMobile());
        if (CollUtils.isEmpty(list)) {
            return CollUtils.newArrayList();
        }
        Integer dayNo = param.getPageNo();
        // key: dayNo
        // val: 开始日期
        Map<Integer, String> beginDateMap = CollUtils.newHashMap();
        // key: dayNo
        // val: 结束日期
        Map<Integer, String> endDateMap = CollUtils.newHashMap();

        int dateSize = list.size();
        Record item = null;
        int page = 1;
        int tempNum = 0;
        // true:启动新的页码
        boolean pageReset = true;
        for (int i = 0; i < dateSize; i++) {
            item = list.get(i);
            tempNum += item.getNum();
            if (pageReset) {
                // 设置开始日期
                beginDateMap.put(page, item.getDate());
                pageReset = false;
            }

            if (tempNum >= BigDecimal.TEN.intValue()) {
                // 大于等于10，占1页
                endDateMap.put(page, item.getDate());
                page++;
                // 清零
                tempNum = 0;
                pageReset = true;
            }
        }
        // 结束
        String bigDate = beginDateMap.get(dayNo);
        // 开始
        String smallDate = endDateMap.get(dayNo);
	// 根据开始日期与结束日期查询即可

    }