一、Explain工具：SQL优化的利器

1.1 什么是Explain？

Explain是MySQL提供的用于查看SQL执行计划的工具。通过在SELECT语句前添加EXPLAIN关键字，可以模拟优化器执行SQL语句，返回执行计划信息而不实际执行SQL。

-- 基础用法
EXPLAIN SELECT * FROM employees WHERE name = 'Lilei';

-- MySQL 8.0注意：extended已被废弃
-- MySQL 5.7可用：EXPLAIN EXTENDED SELECT ...
-- MySQL 8.0只需：EXPLAIN SELECT ...

1.2 Explain的两个变种（了解历史）

1. EXPLAIN EXTENDED（MySQL 8.0已废弃）

   • 额外提供查询优化信息

   • 通过SHOW WARNINGS查看优化后语句

2. EXPLAIN PARTITIONS

   • 显示查询将访问的分区（针对分区表）

---

二、Explain各列深度解析

2.1 id列：SELECT的执行顺序

• id相同：执行顺序从上到下

• id不同：id越大优先级越高，越先执行

• id为NULL：表示是结果集，最后执行

2.2 select_type列：查询类型

类型	说明	示例
SIMPLE	简单查询，不包含子查询或UNION	EXPLAIN SELECT * FROM t1
PRIMARY	复杂查询中最外层的SELECT	包含子查询的外层查询
SUBQUERY	包含在SELECT中的子查询	SELECT (SELECT ...) FROM ...
DERIVED	FROM子句中的子查询（派生表）	SELECT * FROM (SELECT ...) tmp
UNION	UNION中的第二个或后续SELECT	SELECT ... UNION SELECT ...
UNION RESULT	UNION的结果集	<union1,2>

2.3 table列：访问的表

• 显示正在访问的表名

• 如果是派生表，格式为<derivedN>，N是id

• 如果是UNION结果，格式为<union1,2>

2.4 type列：访问类型（关键指标）

性能从好到坏排序：
system > const > eq_ref > ref > range > index > ALL

各类型详解：

1. system：表只有一行记录（系统表）

2. const：通过主键或唯一索引一次就找到

   EXPLAIN SELECT * FROM film WHERE id = 1;

3. eq_ref：主键或唯一索引关联查询

   EXPLAIN SELECT * FROM film_actor 
   LEFT JOIN film ON film_actor.film_id = film.id;

4. ref：普通索引查询

   EXPLAIN SELECT * FROM film WHERE name = 'film1';

5. range：范围查询

   EXPLAIN SELECT * FROM actor WHERE id > 1;

6. index：全索引扫描

   EXPLAIN SELECT * FROM film;  -- 有索引覆盖

7. ALL：全表扫描（需要优化）

   EXPLAIN SELECT * FROM actor;

优化目标：至少要达到range级别，最好达到ref。

2.5 possible_keys列：可能用到的索引

显示查询可能使用的索引，但不一定实际使用。

2.6 key列：实际使用的索引

显示MySQL实际决定使用的索引。如果为NULL，表示未使用索引。

2.7 key_len列：索引使用的字节数

计算规则：

• 字符串类型：

  • char(n)：3n字节（utf8，存汉字）

  • varchar(n)：3n + 2字节

• 数值类型：

  • tinyint：1字节

  • int：4字节

  • bigint：8字节

• 时间类型：

  • date：3字节

  • timestamp：4字节

  • datetime：8字节

• 可为NULL的字段：额外+1字节

通过key_len判断使用了联合索引的哪些部分：

-- 联合索引 idx_film_actor_id(film_id, actor_id)
EXPLAIN SELECT * FROM film_actor WHERE film_id = 2;
-- key_len=4，说明只用了film_id列

2.8 ref列：索引的引用

显示索引的哪一列被使用了，常见值：

• const：常量值

• 列名：如film.id

2.9 rows列：预计扫描行数

MySQL估计需要读取的行数，不是结果集行数。

