尧图网页设计 
直接开门见山吧。这个系列写到这里从建表、改表、增删数据一路过来终于到了最常打交道的一环——查询。MySQL表的基本查询说白了就是围绕SELECT展开的各种玩法但玩法多不代表可以乱玩。我见过太多同行写了几年的SELECT遇到慢查询还是只会加索引遇到GROUP BY报错还是只会百度遇到分页深翻页还是硬着头皮LIMIT 100000, 20。这篇文章不打算按官方文档那种顺序给你罗列子句而是按照“拿到需求怎么拆解 - 单表查询的实战细节 - 分组聚合与多表查询 - 怎么用执行计划和索引把查询调快 - 高频报错排查”这个思路来写。适合刚把SELECT语法学完、准备上手干活的新手也适合那些写了几年 SQL 但对底层执行细节一知半解、想系统查漏补缺的人。1. 查询设计与执行顺序先拆需求再写SQL1.1 拿到查询需求第一件事不是写SQL很多人拿到需求上来就写SELECT * FROM ...然后一步步加条件最后发现SQL又长又乱性能还差。我的习惯是先花一两分钟把需求翻译成“查什么表、要哪些字段、过滤什么行、要不要分组、怎么排序、取多少条”。举个例子业务方说“查最近30天每个品类的销售额按销售额倒序只要前3名。”翻译一下就是数据源订单表时间字段是下单时间过滤条件下单时间 最近30天分组维度品类category_id聚合方式销售额求和SUM(amount)排序按销售额倒序限制条数3条对应到SQL就是SELECT category_id, SUM(amount) AS total_amount FROM orders WHERE order_time NOW() - INTERVAL 30 DAY GROUP BY category_id ORDER BY total_amount DESC LIMIT 3;这套翻译过程看起来很基础但很多人栽在思路上先写了SELECT再想WHERE最后发现要加GROUP BY又开始纠结WHERE和HAVING到底该用哪个。如果一开始就按“数据源 - 过滤 - 分组 - 投影 - 排序 - 限制”这个流程去想基本不会乱。1.2 SQL逻辑执行顺序和书写顺序完全两码事这是新手最容易懵的点。SELECT的书写顺序是SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY - LIMIT但数据库引擎的逻辑执行顺序是FROM确定数据源可能涉及多张表JOINWHERE对源数据做行级过滤GROUP BY按列分组HAVING对分组后的结果做过滤SELECT投影需要的列计算表达式ORDER BY排序LIMIT限制返回行数把这个顺序刻在脑子里很多问题会瞬间想通。比如为什么WHERE里不能用聚合函数因为执行WHERE的时候GROUP BY还没执行聚合结果压根不存在。为什么WHERE里不能直接用SELECT里定义的别名因为SELECT在WHERE后面才执行。为什么HAVING能用聚合函数因为它执行在分组之后。逻辑顺序还有一层实际意义WHERE先把数据过滤掉一批能显著减少后续GROUP BY、ORDER BY处理的数据量。很多人写SQL时把能下推到WHERE的条件放到HAVING里表面上看结果一样但性能可能差出几个数量级。记住一个原则能早过滤就早过滤。2. 单表查询的核心细节WHERE、排序与分页的坑2.1 WHERE条件组合优先级是隐形的雷区单表查询里WHERE是最容易出现逻辑错误的环节。最常见的是AND和OR混用时不加括号。SQL里AND优先级高于OR但大多数人写代码时不会刻意记这个优先级往往凭直觉理解。看这条SQLSELECT * FROM orders WHERE status paid OR status pending AND amount 100;你以为它是“状态为 paid或者状态为 pending 且金额大于100”实际上它执行的是“状态为 paid或者状态为 pending 且金额大于100”。如果同一个表里paid和pending想用不同的附加条件就必须用括号明确表达SELECT * FROM orders WHERE (status paid OR status pending) AND amount 100;我见过不止一次生产事故就是因为少了一对括号导致本应只查两种状态的数据结果多查出一堆其他状态的行。这种问题不上线压测很难发现因为数据量小的时候结果差异不明显一旦数据量上来错误逻辑带来的脏数据会让你怀疑人生。WHERE里还有一个新手高频踩坑点NULL值判断。NULL不代表“空字符串”也不代表“0”它代表“未知”。所以用 NULL去查永远查不出任何行必须用IS NULL或IS NOT NULL。此外如果你在WHERE里写了column ! value那么column为NULL的行也不会被查出来因为NULL参与比较的结果是“未知”不满足“不等于”这个条件。这个特性经常导致统计数据对不上排查起来还特别隐蔽。2.2 隐式类型转换索引失效的隐形杀手再说一个实战里特别容易忽略的问题隐式类型转换。如果字段是字符串类型但你传入的是数字MySQL会尝试把字段值转换成数字再比较。问题是一旦对索引列做了函数或转换操作索引就失效了。举个真实例子。用户表user的手机号phone是VARCHAR(11)类型你写了SELECT * FROM user WHERE phone 13800138000;这条SQL看着没毛病但phone字段是字符串右侧是数字MySQL会隐式地把phone转成数字再去比较。结果就是索引idx_phone无法被正常使用执行计划直接变成全表扫描。正确写法是写成字符串SELECT * FROM user WHERE phone 13800138000;这东西在数据量小的本地环境完全感受不到差异放到几百万行的大表上同一个查询可能从几十毫秒变成几秒钟。写SQL时一定要检查字段类型是什么传入值的类型是什么保持两边一致。2.3 ORDER BY排序细节里的脏水排序看着简单但其中暗坑不少。先说多字段排序。ORDER BY column1, column2的意思是“先按 column1 排column1 相同时再按 column2 排”不是“两列分别排序后拼接”。如果你要一列升序一列降序需要显式指定ASC或DESCSELECT * FROM products ORDER BY category_id ASC, price DESC;再说NULL值的排序。MySQL默认NULL在升序时排在最前面降序时排在最后面。这经常不是业务想要的结果。如果想让NULL排在最后面可以用SELECT * FROM users ORDER BY ISNULL(age), age ASC;ISNULL(age)对NULL返回1对非NULL返回0这样排序时非NULL行在前NULL行统一沉底。5.7没有NULLS LAST这种写法用这个技巧就够了。8.0虽然支持NULLS LAST但社区版5.7用户量仍然很大这个技巧值得记住。排序还牵扯一个性能问题。如果ORDER BY的字段没有索引MySQL就需要把结果集加载到临时表做文件排序filesort。数据量大时排序成本极高这在后面执行计划部分会展开讲。2.4 LIMIT深分页经典性能陷阱LIMIT offset, count的分页方式是所有后端开发最早学会的。但等表数据涨到千万级你会发现一个残酷事实LIMIT 1000000, 20不是只查20条而是先扫描前100万条然后丢弃前999980条最后才返回20条。offset越大扫描越深查询越慢。两种常用优化手段分享给你。第一种记录上一页的最大ID下一页用WHERE id 上一页最大id ORDER BY id LIMIT 20。这种“键值分页”方式彻底避免了offset深翻前提是排序字段是主键或有唯一索引。-- 第一页 SELECT * FROM orders ORDER BY id LIMIT 20; -- 第二页假设上一页最后一条 id 12345 SELECT * FROM orders WHERE id 12345 ORDER BY id LIMIT 20;第二种延迟关联。适用于排序字段不是主键、且要查询的字段很多无法避免回表的场景。先把排序字段和主键查出来再与原表关联取完整行SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY amount DESC LIMIT 1000000, 20 ) t ON o.id t.id;核心思路是先走覆盖索引把主键取出来只对索引数据做深分页再去回表取完整记录。内层查询扫描的仍然是一样多的数据但因为它只取了索引字段IO开销远小于取全表数据行。3. 分组聚合与多表查询JOIN和GROUP BY的组合拳3.1 COUNT、SUM与GROUP BY的几个细节坑先聊COUNT。COUNT(*)、COUNT(1)、COUNT(主键)、COUNT(字段)在结果上有细微但重要的区别。COUNT(*)统计行数包括NULL行COUNT(字段)只统计该字段非NULL的行。所以在对可空字段计数时COUNT(字段)会比你预期的行数少这在统计报表场景里是常见的数据不一致根因。-- 统计订单总数 SELECT COUNT(*) FROM orders; -- 统计有优惠券的订单数 SELECT COUNT(coupon_id) FROM orders;两者的执行效率在当前版本的InnoDB下基本没有差异不必纠结网上流传的“COUNT(1)比COUNT(*)快”的旧说法。再说GROUP BY与HAVING的配合。WHERE在分组前过滤原始行HAVING在分组后过滤聚合结果。比如“查金额大于100的订单中每个用户的订单总金额超过1000的用户”SELECT user_id, SUM(amount) AS total FROM orders WHERE amount 100 GROUP BY user_id HAVING total 1000;注意WHERE和HAVING不能互换。如果你把amount 100放到HAVING里数据库会先把所有金额的订单都分组聚合一遍再过滤掉小金额的组白算了很多数据性能差很多。能下推到WHERE的条件绝不放HAVING。还有一个几乎每个MySQL开发者都踩过的坑ONLY_FULL_GROUP_BY报错错误码1055。默认开启这个模式后SELECT中出现的非聚合列必须出现在GROUP BY里。5.7默认开启很多人从5.6升级上来后老SQL突然开始报错-- 报错select的name不在group by中 SELECT user_id, name, COUNT(*) FROM orders GROUP BY user_id;解决办法不是关掉ONLY_FULL_GROUP_BY而是改SQL要么把name加进GROUP BY要么用ANY_VALUE(name)显式声明“这个字段取任意一条的值”。很多资料让你改sql_mode但我建议谨慎对待因为ONLY_FULL_GROUP_BY能防止写出语义不确定的SQL为了保证查询结果可预期别轻易关。3.2 多表JOIN连接条件必须想清楚多表查询是基本查询系列里的一道分水岭。INNER JOIN取两表交集LEFT JOIN保留左表全部记录右表无匹配就补NULL。这个定义本身很容易理解难的是写出正确的关联条件。实战里最常见的错误是连接条件没写全导致结果行数膨胀。比如事实表和维度表关联时如果维度表里有重复记录关联后事实行会被翻倍复制。我处理过一个真实案例订单明细关联商品分类时因为分类表里有几条重复记录报表的销售金额直接翻了三倍。排查半天最后发现是多对多关联导致笛卡尔积局部放大。解决思路是先确认关联字段在关联表里是否唯一。如果不唯一用子查询或GROUP BY先对关联表去重再参与JOIN。子查询和JOIN的选择也是一个老生常谈的问题。INNER JOIN多数时候可以改写成子查询反之亦然。我的经验是能用JOIN的尽量用JOIN因为优化器对JOIN的优化空间更大如调整连接顺序、使用BNL等。但子查询在某些场景下更直观比如“查用户表中哪些用户下过订单”用EXISTS子查询语义清晰且可以提前终止扫描SELECT * FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id );这里还有个“小表驱动大表”的原则EXISTS更适合外表小、内表大的场景因为EXISTS只需要判断存在性找到第一条匹配就会返回。而IN适合外表大、内表小的场景。不过在MySQL 5.7及以上版本里优化器会自动做半连接优化二者差距逐渐变小。作为开发者把SQL语义写清楚比纠结微优化更重要。3.3 场景延伸跨表合并与自动拉表热搜词里有个“跨表合并”顺带提一下。如果多张表结构一致需要合并结果集展示用UNION或UNION ALL。UNION会去重UNION ALL不去重。没有去重需求时务必用UNION ALL因为去重需要额外排序或哈希消耗不小SELECT id, name, user AS source FROM users UNION ALL SELECT id, name, member AS source FROM members;另外那个热搜“python如何连接公司系统实现自动拉表”本质上就是把你手写好的SQL通过Python的pymysql或SQLAlchemy执行然后把结果集写入Excel或数仓。很多人的误区是以为自动拉表需要重新写一套查询逻辑其实完全不是你的核心资产依然是SQL本身——尤其是查询的性能和正确性。把本文的查询细节掌握好用Python封装只是半小时的活。4. 查询效率排查用EXPLAIN和执行计划说话4.1 别猜了用EXPLAIN看真相优化查询最忌讳拍脑袋。是索引没用上还是排序代价太高还是扫描行数太多——一条EXPLAIN全部告诉你。EXPLAIN SELECT user_id, SUM(amount) FROM orders WHERE status paid GROUP BY user_id;执行结果里重点看几列列名含义重点关注type访问类型const eq_ref ref range index ALL看到ALL要警惕key实际使用的索引NULL 说明没走索引rows预估扫描行数数字越大越危险Extra附加信息出现Using filesort、Using temporary说明有额外排序或临时表开销type字段是判断查询质量的第一个信号。ALL是全表扫描通常意味着SQL有严重问题。index是扫描了整个索引树比ALL好一些但也不算高效。range是范围扫描比如WHERE id 100还算可以。ref和eq_ref是等值匹配在JOIN场景里算是健康状态。const是最理想状态用主键或唯一索引等值查询。Extra里看到Using filesort要留意。它不代表真的在磁盘上排序而是说明这个排序字段没走索引需要额外排序操作。大结果集上的filesort代价很高。看到Using temporary同理说明查询创建了临时表通常和GROUP BY、DISTINCT有关。实际排查时我的习惯是拿一条慢SQL出来先看type是不是ALL再看key是不是NULL然后看rows估算扫描量最后看Extra有没有排序和临时表。四步走完问题基本定位到七七八八了。4.2 索引回表与覆盖索引为什么查了个寂寞这是索引优化的核心知识点也和热搜词里“辅助索引如何避免回表”直接相关。InnoDB的索引分两类主键索引聚簇索引和辅助索引二级索引。主键索引的叶子节点存的是整行数据所以用主键查数据一次索引查找就拿到了全部字段。但辅助索引的叶子节点只存了索引列和主键值。比如你在name字段上建了索引那么WHERE name 张三时先查到的是(name, 主键id)然后还得拿着主键id再去主键索引里找一次整行数据。这个第二次查找就是“回表”。回表本身不致命但数据量大时每一行都回表就意味着大量随机IO。避免回表的思路是“覆盖索引”——让辅助索引覆盖你要查询的所有字段。-- 假设在 (name, status) 上有联合索引 idx_name_status SELECT name, status FROM users WHERE name 张三;这条SQL只涉及name和status两个字段它们都在索引idx_name_status中MySQL直接从索引中就能返回结果不需要回表。EXPLAIN的Extra列会出现Using index这就是覆盖索引的标志。要掌握两个实操要点。第一联合索引的字段顺序影响巨大等值条件字段放前面范围条件字段放后面。(name, status)能命中WHERE name 张三 AND status 1但(status, name)在同样的查询里可能无法高效使用。第二不要为了覆盖索引无限加字段索引列越多写入开销越大索引文件也越大。通常优先覆盖高频查询的字段即可。几千万行大表场景下尤其是前后端开发经常写的那种SELECT *风格一查就是整行数据覆盖索引很难生效。这种时候就得靠延迟关联或者改写查询字段去掉不必要的列。5. 高频问题与排查技巧实录5.1 慢查询定位三板斧线上遇到查询慢先别急着加索引。按下面的顺序排查效率最高开启或查看慢查询日志。MySQL里把slow_query_log打开然后设置long_query_time 1超过1秒的查询会被记录。也可以现场用EXPLAIN直接分析。复现并拿到EXPLAIN结果看type和key。发现全表扫描时先检查WHERE条件字段是否有索引再检查是否因为函数、隐式转换导致索引失效。如果索引没问题看Extra里有没有Using filesort或Using temporary。有的话优先改造排序和分组逻辑比如把排序字段纳入索引。最后才考虑业务层面优化是否一定要查实时数据能否走缓存能否归档历史数据能否分库分表。这套排查路径我在几次生产事故里验证过基本能覆盖90%的慢查询场景。5.2 常见报错速查表错误码报错信息常见原因解决方案1055Expression not in GROUP BYONLY_FULL_GROUP_BY模式下非聚合列未分组将列加入GROUP BY或使用ANY_VALUE()1267Illegal mix of collationsJOIN两表字符集或排序规则不一致统一表字段的字符集和排序规则1172Subquery returns more than 1 row子查询返回多行但用于单值比较改用IN或EXISTS1071Specified key was too long索引字段长度超过InnoDB限制减少索引列长度或改用前缀索引1205Lock wait timeout exceeded查询等待行锁超时检查事务是否未提交优化事务范围3156SSL connection error客户端与服务端SSL协商失败检查SSL证书配置或调整连接参数这里多说一句关于SSL连接错误。不少人在用工具连接数据库时报SSL相关错误第一反应就是关掉SSL其实这不是最稳妥的做法。私有网络环境下为了快速排查可以临时禁用SSL但生产环境建议优先检查服务端证书是否有效、时区是否正常、客户端是否做了SNI校验。当然如果是开发环境且安全要求不高通过连接参数关闭SSL是最快的解法这个取舍看具体场景。5.3 两个值得一提的“常规反面教材”第一个是ORDER BY RAND()随机取行。很多人取几条随机记录时直接ORDER BY RAND() LIMIT 5这条SQL会对全表数据做随机排序几万行就明显变慢百万行直接卡死。更优解是取主键范围随机生成几个ID值然后WHERE id IN (…)。第二个是SELECT *的滥用。不只是返回多余字段浪费带宽更严重的是它让覆盖索引基本失效因为绝大多数索引都覆盖不了全部字段。尤其在多表JOIN的场景每个表都SELECT *回表数量是灾难级的。尽量显式列出需要的字段这是成本几乎为零的优化。写在最后的一点体会从SELECT 1到能处理千万级大表的查询这个过程中我觉得最关键的不是背语法而是建立两层直觉第一层是对SQL执行顺序的直觉能预判一条SQL里的条件、分组、排序在什么阶段生效第二层是对执行计划的直觉看到慢查询能在心里推演出表是怎么被访问的、索引是怎么被使用的。这两层直觉没捷径只能靠多写、多分析、多踩坑。建议你现在就找一条自己项目里的慢查询开个EXPLAIN跑一遍对着本文第四节的内容逐列看一遍把type、key、rows、Extra的含义落到真实数据上。下次再碰到查询问题你就不会再是搜报错、碰运气的心态而是能自己一步步拆到根因。系列后续如果大家有兴趣我可以再聊聊存储过程、窗口函数、分区表这类进阶主题。尤其窗口函数在报表统计里能替代掉很多复杂的自治查询值得单独开一篇。 MySQL查询优化实战：从SELECT语法到索引与慢查询排查
直接开门见山吧。这个系列写到这里从建表、改表、增删数据一路过来终于到了最常打交道的一环——查询。MySQL表的基本查询说白了就是围绕SELECT展开的各种玩法但玩法多不代表可以乱玩。我见过太多同行写了几年的SELECT遇到慢查询还是只会加索引遇到GROUP BY报错还是只会百度遇到分页深翻页还是硬着头皮LIMIT 100000, 20。这篇文章不打算按官方文档那种顺序给你罗列子句而是按照“拿到需求怎么拆解 - 单表查询的实战细节 - 分组聚合与多表查询 - 怎么用执行计划和索引把查询调快 - 高频报错排查”这个思路来写。适合刚把SELECT语法学完、准备上手干活的新手也适合那些写了几年 SQL 但对底层执行细节一知半解、想系统查漏补缺的人。1. 查询设计与执行顺序先拆需求再写SQL1.1 拿到查询需求第一件事不是写SQL很多人拿到需求上来就写SELECT * FROM ...然后一步步加条件最后发现SQL又长又乱性能还差。我的习惯是先花一两分钟把需求翻译成“查什么表、要哪些字段、过滤什么行、要不要分组、怎么排序、取多少条”。举个例子业务方说“查最近30天每个品类的销售额按销售额倒序只要前3名。”翻译一下就是数据源订单表时间字段是下单时间过滤条件下单时间 最近30天分组维度品类category_id聚合方式销售额求和SUM(amount)排序按销售额倒序限制条数3条对应到SQL就是SELECT category_id, SUM(amount) AS total_amount FROM orders WHERE order_time NOW() - INTERVAL 30 DAY GROUP BY category_id ORDER BY total_amount DESC LIMIT 3;这套翻译过程看起来很基础但很多人栽在思路上先写了SELECT再想WHERE最后发现要加GROUP BY又开始纠结WHERE和HAVING到底该用哪个。如果一开始就按“数据源 - 过滤 - 分组 - 投影 - 排序 - 限制”这个流程去想基本不会乱。1.2 SQL逻辑执行顺序和书写顺序完全两码事这是新手最容易懵的点。SELECT的书写顺序是SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY - LIMIT但数据库引擎的逻辑执行顺序是FROM确定数据源可能涉及多张表JOINWHERE对源数据做行级过滤GROUP BY按列分组HAVING对分组后的结果做过滤SELECT投影需要的列计算表达式ORDER BY排序LIMIT限制返回行数把这个顺序刻在脑子里很多问题会瞬间想通。比如为什么WHERE里不能用聚合函数因为执行WHERE的时候GROUP BY还没执行聚合结果压根不存在。为什么WHERE里不能直接用SELECT里定义的别名因为SELECT在WHERE后面才执行。为什么HAVING能用聚合函数因为它执行在分组之后。逻辑顺序还有一层实际意义WHERE先把数据过滤掉一批能显著减少后续GROUP BY、ORDER BY处理的数据量。很多人写SQL时把能下推到WHERE的条件放到HAVING里表面上看结果一样但性能可能差出几个数量级。记住一个原则能早过滤就早过滤。2. 单表查询的核心细节WHERE、排序与分页的坑2.1 WHERE条件组合优先级是隐形的雷区单表查询里WHERE是最容易出现逻辑错误的环节。最常见的是AND和OR混用时不加括号。SQL里AND优先级高于OR但大多数人写代码时不会刻意记这个优先级往往凭直觉理解。看这条SQLSELECT * FROM orders WHERE status paid OR status pending AND amount 100;你以为它是“状态为 paid或者状态为 pending 且金额大于100”实际上它执行的是“状态为 paid或者状态为 pending 且金额大于100”。如果同一个表里paid和pending想用不同的附加条件就必须用括号明确表达SELECT * FROM orders WHERE (status paid OR status pending) AND amount 100;我见过不止一次生产事故就是因为少了一对括号导致本应只查两种状态的数据结果多查出一堆其他状态的行。这种问题不上线压测很难发现因为数据量小的时候结果差异不明显一旦数据量上来错误逻辑带来的脏数据会让你怀疑人生。WHERE里还有一个新手高频踩坑点NULL值判断。NULL不代表“空字符串”也不代表“0”它代表“未知”。所以用 NULL去查永远查不出任何行必须用IS NULL或IS NOT NULL。此外如果你在WHERE里写了column ! value那么column为NULL的行也不会被查出来因为NULL参与比较的结果是“未知”不满足“不等于”这个条件。这个特性经常导致统计数据对不上排查起来还特别隐蔽。2.2 隐式类型转换索引失效的隐形杀手再说一个实战里特别容易忽略的问题隐式类型转换。如果字段是字符串类型但你传入的是数字MySQL会尝试把字段值转换成数字再比较。问题是一旦对索引列做了函数或转换操作索引就失效了。举个真实例子。用户表user的手机号phone是VARCHAR(11)类型你写了SELECT * FROM user WHERE phone 13800138000;这条SQL看着没毛病但phone字段是字符串右侧是数字MySQL会隐式地把phone转成数字再去比较。结果就是索引idx_phone无法被正常使用执行计划直接变成全表扫描。正确写法是写成字符串SELECT * FROM user WHERE phone 13800138000;这东西在数据量小的本地环境完全感受不到差异放到几百万行的大表上同一个查询可能从几十毫秒变成几秒钟。写SQL时一定要检查字段类型是什么传入值的类型是什么保持两边一致。2.3 ORDER BY排序细节里的脏水排序看着简单但其中暗坑不少。先说多字段排序。ORDER BY column1, column2的意思是“先按 column1 排column1 相同时再按 column2 排”不是“两列分别排序后拼接”。如果你要一列升序一列降序需要显式指定ASC或DESCSELECT * FROM products ORDER BY category_id ASC, price DESC;再说NULL值的排序。MySQL默认NULL在升序时排在最前面降序时排在最后面。这经常不是业务想要的结果。如果想让NULL排在最后面可以用SELECT * FROM users ORDER BY ISNULL(age), age ASC;ISNULL(age)对NULL返回1对非NULL返回0这样排序时非NULL行在前NULL行统一沉底。5.7没有NULLS LAST这种写法用这个技巧就够了。8.0虽然支持NULLS LAST但社区版5.7用户量仍然很大这个技巧值得记住。排序还牵扯一个性能问题。如果ORDER BY的字段没有索引MySQL就需要把结果集加载到临时表做文件排序filesort。数据量大时排序成本极高这在后面执行计划部分会展开讲。2.4 LIMIT深分页经典性能陷阱LIMIT offset, count的分页方式是所有后端开发最早学会的。但等表数据涨到千万级你会发现一个残酷事实LIMIT 1000000, 20不是只查20条而是先扫描前100万条然后丢弃前999980条最后才返回20条。offset越大扫描越深查询越慢。两种常用优化手段分享给你。第一种记录上一页的最大ID下一页用WHERE id 上一页最大id ORDER BY id LIMIT 20。这种“键值分页”方式彻底避免了offset深翻前提是排序字段是主键或有唯一索引。-- 第一页 SELECT * FROM orders ORDER BY id LIMIT 20; -- 第二页假设上一页最后一条 id 12345 SELECT * FROM orders WHERE id 12345 ORDER BY id LIMIT 20;第二种延迟关联。适用于排序字段不是主键、且要查询的字段很多无法避免回表的场景。先把排序字段和主键查出来再与原表关联取完整行SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY amount DESC LIMIT 1000000, 20 ) t ON o.id t.id;核心思路是先走覆盖索引把主键取出来只对索引数据做深分页再去回表取完整记录。内层查询扫描的仍然是一样多的数据但因为它只取了索引字段IO开销远小于取全表数据行。3. 分组聚合与多表查询JOIN和GROUP BY的组合拳3.1 COUNT、SUM与GROUP BY的几个细节坑先聊COUNT。COUNT(*)、COUNT(1)、COUNT(主键)、COUNT(字段)在结果上有细微但重要的区别。COUNT(*)统计行数包括NULL行COUNT(字段)只统计该字段非NULL的行。所以在对可空字段计数时COUNT(字段)会比你预期的行数少这在统计报表场景里是常见的数据不一致根因。-- 统计订单总数 SELECT COUNT(*) FROM orders; -- 统计有优惠券的订单数 SELECT COUNT(coupon_id) FROM orders;两者的执行效率在当前版本的InnoDB下基本没有差异不必纠结网上流传的“COUNT(1)比COUNT(*)快”的旧说法。再说GROUP BY与HAVING的配合。WHERE在分组前过滤原始行HAVING在分组后过滤聚合结果。比如“查金额大于100的订单中每个用户的订单总金额超过1000的用户”SELECT user_id, SUM(amount) AS total FROM orders WHERE amount 100 GROUP BY user_id HAVING total 1000;注意WHERE和HAVING不能互换。如果你把amount 100放到HAVING里数据库会先把所有金额的订单都分组聚合一遍再过滤掉小金额的组白算了很多数据性能差很多。能下推到WHERE的条件绝不放HAVING。还有一个几乎每个MySQL开发者都踩过的坑ONLY_FULL_GROUP_BY报错错误码1055。默认开启这个模式后SELECT中出现的非聚合列必须出现在GROUP BY里。5.7默认开启很多人从5.6升级上来后老SQL突然开始报错-- 报错select的name不在group by中 SELECT user_id, name, COUNT(*) FROM orders GROUP BY user_id;解决办法不是关掉ONLY_FULL_GROUP_BY而是改SQL要么把name加进GROUP BY要么用ANY_VALUE(name)显式声明“这个字段取任意一条的值”。很多资料让你改sql_mode但我建议谨慎对待因为ONLY_FULL_GROUP_BY能防止写出语义不确定的SQL为了保证查询结果可预期别轻易关。3.2 多表JOIN连接条件必须想清楚多表查询是基本查询系列里的一道分水岭。INNER JOIN取两表交集LEFT JOIN保留左表全部记录右表无匹配就补NULL。这个定义本身很容易理解难的是写出正确的关联条件。实战里最常见的错误是连接条件没写全导致结果行数膨胀。比如事实表和维度表关联时如果维度表里有重复记录关联后事实行会被翻倍复制。我处理过一个真实案例订单明细关联商品分类时因为分类表里有几条重复记录报表的销售金额直接翻了三倍。排查半天最后发现是多对多关联导致笛卡尔积局部放大。解决思路是先确认关联字段在关联表里是否唯一。如果不唯一用子查询或GROUP BY先对关联表去重再参与JOIN。子查询和JOIN的选择也是一个老生常谈的问题。INNER JOIN多数时候可以改写成子查询反之亦然。我的经验是能用JOIN的尽量用JOIN因为优化器对JOIN的优化空间更大如调整连接顺序、使用BNL等。但子查询在某些场景下更直观比如“查用户表中哪些用户下过订单”用EXISTS子查询语义清晰且可以提前终止扫描SELECT * FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id );这里还有个“小表驱动大表”的原则EXISTS更适合外表小、内表大的场景因为EXISTS只需要判断存在性找到第一条匹配就会返回。而IN适合外表大、内表小的场景。不过在MySQL 5.7及以上版本里优化器会自动做半连接优化二者差距逐渐变小。作为开发者把SQL语义写清楚比纠结微优化更重要。3.3 场景延伸跨表合并与自动拉表热搜词里有个“跨表合并”顺带提一下。如果多张表结构一致需要合并结果集展示用UNION或UNION ALL。UNION会去重UNION ALL不去重。没有去重需求时务必用UNION ALL因为去重需要额外排序或哈希消耗不小SELECT id, name, user AS source FROM users UNION ALL SELECT id, name, member AS source FROM members;另外那个热搜“python如何连接公司系统实现自动拉表”本质上就是把你手写好的SQL通过Python的pymysql或SQLAlchemy执行然后把结果集写入Excel或数仓。很多人的误区是以为自动拉表需要重新写一套查询逻辑其实完全不是你的核心资产依然是SQL本身——尤其是查询的性能和正确性。把本文的查询细节掌握好用Python封装只是半小时的活。4. 查询效率排查用EXPLAIN和执行计划说话4.1 别猜了用EXPLAIN看真相优化查询最忌讳拍脑袋。是索引没用上还是排序代价太高还是扫描行数太多——一条EXPLAIN全部告诉你。EXPLAIN SELECT user_id, SUM(amount) FROM orders WHERE status paid GROUP BY user_id;执行结果里重点看几列列名含义重点关注type访问类型const eq_ref ref range index ALL看到ALL要警惕key实际使用的索引NULL 说明没走索引rows预估扫描行数数字越大越危险Extra附加信息出现Using filesort、Using temporary说明有额外排序或临时表开销type字段是判断查询质量的第一个信号。ALL是全表扫描通常意味着SQL有严重问题。index是扫描了整个索引树比ALL好一些但也不算高效。range是范围扫描比如WHERE id 100还算可以。ref和eq_ref是等值匹配在JOIN场景里算是健康状态。const是最理想状态用主键或唯一索引等值查询。Extra里看到Using filesort要留意。它不代表真的在磁盘上排序而是说明这个排序字段没走索引需要额外排序操作。大结果集上的filesort代价很高。看到Using temporary同理说明查询创建了临时表通常和GROUP BY、DISTINCT有关。实际排查时我的习惯是拿一条慢SQL出来先看type是不是ALL再看key是不是NULL然后看rows估算扫描量最后看Extra有没有排序和临时表。四步走完问题基本定位到七七八八了。4.2 索引回表与覆盖索引为什么查了个寂寞这是索引优化的核心知识点也和热搜词里“辅助索引如何避免回表”直接相关。InnoDB的索引分两类主键索引聚簇索引和辅助索引二级索引。主键索引的叶子节点存的是整行数据所以用主键查数据一次索引查找就拿到了全部字段。但辅助索引的叶子节点只存了索引列和主键值。比如你在name字段上建了索引那么WHERE name 张三时先查到的是(name, 主键id)然后还得拿着主键id再去主键索引里找一次整行数据。这个第二次查找就是“回表”。回表本身不致命但数据量大时每一行都回表就意味着大量随机IO。避免回表的思路是“覆盖索引”——让辅助索引覆盖你要查询的所有字段。-- 假设在 (name, status) 上有联合索引 idx_name_status SELECT name, status FROM users WHERE name 张三;这条SQL只涉及name和status两个字段它们都在索引idx_name_status中MySQL直接从索引中就能返回结果不需要回表。EXPLAIN的Extra列会出现Using index这就是覆盖索引的标志。要掌握两个实操要点。第一联合索引的字段顺序影响巨大等值条件字段放前面范围条件字段放后面。(name, status)能命中WHERE name 张三 AND status 1但(status, name)在同样的查询里可能无法高效使用。第二不要为了覆盖索引无限加字段索引列越多写入开销越大索引文件也越大。通常优先覆盖高频查询的字段即可。几千万行大表场景下尤其是前后端开发经常写的那种SELECT *风格一查就是整行数据覆盖索引很难生效。这种时候就得靠延迟关联或者改写查询字段去掉不必要的列。5. 高频问题与排查技巧实录5.1 慢查询定位三板斧线上遇到查询慢先别急着加索引。按下面的顺序排查效率最高开启或查看慢查询日志。MySQL里把slow_query_log打开然后设置long_query_time 1超过1秒的查询会被记录。也可以现场用EXPLAIN直接分析。复现并拿到EXPLAIN结果看type和key。发现全表扫描时先检查WHERE条件字段是否有索引再检查是否因为函数、隐式转换导致索引失效。如果索引没问题看Extra里有没有Using filesort或Using temporary。有的话优先改造排序和分组逻辑比如把排序字段纳入索引。最后才考虑业务层面优化是否一定要查实时数据能否走缓存能否归档历史数据能否分库分表。这套排查路径我在几次生产事故里验证过基本能覆盖90%的慢查询场景。5.2 常见报错速查表错误码报错信息常见原因解决方案1055Expression not in GROUP BYONLY_FULL_GROUP_BY模式下非聚合列未分组将列加入GROUP BY或使用ANY_VALUE()1267Illegal mix of collationsJOIN两表字符集或排序规则不一致统一表字段的字符集和排序规则1172Subquery returns more than 1 row子查询返回多行但用于单值比较改用IN或EXISTS1071Specified key was too long索引字段长度超过InnoDB限制减少索引列长度或改用前缀索引1205Lock wait timeout exceeded查询等待行锁超时检查事务是否未提交优化事务范围3156SSL connection error客户端与服务端SSL协商失败检查SSL证书配置或调整连接参数这里多说一句关于SSL连接错误。不少人在用工具连接数据库时报SSL相关错误第一反应就是关掉SSL其实这不是最稳妥的做法。私有网络环境下为了快速排查可以临时禁用SSL但生产环境建议优先检查服务端证书是否有效、时区是否正常、客户端是否做了SNI校验。当然如果是开发环境且安全要求不高通过连接参数关闭SSL是最快的解法这个取舍看具体场景。5.3 两个值得一提的“常规反面教材”第一个是ORDER BY RAND()随机取行。很多人取几条随机记录时直接ORDER BY RAND() LIMIT 5这条SQL会对全表数据做随机排序几万行就明显变慢百万行直接卡死。更优解是取主键范围随机生成几个ID值然后WHERE id IN (…)。第二个是SELECT *的滥用。不只是返回多余字段浪费带宽更严重的是它让覆盖索引基本失效因为绝大多数索引都覆盖不了全部字段。尤其在多表JOIN的场景每个表都SELECT *回表数量是灾难级的。尽量显式列出需要的字段这是成本几乎为零的优化。写在最后的一点体会从SELECT 1到能处理千万级大表的查询这个过程中我觉得最关键的不是背语法而是建立两层直觉第一层是对SQL执行顺序的直觉能预判一条SQL里的条件、分组、排序在什么阶段生效第二层是对执行计划的直觉看到慢查询能在心里推演出表是怎么被访问的、索引是怎么被使用的。这两层直觉没捷径只能靠多写、多分析、多踩坑。建议你现在就找一条自己项目里的慢查询开个EXPLAIN跑一遍对着本文第四节的内容逐列看一遍把type、key、rows、Extra的含义落到真实数据上。下次再碰到查询问题你就不会再是搜报错、碰运气的心态而是能自己一步步拆到根因。系列后续如果大家有兴趣我可以再聊聊存储过程、窗口函数、分区表这类进阶主题。尤其窗口函数在报表统计里能替代掉很多复杂的自治查询值得单独开一篇。