MYSQL一次慢查询优化，不要被“索引“蒙蔽了双眼

最近光衰趋势的excel表格一直不能正常发送。手工执行，发现某sql执行后返回查询结果要15分钟。判定是数据库原因导致，急需优化。

定位元凶，慢查询SQL:
select ifnull(t2.tx_dbm,‘0’) from devops.pvs_switch_port t1
left join devops.pvs_sfpshow t2 on (t1.ip=t2.ip and t1.port=t2.port) and t2.inserttime=202208282310 order by t1.ip,t1.port+0;

观察sql，有用到join，sort。目测可能需优化的地方，下面给出了各项配置数据:
1、 innodb_pool_size = 32G 【服务器内存为64G】
2、 慢查询索引，经过explain分析，发现都已经走索引了，无需添加
3、 SQL中有join，order ,检查 join_buffer_size，sort_buffer_size参数分表为16，32M。
4、 pvs_sfpshow表有4200W数据

由此：可以优化的地方：
1、 加大innnodb_pool_size =50G 【64*80%】
2、 Sfp_show 分表

测试：
1、 查询去掉order ，查询时间差不多，此项忽略
2、 将where条件放入t2中先执行，然后查询时间为，15.6分钟，时间长没变化
3、 将一天数据单独插入一个表中（create table XXX select XXX），然后查询。0.016秒。查询极快,以此判断大概率是数据量问题导致。要么分区，要么建临时表，要么优化索引

遇到难题：
1、 inserttime是varchar类型的，不支持range分区
2、 将inserttime改为bigint后，要求range字段为主键或者主键字段之一。
3、 遂更改主键为id,inserttime

由此：
1、 加大内存配置
2、 Sfpshow表做分区
ALTER TABLE pvs_sfpshow PARTITION BY RANGE( inserttime ) (PARTITION p20220829 VALUES LESS THAN (202208292359))
3、 加定时任务，每天自动分区
4、 以上更改后，虽然能执行，但是发现仍15分钟。仔细查看索引，发现并没有自己预想的索引，故此怀疑索引字段不够。遂将索引字段修改为inserttime,ip,port，查询变成了0.3秒

总结：此SQL虽然走了索引，但索引字段不够，查询仍缓慢，需将where中的字段都修改为索引字段，查询优化成功

以下附上分区脚本：（脚本复用zabbix的分区脚本，做了简单修改）

#!/bin/bash

#配置环境变量
DB_USER="devops"
DB_PWD="dddd"
DB_NAME="devops"
DB_PORT="3306"
DB_HOST="127.0.0.1"
MYSQL_BIN="/home/mysql-8.0.25/bin/mysql"

#历史数据保留时间，单位天
HISTORY_DAYS=180

#趋势数据保留时间，单位月
TREND_MONTHS=12

HISTORY_TABLE="pvs_sfpshow"
TREND_TABLE="trends"

#MYSQL连接命令
MYSQL_CMD=$(echo ${MYSQL_BIN} -u${DB_USER} -p${DB_PWD} -P${DB_PORT} -h${DB_HOST} ${DB_NAME})

function create_partitions_history() {
    #给历史表创建分区
    for PARTITIONS_CREATE_EVERY_DAY in $(date +"%Y%m%d") $(date +"%Y%m%d" --date='1 days') $(date +"%Y%m%d" --date='2 days') $(date +"%Y%m%d" --date='3 days')  $(date +"%Y%m%d" --date='4 days') $(date +"%Y%m%d" --date='5 days') $(date +"%Y%m%d" --date='6 days') $(date +"%Y%m%d" --date='7 days')
    do
        printf "$PARTITIONS_CREATE_EVERY_DAY"
        TIME_PARTITIONS=${PARTITIONS_CREATE_EVERY_DAY}2359
        printf "$TIME_PARTITIONS"
        for TABLE_NAME in ${HISTORY_TABLE}
        do
            SQL1=$(echo "show create table ${TABLE_NAME};")
            RET1=$(${MYSQL_CMD} -e "${SQL1}"|grep "PARTITION BY RANGE"|wc -l)
            #表结构中的表分区不存在，则创建
            if [ "${RET1}" == "0" ];then
                SQL2=$(echo "ALTER TABLE $TABLE_NAME PARTITION BY RANGE( inserttime ) (PARTITION p${PARTITIONS_CREATE_EVERY_DAY}  VALUES LESS THAN (${TIME_PARTITIONS}));")
                printf "$SQL2"
                RET2=$(${MYSQL_CMD} -e "${SQL2}")
                printf "$SQL2"
                if [ "${RET2}" != "" ];then
                    echo  ${RET2}
                    echo "${SQL2}"
                else
                    printf "table %-12s create partitions p${PARTITIONS_CREATE_EVERY_DAY}\n" ${TABLE_NAME}
                fi
                continue
            fi
            #表结构中的表分区已经存在，则创建分区
            if [ "${RET1}" != "0" ];then
                SQL3=$(echo "show create table ${TABLE_NAME};")
                RET3=$(${MYSQL_CMD} -e "${SQL3}"|grep "p${PARTITIONS_CREATE_EVERY_DAY}"|wc -l)
                if [ "${RET3}" == "0" ];then
                    TIME_PARTITIONS=${PARTITIONS_CREATE_EVERY_DAY}2359
                    SQL4=$(echo "ALTER TABLE $TABLE_NAME  ADD PARTITION (PARTITION p${PARTITIONS_CREATE_EVERY_DAY} VALUES LESS THAN (${TIME_PARTITIONS}));")
                    printf "$SQL4"
                    RET4=$(${MYSQL_CMD} -e "${SQL4}")

                    if [ "${RET4}" != "" ];then
                        echo  ${RET4}
                        echo "${SQL4}"
                    else
                        printf "table %-12s create partitions p${PARTITIONS_CREATE_EVERY_DAY}\n" ${TABLE_NAME}
                    fi
                fi
            fi
        done
    done
}

