1. 为什么选择Spring整合MyBatis在Java企业级应用开发中持久层框架的选择往往决定了项目的开发效率和后期维护成本。MyBatis作为半自动化的ORM框架与Spring生态的整合已经成为现代Java开发的标配组合。这种组合方式完美结合了Spring的依赖注入优势和MyBatis的SQL灵活性。我经历过多个从Hibernate迁移到MyBatis的项目最大的感受是对于复杂业务系统特别是需要精细控制SQL性能的场景MyBatis提供的原生SQL控制能力是无可替代的。Spring的IoC容器则让MyBatis的配置和使用变得更加优雅。注意虽然JPA规范日趋完善但在需要复杂查询、存储过程调用或数据库特定功能时MyBatis仍然是更灵活的选择。1.1 技术选型对比让我们先看一个简单的对比表格了解不同持久层方案的特点特性纯JDBCHibernateMyBatisSpring Data JPASQL控制度完全控制几乎无控制完全控制有限控制学习曲线陡峭中等平缓平缓性能优化空间极大有限极大有限与Spring集成难度高低低极低适合场景遗留系统CRUD密集型复杂查询快速开发从实际项目经验来看MyBatis特别适合以下场景需要复用现有复杂SQL的场景需要调用存储过程的项目对性能有极致要求的核心业务模块数据库设计不符合JPA规范的遗留系统1.2 版本兼容性考量在开始整合前版本匹配是首要考虑的问题。根据我的踩坑经验不同版本的组合可能会带来意想不到的问题Spring 5.x MyBatis 3.5 是最稳定的组合如果使用Spring Boot2.7.x版本对MyBatis的支持最成熟要特别注意MyBatis-Spring桥接包的版本它必须与MyBatis核心版本匹配我曾经在一个项目中使用Spring Boot 2.4 MyBatis 3.4.6结果遇到了事务管理不生效的问题。后来发现是桥接包版本不兼容导致的。这个教训告诉我版本匹配表必须作为项目启动时的第一份技术文档。2. 环境准备与基础配置2.1 Maven依赖配置对于Maven项目pom.xml中需要配置以下核心依赖!-- Spring Boot Starter -- dependency groupIdorg.springframework.boot/groupId artifactIdspring-boot-starter/artifactId version2.7.12/version /dependency !-- MyBatis核心 -- dependency groupIdorg.mybatis/groupId artifactIdmybatis/artifactId version3.5.6/version /dependency !-- MyBatis-Spring桥接 -- dependency groupIdorg.mybatis/groupId artifactIdmybatis-spring/artifactId version2.0.6/version /dependency !-- Spring JDBC (事务管理需要) -- dependency groupIdorg.springframework/groupId artifactIdspring-jdbc/artifactId version5.3.23/version /dependency !-- 数据库驱动 (以MySQL为例) -- dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId version8.0.28/version /dependency在实际项目中我通常会额外添加这些实用依赖MyBatis Plus增强工具包PageHelper分页插件Druid高性能连接池2.2 数据源配置Spring Boot的application.yml中配置数据源spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/mybatis_demo?useSSLfalseserverTimezoneUTC username: root password: 123456 type: com.alibaba.druid.pool.DruidDataSource # Druid连接池专用配置 druid: initial-size: 5 min-idle: 5 max-active: 20 max-wait: 60000 time-between-eviction-runs-millis: 60000 min-evictable-idle-time-millis: 300000 validation-query: SELECT 1 FROM DUAL test-while-idle: true test-on-borrow: false test-on-return: false经验分享Druid的连接池监控页面非常实用可以通过以下配置开启spring: datasource: druid: stat-view-servlet: enabled: true url-pattern: /druid/* login-username: admin login-password: admin2.3 MyBatis配置类创建MyBatis的Spring配置类Configuration MapperScan(com.example.mapper) // 指定Mapper接口所在包 public class MyBatisConfig { Autowired private DataSource dataSource; Bean public SqlSessionFactory sqlSessionFactory() throws Exception { SqlSessionFactoryBean factoryBean new SqlSessionFactoryBean(); factoryBean.setDataSource(dataSource); // 配置XML文件位置 factoryBean.setMapperLocations( new PathMatchingResourcePatternResolver() .getResources(classpath:mapper/*.xml)); // 添加自定义配置 org.apache.ibatis.session.Configuration configuration new org.apache.ibatis.session.Configuration(); configuration.setMapUnderscoreToCamelCase(true); // 开启驼峰命名转换 configuration.setDefaultFetchSize(100); // 设置默认获取数量 configuration.setDefaultStatementTimeout(30); // 设置超时时间 factoryBean.setConfiguration(configuration); return factoryBean.getObject(); } Bean public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) { return new SqlSessionTemplate(sqlSessionFactory); } }我曾经在一个项目中因为没有设置defaultStatementTimeout导致某些复杂查询长时间运行却不超时最终拖垮了整个数据库。这个配置项对于生产环境至关重要。3. Mapper开发全流程3.1 接口与XML的对应关系MyBatis的核心设计理念就是将Java接口与XML映射文件绑定。这种设计带来了极大的灵活性但也容易因配置不当导致各种问题。正确的对应关系应该遵循这些规则接口全限定名与XML的namespace完全一致方法名与XML中的id属性一致参数类型与parameterType匹配返回类型与resultType/resultMap匹配一个典型的Mapper接口示例public interface UserMapper { Select(SELECT * FROM users WHERE id #{id}) User selectById(Param(id) Long id); ListUser selectByCondition(UserQuery query); Options(useGeneratedKeys true, keyProperty id) Insert(INSERT INTO users(name,email) VALUES(#{name},#{email})) int insert(User user); Update(UPDATE users SET name#{name}, email#{email} WHERE id#{id}) int update(User user); Delete(DELETE FROM users WHERE id#{id}) int delete(Long id); }对应的XML映射文件resources/mapper/UserMapper.xml?xml version1.0 encodingUTF-8? !DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd mapper namespacecom.example.mapper.UserMapper resultMap iduserResultMap typecom.example.entity.User id propertyid columnid/ result propertyname columnname/ result propertyemail columnemail/ result propertycreateTime columncreate_time/ /resultMap select idselectByCondition resultMapuserResultMap SELECT * FROM users where if testname ! null and name ! AND name LIKE CONCAT(%, #{name}, %) /if if testemail ! null AND email #{email} /if if testcreateTimeStart ! null AND create_time #{createTimeStart} /if if testcreateTimeEnd ! null AND create_time #{createTimeEnd} /if /where ORDER BY id DESC /select /mapper3.2 动态SQL技巧MyBatis强大的动态SQL能力是其核心竞争力之一。以下是几种常用的动态SQL写法条件查询where ifselect idsearchUsers resultTypeUser SELECT * FROM users where if testname ! null name #{name} /if if testage ! null AND age #{age} /if /where /select选择更新set ifupdate idupdateUser parameterTypeUser UPDATE users set if testname ! nullname#{name},/if if testemail ! nullemail#{email},/if /set WHERE id#{id} /update批量插入foreachinsert idbatchInsert parameterTypejava.util.List INSERT INTO users (name, email) VALUES foreach collectionlist itemuser separator, (#{user.name}, #{user.email}) /foreach /insert多条件选择choose/when/otherwiseselect idselectByStatus resultTypeUser SELECT * FROM users where choose when teststatus active AND active 1 /when when teststatus inactive AND active 0 /when otherwise AND active IS NOT NULL /otherwise /choose /where /select避坑指南在foreach中使用集合参数时务必注意collection属性的取值单参数List类型collectionlist单参数数组类型collectionarray多参数使用Param注解collection参数名3.3 结果映射高级技巧MyBatis的结果映射(resultMap)功能非常强大可以处理各种复杂的映射场景。一对一关联查询resultMap idorderWithUserMap typeOrder id propertyid columnorder_id/ result propertyorderNo columnorder_no/ !-- 关联用户对象 -- association propertyuser javaTypeUser id propertyid columnuser_id/ result propertyname columnuser_name/ /association /resultMap select idselectOrderWithUser resultMaporderWithUserMap SELECT o.id as order_id, o.order_no, u.id as user_id, u.name as user_name FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE o.id #{id} /select一对多关联查询resultMap iduserWithOrdersMap typeUser id propertyid columnid/ result propertyname columnname/ !-- 订单集合 -- collection propertyorders ofTypeOrder id propertyid columnorder_id/ result propertyorderNo columnorder_no/ /collection /resultMap select idselectUserWithOrders resultMapuserWithOrdersMap SELECT u.id, u.name, o.id as order_id, o.order_no FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE u.id #{id} /select嵌套结果映射resultMap iddetailedOrderMap typeOrder id propertyid columnid/ association propertyuser resultMapuserMap/ collection propertyitems resultMapitemMap/ /resultMap resultMap iduserMap typeUser id propertyid columnuser_id/ result propertyname columnuser_name/ /resultMap resultMap iditemMap typeOrderItem id propertyid columnitem_id/ result propertyproductName columnproduct_name/ /resultMap枚举类型处理public enum UserStatus { ACTIVE(1), INACTIVE(0), LOCKED(-1); private final int code; UserStatus(int code) { this.code code; } public int getCode() { return code; } public static UserStatus fromCode(int code) { for (UserStatus status : values()) { if (status.code code) { return status; } } throw new IllegalArgumentException(未知状态码: code); } }resultMap iduserResultMap typeUser result propertystatus columnstatus typeHandlerorg.apache.ibatis.type.EnumOrdinalTypeHandler/ /resultMap4. 高级特性与性能优化4.1 插件开发实战MyBatis的插件机制允许我们在SQL执行的各个阶段插入自定义逻辑。最常见的插件应用场景包括SQL性能监控分页处理敏感数据加解密审计字段自动填充下面是一个完整的SQL执行时间统计插件实现Intercepts({ Signature(type StatementHandler.class, method query, args {Statement.class, ResultHandler.class}), Signature(type StatementHandler.class, method update, args {Statement.class}), Signature(type StatementHandler.class, method batch, args {Statement.class}) }) public class SqlCostTimeInterceptor implements Interceptor { private static final Logger logger LoggerFactory.getLogger(SqlCostTimeInterceptor.class); Override public Object intercept(Invocation invocation) throws Throwable { long startTime System.currentTimeMillis(); try { return invocation.proceed(); } finally { long costTime System.currentTimeMillis() - startTime; StatementHandler statementHandler (StatementHandler) invocation.getTarget(); String sql statementHandler.getBoundSql().getSql(); if (costTime 200) { logger.warn(SQL执行耗时: {}ms - {}, costTime, sql); } else { logger.debug(SQL执行耗时: {}ms - {}, costTime, sql); } } } Override public Object plugin(Object target) { return Plugin.wrap(target, this); } Override public void setProperties(Properties properties) { // 可以读取配置参数 } }注册插件到MyBatis配置Bean public SqlCostTimeInterceptor sqlCostTimeInterceptor() { return new SqlCostTimeInterceptor(); } Bean public ConfigurationCustomizer configurationCustomizer() { return configuration - { configuration.addInterceptor(sqlCostTimeInterceptor()); }; }4.2 二级缓存配置MyBatis的二级缓存可以显著提升查询性能但使用不当会导致脏读问题。以下是安全使用二级缓存的配置方式!-- 在mybatis-config.xml中 -- settings setting namecacheEnabled valuetrue/ /settings !-- 在Mapper.xml中 -- cache evictionLRU flushInterval60000 size512 readOnlytrue/实际项目中我建议只在读多写少的场景使用二级缓存设置合理的flushInterval如5分钟避免在分布式环境中使用本地缓存对于关键业务数据考虑手动控制缓存清除4.3 批量操作优化批量操作是性能优化的重点。以下是几种批量处理的正确姿势方式一foreach批量插入public interface UserMapper { void batchInsert(Param(list) ListUser users); }insert idbatchInsert INSERT INTO users (name, email) VALUES foreach collectionlist itemuser separator, (#{user.name}, #{user.email}) /foreach /insert方式二BATCH执行器// 获取批量模式的SqlSession SqlSession sqlSession sqlSessionFactory.openSession(ExecutorType.BATCH); try { UserMapper mapper sqlSession.getMapper(UserMapper.class); for (User user : userList) { mapper.insert(user); } sqlSession.commit(); } finally { sqlSession.close(); }方式三rewriteBatchedStatements在JDBC连接字符串中添加rewriteBatchedStatementstrue可以显著提升MySQL批量插入性能jdbc:mysql://localhost:3306/db?rewriteBatchedStatementstrue性能测试数据在插入10000条记录时普通插入耗时约12秒批量模式仅需1.5秒而rewriteBatchedStatements批量模式仅需0.8秒。4.4 分页查询实现分页是Web应用中最常见的需求之一。以下是几种分页方案对比1. 使用PageHelper插件// 引入依赖 dependency groupIdcom.github.pagehelper/groupId artifactIdpagehelper-spring-boot-starter/artifactId version1.4.1/version /dependency // 使用示例 PageHelper.startPage(1, 10); // 第1页每页10条 ListUser users userMapper.selectAll(); PageInfoUser pageInfo new PageInfo(users);2. 手动实现分页select idselectByPage resultTypeUser SELECT * FROM users ORDER BY id LIMIT #{offset}, #{pageSize} /select3. 使用MyBatis-Plus分页// 配置分页插件 Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); return interceptor; } // 使用示例 PageUser page new Page(1, 10); userMapper.selectPage(page, null); ListUser records page.getRecords();5. 生产环境最佳实践5.1 SQL注入防护虽然MyBatis使用预编译语句可以有效防止SQL注入但不当使用仍然存在风险危险写法$符号直接拼接select idselectByOrder resultTypeUser SELECT * FROM users ORDER BY ${columnName} /select安全写法使用#{}或白名单校验// 在Java代码中校验排序字段 private static final SetString ALLOWED_SORT_COLUMNS Set.of(id, name, email, create_time); public ListUser selectUsers(String sortBy) { if (!ALLOWED_SORT_COLUMNS.contains(sortBy)) { sortBy id; } return userMapper.selectByOrder(sortBy); }select idselectByOrder resultTypeUser SELECT * FROM users ORDER BY ${columnName} !-- 注意即使使用白名单校验$仍然有风险最好在应用层排序 -- /select5.2 事务管理Spring与MyBatis整合后可以使用Spring的声明式事务管理Service public class UserService { Autowired private UserMapper userMapper; Transactional(rollbackFor Exception.class) public void updateUser(User user) { // 业务逻辑... userMapper.update(user); // 更多数据库操作... } Transactional(propagation Propagation.REQUIRES_NEW) public void logOperation(OperationLog log) { // 独立事务记录日志 } }常见事务问题排查确保使用的是Spring代理对象调用事务方法的类不能被final修饰检查异常是否被捕获未抛出默认只回滚RuntimeException确认数据源是否配置了事务管理器检查Transactional是否添加在public方法上5.3 监控与调优生产环境必备的监控措施1. Druid监控spring: datasource: druid: filter: stat: enabled: true log-slow-sql: true slow-sql-millis: 1000 web-stat-filter: enabled: true aop-patterns: com.example.mapper.*2. 慢SQL日志!-- mybatis-config.xml -- settings setting namelogImpl valueSLF4J/ /settings !-- logback.xml -- logger nameorg.mybatis levelDEBUG/ logger namejava.sql levelDEBUG/3. 连接池监控指标RestController public class DruidStatController { Autowired private DataSource dataSource; GetMapping(/druid/stat) public Object druidStat() { DruidDataSource druidDataSource (DruidDataSource) dataSource; MapString, Object stat new HashMap(); stat.put(activeCount, druidDataSource.getActiveCount()); stat.put(activePeak, druidDataSource.getActivePeak()); stat.put(poolingCount, druidDataSource.getPoolingCount()); stat.put(connectCount, druidDataSource.getConnectCount()); stat.put(waitThreadCount, druidDataSource.getWaitThreadCount()); return stat; } }5.4 常见问题解决方案问题1Mapper接口无法注入检查MapperScan是否配置正确确认Mapper接口是否有Repository或Mapper注解检查XML文件是否在classpath对应路径问题2字段值为null检查数据库字段名与Java属性名是否匹配开启mapUnderscoreToCamelCase确认resultMap配置是否正确检查Getter/Setter方法是否存在问题3事务不生效确认是否抛出了RuntimeException检查是否调用了同类中的其他方法自调用问题确认数据源是否配置了事务管理器问题4分页插件冲突检查是否引入了多个分页插件如PageHelper和MyBatis-Plus分页确认插件执行顺序是否正确问题5XML特殊字符处理!-- 错误写法 -- select idselectActiveUsers SELECT * FROM users WHERE status 1 AND age 30 /select !-- 正确写法 -- select idselectActiveUsers SELECT * FROM users WHERE status 1 AND age lt; 30 /select !-- 或者使用CDATA -- select idselectActiveUsers ![CDATA[ SELECT * FROM users WHERE status 1 AND age 30 ]] /select6. 扩展与集成方案6.1 与MyBatis-Plus整合MyBatis-Plus是MyBatis的增强工具提供了大量开箱即用的功能// 基础Mapper接口 public interface UserMapper extends BaseMapperUser { // 已经内置了基本的CRUD方法 } // 服务层示例 Service public class UserService { Autowired private UserMapper userMapper; public ListUser selectByCondition(UserQuery query) { QueryWrapperUser wrapper new QueryWrapper(); wrapper.like(StringUtils.isNotBlank(query.getName()), name, query.getName()) .eq(query.getStatus() ! null, status, query.getStatus()) .between(query.getStartTime() ! null query.getEndTime() ! null, create_time, query.getStartTime(), query.getEndTime()); return userMapper.selectList(wrapper); } }6.2 多数据源配置大型项目往往需要访问多个数据源Configuration MapperScan(basePackages com.example.primary.mapper, sqlSessionFactoryRef primarySqlSessionFactory) public class PrimaryDataSourceConfig { Bean ConfigurationProperties(spring.datasource.primary) public DataSource primaryDataSource() { return DruidDataSourceBuilder.create().build(); } Bean public SqlSessionFactory primarySqlSessionFactory( Qualifier(primaryDataSource) DataSource dataSource) throws Exception { SqlSessionFactoryBean factoryBean new SqlSessionFactoryBean(); factoryBean.setDataSource(dataSource); return factoryBean.getObject(); } Bean public DataSourceTransactionManager primaryTransactionManager( Qualifier(primaryDataSource) DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } } // 第二个数据源配置类似使用不同的包扫描路径和Bean名称6.3 代码生成器配置MyBatis Generator可以自动生成基础代码!-- generatorConfig.xml -- context idmysql targetRuntimeMyBatis3 jdbcConnection driverClasscom.mysql.cj.jdbc.Driver connectionURLjdbc:mysql://localhost:3306/mybatis_demo userIdroot password123456/ javaModelGenerator targetPackagecom.example.entity targetProjectsrc/main/java/ sqlMapGenerator targetPackagemapper targetProjectsrc/main/resources/ javaClientGenerator typeXMLMAPPER targetPackagecom.example.mapper targetProjectsrc/main/java/ table tableNameuser domainObjectNameUser/ table tableNameorder domainObjectNameOrder/ /context运行生成器public class MyBatisGenerator { public static void main(String[] args) throws Exception { ListString warnings new ArrayList(); ConfigurationParser cp new ConfigurationParser(warnings); Configuration config cp.parseConfiguration( MyBatisGenerator.class.getResourceAsStream(/generatorConfig.xml)); DefaultShellCallback callback new DefaultShellCallback(true); MyBatisGenerator generator new MyBatisGenerator(config, callback, warnings); generator.generate(null); warnings.forEach(System.out::println); } }6.4 与Spring Boot深度整合Spring Boot提供了自动配置的MyBatis starter# application.yml mybatis: mapper-locations: classpath:mapper/**/*.xml type-aliases-package: com.example.entity configuration: map-underscore-to-camel-case: true default-fetch-size: 100 default-statement-timeout: 30自定义配置类Configuration public class MyBatisCustomConfig { Bean public ConfigurationCustomizer mybatisConfigurationCustomizer() { return configuration - { // 添加自定义类型处理器 configuration.getTypeHandlerRegistry() .register(MyEnumTypeHandler.class); // 添加插件 configuration.addInterceptor(new SqlCostTimeInterceptor()); }; } }7. 实战案例用户管理系统7.1 领域模型设计// 用户实体 Data public class User { private Long id; private String username; private String password; private String email; private UserStatus status; private Date createTime; private Date updateTime; } // 查询条件封装 Data public class UserQuery { private String username; private String email; private UserStatus status; private Date createTimeStart; private Date createTimeEnd; private Integer pageNum 1; private Integer pageSize 10; }7.2 核心Mapper实现public interface UserMapper { // 基础CRUD Select(SELECT * FROM user WHERE id #{id}) User selectById(Long id); Options(useGeneratedKeys true, keyProperty id) Insert(INSERT INTO user(username,password,email,status) VALUES(#{username},#{password},#{email},#{status})) int insert(User user); Update(UPDATE user SET username#{username},email#{email}, status#{status} WHERE id#{id}) int update(User user); Delete(DELETE FROM user WHERE id#{id}) int delete(Long id); // 条件查询 ListUser selectByCondition(UserQuery query); // 批量操作 int batchInsert(Param(list) ListUser users); // 关联查询 Select(SELECT u.* FROM user u JOIN user_role ur ON u.id ur.user_id WHERE ur.role_id #{roleId}) ListUser selectByRoleId(Long roleId); }对应的XML映射文件select idselectByCondition resultTypeUser SELECT * FROM user where if testusername ! null and username ! AND username LIKE CONCAT(%, #{username}, %) /if if testemail ! null and email ! AND email #{email} /if if teststatus ! null AND status #{status} /if if testcreateTimeStart ! null AND create_time #{createTimeStart} /if if testcreateTimeEnd ! null AND create_time #{createTimeEnd} /if /where ORDER BY id DESC LIMIT #{offset}, #{pageSize} /select7.3 服务层实现Service Transactional(readOnly true) public class UserService { Autowired private UserMapper userMapper; public PageInfoUser queryUsers(UserQuery query) { PageHelper.startPage(query.getPageNum(), query.getPageSize()); ListUser users userMapper.selectByCondition(query); return new PageInfo(users); } Transactional(rollbackFor Exception.class) public void createUser(User user) { user.setCreateTime(new Date()); user.setUpdateTime(new Date()); userMapper.insert(user); } Transactional(rollbackFor Exception.class) public void updateUser(User user) { user.setUpdateTime(new Date()); userMapper.update(user); } Transactional(rollbackFor Exception.class) public void deleteUser(Long id) { userMapper.delete(id); } Transactional(rollbackFor Exception.class) public void batchCreateUsers(ListUser users) { users.forEach(user - { user.setCreateTime(new Date()); user.setUpdateTime(new Date()); }); userMapper.batchInsert(users); } }7.4 控制器层RestController RequestMapping(/api/users) public class UserController { Autowired private UserService userService; GetMapping public ResultPageInfoUser listUsers(UserQuery query) { return Result.success(userService.queryUsers(query)); } PostMapping public ResultVoid createUser(RequestBody Valid User user) { userService.createUser(user); return Result.success(); } PutMapping(/{id}) public ResultVoid updateUser(PathVariable Long id, RequestBody Valid User user) { user.setId(id); userService.updateUser(user); return Result.success(); } DeleteMapping(/{id}) public ResultVoid deleteUser(PathVariable Long id) { userService.deleteUser(id); return Result.success(); } }8. 性能调优实战8.1 SQL执行计划分析通过EXPLAIN分析SQL性能public interface ExplainMapper { Select(EXPLAIN ${sql}) ListMapString, Object explain(Param(sql) String sql); } // 使用示例 public void analyzeQuery() { String sql SELECT * FROM user WHERE status 1 ORDER BY create_time DESC; ListMapString, Object result explainMapper.explain(sql); result.forEach(row - { System.out.println(id: row.get(id)); System.out.println(select_type: row.get(select_type)); System.out.println(table: row.get(table)); System.out.println(type: row.get(type)); System.out.println(possible_keys: row.get(possible_keys)); System.out.println(key: row.get(key)); System.out.println(rows: row.get(rows)); System.out.println(Extra: row.get(Extra)); }); }关键指标解读typeALL表示全表扫描应优化为range或refkey实际使用的索引rows预估扫描行数ExtraUsing filesort表示需要优化排序8.2 索引优化策略1. 复合索引设计-- 为常用查询条件创建复合索引 ALTER TABLE user ADD INDEX idx_status_create_time (status, create_time); -- 覆盖索引优化 ALTER TABLE user ADD INDEX idx_username_email (username, email);2. 索引使用要点遵循最左前缀原则避免在索引列上使用函数字符串字段考虑前缀索引区分度低的字段不适合建索引8.3 连接池优化Druid连接池关键参数调优spring: datasource: druid: # 初始连接数 initial-size: 10 # 最小空闲连接 min-idle: 10 # 最大活跃连接 max-active: 50 # 获取连接等待超时时间(毫秒) max-wait: 60000 # 配置间隔多久检测空闲连接(毫秒) time-between-eviction-runs-millis: 60000 # 连接最小生存时间(毫秒) min-evictable-idle-time-millis: 300000 # 测试连接有效性的SQL validation-query: SELECT 1 # 申请连接时执行validationQuery检测连接有效性 test-on-borrow: false # 归还连接时执行validationQuery检测连接有效性 test-on-return: false # 空闲时检测连接有效性 test-while-idle: true8.4 缓存策略优化**多级