Null 值对索引排序的影响案例一则
2018-02-06 19:22 那个,我 阅读(410) 评论(0) 编辑 收藏 举报--原SQL 语句如下:
select * from (select tmp_tb.*, ROWNUM row_id from (select wpid, customer_id, customer_name, gender, to_char(wi.call_time, 'yyyy-mm-dd hh24:mi:ss') call_time, call_num, phoneno, tel, cardtype, cardnum, note, sessionid, (select c.nodevalue from sys_code c where c.auto_id = wi.wp_type) wp_type, smalltype, order_type, priority, is_callback, is_hidden, district, address, summary, wp_title, wp_remark, (select c.nodename from sys_code c where c.auto_id = wi.wp_source) wp_source, (select c.nodevalue from sys_code c where c.auto_id = wi.class1) class1, (select c.nodevalue from sys_code c where c.auto_id = wi.class2) class2, (select c.nodevalue from sys_code c where c.auto_id = wi.class3) class3, (select c.nodevalue from sys_code c where c.auto_id = wi.class4) class4, upload_acceptance, upload_handle, upload_back, upload_supervise, is_repeat, to_char(wi.starttime, 'yyyy-mm-dd hh24:mi:ss') starttime, sender, to_char(wi.updatetime, 'yyyy-mm-dd hh24:mi:ss') updatetime, lastmessage, (select sf.statename from sys_function sf where sf.auto_id = wi.state) state, (select sf.statename from sys_function sf where sf.auto_id = wi.next_state) next_state, next_state next_state_id, dept_center, (select stu.stru_name from sys_stru stu where stu.auto_id = wi.dept_level1) dept_level1, (select stu.stru_name from sys_stru stu where stu.auto_id = wi.dept_level2) dept_level2, (select stu.stru_name from sys_stru stu where stu.auto_id = wi.dept_level3) dept_level3, (select stu.stru_name from sys_stru stu where stu.auto_id = wi.dept_cooperate1) dept_cooperate1, (select stu.stru_name from sys_stru stu where stu.auto_id = wi.dept_cooperate2) dept_cooperate2, is_reminder, is_supervise, is_overtime, is_review, to_char(wi.send_time, 'yyyy-mm-dd hh24:mi:ss') send_time, to_char(wi.time_boundry, 'yyyy-mm-dd hh24:mi:ss') time_boundry, source_address, ciid1, ciid2, complaintname, complainttel, compensate_money, back_source, donereport, reply_point, to_char(wi.reply_time, 'yyyy-mm-dd hh24:mi:ss') reply_time, solved, passoperator, to_char(wi.donetime, 'yyyy-mm-dd hh24:mi:ss') donetime, dept_workno, specialSeat_type, to_char(wi.acceptTime_boundry, 'yyyy-mm-dd hh24:mi:ss') acceptTime_boundry, to_char(wi.acceptTime, 'yyyy-mm-dd hh24:mi:ss') acceptTime, orderSymbol, timeout_flag, send_flag, getLastWorknoByWpid(wi.wpid) operatorno, is_relation, (select count(1) from wp_info wp where wp.relation_wpid = wi.wpid) total, ep_flag, is_difficult, (select count(1) from wp_early_warning_info where wpid = wi.wpid) lid, endresult from xxx_info wi where 1 = 1 order by wi.starttime desc) tmp_tb where ROWNUM <= 10) where row_id > 0
--执行时间 147s
通过 explain plan for 上述语句,获取其执行计划 select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 964360475 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 116K| | 205K (1)| 00:41:04 | | 1 | TABLE ACCESS BY INDEX ROWID| SYS_CODE | 1 | 17 | | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | SYS_C005980 | 1 | | | 1 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| SYS_CODE | 1 | 17 | | 2 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | SYS_C005980 | 1 | | | 1 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID| SYS_CODE | 1 | 17 | | 2 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | SYS_C005980 | 1 | | | 1 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID| SYS_CODE | 1 | 17 | | 2 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | SYS_C005980 | 1 | | | 1 (0)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID| SYS_CODE | 1 | 17 | | 2 (0)| 00:00:01 | |* 10 | INDEX UNIQUE SCAN | SYS_C005980 | 1 | | | 1 (0)| 00:00:01 | | 11 | TABLE ACCESS BY INDEX ROWID| SYS_CODE | 1 | 17 | | 2 (0)| 00:00:01 | |* 12 | INDEX UNIQUE SCAN | SYS_C005980 | 1 | | | 1 (0)| 00:00:01 | | 13 | TABLE ACCESS BY INDEX ROWID| SYS_FUNCTION | 1 | 15 | | 1 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | SYS_FUNC_ID | 1 | | | 0 (0)| 00:00:01 | | 15 | TABLE ACCESS BY INDEX ROWID| SYS_FUNCTION | 1 | 15 | | 1 (0)| 00:00:01 | |* 16 | INDEX UNIQUE SCAN | SYS_FUNC_ID | 1 | | | 0 (0)| 00:00:01 | | 17 | TABLE ACCESS BY INDEX ROWID| SYS_STRU | 1 | 21 | | 2 (0)| 00:00:01 | |* 18 | INDEX UNIQUE SCAN | STRU_ID_PK | 1 | | | 1 (0)| 00:00:01 | | 19 | TABLE ACCESS BY INDEX ROWID| SYS_STRU | 1 | 21 | | 2 (0)| 00:00:01 | |* 20 | INDEX UNIQUE SCAN | STRU_ID_PK | 1 | | | 1 (0)| 00:00:01 | | 21 | TABLE ACCESS BY INDEX ROWID| SYS_STRU | 1 | 21 | | 2 (0)| 00:00:01 | |* 22 | INDEX UNIQUE SCAN | STRU_ID_PK | 1 | | | 1 (0)| 00:00:01 | | 23 | TABLE ACCESS BY INDEX ROWID| SYS_STRU | 1 | 21 | | 2 (0)| 00:00:01 | |* 24 | INDEX UNIQUE SCAN | STRU_ID_PK | 1 | | | 1 (0)| 00:00:01 | | 25 | TABLE ACCESS BY INDEX ROWID| SYS_STRU | 1 | 21 | | 2 (0)| 00:00:01 | |* 26 | INDEX UNIQUE SCAN | STRU_ID_PK | 1 | | | 1 (0)| 00:00:01 | | 27 | SORT AGGREGATE | | 1 | 2 | | | | |* 28 | INDEX RANGE SCAN | IDX_WPINFO_RELATIONWPID | 32 | 64 | | 1 (0)| 00:00:01 | | 29 | SORT AGGREGATE | | 1 | 15 | | | | |* 30 | INDEX RANGE SCAN | IDX_WEWI_WPID | 1 | 15 | | 1 (0)| 00:00:01 | |* 31 | VIEW | | 10 | 116K| | 205K (1)| 00:41:04 | |* 32 | COUNT STOPKEY | | | | | | | | 33 | VIEW | | 1051K| 11G| | 205K (1)| 00:41:04 | |* 34 | SORT ORDER BY STOPKEY | | 1051K| 778M| 1026M| 205K (1)| 00:41:04 | | 35 | TABLE ACCESS FULL | WP_INFO | 1051K| 778M| | 33545 (1)| 00:06:43 | --------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C"."AUTO_ID"=TO_NUMBER(:B1)) 4 - access("C"."AUTO_ID"=TO_NUMBER(:B1)) 6 - access("C"."AUTO_ID"=TO_NUMBER(:B1)) 8 - access("C"."AUTO_ID"=TO_NUMBER(:B1)) 10 - access("C"."AUTO_ID"=TO_NUMBER(:B1)) 12 - access("C"."AUTO_ID"=TO_NUMBER(:B1)) 14 - access("SF"."AUTO_ID"=TO_NUMBER(:B1)) 16 - access("SF"."AUTO_ID"=TO_NUMBER(:B1)) 18 - access("STU"."AUTO_ID"=:B1) 20 - access("STU"."AUTO_ID"=:B1) 22 - access("STU"."AUTO_ID"=:B1) 24 - access("STU"."AUTO_ID"=:B1) 26 - access("STU"."AUTO_ID"=:B1) 28 - access("WP"."RELATION_WPID"=:B1) 30 - access("WPID"=:B1) 31 - filter("ROW_ID">0) 32 - filter(ROWNUM<=10) 34 - filter(ROWNUM<=10)
该语句看起来挺长,但主要还是从WP_INFO 表取数据,无其他where条件限制,根据starttime 对全表倒序排序,取前十
执行计划看出WP_INFO表 行数1051K ,全表扫描,排序操作用了1026M空间。那么该如何优化呢?
既然存在排序行为,那么是否可以考虑在排序列建索引呢?索引存储了已排序好的非空列值,那么是否可以通过建索引的方式避免排序操作呢?
检查该表发现starttime列已经存在索引。
那么为什么不走索引呢?难道一定要有限制条件?还是说空值限制了索引的使用? starttime 列确实可为空。
针对空值有两种处理办法:
1. 修改表的列 starttime 属性非空,但生产库岂敢随意修改。
alter table xxx modify starttime not null;
2. Where 条件中添加限制,只取非空值,如下:
where 1 = 1
and wi.starttime is not null
order by wi.starttime desc) tmp_tb
查看新的执行计划:
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 793409466 ---------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 116K| 12 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | SYS_CODE | 1 | 17 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | SYS_C005980 | 1 | | 1 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID | SYS_CODE | 1 | 17 | 2 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | SYS_C005980 | 1 | | 1 (0)| 00:00:01 | | 5 | TABLE ACCESS BY INDEX ROWID | SYS_CODE | 1 | 17 | 2 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | SYS_C005980 | 1 | | 1 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID | SYS_CODE | 1 | 17 | 2 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | SYS_C005980 | 1 | | 1 (0)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID | SYS_CODE | 1 | 17 | 2 (0)| 00:00:01 | |* 10 | INDEX UNIQUE SCAN | SYS_C005980 | 1 | | 1 (0)| 00:00:01 | | 11 | TABLE ACCESS BY INDEX ROWID | SYS_CODE | 1 | 17 | 2 (0)| 00:00:01 | |* 12 | INDEX UNIQUE SCAN | SYS_C005980 | 1 | | 1 (0)| 00:00:01 | | 13 | TABLE ACCESS BY INDEX ROWID | SYS_FUNCTION | 1 | 15 | 1 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | SYS_FUNC_ID | 1 | | 0 (0)| 00:00:01 | | 15 | TABLE ACCESS BY INDEX ROWID | SYS_FUNCTION | 1 | 15 | 1 (0)| 00:00:01 | |* 16 | INDEX UNIQUE SCAN | SYS_FUNC_ID | 1 | | 0 (0)| 00:00:01 | | 17 | TABLE ACCESS BY INDEX ROWID | SYS_STRU | 1 | 21 | 2 (0)| 00:00:01 | |* 18 | INDEX UNIQUE SCAN | STRU_ID_PK | 1 | | 1 (0)| 00:00:01 | | 19 | TABLE ACCESS BY INDEX ROWID | SYS_STRU | 1 | 21 | 2 (0)| 00:00:01 | |* 20 | INDEX UNIQUE SCAN | STRU_ID_PK | 1 | | 1 (0)| 00:00:01 | | 21 | TABLE ACCESS BY INDEX ROWID | SYS_STRU | 1 | 21 | 2 (0)| 00:00:01 | |* 22 | INDEX UNIQUE SCAN | STRU_ID_PK | 1 | | 1 (0)| 00:00:01 | | 23 | TABLE ACCESS BY INDEX ROWID | SYS_STRU | 1 | 21 | 2 (0)| 00:00:01 | |* 24 | INDEX UNIQUE SCAN | STRU_ID_PK | 1 | | 1 (0)| 00:00:01 | | 25 | TABLE ACCESS BY INDEX ROWID | SYS_STRU | 1 | 21 | 2 (0)| 00:00:01 | |* 26 | INDEX UNIQUE SCAN | STRU_ID_PK | 1 | | 1 (0)| 00:00:01 | | 27 | SORT AGGREGATE | | 1 | 2 | | | |* 28 | INDEX RANGE SCAN | IDX_WPINFO_RELATIONWPID | 32 | 64 | 1 (0)| 00:00:01 | | 29 | SORT AGGREGATE | | 1 | 15 | | | |* 30 | INDEX RANGE SCAN | IDX_WEWI_WPID | 1 | 15 | 1 (0)| 00:00:01 | |* 31 | VIEW | | 10 | 116K| 12 (0)| 00:00:01 | |* 32 | COUNT STOPKEY | | | | | | | 33 | VIEW | | 10 | 116K| 12 (0)| 00:00:01 | | 34 | TABLE ACCESS BY INDEX ROWID| WP_INFO | 1051K| 778M| 12 (0)| 00:00:01 | |* 35 | INDEX FULL SCAN DESCENDING| IDX_WPINFO_STIME | 10 | | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C"."AUTO_ID"=TO_NUMBER(:B1)) 4 - access("C"."AUTO_ID"=TO_NUMBER(:B1)) 6 - access("C"."AUTO_ID"=TO_NUMBER(:B1)) 8 - access("C"."AUTO_ID"=TO_NUMBER(:B1)) 10 - access("C"."AUTO_ID"=TO_NUMBER(:B1)) 12 - access("C"."AUTO_ID"=TO_NUMBER(:B1)) 14 - access("SF"."AUTO_ID"=TO_NUMBER(:B1)) 16 - access("SF"."AUTO_ID"=TO_NUMBER(:B1)) 18 - access("STU"."AUTO_ID"=:B1) 20 - access("STU"."AUTO_ID"=:B1) 22 - access("STU"."AUTO_ID"=:B1) 24 - access("STU"."AUTO_ID"=:B1) 26 - access("STU"."AUTO_ID"=:B1) 28 - access("WP"."RELATION_WPID"=:B1) 30 - access("WPID"=:B1) 31 - filter("ROW_ID">0) 32 - filter(ROWNUM<=10) 35 - filter("WI"."STARTTIME" IS NOT NULL)
cost 从205k 下降到了12 ,评估执行时间从41min 下降到0.01 s, 效果很好有木有。看下面执行计划走了索引全扫描,没了排序操作。
实际执行 0.06s ,从147s 下降到0.06s 效率提升了2000多倍。
要等很久的一个查询秒出了。想想就有点小激动,所以记录一下。
总结:索引只保存确定值,而 Null 值不确定所以并不被索引记录,如果根据索引排序,那么这个排序并没有Null值,排序结果对于原表来说不准确,数据库不能返回给你一个假的结果,所以不能走索引。
如果索引列根本没有空值呢?所有的值都被索引所记录? 事实是不论这个列是否有空值,但既然可以为空,那么数据库就认为可能存在空值,坚决不能走索引。
通过修改列属性为非空,或在条件中限制只取非空值,数据库就知道索引中确实包含了你需要的所有值,数据库就可以放心大胆的走索引了。
同理类似,select max/min/count(1)/count(*) 都会因为可能存在空值导致返回结果不准确而不走索引。
学习如茶,需细细品味。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步