系列导读本篇将深入讲解数据库性能优化的核心方法与实战技巧。文章目录一、SQL 优化1.1 慢查询分析1.2 EXPLAIN 解读1.3 优化原则二、索引优化2.1 索引类型2.2 索引设计原则2.3 组合索引示例三、架构优化3.1 读写分离3.2 分库分表3.3 缓存策略四、连接池优化4.1 HikariCP 配置4.2 连接池监控总结一、SQL 优化1.1 慢查询分析-- 开启慢查询日志SETGLOBALslow_query_logON;SETGLOBALlong_query_time1;-- 分析慢查询EXPLAINSELECT*FROMordersWHEREuser_id100;1.2 EXPLAIN 解读字段说明type访问类型ALL/index/range/ref/constkey使用的索引rows扫描行数Extra额外信息1.3 优化原则-- 避免 SELECT *SELECTid,nameFROMusersWHEREid1;-- 避免函数操作-- 差SELECT*FROMordersWHEREDATE(create_time)2024-01-01;-- 好SELECT*FROMordersWHEREcreate_time2024-01-01ANDcreate_time2024-01-02;-- 避免 OR-- 差SELECT*FROMusersWHEREname张三ORage20;-- 好SELECT*FROMusersWHEREname张三UNIONSELECT*FROMusersWHEREage20;-- 分页优化-- 差SELECT*FROMordersLIMIT1000000,10;-- 好SELECT*FROMordersWHEREid1000000LIMIT10;二、索引优化2.1 索引类型类型说明主键索引唯一、非空唯一索引唯一普通索引加速查询组合索引多列索引全文索引文本搜索2.2 索引设计原则1. 选择区分度高的列 2. 遵循最左前缀原则 3. 覆盖索引减少回表 4. 控制索引数量 5. 避免冗余索引2.3 组合索引示例-- 组合索引CREATEINDEXidx_user_status_timeONorders(user_id,status,create_time);-- 命中索引SELECT*FROMordersWHEREuser_id1;SELECT*FROMordersWHEREuser_id1ANDstatus1;SELECT*FROMordersWHEREuser_id1ANDstatus1ANDcreate_time2024-01-01;-- 不命中索引SELECT*FROMordersWHEREstatus1;SELECT*FROMordersWHEREcreate_time2024-01-01;三、架构优化3.1 读写分离# ShardingSphere 配置spring:shardingsphere:datasource:names:master,slavemaster:type:com.zaxxer.hikari.HikariDataSourcejdbc-url:jdbc:mysql://master:3306/mydbslave:type:com.zaxxer.hikari.HikariDataSourcejdbc-url:jdbc:mysql://slave:3306/mydbrules:readwrite-splitting:data-sources:myds:write-data-source-name:masterread-data-source-names:slave3.2 分库分表# 分片配置spring:shardingsphere:rules:sharding:tables:t_order:actual-data-nodes:ds${0..1}.t_order_${0..1}table-strategy:standard:sharding-column:order_idsharding-algorithm-name:order-inline3.3 缓存策略// 缓存穿透防护publicUsergetUser(Longid){Stringkeyuser:id;UseruserredisTemplate.opsForValue().get(key);if(user!null){returnuser;}// 防止缓存穿透if(redisTemplate.hasKey(null:id)){returnnull;}useruserMapper.selectById(id);if(user!null){redisTemplate.opsForValue().set(key,user,1,TimeUnit.HOURS);}else{// 空值缓存redisTemplate.opsForValue().set(null:id,,5,TimeUnit.MINUTES);}returnuser;}四、连接池优化4.1 HikariCP 配置spring:datasource:hikari:minimum-idle:10maximum-pool-size:50idle-timeout:600000max-lifetime:1800000connection-timeout:30000pool-name:OrderHikariPool4.2 连接池监控// 获取连接池状态HikariDataSourcedataSource(HikariDataSource)applicationContext.getBean(DataSource.class);HikariPoolMXBeanpooldataSource.getHikariPoolMXBean();log.info(活跃连接: {},pool.getActiveConnections());log.info(空闲连接: {},pool.getIdleConnections());log.info(等待线程: {},pool.getThreadsAwaitingConnection());总结✅SQL 优化慢查询、EXPLAIN、优化原则✅索引优化类型、设计原则、组合索引✅架构优化读写分离、分库分表、缓存✅连接池优化HikariCP 配置、监控作者刘~浪地球更新时间2026-04-17