1. 先把“表”这件事想清楚字段、类型与约束只要是接触过MySQL的人应该都有过这种经历刚开始建表时特别随意字段类型凭感觉选约束能不加就不加等业务跑了大半年数据量上来之后才发现一堆历史遗留问题——查询慢、乱码、数据对不上、没法统计。再回头改表结构又怕锁表影响线上业务。所以聊MySQL表操作和查询之前我想先说说建表阶段那些容易被忽略、但影响极其深远的基础设计问题。毕竟表结构设计得是否合理直接决定后面所有查询语句好不好写、跑得快不快。1.1 字段类型选错了后面全是泪字段类型是表结构里最基础但也最容易出错的地方。很多新手喜欢“省事”所有字符串一律用varchar(255)所有数值一律用int日期就放一个字符串字段存进去。短期看确实没问题等数据量到了几百万行各种问题就来了字符串类型的日期没法直接用日期函数做范围统计int存不下手机号直接溢出varchar(255)在utf8mb4字符集下单字段索引长度超限导致索引失效。这里我直接给一套常用的选型参考整数能用int就别用bigint能用tinyint就别用int。状态值、开关值用tinyint主键用bigint还是int取决于业务量预估但不要一拍脑袋就给所有表都上bigint。小数不要用float和double存金额精度丢失的问题在实际项目中非常坑。用decimal比如decimal(10,2)10表示总位数2表示小数位。字符串定长的用char比如身份证号、手机号这种长度固定的变长的用varchar但要给合理的最大长度别一上来就255。长文本用text但要注意text字段不能有默认值索引也只能前缀索引。日期时间这个特别值得多说一句。强烈建议用datetime不要用timestamp因为timestamp有2038年的上限问题。另外不要用字符串存日期虽然看起来方便但丢了时间函数的便利性也无法利用索引做高效的区间查询。有人可能会问datetime和timestamp到底差在哪。简单说timestamp存储的是UTC时间存的时候会转时区取的时候也会转回来受时区设置影响datetime存的就是字面时间不涉及时区转换。对于业务系统来说大多数情况下datetime更直观、更可控避免因为数据库时区和应用时区不一致造成数据错乱。1.2 约束与索引建表时一次想好别等数据量大了再回头约束是很多人建表时最容易忽略的东西。常见的主键约束、唯一约束、非空约束、默认值约束以及外键约束我发现很多项目干脆一个外键都不用。不用的原因也简单外键在写入的时候有额外的校验开销分布式拆分表的时候也麻烦所以业务系统基本都靠应用层来保证关联数据的完整性。这可以理解但至少主键、唯一键、非空、默认值这些基础约束该加就得加。主键的选择上我建议用自增int/bigint作为主键业务字段不要直接当主键。原因有两个一是InnoDB是聚簇索引组织表主键是物理存储的顺序依据自增主键能保证插入时顺序写入减少页分裂二是业务字段的变更不可控拿手机号当主键哪天允许用户改绑手机号整个索引结构都得跟着动。唯一约束这块要注意它和普通索引的区别。唯一约束本身就是索引只是额外加了唯一性校验。所以如果业务上需要保证某个字段唯一直接加unique key不要用普通索引之后再在应用层做查询校验既慢又容易有并发漏洞。还有个很实用的习惯每张表都加上创建时间和更新时间两个字段类型用datetime更新时间可以靠业务代码维护也可以建表时用ON UPDATE CURRENT_TIMESTAMP自动更新。这两个字段在排查数据问题时几乎是救命稻草尤其是出现脏数据、重复数据、异常数据的时候能迅速定位出问题时间点。1.3 字符集与排序规则乱码和排序不对的头号原因字符集问题可以说是MySQL新手时期最常遇到的坑之一。我记得第一次接手一个老项目明明代码里都是UTF-8数据库里却出现一串问号和乱码排查了半天最后发现库的默认字符集是latin1。这种问题一旦上线之后再想改代价非常大。所以在建库建表的时候第一件事就是明确字符集。现在的统一标准就是utf8mb4注意不是utf8。MySQL里的utf8是utf8mb3的别名只支持最多3字节的字符像emoji这种4字节字符就存不了会报错或者变成乱码。utf8mb4才是真正的“完整UTF-8”兼容所有Unicode字符。排序规则也要一并确认。utf8mb4_general_ci和utf8mb4_unicode_ci是两种常见选择。前者排序快但精度略差后者排序更准确但稍微慢一点。绝大多数业务系统其实用哪个差别都不大需要重点注意的是别出现表、库、连接层字符集不一致的情况。判断起来也简单执行一次查询如果发现中文乱码先别急着改数据从客户端连接、到数据库连接、再到表和字段的字符集一层层排查。最常见的坑是连接层用了utf8而表和字段是utf8mb4中文正常遇到特殊字符就出问题。2. 建表、改表、删表一张表的完整生命周期管理前面说了设计层面的问题接下来就进入实际操作环节。表操作无非就是建表、改表、删表这三类但每一类里都有不少细节值得展开。很多人写SQL的时候只关心能不能跑通忽略了操作带来的影响面尤其是生产环境里一条ALTER TABLE就可能把整个业务拖垮。2.1 建表从需求到DDL落地的实操写法先看一个实际的建表示例。假设要建一张用户订单表包含订单号、用户ID、商品名称、订单金额、订单状态、创建时间、更新时间这些基本字段。第一版DDL可以这样写CREATE TABLE orders ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键ID, order_no VARCHAR(64) NOT NULL COMMENT 订单号, user_id BIGINT NOT NULL COMMENT 用户ID, product_name VARCHAR(128) NOT NULL COMMENT 商品名称, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态0待支付 1已支付 2已取消, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT用户订单表;这个建表语句里的几个细节值得展开说明一下。首先是ENGINEInnoDBMySQL 5.7之后默认就是InnoDB但写出来更明确。InnoDB支持事务、行级锁、崩溃恢复是绝大多数业务场景的唯一选择。有些人可能听说过MyISAM查询快但它不支持事务、不支持行锁写并发一高就全线阻塞真不适合业务系统用。其次是每个字段都加了COMMENT注释。这个习惯非常好。一个项目寿命往往远超想象两三年后你自己回头维护这张表如果没有注释真的会想不起来某个字段是干什么用的。更不用说团队协作别人接手你的表结构时良好的注释能省下大量沟通成本。再注意到status字段用的是TINYINT而不是VARCHAR或者ENUM。用数字表示状态扩展性好0、1、2不够用随时可以加。但这里有个小建议数字状态值一定要配注释说明含义否则时间长了没人知道1代表什么。不过更推荐的做法是在应用层代码里定义状态常量注释辅助解释两边对应着看。2.2 ALTER TABLE加字段、改字段、加索引的常见操作业务迭代过程中表结构几乎一定会变。ALTER TABLE的语法比较简单但生产环境里要特别注意执行成本和锁表风险。加字段是最常见的操作。比如给订单表加一个支付时间字段ALTER TABLE orders ADD COLUMN paid_at DATETIME DEFAULT NULL COMMENT 支付时间 AFTER status;这里有一个非常实用的习惯把新字段用AFTER关键字放在相关字段旁边而不是默认加到最后。虽然最终效果不影响查询但看表结构时逻辑会更清晰。另外注意在数据量很大的表上执行ALTER TABLEInnoDB需要重建表会锁住写操作耗时可能非常长甚至导致主从延迟。直到MySQL 5.6引入了在线DDL加上MySQL 8.0的进一步优化很多ALTER操作已经不会完全锁表了但字段类型变更、修改字符集这类操作仍然可能很重。所以在生产环境做表结构变更前我一般会先确认表的行数和当前实例的负载尽量挑业务低峰期操作。修改字段类型和默认值也是高频操作。语法如下ALTER TABLE orders MODIFY COLUMN status TINYINT NOT NULL DEFAULT 1 COMMENT 订单状态0待支付 1已支付 2已取消 3已退款;同样一个MODIFY操作看似简单但如果表很大执行期间同样是有代价的。而且MODIFY COLUMN会改变字段的整条定义如果你只写了一部分属性其他属性会被重置所以执行前最好先用SHOW CREATE TABLE把原有定义完整看一下再确认要改的信息避免误把已有的NOT NULL、DEFAULT等属性弄丢。加索引的常见操作ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);这是一个典型的联合索引能够同时优化“查某个用户的订单按时间倒序”这类高频查询。联合索引的核心设计原则是区分度高的字段放前面等值查询字段放前面范围查询字段放后面。这个后面讲到查询优化时还会再展开。2.3 删表与清空DROP / TRUNCATE / DELETE的本质区别删表有三种方式很多人分不清它们的适用场景。DROP TABLE是整张表直接删掉表结构、数据、索引全部消失不可恢复。TRUNCATE TABLE是清空数据但保留表结构相当于重置这张表不可按条件删也不能回滚。DELETE FROM是逐行删除可以带WHERE条件且走事务可以回滚。所以日常开发中真正不要这张表了才用DROP想要快速清空一张临时表或日志表的数据用TRUNCATE删除符合条件的部分数据用DELETE。这里特别提一个问题很多人误以为DELETE之后数据量还是没降下来。这是因为InnoDB删除的行标记为“已删除”但物理空间没有被立即释放后续有新数据插入时才会复用这些空间。如果只是跑了一次DELETE FROM大表磁盘空间不变是很正常的不要慌张。想要真正收缩表空间可以执行OPTIMIZE TABLE或者ALTER TABLE ... ENGINEInnoDB来重建表但这又是一个重操作生产环境慎用。3. 单表查询SELECT的核心逻辑与执行顺序聊完表操作进入查询部分。查询是MySQL里最日常、也最考验功力的操作。很多人写了几年的SQL遇到慢查询还是只会加索引其实很多问题的根源不在于缺索引而在于没有真正理解SELECT的执行逻辑。3.1 SELECT语法顺序与执行顺序是两码事这是个老生常谈却又极其重要的话题。一条完整的SELECT语句书写的顺序是SELECT 字段 FROM 表 WHERE 条件 GROUP BY 分组字段 HAVING 分组后条件 ORDER BY 排序字段 LIMIT 偏移量, 行数但SQL真正执行的顺序不是这个。MySQL会先走FROM找到数据源然后WHERE过滤原始数据再GROUP BY分组之后HAVING过滤分组结果接着SELECT投影目标字段然后ORDER BY排序最后LIMIT截取。理解这个执行顺序最直接的好处是能解释很多“奇怪”的现象。比如WHERE条件里不能用SELECT中定义的别名因为WHERE执行时SELECT还没执行别名自然不存在。而ORDER BY可以用别名因为排序发生在SELECT之后。再比如为什么GROUP BY之后SELECT能用的字段要么是分组字段要么是聚合函数的结果因为分组后每组只保留一行其他字段的值可能有多条MySQL无法确定取哪个在严格模式下会直接报错。我见过一个很典型的案例某个统计报表里SQL写得特别长里面在WHERE里判断了一个用CASE WHEN生成的别名结果一直报错。我当时给出的修改方案就是把那个判断挪到HAVING里或者在外面再包一层子查询问题立刻解决。这种问题如果不懂执行顺序光看报错信息很难定位。3.2 WHERE过滤条件的类型陷阱和经验写法WHERE是查询中最常用、也最容易埋坑的子句。最常见的一个坑是类型不一致导致索引失效。比如表里user_id是VARCHAR类型但查询时传了一个数字MySQL会把字符串类型字段隐式转换成数字来比较导致该字段上的索引无法使用。反过来也一样如果字段是INT类型查询条件里传了字符串同样存在隐式转换。所以一条基础经验是字段类型和条件值类型保持一致别让MySQL帮你做隐式转换。还有一个常见问题是函数包裹字段。比如在创建时间字段上写了WHERE DATE(created_at) 2024-01-01乍一看没毛病但实际上DATE()函数让created_at无法直接使用索引MySQL不得不对每一行执行函数计算然后再过滤。正确写法是改成范围条件WHERE created_at 2024-01-01 AND created_at 2024-01-02这是一个非常高频的优化点凡是出现“查询慢但明明加了索引还是慢”的情况第一反应就应该是检查WHERE条件里有没有对索引字段使用函数或者做了隐式转换。如果要表达得更严谨一点想让索引生效就要让索引字段保持“裸状态”不做任何包裹计算。3.3 GROUP BY与HAVING分组统计的正确姿势GROUP BY用于对数据进行分组统计是报表类需求的基石。比如统计每天订单数SELECT DATE(created_at) AS day, COUNT(*) AS order_count FROM orders WHERE created_at 2024-01-01 GROUP BY day ORDER BY day;注意这里的DAY这个别名在GROUP BY和ORDER BY里都能用因为这两步都发生在SELECT之后。这个写法在MySQL里是允许的而且很常见。GROUP BY和HAVING经常一起出现HAVING用于对分组后的结果再做过滤。很多人会把HAVING和WHERE搞混记一个简单的区别WHERE在分组之前过滤行HAVING在分组之后过滤分组。WHERE不能使用聚合函数HAVING可以。比如要找出订单数超过100的用户IDSELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id HAVING order_count 100;这条SQL是先按用户分组统计再过滤出订单数大于100的用户。如果把这个条件放到WHERE里就会直接报错因为COUNT的值在WHERE阶段根本还没算出来。还有一点要注意HAVING的效率通常比WHERE差因为HAVING要等分组完成才能过滤所以能用WHERE提前过滤的数据尽量在WHERE阶段就过滤掉减少分组压力。3.4 ORDER BY与LIMIT排序、分页与深分页优化排序和分页是日常接口开发里离不开的操作。最简单的查询SELECT * FROM orders WHERE user_id 10001 ORDER BY created_at DESC LIMIT 20;这里有一个联合索引设计的最佳实践如果这个查询是高频的可以建一个(user_id, created_at)的联合索引这样WHERE定位到用户后索引内部已经按created_at排好序了直接取前20条就行省去排序环节。这就是前面建表部分提到联合索引设计原则的应用。LIMIT的分页写法也很常见但深分页是个隐藏的大坑。比如SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;这条SQL会把前100万条数据都查出来然后丢弃只留下最后的20条。偏移量越大查询越慢而且是线性增长的。解决思路有几种。第一种是记录上一次查询的最大ID下次查询带上WHERE id 上次最大ID的条件用延迟关联或索引覆盖的方式去翻页。第二种是子查询先取偏移段的ID列表再回表拿完整数据SELECT * FROM orders WHERE id ( SELECT id FROM orders ORDER BY id LIMIT 1000000, 1 ) ORDER BY id LIMIT 20;这种方式先用索引快速定位到第1000001条的位置再往后取20条比直接LIMIT 1000000, 20高效得多。但要注意这种优化方案只适用于有自增主键且数据没有频繁物理删除的场景如果数据有大量随机删除ID之间的空洞会让分页结果不一致。实际项目中需要结合业务场景权衡。4. 多表查询JOIN、子查询、UNION怎么选单表查询解决的是单个数据源的问题但业务需求往往需要关联多张表。多表查询的实现方式有三种JOIN、子查询、UNION。每种方式都有各自的适用场景没有绝对的好坏关键是要理解它们的执行逻辑。4.1 JOIN家族INNER、LEFT、RIGHT的区别与场景JOIN是关联查询最核心的写法。最常见的三种是INNER JOIN、LEFT JOIN、RIGHT JOIN。网上有很多图解释它们的区别这里我用实际业务来举例说明。假设有两张表用户表users和订单表orders。想知道“哪些用户下过单”用INNER JOIN只返回两边都匹配的行SELECT users.id, users.name, orders.order_no FROM users INNER JOIN orders ON users.id orders.user_id;想知道“每个用户和他的所有订单没下过单的用户也要显示出来”用LEFT JOIN左表数据全部保留右表无匹配就以NULL填充SELECT users.id, users.name, orders.order_no FROM users LEFT JOIN orders ON users.id orders.user_id;这个场景最常见的就是列表页需要展示主表数据即使副表没数据也要显示一行。RIGHT JOIN的用法和LEFT JOIN对称以右表为主但实际开发中用得很少。我个人的建议是统一用LEFT JOIN就好不要混用RIGHT JOIN否则SQL的可读性会变差团队同事看代码也不舒服。关联条件上有一个极其重要的坑LEFT JOIN中如果在WHERE里对右表字段加过滤条件会导致“左表保留全部行”的语义被破坏。举例SELECT users.id, users.name, orders.order_no FROM users LEFT JOIN orders ON users.id orders.user_id WHERE orders.status 1;这条SQL的意图可能是“查所有用户中已支付订单的关联情况”但实际执行时因为WHERE在JOIN之后过滤已经把左表中没有匹配订单的用户行全部剔除了LEFT JOIN变成了INNER JOIN的效果。想要保持左表全部保留应该把过滤条件写在ON子句里SELECT users.id, users.name, orders.order_no FROM users LEFT JOIN orders ON users.id orders.user_id AND orders.status 1;这个细节我已经在真实项目中碰到不止一次每次都是面试题级别的经典陷阱而且业务上一旦踩中定位问题往往要花不少时间。4.2 子查询与JOIN的取舍子查询是嵌套在主查询里的SELECT语句可以分为FROM子句里的子查询、 WHERE子句里的子查询、SELECT子句里的标量子查询几种。比如要查“每个用户最近一笔订单”可以用关联子查询的方式SELECT o.* FROM orders o WHERE o.id ( SELECT MAX(id) FROM orders o2 WHERE o2.user_id o.user_id );这个写法理解起来比较简单但它对每条订单都会执行一次子查询如果数据量大性能会很差。还有一类常见的子查询是用IN比如SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount 100);这种写法的隐患是如果子查询返回的结果集特别大IN列表会非常长效率不高。而且某些MySQL版本对IN子查询的优化做得不够好执行计划可能会退化成全表扫描。更稳健的做法是用EXISTS改写或者改成JOINSELECT DISTINCT users.* FROM users INNER JOIN orders ON users.id orders.user_id AND orders.amount 100;用JOIN的语义更直白执行计划可控性也更高。当然JOIN之后如果主表数据有重复关联需要加上DISTINCT去重这时要自己确认是否会产生重复行。子查询和JOIN的选择没有绝对标准我的经验是小数据量、语义直观的子查询可以用大数据量、性能敏感的查询优先考虑JOIN和EXISTS然后通过EXPLAIN确认执行计划。4.3 UNION与UNION ALL别让去重拖慢查询UNION和UNION ALL都是将多个SELECT的结果集合并输出。区别在于UNION会去重UNION ALL不去重。这一字之差性能差距可能非常明显因为去重意味着需要对整个结果集排序或走哈希去重额外开销极大。业务上如果明确两个结果集不可能有重复或者重复无害直接用UNION ALL就没问题了。举一个实际场景要查“订单金额大于1000的订单”和“最近7天创建的订单”的合并列表。两个条件有交叉的可能但最终展示订单列表时重复也没关系所以SELECT order_no, created_at FROM orders WHERE amount 1000 UNION ALL SELECT order_no, created_at FROM orders WHERE created_at NOW() - INTERVAL 7 DAY;这样比UNION快不少而且逻辑更符合业务预期。另外还有一个容易被忽略的事项UNION的子句之间各SELECT返回的列数和数据类型必须一致否则MySQL会报错。比如第一个SELECT返回了两列第二个SELECT返回了三列直接编译失败。如果各表中列名不一致第一个SELECT的列名会作为结果集的列名。5. 提升查询性能的关键索引与执行计划前面讲了很多查询写法但真正跑线上查询的时候性能怎么样不能靠猜得靠数据说话。这一部分重点说索引的坑和EXPLAIN执行计划的用法。5.1 索引失效的几种常见姿势索引失效是慢查询的头号原因。我总结过几条高频踩坑场景基本覆盖了日常开发里90%的问题。第一对索引列使用函数或表达式计算。前面已经提过WHERE DATE(created_at) 2024-01-01会导致索引失效。同样地WHERE amount 1 100这种写法也会让amount字段的索引失效。第二隐式类型转换。varchar字段和数字比较、int字段和字符串比较都会让MySQL放弃索引。第三LIKE模糊匹配以%开头。比如WHERE name LIKE %张%因为无法确定前缀B树无法定位只能全表扫。反过来WHERE name LIKE 张%可以走索引。第四联合索引不使用最左前缀。建了(a, b, c)联合索引条件里直接查b1跳过a索引用不上。这是最左前缀原则决定的。在设计联合索引时一定要把最高频的等值查询字段放最左边。第五OR条件导致索引失效。比如WHERE a 1 OR b 2如果只有a字段有索引MySQL可能选择全表扫描。优化方式是把OR改成UNION或者给b也加上索引。第六对NULL值的判断。WHERE name IS NULL不一定导致索引失效但WHERE name IS NOT NULL在很多场景下优化器会认为返回数据量太大直接放弃索引。这里没有绝对结论实际操作中以EXPLAIN结果为准。第七ORDER BY和GROUP BY的字段顺序与索引顺序不一致。比如联合索引(a, b)但SQL里ORDER BY b, a索引顺序不匹配排序走文件排序性能下降。5.2 EXPLAIN怎么看重点字段速查EXPLAIN是排查慢查询的核心工具。用法很简单在SELECT前面加个EXPLAIN关键字就行EXPLAIN SELECT * FROM orders WHERE user_id 10001 AND created_at 2024-01-01;返回结果里重点看这几个字段type访问类型。从好到差依次是const、eq_ref、ref、range、index、ALL。出现ALL表示全表扫描要高度警惕。range说明扫描了一个索引范围比全表扫描好得多。key实际用到的索引名。如果为NULL说明没走索引。rows预估扫描的行数。这个数越大查询通常越慢。Extra额外信息。出现Using filesort或Using temporary说明排序或分组用了临时文件性能往往不佳出现Using index则说明是覆盖索引性能很好。举个实际排查案例某次线上接口变慢EXPLAIN结果显示一条查询的type为ALLrows达到500多万。打开SQL一看WHERE条件里对create_time用了DATE()函数。改成范围条件后type变成rangerows降到几千接口响应时间从接近2秒降到几十毫秒。这就是EXPLAIN的价值——用数据定位问题而不是拍脑袋猜。5.3 一个真实慢查询优化案例之前处理过某业务系统的一个慢查询是订单列表接口。SQL大概长这样SELECT id, order_no, user_id, amount, status, created_at FROM orders WHERE user_id 12345 ORDER BY created_at DESC LIMIT 10;表里订单量接近千万user_id和created_at各自都是独立索引但查询依然慢。EXPLAIN之后发现优化器在两个索引之间选择了一个但Extra里出现了Using filesort也就是排序没有完全走索引。原因很简单优化器走的是idx_user_id先定位到该用户的所有订单再对这些订单按created_at排序。因为created_at不满足索引顺序排序操作只能在内存或磁盘上做数据量一大就慢。解决方案就是前面提到的联合索引ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);这个联合索引既能通过user_id快速定位又能在索引内部天然按created_at排好序完美匹配ORDER BY的需求。加上索引之后EXPLAIN显示Using filesort消失查询时间从几百毫秒降到个位数毫秒。这个案例值得记住的不是SQL写法本身而是索引设计要兼顾WHERE和ORDER BY。很多人在联合索引上只考虑过滤字段忽略了排序字段结果WHERE很快但ORDER BY把整个查询拖慢。6. 常见问题与排查技巧实录最后把这些年在MySQL表操作和查询这块遇到的高频问题统一整理成一张速查表方便大家在实际开发中对号入座。症状可能原因排查思路解决方向中文乱码字符集不一致或连接层字符集不对检查库、表、字段、连接的字符集统一为utf8mb4重建连接并确认character_set_server查询越来越慢缺索引、索引失效或扫描行数过多执行EXPLAIN看type、rows、key补联合索引、改写条件、避免函数包裹加了索引还是慢索引列被函数包裹、隐式类型转换或OR条件检查WHERE里索引列是否“裸状态”去掉函数/转换OR改为UNION或补索引排序慢索引顺序与ORDER BY不匹配看Extra是否有Using filesort设计联合索引时把排序字段放进去LIMIT翻页到后面很慢深分页在大偏移量下扫描大量无用行分析偏移量大小、扫描行数用ID定位、延迟关联或减少偏移量DELETE后表空间不减少InnoDB的已删除行空间未立即释放查看表大小、碎片情况定期OPTIMIZE TABLE或接受空间复用机制同一查询不同环境结果不同字符集、排序规则或数据分布差异对比两边的表结构定义和数据分布用小数据量复现并对比执行计划GROUP BY报错没开启严格模式或SELECT字段与GROUP BY字段不匹配查看sql_mode配置和SQL写法把非分组字段改为聚合函数或加入GROUP BY关联查询数据量暴增JOIN条件产生笛卡尔积检查ON条件是否缺失或条件过宽确认关联字段有索引条件收窄查询结果包含重复数据JOIN一对一关系变一对多检查关联表的唯一性、关联键是否有重复加DISTINCT或调整关联SQL排查问题的时候有一个实用心法不要一上来就改SQL。先花两分钟看表结构再执行EXPLAIN看执行计划最后才动手改。大多数慢查询问题在执行计划阶段就能露出马脚改SQL只是收尾动作。还有一个经验之谈排查过程中把当时的表结构、数据量、SQL语句、执行计划这四个信息记录下来问题解决之后再回头复盘一段时间下来自己解决问题的能力会提升一大截。表操作和查询是MySQL最基础的能力但也是最值得反复打磨的能力。工作中见到过太多“上线前没问题跑了一段时间之后突然慢得出奇”的案例究其原因多数不是SQL本身的问题而是表结构设计和查询方式对数据增长的适应性不足。比如索引设计没考虑到数据规模的增长、表结构变更没有预留弹性、深分页没有提前做容量规划。所以我的经验是写建表DDL和查询SQL的时候多问自己一句这个表和这条SQL在数据量再涨十倍、涨百倍之后还能不能撑住带着这个意识去写每一行SQL能避掉绝大部分性能坑。最后再分享一个小技巧每张业务表都留一个自增主键和创建时间字段这两个字段虽然平淡却是后续排查问题时最可靠的抓手。
