技术博客
首页 所有文章 标签 分类 关于
技术博客

目录

Oracle-数据库基础入门六视图与索引的深入解析

 收录于 Oracle
   约 4772 字   预计阅读 10 分钟 
https://bing.ee123.net/img/rand?artid=146076363

Oracle 数据库基础入门（六）：视图与索引的深入解析

在 Oracle 数据库的知识体系中，视图与索引是提升数据库操作效率和优化数据管理的重要工具。对于 Java 全栈开发者而言，熟练掌握视图与索引的运用，不仅能够增强数据库查询的性能，还能为构建高效稳定的后端应用提供有力支持。接下来，让我们深入探索 Oracle 数据库中的视图与索引。

目录

一、Oracle 视图

（一）视图的本质与作用

视图，从本质上来说，是一张虚拟表。它并不像物理表那样实际存储数据，其数据来源于底层的多张物理表。视图的主要作用是降低程序员编写 SQL 语句的复杂度。通过将复杂的查询逻辑封装在视图中，后续在进行数据查询时，只需对视图进行操作，而无需每次都编写复杂的多表联合查询语句。视图分为普通视图和物理视图。

（二）普通视图

  1. 语法与创建示例 ：创建普通视图的语法为 Create view 视图名称 as select 查询语句 。例如，我们有 Employees 表和 Departments 表，想要创建一个能够同时查询员工信息及其所在部门名称的视图，可以这样写：
Create view emp_dept_view AS 
Select
    E.*,
    D.Departmentname
From
    Employees e
Left join departments d on e.Departmentid = d.Id;
  1. 视图查询 ：查询视图与查询普通表类似，使用 Select * from 视图名称 [where 条件筛选] [order by 排序] [group by分组] 。比如，要查询工资大于 6000 的用户，可以这样写：
Select
    *
From
    Emp_dept_view
Where
    Salary >= 6000;

需要注意的是，视图主要用于查询操作，通常不建议针对视图进行新增、修改和删除操作。因为视图本身不存储数据，对视图的增删改操作可能会导致底层物理表数据的不一致或不可预期的结果。在 Java 全栈开发中，当我们开发企业人力资源管理系统时，可能会创建类似上述的视图。后端开发人员通过 Java 代码使用 JDBC 或相关框架（如 MyBatis）连接数据库，查询该视图获取员工和部门的关联信息。然后将这些信息封装成 Java 对象（如 EmployeeDepartment 对象，包含员工和部门的相关属性），传递给前端。前端可以利用这些数据在员工信息展示页面中，方便地呈现员工所属部门等详细信息，提升用户体验。

（三）物理视图

  1. 与普通视图的区别 ：在 MySQL 数据库中，只有普通视图，而 Oracle 数据库提出了物理视图的概念。普通视图在每次查询时都需要扫描一次底层物理表数据，这在数据量较大时，性能表现较差。物理视图则通过预先计算并存储查询结果，大大提高了查询性能。不过，物理视图的缺点是会占用更大的磁盘空间。

    https://i-blog.csdnimg.cn/direct/2cd26f66577142d491c2ed8622cdb547.png

  2. 语法与创建示例 ：创建物理视图的语法为 Create materialized view 视图名称 referesh on commit as select 查询语句 。例如：

Create materialized view emp_dept_mater_view refresh on commit as 
select
    E.*,
    D.Departmentname
From
    Employes e,
    Departments d
Where
    e.departmentid = d.id;

https://i-blog.csdnimg.cn/direct/2d06121337da41abbe4b35b7945bebb3.png

需要注意的是，物理视图创建时，联表不能携带 left/right/inner join on 。同样，物理视图也主要适用于查询操作，不适合进行新增、修改和删除。在 Java 全栈开发的电商数据分析系统中，如果需要频繁查询一些复杂的商品销售统计数据，使用物理视图可以显著提升查询性能。后端开发人员创建物理视图后，在 Java 代码中查询该视图，将统计结果传递给前端。前端可以根据这些数据生成销售报表、图表等，为运营人员提供高效的数据展示和分析功能。

