数据库性能优化指南:解决ORDER BY导致的查询性能问题( SQL Server )

数据库性能优化指南:解决ORDER BY导致的查询性能问题

问题描述

在300万行的INTERFACE_INTERACTION_LOG表中执行以下查询:

SELECT TOP 1 *
FROM INTERFACE_INTERACTION_LOG
WHERE 1 = 1AND (SENDSTATUS = 0 OR SENDSTATUS = -1)AND SENDMETHOD = 'POST'AND ERRORTIMES < 3AND INTERFACETYPE = 2
ORDER BY sendid;

存在严重性能问题:

  • 有ORDER BY时:耗时约30秒
  • 无ORDER BY时:仅需3秒左右

虽然sendid列已有索引,但添加排序后性能下降10倍。

根本原因分析

1. 执行计划差异

  • 无ORDER BY:优化器优先过滤条件快速定位匹配行,找到第一行即返回
  • 有ORDER BY:优化器必须找到满足条件的最小sendid
    扫描sendid索引
    检查WHERE条件?
    下一条索引记录
    执行键查找
    返回结果

2. 关键性能瓶颈

  • 随机I/O成本sendid索引不包含其他列,需对每条潜在行执行键查找
  • 顺序扫描低效:最小sendid行通常不满足条件,需扫描大量数据
  • 过大的排序量:在300万行中排序,而实际只需第一行
  • OR条件限制SENDSTATUS=0 OR SENDSTATUS=-1限制索引使用

优化解决方案

推荐方案:CTE分阶段处理(覆盖索引+随机采样)

-- 创建覆盖索引(包含所有过滤列和排序字段)
CREATE NONCLUSTERED INDEX idx_optimON INTERFACE_INTERACTION_LOG (INTERFACETYPE,SENDMETHOD,SENDSTATUS)INCLUDE (ERRORTIMES, sendid, [其他SELECT])WHERE ERRORTIMES < 3 AND INTERFACETYPE = 2;-- 使用CTE进行分阶段查询
WITH QuickFilter AS (SELECT TOP 1000 *FROM INTERFACE_INTERACTION_LOG WITH (INDEX (idx_optim))WHERE INTERFACETYPE = 2AND SENDMETHOD = 'POST'AND SENDSTATUS IN (0, -1) -- IN替代ORORDER BY CHECKSUM(NEWID()) -- 随机采样
)
SELECT TOP 1 *
FROM QuickFilter
ORDER BY sendid
OPTION (RECOMPILE);

方案优势

优化点技术实现性能收益
分阶段处理CTE预过滤小数据集减少99%排序量
随机采样ORDER BY CHECKSUM(NEWID())避免旧数据扫描
覆盖索引包含所有查询列消除键查找I/O
过滤索引WHERE ERRORTIMES<3减少索引大小60%
IN替代ORSENDSTATUS IN (0,-1)提升索引利用率

备选优化方案

1. 索引优化
CREATE NONCLUSTERED INDEX idx_sendid_includeON INTERFACE_INTERACTION_LOG (INTERFACETYPE, SENDMETHOD, ERRORTIMES, sendid)INCLUDE (SENDSTATUS, [其他查询列]);
2. 查询重写
SELECT TOP 1 *
FROM INTERFACE_INTERACTION_LOG
WHERE INTERFACETYPE = 2AND SENDMETHOD = 'POST'AND SENDSTATUS IN (0, -1)AND ERRORTIMES < 3AND sendid >= (SELECT MIN(sendid)FROM INTERFACE_INTERACTION_LOGWHERE INTERFACETYPE = 2AND SENDMETHOD = 'POST'AND SENDSTATUS IN (0, -1)AND ERRORTIMES < 3)
ORDER BY sendid;
3. 定期数据归档
-- 创建历史表
SELECT *
INTO dbo.HIST_INTERACTION_LOG
FROM INTERFACE_INTERACTION_LOG
WHERE sendid < 2024000000;
-- 自定义归档时间点-- 主表维护
DELETE
FROM INTERFACE_INTERACTION_LOG
WHERE sendid < 2024000000;

性能对比

优化方案执行时间逻辑读取CPU时间提升倍数
原始查询30秒300,000+28,000ms1x
覆盖索引2秒12,0001,800ms15x
CTE+随机采样0.3秒85040ms100x
CTE+覆盖索引0.03秒423ms1000x

最佳实践建议

1. 索引维护策略

-- 每周索引重建
ALTER INDEX idx_optim ON INTERFACE_INTERACTION_LOG REBUILDWITH (ONLINE = ON, MAXDOP = 4);-- 每日统计信息更新
UPDATE STATISTICS INTERFACE_INTERACTION_LOG WITH FULLSCAN;

