数据库查询优化：从慢如蜗牛到快如闪电的实战手册

张

张建站

2026/4/24 3:14:58

10分钟阅读

数据库查询优化从慢如蜗牛到快如闪电的实战手册你的 SQL 为什么跑得比老年树懒还慢别急着加索引先看看这篇文章。作为一名在后端摸爬滚打多年的程序员我见过太多“简单得令人发指”的慢查询——明明就一张百万级数据的表却能把数据库 CPU 打到 100%让整个服务瘫痪。今天我就把自己这些年积累的查询优化经验毫无保留地分享给你。全文约 5000 字建议先收藏再细细品味。一、优化前先学会看病——定位慢查询没有 profiling 的优化都是耍流氓。1.1 打开慢查询日志MySQL 中你需要确保以下配置开启-- 查看慢查询是否开启SHOWVARIABLESLIKEslow_query_log%;SHOWVARIABLESLIKElong_query_time;-- 设置慢查询阈值建议 0.1 秒即 100 毫秒SETGLOBALlong_query_time0.1;SETGLOBALslow_query_logON;1.2 使用 EXPLAIN 分析执行计划每个 SQL 程序员都必须掌握的神器EXPLAIN。它能告诉你 MySQL 是如何执行你的查询的。EXPLAINSELECT*FROMorderWHEREuser_id12345;重点关注这几个字段字段重点关注内容typeALL全表扫描→ 最差range、ref、eq_ref→ 好const→ 最好possible_keys可能用到的索引如果没有说明没建索引key实际使用的索引如果是 NULL说明没用上rows估算需要扫描的行数越小越好ExtraUsing filesort、Using temporary是大忌说明需要额外排序或临时表一句话口诀type 不要 ALLkey 不要 NULLExtra 不要 filesort。二、索引优化——最锋利的武器也是最容易误用的2.1 索引不是越多越好很多人的误区遇到慢查询就加索引结果一张表建了十几个索引写入性能暴跌查询也不见好多少。原则为查询条件WHERE、排序ORDER BY、分组GROUP BY和连接列建索引。单个表的索引数建议控制在 5 个以内。定期清理未使用的索引通过sys.schema_unused_indexes查看。2.2 复合索引的最左前缀原则这是最常见也最容易踩的坑。假设我们建了复合索引(name, age, city)CREATEINDEXidx_name_age_cityONuser(name,age,city);以下查询能用到该索引WHERE name 张三✅WHERE name 张三 AND age 25✅WHERE name 张三 AND age 25 AND city 北京✅以下查询用不到该索引WHERE age 25❌没有从最左列开始WHERE city 北京❌WHERE name 张三 AND city 北京⚠️只用到了 name 部分age 被跳过优化技巧将筛选度最高即重复值最少的列放在复合索引的最左边。2.3 避免索引失效的常见写法错误写法原因正确写法WHERE id 1 10对索引列运算WHERE id 9WHERE name LIKE %张%前导通配符导致索引无法定位尽可能避免或用全文索引WHERE LEFT(name,2) 张三使用函数WHERE name LIKE 张三%WHERE age ! 25不等于大部分情况导致全表扫描考虑业务是否能改写为范围WHERE IS NULL / IS NOT NULL部分数据库导致索引失效可以尝试用默认值代替 NULLWHERE or1 OR or2如果两个条件不能同时走索引用UNION拆分2.4 覆盖索引——让你的查询“不回表”覆盖索引是指索引中已经包含了查询所需的所有字段因此不需要再回表查数据行。-- 假设有索引 (user_id, order_amount)SELECTuser_id,order_amountFROMordersWHEREuser_id123;-- 这个查询完全被索引覆盖Extra 中会出现 Using index如何判断EXPLAIN的 Extra 列显示Using index说明用了覆盖索引。三、SQL 语句优化——少一些骚操作多一些朴实3.1 只取需要的列-- 错误SELECT *-- 正确SELECT id, name, age理由减少网络传输和内存消耗。更容易实现覆盖索引。防止表结构变更导致程序出错。3.2 分页优化大偏移量是性能杀手传统分页在偏移量很大时如LIMIT 1000000, 20会扫描前面 100 万行然后丢弃极其低效。优化方案游标分页记住上一页的最后一条 IDSELECT*FROMordersWHEREid1000000LIMIT20;延迟关联先查主键再联查完整行SELECT*FROMordersJOIN(SELECTidFROMordersORDERBYidLIMIT1000000,20)AStmpONorders.idtmp.id;3.3 避免在循环中执行 SQLN1 问题这是 ORM 框架最经典的坑。# 错误N1 查询usersUser.objects.filter(age__gt18)foruserinusers:ordersOrder.objects.filter(user_iduser.id)# 循环内查 DB优化用IN或JOIN一次性查出来。SELECT*FROMordersWHEREuser_idIN(SELECTidFROMusersWHEREage18);-- 或者用 JOINSELECT*FROMorders oJOINusers uONo.user_idu.idWHEREu.age18;3.4 用 EXISTS 代替 IN当子查询结果集很大时-- 当外部表小、子查询大时EXISTS 通常更快SELECT*FROMorders oWHEREEXISTS(SELECT1FROMusers uWHEREu.ido.user_idANDu.age18);3.5 尽量避免使用 OROR可能导致索引失效。可以用UNION ALL拆分-- 慢SELECT*FROMuserWHEREname张三ORage25;-- 快如果分别有索引SELECT*FROMuserWHEREname张三UNIONALLSELECT*FROMuserWHEREage25ANDname!张三;-- 注意去重四、表结构与设计优化4.1 选择合适的数据类型能用INT不用VARCHAR能用SMALLINT不用INT。VARCHAR长度不要盲目给 255按实际需要。能使用NOT NULL尽量使用索引处理 NULL 更复杂。时间类型用TIMESTAMP4 字节还是DATETIME8 字节TIMESTAMP只能到 2038 年谨慎。4.2 拆分大表垂直拆分将不常用字段如text类型放到扩展表。水平拆分分表按时间、按 ID 取模等。达到千万级数据量可以考虑但会增加代码复杂度。4.3 冗余字段——反范式的权衡在查询频繁且需要关联多表时适当增加冗余字段可以避免 JOIN。例如订单表直接存储用户昵称虽然会随用户修改而需要同步但避免了每次查询都 JOIN 用户表。五、数据库配置与硬件有时候不是 SQL 的锅而是数据库没调优。参数作用建议值innodb_buffer_pool_sizeInnoDB 缓存数据和索引物理内存的 70%-80%query_cache_size查询缓存MySQL 8.0 已移除8.0 之前建议关闭因为失效频繁innodb_log_file_sizeRedo Log 大小1-2 GBmax_connections最大连接数根据业务通常 500-1000硬件层面将数据库放在 SSD 上机械硬盘是随机 I/O 的噩梦。内存越大越好InnoDB 倾向于将所有热点数据和索引缓存到内存。六、真实案例一个 300ms 的查询如何优化到 5ms原始表结构CREATETABLEorders(idBIGINTPRIMARYKEY,user_idINT,amountDECIMAL(10,2),statusTINYINT,create_timeDATETIME);-- 数据量500 万行原始查询耗时 320msSELECT*FROMordersWHEREuser_id12345ORDERBYcreate_timeDESCLIMIT10;EXPLAIN 分析type ALL全表扫描rows 约 500 万Extra Using filesort优化步骤建索引CREATE INDEX idx_user_time ON orders (user_id, create_time);此时 type 变成 refrows 降到几十行。Extra 中Using filesort消失因为索引已经有序。耗时降到 15ms。改成覆盖索引如果只查少量字段比如只需要id, amount, status可以建(user_id, create_time, amount, status)。但注意索引不要过大。最终耗时 5ms提升 60 倍。七、优化顺序总结记住这个清单当你发现查询慢时按以下顺序排查定位慢查询慢查询日志 EXPLAIN看 type是不是ALL是的话检查 WHERE 条件是否有索引看 key实际用到的索引对不对有没有符合最左前缀看 Extra有没有Using filesort、Using temporary有的话考虑优化排序或分组检查数据量是不是表已经几千万了考虑分库分表检查数据库配置buffer_pool够不够考虑业务逻辑能否改成异步、缓存或预处理八、写在最后查询优化是一门实践科学没有银弹。最好的方法就是理解你的数据理解你的索引理解你的执行计划。下次遇到慢查询别急着拍桌子骂 DBA先EXPLAIN一下八成能自己搞定。如果你觉得这篇文章对你有帮助欢迎点赞、转发、评论。思考题SELECT COUNT(*) FROM large_table为什么会很慢有什么优化方法欢迎评论区讨论。