二、索引

（一）索引的重要性与类比

索引是数据库查询调用的最佳手段之一，它类似于书的目录。当我们需要在书中查找特定内容时，通过目录可以快速定位到相关页面。在数据库中，索引能够帮助我们快速定位到所需的数据行，极大地提高查询效率。索引有多种类型，包括分类索引、主键索引、唯一索引、外键索引、组合索引、反向键索引、位图索引等。

（二）索引结构

  1. Oracle 数据库的 B 树结构 ：Oracle 数据库索引底层的数据存储结构是 B 树（B - Tree）。在 B 树中，数据挂载在主键索引上。B 树的特点是能够高效地进行数据的插入、删除和查找操作，适用于各种类型的数据。
  2. MySQL 数据库的 B + 树结构 ：MySQL 数据库使用 B + 树（B + Tree）。B + 树与 B 树有所不同，聚簇索引中索引和数据是挂在一起的，主键索引通常都是聚簇的，这意味着找到索引就能够直接找到数据。而非聚簇索引中索引和数据是分开存储的，使用非聚簇索引查询时可能会出现回表的情况，即先通过索引找到数据的位置，再到实际的数据存储位置获取完整的数据。在 Java 全栈开发中，了解不同数据库的索引结构有助于我们在进行数据库设计和查询优化时做出更合理的决策。例如，在设计数据库表结构时，根据数据的特点和查询需求选择合适的索引类型，以提高系统性能。

（三）主键索引

在创建表时，使用 primary key 主键约束，实际上也就创建了主键索引。例如：

create table EMP_INFO(
    id number primary key,
    -- 其他字段
);

对于主键索引，查询时效率非常高。比如查询 id 为 10 的记录：

select * from EMP_INFO where id = 10;

在 Java 全栈开发的用户管理系统中，通过主键索引查询用户信息能够快速响应前端请求。后端开发人员在编写查询用户信息的 SQL 语句时，利用主键索引的高效性，将查询结果快速返回给前端，提升用户体验。

（四）唯一索引

  1. 创建语法与示例 ：建表时使用 unique 唯一约束，实际上就是创建了唯一索引。也可以使用以下语法单独创建唯一索引： create unique index 索引名称 on 表(字段) 。例如：
create unique index uk_login_name on EMP_INFO(LOGIN_NAME);

https://i-blog.csdnimg.cn/direct/4c27e189fe494942933e3bdae6057dc2.png

  1. 查询示例与回表问题 ：假设我们有如下查询：
-- 查询目录中的所有内容
select id,login_name from emp_info;
-- 在目录表中，筛选对应的数据
select id,login_name from emp_info where login_name = 'zy';

这两个查询直接通过唯一索引就可以获取到所需数据。但如果查询的字段超出了索引覆盖的范围，就可能会出现回表的情况，例如：

select id,login_name,emp_name from emp_info where login_name = 'zy';

回表操作会降低查询效率，在实际开发中应尽量规避。在 Java 全栈开发的系统登录模块中，使用唯一索引查询用户登录名能够快速验证用户身份。但如果在查询用户登录信息时，不小心查询了过多未被索引覆盖的字段，就可能导致回表，影响系统性能。因此，开发人员需要对查询语句进行优化，确保查询字段在索引覆盖范围内。

（五）普通索引

普通索引和唯一索引的区别在于普通索引可以存储重复的值。创建普通索引的语法为 create index 索引名称 on 表(字段) 。例如，在 emp_info 的 emp_name 字段上构建一个普通索引：

create index idx_emp_name on emp_info(emp_name);

https://i-blog.csdnimg.cn/direct/2ddc481c20a74ed8bbe84e3cfee938a7.png

查询示例如下：

-- 查询整个索引表
select id,emp_name from emp_info;
-- 从索引表中查询姓 "赵"的人
select id,emp_name from emp_info where emp_name like '赵%';

当查询的字段超出索引覆盖范围时，同样会出现回表情况：

