前言

日常开发中大部分接口慢、数据库压力大，根源都出自 SQL 写法不规范；而涉及数据增删改、资金、库存等核心业务，还必须依靠数据库事务保障数据安全。
本文整合常用 SQL 优化技巧 + 事务知识点，包含基础建表语句、错误写法、优化写法、代码实战、课后练习，所有内容均可直接在 MySQL、Java 项目中运行测试。

一、基础测试数据表（统一环境）

先执行以下 SQL 创建测试表，后续所有案例、练习均基于这两张表。

-- 用户表
CREATE TABLE `user` (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20),
  phone VARCHAR(11),
  address VARCHAR(100)
);

-- 订单表
CREATE TABLE `order` (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  order_name VARCHAR(30),
  create_time DATETIME
);

二、优化一：禁止使用 SELECT *，按需查询字段

问题说明

SELECT * 会查询表中所有字段，不仅增加网络传输、内存开销，还会导致索引失效、无法走索引覆盖，数据量越大影响越明显。

低级写法（不推荐）

SELECT * FROM user;

高级优化写法（推荐）

只查询业务真正需要的字段，精简数据返回。

SELECT id, name FROM user;

三、优化二：模糊查询索引优化（避免左模糊、全模糊）

问题说明

MySQL 索引遵循最左前缀原则，%关键词、%关键词% 会直接导致索引失效，触发全表扫描；业务允许场景下优先使用 关键词% 前缀模糊匹配。

低级写法（索引失效，全表扫描）

-- 左右都带 %，无法走索引
SELECT * FROM user WHERE name LIKE '%李%';

高级优化写法（正常命中索引）

-- 前缀匹配，可正常使用索引
SELECT * FROM user WHERE name LIKE '李%';

四、优化三：嵌套子查询改为 JOIN / EXISTS 关联查询

问题说明

IN + 子查询 在数据量大时效率较低，MySQL 解析嵌套子查询性能差；多表关联场景优先使用 JOIN 联表查询，执行效率更高、可读性更强。

低级写法（嵌套 IN 子查询）

SELECT * FROM user 
WHERE id IN (SELECT user_id FROM `order`);

高级优化写法（INNER JOIN 联表查询）

SELECT u.id, u.name 
FROM user u
INNER JOIN `order` o ON u.id = o.user_id;

补充说明：加上 DISTINCT 去重，防止一个用户多条订单导致姓名重复展示。

五、优化四：大批量数据更新，采用分批更新（防锁表）

问题说明

直接全表 UPDATE 海量数据，会锁表、锁行、拉高数据库 CPU，影响线上正常业务。海量更新必须拆分批次，小批量多次执行。

低级写法（全量更新，线上严禁使用）

-- 一次性更新全表，数据量大极易锁表、阻塞业务
UPDATE `order` SET order_name='已归档';

高级优化写法（分批更新）

根据主键 ID 范围拆分，每次只更新一小部分数据，执行后手动提交事务。

-- 每次只更新 1000 条，根据主键范围控制批次
UPDATE `order` SET order_name='已归档' 
WHERE order_id BETWEEN 1 AND 1000;
COMMIT;

六、MySQL 事务详解（核心数据安全保障）

6.1 什么是事务

事务是数据库执行的一组 SQL 操作单元，这一组 SQL 要么全部执行成功，要么全部执行失败回滚，不会出现半截执行的异常状态。
典型场景：转账、下单扣库存、资金核算、订单创建，必须依靠事务保证数据一致性。

6.2 事务四大特性（ACID 面试必背）

1. 原子性（Atomicity）
   事务内所有操作不可分割，全部成功 或 全部回滚。
2. 一致性（Consistency）
   事务执行前后，数据库整体数据状态保持合法一致。
3. 隔离性（Isolation）
   多个事务并发执行时，彼此相互隔离，互不干扰。
4. 持久性（Durability）
   事务提交后，数据永久保存，服务器宕机也不会丢失。

6.3 MySQL 事务基本语法

MySQL 默认自动提交事务，每条 SQL 执行后立即生效。手动控制事务分为三步：

1. START TRANSACTION / BEGIN：开启事务
2. 执行多条业务 SQL
3. COMMIT：提交事务（数据永久生效）
4. ROLLBACK：回滚事务（撤销所有操作，恢复原样）

基础语法模板

-- 1. 开启事务
BEGIN; 

-- 2. 执行多条业务SQL
sql1;
sql2;

-- 3. 正常执行 → 提交
COMMIT;

-- 出现异常 → 回滚
-- ROLLBACK;

6.4 实战案例：模拟转账

1）准备测试表与数据

-- 账户表
CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) COMMENT '账户名',
    money DECIMAL(10,2) COMMENT '余额'
);

-- 初始化数据
INSERT INTO account(name, money) VALUES 
('张三', 1000),
('李四', 1000);

2）不加事务（存在数据风险）

需求：张三转给李四 200 元。两条 SQL 中间若程序崩溃、数据库宕机，会出现扣款成功、收款失败，数据错乱。

