Mybatis学习笔记(七)

Spring Boot集成

简要描述：MyBatis-Plus与Spring Boot的深度集成，提供了自动配置、启动器等特性，大大简化了配置和使用。

核心概念：

• 自动配置：基于条件的自动配置机制
• 启动器：简化依赖管理的starter
• 配置属性：通过application.yml进行配置
• 条件化配置：根据环境动态调整配置

SpringBoot自动配置

简要描述：Spring Boot通过自动配置机制，自动装配MyBatis-Plus相关的Bean，无需手动配置大量的XML或Java配置。

核心概念：

• @EnableAutoConfiguration：启用自动配置
• @ConditionalOn*：条件化配置注解
• AutoConfiguration类：自动配置类
• spring.factories：自动配置发现机制

自动配置原理：

// MyBatis-Plus自动配置类
@Configuration
@ConditionalOnClass({SqlSessionFactory.class, SqlSessionFactoryBean.class, MybatisPlusAutoConfiguration.class})
@ConditionalOnSingleCandidate(DataSource.class)
@EnableConfigurationProperties(MybatisPlusProperties.class)
@AutoConfigureAfter({DataSourceAutoConfiguration.class, MybatisPlusLanguageDriverAutoConfiguration.class})
public class MybatisPlusAutoConfiguration implements InitializingBean {private static final Logger logger = LoggerFactory.getLogger(MybatisPlusAutoConfiguration.class);private final MybatisPlusProperties properties;private final Interceptor[] interceptors;private final TypeHandler[] typeHandlers;private final LanguageDriver[] languageDrivers;private final ResourceLoader resourceLoader;private final DatabaseIdProvider databaseIdProvider;private final List<ConfigurationCustomizer> configurationCustomizers;private final List<MybatisPlusPropertiesCustomizer> mybatisPlusPropertiesCustomizers;private final ApplicationContext applicationContext;public MybatisPlusAutoConfiguration(MybatisPlusProperties properties,ObjectProvider<Interceptor[]> interceptorsProvider,ObjectProvider<TypeHandler[]> typeHandlersProvider,ObjectProvider<LanguageDriver[]> languageDriversProvider,ResourceLoader resourceLoader,ObjectProvider<DatabaseIdProvider> databaseIdProvider,ObjectProvider<List<ConfigurationCustomizer>> configurationCustomizersProvider,ObjectProvider<List<MybatisPlusPropertiesCustomizer>> mybatisPlusPropertiesCustomizerProvider,ApplicationContext applicationContext) {this.properties = properties;this.interceptors = interceptorsProvider.getIfAvailable();this.typeHandlers = typeHandlersProvider.getIfAvailable();this.languageDrivers = languageDriversProvider.getIfAvailable();this.resourceLoader = resourceLoader;this.databaseIdProvider = databaseIdProvider.getIfAvailable();this.configurationCustomizers = configurationCustomizersProvider.getIfAvailable();this.mybatisPlusPropertiesCustomizers = mybatisPlusPropertiesCustomizerProvider.getIfAvailable();this.applicationContext = applicationContext;}@Bean@ConditionalOnMissingBeanpublic SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {// 使用 MybatisSqlSessionFactoryBean 而不是 SqlSessionFactoryBeanMybatisSqlSessionFactoryBean factory = new MybatisSqlSessionFactoryBean();factory.setDataSource(dataSource);factory.setVfs(SpringBootVFS.class);if (StringUtils.hasText(this.properties.getConfigLocation())) {factory.setConfigLocation(this.resourceLoader.getResource(this.properties.getConfigLocation()));}applyConfiguration(factory);if (this.properties.getConfigurationProperties() != null) {factory.setConfigurationProperties(this.properties.getConfigurationProperties());}if (!ObjectUtils.isEmpty(this.interceptors)) {factory.setPlugins(this.interceptors);}if (this.databaseIdProvider != null) {factory.setDatabaseIdProvider(this.databaseIdProvider);}if (StringUtils.hasLength(this.properties.getTypeAliasesPackage())) {factory.setTypeAliasesPackage(this.properties.getTypeAliasesPackage());}if (this.properties.getTypeAliasesSuperType() != null) {factory.setTypeAliasesSuperType(this.properties.getTypeAliasesSuperType());}if (StringUtils.hasLength(this.properties.getTypeHandlersPackage())) {factory.setTypeHandlersPackage(this.properties.getTypeHandlersPackage());}if (!ObjectUtils.isEmpty(this.typeHandlers)) {factory.setTypeHandlers(this.typeHandlers);}Resource[] mapperLocations = this.properties.resolveMapperLocations();if (!ObjectUtils.isEmpty(mapperLocations)) {factory.setMapperLocations(mapperLocations);}// 修改源码支持定制化 GlobalConfigGlobalConfig globalConfig = this.properties.getGlobalConfig();//注入填充器this.getBeanThen(MetaObjectHandler.class, globalConfig::setMetaObjectHandler);//注入主键生成器this.getBeanThen(IKeyGenerator.class, i -> globalConfig.getDbConfig().setKeyGenerator(i));//注入sql注入器this.getBeanThen(ISqlInjector.class, globalConfig::setSqlInjector);//注入ID生成器this.getBeanThen(IdentifierGenerator.class, globalConfig::setIdentifierGenerator);//设置 GlobalConfig 到 MybatisSqlSessionFactoryBeanfactory.setGlobalConfig(globalConfig);return factory.getObject();}private <T> void getBeanThen(Class<T> clazz, Consumer<T> consumer) {if (this.applicationContext.getBeanNamesForType(clazz, false, false).length > 0) {consumer.accept(this.applicationContext.getBean(clazz));}}private void applyConfiguration(MybatisSqlSessionFactoryBean factory) {MybatisPlusConfiguration configuration = this.properties.getConfiguration();if (configuration == null && !StringUtils.hasText(this.properties.getConfigLocation())) {configuration = new MybatisPlusConfiguration();}if (configuration != null && !CollectionUtils.isEmpty(this.configurationCustomizers)) {for (ConfigurationCustomizer customizer : this.configurationCustomizers) {customizer.customize(configuration);}}factory.setConfiguration(configuration);}@Bean@ConditionalOnMissingBeanpublic SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {ExecutorType executorType = this.properties.getExecutorType();if (executorType != null) {return new SqlSessionTemplate(sqlSessionFactory, executorType);} else {return new SqlSessionTemplate(sqlSessionFactory);}}@Overridepublic void afterPropertiesSet() {if (!CollectionUtils.isEmpty(this.mybatisPlusPropertiesCustomizers)) {this.mybatisPlusPropertiesCustomizers.forEach(i -> i.customize(this.properties));}checkConfigFileExists();}private void checkConfigFileExists() {if (this.properties.isCheckConfigLocation() && StringUtils.hasText(this.properties.getConfigLocation())) {Resource resource = this.resourceLoader.getResource(this.properties.getConfigLocation());Assert.state(resource.exists(),"Cannot find config location: " + resource + " (please add config file or check your Mybatis configuration)");}}
}

自定义自动配置：

// 自定义MyBatis-Plus配置
@Configuration
@AutoConfigureAfter(MybatisPlusAutoConfiguration.class)
public class CustomMybatisPlusAutoConfiguration {@Bean@ConditionalOnMissingBeanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();// 分页插件PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);paginationInnerInterceptor.setMaxLimit(500L);paginationInnerInterceptor.setOverflow(false);interceptor.addInnerInterceptor(paginationInnerInterceptor);// 乐观锁插件interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());// 防全表更新与删除插件interceptor.addInnerInterceptor(new BlockAttackInnerInterceptor());return interceptor;}@Bean@ConditionalOnMissingBeanpublic MetaObjectHandler metaObjectHandler() {return new CustomMetaObjectHandler();}@Bean@ConditionalOnMissingBeanpublic ISqlInjector sqlInjector() {return new CustomSqlInjector();}@Bean@ConditionalOnMissingBeanpublic IdentifierGenerator identifierGenerator() {return new CustomIdentifierGenerator();}
}// 自定义填充器
public class CustomMetaObjectHandler implements MetaObjectHandler {@Overridepublic void insertFill(MetaObject metaObject) {this.strictInsertFill(metaObject, "createTime", LocalDateTime.class, LocalDateTime.now());this.strictInsertFill(metaObject, "updateTime", LocalDateTime.class, LocalDateTime.now());this.strictInsertFill(metaObject, "createBy", String.class, getCurrentUser());this.strictInsertFill(metaObject, "updateBy", String.class, getCurrentUser());this.strictInsertFill(metaObject, "deleted", Integer.class, 0);}@Overridepublic void updateFill(MetaObject metaObject) {this.strictUpdateFill(metaObject, "updateTime", LocalDateTime.class, LocalDateTime.now());this.strictUpdateFill(metaObject, "updateBy", String.class, getCurrentUser());}private String getCurrentUser() {// 从SecurityContext或其他地方获取当前用户return "system";}
}

配置文件详解

简要描述：Spring Boot通过application.yml或application.properties文件提供了丰富的配置选项，可以灵活配置MyBatis-Plus的各种特性。

核心配置项：

• 数据源配置：数据库连接相关配置
• MyBatis配置：MyBatis核心配置
• MyBatis-Plus配置：MyBatis-Plus特有配置
• 日志配置：SQL日志输出配置

完整配置示例：

# application.yml
spring:# 数据源配置datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=falseusername: rootpassword: password# 连接池配置（使用Druid）druid:initial-size: 5min-idle: 5max-active: 20max-wait: 60000time-between-eviction-runs-millis: 60000min-evictable-idle-time-millis: 300000validation-query: SELECT 1test-while-idle: truetest-on-borrow: falsetest-on-return: falsepool-prepared-statements: truemax-pool-prepared-statement-per-connection-size: 20# 配置监控统计拦截的filtersfilters: stat,wall,slf4j# 通过connectProperties属性来打开mergeSql功能；慢SQL记录connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000# 配置web监控web-stat-filter:enabled: trueurl-pattern: /*exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"stat-view-servlet:enabled: trueurl-pattern: /druid/*reset-enable: falselogin-username: adminlogin-password: admin# MyBatis-Plus配置
mybatis-plus:# 配置文件位置config-location: classpath:mybatis-config.xml# Mapper XML文件位置mapper-locations: classpath*:mapper/**/*Mapper.xml# 实体类包路径type-aliases-package: com.example.entity# 类型处理器包路径type-handlers-package: com.example.typehandler# 执行器类型executor-type: simple# 配置属性configuration-properties:key1: value1key2: value2# MyBatis原生配置configuration:# 开启驼峰命名转换map-underscore-to-camel-case: true# 开启缓存cache-enabled: true# 设置懒加载lazy-loading-enabled: true# 设置积极懒加载aggressive-lazy-loading: false# 允许多结果集multiple-result-sets-enabled: true# 允许使用列标签use-column-label: true# 允许使用自定义缓存use-generated-keys: false# 给予被嵌套的resultMap以字段-属性的映射支持auto-mapping-behavior: partial# 对于未知的SQL查询，允许返回不同的结果集以达到通用的效果auto-mapping-unknown-column-behavior: warning# 配置默认的执行器default-executor-type: simple# 对于批量更新操作缓存SQL以提高性能default-statement-timeout: 25# 设置超时时间default-fetch-size: 100# 允许在嵌套语句中使用分页safe-row-bounds-enabled: false# 允许在嵌套语句中使用分页safe-result-handler-enabled: true# 是否开启自动驼峰命名规则映射map-underscore-to-camel-case: true# 本地缓存机制local-cache-scope: session# 数据库超厂商标识jdbc-type-for-null: other# 指定当结果集中值为null的时候如何处理call-setters-on-nulls: false# 指定MyBatis增加到日志名称的前缀log-prefix: mybatis-plus# 指定MyBatis所用日志的具体实现log-impl: org.apache.ibatis.logging.slf4j.Slf4jImpl# 使用实际参数名称use-actual-param-name: true# 返回map时true:当查询返回的所有列都为空时，MyBatis返回null false:当查询返回的所有列都为空时，MyBatis返回一个空的Mapreturn-instance-for-empty-row: false# 指定VFS的实现vfs-impl: org.mybatis.spring.boot.autoconfigure.SpringBootVFS# 指定默认的类型别名超类default-scripting-language-driver: org.apache.ibatis.scripting.xmltags.XMLLanguageDriver# 全局配置global-config:# 是否控制台 print mybatis-plus 的 LOGObanner: true# 是否初始化 SqlRunnerenable-sql-runner: false# 数据库配置db-config:# 主键类型（AUTO:数据库自增 NONE:无状态 INPUT:自行输入 ASSIGN_ID:分配ID ASSIGN_UUID:分配UUID）id-type: ASSIGN_ID# 表名前缀table-prefix: t_# 字段名前缀column-prefix: # 表名是否使用驼峰转下划线命名table-underline: true# 字段是否使用驼峰转下划线命名column-underline: true# 大写命名capital-mode: false# 表关键词 keywordkey-generator: com.baomidou.mybatisplus.core.incrementer.DefaultIdentifierGenerator# 逻辑删除全局值（默认 1、表示已删除）logic-delete-value: 1# 逻辑未删除全局值（默认 0、表示未删除）logic-not-delete-value: 0# 字段验证策略insert-strategy: not_nullupdate-strategy: not_nullwhere-strategy: not_null# 日志配置
logging:level:# MyBatis日志com.example.mapper: debug# 根日志级别root: info# SQL日志org.springframework.jdbc: debugpattern:console: "%d{yyyy-MM-dd HH:mm:ss} [%thread] %-5level %logger{36} - %msg%n"file: "%d{yyyy-MM-dd HH:mm:ss} [%thread] %-5level %logger{36} - %msg%n"file:name: logs/application.logmax-size: 10MBmax-history: 30

事务管理集成

简要描述：Spring Boot与MyBatis-Plus的事务管理集成，支持声明式事务、编程式事务等多种事务管理方式。

核心概念：

• 声明式事务：通过@Transactional注解管理事务
• 编程式事务：通过TransactionTemplate管理事务
• 事务传播：事务的传播行为
• 事务隔离：事务的隔离级别

事务配置：

@Configuration
@EnableTransactionManagement
public class TransactionConfig {@Beanpublic PlatformTransactionManager transactionManager(DataSource dataSource) {return new DataSourceTransactionManager(dataSource);}@Beanpublic TransactionTemplate transactionTemplate(PlatformTransactionManager transactionManager) {return new TransactionTemplate(transactionManager);}
}

声明式事务使用：

@Service
@Transactional(rollbackFor = Exception.class)
public class UserService {@Autowiredprivate UserMapper userMapper;@Autowiredprivate UserRoleMapper userRoleMapper;// 默认事务配置public void saveUser(User user) {userMapper.insert(user);}// 只读事务@Transactional(readOnly = true)public User findById(Long id) {return userMapper.selectById(id);}// 指定传播行为@Transactional(propagation = Propagation.REQUIRES_NEW)public void saveUserWithNewTransaction(User user) {userMapper.insert(user);}// 指定隔离级别@Transactional(isolation = Isolation.READ_COMMITTED)public void updateUser(User user) {userMapper.updateById(user);}// 复杂事务场景@Transactional(rollbackFor = Exception.class)public void saveUserWithRoles(User user, List<Long> roleIds) {// 保存用户userMapper.insert(user);// 保存用户角色关系for (Long roleId : roleIds) {UserRole userRole = new UserRole();userRole.setUserId(user.getId());userRole.setRoleId(roleId);userRoleMapper.insert(userRole);}// 模拟异常，测试事务回滚if (user.getUsername().equals("error")) {throw new RuntimeException("模拟异常");}}
}

编程式事务使用：

@Service
public class OrderService {@Autowiredprivate OrderMapper orderMapper;@Autowiredprivate TransactionTemplate transactionTemplate;public void createOrder(Order order) {transactionTemplate.execute(status -> {try {// 业务逻辑orderMapper.insert(order);// 其他操作processOrderItems(order);return null;} catch (Exception e) {// 手动回滚status.setRollbackOnly();throw new RuntimeException("订单创建失败", e);}});}private void processOrderItems(Order order) {// 处理订单项}
}

测试环境配置

简要描述：为MyBatis-Plus配置测试环境，包括单元测试、集成测试等不同层次的测试配置。

核心概念：

• @SpringBootTest：Spring Boot测试注解
• @MybatisTest：MyBatis专用测试注解
• TestContainers：容器化测试
• H2数据库：内存数据库测试

测试依赖配置：

<dependencies><!-- Spring Boot Test --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><!-- MyBatis-Plus Test --><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter-test</artifactId><version>3.5.3.1</version><scope>test</scope></dependency><!-- H2数据库 --><dependency><groupId>com.h2database</groupId><artifactId>h2</artifactId><scope>test</scope></dependency><!-- TestContainers --><dependency><groupId>org.testcontainers</groupId><artifactId>junit-jupiter</artifactId><scope>test</scope></dependency><dependency><groupId>org.testcontainers</groupId><artifactId>mysql</artifactId><scope>test</scope></dependency>
</dependencies>

测试配置文件：

# application-test.yml
spring:datasource:driver-class-name: org.h2.Driverurl: jdbc:h2:mem:testdb;MODE=MySQL;DATABASE_TO_LOWER=TRUEusername: sapassword: h2:console:enabled: truepath: /h2-consolesql:init:schema-locations: classpath:schema.sqldata-locations: classpath:data.sqlmode: alwaysmybatis-plus:configuration:log-impl: org.apache.ibatis.logging.stdout.StdOutImplglobal-config:db-config:id-type: autologic-delete-value: 1logic-not-delete-value: 0logging:level:com.example.mapper: debug

单元测试示例：

// Mapper层测试
@MybatisTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
class UserMapperTest {@Autowiredprivate TestEntityManager entityManager;@Autowiredprivate UserMapper userMapper;@Testvoid testInsert() {// 准备测试数据User user = new User();user.setUsername("test");user.setEmail("test@example.com");// 执行测试int result = userMapper.insert(user);// 验证结果assertThat(result).isEqualTo(1);assertThat(user.getId()).isNotNull();}@Testvoid testSelectById() {// 准备测试数据User user = new User();user.setUsername("test");user.setEmail("test@example.com");entityManager.persistAndFlush(user);// 执行测试User found = userMapper.selectById(user.getId());// 验证结果assertThat(found).isNotNull();assertThat(found.getUsername()).isEqualTo("test");}@Testvoid testSelectByCondition() {// 准备测试数据User user1 = new User();user1.setUsername("test1");user1.setEmail("test1@example.com");entityManager.persistAndFlush(user1);User user2 = new User();user2.setUsername("test2");user2.setEmail("test2@example.com");entityManager.persistAndFlush(user2);// 执行测试QueryWrapper<User> wrapper = new QueryWrapper<>();wrapper.like("username", "test");List<User> users = userMapper.selectList(wrapper);// 验证结果assertThat(users).hasSize(2);}
}

Service层测试：

@SpringBootTest
@ActiveProfiles("test")
@Transactional
@Rollback
class UserServiceTest {@Autowiredprivate UserService userService;@Testvoid testSaveUser() {// 准备测试数据User user = new User();user.setUsername("test");user.setEmail("test@example.com");// 执行测试userService.saveUser(user);// 验证结果assertThat(user.getId()).isNotNull();assertThat(user.getCreateTime()).isNotNull();}@Testvoid testSaveUserWithRoles() {// 准备测试数据User user = new User();user.setUsername("test");user.setEmail("test@example.com");List<Long> roleIds = Arrays.asList(1L, 2L);// 执行测试userService.saveUserWithRoles(user, roleIds);// 验证结果assertThat(user.getId()).isNotNull();// 验证角色关系QueryWrapper<UserRole> wrapper = new QueryWrapper<>();wrapper.eq("user_id", user.getId());List<UserRole> userRoles = userRoleMapper.selectList(wrapper);assertThat(userRoles).hasSize(2);}@Testvoid testTransactionRollback() {// 准备测试数据User user = new User();user.setUsername("error"); // 触发异常user.setEmail("error@example.com");List<Long> roleIds = Arrays.asList(1L, 2L);// 执行测试并验证异常assertThrows(RuntimeException.class, () -> {userService.saveUserWithRoles(user, roleIds);});// 验证事务回滚QueryWrapper<User> userWrapper = new QueryWrapper<>();userWrapper.eq("username", "error");List<User> users = userMapper.selectList(userWrapper);assertThat(users).isEmpty();}
}

TestContainers集成测试：

@SpringBootTest
@Testcontainers
class UserServiceIntegrationTest {@Containerstatic MySQLContainer<?> mysql = new MySQLContainer<>("mysql:8.0").withDatabaseName("testdb").withUsername("test").withPassword("test");@DynamicPropertySourcestatic void configureProperties(DynamicPropertyRegistry registry) {registry.add("spring.datasource.url", mysql::getJdbcUrl);registry.add("spring.datasource.username", mysql::getUsername);registry.add("spring.datasource.password", mysql::getPassword);}@Autowiredprivate UserService userService;@Testvoid testUserOperations() {// 测试用户操作User user = new User();user.setUsername("integration-test");user.setEmail("integration@example.com");userService.saveUser(user);User found = userService.findById(user.getId());assertThat(found).isNotNull();assertThat(found.getUsername()).isEqualTo("integration-test");}
}

性能优化与监控

简要描述：MyBatis-Plus的性能优化涉及SQL优化、缓存策略、连接池配置、监控诊断等多个方面，通过合理的配置和使用可以显著提升应用性能。

核心概念：

• SQL性能分析：分析SQL执行效率
• 慢查询优化：识别和优化慢查询
• 缓存策略：合理使用缓存提升性能
• 连接池优化：优化数据库连接池配置
• 监控诊断：实时监控和问题诊断

SQL性能分析

简要描述：通过各种工具和插件分析SQL执行性能，识别性能瓶颈。

核心概念：

• 执行计划分析：分析SQL执行计划
• 性能监控插件：MyBatis-Plus性能监控插件
• SQL统计：SQL执行统计信息
• 性能指标：关键性能指标监控

性能监控插件配置：

// 性能分析插件
@Configuration
public class PerformanceConfig {@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();// SQL性能规范插件interceptor.addInnerInterceptor(new IllegalSQLInnerInterceptor());// 分页插件PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);paginationInnerInterceptor.setMaxLimit(1000L);interceptor.addInnerInterceptor(paginationInnerInterceptor);return interceptor;}@Bean@Profile("dev")public PerformanceInterceptor performanceInterceptor() {PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();// 设置SQL执行最大时长，超过自动停止运行，有助于发现问题performanceInterceptor.setMaxTime(1000);// 设置SQL格式化，默认falseperformanceInterceptor.setFormat(true);return performanceInterceptor;}
}// 自定义性能监控插件
@Component
public class CustomPerformanceInterceptor implements Interceptor {private static final Logger logger = LoggerFactory.getLogger(CustomPerformanceInterceptor.class);@Overridepublic Object intercept(Invocation invocation) throws Throwable {long startTime = System.currentTimeMillis();try {Object result = invocation.proceed();long endTime = System.currentTimeMillis();long executeTime = endTime - startTime;// 记录执行时间if (executeTime > 100) { // 超过100ms的SQL记录警告MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];Object parameter = invocation.getArgs()[1];logger.warn("慢SQL检测 - 执行时间: {}ms, SQL ID: {}, 参数: {}", executeTime, mappedStatement.getId(), parameter);}return result;} catch (Exception e) {long endTime = System.currentTimeMillis();long executeTime = endTime - startTime;logger.error("SQL执行异常 - 执行时间: {}ms, 异常信息: {}", executeTime, e.getMessage());throw e;}}@Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this);}@Overridepublic void setProperties(Properties properties) {// 设置属性}
}

执行计划分析工具：

// 执行计划分析工具
@Component
public class ExecutionPlanAnalyzer {@Autowiredprivate SqlSessionFactory sqlSessionFactory;public List<ExecutionPlan> analyzeExecutionPlan(String sql, Object... params) {try (SqlSession sqlSession = sqlSessionFactory.openSession()) {// 执行EXPLAIN分析String explainSql = "EXPLAIN " + sql;List<Map<String, Object>> results = sqlSession.selectList("analyzeExecutionPlan", Map.of("sql", explainSql, "params", params));return results.stream().map(this::convertToExecutionPlan).collect(Collectors.toList());}}private ExecutionPlan convertToExecutionPlan(Map<String, Object> result) {ExecutionPlan plan = new ExecutionPlan();plan.setId((Integer) result.get("id"));plan.setSelectType((String) result.get("select_type"));plan.setTable((String) result.get("table"));plan.setType((String) result.get("type"));plan.setPossibleKeys((String) result.get("possible_keys"));plan.setKey((String) result.get("key"));plan.setKeyLen((String) result.get("key_len"));plan.setRef((String) result.get("ref"));plan.setRows((Long) result.get("rows"));plan.setExtra((String) result.get("Extra"));return plan;}public boolean hasPerformanceIssues(List<ExecutionPlan> plans) {for (ExecutionPlan plan : plans) {// 检查是否有性能问题if ("ALL".equals(plan.getType()) || // 全表扫描plan.getRows() > 10000 || // 扫描行数过多plan.getExtra().contains("Using filesort") || // 文件排序plan.getExtra().contains("Using temporary")) { // 使用临时表return true;}}return false;}
}

慢查询优化

简要描述：识别、分析和优化慢查询，提升数据库查询性能。

核心概念：

• 慢查询日志：记录执行时间超过阈值的SQL
• 索引优化：合理创建和使用索引
• 查询重写：优化SQL语句结构
• 分页优化：优化大数据量分页查询

慢查询监控配置：

# application.yml
spring:datasource:druid:# 慢SQL记录filter:stat:enabled: trueslow-sql-millis: 1000log-slow-sql: true# 监控配置stat-view-servlet:enabled: trueurl-pattern: /druid/*reset-enable: falseweb-stat-filter:enabled: trueurl-pattern: /*exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"# MyBatis-Plus慢查询配置
mybatis-plus:configuration:# 开启SQL日志log-impl: org.apache.ibatis.logging.slf4j.Slf4jImplglobal-config:# 性能分析插件enable-sql-runner: truelogging:level:# 开启SQL日志com.example.mapper: debug# Druid慢SQL日志druid.sql.Statement: debug

慢查询优化策略：

// 慢查询优化服务
@Service
public class SlowQueryOptimizationService {@Autowiredprivate UserMapper userMapper;// 优化前：全表扫描public List<User> findUsersByNameBad(String name) {QueryWrapper<User> wrapper = new QueryWrapper<>();wrapper.like("username", "%" + name + "%"); // 前缀模糊查询，无法使用索引return userMapper.selectList(wrapper);}// 优化后：使用索引public List<User> findUsersByNameGood(String name) {QueryWrapper<User> wrapper = new QueryWrapper<>();wrapper.likeRight("username", name); // 右模糊查询，可以使用索引return userMapper.selectList(wrapper);}// 优化前：N+1查询问题public List<UserWithRoles> findUsersWithRolesBad() {List<User> users = userMapper.selectList(null);return users.stream().map(user -> {List<Role> roles = roleMapper.selectByUserId(user.getId()); // N+1查询return new UserWithRoles(user, roles);}).collect(Collectors.toList());}// 优化后：批量查询public List<UserWithRoles> findUsersWithRolesGood() {List<User> users = userMapper.selectList(null);if (users.isEmpty()) {return Collections.emptyList();}List<Long> userIds = users.stream().map(User::getId).collect(Collectors.toList());// 批量查询角色List<UserRole> userRoles = userRoleMapper.selectList(new QueryWrapper<UserRole>().in("user_id", userIds));// 构建用户角色映射Map<Long, List<Role>> userRoleMap = userRoles.stream().collect(Collectors.groupingBy(UserRole::getUserId,Collectors.mapping(ur -> roleMapper.selectById(ur.getRoleId()),Collectors.toList())));return users.stream().map(user -> new UserWithRoles(user, userRoleMap.getOrDefault(user.getId(), Collections.emptyList()))).collect(Collectors.toList());}// 分页查询优化public IPage<User> findUsersWithPagination(int current, int size, String keyword) {Page<User> page = new Page<>(current, size);QueryWrapper<User> wrapper = new QueryWrapper<>();if (StringUtils.hasText(keyword)) {wrapper.and(w -> w.like("username", keyword).or().like("email", keyword));}// 优化：只查询必要字段wrapper.select("id", "username", "email", "create_time");return userMapper.selectPage(page, wrapper);}// 大数据量分页优化（游标分页）public List<User> findUsersWithCursorPagination(Long lastId, int size) {QueryWrapper<User> wrapper = new QueryWrapper<>();if (lastId != null) {wrapper.gt("id", lastId);}wrapper.orderByAsc("id");wrapper.last("LIMIT " + size);return userMapper.selectList(wrapper);}
}

索引优化建议：

-- 创建合适的索引
-- 1. 单列索引
CREATE INDEX idx_user_username ON user(username);
CREATE INDEX idx_user_email ON user(email);
CREATE INDEX idx_user_create_time ON user(create_time);-- 2. 复合索引（注意字段顺序）
CREATE INDEX idx_user_status_create_time ON user(status, create_time);
CREATE INDEX idx_user_dept_status ON user(dept_id, status);-- 3. 覆盖索引（包含查询所需的所有字段）
CREATE INDEX idx_user_cover ON user(status, username, email, create_time);-- 4. 前缀索引（对于长字符串字段）
CREATE INDEX idx_user_description ON user(description(50));-- 5. 函数索引（MySQL 8.0+）
CREATE INDEX idx_user_upper_username ON user((UPPER(username)));

缓存优化策略

简要描述：合理使用MyBatis一级缓存、二级缓存以及外部缓存系统，提升查询性能。

核心概念：

• 一级缓存：SqlSession级别的缓存
• 二级缓存：Mapper级别的缓存
• 外部缓存：Redis等外部缓存系统
• 缓存策略：缓存的使用策略和失效机制

MyBatis缓存配置：

# application.yml
mybatis-plus:configuration:# 开启二级缓存cache-enabled: true# 本地缓存作用域local-cache-scope: session# 懒加载配置lazy-loading-enabled: trueaggressive-lazy-loading: false

Redis缓存集成：

// Redis缓存配置
@Configuration
@EnableCaching
public class CacheConfig {@Beanpublic RedisTemplate<String, Object> redisTemplate(RedisConnectionFactory factory) {RedisTemplate<String, Object> template = new RedisTemplate<>();template.setConnectionFactory(factory);// 设置序列化器Jackson2JsonRedisSerializer<Object> serializer = new Jackson2JsonRedisSerializer<>(Object.class);ObjectMapper mapper = new ObjectMapper();mapper.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.ANY);mapper.activateDefaultTyping(LaissezFaireSubTypeValidator.instance, ObjectMapper.DefaultTyping.NON_FINAL);serializer.setObjectMapper(mapper);template.setKeySerializer(new StringRedisSerializer());template.setHashKeySerializer(new StringRedisSerializer());template.setValueSerializer(serializer);template.setHashValueSerializer(serializer);template.afterPropertiesSet();return template;}@Beanpublic CacheManager cacheManager(RedisConnectionFactory factory) {RedisCacheConfiguration config = RedisCacheConfiguration.defaultCacheConfig().entryTtl(Duration.ofMinutes(30)).serializeKeysWith(RedisSerializationContext.SerializationPair.fromSerializer(new StringRedisSerializer())).serializeValuesWith(RedisSerializationContext.SerializationPair.fromSerializer(new GenericJackson2JsonRedisSerializer())).disableCachingNullValues();return RedisCacheManager.builder(factory).cacheDefaults(config).build();}
}

Spring Cache注解使用：

@Service
public class UserService {@Autowiredprivate UserMapper userMapper;// 缓存查询结果@Cacheable(value = "users", key = "#id")public User findById(Long id) {return userMapper.selectById(id);}// 缓存查询结果（条件缓存）@Cacheable(value = "users", key = "#username", condition = "#username.length() > 3")public User findByUsername(String username) {QueryWrapper<User> wrapper = new QueryWrapper<>();wrapper.eq("username", username);return userMapper.selectOne(wrapper);}// 更新时清除缓存@CacheEvict(value = "users", key = "#user.id")public void updateUser(User user) {userMapper.updateById(user);}// 删除时清除缓存@CacheEvict(value = "users", key = "#id")public void deleteUser(Long id) {userMapper.deleteById(id);}// 清除所有缓存@CacheEvict(value = "users", allEntries = true)public void clearAllCache() {// 清除所有用户缓存}// 更新缓存@CachePut(value = "users", key = "#user.id")public User saveUser(User user) {userMapper.insert(user);return user;}
}

连接池优化

简要描述：优化数据库连接池配置，提升数据库连接效率和应用性能。

核心概念：

• 连接池大小：合理设置连接池大小
• 连接超时：设置合适的连接超时时间
• 连接验证：连接有效性验证
• 连接监控：连接池状态监控

Druid连接池优化配置：

# application.yml
spring:datasource:type: com.alibaba.druid.pool.DruidDataSourcedruid:# 初始连接数initial-size: 10# 最小空闲连接数min-idle: 10# 最大活跃连接数max-active: 100# 获取连接等待超时时间max-wait: 60000# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒time-between-eviction-runs-millis: 60000# 配置一个连接在池中最小生存的时间，单位是毫秒min-evictable-idle-time-millis: 300000# 配置一个连接在池中最大生存的时间，单位是毫秒max-evictable-idle-time-millis: 900000# 用来检测连接是否有效的sql，要求是一个查询语句validation-query: SELECT 1# 建议配置为true，不影响性能，并且保证安全性test-while-idle: true# 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能test-on-borrow: false# 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能test-on-return: false# 是否缓存preparedStatement，也就是PSCachepool-prepared-statements: true# 要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为truemax-pool-prepared-statement-per-connection-size: 20# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙filters: stat,wall,slf4j# 通过connectProperties属性来打开mergeSql功能；慢SQL记录connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=2000# 合并多个DruidDataSource的监控数据use-global-data-source-stat: true# 配置web监控web-stat-filter:enabled: trueurl-pattern: /*exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"session-stat-enable: falsesession-stat-max-count: 1000principal-session-name: adminprincipal-cookie-name: adminprofile-enable: true# 配置监控页面stat-view-servlet:enabled: trueurl-pattern: /druid/*# IP白名单(没有配置或者为空，则允许所有访问)allow: 127.0.0.1,192.168.163.1# IP黑名单 (存在共同时，deny优先于allow)deny: 192.168.1.73# 禁用HTML页面上的"Reset All"功能reset-enable: false# 登录名login-username: admin# 登录密码login-password: 123456

HikariCP连接池优化配置：

# application.yml
spring:datasource:type: com.zaxxer.hikari.HikariDataSourcehikari:# 连接池名称pool-name: HikariCP# 最小空闲连接数minimum-idle: 10# 最大连接池大小maximum-pool-size: 100# 自动提交auto-commit: true# 空闲连接存活最大时间，默认600000（10分钟）idle-timeout: 600000# 连接池最大生命周期，0表示无限生命周期，默认1800000即30分钟max-lifetime: 1800000# 连接超时时间，默认30000即30秒connection-timeout: 30000# 测试连接是否可用的查询语句connection-test-query: SELECT 1# 连接初始化SQLconnection-init-sql: SET NAMES utf8mb4# 数据库连接超时时间,默认30秒，即30000validation-timeout: 5000# 空闲连接检测周期，默认30000毫秒keepalive-time: 30000# 是否允许连接泄露检测leak-detection-threshold: 60000

连接池监控：

// 连接池监控服务
@Service
public class DataSourceMonitorService {@Autowiredprivate DataSource dataSource;public DataSourceStats getDataSourceStats() {if (dataSource instanceof DruidDataSource) {return getDruidStats((DruidDataSource) dataSource);} else if (dataSource instanceof HikariDataSource) {return getHikariStats((HikariDataSource) dataSource);}return new DataSourceStats();}private DataSourceStats getDruidStats(DruidDataSource druidDataSource) {DataSourceStats stats = new DataSourceStats();stats.setActiveCount(druidDataSource.getActiveCount());stats.setPoolingCount(druidDataSource.getPoolingCount());stats.setMaxActive(druidDataSource.getMaxActive());stats.setCreateCount(druidDataSource.getCreateCount());stats.setDestroyCount(druidDataSource.getDestroyCount());stats.setConnectCount(druidDataSource.getConnectCount());stats.setCloseCount(druidDataSource.getCloseCount());return stats;}private DataSourceStats getHikariStats(HikariDataSource hikariDataSource) {DataSourceStats stats = new DataSourceStats();HikariPoolMXBean poolBean = hikariDataSource.getHikariPoolMXBean();stats.setActiveCount(poolBean.getActiveConnections());stats.setPoolingCount(poolBean.getIdleConnections());stats.setMaxActive(hikariDataSource.getMaximumPoolSize());stats.setTotalConnections(poolBean.getTotalConnections());return stats;}@Scheduled(fixedRate = 30000) // 每30秒监控一次public void monitorDataSource() {DataSourceStats stats = getDataSourceStats();// 记录监控日志logger.info("数据源监控 - 活跃连接: {}, 空闲连接: {}, 最大连接: {}", stats.getActiveCount(), stats.getPoolingCount(), stats.getMaxActive());// 检查连接池健康状况if (stats.getActiveCount() > stats.getMaxActive() * 0.8) {logger.warn("连接池使用率过高: {}%", (double) stats.getActiveCount() / stats.getMaxActive() * 100);}}
}

监控与诊断

简要描述：通过各种监控工具和诊断手段，实时监控MyBatis-Plus应用的性能状况。

核心概念：

• 性能指标监控：关键性能指标的实时监控
• 健康检查：应用健康状况检查
• 链路追踪：分布式链路追踪
• 告警机制：异常情况告警

Actuator监控配置：

# application.yml
management:endpoints:web:exposure:include: "*"endpoint:health:show-details: alwaysmetrics:enabled: truemetrics:export:prometheus:enabled: truedistribution:percentiles-histogram:http.server.requests: truepercentiles:http.server.requests: 0.5, 0.9, 0.95, 0.99

自定义健康检查：

// 数据库健康检查
@Component
public class DatabaseHealthIndicator implements HealthIndicator {@Autowiredprivate SqlSessionFactory sqlSessionFactory;@Overridepublic Health health() {try (SqlSession sqlSession = sqlSessionFactory.openSession()) {// 执行简单查询测试数据库连接sqlSession.selectOne("SELECT 1");return Health.up().withDetail("database", "Available").withDetail("validationQuery", "SELECT 1").build();} catch (Exception e) {return Health.down().withDetail("database", "Unavailable").withDetail("error", e.getMessage()).build();}}
}// MyBatis-Plus健康检查
@Component
public class MybatisPlusHealthIndicator implements HealthIndicator {@Autowiredprivate UserMapper userMapper;@Overridepublic Health health() {try {// 测试基本CRUD操作long count = userMapper.selectCount(null);return Health.up().withDetail("mybatis-plus", "Available").withDetail("userCount", count).build();} catch (Exception e) {return Health.down().withDetail("mybatis-plus", "Unavailable").withDetail("error", e.getMessage()).build();}}
}

性能指标收集：

// 自定义性能指标
@Component
public class MybatisPlusMetrics {private final MeterRegistry meterRegistry;private final Counter sqlExecutionCounter;private final Timer sqlExecutionTimer;private final AtomicLong slowQueryCount = new AtomicLong(0);public MybatisPlusMetrics(MeterRegistry meterRegistry) {this.meterRegistry = meterRegistry;this.sqlExecutionCounter = Counter.builder("mybatis.sql.executions").description("Total SQL executions").register(meterRegistry);this.sqlExecutionTimer = Timer.builder("mybatis.sql.duration").description("SQL execution duration").register(meterRegistry);Gauge.builder("mybatis.sql.slow.count").description("Number of slow queries").register(meterRegistry, this, MybatisPlusMetrics::getSlowQueryCount);}public void recordSqlExecution(String sqlId, long duration) {sqlExecutionCounter.increment(Tags.of("sql.id", sqlId,"status", "success"));sqlExecutionTimer.record(duration, TimeUnit.MILLISECONDS,Tags.of("sql.id", sqlId));}public void recordSqlError(String sqlId, String errorType) {sqlExecutionCounter.increment(Tags.of("sql.id", sqlId,"status", "error","error.type", errorType));}private double getSlowQueryCount() {return slowQueryCount.get();}public void incrementSlowQueryCount() {slowQueryCount.incrementAndGet();}
}