搞MySQL搞了好些年日常打交道最多的就是建表和查数据。表结构设计得好不好查询写得到不到位直接决定了你后续是躺平喝茶还是半夜爬起来处理慢查询。尤其是那些刚入门的朋友被各种教程带着复制粘贴建表全靠直觉查询全靠运气出了问题又不知道该查哪里最后只能把锅甩给“数据库不稳定”。这篇文章我就基于实际经验把MySQL的表操作和查询这两块掰开揉碎讲清楚从建表时的字段类型选择、约束设计到查询时的执行逻辑、索引优化再到常见报错的排查路径都给你梳理一遍保证你看完能直接用在自己的项目里。1. 建表是根基字段类型、约束与字符集的取舍很多人觉得建表不就是写个CREATE TABLE吗有什么好讲的。但恰恰是这一步决定了一张表未来几年的命运。字段类型选错了轻则浪费存储空间重则查询慢到让你怀疑人生约束没设计好业务层写再多校验代码都挡不住脏数据进来。所以建表这块我建议你多花点心思。1.1 字段类型怎么选别再用VARCHAR存所有东西新手最常见的问题就是不管什么数据都往VARCHAR里塞手机号、日期、金额、状态码全部用字符串存。短期看确实省事但长期看全是坑。日期字段用字符串存你就没法直接用DATE_SUB、DATE_FORMAT这类函数做计算和格式化排序也是按字典序排的2024-01-01和2024-9-1排出来的结果保证让你一脸懵。金额字段用字符串存计算的时候还得CAST一下精度还容易出问题。状态码用字符串存索引长度白白变长查询效率也跟着掉。我个人的实践经验是能用整数用整数能用日期用日期金额就用DECIMAL。比如用户状态、订单状态这种枚举值用TINYINT就够了0表示待支付、1表示已支付、2表示已取消清晰又高效。时间戳字段用INT存也行但可读性太差不如直接用DATETIME。还有一个容易踩的坑是DECIMAL它不会丢失精度存金额就选DECIMAL(10,2)千万别用FLOAT或DOUBLE浮点数存金额算账的时候那种“0.10.2不等于0.3”的问题会要了财务的命。另一个值得说的是长度设置。VARCHAR(255)是很多人偷懒用的默认值但如果你明确知道用户名最长不会超过20个字符就老老实实写VARCHAR(20)。VARCHAR是按实际内容长度存储的不是固定分配所以长度设多大不影响存储空间但会影响内存排序和临时表的效率而且索引长度也和字段长度强相关。能设小绝不设大这是建表的基本修养。1.2 主键、约束与索引建表时的隐形决策主键是一张表的灵魂没主键的表就像没有身份证的人数据重复了都没办法纠正。MySQL的InnoDB存储引擎默认使用聚簇索引组织数据而聚簇索引的叶子节点就存着整行数据。这意味着主键的选择会直接影响所有二级索引的性能。我的建议简单粗暴使用自增BIGINT作为主键或者用雪花算法生成分布式ID。不要用UUID当主键UUID是无序的随机字符串插入时会导致聚簇索引频繁分裂页、产生碎片数据量大了之后插入性能会急剧恶化硬盘上的表文件也会大得离谱。约束方面除了主键约束还有NOT NULL、UNIQUE、DEFAULT、FOREIGN KEY这几个。NOT NULL一定要按业务语义设定。比如用户注册时间理论上注册成功就一定有值那就加NOT NULL防止业务逻辑有漏洞时写入NULL。UNIQUE约束用来保证唯一性比如用户表的手机号列加个UNIQUE KEY比在业务代码里先SELECT再INSERT靠谱得多并发下后者必然出重复数据。DEFAULT值也建议顺手写上很多字段是有默认值的比如状态字段默认0、创建时间默认CURRENT_TIMESTAMP这样插入数据时少传几个字段也完全没问题。外键约束我持保留态度。互联网大厂的高并发场景下普遍不建议物理外键因为外键在写入时要额外检查关联表的记录带来额外的锁竞争和性能损耗。如果你做的是传统企业级应用数据量不大、并发不高物理外键反而能省掉很多应用层的判断逻辑、保证数据完整性用也无妨。关键看场景没有绝对的对错。1.3 字符集与排序规则乱码和排序错乱的根源字符集这块记住两句话数据库、表、字段的字符集统一用utf8mb4排序规则统一用utf8mb4_general_ci或utf8mb4_0900_ai_ci。为什么不用utf8因为MySQL的utf8是utf8mb3它只支持最多3个字节的字符存不了emoji和一些特殊汉字。现在谁聊天不带个表情万一将来业务要存emoji字段就得重建。与其到时候炸了再救火不如一开始就上utf8mb4。排序规则里的ci表示大小写不敏感对绝大多数业务场景都适用。如果你有特殊需求例如用户名登录时要区分大小写那就要用utf8mb4_bin。这块还要提醒一个细节如果建库时用了utf8mb4建表时没显式指定表会继承库的字符集建表时指定了但某些字段漏了字段会继承表的字符集。这种一脉相承的机制经常让很多人以为“我设置了呀怎么还乱码”十有八九是有人后来手动改过某个字段的字符集或者链接层没指定字符集。排查乱码时先看表结构的SHOW CREATE TABLE再看连接串是否加了characterEncodingutf8这两步能解决九成问题。1.4 建表实操从需求到可落地的完整SQL光说不练假把式我随手写一个订单表的建表语句顺便把刚才讲的东西串起来CREATE TABLE t_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, order_no VARCHAR(32) NOT NULL COMMENT 订单号, user_id BIGINT UNSIGNED NOT NULL COMMENT 用户ID, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单总金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态 0-待支付 1-已支付 2-已取消, pay_time DATETIME DEFAULT NULL COMMENT 支付时间, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time 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_create_time (create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT订单表;这个表有几个细节值得逐一说清楚。order_no加了唯一索引这是业务主键和物理主键id分开是常规操作。user_id建普通索引因为后边要按照用户查订单列表。create_time也建索引用于按时间范围统计订单量。update_time用了ON UPDATE CURRENT_TIMESTAMP这样每次记录被更新时这个字段会自动刷新省得在应用层手动维护。ENGINE指定InnoDB这是MySQL 8.0默认引擎支持事务和行级锁。CHARSET和COLLATE显式写出来避免依赖库级别的隐式配置。2. 表结构修改ALTER TABLE操作与在线DDL的实战经验数据库上线以后需求可不会等你。今天要加个字段明天要改个长度后天要删个列这类操作躲不掉。但ALTER TABLE不是想怎么改就怎么改的MySQL在处理某些DDL时会锁全表直接阻塞线上读写这就是生产事故的导火索。2.1 增删改字段语法、位置与代价增字段最常用的语法是ALTER TABLE t_order ADD COLUMN remark VARCHAR(255) DEFAULT NULL COMMENT 备注 AFTER status;AFTER关键字可以控制新字段加在哪个列后边不写的话默认追加在最后。这里有个点要注意如果你只关心能不能查询数据字段位置无关紧要但如果你有强迫症想保持表结构整洁或者项目里的代码生成工具会依赖字段顺序那么还是显式指定位置更省心。修改字段类型和长度也很常见ALTER TABLE t_order MODIFY COLUMN remark VARCHAR(500) DEFAULT NULL COMMENT 备注;MODIFY COLUMN可以修改字段类型、长度、默认值、注释等。还有一个CHANGE COLUMN相比MODIFY多了一个重命名的功能ALTER TABLE t_order CHANGE COLUMN remark remark_info VARCHAR(500) DEFAULT NULL COMMENT 备注信息;需要提醒的是ALTER TABLE在修改大表时是个重量级操作。MySQL 8.0之前的版本很多ALTER操作都会锁表执行期间业务写入会全部卡住。MySQL 8.0里部分DDL已经支持了INSTANT算法比如加列的时候可以瞬间完成不重建表。但并非所有操作都支持所以生产环境做表结构变更前必须评估表的大小如果是千万级以上的表建议用在线DDL工具。2.2 大表改表的方案从原生DDL到gh-ost原生ALTER TABLE在大表上的性能问题核心在于它需要COPY整个表的数据很多操作确实如此并在COPY期间获取MDL锁。MySQL 8.0虽然有优化但实际操作时仍建议谨慎评估。业界常用方案有两个一个是Percona Toolkit里的pt-online-schema-change另一个是GitHub开源的gh-ost。pt-online-schema-change的原理是创建一个新表按主键分批把旧表数据拷贝到新表然后通过触发器把拷贝期间的增量数据同步到新表最后原子切换表名。这个过程几乎不阻塞业务读写适合大表变更。gh-ost也是同样的思路但它不依赖触发器而是通过解析binlog实现增量同步侵入性更小在主从环境下使用更安全。我个人的经验是如果表数据量在百万以下并且业务允许短时间锁表直接ALTER TABLE没问题。如果数据量在千万级别或者你的业务是7×24小时在线服务那就一定要用在线工具而且建议在业务低峰期执行同时做好失败回滚预案。变更前备份表结构、记录变更SQL变更后检查数据和索引状态稳字当头。2.3 表管理操作TRUNCATE、DROP与恢复策略DELETE FROM t_order这种操作只删除数据不动表结构可以加WHERE条件精确删除。但如果要清空整张表DELETE的效率就太低了它是一行一行删的还要记binlog速度很慢。这时候用TRUNCATE TABLE t_order它直接删除所有数据并重置自增ID速度飞快但它没有任何条件过滤的能力也不能回滚一旦执行就是全表清空。DROP TABLE就更不用说了整个表结构加数据一起消失。这里我必须强调一个实操习惯任何DROP和TRUNCATE操作执行前先在测试环境验证一遍SQL再在生产环境执行前用SHOW CREATE TABLE确认这是你要删的表。另外强烈建议日常做好备份。最简单的备份策略是mysqldump定期导出也可以开binlog日志这样误删后还能通过binlog恢复到某个时间点。不要觉得备份麻烦真出事的时候备份就是救命稻草。3. 查询基本功从SELECT到执行顺序的底层逻辑写查询SQL是开发者的日常但真正能把SELECT写明白的人其实不多。很多人的SQL能跑出结果但效率极差、逻辑经不起推敲。搞清楚SQL执行顺序是写出正确且高效查询的第一步。3.1 常用查询条件WHERE背后的类型陷阱一个标准的查询语句长这样SELECT user_id, order_no, total_amount, status FROM t_order WHERE status 1 AND total_amount 1000 ORDER BY create_time DESC LIMIT 10;WHERE后面可以跟等值条件、范围条件、模糊查询、IN列表、IS NULL判断等。这里有一个经典陷阱字段类型和条件的类型不一致时MySQL可能无法走索引。举例来说如果order_no的类型是VARCHAR但查询时没加引号写成了WHERE order_no 123456MySQL会做隐式类型转换把字符串转成数字来比较。这个转换会让索引失效全表扫描惨不忍睹。模糊查询也要留意。LIKE abc%是可以走索引的但LIKE %abc这种前置通配符是走不了索引的就算字段上建了索引也没用。如果业务确实需要这种后缀模糊匹配可以考虑改存储方案比如搜索引擎、倒排索引或者用覆盖索引技巧来优化。NULL的判断也有门道。WHERE 字段 NULL查不出来任何结果必须用IS NULL。而且NULL参与运算的结果永远是NULL比如字段 1如果字段是NULL结果也是NULL。设计表结构时能用NOT NULL就用NOT NULL这不仅能减少查询时的判断麻烦对索引性能也有帮助。3.2 排序、去重与分页的细节点排序是查询的刚需。ORDER BY默认升序ASC降序要写DESC。多字段排序时从左到右依次生效比如ORDER BY status ASC, create_time DESC就是先按状态排状态相同再按时间倒序排。排序字段如果没有索引MySQL就要生成临时表做filesort数据量大时性能会非常差这在执行计划里能看到Extra列有Using filesort。去重最常用的是DISTINCTSELECT DISTINCT user_id FROM t_order WHERE status 1;DISTINCT是去重整行的不是只去重其中一个字段。如果你只想要某个字段的去重结果那么返回的SELECT子句只能包含该字段。如果需要同时查多个字段但只想去重其中一个DISTINCT就无能为力了可以考虑GROUP BY方案。另外很多人混淆了DISTINCT和GROUP BY其实在去重场景下它们的效果基本一致用法上GROUP BY更灵活因为可以配合聚合函数。分页是另一个高频场景SELECT * FROM t_order ORDER BY create_time DESC LIMIT 100000, 20;这个查询看起来没问题但LIMIT的offset越大性能越差因为MySQL要把前100000行全部扫出来丢掉再返回后面的20行。数据量大了之后深分页的接口会越来越慢。一个优化方案是用延迟关联SELECT t.* FROM t_order t INNER JOIN ( SELECT id FROM t_order ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON t.id tmp.id;子查询里只用主键排序并取20个ID然后再回原表关联查询完整字段这样能大大减少扫描量。网上还有基于游标分页的方案比如WHERE create_time 上一次查询的最小时间 ORDER BY create_time DESC LIMIT 20也需要根据业务场景取舍不过延迟关联在多数场景下效果最直接。3.3 聚合查询GROUP BY与HAVING的正确配合需要统计每个用户的订单数量、月销售总额这类需求就得用聚合查询SELECT user_id, COUNT(*) AS order_cnt, SUM(total_amount) AS total_amount FROM t_order WHERE create_time 2024-01-01 GROUP BY user_id HAVING order_cnt 3 ORDER BY total_amount DESC;GROUP BY按某个字段分组后SELECT后的字段一般有两种来源一种是分组字段本身另一种是聚合函数的结果。如果你想查user_id之外的字段比如user_name就必须把它也放到GROUP BY里或者用MIN/MAX这种聚合函数来包裹否则在MySQL的严格模式下会直接报错这也是网上常见的“only_full_group_by”报错的根源。HAVING是分组后的过滤条件它和WHERE的最大区别是WHERE在分组之前执行HAVING在分组之后执行。所以如果你要过滤的是“销售总额大于10000的用户”这个条件必须在HAVING里写因为WHERE执行时SUM还没算出来呢。聚合查询还要注意COUNT的细节。COUNT(*)和COUNT(1)基本没差别统计的是行数COUNT(某个字段)统计的是该字段非NULL的行数。如果你的字段允许为NULL那COUNT的结果可能不是你想的那样。4. 多表关联与子查询JOIN、IN、EXISTS的取舍单表查询只是基础真实业务里数据很少全在单张表里。用户表、订单表、商品表、分类表查一个订单详情就得关联好几张表。多表关联看起来是不难但一旦写不好笛卡尔积直接能把数据库打挂。4.1 JOIN的类型选择与性能差异JOIN主要分INNER JOIN、LEFT JOIN、RIGHT JOIN、CROSS JOIN几种。INNER JOIN只返回两边表都匹配上的数据LEFT JOIN返回左表所有数据加上右表匹配的数据右表没有匹配就是NULLRIGHT JOIN正好反过来。日常用得最多的是INNER JOIN和LEFT JOINRIGHT JOIN基本可以被改写为LEFT JOIN可读性更好也不容易出错。SELECT u.user_name, o.order_no, o.total_amount FROM t_user u INNER JOIN t_order o ON u.id o.user_id WHERE o.status 1;这里要注意JOIN的性能核心是连接字段要有索引。连接字段没有索引MySQL就要从驱动表一行一行去被驱动表里全表扫性能瞬间爆炸。在JOIN操作前先用EXPLAIN确认被驱动表的连接字段上用到了索引。还有个很多人会踩的坑是JOIN条件写错导致的笛卡尔积膨胀。比如两张表各有一万条数据你写ON条件时用了错误的字段结果集合可能变成一亿行。我之前排查过同事的一个慢查询就是他把ON u.id o.user_id写成了ON u.id 0 AND o.user_id 0这种条件每条都成立结果是灾难性的。4.2 子查询的三个高频场景与坑子查询指的是嵌套在SELECT、FROM、WHERE里的查询语句。最常见的三个场景是WHERE字段IN一个子查询、FROM里放一个子查询作为临时表、SELECT里标量子查询。举个例子SELECT u.* FROM t_user u WHERE u.id IN (SELECT user_id FROM t_order WHERE status 1);这个查询的意思很直白找出有已支付订单的所有用户。但在MySQL的早期版本里IN子查询的性能优化并不理想有时候会被改写成全表扫描效率极低。遇到这种情况优先考虑改写为EXISTS或者JOIN。上面这个SQL改用EXISTS就是SELECT u.* FROM t_user u WHERE EXISTS ( SELECT 1 FROM t_order o WHERE o.user_id u.id AND o.status 1 );EXISTS是半连接语义它不关心子查询返回多少行只要子查询能匹配到一条记录条件就算成立。这种写法对子查询的性能要求低而且逻辑和索引策略往往更优。但要注意EXISTS的子查询里不要用SELECT *写成SELECT 1就行语义更清晰性能上也没有额外开销。FROM里放子查询也很常见把一个聚合查询的结果当作临时表来用SELECT tmp.user_id, tmp.cnt FROM ( SELECT user_id, COUNT(*) AS cnt FROM t_order GROUP BY user_id ) tmp WHERE tmp.cnt 10;这个临时表会在查询结束后自动销毁适合做多步骤分析。但要注意MySQL会给临时表自动加一个别名的要求FROM子查询必须写别名不写直接报错。4.3 关联更新与删除一条SQL完不成的事更新和删除操作中的子查询是另一个高频场景。比如“把2024年下过单的用户状态改为VIP”新手会这么写UPDATE t_user SET user_level VIP WHERE id IN (SELECT user_id FROM t_order WHERE create_time 2024-01-01);这条SQL在MySQL里会直接报错因为MySQL不允许在更新一张表时子查询里同时查询并关联同一张表实际是子查询读取t_user相关数据时会与UPDATE的锁定目标冲突不同版本报错信息略有差异。解决方法是把子查询再包一层临时表UPDATE t_user SET user_level VIP WHERE id IN ( SELECT user_id FROM ( SELECT DISTINCT user_id FROM t_order WHERE create_time 2024-01-01 ) tmp );删除操作也有类似的限制DELETE加子查询修改同一张表时会报“You cant specify target table for update in FROM clause”这类错误也是靠包一层临时表解决。这个坑在面试里很喜欢考实际业务里更是常有懂了就不会在那里抓耳挠腮。5. 查询性能优化索引、执行计划与慢日志慢查询是数据库最常见的病但诊断慢查询本身并不难。只要你有索引设计和执行计划这两把刷子再配合慢查询日志基本能定位绝大多数性能问题。这一章节我重点讲这些实操内容。5.1 索引设计的核心原则与失效场景索引的本质是一种有序的数据结构InnoDB里默认是B树。它牺牲写入性能换取查询性能决策依据是“访问模式”。一般来说针对高频查询的WHERE条件、JOIN的ON条件、ORDER BY的排序字段都应该建立合适索引。普通索引、唯一索引、联合索引、全文索引是几种常见类型。联合索引要特别注意最左前缀原则比如你在(user_id, status)上建立了联合索引那么WHERE user_id ?可以走这个索引WHERE user_id ? AND status ?也可以走这个索引但如果只写WHERE status ?索引就使用不了。设计联合索引时一般把等值查询的字段放前面范围查询的字段放后面。索引失效的场景我列一个在实际环境中高频出现的清单对索引字段做函数操作比如WHERE DATE(create_time) 2024-01-01create_time上建了索引也没用应该写成create_time 2024-01-01 AND create_time 2024-01-02。隐式类型转换前面提过的字符串字段和数字比较那个坑。LIKE前置通配符比如LIKE %abc。联合索引不满足最左前缀。IS NOT NULL在部分情况下可能不走索引。5.2 EXPLAIN执行计划怎么读当一条SQL慢下来第一件事就是用EXPLAIN看它的执行计划。EXPLAIN的输出有很多列我挑最核心的讲type、key、rows、Extra。type从好到差依次是const、eq_ref、ref、range、index、ALL。const表示主键或唯一索引等值查询最理想ref表示使用了非唯一索引等值查询range表示索引范围扫描index表示全索引扫描ALL就是全表扫描这是最需要警惕的。我们的目标就是让关键查询的type达到ref或range以上。key表示实际用到的索引rows是预估扫描的行数这个值越小越好但它只是估计值有时候不太准确。Extra列值得细看出现Using filesort说明排序没走索引要检查ORDER BY字段是否需要加索引出现Using temporary说明查询用到了临时表常见于GROUP BY和DISTINCT可以优化出现Using index说明用到了覆盖索引查询的字段都能从索引中直接取得不需要回表这是最优状态。前阵子我接手一个查询SQL很简单SELECT * FROM t_order WHERE status 1 ORDER BY create_time DESC;EXPLAIN一看type是ALLExtra里有Using filesort因为status区分度太低了MySQL觉得还不如全表扫。当时的优化方案是在(status, create_time)上建联合索引这样WHERE和ORDER BY都能覆盖type变成refUsing filesort也没了查询速度从1.8秒降到了0.03秒。5.3 慢查询日志找出拖后腿的SQL慢查询日志是MySQL自带的一个功能专门记录执行时间超过指定阈值的SQL。平时的线上环境一定要开启不然你根本不知道哪些SQL在拖后腿。相关的核心参数是SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;long_query_time单位是秒一般设为1秒就够用了。生产环境如果很忙可以设0.5秒抓得更细但日志量也会大一些。开启后MySQL会把执行时间超过阈值的SQL、执行次数、锁等待时间、扫描行数等信息都记录到慢日志文件里。分析慢日志最简单的方式是用mysqldumpslow工具来做汇总。比如看访问次数最多的5条慢查询mysqldumpslow -s c -t 5 /var/log/mysql/slow.log还有更直观的方案是接上可视化平台它是基于Percona Toolkit的数据收集Web展示方案可以按总耗时、平均耗时、扫描行数排名非常方便。我的习惯是每周看一次慢查询报告重点关注那些“经常出现且单次执行时间很高”的SQL把它们揪出来用EXPLAIN分析能优化索引的先优化索引不能优化的再考虑改写SQL逻辑。慢查询日志加EXPLAIN这是DBA和开发排查性能问题最基础、最有效的一套组合拳。6. 高频问题与排查思路速查实战中踩过太多坑了有的是自己踩的有的是帮别人擦屁股踩的。我把一些最常见的报错和问题整理成一个速查表配合排查思路一并写出来对提升效率特别有帮助。6.1 常见报错的根源与解决报错/问题常见原因解决方案ERROR 1064 (42000) SQL语法错误关键字没加反引号、逗号缺失、引号不匹配用SHOW CREATE TABLE检查字段名避免使用保留字ERROR 1054 (42S22) Unknown column查询的列名不存在或表别名没写对仔细检查SELECT、WHERE、JOIN中所有字段名Error 1055 only_full_group_bySELECT的字段没在GROUP BY里也不是聚合函数把字段加到GROUP BY里或用ANY_VALUE包裹ERROR 1093 You cant specify target tableUPDATE/DELETE的子查询涉及同一张表用中间临时表包一层子查询查询结果中文乱码连接字符集和后端工具字符集不匹配连接串加characterEncodingutf8检查表格字符集分页越来越慢LIMIT深offset扫描大量行用延迟关联或游标分页索引明明建了却不生效函数操作、类型转换、最左前缀不满足用EXPLAIN确认执行计划避免在字段上做运算这些报错里only_full_group_by是最多初学者困惑的。MySQL 5.7之后默认开启了ONLY_FULL_GROUP_BY模式对GROUP BY的要求更严格了。很多人以前写的“SELECT a, b FROM t GROUP BY a”这种不规范SQL在5.7之后就会报错。遇到这个问题优先改SQL通过调整分组字段或使用聚合函数来满足模式要求而不是直接改数据库配置关闭该模式。保留严格模式长期看是督促自己写出更好SQL的办法。6.2 一套可复用的排查路径当线上出现一个数据库相关的疑难杂症我建议按下面的路径排查不要东一榔头西一棒子先确认问题范围。是单条SQL慢还是整个数据库慢是某个特定的查询接口慢还是所有写入都变慢了范围不同排查方向完全不同。单条SQL慢的先EXPLAIN。看一眼type、key、rows、Extra是索引没建对还是SQL写得有问题。整个库都慢的先看系统负载。SHOW PROCESSLIST看谁在占用连接有没有锁等待有没有长事务在跑。通过性能指标看是否有CPU飙升、磁盘IO异常这可能就是有慢查询在批量扫描。锁等待问题查information_schema.innodb_trx和sys.innodb_lock_waits找出持锁的事务和等待锁的事务不要盲目KILL连接先确认事务来源。开启慢查询日志持续观察一段时间还原事故现场。很多偶发问题平时看不到慢日志能帮你记录下来。这套路径谈不上高深但确实能解决我遇到过的大多数问题。很多紧急事故最后复盘出来原因都很朴素要么是SQL忘记加WHERE条件了要么是索引在最近一次表结构变更时被误删了要么是某个人手动执行了一条全表更新的SQL忘了带事务。数据库的问题大多是“人祸”而排查路径的意义就在于让你用最短的时间找到那个犯错的人。结尾做了这么多年MySQL相关的工作我的体会是表操作和查询从来不是背诵语法的问题核心是对数据结构、索引机制、执行计划有一个基本的预判能力。建表时多想一步“以后要怎么查这张表”写查询时多想一步“这条SQL会怎么扫描数据”就能规避掉绝大多数性能坑。最后再分享一个工作习惯所有涉及生产环境的ALTER TABLE、DELETE、UPDATE先在测试库跑一遍备份好再执行这是我可以安然度过这么多年运维生涯的最大秘诀。希望这篇文章能帮你在MySQL这条路上少踩几个坑把表建扎实了把查询写聪明了。 MySQL建表与查询优化实战：从字段类型到索引调优
搞MySQL搞了好些年日常打交道最多的就是建表和查数据。表结构设计得好不好查询写得到不到位直接决定了你后续是躺平喝茶还是半夜爬起来处理慢查询。尤其是那些刚入门的朋友被各种教程带着复制粘贴建表全靠直觉查询全靠运气出了问题又不知道该查哪里最后只能把锅甩给“数据库不稳定”。这篇文章我就基于实际经验把MySQL的表操作和查询这两块掰开揉碎讲清楚从建表时的字段类型选择、约束设计到查询时的执行逻辑、索引优化再到常见报错的排查路径都给你梳理一遍保证你看完能直接用在自己的项目里。1. 建表是根基字段类型、约束与字符集的取舍很多人觉得建表不就是写个CREATE TABLE吗有什么好讲的。但恰恰是这一步决定了一张表未来几年的命运。字段类型选错了轻则浪费存储空间重则查询慢到让你怀疑人生约束没设计好业务层写再多校验代码都挡不住脏数据进来。所以建表这块我建议你多花点心思。1.1 字段类型怎么选别再用VARCHAR存所有东西新手最常见的问题就是不管什么数据都往VARCHAR里塞手机号、日期、金额、状态码全部用字符串存。短期看确实省事但长期看全是坑。日期字段用字符串存你就没法直接用DATE_SUB、DATE_FORMAT这类函数做计算和格式化排序也是按字典序排的2024-01-01和2024-9-1排出来的结果保证让你一脸懵。金额字段用字符串存计算的时候还得CAST一下精度还容易出问题。状态码用字符串存索引长度白白变长查询效率也跟着掉。我个人的实践经验是能用整数用整数能用日期用日期金额就用DECIMAL。比如用户状态、订单状态这种枚举值用TINYINT就够了0表示待支付、1表示已支付、2表示已取消清晰又高效。时间戳字段用INT存也行但可读性太差不如直接用DATETIME。还有一个容易踩的坑是DECIMAL它不会丢失精度存金额就选DECIMAL(10,2)千万别用FLOAT或DOUBLE浮点数存金额算账的时候那种“0.10.2不等于0.3”的问题会要了财务的命。另一个值得说的是长度设置。VARCHAR(255)是很多人偷懒用的默认值但如果你明确知道用户名最长不会超过20个字符就老老实实写VARCHAR(20)。VARCHAR是按实际内容长度存储的不是固定分配所以长度设多大不影响存储空间但会影响内存排序和临时表的效率而且索引长度也和字段长度强相关。能设小绝不设大这是建表的基本修养。1.2 主键、约束与索引建表时的隐形决策主键是一张表的灵魂没主键的表就像没有身份证的人数据重复了都没办法纠正。MySQL的InnoDB存储引擎默认使用聚簇索引组织数据而聚簇索引的叶子节点就存着整行数据。这意味着主键的选择会直接影响所有二级索引的性能。我的建议简单粗暴使用自增BIGINT作为主键或者用雪花算法生成分布式ID。不要用UUID当主键UUID是无序的随机字符串插入时会导致聚簇索引频繁分裂页、产生碎片数据量大了之后插入性能会急剧恶化硬盘上的表文件也会大得离谱。约束方面除了主键约束还有NOT NULL、UNIQUE、DEFAULT、FOREIGN KEY这几个。NOT NULL一定要按业务语义设定。比如用户注册时间理论上注册成功就一定有值那就加NOT NULL防止业务逻辑有漏洞时写入NULL。UNIQUE约束用来保证唯一性比如用户表的手机号列加个UNIQUE KEY比在业务代码里先SELECT再INSERT靠谱得多并发下后者必然出重复数据。DEFAULT值也建议顺手写上很多字段是有默认值的比如状态字段默认0、创建时间默认CURRENT_TIMESTAMP这样插入数据时少传几个字段也完全没问题。外键约束我持保留态度。互联网大厂的高并发场景下普遍不建议物理外键因为外键在写入时要额外检查关联表的记录带来额外的锁竞争和性能损耗。如果你做的是传统企业级应用数据量不大、并发不高物理外键反而能省掉很多应用层的判断逻辑、保证数据完整性用也无妨。关键看场景没有绝对的对错。1.3 字符集与排序规则乱码和排序错乱的根源字符集这块记住两句话数据库、表、字段的字符集统一用utf8mb4排序规则统一用utf8mb4_general_ci或utf8mb4_0900_ai_ci。为什么不用utf8因为MySQL的utf8是utf8mb3它只支持最多3个字节的字符存不了emoji和一些特殊汉字。现在谁聊天不带个表情万一将来业务要存emoji字段就得重建。与其到时候炸了再救火不如一开始就上utf8mb4。排序规则里的ci表示大小写不敏感对绝大多数业务场景都适用。如果你有特殊需求例如用户名登录时要区分大小写那就要用utf8mb4_bin。这块还要提醒一个细节如果建库时用了utf8mb4建表时没显式指定表会继承库的字符集建表时指定了但某些字段漏了字段会继承表的字符集。这种一脉相承的机制经常让很多人以为“我设置了呀怎么还乱码”十有八九是有人后来手动改过某个字段的字符集或者链接层没指定字符集。排查乱码时先看表结构的SHOW CREATE TABLE再看连接串是否加了characterEncodingutf8这两步能解决九成问题。1.4 建表实操从需求到可落地的完整SQL光说不练假把式我随手写一个订单表的建表语句顺便把刚才讲的东西串起来CREATE TABLE t_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, order_no VARCHAR(32) NOT NULL COMMENT 订单号, user_id BIGINT UNSIGNED NOT NULL COMMENT 用户ID, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 订单总金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态 0-待支付 1-已支付 2-已取消, pay_time DATETIME DEFAULT NULL COMMENT 支付时间, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time 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_create_time (create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT订单表;这个表有几个细节值得逐一说清楚。order_no加了唯一索引这是业务主键和物理主键id分开是常规操作。user_id建普通索引因为后边要按照用户查订单列表。create_time也建索引用于按时间范围统计订单量。update_time用了ON UPDATE CURRENT_TIMESTAMP这样每次记录被更新时这个字段会自动刷新省得在应用层手动维护。ENGINE指定InnoDB这是MySQL 8.0默认引擎支持事务和行级锁。CHARSET和COLLATE显式写出来避免依赖库级别的隐式配置。2. 表结构修改ALTER TABLE操作与在线DDL的实战经验数据库上线以后需求可不会等你。今天要加个字段明天要改个长度后天要删个列这类操作躲不掉。但ALTER TABLE不是想怎么改就怎么改的MySQL在处理某些DDL时会锁全表直接阻塞线上读写这就是生产事故的导火索。2.1 增删改字段语法、位置与代价增字段最常用的语法是ALTER TABLE t_order ADD COLUMN remark VARCHAR(255) DEFAULT NULL COMMENT 备注 AFTER status;AFTER关键字可以控制新字段加在哪个列后边不写的话默认追加在最后。这里有个点要注意如果你只关心能不能查询数据字段位置无关紧要但如果你有强迫症想保持表结构整洁或者项目里的代码生成工具会依赖字段顺序那么还是显式指定位置更省心。修改字段类型和长度也很常见ALTER TABLE t_order MODIFY COLUMN remark VARCHAR(500) DEFAULT NULL COMMENT 备注;MODIFY COLUMN可以修改字段类型、长度、默认值、注释等。还有一个CHANGE COLUMN相比MODIFY多了一个重命名的功能ALTER TABLE t_order CHANGE COLUMN remark remark_info VARCHAR(500) DEFAULT NULL COMMENT 备注信息;需要提醒的是ALTER TABLE在修改大表时是个重量级操作。MySQL 8.0之前的版本很多ALTER操作都会锁表执行期间业务写入会全部卡住。MySQL 8.0里部分DDL已经支持了INSTANT算法比如加列的时候可以瞬间完成不重建表。但并非所有操作都支持所以生产环境做表结构变更前必须评估表的大小如果是千万级以上的表建议用在线DDL工具。2.2 大表改表的方案从原生DDL到gh-ost原生ALTER TABLE在大表上的性能问题核心在于它需要COPY整个表的数据很多操作确实如此并在COPY期间获取MDL锁。MySQL 8.0虽然有优化但实际操作时仍建议谨慎评估。业界常用方案有两个一个是Percona Toolkit里的pt-online-schema-change另一个是GitHub开源的gh-ost。pt-online-schema-change的原理是创建一个新表按主键分批把旧表数据拷贝到新表然后通过触发器把拷贝期间的增量数据同步到新表最后原子切换表名。这个过程几乎不阻塞业务读写适合大表变更。gh-ost也是同样的思路但它不依赖触发器而是通过解析binlog实现增量同步侵入性更小在主从环境下使用更安全。我个人的经验是如果表数据量在百万以下并且业务允许短时间锁表直接ALTER TABLE没问题。如果数据量在千万级别或者你的业务是7×24小时在线服务那就一定要用在线工具而且建议在业务低峰期执行同时做好失败回滚预案。变更前备份表结构、记录变更SQL变更后检查数据和索引状态稳字当头。2.3 表管理操作TRUNCATE、DROP与恢复策略DELETE FROM t_order这种操作只删除数据不动表结构可以加WHERE条件精确删除。但如果要清空整张表DELETE的效率就太低了它是一行一行删的还要记binlog速度很慢。这时候用TRUNCATE TABLE t_order它直接删除所有数据并重置自增ID速度飞快但它没有任何条件过滤的能力也不能回滚一旦执行就是全表清空。DROP TABLE就更不用说了整个表结构加数据一起消失。这里我必须强调一个实操习惯任何DROP和TRUNCATE操作执行前先在测试环境验证一遍SQL再在生产环境执行前用SHOW CREATE TABLE确认这是你要删的表。另外强烈建议日常做好备份。最简单的备份策略是mysqldump定期导出也可以开binlog日志这样误删后还能通过binlog恢复到某个时间点。不要觉得备份麻烦真出事的时候备份就是救命稻草。3. 查询基本功从SELECT到执行顺序的底层逻辑写查询SQL是开发者的日常但真正能把SELECT写明白的人其实不多。很多人的SQL能跑出结果但效率极差、逻辑经不起推敲。搞清楚SQL执行顺序是写出正确且高效查询的第一步。3.1 常用查询条件WHERE背后的类型陷阱一个标准的查询语句长这样SELECT user_id, order_no, total_amount, status FROM t_order WHERE status 1 AND total_amount 1000 ORDER BY create_time DESC LIMIT 10;WHERE后面可以跟等值条件、范围条件、模糊查询、IN列表、IS NULL判断等。这里有一个经典陷阱字段类型和条件的类型不一致时MySQL可能无法走索引。举例来说如果order_no的类型是VARCHAR但查询时没加引号写成了WHERE order_no 123456MySQL会做隐式类型转换把字符串转成数字来比较。这个转换会让索引失效全表扫描惨不忍睹。模糊查询也要留意。LIKE abc%是可以走索引的但LIKE %abc这种前置通配符是走不了索引的就算字段上建了索引也没用。如果业务确实需要这种后缀模糊匹配可以考虑改存储方案比如搜索引擎、倒排索引或者用覆盖索引技巧来优化。NULL的判断也有门道。WHERE 字段 NULL查不出来任何结果必须用IS NULL。而且NULL参与运算的结果永远是NULL比如字段 1如果字段是NULL结果也是NULL。设计表结构时能用NOT NULL就用NOT NULL这不仅能减少查询时的判断麻烦对索引性能也有帮助。3.2 排序、去重与分页的细节点排序是查询的刚需。ORDER BY默认升序ASC降序要写DESC。多字段排序时从左到右依次生效比如ORDER BY status ASC, create_time DESC就是先按状态排状态相同再按时间倒序排。排序字段如果没有索引MySQL就要生成临时表做filesort数据量大时性能会非常差这在执行计划里能看到Extra列有Using filesort。去重最常用的是DISTINCTSELECT DISTINCT user_id FROM t_order WHERE status 1;DISTINCT是去重整行的不是只去重其中一个字段。如果你只想要某个字段的去重结果那么返回的SELECT子句只能包含该字段。如果需要同时查多个字段但只想去重其中一个DISTINCT就无能为力了可以考虑GROUP BY方案。另外很多人混淆了DISTINCT和GROUP BY其实在去重场景下它们的效果基本一致用法上GROUP BY更灵活因为可以配合聚合函数。分页是另一个高频场景SELECT * FROM t_order ORDER BY create_time DESC LIMIT 100000, 20;这个查询看起来没问题但LIMIT的offset越大性能越差因为MySQL要把前100000行全部扫出来丢掉再返回后面的20行。数据量大了之后深分页的接口会越来越慢。一个优化方案是用延迟关联SELECT t.* FROM t_order t INNER JOIN ( SELECT id FROM t_order ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON t.id tmp.id;子查询里只用主键排序并取20个ID然后再回原表关联查询完整字段这样能大大减少扫描量。网上还有基于游标分页的方案比如WHERE create_time 上一次查询的最小时间 ORDER BY create_time DESC LIMIT 20也需要根据业务场景取舍不过延迟关联在多数场景下效果最直接。3.3 聚合查询GROUP BY与HAVING的正确配合需要统计每个用户的订单数量、月销售总额这类需求就得用聚合查询SELECT user_id, COUNT(*) AS order_cnt, SUM(total_amount) AS total_amount FROM t_order WHERE create_time 2024-01-01 GROUP BY user_id HAVING order_cnt 3 ORDER BY total_amount DESC;GROUP BY按某个字段分组后SELECT后的字段一般有两种来源一种是分组字段本身另一种是聚合函数的结果。如果你想查user_id之外的字段比如user_name就必须把它也放到GROUP BY里或者用MIN/MAX这种聚合函数来包裹否则在MySQL的严格模式下会直接报错这也是网上常见的“only_full_group_by”报错的根源。HAVING是分组后的过滤条件它和WHERE的最大区别是WHERE在分组之前执行HAVING在分组之后执行。所以如果你要过滤的是“销售总额大于10000的用户”这个条件必须在HAVING里写因为WHERE执行时SUM还没算出来呢。聚合查询还要注意COUNT的细节。COUNT(*)和COUNT(1)基本没差别统计的是行数COUNT(某个字段)统计的是该字段非NULL的行数。如果你的字段允许为NULL那COUNT的结果可能不是你想的那样。4. 多表关联与子查询JOIN、IN、EXISTS的取舍单表查询只是基础真实业务里数据很少全在单张表里。用户表、订单表、商品表、分类表查一个订单详情就得关联好几张表。多表关联看起来是不难但一旦写不好笛卡尔积直接能把数据库打挂。4.1 JOIN的类型选择与性能差异JOIN主要分INNER JOIN、LEFT JOIN、RIGHT JOIN、CROSS JOIN几种。INNER JOIN只返回两边表都匹配上的数据LEFT JOIN返回左表所有数据加上右表匹配的数据右表没有匹配就是NULLRIGHT JOIN正好反过来。日常用得最多的是INNER JOIN和LEFT JOINRIGHT JOIN基本可以被改写为LEFT JOIN可读性更好也不容易出错。SELECT u.user_name, o.order_no, o.total_amount FROM t_user u INNER JOIN t_order o ON u.id o.user_id WHERE o.status 1;这里要注意JOIN的性能核心是连接字段要有索引。连接字段没有索引MySQL就要从驱动表一行一行去被驱动表里全表扫性能瞬间爆炸。在JOIN操作前先用EXPLAIN确认被驱动表的连接字段上用到了索引。还有个很多人会踩的坑是JOIN条件写错导致的笛卡尔积膨胀。比如两张表各有一万条数据你写ON条件时用了错误的字段结果集合可能变成一亿行。我之前排查过同事的一个慢查询就是他把ON u.id o.user_id写成了ON u.id 0 AND o.user_id 0这种条件每条都成立结果是灾难性的。4.2 子查询的三个高频场景与坑子查询指的是嵌套在SELECT、FROM、WHERE里的查询语句。最常见的三个场景是WHERE字段IN一个子查询、FROM里放一个子查询作为临时表、SELECT里标量子查询。举个例子SELECT u.* FROM t_user u WHERE u.id IN (SELECT user_id FROM t_order WHERE status 1);这个查询的意思很直白找出有已支付订单的所有用户。但在MySQL的早期版本里IN子查询的性能优化并不理想有时候会被改写成全表扫描效率极低。遇到这种情况优先考虑改写为EXISTS或者JOIN。上面这个SQL改用EXISTS就是SELECT u.* FROM t_user u WHERE EXISTS ( SELECT 1 FROM t_order o WHERE o.user_id u.id AND o.status 1 );EXISTS是半连接语义它不关心子查询返回多少行只要子查询能匹配到一条记录条件就算成立。这种写法对子查询的性能要求低而且逻辑和索引策略往往更优。但要注意EXISTS的子查询里不要用SELECT *写成SELECT 1就行语义更清晰性能上也没有额外开销。FROM里放子查询也很常见把一个聚合查询的结果当作临时表来用SELECT tmp.user_id, tmp.cnt FROM ( SELECT user_id, COUNT(*) AS cnt FROM t_order GROUP BY user_id ) tmp WHERE tmp.cnt 10;这个临时表会在查询结束后自动销毁适合做多步骤分析。但要注意MySQL会给临时表自动加一个别名的要求FROM子查询必须写别名不写直接报错。4.3 关联更新与删除一条SQL完不成的事更新和删除操作中的子查询是另一个高频场景。比如“把2024年下过单的用户状态改为VIP”新手会这么写UPDATE t_user SET user_level VIP WHERE id IN (SELECT user_id FROM t_order WHERE create_time 2024-01-01);这条SQL在MySQL里会直接报错因为MySQL不允许在更新一张表时子查询里同时查询并关联同一张表实际是子查询读取t_user相关数据时会与UPDATE的锁定目标冲突不同版本报错信息略有差异。解决方法是把子查询再包一层临时表UPDATE t_user SET user_level VIP WHERE id IN ( SELECT user_id FROM ( SELECT DISTINCT user_id FROM t_order WHERE create_time 2024-01-01 ) tmp );删除操作也有类似的限制DELETE加子查询修改同一张表时会报“You cant specify target table for update in FROM clause”这类错误也是靠包一层临时表解决。这个坑在面试里很喜欢考实际业务里更是常有懂了就不会在那里抓耳挠腮。5. 查询性能优化索引、执行计划与慢日志慢查询是数据库最常见的病但诊断慢查询本身并不难。只要你有索引设计和执行计划这两把刷子再配合慢查询日志基本能定位绝大多数性能问题。这一章节我重点讲这些实操内容。5.1 索引设计的核心原则与失效场景索引的本质是一种有序的数据结构InnoDB里默认是B树。它牺牲写入性能换取查询性能决策依据是“访问模式”。一般来说针对高频查询的WHERE条件、JOIN的ON条件、ORDER BY的排序字段都应该建立合适索引。普通索引、唯一索引、联合索引、全文索引是几种常见类型。联合索引要特别注意最左前缀原则比如你在(user_id, status)上建立了联合索引那么WHERE user_id ?可以走这个索引WHERE user_id ? AND status ?也可以走这个索引但如果只写WHERE status ?索引就使用不了。设计联合索引时一般把等值查询的字段放前面范围查询的字段放后面。索引失效的场景我列一个在实际环境中高频出现的清单对索引字段做函数操作比如WHERE DATE(create_time) 2024-01-01create_time上建了索引也没用应该写成create_time 2024-01-01 AND create_time 2024-01-02。隐式类型转换前面提过的字符串字段和数字比较那个坑。LIKE前置通配符比如LIKE %abc。联合索引不满足最左前缀。IS NOT NULL在部分情况下可能不走索引。5.2 EXPLAIN执行计划怎么读当一条SQL慢下来第一件事就是用EXPLAIN看它的执行计划。EXPLAIN的输出有很多列我挑最核心的讲type、key、rows、Extra。type从好到差依次是const、eq_ref、ref、range、index、ALL。const表示主键或唯一索引等值查询最理想ref表示使用了非唯一索引等值查询range表示索引范围扫描index表示全索引扫描ALL就是全表扫描这是最需要警惕的。我们的目标就是让关键查询的type达到ref或range以上。key表示实际用到的索引rows是预估扫描的行数这个值越小越好但它只是估计值有时候不太准确。Extra列值得细看出现Using filesort说明排序没走索引要检查ORDER BY字段是否需要加索引出现Using temporary说明查询用到了临时表常见于GROUP BY和DISTINCT可以优化出现Using index说明用到了覆盖索引查询的字段都能从索引中直接取得不需要回表这是最优状态。前阵子我接手一个查询SQL很简单SELECT * FROM t_order WHERE status 1 ORDER BY create_time DESC;EXPLAIN一看type是ALLExtra里有Using filesort因为status区分度太低了MySQL觉得还不如全表扫。当时的优化方案是在(status, create_time)上建联合索引这样WHERE和ORDER BY都能覆盖type变成refUsing filesort也没了查询速度从1.8秒降到了0.03秒。5.3 慢查询日志找出拖后腿的SQL慢查询日志是MySQL自带的一个功能专门记录执行时间超过指定阈值的SQL。平时的线上环境一定要开启不然你根本不知道哪些SQL在拖后腿。相关的核心参数是SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;long_query_time单位是秒一般设为1秒就够用了。生产环境如果很忙可以设0.5秒抓得更细但日志量也会大一些。开启后MySQL会把执行时间超过阈值的SQL、执行次数、锁等待时间、扫描行数等信息都记录到慢日志文件里。分析慢日志最简单的方式是用mysqldumpslow工具来做汇总。比如看访问次数最多的5条慢查询mysqldumpslow -s c -t 5 /var/log/mysql/slow.log还有更直观的方案是接上可视化平台它是基于Percona Toolkit的数据收集Web展示方案可以按总耗时、平均耗时、扫描行数排名非常方便。我的习惯是每周看一次慢查询报告重点关注那些“经常出现且单次执行时间很高”的SQL把它们揪出来用EXPLAIN分析能优化索引的先优化索引不能优化的再考虑改写SQL逻辑。慢查询日志加EXPLAIN这是DBA和开发排查性能问题最基础、最有效的一套组合拳。6. 高频问题与排查思路速查实战中踩过太多坑了有的是自己踩的有的是帮别人擦屁股踩的。我把一些最常见的报错和问题整理成一个速查表配合排查思路一并写出来对提升效率特别有帮助。6.1 常见报错的根源与解决报错/问题常见原因解决方案ERROR 1064 (42000) SQL语法错误关键字没加反引号、逗号缺失、引号不匹配用SHOW CREATE TABLE检查字段名避免使用保留字ERROR 1054 (42S22) Unknown column查询的列名不存在或表别名没写对仔细检查SELECT、WHERE、JOIN中所有字段名Error 1055 only_full_group_bySELECT的字段没在GROUP BY里也不是聚合函数把字段加到GROUP BY里或用ANY_VALUE包裹ERROR 1093 You cant specify target tableUPDATE/DELETE的子查询涉及同一张表用中间临时表包一层子查询查询结果中文乱码连接字符集和后端工具字符集不匹配连接串加characterEncodingutf8检查表格字符集分页越来越慢LIMIT深offset扫描大量行用延迟关联或游标分页索引明明建了却不生效函数操作、类型转换、最左前缀不满足用EXPLAIN确认执行计划避免在字段上做运算这些报错里only_full_group_by是最多初学者困惑的。MySQL 5.7之后默认开启了ONLY_FULL_GROUP_BY模式对GROUP BY的要求更严格了。很多人以前写的“SELECT a, b FROM t GROUP BY a”这种不规范SQL在5.7之后就会报错。遇到这个问题优先改SQL通过调整分组字段或使用聚合函数来满足模式要求而不是直接改数据库配置关闭该模式。保留严格模式长期看是督促自己写出更好SQL的办法。6.2 一套可复用的排查路径当线上出现一个数据库相关的疑难杂症我建议按下面的路径排查不要东一榔头西一棒子先确认问题范围。是单条SQL慢还是整个数据库慢是某个特定的查询接口慢还是所有写入都变慢了范围不同排查方向完全不同。单条SQL慢的先EXPLAIN。看一眼type、key、rows、Extra是索引没建对还是SQL写得有问题。整个库都慢的先看系统负载。SHOW PROCESSLIST看谁在占用连接有没有锁等待有没有长事务在跑。通过性能指标看是否有CPU飙升、磁盘IO异常这可能就是有慢查询在批量扫描。锁等待问题查information_schema.innodb_trx和sys.innodb_lock_waits找出持锁的事务和等待锁的事务不要盲目KILL连接先确认事务来源。开启慢查询日志持续观察一段时间还原事故现场。很多偶发问题平时看不到慢日志能帮你记录下来。这套路径谈不上高深但确实能解决我遇到过的大多数问题。很多紧急事故最后复盘出来原因都很朴素要么是SQL忘记加WHERE条件了要么是索引在最近一次表结构变更时被误删了要么是某个人手动执行了一条全表更新的SQL忘了带事务。数据库的问题大多是“人祸”而排查路径的意义就在于让你用最短的时间找到那个犯错的人。结尾做了这么多年MySQL相关的工作我的体会是表操作和查询从来不是背诵语法的问题核心是对数据结构、索引机制、执行计划有一个基本的预判能力。建表时多想一步“以后要怎么查这张表”写查询时多想一步“这条SQL会怎么扫描数据”就能规避掉绝大多数性能坑。最后再分享一个工作习惯所有涉及生产环境的ALTER TABLE、DELETE、UPDATE先在测试库跑一遍备份好再执行这是我可以安然度过这么多年运维生涯的最大秘诀。希望这篇文章能帮你在MySQL这条路上少踩几个坑把表建扎实了把查询写聪明了。