线上业务系统的性能瓶颈，90%以上都集中在数据库层，而慢SQL是引发数据库雪崩的头号元凶。很多开发者优化SQL时，只会盲目加索引，却不懂执行计划的底层逻辑，最终导致索引滥用、性能越优化越差，甚至引发线上故障。
一、执行计划：SQL优化的"导航仪"
1.1 MySQL SQL执行全链路
要读懂执行计划，首先要明确SQL从输入到返回结果的完整执行流程，这是理解执行计划来源的基础： 客户端请求 → 连接器（身份认证、权限校验）→ 解析器（词法解析、语法解析，生成语法树）→ 预处理器（校验表、字段是否存在，权限二次校验）→ 优化器（生成最优执行计划）→ 执行器（调用存储引擎接口执行）→ 存储引擎（InnoDB，返回数据） 注：MySQL 8.0已彻底移除查询缓存模块，生产环境无需关注相关配置。
1.2 执行计划的获取方式
执行计划是MySQL优化器生成的SQL执行步骤明细，通过EXPLAIN关键字获取，核心用法有3种：
- 基础用法：
EXPLAIN SELECT * FROM user_info WHERE id = 1; - 详细JSON格式：
EXPLAIN FORMAT=JSON SELECT * FROM user_info WHERE id = 1;可查看优化器的成本计算、执行细节 - 执行分析（MySQL 8.0.18+支持）：
EXPLAIN ANALYZE SELECT * FROM user_info WHERE id = 1;实际执行SQL，返回执行计划+真实执行耗时、扫描行数、循环次数等，是优化的终极利器
1.3 执行计划核心字段全解
执行计划的输出有12个核心字段，每个字段都对应SQL执行的关键信息，必须逐个吃透。以下所有实例均基于统一的测试表，MySQL 8.0可直接执行：
CREATE TABLE `user_info` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`user_no` varchar(32) NOT NULL COMMENT '用户编号',
`user_name` varchar(64) NOT NULL COMMENT '用户姓名',
`age` int NOT NULL DEFAULT '0' COMMENT '年龄',
`gender` tinyint NOT NULL DEFAULT '0' COMMENT '性别 0-未知 1-男 2-女',
`phone` varchar(11) NOT NULL COMMENT '手机号',
`create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
`update_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_no` (`user_no`),
KEY `idx_age_create_time` (`age`,`create_time`),
KEY `idx_name_phone` (`user_name`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户信息表';
CREATE TABLE `order_info` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`order_no` varchar(32) NOT NULL COMMENT '订单编号',
`user_id` bigint unsigned NOT NULL COMMENT '用户ID',
`order_amount` decimal(10,2) NOT NULL COMMENT '订单金额',
`order_status` tinyint NOT NULL DEFAULT '0' COMMENT '订单状态',
`create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_order_no` (`order_no`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单表';
1.3.1 id：执行顺序标识
id值相同，执行顺序从上到下；id值不同，id越大越先执行；id为NULL的行最后执行。用于区分复杂查询（子查询、联表查询）的执行顺序，示例：
EXPLAIN SELECT * FROM user_info WHERE id = (SELECT id FROM user_info WHERE user_no = 'U001');
子查询的id为2，优先执行；外层查询id为1，后执行。
1.3.2 select_type：查询类型
标识当前查询的类型，核心类型及含义：
SIMPLE：简单查询，不包含子查询、UNIONPRIMARY：复杂查询的最外层查询SUBQUERY：SELECT/WHERE中的子查询DERIVED：FROM中的子查询（派生表）UNION：UNION中的第二个及以后的查询UNION RESULT：UNION的结果集
1.3.3 type：访问类型（核心优化指标）
标识MySQL找到目标行的方式，是性能最核心的指标，性能从优到劣的顺序为：system > const > eq_ref > ref > range > index > ALL生产环境核心要求：所有SQL的type必须至少达到range级别，核心查询必须达到ref级别。 逐个拆解核心级别：
system：系统表，只有一行数据，极少出现const：常量查询，通过主键/唯一索引等值查询，最多匹配一行数据，性能最优
EXPLAIN SELECT * FROM user_info WHERE id = 1;
EXPLAIN SELECT * FROM user_info WHERE user_no = 'U001';
eq_ref：联表查询时，被驱动表通过主键/唯一索引等值关联，每行匹配最多一行数据，是联表查询的最优级别
EXPLAIN SELECT o.* FROM order_info o LEFT JOIN user_info u ON o.user_id = u.id WHERE o.order_no = 'O001';
ref：非唯一索引等值查询，匹配多行数据，是普通查询的最优级别
EXPLAIN SELECT * FROM user_info WHERE age = 18;
range：范围查询，使用索引进行范围匹配，比如>、<、>=、<=、IN、BETWEEN
EXPLAIN SELECT * FROM user_info WHERE age BETWEEN 18 AND 30;
EXPLAIN SELECT * FROM user_info WHERE id IN (1,2,3);
index：遍历整个索引树，比全表扫描略快，因为索引文件比数据文件小ALL：全表扫描，性能最差，必须优化
1.3.4 possible_keys & key：索引匹配情况
possible_keys：可能用到的索引，优化器评估的候选索引key：实际用到的索引，为NULL表示没有用到索引 核心注意点：possible_keys有值但key为NULL，说明索引失效，必须优化。
1.3.5 key_len：索引使用长度
标识MySQL使用的索引字节数，可用于判断联合索引的哪些字段被用到。 MySQL 8.0 utf8mb4字符集下的计算规则：
- 数值类型：TINYINT=1，INT=4，BIGINT=8
- 字符串类型：VARCHAR(n) = 4*n + 2（变长字段额外2字节），NOT NULL字段无需额外字节，允许NULL的字段额外1字节
- 时间类型：DATETIME(3)=8 示例：联合索引
idx_age_create_time(age, create_time)，age是INT NOT NULL（4字节），create_time是DATETIME(3) NOT NULL（8字节）。若key_len=4，说明只用到了age字段；若key_len=12，说明两个字段都用到了。
1.3.6 ref：索引匹配的常量/字段
标识与索引列进行等值匹配的字段或常量，比如const、u.id等，可判断索引的匹配精度。
1.3.7 rows & filtered：扫描行数预估
rows：MySQL预估的需要扫描的行数，数值越小越好filtered：符合查询条件的行数占扫描行数的比例，数值越接近100%越好，低于10%说明索引选择不合理 示例：rows=10000，filtered=10.00，说明扫描了10000行，只有1000行符合条件，索引效率极低，必须优化。
1.3.8 Extra：额外信息（优化核心）
Extra字段包含SQL执行的关键细节，是优化的重中之重，核心项及优化方案如下：
Using index：覆盖索引，查询的所有字段都在索引中，无需回表查询，性能最优，是优化的核心目标
EXPLAIN SELECT user_name, phone FROM user_info WHERE user_name = '张三';
Using where：通过索引扫描后，再用WHERE条件过滤，通常说明索引覆盖不全Using filesort：无法利用索引完成排序，MySQL对结果集进行额外的排序操作，是核心优化点
-- 反例：无法用到索引排序
EXPLAIN SELECT * FROM user_info WHERE age > 18 ORDER BY create_time;
-- 优化：联合索引匹配查询+排序字段
EXPLAIN SELECT * FROM user_info WHERE age > 18 ORDER BY age, create_time;
核心纠正：Using filesort不是磁盘排序，只要无法用索引排序，无论内存还是磁盘排序，都会显示该标识。只有当排序结果超过sort_buffer_size时，才会触发磁盘临时文件排序。 4. Using temporary：MySQL需要创建临时表存储中间结果，常见于GROUP BY、UNION、DISTINCT，性能极差，必须优化 5. Impossible WHERE：WHERE条件永远为false，比如WHERE 1=2，无数据返回 6. Using join buffer：联表查询时，被驱动表无法用到索引，需要用连接缓存，说明联表关联字段没有加索引，必须优化
1.4 执行计划正确解读顺序
很多人拿到执行计划不知道从哪看，正确的解读顺序如下：
1.5 EXPLAIN ANALYZE：真实执行分析
MySQL 8.0.18+提供的EXPLAIN ANALYZE，会实际执行SQL，返回执行计划+真实执行数据，包括实际执行时间、实际扫描行数、实际匹配行数、循环执行次数、排序/分组的耗时与内存使用情况，是排查优化效果、定位复杂慢SQL的终极工具，示例：
EXPLAIN ANALYZE SELECT * FROM user_info WHERE age BETWEEN 18 AND 30 ORDER BY create_time;
二、慢SQL优化：全场景根治方案
2.1 索引优化核心原则与失效场景
InnoDB采用B+树索引结构，所有数据都存储在主键索引的叶子节点（聚簇索引），普通索引的叶子节点存储主键值（二级索引），通过二级索引查询需要先查二级索引得到主键，再查主键索引得到数据（回表操作）。
2.1.1 最左前缀原则
联合索引的匹配必须从左到右依次匹配，遇到范围查询时，右侧的字段会失效，是联合索引设计的核心原则。 示例：联合索引idx_a_b_c(a,b,c)
- 可匹配的查询：
a=1、a=1 AND b=2、a=1 AND b=2 AND c=3、a=1 AND b BETWEEN 2 AND 3 - 不可匹配的查询：
b=2、a=1 AND c=3（跳过b字段）、a=1 AND b>2 AND c=3（c字段失效）
2.1.2 高频索引失效场景与优化方案
以下场景是生产中90%的索引失效原因，每个场景都有明确的原理和优化方案：
- 索引列上执行函数运算、表达式计算原理：B+树存储的是索引列的原始值，对索引列做函数运算后，无法匹配索引树，导致全表扫描。
-- 反例：索引列使用函数，失效
EXPLAIN SELECT * FROM user_info WHERE YEAR(create_time) = 2024;
-- 正例：范围查询，保留索引列原始值，用到索引
EXPLAIN SELECT * FROM user_info WHERE create_time BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59';
- 隐式类型转换原理：当查询条件的类型与索引列的类型不一致时，MySQL会对索引列做隐式类型转换，相当于对索引列执行函数，导致索引失效。 核心规则：字符串列与数字常量比较，索引失效；数字列与字符串常量比较，索引有效。
-- 反例：user_no是varchar类型，与数字常量比较，隐式转换，索引失效
EXPLAIN SELECT * FROM user_info WHERE user_no = 123;
-- 正例：类型匹配，用到索引
EXPLAIN SELECT * FROM user_info WHERE user_no = '123';
- 模糊查询以%开头原理：B+树的索引排序是按前缀匹配的，以%开头的模糊查询，无法利用索引的前缀排序，导致索引失效。
-- 反例：%开头，索引失效
EXPLAIN SELECT * FROM user_info WHERE user_name LIKE '%张三';
-- 优化方案1：覆盖索引，仅查询索引内的字段，可触发索引扫描
EXPLAIN SELECT user_name, phone FROM user_info WHERE user_name LIKE '%张三';
-- 优化方案2：全文索引，MySQL 8.0支持中文全文索引
ALTER TABLE user_info ADD FULLTEXT INDEX `ft_user_name` (`user_name`);
EXPLAIN SELECT * FROM user_info WHERE MATCH(user_name) AGAINST('张三' IN NATURAL LANGUAGE MODE);
- OR条件两侧未全部建立索引原理：OR条件只要有一侧没有索引，MySQL就会放弃索引，执行全表扫描。
-- 反例：gender无索引，全表扫描
EXPLAIN SELECT * FROM user_info WHERE id = 1 OR gender = 1;
-- 正例：UNION ALL拆分，两个查询都用到索引
EXPLAIN SELECT * FROM user_info WHERE id = 1 UNION ALL SELECT * FROM user_info WHERE gender = 1;
- NOT IN、!=、<> 负向查询原理：负向查询无法利用B+树的有序性，大部分场景会触发全表扫描，仅当主键/唯一索引的负向查询，可触发range级别扫描。
-- 反例：普通索引负向查询，全表扫描
EXPLAIN SELECT * FROM user_info WHERE age != 18;
-- 优化方案：业务上用正向范围查询替代，或限制主键范围
EXPLAIN SELECT * FROM user_info WHERE age < 18 OR age > 18;
EXPLAIN SELECT * FROM user_info WHERE id NOT IN (1,2,3);
- JOIN关联字段类型、字符集不一致原理：联表查询时，关联字段的类型、字符集不一致，会触发隐式类型转换，导致被驱动表的索引失效。
-- 反例：user_info.id是bigint，order_info.user_id是varchar，关联时隐式转换，索引失效
ALTER TABLE order_info MODIFY COLUMN `user_id` varchar(32) NOT NULL COMMENT '用户ID';
EXPLAIN SELECT o.* FROM order_info o LEFT JOIN user_info u ON o.user_id = u.id;
-- 正例：关联字段类型、字符集完全一致，用到索引
ALTER TABLE order_info MODIFY COLUMN `user_id` bigint unsigned NOT NULL COMMENT '用户ID';
EXPLAIN SELECT o.* FROM order_info o LEFT JOIN user_info u ON o.user_id = u.id;
2.2 联表查询优化
MySQL联表查询的核心算法是Nested-Loop Join（NLJ），驱动表的每一行数据，到被驱动表中匹配符合条件的行，循环次数=驱动表行数×被驱动表单次查询成本。 核心优化原则：小表驱动大表，被驱动表的关联字段必须建立索引
2.2.1 驱动表选择规则
- LEFT JOIN：左表为驱动表，右表为被驱动表
- RIGHT JOIN：右表为驱动表，左表为被驱动表
- INNER JOIN：MySQL优化器会自动选择数据量小的表作为驱动表，大表作为被驱动表
2.2.2 联表算法优化
- Nested-Loop Join（NLJ）：被驱动表关联字段有索引时使用，性能最优，循环次数=驱动表行数
- Block Nested-Loop Join（BNL）：被驱动表关联字段无索引时使用，将驱动表数据加载到join buffer中，批量匹配被驱动表，循环次数=驱动表行数/join_buffer_size，性能极差，必须优化
- Batched Key Access（BKA）：MySQL 8.0默认开启，基于MRR（Multi-Range Read）优化，批量读取被驱动表的索引数据，减少随机IO，提升联表性能
2.3 分页查询优化
深分页查询是生产中最常见的慢SQL场景，比如LIMIT 100000, 20，MySQL会先扫描100020行数据，然后丢弃前100000行，仅返回后20行，扫描行数随分页深度线性增长，性能急剧下降。
核心优化方案：
- 主键覆盖优化：利用主键索引的有序性，先定位到分页起始的主键值，再查询后续数据
-- 反例：深分页全表扫描
EXPLAIN SELECT * FROM user_info ORDER BY id LIMIT 100000, 20;
-- 正例：主键覆盖优化，仅扫描20行
EXPLAIN SELECT * FROM user_info WHERE id >= (SELECT id FROM user_info ORDER BY id LIMIT 100000, 1) LIMIT 20;
- 延迟关联优化：先通过覆盖索引查询分页所需的主键，再通过主键关联查询完整数据，减少回表次数
-- 正例：延迟关联优化
EXPLAIN SELECT a.* FROM user_info a INNER JOIN (SELECT id FROM user_info ORDER BY id LIMIT 100000, 20) b ON a.id = b.id;
- 业务限制分页深度：禁止无限制分页，比如最多允许翻页到100页，超过后提示用户缩小查询范围，从根源上避免深分页问题。
2.4 分组与排序优化
GROUP BY和ORDER BY是引发Using temporary、Using filesort的核心原因，优化的核心原则是：让排序/分组的字段与索引的顺序、升降序完全一致，避免额外的排序操作
2.4.1 排序优化核心规则
- ORDER BY的字段顺序必须与联合索引的字段顺序完全一致
- ORDER BY的字段升降序必须与联合索引的升降序完全一致
- 排序字段必须全部在同一个联合索引中
-- 反例：排序字段与索引顺序不一致，触发Using filesort
EXPLAIN SELECT * FROM user_info WHERE age = 18 ORDER BY create_time DESC, age ASC;
-- 正例：排序字段与索引顺序、升降序一致，无Using filesort
EXPLAIN SELECT * FROM user_info WHERE age = 18 ORDER BY age ASC, create_time DESC;
2.4.2 分组优化核心规则
- GROUP BY的字段顺序必须与联合索引的字段顺序完全一致
- MySQL 8.0.13+已移除GROUP BY的隐式排序，无需额外添加ORDER BY NULL
- 避免分组结果集过大，触发临时表
-- 反例：分组字段无索引，触发Using temporary、Using filesort
EXPLAIN SELECT age, COUNT(*) FROM user_info GROUP BY age;
-- 正例：分组字段有索引，无临时表、无文件排序
EXPLAIN SELECT age, COUNT(*) FROM user_info USE INDEX (idx_age_create_time) GROUP BY age;
2.5 聚合函数优化
常见的聚合函数有COUNT、SUM、MAX、MIN，优化核心是利用索引减少扫描行数。
2.5.1 COUNT函数优化
核心纠正：COUNT()、COUNT(1)、COUNT(主键)的性能几乎无差异，MySQL优化器会选择最小的辅助索引进行统计，COUNT(非空字段)与COUNT()等价，COUNT(允许NULL的字段)会忽略NULL值，性能最差。
-- 最优写法：统计总行数
SELECT COUNT(*) FROM user_info;
-- 反例：统计行数却用COUNT(字段)，性能差
SELECT COUNT(age) FROM user_info;
-- 优化：判断是否存在符合条件的数据，用LIMIT 1替代COUNT(*)
-- 反例：全表扫描符合条件的行
SELECT COUNT(*) FROM user_info WHERE age > 18 LIMIT 1;
-- 正例：找到第一行就返回，无需扫描全表
SELECT 1 FROM user_info WHERE age > 18 LIMIT 1;
2.5.2 MAX/MIN函数优化
MAX/MIN函数可直接利用B+树的有序性，索引列的MAX/MIN查询只需访问索引树的根节点或叶子节点的首尾，性能最优。
-- 正例：索引列的MAX查询，无需扫描全表
EXPLAIN SELECT MAX(create_time) FROM user_info WHERE age = 18;
2.6 批量操作优化
批量插入、更新是提升写入性能的核心方案，可大幅减少网络IO、事务开销、锁等待时间。
-- 反例：循环单条插入，性能极差
INSERT INTO user_info (user_no, user_name, age, phone) VALUES ('U001', '张三', 18, '13800138000');
INSERT INTO user_info (user_no, user_name, age, phone) VALUES ('U002', '李四', 20, '13800138001');
-- 正例：批量插入，一次提交
INSERT INTO user_info (user_no, user_name, age, phone)
VALUES ('U001', '张三', 18, '13800138000'), ('U002', '李四', 20, '13800138001');
-- 反例：大表单条更新，锁表时间长，引发锁等待
UPDATE order_info SET order_status = 1 WHERE create_time < '2024-01-01';
-- 正例：分批更新，每次更新1000条，避免锁表
UPDATE order_info SET order_status = 1 WHERE create_time < '2024-01-01' AND id BETWEEN 1 AND 1000;
UPDATE order_info SET order_status = 1 WHERE create_time < '2024-01-01' AND id BETWEEN 1001 AND 2000;
三、SQL编写与设计规范
规范是避免慢SQL的第一道防线，以下规范均经过生产环境验证，可直接落地。
3.1 表设计规范
- 所有表必须有主键，优先使用
BIGINT UNSIGNED AUTO_INCREMENT，禁止使用UUID作为主键，避免InnoDB页分裂，影响写入性能。 - 所有字段必须设置
NOT NULL DEFAULT，NULL值会占用额外存储空间，导致索引、统计、比较运算更复杂。 - 字符串字段优先使用
VARCHAR，长度不超过2048，大文本使用TEXT，禁止在TEXT字段上建立索引。 - 小数类型必须使用
DECIMAL，禁止使用FLOAT、DOUBLE，避免精度丢失。 - 时间字段必须使用
DATETIME(3)，精度到毫秒，禁止使用VARCHAR、INT存储时间。 - 所有表和字段必须添加
COMMENT注释，明确业务含义。 - 表的字符集统一使用
utf8mb4，排序规则使用utf8mb4_0900_ai_ci，支持emoji和所有中文。 - 禁止使用外键约束，外键的关联逻辑在业务层实现，避免写入时的外键校验开销和死锁风险。
3.2 索引设计规范
- 单表索引数量控制在5个以内，联合索引的字段数量不超过5个，避免索引过多影响写入性能。
- 索引命名规范：主键索引
pk_字段名，唯一索引uk_字段名，普通索引idx_字段1_字段2_字段3（按最左前缀顺序命名）。 - 联合索引设计遵循最左前缀原则，区分度高的字段放在左侧，区分度计算公式：
COUNT(DISTINCT 字段)/COUNT(*)，越接近1区分度越高。 - 禁止给更新频繁、区分度极低的字段建立索引，比如性别、状态（仅2-3个枚举值），除非数据分布极不均匀，且查询目标为占比极低的枚举值。
- 联表查询的关联字段必须建立索引，且类型、字符集完全一致。
- 优先建立覆盖索引，避免回表操作，核心查询必须实现覆盖索引。
- 禁止使用冗余索引，比如已有联合索引
idx_a_b(a,b)，禁止再建立idx_a(a)。
3.3 查询编写规范
- 禁止使用
SELECT *，必须指定具体的查询字段，避免返回无用数据，无法使用覆盖索引。 - 禁止使用不带WHERE条件的SELECT、UPDATE、DELETE语句。
- 禁止在WHERE条件中对索引列执行函数运算、表达式计算、类型转换。
- 禁止使用以%开头的模糊查询，除非使用覆盖索引或全文索引。
- 禁止使用OR条件连接无索引的字段，用UNION ALL拆分查询。
- 禁止使用负向查询（NOT IN、!=、<>），除非是主键/唯一索引，且结果集可控。
- 禁止子查询嵌套超过3层，优先使用JOIN查询替代子查询。
- 大表禁止JOIN超过3张表，高并发场景尽量避免JOIN，用冗余字段或单表查询替代。
- 深分页查询必须使用主键覆盖或延迟关联优化，禁止直接使用LIMIT offset, size。
- 禁止在业务代码中使用COUNT(*)判断数据是否存在，用SELECT 1 ... LIMIT 1替代。
3.4 写入操作规范
- 批量插入、更新必须使用批量操作，禁止循环单条写入，单次批量操作的数据量控制在1000条以内。
- 大表更新必须分批执行，每次更新的数据量控制在1000条以内，避免锁表和长事务。
- 高并发场景禁止使用长事务，写入操作必须尽量简短，快速提交。
- 禁止使用UPDATE ... JOIN多表更新，避免锁等待时间过长。
- 插入数据时，禁止指定主键值，使用数据库自增主键，保证主键有序。
四、Java工程化落地：MyBatis-Plus最佳实践
基于Spring Boot 3.2.4、JDK 17、MyBatis-Plus 3.5.6实现，符合编码规范，结合SQL优化最佳实践。
4.1 Maven核心依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>3.2.4</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.6</version>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.3.0</version>
</dependency>
<dependency>
<groupId>org.springdoc</groupId>
<artifactId>springdoc-openapi-starter-webmvc-ui</artifactId>
<version>2.5.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.32</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.alibaba.fastjson2</groupId>
<artifactId>fastjson2</artifactId>
<version>2.0.52</version>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>33.1.0-jre</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>3.2.4</version>
</dependency>
</dependencies>
4.2 实体类
package com.jam.demo.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;
import java.time.LocalDateTime;
/**
* 用户信息实体类
* @author ken
*/
@Data
@TableName("user_info")
@Schema(name = "UserInfo", description = "用户信息实体")
public class UserInfo {
@TableId(type = IdType.AUTO)
@Schema(description = "主键ID", example = "1")
private Long id;
@TableField("user_no")
@Schema(description = "用户编号", example = "U001")
private String userNo;
@TableField("user_name")
@Schema(description = "用户姓名", example = "张三")
private String userName;
@TableField("age")
@Schema(description = "年龄", example = "18")
private Integer age;
@TableField("gender")
@Schema(description = "性别 0-未知 1-男 2-女", example = "1")
private Integer gender;
@TableField("phone")
@Schema(description = "手机号", example = "13800138000")
private String phone;
@TableField("create_time")
@Schema(description = "创建时间")
private LocalDateTime createTime;
@TableField("update_time")
@Schema(description = "更新时间")
private LocalDateTime updateTime;
}
4.3 Mapper接口
package com.jam.demo.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.jam.demo.entity.UserInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
/**
* 用户信息Mapper接口
* @author ken
*/
@Mapper
public interface UserInfoMapper extends BaseMapper<UserInfo> {
/**
* 延迟关联分页查询用户列表
* @param page 分页参数
* @param minAge 最小年龄
* @param maxAge 最大年龄
* @return 分页结果
*/
IPage<UserInfo> selectUserPageByDelayJoin(Page<UserInfo> page, @Param("minAge") Integer minAge, @Param("maxAge") Integer maxAge);
}
4.4 Mapper XML文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jam.demo.mapper.UserInfoMapper">
<resultMap id="BaseResultMap" type="com.jam.demo.entity.UserInfo">
<id column="id" property="id" jdbcType="BIGINT"/>
<result column="user_no" property="userNo" jdbcType="VARCHAR"/>
<result column="user_name" property="userName" jdbcType="VARCHAR"/>
<result column="age" property="age" jdbcType="INTEGER"/>
<result column="gender" property="gender" jdbcType="TINYINT"/>
<result column="phone" property="phone" jdbcType="VARCHAR"/>
<result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
<result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
</resultMap>
<select id="selectUserPageByDelayJoin" resultMap="BaseResultMap">
SELECT a.* FROM user_info a
INNER JOIN (
SELECT id FROM user_info
WHERE age BETWEEN #{minAge} AND #{maxAge}
ORDER BY id
) b ON a.id = b.id
</select>
</mapper>
4.5 Service层接口
package com.jam.demo.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.jam.demo.entity.UserInfo;
/**
* 用户信息服务接口
* @author ken
*/
public interface UserInfoService extends IService<UserInfo> {
/**
* 分页查询用户列表
* @param pageNum 页码
* @param pageSize 每页条数
* @param minAge 最小年龄
* @param maxAge 最大年龄
* @return 分页结果
*/
IPage<UserInfo> getUserPage(Integer pageNum, Integer pageSize, Integer minAge, Integer maxAge);
/**
* 批量保存用户信息
* @param userInfoList 用户列表
* @return 保存结果
*/
Boolean batchSaveUserInfo(java.util.List<UserInfo> userInfoList);
}
4.6 Service层实现类
package com.jam.demo.service.impl;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.google.common.collect.Lists;
import com.jam.demo.entity.UserInfo;
import com.jam.demo.mapper.UserInfoMapper;
import com.jam.demo.service.UserInfoService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;
import jakarta.annotation.Resource;
import java.util.List;
/**
* 用户信息服务实现类
* @author ken
*/
@Slf4j
@Service
public class UserInfoServiceImpl extends ServiceImpl<UserInfoMapper, UserInfo> implements UserInfoService {
@Resource
private UserInfoMapper userInfoMapper;
@Resource
private TransactionTemplate transactionTemplate;
private static final int BATCH_SIZE = 1000;
@Override
public IPage<UserInfo> getUserPage(Integer pageNum, Integer pageSize, Integer minAge, Integer maxAge) {
if (ObjectUtils.isEmpty(pageNum) || pageNum < 1) {
pageNum = 1;
}
if (ObjectUtils.isEmpty(pageSize) || pageSize < 1 || pageSize > 100) {
pageSize = 20;
}
Page<UserInfo> page = new Page<>(pageNum, pageSize);
return userInfoMapper.selectUserPageByDelayJoin(page, minAge, maxAge);
}
@Override
public Boolean batchSaveUserInfo(List<UserInfo> userInfoList) {
if (CollectionUtils.isEmpty(userInfoList)) {
return Boolean.TRUE;
}
return transactionTemplate.execute(new TransactionCallback<Boolean>() {
@Override
public Boolean doInTransaction(TransactionStatus status) {
try {
List<List<UserInfo>> partitionList = Lists.partition(userInfoList, BATCH_SIZE);
for (List<UserInfo> partition : partitionList) {
saveBatch(partition, BATCH_SIZE);
}
return Boolean.TRUE;
} catch (Exception e) {
status.setRollbackOnly();
log.error("批量保存用户信息失败", e);
return Boolean.FALSE;
}
}
});
}
}
4.7 Controller层
package com.jam.demo.controller;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.jam.demo.entity.UserInfo;
import com.jam.demo.service.UserInfoService;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.Parameter;
import io.swagger.v3.oas.annotations.tags.Tag;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import jakarta.annotation.Resource;
import java.util.List;
/**
* 用户信息控制器
* @author ken
*/
@Slf4j
@RestController
@RequestMapping("/user")
@Tag(name = "用户信息管理", description = "用户信息的增删改查接口")
public class UserInfoController {
@Resource
private UserInfoService userInfoService;
@GetMapping("/page")
@Operation(summary = "分页查询用户列表", description = "延迟关联优化的分页查询，支持年龄范围筛选")
public ResponseEntity<IPage<UserInfo>> getUserPage(
@Parameter(description = "页码", example = "1") @RequestParam(defaultValue = "1") Integer pageNum,
@Parameter(description = "每页条数", example = "20") @RequestParam(defaultValue = "20") Integer pageSize,
@Parameter(description = "最小年龄", example = "18") @RequestParam Integer minAge,
@Parameter(description = "最大年龄", example = "30") @RequestParam Integer maxAge
) {
IPage<UserInfo> pageResult = userInfoService.getUserPage(pageNum, pageSize, minAge, maxAge);
return ResponseEntity.ok(pageResult);
}
@PostMapping("/batch/save")
@Operation(summary = "批量保存用户信息", description = "分批批量插入，提升写入性能")
public ResponseEntity<Boolean> batchSaveUserInfo(@RequestBody List<UserInfo> userInfoList) {
Boolean result = userInfoService.batchSaveUserInfo(userInfoList);
return ResponseEntity.ok(result);
}
@GetMapping("/{id}")
@Operation(summary = "根据ID查询用户信息", description = "主键查询，性能最优")
public ResponseEntity<UserInfo> getUserById(@Parameter(description = "用户ID", example = "1") @PathVariable Long id) {
if (id == null || id < 1) {
return ResponseEntity.badRequest().build();
}
UserInfo userInfo = userInfoService.getById(id);
return ResponseEntity.ok(userInfo);
}
}
五、慢SQL监控与排查体系
优化的前提是发现问题，建立完善的慢SQL监控体系，是线上数据库稳定运行的核心保障。
5.1 MySQL慢查询日志开启
MySQL慢查询日志会记录执行时间超过阈值的SQL，是发现慢SQL的核心手段，MySQL 8.0配置方式：
# my.cnf 配置文件
slow_query_log = ON
long_query_time = 1
log_queries_not_using_indexes = ON
log_output = FILE
slow_query_log_file = /var/log/mysql/slow.log
配置修改后，无需重启MySQL，执行以下命令即可生效：
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
5.2 慢查询日志分析工具
- mysqldumpslow：MySQL自带的慢日志分析工具，可快速统计慢SQL的执行次数、平均耗时、扫描行数等
# 统计耗时最长的10条SQL
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 统计访问次数最多的10条SQL
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
- pt-query-digest：Percona Toolkit提供的慢日志分析工具，功能更强大，可生成详细的分析报告，是生产环境的首选工具。
5.3 线上慢SQL排查流程
- 开启慢查询日志，定期收集慢SQL
- 用分析工具统计慢SQL的频次、耗时、扫描行数
- 用EXPLAIN/EXPLAIN ANALYZE分析执行计划，定位性能瓶颈
- 针对性优化索引、改写SQL、调整表结构
- 验证优化效果，对比优化前后的执行计划和耗时
- 落地规范，避免同类问题再次发生
六、常见优化误区纠正
- 索引越多越好：错误。索引会大幅降低INSERT、UPDATE、DELETE的性能，因为每次写入都需要维护所有索引的B+树，单表索引必须控制在5个以内。
- 主键索引一定比普通索引快：错误。如果普通索引是覆盖索引，无需回表查询，性能比主键索引更快。
- Using filesort就是磁盘排序：错误。只要无法利用索引完成排序，无论内存还是磁盘排序，都会显示Using filesort，只有当排序结果超过sort_buffer_size时，才会触发磁盘排序。
- COUNT(1)比COUNT(*)快：错误。MySQL 8.0中，COUNT()和COUNT(1)的性能几乎无差异，优化器会选择最小的辅助索引进行统计，COUNT()是SQL标准定义的统计行数的语法，优先使用。
- 联合索引的字段顺序无关紧要：错误。联合索引必须遵循最左前缀原则，区分度高的字段放在左侧，否则会导致索引失效。
- IN子查询一定比JOIN慢：错误。MySQL 8.0的优化器已经能很好的优化IN子查询，会自动转换为JOIN执行，性能与JOIN无差异，NOT IN子查询除外。
SQL性能优化的核心，不是盲目加索引，而是理解MySQL的底层执行逻辑，读懂执行计划，从业务场景出发，设计合理的表结构和索引，编写符合规范的SQL。 优化的最高境界，是减少数据库的访问，比如通过缓存、冗余字段、业务逻辑优化，从根源上避免慢SQL的产生。只有把底层逻辑吃透，才能以不变应万变，搞定所有线上SQL性能问题。