function drop_partitions_history() {
    #删除历史表分区
    for PARTITIONS_DELETE_DAYS_AGO in $(date +"%Y%m%d" --date="${HISTORY_DAYS} days ago")
    do
        for TABLE_NAME in ${HISTORY_TABLE}
        do
            SQL=$(echo -e  "show create table ${TABLE_NAME};")
            RET=$(${MYSQL_CMD} -e "${SQL}"|grep "p${PARTITIONS_DELETE_DAYS_AGO}"|wc -l)
            if [ "${RET}" == "1" ];then
                SQL=$(echo "ALTER TABLE ${TABLE_NAME} DROP PARTITION p${PARTITIONS_DELETE_DAYS_AGO};")
                RET=$(${MYSQL_CMD} -e "${SQL}")
                if [ "${RET}" != "" ];then
                    echo  ${RET}
                    echo "${SQL}"
                else
                    printf "table %-12s drop partitions p${PARTITIONS_DELETE_DAYS_AGO}\n" ${TABLE_NAME}
                fi
            fi
        done
    done
}

function create_partitions_trend() {
    #创建趋势表分区
    for PARTITIONS_CREATE_EVERY_MONTHS in $(date +"%Y%m") $(date +"%Y%m" --date='1 months') $(date +"%Y%m" --date='2 months') $(date +"%Y%m" --date='3 months') $(date +"%Y%m" --date='4 months') $(date +"%Y%m" --date='5 months')
    do
        TIME_PARTITIONS=$(date -d "$(echo ${PARTITIONS_CREATE_EVERY_MONTHS}01 00:00:00)" +%s)
        for TABLE_NAME in ${TREND_TABLE}
        do
            SQL1=$(echo "show create table ${TABLE_NAME};")
            RET1=$(${MYSQL_CMD} -e "${SQL1}"|grep "PARTITION BY RANGE"|wc -l)
            #表结构中的表分区不存在，则创建
            if [ "${RET1}" == "0" ];then
                SQL2=$(echo "ALTER TABLE $TABLE_NAME PARTITION BY RANGE( clock ) (PARTITION p${PARTITIONS_CREATE_EVERY_MONTHS}  VALUES LESS THAN (${TIME_PARTITIONS}));")
                RET2=$(${MYSQL_CMD} -e "${SQL2}")
                if [ "${RET2}" != "" ];then
                    echo  ${RET2}
                    echo "${SQL2}"
                else
                    printf "table %-12s create partitions p${PARTITIONS_CREATE_EVERY_MONTHS}\n" ${TABLE_NAME}
                fi
                continue
            fi
            #表结构中的表分区已经存在，则创建分区
            if [ "${RET1}" != "0" ];then
                SQL3=$(echo "show create table ${TABLE_NAME};")
                RET3=$(${MYSQL_CMD} -e "${SQL3}"|grep "p${PARTITIONS_CREATE_EVERY_MONTHS}"|wc -l)
                if [ "${RET3}" == "0" ];then

                    SQL4=$(echo "ALTER TABLE ${TABLE_NAME}  ADD PARTITION (PARTITION p${PARTITIONS_CREATE_EVERY_MONTHS} VALUES LESS THAN (${TIME_PARTITIONS}));")
                    RET4=$(${MYSQL_CMD} -e "${SQL4}")
                    if [ "${RET4}" != "" ];then
                        echo  ${RET4}
                        echo "${SQL4}"
                    else
                        printf "table %-12s create partitions p${PARTITIONS_CREATE_EVERY_MONTHS}\n" ${TABLE_NAME}
                    fi
                fi
            fi
        done
    done
}

function drop_partitions_trend() {
    #删除趋势表分区
    for PARTITIONS_DELETE_MONTHS_AGO in $(date +"%Y%m" --date="${TREND_MONTHS} months ago")
    do
        for TABLE_NAME in ${TREND_TABLE}
        do
            SQL=$(echo "show create table ${TABLE_NAME};")
            RET=$(${MYSQL_CMD} -e "${SQL}"|grep "p${PARTITIONS_DELETE_MONTHS_AGO}"|wc -l)
            if [ "${RET}" == "1" ];then
                SQL=$(echo "ALTER TABLE ${TABLE_NAME} DROP PARTITION p${PARTITIONS_DELETE_MONTHS_AGO};")
                RET=$(${MYSQL_CMD} -e "${SQL}")
                if [ "${RET}" != "" ];then
                    echo  ${RET}
                    echo "${SQL}"
                else
                    printf "table %-12s drop partitions p${PARTITIONS_DELETE_MONTHS_AGO}\n" ${TABLE_NAME}
                fi
            fi
        done
    done
}

function main() {
    create_partitions_history
    #create_partitions_trend
    drop_partitions_history
    #drop_partitions_trend
}

main

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164