不懂利用选择性索引提高效率
数据库的设计是很灵活多变的,要有跳出常规思维的想法。结算组有个兄弟问我:“能不能只对某个字段的某些特定的值建索引?”问这话值得表扬,因为他会思考,认为这样的索引要比普通索引“个头”来的小一点,所以会更块!
确实有只对列的某些值建索引的方法,测试如下:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as ljb SQL> drop table ljb_test; Table dropped SQL> create table ljb_test (id int ,status varchar2(2)); Table created
这里表示只对status列的值为N的情况建索引
SQL> create index id_status on ljb_test (Case when status= 'N' then 'N' end); Index created 插入很多为Y的值 SQL> insert into ljb_test select rownum ,'Y' from dual connect by rownum<=100000; 100000 rows inserted 插入单条值为N的记录 SQL> insert into ljb_test select 1 ,'N' from dual; 1 row inserted SQL> commit; Commit complete
分析的目的是为了让查询在基于COST模式下,因为只为字段的部分值建索引本质是函数索引,要在COST模式下才生效。
SQL> analyze table ljb_test compute statistics for table for all indexes for all indexed columns; Table analyzed 以下这个select * from ljb_test where (case when status='N' then 'N' end)='N’写法不能变,如果是select * from ljb_test where status='N'将无效!我见过有些人设置了选择性索引,却这样调用的,结果根本起不到任何效果! SQL> explain plan for select * from ljb_test where (case when status='N' then 'N' end)='N'; Explained
可以看出来代价还是比较低的,效果不错!
SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------- Plan hash value: 3317130416 ---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T ---------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 0 | 1 | TABLE ACCESS BY INDEX ROWID| ljb_test | 1 | 10 | 2 (0)| 0 |* 2 | INDEX RANGE SCAN | ID_STATUS | 1 | | 1 (0)| 0 ---------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(CASE "STATUS" WHEN 'N' THEN 'N' END ='N') 14 rows selected
接着删除掉选择性索引,建普通索引比较一下效果
SQL> drop index id_status; Index dropped SQL> create index id_normal on ljb_test(status); Index created SQL> explain plan for select * from ljb_test where status='N'; Explained
发现同样是走了索引,但是代价却高出许多!
SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2648582318 ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000 | 10000 | 40 (0)| 0 | 1 | TABLE ACCESS BY INDEX ROWID| ljb_test | 1000 | 10000 | 40 (0)| 0 |* 2 | INDEX RANGE SCAN | ID_NORMAL | 400 | | 91 (0)| 0 -------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------------------------ 2 - access("STATUS"='N') 14 rows selected
不难看两者效率差别巨大,所以在你知道某些字段值的分布,并确定某些字段的特定值比较少,而你的应用却经常查询这些特定的少量值的时候,可以用这个方法, 于是选择性索引就闪亮登场,有了大展身手的机会了!
介绍完了选择性索引,大家能回答为什么更快吗?很多人会说:“这简单,选择性索引只选择少的特定值做索引,那索引块就很小,搜索起来更快!”回答正确!不过你能回答索引块变小是多小,和普通索引差距有多少?能量化为具体值吗?下面我来回答这个问题。
以下是选择性索引id_status建好后,索引的情况
SQL> analyze index id_status validate structure; Index analyzed SQL> select name,btree_space,lf_rows,height from index_stats; NAME BTREE_SPACE LF_ROWS HEIGHT ------------------------------ ----------- ---------- ------------------------------------------------------ ID_STATUS 8000 1 1 接着是看id_normal,普通索引的情况 SQL> select name,btree_space,lf_rows,height from index_stats; NAME BTREE_SPACE LF_ROWS HEIGHT ------------------------------ ----------- ---------- ------------------------------------------------------- ID_NORMAL 1464032 100001 2
总结:很明显,选择索引的大小为8K,而普通索引的大小为1464K;选择索引的高度为1,而普通索引的的高度为2。差异相当显著!通过本小节应该学会巧妙的将选择性索引应用到的工作中去,会带来意想不到效果!
no comment untill now