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
最左前缀法则是数据库中针对复合索引使用的一个原则。它指的是：在使用复合索引时，查询条件必须按照索引中从左到右的顺序并且不跳过索引中间的列使用，从第一个开始，逐步向右，才能有效利用索引。
三、索引优化
文字版
示例版
