Skip to content
高进波的博客

收获，不止SQL优化——抓住SQL的本质

《收获，不止SQL优化——抓住SQL的本质》读书笔记

第1章 全局在胸 - 用工具对SQL整体优化

从整体：

  • 关注点：
    • 外界环境影响
    • 执行频率、次数
    • 针对系列SQL的调优
  • 常见问题：
    • 存储IO故障
    • 系统网络慢
    • 主机资源繁忙
  • 工具：
    • AWR (Automatic Workload Repository)
    • ASH (Active Session History)
    • ADDM (Automatic Database Diagnostic Monitor)
    • AWRDD (AWR Delta Report)

到局部：

  • 关注点：
    • 单次执行情况
    • 针对某条SQL的调优
  • 常见问题：
    • 仅某个菜单查询慢
    • 仅某个SQL执行慢
  • 工具：
    • EXPLAIN
    • SET AUTOTRACE ON
    • AWRRPT.SQL

第2章 风驰电擎 - 有效缩短SQL优化过程

分析操作难度：

  • 关注点：
    • SQL返回记录数
    • SQL执行时长
    • SQL执行次数
    • 机器配置高低

第3章 循规蹈矩 - 如何读懂SQL执行计划

MySQL 收集统计信息：

  1. 手动收集：

    • 执行 ANALYZE TABLE。
    • 适用于 InnoDB 和 MyISAM 存储引擎。
    • 注意：对于大表，操作可能影响性能。

  2. 自动触发：

    • 第一次打开表时。
    • 表修改行数超过 1/6 或 20 亿条时。
    • 插入新记录时。
    • 执行 SHOW INDEX、SHOW TABLE STATUS 或查询 information_schema 相关表时。

  3. 开启参数 innodb_stats_on_metadata：

    • 访问以下表时会触发统计信息收集：
      • information_schema.TABLES
      • information_schema.STATISTICS
      • information_schema.PARTITIONS
      • information_schema.KEY_COLUMN_USAGE
      • information_schema.TABLE_CONSTRAINTS
      • information_schema.REFERENTIAL_CONSTRAINTS

参数说明：

  • innodb_stats_sample_pages：每次收集统计信息的采样页数，默认为 20。
  • innodb_stats_persistent：默认开启，将统计信息保存到磁盘，避免动态更新，节省资源。

查询表的统计信息：

SELECT TABLE_SCHEMA, table_name, column_name, CARDINALITY
FROM information_schema.STATISTICS
WHERE table_name = 'salaries';


SHOW INDEX FROM employees.salaries;


SELECT table_rows
FROM information_schema.tables
WHERE table_name = 'salaries';


SELECT COUNT(1) FROM employees.salaries;


ANALYZE TABLE employees.salaries;


SELECT table_rows
FROM information_schema.tables
WHERE table_name = 'salaries';


SELECT TABLE_SCHEMA, table_name, column_name, CARDINALITY
FROM information_schema.STATISTICS
WHERE table_name = 'salaries';


SHOW INDEX FROM employees.salaries;

读懂执行计划：

1) 单独型：

  • 特征图：1→2→3，访问顺序为 3, 2, 1（从远到近）。
  • 示例： 
    SELECT deptno, count(*)
    FROM emp
    WHERE job = 'CLERK' AND sal < 3000
    GROUP BY deptno;

2) 联合型：

  • 非关联：

    • 特征图：1→2，访问顺序为 2, 3, 4, 1（从上到下，无关联性）。
    • 示例： 
      SELECT ename FROM emp
      UNION ALL
      SELECT dname FROM dept
      UNION ALL
      SELECT '%' FROM dual;

  • 关联（NL）：

    • 特征图：1→2，访问顺序为 2, 4, 3, 1（从上到下，从远到近，有关联性）。
    • 示例： 
      SELECT /*+ ordered use_nl(dept) index(dept) */ *
      FROM emp, dept
      WHERE emp.deptno = dept.deptno AND emp.comm IS NULL AND dept.dname != 'SALES';

