一、Oracle性能优化机制及应用
1. 大型复杂查询优化实现
实现原理：
- CBO优化器：基于成本的优化器(Cost-Based Optimizer)会收集详细的统计信息，评估数千种执行计划可能
- 自适应执行计划：12c开始引入，可在执行过程中根据实际数据调整计划
- 星型转换：专门优化数据仓库中的星型模式查询
- 并行查询：自动将大查询分解为并行执行单元(通过PX进程)
应用实践：
-- 启用并行查询(8个进程)
SELECT /*+ PARALLEL(8) */ * FROM large_table WHERE condition;
-- 收集统计信息(优化器使用)
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','TABLE');
-- 使用物化视图加速复杂聚合
CREATE MATERIALIZED VIEW sales_mv
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE
AS SELECT region, SUM(amount) FROM sales GROUP BY region;
-- 查询时自动重写到物化视图
SELECT region, SUM(amount) FROM sales GROUP BY region;
2. 并发处理实现
实现原理：
- 多版本并发控制(MVCC)：通过UNDO段实现读一致性
- 高级锁机制：行级锁、表级锁、TX锁(事务锁)等精细控制
- 资源管理器：可限制用户/会话的资源使用
- In-Memory选项：将热数据保持在内存列式存储中
应用实践：
-- 查看锁情况
SELECT * FROM v$locked_object;
-- 使用SKIP LOCKED处理高并发队列
SELECT * FROM order_queue
WHERE status='PENDING' AND rownum=1
FOR UPDATE SKIP LOCKED;
-- 配置资源计划限制用户资源
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'APP_PLAN',
group_or_subplan => 'WEB_USERS',
mgmt_p1 => 50);
END;
3. 高级缓存机制
实现原理：
- SGA架构：包含Buffer Cache(数据块)、Shared Pool(SQL计划)、Redo Log Buffer等
- Result Cache：缓存查询结果集
- In-Memory Column Store：12c引入的列式内存存储
应用实践：
-- 启用结果缓存
SELECT /*+ RESULT_CACHE */ product_id, AVG(price)
FROM sales GROUP BY product_id;
-- 监控缓存命中率
SELECT name, physical_reads, db_block_gets,
round((1-(physical_reads/(db_block_gets + consistent_gets)))*100 "Hit Ratio"
FROM v$buffer_pool_statistics;
-- 配置In-Memory列存储
ALTER TABLE sales INMEMORY PRIORITY HIGH;
4. 分区表实现
实现原理：
- 分区消除：查询时自动排除无关分区
- 分区剪枝：执行计划中只访问必要分区
- 分区交换：快速加载数据(ETL场景)
应用实践：
-- 创建范围分区表
CREATE TABLE sales (
sale_id NUMBER,
sale_date DATE,
amount NUMBER
) PARTITION BY RANGE (sale_date) (
PARTITION p2020 VALUES LESS THAN (TO_DATE('2021-01-01','YYYY-MM-DD')),
PARTITION p2021 VALUES LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD'))
);
-- 分区维护(添加新分区)
ALTER TABLE sales ADD PARTITION p2022
VALUES LESS THAN (TO_DATE('2023-01-01','YYYY-MM-DD'));
-- 查询特定分区
SELECT * FROM sales PARTITION(p2021);
二、MySQL性能特性实现及应用
1. 简单查询性能优化
实现原理：
- 简单执行引擎：优化器复杂度远低于Oracle
- 覆盖索引：通过索引直接获取数据(不回表)
- ICP优化：Index Condition Pushdown，在存储引擎层过滤数据
- MRR优化：Multi-Range Read，优化随机IO
应用实践：
-- 创建覆盖索引
ALTER TABLE orders ADD INDEX idx_status_date (status, order_date);
-- 使用FORCE INDEX强制索引
SELECT * FROM orders FORCE INDEX(idx_status_date)
WHERE status='SHIPPED' AND order_date > '2023-01-01';
-- 监控索引使用
EXPLAIN FORMAT=JSON
SELECT * FROM users WHERE last_login > NOW() - INTERVAL 30 DAY;
2. 高并发读实现
实现原理：
- MVCC机制：通过undo日志实现非锁定读
- 多版本控制：每个事务看到特定时间点的数据快照
- 缓冲池：InnoDB Buffer Pool缓存热数据页
- Change Buffer：优化非唯一索引的DML操作
应用实践：
-- 配置缓冲池大小(通常设为物理内存的50-70%)
SET GLOBAL innodb_buffer_pool_size=8G;
-- 使用READ UNCOMMITTED隔离级别加速只读查询
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT COUNT(*) FROM large_log_table;
-- 优化只读事务
START TRANSACTION READ ONLY;
-- 多个查询...
COMMIT;
3. 写入性能优化方案
实现原理：
- 批量插入：减少事务提交次数
- 延迟索引更新：Change Buffer机制
- 并行复制：8.0+支持基于事务的并行复制
应用实践：
-- 使用批量插入代替单条插入
INSERT INTO orders (user_id, amount) VALUES
(1, 100), (2, 200), (3, 300);
-- 大表DDL操作使用online DDL
ALTER TABLE orders ADD INDEX idx_new (new_column), ALGORITHM=INPLACE, LOCK=NONE;
-- 调整刷新策略(危险，仅适用于可丢失数据的场景)
SET GLOBAL innodb_flush_log_at_trx_commit=2;
SET GLOBAL sync_binlog=0;
4. MySQL 8.0性能提升关键点
实现原理：
- 哈希连接：替代嵌套循环连接
- 不可见索引：测试索引不影响生产
- 窗口函数：减少自连接查询
- 直方图统计：优化选择性估计
应用实践：
-- 使用窗口函数替代复杂自连接
SELECT user_id, order_date,
RANK() OVER (PARTITION BY user_id ORDER BY order_date DESC) as rank
FROM orders;
-- 创建不可见索引测试性能
ALTER TABLE customers ADD INDEX idx_email (email) INVISIBLE;
-- 测试后决定是否可见
ALTER TABLE customers ALTER INDEX idx_email VISIBLE;
-- 使用哈希连接提示
SELECT /*+ HASH_JOIN(t1, t2) */ * FROM t1 JOIN t2 ON t1.id=t2.id;
三、实战性能对比案例
案例1：订单分析报表查询
Oracle实现：
-- 利用物化视图和分区
CREATE MATERIALIZED VIEW order_analysis_mv
PARTITION BY RANGE (order_date)
REFRESH COMPLETE ON DEMAND
AS
SELECT customer_id, product_id,
SUM(amount) as total_amount,
COUNT(*) as order_count
FROM orders
GROUP BY customer_id, product_id, TRUNC(order_date,'MM');
-- 查询时自动重写
SELECT product_id, SUM(total_amount)
FROM order_analysis_mv
WHERE order_date BETWEEN :start AND :end
GROUP BY product_id;
MySQL实现：
-- 8.0+使用窗口函数
WITH daily_totals AS (
SELECT product_id, order_date,
SUM(amount) OVER (PARTITION BY product_id, DATE(order_date)) as daily_sum
FROM orders
WHERE order_date BETWEEN :start AND :end
)
SELECT product_id, SUM(daily_sum)
FROM (SELECT DISTINCT product_id, DATE(order_date), daily_sum
FROM daily_totals) as distinct_days
GROUP BY product_id;
-- 创建适当索引
ALTER TABLE orders ADD INDEX idx_product_date (product_id, order_date);
四、选型建议
选择Oracle的场景：
- 需要处理TB级数据仓库
- 高并发OLTP系统(如银行核心)
- 需要高级RAC集群的场合
- 有复杂PL/SQL业务逻辑
选择MySQL的场景：
- Web应用后端数据库
- 读写比例高的系统(如CMS)
- 需要快速迭代的开发项目
- 云原生/容器化部署环境
混合架构：
- 用Oracle作为主OLTP+分析仓库
- 用MySQL作为只读副本处理前端查询
- 通过GoldenGate或Debezium同步数据
两种数据库都有其适用场景，关键是根据业务需求、团队技能和预算做出合理选择。