做后端开发这些年几乎每个项目都逃不过和 MySQL 打交道。而所有和 MySQL 的纠缠最后都会落到“表”这个最基本也最要命的东西上——表结构设计得合理后面半年都省心表结构设计得随意等你发现不对劲的时候改一次表可能就要熬夜上线。这篇东西我不打算讲安装、讲配置文件就专注把“表的相关操作”这一块讲透从建表、改表、加索引到数据增删改背后的事务和锁再到我实际踩过的一堆坑全给你捋一遍。不管是刚入门的学生还是工作了两三年想补补短板的开发应该都能从这里拿走点能直接用的东西。1. 先把表建明白字段类型与约束的设计细节很多新手觉得建表就是写个 CREATE TABLE把字段名和类型一填就完事。但表结构才是整个数据库设计的根字段类型选错了后面所有查询、索引、存储都会跟着遭殃。我自己接手过不少历史项目最头疼的就是看到那种“所有字符串一律 VARCHAR(255)、所有数字一律 INT、所有时间一律 VARCHAR”的表这种表不是不能用是等数据量上来之后查询慢到你想骂人还不好改。1.1 字段类型选错后面全是坑先说说最常见的整数类型。MySQL 提供了 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT 这几种区别就是取值范围和占用字节数。选型的逻辑其实很简单按业务上限来同时留出余量但别盲目给 BIGINT。比如状态字段就 0/1/2 三个值TINYINT 足够了一般业务主键用 INT 或 BIGINT如果牵扯到雪花 ID、订单号那种 19 位数字必须 BIGINT。另外还有个细节如果确认字段不会为负加上 UNSIGNED 能扩大正数范围但要注意 UNSIGNED 参与计算时容易出隐式转换的坑比如INT UNSIGNED减去一个负数会报错所以在设计阶段就要想清楚。字符串类型这里水最深。CHAR 和 VARCHAR 的区别很多人背过八股但没真正理解。CHAR 是定长适合长度固定且短的场景比如手机号、MD5 值VARCHAR 是变长适合大多数文本场景。VARCHAR(n) 里的 n 是字符数不是字节数这一点特别容易搞错。在 utf8mb4 字符集下一个中文占 3~4 字节一个 VARCHAR(255) 最多能存 255 个字符但实际占用的物理空间要按字节算所以别以为 255 就是 255 字节也别随便把 VARCHAR 拉到 1000 以上太长的话行溢出、索引长度受限都会接踵而至。至于 TEXT/BLOB能不用就不用尤其别拿 TEXT 当索引字段InnoDB 对索引键长度有上限大字段还会导致聚簇索引里的行记录变大查询性能肉眼可见地下降。真需要存大文本考虑拆分出去单独建表。小数类型是个经典考点。FLOAT 和 DOUBLE 是浮点数会有精度丢失比如存 0.1 在二进制里就是无限循环。金额、费率、百分比这类对精度敏感的数据一律用 DECIMAL。我之前见过一张表用 DOUBLE 存单价刚开始没感觉等数据累计到几十万行SUM 出来的金额对不上账财务那里直接炸锅。DECIMAL 是按字符串存储的精度可控代价是空间更大、计算稍慢但对业务正确性来说这点代价完全值得。另外百分比字段建议用 DECIMAL(5,4) 或 DECIMAL(8,4) 这种显式指定精度别默认 DECIMAL(10,2) 然后发现百分数只能存两位小数。时间字段更是重灾区。DATETIME 和 TIMESTAMP 的区别要记清楚DATETIME 存的是“墙上时间”不带时区范围是 1000~9999 年TIMESTAMP 存的是 UTC 时间戳显示时会按会话时区转换范围只到 2038 年。如果你做的是面向全球用户的系统建议统一用 DATETIME 存 UTC 时间或者用 TIMESTAMP 配合统一时区设置最怕的就是同一个库里既有 DATETIME 又有 TIMESTAMP时间又混着本地时区和 UTC查出来的数据你根本不知道是什么时间。还有个小技巧字段设计时可以直接把默认值设成CURRENT_TIMESTAMP更新时用ON UPDATE CURRENT_TIMESTAMP这样创建时间和更新时间就不用每次手动填了。如果业务上有字符串形式的日期别直接存字符串入库前用 STR_TO_DATE 转成日期类型或者规定接口层统一传时间戳不然后面按日期范围查询、按月分区全都做不了。MySQL 8.0 开始支持原生 JSON 类型这个类型在存储动态属性时很好用。比如订单表想存一些额外的扩展信息、标签、配置项用 JSON 字段能省掉一张 KV 表。但注意JSON 类型不能直接对内部字段建普通索引想按 JSON 里的某个 key 查得用生成列GENERATED COLUMN把那个 key 提取出来再建索引。这个功能是能用的但别把核心查询逻辑都压到 JSON 上JSON 字段更新时会导致整行重写频率高了对写入性能影响不小。1.2 约束和默认值让数据库替你把关建表时就把约束写好比在应用层反复判断要省心得多。首先是 NOT NULL 和 DEFAULT 的组合。我见过很多人容忍字段为 NULL觉得“NULL 就 NULL 呗”但在 InnoDB 里 NULL 值对索引不友好统计信息会偏查询条件写WHERE col NULL还永远不成立得写 IS NULL。更烦人的是多个字段都允许 NULL 时索引的存储和判断逻辑都会变复杂。所以我的建议很粗暴除了少数确实存在“未知”含义的字段其他一律 NOT NULL并给一个合理的默认值。比如数值字段默认 0状态字段默认枚举里的初始值时间字段默认 CURRENT_TIMESTAMP。NULL 和空字符串、0 是有语义区别的设计时要想清楚你到底需要哪种。主键的选择值得单独拿出来说。自增 INT/BIGINT 主键最大优势是写入顺序和索引顺序一致页分裂少性能稳定适合大多数内部系统。但一旦涉及分布式、多库多表自增主键容易撞车这时候要么用雪花算法生成 BIGINT要么用 UUID。用 UUID 做主键要注意标准 UUID 是无序字符串直接当主键会导致频繁的页分裂、索引膨胀性能远不如自增。如果业务强制需要 UUID可以考虑改成顺序 UUID或者仍然用自增主键UUID 做成唯一索引。另外主键越短越好因为在 InnoDB 里二级索引叶子节点存的是主键值主键长了所有二级索引都会变大。UNIQUE 约束和唯一索引在 MySQL 里实现机制几乎一样都是建唯一索引来保证约束。区别在于约束是逻辑层面的索引是物理层面的但你实际创建时写UNIQUE KEY就够了。它有两个角色一是防重复二是给查询用。比如用户表的手机号字段既要做登录匹配又要防重复注册一个 UNIQUE 索引全搞定。但别滥用唯一索引每一次 INSERT 都要多一次索引查找判断冲突写频繁和批量导入的场景要特别注意。外键这块我得说点不一样的观点。教科书上强调外键保证引用完整性听起来很美但生产环境我几乎不建外键原因很简单外键会让每次 INSERT/UPDATE 都去检查父表这在低并发下没感觉一旦并发上来锁竞争和死锁概率明显上升而且分库分表后外键直接失效。大厂普遍的做法是在应用层保证数据关系数据库层面只建普通索引。这不是说外键一无是处在后台管理系统、内部工具这类低并发、强一致要求的场景用外键确实能省事但线上高并发核心链路我劝你慎重。CHECK 约束是另一个容易忽视的点。MySQL 8.0.16 之前CHECK 约束只解析不执行写了等于没写。从 8.0.16 开始才真正生效。所以如果你的库是 5.7别指望 CHECK 能拦截非法值老老实实用应用层校验或者用 ENUM、TINYINT 加注释来限制取值范围。ENUM 类型也顺带说一句它看着能限制枚举值但修改枚举值需要 ALTER TABLE扩展性很差而且 ENUM 排序走的是定义顺序不是字典序容易出幺蛾子。状态字段我更推荐 TINYINT 注释文档或者 MySQL 8.0 的 CHECK。一个典型的建表语句大概长这样你把规则看清楚后面照着写就行CREATE TABLE t_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, order_no VARCHAR(32) NOT NULL COMMENT 业务订单号, user_id BIGINT UNSIGNED NOT NULL COMMENT 用户ID, status TINYINT NOT NULL DEFAULT 0 COMMENT 状态:0创建,1支付,2取消, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 总金额, remark VARCHAR(255) NOT NULL DEFAULT COMMENT 备注, 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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT订单表;注意几个点表名和字段名我习惯用反引号包起来防止和保留字冲突引擎显式指定 InnoDB别依赖默认值字符集统一 utf8mb4排序规则用 8.0 的默认utf8mb4_0900_ai_ci。utf8mb4 是必须的传统的 utf8 在 MySQL 里是 utf8mb3存不了 emoji 和部分生僻字坑过无数人。2. 表结构变更ALTER TABLE 的正确打开方式需求总是会变的表结构也一样。你设计得再周全业务一迭代加字段、改类型、删字段都是家常便饭。ALTER TABLE 是 DDL 操作但它在 MySQL 里的执行机制和普通认知不太一样——它不是一个瞬间完成的逻辑操作涉及到数据文件的拷贝和重建所以大表上执行 ALTER TABLE 可能把库搞挂。2.1 ALTER TABLE 基本操作先过一遍日常最高频的几个改表操作。加字段用 ADD COLUMNALTER TABLE t_order ADD COLUMN pay_time DATETIME NULL COMMENT 支付时间;注意我加了 NULL因为老数据没有支付时间你需要给一个默认值或者允许 NULL。如果表里有数据新增 NOT NULL 字段且不给默认值执行会失败给默认值的话8.0 里走 INSTANT 算法很快5.7 里可能还是全表重建。删字段用 DROP COLUMNALTER TABLE t_order DROP COLUMN remark;修改字段类型或默认值用 MODIFY COLUMNALTER TABLE t_order MODIFY COLUMN status TINYINT NOT NULL DEFAULT 1 COMMENT 状态:1支付,2取消;MODIFY 和 CHANGE 的区别是MODIFY 改类型或默认值但保留字段名CHANGE 可以同时改字段名。改字段名用 CHANGE COLUMNALTER TABLE t_order CHANGE COLUMN pay_time paid_at DATETIME NULL COMMENT 支付时间;重命名表用 RENAME TABLERENAME TABLE t_order TO t_user_order;这些语法本身不难难的是执行时机和影响预估。每次 ALTER TABLE 之前我都建议先看一眼表有多大、有没有长事务、是不是高峰期。一个 5000 万行的表ALTER TABLE 执行半小时是正常的如果这时候赶上业务高峰轻则锁等待重则把连接池打爆。2.2 在线 DDL 与效率优化MySQL 8.0 的在线 DDL 比 5.7 进步了一大截但很多人没搞清楚它的行为。ALTER TABLE 可以带 ALGORITHM 和 LOCK 子句用来声明允许用什么算法和什么锁级别ALGORITHMINSTANT8.0 新增的“秒加”算法只修改数据字典不重建表适用于加字段、加默认值等操作。ALGORITHMINPLACE直接在原表上操作不拷贝全表数据但可能还是需要重建索引或聚簇表。ALGORITHMCOPY最原始的方式拷贝整张表期间需要锁表5.7 时代很多操作默认就是 COPY慢得吓人。LOCKNONE不锁表允许并发 DML但部分操作不支持。LOCKSHARED允许读禁止写。LOCKEXCLUSIVE读写都禁止。实际操作中你不需要每次手动指定这两个子句MySQL 会按最优方式选择。但了解它对你的价值在于当你发现一条 ALTER TABLE 慢得要命时你能判断它是不是走了 COPY能不能改成更快的方式。比如 5.7 里给大表加字段如果走到 COPY你可以考虑用 pt-online-schema-change 这类工具它通过创建影子表、拷贝数据、切表名的方式做到在线变更。pt-osc 的原理不复杂先建一个和原表结构一样的新表然后慢慢把原表数据导过去期间用触发器捕获增量变更最后把原表和新表切换。这个工具我实际用过不少次配合 Percona Toolkit 里的 pt-osc线上改大表基本无感但要注意它依赖触发器对原表的写压力有一定放大还是要避开峰值。2.3 改表操作避坑清单改表这事我踩过的坑能列一长串挑最痛的几个说。第一改表前必须备份。别以为 ALTER TABLE 失败了能自动回滚MySQL 8.0 的 DDL 有原子性5.7 及之前 DDL 执行到一半失败的话表可能处于不一致状态恢复起来极其痛苦。稳妥的做法是改之前用 mysqldump 或者先建一张备份表哪怕只是CREATE TABLE t_order_bak LIKE t_order; INSERT INTO t_order_bak SELECT * FROM t_order;关键时候能救命。第二别在业务高峰期跑大表 DDL。你可以用SHOW PROCESSLIST看看当前有没有长事务或者查看information_schema.innodb_trx里有没有未提交的大事务。大事务没结束DDL 可能一直卡在等待 MDL 锁元数据锁上后面所有对这个表的读写都会被堵住一分钟内线上就能雪崩。第三加字段别任性放在中间位置。ALTER TABLE t ADD COLUMN col INT AFTER some_col这种写法看着是为了让字段排列好看但在大表上AFTER 选项可能强制走 COPY 算法8.0 的 INSTANT 也有限制只能在列末尾加。所以线上加字段直接默认加到末尾字段顺序就别讲究了。第四改字段类型要评估长度和溢出。比如 VARCHAR(64) 改成 VARCHAR(128)如果表数据量很大可能触发重建把 INT 改成 BIGINT更是要小心5.7 下基本是 COPY生产环境建议离线或者低峰期执行。改表类型前先查最大长度和分布比如SELECT MAX(LENGTH(col)), COUNT(*) FROM t WHERE col IS NOT NULL避免改完之后超长报错。第五多表操作时注意外键依赖和视图依赖。如果表被视图引用或存在外键约束ALTER TABLE 可能因为其他表的锁而卡住。碰到这种先 DROP 外键再改改完再加回来或者干脆别用外键。3. 索引让表在数据量上来之后还能撑住建表只是第一步真正决定表能不能扛住业务的是索引设计。我一直跟团队里的新人说如果你只会在主键上建索引那说明你还没入数据库的门。索引不是越多越好也不是建了就一定能被用到这里面的门道恰恰是区分“能用”和“会用”的分水岭。3.1 索引类型怎么选InnoDB 里最常见的索引底层是 B 树按实现又分为主键索引、唯一索引、普通索引和全文索引。它们的区别我一句话概括主键索引的叶子节点存整行数据叫聚簇索引其他索引的叶子节点存主键值叫二级索引查的时候可能要回表。理解这个你才能解释“为什么索引字段越短越好”——二级索引叶子存的是主键值如果主键是 BIGINT每个二级索引条目都带着 8 字节主键主键越长索引自然越大。普通索引的创建很简单ALTER TABLE t_order ADD INDEX idx_user_id (user_id); ALTER TABLE t_order ADD UNIQUE INDEX uk_order_no (order_no);创建索引时还可以建前缀索引就是只对字符串的前 N 个字符建索引比如INDEX idx_remark (remark(20))。前缀索引能显著减小索引体积但缺点是不能做覆盖索引扫描排序也可能出问题。前缀长度怎么定可以用选择性来算比如SELECT COUNT(DISTINCT LEFT(remark,10)) / COUNT(*) FROM t_order选择性接近 1 才效果好。一般不是大文本字段没必要前缀。索引类型除了 B 树还有 HASH 索引和 FULLTEXT 索引。InnoDB 的 HASH 索引是自适应的你没法手动指定MEMORY 引擎支持显式 HASH 索引但生产环境很少用。FULLTEXT 全文索引适合中文分词搜索不过实际体验一般真正做全文检索我更推荐 ESMySQL 全文索引在中文上的分词能力、搜索语法和扩展性都不够打。3.2 联合索引、最左前缀与覆盖索引联合索引是面试高频点也是日常开发最容易用错的地方。比如INDEX idx_user_status (user_id, status)它本质上是一个按 user_id 排序、相同 user_id 内再按 status 排序的二级索引。所以查询条件里只有 status 而没有 user_id 时这个索引用不上因为索引的第一列就没有参与匹配——这就是最左前缀原则。举个例子WHERE status 1走不了刚才那个联合索引但WHERE user_id 100 AND status 1可以走WHERE user_id 100也可以走。联合索引的设计原则是查询频率最高、区分度最高的字段放最左边。不过也要小心过度设计联合索引会带来写放大每次 INSERT 都要维护多个索引树所以索引数量控制在 5 个以内比较稳妥。覆盖索引是另一个能大幅提升查询性能的东西。当二级索引的叶子节点已经包含你需要的所有列时就不需要回表了这叫覆盖索引。比如表上有INDEX idx_user_id (user_id)你执行SELECT id, user_id FROM t_order WHERE user_id 100id 是主键索引叶子节点自带主键值所以这个查询直接扫索引就能返回不用碰数据行。日常优化慢查询时把 SELECT 的字段收窄到索引覆盖的范围内往往立竿见影。索引下推Index Condition PushdownICP也是 8.0 和 5.6 就有的优化。没有 ICP 时MySQL 会用索引把可能的行捞出来再回表过滤有了 ICP部分 WHERE 条件能直接在索引层判断。比如联合索引 (a,b)查询条件是a1 AND b LIKE x%b 的最左匹配不完整但 b 的过滤条件可以下推到索引层减少回表次数。这个优化是自动的但前提是你的 MySQL 版本和索引设计合适。3.3 用 EXPLAIN 看执行计划索引建得好不好别靠猜用 EXPLAIN。这是我看一个 SQL 是否高效的第一手段也是最该养成的习惯。用法极其简单EXPLAIN SELECT * FROM t_order WHERE user_id 100 AND status 1;重点看几个列。type 列表示访问类型从好到差大致是const / eq_ref / ref / range / index / ALL。type 为 ALL 说明全表扫描数据量一大基本完蛋index 说明扫描了整棵索引树比 ALL 好但也没好到哪去range 说明走了索引范围扫描比如 BETWEEN 和 可用ref 是非唯一索引等值匹配eq_ref 是唯一索引等值匹配JOIN 时常见const 是主键或唯一索引等值匹配理论最快。key 列表示实际用的索引rows 列是估计扫描行数Extra 列里要特别注意几个关键词Using filesort 意味着排序没走索引要额外排序大结果集下性能堪忧Using temporary 意味着用了临时表常见于 GROUP BY 和 DISTINCTUsing index 是覆盖索引的好信号值得高兴。还有 Using where 通常代表索引没有完全过滤所有条件。看到一个慢查询我的排查顺序是先 EXPLAIN看 type 是不是 ALL有没有用上 keyExtra 里有没有 filesort然后看 rows 和实际返回行数差多少再尝试调整索引或改写 SQL。比如 ORDER BY 想走索引要让 ORDER BY 的字段和索引顺序一致且排序方向一致where 条件里对索引字段做函数运算或隐式类型转换索引就废了这一点下面常见问题里还会细说。4. 表里的数据增删改事务、锁与隔离级别表结构搭好了索引也建好了接下来就是日常的 INSERT、UPDATE、DELETE。这个阶段最容易出问题的反而不是 SQL 语法而是并发环境下的数据一致性、锁和事务。很多时候线上出故障不是你 SQL 写错了而是多个事务互相干扰把表锁住或者搞出死锁。4.1 INSERT / UPDATE / DELETEINSERT 有几个变体值得分清。普通 INSERT 在遇到唯一键冲突时直接报错INSERT IGNORE 会忽略冲突但不返回影响行数REPLACE INTO 是“删了再插”注意它会把整行替换掉不只是冲突字段ON DUPLICATE KEY UPDATE 则是“存在就更新不存在就插入”这是最常用也最符合业务直觉的写法。比如订单表要记录用户积分变动防止重复插入就用INSERT INTO t_user_points (user_id, points) VALUES (100, 50) ON DUPLICATE KEY UPDATE points points 50;这里前提是 user_id 有唯一索引。用之前一定要确认业务上“更新”和“插入”的语义别把不该覆盖的字段抹了。UPDATE 和 DELETE 最大的坑就是忘记 WHERE。有一次我亲眼见同事在测试环境执行UPDATE t_user SET status 0直接把全表状态清了还好是测试环境。后来我给自己定了个规矩生产环境账号开启 sql_safe_updates这玩意能禁止不带 WHERE 或 WHERE 不带索引的 UPDATE/DELETE是保命符。另一个习惯是DELETE 大批量数据时别一次性删完可以分批次DELETE FROM t_log WHERE created_at 2024-01-01 LIMIT 1000;循环执行每次递 500 毫秒避免长事务、锁范围过大和 binlog 堆积。删除大表数据更彻底的做法是直接 DROP 表或者 TRUNCATE TABLETRUNCATE 是 DDL 不是 DML速度极快但它会重置自增计数器。如果只是清理过期数据且想保留表结构可以考虑按日期分表分区直接 DROP 分区比 DELETE 快几个量级。4.2 事务与隔离级别直觉理解事务的 ACID 特性是基础但真正干活的时候你更关心的是隔离级别。MySQL InnoDB 默认隔离级别是 REPEATABLE READ可重复读这也是它和 Oracle 默认 READ COMMITTED 的主要差别。四个隔离级别从松到严READ UNCOMMITTED能读到别人没提交的数据脏读基本没人用。READ COMMITTED只能读到已提交的数据解决脏读但有不可重复读问题同一个事务里两次查询同一行结果可能不同。REPEATABLE READ同一个事务里多次读同一行结果一致解决不可重复读。MySQL 的 InnoDB 在这个级别下还借助间隙锁解决了大部分幻读问题。SERIALIZABLE所有事务串行执行性能最差基本不用。日常用默认的 REPEATABLE READ 就够了。但要注意事务越小越好一个事务里别塞太多操作。我见过有人把几百条 INSERT 放在一个事务里事务动辄执行几十秒这样做的恶果是持有的行锁和间隙锁范围巨大其他事务全被堵住binlog 写入压力大回滚日志膨胀。事务应该像“用完即走”的临时工开、干、提交别拖泥带水。提交后用不用手动 ROLLBACK看情况如果中途发生异常应该回滚保证数据一致性。4.3 锁与死锁问题的排查InnoDB 的锁按粒度分有表锁和行锁。表锁主要是 DDL 引起的 MDL 锁以及显式 LOCK TABLE行锁才是日常主要对象。行锁进一步分共享锁S读锁和排他锁X写锁SELECT 默认不加行锁但SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE会加锁。UPDATE、DELETE、INSERT 天然需要排他锁。更隐蔽的是间隙锁和临键锁Next-Key Lock。在 REPEATABLE READ 级别下InnoDB 为了防止幻读会在索引记录之间的间隙上加锁。这意味着即使你只 UPDATE 一行也可能锁住一个范围导致其他会话在这个范围内插不进新数据。这是死锁的根源之一。死锁出现时MySQL 会自动检测并回滚其中一个事务报错Deadlock found when trying to get lock; try restarting transaction。应用层遇到死锁的正确姿势是捕获异常并重试而不是让它直接暴露给用户。排查锁等待我常用的 SQL 是-- 看当前有哪些事务在跑 SELECT * FROM information_schema.innodb_trx\G -- 看事务在等什么锁 SELECT * FROM information_schema.innodb_lock_waits\G如果发现某个事务长时间持有锁不释放先查它是什么 SQL、跑了多久再用 KILL 把阻塞源头的事务干掉。有一次线上订单接口突然超时查下来是一条 UPDATE 没有 WHERE把整表行都锁住了后面所有对该表的操作排队等锁最后我只能 kill 掉那个事务才恢复。这种事故很常见根子就是 DML 语句太粗心。5. 我碰过的表相关坑问题复盘与排查思路下面这些坑都是我在真实项目里踩过的有些让我加了半夜班有些让我被 DBA 拉去开会。我把它们整理成一个速查表你可以直接对照着自己项目排查一遍。5.1 隐式转换导致索引失效这是最常见的“索引建了但不生效”的原因之一。表里的 user_id 字段是 VARCHAR(32)但你写 SQL 时传了一个数字 100SELECT * FROM t_user WHERE user_id 100;MySQL 会把字段和常量都转成浮点数比较相当于对字段做了函数操作索引就废了。查一下执行计划type 往往是 ALL。解决办法很简单SQL 里传字符串WHERE user_id 100或者干脆把字段类型改成 BIGINT。怎么发现把真实 SQL 拿出来 EXPLAIN 一遍凡是 key 为 NULL 的先看类型匹配。5.2 大表删除空间不释放DELETE FROM t_log WHERE created_at 2024-01-01; 执行完你以为磁盘空间会立刻释放并不会。InnoDB 是把数据标记删除物理文件大小不变除非重组表。所以你会看到表空间文件一直占着几个 GB以为是没删干净。想彻底释放可以用OPTIMIZE TABLE t_log;或者更简单粗暴的ALTER TABLE t_log ENGINEInnoDB;这两种方式都会重建表大表执行时间长必须在低峰期跑。更优雅的方案是日志类表从一开始就按月分区过期直接 DROP PARTITION秒级释放空间还不影响线上查询。这也是我强烈建议日志表、流水表做分区的原因。5.3 自增主键断层很多人发现自增主键不连续就慌觉得是不是数据丢了。其实在 InnoDB 里事务回滚、INSERT 失败、批量插入新版本、手动指定 ID 都会导致断层这是正常现象不是 bug。自增主键只保证唯一性不保证连续性业务上不要把主键的连续性当语义。唯一要注意的是 8.0 之前的版本自增计数器是存在内存里的重启后可能复用之前分配过的 ID导致主键冲突8.0 把计数器的持久化修好了这个问题基本绝迹。5.4 字符集与排序规则不一致这个坑藏在 JOIN 里。表 A 的 user_name 是 utf8mb4_unicode_ci表 B 的 user_name 是 utf8mb4_0900_ai_ci两张表 JOIN 时MySQL 无法直接比较两个不同排序规则的字段会先把其中一边做转换导致索引失效甚至直接报错Illegal mix of collations。我的习惯是建表语句里全都显式写死CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci字符串字段不要在 JOIN 条件里跨库跨表搞不同排序规则。如果真要兼容可以在 JOIN 时用 COLLATE 指定SELECT * FROM a JOIN b ON a.name b.name COLLATE utf8mb4_0900_ai_ci;但更推荐在设计阶段统一标准。5.5 COUNT(*) 慢MyISAM 引擎里 COUNT() 是 O(1)因为引擎维护了总行数。InnoDB 由于支持事务和 MVCC每个事务看到的行数可能不同所以 COUNT() 必须实时扫描统计几百万行的表 COUNT(*) 就会明显变慢。优化方法用EXPLAIN SELECT COUNT(*) FROM t看优化器估算的 rows能满足“大概数量”的需求。业务需要精确计数专门建一张计数表每次 INSERT/DELETE 时用事务同步更新。按条件统计时尽量走二级索引的覆盖扫描比扫主键聚簇索引快。6. 一张业务表从设计到落地的完整小案例讲了这么多理论和坑我拿一张真实的订单表来完整走一遍流程从需求分析、字段设计到索引规划、上线观察你可以照着这个思路迁移到自己的业务里。6.1 需求与字段评估假设我们要设计一张电商订单表。核心字段有订单号、用户 ID、商品快照信息、商品数量、订单金额、支付金额、订单状态、支付时间、发货时间、创建时间、更新时间、备注。这里有几个设计决策订单号是业务强关联的编号必须唯一但不要做主键因为订单号可能带前缀、业务规则变化做主键不稳。单独建唯一索引即可。商品快照不要直接落在订单表里如果商品信息会变订单需要保存下单时的商品名、单价、图片可以拆一张订单明细表。订单主表只存总金额和数量。金额用 DECIMAL(12,2)别用 DOUBLE。状态用 TINYINT注释写清楚每个值的含义。创建和更新时间用默认值自动维护。最终建表语句类似第一节里给的那个字段可以根据实际补齐但原则是不变能拆的拆、能定长的定长、能 NOT NULL 的绝对不 NULL。6.2 容量估算与索引规划索引规划之前先估算数据量。假设目标是支撑 1000 万订单每行订单主表估算约 200 字节那么主表数据量约 2GB加上索引假设二级索引两个每个索引大小约数据量的 0.2~0.3 倍整体不到 3GB单库单表撑住没问题。如果预估 1 亿订单那就要考虑按用户 ID 哈希分表或者按时间分区。索引上订单状态这个字段区分度极低只有几个值单独建索引几乎没用查询基本都是“某个用户的最新订单”或者“某时间段的订单”所以联合索引更合理ALTER TABLE t_order ADD INDEX idx_user_created (user_id, created_at); ALTER TABLE t_order ADD INDEX idx_created_status (created_at, status);第一个索引覆盖“用户查自己订单”的场景第二个索引覆盖“运营按时间段查指定状态订单”的场景。如果经常用订单号查唯一索引已经有了。注意没必要再单独给 user_id 建单列索引联合索引的左前缀已经能覆盖。6.3 上线后的观察点表上线不是设计完就完事我至少会观察两周。第一看慢查询日志把执行时间超过 100ms 的 SQL 捞出来逐个 EXPLAIN确认有没有全表扫描的漏网之鱼。第二看表的状态SHOW TABLE STATUS LIKE t_order;重点看行数、数据长度和索引长度判断实际增长是否和估算一致。第三看锁等待和死锁情况日志里如果频繁出现死锁说明索引设计或事务边界有问题趁数据量小赶紧改。第四看索引使用情况MySQL 里可以查performance_schema.table_io_waits_summary_by_index_usage那些从来没有被用过的索引果断删掉别为了“以防万一”留着当写负担。我个人实际做表设计时还有一个习惯所有核心表的字段注释必须写清楚枚举值取值范围用注释或单独文档列出来建表 SQL 提交到 Git 仓库里做版本管理。表结构变更和代码变更一样要 review不要直接在生产库上手改。这样哪怕过了一年新同事接手看建表 SQL 就能还原业务逻辑比啃代码快得多。MySQL 表和人的关系就是这样——你前期认真设计它它后期就少给你添乱你前期图省事后面就得用无数个加班来还。所以每次建表前多花十分钟把字段类型、约束、索引想清楚这笔账怎么算都划算。