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

2024-03-26 16:10
文章标签 mysql 查询 优化 索引 一次 database 不要 蒙蔽 双眼

本文主要是介绍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=12HISTORY_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')doprintf "$PARTITIONS_CREATE_EVERY_DAY"TIME_PARTITIONS=${PARTITIONS_CREATE_EVERY_DAY}2359printf "$TIME_PARTITIONS"for TABLE_NAME in ${HISTORY_TABLE}doSQL1=$(echo "show create table ${TABLE_NAME};")RET1=$(${MYSQL_CMD} -e "${SQL1}"|grep "PARTITION BY RANGE"|wc -l)#表结构中的表分区不存在，则创建if [ "${RET1}" == "0" ];thenSQL2=$(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}" != "" ];thenecho  ${RET2}echo "${SQL2}"elseprintf "table %-12s create partitions p${PARTITIONS_CREATE_EVERY_DAY}\n" ${TABLE_NAME}ficontinuefi#表结构中的表分区已经存在，则创建分区if [ "${RET1}" != "0" ];thenSQL3=$(echo "show create table ${TABLE_NAME};")RET3=$(${MYSQL_CMD} -e "${SQL3}"|grep "p${PARTITIONS_CREATE_EVERY_DAY}"|wc -l)if [ "${RET3}" == "0" ];thenTIME_PARTITIONS=${PARTITIONS_CREATE_EVERY_DAY}2359SQL4=$(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}" != "" ];thenecho  ${RET4}echo "${SQL4}"elseprintf "table %-12s create partitions p${PARTITIONS_CREATE_EVERY_DAY}\n" ${TABLE_NAME}fififidonedone
}function drop_partitions_history() {#删除历史表分区for PARTITIONS_DELETE_DAYS_AGO in $(date +"%Y%m%d" --date="${HISTORY_DAYS} days ago")dofor TABLE_NAME in ${HISTORY_TABLE}doSQL=$(echo -e  "show create table ${TABLE_NAME};")RET=$(${MYSQL_CMD} -e "${SQL}"|grep "p${PARTITIONS_DELETE_DAYS_AGO}"|wc -l)if [ "${RET}" == "1" ];thenSQL=$(echo "ALTER TABLE ${TABLE_NAME} DROP PARTITION p${PARTITIONS_DELETE_DAYS_AGO};")RET=$(${MYSQL_CMD} -e "${SQL}")if [ "${RET}" != "" ];thenecho  ${RET}echo "${SQL}"elseprintf "table %-12s drop partitions p${PARTITIONS_DELETE_DAYS_AGO}\n" ${TABLE_NAME}fifidonedone
}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')doTIME_PARTITIONS=$(date -d "$(echo ${PARTITIONS_CREATE_EVERY_MONTHS}01 00:00:00)" +%s)for TABLE_NAME in ${TREND_TABLE}doSQL1=$(echo "show create table ${TABLE_NAME};")RET1=$(${MYSQL_CMD} -e "${SQL1}"|grep "PARTITION BY RANGE"|wc -l)#表结构中的表分区不存在，则创建if [ "${RET1}" == "0" ];thenSQL2=$(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}" != "" ];thenecho  ${RET2}echo "${SQL2}"elseprintf "table %-12s create partitions p${PARTITIONS_CREATE_EVERY_MONTHS}\n" ${TABLE_NAME}ficontinuefi#表结构中的表分区已经存在，则创建分区if [ "${RET1}" != "0" ];thenSQL3=$(echo "show create table ${TABLE_NAME};")RET3=$(${MYSQL_CMD} -e "${SQL3}"|grep "p${PARTITIONS_CREATE_EVERY_MONTHS}"|wc -l)if [ "${RET3}" == "0" ];thenSQL4=$(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}" != "" ];thenecho  ${RET4}echo "${SQL4}"elseprintf "table %-12s create partitions p${PARTITIONS_CREATE_EVERY_MONTHS}\n" ${TABLE_NAME}fififidonedone
}function drop_partitions_trend() {#删除趋势表分区for PARTITIONS_DELETE_MONTHS_AGO in $(date +"%Y%m" --date="${TREND_MONTHS} months ago")dofor TABLE_NAME in ${TREND_TABLE}doSQL=$(echo "show create table ${TABLE_NAME};")RET=$(${MYSQL_CMD} -e "${SQL}"|grep "p${PARTITIONS_DELETE_MONTHS_AGO}"|wc -l)if [ "${RET}" == "1" ];thenSQL=$(echo "ALTER TABLE ${TABLE_NAME} DROP PARTITION p${PARTITIONS_DELETE_MONTHS_AGO};")RET=$(${MYSQL_CMD} -e "${SQL}")if [ "${RET}" != "" ];thenecho  ${RET}echo "${SQL}"elseprintf "table %-12s drop partitions p${PARTITIONS_DELETE_MONTHS_AGO}\n" ${TABLE_NAME}fifidonedone
}function main() {create_partitions_history#create_partitions_trenddrop_partitions_history#drop_partitions_trend
}main

