MySQL 8.0 SQL 层面性能优化（超详细、纯干货、可直接落地）

最新推荐文章于 2026-06-24 16:12:54 发布

原创最新推荐文章于 2026-06-24 16:12:54 发布 · 563 阅读

6 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#mysql #sql #性能优化

这是专门针对 MySQL 8.0、只聚焦 SQL 优化的系统性深度讲解，覆盖索引、SQL 编写、事务、函数、锁、执行计划等所有核心论点，全部是生产环境高频踩坑点 + 最优解决方案。

我会用原理 + 反例 + 正例 + 8.0 新特性的方式讲透，让你能直接用于业务优化。

一、SQL 优化的核心本质（必须先理解）

MySQL 8.0 SQL 优化只有一句话： 让数据库尽量走最优索引，尽量减少扫描行数，尽量避免回表、排序、临时表。

SQL 慢的 99% 原因：

没索引
索引失效
扫描行数太多
做了无谓的排序 / 分组 / 联表
事务 / 锁导致阻塞

二、MySQL 8.0 SQL 优化 10 大核心论点（逐点精讲）

论点 1：严禁使用 `SELECT *`，必须显式查询字段（最高频优化）

原理

SELECT * 会读取所有字段，强制回表，无法使用覆盖索引，大幅增加 IO、网络、内存消耗。 MySQL 8.0 优化器对 * 没有特殊优化。

反例（性能极差）

sql

SELECT * FROM user WHERE mobile = '13800138000';

正例（性能提升 5~50 倍）

sql

SELECT id,username,mobile FROM user WHERE mobile = '13800138000';

关键收益

命中覆盖索引，无需回表
减少数据传输
避免大字段拖慢查询

论点 2：索引列上禁止使用函数、运算、隐式转换（索引失效第一大坑）

原理

一旦对索引字段做运算 / 函数，MySQL 8.0 无法使用 B+ 树索引，直接触发全表扫描。

典型失效场景

字段类型不匹配（字符串传数字）
索引列使用函数：YEAR(create_time)、DATE()
索引列运算：age + 1 = 18

反例

sql

SELECT id FROM order WHERE YEAR(create_time) = 2025;

正例

sql

SELECT id FROM order WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01';

MySQL 8.0 新特性：函数索引（解决函数导致索引失效）

sql

CREATE INDEX idx_year_create ON order ( (YEAR(create_time)) );

这是 8.0 独有的超强优化。

论点 3：严格遵守联合索引「最左前缀原则」（80% 索引优化靠它）

原理

联合索引 (a,b,c) 等效：

a
a,b
a,b,c

跳过前面的字段直接查后面 → 索引失效。

反例

sql

SELECT * FROM table WHERE b=1 AND c=2;  -- 不走索引

正例

sql

SELECT * FROM table WHERE a=1 AND b=1 AND c=2; -- 全索引匹配

最佳实践

等值在前，范围在后

plaintext

where a=? and b=? and c>?
索引 (a,b,c)

论点 4：`OR` 必须全部带索引，否则整句索引失效

原理

OR 只要有一个字段无索引 → 全表扫描。

反例

sql

SELECT * FROM user WHERE name = '张三' OR age = 20;
-- name 有索引，age 无索引 → 整句失效

优化方案

给 OR 所有字段建索引
改用 UNION ALL（性能更高）

sql

SELECT * FROM user WHERE name='张三'
UNION ALL
SELECT * FROM user WHERE age=20;

论点 5：`!= / <> / NOT IN / IS NOT NULL` 会导致索引失效（高频坑）

原理

MySQL 8.0 优化器认为这些操作无法高效走索引，会直接全表扫描。

反例

sql

SELECT * FROM user WHERE status != 1;

解决方案

改用范围查询
业务上用枚举代替（0/1/2）
用覆盖索引绕过

论点 6：深度分页 `LIMIT 1000000,20` 必须优化（超级慢 SQL）

原理

MySQL 会先扫描 1000020 条数据，再丢弃前 1000000 条，性能灾难。

