最近帮朋友排查一个线上问题他那个业务表才跑了几个月数据量刚过百万查询就慢得离谱。我上去一看表结构稀烂该用数字的用了字符串时间字段没用索引连主键都是带业务含义的字段改起来相当被动。这件事再次印证了一个老生常谈的说法MySQL表的基本操作看着简单但建表时的一个小选择可能决定你半年后是睡个好觉还是大半夜爬起来看慢查询日志。这篇文章不打算讲太玄的东西就聚焦在“表”本身——从设计思路到建表语法从查看结构到修改字段从删除表到常见踩坑全部是我实际干活时反复用到的内容。适合刚接触MySQL的新手系统过一遍也适合写了一阵子SQL但没认真抠过表结构的同学查漏补缺。我尽量把“为什么这么做”也讲清楚而不是只丢给你一条能跑的SQL。1. 开工之前先搞清楚一张表是怎么设计的1.1 字段类型选不对后期哭都来不及很多人建表特别随意所有文本一律varchar(255)所有数字一律int时间一律varchar。这种图省事的做法等到数据量上来之后每一处都是坑。整数字段用int还是bigint要看业务上限。int最大能存21亿多一般业务主键、状态码、计数都够用但如果存的是雪花算法生成的ID、或者未来可能破亿的流水ID直接上bigint。别想着先用int顶着后面再改改字段类型在数据量大的表上是灾难级的操作。小数金额千万不要用float和double二进制浮点天生有精度问题。早期做电商系统的人踩过无数遍这个坑两个float相加结果出来一串9.999999。正确做法是用decimal金额、利率、百分比这类精确数值都归它管。decimal(10,2)表示总长度10位、小数2位最大能存到九千多万如果你做的是大额交易场景把整数位放宽到16甚至18。日期时间类型分三类date只存日期datetime存日期时间timestamp也存日期时间但有2038年问题。很多新人在这个环节纠结“该用datetime还是timestamp”我个人的习惯是业务时间统一用datetime语义明确不受时区影响只有需要跟随服务器时区自动换算的字段比如记录用户最后活跃时间才用timestamp。最忌讳的是拿varchar存时间“2024-01-15 10:30:00”这种字符串比datetime多占空间还不能用日期函数直接计算排序也不是按时间语义排的。1.2 主键、唯一键和普通索引别再傻傻分不清主键是表的灵魂InnoDB引擎下数据本身就是按主键组织的。设计主键时第一原则是“与业务无关”自增id或者应用层生成的分布式id都行但别用身份证号、手机号这种业务字段。业务字段将来一旦有变动规则你要改的就不只是这行数据还有所有引用它的外键关联。主键最好还是单列避免复合主键。复合主键在InnoDB里会引起二级索引存储冗余每张二级索引都会完整复制整个主键列主键列越多索引占用的空间越大写入时开销也越高。我曾经接手过一个老系统用三列联合做主键光索引体积就比数据本身还大后来花了很大力气才拆掉。唯一键和普通索引的区别要分清。唯一键约束的是“这一列不能重复”普通索引只加速查询不做限制。业务上像用户表的手机号、订单表的订单号都该加唯一索引这既是性能优化也是数据完整性兜底比用代码判断“是否已存在”靠谱得多。普通索引则是给高频查询的字段加的但加索引不是越多越好每次INSERT/UPDATE都要维护索引索引多了写入自然慢。1.3 存储引擎和字符集开局就要定好MySQL 5.7之后默认InnoDB基本不用纠结选不选直接InnoDB。InnoDB支持事务、行级锁、崩溃恢复这是正经业务系统的基本盘。MyISAM虽然查询快、索引紧凑但不支持事务表锁并发能力差除了某些只读报表场景我基本不用。字符集必须用utf8mb4不是utf8。MySQL里的utf8是utf8mb3只支持到三个字节的编码emoji表情和一些生僻字四字节字符存进去就报错或变问号。utf8mb4是utf8的超集兼容所有内容。排序规则一般选utf8mb4_general_ci就够了比较宽松不区分大小写如果你有严格的排序需求再去研究utf8mb4_unicode_ci和utf8mb4_0900_ai_ci的差别普通项目用不上这么细。这点要注意建库时指定了utf8mb4不代表建表时自动继承。MySQL的字符集继承链是“服务器数据库表列”每一层都可以覆盖。最稳妥的做法是建表语句里显式写上charsetutf8mb4不要赌默认值。2. 建表实操一条CREATE TABLE背后的门道2.1 一个能直接落地的建表范例先看一个典型的用户表示例这表结构是我在多个项目里反复调整后觉得比较顺手的版本CREATE TABLE user_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(64) NOT NULL COMMENT 用户名, phone VARCHAR(20) DEFAULT NULL COMMENT 手机号, email VARCHAR(128) DEFAULT NULL COMMENT 邮箱, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常 2禁用 3注销, 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_username (username), KEY idx_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户信息表;这条语句我拆开讲几句。id用BIGINT UNSIGNED是为了多存一倍的整数配合自增主键用非常省心。username定成64是因为用户名通常有长度限制别一上来就varchar(255)长度越长索引占的空间越大。status用TINYINT因为状态枚举就那么三五个用int纯粹浪费。created_at和updated_at用DEFAULT CURRENT_TIMESTAMP插入时自动填时间更新时ON UPDATE自动刷新省掉应用层手动传时间戳的麻烦。总结一下这里的关键决策整数按需选类型变长字符串长度卡紧上限枚举状态用最小整数类型时间交给数据库自动维护。这套组合拳打下来单行数据体积控制住了索引大小和查询性能自然就好。2.2 列属性细节NULL、DEFAULT和COMMENT建表时最容易被忽视的是NULL约束。很多字段业务上根本不允许为空但建表时偷懒没写NOT NULL结果应用层传了个空值进来逻辑判断全乱。设计原则是字段要么NOT NULL并给一个合理的DEFAULT值要么允许NULL但应用层保证不传NULL。我的习惯是能NOT NULL就NOT NULL配合DEFAULT可以把业务规则直接下沉到数据库层少写一堆防御代码。DEFAULT的使用有几个注意点。MySQL 8.0支持表达式默认值比如DEFAULT (UUID())但5.7之前只支持常量跨版本迁移时要注意兼容性。日期类型用DEFAULT CURRENT_TIMESTAMP很顺手但是一张表里多个日期字段都用它时只有第一个能自动初始化后面的需要显式赋值这个细节容易踩。COMMENT注释建议每条字段都写。写注释不是为了应付规范而是半年后你自己回来看表结构时不至于对着一个字段名猜半天。我见过太多表字段名叫flag你说它是状态、标记还是类型根本分不清。有注释的表结构就是活文档配合SHOW FULL COLUMNS可以直接导出说明。2.3 索引该怎么建一次说透索引的建立不是拍脑袋核心依据是查询条件。如果一条WHERE子句高频出现phone字段那就给phone建索引如果排序经常用created_at也可以考虑在它上面建索引。但建立索引要权衡代价表越大、写入越频繁索引的维护成本越明显。前缀索引是个实用技巧。比如邮箱字段大部分查询是“根据邮箱精确匹配”但如果你的查询总是带着域名后缀xxx.com那没必要给整个邮箱建索引。可以建KEY idx_email (email(20))只索引前20个字符索引体积大大减小。缺点是不能再用于覆盖索引优化具体要不要用看查询模式。联合索引要遵守“最左前缀”原则。假设建了(a, b, c)联合索引实际上生效的是a、ab、abc这三种组合直接查b或者bc是走不了这个索引的。设计联合索引时把区分度高的字段放左边把等值查询的字段放前面范围查询的字段放后面这能让索引发挥最大价值。3. 表结构的日常维护查看、修改和调整3.1 查看表结构别再用DESC一条道走到黑初学者最熟悉的查看命令是DESC它简洁直观能列出字段名、类型、是否为空、默认值和额外信息DESC user_info;但DESC看不到索引、字符集、注释这些关键信息。我排查线上问题时更常用SHOW CREATE TABLE user_info; SHOW FULL COLUMNS FROM user_info; SHOW INDEX FROM user_info;SHOW CREATE TABLE能直接看到完整的建表语句包括字符集、引擎、索引、注释是做表结构对比和迁移时最靠谱的依据。它的输出格式可以直接复制出来改成新表非常省事。SHOW FULL COLUMNS在DESC的基础上多了字符集和注释两列适合审查字段设计的规范性。SHOW INDEX输出索引名、列名、唯一性、基数等判断索引是否真正被用上时很关键。查看一条SQL有没有走索引最终要落到EXPLAIN上EXPLAIN SELECT * FROM user_info WHERE username abc;重点关注type列最好是ref、eq_ref最差是ALL全表扫描和possible_keys、key这两列是否一致。如果possible_keys里有索引但key里是NULL说明优化器判断走索引还不如全表扫通常是数据量小或者选择性不够理想。3.2 ALTER TABLE的常见操作改错了很伤修改表结构用ALTER TABLE项目迭代时绕不开。常见操作我列一下-- 新增字段 ALTER TABLE user_info ADD COLUMN nickname VARCHAR(64) DEFAULT NULL COMMENT 昵称 AFTER username; -- 修改字段类型 ALTER TABLE user_info MODIFY COLUMN phone VARCHAR(20) DEFAULT NULL COMMENT 手机号; -- 重命名字段 ALTER TABLE user_info CHANGE COLUMN phone mobile VARCHAR(20) DEFAULT NULL COMMENT 手机号; -- 删除字段 ALTER TABLE user_info DROP COLUMN email; -- 新增索引 ALTER TABLE user_info ADD KEY idx_email (email); -- 删除索引 ALTER TABLE user_info DROP INDEX idx_email;ADD COLUMN和MODIFY COLUMN有一个老生常谈的坑在线DDL并不完全是“在线”。5.6之前ALTER TABLE会锁表5.7和8.0对大部分操作做了优化但大表上执行MODIFY COLUMN修改字段类型依然可能长时间持有元数据锁拖垮业务写入。我的习惯是生产环境的大表变更选择低峰期执行或者用工具如开源社区的改表工具做成批量小事务执行降低锁时长。CHANGE和MODIFY的区别要记牢CHANGE能同时改字段名和定义MODIFY只能改定义。只改类型、默认值就用MODIFY改了名就必须CHANGE。删字段也是个严肃操作。删除后该列的索引也会被删除如果应用层还有代码引用这个字段SQL直接报错。删字段之前先全局搜一遍代码引用点确认没人在用再动手。3.3 重命名表和移动表别忽略外键引用重命名表用RENAME TABLERENAME TABLE user_info TO user_profile;这个操作会直接修改表名InnoDB下是原子操作速度很快。但如果有外键引用这张表重命名后外键会失效或者报错需要同步更新。虽然实际项目中我强烈建议少用外键但确实用了外键的表重命名时必须把关联关系一起理清楚。移动表到另一个数据库也走RENAMERENAME TABLE db1.user_info TO db2.user_profile;前提是两个库在同一实例上。这个操作会带着数据和索引一起迁移不用导出导入堪称同实例迁移的神器。跨实例则只能用mysqldump或传输表空间的方式后者操作门槛高日常备份恢复用dump就够了。4. 删除和清空DROP、TRUNCATE、DELETE的恩怨情仇4.1 三者的区别一张表说清很多新手不知道DROP、TRUNCATE、DELETE到底怎么选这里直接做对比操作删除内容结构保留可回滚自增重置性能DELETE按条件删除行保留事务内可回滚不重置慢逐行删TRUNCATE清空全部行保留不可回滚重置快整体释放DROP删除整张表删除不可回滚表都没了快DELETE是DML逐行删除并记录binlog可以在事务里ROLLBACK。数据量大了之后DELETE会非常慢因为每删一行都要维护索引和记录日志。全表删除几百万行数据用DELETE能跑几分钟以上。TRUNCATE是DDL直接重建表结构不逐行记录日志所以快很多。它会把自增ID重置为1这个特性很重要如果业务上要求ID不能复用千万别用TRUNCATE清表否则新插入的第一行数据ID又是1。另外TRUNCATE在部分数据库里不能在有外键引用的表上执行MySQL里会报错。DROP是物理删除表结构连同数据一起消失。执行之前必须想清楚没备份的话删了就真的没了。4.2 生产环境删表的正确姿势生产环境删表我见过不少翻车案例这里总结几条安全规则。第一确认备份。无论DROP还是TRUNCATE执行前先确认有可用备份最简单的是mysqldump把表导出来。即使你只是想清理测试数据也要养成备份习惯。第二确认关联。这张表有没有被其他表引用应用层有没有定时任务在写这张表计划任务和慢查询日志里能不能看到针对它的访问先查清再动手。实在不确定就先RENAME成user_info_bak_20240115之类的名字观察一两天线上无异常再DROP。这套“先改名、后观察、再删除”的操作成本极低安全性极高。第三DROP一张大表本身也可能引发主从延迟。因为Master上删表很快但Binlog传到Slave后Slave执行同样的DROP时如果IO压力大会产生延迟。大表直接DROP还会引发缓冲池抖动更稳的做法是先TRUNCATE数据或分批删除再DROP表结构。线上做过一次几百GB的大表删除直接DROP导致主库IO飙升后来学乖了先用工具按主键批次删除再DROP平滑得多。4.3 清空表数据但想保留ID连续怎么办有时候测试环境需要清空数据但希望自增ID继续从上次的位置往下走不要重置。TRUNCATE做不到DELETE可以做到但DELETE全表太慢。我的做法是DELETE配合ALTER TABLE ... AUTO_INCREMENT 0手动调整或者干脆从业务层面绕过先备份ID和必要字段然后大规模DELETE最后用ALTER TABLE把自增值调回去。不过在业务表上真的建议让ID留坑自增ID中间缺几段根本不影响正确性纠结ID连续性属于给自己找事。5. 表操作常见问题与排查经验5.1 字符集乱码和插入失败别老怪代码插入中文变问号或者报“Incorrect string value”九成是字符集问题。排查思路从下往上先看表的字符集SHOW CREATE TABLE再看连接的字符集SHOW VARIABLES LIKE character_set_connection最后看JDBC连接串或者客户端配置是否显式指定utf8mb4。最常见的坑是表和库都是utf8mb4但连接层用了latin1数据写入时被转码搞坏了。客户端连接串里加上characterEncodingutf8Java侧或者连接后执行SET NAMES utf8mb4能解决大部分乱码问题。已经乱码的数据想修复非常麻烦通常只能重新录入所以预防比修复重要得多。5.2 自增主键用完了怎么办int类型的主键最大值是21亿多如果不小心设计成int又没加UNSIGNED上限只有约21亿。流量大的业务真的可能在某个深夜把它跑满插入时报主键冲突整个写入链路全断。遇到这种情况第一步是用ALTER TABLE ... MODIFY COLUMN id BIGINT把主键升级为bigint期间表会被锁住业务会短时阻塞但这是最快恢复的方法。更被动的情况是数据量巨大升级耗时太长那只能考虑建新表迁移数据。真正稳妥的预防措施从第一天起就让主键用BIGINT反正多几个字节几乎不占什么空间。5.3 修改表结构导致锁表线上服务卡死我在一个小型电商项目上遇到过订单表几千万行下午三点运营提交了一个“加个字段”的工单研发直接在库里跑了ALTER TABLE结果表锁了两个多小时支付回调全部超时最后是业务方电话连环CALL才知道出事了。这个问题的根因是对在线DDL的错觉。虽然MySQL 5.6以后支持Online DDL但“在线”和“无锁”是两回事对于修改字段类型、重组索引这类操作它依然需要短暂排他锁。正确处理方式大表变更务必走低峰期变更前评估数据量用pt-osc这类工具在后台以chunk方式拷贝数据并同步binlog把锁表时间压缩到秒级变更完了立刻用SHOW PROCESSLIST检查有无残留的Waiting for table metadata lock。5.4 字段名跟关键字撞车SQL跑不动建表字段用到了order、group、desc这些保留字SQL直接报语法错误。解决办法是字段名加反引号或者干脆避免用这些关键词做字段名。我的习惯是设计字段时就避开保留字比如订单号用order_no而不是order描述用description而不是desc。这不是什么高深问题但新人遇到时往往一脸懵。5.5 一张表该不该垂直拆什么时候拆“表设计得好不用拆”是理想情况现实里谁也预测不准业务能涨多快。判断要不要拆看两个指标单表数据量以及单行宽度。数据量超过千万级且还在快速增长时要开始考虑分库分表了单行包含大量TEXT/BLOB字段查询时会拖慢其他字段的读取速度这种场景适合垂直拆分把大字段单独拆到扩展表。不过我的建议是业务早期千万别主动拆分。拆表的代价非常高代码里所有JOIN都要重写事务跨度变大复杂度指数上升。我的保守做法是数据量预计三年内破亿才考虑分表否则优先优化索引和SQL。很多时候一张烂查询比一张大表更致命。6. 几个实操小习惯关键时刻能救命最后分享几个我在实际工作中沉淀下来的小习惯不一定写进任何教程但确实帮我避开过很多问题。习惯一是每次建表都保存一份DDL脚本到版本库。这样即使表结构被谁误改了也能立刻通过对比脚本找出差异。配合SHOW CREATE TABLE生成补丁表结构演进历史全程可追溯。习惯二是所有核心业务表都预留created_at和updated_at两个时间字段。这不是流水账而是将来排查数据问题时的关键线索。“这条数据是什么时候写的、最近一次是什么时候更新的”这两个信息能帮你快速定位脏数据产生的时间窗口。习惯三是删除数据之前先SELECT一遍。执行DELETE FROM user_info WHERE status 3之前我永远先跑一句SELECT COUNT(*) FROM user_info WHERE status 3确认影响行数在预期范围内再删。加个LIMIT虽然DELETE不支持但可以先SELECT LIMIT看样例确认删的就是目标数据。习惯四是把EXPLAIN当成常规操作看待。任何一条要上线的慢查询都先EXPLAIN一遍确认走的是索引而不是全表扫。这个习惯持续几周后你对索引的理解会明显提升建表时也会下意识考虑查询模式。MySQL表的基本操作大概就这些。从建表设计到结构变更再到删除清理每一步都不难但每一步都藏着影响线上稳定性的细节。我这几年处理过不少数据库事故回头看根源大多不是SQL写得多烂而是建表那一步埋下了雷。希望这篇内容能让你在建表时多想一步之后少熬几个夜。