一、前言

在上一篇中，我们系统学习了MySQL事务的核心知识，掌握了事务的ACID特性、隔离级别和实操方法，能够在真实业务中使用事务保证数据一致性，应对多步操作的并发场景。但在生产环境中，仅仅保证数据一致性和功能正常是不够的——随着业务迭代，数据表数据量持续增长、SQL查询日益复杂、并发请求不断增加，MySQL的运行性能会逐渐下降：查询变慢、事务阻塞、服务器负载过高，甚至出现超时报错，这些问题都会直接影响用户体验和业务稳定性。

而MySQL性能优化，就是解决这些问题的关键。性能优化不是单一操作，而是一套系统性的方案，涵盖SQL语句优化、索引优化、事务优化等多个维度，核心目标是“提升查询效率、减少资源占用、避免并发阻塞”。本篇作为系列第六篇，将聚焦MySQL性能优化的实战技巧，延续前序篇章的实操风格，结合真实业务中的低效场景，从“SQL语句优化→索引进阶优化→事务优化→EXPLAIN工具使用”逐步展开，搭配具体案例和避坑重点，帮助大家彻底掌握性能优化方法，让MySQL运行更流畅、更高效，从容应对生产级复杂场景。

二、SQL语句优化（基础且核心，新手必掌握）

SQL语句是MySQL与用户交互的核心，也是性能消耗的主要来源——很多性能问题，本质上都是“低效SQL”导致的（如全表扫描、冗余查询、不合理的连接方式）。SQL优化的核心原则是“减少数据扫描量、避免无效操作、合理利用索引”，以下是生产环境中最常用、最易落地的SQL优化技巧，结合案例逐一讲解。

2.1 避免全表扫描（最关键的优化）

全表扫描是SQL性能低下的主要原因之一——当数据表数据量达到万级、十万级以上时，全表扫描会消耗大量的磁盘I/O和CPU资源，导致查询速度极慢。避免全表扫描的核心，就是让SQL语句能够利用索引，精准定位数据。

常见场景与优化方案

• 场景1：查询条件中使用非索引字段错误示例：给user表的name字段创建了普通索引，但查询时使用age字段（无索引）作为条件，导致全表扫描。

– 错误：age字段无索引，执行全表扫描
SELECT * FROM user WHERE age = 20;

– 优化方案1：给age字段创建普通索引（高频查询场景）
ALTER TABLE user ADD INDEX idx_user_age (age);

– 优化方案2：若age字段查询频率低，可避免单独创建索引，通过其他方式过滤（如结合索引字段）
SELECT * FROM user WHERE name = ‘张三’ AND age = 20; – name有索引，可利用索引过滤后再筛选age

• 场景2：查询条件中使用函数或隐式转换错误示例：给phone字段（VARCHAR类型）创建了唯一索引，但查询时使用函数或隐式转换，导致索引失效，触发全表扫描（呼应上一篇索引失效场景）。

– 错误1：函数操作导致索引失效（全表扫描）
SELECT * FROM user WHERE LEFT(name, 1) = ‘张’;
– 错误2：隐式转换导致索引失效（phone是VARCHAR，用数字查询）
SELECT * FROM user WHERE phone = 13800138000;

– 优化方案1：避免函数操作，改写SQL
SELECT * FROM user WHERE name LIKE ‘张%’; – 通配符在结尾，可利用索引
– 优化方案2：避免隐式转换，保持字段类型一致
SELECT * FROM user WHERE phone = ‘13800138000’;

• 场景3：使用SELECT * 查询所有字段错误示例：无论需要哪些字段，都用SELECT * 查询，会导致MySQL扫描所有字段，增加数据传输和处理开销，尤其当表中有大字段（如text、blob）时，性能影响更明显。

– 错误：SELECT * 扫描所有字段
SELECT * FROM user WHERE name = ‘张三’;

– 优化方案：只查询需要的字段，减少数据传输
SELECT id, name, phone FROM user WHERE name = ‘张三’;

2.2 优化排序和分组操作
当SQL语句中包含ORDER BY（排序）、GROUP BY（分组）时，若处理不当，会导致MySQL执行额外的排序操作（Using filesort），消耗大量CPU资源，尤其是大数据量场景下，排序效率极低。优化核心是“让排序/分组操作利用索引，避免手动排序”。

实操案例与优化

