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