SQL-not exists翻页查询数据为空问题

最新推荐文章于 2025-04-13 14:09:20 发布
最新推荐文章于 2025-04-13 14:09:20 发布
阅读量818 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： SQLServer
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/hebbers/article/details/83113346

今天做了个多表连接查询语句，前端有翻页功能，在最后一页时数据查询总是为空。翻页主要利用not exists来实现，经过反复排查，发现查询结果中利用翻页的主键ID会存在重复的情况，致使无法过滤出正确数据。最后通过增加一个“行号”字段解决该问题，即ROW_NUMBER() 方法，将该字段设为翻页主键即可。

原始数据：

标题

问题翻页：

标题

解决方案：

标题

 

超强干货之---Python-数据爬取（爬虫）
房东的猫的博客
07-12 5万+
定义： 深度优先搜索是一种遍历或搜索树或图的算法，从起始节点开始，一直沿着一个分支走到底，再回溯到上一个节点继续搜索下一个分支，直到遍历完所有节点。特点递归：通常用递归实现，或者使用栈来模拟递归过程。内存占用低：在有大量分支的情况下，内存占用比广度优先搜索低。适合目标较深的情况：如果目标节点距离起始节点较深，DFS能更快找到目标。适用场景需要遍历所有节点的情况，如生成树、迷宫搜索。目标节点较深，且分支较多时。在大规模数据爬取时，选择合适的存储方式取决于数据的规模、结构和访问需求。
SQL Server 存储过程 分页、条件查询
大蛋的博客
08-01 555
SQL Server 存储过程 分页、条件查询2019/08/01 18：17 2019/08/01 18：17 --切换到db_StuManagement数据库 use db_StuManagement go --分页查询 --判断存储过程是否存在 if exists(SELECT * FROM sys.objects where name='proc_PagePaging') drop...
mysql not exists很慢_查询速度优化用not EXISTS 代替 not in
weixin_36325338的博客
02-09 3605
exists : 强调的是是否返回结果集，不要求知道返回什么, 比如：select name from student where sex = 'm' and mark exists(select 1 from grade where ...) ,只要exists引导的子句有结果集返回，那么exists这个条件就算成立了,大家注意返回的字段始终为1，如果改成“select2 from grade...
[原]not in/not exists 的 null 陷阱
weixin_34128839的博客
09-04 302
以前遇到了 not in 子查询的一个 null 陷阱，有经验的朋友可能知道怎么回事了，用代码来说就是： -- 创建两张测试表： create table tmp01 as with tmp as ( select 1 as id from dual union all select 2 from dual union all select 3 ...
EXISTS（或NOT EXISTS）关键字
zhangvalue的博客
11-04 1244
使用EXISTS（或NOT EXISTS）关键字引入一个子查询时， 子查询实际上不产生任何数据； 它只返回TRUE或FALS值。 指定一个子查询，检测行的存在。 如果子查询包含行，则返回 TRUE IN效率要差(返回子查询) exists : 强调的是是否返回结果集，不要求知道返回什么, 比如： select name from student where sex = 'm' and mark exists(select 1 from grade where ...) ,只要 exists引导的..
使用Not Exists实现返回条件为一个值或空值的数据
aobc72208的博客
11-30 604
Select * From base Where org_id = 1 Or Not Exists(Select * From base Where org_id =1) 转载于:https://www.cnblogs.com/caoxch/archive/2006/11/30/577495.html
exists与not exists的概念与用法
走着的专栏
10-16 1875
原理：利用exists或not exists查询时，它是对父句中的每一次循环都执行查询子句来判断当前循环指向的元组是否满足查询子句的结果集不为（为）空，如果满足了的话，就将当前元组输出到父句的结果集中。1.existsselect ... from ... where exists ( 查询子句; )如果有结果集被返回，则父句中的where语句的结果为真，那么输出当前元组到结果集中。2.no
MyBatis-Plus
我的博客
07-03 715
IService 里面已经提供了很多常用方法，例如批量保存、批量更新等。只需要配置好直接调用即可。
mysql百万数据 查总数都特别慢_mysql百万级数据分页查询缓慢优化方法
weixin_39598472的博客
02-06 3314
参考网址1：https://www.cnblogs.com/nightOfStreet/p/9647926.html -------------修改需求一、与产品商讨 修改需求后端工程师，在接到分页list需求的时候，内心是这样的画面是这样的代码大概是这样的select count(id) from … 查出总数select * from …. limit m,n ...
数据库——sql优化（百万数据优化方案）
qq_36470686的博客
12-03 1760
一、sql数据库优化 1、索引 建立索引是数据库优化各种方案之中成本最低，见效最快的解决方案，一般来讲，数据库规模在几十万和几百万级别的时候见效最快，即便是有不太复杂的表关联，也能大幅度提高sql的运行效率。  建立索引需要注意的地方 1、索引一般加在查询条件的关键字上，如果有多个查询条件关键字，还可以添加组合索引，写sql的时候需要注意，索引字段和sql字段需要保持一致，否则索引会无效。...
数据库not exists真假判断
weixin_42583683的博客
02-09 437
在数据库中，NOT EXISTS 用于判断一个子查询的结果是否为空。如果结果为空，则返回 TRUE，否则返回 FALSE。 例如，如果你想查找所有不存在于表 orders 中的客户，你可以使用如下查询： SELECT * FROM customers WHERE NOT EXISTS ( SELECT 1 FROM orders WHERE orders.customer_id = custo...
exists 和not exists用法
qq_40274514的博客
07-11 1634
exists 和not exists用法
EXISTS与NOT EXISTS
wonderfulooo的博客
04-13 836
谓词EXISTS是一元运算符，运算数是一个集合，如果该集合不是空集，则运算结果为真，否则预算结果为假。
sql查询篇，exists和not exists的使用
u011529483的博客
12-01 2416
使用exists时， 它只要括号中的表达式有一个值存在， 就立刻返回true，而不用遍历表中所有的数据。在一定场景使用可以提高SQL的执行效率。
注意事项: Oracle Not Exists 及 Not In 使用
weixin_30898109的博客
07-13 194
select value from temp_a awhere a.id between 1 and 100and not exists(select * from temp_b b where a.value=b.value); 这时能查出结果 select value from temp_a awhere a.id between 1 and 100and a.valuenot...
SQL 子查询 EXISTS 和 NOT EXISTS
热门推荐
王小军的博客
11-08 8万+
MySQL EXISTS 和 NOT EXISTS 子查询语法如下： SELECT … FROM table WHERE EXISTS (subquery) 该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留。MySQL EXISTS 子查询实例下面以实际的例子来理解 EXISTS 子查询。下面是原始的数据表：art
sql 中 not in 或者 not exists 查不出数据
cwrygr的博客
09-06 3126
可能原因：B表中存在NULL值 例子： 1、查不出数据 not in select name from a where name not in (select distinct name from b )   not exists  select name from a where  not exists (select distinct name from b  ) 
mysql的sql语句的优化经验
最新发布
08-14
<think>我们参考了用户提供的引用内容，其中提到了SQL优化的多个方面，包括大批量插入数据、优化insert语句、优化order by语句、优化group by语句、优化嵌套查询、优化OR条件、优化分页查询以及使用SQL提示等。同时，用户还提到了使用EXPLAIN分析查询、避免全表扫描、索引的使用注意事项等。 结合这些引用内容，我们将从以下几个方面详细阐述MySQL SQL语句优化的技巧和最佳实践： 1. **索引优化**：包括索引的创建、使用和避免索引失效。 2. **查询优化**：针对不同类型的查询（如JOIN、子查询、分页查询等）进行优化。 3. **语句写法优化**：避免在WHERE子句中进行表达式运算，合理使用索引等。 4. **使用EXPLAIN分析**：通过执行计划了解查询性能。 5. **特定场景优化**：如大批量插入数据、优化ORDER BY和GROUP BY等。 下面详细展开： ### 一、索引优化（核心） 1. **创建合适的索引** - 在WHERE、JOIN、ORDER BY和GROUP BY涉及的列上创建索引[^2]。 - 避免在区分度低的列上建索引（如性别、状态等）。 - 使用覆盖索引（Covering Index）减少回表查询。 2. **避免索引失效** - 不在索引列上做计算或函数操作[^4]： ```sql -- 错误示例：在索引列上做计算 SELECT id FROM user WHERE num/2 = 100; -- 正确示例： SELECT id FROM user WHERE num = 100*2; ``` - 避免使用左模糊查询（`LIKE '%abc'`），尽量使用右模糊（`LIKE 'abc%'`）[^1]。 - 注意联合索引的最左前缀原则。 ### 二、查询优化 1. **优化ORDER BY语句** - 确保ORDER BY的列上有索引[^2]。 - 避免使用`ORDER BY RAND()`，因为会导致全表扫描。对于随机抽取，可考虑先计算总行数，再随机选择偏移量[^2]。 - 当使用多个列排序时，注意联合索引的顺序。 2. **优化GROUP BY语句** - GROUP BY的列尽量创建索引。 - 如果不需要排序，可以加上`ORDER BY NULL`来避免排序消耗。 3. **优化嵌套查询（子查询）** - 尽量将子查询转化为连接查询（JOIN），因为连接查询通常更高效[^1]。 4. **优化OR条件** - 对于OR条件，可以考虑使用UNION或UNION ALL来替代，特别是当OR条件涉及不同索引时[^1]。 - 确保每个OR分支都使用了索引。 5. **优化分页查询** - 避免使用`LIMIT offset, size`中的大偏移量。可以通过记录上一页的最大ID来优化： ```sql -- 原始：效率低 SELECT * FROM table ORDER BY id LIMIT 1000000, 10; -- 优化：使用条件过滤 SELECT * FROM table WHERE id > 1000000 ORDER BY id LIMIT 10; ``` ### 三、语句写法优化 1. **避免SELECT *** - 只查询需要的字段，减少数据传输量。 2. **使用批量操作** - 批量插入：使用多值插入（`INSERT INTO ... VALUES (...), (...), ...`）代替多条单行插入[^1]。 - 批量更新：使用CASE语句或批量提交。 3. **避免在WHERE子句中对字段进行表达式操作** - 如引用[4]所示，将表达式操作移至运算符右侧。 ### 四、使用EXPLAIN分析查询 通过EXPLAIN命令可以获取查询的执行计划，帮助我们分析查询性能[^3]： ```sql EXPLAIN SELECT * FROM table WHERE condition; ``` 关注以下列： - `type`：访问类型（如const, ref, range, index, ALL）。 - `key`：实际使用的索引。 - `rows`：预估扫描行数。 - `Extra`：额外信息（如Using filesort, Using temporary等，表示需要优化）。 ### 五、特定场景优化 1. **大批量插入数据** - 使用`LOAD DATA INFILE`代替INSERT语句，速度更快[^1]。 - 如果必须使用INSERT，则使用多值插入，并关闭自动提交，使用事务。 2. **使用SQL提示** - `USE INDEX`：建议使用某个索引。 - `IGNORE INDEX`：忽略某个索引。 - `FORCE INDEX`：强制使用某个索引[^1]。 ### 示例：优化分页查询 **问题**：分页查询深度翻页时性能差。 **原始SQL**： ```sql SELECT * FROM orders ORDER BY id LIMIT 1000000, 10; ``` **优化方案**： 1. 使用覆盖索引先获取ID，再回表查询： ```sql SELECT * FROM orders WHERE id >= (SELECT id FROM orders ORDER BY id LIMIT 1000000, 1) ORDER BY id LIMIT 10; ``` 2. 或者记录上一页最后一条记录的ID： ```sql SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10; ``` ### 总结 MySQL优化是一个综合性的工作，需要结合索引、查询语句、配置以及硬件资源等多方面进行。核心原则是减少数据扫描量，充分利用索引，避免全表扫描。 §§ 相关问题 §§ 1. 如何通过EXPLAIN结果判断是否需要优化？ 2. 在哪些情况下应该避免使用索引？ 3. 如何优化MySQL中的JOIN查询？ 4. 大批量数据导入MySQL有哪些优化方法？</think>以下是根据MySQL优化经验总结的SQL语句优化技巧与最佳实践，结合引用内容及数据库核心原理： ### 一、索引优化核心技巧 1. **索引创建原则** - `WHERE`和`ORDER BY`涉及的列优先建索引[^2] - 避免在低区分度列（如性别）建索引 - 联合索引遵循最左前缀原则： ```sql -- 索引(a,b,c)生效场景： WHERE a=1 AND b=2 -- ✔️ 生效 WHERE b=2 AND c=3 -- ❌ 失效 ``` 2. **索引失效场景规避** | 失效场景 | 解决方案 | |-------------------------|------------------------------| | 索引列参与计算 | `WHERE num/2=100` → `WHERE num=200`[^4] | | `LIKE`左模糊匹配 | `'%abc'` → `'abc%'` | | 隐式类型转换 | 确保比较值类型匹配索引列类型 | --- ### 二、关键语句优化方案 #### 1. ORDER BY优化 - **排序字段必须索引化** ```sql -- 优化前（无索引）： SELECT * FROM orders ORDER BY create_time DESC; -- 触发Filesort -- 优化后： ALTER TABLE orders ADD INDEX idx_create(create_time); -- 创建索引[^2] ``` - **严禁使用`ORDER BY RAND()`** 随机抽样改用方案： ```sql -- 替代方案（效率提升100倍+）： SELECT * FROM table WHERE id >= (FLOOR(RAND() * MAX(id))) LIMIT 1; ``` #### 2. GROUP BY优化 - 添加`ORDER BY NULL`避免排序消耗： ```sql -- 优化前（隐式排序）： SELECT department, COUNT(*) FROM employees GROUP BY department; -- 优化后： SELECT department, COUNT(*) FROM employees GROUP BY department ORDER BY NULL; ``` #### 3. 分页查询优化 - **深度分页解决方案**： ```sql -- 原始方案（扫描前100000行）： SELECT * FROM logs LIMIT 100000, 10; -- 优化方案（扫描最后10行）： SELECT * FROM logs WHERE id > 100000 ORDER BY id LIMIT 10; ``` #### 4. OR条件优化 - 使用`UNION`替代低效OR： ```sql -- 优化前（可能全表扫描）： SELECT * FROM users WHERE age<18 OR salary>100000; -- 优化后： SELECT * FROM users WHERE age<18 UNION SELECT * FROM users WHERE salary>100000; -- 需各自有索引 ``` --- ### 三、SQL编写规范 1. **避免全表扫描** - 禁用无条件的`SELECT *`[^2] - 用`EXISTS`替代`IN`子查询： ```sql -- 低效： SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip=1); -- 高效： SELECT * FROM orders o WHERE EXISTS ( SELECT 1 FROM users u WHERE u.id=o.user_id AND u.vip=1 ); ``` 2. **批量操作优化** ```sql -- 单条插入（低效）： INSERT INTO logs(time) VALUES ('2023-01-01'); INSERT INTO logs(time) VALUES ('2023-01-02'); -- 批量插入（效率提升10倍+）： INSERT INTO logs(time) VALUES ('2023-01-01'), ('2023-01-02'); [^1] ``` 3. **函数操作规范** 禁止在WHERE左侧使用函数： ```sql -- 错误示例： SELECT * FROM users WHERE DATE(create_time)='2023-01-01'; -- 正确示例： SELECT * FROM users WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59'; ``` --- ### 四、性能分析工具 1. **EXPLAIN执行计划** ```sql EXPLAIN SELECT * FROM products WHERE price>100; ``` - 关键指标解读： - `type`：`const` > `ref` > `range` > `ALL`（避免ALL）[^3] - `Extra`：`Using filesort`/`Using temporary`需优化 - `rows`：扫描行数越少越好 2. **慢查询日志配置** ```ini slow_query_log = ON long_query_time = 1 # 记录超过1秒的查询 log_queries_not_using_indexes = ON ``` --- ### 五、高级优化技巧 1. **覆盖索引（Covering Index）** ```sql -- 创建联合索引 (category, price) SELECT category, price FROM products WHERE category='Electronics'; -- 无需回表 ``` 2. **索引下推（ICP）** MySQL 5.6+ 在存储引擎层过滤数据，减少回表次数 3. **强制索引提示** ```sql SELECT * FROM orders FORCE INDEX(idx_user) WHERE user_id=100; [^1] ``` --- ### 优化案例：电商订单查询 **问题SQL**（执行2.8s）： ```sql SELECT * FROM orders WHERE user_id=1000 AND status=1 ORDER BY create_time DESC LIMIT 10; ``` **优化步骤**： 1. 添加联合索引：`ALTER TABLE orders ADD INDEX idx_user_status_time(user_id,status,create_time)` 2. 改写为覆盖索引：`SELECT order_id, amount, ...`（避免`SELECT *`） 3. 使用延迟关联： ```sql SELECT * FROM orders o JOIN (SELECT order_id FROM orders WHERE user_id=1000 AND status=1 ORDER BY create_time DESC LIMIT 10) tmp ON o.order_id=tmp.order_id; ``` **结果**：响应时间降至 **0.02s**[^3] ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值