数据库性能优化实战：我在生产环境踩过的那些坑

系列导读本篇将深入讲解数据库性能优化的核心方法与实战技巧。作为一名在后端开发一线奋斗了5年的工程师我几乎每天都会和数据库打交道。从最初的查询超时、PGC告警到后来的连接池耗尽、慢查询拖垮整个服务这些问题我都一一经历过。今天就把这些宝贵的实战经验分享给大家保证都是干货文章目录一、SQL 优化1.1 慢查询分析找到性能杀手1.2 EXPLAIN 解读帮我们读懂查询的体检报告1.3 优化原则老司机的血泪经验❌ 避免 SELECT *❌ 避免在索引列上使用函数❌ 避免使用 OR❌ 分页优化远离 LIMIT 1000000, 10二、索引优化2.1 索引类型选对优化方法很重要2.2 索引设计原则老司机私房菜2.3 组合索引示例最左前缀的奥秘三、架构优化3.1 读写分离让主库喘口气3.2 分库分表突破单机存储瓶颈3.3 缓存策略拒绝缓存雪崩四、连接池优化4.1 HikariCP 配置让连接池物尽其用4.2 连接池监控知己知彼总结一、SQL 优化1.1 慢查询分析找到性能杀手在我负责的一个电商订单系统中曾经出现过这样的问题每天下午3点左右系统响应时间突然从正常的200ms飙升到3秒以上客服电话被打爆。经过排查发现是延迟订单报表查询导致的——订单表数据量已经突破2000万条。如何开启慢查询日志-- 开启慢查询日志SETGLOBALslow_query_logON;-- 设置慢查询阈值为1秒SETGLOBALlong_query_time1;-- 查看慢查询日志文件位置SHOWVARIABLESLIKEslow_query_log_file;开启后MySQL会将执行时间超过阈值的SQL记录到日志文件中。我们可以通过以下命令查看-- 分析慢查询推荐使用mysqldumpslowmysqldumpslow-s t-t10/var/log/mysql/slow.log-- 也可以直接查看最近10条慢查询SELECT*FROMmysql.slow_logORDERBYstart_timeDESCLIMIT10;使用 EXPLAIN 分析执行计划-- 分析查询执行计划EXPLAINSELECTo.*,u.name,u.phoneFROMorders oLEFTJOINusers uONo.user_idu.idWHEREo.status1ANDo.create_time2024-01-01;1.2 EXPLAIN 解读帮我们读懂查询的体检报告字段说明优化目标type访问类型最好达到 ref 或 const避免 ALL全表扫描key使用的索引确保不为 NULLrows扫描行数越少越好理论上应该接近实际返回行数Extra额外信息避免 Using filesort 和 Using temporarytype 字段的优先级从好到差const常量连接最多匹配一行eq_ref唯一索引扫描ref非唯一索引扫描range索引范围扫描index全索引扫描ALL全表扫描最危险实战经验我曾经见过一个SQL的type是ALLrows显示扫描了500万行结果只返回1条数据。就是在WHERE条件中漏了一个索引字段导致全表扫描。加上索引后查询时间从2秒降到5毫秒。1.3 优化原则老司机的血泪经验❌ 避免 SELECT *-- 差查询所有字段SELECT*FROMusersWHEREid1;-- 好只查询需要的字段SELECTid,name,emailFROMusersWHEREid1;为什么SELECT * 会增加网络传输开销而且如果表结构发生变化可能导致代码报错。更重要的是它无法利用覆盖索引。❌ 避免在索引列上使用函数-- 差使用函数导致索引失效SELECT*FROMordersWHEREDATE(create_time)2024-01-01;-- 好使用范围查询SELECT*FROMordersWHEREcreate_time2024-01-01 00:00:00ANDcreate_time2024-01-02 00:00:00;踩坑记我曾经写过WHERE YEAR(create_time) 2024这样的SQL结果索引完全失效2000万的表跑了15秒后来改成范围查询秒级响应。❌ 避免使用 OR-- 差OR导致索引失效SELECT*FROMusersWHEREname张三ORage20;-- 好使用 UNIONSELECT*FROMusersWHEREname张三UNIONSELECT*FROMusersWHEREage20;❌ 分页优化远离 LIMIT 1000000, 10-- 差深度分页MySQL先扫描前100万行SELECT*FROMordersLIMIT1000000,10;-- 好基于主键的延迟关联SELECTo.*FROMorders oINNERJOIN(SELECTidFROMordersLIMIT1000000,10)tONo.idt.id;-- 更好记住上次查询的最大IDSELECT*FROMordersWHEREid1000000LIMIT10;实战经验在订单列表页如果用户直接跳到第10万页普通的LIMIT语法会让数据库扫描10万零10行极其慢。推荐使用游标分页或者深度分页优化方案。二、索引优化2.1 索引类型选对优化方法很重要类型说明使用场景主键索引唯一、非空每张表必须有用于唯一标识唯一索引唯一但可为空业务上需要唯一约束的字段普通索引加速查询常规查询条件字段组合索引多列组合多条件查询遵循最左前缀全文索引文本搜索标题、内容等文本搜索场景2.2 索引设计原则老司机私房菜选择区分度高的列区分度 COUNT(DISTINCT col) / COUNT(*)遵循最左前缀原则覆盖索引减少回表控制索引数量单表不超过5个避免冗余索引实战经验如何判断字段区分度性别字段区分度约0.5男/女不适合建索引状态字段区分度约0.110个状态效果一般用户ID区分度接近1必须建索引订单号区分度为1必须建索引2.3 组合索引示例最左前缀的奥秘-- 创建组合索引(user_id, status, create_time)CREATEINDEXidx_user_status_timeONorders(user_id,status,create_time);-- ✅ 命中索引完全匹配最左前缀SELECT*FROMordersWHEREuser_id1;SELECT*FROMordersWHEREuser_id1ANDstatus1;SELECT*FROMordersWHEREuser_id1ANDstatus1ANDcreate_time2024-01-01;-- ✅ 命中索引范围查询不影响后续字段SELECT*FROMordersWHEREuser_id1ANDcreate_time2024-01-01;-- ❌ 不命中索引跳过最左前缀SELECT*FROMordersWHEREstatus1;SELECT*FROMordersWHEREcreate_time2024-01-01;-- ❌ 不命中索引中断最左前缀SELECT*FROMordersWHEREuser_id1ANDcreate_time2024-01-01;组合索引字段顺序原则区分度高的放前面等值查询的放前面范围查询的放最后三、架构优化3.1 读写分离让主库喘口气当单库无法承受读写压力时读写分离是第一步。之前参与的项目在引入读写分离后查询性能提升了3-5倍。ShardingSphere 配置示例# application.ymlspring:shardingsphere:datasource:names:master,slavemaster:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverjdbc-url:jdbc:mysql://master:3306/mydb?useUnicodetruecharacterEncodingutf8username:rootpassword:your_passwordslave:type:com.zaxxer.hikari.HikariDataSourcedriver-class-name:com.mysql.cj.jdbc.Driverjdbc-url:jdbc:mysql://slave:3306/mydb?useUnicodetruecharacterEncodingutf8username:rootpassword:your_passwordrules:readwrite-splitting:data-sources:myds:write-data-source-name:masterread-data-source-names:slaveload-balancer-name:round_robin实战经验读写分离后一定要小心主从延迟问题对于需要强一致性的场景如支付、库存扣减必须走主库。可以用ShardingSphereRouting(master)注解强制指定数据源。3.2 分库分表突破单机存储瓶颈当单表数据量超过2000万条时就要考虑分库分表了。ShardingSphere 分片配置spring:shardingsphere:rules:sharding:tables:t_order:actual-data-nodes:ds${0..1}.t_order_${0..1}database-strategy:standard:sharding-column:user_idsharding-algorithm-name:database-inlinetable-strategy:standard:sharding-column:order_idsharding-algorithm-name:order-inlinekey-generate-strategy:column:order_idkey-generator-name:snowflakesharding-algorithms:database-inline:type:INLINEprops:algorithm-expression:ds${user_id % 2}order-inline:type:INLINEprops:algorithm-expression:t_order_${order_id % 4}分片键选择分片键的选择至关重要通常选择查询条件中最频繁的字段。例如订单表按user_id分片可以快速查询某个用户的所有订单。3.3 缓存策略拒绝缓存雪崩// 缓存穿透防护 缓存雪崩解决方案ServicepublicclassUserService{AutowiredprivateRedisTemplateString,ObjectredisTemplate;AutowiredprivateUserMapperuserMapper;privatestaticfinalDurationCACHE_TIMEOUTDuration.ofHours(1);privatestaticfinalDurationNULL_CACHE_TIMEOUTDuration.ofMinutes(5);publicUsergetUser(Longid){Stringkeyuser:id;// 1. 先查缓存Useruser(User)redisTemplate.opsForValue().get(key);if(user!null){log.debug(缓存命中: userId{},id);returnuser;}// 2. 防止缓存穿透查询空值标记BooleanhasNullMarkredisTemplate.hasKey(null:user:id);if(Boolean.TRUE.equals(hasNullMark)){log.debug(缓存穿透防护: userId{},id);returnnull;}// 3. 查数据库useruserMapper.selectById(id);// 4. 写入缓存if(user!null){// 加上随机过期时间防止缓存雪崩DurationrandomTimeoutCACHE_TIMEOUT.plusMinutes(ThreadLocalRandom.current().nextInt(30));redisTemplate.opsForValue().set(key,user,randomTimeout);log.debug(缓存写入: userId{},id);}else{// 空值缓存防止缓存穿透redisTemplate.opsForValue().set(null:user:id,,NULL_CACHE_TIMEOUT);log.debug(空值缓存: userId{},id);}returnuser;}}实战经验缓存问题总结缓存穿透查询不存在的数据频繁打数据库 → 用空值缓存解决缓存击穿热点key过期瞬间大量请求打数据库 → 用分布式锁解决缓存雪崩大量key同时过期 → 用随机过期时间解决四、连接池优化4.1 HikariCP 配置让连接池物尽其用HikariCP是目前Java生态中最快的连接池我的项目从Druid切换到HikariCP后响应时间平均降低了15%。spring:datasource:hikari:# 最小空闲连接数minimum-idle:10# 最大连接池大小maximum-pool-size:50# 空闲连接超时时间毫秒idle-timeout:600000# 连接最大生命周期毫秒max-lifetime:1800000# 获取连接超时时间毫秒connection-timeout:30000# 连接池名称pool-name:OrderHikariPool# 是否允许自动提交auto-commit:true# 连接测试查询connection-test-query:SELECT 1参数调优建议参数默认值建议值说明minimum-idle10CPU核心数×2保持足够的预热连接maximum-pool-size-CPU核心数×2过高会导致数据库压力过大connection-timeout3000010000-30000根据业务容忍度调整max-lifetime180000030分钟避免数据库连接断开实战经验如何计算最佳连接池大小有个经典公式连接池大小 (核心数 × 2) 有效磁盘数。例如4核CPU SSD连接池大小约为 4×21 9取10即可。4.2 连接池监控知己知彼ServicepublicclassHikariCPMonitor{privatestaticfinalLoggerlogLoggerFactory.getLogger(HikariCPMonitor.class);AutowiredprivateDataSourcedataSource;Scheduled(fixedRate60000)// 每分钟执行一次publicvoidmonitor(){if(dataSourceinstanceofHikariDataSource){HikariDataSourcehikariDS(HikariDataSource)dataSource;HikariPoolMXBeanpoolhikariDS.getHikariPoolMXBean();log.info( HikariCP 连接池状态 );log.info(活跃连接数: {},pool.getActiveConnections());log.info(空闲连接数: {},pool.getIdleConnections());log.info(等待线程数: {},pool.getThreadsAwaitingConnection());log.info(总连接数: {},pool.getTotalConnections());log.info();// 告警逻辑if(pool.getThreadsAwaitingConnection()5){log.warn(连接池等待线程过多可能需要调优);}if(pool.getActiveConnections()hikariDS.getMaximumPoolSize()){log.error(连接池已耗尽需要紧急处理);// 发送告警通知sendAlert(连接池耗尽告警);}}}privatevoidsendAlert(Stringmessage){// 这里接入你的告警系统log.error(发送告警: {},message);}}总结✅SQL 优化慢查询分析、EXPLAIN执行计划解读、避免SELECT *、函数操作、OR和深度分页✅索引优化索引类型选择、设计原则、组合索引最左前缀法则✅架构优化读写分离、分库分表、缓存策略穿透/击穿/雪崩✅连接池优化HikariCP参数调优、连接池监控告警最后忠告数据库优化是一个系统工程一定要先测量再优化。使用EXPLAIN分析SQL创建合适的索引从架构层面考虑读写分离和分库分表。记住没有优化的优化就是最大的浪费。作者刘~浪地球更新时间2026-04-15本文声明原创不易转载请注明出处如有问题欢迎评论区留言讨论。