关于分页查询和column is null能否走索引的分析补充

php中文网

发布时间：2016-06-07 16:38:09

1360人浏览过

来源于php中文网

原创

群里有朋友在谈到关于分页查询的问题，类似下面的sql想让其走索引 select * from (select * from ta order by object_id desc) where rownum 这位朋友在排序列上建立了索引，但是执行计划并不走索引来避免排序，而是全表扫描然后排序后取了前几条数据，这个

群里有朋友在谈到关于分页查询的问题，类似下面的sql想让其走索引<br> select * from (select * from ta order by object_id desc) where rownum 这位朋友在排序列上建立了索引，但是执行计划并不走索引来避免排序，而是全表扫描然后排序后取了前几条数据，这个消耗成本是很高的，我们来看看如何让这类分页查询走索引（这里的索引我们都理解为b tree索引，而不是bitmap索引）

sql> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production

SQL> create table ta as select * from dba_objects;

Table created.

SQL> create index ind_id_null on ta(object_id);

Index created.

SQL> execute dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TA');

PL/SQL procedure successfully completed.

SQL> select * from ta where object_id is null;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 824468716

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 | 292 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TA | 1 | 101 | 292 (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID" IS NULL)

Statistics
----------------------------------------------------------
42 recursive calls
0 db block gets
1078 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed

这里看出cbo是不会走object_id列上的索引来避免排序和全表扫描。
SQL> select * from (select * from ta order by object_id desc) where rownum

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2218702745

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 1863 | | 2025 (1)| 00:00:25 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 74906 | 14M| | 2025 (1)| 00:00:25 |
|* 3 | SORT ORDER BY STOPKEY| | 74906 | 7388K| 9M| 2025 (1)| 00:00:25 |
| 4 | TABLE ACCESS FULL | TA | 74906 | 7388K| | 293 (1)| 00:00:04 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM 3 - filter(ROWNUM

Statistics
----------------------------------------------------------
164 recursive calls
0 db block gets
1101 consistent gets
0 physical reads
0 redo size
2306 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
9 rows processed

那么这里有什么问题导致cbo不去考虑索引了，其实b tree索引存储的key是不能全部为null的，由于object_id列上没有not null的约束，而cbo的执行计划不能影响sql的执行结果，索引这里cbo没办法去认为通过索引回表，然后count stopkey取前几条来完成查询

而如果我们添加not null约束，或者在内部的查询结果中添加一个object_id is not null约束的过滤条件，那么此时cbo就知道了能够通过现在有的b tree索引回表的方式来完成查询
SQL> select * from (select * from ta where object_id is not null order by object_id desc) where rownum

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 679434780

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 1863 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 9 | 1863 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TA | 74906 | 7388K| 3 (0)| 00:00:01 |
|* 4 | INDEX FULL SCAN DESCENDING| IND_ID_NULL | 9 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM 4 - filter("OBJECT_ID" IS NOT NULL)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
2306 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

那么如果业务中有object_id等于null的值，那么这个查询可能会影响结果，而且oracle对于null值的排序正是认为null是最大值的。

标小智

智能LOGO设计生成器

下载

那么这个分页查询如果没有not null约束或者过滤条件，就不能走索引了吗，其实不然，小鱼之前处理过下面的类似的case，是对单个的列进行is null的谓词过滤

SQL> create index ind_id_multi_null on ta(1,object_id);

Index created.

SQL> select /*+index(ta,ind_id_multi_null)*/* from ta where object_id is null;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 849692407

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 | 199 (1)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| TA | 1 | 101 | 199 (1)| 00:00:03 |
|* 2 | INDEX FULL SCAN | IND_ID_MULTI_NULL | 1 | | 199 (1)| 00:00:03 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID" IS NULL)
filter("OBJECT_ID" IS NULL)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
198 consistent gets
197 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

这个上面走的全索引扫描然后回表的方式来过滤的object_id is null的，这个是因为把索引的前导列弄错了导致的，如果我们建立下面的索引，把过滤列放在索引的前导列上
SQL> create index ind_id_nulti_null_bak on ta(object_id,1);

Index created.

SQL> select * from ta where object_id is null;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2610853831

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TA | 1 | 101 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_ID_NULTI_NULL_BAK | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID" IS NULL)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

这个已经可以走这个复合索引的索引范围扫描了，那么最开始那个分页查询同样可以走全索引扫描，这个扫描只会扫描rownum分页数目的key然后回表，这个绝对比大表的全表扫描然后排序的成本要低很多。
SQL> select * from (select * from ta order by object_id desc) where rownum

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2361786208

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 1863 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 9 | 1863 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| TA | 74906 | 7388K| 3 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN DESCENDING| IND_ID_NULTI_NULL_BAK | 9 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM

至此最开始那个分页查询我们已经优化完毕了。

这里有两点需要注意的地方：
1对于object_id is null这类过滤条件并不是不能走索引范围扫描的，我们只需要建立该列为前导列的复合索引就有可能让cbo考虑该索引
2还有就是分页查询要利用索引完成索引全扫描rownum分页数据的key然后回表的方式，一定要考虑该列是否有not null的约束或者过滤条件，这个可能造成部分分页查询无法通过索引完成。

原文地址：关于分页查询和column is null能否走索引的分析补充, 感谢原作者分享。

使用 OSS 存储图片：流量计费、安全防范、压缩方式等问题解答

MySQL 中的 key_len 为什么会大于索引列的长度？

pymysql 如何正确转义 ON DUPLICATE KEY UPDATE 中的 %(updatetime)s？

联合查询中丢失数据如何处理？如何显示未与任何group关联的strategy信息，并将其Gatewaymac值设为空？

MySQL 查询优化：如何高效查找小于等于指定月份的最大月份？

本站声明：本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

上一篇：scala作为 redis client 下一篇：centos 安装HUE3.5-3.6

热门AI工具

DeepSeek

幻方量化公司旗下的开源大模型平台

AI编程开发 AI聊天问答

豆包大模型

字节跳动自主研发的一系列大型语言模型

AI编程开发 AI大模型

WorkBuddy

腾讯云推出的AI原生桌面智能体工作台

AI办公学习 Agent智能体

腾讯元宝

腾讯混元平台推出的AI助手

文档处理 Excel 表格

文心一言

文心一言是百度开发的AI聊天机器人，通过对话可以生成各种形式的内容。

AI编程开发 AI文本写作

讯飞写作

基于讯飞星火大模型的AI写作工具，可以快速生成新闻稿件、品宣文案、工作总结、心得体会等各种文文稿

AI文本写作中文写作

即梦AI

一站式AI创作平台，免费AI图片和视频生成。

图片拼接图画生成

ChatGPT

最最强大的AI聊天机器人程序，ChatGPT不单是聊天机器人，还能进行撰写邮件、视频脚本、文案、翻译、代码等任务。

AI编程开发 AI文本写作

智谱清言 - 免费全能的AI助手

AI编程开发 Agent智能体

相关专题

TypeScript类型系统进阶与大型前端项目实践

本专题围绕 TypeScript 在大型前端项目中的应用展开，深入讲解类型系统设计与工程化开发方法。内容包括泛型与高级类型、类型推断机制、声明文件编写、模块化结构设计以及代码规范管理。通过真实项目案例分析，帮助开发者构建类型安全、结构清晰、易维护的前端工程体系，提高团队协作效率与代码质量。

2026.03.13

Python异步编程与Asyncio高并发应用实践

本专题围绕 Python 异步编程模型展开，深入讲解 Asyncio 框架的核心原理与应用实践。内容包括事件循环机制、协程任务调度、异步 IO 处理以及并发任务管理策略。通过构建高并发网络请求与异步数据处理案例，帮助开发者掌握 Python 在高并发场景中的高效开发方法，并提升系统资源利用率与整体运行性能。

117

2026.03.12

C# ASP.NET Core微服务架构与API网关实践

本专题围绕 C# 在现代后端架构中的微服务实践展开，系统讲解基于 ASP.NET Core 构建可扩展服务体系的核心方法。内容涵盖服务拆分策略、RESTful API 设计、服务间通信、API 网关统一入口管理以及服务治理机制。通过真实项目案例，帮助开发者掌握构建高可用微服务系统的关键技术，提高系统的可扩展性与维护效率。

350

2026.03.11

Go高并发任务调度与Goroutine池化实践

本专题围绕 Go 语言在高并发任务处理场景中的实践展开，系统讲解 Goroutine 调度模型、Channel 通信机制以及并发控制策略。内容包括任务队列设计、Goroutine 池化管理、资源限制控制以及并发任务的性能优化方法。通过实际案例演示，帮助开发者构建稳定高效的 Go 并发任务处理系统，提高系统在高负载环境下的处理能力与稳定性。

2026.03.10

Kotlin Android模块化架构与组件化开发实践

本专题围绕 Kotlin 在 Android 应用开发中的架构实践展开，重点讲解模块化设计与组件化开发的实现思路。内容包括项目模块拆分策略、公共组件封装、依赖管理优化、路由通信机制以及大型项目的工程化管理方法。通过真实项目案例分析，帮助开发者构建结构清晰、易扩展且维护成本低的 Android 应用架构体系，提升团队协作效率与项目迭代速度。

109

2026.03.09

JavaScript浏览器渲染机制与前端性能优化实践

本专题围绕 JavaScript 在浏览器中的执行与渲染机制展开，系统讲解 DOM 构建、CSSOM 解析、重排与重绘原理，以及关键渲染路径优化方法。内容涵盖事件循环机制、异步任务调度、资源加载优化、代码拆分与懒加载等性能优化策略。通过真实前端项目案例，帮助开发者理解浏览器底层工作原理，并掌握提升网页加载速度与交互体验的实用技巧。

108

2026.03.06

Rust内存安全机制与所有权模型深度实践

本专题围绕 Rust 语言核心特性展开，深入讲解所有权机制、借用规则、生命周期管理以及智能指针等关键概念。通过系统级开发案例，分析内存安全保障原理与零成本抽象优势，并结合并发场景讲解 Send 与 Sync 特性实现机制。帮助开发者真正理解 Rust 的设计哲学，掌握在高性能与安全性并重场景中的工程实践能力。

243

2026.03.05

PHP高性能API设计与Laravel服务架构实践

本专题围绕 PHP 在现代 Web 后端开发中的高性能实践展开，重点讲解基于 Laravel 框架构建可扩展 API 服务的核心方法。内容涵盖路由与中间件机制、服务容器与依赖注入、接口版本管理、缓存策略设计以及队列异步处理方案。同时结合高并发场景，深入分析性能瓶颈定位与优化思路，帮助开发者构建稳定、高效、易维护的 PHP 后端服务体系。

684

2026.03.04