SQL调优与数据建模 面试备战

从最优到最差:

system → const → eq_ref → ref → range → index → ALL

• system/const: 表中只有一行或通过主键/唯一索引精确匹配一行
• eq_ref: JOIN时通过主键/唯一索引匹配, 每行最多匹配一条
• ref: 普通索引等值匹配, 可能多条
• range: 索引范围扫描 (BETWEEN, >, <, IN)
• index: 全索引扫描 (比ALL好, 但还是扫全部)
• ALL: 全表扫描, 必须优化!

目标: 至少达到 range 级别, 最好 ref 及以上。

覆盖索引: 一个索引包含了查询所需的所有列, 因此不需要回表(不需要去聚簇索引取完整行)。

为什么快: 二级索引通常比聚簇索引小得多(一个页能存更多记录), 且省去了回表的随机IO。EXPLAIN 中 Extra 会显示 Using index。

-- INDEX(name, age) 覆盖了查询
EXPLAIN SELECT name, age FROM users WHERE name = '张三';
-- Extra: Using index ← 不回表!

复合索引 INDEX(a, b, c) 实际创建了 (a), (a,b), (a,b,c) 三种索引能力。查询必须从最左列开始匹配, 不能跳过中间列。

• WHERE a=1 AND b=2 AND c=3 → 命中全部三列 ✓
• WHERE a=1 AND b=2 → 命中 a, b ✓
• WHERE a=1 AND c=3 → 只命中 a (c无法用, 跳过了b) △
• WHERE b=2 AND c=3 → 完全不命中 ✗

Excel类比: 像三级排序(省→市→区), 跳过省直接按市排就没有意义。另外, 范围查询后的列也无法使用索引。

1. 对索引列使用函数: WHERE YEAR(date)=2025 → 改为范围查询
2. 对索引列做运算: WHERE age+1=30 → 改为 WHERE age=29
3. 隐式类型转换: VARCHAR列用数字查 WHERE phone=1380000 → 加引号
4. LIKE '%xxx': 以通配符开头 → 改为 'xxx%' 或用全文索引
5. OR连接无索引列: WHERE a=1 OR b=2 (b无索引) → 给b也加索引或用UNION

其他还有: !=, NOT IN, IS NOT NULL, ORDER BY方向不一致, 优化器判断全表更快等。

• COUNT(*): 统计总行数, 包含NULL值, InnoDB会选择最小的索引扫描
• COUNT(1): 与 COUNT(*) 完全等价, 没有区别
• COUNT(列名): 统计该列非NULL的行数, 需要逐行判断, 较慢

结论: 优先用 COUNT(*), 它是标准写法且语义最清晰。

索引下推 (Index Condition Pushdown, MySQL 5.6+) 是将 WHERE 中可以用索引过滤但无法完全走索引的条件, 下推到存储引擎层提前执行, 减少回表次数。

例子: INDEX(name, age), 查询 WHERE name LIKE '张%' AND age > 30

• 无ICP: 先用 name LIKE '张%' 找到所有姓张的, 全部回表, 再在Server层过滤 age
• 有ICP: 在索引中直接判断 age, 只回表满足条件的行

EXPLAIN 标志: Extra 中出现 Using index condition。

MySQL的 LIMIT offset, n 会先扫描 offset + n 行, 再丢掉前 offset 行。offset很大时绝大部分扫描都是浪费。

优化方案:

1. 延迟关联 (Deferred Join): 子查询用覆盖索引取ID, 外层只回表少量行
2. 游标法: WHERE id > last_id ORDER BY id LIMIT 10, 完美利用索引, 不需要OFFSET
3. BETWEEN: 如果ID连续, 直接 WHERE id BETWEEN ... AND ...

-- 延迟关联
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000,10) t ON o.id=t.id;

Using filesort 表示 MySQL 无法利用索引来完成排序, 需要在内存(或磁盘)中额外做排序操作。这是一个性能警告。

消除方法:

• 确保 ORDER BY 的列有索引
• 如果是 WHERE a=1 ORDER BY b, 创建复合索引 (a, b)
• ORDER BY 的列顺序必须与索引顺序一致, 不能反
• 如果是多列排序, 方向必须一致 (都ASC或都DESC, MySQL 8.0+支持降序索引)