-- 场景：查询用户信息，按age排序（age字段有普通索引）
-- 错误：排序字段与索引字段一致，但查询字段不包含在索引中，可能触发Using filesort
SELECT id, name, age FROM user WHERE name LIKE '张%' ORDER BY age;

-- 优化方案：创建联合索引，包含查询字段和排序字段（覆盖索引）
ALTER TABLE user ADD INDEX idx_user_name_age (name, age);
-- 此时查询会利用联合索引，无需额外排序，效率大幅提升
SELECT id, name, age FROM user WHERE name LIKE '张%' ORDER BY age;

关键说明：覆盖索引（查询字段和排序/分组字段都包含在索引中）是优化排序、分组操作的核心，能让MySQL直接通过索引获取数据，避免回表查询和手动排序。

2.3 优化多表连接查询
多表连接（JOIN）是业务中常用的查询方式，但不合理的连接方式和连接顺序，会导致查询效率极低。多表连接优化的核心是“小表驱动大表、避免笛卡尔积、合理使用连接类型”。

核心优化技巧

• 小表驱动大表：用数据量少的表（小表）作为驱动表，数据量大的表（大表）作为被驱动表，减少连接次数。MySQL中，LEFT JOIN 左表是驱动表，RIGHT JOIN 右表是驱动表，INNER JOIN 会自动选择小表作为驱动表。

• 避免笛卡尔积：多表连接时，必须添加有效的连接条件（ON子句），否则会产生笛卡尔积（两表数据量相乘），导致查询结果暴增，性能崩溃。

• 优先使用INNER JOIN：INNER JOIN 只返回两表匹配的数据，过滤效率高；LEFT JOIN/RIGHT JOIN 会返回左表/右表的所有数据，若无需全部返回，尽量替换为INNER JOIN。

实操案例与优化

-- 场景：查询用户订单信息（user表和order表连接）
-- 错误1：无连接条件，产生笛卡尔积（若user有1000条数据，order有10000条，结果会有1000*10000=1000万条）
SELECT u.name, o.order_no FROM user u JOIN `order` o;

-- 错误2：用大表作为驱动表（假设order表数据量大于user表）
SELECT u.name, o.order_no FROM `order` o LEFT JOIN user u ON o.user_id = u.id;

-- 优化方案1：添加有效连接条件，避免笛卡尔积
SELECT u.name, o.order_no FROM user u JOIN `order` o ON u.id = o.user_id;

-- 优化方案2：小表驱动大表（user表作为驱动表），并给连接字段创建索引
ALTER TABLE `order` ADD INDEX idx_order_userid (user_id);  -- 给order表的user_id创建索引
SELECT u.name, o.order_no FROM user u LEFT JOIN `order` o ON u.id = o.user_id;

2.4 其他SQL优化小技巧

• 避免使用OR连接非索引字段：OR连接的字段若有一个无索引，会导致整个查询索引失效，可替换为UNION ALL（效率高于UNION）。

• 避免使用NOT IN、!=、<> 等运算符：这类运算符会导致索引失效，可替换为IN、<=、>= 等。

• 合理使用分页查询：大数据量分页（如LIMIT 10000, 10）会导致MySQL扫描前10010条数据，效率极低，可通过索引定位分页位置优化。

• 避免重复查询：相同的查询结果可缓存（如应用层缓存、MySQL缓存），减少重复执行SQL的开销。

三、索引进阶优化（避开冗余，提升索引效率）

在上一篇中，我们学习了索引的基础用法和失效场景，而索引优化不仅是“创建索引”，更重要的是“创建有效索引、避免冗余索引、优化索引结构”——不合理的索引（如冗余索引、无效索引）不仅无法提升性能，还会增加磁盘占用和写操作开销。以下是索引进阶优化的核心技巧。

3.1 避免冗余索引（重点避坑）

冗余索引是指“多个索引的功能重叠，导致重复维护”，比如给name字段创建普通索引，又给name和age字段创建联合索引（idx_user_name_age），此时普通索引idx_user_name就是冗余索引——因为联合索引的最左字段是name，完全可以替代idx_user_name的功能。

冗余索引示例与优化

-- 错误：存在冗余索引
CREATE INDEX idx_user_name ON user (name);  -- 普通索引（冗余）
CREATE INDEX idx_user_name_age ON user (name, age);  -- 联合索引

-- 优化方案：删除冗余索引，保留联合索引
DROP INDEX idx_user_name ON user;

