数据库表设计规范_MYSQL表设计规范

日常工作总结，不是通用规范

数据库的命名、表格的命名以及列的命名必须全部采用小写字母，并且用下划线进行分隔。命名时，每个名称的长度不能超过十二个字符。命名应当直观易懂，最好选用名词而非动词。推荐使用特定的存储引擎。在处理需要精确的小浮点数时，应避免使用FLOAT类型，而是选用其他更合适的类型。对于非负数值的存储，建议使用相应的数据类型。在存储IPv4地址时，应使用INT类型。定义整形时不用指定大小，比如直接用INT，不用INT(4)。选用精简的数据类型，比如数值在0到80之间就选这个。不推荐用ENUM类型，用另一种方式来替代。尽量少用TEXT和BLOB类型。(N)里的N代表字符数量不是字节长度，比如(255)能存255个汉字，选(N)时要按实际字符宽度来定。(N) 要尽可能小，因为一个表所有字段的最大长度为65535字节，排序和创建临时表等内存操作会受限于N的长度，表字符集选用UTF8，存储变长字符串，年使用YEAR类型，日期使用DATE类型，时间（精确到秒）建议用类型，因为该类型用4字节，另一种类型用8字节。字段值不能为空。把体积过大的字段移到别的数据表中。不允许在数据库里用BLOB来存图片、文件等资料。修改表的结构要告知数据库管理员进行审查。二、关于索引，非唯一索引的命名要遵循“idx_第一个字段名_第二个字段名”的规则

_字段名

确立名称。 2. 单一索引务必遵循“唯一_列名_列名

_字段名

需要指定名称。索引的标识应当是小写的。索引所含的字段不宜过多，最好在五个以下。单张数据表所建立的索引不宜超过五个。当唯一键由三个或更少字段构成，并且这些字段都是整数类型时，应将唯一键设置为基本键。当不存在唯一键，或者唯一键不满足第5条所述条件时，应采用自动增长的id（或借助编号工具生成）作为基本键。第8条，唯一键与基本键不应相同。排序索引时，要考虑字段值去重后的数量，数量多的要放在前面位置。对ORDER BY和GROUP BY的字段，需要放在索引的后面部分。要检查SQL语句是否合理利用索引，尽量减少extra列的出现，比如避免出现File Sort和Using等提示。编写语句时，要根据WHERE条件来添加索引。不推荐运用%符号进行模糊检索，比如采用LIKE “%weibo”的方式。针对字符长度较大的字段，在构建索引时，应额外设立crc32或MD5 Hash类型字段，并针对该Hash字段建立索引。需要恰当地设计联合索引，防止字段出现重复，(a,b,c)的索引效果等同于(a)、(a,b)以及(a,b,c)的组合。要有效运用覆盖索引。SQL调整需核实索引是否调整并知会数据库管理员，三、SQL指令采用逗号分隔可提升效率且防止SQL攻击，SQL指令中IN子句的值不宜过多，、指令无需使用LIMIT，WHERE子句必须选用适配的类型，防止MySQL进行隐式类型转换，指令仅查询必要的字段。必须明确指出字段名称，不用星号，不用将数据插入到 table() 中。通过逗号分隔，从 table 中获取，根据条件筛选。

而不是 FROM table WHERE

和 FROM table WHERE

在查询语句中，若涉及非相等性判断，比如使用IN或!=等操作符，后续条件可能无法利用索引进行优化。应防止在SQL命令里执行数学计算或函数调用，以免让业务规则与数据库系统产生强依赖关系。当通过批处理方式向表中插入数据时，括号内记录的组数不宜设置得太多。不要运用存储过程、触发器、函数等，它们容易让业务逻辑和数据库紧密相连，而且MySQL的存储过程、触发器、函数里可能会有一些缺陷。不要使用JOIN操作。要借助恰当的SQL语句来降低与数据库的沟通频次。不要使用ORDER BY RAND()，需要换成其他处理手段。推荐采用高效的分页技巧来优化分页性能。统计表记录数据时应用COUNT星号符号，不宜使用COUNT圆括号空参数或COUNT圆括号数字一。禁止在子查询中实施后台维护与数据分析类指令。四点建议每张散列表存储数据不超过五千万条。可融合hash散列、range范围、table表等策略来构建散表。散表可以借助md5函数或其他类似哈希运算进行，表名需要加上十六进制后缀，例如。更推荐采用CRC32取模这类数学方法来散表，表名后缀用数字表示，数字需从零开始且宽度统一，比如要散布一百个表，后缀应为00到99。五点说明使用时间散表，表名后缀必须符合特定规范，例如按天分散或按月分散。五、进行其他大批量数据传输工作，必须由数据库管理员审核，同时全程监控服务运行状态，确保稳定；实施大规模数据变更操作，同样需要数据库管理员审核，并密切观察服务表现；当产品遭遇非数据库运维环节引发的问题或故障，例如网站被恶意抓取等情况，应迅速告知数据库管理员，以便维护服务连续性。业务环节系统发生故障等波及数据库运作的情况,务必迅速告知数据库管理员,以保障服务正常运行。业务环节的宣传活动,应预先告知数据库管理员实施服务与访问的检测。倘若因业务环节操作失误造成数据遗失,须进行数据复原,务必即刻通知数据库管理员,并递交精确时刻,操作失误指令等关键信息。FAQ

