当前位置： 首页 > news >正文

Oracle 学习笔记

news 2025/7/15 10:44:33

1. Oracle 的基本使用规范 ，Oracle 和 MySQL 的主要区别？

Oracle 大小写不敏感，所以一般表名列名都采用大写 + 下划线，加双引号大小写敏感；MySQL 默认区分大小写（受系统和配置影响）；

Oracle 表名列名最长字符长度为 30 个字符；MySQL 表名最多 64 字符，列名最多 64 字符；

Oracle 中 '' 等价于 NULL（与 MySQL 不同）， Oracle 中 '' 就是 NULL，而 MySQL 中 '' 是一个有效的字符串；MySQL '' 是有效的空字符串；

Oracle 使用 VARCHAR2 替代 VARCHAR（Oracle 中 VARCHAR 行为不明确，不推荐使用）；MySQL 使用 VARCHAR；

Oracle 精确数值使用 NUMBER(p,s)，非浮点类型；

Oracle 支持所有四种标准隔离级别，默认 READ COMMITTED，MySQL 支持所有隔离级别，默认 REPEATABLE READ；

Oracle 不支持 Limit 所以需要用 ROWNUM，先查出 0 ~ endRow，再查出 startRow ~ endRow；MySQL 原生支持 LIMIT offset, size；

SELECT * FROM (
SELECT borh.*, ROWNUM AS RNUM FROM (
SELECT * FROM BALLAST_OPS_REPORT_HARBOR) borh WHERE ROWNUM <= 4) t WHERE RNUM >= 2

2. Oracle 的常见数据类型有哪些？

  • VARCHAR2(n)
    可变长度字符串，最多 n 个字节，推荐用于存储普通文本。
  • CHAR(n)
    固定长度字符串，长度不足时右侧用空格填充。
  • CLOB
    大文本对象，用于存储大量文本数据（最大可达 4GB）。
  • BLOB
    大二进制对象，用于存储二进制数据，如图片、文件（最大可达 4GB）。
  • NUMBER(p,s)
    精确数值类型，p 是精度（总位数），s 是小数位数，适合财务等高精度需求。
  • NUMBER
    不指定精度的数字类型，最多支持 38 位有效数字。

3. Oracle 的事务隔离级别有哪些？默认是什么？

  • 读未提交（Read Uncommitted）
    • Oracle 实际上不支持该隔离级别，无法读取到未提交的数据。
  • 读已提交（Read Committed）
    • Oracle 的默认隔离级别。事务只能读取到其他事务已经提交的数据，避免了脏读。
    • 通过多版本并发控制（MVCC）实现，保证读到的数据一致性。
  • 可重复读（Repeatable Read）
    • Oracle 不直接支持该隔离级别，但通过快照读和序列化实现类似效果。
  • 串行化（Serializable）
    • 最高隔离级别，事务完全串行执行，防止脏读、不可重复读和幻读，但性能开销较大。

4. Oracle 如何实现分页查询？

Oracle 不支持 Limit 所以需要用 ROWNUM，先查出 0 ~ endRow，再查出 startRow ~ endRow；MySQL 原生支持 LIMIT offset, size；

SELECT * FROM (
SELECT borh.*, ROWNUM AS RNUM FROM (
SELECT * FROM BALLAST_OPS_REPORT_HARBOR) borh WHERE ROWNUM <= 4) t WHERE RNUM >= 2

5. Oracle 的索引有哪些类型？分别适用什么场景？

索引类型

说明

适用场景

B树索引（B-Tree Index）

默认且最常用的索引类型，基于平衡树结构，支持等值和范围查询。

查询条件是等值、范围、排序，数据分布较均匀的场景。

位图索引（Bitmap Index）

用位图方式存储索引，适合低基数（低唯一性）列，节省空间，适合复杂条件组合。

数据量大，且字段取值有限（如性别、状态等），主要用于数据仓库分析场景。

函数索引（Function-based Index）

对表达式或函数的结果建立索引，支持复杂查询。

查询中使用了函数或表达式过滤的字段，如 UPPER(col) 或计算列。

聚簇索引（Clustered Index）

把相关数据物理存储在一起，基于聚簇键，物理顺序与索引顺序相同。

表中经常基于聚簇键进行范围查询，且关联数据访问较多。

