HoRain云--MyBatis与MySQL性能优化实战

ORM框架中MyBatis因灵活SQL而受欢迎。但SQL写不好性能会急剧下降。本文总结MyBatis和MySQL优化技巧。一、MyBatis核心select idselectUser resultTypeUser SELECT id, name, age FROM user WHERE id #{id} /select#{}预编译${}拼接有SQL注入风险。二、动态SQLselect idsearch SELECT * FROM user where if testname ! null AND name LIKE CONCAT(%, #{name}, %) /if if testage ! null AND age #{age} /if /where /select三、一级二级缓存一级缓存SqlSession级别默认开启二级缓存Mapper级别需cache/cache evictionLRU flushInterval60000 size512/注意分布式环境慎用二级缓存。四、分页优化使用PageHelperPageHelper.startPage(1, 10); ListUser users userMapper.selectAll(); PageInfoUser pageInfo new PageInfo(users);深分页优化-- 慢 SELECT * FROM user LIMIT 1000000, 10; -- 快使用上次最大id SELECT * FROM user WHERE id 1000000 LIMIT 10;五、索引优化最左前缀原则覆盖索引避免索引失效函数、类型转换、前导模糊使用EXPLAIN分析EXPLAIN SELECT * FROM user WHERE name Tom;六、SQL优化技巧避免SELECT *小表驱动大表用JOIN代替子查询批量插入用INSERT INTO ... VALUES (...), (...)更新用主键避免大事务七、连接池HikariCP配置spring: datasource: hikari: maximum-pool-size: 20 minimum-idle: 5 connection-timeout: 30000 idle-timeout: 600000 max-lifetime: 1800000八、分库分表垂直拆分按业务水平拆分按数据中间件ShardingSphere、MyCatspring: shardingsphere: rules: sharding: tables: t_order: actual-data-nodes: db$-{0..1}.t_order$-{0..1} table-strategy: standard: sharding-column: order_id sharding-algorithm-name: inline九、实战案例某查询从3秒优化到50ms原SQLSELECT * FROM order WHERE DATE(create_time) 2024-01-01问题函数导致索引失效优化WHERE create_time 2024-01-01 AND create_time 2024-01-02添加联合索引(create_time, status)十、总结MyBatis优化核心是SQL和索引。掌握EXPLAIN、连接池、分库分表能应对海量数据场景。