优化方案：主键延迟关联

sql

SELECT t.* FROM user t
JOIN (SELECT id FROM user ORDER BY id LIMIT 1000000,20) AS tmp
ON t.id = tmp.id;

性能提升 10~100 倍。

论点 7：`JOIN` 联表最多 3 张，且关联字段必须同类型、有索引

原理

JOIN 无索引 → 笛卡尔积，性能爆炸
字段类型不一致 → 隐式转换 → 索引失效

反例

sql

SELECT * FROM order o
JOIN user u ON o.user_id = u.id;
-- user_id 无索引 → 慢查询

正例

sql

-- order 表建立 index(user_id)
SELECT o.order_no,u.username FROM order o
JOIN user u ON o.user_id = u.id;

强制规范

大表禁止 JOIN，小表驱动大表

论点 8：`GROUP BY / ORDER BY` 字段必须在索引里，避免 filesort

原理

排序字段无索引 → filesort（文件排序），速度极慢。

优化原则

GROUP BY、ORDER BY 字段建立联合索引
排序字段数量尽量 ≤ 3 个
禁止跨表排序

正例

sql

-- index (user_id, create_time)
SELECT * FROM order
WHERE user_id = 100
ORDER BY create_time DESC;

论点 9：事务必须小、快、短，禁止长事务（MySQL 8.0 锁优化关键）

长事务危害

锁等待严重
MVCC 版本堆积
undo log 膨胀
导致 CPU / IO 飙升

正确写法

sql

START TRANSACTION;
UPDATE user SET balance=balance-10 WHERE id=100;
INSERT INTO log ...;
COMMIT; -- 执行完立即提交

MySQL 8.0 默认隔离级别

推荐：READ COMMITTED（RC） 锁粒度最小，并发最高。

论点 10：必须使用 `EXPLAIN / EXPLAIN ANALYZE` 分析 SQL（8.0 神器）

MySQL 8.0 新增：`EXPLAIN ANALYZE`

直接显示 SQL 实际执行时间、扫描行数、循环次数，是优化神器。

sql

EXPLAIN ANALYZE
SELECT * FROM user WHERE mobile = '13800138000';

重点看 3 个字段

type：要求达到 range、ref、const，最差必须是 range，禁止 ALL（全表扫描）
key：必须命中索引
rows：扫描行数越少越好

三、MySQL 8.0 独有的 SQL 优化新特性（必须掌握）

1. 隐藏索引（用于优化测试，不影响业务）

sql

ALTER TABLE user ALTER INDEX idx_mobile INVISIBLE;

测试无索引是否变慢，安全无风险。

2. 函数索引（解决函数导致索引失效）

sql

CREATE INDEX idx_day_create ON order ( (DATE(create_time)) );

3. 降序索引（真正的物理降序，排序极快）

sql

CREATE INDEX idx_time_desc ON order (create_time DESC);

4. 通用表表达式（CTE）优化复杂 SQL

sql

WITH tmp AS (SELECT id FROM user WHERE age>18)
SELECT * FROM tmp;

比子查询快 30%+。

四、SQL 优化终极判断标准（能背下来就是高手）

一条优质 SQL 必须满足：

** 不使用 SELECT ***
命中索引
type 至少 range
无 filesort、无临时表
扫描行数 ≤ 返回行数 × 2
无函数、无运算、无隐式转换
小事务、快提交
JOIN 字段有索引
深度分页用延迟关联
用 EXPLAIN 验证

五、最实用的 10 条 SQL 优化军规（生产必用）

禁止 SELECT *
禁止索引列上使用函数
禁止隐式类型转换
联合索引遵循最左前缀
小事务快提交
深度分页必须优化
JOIN 必须有索引
禁用 OR 无索引字段
排序字段必须建索引
所有 SQL 必须 EXPLAIN

总结

MySQL 8.0 SQL 优化的核心就是： 索引最优、扫描最少、不回表、不排序、不临时表、小事务。