like 百分号时强制使用索引，是否能起到应有的效果？排序分页时如何避免全表查？

dinghun8leech 2010-06-24 04:16:53

数据库Oracle11g
--------------
name字段已建索引
原sql：（由于使用了like，并且内容前有百分号，默认是不使用索引的）

select id from user where name like '%admin%';

修改后sql：（强制使用索引，实际结果确实使用了该索引）

select /*+INDEX_ASC (user NAME_INDEX)*/id from user where name like '%admin%';

--------------
问题1：像这样强制使用索引进行like检索时是否真的缩小了检索范围而达到使用索引应有的效果？如果没有，如何修改？
--------------
问题2：排序+分页查询当前常用的方式为三层嵌套sql，内层排序、查出所有记录，中间层过滤超过最大值的部分，最外层过滤小于最小值的部分，但此法无可避免的使用了全表查询，这该如何避免？希望能提示下思路，直接给出示例sql更好。
--------------
谢谢大家！

...全文

518 22 打赏收藏转发到动态举报

写回复

用AI写文章

22 条回复

切换为时间正序

请发表友善的回复…

发表回复

CoffeePhoton 2011-03-10

打赏
举报

真的没比较好的办法么

dinghun8leech 2010-07-10

打赏
举报

来晚了，结贴。
我得花些时间来学点基础了，呵呵。
谢谢大家！

minitoy 2010-07-10

打赏
举报

这样即使使用了索引也没用，反而增加了开销。
因为增加了对所有所以条目的检索。
强制前：全表扫描
强制后：索引全扫描+全表扫描

minitoy 2010-07-10

打赏
举报

你可以试试在上面加个函数索引instr

kingstarer 2010-07-01

打赏
举报

lz的需求类似于搜索引擎

jame_tian 2010-07-01

打赏
举报

最近也碰到大数据量的处理,过来学习一下!

weiwenbai 2010-06-30

打赏
举报

学习学习。。。

vber1010 2010-06-30

打赏
举报

查询走了索引未必会增加查询速度，基于成本的优化器（CBO）大多数时候会自动选择优化的查询方式。

dinghun8leech 2010-06-30

打赏
举报

[Quote=引用 13 楼 kingsunsha 的回复:]
like条件中以%起首即使走索引，也是全索引扫描再加table scan by rowid，如果索引只是在name上的而且符合条件的记录超过全表记录的20%，那效果通常还不如直接走全表扫描。

如果只是要优化这一句查询，那是很简单的，删除name上的索引，建一个包含name和id的索引，那该查询就完全走索引（虽然是全索引扫描）而不会去读表，效率应该高很多。[/Quote]

多谢前辈解答，学习了！

KingSunSha 2010-06-30

打赏
举报

select id from user where name like '%admin%';

like条件中以%起首即使走索引，也是全索引扫描再加table scan by rowid，如果索引只是在name上的而且符合条件的记录超过全表记录的20%，那效果通常还不如直接走全表扫描。

如果只是要优化这一句查询，那是很简单的，删除name上的索引，建一个包含name和id的索引，那该查询就完全走索引（虽然是全索引扫描）而不会去读表，效率应该高很多。

dinghun8leech 2010-06-29

打赏
举报

呼唤大牛~~求助~~~~~~~~~

duqiangcise 2010-06-27

打赏
举报

mark!

dinghun8leech 2010-06-27

打赏
举报

to9L：
如果在最内层使用rownum < x，再加上排序，出来的rownum就是乱序，外层再一截取，就不对了，会造成查出重复数据的情况。
我用的是oracle呀。

dinghun8leech 2010-06-26

打赏
举报

[Quote=引用 2 楼 woodlong 的回复:]
如果admin前不加%是可以用上索引的
[/Quote]
需求就是模糊查询而又需要避免全表检索。所以必须要有前后两个百分号。
[Quote=引用 7 楼 go_away_gui 的回复:]
拿分走人，谢谢~
[/Quote]
说点和本贴问题沾边儿的话，我一定给你分，呵呵。

碧海情天-赵亮 2010-06-26

打赏
举报

1.这个没必要再多想，SQL语句在执行前已经会自动进行优化。LIKE查询是最常用的，怎么可能不对其进行最优化呢。你再多加什么都是画蛇添足，效果只能越差。

2.这个，为什么不可以直接在最里层查询条件时直接用rownum来避免全表查询呢,你用的不是ORACLE吗，当rownum大于你设的条件，就不会继续搜索下去了。