select * from emp_info where emp_name like '赵%';

为了减少回表次数，可以采用索引覆盖和索引下推的方法。在 Java 全栈开发的客户信息管理系统中，如果经常需要根据客户姓名进行查询，创建普通索引可以提高查询效率。但开发人员要注意避免因查询语句不当导致的回表问题，通过合理设计查询语句和索引结构，提升系统性能。

（六）组合索引

https://i-blog.csdnimg.cn/direct/f5fc2bc09bd442689ca6edf0c6aea2a6.png

组合索引，也称为复合索引。索引覆盖实际上就是利用组合索引将查询的多列都组合到同一个索引表中。创建组合索引的语法为 create index 索引名称 on 表(字段1,字段2,……) 。例如

create index idx_emp_name_emp_no on emp_info(emp_no,emp_name);

查询时要满足 “最左化查询原则”，例如：

https://i-blog.csdnimg.cn/direct/601c773aba084d248d3fcb45b205844f.png

select id,emp_name,emp_no from emp_info where emp_name like '赵%' and emp_no like '00%';

最左化原则要求在使用组合索引查询时，先匹配最左边的索引字段，然后依次下推到其他字段进行匹配。索引下推则是在组合索引中，按照最左化原则，先在一级索引中进行匹配，然后下推到二级索引继续匹配，以此类推。在 Java 全栈开发的订单管理系统中，如果经常需要根据订单编号和客户姓名进行联合查询，创建组合索引并遵循最左化原则能够显著提高查询效率。后端开发人员在编写查询订单信息的 SQL 语句时，要确保查询条件符合组合索引的最左化原则，从而充分利用索引的优势，快速返回查询结果给前端，提升系统响应速度。

（七）执行计划

执行计划是数据库提供的一种查询 SQL 执行方案的技术。通过执行计划，我们可以了解数据库是如何执行 SQL 查询的，从而对查询进行优化。使用执行计划的语法如下

explain plan for 查询语句;
select * from table(dbms_xplan.display);

例如：

https://i-blog.csdnimg.cn/direct/67fa457659bf43b89fcb8bdc2fdd5ba4.png

explain plan for select id,emp_name,emp_no from emp_info where emp_no like '00%' and emp_name like '赵%'; 
-- 显示最新的执行计划
select * from table(dbms_xplan.display);

在 Java 全栈开发中，开发人员可以通过分析执行计划，了解查询语句的性能瓶颈，进而优化 SQL 语句。比如，如果执行计划显示某个查询使用了全表扫描而不是索引，开发人员可以检查索引是否存在、是否合理，以及查询语句是否正确使用了索引等，通过优化使查询能够利用索引，提高查询效率。

（八）外键索引

当表和表之间在外键上添加了外键约束时，在查询时会自动使用外键索引。例如：

-- 父表
create table class_info(
    id number(11) primary key,
    class_name nvarchar2(20)
);
-- 子表
create table students(
    id number(11) primary key,
    stu_no varchar(11) unique,
    stu_name nvarchar2(20) not null,
    age number(3) check (age >=0 and age<= 200 ),
    gender char(3) check (gender in ('MALE','FEMALE')),
    fk_class_id number(11) constraint fk_cls_cons references class_info(id)
);
-- on s.fk_class_id = c.id; 使用外键索引
select s.* from students s left join class_info c on s.fk_class_id = c.id;

在 Java 全栈开发的学校管理系统中，通过外键索引能够快速查询学生所属班级等关联信息。后端开发人员利用外键索引的特性，编写高效的查询语句，将查询结果传递给前端，用于展示学生的班级信息等功能。

（九）反向键索引

反向键索引是一种特殊的索引类型，了解即可。创建反向键索引的语法为：

-- 普通索引
create index 索引名称 on 表(字段);
-- 反向键索引
create index 索引名称 on 表(字段) reverse;

https://i-blog.csdnimg.cn/direct/01a3e37209624e50b73477dd0faae6ef.png