反向键索引（Reverse Key Index）

将索引键的字节顺序反转，避免顺序插入导致的块争用。

高并发插入且索引键单调递增（如序列号）时，防止索引块“热点”。

全文索引（Oracle Text Index）

支持全文搜索，基于文本内容进行索引。

需要全文搜索或文本内容模糊匹配的场景。

域索引（Domain Index）

用于特殊数据类型（如空间数据、XML数据）的索引。

存储空间数据（SDO_GEOMETRY）或XML文档时。

6. Oracle 的一致性读是怎么实现的？

Oracle 通过 MVCC（多版本并发控制）机制 实现一致性读，不加锁也能保证事务隔离与数据一致性。

Oracle 的 SCN 是全局递增的“时间戳”，Undo 中保存的是历史版本 + 修改时的 SCN，Oracle 通过比对事务的快照 SCN 和数据的修改 SCN 实现一致性读。

  1. SCN（System Change Number）
    • Oracle 的全局递增“逻辑时间戳”，每次事务提交或系统变更都会生成新的 SCN。
  1. Undo Segment（回滚段）
    • 每条数据在被修改前，Oracle 会把其旧版本备份到 Undo 中，记录修改前值及当时的 SCN。
  1. 一致性读过程：
    • 查询事务开始时，Oracle 会拍下当前系统 SCN，称为“快照 SCN”；
    • 查询数据时：
      • 如果当前数据行的 SCN ≤ 快照 SCN，则说明是快照前就已提交的版本 → 可见；
      • 如果数据行的 SCN > 快照 SCN，则说明是快照之后才发生的变更 → 不可见，Oracle 会回溯 Undo 中旧版本，直到找到可见版本或无数据可见。

例子：

    1. 初始表为空，SCN = 100；
    2. 事务 T1 插入一行数据并提交 → Oracle 生成 SCN = 101；
      • Undo 中保存的是“插入前的数据”（即没有这条记录）；
    1. 事务 T2 修改这条数据并提交 → Oracle 生成 SCN = 102；
      • Undo 中保存的是“旧值”及修改时间 SCN = 101；
    1. 事务 T3 删除数据并提交 → Oracle 生成 SCN = 103；
      • Undo 中保存的是“被删除前的数据”及 SCN = 102；

每一次数据的变化，都会：

    • 分配一个新的 SCN；
    • 在 Undo 中保留前一个版本；
    • 当前块上的数据会标记“最后修改的 SCN”。

7. Oracle 如何防止幻读？

幻读是指在一个事务内两次执行相同的查询时，第二次查询出现了之前不存在的新行（即“幻影”行），导致结果不一致。

  • MySQL InnoDB（默认 REPEATABLE READ） 通过 MVCC + 间隙锁（Gap Lock）来防止幻读。
  • Oracle 不使用间隙锁，而是通过隔离级别 + undo + SCN 快照机制，要想防止幻读，必须选择更高的隔离级别或强制加锁。

8. Oracle 如何查看 SQL 的执行计划？

使用 EXPLAIN PLAN FOR + SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)

说明：

    • EXPLAIN PLAN 会将执行计划写入 PLAN_TABLE。
    • DBMS_XPLAN.DISPLAY() 是 Oracle 提供的包，用来格式化读取。
explain plan FOR SELECT * FROM (
SELECT borh.*, ROWNUM AS RNUM FROM (
SELECT * FROM BALLAST_OPS_REPORT_HARBOR) borh WHERE ROWNUM <= 4) t WHERE RNUM >= 2
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

9. Oracle 的索引什么时候会失效？

1.最左前缀匹配原则
Oracle 和 MySQL 都遵循最左前缀原则，多列索引查询必须从最左侧的列开始连续匹配，否则索引失效。