第4章 运筹帷幄 - 左右SQL执行计划妙招

（待补充）

第5章 且慢，感受体系结构让SQL飞

（待补充）

第6章 且慢，体验逻辑结构让SQL飞

（待补充）

第7章 且慢，探寻表的设计让SQL飞

  • 核心观点：
    • 表设计应结合业务需求。
    • 站在业务层面思考表设计，才能达到最优效果。

第8章 且慢，学习索引如何让SQL飞

组合索引最佳顺序：

  • 等值查询列置前。

改造写法示例：

1) 同时取最大值和最小值：

  • 前： 
    SELECT MAX(object_id), MIN(object_id) FROM t;
  • 后： 
    SELECT MAX(max), MIN(min)
    FROM (
      SELECT MAX(object_id) max FROM t
    ) a,
    (
      SELECT MIN(object_id) min FROM t
    ) b;

2) 组合索引优化：

  • 前： 
    SELECT *
    FROM t
    WHERE object_TYPE = 'TABLE' AND OBJECT_ID >= 20 AND OBJECT_ID <= 23;
  • 后： 
    SELECT *
    FROM t
    WHERE object_TYPE = 'TABLE' AND OBJECT_ID IN (20, 21, 22, 23);

3) 增加检索条件：

  • 前： 
    SELECT *
    FROM t
    WHERE object_type = 'VIEW' AND OWNER = 'LJB';
  • 后： 
    SELECT *
    FROM t
    WHERE object_type = 'VIEW' AND OBJECT_ID IN (20, 21, 22) AND OWNER = 'LJB';

第9章 且慢，弄清索引之阻碍让SQL飞

索引的开销：

  • 热块竞争
  • 回表开销
  • 更新开销
  • 建立开销

索引的负面影响：

  • 记录越多，插入越慢。
  • 建索引会产生全表锁和全表排序。

索引优化建议：

  • 避免表交叉重复建立索引。
  • 删除未使用的索引。
  • 组合列过多的索引需谨慎。
  • 避免对索引列进行计算。

第10章 且慢，其他索引应用让SQL飞

1. 位图索引：

  • 适用场景：
    • 列重复度极高。
    • 即席查询场景。
    • 使用 COUNT(*) 统计条数时高效。
  • 示例： 
    CREATE BITMAP INDEX idx_bitm_t_status ON t(status);
    SELECT COUNT(*) FROM t;
  • 不适用场景：
    • 列频繁更新（可能导致死锁）。
    • 列重复度低。

2. 函数索引：

  • 妙用：
    • 减少递归调用。
  • 示例： 
    CREATE INDEX idx_func_id ON t1(get_obj_name(id));
    SELECT * FROM t1 WHERE get_obj_name(id) = 'TEST';

第11章 且慢，表连接的秘密让SQL飞

（待补充）

第12章 动手，经典等价改写让SQL飞

  • 优化本质：
    • 减少访问路径。
    • 只取所需列，避免回表。
    • 避免递归调用和 SQL 函数调用。
    • 避免使用触发器。

第13章 动手，过程函数优化让SQL飞

（待补充）

第14章 动手，高级写法应用让SQL飞

（待补充）

第15章 动手，分析函数让SQL飞

  • 作用：
    • 简化开发工作。
    • 实现特殊需求。
  • 示例： 
    SELECT dept_id, SUM(sal)
    FROM emp
    GROUP BY dept_id
    ORDER BY dept_id;

第16章 动手，把握需求改写让SQL飞

（待补充）

第17章 总结与延伸：从勿信讹传到洞若观火

SQL优化误区：

  1. COUNT(列) vs COUNT(*)：

    • 无索引时，两者性能相当。
    • 有索引且索引列非空时，COUNT(*) 更快。

  2. 表连接顺序：

    • 基于代价优化，而非基于规则。

总结：SQL 优化需要结合工具、执行计划、索引设计、表结构设计等多方面知识，同时避免常见误区，才能真正实现高效优化。