013、数据库性能优化：索引、查询与连接池

013、数据库性能优化索引、查询与连接池一、从一次慢查询说起上周排查一个线上问题接口响应时间从平均 50ms 飙到 2 秒以上。打开慢查询日志抓到一条 SQLSELECT*FROMorder_detailWHEREuser_id10086ANDstatus1ANDcreate_time2024-01-01ORDERBYupdate_timeDESCLIMIT20;表里有 300 万条数据这条查询居然扫了 280 万行。第一反应是索引问题一看表结构果然只在id上建了主键索引。问题就出在这里——没有为查询条件建立合适的索引。二、索引不是越多越好很多人觉得索引能加速查询就拼命建索引。我见过一张表建了 15 个索引写入速度慢得像蜗牛。索引的本质是用空间换时间每个索引都是一棵 B 树写入时要维护所有索引树。踩坑案例曾经在status字段上单独建索引查询依然慢。后来明白status只有 0/1 两种值区分度太低MySQL 优化器可能直接忽略这个索引。正确姿势对上面那条慢查询我建的是联合索引ALTERTABLEorder_detailADDINDEXidx_user_status_time(user_id,status,create_time);注意字段顺序user_id放第一因为等值查询区分度高status放第二虽然区分度低但能利用索引过滤create_time放最后用于范围查询别这样写-- 顺序乱放索引可能失效ADDINDEXidx_time_status_user(create_time,status,user_id);三、查询优化的几个细节1. 避免 SELECT *-- 坏习惯读所有字段SELECT*FROMlarge_tableWHERE...-- 好习惯只取需要的SELECTid,name,statusFROMlarge_tableWHERE...多一个字段就可能多一次回表操作尤其 TEXT/BLOB 字段。2. 小心 JOIN 陷阱-- 大表 JOIN 大表性能灾难SELECT*FROMtable_a aJOINtable_b bONa.idb.a_idWHEREa.create_time2024-01-01;-- 先过滤再 JOINSELECT*FROM(SELECTid,nameFROMtable_aWHEREcreate_time2024-01-01)aJOINtable_b bONa.idb.a_id;3. 分页的坑-- 深度分页越往后越慢SELECT*FROMtableLIMIT1000000,20;-- 优化记住上一页最后一条的 idSELECT*FROMtableWHEREid1000000LIMIT20;四、连接池别小看这池子水早期项目直接每次查询创建连接QPS 到 500 就报Too many connections。后来上了连接池稳定支撑 3000 QPS。关键参数配置以 HikariCP 为例# 这里踩过坑maxLifetime 设太短会导致连接频繁重建hikari:maximum-pool-size:20# 根据业务调整不是越大越好minimum-idle:10max-lifetime:1800000# 30分钟别低于 30sconnection-timeout:3000# 获取连接超时时间idle-timeout:600000# 空闲连接超时监控指标要看活跃连接数空闲连接数等待获取连接的线程数连接创建/销毁频率五、ORM 的甜与苦用 Django ORM 或 SQLAlchemy 确实方便但生成的 SQL 可能很蠢。一定要开启 query log定期检查。案例# 这样写会产生 N1 查询usersUser.objects.filter(age__gt20)foruserinusers:print(user.profile.address)# 每次循环都查一次 profile# 优化使用 select_relatedusersUser.objects.select_related(profile).filter(age__gt20)六、个人经验包索引创建后要验证用EXPLAIN看执行计划关注type字段至少 range 以上rows越小越好。定期清理慢查询每周看一次慢查询日志超过 200ms 的都要分析。连接池不是银弹连接数设置要考虑数据库最大连接数max_connections留 20% 余量给管理连接。冷热数据分离3 个月前的订单数据归档到历史表主表只留热数据。压测时关注数据库CPU 使用率超过 70% 或连接数飙高就要考虑优化了。开发环境用真实数据量测试用 100 条数据测不出性能问题至少灌 10 万条。数据库优化是个细致活需要持续观察和调整。最好的优化时机是设计阶段最贵的优化时机是上线以后。先理清业务查询模式再动手加索引别凭感觉来。