前言日常开发中大部分接口慢、数据库压力大根源都出自 SQL 写法不规范而涉及数据增删改、资金、库存等核心业务还必须依靠数据库事务保障数据安全。本文整合常用 SQL 优化技巧 事务知识点包含基础建表语句、错误写法、优化写法、代码实战、课后练习所有内容均可直接在 MySQL、Java 项目中运行测试。一、基础测试数据表统一环境先执行以下 SQL 创建测试表后续所有案例、练习均基于这两张表。-- 用户表CREATETABLEuser(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(20),phoneVARCHAR(11),addressVARCHAR(100));-- 订单表CREATETABLEorder(order_idINTPRIMARYKEYAUTO_INCREMENT,user_idINT,order_nameVARCHAR(30),create_timeDATETIME);二、优化一禁止使用 SELECT *按需查询字段问题说明SELECT *会查询表中所有字段不仅增加网络传输、内存开销还会导致索引失效、无法走索引覆盖数据量越大影响越明显。低级写法不推荐SELECT*FROMuser;高级优化写法推荐只查询业务真正需要的字段精简数据返回。SELECTid,nameFROMuser;三、优化二模糊查询索引优化避免左模糊、全模糊问题说明MySQL 索引遵循最左前缀原则%关键词、%关键词%会直接导致索引失效触发全表扫描业务允许场景下优先使用关键词%前缀模糊匹配。低级写法索引失效全表扫描-- 左右都带 %无法走索引SELECT*FROMuserWHEREnameLIKE%李%;高级优化写法正常命中索引-- 前缀匹配可正常使用索引SELECT*FROMuserWHEREnameLIKE李%;四、优化三嵌套子查询改为 JOIN / EXISTS 关联查询问题说明IN 子查询在数据量大时效率较低MySQL 解析嵌套子查询性能差多表关联场景优先使用JOIN联表查询执行效率更高、可读性更强。低级写法嵌套 IN 子查询SELECT*FROMuserWHEREidIN(SELECTuser_idFROMorder);高级优化写法INNER JOIN 联表查询SELECTu.id,u.nameFROMuseruINNERJOINorderoONu.ido.user_id;补充说明加上DISTINCT去重防止一个用户多条订单导致姓名重复展示。五、优化四大批量数据更新采用分批更新防锁表问题说明直接全表UPDATE海量数据会锁表、锁行、拉高数据库 CPU影响线上正常业务。海量更新必须拆分批次小批量多次执行。低级写法全量更新线上严禁使用-- 一次性更新全表数据量大极易锁表、阻塞业务UPDATEorderSETorder_name已归档;高级优化写法分批更新根据主键 ID 范围拆分每次只更新一小部分数据执行后手动提交事务。-- 每次只更新 1000 条根据主键范围控制批次UPDATEorderSETorder_name已归档WHEREorder_idBETWEEN1AND1000;COMMIT;六、MySQL 事务详解核心数据安全保障6.1 什么是事务事务是数据库执行的一组 SQL 操作单元这一组 SQL 要么全部执行成功要么全部执行失败回滚不会出现半截执行的异常状态。典型场景转账、下单扣库存、资金核算、订单创建必须依靠事务保证数据一致性。6.2 事务四大特性ACID 面试必背原子性Atomicity事务内所有操作不可分割全部成功 或 全部回滚。一致性Consistency事务执行前后数据库整体数据状态保持合法一致。隔离性Isolation多个事务并发执行时彼此相互隔离互不干扰。持久性Durability事务提交后数据永久保存服务器宕机也不会丢失。6.3 MySQL 事务基本语法MySQL 默认自动提交事务每条 SQL 执行后立即生效。手动控制事务分为三步START TRANSACTION/BEGIN开启事务执行多条业务 SQLCOMMIT提交事务数据永久生效ROLLBACK回滚事务撤销所有操作恢复原样基础语法模板-- 1. 开启事务BEGIN;-- 2. 执行多条业务SQLsql1;sql2;-- 3. 正常执行 → 提交COMMIT;-- 出现异常 → 回滚-- ROLLBACK;6.4 实战案例模拟转账1准备测试表与数据-- 账户表CREATETABLEaccount(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(20)COMMENT账户名,moneyDECIMAL(10,2)COMMENT余额);-- 初始化数据INSERTINTOaccount(name,money)VALUES(张三,1000),(李四,1000);2不加事务存在数据风险需求张三转给李四 200 元。两条 SQL 中间若程序崩溃、数据库宕机会出现扣款成功、收款失败数据错乱。-- 危险写法无事务并发/异常会丢数据UPDATEaccountSETmoneymoney-200WHEREname张三;UPDATEaccountSETmoneymoney200WHEREname李四;3加事务标准安全写法两条 SQL 纳入同一个事务保证同时成功 / 同时回滚。-- 开启事务BEGIN;-- 第一步张三扣钱UPDATEaccountSETmoneymoney-200WHEREname张三;-- 第二步李四加钱UPDATEaccountSETmoneymoney200WHEREname李四;-- 全部执行正常提交事务数据永久生效COMMIT;4事务回滚演示模拟异常人为制造异常触发回滚所有操作全部撤销。BEGIN;UPDATEaccountSETmoneymoney-200WHEREname张三;-- 模拟中间出现异常、报错、程序中断-- 主动回滚撤销所有操作ROLLBACK;-- 回滚后数据回到初始状态6.5 Java 代码中使用事务Spring 声明式事务实际开发中很少手写BEGIN/COMMIT/ROLLBACKSpring 提供注解式事务简洁易用。核心注解Transactional加在类 / 方法上开启事务管理方法正常执行 → 自动提交方法抛出异常 → 自动回滚完整业务代码转账案例importorg.springframework.stereotype.Service;importorg.springframework.transaction.annotation.Transactional;importjavax.annotation.Resource;ServicepublicclassAccountService{ResourceprivateAccountMapperaccountMapper;/** * 转账业务加上事务注解保证原子性 * rollbackFor Exception.class 代表所有异常都触发回滚 */Transactional(rollbackForException.class)publicvoidtransfer(StringfromUser,StringtoUser,Doubleamount){// 扣转出账户余额accountMapper.subMoney(fromUser,amount);// 模拟代码异常测试事务回滚// int i 1 / 0;// 转入账户加余额accountMapper.addMoney(toUser,amount);}}Mapper 层代码importorg.apache.ibatis.annotations.Param;importorg.apache.ibatis.annotations.Update;publicinterfaceAccountMapper{// 扣余额Update(UPDATE account SET money money - #{amount} WHERE name #{name})intsubMoney(Param(name)Stringname,Param(amount)Doubleamount);// 加余额Update(UPDATE account SET money money #{amount} WHERE name #{name})intaddMoney(Param(name)Stringname,Param(amount)Doubleamount);}6.6 事务隔离级别并发场景MySQL 共 4 种隔离级别用来解决并发事务产生的脏读、不可重复读、幻读问题级别由低到高读未提交Read Uncommitted可读到其他事务未提交数据生产环境基本不使用。读已提交Read Committed只能读到其他事务已提交的数据解决脏读。可重复读Repeatable ReadMySQL InnoDB 默认级别同一个事务内多次读取结果一致解决脏读、不可重复读。串行化Serializable最高隔离级别事务完全串行执行无并发问题性能最差。查看/修改隔离级别-- 查看当前隔离级别showvariablesliketx_isolation;-- 设置会话隔离级别SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;6.7 开发常见事务问题 避坑事务失效场景方法使用private修饰AOP 无法拦截同类中方法内部调用Transactional注解方法异常被try-catch捕获且没有向外抛出大事务问题事务包裹过多 SQL、查询、网络请求事务执行时间过长会引发锁等待、死锁、数据库性能下降。优化精简事务范围仅将核心写操作放入事务。长事务规避批量操作、大量查询逻辑不要放在事务中合理拆分业务。七、整体总结7.1 SQL 优化要点杜绝SELECT *按需查询字段减少数据传输与内存消耗模糊查询优先使用前缀匹配避免左模糊、全模糊导致索引失效多表查询放弃嵌套子查询优先使用JOIN联表提升执行效率海量数据更新必须分批执行防止锁表、阻塞线上业务。7.2 事务核心要点事务核心作用保证一组操作原子性防止数据错乱四大特性 ACID 是面试高频考点简单场景用原生 SQL 手动事务项目开发统一使用 SpringTransactional线上优先使用读已提交 / 可重复读隔离级别重点避坑警惕事务失效、大事务、异常捕获不当等问题。7.3 业务技术串联在电商、金融等项目中整套技术会组合使用下单、扣库存、生成订单 → 依靠事务保证数据一致高并发场景 → 搭配 Redis 分布式锁控制并发异步通知、流量削峰 → 使用 RocketMQ 消息队列接口查询提速 → 依赖索引 本文 SQL 优化规范。