快乐学习
前程无忧、中华英才非你莫属!

数据库索引选项

练习

13.1 调查你当前使用的DBMS版本关于索引的限制和高级选项。

.索引行压缩与异常情况

MySQL支持

Oracle支持


MySQL使用NULL值实现索引行压缩。但不推荐在实际中使用NULL来代替一个特定的值,因为从长远来看,这可能会导致应用系统错误。

[sql] view plain copy

  1. create table t1 (a int);  

  2. insert into t1 values (1),(2),(3),(null),(4),(null),(5),(null);  

  3. commit;  

  4. select count(*) from t1;  

  5. create unique index idx1 on t1(a);  

  6.   

  7. explain select * from t1 where a=1;  

  8. explain select * from t1 where a is null;  


表中有三个NULL值,但唯一索引创建成功。从图1可以看到,查询NULL值的记录使用了idx的索引覆盖,但type类型却是ref,表示索引的数据值是非唯一的。从Extra列的Using where可以看到需要过滤元组,可见多行NULL值被压缩成一个唯一键值。


.索引键以外的其他索引列

MySQL不支持

Oracle不支持


.唯一性约束

MySQL不支持

Oracle支持

[sql] 

  1. create table t1(a int,b varchar2(20));  

  2. create index idx1 on t1(a,b);  

  3. alter table t1 add primary key (a);  

  4. select index_name,uniqueness from user_indexes;  

查询结果如图2所示

[sql] 

  1. select constraint_name,constraint_type from user_constraints;  

查询结果如图3所示

从图2可以看到,表t1上只有一个非唯一索引idx1。从图3可以看到,表t1上有一个主键约束。索引idx1可以被用来约束主键a的唯一性。这可以通过引入primary key约束来做到。


再来看MySQL

[sql] 

  1. create table t1(a int,b varchar(20));  

  2. create index idx1 on t1(a,b);  

  3. alter table t1 add primary key (a);  

  4. show index from t1;  

查询结果如图4所示

MySQL在主键上新建了一个名为PRIMARY的索引,而没有使用已有的idx1索引加主键约束的方式。


.从两个方向扫描索引

MySQL支持

Oracle支持

[sql] 

  1. create table t1 as select * from dba_objects;  

  2. create index idx1 on t1(owner,object_id);  

  3. exec dbms_stats.gather_table_stats(ownname=>'WXY',tabname => 'T1',estimate_percent=> 20 , cascade=> TRUE );  

  4. set autotrace traceonly;  

  5. select * from t1 where owner='SYS' and object_id > 50000 order by object_id desc;  

查询结果如图5所示,进行了索引范围逆向扫描。


再来看MySQL

[sql] view plain copy

  1. create table t1 as select * from information_schema.tables;  

  2. create index idx1 on t1(table_schema,table_name);  

  3. explain select * from t1 where table_schema='test' order by table_name desc;  

  4. explain select * from t1 where table_schema='test' order by table_name;  

查询结果如图6所示,无论是正向还是反向order by,都是使用的索引扫描,没有额外的排序。


.索引键截断

MySQL不支持

Oracle不支持


.基于函数的索引

MySQL不支持

Oracle支持


.索引跳跃式扫描

MySQL支持(Loose Index Scan)一个例子http://explainextended.com/2010/05/08/max-and-min-on-a-composite-index/

Oracle支持(INDEX SKIP SCAN)


.块索引

MySQL不支持

Oracle支持(cluster index)


.数据分区的二级索引

MySQL分区表只支持本地索引。

Oracle分区表支持本地索引(Local)和全局索引(Global)。

打赏
赞(0) 打赏
未经允许不得转载:同乐学堂 » 数据库索引选项

特别的技术,给特别的你!

联系QQ:1071235258QQ群:710045715

觉得文章有用就打赏一下文章作者

非常感谢你的打赏,我们将继续给力更多优质内容,让我们一起创建更加美好的网络世界!

支付宝扫一扫打赏

微信扫一扫打赏

error: Sorry,暂时内容不可复制!