尧图网站建设 
很多人学了几年数据库建表还是靠感觉约束看心情加索引全部建在主键上。前阵子给团队做数据库基础培训我把表定义、修改/删除表、索引操作、完整性约束这四块从头到尾重新梳理了一遍发现不少平时写了无数遍的SQL其实很多细节都没真正吃透。就说一个最简单的例子同样一个DROP TABLE为什么有的环境执行后磁盘空间还在涨这背后涉及的是事务日志和存储引擎的机制而不只是删掉一张表那么简单。这篇内容适合正在系统学SQL的人、准备数据库面试的求职者以及工作中天天写SQL但没系统补过基础的开发者。我尽量把概念讲透同时每个知识点都给典型示例你照着敲一遍就能掌握。1. 为什么值得把建表这件事重新捋一遍先说个我观察到的现场很多开发者的SQL水平停留在能跑通阶段面试问主键和唯一键的区别能答上来问外键在什么场景下反而会拖垮性能就开始支支吾吾。更麻烦的是工作里建表不规范字段类型用VARCHAR(255)一把梭该用DECIMAL的用FLOAT等数据量上来之后全是坑。表定义是整个数据库设计的地基。你在一张表上建了错误的约束后面所有引用这张表的SQL都可能被拖下水一个字段的类型选错轻则多占用存储重则导致索引失效。索引操作也一样建索引看似简单建在什么列上、用什么顺序建复合索引、什么时候该用覆盖索引这些决策直接决定查询是走索引还是全表扫描。而完整性约束则是保证数据质量最后一道防线。这四个主题既独立又彼此关联。表定义是骨架完整性约束是规矩索引是加速器修改和删除表则是上线之后日常要面对的维护动作。我在下面按实际操作顺序逐步展开每个部分都带示例和可以照抄的写法。2. 表定义CREATE TABLE时的选型和细节建表这步看着简单就是写几个字段名和类型但真正做过几年数据库维护的人都知道建表决策会跟着你很长时间。字段一旦有了数据改类型的成本就非常高尤其在大表上ALTER TABLE锁表时间可能让业务直接停摆。2.1 列类型不是在选能存什么而是在选效率边界类型选择的核心原则是在满足业务需求的前提下选择最节省存储空间的类型。但最节省很容易被误解成最小实际要结合查询场景。先说整数。INT占4字节范围约21亿绝大多数业务主键和计数场景够用BIGINT占8字节适合可能超过21亿的场景或者分布式ID。我的建议是主键直接上BIGINT别省这4个字节。为什么业务增长很难预测等ID耗尽再迁移主键类型那才是真正的灾难。普通状态字段用TINYINT或SMALLINT就够用INT反而浪费。字符类型是最容易出问题的。CHAR(N)是定长VARCHAR(N)是变长但VARCHAR(N)里的N不是字节数是字符数。这里有个经典误区VARCHAR(10)到底能存几个中文答案是10个字符跟汉字还是英文没关系。但如果你用utf8mb4字符集一个字符最多占4字节所以VARCHAR(10)最多占用40字节索引长度限制要按这个算很多人在这个细节上吃过亏。数值里的DECIMAL和FLOAT也经常被用混。金额、汇率、价格这类需要精确计算的必须用DECIMAL(p,s)其中p是总位数s是小数位数。FLOAT和DOUBLE是浮点存储会有精度误差算钱的时候会出现0.10.20.30000000000000004这种问题。实测下来写DECIMAL(10,2)存金额既安全又直观但要注意DECIMAL的运算比FLOAT慢如果做海量浮点计算又不要求精度才考虑FLOAT。日期时间类型也是重灾区。MySQL里DATE只存日期DATETIME存日期和时间且不受时区影响TIMESTAMP则受时区影响且在2038年会溢出。创建时间、更新时间这类字段我习惯用DATETIME因为TIMESTAMP的范围上限是2038年对某些长周期业务来说不够保险。定义时默认值建议写成DEFAULT CURRENT_TIMESTAMP更新字段加ON UPDATE CURRENT_TIMESTAMP很多版本的ORM也能识别这套写法。2.2 默认值、自增列与命名规范的约定建表时用不用默认值这里有个实际考量尽量把能用数据库解决的规则留在数据库里而不是靠应用层保证。比如状态字段默认值0表示待处理比如创建时间默认当前时间比如逻辑删除标记默认0。这样即使某个新来的同事忘记在INSERT语句里给这些字段赋值数据也不会变成NULL或者错误值。自增列AUTO_INCREMENT也是容易出问题的地方。MySQL里自增列必须是索引列通常配合主键使用。删除过数据之后再插入自增值会继续递增不会回填这是正常现象别奇怪为什么ID不是连续的。真正要小心的是大批量删数据后想重置自增ID用TRUNCATE会重置DELETE不会这个区别在后面的修改删除表部分展开讲。命名规范这事情每家公司有每家的习惯但我自己的经验是统一比优雅更重要。表名用业务模块前缀比如user_account、order_main字段名全程小写加下划线主键统一叫id业务关联字段用xxx_id状态字段叫status时间字段叫created_at/updated_at。这套命名法在ORM、报表工具、数仓同步里都通用面试时提到规范建表用这套说法也很加分。建表的演示语句长这样CREATE TABLE user_account ( id BIGINT AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(32) NOT NULL COMMENT 用户名, password_hash CHAR(64) NOT NULL COMMENT 密码哈希, balance 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_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户账户表;这张表的写法有几个值得学习的地方用户名加了唯一索引保证注册不会重复金额用了DECIMAL(10,2)而不是FLOAT状态字段给了默认值utf8mb4字符集支持四字节表情符号避免Emoji存不进去的问题。2.3 关于临时表与表分区的一点补充面试和实际工作中还会碰到临时表分两种一种是CREATE TEMPORARY TABLE只在当前会话可见断开连接自动消失另一种是普通表加_tmp后缀用来做数据迁移中间表。前者适合存储过程内部的中间结果后者适合大表结构调整前备份数据。表分区则是把一张大表按规则拆成多个物理分区但逻辑上仍是一张表。常见的分区方式有RANGE按范围分、HASH按散列分、LIST按枚举值分。分区的核心收益是查询条件能命中分区时可以只扫描对应分区文件同时大表TRUNCATE PARTITION比逐条DELETE快得多。但分区也有副作用尤其是分区键必须包含在主键/唯一键里这个限制经常让人头疼。我的建议是单表超过千万级再考虑分区千万以下先做好索引。3. 修改和删除表ALTER、TRUNCATE与DROP的本质区别表结构上线后不是一劳永逸的加字段、改类型、删约束这些操作每天都在生产环境发生。这一章节重点聊聊ALTER TABLE的常用操作以及为什么TRUNCATE和DROP和DELETE明明都是删行为差距却这么大。3.1 ALTER TABLE 的常用操作与锁表问题先列一份实用清单每个都是高频操作-- 添加字段 ALTER TABLE user_account ADD COLUMN last_login_at DATETIME NULL COMMENT 最后登录时间; -- 修改字段类型 ALTER TABLE user_account MODIFY COLUMN username VARCHAR(64) NOT NULL COMMENT 用户名; -- 重命名字段 ALTER TABLE user_account CHANGE COLUMN username user_name VARCHAR(32) NOT NULL COMMENT 用户名; -- 删除字段 ALTER TABLE user_account DROP COLUMN last_login_at; -- 添加索引 ALTER TABLE user_account ADD INDEX idx_status (status); -- 删除索引 ALTER TABLE user_account DROP INDEX idx_status;这里有个生产环境最常见的坑在数据量很大的表上执行ALTER TABLE可能锁表很久。MySQL 5.6之前的版本ALTER TABLE大概率需要全表重建耗时跟数据量和IO负载直接挂钩5.6以后有了Online DDL在线DDL部分操作可以一边改一边允许并发DML但像MODIFY COLUMN这种改类型或者CHANGE COLUMN改名字的底层仍可能触发表重建。我的实操建议是生产环境的表结构变更优先在业务低峰期做如果条件允许用专门的工具比如pt-online-schema-changePercona Toolkit来做无锁变更。它的思路是先建一个空表副本然后通过触发器同步增量数据最后原子切换表名。这工具我用了很多年踩坑少但要注意它要求源表必须要有主键否则同步机制会失效。3.2 TRUNCATE、DELETE、DROP的底层区别这张对比表值得背下来操作是否走事务是否逐行删除可否带WHERE是否释放存储空间是否重置自增DELETE是是可以否产生大量日志否TRUNCATE否DDL否直接重建表不可以是是DROP否DDL否整表移除不可以是不适用DELETE是DML每一行删除都写事务日志可以回滚。这也是为什么大表DELETE巨慢的原因——不是逐行删除的速度问题而是每删一行都要记一条日志日志增长非常快。TRUNCATE是DDL本质是直接释放表的数据页并把表结构重建所以速度飞快但它不会逐行触发触发器想找回数据只能靠备份和日志恢复。这里有个重要提醒TRUNCATE和DROP虽然是DDL在MySQL里执行后隐式提交无法通过事务回滚。如果你习惯把删除操作包在事务里做千万别把TRUNCATE放进去它不会跟你客气直接生效。DROP和TRUNCATE还有一个区别TRUNCATE保留表结构DROP连表结构一起删除。所以实际开发中定时清理历史数据的脚本如果只想清空数据保留表结构用TRUNCATE如果整张表废弃了用DROP。3.3 删除表之前必须确认的几件事DROP TABLE这个操作一旦执行成功表和数据基本就没了除非靠定期备份和前一天的binlog去恢复。我处理线上事故的经验里有几个确认点是必须过的确认这张表是否被其他表的外键引用。MySQL里如果存在外键约束DROP父表会直接报错DROP子表倒是可以但后续程序查询父表关联时可能逻辑出错。确认有没有下游任务在读取这张表。数仓同步、报表系统、定时任务经常在你不知道的地方引用数据表表被DROP之后这些任务全部告警。确认删除范围。如果只是想清空部分数据用DELETE配WHERE别用TRUNCATE。TRUNCATE不支持条件删除一执行就是全表清空。我个人的习惯是任何DROP和TRUNCATE操作前先跑一个备份脚本把数据导出到文件哪怕备份完再用不上也比出事时干瞪眼强。4. 索引操作从建索引到索引失效索引操作是数据库进阶绕不开的一关。索引的本质是数据结构MySQL InnoDB默认用的是B树它能让我们从逐行扫描变成按树查找。面试最喜欢问的问题其实就是看你能不能把索引的使用场景讲明白。4.1 聚簇索引与非聚簇索引一张表只能有一个物理顺序先说聚簇索引。InnoDB的表数据本身就是按主键聚簇排列的也就是说主键索引的叶子节点直接存整行数据。这张表的物理顺序和主键顺序一致这就是聚簇索引。一张表只能有一个聚簇索引因为数据不可能同时按两种顺序物理排列。非聚簇索引也叫二级索引叶子节点存的是索引列的值和主键值。走二级索引查询的时候先找到匹配的二级索引条目拿到主键值再回聚簇索引里取整行数据这个过程叫回表。回表次数多查询就慢所以就有了覆盖索引的概念如果查询需要的所有列都包含在二级索引里就不需要回表了。-- 覆盖索引示例只需要id、username两列二级索引里都有就不回表 SELECT id, username FROM user_account WHERE username zhangsan;这条SQL如果只建了uk_username(username)唯一索引那username匹配后能直接拿到id两个字段都在索引里直接覆盖。但如果你SELECT的是balance字段就必须回表拿整行数据了。你问我怎么知道走没走覆盖索引最简单的办法是看执行计划的Extra列如果出现Using index说明走了覆盖索引如果出现Using index condition说明只做了索引下推部分优化仍需回表。4.2 复合索引的列顺序最左前缀原则复合索引联合索引是多个列合建的索引。它遵循最左前缀原则查询条件里如果从索引的最左列开始连续匹配就能用到索引跳过最左列直接查后面的列索引基本失效。举例来说建一个复合索引idx_status_created (status, created_at)-- 走索引条件中包含了最左列status SELECT * FROM user_account WHERE status 0 AND created_at 2024-01-01; -- 走索引只用到了最左列statuscreated_at范围让右边界失效但status能定位 SELECT * FROM user_account WHERE status 0; -- 不走索引跳过了status直接查created_at最左前缀断裂 SELECT * FROM user_account WHERE created_at 2024-01-01;这里有个容易混淆的点为什么只用status能走索引而只用created_at不能因为复合索引是先把status排序好再在相同status内部按created_at排序。如果跳过status整个B树的排列顺序对created_at来说就是无序的没有快速查找的依据。实际工作中设计复合索引我的经验顺序是等值查询的列放前面范围查询的列放后面区分度高的列尽量放前面。比如状态字段区分度很低就0和1时间字段区分度高有人会问是不是把时间放前面不对因为状态是等值条件时间是范围条件。等值条件放最左能让索引快速过滤到目标范围如果时间放最左等值状态的条件没法快速缩小范围反而更容易扫太多数据。4.3 索引失效的常见场景与排查手段很多人建了一堆索引EXPLAIN一看还是typeALL全表扫描老觉得自己SQL写错了。其实索引失效的原因就那么几种见一个灭一个就行。第一类对索引列做了函数运算或隐式类型转换。WHERE DATE(created_at) 2024-01-01不走索引因为索引存的是原始值不是函数运算后的值。正确写法是WHERE created_at 2024-01-01 AND created_at 2024-01-02。同理WHERE phone 13800138000如果phone是VARCHAR类型整数会隐式转字符串也可能导致索引失效最好保持类型一致。第二类使用前导通配符的LIKE。LIKE %admin%不走索引因为前缀未知B树没法从中间开始定位。但LIKE admin%走索引前缀是确定的。如果必须做模糊查询考虑用全文索引或者pg_trgm这类方案。第三类复合索引里没有最左列。这个上面已经讲了属于设计层面的失效用EXPLAIN一眼就能看出来。排查手段方面EXPLAIN是最基础的重点看几个字段type最好到const、ref、rangeALL就危险了、key实际使用的索引名、rows预估扫描行数、Extra有没有Using filesort、Using temporary。如果看到Using filesort说明排序没走索引可以考虑把排序字段加进索引看到Using temporary说明用了临时表通常是GROUP BY或DISTINCT做了一次额外的排序。索引建立的数量也不是越多越好。索引会拖慢写入速度——每次插入、更新都要同步维护索引结构。我见过一张表建了十几个索引写入的时候慢到怀疑人生。一般来说单表索引控制在5个以内优先覆盖查询最频繁的那几条SQL路径。5. 完整性约束主键、外键、唯一约束与CHECK的取舍完整性约束听起来像繁文缛节但它是保证数据不出错的核心机制。你可以把约束理解成数据库守卫不让脏数据溜进来。没有约束的表应用层写错一个字段类型或业务逻辑数据就彻底脏了后面清洗成本高得吓人。5.1 主键与唯一键都有唯一性但地位完全不同主键约束PRIMARY KEY和唯一约束UNIQUE KEY都要求列值唯一都能加速查询但有本质区别一张表只能有一个主键但可以有多个唯一键。主键列不允许NULL唯一键列可以有一个NULL值MySQL里甚至可以多个NULL。主键是InnoDB聚簇索引的依托唯一键是普通二级索引。实际选型上主键要选业务稳定且不会频繁变更的列。几乎不会有人把手机号当主键因为手机号可能换身份证号理论上唯一但用户可能注销重新注册且身份证号长度19位做主键索引会比自增整数更占空间。所以业务表用自增BIGINT做主键最省事。但分库分表和分布式场景下自增主键会撞号这时用雪花ID或者UUID注意UUID是无序字符串直接做主键会导致索引页频繁分裂更好的做法是加一个自增列做代理主键或者用有序UUID方案。5.2 外键到底要不要用一个存在多年的话题外键约束FOREIGN KEY用来保证两张表之间的引用完整性子表的引用列必须存在于父表主键列中或者为NULL。比如订单表引用用户表没有外键时程序可能把订单关联到一个不存在的用户有外键时数据库直接拒绝插入。那为什么很多互联网公司明确禁止外键原因就一个字性能。外键约束让每次插入、更新子表时都要去父表检查一次引用是否有效在高并发写入场景下这个额外检查既是锁竞争点又是性能瓶颈。同时外键还会让删除父表数据变得复杂得先清干净子表或者级联删除稍不注意就锁一堆表。我的个人经验是核心业务的小团队、强一致要求高的系统可以适当用外键因为数据安全性收益大于性能损失但大规模互联网系统外键基本不用改用应用层校验和管理代码保证一致性。如果你面试被问到外键的利弊把保证引用完整性与性能、扩展性的冲突讲清楚比只回答外键用来保证完整性要好得多。典型的建外键语句用来理解语法即可ALTER TABLE order_main ADD CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user_account(id);5.3 CHECK约束与NOT NULL的实际组合CHECK约束用于限定字段取值范围。比如价格必须大于0状态必须在合法的枚举集合内年龄必须在0-120之间。CREATE TABLE product ( id BIGINT AUTO_INCREMENT PRIMARY KEY, price DECIMAL(10,2) NOT NULL, stock INT NOT NULL, status TINYINT NOT NULL, CONSTRAINT chk_price_positive CHECK (price 0), CONSTRAINT chk_stock_nonnegative CHECK (stock 0), CONSTRAINT chk_status_valid CHECK (status IN (0, 1, 2)) );这里有个知识点MySQL 8.0.16之前的版本CHECK约束只会解析不会强制执行很多人写了CHECK发现没用就是这个原因。8.0.16之后MySQL才真正支持CHECK约束。如果你用的是8.0以下版本要限制字段范围只能靠ENUM类型或者应用层判断。Oracle和PostgreSQL里CHECK约束是一直正常生效的。NOT NULL是我眼里最容易被低估的约束。很多设计者为了灵活允许大量字段为NULL结果查询时处处都要处理NULL的坑NULL与任何值比较都是NULLWHERE status ! 0是查不出NULL记录的COUNT(column)会自动忽略NULLUNIQUE索引允许多个NULL值。我的建议是业务上必须有值的字段就坚决加NOT NULL和默认值这样查询逻辑少一层NULL怎么办的复杂度。完整性约束还有一个容易被忽略的维度是约束的命名。给约束起名字CONSTRAINT chk_xxx方便后续定位和删除如果不给名字数据库会自己生成一串随机名字排查问题的时候还得先查约束名特别折腾。规范命名的约束名一般格式是chk_表名_字段名、fk_表名_引用表名、uk_表名_字段名。6. 梳理完之后的实操建议整个梳理过程中我反复体验到一件事数据库崩不崩很多时候不是看高级特性用得多不多而是看基础做没做扎实。表定义时舍得花十分钟把字段类型、约束、命名想清楚后面能省下无数加班的夜晚。索引设计前先做一轮真实查询路径的梳理比盲目加索引有用得多。最后给几条我实操中沉淀下来的经验一是SQL语句不管多简单都要亲手在测试环境跑一遍再上生产特别是带ALTER、TRUNCATE、DROP的语句手滑一下就是事故。二是定期收集慢查询日志把执行计划翻出来看一遍发现全表扫描的SQL就顺手补索引。这套动作做好数据库的性能问题能提前处理掉一大半。三是最基础的约束和类型规范最好整理成团队的建表规范文档新人都按这个来代码review时按这个检查。很多线上问题本质上都是表设计阶段的偷懒留下的债越早还利息越低。这套梳理加在一起其实就是从会写SQL到懂SQL的过程。前者是语法熟练度后者是数据结构、存储引擎、优化器之间的互相理解。如果你看完这篇能回头把自己项目里的核心表结构和索引设计重新捋一遍并且说得出每个字段为什么这么定那这五千字就算没白写。 从建表到索引优化：SQL表定义与完整性约束实战指南
很多人学了几年数据库建表还是靠感觉约束看心情加索引全部建在主键上。前阵子给团队做数据库基础培训我把表定义、修改/删除表、索引操作、完整性约束这四块从头到尾重新梳理了一遍发现不少平时写了无数遍的SQL其实很多细节都没真正吃透。就说一个最简单的例子同样一个DROP TABLE为什么有的环境执行后磁盘空间还在涨这背后涉及的是事务日志和存储引擎的机制而不只是删掉一张表那么简单。这篇内容适合正在系统学SQL的人、准备数据库面试的求职者以及工作中天天写SQL但没系统补过基础的开发者。我尽量把概念讲透同时每个知识点都给典型示例你照着敲一遍就能掌握。1. 为什么值得把建表这件事重新捋一遍先说个我观察到的现场很多开发者的SQL水平停留在能跑通阶段面试问主键和唯一键的区别能答上来问外键在什么场景下反而会拖垮性能就开始支支吾吾。更麻烦的是工作里建表不规范字段类型用VARCHAR(255)一把梭该用DECIMAL的用FLOAT等数据量上来之后全是坑。表定义是整个数据库设计的地基。你在一张表上建了错误的约束后面所有引用这张表的SQL都可能被拖下水一个字段的类型选错轻则多占用存储重则导致索引失效。索引操作也一样建索引看似简单建在什么列上、用什么顺序建复合索引、什么时候该用覆盖索引这些决策直接决定查询是走索引还是全表扫描。而完整性约束则是保证数据质量最后一道防线。这四个主题既独立又彼此关联。表定义是骨架完整性约束是规矩索引是加速器修改和删除表则是上线之后日常要面对的维护动作。我在下面按实际操作顺序逐步展开每个部分都带示例和可以照抄的写法。2. 表定义CREATE TABLE时的选型和细节建表这步看着简单就是写几个字段名和类型但真正做过几年数据库维护的人都知道建表决策会跟着你很长时间。字段一旦有了数据改类型的成本就非常高尤其在大表上ALTER TABLE锁表时间可能让业务直接停摆。2.1 列类型不是在选能存什么而是在选效率边界类型选择的核心原则是在满足业务需求的前提下选择最节省存储空间的类型。但最节省很容易被误解成最小实际要结合查询场景。先说整数。INT占4字节范围约21亿绝大多数业务主键和计数场景够用BIGINT占8字节适合可能超过21亿的场景或者分布式ID。我的建议是主键直接上BIGINT别省这4个字节。为什么业务增长很难预测等ID耗尽再迁移主键类型那才是真正的灾难。普通状态字段用TINYINT或SMALLINT就够用INT反而浪费。字符类型是最容易出问题的。CHAR(N)是定长VARCHAR(N)是变长但VARCHAR(N)里的N不是字节数是字符数。这里有个经典误区VARCHAR(10)到底能存几个中文答案是10个字符跟汉字还是英文没关系。但如果你用utf8mb4字符集一个字符最多占4字节所以VARCHAR(10)最多占用40字节索引长度限制要按这个算很多人在这个细节上吃过亏。数值里的DECIMAL和FLOAT也经常被用混。金额、汇率、价格这类需要精确计算的必须用DECIMAL(p,s)其中p是总位数s是小数位数。FLOAT和DOUBLE是浮点存储会有精度误差算钱的时候会出现0.10.20.30000000000000004这种问题。实测下来写DECIMAL(10,2)存金额既安全又直观但要注意DECIMAL的运算比FLOAT慢如果做海量浮点计算又不要求精度才考虑FLOAT。日期时间类型也是重灾区。MySQL里DATE只存日期DATETIME存日期和时间且不受时区影响TIMESTAMP则受时区影响且在2038年会溢出。创建时间、更新时间这类字段我习惯用DATETIME因为TIMESTAMP的范围上限是2038年对某些长周期业务来说不够保险。定义时默认值建议写成DEFAULT CURRENT_TIMESTAMP更新字段加ON UPDATE CURRENT_TIMESTAMP很多版本的ORM也能识别这套写法。2.2 默认值、自增列与命名规范的约定建表时用不用默认值这里有个实际考量尽量把能用数据库解决的规则留在数据库里而不是靠应用层保证。比如状态字段默认值0表示待处理比如创建时间默认当前时间比如逻辑删除标记默认0。这样即使某个新来的同事忘记在INSERT语句里给这些字段赋值数据也不会变成NULL或者错误值。自增列AUTO_INCREMENT也是容易出问题的地方。MySQL里自增列必须是索引列通常配合主键使用。删除过数据之后再插入自增值会继续递增不会回填这是正常现象别奇怪为什么ID不是连续的。真正要小心的是大批量删数据后想重置自增ID用TRUNCATE会重置DELETE不会这个区别在后面的修改删除表部分展开讲。命名规范这事情每家公司有每家的习惯但我自己的经验是统一比优雅更重要。表名用业务模块前缀比如user_account、order_main字段名全程小写加下划线主键统一叫id业务关联字段用xxx_id状态字段叫status时间字段叫created_at/updated_at。这套命名法在ORM、报表工具、数仓同步里都通用面试时提到规范建表用这套说法也很加分。建表的演示语句长这样CREATE TABLE user_account ( id BIGINT AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(32) NOT NULL COMMENT 用户名, password_hash CHAR(64) NOT NULL COMMENT 密码哈希, balance 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_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户账户表;这张表的写法有几个值得学习的地方用户名加了唯一索引保证注册不会重复金额用了DECIMAL(10,2)而不是FLOAT状态字段给了默认值utf8mb4字符集支持四字节表情符号避免Emoji存不进去的问题。2.3 关于临时表与表分区的一点补充面试和实际工作中还会碰到临时表分两种一种是CREATE TEMPORARY TABLE只在当前会话可见断开连接自动消失另一种是普通表加_tmp后缀用来做数据迁移中间表。前者适合存储过程内部的中间结果后者适合大表结构调整前备份数据。表分区则是把一张大表按规则拆成多个物理分区但逻辑上仍是一张表。常见的分区方式有RANGE按范围分、HASH按散列分、LIST按枚举值分。分区的核心收益是查询条件能命中分区时可以只扫描对应分区文件同时大表TRUNCATE PARTITION比逐条DELETE快得多。但分区也有副作用尤其是分区键必须包含在主键/唯一键里这个限制经常让人头疼。我的建议是单表超过千万级再考虑分区千万以下先做好索引。3. 修改和删除表ALTER、TRUNCATE与DROP的本质区别表结构上线后不是一劳永逸的加字段、改类型、删约束这些操作每天都在生产环境发生。这一章节重点聊聊ALTER TABLE的常用操作以及为什么TRUNCATE和DROP和DELETE明明都是删行为差距却这么大。3.1 ALTER TABLE 的常用操作与锁表问题先列一份实用清单每个都是高频操作-- 添加字段 ALTER TABLE user_account ADD COLUMN last_login_at DATETIME NULL COMMENT 最后登录时间; -- 修改字段类型 ALTER TABLE user_account MODIFY COLUMN username VARCHAR(64) NOT NULL COMMENT 用户名; -- 重命名字段 ALTER TABLE user_account CHANGE COLUMN username user_name VARCHAR(32) NOT NULL COMMENT 用户名; -- 删除字段 ALTER TABLE user_account DROP COLUMN last_login_at; -- 添加索引 ALTER TABLE user_account ADD INDEX idx_status (status); -- 删除索引 ALTER TABLE user_account DROP INDEX idx_status;这里有个生产环境最常见的坑在数据量很大的表上执行ALTER TABLE可能锁表很久。MySQL 5.6之前的版本ALTER TABLE大概率需要全表重建耗时跟数据量和IO负载直接挂钩5.6以后有了Online DDL在线DDL部分操作可以一边改一边允许并发DML但像MODIFY COLUMN这种改类型或者CHANGE COLUMN改名字的底层仍可能触发表重建。我的实操建议是生产环境的表结构变更优先在业务低峰期做如果条件允许用专门的工具比如pt-online-schema-changePercona Toolkit来做无锁变更。它的思路是先建一个空表副本然后通过触发器同步增量数据最后原子切换表名。这工具我用了很多年踩坑少但要注意它要求源表必须要有主键否则同步机制会失效。3.2 TRUNCATE、DELETE、DROP的底层区别这张对比表值得背下来操作是否走事务是否逐行删除可否带WHERE是否释放存储空间是否重置自增DELETE是是可以否产生大量日志否TRUNCATE否DDL否直接重建表不可以是是DROP否DDL否整表移除不可以是不适用DELETE是DML每一行删除都写事务日志可以回滚。这也是为什么大表DELETE巨慢的原因——不是逐行删除的速度问题而是每删一行都要记一条日志日志增长非常快。TRUNCATE是DDL本质是直接释放表的数据页并把表结构重建所以速度飞快但它不会逐行触发触发器想找回数据只能靠备份和日志恢复。这里有个重要提醒TRUNCATE和DROP虽然是DDL在MySQL里执行后隐式提交无法通过事务回滚。如果你习惯把删除操作包在事务里做千万别把TRUNCATE放进去它不会跟你客气直接生效。DROP和TRUNCATE还有一个区别TRUNCATE保留表结构DROP连表结构一起删除。所以实际开发中定时清理历史数据的脚本如果只想清空数据保留表结构用TRUNCATE如果整张表废弃了用DROP。3.3 删除表之前必须确认的几件事DROP TABLE这个操作一旦执行成功表和数据基本就没了除非靠定期备份和前一天的binlog去恢复。我处理线上事故的经验里有几个确认点是必须过的确认这张表是否被其他表的外键引用。MySQL里如果存在外键约束DROP父表会直接报错DROP子表倒是可以但后续程序查询父表关联时可能逻辑出错。确认有没有下游任务在读取这张表。数仓同步、报表系统、定时任务经常在你不知道的地方引用数据表表被DROP之后这些任务全部告警。确认删除范围。如果只是想清空部分数据用DELETE配WHERE别用TRUNCATE。TRUNCATE不支持条件删除一执行就是全表清空。我个人的习惯是任何DROP和TRUNCATE操作前先跑一个备份脚本把数据导出到文件哪怕备份完再用不上也比出事时干瞪眼强。4. 索引操作从建索引到索引失效索引操作是数据库进阶绕不开的一关。索引的本质是数据结构MySQL InnoDB默认用的是B树它能让我们从逐行扫描变成按树查找。面试最喜欢问的问题其实就是看你能不能把索引的使用场景讲明白。4.1 聚簇索引与非聚簇索引一张表只能有一个物理顺序先说聚簇索引。InnoDB的表数据本身就是按主键聚簇排列的也就是说主键索引的叶子节点直接存整行数据。这张表的物理顺序和主键顺序一致这就是聚簇索引。一张表只能有一个聚簇索引因为数据不可能同时按两种顺序物理排列。非聚簇索引也叫二级索引叶子节点存的是索引列的值和主键值。走二级索引查询的时候先找到匹配的二级索引条目拿到主键值再回聚簇索引里取整行数据这个过程叫回表。回表次数多查询就慢所以就有了覆盖索引的概念如果查询需要的所有列都包含在二级索引里就不需要回表了。-- 覆盖索引示例只需要id、username两列二级索引里都有就不回表 SELECT id, username FROM user_account WHERE username zhangsan;这条SQL如果只建了uk_username(username)唯一索引那username匹配后能直接拿到id两个字段都在索引里直接覆盖。但如果你SELECT的是balance字段就必须回表拿整行数据了。你问我怎么知道走没走覆盖索引最简单的办法是看执行计划的Extra列如果出现Using index说明走了覆盖索引如果出现Using index condition说明只做了索引下推部分优化仍需回表。4.2 复合索引的列顺序最左前缀原则复合索引联合索引是多个列合建的索引。它遵循最左前缀原则查询条件里如果从索引的最左列开始连续匹配就能用到索引跳过最左列直接查后面的列索引基本失效。举例来说建一个复合索引idx_status_created (status, created_at)-- 走索引条件中包含了最左列status SELECT * FROM user_account WHERE status 0 AND created_at 2024-01-01; -- 走索引只用到了最左列statuscreated_at范围让右边界失效但status能定位 SELECT * FROM user_account WHERE status 0; -- 不走索引跳过了status直接查created_at最左前缀断裂 SELECT * FROM user_account WHERE created_at 2024-01-01;这里有个容易混淆的点为什么只用status能走索引而只用created_at不能因为复合索引是先把status排序好再在相同status内部按created_at排序。如果跳过status整个B树的排列顺序对created_at来说就是无序的没有快速查找的依据。实际工作中设计复合索引我的经验顺序是等值查询的列放前面范围查询的列放后面区分度高的列尽量放前面。比如状态字段区分度很低就0和1时间字段区分度高有人会问是不是把时间放前面不对因为状态是等值条件时间是范围条件。等值条件放最左能让索引快速过滤到目标范围如果时间放最左等值状态的条件没法快速缩小范围反而更容易扫太多数据。4.3 索引失效的常见场景与排查手段很多人建了一堆索引EXPLAIN一看还是typeALL全表扫描老觉得自己SQL写错了。其实索引失效的原因就那么几种见一个灭一个就行。第一类对索引列做了函数运算或隐式类型转换。WHERE DATE(created_at) 2024-01-01不走索引因为索引存的是原始值不是函数运算后的值。正确写法是WHERE created_at 2024-01-01 AND created_at 2024-01-02。同理WHERE phone 13800138000如果phone是VARCHAR类型整数会隐式转字符串也可能导致索引失效最好保持类型一致。第二类使用前导通配符的LIKE。LIKE %admin%不走索引因为前缀未知B树没法从中间开始定位。但LIKE admin%走索引前缀是确定的。如果必须做模糊查询考虑用全文索引或者pg_trgm这类方案。第三类复合索引里没有最左列。这个上面已经讲了属于设计层面的失效用EXPLAIN一眼就能看出来。排查手段方面EXPLAIN是最基础的重点看几个字段type最好到const、ref、rangeALL就危险了、key实际使用的索引名、rows预估扫描行数、Extra有没有Using filesort、Using temporary。如果看到Using filesort说明排序没走索引可以考虑把排序字段加进索引看到Using temporary说明用了临时表通常是GROUP BY或DISTINCT做了一次额外的排序。索引建立的数量也不是越多越好。索引会拖慢写入速度——每次插入、更新都要同步维护索引结构。我见过一张表建了十几个索引写入的时候慢到怀疑人生。一般来说单表索引控制在5个以内优先覆盖查询最频繁的那几条SQL路径。5. 完整性约束主键、外键、唯一约束与CHECK的取舍完整性约束听起来像繁文缛节但它是保证数据不出错的核心机制。你可以把约束理解成数据库守卫不让脏数据溜进来。没有约束的表应用层写错一个字段类型或业务逻辑数据就彻底脏了后面清洗成本高得吓人。5.1 主键与唯一键都有唯一性但地位完全不同主键约束PRIMARY KEY和唯一约束UNIQUE KEY都要求列值唯一都能加速查询但有本质区别一张表只能有一个主键但可以有多个唯一键。主键列不允许NULL唯一键列可以有一个NULL值MySQL里甚至可以多个NULL。主键是InnoDB聚簇索引的依托唯一键是普通二级索引。实际选型上主键要选业务稳定且不会频繁变更的列。几乎不会有人把手机号当主键因为手机号可能换身份证号理论上唯一但用户可能注销重新注册且身份证号长度19位做主键索引会比自增整数更占空间。所以业务表用自增BIGINT做主键最省事。但分库分表和分布式场景下自增主键会撞号这时用雪花ID或者UUID注意UUID是无序字符串直接做主键会导致索引页频繁分裂更好的做法是加一个自增列做代理主键或者用有序UUID方案。5.2 外键到底要不要用一个存在多年的话题外键约束FOREIGN KEY用来保证两张表之间的引用完整性子表的引用列必须存在于父表主键列中或者为NULL。比如订单表引用用户表没有外键时程序可能把订单关联到一个不存在的用户有外键时数据库直接拒绝插入。那为什么很多互联网公司明确禁止外键原因就一个字性能。外键约束让每次插入、更新子表时都要去父表检查一次引用是否有效在高并发写入场景下这个额外检查既是锁竞争点又是性能瓶颈。同时外键还会让删除父表数据变得复杂得先清干净子表或者级联删除稍不注意就锁一堆表。我的个人经验是核心业务的小团队、强一致要求高的系统可以适当用外键因为数据安全性收益大于性能损失但大规模互联网系统外键基本不用改用应用层校验和管理代码保证一致性。如果你面试被问到外键的利弊把保证引用完整性与性能、扩展性的冲突讲清楚比只回答外键用来保证完整性要好得多。典型的建外键语句用来理解语法即可ALTER TABLE order_main ADD CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES user_account(id);5.3 CHECK约束与NOT NULL的实际组合CHECK约束用于限定字段取值范围。比如价格必须大于0状态必须在合法的枚举集合内年龄必须在0-120之间。CREATE TABLE product ( id BIGINT AUTO_INCREMENT PRIMARY KEY, price DECIMAL(10,2) NOT NULL, stock INT NOT NULL, status TINYINT NOT NULL, CONSTRAINT chk_price_positive CHECK (price 0), CONSTRAINT chk_stock_nonnegative CHECK (stock 0), CONSTRAINT chk_status_valid CHECK (status IN (0, 1, 2)) );这里有个知识点MySQL 8.0.16之前的版本CHECK约束只会解析不会强制执行很多人写了CHECK发现没用就是这个原因。8.0.16之后MySQL才真正支持CHECK约束。如果你用的是8.0以下版本要限制字段范围只能靠ENUM类型或者应用层判断。Oracle和PostgreSQL里CHECK约束是一直正常生效的。NOT NULL是我眼里最容易被低估的约束。很多设计者为了灵活允许大量字段为NULL结果查询时处处都要处理NULL的坑NULL与任何值比较都是NULLWHERE status ! 0是查不出NULL记录的COUNT(column)会自动忽略NULLUNIQUE索引允许多个NULL值。我的建议是业务上必须有值的字段就坚决加NOT NULL和默认值这样查询逻辑少一层NULL怎么办的复杂度。完整性约束还有一个容易被忽略的维度是约束的命名。给约束起名字CONSTRAINT chk_xxx方便后续定位和删除如果不给名字数据库会自己生成一串随机名字排查问题的时候还得先查约束名特别折腾。规范命名的约束名一般格式是chk_表名_字段名、fk_表名_引用表名、uk_表名_字段名。6. 梳理完之后的实操建议整个梳理过程中我反复体验到一件事数据库崩不崩很多时候不是看高级特性用得多不多而是看基础做没做扎实。表定义时舍得花十分钟把字段类型、约束、命名想清楚后面能省下无数加班的夜晚。索引设计前先做一轮真实查询路径的梳理比盲目加索引有用得多。最后给几条我实操中沉淀下来的经验一是SQL语句不管多简单都要亲手在测试环境跑一遍再上生产特别是带ALTER、TRUNCATE、DROP的语句手滑一下就是事故。二是定期收集慢查询日志把执行计划翻出来看一遍发现全表扫描的SQL就顺手补索引。这套动作做好数据库的性能问题能提前处理掉一大半。三是最基础的约束和类型规范最好整理成团队的建表规范文档新人都按这个来代码review时按这个检查。很多线上问题本质上都是表设计阶段的偷懒留下的债越早还利息越低。这套梳理加在一起其实就是从会写SQL到懂SQL的过程。前者是语法熟练度后者是数据结构、存储引擎、优化器之间的互相理解。如果你看完这篇能回头把自己项目里的核心表结构和索引设计重新捋一遍并且说得出每个字段为什么这么定那这五千字就算没白写。