-- 验证：联合索引可替代普通索引的功能
SELECT * FROM user WHERE name = '张三';  -- 可利用联合索引idx_user_name_age

核心原则：联合索引可以覆盖其最左字段的普通索引功能，无需单独给最左字段创建普通索引，避免冗余。

3.2 优化联合索引的字段顺序
联合索引的字段顺序直接影响索引的生效效率，核心原则是“高频查询字段放左边、高基数字段放左边”。

• 高频查询字段放左边：查询条件中最常使用的字段，放在联合索引的最左侧，满足最左前缀原则，确保索引生效。

• 高基数字段放左边：高基数字段（字段值重复率低，如id、phone）放在左边，能更快地过滤数据，减少后续扫描量；低基数字段（如性别、状态）放在右边，过滤效果差。

实操案例与优化

-- 场景：查询条件经常是 WHERE name = '张三' AND age = 20（name是高频查询字段，基数高于age）
-- 错误：联合索引字段顺序颠倒，过滤效率低
CREATE INDEX idx_user_age_name ON user (age, name);

-- 优化方案：高频、高基数字段放左边
CREATE INDEX idx_user_name_age ON user (name, age);

-- 验证：查询可高效利用联合索引
SELECT * FROM user WHERE name = '张三' AND age = 20;

3.3 合理使用覆盖索引（减少回表）
覆盖索引是指“查询的所有字段（包括查询条件、排序字段、返回字段）都包含在索引中”，MySQL无需回表查询数据表中的数据，直接通过索引就能获取所有需要的信息，大幅提升查询效率。

实操案例与优化

-- 场景：查询用户的name和age，条件是name LIKE '张%'，按age排序
-- 错误：索引只包含name，查询时需要回表获取age字段，效率低
CREATE INDEX idx_user_name ON user (name);
SELECT name, age FROM user WHERE name LIKE '张%' ORDER BY age;

-- 优化方案：创建覆盖索引，包含name、age字段
CREATE INDEX idx_user_name_age ON user (name, age);
-- 此时查询无需回表，直接通过索引获取name和age，效率大幅提升
SELECT name, age FROM user WHERE name LIKE '张%' ORDER BY age;

3.4 定期清理无效索引
生产环境中，随着业务迭代，部分索引可能会变得无效（如字段不再用于查询、业务逻辑变更），这些无效索引会占用磁盘空间、降低写操作效率，需要定期清理。

如何判断无效索引：通过MySQL的慢查询日志、EXPLAIN工具，查看索引的使用情况，若某条索引长期未被使用，且不影响业务查询，则可视为无效索引，进行删除。

四、事务优化（减少阻塞，提升并发性能）

事务优化的核心目标是“减少事务执行时间、避免事务阻塞、降低死锁概率”，因为事务执行时间越长，占用的数据库资源越多，并发场景下越容易出现阻塞和死锁，影响整体性能。以下是事务优化的核心技巧，结合前序事务知识展开。

4.1 缩短事务执行时间（核心）

事务执行时间越长，锁占用时间越长，并发阻塞的概率越高。缩短事务执行时间的核心是“让事务只包含必要的SQL操作，避免不必要的操作在事务中执行”。

实操案例与优化

-- 错误：事务中包含不必要的查询（验证数据）和等待操作，执行时间过长
SET AUTOCOMMIT = 0;
START TRANSACTION;
-- 不必要的操作：查询用户余额（可在事务外执行）
SELECT balance FROM account WHERE username = '用户A';
-- 核心操作：扣钱和加钱
UPDATE account SET balance = balance - 100 WHERE username = '用户A';
UPDATE account SET balance = balance + 100 WHERE username = '用户B';
-- 不必要的操作：等待日志记录（可在事务外执行）
INSERT INTO operation_log (operate, create_time) VALUES ('转账', NOW());
COMMIT;
SET AUTOCOMMIT = 1;

-- 优化方案：事务只包含核心SQL，不必要的操作移到事务外
-- 事务外执行：查询和日志记录
SELECT balance FROM account WHERE username = '用户A';
-- 事务内只执行核心操作，缩短执行时间
SET AUTOCOMMIT = 0;
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE username = '用户A';
UPDATE account SET balance = balance + 100 WHERE username = '用户B';
COMMIT;
SET AUTOCOMMIT = 1;
-- 事务外执行：日志记录
INSERT INTO operation_log (operate, create_time) VALUES ('转账', NOW());

