MySQL查询优化实战：从Select *到慢查询排查与性能调优

1. 先把Select * From这条“看家本领”整明白1.1 一条最简单的查询背后发生了什么做了这么多年MySQL我越来越觉得很多人对Select * From这条语句的理解停留在“它能查出数据”这个层面至于它背后到底干了什么、什么时候该用它、什么时候不该用它其实没太想清楚。这里我想先把它掰开揉碎讲一遍。Select * From是SQL里最基础、最高频的查询语句没有之一。它的执行过程大体可以分为几个阶段客户端把SQL文本发送到服务端经过词法分析和语法解析后生成一棵解析树再经过查询优化器生成执行计划最后通过存储引擎接口去读取数据。Storage Engine存储引擎逐行扫描数据页把命中的记录返回给Server层Server层再做后续的投影、过滤最后把结果集发送给客户端。这个过程听起来简单但里面藏着很多细节。比如Select *意味着“把这张表的所有列都查出来”优化器在执行的时候要先把表的元数据读出来获得全部的列清单然后逐个列去组装结果行。如果你只需要其中两列这个“组装”动作就是白做的。更重要的是如果表上有组合索引Select *会导致优化器无法使用“覆盖索引”技术因为索引里只包含了部分列要取其余的列就必须回到聚簇索引里去“回表”捞数据这一来一回就是一次磁盘随机I/O。我用个生活化的类比解释一下覆盖索引和回表你可以把聚簇索引理解成一本书的正文页每一页上什么内容都有二级索引则相当于书后面的“关键词索引页”它只有关键词和页码。你想找某个词的解释如果关键词索引页上已经把解释写全了就不用翻到正文页这就是覆盖索引如果索引页上只有页码你就得按页码去翻正文那就是回表。Select *几乎强制你每次都“翻正文”哪怕你只需要看一个词条。1.2 生产环境中为什么我不推荐直接写Select *先说结论你在本地做调研、临时看数据、验证表结构随便写Select *没问题项目代码里最好别这么干。第一条原因是网络传输。一个表如果有三十个字段其中二十五个都是text类型的大字段你只想取id和name结果Select *把几百KB甚至几MB的文本数据全捞出来丢到网络上一次请求还好并发一上来带宽立刻被打满。跨机房调用、微服务之间的数据交互这种浪费极其致命。第二条原因是表结构耦合。你写SQL是给现在的表结构用的可数据库表是会演进的。今天这个表加了两个字段你代码里的Select *结果集结构就变了。如果你的代码是按列下标取值的比如很多老项目里用resultSet.getString(3)这种方式拿数据表结构一变就是线上事故。即使按列名取额外多传的字段也会白白消耗内存和网络。第三条原因是会让执行计划变差。这一点和上一条说的回表有关。如果查询条件能命中一个二级索引而我们只需要索引中的列优化器会选择直接扫描索引又快又省。一旦改成Select *优化器算一下回表的代价可能宁可选择全表扫描也不用你的索引。我见过不少线上慢查询排查到最后发现把Select *改成明确列名后同一个SQL从800ms降到了50ms原因就在这里。所以我的习惯是凡是会跑在业务链路里的SQL一律写明确列名只有在控制台里手工排查、开发环境里看数据的时候才图省事用Select *。1.3 Select输出列可以玩出的花样Select后面不只能跟*和列名还可以跟表达式、函数、常量、别名甚至嵌套子查询。掌握这些写法能省掉很多应用层代码。-- 别名与计算列 SELECT user_id AS uid, user_name AS 姓名, YEAR(create_time) AS 注册年份, IF(status 1, 正常, 冻结) AS 状态文案 FROM user_info WHERE create_time 2024-01-01;这里有一个非常实用的小技巧给字段设置默认值也可以用Select表达式来兜底。比如某个字段允许为NULL但你查询时希望它为空时显示成0或“未知”。SELECT user_id, IFNULL(score, 0) AS score, COALESCE(phone, 未绑定) AS phone FROM user_info;IFNULL和COALESCE都能做空值兜底区别在于IFNULL只接受两个参数COALESCE可以接受多个参数返回第一个非NULL的值。实际工作中我更喜欢COALESCE因为它的语义更通用而且从Oracle或PostgreSQL迁过来的SQL几乎不用改。另外要提醒一句Select里可以写子查询也就是“标量子查询”但性能上要小心。如果外层表有几万行子查询就会执行几万次这就是典型的“逐行触发”数据库压力会非常大。能用Join解决的就别用标量子查询这一点后面讲多表查询我还会再提。2. Where条件过滤查询语句里最考验功力的部分2.1 条件组合与运算符优先级光会Select还不行绝大多数查询都要带Where条件否则就是把整张表读出来。Where子句是SQL里最体现基本功的地方这里埋着不少坑。Where支持的关系运算符包括、、、、、!或逻辑运算符包括AND、OR、NOT。多条件组合的时候AND的优先级高于OR这一点经常有人忘导致查询结果和预期不符。-- 意图查询“上海或北京”且“状态正常”的用户 -- 错误写法OR没有加括号和预期不一致 SELECT * FROM user_info WHERE region 上海 OR region 北京 AND status 1; -- 正确写法 SELECT * FROM user_info WHERE (region 上海 OR region 北京) AND status 1;第一个SQL实际执行的时候会先算北京 AND 状态正常再算上海 OR ...结果把上海所有用户都查出来了无论他们状态是否正常。这个坑我在代码评审里遇到过不止一次每次都有人拍脑袋说“我明明写了and啊”。所以条件一多我建议干脆用括号把逻辑分组写清楚既能防止优先级搞错也方便别人阅读。还有一个容易踩坑的地方是NULL的判断。Where条件里写status NULL是永远查不出来数据的因为NULL和任何值比较结果都是UNKNOWN不是TRUE。必须写成status IS NULL或者status IS NOT NULL。同理WHERE 列名 ! 某个值也查不出该列为NULL的行因为NULL ! 某个值结果是UNKNOWN被过滤掉了。这是个非常经典的隐性Bug我见过有人在统计“非某状态的记录数”时把NULL行漏掉导致数据对不上账。2.2 模糊查询LIKE的两种写法要分清LIKE模糊查询是搜索场景的常客但很多人不知道它有两种模式。一种是普通模式%代表任意多个字符_代表任意单个字符另一种是转义模式用ESCAPE关键字来定义转义符。-- 查名字带“张”的用户 SELECT * FROM user_info WHERE user_name LIKE %张%; -- 查名字以“张”开头的用户 SELECT * FROM user_info WHERE user_name LIKE 张%; -- 查名字第二个字是“三”的用户 SELECT * FROM user_info WHERE user_name LIKE _三%; -- 查询包含“%”字面量的数据需要转义 SELECT * FROM log_table WHERE message LIKE %50\%% ESCAPE \\;模糊查询的性能问题要特别留意LIKE 张%这种前缀匹配如果列上有索引是可以走索引的但LIKE %张%这种包含匹配因为通配符在最前面索引就失效了只能全表扫描。数据量小的表无所谓数据量上千万的时候一次%关键字%查询能把数据库拖到CPU飙高。这种场景建议改用全文索引或者引入Elasticsearch一类的搜索引擎来处理。2.3 In、Exists与Not In的经典陷阱IN是最常用的集合匹配语法但有一个具体的报错非常热门“in查询语句报错”。这个报错的常见原因有三个。第一个是IN后面跟着一个超大的子查询或列表比如IN (SELECT ... FROM 另一张大表)有些版本的MySQL会生成效率很差的执行计划甚至直接报Subquery returns more than 1 row。注意这个报错的触发场景是子查询返回了多行但你把它放到了期望单值的地方。比如-- 错误子查询返回多行无法作为单值比较 SELECT * FROM A WHERE A.id (SELECT aid FROM B WHERE b.status 1);这里如果B表有多行状态为1就会直接报错。正确写法是把改成IN。第二个常见原因是IN列表里的元素个数过多。MySQL对IN列表的长度没有硬性上限但列表太长会带来两个问题一是SQL文本过长网络包可能要分片传输二是优化器处理超大IN列表时可能退化成低效的执行计划。我个人的经验是超过1000个值就要考虑改写比如拆成多次查询、用临时表关联或者改用JOIN。第三个是NOT IN遇到NULL的陷阱。假如子查询的结果里有NULLNOT IN会直接返回空结果一条数据都查不出来。原因还是NULL参与比较时的UNKNOWN逻辑。处理办法是把NOT IN改写成NOT EXISTS或者在子查询里显式过滤掉NULL。-- 稳妥写法NOT EXISTS SELECT * FROM user_info u WHERE NOT EXISTS ( SELECT 1 FROM order_info o WHERE o.user_id u.user_id AND o.pay_status 1 );这里强调一个很实用的替换思路能用EXISTS的时候尽量别用IN尤其是子查询表很大的情况。EXISTS是“存在即返回”只要找到一条满足条件的记录就会短路停止IN通常会把子查询结果全部物化出来再做外层匹配。两者语义上可以替换但性能差距在实际生产中非常明显。3. 排序、去重与分页结果集处理的三大高频动作3.1 Order by排序别忽略排序字段的索引排序在SQL里是个“隐形消耗大户”。表面上看只是一句ORDER BY create_time DESC实际上如果排序字段没有索引MySQL需要把结果集先放到临时表中再在内存或磁盘上做排序操作。数据量一大filesort文件排序就会让查询性能急转直下。排序优化的核心思路很简单让排序字段尽量走索引。比如-- 如果查询条件是 status排序是 create_time建议建联合索引 CREATE INDEX idx_status_create_time ON user_info(status, create_time); -- 这样下面的查询可以直接从索引里取到排好序的数据 SELECT user_id, user_name, create_time FROM user_info WHERE status 1 ORDER BY create_time DESC;这里有一个索引排序的细节索引的顺序是(status, create_time)WHERE status 1定位到一个范围后create_time天然就是有序的排序操作直接省掉。反过来如果索引建的是(create_time, status)那status 1这个条件会让create_time的顺序被打破还是要额外排序。排序时还经常出现中文排序不符合预期的问题。MySQL默认的字符串排序规则是utf8mb4_general_ci它对中文是按Unicode编码排序的如果你期望按拼音排序需要在ORDER BY里指定排序规则SELECT * FROM user_info ORDER BY user_name COLLATE utf8mb4_zh_0900_as_cs;不过这类排序会极大地消耗性能一般不推荐在数据库层面做中文排序放到应用层处理更合适。3.2 Distinct去重的边界条件SELECT DISTINCT是去重的利器但它有一个让人容易误判的地方它是“整行去重”不是“单列去重”。很多人想查“这张表里有多少个不同的城市”于是写SELECT DISTINCT city FROM user_info这个没问题。但如果写成SELECT DISTINCT city, age FROM user_info它返回的是“城市和年龄组合不重复”的记录而不是“城市不重复”。如果想按某一列去重、同时保留其他字段信息DISTINCT是做不到的得用GROUP BY配合聚合函数或者使用窗口函数。比如查每个城市最新的一个用户-- 窗口函数写法 SELECT user_id, user_name, city FROM ( SELECT user_id, user_name, city, ROW_NUMBER() OVER (PARTITION BY city ORDER BY create_time DESC) AS rn FROM user_info ) t WHERE t.rn 1;这个需求如果不用窗口函数就得靠子查询加关联SQL会绕很多。MySQL 8.0开始支持窗口函数我强烈建议大家把这类写法用起来它是处理“分组取前N条”这类问题的最简洁方案。DISTINCT和GROUP BY到底怎么选也有讲究。在两列去重场景下SELECT DISTINCT a, b FROM t和SELECT a, b FROM t GROUP BY a, b结果集是一样的。区别在于GROUP BY可以和聚合函数配套使用而DISTINCT不行。从执行计划来看两者的底层处理方式大同小异实际使用时主要看语义是否清晰。3.3 Limit分页与深翻页的性能问题分页查询是前端列表页必不可少的动作通常写法是SELECT * FROM order_list ORDER BY id DESC LIMIT 0, 20;这种写法在前几百页没问题但一旦翻到很深的页码比如LIMIT 100000, 20MySQL仍然要把前100000条数据全部扫描出来然后丢弃掉只返回最后20条。这个过程既消耗I/O又消耗内存越往后翻越慢。深分页的优化方案主要有两种。第一种是“延迟关联”先用覆盖索引查出目标页的主键ID再关联回原表取完整数据SELECT o.* FROM order_list o INNER JOIN ( SELECT id FROM order_list ORDER BY id DESC LIMIT 100000, 20 ) t ON o.id t.id;第二种是基于游标的分页也就是“键值分页”。不记录页码而是记录上一页最后一条数据的ID下一页查询时带上这个ID做条件SELECT * FROM order_list WHERE id 100020 ORDER BY id DESC LIMIT 20;这种方案没有深翻页的“扫描并丢弃”过程每次查询都能直接定位到起始位置性能极其稳定。我在实际项目中遇到列表页超过100万条数据的场景就是靠这个方案解决的。它的代价是前端不能随意跳页只能“上一页/下一页”地翻但对大多数业务场景来说完全够用。4. 聚合函数与分组统计让查询从“取数”变成“分析”4.1 常用聚合函数组合的实战写法聚合函数是SQL从“查数据”进阶到“做统计”的分水岭。最常用的五个是COUNT、SUM、AVG、MAX、MIN。这里有几个细节容易被忽视。COUNT(*)和COUNT(列名)的行为不一样COUNT(*)统计的是记录行数包括NULL行COUNT(列名)统计的是该列非NULL值的个数。比如统计“有手机号的用户数”必须写COUNT(phone)写COUNT(*)就错了。SUM和AVG会忽略NULL值但如果全是NULLSUM返回NULL而不是0AVG也返回NULL。这时可以用IFNULL或COALESCE兜底。聚合函数还有一个高频应用场景是“条件统计”比如统计订单表中已支付金额和未支付金额。最简洁的写法是用SUM配合IF或CASE WHENSELECT COUNT(*) AS total_order, SUM(IF(pay_status 1, order_amount, 0)) AS paid_amount, SUM(IF(pay_status 0, order_amount, 0)) AS unpaid_amount FROM order_list WHERE create_time 2025-01-01;这种写法只用一次全表扫描就把多个维度的统计做完了如果拆成三条SQL分三次查性能完全没法比。4.2 Group By与Having的关系GROUP BY是分组统计的核心语法。逻辑上它是把同一个分组键的行归到一起然后对每个组执行聚合函数。HAVING是用来过滤分组结果的它和WHERE最大的区别是WHERE在分组之前过滤原始行HAVING在分组之后过滤聚合结果。所以WHERE里不能用聚合函数HAVING里可以使用。-- 查出订单数超过5笔的用户 SELECT user_id, COUNT(*) AS order_cnt FROM order_list WHERE pay_status 1 GROUP BY user_id HAVING COUNT(*) 5;这里有一个经典的优化细节能在WHERE里过滤的条件不要留到HAVING。因为WHERE提前过滤能减少分组的数据量而HAVING是在分组全部算完之后才过滤意味着大量无效数据也参与了聚合计算。分组查询还有一个容易踩的坑是“SELECT的列必须出现在GROUP BY里或聚合函数中”。在MySQL的ONLY_FULL_GROUP_BY模式下SELECT user_id, user_name FROM order_list GROUP BY user_id会直接报错。这是SQL标准的要求防止出现“分组后的非确定性取值”。MySQL 5.7及以上版本默认开启这个模式老项目如果是5.6版本迁过来的常常会碰到这类报错。解法就是把user_name也加进GROUP BY或者用MAX(user_name)等聚合函数包住。4.3 分组后的拼接与统计技巧分组场景里有一类需求看似简单实际写起来很绕把同一个用户的多条记录拼成一个字符串。MySQL提供了GROUP_CONCAT函数一行搞定-- 把每个用户的订单号拼成一列 SELECT user_id, GROUP_CONCAT(order_no ORDER BY create_time DESC SEPARATOR 、) FROM order_list GROUP BY user_id;GROUP_CONCAT默认拼接长度限制是1024字节超过的部分会被截断。处理大量数据拼接时需要先执行SET SESSION group_concat_max_len 102400;调整上限否则结果会莫名缺失。分组查询还可以配合WITH ROLLUP做小计但这个语法用得不多因为它会额外产生一组NULL分组行应用层处理起来比较别扭我建议能用程序做的小计就程序做别用这个扩展语法。5. 多表联接Join背后到底是怎样一种运算5.1 Inner Join、Left Join、Right Join的语义边界多表联查是SQL里最难啃但又绕不开的部分也是面试中常考的重点。很多人对JOIN的理解停留在“把两张表拼起来”至于拼完之后哪些行保留、哪些行丢弃往往说不清楚。我用最直白的话解释一下INNER JOIN内连接返回的是两张表都能匹配上的记录LEFT JOIN左连接以左表为基准左表全部保留右表没有匹配的行就用NULL填充RIGHT JOIN正好反过来以右表为基准。MySQL里我几乎没见过有人用RIGHT JOIN因为把表顺序换一下RIGHT JOIN就能改写成LEFT JOIN可读性反而更好。-- 查询每个用户以及他们的订单 -- 用户表全部保留没有订单的用户订单字段为NULL SELECT u.user_id, u.user_name, o.order_no, o.order_amount FROM user_info u LEFT JOIN order_list o ON o.user_id u.user_id;这里要特别提醒一个关联条件里的坑ON条件和WHERE条件的执行顺序不一样。ON里的条件决定“怎么匹配”WHERE里的条件决定“匹配完成后保留哪些行”。对LEFT JOIN来说如果WHERE里写了右表字段的过滤条件左连接的效果就被破坏了变成了INNER JOIN因为不满足条件的右表行对应的左表行也被过滤掉了。-- 这个查询会把没有订单的用户过滤掉LEFT JOIN白写了 SELECT u.user_id, o.order_no FROM user_info u LEFT JOIN order_list o ON o.user_id u.user_id WHERE o.order_amount 100;如果确实要保留“无订单”的用户同时只展示满足金额的订单条件应该放在ON里SELECT u.user_id, o.order_no FROM user_info u LEFT JOIN order_list o ON o.user_id u.user_id AND o.order_amount 100;这两个SQL看起来差不多执行结果差之千里。这种“LEFT JOIN被WHERE悄悄变成INNER JOIN”的问题在我做过的数据修复脚本里出现过好几次每次都是查出来的记录数比预期少回头一查才发现问题出在条件位置。5.2 联表查询最常见的三个坑第一个坑是关联字段类型不一致导致索引失效。比如A表的user_id是VARCHAR(20)B表的user_id是BIGINT两张表做关联时MySQL需要做隐式类型转换这一转换就可能让索引失效全表扫描警告亮起来。联表查询的性能优化第一步永远是确认关联字段是否同类型、是否有索引。第二个坑是重复数据导致的笛卡尔积膨胀。如果关联字段在右表中不是唯一的一行左记录会匹配出多行右记录结果集行数会成倍膨胀。比如user_info LEFT JOIN order_list一个用户有10个订单这个用户就会输出10行。如果在写SQL之前没想清楚关联字段的粒度查出来的结果里出现大量重复行就会下意识去加DISTINCT但DISTINCT加错场景反而掩盖了逻辑问题。正确做法是先确认“这一列在关联表里是否唯一”。第三个坑是关联了太多表导致性能断崖式下跌。N张表关联优化器需要在庞大的连接顺序组合里找最优解。表一多执行计划就可能变得非常离谱。我见过有同事一次JOIN了8张表整条SQL跑了30多秒。排查后发现其中有两张表是可以通过合并冗余字段省掉的。联表数最好不要超过3张超过这个阈值先审视能不能拆成多次查询或建宽表。6. 与查询密切相关的实用语句更新、子查询与存储过程6.1 Update与子查询联用的正确姿势查询语句的学习不能只停留在SELECT因为实际生产环境里你经常需要用查询的结果去更新数据。UPDATE配合子查询是最常见的组合。-- 把“上海”用户的积分统一加100 UPDATE user_info SET score score 100 WHERE user_id IN ( SELECT user_id FROM user_region WHERE region 上海 );MySQL有一个限制UPDATE的目标表不能同时出现在FROM子查询里否则会报You cant specify target table for update in FROM clause。比如你想把“积分最高的用户”标记为VIP会写-- MySQL直接报错 UPDATE user_info SET is_vip 1 WHERE user_id ( SELECT user_id FROM user_info ORDER BY score DESC LIMIT 1 );解决办法是套一层派生表让MySQL感知不到直接引用UPDATE user_info SET is_vip 1 WHERE user_id ( SELECT uid FROM ( SELECT user_id AS uid FROM user_info ORDER BY score DESC LIMIT 1 ) tmp );这种“套娃”写法看起来丑但它是MySQL在语法限制下的标准解法工作中经常要写。6.2 给字段设置默认值及常见的修改场景热搜词里有一条非常具体“sql select查询语句 给某一个字段设置默认值”。这也说明了实际开发中的高频需求。在MySQL里有三种“默认值”相关的操作很多人把它们的适用场景搞混了。第一种是建表时指定列的默认值CREATE TABLE test_user ( id INT PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(50), status TINYINT NOT NULL DEFAULT 0, score INT NOT NULL DEFAULT 0 );第二种是修改已有表的列默认值ALTER TABLE test_user ALTER COLUMN score SET DEFAULT 100; ALTER TABLE test_user ALTER COLUMN score DROP DEFAULT;第三种是在SELECT查询结果中给字段设置展示层的默认值这个前面也提过用IFNULL或COALESCE实现SELECT user_name, IFNULL(score, 0) FROM test_user;有一点要注意修改列默认值这个操作在MySQL 8.0里虽然加了INSTANT算法但不同版本行为不完全一致。大表执行ALTER TABLE MODIFY COLUMN会触发表重建耗时可能以小时计。在8.0里可以用ALTER TABLE ... ALTER COLUMN ... SET DEFAULT这种语法来绕过表重建只改元数据速度快得多。这算是我在实际运维里总结的一个小经验。6.3 存储过程中声明变量的常见要点存储过程和查询语句的关系也很密切你要写存储过程就绕不开SELECT ... INTO、DECLARE、CURSOR这些语法。热搜词里“mysql声明存储过程”、“mysql存储过程”、“mysql声明变量”都是典型的学习需求。存储过程中的变量声明有几个容易踩的点。第一个是变量声明必须放在存储过程体的开头不能夹在BEGIN ... END块的中间。第二个是变量名前最好加v_前缀做区分避免和列名冲突。第三个是SELECT ... INTO只能给一个或多个变量赋值它要求查询结果必须是一条记录多一条报错少一条则变量保持原值。DELIMITER // CREATE PROCEDURE get_user_score(IN p_uid INT, OUT p_score INT) BEGIN DECLARE v_score INT DEFAULT 0; SELECT score INTO v_score FROM user_info WHERE user_id p_uid; SET p_score v_score; END // DELIMITER ;调用方式CALL get_user_score(1001, user_score); SELECT user_score;存储过程的痛点在于难以调试和维护。我在实际项目中只把存储过程用在两类场景一是定时任务的ETL批处理二是复杂报表的汇总计算。偏业务的查询逻辑我尽量放在应用层因为应用层有版本控制、有单元测试、有代码审查存储过程这些都没有。可一旦需要写存储过程语法细节还是要扎实掌握不然线上写错一个变量名往往要重启整个存储过程排查半天。7. 查询性能调优从慢SQL日志到索引优化7.1 先定位到慢SQL再谈优化很多刚接触数据库优化的人一上来就问“怎么建索引”但我的经验是优化的第一步永远是先定位问题SQL。MySQL天生就带了一个慢查询日志工具不借助任何第三方组件就能找到那些需要优化的查询。MySQL 8.0里通过一组参数控制慢查询日志-- 查看当前慢日志配置 SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time; -- 开启慢日志 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;long_query_time单位是秒我一般设成1超过1秒的SQL都记录下来。日志文件路径可以用SHOW VARIABLES LIKE slow_query_log_file查看。线上数据库不能随便重启但修改GLOBAL级别的变量是即时生效的。更好的方式是打开log_queries_not_using_indexes把“没走索引的查询”也记下来。这个开关开一天你会看到很多平时根本不注意的全表扫描SQL它们平时都在沉默地消耗数据库资源。7.2 Explain执行计划怎么看重点拿到一条慢SQL后下一步就是用EXPLAIN去看执行计划。这是MySQL优化里最核心的技能。EXPLAIN SELECT user_id, order_no, amount FROM order_list o INNER JOIN user_info u ON o.user_id u.user_id WHERE o.create_time 2025-01-01 ORDER BY o.create_time DESC;执行计划里要重点看的列我整理了一次列名重点关注内容type访问类型最好的是const和eq_ref其次是ref和range最差是ALL全表扫描key实际使用的索引名为NULL说明没走索引rows预估扫描行数这个数字越大越危险Extra出现Using filesort说明要额外排序出现Using temporary说明用了临时表都要警惕type这一列我从强到弱排个序systemconsteq_refrefrangeindexALL。生产中如果看到ALL先核实数据量表只有几千行全表扫描无所谓表有几千万行还全表扫描那就是事故。7.3 索引创建的原则与实际经验索引是MySQL性能优化的核心但“乱建索引”比“没有索引”更可怕。每个索引都会占用额外的磁盘空间拖慢写入速度优化器选错索引还会导致查询性能异常。我给出一套相对稳妥的原则频繁出现在WHERE条件的列优先建索引联合索引的字段顺序按“区分度从高到低”排列区分度高的列放在前面常用排序的列可以加入联合索引避免filesort索引列不要参与函数计算比如WHERE YEAR(create_time) 2025会让索引失效应改成WHERE create_time 2025-01-01 AND create_time 2026-01-01一个表的索引数量控制在5个以内。关于联合索引有一个“最左前缀原则”必须讲透索引(a, b, c)可以支持WHERE a ?、WHERE a ? AND b ?、WHERE a ? AND b ? AND c ?这些查询但不能支持WHERE b ?或WHERE c ?因为b和c不是从最左边开始的。很多人面试时背得出这句话实际写SQL时还是会踩坑。比如索引建了(status, create_time)查询条件只写了WHERE create_time 2025-01-01这个索引就用不上必须把status条件也补上。8. 日常操作中的高频报错与排查经验8.1 按命中率整理的热门报错处理这些年接触过大量MySQL报错有些出现频率极高每次都要花时间排查。我把最经典的几个整理成了一份速查表这些内容也和热搜词高度吻合。报错信息常见原因解决方案Subquery returns more than 1 row子查询返回多行但外层用了比较改用IN或EXISTSYou cant specify target table for update in FROM clauseUPDATE的子查询中引用了目标表套一层派生表Unknown column in where clause列名拼写错误或表别名写错检查字段名和表别名Incorrect integer value: abc for column status插入或查询时字符串和整数类型不匹配检查参数类型别让应用层传错Packet for query is too large (xxxx 4194304)单次SQL文本超过max_allowed_packet限制调大max_allowed_packet参数Cant connect to MySQL server (10061)服务未启动或端口未开放检查服务状态、防火墙、端口号这里重点说一个和端口相关的热门话题“mysql端口号”。MySQL默认端口是3306很多人遇到连不上的问题第一反应是密码错了但排查后发现是服务端口没开放。云服务器上部署MySQL时安全组规则里要放行3306端口同时检查服务器本机防火墙。MySQL配置文件中修改端口的参数是port3307改完记得重启服务。另一个高频问题是“mysql的初始密码是什么”。MySQL 5.7及之前版本用mysqld --initialize-insecure安装时本地root用户初始密码为空直接回车就能登录。MySQL 8.0用mysqld --initialize安装时会生成一个临时随机密码记录在错误日志里通常在数据目录下的hostname.err文件中搜索temporary password关键字能找到。8.2 不同数据库方言的识别与适配热搜词里有一条“postgresql sqllite mysql”说明不少人在同时接触不同的数据库。确实后端开发中经常需要分辨SQL方言尤其是SELECT相关语法在不同数据库里差异不小。举个例子热搜词里有一条“select top 1000 * from [dbo].[dc_ods_rkyxjc_lotdatacollection]”这种SELECT TOP加[方括号]的写法是SQL Server的方言MySQL不但不支持TOP也不会用方括号包裹表名MySQL里表名用反引号。另一个词条“event filter with query select * from __instancemodificationevent within 60”是Windows WMI的查询语法也不是标准SQL。碰到这类问题先确认目标数据库类型再写具体语法这是最基本的排查思路。分页语法的差异也值得写一笔数据库分页写法MySQLLIMIT 0, 20或LIMIT 20 OFFSET 0PostgreSQLLIMIT 20 OFFSET 0SQL ServerOFFSET 0 ROWS FETCH NEXT 20 ROWS ONLYOracleFETCH FIRST 20 ROWS ONLY我在多数据库兼容项目里的经验是不要在SQL里写数据库特有的方言能用标准SQL就用标准SQL。比如分页先查出来再在应用层截断虽然会浪费一点网络传输但换来的可维护性非常高。8.3 常用图形化工具的效率小技巧“mysql workbench使用教程”和“navicat连接mysql”这两条热搜说明很多人还是习惯用图形化客户端操作MySQL。图形化工具确实能提升日常开发和排查的效率但使用上有些小技巧。用Workbench连接MySQL时最常见的问题是连接不上8.0版本的数据库因为8.0默认的认证插件改成了caching_sha2_password老版本的Workbench或Navicat可能不支持。解决办法有两个一是升级客户端到支持该插件的最新版本二是在创建用户时显式指定旧的认证插件CREATE USER app_user% IDENTIFIED WITH mysql_native_password BY your_password;在图形化工具里执行SQL时有几点建议大表查询一定要加LIMIT防止工具把几十万行全部拉下来导致界面卡死执行UPDATE和DELETE之前先开启事务确认影响行数后再提交SELECT耗时长的语句先EXPLAIN看执行计划再跑。另外一个很多人没注意到的点是Workbench里点击表的“编辑行数据”时它默认执行的是SELECT * FROM table_name LIMIT 1000这1000行的限制可以在Edit - Preferences - SQL Editor里调整但我不建议调太大编辑模式下拉太多数据容易误操作数据修复类的操作尽量用SQL语句来执行。9. 关于MySQL安装与版本选型的一点经验补充热搜词里“mysql安装教程”、“mysql下载官网”、“mysql安装配置教程”出现了很多次说明新手阶段最大的障碍就是“第一次把环境跑起来”。我在这里把安装流程里最容易被卡住的地方集中讲一遍。首先明确版本选型。MySQL官网下载页提供多个版本企业版需要授权社区版完全免费。两个大版本的选择问题5.7是经典稳定版兼容性好生态成熟8.0是当前推荐版本性能更好支持窗口函数和公共表表达式但有很多细节和5.7不一样。新项目我建议直接用8.0老项目升级要谨慎特别是检查驱动版本和SQL语法兼容性。安装过程里最容易出问题的是Linux环境下的初始化步骤。mysqld --initialize执行完会生成临时密码而mysqld --initialize-insecure会创建空密码的root用户。很多教程里只写了前者结果新手拿着临时密码登录时因为包含特殊字符又不好复制反而折腾半天。我自己的做法是开发环境用--initialize-insecure登录后马上设置密码mysql -u root ALTER USER rootlocalhost IDENTIFIED BY 新密码; FLUSH PRIVILEGES;另一个高频问题是配置文件路径。MySQL的配置文件名是my.cnfLinux或my.iniWindows配置文件的加载顺序可以用以下命令查看mysql --help | grep my.cnf修改配置文件后重启服务Linux用systemctl restart mysqldWindows在服务管理器里重启MySQL服务即可。改端口、改max_allowed_packet、设字符集这类需求都走配置文件不建议每次都改全局变量因为重启后全局变量会恢复默认值。安装还会遇到“mysql的初始密码是什么”这个问题这部分之前已经讲过8.0版本初始化后的随机密码在错误日志中日志文件名是主机名.err可以用grep temporary password 主机名.err快速定位。我个人在实际操作中还有一个体会无论是初学还是老手手边常备一个MySQL官方文档和一个本地的测试库非常有帮助。SQL语句的执行计划和索引优化很多知识点光看书是记不牢的只有在真实的千万级数据表上跑一次EXPLAIN亲眼看到type从ALL变成ref把一条慢查询优化到几百毫秒才会真正理解索引为什么能快、什么时候会失效。这份对Select * From以及一系列查询语句的理解就是在无数次“写SQL、看执行计划、改索引”的循环里一点一点积累起来的。