一次对sql server查询的优化

项目需求，要对一个八千万左右的表进行查询，表内有一个区分类型（大概一共两千多个类型）字段，还有年，月，日（从1949到现在，一小部分数据没有年月日，可以忽略不查询）字段，查询的是某些类型在某些年份某个固定的时间段（忽略2.29）内的某几个字段数据。

 

比如需要10种类型，10个年份在7月1号到7月30号的三个字段的数据。

我们假设一下表，假设表名叫data，字段如下

id(varchar)，type(varchar)，year(int)，month（int），day(int)，insertdate(timestamp)，data1(double)，data2(double)，data3(double)等。

那么sql 大概如下：

SELECT
    id,
    type,
    YEAR,
    MONTH,
    DAY,
    insertdate,
    data1，data2，data3 
FROM
    data 
WHERE
    type IN ( 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h' ) 
    AND YEAR IN ( 1949, 1958, 1967, 1989, 1999, 2004, 2008, 2020, 2021 ) 
    AND (
        insertdate BETWEEN ( '1949-07-01 00:00:00', '1949-07-30 23:59:59' ) 
        OR insertdate BETWEEN ( '1958-07-01 00:00:00', '1958-07-30 23:59:59' ) 
        OR insertdate BETWEEN ( '1967-07-01 00:00:00', '1967-07-30 23:59:59' ) 
        OR insertdate BETWEEN ( '1989-07-01 00:00:00', '1989-07-30 23:59:59' ) 
        OR insertdate BETWEEN ( '1999-07-01 00:00:00', '1999-07-30 23:59:59' ) 
        OR insertdate BETWEEN ( '2004-07-01 00:00:00', '2004-07-30 23:59:59' ) 
        OR insertdate BETWEEN ( '2008-07-01 00:00:00', '2008-07-30 23:59:59' ) 
        OR insertdate BETWEEN ( '2020-07-01 00:00:00', '2020-07-30 23:59:59' ) 
    OR insertdate BETWEEN ( '2021-07-01 00:00:00', '2021-07-30 23:59:59' ) 
    )

真实的sql是用mybatis plus生成的，但执行大概就是上面的样子，

一开始，当类型还少的时候，查询还是相对较快的，但是当类型变多的时候，很快就照成查询要十几分钟。

下面对这次查询进行优化，首先是优化sql，把in替换成“= or”，把between 替换成 month 和 day 的“= or”,替换查询year和type的顺序。

同时show plan 查看sql解析情况，设置索引 [Year] ASC, [type] ASC, [Month] ASC, [Day] ASC

1 优化sql

优化后的sql是这样子的。

SELECT
    id,
    type,
    YEAR,
    MONTH,
    DAY,
    insertdate,
    data1，data2，data3 
FROM
    data 
WHERE
    (
        YEAR = 1949 
        OR YEAR = 1958 
        OR YEAR = 1967 
        OR YEAR = 1989 
        OR YEAR = 1999 
        OR YEAR = 2004 
        OR YEAR = 2008 
        OR YEAR = 2020 
        OR YEAR = 2021 
    ) 
    AND (
        type = 'a' 
        OR type = 'b' 
        OR type = 'c' 
        OR type = 'd' 
        OR type = 'e' 
        OR type = 'f' 
        OR type = 'g' 
        OR type = 'h' 
    ) 
    ) 
    AND (
        ( MONTH = 7 AND DAY = 1 ) 
        OR ( MONTH = 7 AND DAY = 2 ) 
        OR ( MONTH = 7 AND DAY = 3 ) 
        OR ( MONTH = 7 AND DAY = 4 ) 
        OR ( MONTH = 7 AND DAY = 5 ) 
        OR ( MONTH = 7 AND DAY = 6 ) 
        OR ( MONTH = 7 AND DAY = 7 ) 
        '''
        OR 
        ''' 
    OR ( MONTH = 7 AND DAY = 30 ) 
    )

此时，非聚集索引 [Year] ASC, [type] ASC, [Month] ASC, [Day] ASC正好可以完全使用，当查询类型四百多时已经优化到了十一二秒左右。

注意，其实也可以改为 （year = 1949 and month =7 and day =1),这样弄的索引设置以及哪种方法更快好需要测试。

2 并发优化

事实上，如果类型只有十个时，查询只需要0.3秒，继续查看show plan（explain），可以发现类型四百多时彼十个多了一步gather（实际测试类型到15个时就会有这一步），

这一步完全可以在java程序里通过多线程并发优化，在spring中配置好线程池，使用countdownlatch，可以将sql优化到3秒左右（cpu性能有限）。