Mysql高级篇（中）—— 索引优化

一、索引分析案例

使用 EXPLAIN 分析 SQL 查询性能是数据库优化的重要环节。EXPLAIN 能展示查询的执行计划，帮助我们找出潜在的性能瓶颈。我们可以从 EXPLAIN 输出中的多个列（如 type、rows、Extra、key 等）来分析查询的执行情况， 常见的优化手段包括 创建单列或复合索引、避免全表扫描、减少排序操作以及充分利用数据库索引覆盖 等。以下我将详细分析单表、两表、三表查询案例，给出性能分析和优化建议。

案例 1：单表查询

场景描述

我们有一个 employees 表，包含员工的姓名、年龄、部门等信息，结构如下：

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department_id INT,
    hire_date DATE,
    salary DECIMAL(10, 2),
    INDEX (department_id),
    INDEX (age)
);

查询要求：查找年龄在 30 岁以上的某部门的所有员工。

SELECT * FROM employees WHERE department_id = 1 AND age > 30;

使用 EXPLAIN 分析该查询：

EXPLAIN SELECT * FROM employees WHERE department_id = 1 AND age > 30;

案例 2：两表连接查询

场景描述

我们有两个表：employees 和 departments。查询要求是找到所有在 "HR" 部门的员工，并按他们的薪水降序排列。

SELECT e.name, e.salary FROM employees e 
JOIN departments d ON e.department_id = d.id 
WHERE d.name = 'HR' ORDER BY e.salary DESC;

使用 EXPLAIN 分析该查询：

EXPLAIN SELECT e.name, e.salary FROM employees e 
JOIN departments d ON e.department_id = d.id 
WHERE d.name = 'HR' ORDER BY e.salary DESC;

案例 3：三表连接查询

场景描述

假设我们有三个表：employees、department 和 projects，查询要求是查找所有参与某个项目的员工姓名、薪水和所属部门。

SELECT e.name, e.salary, d.name FROM employees e 
JOIN departments d ON e.department_id = d.id 
JOIN projects p ON e.id = p.employee_id 
WHERE p.project_name = 'Project A';

使用 EXPLAIN 分析该查询：

EXPLAIN SELECT e.name, e.salary, d.name FROM employees e 
JOIN departments d ON e.department_id = d.id 
JOIN projects p ON e.id = p.employee_id 
WHERE p.project_name = 'Project A';

二、避免索引失效

索引失效 问题是指数据库中的查询操作无法正确使用已有的索引，导致查询效率大幅下降。这通常发生在 SQL 语句的编写方式、表结构的设计或数据的变化未能与索引策略相匹配时。以下是几种常见的索引失效原因及避免方法，结合案例来说明。

常见索引失效场景简述

序号	索引失效场景
1	索引列上进行（计算、函数、类型转换）等操作
2	使用 !=、<> 或者 NOT IN等操作
3	is null，is not null也无法使用索引
4	OR 条件
5	like以通配符开头(‘%abc…’)的操作
6	使用复合索引时 没有遵循最佳左前缀法则

上述场景示例解析:

场景 1

当查询条件 使用了函数或表达式 时，索引会失效。 例如，对于表 users，如果在 name 字段上有索引，但执行以下查询：

SELECT * FROM users WHERE UPPER(name) = 'JOHN';

由于 UPPER(name) 使用了函数，MySQL 无法利用 name 字段上的索引。要避免这种情况，可以在插入或更新数据时将数据规范化为大写或小写，或者改用不依赖函数的查询方式：

SELECT * FROM users WHERE name = 'john';  -- 假设所有 name 都存储为小写

---

当 字段和查询条件的类型不一致 时，数据库会进行 隐式类型转换 ，这也会导致索引失效。例如，id 字段是整数类型，但查询时传入的是字符串：

SELECT * FROM users WHERE id = '123';  -- id 为 INT

因为 id 字段是整数类型，数据库会尝试将 '123' 转换为数字，导致索引无法使用。正确做法是确保数据类型一致：

SELECT * FROM users WHERE id = 123;

---

---

场景 2

在使用 不等操作符 (!=、<>) 或者 NOT IN 时，索引通常不会生效。例如，以下查询可能无法使用索引：

SELECT * FROM users WHERE age != 30;

要避免这种情况，可以改用其他逻辑，如将查询拆分为多个条件（在特定情况下适用）：

SELECT * FROM users WHERE age < 30 OR age > 30;

---

---

场景 3

---

---

场景 4

---

---

场景 5

---

---

场景 6

最左前缀法则 是数据库中针对 复合索引 使用的一个原则。它指的是：在使用复合索引时，查询条件必须按照 索引中从左到右的顺序并且不跳过索引中间的列 使用，从第一个开始，逐步向右，才能有效利用索引。

三、索引优化

文字版

示例版