例如：

create index idx_emp_name_reverse on emp_info(emp_name) reverse;

（十）位图索引

位图索引是一种特殊的索引，比较适合于重复值较多的列，例如性别（男 / 女）、状态等。创建位图索引的语法为 create bitmap index 索引名称 on 表的表名(字段) 。例如：

create bitmap index idx_gender_bitmap on emp_info(gender);

（十一）不适合添加索引的条件

  1. 经常变化的列 ：由于索引需要维护，如果列的值经常变化，会导致索引频繁更新，降低数据库性能。
  2. 小表 ：对于数据量较小的表，全表扫描的效率可能更高，添加索引反而会增加存储开销。
  3. 列中存在大量的重复值 ：在这种情况下，索引的优势不明显，因为通过索引查询和全表扫描的效率差异不大。
  4. 当列中数据太多 ：如果列中的数据量非常大，创建索引可能会占用大量的磁盘空间，并且索引的维护成本也会很高。
  5. 很少使用的列 ：为很少使用的列创建索引是一种资源浪费，因为这些索引几乎不会被使用。

（十二）索引失效的场景

  1. SQL 语句中使用 * 或非索引字段 ：使用 * 会导致数据库查询所有字段，可能无法利用索引。查询非索引字段也会使索引失效。
  2. SQL 包含四则运算 ：例如在查询条件中对字段进行加、减、乘、除等运算，可能导致索引无法使用。
  3. SQL 中在执行模糊查询时，将 % 写到前面 ：如 where column like '%value' ，这种情况下无法使用索引进行查询。
  4. SQL 中使用 or 关键字 ：使用 or 连接多个查询条件时，索引可能失效。
  5. SQL 中使用 <? 或 != ：这两种运算符可能导致索引无法使用。
  6. SQL 中使用 in 或 not in ：在某些情况下， in 和 not in 会使索引失效。
  7. 组合索引没有遵从 “最左化原则” ：如果在使用组合索引时不遵循最左化原则，索引可能无法发挥作用。
  8. 数据库认为不需要使用索引 ：数据库的查询优化器会根据数据量、查询条件等因素判断是否使用索引，如果认为全表扫描更高效，索引就会失效。

三、企业工作小技巧

  1. 视图的使用技巧 ：在企业项目中，当涉及复杂的多表联合查询时，优先考虑创建视图。例如，在一个大型企业的财务系统中，涉及多个财务相关表的联合查询来生成财务报表。通过创建视图，将复杂的查询逻辑封装起来，不仅方便了开发人员后续的查询操作，还能提高代码的可读性和可维护性。同时，在 Java 全栈开发中，视图可以与后端框架紧密结合。比如在 Spring Boot 项目中，通过 MyBatis 操作视图，将视图查询结果映射为 Java 对象，方便前端调用接口获取数据。
  2. 索引的优化技巧 ：在创建索引时，要根据业务需求和数据特点进行合理设计。对于高频查询的字段，优先创建索引。例如，在电商系统中，用户经常根据商品名称和价格进行查询，那么可以在商品表的 product_name 和 price 字段上创建索引。同时，要定期对索引进行维护，比如使用 ALTER INDEX 语句对索引进行重建或重组，以提高索引的性能。在 Java 全栈开发中，开发人员要时刻关注索引的使用情况。通过分析执行计划，及时发现索引失效的情况，并对 SQL 语句和索引结构进行优化。此外，在数据量较大的系统中，可以使用缓存技术（如 Redis）结合索引，进一步提高查询性能。先从缓存中查询数据，如果缓存中没有，再通过索引从数据库中查询，将查询结果存入缓存，以便下次查询使用。

通过对 Oracle 数据库视图与索引的深入学习，我们掌握了提升数据库性能和优化数据管理的重要技能。在未来的 Java 全栈开发工作中，灵活运用视图与索引，将为我们构建高效、稳定的应用系统提供有力保障。

更新于 2025-03-06
 数据库, Sql, Oracle, Java
返回 | 主页