2.10 filtered列：过滤比例

表示存储引擎返回的数据在Server层过滤后，剩余多少满足查询条件的比例（百分比）。

2.11 Extra列：额外信息（重要）

值	说明	优化建议
Using index	使用覆盖索引	良好
Using where	使用WHERE过滤，查询列未被索引覆盖	考虑覆盖索引
Using index condition	使用索引下推（ICP）	良好
Using temporary	使用临时表	考虑用索引优化
Using filesort	使用文件排序	考虑用索引优化排序
Select tables optimized away	使用聚合函数优化	良好
Using join buffer	使用连接缓存	可适当增大join_buffer_size

---

三、索引最佳实践（避坑指南）

3.1 示例表结构

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`, `age`, `position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

3.2 全值匹配（最佳情况）

-- 使用联合索引的全部列
EXPLAIN SELECT * FROM employees 
WHERE name = 'Lilei' AND age = 22 AND position = 'manager';
-- type: ref, key_len: 140, Extra: Using index condition

3.3 最左前缀法则

规则：查询从索引的最左列开始，不跳过中间列。

-- ✅ 使用索引（最左列name）
EXPLAIN SELECT * FROM employees WHERE name = 'Bill' AND age = 31;

-- ❌ 不使用索引（缺少最左列name）
EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';
EXPLAIN SELECT * FROM employees WHERE position = 'manager';

3.4 不在索引列上做任何操作

索引失效场景：计算、函数、类型转换。

-- ✅ 使用索引
EXPLAIN SELECT * FROM employees WHERE name = 'Lilei';

-- ❌ 索引失效（使用函数）
EXPLAIN SELECT * FROM employees WHERE LEFT(name, 3) = 'Lilei';

-- ❌ 索引失效（使用日期函数）
EXPLAIN SELECT * FROM employees WHERE DATE(hire_time) = '2018-09-30';

-- ✅ 优化为范围查询（可能走索引）
EXPLAIN SELECT * FROM employees 
WHERE hire_time >= '2018-09-30 00:00:00' 
  AND hire_time <= '2018-09-30 23:59:59';

3.5 范围条件右边的列索引失效

-- ✅ 使用索引全部三列
EXPLAIN SELECT * FROM employees 
WHERE name = 'Lilei' AND age = 22 AND position = 'manager';

-- ❌ position索引失效（age用了范围查询）
EXPLAIN SELECT * FROM employees 
WHERE name = 'Lilei' AND age > 22 AND position = 'manager';
-- 实际只用到name和age两列

3.6 尽量使用覆盖索引

-- ✅ 覆盖索引，Extra: Using index
EXPLAIN SELECT name, age FROM employees 
WHERE name = 'Lilei' AND age = 23 AND position = 'manager';

-- ❌ 需要回表，Extra: Using index condition
EXPLAIN SELECT * FROM employees 
WHERE name = 'Lilei' AND age = 23 AND position = 'manager';

3.7 不等于/not in/not exists导致全表扫描

-- ❌ 通常不走索引（数据量小时可能走）
EXPLAIN SELECT * FROM employees WHERE name != 'Lilei';
EXPLAIN SELECT * FROM employees WHERE name NOT IN ('Lilei', 'HanMeimei');

3.8 is null/is not null可能不走索引

-- 通常不走索引，除非数据分布特殊
EXPLAIN SELECT * FROM employees WHERE name IS NULL;

3.9 like通配符开头索引失效

-- ❌ 通配符开头，索引失效
EXPLAIN SELECT * FROM employees WHERE name LIKE '%lei';

-- ✅ 通配符在结尾，使用索引
EXPLAIN SELECT * FROM employees WHERE name LIKE 'lei%';

-- ✅ 使用覆盖索引解决'%xxx%'问题
EXPLAIN SELECT name, age, position FROM employees WHERE name LIKE '%lei%';
-- Extra: Using where; Using index

3.10 字符串不加单引号索引失效

