LumantoBlog

Appearance

MySQL 第六章：索引与查询优化 ​

本章目标：理解索引的价值与代价，掌握常见索引类型、建索引策略、EXPLAIN 分析方法，并能落地 SQL 优化。

1. 为什么需要索引 ​

没有索引时，数据库需要全表扫描（逐行比对）；数据量一大，查询会明显变慢。

有索引后，数据库可通过有序结构快速定位目标数据，减少扫描行数。

graph LR
    A[SQL 查询请求] --> B{是否有可用索引}
    B -- 否 --> C[全表扫描]
    B -- 是 --> D[索引定位]
    C --> E[返回结果]
    D --> E

2. 索引基础概念 ​

2.1 什么是索引 ​

  • 索引是帮助数据库快速查找数据的数据结构
  • 本质是“空间换时间”
  • 常见底层结构：B+Tree（InnoDB 默认）

2.2 索引的收益与成本 ​

收益：

  • 提升 WHERE、JOIN、ORDER BY、GROUP BY 查询性能
  • 降低大表扫描带来的 IO 压力

成本：

  • 占用额外磁盘空间
  • INSERT / UPDATE / DELETE 时需要维护索引，写入变慢

3. 常见索引类型 ​

3.1 主键索引（PRIMARY KEY） ​

  • 每张 InnoDB 表必须有主键（建议有）
  • 主键索引通常也是聚簇索引（数据行与主键顺序组织）

3.2 唯一索引（UNIQUE） ​

  • 保证索引列值唯一，如手机号、邮箱、用户名

3.3 普通索引（INDEX） ​

  • 最常见，用于提升查询速度

3.4 联合索引（Composite Index） ​

  • 多列组合索引，如 (department, status, salary)
  • 遵循最左前缀原则

4. 建索引实战 ​

假设有员工表：

sql
CREATE TABLE employees (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  department VARCHAR(30) NOT NULL,
  status TINYINT NOT NULL DEFAULT 1,
  salary DECIMAL(10,2) NOT NULL,
  hire_date DATE NOT NULL
);

4.1 创建普通索引 ​

sql
CREATE INDEX idx_emp_department ON employees(department);

4.2 创建唯一索引 ​

sql
CREATE UNIQUE INDEX uk_emp_name ON employees(name);

4.3 创建联合索引 ​

sql
CREATE INDEX idx_emp_dept_status_salary
ON employees(department, status, salary);

4.4 查看和删除索引 ​

sql
SHOW INDEX FROM employees;

DROP INDEX idx_emp_department ON employees;

5. 最左前缀原则（重点） ​

联合索引 (a, b, c) 可命中的典型条件：

  • WHERE a = ?
  • WHERE a = ? AND b = ?
  • WHERE a = ? AND b = ? AND c = ?
  • WHERE a = ? AND c = ?（部分场景可部分利用）

较难利用索引的写法：

  • WHERE b = ? AND c = ?（缺少最左列 a）

6. EXPLAIN：看懂执行计划 ​

EXPLAIN 是分析 SQL 性能的入口。

sql
EXPLAIN
SELECT id, name, salary
FROM employees
WHERE department = '研发' AND status = 1
ORDER BY salary DESC;

重点关注列：

  • type：访问类型（越靠近 const/ref/range 越好，避免 ALL）
  • key：实际使用的索引
  • rows：预计扫描行数（越小越好）
  • Extra：是否 Using filesort、Using temporary

7. 高频优化策略 ​

7.1 避免 SELECT * ​

只查需要字段，减少回表和网络传输成本。

7.2 给高频过滤字段加索引 ​

如订单表常按 user_id、status、created_at 查询，就优先考虑这些字段。

7.3 索引列避免函数计算 ​

sql
-- 不推荐：可能导致索引失效
WHERE DATE(created_at) = '2026-04-21';

-- 推荐：范围查询
WHERE created_at >= '2026-04-21 00:00:00'
  AND created_at < '2026-04-22 00:00:00';

7.4 减少隐式类型转换 ​

字段是数字就传数字，字段是字符串就传字符串，避免索引失效。

7.5 合理使用分页 ​

大偏移量分页（LIMIT 100000, 20）代价高，可结合“覆盖索引 + 延迟关联”优化。

8. 典型优化案例 ​

8.1 慢查询 SQL ​

sql
SELECT *
FROM employees
WHERE department = '研发'
  AND status = 1
ORDER BY salary DESC;

问题：

  • SELECT * 字段过多
  • 若无合适索引，会出现大量扫描 + 排序

8.2 优化方案 ​

  1. 建联合索引
sql
CREATE INDEX idx_emp_dept_status_salary
ON employees(department, status, salary);
  1. 精简字段
sql
SELECT id, name, salary
FROM employees
WHERE department = '研发'
  AND status = 1
ORDER BY salary DESC;
  1. 使用 EXPLAIN 对比优化前后 rows 与 type

9. 面试高频点 ​

9.1 聚簇索引和二级索引区别 ​

  • 聚簇索引：数据行本身按主键组织（InnoDB）
  • 二级索引：叶子节点存主键值，查非覆盖字段可能回表

9.2 什么是回表 ​

先通过二级索引找到主键，再回到主键索引取完整行数据。

9.3 什么是覆盖索引 ​

查询字段全部在索引里，直接返回，不需要回表，性能更好。

9.4 索引是不是越多越好 ​

不是。索引过多会拖慢写入、占空间、增加维护复杂度。

10. 练习题 ​

  1. 给 orders(user_id, status, created_at) 设计一个合理联合索引。
  2. 使用 EXPLAIN 分析一条你自己的业务查询 SQL。
  3. 举例说明一个会导致索引失效的 SQL，并改写成可命中索引的版本。
  4. 说明 SELECT * 为什么在大表中通常不推荐。
  5. 解释“最左前缀原则”并给出 2 条能命中、1 条不能命中的示例。

11. 本章小结 ​

  • 索引是查询性能优化的第一抓手，但不是万能药。
  • 先定位慢 SQL，再看执行计划，再决定是否改 SQL 或加索引。
  • 优化的核心是：减少扫描、减少排序、减少回表、减少无效数据传输。