这篇关于MYSQL一次慢查询优化，不要被“索引“蒙蔽了双眼的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/849069。

相关文章

SpringBoot3实现Gzip压缩优化的技术指南

SpringBoot3实现Gzip压缩优化的技术指南

《SpringBoot3实现Gzip压缩优化的技术指南》随着Web应用的用户量和数据量增加,网络带宽和页面加载速度逐渐成为瓶颈,为了减少数据传输量,提高用户体验,我们可以使用Gzip压缩HTTP响应,... 目录1、简述2、配置2.1 添加依赖2.2 配置 Gzip 压缩3、服务端应用4、前端应用4.1 N
阅读更多...
MySQL双主搭建+keepalived高可用的实现

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制，确认复制是否成功4.同
阅读更多...
Spring Boot + MyBatis Plus 高效开发实战从入门到进阶优化(推荐)

Spring Boot + MyBatis Plus 高效开发实战从入门到进阶优化(推荐)

《SpringBoot+MyBatisPlus高效开发实战从入门到进阶优化(推荐)》本文将详细介绍SpringBoot+MyBatisPlus的完整开发流程,并深入剖析分页查询、批量操作、动... 目录Spring Boot + MyBATis Plus 高效开发实战：从入门到进阶优化1. MyBatis
阅读更多...
MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL？1.2 动态SQL的优点1.3 动态S
阅读更多...
Mysql表的简单操作(基本技能)

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例：修改表在数据库中，表的操作主要
阅读更多...
Python如何使用__slots__实现节省内存和性能优化

Python如何使用__slots__实现节省内存和性能优化

《Python如何使用__slots__实现节省内存和性能优化》你有想过,一个小小的__slots__能让你的Python类内存消耗直接减半吗,没错,今天咱们要聊的就是这个让人眼前一亮的技巧,感兴趣的... 目录背景：内存吃得满满的类__slots__：你的内存管理小助手举个大概的例子：看看效果如何？1.
阅读更多...
一文详解SpringBoot响应压缩功能的配置与优化

一文详解SpringBoot响应压缩功能的配置与优化

《一文详解SpringBoot响应压缩功能的配置与优化》SpringBoot的响应压缩功能基于智能协商机制,需同时满足很多条件,本文主要为大家详细介绍了SpringBoot响应压缩功能的配置与优化,需... 目录一、核心工作机制1.1 自动协商触发条件1.2 压缩处理流程二、配置方案详解2.1 基础YAML
阅读更多...
mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言：第一步：第二步：第三步：总结：前言：当你想通过命令窗口想打开mysql时候发现提http://www.cpp
阅读更多...
MySQL大表数据的分区与分库分表的实现

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区？1.2 分区的类型1.3 分区的优点1.4 分
阅读更多...
MySQL错误代码2058和2059的解决办法

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》：本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法（敲重点！！！）1. php前置理解2058和2059的错误
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2