SQL 优化

SQL 优化是通过分析 SQL 执行逻辑、执行计划和数据库资源使用调整 SQL 写法、索引结构、数据库参数和表设计以最小的系统资源消耗获得最快的执行速度的系统性工程。它不是简单的 加索引而是从 SQL 解析、执行到数据返回的全链路优化是解决 80% 以上数据库性能问题的核心手段。1、SQL 优化基础1.1、SQL 执行的完整流程执行一条 SQL 需要经过 5 个阶段每个阶段都可能成为性能瓶颈SQL 输入 → 解析阶段 → 优化阶段 → 执行阶段 → 数据返回解析阶段检查 SQL 语法、语义和权限生成解析树硬解析第一次执行 SQL生成新的执行计划消耗大量 CPU 和内存软解析SQL 已在共享池中存在直接复用执行计划性能最优优化阶段优化器根据统计信息生成最优执行计划执行阶段按照执行计划访问数据数据返回将结果集返回给客户端核心原则SQL 优化的本质是减少数据访问量和减少数据处理量。1.2、硬解析 vs 软解析对比维度硬解析软解析执行计划生成重新生成直接复用CPU 消耗极高极低共享池锁竞争严重无适用场景第一次执行重复执行优化目标软解析率 99%。1.3、执行计划SQL 优化的核心执行计划是 Oracle 优化器生成的 SQL 执行步骤是 SQL 优化的唯一依据。查看执行计划的方法-- 方法1EXPLAIN PLAN查看预估执行计划 EXPLAIN PLAN FOR SELECT * FROM emp WHERE empno7369; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()); -- 方法2DBMS_XPLAN.DISPLAY_CURSOR查看真实执行计划最准确 SELECT /* GATHER_PLAN_STATISTICS */ * FROM emp WHERE empno7369; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMATALLSTATS LAST)); -- 方法3SQL*Plus AUTOTRACE SET AUTOTRACE ON; SELECT * FROM emp WHERE empno7369;执行计划关键信息解读Id执行步骤编号从 0 开始数字越小越先执行Operation执行操作如全表扫描、索引扫描、连接Name操作涉及的表或索引名称Rows优化器预估的返回行数基数Bytes预估的返回字节数Cost优化器计算的执行成本值越小越好Time预估的执行时间2、SQL 优化方法论2.1、第一步定位 Top SQL先从系统整体入手找出消耗资源最多的 SQL-- 从AWR中获取历史Top SQL按CPU消耗排序 SELECT sql_id, sql_text, cpu_time/1000000 AS cpu_sec, elapsed_time/1000000 AS elapsed_sec, executions, buffer_gets FROM dba_hist_sqlstat WHERE snap_id BETWEEN :start_snap AND :end_snap ORDER BY cpu_time DESC; -- 从ASH中获取当前正在执行的慢SQL SELECT sql_id, sql_text, session_id, wait_event, seconds_in_wait FROM v$active_session_history WHERE sample_time SYSDATE-1/24 AND session_stateON CPU ORDER BY seconds_in_wait DESC;2.2、第二步分析执行计划拿到 SQL 后第一步就是查看真实执行计划重点关注全表扫描TABLE ACCESS FULL大表全表扫描通常是性能问题的根源索引扫描类型优先使用 INDEX UNIQUE SCAN其次 INDEX RANGE SCAN避免 INDEX FULL SCAN连接方式嵌套循环适合小表驱动大表哈希连接适合大表连接基数预估如果预估行数和实际行数相差 10 倍以上说明统计信息不准确排序操作避免不必要的排序如 ORDER BY、GROUP BY、DISTINCT2.3、第三步实施优化措施根据执行计划分析结果选择合适的优化手段索引优化创建合适的索引删除无用索引SQL 改写优化 SQL 写法避免低效语法统计信息更新确保表和索引的统计信息准确参数调整调整优化器相关参数表结构优化分区表、表压缩、数据类型优化2.4、第四步验证优化效果优化后必须验证效果对比优化前后的性能指标执行时间CPU 消耗逻辑读buffer gets物理读physical reads执行计划是否符合预期实例某公司 ERP 慢查询优化SSituation - 情境某公司核心 ERP 系统的生产订单查询功能突然变慢原来 1 秒内返回的查询现在需要 30 秒以上导致生产线调度系统无法正常工作。TTask - 任务在 30 分钟内解决 SQL 性能问题恢复系统正常运行确保生产线不受影响。AAction - 行动1、定位 Top SQL使用 ASH 找到消耗 CPU 最多的 SQL2、分析执行计划查看执行计划发现SQL 正在对 production_orders 表5000 万行进行全表扫描成本高达 120 万。3、查找问题根源order_date 和 status 列没有复合索引表的统计信息已经 3 个月没有更新优化器错误地选择了全表扫描4、实施优化-- 1. 创建复合索引order_date在前status在后 CREATE INDEX idx_prod_orders_date_status ON production_orders(order_date, status) ONLINE; -- 2. 收集表的统计信息 EXEC DBMS_STATS.GATHER_TABLE_STATS(ERP, PRODUCTION_ORDERS, cascadeTRUE);5、验证效果再次执行 SQL执行时间从 30 秒缩短到 0.2 秒数据库 CPU 使用率下降到 30%。RResult - 结果SQL 执行时间从 30 秒缩短到 0.2 秒性能大幅提升数据库 CPU 使用率恢复正常生产线调度系统恢复正常运行没有影响生产进度落地改进建立 SQL 性能基线定期检查和更新统计信息。