-- ✅ 使用索引
EXPLAIN SELECT * FROM employees WHERE name = '1000';

-- ❌ 类型转换，索引失效
EXPLAIN SELECT * FROM employees WHERE name = 1000;

3.11 少用or/in，优化器可能不选择索引

-- ❌ 可能不走索引
EXPLAIN SELECT * FROM employees 
WHERE name = 'Lilei' OR name = 'HanMeimei';

3.12 范围查询优化

-- 添加单值索引
ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE;

-- 大范围可能不走索引
EXPLAIN SELECT * FROM employees WHERE age >= 1 AND age <= 2000;
-- type: ALL（全表扫描）

-- 拆分为小范围
EXPLAIN SELECT * FROM employees WHERE age >= 1 AND age <= 1000;
EXPLAIN SELECT * FROM employees WHERE age >= 1001 AND age <= 2000;
-- type: range（使用索引）

---

四、MySQL 8.0的特别注意事项

4.1 EXPLAIN EXTENDED已废弃

MySQL 8.0已移除EXPLAIN EXTENDED命令，直接使用EXPLAIN即可。

4.2 部分查询行为变化

测试发现，某些在MySQL 5.7中索引失效的场景，在MySQL 8.0中可能优化器会选择使用索引：

-- MySQL 5.7：可能不走索引
-- MySQL 8.0：优化器可能选择走索引
EXPLAIN SELECT * FROM employees WHERE name != 'Lilei';
EXPLAIN SELECT * FROM employees WHERE name = 'Lilei' OR name = 'HanMeimei';

建议：实际测试验证，不要完全依赖经验。

---

五、索引使用总结表（联合索引idx_a_b_c）

WHERE条件	索引使用情况	说明
a = 3	✅ 用到a	最左匹配
a = 3 AND b = 5	✅ 用到a,b	最左匹配，不跳过
a = 3 AND b = 5 AND c = 4	✅ 用到a,b,c	全值匹配
b = 3 或 b = 3 AND c = 4 或 c = 4	❌ 未用索引	缺少最左列
a = 3 AND c = 5	✅ 只用到a	b中间断了
a = 3 AND b > 4 AND c = 5	✅ 用到a,b	c不能用在范围之后
a = 3 AND b LIKE 'kk%' AND c = 4	✅ 用到a,b,c	LIKE 'kk%'相当于范围
a = 3 AND b LIKE '%kk' AND c = 4	✅ 只用到a	LIKE '%kk'索引失效
a = 3 AND b LIKE '%kk%' AND c = 4	✅ 只用到a	LIKE '%kk%'索引失效
a = 3 AND b LIKE 'k%kk%' AND c = 4	✅ 用到a,b,c	LIKE 'k%kk%'相当于范围

---

六、Java开发者实战建议

6.1 代码层面

1. 避免在循环中执行SQL：使用批量操作

2. 合理使用连接池：如HikariCP，配置合适参数

3. 监控慢查询：定期分析慢查询日志

6.2 SQL编写

// ❌ 避免
String sql = "SELECT * FROM users WHERE DATE(create_time) = ?";

// ✅ 推荐
String sql = "SELECT * FROM users WHERE create_time >= ? AND create_time < ?";

6.3 工具使用

1. 开发阶段：对复杂SQL执行EXPLAIN分析

2. 测试阶段：使用真实数据量测试SQL性能

3. 上线前：进行压力测试，验证索引有效性

---

七、总结

Explain是MySQL SQL优化的核心工具，理解每一列的含义是写出高效SQL的基础。索引优化需要结合业务场景，遵循最佳实践，但也要注意MySQL版本差异带来的行为变化。

记住几个关键点：

1. 最左前缀原则是联合索引的基础

2. 避免在索引列上做计算、函数、类型转换

3. 尽量使用覆盖索引，减少SELECT *

4. 范围查询注意右边的列索引失效

5. 定期分析慢查询，持续优化