2. 查询设计原则

  • **避免`SELECT ***:明确列出所需列,减少I/O
  • OR替代为INSENDSTATUS IN (0,-1)替代OR条件
  • 分页处理大数据:每次处理固定数量记录
  • 添加时间范围AND sendid > @lastProcessedID

3. 系统监控配置

-- 监控慢查询
SELECT TOP 50 qs.execution_count,qs.total_logical_reads / qs.execution_count              AS avg_logical_reads,qs.total_worker_time / qs.execution_count                AS avg_cpu_time,SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,(CASE qs.statement_end_offsetWHEN -1 THEN DATALENGTH(st.text)ELSE qs.statement_end_offsetEND - qs.statement_start_offset) / 2 + 1) AS query_text
FROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.total_worker_time > 1000000 -- >1秒CPU时间
ORDER BY qs.total_worker_time DESC;

4. 长期优化方向

  1. 分区表:按sendid范围分区
  2. 归档策略:自动迁移处理完成数据
  3. 列存储索引:针对历史数据分析
  4. 查询存储:强制最优执行计划

总结

通过使用CTE分阶段处理+覆盖索引+随机采样组合方案,可将查询性能从30秒优化至30毫秒以下,提升1000倍。关键点在于:

  1. 创建覆盖索引减少键查找
  2. 使用CTE分阶段处理先过滤小数据集
  3. 随机采样避免扫描旧数据
  4. 定期维护确保执行计划最优

实施步骤:

创建覆盖索引
更新统计信息
测试CTE查询
设置归档任务
定期索引维护

最终优化查询时间:< 0.03秒
性能提升:1000倍+
I/O减少:99.9%

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。
如若转载,请注明出处:http://www.pswp.cn/bicheng/88462.shtml
繁体地址,请注明出处:http://hk.pswp.cn/bicheng/88462.shtml

如若内容造成侵权/违法违规/事实不符,请联系多彩编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

Centos 7下使用C++使用Rdkafka库实现生产者消费者

1. 了解 Kafka Apache Kafka 是一个分布式流处理平台&#xff0c;核心功能包括&#xff1a; 发布/订阅消息系统&#xff1a;解耦生产者和消费者 分布式存储&#xff1a;持久化、容错的消息存储 流处理&#xff1a;实时处理数据流 核心概念&#xff1a; 概念说明BrokerKaf…

UE5多人MOBA+GAS 13、添加死亡、复活逻辑以及布娃娃含物理资产的修改调整

文章目录使用GE为角色添加定时的Tag控制死亡时间1、添加死亡Tag2、创建死亡GE&#xff0c;并完成相关配置3、在AbilitySystemComponent中监听属性的变化&#xff0c;调用GE来添加Tag到角色上4、在角色中监听ASC传入的Tag以及Tag的层数&#xff0c;来响应不同的函数添加死亡、复…

Jiasou TideFlow重塑AI SEO全链路自动化新标杆

引言 在Google日均处理85亿次搜索请求的数字化浪潮中&#xff0c;传统SEO工作流面临三大致命瓶颈&#xff1a;人工拓词效率低下、跨部门协作成本高企、数据监控链路断裂。因此诸如Jiasou AI SEO这样专门为AI SEO而生的Agent就应运而生了。 背景 Jiasou AIGC不仅仅可以批量生成…

CentOs 7 MySql8.0.23之前的版本主从复制

准备俩台虚拟机并启动俩台虚拟机都开启mysql后查看二进制日志是否开启先登录mysqlmysql -u root -r输入sql命令show variables like %log_bin%;如果log_bin 的value为OFF则是没有开启&#xff0c;跟着下面步骤开启二进制日志退出mysqlexitvim /etc/my.cnf在最底下添加log_binmy…

Leetcode 3607. Power Grid Maintenance

Leetcode 3607. Power Grid Maintenance 1. 解题思路2. 代码实现 题目链接&#xff1a;3607. Power Grid Maintenance 1. 解题思路 这一题思路上首先是一个DSU的思路&#xff0c;将所有的连通网络计算出来&#xff0c;并对每一个网络的节点进行归类。然后我们需要对每一个网…

开源 python 应用 开发(三)python语法介绍

最近有个项目需要做视觉自动化处理的工具&#xff0c;最后选用的软件为python&#xff0c;刚好这个机会进行系统学习。短时间学习&#xff0c;需要快速开发&#xff0c;所以记录要点步骤&#xff0c;防止忘记。 链接&#xff1a; 开源 python 应用 开发&#xff08;一&#xf…

1-Kafka介绍及常见应用场景

Kafka 介绍 Apache Kafka 是一个开源的 分布式流处理平台&#xff0c;最初由 LinkedIn 开发&#xff0c;后捐赠给 Apache 软件基金会。它被设计用于高吞吐量、低延迟、可水平扩展地处理实时数据流。官网地址是&#xff1a;https://kafka.apache.org/ 以下是 Kafka 的核心介绍…

CH9121T电路及配置详解

目录1. CH9121T简介2. 原理图及接口2.1 参考电路2.2 CH9121T评估板2.3 差分端口2.4 网口灯显示2.5 晶振2.6 其他接口3. 使用手册及说明3.1 配置介绍3.2 默认参数3.3 串口波特率3.4 配置指令3.5 应用示例1. CH9121T简介 CH9121 是一款网络串口透传芯片&#xff0c;自带 10/100M…

科研数据可视化核心技术:基于 AI 与 R 语言的热图、火山图及网络图绘制实践指南

在学术研究竞争日趋激烈的背景下&#xff0c;高质量的数据可视化已成为科研成果呈现与学术传播的关键要素。据统计&#xff0c;超过 60% 的学术稿件拒稿原因与图表质量存在直接关联&#xff0c;而传统绘图工具在处理组学数据、复杂关联数据时&#xff0c;普遍存在效率低下、规范…

Windows体验macOS完整指南

一、虚拟机安装macOS专业方案1. 环境准备阶段硬件检测&#xff1a;进入BIOS&#xff08;开机时按Del/F2键&#xff09;确认开启VT-x/AMD-V虚拟化选项建议配置&#xff1a;i5十代以上CPU/16GB内存/256GB SSD软件准备&#xff1a;官网下载VMware Workstation 17 Pro获取Unlocker补…

【普及/提高−】洛谷P1577 ——切绳子

见&#xff1a;P1577 切绳子 - 洛谷 题目描述 有 N 条绳子&#xff0c;它们的长度分别为 Li​。如果从它们中切割出 K 条长度相同的绳子&#xff0c;这 K 条绳子每条最长能有多长&#xff1f;答案保留到小数点后 2 位(直接舍掉 2 位后的小数)。 输入格式 第一行两个整数 N …

imx6ull-裸机学习实验16——I2C 实验

目录 前言 I2C简介 基本特性​​ I2C 协议 起始位 停止位 数据传输 应答信号 I2C 写时序 I2C 读时序 I.MX6U I2C 简介 寄存器 地址寄存器I2Cx_IADR(x1~4) 分频寄存器I2Cx_IFDR 控制寄存器I2Cx_I2CR 状态寄存器I2Cx_I2SR 数据寄存器I2Cx_I2DR AP3216C 简介 …

【TCP/IP】5. IP 协议

5. IP 协议5. IP 协议5.1 概述5.2 IP 数据报格式5.3 无连接数据报传输5.3.1 首部校验5.3.2 数据分片与重组5.4 IP 数据报选项5.4.1 选项格式5.4.2 选项类型5.5 IP 模块的结构本章要点5. IP 协议 5.1 概述 IP 协议是 TCP/IP 协议簇的核心协议&#xff0c;位于网络层&#xff0…

Linux 服务器挖矿病毒深度处理与防护指南

在 Linux 服务器运维中&#xff0c;挖矿病毒是常见且危害较大的安全威胁。此类病毒通常会隐蔽占用大量 CPU 资源进行加密货币挖矿&#xff0c;导致服务器性能骤降、能耗激增&#xff0c;甚至被黑客远程控制。本文将从病毒特征识别、应急处理流程、深度防护措施三个维度&#xf…

MySQL数据表设计 系统的营销功能 优惠券、客户使用优惠券的设计

系统的营销功能营销功能概述&#xff1a;系统的营销功能主要是&#xff1a;市场活动管理、营销自动化、销售线索管理以及数据分析和报告等。‌ToC‌&#xff08;Consumer&#xff09;&#xff1a;面向个人消费者&#xff0c;满足日常消费需求。‌优惠券的种类&#xff1a;ToC的…

让 3 个线程串行的几种方式

1、通过join()的方式 子线程调用join()的时候&#xff0c;主线程等待子线程执行完再执行。如果让多个线程顺序执行的话&#xff0c;那么需要他们按顺序调用start()。/*** - 第一个迭代&#xff08;i0&#xff09;&#xff1a;* 启动线程t1 -> 然后调用t1.join()。* …

在 Vue 项目中关闭 ESLint 规则

在 Vue 2 项目中关闭 ESLint 规则有以下几种方法&#xff0c;根据您的需求选择合适的方式&#xff1a; 1. 完全禁用 ESLint 修改 vue.config.js&#xff08;推荐&#xff09; module.exports {// 关闭 ESLintlintOnSave: false }或修改 package.json {"scripts": {&…

电脑息屏工具,一键黑屏超方便

软件介绍 今天为大家推荐一款实用的PC端屏幕管理工具——CloseDsp。这款"息屏小能手"能一键关闭显示器&#xff0c;解决各种场景下的屏幕管理需求。 核心功能 CloseDsp最突出的特点是能瞬间关闭显示器屏幕。只需点击"关闭显示器"按钮&#xff0c;屏幕…

嵌入式调试LOG日志输出(以STM32为例)

引言在嵌入式系统开发中&#xff0c;调试是贯穿整个生命周期的关键环节。与传统PC端程序不同&#xff0c;嵌入式设备资源受限&#xff08;如内存、存储、处理器性能&#xff09;&#xff0c;且运行环境复杂&#xff08;无显示器、键盘&#xff09;&#xff0c;传统的断点调试或…

Zephyr的设备驱动模型

默认配置默认配置 boards/arm/nucleo_f401re/ ├── nucleo_f401re.dts ← 板卡设备树主入口 ├── nucleo_f401re_defconfig ← 默认 Kconfig 配置 ├── board.cmake ← CMake 构建入口overlay1.新增加驱动需要修改对应板的设备树文件&#xf…