-- 危险写法：无事务，并发/异常会丢数据
UPDATE account SET money = money - 200 WHERE name = '张三';
UPDATE account SET money = money + 200 WHERE name = '李四';

3）加事务（标准安全写法）

两条 SQL 纳入同一个事务，保证同时成功 / 同时回滚。

-- 开启事务
BEGIN;

-- 第一步：张三扣钱
UPDATE account SET money = money - 200 WHERE name = '张三';

-- 第二步：李四加钱
UPDATE account SET money = money + 200 WHERE name = '李四';

-- 全部执行正常，提交事务，数据永久生效
COMMIT;

4）事务回滚演示（模拟异常）

人为制造异常，触发回滚，所有操作全部撤销。

BEGIN;

UPDATE account SET money = money - 200 WHERE name = '张三';

-- 模拟中间出现异常、报错、程序中断
-- 主动回滚，撤销所有操作
ROLLBACK;

-- 回滚后，数据回到初始状态

6.5 Java 代码中使用事务（Spring 声明式事务）

实际开发中很少手写 BEGIN/COMMIT/ROLLBACK，Spring 提供注解式事务，简洁易用。

核心注解

• @Transactional：加在类 / 方法上，开启事务管理
• 方法正常执行 → 自动提交
• 方法抛出异常 → 自动回滚

完整业务代码（转账案例）

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resource;

@Service
public class AccountService {

    @Resource
    private AccountMapper accountMapper;

    /**
     * 转账业务：加上事务注解，保证原子性
     * rollbackFor = Exception.class 代表所有异常都触发回滚
     */
    @Transactional(rollbackFor = Exception.class)
    public void transfer(String fromUser, String toUser, Double amount) {
        // 扣转出账户余额
        accountMapper.subMoney(fromUser, amount);

        // 模拟代码异常，测试事务回滚
        // int i = 1 / 0;

        // 转入账户加余额
        accountMapper.addMoney(toUser, amount);
    }
}

Mapper 层代码

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Update;

public interface AccountMapper {

    // 扣余额
    @Update("UPDATE account SET money = money - #{amount} WHERE name = #{name}")
    int subMoney(@Param("name") String name, @Param("amount") Double amount);

    // 加余额
    @Update("UPDATE account SET money = money + #{amount} WHERE name = #{name}")
    int addMoney(@Param("name") String name, @Param("amount") Double amount);
}

6.6 事务隔离级别（并发场景）

MySQL 共 4 种隔离级别，用来解决并发事务产生的脏读、不可重复读、幻读问题，级别由低到高：

1. 读未提交（Read Uncommitted）
   可读到其他事务未提交数据，生产环境基本不使用。
2. 读已提交（Read Committed）
   只能读到其他事务已提交的数据，解决脏读。
3. 可重复读（Repeatable Read） MySQL InnoDB 默认级别
   同一个事务内，多次读取结果一致，解决脏读、不可重复读。
4. 串行化（Serializable）
   最高隔离级别，事务完全串行执行，无并发问题，性能最差。

查看/修改隔离级别

-- 查看当前隔离级别
show variables like 'tx_isolation';

-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

6.7 开发常见事务问题 & 避坑

1. 事务失效场景
   • 方法使用 private 修饰（AOP 无法拦截）
   • 同类中方法内部调用 @Transactional 注解方法
   • 异常被 try-catch 捕获且没有向外抛出
2. 大事务问题
   事务包裹过多 SQL、查询、网络请求，事务执行时间过长，会引发锁等待、死锁、数据库性能下降。
   优化：精简事务范围，仅将核心写操作放入事务。
3. 长事务规避
   批量操作、大量查询逻辑不要放在事务中，合理拆分业务。

七、整体总结

7.1 SQL 优化要点

1. 杜绝 SELECT *，按需查询字段，减少数据传输与内存消耗；
2. 模糊查询优先使用前缀匹配，避免左模糊、全模糊导致索引失效；
3. 多表查询放弃嵌套子查询，优先使用 JOIN 联表，提升执行效率；
4. 海量数据更新必须分批执行，防止锁表、阻塞线上业务。

7.2 事务核心要点

1. 事务核心作用：保证一组操作原子性，防止数据错乱；
2. 四大特性 ACID 是面试高频考点；
3. 简单场景用原生 SQL 手动事务，项目开发统一使用 Spring @Transactional；
4. 线上优先使用 读已提交 / 可重复读 隔离级别；
5. 重点避坑：警惕事务失效、大事务、异常捕获不当等问题。

7.3 业务技术串联

在电商、金融等项目中，整套技术会组合使用：

1. 下单、扣库存、生成订单 → 依靠事务保证数据一致；
2. 高并发场景 → 搭配 Redis 分布式锁控制并发；
3. 异步通知、流量削峰 → 使用 RocketMQ 消息队列；
4. 接口查询提速 → 依赖索引 + 本文 SQL 优化规范。