比如索引 (a,b,c)，查询条件用 a 或 a,b 可以用索引，单独用 b 或 c 则不能。

  1. 函数或表达式导致索引失效
    例如 WHERE UPPER(name) = 'ABC'，这类对字段应用函数会导致索引失效（除非是函数索引）。Oracle 和 MySQL 都是如此，默认索引字段上加函数，索引不能被使用。
  1. 隐式类型转换
    比如字段是数字类型，查询条件传字符串，数据库做隐式类型转换，会导致索引失效。两者都存在，尤其 MySQL 在这方面更敏感。
  1. 范围查询导致后续索引列失效
    对于复合索引 (a,b,c)，如果对 a 使用了范围查询（>, <, BETWEEN 等），那么 b 之后的列索引无法被利用。Oracle 和 MySQL 都遵循此规则。
  1. OR 连接多个条件导致索引失效
    如 WHERE a = 1 OR b = 2，可能导致索引失效，或导致全表扫描。两者均有此问题，MySQL 支持部分索引合并，Oracle 有时会用Bitmap索引。
  1. 隐式使用通配符导致索引失效
    如 LIKE '%abc'（前置通配符），索引不能用。Oracle 和 MySQL 均如此。
  1. 数据分布极度不均衡导致优化器放弃索引
    如某索引列只有少数几个不同值，导致索引选择率低，优化器可能放弃使用索引。两者都有。
  1. 字段前面带有空格或不可见字符
    可能导致索引匹配失败，影响查询性能。两者都可能出现。
  1. Null 值索引处理差异
    Oracle 索引不存储值为NULL的行；MySQL InnoDB 存储NULL。这导致某些 NULL 查询的索引使用差异。
  1. 索引类型不同导致使用差异
    Oracle 支持Bitmap索引、函数索引等，MySQL主要是B树和哈希索引（Memory表）。某些特殊索引类型的使用和失效规则不完全一样。

10. Oracle 中的锁有哪些？死锁如何检测？

锁类型

说明

行级锁 (TX)

默认加锁方式，细粒度

表级锁（TM）

显式或隐式对整表加锁

DDL 锁

防止并发修改结构

MySQL InnoDB 死锁检测

维护一张等待图：事务作为节点，事务间因为等待锁而形成边。

当事务尝试获取锁被阻塞时，InnoDB检查等待图是否有环路。

如果检测到环路，InnoDB选择一个事务作为“牺牲者”回滚，释放锁，打破死锁。

死锁检测频率高且响应快，适合高并发场景。

Oracle 死锁检测

Oracle 也基于等待图检测死锁，利用锁结构及会话信息管理等待。

Oracle锁分为行锁(TX)、表锁(TM)等，死锁检测时扫描会话等待和持有锁的资源。

发现死锁后抛出异常，自动回滚其中一个事务。

Oracle的死锁检测还可以依赖 DBA 视图和工具辅助定位。

11. 如何定位并优化一条慢 SQL？

高频 SQL 建索引，构建覆盖索引，避免索引失效，大数据分页查，统计信息常更新。

12. 有一个用户表 + 订单表，如何优化查询“最近 6 个月有订单的活跃用户”？

SELECT *
FROM user u
WHERE EXISTS (SELECT 1FROM `order` oWHERE o.user_id = u.idAND o.order_time >= DATE_SUB(NOW(), INTERVAL 6 MONTH)LIMIT 1
);

查询最近 6 个月有订单的用户，我通常会使用子查询 + EXISTS 的方式，避免大表 JOIN 造成资源浪费，并确保 WHERE 条件可以命中联合索引（user_id, order_time）。对于数据量较大且频繁查询的场景，我还会考虑使用中间缓存表或临时表，定时计算活跃用户，提升查询效率，避免每次都访问订单大表。

13. 大表更新太慢怎么办？

1.分批执行

UPDATE my_big_table
SET status = 'DONE'
WHERE status = 'TODO'AND ROWNUM <= 10000;

2.建立覆盖索引， 大表更新一定要走索引；

3.异步处理 + 消息队列：将更新请求写入 Kafka、RabbitMQ 等队列，后台消费者慢慢消费，做批量更新，高并发低阻塞，还可降级熔断；

大表更新慢我通常从两个方向处理，一是从执行逻辑上控制更新粒度，比如采用分页/分片批量更新、控制单次操作数量，避免锁全表；二是从执行效率上做优化，比如为 WHERE 条件添加合适索引，避免全表扫描，并使用原生 SQL 批处理代替 ORM 带来的低效更新。在数据量极大或对实时性要求不高的场景，我会考虑使用异步更新、临时表重命名等方案，甚至通过中间表记录需要更新的数据，由后台任务定期清理。