回表: 通过二级索引找到主键值后, 再用主键去聚簇索引查找完整行数据的过程。涉及两次索引查找, 其中聚簇索引查找通常是随机IO。

为什么避免: 回表意味着每条匹配记录都需要一次随机IO, 当匹配行数很多时(比如上万行), 性能急剧下降。

避免方式: 使用覆盖索引, 让查询所需的所有列都在二级索引中, EXPLAIN 显示 Using index。

MySQL优化器基于统计信息(索引基数 cardinality)来估算不同执行计划的成本, 选择成本最低的。但统计信息可能不准确:

• 数据分布不均匀 (比如 status 90%都是 'ACTIVE')
• 统计信息未及时更新
• 对于复杂查询, 优化器的成本估算可能偏差较大

处理方式:

1. ANALYZE TABLE 更新统计信息
2. FORCE INDEX(索引名) 强制使用指定索引(慎用)
3. USE INDEX(索引名) 建议使用(优化器仍可覆盖)
4. 调整 SQL 写法让优化器更容易做正确选择

• UNION: 合并结果集并去重, 相当于加了 DISTINCT, 需要临时表排序
• UNION ALL: 合并结果集不去重, 直接拼接, 性能更好

最佳实践: 除非确定需要去重, 否则优先用 UNION ALL。UNION 的去重操作消耗大量资源。

在 MySQL 中, JOIN 通常比子查询快, 尤其是:

• DEPENDENT SUBQUERY: 相关子查询会对外层每一行都执行一次子查询, 极慢 → 改写为JOIN
• IN 子查询: MySQL 5.6+ 优化为 semi-join, 但有时仍不如直接 JOIN

-- 慢: 相关子查询
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip=1);
-- 快: JOIN
SELECT o.* FROM orders o JOIN users u ON o.user_id=u.id WHERE u.vip=1;

星型模型: 事实表居中, 维度表直接连接, 维度表不规范化 (有冗余)。JOIN少, 查询快。

雪花模型: 维度表继续拆分成子维度表, 完全规范化。节省空间, 但JOIN多, 查询复杂。

Excel类比: 星型=大表直连下拉菜单表; 雪花=下拉菜单表还有自己的下拉菜单。

选择建议: 大多数OLAP场景选星型模型, 查询性能优先。存储特别敏感且维度层级复杂时考虑雪花模型。

事实表 (Fact Table):

• 存储业务事件/交易的度量数据 (订单、产量、停机记录)
• 包含外键(关联维度表) + 数值度量(金额、数量、时长)
• 增长极快, 通常百万到数十亿行

维度表 (Dimension Table):

• 存储描述性属性 (产品信息、员工信息、时间信息)
• 包含主键 + 文本描述字段
• 增长缓慢, 通常几百到几万行

Excel类比: 事实表=每天不断追加的记录行; 维度表=固定不变的下拉选项列表。

SCD Type 2 通过新增行来保留维度属性的完整变更历史。每条记录需要:

• 代理键 (Surrogate Key): 自增主键, 唯一标识每个版本
• 自然键 (Natural Key): 原始业务ID (如员工编号)
• 有效期: start_date / end_date 或 is_current 标记

当属性变更时, 将旧记录的 end_date 设为当前日期, is_current 设为 N, 然后插入新记录。事实表通过代理键关联, 确保历史事实关联的是当时有效的维度值。

当 ORDER BY 的列顺序与索引列顺序完全一致时, MySQL 可以利用索引的有序性直接返回排序结果, 不需要额外排序。

• INDEX(a, b) + ORDER BY a, b → Using index ✓
• INDEX(a, b) + WHERE a=1 ORDER BY b → Using index ✓ (a确定后b有序)
• INDEX(a, b) + ORDER BY b, a → Using filesort ✗ (顺序反了)
• INDEX(a, b) + WHERE a > 1 ORDER BY b → Using filesort ✗ (a是范围, b不再有序)

聚簇索引 (Clustered Index):

• InnoDB中主键索引就是聚簇索引, 叶子节点存储完整行数据
• 一张表只有一个聚簇索引
• 数据物理上按主键顺序存储

非聚簇索引 (Secondary Index / 二级索引):