库名、表名、字段名需全部采用小写字母，并使用下划线进行分隔。MySQL中存在一个配置项es，其值不可调整，在linux系统上初始设定为0，表示库表名会按照实际输入来存储，并且对大小写敏感。若该参数设置为1，库表名则统一以小写形式保存，不再区分大小写。

b) 当同时使用大小写字母时,或许会出现abc,Abc,ABC等好几个表格并存的情况,这种情况很容易造成混淆。

字段名称在展示时会区分大小写，然而在操作时并不会考虑大小写差异，也就是说不能创建两个名称相同但大小写不同的字段。

d) 为了统一规范， 库名、表名、字段名使用小写字母。

库名、表名、字段名长度须在12字符以内。虽然系统允许最长使用64个字符，不过出于规范统一、识别便利以及降低传输负担的考虑，实际命名需严格控制在12字符之内。

库名、表名、字段名都应直观易懂,推荐采用名词而非动词。例如,用户评论这个表名就十分恰当。

库表是现实中的实体，一种具体事物，因此推荐采用名词来表述。

推荐采用特定的数据库引擎，因为5.5版本以后的系统，其原配的存储机制具备支持数据操作记录，能够进行事务处理，拥有更为精细的行级锁定机制，同时具备更强的数据恢复能力，在处理大量并发请求时效率更为出色，并且能够充分适配多核心处理器、大容量内存以及固态硬盘等先进的硬件设施，详细对比信息请参考附件中的官方技术文档。

1-5.存储精确浮点数必须使用替代FLOAT和。mysql里的非整数数值类型包括浮点数，比如float（单精度）和real（双精度），还有定点数，单精度浮点数的有效数字二进制长度为24位，换算成十进制是8位；双精度浮点数的有效数字二进制长度是53位，换算成十进制是16位，如果某个实数的有效数字超过8位，采用单精度浮点数表示就会导致偏差，同样，如果某个实数的有效数字超过16位，使用双精度浮点数表示也会造成偏差

计算机内部的浮点数采用二进制形式存储，然而将十进制数值转为二进制浮点数的过程中，常会引入偏差，这是因为并非所有数值都能转化为有限长度的二进制表达，即精确的十进制数未必能以二进制形式完整呈现，特别是含有小数部分的十进制数，往往难以用二进制精确表示。

删除表名若存在表名； 创建表表名字段值浮点型长度精度； 插入表表名值值点六七，值点六八； 查询表表名值； +-----------+ | 值 | +-----------+ | 点六七 | | 点六九 | +-----------+

推荐采用保存非负数的方式，相同的字节长度，能记录的数值幅度更宽，比如 有符号情况下的数值区间是从负128到正127，无符号情况下的数值区间是从0到正255

如何借助INT类型来保存IPv4地址，而不是采用char(15)格式，可以利用MySQL提供的函数完成转换过程。IPv6地址目前尚无对应的转换函数，需要借助其他方法实现存储，例如直接将字符串形式的IP地址（如'209.207.224.40'）存入相应字段。

INT，M值具体指代什么内容？留意括号中的数字仅表明显示长度，并不决定存储区间，例如INT(3)会默认呈现三位数，以空格填充，若超出则按实际显示，而java客户端等无法实现这种功能。

1-10.不建议使用ENUM、SET类型，使用来代替。ENUM类型存在三项不足之处: 新增枚举值需执行数据定义语言操作，存在默认值设置难题（当尝试插入枚举范围之外的字符串时，系统会将其转化为空字符串作为特殊错误标记），以及索引支持受限实例: 删除表时使用命令如 drop table if t; 创建表 t包含性别字段 sex 为枚举类型('0','1'); 向表插入数据时，先插入数字1，再插入字符串'3'; 执行查询操作后结果如下所示 +------+ | sex | +------+ |0 | +------+ 显示共两行数据（查询耗时0秒）

应尽量减少对TEXT和BLOB类型的运用，因为索引排序存在困难，只能通过设定字段长度或手动指定ORDER BY语句中的排序长度来实现排序

b) 该系统无法处理text和blog格式，会在存储设备上建立临时数据表

c) 可能浪费更多的空间

d) 可能无法使用 hash index

e) 导致使用where没有索引的语句变慢

是否会有额外存储空间产生？若M等于256，那么长度就需要用两个字节来记录。

选择表字符集为UTF8，a)utf8字符集下，汉字占用三个字节，而ASCII码字符依旧是一个字节。

b) 统一，不会有转换产生乱码风险

c) 海外地区的用户，例如美国、印度、台岛，无需配置简体中文功能，即可顺畅阅读您的文字，同时不会遭遇显示错误