14. 数据库连接暴增如何排查？

数据库连接暴增，80% 是由于连接未释放或连接池参数不当，20% 是高并发或死循环代码引起，排查从数据库 → 应用日志 → 连接池状态三级入手。

是的，我们在线上遇到过一次数据库连接数飙升导致接口阻塞的问题。根据经验，我按下面 3 步排查的：

    1. 数据库层排查
      • 通过 SHOW PROCESSLIST（MySQL）或 v$session（Oracle）查看活跃连接。
      • 判断是否是某个服务/SQL 占用了大量连接，确认连接数来源。
    1. 应用日志排查
      • 查看是否有连接泄露、未关闭连接的异常，例如：Timeout waiting for idle object、Connection is closed。
      • 检查定时任务、循环逻辑、重试机制是否导致异常请求。
    1. 连接池状态检查
      • 查看连接池（如 Hikari、Druid）的运行指标，比如活动连接数是否已满、空闲连接是否为 0。
      • 是否设置了合理的 最大连接数 / 连接超时 / 连接存活时间。
      • 是否启用了 连接泄露检测。

最终定位是某个服务接口高并发调用下存在连接未释放问题，通过加上 try-with-resources 保证释放连接，并优化了连接池配置，问题顺利解决。

15. 如何设计一个高并发插入的主键策略？

1.数据库自增（IDENTITY / SEQUENCE）

优点：实现简单，数据库自动生成。

缺点：写压力大时，可能成为数据库性能瓶颈；分布式环境难保证全局唯一。

2. UUID（全局唯一标识符）

优点：全局唯一，适合分布式环境。

缺点：占用空间大，索引性能较差，插入时会导致随机IO。

3. 雪花算法（Snowflake）

优点：高性能，生成的ID趋势递增，利于索引；支持分布式。

缺点：需要额外服务或集成。

4. 数据库序列 + 应用前缀

优点：数据库序列保证唯一，应用区分不同节点。

缺点：复杂度稍高，需要协调序列号范围。

5. 业务号策略（基于时间戳 + 机器ID + 序号）

优点：自定义灵活，支持高并发和分布式。

缺点：需要精心设计防止重复。

http://www.lryc.cn/news/588179.html

相关文章：

  • 【橘子分布式】Thrift RPC(理论篇)
  • LINUX714 自动挂载/nfs；物理卷
  • 基于STM32的智能抽水灌溉系统设计（蓝牙版）
  • 前端开发中的常见问题及解决方案
  • 数据结构——优先队列(priority_queue)的巧妙运用
  • 渗透第一次总结
  • 【Python办公】Python如何批量提取PDF中的表格
  • 前端基础之《Vue（22）—安装MongoDB》
  • 【Java EE初阶 --- 网络原理】初识网络
  • 第十七节：第五部分：网络通信：TCP通信-支持与多个客户端同时通信
  • 如何使用Cisco DevNet提供的免费ACI学习实验室（Learning Labs）？（Grok3 回答）
  • 笔试——Day6
  • CISSP知识点汇总- 通信与网络安全
  • 内部文件审计：企业文件服务器审计对网络安全提升有哪些帮助？
  • 密码学中立方攻击的另类应用
  • 安全初级（一）
  • 多租户云环境下的隔离性保障：虚拟化、容器、安全组如何协同防护？
  • git 访问 github
  • 【深度学习框架终极PK】TensorFlow/PyTorch/MindSpore深度解析！选对框架效率翻倍
  • 智能Agent场景实战指南 Day 12：医疗咨询Agent设计模式
  • vue3+arcgisAPI4示例:自定义多个气泡窗口展示(附源码下载)
  • C#中发布订阅的阻塞非阻塞
  • Spring Boot + Vue2 实现腾讯云 COS 文件上传：从零搭建分片上传系统
  • QT——信号与槽
  • Zabbix在MySQL性能监控方面的运用
  • 闲庭信步使用图像验证平台加速FPGA的开发：第十五课——基于sobel算子边缘检测的FPGA实现
  • Spring核心概念指南
  • Linux部署Mysql
  • (LeetCode 每日一题) 1290. 二进制链表转整数 (链表+二进制)
  • 微前端框架深度对决：qiankun、micro-app、wujie 技术内幕与架构选型指南