IT需求提示未读信息查询：深度技术解析与性能优化指南【类似：钉钉已读 功能】

IT需求提示未读信息查询：深度技术解析与性能优化指南【类似：钉钉已读 功能】

DROP TABLE IF EXISTS rs_kpi_it_need_tip;
CREATE TABLE IF NOT EXISTS `rs_kpi_it_need_tip`
(`id`         bigint          NOT NULL AUTO_INCREMENT COMMENT '主键ID，唯一标识一条提示记录',`need_id`    bigint          NOT NULL COMMENT '关联IT需求ID',`route_name` varchar(255)    NOT NULL COMMENT '提示位置名称（路由名称，如：IT需求列表）',`title`      varchar(100)    NOT NULL COMMENT '提示标题（简洁概括提示核心内容，如：“IT需求提交前需补全附件”）',`content`    text                     DEFAULT NULL COMMENT '内容MD格式',`ct`         datetime(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',`creator`    bigint unsigned NOT NULL COMMENT '创建人id',`ut`         datetime(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',`updater`    bigint unsigned NOT NULL COMMENT '更新人id',`is_deleted` tinyint(1)      NOT NULL DEFAULT 0 COMMENT ' 逻辑删除标识（0=未删除，1=已删除）',PRIMARY KEY (`id`),-- 索引1：按“提示位置”查询（前端路由匹配提示时高频使用）INDEX `idx_route_name` (`route_name`),-- 索引2：逻辑删除+路由（避免查询已删除数据，提升筛选效率）INDEX `idx_is_deleted_route` (`is_deleted`, `route_name`)
) COMMENT 'IT需求提示';DROP TABLE IF EXISTS rs_kpi_it_need_tip_relation;
CREATE TABLE IF NOT EXISTS `rs_kpi_it_need_tip_relation`
(`tip_id`  bigint NOT NULL COMMENT '提示记录ID',`user_id` bigint NOT NULL COMMENT '关联IT需求人ID'
) COMMENT '用户已读-IT需求提示关系表';

一、业务模型与数据特征分析

未读信息查询的本质是集合差运算：需从"有效提示集合"（rs_kpi_it_need_tip中is_deleted=0的记录）中排除"用户已读集合"（rs_kpi_it_need_tip_relation中该用户的tip_id）。两张核心表的特征决定了查询优化的方向：

表名数据增长模式核心字段基数索引现状性能敏感点
rs_kpi_it_need_tip随业务需求增长，总量可控（万级至十万级）route_name（低基数，如10-20个路由）、is_deleted（极低基数）有idx_is_deleted_route联合索引过滤未删除+路由时需高效命中索引
rs_kpi_it_need_tip_relation随用户数和提示数呈线性增长（可能达百万/千万级）user_id（高基数）、tip_id（中高基数）无索引按用户查询已读记录时易全表扫描

关键结论：已读关系表（rs_kpi_it_need_tip_relation）是性能瓶颈的核心，其索引设计直接决定查询效率。

二、三种查询方式的执行原理深度剖析

（一）LEFT JOIN + IS NULL：关联过滤模式

执行链路
  1. 驱动表选择：MySQL优化器通常选择小表作为驱动表。若rs_kpi_it_need_tip过滤后数据量小（如特定路由下的提示），则作为驱动表；否则可能选择已读表。
  2. 关联逻辑：
    • 对驱动表每条记录，通过ON条件（t.id = r.tip_id AND r.user_id=?）到被驱动表匹配
    • 未匹配的记录保留，最终通过r.tip_id IS NULL过滤出未读数据
  3. 隐式成本：
    • 需生成临时关联结果集（内存或磁盘临时表），大数据量时触发Using temporary
    • 若关联字段无索引，被驱动表需全表扫描（type: ALL），时间复杂度O(N*M)
执行计划特征
id  select_type  table  type  key                  rows  Extra
1   SIMPLE       t      ref   idx_is_deleted_route 100   Using where; Using index
1   SIMPLE       r      ref   idx_user_tip         50    Using where; Not exists
  • 当已读表有索引时，r表type为ref，否则为ALL
  • 可能出现Using join buffer（无索引时），性能骤降

（二）NOT EXISTS：存在性判定模式

执行链路
  1. 主表扫描：先扫描rs_kpi_it_need_tip并过滤is_deleted=0（利用idx_is_deleted_route索引）
  2. 子查询短路执行：
    • 对主表每条记录，执行子查询EXISTS (SELECT 1 FROM r WHERE r.tip_id=t.id AND r.user_id=?)
    • 一旦找到匹配记录，立即终止子查询（短路特性），无需扫描全表
  3. 优化器优势：
    • 无需生成临时表，直接通过布尔判断过滤记录
    • 子查询可被优化为"索引查找"，而非"索引扫描"
执行计划特征
id  select_type  table  type  key                  rows  Extra
1   SIMPLE       t      ref   idx_is_deleted_route 100   Using where; Using index
1   SIMPLE       r      ref   idx_user_tip         1     Using where; Not exists
  • 子查询type为ref时，说明通过索引快速判定存在性
  • rows列接近1，表明短路执行生效

（三）NOT IN：集合排除模式

执行链路
  1. 子查询执行：先执行SELECT r.tip_id FROM r WHERE user_id=?，生成已读ID集合（临时内存表）
  2. 主表过滤：扫描主表并判断t.id是否不在上述集合中
  3. 风险点：
    • 子查询结果集过大时，内存占用激增（O(M)空间复杂度）
    • NULL值陷阱：若子查询返回NULL，由于NULL <> 任何值为UNKNOWN，导致整体结果为空
    • MySQL对NOT IN优化较弱，即使有索引也可能全量扫描子查询结果
执行计划特征
id  select_type   table  type  key                  rows  Extra
1   PRIMARY       t      ref   idx_is_deleted_route 100   Using where; Using index
2   SUBQUERY      r      ref   idx_user_tip         50    Using index
  • 子查询作为SUBQUERY单独执行，结果被临时存储
  • 主查询可能出现Using where; Using filesort（排序排除）

三种方式的核心差异对比

维度LEFT JOIN + IS NULLNOT EXISTSNOT IN
时间复杂度O(N log M)（有索引）O(N log M)（有索引）O(N + M)（子查询全量）
空间复杂度O(N + M)（临时关联集）O(1)（无临时集）O(M)（已读ID集合）
索引依赖度高（需关联字段索引）中（子查询字段索引）高（子查询字段索引）
大数据量适应性差（临时集膨胀）优（短路执行）差（内存溢出风险）
NULL值兼容性兼容兼容不兼容（子查询含NULL时）

三、索引设计的数学原理与实践

（一）已读关系表的最优索引：(user_id, tip_id)复合索引

设计依据
  1. 前缀匹配原则：查询条件为user_id=? AND tip_id=?，复合索引前缀user_id可快速定位用户
  2. 覆盖查询需求：索引包含tip_id，无需回表（Using index）
  3. 基数权衡：user_id基数高于tip_id，作为前缀可更快速过滤数据
性能提升测算
  • 无索引时：查询某用户已读记录需扫描全表（100万行），耗时约1000ms
  • 有索引时：通过B+树定位，仅需3-4次IO（约10ms），性能提升100倍
反例分析：(tip_id, user_id)索引为何不优？
  • 查询条件为user_id=?，无法匹配索引前缀，导致索引失效（type: ALL）

（二）主表索引的优化补充

针对路由筛选场景
  • 现有idx_is_deleted_route (is_deleted, route_name)可覆盖WHERE is_deleted=0 AND route_name=?
  • 若需按创建时间排序（如ORDER BY ct DESC），可扩展为(is_deleted, route_name, ct)复合索引，避免Using filesort
索引选择性验证
-- 计算索引选择性（越接近1越好）
SELECT COUNT(DISTINCT route_name)/COUNT(*) FROM rs_kpi_it_need_tip WHERE is_deleted=0;
  • 若选择性低（如<0.1），索引收益有限，可能被优化器忽略

四、不同数据规模下的性能实测与调优策略

（一）中小规模数据（提示表<10万，已读表<100万）

性能基准（有索引时）
查询方式平均耗时QPS内存占用
LEFT JOIN + IS NULL80ms12500中
NOT EXISTS65ms15300低
NOT IN70ms14200中
调优建议
  • 可根据开发习惯选择，推荐NOT EXISTS（略优）
  • 避免SELECT *，只查询必要字段（利用覆盖索引）

（二）中大规模数据（提示表10万100万，已读表100万1000万）

性能瓶颈
  • LEFT JOIN 可能触发Using temporary和Using filesort（临时表排序）
  • NOT IN 子查询结果集过大，导致内存溢出（tmp_table_size限制）
实测数据（已读表500万行）
查询方式平均耗时关键执行计划项
LEFT JOIN + IS NULL1200msUsing temporary
NOT EXISTS110msUsing index; Not exists
NOT IN890msUsing where; Using filesort
调优建议

  1. 强制索引：对已读表添加FORCE INDEX (idx_user_tip)避免优化器误判

  2. 拆分查询：先查已读ID到内存，再批量查询未读（适合应用层优化）

    List<Long> readTipIds = jdbc.query("SELECT tip_id FROM r WHERE user_id=?", ...);
List<Tip> unreadTips = jdbc.query("SELECT * FROM t WHERE id NOT IN (?,?) AND ...", readTipIds);

（三）超大规模数据（提示表>100万，已读表>1000万）

架构级优化
  1. 分库分表：
    • 已读表按user_id哈希分片，避免单表过大
    • 提示表按route_name或时间范围分区
  2. 缓存策略：
    • 用Redis存储用户未读计数（user:unread:count:{userId}）
    • 缓存近期未读列表（设置合理TTL，如1小时）
  3. 异步更新：
    • 已读状态变更通过消息队列异步写入数据库
    • 读多写少场景下，先更新缓存再异步落库
业务妥协方案
  • 未读数量超过阈值（如99）时显示"99+"，避免全量查询
  • 分页加载未读数据，限制单页数量（如20条）

五、数据库引擎差异与适配

（一）MySQL vs PostgreSQL

特性MySQL 8.0PostgreSQL 14
NOT EXISTS 优化优秀（短路执行）优秀（与LEFT JOIN等效）
索引类型支持B+树为主支持B+树、GiST、GIN等
临时表处理内存不足时写入磁盘自动管理临时表空间
执行计划分析EXPLAIN FORMAT=JSONEXPLAIN ANALYZE（实时统计）
PostgreSQL优化建议
  • 已读表索引：CREATE INDEX idx_user_tip ON rs_kpi_it_need_tip_relation (user_id, tip_id) INCLUDE (tip_id);（覆盖索引）
  • 利用EXPLAIN ANALYZE获取实际执行时间，精准调优

（二）索引维护策略

  1. 定期重建：已读表索引碎片化严重时（SHOW INDEX FROM r查看Cardinality）

    ALTER TABLE rs_kpi_it_need_tip_relation REBUILD INDEX idx_user_tip;

  2. 统计信息更新：

    ANALYZE rs_kpi_it_need_tip_relation;  -- PostgreSQL
ANALYZE TABLE rs_kpi_it_need_tip_relation;  -- MySQL

    确保优化器获取准确的行数估算，避免错误的执行计划

六、结论与最佳实践

核心结论

  1. 查询方式优先级：NOT EXISTS > LEFT JOIN + IS NULL > NOT IN（全场景适用）
  2. 索引必须项：rs_kpi_it_need_tip_relation添加(user_id, tip_id)复合索引，无妥协空间
  3. 性能临界点：已读表数据量超过100万行时，必须通过索引+执行计划分析进行优化

分步实施指南

  1. 紧急优化：立即为已读表添加(user_id, tip_id)索引
  2. 中期优化：将生产环境查询统一迁移至NOT EXISTS模式，清理SELECT *
  3. 长期规划：
    • 监控已读表数据增长趋势，达千万级时启动分库分表
    • 引入缓存层降低数据库压力，尤其高频查询场景

通过以上深度优化，未读信息查询的响应时间可从秒级降至毫秒级，支撑高并发业务场景（如10万用户同时在线查询）。

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

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

相关文章

Django中的软删除

Django中的软删除

软删除&#xff08;Soft Delete&#xff09;是一种数据删除策略&#xff0c;它并不真正从数据库中删除记录&#xff0c;而是通过标记&#xff08;如 is_deleted 字段&#xff09;来表示记录已被删除。 这样做的好处是可以保留数据历史&#xff0c;支持数据恢复和审计。 在 Djan…
阅读更多...
JavaEE 进阶第四期：开启前端入门之旅(四)

JavaEE 进阶第四期：开启前端入门之旅(四)

专栏&#xff1a;JavaEE 进阶跃迁营 个人主页&#xff1a;手握风云 目录 一、常用CSS 1.1. border 1.2. width/height 1.3. padding&#xff1a;内边距 1.4. margin&#xff1a;外边距 二、初始JavaScript 2.1. JavaScript是什么 2.2. 发展历史 2.3. JavaScript 和 HT…
阅读更多...
学习日记-SpringMVC-day49-9.4

学习日记-SpringMVC-day49-9.4

知识点&#xff1a;1.RequestMapping&#xff08;3&#xff09;知识点核心内容重点RequestMapping注解的parameters属性通过parameters指定请求参数条件&#xff08;如bookID&#xff09;&#xff0c;控制请求匹配规则&#xff08;必须包含/排除特定参数或值&#xff09;参数存…
阅读更多...
【Day 50 】Linux-nginx反向代理与负载均衡

【Day 50 】Linux-nginx反向代理与负载均衡

概述在现代 Web 架构中&#xff0c;Nginx 作为高并发、高性能的 HTTP 和反向代理服务器&#xff0c;被广泛应用于提升服务性能、增强系统安全性和实现负载均衡。其中&#xff0c;反向代理能够隐藏后端服务器信息并优化请求处理流程&#xff0c;负载均衡则可将请求分发到多个后端…
阅读更多...
vue中配置 ts

vue中配置 ts

在 Vue 项目中配置 TypeScript&#xff08;TS&#xff09;可以提升代码的类型安全性和开发体验。以下是在 Vue 项目&#xff08;基于 Vite&#xff09;中配置 TypeScript 的详细步骤和关键配置&#xff1a; 一、创建支持 TypeScript 的 Vue 项目 如果是新建项目&#xff0c;推…
阅读更多...
阿里云镜像地址获取，并安装 docker的mysql和nginx等服务，java,python,ffmpeg,go等环境

阿里云镜像地址获取，并安装 docker的mysql和nginx等服务，java,python,ffmpeg,go等环境

阿里云那个镜像地址获取 阿里云镜像加速器不是一个通用的 registry.cn-hangzhou.aliyuncs.com&#xff0c;而是你账号专属的&#xff0c;比如这样&#xff1a; https://abcd1234.mirror.aliyuncs.com&#x1f449; 登录阿里云控制台获取&#xff1a; 阿里云镜像加速器 然后替…
阅读更多...
conda环境导出

conda环境导出

1. 激活你想要打包的环境首先&#xff0c;确保你激活了你要打包的 conda 环境&#xff1a;conda activate qwen2. 导出环境配置使用 conda 命令将当前环境的配置导出为一个 .yml 文件&#xff0c;记录下环境中所有的依赖和版本&#xff1a;conda list --export > techgpt_en…
阅读更多...
openEuler2403安装部署Kafka

openEuler2403安装部署Kafka

文章目录 openEuler2403安装部署Kafka with KRaft一、前言1.简介2.架构3.环境 二、正文1.部署服务器2.基础环境1&#xff09;JDK 安装部署2&#xff09;关闭防火墙 3.单机部署1&#xff09;下载软件包2&#xff09;修改配置文件3&#xff09;格式化存储目录4&#xff09;单机启…
阅读更多...
发布工业智能体，云从科技打造制造业AI“运营大脑”

发布工业智能体，云从科技打造制造业AI“运营大脑”

近日&#xff0c;在2025世界智能产业博览会重庆市工业智能体首发仪式现场&#xff0c;云从科技重磅发布经营决策-产线运营智能体&#xff0c;为制造业的智能化转型提供了全新的解决方案。该智能体的亮相&#xff0c;不仅代表着人工智能技术在工业领域的深度应用&#xff0c;更标…
阅读更多...
【Linux基础】parted命令详解：从入门到精通的磁盘分区管理完全指南

【Linux基础】parted命令详解：从入门到精通的磁盘分区管理完全指南

目录 前言 1 parted命令概述 1.1 什么是parted 1.2 parted与fdisk的对比 1.3 parted的主要优势 2 parted命令的安装与基本语法 2.1 在不同Linux发行版中安装parted 2.2 parted的基本语法 2.3 parted的工作模式 3 parted交互式命令详解 3.1 交互式操作流程 3.2 主要…
阅读更多...
如何在路由器上配置DHCP服务器？

如何在路由器上配置DHCP服务器？

在路由器上配置DHCP服务器的步骤因品牌&#xff08;如TP-Link、华为、小米、华硕等&#xff09;略有差异&#xff0c;但核心流程一致&#xff0c;主要包括登录管理界面、开启DHCP功能、设置IP地址池及相关参数。以下是通用操作指南&#xff1a; 一、准备工作 确保电脑/手机已连…
阅读更多...
HTML和CSS学习

HTML和CSS学习

HTML学习 注释 <!-- -->组成 告诉浏览器我是html文件<!DOCTYPE html> <title>浏览器标签</title> <body> <!--- 其中是主要内容 ---> <p> 段落 </p> </body> </html> (结束点…
阅读更多...
OpenTenBase vs MySQL vs Oracle，企业级应用数据库实盘对比分析

OpenTenBase vs MySQL vs Oracle，企业级应用数据库实盘对比分析

摘要 因为工作久了的缘故&#xff0c;接触过不少数据库。公司的管理系统用的MySQL&#xff0c;财务系统用的Oracle。随着时代发展&#xff0c;国产开源数据库已经在性能上能与这些国际知名顶尖数据库品牌相媲美&#xff0c;其中OpenTenBase以其开放环境和优越性能脱颖而出&…
阅读更多...
Oracle 备份与恢复常见的七大问题

Oracle 备份与恢复常见的七大问题

为了最大限度保障数据的安全性&#xff0c;同时能在不可预计灾难的情况下保证数据的快速恢复&#xff0c;需要根据数据的类型和重要程度制定相应的备份和恢复方案。在这个过程中&#xff0c;DBA的职责就是要保证数据库&#xff08;其它数据由其它岗位负责&#xff09;的高可用和…
阅读更多...
StringBuilder类的数据结构和扩容方式解读

StringBuilder类的数据结构和扩容方式解读

目录 StringBuilder是什么 核心特性&#xff1a; StringBuilder数据结构 1. 核心存储结构&#xff08;基于父类 AbstractStringBuilder&#xff09; 2. 类定义与继承关系 3. 数据结构的核心特点 StringBuilder数据结构的初始化方式 1. 无参构造&#xff1a;默认初始容量…
阅读更多...
LangChain实战（十七）：构建与PDF/PPT文档对话的AI助手

LangChain实战（十七）：构建与PDF/PPT文档对话的AI助手

本文是《LangChain实战课》系列的第十七篇，将专篇深入讲解如何构建能够与PDF和PPT文档进行智能对话的AI助手。通过学习本文，您将掌握复杂格式文档的解析技巧、文本与表格处理技术，以及实现精准问答的系统方法。 前言 在日常工作和学习中，PDF和PPT文档是我们最常接触的文档…
阅读更多...
鱼眼相机模型

鱼眼相机模型

鱼眼相机模型 最近涉及鱼眼相机模型、标定使用等&#xff0c;作为记录&#xff0c;更新很久不曾更新的博客。 文章目录鱼眼相机模型1 相机成像2 鱼眼模型3 畸变3.1 适用针孔和MEI3.2 Kannala-Brandt鱼眼模型4 代码实现1 相机成像 针孔相机&#xff1a;所有光线从一个孔&#xf…
阅读更多...
大语言模型提示词工程详尽实战指南

大语言模型提示词工程详尽实战指南

引言&#xff1a;与大型语言模型&#xff08;LLM&#xff09;高效对话的艺术大型语言模型&#xff08;LLM&#xff09;——例如我们熟知的GPT系列、Claude、Llama等——在自然语言处理&#xff08;NLP&#xff09;领域展现了惊人的能力&#xff0c;能够执行文本摘要、翻译、代码…
阅读更多...
HTTP 请求体格式详解

HTTP 请求体格式详解

1. 概览与概念 Content-Type&#xff1a;HTTP 请求/响应头&#xff0c;表示消息体的媒体类型&#xff08;MIME type&#xff09;。服务端用它决定如何解析请求体。常见场景&#xff1a; 纯结构化数据&#xff08;JSON&#xff09; → application/json表单 文件上传 → multip…
阅读更多...
事务设置和消息分发

事务设置和消息分发

事务 RabbitMQ是基于AMQP协议实现的&#xff0c;该协议实现了事务机制&#xff0c;因此RabbitMQ也支持事务机制. SpringAMQP也提供了对事务相关的操作&#xff0c;RabbitMQ事务允许开发者确保消息的发送和接收是原子性的&#xff0c;要么 全部成功&#xff0c;要么全部失败.| 前…
阅读更多...
最新文章

Copyright @ 2022~2023