sql优化

 

SELECT
    *
FROM
    d_secondMinuteData AS fd
WHERE
    fd.collectionTime BETWEEN '2022-04-01 00:00:00'
AND '2022-04-08 23:59:59'
AND fd.stationBranchId = 2287
AND DATE_FORMAT(
    fd.collectionTime,
    '%H:%i:%s'
) = '00:00:00'




SELECT
    *
FROM
    d_secondMinuteData AS fd
WHERE
    fd.stationBranchId = 2287
AND fd.collectionTime IN (
    '2022-04-01 00:00:00',
    '2022-04-02 00:00:00',
    '2022-04-03 00:00:00',
    '2022-04-04 00:00:00',
    '2022-04-05 00:00:00',
    '2022-04-06 00:00:00',
    '2022-04-07 00:00:00',
    '2022-04-08 00:00:00'
)

 collectionTime是索引，但是用函数处理索引字段会导致索引失效，但是用in不会导致索引失效，所以两个sql的效率相差很大

开始日期和结束日期可以用下面函数处理成list

com.rn.smart.monitor.utils.UtilTime#getBetweenTwoDateList

/****
     * 求两个日期间月份,天,小时集合
     *
     * @param startDateStr
     *            开始时间
     * @param endDateStr
     *            结束时间
     * @param type
     *            类型: 1：月份,2:天,3:小时,4:天(显示格式02-04 二月四号,只有月和日),5:天(显示格式:03,
     *            月份已经限定在一个月,只显示今天是几号)
     * @return
     * @throws ParseException
     */
    public static List<String> getBetweenTwoDateList(String startDateStr,
                                                     String endDateStr, int type) throws ParseException {
        List<String> list = new LinkedList<String>();
        DateFormat simpleDateFormat;
        Date startDate;
        Date endDate;
        try {
            simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            startDate = simpleDateFormat.parse(startDateStr); // 开始日期
            endDate = simpleDateFormat.parse(endDateStr); // 结束日期
        } catch (Exception ex) {
            simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            startDate = simpleDateFormat.parse(startDateStr); // 开始日期
            endDate = simpleDateFormat.parse(endDateStr); // 结束日期
        }

        Calendar startCalendar = Calendar.getInstance();
        Calendar endCalendar = Calendar.getInstance();
        startCalendar.setTime(startDate);
        endCalendar.setTime(endDate);
        String result = null;
        while (startCalendar.compareTo(endCalendar) <= 0) {
            startDate = startCalendar.getTime();
            switch (type) {

                case 0:
                    result = new SimpleDateFormat("yyyy-MM-dd 00:00:00")
                            .format(startDate);
                    result = result.substring(0, result.length());
                    list.add(result);
                    // 开始日期加一个天直到等于结束日期为止
                    startCalendar.add(Calendar.DAY_OF_MONTH, 1);
                    break;
                case 1:
                    result = new SimpleDateFormat("yyyy-MM").format(startDate);
                    result = result.substring(0, result.length());
                    list.add(result);
                    // 开始日期加一个月直到等于结束日期为止
                    startCalendar.add(Calendar.MONTH, 1);
                    break;
                case 2:
                    result = new SimpleDateFormat("yyyy-MM-dd").format(startDate);
                    result = result.substring(0, result.length());
                    list.add(result);
                    // 开始日期加一个天直到等于结束日期为止
                    startCalendar.add(Calendar.DAY_OF_MONTH, 1);
                    break;
                case 4:
                    result = new SimpleDateFormat("MM-dd").format(startDate);
                    result = result.substring(0, result.length());
                    list.add(result);
                    // 开始日期加一个天直到等于结束日期为止
                    startCalendar.add(Calendar.DAY_OF_MONTH, 1);
                    break;
                case 5:
                    result = new SimpleDateFormat("dd").format(startDate);
                    result = result.substring(0, result.length());
                    list.add(result);
                    // 开始日期加一个天直到等于结束日期为止
                    startCalendar.add(Calendar.DAY_OF_MONTH, 1);
                    break;
                default:
                    result = new SimpleDateFormat("yyyy-MM-dd HH")
                            .format(startDate);
                    result = result.substring(0, result.length());
                    list.add(result);
                    // 开始日期加一个月直到等于结束日期为止
                    startCalendar.add(Calendar.HOUR, 1);
                    break;
            }
        }
        return list;
    }