4.2 避免事务阻塞和死锁
并发场景下，多个事务同时操作同一批数据，容易出现事务阻塞（一个事务等待另一个事务释放锁）和死锁（两个事务相互等待对方释放锁），导致事务执行失败或超时。

核心优化技巧

• 统一操作顺序：多个事务操作同一批表/数据时，采用统一的操作顺序，避免相互等待。例如：两个事务都需要操作account表和order表，均先操作account表，再操作order表。

• 避免长事务：长事务会长期占用锁，增加阻塞和死锁概率，尽量将长事务拆分为多个短事务（若业务允许）。

• 合理设置锁粒度：MySQL的InnoDB存储引擎支持行锁（粒度细，并发高）和表锁（粒度粗，并发低），尽量使用行锁（通过索引定位数据，触发行锁），避免表锁。

• 及时释放锁：事务提交或回滚后，及时释放锁，避免长时间占用锁资源。

死锁示例与解决方案

-- 死锁场景：两个事务相互等待对方释放锁
-- 事务A
SET AUTOCOMMIT = 0;
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE username = '用户A';  -- 锁定用户A的数据
-- 此时事务B执行更新用户B的数据，然后等待事务A释放用户A的锁
-- 事务B
SET AUTOCOMMIT = 0;
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE username = '用户B';  -- 锁定用户B的数据
UPDATE account SET balance = balance + 100 WHERE username = '用户A';  -- 等待事务A释放锁
-- 事务A继续执行，需要锁定用户B的数据，等待事务B释放锁，形成死锁

-- 解决方案：统一操作顺序，两个事务均先操作用户A，再操作用户B
-- 事务A
SET AUTOCOMMIT = 0;
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE username = '用户A';
UPDATE account SET balance = balance + 100 WHERE username = '用户B';
COMMIT;
SET AUTOCOMMIT = 1;

-- 事务B
SET AUTOCOMMIT = 0;
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE username = '用户A';
UPDATE account SET balance = balance + 100 WHERE username = '用户B';
COMMIT;
SET AUTOCOMMIT = 1;

4.3 合理设置事务隔离级别
事务隔离级别越高，并发性能越低，需根据业务场景选择合适的隔离级别（呼应上一篇隔离级别知识）：

• 普通业务场景（如普通查询、非核心下单）：使用默认的“可重复读”，兼顾一致性和并发性能。

• 对并发性能要求高、数据一致性要求一般（如首页查询）：可降低为“读已提交”。

• 核心业务场景（如金融转账）：可提升为“串行化”，保证数据绝对一致，但需接受并发性能下降。

五、EXPLAIN工具使用（定位性能问题的核心）

EXPLAIN是MySQL提供的核心工具，用于查看SQL语句的执行计划，能够直观地看到SQL的执行过程（如是否使用索引、是否全表扫描、排序方式等），是定位SQL性能问题、验证优化效果的关键。

核心用法：在SQL语句前加上EXPLAIN，执行后即可查看执行计划，重点关注“type”“key”“Extra”三个字段。

5.1 EXPLAIN执行计划核心字段解读

字段	核心含义	重点取值及说明
type	SQL的查询类型，反映查询效率	从优到劣：system > const > eq_ref > ref > range > ALL（全表扫描）；重点避免ALL，尽量优化到ref及以上
key	SQL执行时使用的索引	取值为NULL：未使用索引；取值为索引名称：使用了对应索引
Extra	额外执行信息，关键优化依据	Using index：使用覆盖索引；Using filesort：手动排序（需优化）；Using temporary：使用临时表（需优化）；Using where：使用WHERE过滤

5.2 实操案例：用EXPLAIN定位并优化SQL

-- 场景：查询用户信息，条件是age=20，未创建索引
EXPLAIN SELECT id, name FROM user WHERE age = 20;

-- 执行计划解读：
-- type = ALL（全表扫描，效率低）
-- key = NULL（未使用索引）
-- Extra = Using where（使用WHERE过滤，但仍全表扫描）

-- 优化方案：给age字段创建普通索引
ALTER TABLE user ADD INDEX idx_user_age (age);

-- 再次执行EXPLAIN，查看优化效果
EXPLAIN SELECT id, name FROM user WHERE age = 20;