• 叶子节点存储主键值而非完整行
• 查询非索引列时需要回表 (用主键去聚簇索引取数据)
• 可以有多个二级索引

Excel类比: 聚簇索引=Excel按行号排列的完整数据表; 二级索引=按姓名排序的索引卡, 卡上只有姓名和行号。

rows 是优化器的估算值, 基于统计信息, 可能不准确:

• 实际数据分布可能比统计信息更集中 (选择性更好)
• 使用了 LIMIT, 可能在扫描很多行之前就满足了
• 统计信息过期, ANALYZE TABLE 后可能更新
• 覆盖索引场景下虽然 rows 大但全是内存操作, 无随机IO

Using temporary 表示 MySQL 创建了临时表来处理 GROUP BY。通常发生在:

• GROUP BY 的列没有索引
• GROUP BY 的列与索引顺序不一致
• GROUP BY 和 ORDER BY 的列不同

优化方式:

1. 给 GROUP BY 的列添加索引
2. 如果不需要排序, 加 ORDER BY NULL 避免隐式排序
3. 调大 tmp_table_size 和 max_heap_table_size 让临时表在内存中
4. 考虑使用松散索引扫描 (Loose Index Scan)

事实表 fact_orders:

• order_id (代理键), date_id (FK→dim_date), customer_id (FK→dim_customer), product_id (FK→dim_product)
• 度量: quantity, amount, discount_amount

维度表:

• dim_date: date_id, full_date, year, quarter, month, weekday
• dim_customer: customer_id, name, city, level, industry
• dim_product: product_id, name, category, brand, unit_price

设计原则: 事实表只存ID和度量, 描述信息由维度表提供; 维度表做适度冗余(星型模型)减少JOIN。

核心问题是 OLTP 和 OLAP 共享同一个数据库实例, 复杂查询占用了大量资源。

1. 读写分离: MySQL主库写, 从库读, 减轻主库压力
2. OLTP/OLAP分离: 引入 ClickHouse/Doris 等OLAP引擎专门跑报表
3. 数据同步: 通过 Canal/Debezium 监听 binlog 准实时同步到OLAP系统
4. 报表改道: 所有报表查询指向 OLAP 系统
5. 报表缓存: 高频报表结果缓存到 Redis, 定时刷新

本质: 业务系统和分析系统物理分离, 各自使用合适的数据库引擎。

判断步骤:

1. EXPLAIN 查看 type 是否为 ALL 或 index
2. 检查 key 是否为 NULL
3. 看 rows 是否远大于预期
4. 检查 Extra 是否有 Using filesort / Using temporary

加索引原则:

1. WHERE 条件中的等值列 → 放在索引前面
2. ORDER BY / GROUP BY 的列 → 接在 WHERE 条件后面
3. 范围查询 (> , BETWEEN, LIKE) → 放在索引最后
4. SELECT 的列 → 考虑用覆盖索引避免回表
5. 优先创建复合索引而非多个单列索引 (遵循最左前缀)

ETL = Extract(抽取) + Transform(转换) + Load(加载)

• Extract: 从MySQL、日志文件、API等数据源读取原始数据
• Transform: 数据清洗(去重、格式化、空值处理)、维度关联、SCD处理、业务计算
• Load: 将处理好的数据写入数据仓库(ClickHouse/Doris/Hive)

为什么需要: 业务系统(OLTP)的数据格式和结构不适合直接做分析。ETL将数据从"事务友好"转换为"分析友好"的形态。

系统化优化步骤:

1. EXPLAIN 分析: 查看 type, key, rows, Extra, 定位瓶颈
2. 检查表结构: 表有多少行? 哪些列有索引? 数据分布如何?
3. 加索引: 根据WHERE/ORDER BY/GROUP BY创建合适的复合索引
4. 改写SQL: 子查询→JOIN, UNION→UNION ALL, 深翻页→延迟关联
5. 利用覆盖索引: 减少回表次数
6. 检查索引是否生效: 隐式转换? 函数操作? 类型不匹配?
7. 业务层面: 真的需要查这么多数据? 能否缓存? 能否预计算?
8. 架构层面: 数据量是否到了需要分库分表/引入OLAP的程度?

核心思路: 先定位(慢查询日志+EXPLAIN) → 再优化(索引+SQL改写) → 最后考虑架构升级。