2023年7月13日发(作者：)

数据库设计规范数据库的重要性不⾔⽽喻。对程序员来说跟数据库打交道更是家常便饭。数据库给开发带来了巨⼤的便利。我们或多或少的知道⼀些数据库设计规范，但并不全⾯。今天我就简单整理⼀下，帮⾃⼰做个总结梳理，也希望可以帮到⼩伙伴们。数据库设计规范包括命名规范、库表基础规范、字段规范、索引规范和SQL设计规范。1. 命名规范1.1 库名、表名、字段名禁⽌使⽤MySQL保留字。1.2 库名、表名、字段名使⽤常⽤英语⽽不要使⽤编码，需见名知意，命名与业务、产品线等相关联。中⽂词汇的英语翻译可以参考常⽤术语来选择相应的英⽂词汇。1.3 库名、表名、字段名必须是名词的复数形式，并且使⽤⼩写字母，多个名词采⽤下划线分割单词。MySQL有配置参数lower_case_table_names=1，即库表名以⼩写存储，⼤⼩写不敏感。如果是0，则库表名以实际情况存储，⼤⼩写敏感；如果是2，以实际情况存储，但以⼩写⽐较。如果⼤⼩写混合使⽤，可能存在abc、Abc、ABC等多个表共存，容易导致混乱。字段名显⽰区分⼤⼩写，但实际使⽤时不区分，即不可以建⽴两个名字⼀样但⼤⼩写不⼀样的字段。为了统⼀规范， 库名、表名、字段名使⽤⼩写字母，不允许-号。1.4 库名、表名、字段名禁⽌超过32个字符。库名、表名、字段名⽀持最多64个字符，但为了统⼀规范、易于辨识以及减少传输量，禁⽌超过32个字符1.5 索引命名规则索引按照idx_table_column1_column2。其中table是建⽴索引的表名，column1和column2是建⽴索引的字段名。索引名限制在32个字符内。当索引名超过32字符时，可⽤缩写来减少索引名的长度，如description --> desc；information --> info；address --> addr等。1.6 主键、外键命名规则主键按照PK_table的规则命名，其中table为数据库表名。唯⼀键按照UK_table_column的规则命名。其中table为数据块表名，column为字段名。外键按照FK_parent_child_nn的规则命名。其中parent为⽗表名，child为⼦表名，nn为序列号。2. 库表基础规范2.1 使⽤InnoDB存储引擎。MySQL 5.5版本开始默认存储引擎就是InnoDB，5.7版本开始，系统表都放弃MyISAM了。2.2 表字符集使⽤UTF8MB4字符集，校验字符集使⽤utf8mb4_general_ci。UTF8字符集存储汉字占⽤3个字节，存储英⽂字符占⽤⼀个字节校对字符集使⽤默认的utf8mb4_general_ci。特别对于使⽤GUI⼯具设计表结构时，要检查它⽣成的SQL定义连接的客户端也使⽤utf8，建⽴连接时指定charset或SET NAMES UTF8;。如果遇到EMOJ等表情符号的存储需求，可申请使⽤UTF8MB4字符集2.3 所有表都要添加注释，除主键外的字段都需要添加注释类status型需指明主要值的含义，如'0-离线，1-在线'2.4 控制单表字段数量单表字段数上限30左右，再多的话考虑垂直分表，⼀是冷热数据分离，⼆是⼤字段分离，三是常在⼀起做条件和返回列的不分离。表字段控制少⽽精，可以提⾼I/O效率，内存缓存更多有效数据，从⽽提⾼响应速度和并发能⼒，后续ALTER TABLE也更快。2.5 所有表都必须显式指定主键主键尽量采⽤⾃增⽅式，InnoDB表实际是⼀棵索引组织表，顺序存储可以提⾼存取效率，充分利⽤磁盘空间。还有对⼀些复杂查询可能需要⾃连接来优化时需要⽤到。只有需要全局唯⼀主键时，使⽤外部⾃增id服务如果没有主键或唯⼀索引，UPDATE/DELETE是通过所有字段来定位操作的⾏，相当于每⾏就是⼀次全表扫描少数情况可以使⽤联合唯⼀主键，需与DBA协商对于主键字段值是从其它地⽅插⼊（⾮⾃⼰使⽤AUTO_INCREMENT⽣产），去掉AUTO_INCREMENT定义。⽐如⼀些31天表、历史⽉份表上，不要AUTO_INCREMENT属性；还有，必须通过全局id服务获取的主键，也要去掉AUTO_INCREMENT定义。2.6 不强制使⽤外键参考即使2个表的字段有明确的外键参考关系，也不使⽤FOREIGN KEY，因为新纪录会去主键表做校验，影响性能。2.7 适度使⽤视图，禁⽌使⽤存储过程、触发器和事件使⽤视图⼀定程度上也是为了降低代码⾥SQL的复杂度，但有时候为了视图的通⽤性会损失性能（⽐如返回不必要的字段）。存储过程（PROCEDURE）虽然可以简化业务端代码，在传统企业写复杂逻辑时可能会⽤到，⽽在互联⽹企业变更是很频繁的，在分库分表的情况下要升级⼀个存储过程相当⿇烦。⼜因为它是不记录log的，所以也不⽅便调试性能问题。如果使⽤过程，⼀定考虑如果执⾏失败的情况。触发器（TRIGGER）也是同样，但也不应该通过它去约束数据的强⼀致性，MySQL只⽀持“基于⾏的触发”，也就是说，触发器始终是针对⼀条记录的，⽽不是针对整个sql语句的，如果变更的数据集⾮常⼤的话，效率会很低。掩盖⼀条SQL背后的⼯作，⼀旦出现问题将是灾难性的，但⼜很难快速分析和定位。再者需要DDL时⽆法使⽤pt-osc⼯具。放在TRANSACTION中执⾏。事件（EVENT）也是⼀种偷懒的表现，⽬前已经遇到数次由于定时任务执⾏失败影响业务的情况，⽽且MySQL⽆法对它做失败预警。建⽴专门的 job scheduler 平台。2.8 单表数据量控制在5000万以内表字段数量不要超过20个，如果有需要建⽴主副表，主键⼀⼀关联，避免单⾏数据过多以及修改记录binlog ROW模式导致⽂件过⼤。特别对于有⼀个text/blob或很⼤长度的varchar字段时，更应考虑单独存储。但也要注意查询条件尽量放在⼀个表上。2.9 尽量只存储单⼀实体类型的数据2.10 数据库中不允许存储明⽂密码所有的密码、scret key和SSH key等类似的保密信息，必须经过⾮对称加密，再保存到数据库中。2.11 尽量符合数据库的⼏个范式。3. 字段规范3.1 char、varchar、text等字符串类型定义对于长度基本固定的列，如果该列恰好更新⼜特别频繁，适合char。 utf8mb4字符集下，尽量使⽤varchar。varchar虽然存储变长字符串，但不可太⼩也不可太⼤。UTF8最多能存21844个汉字，或65532个英⽂varbinary(M)保存的是⼆进制字符串，它保存的是字节⽽不是字符，所以没有字符集的概念，M长度0-255（字节）。只⽤于排序或⽐较时⼤⼩写敏感的类型，不包括密码存储text类型与varchar都类似，存储可变长度，最⼤限制也是2^16，但是它20bytes以后的内容是在数据页以外的空间存储（row_format=dynamic），对它的使⽤需要多⼀次寻址，没有默认值。 ⼀般⽤于存放容量平均都很⼤、操作没有其它字段那样频繁的值。⽹上部分⽂章说要避免使⽤text和blob，要知道如果纯⽤varchar可能会导致⾏溢出，效果差不多，但因为每⾏占⽤字节数过多，会导致buffer_pool能缓存的数据⾏、页下降。另外text和blob上⾯⼀般不会去建索引，⽽是利⽤sphinx之类的第三⽅全⽂搜索引擎，如果确实要创建（前缀）索引，那就会影响性能。凡事看具体场景。另外尽可能把text/blob拆到另⼀个表中BLOB可以看成varbinary的扩展版本，内容以⼆进制字符串存储，⽆字符集，区分⼤⼩写，有⼀种经常提但不⽤的场景：不要在数据库⾥存储图⽚。当字段定义为字符串形时建议使⽤varchar⽽不⽤nvarchar。3.2 int、tinyint、decimal等数字类型定义使⽤tinyint来代替enum和boolean。enum类型在需要修改或增加枚举值时，需要在线DDL，成本较⾼；enum列值如果含有数字类型，可能会引起默认值混淆。tinyint使⽤1个字节，⼀般⽤于status、type、flag的列。建议使⽤unsigned存储⾮负数值，相⽐不使⽤unsigned，可以扩⼤⼀倍使⽤数值范围。int使⽤固定4个字节存储，int(11)与int(4)只是显⽰宽度的区别。但是定义是bigint(20), int(11)，不要随便改动这个显⽰宽度，c++⾥⾯需要这个长度去截取字段。使⽤decimal代替float/double存储精确浮点数。对于货币、⾦额这样的类型，使⽤decimal，如 decimal(9,2)。float默认只能精确到6位有效数字。3.3 timestamp与datetime选择datetime和timestamp类型所占的存储空间不同，前者5个字节(5.5是8字节)，后者4个字节，这样造成的后果是两者能表⽰的时间范围不同。前者范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59，后者范围为 1970-01-01 08:00:01 到 2038-01-19 11:14:07。所以 timestamp ⽀持的范围⽐ datatime 要⼩。timestamp可以在INSERT/UPDATE⾏时，⾃动更新时间字段（如 set_time timestamp NOT NULL DEFAULTCURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP），但⼀个表只能有⼀个这样的定义。timestamp显⽰与时区有关，内部总是以UTC毫秒来存的，还受到严格模式的限制。优先使⽤timestamp，datetime也没问题默认时间，要么CURRENT_TIMESTAMP，要么'1970-01-02 01:01:01'，不要设置为''或0WHERE条件⾥不要对时间列上使⽤时间函数如果使⽤int类型存储时间戳，约定统⼀使⽤int unsigned default 03.4 建议字段都定义为NOT NULL如果是索引字段，⼀定要定义为NOT NULL。因为NULL值会影响cordinate统计，影响优化器对索引的选择虽然表中允许空(NULL)列，但是，空字段是⼀种⽐较特殊的数据类型。数据库在处理的时候，需要进⾏特殊的处理。如此的话，就会增加数据库处理记录的复杂性。当表中有⽐较多的空字段时，在同等条件下，数据库处理的性能会降低许多。如果不能保证INSERT时该字段⼀定有值过来，解决⽅法：通过设置默认值的形式，定义时使⽤DEFAULT ''或DEFAULT 0，来避免空字段的产⽣。若⼀张表中，允许为空的列⽐较多，接近表全部列数的三分之⼀。⽽且， 这些列在⼤部分情况下，都是可有可⽆的。若数据库管理员遇到这种情况，建议另外建⽴⼀张副表，以保存这些列。3.5 字段的默认值所有字段在设计时，除timestamp、image、datetime、smalldatetime、uniqueidentifier、binary、sql_variant、binary、varbinary这些数据类型外，必须有默认值。字符型的默认值为⼀个空字符值串''；数值型的默认值为数值0；逻辑型的默认值为数值0；其中，系统中所有逻辑型中数值0表⽰为假；数值1表⽰为真。datetime、smalldatetime类型的字段没有默认值，必须为NULL。3.6 同⼀意义的字段定义必须相同⽐如不同表中都有user_id字段，那么它的类型、字段长度要设计成⼀样4. 索引规范4.1 索引个数限制索引是双刃剑，会增加维护负担，增⼤I/O压⼒，索引占⽤空间是成倍增加的单张表的索引数量控制在5个以内，或不超过表字段个数的20%。若单张表多个字段在查询需求上都要单独⽤到索引，需要经过DBA评估。4.2 避免冗余索引InnoDB表是⼀棵索引组织表，主键是和数据放在⼀起的聚集索引，普通索引最终指向的是主键地址，所以把主键做最后⼀列是多余的。如crm_id作为主键，联合索引(user_id,crm_id)上的crm_id就完全多余两个索引(a,b,c)、(a,b)，后者为冗余索引。可以利⽤前缀索引来达到加速⽬的，减轻维护负担4.3 没有特殊要求，使⽤⾃增id作为主键主键是⼀种聚集索引，顺序写⼊。组合唯⼀索引作为主键的话，是随机写⼊，适合写少读多的表主键不允许更新4.4 索引尽量建在选择性⾼的列上不在低基数列上建⽴索引，例如性别、类型。但有⼀种情况，idx_feedbackid_type (feedback_id, type)，如果经常⽤type=1⽐较，⽽且能过滤掉90%⾏，那这个组合索引就值得创建。有时候同样的查询语句，由于条件取值不同导致使⽤不同的索引，也是这个道理。索引选择性计算⽅法（基数 ÷ 数据⾏数）Selectivity = Cardinality / Total Rows = select count(distinct col1)/count(*) from tbname，越接近1说明col1上使⽤索引的过滤效果越好⾛索引扫描⾏数超过30%时，改全表扫描4.5 最左前缀原则MySQL使⽤联合索引时，从左向右匹配，遇到断开或者范围查询时，⽆法⽤到后续的索引列。⽐如索引idx_c1_c2_c3 (c1, c2, c3)，相当于创建了(c1)、(c1,c2)、(c1,c2,c3)三个索引，WHERE条件包含上⾯三种情况的字段⽐较则可以⽤到索引，但像WHERE c1=a ANDc3=c只能⽤到c1列的索引，像c2=b AND c3=c等情况就完全⽤不到这个索引遇到范围查询(>、<、between、like)也会停⽌索引匹配，⽐如c1=a AND c2 > 2 AND c3=c，只有c1、c2列上的⽐较能⽤到索引，(c1,c2, c3)排列的索引才可能会都⽤上。WHERE条件⾥⾯字段的顺序与索引顺序⽆关，MySQL优化器会⾃动调整顺序。4.6 前缀索引对超过30个字符长度的列创建索引时，考虑使⽤前缀索引，如idx_cs_guid2 (cs_guid(26))表⽰截取前26个字符做索引，既可以提⾼查找效率，也可以节省空间前缀索引也有它的缺点是，如果在该列上ORDER BY或GROUP BY时⽆法使⽤索引，也不能把它们⽤作覆盖索引(Covering Index)如果在varbinary或blob这种以⼆进制存储的列上建⽴前缀索引，要考虑字符集，括号⾥表⽰的是字节数4.7 合理使⽤覆盖索引减少I/OInnoDB存储引擎中，secondary index(⾮主键索引，⼜称为辅助索引、⼆级索引)没有直接存储⾏地址，⽽是存储主键值。如果⽤户需要查询secondary index中所不包含的数据列，则需要先通过secondary index查找到主键值，然后再通过主键查询到其他数据列，因此需要查询两次。覆盖索引则可以在⼀个索引中获取所有需要的数据列，从⽽避免回表进⾏⼆次查找，节省I/O因此效率较⾼。例如SELECT email，uid FROM user_email WHERE uid = xx，如果uid不是主键，适当时候可以将索引添加为index(uid，email)，以获得性能提升。4.8 尽量不要在频繁更新的列上创建索引如不在定义了ON UPDATE CURRENT_STAMP的列上创建索引，维护成本太⾼（好在MySQL有insert buffer，会合并索引的插⼊）4.9 修改表结构DROP COLUM时要注意与这个字段相关的索引都会改变，变化是从原索引抽掉该字段定义。这种情况有可能导致部分索引重复或失效。5. SQL设计规范5.1 所有关键字的所有字母必须⼤写5.2 杜绝直接SELECT *读取全部字段即使需要所有字段，明确指定所需字段也能减少⽹络带宽消耗，能有效利⽤覆盖索引，表结构变更对程序基本⽆影响。5.3 能确定返回结果只有⼀条时，使⽤LIMIT 1在保证数据不会有误的前提下，能确定结果集数量时，多使⽤LIMIT，尽快地返回结果。5.4 ⼩⼼隐式类型转换转换规则两个参数⾄少有⼀个是NULL时，⽐较的结果也是NULL，例外是使⽤<、=、>对两个NULL做⽐较时会返回1，这两种情况都不需要做类型转换两个参数都是字符串，会按照字符串来⽐较，不做类型转换两个参数都是整数，按照整数来⽐较，不做类型转换⼗六进制的值和⾮数字做⽐较时，会被当做⼆进制串有⼀个参数是timestamp或datetime，并且另外⼀个参数是常量，常量会被转换为timestamp有⼀个参数是decimal类型，如果另外⼀个参数是decimal或者整数，会将整数转换为decimal后进⾏⽐较，如果另外⼀个参数是浮点数，则会把decimal转换为浮点数进⾏⽐较所有其他情况下，两个参数都会被转换为浮点数再进⾏⽐较。如果⼀个索引建⽴在string类型上，如果这个字段和⼀个int类型的值⽐较，符合第 7 条。如phone定义的类型是varchar，但WHERE使⽤phone in (098890)，两个参数都会被当成成浮点型。发⽣这个隐式转换并不是最糟的，最糟的是string转换后的float，MySQL⽆法使⽤索引，这才导致了性能问题。如果是user_id = '1234567' 的情况，符合第 2 条，直接把数字当字符串⽐较。5.5 禁⽌在WHERE条件列上使⽤函数会导致索引失效，如LOWER(email)，qq % 4。可放到等号右边的常量上计算返回⼩结果集不是很⼤的情况下，可以对返回列使⽤函数，简化程序开发5.6 使⽤LIKE模糊匹配，%不要放⾸位会导致索引失效，有这种搜索需求是，考虑其它⽅案，如sphinx全⽂搜索5.7 涉及到复杂SQL时，务必先参考已有索引设计，先EXPLAIN简单SQL拆分，不以代码处理复杂为由。⽐如OR条件： phone=’10000’ OR mobile=’10000’，两个字段各⾃有索引，但只能⽤到其中⼀个。可以拆分成2个sql，或者UNION ALL。先EXPLAIN的好处是可以为了利⽤索引，增加更多查询限制条件5.8 使⽤JOIN时，WHERE条件尽量使⽤充分利⽤同⼀表上的索引如 SELECT t1.a, t2.b * FROM t1, t2 AND t1.a=t2.a AND t1.b=123 AND t2.c= 4，如果t1.c与t2.c字段相同，那么t1上的索引(b, c)就只⽤到b了。此时如果把WHERE条件中的t2.c=4改成t1.c=4，那么可以⽤到完整的索引这种情况可能会在字段冗余设计（反范式）时出现正确选取INNER JOIN和LEFT JOIN。不允许滥⽤LEFT JOIN。5.9 少⽤⼦查询，改⽤JOIN⼩于5.6版本时，⼦查询效率很低，不像Oracle那样先计算⼦查询后外层查询。5.6版本开始得到优化。5.10 考虑使⽤UNION ALL，少使⽤UNION，注意考虑去重UNION ALL不去重，⽽少了排序操作，速度相对⽐UNION要快，如果没有去重的需求，优先使⽤UNION ALL如果UNION结果中有使⽤LIMIT，在2个⼦SQL可能有许多返回值的情况下，各⾃加上LIMIT。如果还有ORDER BY，请找DBA。5.11 IN的内容尽量不超过200个超过500个值使⽤批量的⽅式，否则⼀次执⾏会影响数据库的并发能⼒，因为单SQL只能且⼀直占⽤单CPU，⽽且可能导致主从复制延迟。5.12 拒绝⼤事务⽐如在⼀个事务⾥进⾏多个SELECT，多个UPDATE，如果是⾼频事务，会严重影响MySQL并发能⼒，因为事务持有的锁等资源只在事务ROLLBACK/COMMIT时才能释放。但同时也要权衡数据写⼊的⼀致性。不要再事务⾥⾯做除数据库以外的操作。5.13 避免使⽤IS NULL, IS NOT NULL这样的⽐较5.14 ORDER BY .. LIMIT这种查询更多的是通过索引去优化，但ORDER BY的字段有讲究，⽐如主键id与time都是顺序递增，那就可以考虑ORDER BY id⽽⾮time5.15 c1 < a ORDER BY c2与上⾯不同的是，ORDER BY之前有个范围查询，由前⾯的内容可知，⽤不到类似(c1,c2)的索引，但是可以利⽤(c2,c1)索引。另外还可以改写成JOIN的⽅式实现。5.16 分页优化建议使⽤合理的分页⽅式以提⾼分页效率，⼤页情况下不使⽤跳跃式分页假如有类似下⾯分页语句: SELECT FROM table1 ORDER BY ftime DESC LIMIT 10000,10;这种分页⽅式会导致⼤量的I/O，因为MySQL使⽤的是提前读取策略。 推荐分页⽅式： SELECT FROM table1 WHERE ftime < last_time ORDER BY ftime DESC LIMIT 10即传⼊上⼀次分页的界值。或者： SELECT * FROM table as t1 inner JOIN (SELECT id FROM table ORDER BY time LIMIT 10000，10) as t2 ON =5.17 COUNT计数⾸先COUNT()、COUNT(1)、COUNT(col1)是有区别的，COUNT()表⽰整个结果集有多少条记录，COUNT(1)表⽰结果集⾥以主键统计数量，绝⼤多数情况下COUNT()与COUNT(1)效果⼀样的，但COUNT(col1)表⽰的是结果集⾥col1列NOT NULL的记录数。优先采⽤COUNT()⼤数据量COUNT是消耗资源的操作，甚⾄会拖慢整个库，查询性能问题⽆法解决的，应从产品设计上进⾏重构。例如当频繁需要COUNT的查询，考虑使⽤汇总表遇到DISTINCT的情况，GROUP BY⽅式可能效率更⾼。5.18 DELETE、UPDATE语句改成SELECT再EXPLAINSELECT最多导致数据库慢，写操作才是锁表的罪魁祸⾸5.19 减少与数据库交互的次数，尽量采⽤批量SQL语句INSERT ... ON DUPLICATE KEY UPDATE ...，插⼊⾏后会导致在⼀个唯⼀索引或主键中出现重复值，则执⾏旧⾏UPDATE，如果不重复则直接插⼊，影响1⾏。REPLACE INTO类似，但它是冲突时删除旧⾏。INSERT IGNORE相反，保留旧⾏，丢弃要插⼊的新⾏。INSERT INTO VALUES(),(),()，合并插⼊。5.20 杜绝危险SQL去掉WHERE 1=1这样⽆意义或恒真的条件，如果遇到UPDATE/DELETE或遭到SQL注⼊就恐怖了SQL中不允许出现DDL语句。⼀般也不给予CREATE/ALTER这类权限，但阿⾥云RDS只区分读写⽤户5.21 是否应该ORDER BY主键许多排序的场景，如果主键id是增长的，如果ORDER BY create_time查询慢，有可能使⽤了filesort，此时最简单的办法是看能否换成ORDER BY id，因为id作为主键是递增的，并且附带在了每个⼆级索引后⾯。但是也要谨慎使⽤ ORDER BY id，特别是在EXPLAIN结果看到filesort的情况下，优化器极有可能放弃这个filesort，⽽选择了它所认为更⾼效的扫描⽅式，实则更慢。5.22 使⽤正确的表⽐如要统计昨天的数据这类业务较多，是否可以设计⼀个昨天表，不在31天表上统计，在⽉份表上统计也⾏。或者其它组已经有“半统计”的数据，从他们那抽取数据，⽽不是在原始数据上统计。