-- 优化后执行计划解读：
-- type = ref（使用索引，效率高）
-- key = idx_user_age（使用了创建的索引）
-- Extra = Using index（使用覆盖索引，无需回表）

关键说明：通过EXPLAIN，我们可以快速定位SQL的性能问题（如全表扫描、未使用索引），优化后再通过EXPLAIN验证优化效果，确保优化有效。

六、性能优化实战案例（综合应用）

结合前面的优化技巧，整理一个真实业务中的性能优化案例，帮助大家综合运用所学知识，解决实际性能问题。

场景：用户订单查询，查询效率极低（大数据量场景）

问题描述：user表（10万条数据）和order表（100万条数据），查询“用户姓名为张三，且订单金额大于100元”的订单信息，原始SQL执行时间超过5秒，效率极低。

步骤1：用EXPLAIN定位问题

-- 原始SQL
EXPLAIN SELECT u.name, o.order_no, o.total_price 
FROM user u JOIN `order` o ON u.id = o.user_id 
WHERE u.name = '张三' AND o.total_price > 100;

-- 执行计划问题：
-- 1. type = ALL（order表全表扫描，100万条数据，效率低）
-- 2. key = NULL（order表的user_id字段无索引，未使用索引）
-- 3. Extra = Using where; Using join buffer（使用连接缓冲区，效率低）

步骤2：优化SQL和索引

-- 优化1：给order表的user_id字段创建普通索引（连接字段索引）
ALTER TABLE `order` ADD INDEX idx_order_userid (user_id);

-- 优化2：给order表的total_price字段创建普通索引（查询条件索引）
ALTER TABLE `order` ADD INDEX idx_order_totalprice (total_price);

-- 优化3：创建联合索引，覆盖查询字段（进一步提升效率，避免回表）
ALTER TABLE `order` ADD INDEX idx_order_userid_totalprice (user_id, total_price, order_no);

-- 优化4：优化SQL，只查询需要的字段，避免冗余
SELECT u.name, o.order_no, o.total_price 
FROM user u JOIN `order` o ON u.id = o.user_id 
WHERE u.name = '张三' AND o.total_price > 100;

步骤3：验证优化效果
再次执行EXPLAIN，查看执行计划：

• type = ref（order表使用索引，效率大幅提升）

• key = idx_order_userid_totalprice（使用联合索引）

• Extra = Using index（使用覆盖索引，无需回表）

优化后，SQL执行时间从5秒缩短到50毫秒以内，满足生产环境性能要求。

七、本篇总结

本篇作为系列第六篇，核心围绕MySQL性能优化展开，重点掌握SQL语句优化、索引进阶优化、事务优化和EXPLAIN工具的使用，能够独立定位和解决生产环境中的MySQL性能问题，具体重点如下：

• SQL语句优化：核心是避免全表扫描、优化排序分组、优化多表连接，减少无效操作和数据扫描量，让SQL高效利用索引；

• 索引进阶优化：避免冗余索引、优化联合索引字段顺序、合理使用覆盖索引，定期清理无效索引，提升索引效率，减少资源占用；

• 事务优化：缩短事务执行时间、避免事务阻塞和死锁、合理设置隔离级别，提升并发性能，保证事务可靠性；

• EXPLAIN工具：掌握核心字段解读，能够用EXPLAIN定位SQL性能问题，验证优化效果，是性能优化的核心工具。

至此，你已经掌握了MySQL性能优化的核心实战技巧，能够应对生产环境中常见的性能问题，让MySQL运行更流畅、更高效。下一篇，我们将学习MySQL的高级特性——存储过程、触发器和函数，这些特性能够简化复杂业务逻辑，提升开发效率，是生产级数据库开发的进阶技能。

八、下一篇预告

下一篇我们将进入MySQL高级特性章节——MySQL高级特性实战：存储过程、触发器与函数。

在复杂业务场景中，经常会遇到大量重复的SQL操作（如批量更新、复杂查询），若每次都手动编写SQL，不仅开发效率低，还容易出现错误。存储过程、触发器和函数能够将重复的SQL逻辑封装起来，实现“一次编写、多次调用”，简化开发流程、减少代码冗余。下一篇将详细讲解存储过程、触发器和函数的定义、创建、调用方法，结合真实业务场景，演示如何用这些高级特性简化复杂业务逻辑，同时规避其使用过程中的常见坑，帮你提升数据库开发效率。