《数据库索引设计优化》读书笔记（七）

原创已于 2025-04-03 14:11:59 修改 · 1.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库索引设计

于 2016-02-04 16:27:17 首次发布

《数据库索引设计优化》读书笔记专栏收录该内容

8 篇文章

订阅专栏

本文深入探讨了数据库索引选项，并通过MySQL和Oracle实例对比，揭示了索引行压缩、唯一性约束、从两个方向扫描索引等特性的差异，以及索引键截断、基于函数的索引、索引跳跃式扫描和块索引等功能的实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

第13章数据库索引选项

练习

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

索引行压缩与异常情况

MySQL 支持
Oracle 支持

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

create table t1 (a int);
insert into t1 values (1),(2),(3),(null),(4),(null),(5),(null);
commit;
select count(*) from t1;
create unique index idx1 on t1(a);

explain select * from t1 where a=1;
explain select * from t1 where a is null;

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

索引键以外的其它索引列

MySQL 不支持
Oracle 不支持

索引包含的列（Included Columns / Covered Columns）指的是：不参与查找，但存储在索引中，避免回表查询以减少 I/O。例如：

CREATE INDEX idx_user_orders ON orders (user_id) INCLUDE (order_date, amount);

SQL Server、PostgreSQL 支持索引包含列。

唯一性约束

MySQL 不支持
Oracle 支持

create table t1(a int,b varchar2(20));
create index idx1 on t1(a,b);
alter table t1 add primary key (a);
select index_name,uniqueness from user_indexes;

查询结果如图2 所示

select constraint_name,constraint_type from user_constraints;

查询结果如图3 所示

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

再来看 MySQL：

create table t1(a int,b varchar(20));
create index idx1 on t1(a,b);
alter table t1 add primary key (a);
show index from t1;

查询结果如图4 所示

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

从两个方向扫描索引

MySQL 支持
Oracle 支持

create table t1 as select * from dba_objects;
create index idx1 on t1(owner,object_id);
exec dbms_stats.gather_table_stats(ownname=>'WXY',tabname => 'T1',estimate_percent=> 20 , cascade=> TRUE );
set autotrace traceonly;
select * from t1 where owner='SYS' and object_id > 50000 order by object_id desc;

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

再来看 MySQL：

create table t1 as select * from information_schema.tables;
create index idx1 on t1(table_schema,table_name);
explain select * from t1 where table_schema='test' order by table_name desc;
explain select * from t1 where table_schema='test' order by table_name;

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