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

2024-03-26 16:10

本文主要是介绍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

相关文章

MySQL中EXISTS与IN用法使用与对比分析

《MySQL中EXISTS与IN用法使用与对比分析》在MySQL中,EXISTS和IN都用于子查询中根据另一个查询的结果来过滤主查询的记录,本文将基于工作原理、效率和应用场景进行全面对比... 目录一、基本用法详解1. IN 运算符2. EXISTS 运算符二、EXISTS 与 IN 的选择策略三、性能对比

MySQL常用字符串函数示例和场景介绍

《MySQL常用字符串函数示例和场景介绍》MySQL提供了丰富的字符串函数帮助我们高效地对字符串进行处理、转换和分析,本文我将全面且深入地介绍MySQL常用的字符串函数,并结合具体示例和场景,帮你熟练... 目录一、字符串函数概述1.1 字符串函数的作用1.2 字符串函数分类二、字符串长度与统计函数2.1

SQL Server跟踪自动统计信息更新实战指南

《SQLServer跟踪自动统计信息更新实战指南》本文详解SQLServer自动统计信息更新的跟踪方法,推荐使用扩展事件实时捕获更新操作及详细信息,同时结合系统视图快速检查统计信息状态,重点强调修... 目录SQL Server 如何跟踪自动统计信息更新:深入解析与实战指南 核心跟踪方法1️⃣ 利用系统目录

MySQL 内存使用率常用分析语句

《MySQL内存使用率常用分析语句》用户整理了MySQL内存占用过高的分析方法,涵盖操作系统层确认及数据库层bufferpool、内存模块差值、线程状态、performance_schema性能数据... 目录一、 OS层二、 DB层1. 全局情况2. 内存占js用详情最近连续遇到mysql内存占用过高导致

Mysql中设计数据表的过程解析

《Mysql中设计数据表的过程解析》数据库约束通过NOTNULL、UNIQUE、DEFAULT、主键和外键等规则保障数据完整性,自动校验数据,减少人工错误,提升数据一致性和业务逻辑严谨性,本文介绍My... 目录1.引言2.NOT NULL——制定某列不可以存储NULL值2.UNIQUE——保证某一列的每一

解密SQL查询语句执行的过程

《解密SQL查询语句执行的过程》文章讲解了SQL语句的执行流程,涵盖解析、优化、执行三个核心阶段,并介绍执行计划查看方法EXPLAIN,同时提出性能优化技巧如合理使用索引、避免SELECT*、JOIN... 目录1. SQL语句的基本结构2. SQL语句的执行过程3. SQL语句的执行计划4. 常见的性能优

SQL Server 中的 WITH (NOLOCK) 示例详解

《SQLServer中的WITH(NOLOCK)示例详解》SQLServer中的WITH(NOLOCK)是一种表提示,等同于READUNCOMMITTED隔离级别,允许查询在不获取共享锁的情... 目录SQL Server 中的 WITH (NOLOCK) 详解一、WITH (NOLOCK) 的本质二、工作

MySQL 强制使用特定索引的操作

《MySQL强制使用特定索引的操作》MySQL可通过FORCEINDEX、USEINDEX等语法强制查询使用特定索引,但优化器可能不采纳,需结合EXPLAIN分析执行计划,避免性能下降,注意版本差异... 目录1. 使用FORCE INDEX语法2. 使用USE INDEX语法3. 使用IGNORE IND

Linux系统中查询JDK安装目录的几种常用方法

《Linux系统中查询JDK安装目录的几种常用方法》:本文主要介绍Linux系统中查询JDK安装目录的几种常用方法,方法分别是通过update-alternatives、Java命令、环境变量及目... 目录方法 1:通过update-alternatives查询(推荐)方法 2:检查所有已安装的 JDK方

SQL Server安装时候没有中文选项的解决方法

《SQLServer安装时候没有中文选项的解决方法》用户安装SQLServer时界面全英文,无中文选项,通过修改安装设置中的国家或地区为中文中国,重启安装程序后界面恢复中文,解决了问题,对SQLSe... 你是不是在安装SQL Server时候发现安装界面和别人不同,并且无论如何都没有中文选项?这个问题也