d) ISO-8859-1编码占用了全部单字节容量，在兼容ISO-8859-1的环境里，处理其他编码的字节序列时不会将其舍弃。也就是说，其他编码的字节序列

采用可变长度字符串的存储方式，处理二进制字节序列，无需考虑字符编码的复杂性

建议采用这种格式记录时间，而不是那种，原因是它们都仅精确到秒，首选这个，因为它只需四个字节存储，而那个则需要更多字节。它还具备自动记录初始值以及随时间自动调整的功能。要利用它的自动记录初始值功能，可以这样做。

a) 将当前时间作为ts的默认值:ts 。

b) 当行更新时，更新ts的值:ts 0 ON 。

c) 可以将1和2结合起来:ts ON 。

建议将字段设置为不允许为空。a)倘若该字段被索引，则需额外占用1字节空间

b)使索引，索引统计，值的比较变得更复杂

c)可用0，''代替

d)如果是索引字段，一定要定义为not null

数据库里不能存放图片、文件等BLOB数据,使用分布式文件系统效率更高

MySQL的运行效率受制于索引设置，查询的快慢与索引设计密切相关，所以索引对提升性能作用显著。恰当的索引能够提升检索效率，无论是顺序访问还是随机查找，MySQL都会利用索引快速定位数据页。索引查找过程类似新华字典通过拼音和部首查字，当索引缺失时，只能逐页扫描数据。当MySQL查询不能使用索引时，MySQL会进行全

一张表的索引通常采用b+树结构来维护，当数据发生变动时，需要重新调整b+树，过多的索引会增加维护负担，导致更新操作变慢。

语句的执行过程能揭示MySQL如何处理指令,具体表现为能否应用索引,是否需要遍历全部数据,是否借助临时数据结构,以及是否涉及排序操作等细节。应尽量避免让MySQL执行全表数据检索,频繁使用临时表,或是进行排序运算。详细说明请参阅官方文档。

不建议采用带有百分号的模糊检索，比如使用LIKE “%weibo”这种形式，这样会引起整个数据表进行全面的数据检索。

对超过五十个字符的字段实施索引，可先往表中添列，随后进行索引构建，接着缩减索引字段尺寸，以此提升运作效能。 TABLE url(......url（255）非空 0,INT非空 0,......索引()）

什么是索引覆盖的概念？在数据库引擎层面，非主键索引不直接记录行位置，而是存放主键的值。当用户查询非索引字段的数据时，必须先借助索引找到主键的数值，接着再利用主键去检索其他相关数据，这个流程导致查询过程需要执行两次操作。覆盖索引的含义在于，查询能够通过单个索引来达成，覆盖索引的运行速度通常很快，使用主键进行检索本身就是一种覆盖索引的表现。科学地建立索引，并且恰当运用查询指令，一旦查询触发了覆盖索引，就能得到明显的速度加快。例如查询邮箱和用户标识，依据用户标识进行筛选，倘若用户标识并非核心字段，那么在合适阶段应当为用户标识和邮箱建立索引，借此提升查询效率。

语句应当避免应用LIMIT，或者，这样或许会造成主次信息的不匹配

b) 会记录到错误日志，导致日志占用大量空间

为什么要防止MySQL执行隐式类型转换呢？当MySQL进行这种转换时，可能会把索引列的数据类型改成等于号右侧参数的类型，结果就是无法利用索引，这与在索引字段上应用函数造成的影响是相同的。

不建议运用它，因为它会引发额外的资源消耗，比如处理器、输入输出、内存以及网络频宽；它还容易导致索引失效的情况出现；一旦数据库的架构有所调整，相关的程序代码也必须跟着修改。

怎样降低同数据仓库的通信频次？借助下列指令能够削减同数据库的联络频次：...依据键值关联，将数据归入指定位置，(),()怎样整合多种角度来分散数据表和数据仓库？比如在推特平台，先通过crc32()函数将数据分配到16个不同的数据库，再针对每个数据库里的数据表，每日建立一个新的数据表。

无法运用ORDER BY rand()的原因在于，该方式会从存储设备调取信息，执行排列操作，因而耗费大量输入输出与处理资源，若在软件层面取得随机数，再据此从数据源提取相应数据，则更为高效。

3-15. MySQL中如何进行分页?

若遇到如下分页指令: * 从表内查询数据并按时间降序排列取10000至10010条记录; 此种分页方法将引发频繁的磁盘访问,由于MySQL采用预读机制。 更优的分页方案: * 根据时间字段筛选表内数据

不应用结构繁杂的SQL语句，把一条长SQL分解为多个简短SQL逐一运行。这样做的原因在于，简短的SQL更便于被MySQL的查询缓存所利用，同时能够有效缩短锁定表的时间，并且可以充分利用多核处理器的性能。

存储引擎为何要防止使用计数星号功能，实时性要求高的计数统计可以考虑采用或者借助redis系统，非实时性统计则可以运用独立统计表格，并设定周期性刷新机制进行更新。