数据库表设计思考

（一）：字段设计规范和命名规范

一、设计规范

1.1.是否需要自增ID？

数据库表，一定要有id，而且要用自增id！

有些人喜欢用自定义的，用UUID或者其他七七八八的id，如果在架构设计，代码比较好的情况下，不会出啥大问题，但是一旦代码写的不行，极有可能就造成id重复之类的问题。

自增id另外还有一个好处，就是在数据迁移的时候，分页查询通过id来进行分页，速度会比传统分页快很多。

这个字段还是要有的，但是强烈建议不要在删除行数据，查询数据，修改数据时使用到该字段，因为该字段的单独操作会破坏掉数据的隔离性。也就是前面所说的，所有的sql操作，都要带上租户id再进行。

自增ID规范
  • 则表中的第一个id字段一定是主键且为自动增长；
  • 建议主键是整型,最好是unsigned bigint类型，避免后续需要扩展
  • 为主键选择更有意义的名称，如ID这个名称太过笼统，表达的信息可能不准确。

1.2是否使用备用字段？

在数据表中，不仅设计了当前所需要的字段，而且还在其中留出几个字段作为备用。
比方说，设计了一个人员表（Person），其中已经添加了各种必要的字段，包括姓名（Name）、性别（Sex）、出生年月日（birthday）等等。大功告成之后，忽然想到，将来系统中应该还会有很多其它与人相关的内容吧，比方说毕业院校，比方说工作单位等等，尽管现在根本不需要填写，以后可能还是会用到的吧。拍脑袋一项，那就加入5个varchar2型的字段，分别叫做Text1、Text2……Text5，然后又想，应该还有一些日期型的字段需要备用，就又建立了三个date型的字段，分别起名叫做date1、date2、date3。

【解决方案】

其实上面的这种设计方式就是一种“过度设计”，我们应该做的就是“按需设计”。

因此要禁止在表中建立预留字段，理由如下：

1.无法准确的知道预留字段的类型，所以无法选择合适的类型。

2.无法准确的知道预留字段中所存储的内容，预留字段的命名很难做到见名识义

3.后期维护预留字段所要的成本，同增加一个字段所需要的成本是相同的。对预留字段类型的修改，会对表进行锁定。（修改一个字段的成本，大于新增字段）。

推荐解决办法是，当需要增加相关的信息的时候：

  1. 如果数量很少，而且信息的性质与原表密切相关，那么就可以直接在原表上增加字段，并将相关的数据更新进去；
  2. 如果数量较大，或者并非是原表对象至关重要的属性，那么就可以新增一个表，然后通过键值连接起来；
  3. 对于表的数据的存储位置所导致的性能问题，我们可以通过在特定时间对数据库的数据进行重组来解决，而这项工作对于长期运行的数据库来说，也是需要定期进行的。

1.3是否使用外键、触发器和存储过程？

外键、触发器不要有。 数据的完整性靠程序来保证。

触发器和存储过程容易将业务逻辑和DB耦合在一起。

有了外键、触发器，你会发现： 写代码不方便。 订正数据不方便。 迁移数据也麻烦。 总之，你要是坚持用，后续的坑等着你。

虽然不建议使用外键约束，但是相关联的列上一定要建立索引。

1.4.表应该具备哪些字段？

自增ID

id必为主键，类型为unsigned bigint、单表时自增、步长为 1。若业务场景需要或未来有分库分表扩展需求，类型为unsigned bigint，建议采用唯一id设计，如SnowFlake雪花ID算法，请最后考虑UUID。

创建时间&修改时间

创建时间create_time和修改时间update_time这两个字段，每个表都必须有！ 数据类型为datetime。
注意，一定要用数据的时间戳，自动生成。不要通过代码去操作这两个字段。推荐使用AOP去自动处理。

有了这两个字段。你可以追溯到数据的时间点，创建和修改的时间点。极大方便你在某些情况下的排查数据问题。

建议时间精确到毫秒级别，因为在大数据量的情况下，可能一秒有几十、几百、上千、上万的数据新增都是有可能的。那么秒级在这种情况下完全就不够看了，选择毫秒级别是一个比较好的选择。

创建人&修改人

创建人create_by和修改人update_by两个字段，每个表也都必须有。数据类型为unsigned bigint（关联用户id）或varchar（关联登录名，确保不变）
还是和前面一个原因，出问题的时候可以追溯起因，否则遇上日志过久无法查看或者其他原因出现未知数据，都不知道数据怎么来的，需要花非常大的代价查看日志、代码等。

软删除

只能逻辑删除，不能物理删除，重要！！！

CREATE TABLE `database_name`.`table_name`  (
  `id` bigint(18) UNSIGNED NOT NULL COMMENT '雪花算法id IdGenerate.generateId();',
  `create_by` varchar(20) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_user` varchar(20) DEFAULT NULL COMMENT '修改人',
  `update_by` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '逻辑删除标志0有效，1无效',
  PRIMARY KEY (`id`)
);

1.5范式与反范式

尽量遵守第三范式的标准（3NF）：表内的每一个值只能被表达一次；表内的每一行都应当被唯一的标示；表内不应该存储依赖于其他键的非键信息。

表与表之间的信息,用id进行关联，尽量不要有冗余的信息数据，否则你需要更新同一份信息的时候，需要更新多个地方。

但是在某些情况下，你确认信息不会经常变动，且该信息确实在两个表中都有会比较好，那么，放心的去冗余吧。但是注意，数据的更新用上事务。

查多改少的场景，适合用字段冗余。还是上面的例子：订单详情的查询很多，但是商品名称的修改很少，适合将商品名称冗余到订单表中

字段允许适当冗余，以提高查询性能，但必须考虑数据一致性。冗余字段应遵循以下原则。
1）不是频繁修改的字段。
2）不是唯一索引的字段。
3）不是varchar超长字段，更不能是text字段。

1.6存储引擎和国际化

所有表必须使用Innodb存储引擎，5.6以后的默认引擎，支持事务，行级锁，更好的恢复性，高并发下性能更好。如无说明，建表时一律采用innodb引擎；

数据库和表要使用统一的字符集（如：UTF8），统一字符集可以避免由于字符集转换产生的乱码，MySQL中的UTF8字符集汉字点3个字节，ASCII码占用1个字节。
字符集：utf8mb4、排序规则：utf8mb4_general_ci

1.7 关于分库和分表

当单表行数超过500万或者单表容量超过2GB时，才推荐分库分表。

二、命名规范

2.1数据库表名规范

（1）表名、字段名必须使用小写字母或数字

MySQL在Windows系统中不区分大小写，但在Linux系统中默认区分大小写。

正例：aliyun_admin,level_name
反例：AliyunAdmin,levelName
（2）表名不使用复数名词。

表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应到DO类名也是单数形式，符合表达习惯。

正例：user,employee
反例：users,employees
（3）表的命名最好遵循“业务名称_表的作用”原则
正例：alipay_task，trade_config
反例：yy_all_live_category、yy_alllive_comment_user。
//说明：去除项目名，统一命名规则，均为”yy_alllive_”开头即可。
（4）表的名称一般使用名词或者动宾短语（动宾逻辑顺序统一）。
错误示例：yy_showfriend、yy_user_getpoints、yy_live_program_get。
//说明：去除项目名，统一命名规则，动宾短语分离且动宾逻辑顺序统一。
（5）表名尽量要用英文单词的全拼

不要自己对英文单词进行缩写，也不要使用汉语拼音和小众的英文缩写。可以使用常见的其意义被大众熟知的英文缩写或英文字典中定义的缩写。

数据库对象的命名要能做到见名识义，并且最好不要超过32个字符。

表名称不应该取得太长（一般不超过三个英文单词）

（6）明细表的名称为：主表的名称+字符dtl（detail缩写）

例如：采购定单的名称为：po_order，则采购定单的明细表为：po_orderdtl。

（7）临时库/表必须以tmp为前缀并以日期为后缀。
（8）备份库/表必须以bak为前缀并以日期为后缀。
（9）通用表要加前缀“all_”，示例：all_user。
（10）表必须填写描述信息（使用SQL语句建表时）
（11）一个项目一个数据库，多个项目慎用同一个数据库。

2.2数据库字段命名规范

（1）字段必须填写描述信息。
（2）当修改字段含义或追加字段表示的状态时，需要及时更新字段注释。
（3）多个单词使用下划线’_'分隔

采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线’‘组成，命名简洁明确，多个单词用下划线’'分隔。

反例：username、userid、isfriend、isgood。
//说明：使用下划线进行分类，提升可读性，方便管理
//修改为“user_name”、 “user_id”、 “is_friend”、 “is_good”。
（4）全部小写命名，禁止出现大写。
反例：userID、houseID。
//说明：使用统一规则，修改为“user_id”、“house_id”。
（5）禁用保留字，如name、desc、range、match、delayed等。

请参考MySQL官方保留字。

（6）字段名称一般采用名词或动宾短语。
名词示例：user_id、user_name、sex；动宾短语示例：is_friend、is_good。
（7）命名字段时要用英文单词的全拼

不要自己对英文单词进行缩写。也不要使用汉语拼音和小众的英文缩写。可以使用常见的其意义被大众熟知的英文缩写或英文字典中定义的缩写。

字段命名使用完整名称，禁止缩写

反例：uid、pid。
//说明：使用完整名称，提高可读性，修改为“user_id”、“person_id”。
（7）禁止在命名字段时，重复表的名称

例如，在名employe的表中禁止使用名为employee_lastname的字段。

（8）禁止在命名字段时，包含数据类型。
（9）表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint（1表示是，0表示否）。

说明：任何字段如果为非负数，则必须是unsigned。注意：POJO类中的任何布尔类型的变量，都不要加is前缀，需要在中设置从is_xxx到xxx的映射关系。数据库表示是与否的值，使用tinyint类型，坚持is_xxx的命名方式是为了明确其取值含义与取值范围。

正例：表达逻辑删除的字段名is_deleted，1表示删除，0表示未删除。

2.3数据库字段类型规范

（1）如果存储的字符串长度几乎相等，则使用char定长字符串类型。

例如，11位手机号，邮编(postcode)。

这种固定长度的纯数字，也不要用int类型或long类型，因为只有数字参与了运算，才用数值型。

（2）小数类型为decimal，禁止使用float和double类型。

在存储时，float和double类型存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过decimal的范围，那么建议将数据拆成整数和小数并分开存储。

（3）整型int定义中不添加长度

比如使用INT，而不是INT(4)。

（4）varchar是可变长字符串，不预先分配存储空间

禁止使用varchar类型作为主键。

长度不要超过5000个字符，如果存储长度大于此值，则应定义字段类型为text，独立出来一张表，用主键来对应，避免影响其他字段的索引效率。

（5）禁止使用blob、text类型保留大文本、文件、图片

建议使用其他方式存储，MySQL只保存指针信息。

一列需要占很大空间的字段，一定要单独拎出来，不要和常用信息放一张表。

举个例子： 文章的信息和文章的内容，这一定要分成两个表。否则会给你的文章性能带来极大的挑战。因为很多情况下，查看文章列表，根本不需要查看到文章的内容。

（6）所有字段在设计时，必须有默认值

字符型的默认值为一个空字符值串‘’，数值型的默认值为数值0，逻辑型的默认值为数值0。

系统中所有逻辑型中数值0表示为“假”，数值1表示为“真”，datetime、smalldatetime类型的字段没有默认值，必须为NULL。

除以下数据类型timestamp、image、datetime、smalldatetime、uniqueidentifier、binary、sql_variant、varbinary外

（7）IP地址使用unsigned int类型。

这样比较节约存储空间

select  INET_ATON('192.0.0.0')
select INET_NTOA(3221225472)
（8）避免使用NULL字段

NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效

（9）多表中的相同列，必须保证列定义一致。
（10）不建议使用ENUM类型，使用TINYINT来代替。

1)、假如一个设计不合理的ENUM字段，给程序员带来的就完全是梦魇了，比如一个enum字段的范围是(‘0’,‘1’,‘2’,‘3’,‘4’,‘5’)，而enum的枚举值对应的索引是从1开始的，因此，insert into table (enum)values(1)，插入的并不是1，而是0

(2)、另外假如你在设计好enum的枚举字段范围并使用了一段时间后，再到字段范围中加一个枚举值，并且不是加在最后，那么也就相当于把原来的范围都改变了索引值，也就是当你在查询的时候直接查询值(并加上单引号)，将不会使用enum自身隐藏的索引值来获取结果了

(3)、如果是纯数值型，还是建议采用tinyint字段吧，毕竟它也只占一个字节，即使出现赃数据，也可以被接受，不象enum，如果纯数字型范围，更改了索引，你就不知道你查询的值是否正确了

(4)、如果字段是字符串，并且长度固定，可以尝试用char，如果是数值型，还是用tinyint吧，比较安全稳定，而且即使迁移，也不会出现太多问题

（二）：索引规范和SQL语句规范

一、索引规范

1.索引名称规范

（1）索引名称必须使用小写。

主键索引名为pk_字段名，唯一索引名为uk_字段名，普通索引名则为idx_字段名。d

pk_即primary key，uk_即unique key，idx_即index的简称。
（2）索引字段不能为空
（3）组成索引中的字段数建议不超过5个。单张表的索引数量控制在5个以内。

2.索引创建规范

（1）重要的SQL必须被索引
  • UPDATE、DELETE语句的WHERE条件列
  • ORDER BY、GROUP BY、DISTINCT的字段
  • 多表JOIN的字段

UPDATE、DELETE语句不使用LIMIT

（2）ORDER BY，GROUP BY，DISTINCT的字段需要添加在组合索引的后面。

如果有order by的场景，请注意利用索引的有序性。order by最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。

正例：where a=? and b=? order by c索引为：a_b_c
反例： 索引中有范围查找，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b， 索引a_b无法排序。
（3）建组合索引的时候，区分度最高的在最左边。
正例： 如果where a=? and b=?，a列的几乎接近于唯一值，那么只需要单建idx_a索引即可。
说明： 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。
如：where a>? and b=?那么即使a的区分度更高，也必须把b放在索引的最前列。
（4）选择区分度大的列建立索引，组合索引中，区分度大的字段放在最前；
（5）不在低基数列上建立单独索引

例如“状态”，“性别“只有男、女两种；可以建立联合索引，应该将低基数列放在后面；

（6）业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但会明显提高查找速度；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，就必然有脏数据产生。

（7）主键的选择要慎重 【 强烈建议】
  • 首选使用非空的唯一键， 其次选择自增列或发号器
  • 不使用更新频繁的列，尽量不选择字符串列，不使用UUID MD5 HASH、
（8）唯一键由3个以下字段组成，并且字段都是整形时，使用唯一键作为主键。
（9）在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

对过长的varchar字段建议优先考虑前缀索引，前缀索引长度不超过12个字符;

什么是前缀索引？
前缀索引也叫局部索引，比如给身份证的前 10 位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引。
为什么要用前缀索引？
前缀索引能有效减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。但前缀索引也有它的缺点，不能在 order by 或者 group by 中触发前缀索引(索引会失效,group by 是去重)，也不能把它们用于覆盖索引。
什么情况下适合使用前缀索引？
当字符串本身可能比较长，而且前几个字符就开始不相同，适合使用前缀索引；相反情况下不适合使用前缀索引，比如，整个字段的长度为 20，索引选择性为 0.9，而我们对前 10 个字符建立前缀索引其选择性也只有 0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，就没有创建前缀索引的必要了。

（10）页面搜索严禁左模糊或者全模糊

如果需要，那么请通过搜索引擎来解决。不建议使用%前缀模糊查询，例如LIKE “%weibo”。

（11）唯一键不和主键重复。
（12）使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，UsingTemporary。

二、SQL语言编码规范

1.SQL名称规范

（1）所有关键字必须大写

如：INSERT、UPDATE、DELETE、SELECT及其子句，IF……ELSE、CASE、DECLARE等。

（2）所有函数及其参数中除用户变量以外的部分必须大写。
（3）在定义变量时用到的数据类型必须小写。
（4）对于数据库中表记录的查询和变更，只要涉及多个表，就需要在列名前加表的别名（或表名）进行限定。

说明：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在，就会抛异常。

正例：select t1.name from table_first as t1, table_second as t2 where t1.id=t2.id;
反例：如果没加别名限制，在另一个表中增加了一个同名字段
线上查询可能会导致1052异常：Column 'name' in field list is ambiguous。

在这里插入图片描述

（5）SQL语句中表的别名前加as，并且以t1、t2、t3……的顺序依次命名。

说明：1）别名可以是表的简称，或者是表在SQL语句中出现的顺序，以t1、t2、t3……的方式依次命名。2）在别名前加as可使别名更容易被识别。

2.SQL语句规范

(1）超过三个表禁止join。

需要join的字段，数据类型必须绝对一致；当多表关联查询时，保证被关联的字段需要有索引。
说明：即使双表join，也要注意表索引、SQL性能。

(2)不要使用count(列名)或count(常量)来替代count(*)

count(*)是SQL92定义的标准统计行数的语法，与数据库无关，与NULL和非NULL无关。

不要使用count(列名)或count(常量)来替代count()，count()是SQL92定义的标准统计行数的语法，与数据库无关，与NULL和非NULL无关。

count(distinct column) 计算该列除NULL外的不重复行数。注意，count(distinct column1, column2)，如果其中一列全为NULL，那么即使另一列有不同的值，也返回为0。
当某一列的值全为NULL时，count(column)的返回结果为0，但sum(column)的返回结果为NULL，因此使用sum()时需注意避免NPE问题。

正例：可以使用如下方式避免sum的NPE问题：SELECT IFNULL(SUM (column), 0) FROM table;
(3)使用ISNULL()判断是否为NULL值。

说明：NULL与任何值的直接比较都为NULL。

1）NULL<>NULL的返回结果是NULL，而不是false。
2）NULL=NULL的返回结果是NULL，而不是true。
3）NULL<>1的返回结果是NULL，而不是true。

(4)WHERE条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引。

尽量不要使用!=，大部分情况会跳过索引

正例：where id < 7 and id > 8
反例：where id != 7 and id !=8

尽量不要使用OR、IN运算符，大部分情况会跳过索引

in操作能避免则避免，若实在避免不了，则需要仔细评估in后面的集合元素数量，控制在1000之内。
可使用UNION ALL代替OR、JOIN或EXISTS代替IN

尽量不要使用IS NULL和IS NOT NULL，大部分情况会跳过索引

可使用范围查询或存入-1代替NULL

3.SQL操作规范

（1）当订正数据（特别是删除或修改记录操作）时，要先select，避免出现误删除，确认无误才能执行更新语句
（2）利用覆盖索引进行查询操作，避免回表。

说明：如果想知道一本书的第11章是什么标题，我们有必要翻开第11章对应的那一页吗？只要浏览一下目录就好，这个目录就起到覆盖索引的作用。

正例：能够建立索引的种类分为主键索引、唯一索引、普通索引3种，而覆盖索引只是查询的一种效果
用explain的结果，extra列会出现“using index”。

3.ORM规范

（1）在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。

1）增加查询分析器解析成本。
2）增减字段容易与resultMap配置不一致。
3）多余字段增加网络开销，尤其是text类型的字段。

（2）POJO类的布尔属性不能加is，而数据库字段必须加is_，要求在resultMap中进行字段与属性之间的映射。
（3）不要用resultClass作为返回参数，即使所有类属性名与数据库字段一一对应，也需要定义；反过来，每个表也必然有一个与之对应。
（4）sql.xml配置参数使用：#{}，#param#，不要使用${}，此种方式容易出现SQL注入。
（5）不允许直接将HashMap与Hashtable作为查询结果集的输出。
反例：某工程师为避免写一个<resultMap>xxx </result Map>，直接使用HashTable接收数据库返回结果
结果由于数据库版本不一样，出现日常把bigint转成Long值，而线上把bigint解析成BigInteger的现象，导致线上出现问题。
(6)不要写一个大而全的数据更新接口。

传入为POJO类，不管是不是自己的目标更新字段都进行update table set c1=value1,c2=value2,c3=value3; 是不对的。当执行SQL时，不要更新无改动的字段，一是容易出错；二是效率低；三是增加binlog存储。

高性能MYSQL（一）schema和数据类型

良好的逻辑设计和物理设计是高性能的基石，应该根据要查询的的语句设计Schema（模式）。

在 MySQL 中，schema 是数据库的逻辑容器，它用于组织数据库中的表。每个数据库都有一个默认的 schema，称为 public 。你可以创建自己的 schema，并在其中创建表。

优化的方向有哪些？

1.优化数据类型

（1）尽量选择最小的数据类型。更小的数据类型通常更快，CPU和内存占用更少。
（2）尽量选择简单的数据类型。例如，整型比字符操作代价更低。
（3）尽量避免null值。理由如下：

查询性能下降：如果一个列允许 null 值，那么在执行查询时，数据库必须检查该列是否为 null。这可能会降低查询性能。

使用更多存储空间：因为数据库需要存储一个额外的位来表示该列是否为 null。例如，如果一个列是 VARCHAR(255) 类型，并且允许 null 值，那么数据库需要存储 256 位来存储该列的值。如果该列不允许 null 值，那么数据库只需要存储 255 位来存储该列的值。 当可为null的列被索引时，需要额外1个字节。

通常把NULL的列改为NOT NULL对性能的提升较小，这应该最后考虑。

1.1 整数类型

整数类型有可选的unsigned类型，表示不允许负值，可将正整数范围提高一倍。有和无符号类型具有相同的存储空间和性能，可以根据实际情况选择合适的类型

1.2 实数类型

float和double用于浮点近似计算。
decimal用于小数精确计算，同时可以存储比bigint更大的数。
但是，因为decimal大开销大，在存储数据量比较大的财务数据时，可以考虑将原数扩大100万倍，用bigint代替decimal。

1.3 字符串类型

varchar存储可变长字符串。需要1或2字节额外存储记录字符串长度。适合存储最长长度比平均长度大的字符串。
char存储固定长度字符串。适合固定长度较短的字符串。

1.4 blob和text类型

BLOB 和 TEXT 都是 MySQL 中用于存储大字符串的类型。BLOB 是二进制大对象，而 TEXT 是文本大对象。
BLOB 和 TEXT 的最大长度都没有限制，但在实际使用中，它们的最大长度通常是 4GB。

尽量避免使用 BLOB 和 TEXT 类型。如果确实需要使用它们，则应尽量将值的大小控制在合理范围内。

  • 如果 BLOB 和 TEXT 类型的值太大，则可以使用外部表来存储它们。
  • 可以使用索引来提高 BLOB 和 TEXT 类型的查询性能。
  • 可以使用压缩来减少 BLOB 和 TEXT 类型的存储空间。

1.5 日期和时间类型

datetime保存从1001年到9999年之间的时间，精度为秒。datetime 类型的值需要占用 8 个字节。
timestamp保存从1970年到2038年之间的时间，精度为秒。，而 timestamp 类型的值只需要占用 4 个字节。

1.6 位数据类型

mysql把bit当成字符串类型而不是数字类型来处理。

1.7 为标识列选择数据类型

（1）通常选整数类型做为标识列
（2）不要选枚举类型作为标识列
（3）选字符串类型作为标识列比较吃性能。其中，若使用UUID作为标识列，则建议去掉“-”符号。更好的做法是UNHEX()转换为16位数字存在binary(16)中，获取的时候HEX()再转化为16进制。

1.8 存储IP的数据类型

MySQL 提供了两种工具来存储 IP：

  • INET_ATON() 函数将 IP 地址字符串转换为整数。
  • INET_NTOA() 函数将整数 IP 地址转换为字符串。
SELECT INET_ATON('192.168.1.1');
SELECT INET_NTOA(192168101);

2.优化的常见错误有哪些？

（1） 设计了太多的列
（2）进行了太多的关联
（3） 过度使用枚举

3.关于范式和反范式的选择？

3.1 关于范式

以“员工，部门，部门领导”的表为例：

employee department head
郑大致  市场部   郑大致
王小明  销售部   王小明
李小花  市场部   郑大致
张小军  销售部   王小明

该表中当部门领导被换时，要修改多行的数据。此外，若表中没有员工，就没有部门的数据。

可以拆成员工表和部门表

employee department
郑大致  市场部
王小明  销售部
李小花  市场部
张小军  销售部
department head
市场部   郑大致
销售部   王小明

这样的表符合第二范式。

范式的优缺点：

数据库表设计中的范式是用来规范化数据结构的一种方法。

优点包括：

  1. 数据冗余减少：范式设计可以消除或最小化数据冗余，确保每个数据只在数据库中存储一次。这样可以节省存储空间，并提高数据的一致性和更新效率。

  2. 数据一致性提高：范式设计通过将数据分解为更小的、更规范的组件，可以提高数据的一致性。这意味着每个数据只有一个位置存储，避免了数据不一致的情况。

  3. 数据更新更快：由于数据不重复存储，更新数据变得更加简单和高效。只需更新一处数据，就可以确保整个数据库中的数据是最新的。

  4. 查询性能优化：范式设计可以通过合理地拆分数据表，提高查询性能。每个表只包含特定的数据，可以更快地执行查询操作。

缺点包括：

  1. 查询复杂性增加：范式设计可能会导致查询变得更加复杂，需要进行多个表的连接和关联操作。

  2. 索引失效：范式化可能将不同的列放在不同的表中，如果这些列在同一表中可能属于同一索引。

3.2 关于反范式

假设有一个需求是查看付费用户最近的10条消息

//user表
id user_name account_type
1  张三  付费用户
2  里斯  免费用户
//message表
message_text  published  user_id
我会帮助你解决问题。  2023-10-1 19:01:02   1
晚点回复你。     2023-10-1 18:55:43   1

查询语句如下：

select message_text,user_name from message 
inner join user on message.user_id=user.id
where user.account_type='付费用户' 
order by message.published desc limit 10;

一个有效的查询扫描message表published的索引，同时要检测用户是不是付费用户。这是效率低下的方法。

若是将account_type冗余放在message表中，则不需要关联，这将非常高效。

//user_message表
message_text  published  user_id account_type 
我会帮助你解决问题。  2023-10-1 19:01:02   1 付费用户
晚点回复你。     2023-10-1 18:55:43   1 付费用户
select message_text,user_name from user_message 
where account_type='付费用户' 
order by published desc limit 10;

反范式的优缺点：
数据库表设计中的反范式化是一种优化技术，它追求提高查询性能和降低数据冗余的目标。

优点包括：

  1. 提高查询性能：反范式化可以将相关数据放在一个表中，减少了联接操作，从而提高了查询的性能。
  2. 简化数据模型：反范式化可以简化数据模型，减少了表之间的关联关系，使数据结构更加直观和易于理解。
  3. 减少数据冗余：通过将相关数据集中存储在一个表中，可以减少数据的冗余，节省存储空间。

缺点：

  1. 数据冗余增加了数据更新的复杂性：当数据冗余增加时，对数据的更新操作可能会变得复杂，需要保证冗余数据的一致性。
  2. 可能引发数据不一致：由于数据冗余，如果数据更新不正确或不同步，可能导致数据不一致的问题。
  3. 可能影响数据的完整性：反范式化可能导致数据的完整性约束变得更加复杂，需要额外的措施来确保数据的完整性。

需要根据具体的应用场景和需求来权衡反范式化的利弊，确保在设计数据库表时能够达到预期的性能和数据一致性要求。

4.关于衍生值的存储

例如，查询某个用户发送过多少信息，可以执行一个昂贵的子查询，也可以在user表中新建一个num_messages列，每次更新该值。
然而，有时需要创建一个完整的缓存表或汇总表。

create table msg_per_hr {
	hr datetime not null,
	cnt int unsigned not null,
	primary key(hr)
}
select sum(cnt) from msg_per_hr 
where hr between concat(left(now(),14),'00:00')-interval 23 hour
and concat(left(now(),14),'00:00')-interval 1 hour

例如，以上是保存每个小时发送的消息数，若获取过去24小时的消息数，不用进行昂贵的查询，可以直接查询较小的统计表。

5.关于计数表

如果在表中有一个用于计数的列，当进行更新的时候会遇到并发问题。
例如，记录一个网站的点击次数。

create table hit_count{
	cnt int unsigned not null
}

网站的每次点击都会导致对计数器的更新

update hit_count set cnt = cnt +1;

对于任何想要更新这一行的事务来说，这条记录上都有一个全局的互斥锁。使得事务只能串行执行。

要获得更高的并发性能，也可以将计数器保存在多行中，每次选择随机一行更新。

create table hit_count{
	slot tinyint unsigned not null primary key,
	cnt int unsigned not null
}
update hit_count set cnt = cnt +1 where slot = rand()*100;

可以提前增加100行数据。然后选择随机一行进行更新。
一个常见需求是每隔一段时间要重新开始一个计数器。

create table daily_hit_count{
	day date not null,
	slot tinyint unsigned not null primary key,
	cnt int unsigned not null
}
insert into daily_hit_count(day,slot,cnt)
values(current_date,rand()*100,1)
on duplicate key update cnt = cnt +1

如果怕随着时间的发展表的行数变大，可以做定时任务，将所有记录合并到0号槽中，并删除其他槽位。

数据库表设计：基于业务场景的用户表的设计

本站系统的用户登录设计

通常作为本站系统的登录，有三种设计方式：

  • 用户名+密码
  • 邮箱+密码
  • 手机号+密码

那么该如何设计登录方式呢？

1.三种登录方式，功能是否重复？是否可以只实现其中一种？

功能并不重复。看情况实现。
理由如下：

  • 网站还是需要用户名，用于区别内部用户。
  • 当提及某个用户时，邮箱和手机号，用户可能不想公开。而提及昵称时，昵称可以重复，不能作区分。
  • 用户名一旦选定不允许更改，邮箱和手机号作为第三方的系统，应该允许更改。
    在这里插入图片描述

2.什么时候可以只用邮箱或手机进行登录？

邮箱或手机号是唯一的，但也同时具有“个人联系方式”的特点，与系统生成的UID具有不同的含义，个人应该具有控制自己的信息是否暴露在公共空间的自由。

当与其他用户的交互需求不高，即没有社交需求时，邮箱或手机号可以当做“户头”使用，比如银行、医院、工作站。

而像论坛、游戏等社交需求高的系统，作为“人”，应该保留用户名登录。

除非规定昵称必须唯一，此时可通过昵称“@”提及他人，那么也可以不实现用户名登录。

3.是否允许邮箱或手机号进行更改？

邮箱或手机号是可以更换的，虽然每个邮箱标记着一个单独的联系方式，并且互不重复，但是邮箱作为个人可变信息，应当允许消亡或者发生变动，甚至可以冒名顶替。

因为邮箱这个资产的控制者不属于当前信息系统，是一个第三方的（当然也可以是本系统提供的邮箱服务，但是从当前用户系统来看，属于第三方）。

用户名的控制权却完全属于当前信息系统。

**一个运行良好的信息系统，显然不应该为第三方系统的长久存在作任何担保。**但是它可以确定用户名，以及用户名内部对应的 UID 确是完全自己控制，并且决定处理策略的。

例如：QQ号作为该系统的用户名UID，是随机生成的，没有与其他第三方系统挂钩。然而，部分腾讯的游戏只支持QQ号或QQ邮箱登录，这时因为他们属于同一公司，可以保证系统可靠。可以不设置用户名+密码的方式登录。
而像yahoo邮箱面临关闭这样的事件，若自己的系统只设置邮箱登录，通过此邮箱找回密码就面临失效。

第三方登录的设计

设计案例1

参考：浅谈数据库用户表结构设计，第三方登录的设计如下：

users
|id|nickname|avatar|
|1|慕容雪村|http://…/avatar.jpg|
|2|魔力鸟|http://…/avatar2.jpg|
|3|科比|http://…/avatar3.jpg|
user_auths
|id|user_id|identity_type|identifier|credential|
|1|1|email|123@example.com|password_hash(密码)|
|2|1|phone|13888888888|password_hash(密码)|
|3|1|weibo|微博UID|微博access_token|
|4|2|username|moliniao|password_hash(密码)|
|5|3|weixin|微信UserName|微信token|

该思路是将用户授权和用户信息分开。用户信息好理解，需要的信息都放这种表中就行了。
而用户授权表中，id主键自增，user_id可重复，identifier为唯一键。
登录验证过程如下：
1.通过正则验证或入口第三方登录验证，判断登录类型，比如，手机号。
2.通过sql查询语句SELECT * FROM user_auths WHERE type=’phone’ and identifier=’手机号’取出该条目，并进行验证密码
3.验证通过后取出user_id的用户信息

该设计的优点：

  • 站内登录类型无限扩展，无需更改表结构
  • 手机验证和邮箱验证由原来的两个验证字段phone_verified 和 email_verified，变为一个verified字段即可。而第三方登录验证都默认已认证。
  • 可绑定任意数量的同类型验证方式，比如多个手机号登录，多个微信或邮箱登录同个账号。或者规定只能登录一条。
  • 可在前端做到“无需注册本站帐号”的效果，不用再注册一遍本站账号。
  • 手机或邮箱作为联系方式，仍然可以在用户信息表中作为字段显示。

该设计的缺点：

  • 由1次SQL变成2次SQL
  • 当改密码时，邮箱、用户名、手机号等等的密码要一起改，否则就变成了邮箱+新密码，手机号+旧密码访问了，解决方式是增加字段，区分本站账号和第三方登录账号。
  • 代码量增加了，逻辑判断更复杂了。

根据上述分析，最终设计表结构如下：

|id|nickname|avatar|sex|birthday
|1|慕容雪村|http://…/avatar.jpg|男|19980101

user_auths
|id|user_id|identity_type|identifier|credential|verified|is_self
|1|1|email|123@example.com|password_hash(密码)|1|1
|2|1|phone|13888888888|password_hash(密码)|1|1
|3|1|weibo|微博UID|微博access_token|1|1

设计案例2

根据上述设计案例，我认为这种数据库表结构设计并不让人十分满意。
他将用户授权和用户信息表分开的思路，我十分认可。然而，用户授权表太复杂了，逻辑和代码量增大了，意义含糊不清。
由于用户登录时，要考虑验证码，用户输入密码错误次数，用户是否被禁用，这些都与本站系统有关。
因此考虑一个用例对应一个数据表，从本站输入账号密码和从第三方登录获得授权属于不同的用例，本站登录与第三方登录的数据表应该分开。

为什么使用第三方登录？

我们主要考虑，用户注册或登录时，不愿意设置密码，不愿意填各种资料。而用户在此之前却已经有QQ账号、微信账号等唯一openid。

基于对这些系统的信任，可以通过唯一openid作为登录本站系统的凭证，并获得附带的信息（昵称、头像等）。

所以第三方登录有以下意图：

  • 绑定后的账户，在忘记本站账号密码的情况下，进行另一种登录的手段。
  • 首次注册的用户，在不需要提供账号密码的情况下，进行快速注册和登录，做好引流。
第三方登录有哪些不足之处：

由于本站系统要求的信息五花八门，而第三方系统不一定会提供，比如手机，电子邮箱，这些信息为了以后做营销和找回密码等使用。

有时本站账号是强烈需要的，为了摆脱外部系统的依赖，或为了获取更多用户信息，需要第三方系统与本站账号进行绑定。

但是，首次第三方注册后，很多网站需要又立即注册本站账号，用户本来以为可以快速注册登录，实际上花费时间更长了，可能感觉受到欺骗，放弃注册。

解决方法：
  • 不提供第三方登录
  • 允许第三方登录，首次注册后，以后再通过各种方式诱导用户注册本站账号，补全信息
  • 允许第三方登录，首次注册后，立即注册本站账号，但在此之前做出明确提示

在这里插入图片描述

多账号体系

在第三方登录时，用户一般不希望在本站有多个账号，即每用一种第三方登录（QQ、微信、手机、邮箱）就生成一个user_id，多一种账号。当用户用不同第三方登录时，登录的是不同的账号。

除非本站是不需要交互的，类似银行“户头”，或微博这种希望用户账号多，关注人数多的。

而像有数据信息的账户，类似博客，论坛这种有积分、阅读历史、收藏等数据信息时，用户希望能统一共享数据信息。

对于希望账号统一的情况，采用的设计方案：

a.无视小部分用户希望合并不同登录端账户信息的需求；（开发成本低，影响部分用户体验）

b.提供账号合并功能；（开发成本高，用户体验好）

c.第一次第三方登录后强制注册。（开发成本适中，影响部分用户体验）

业务中的注册登录验证方式

一、账号密码登录
账号密码登录方式，用户已经使用很成熟了，外部依赖度低，但是密码简单时，容易被破解，密码复杂时，记忆难度大。

  • 个体识别度：低
  • 便捷程度：低
  • 技术要求：低
  • 风险程度：高

二、基础通信登录（手机或邮箱）
随着通信覆盖率的基本饱和及IM的发展，手机号码逐渐取代邮箱成为最通用的验证方式。目前阶段，由于注册登录的便捷性，无需记住密码的特点，采用基础通信方式进行身份验证是最为普遍的。
但是手机号可能长期不用，然后又由运营商分配给另一个用户。因此需要进行补充流程的验证，增加了使用成本。

  • 个体识别度：中
  • 便捷程度：中
  • 技术要求：中
  • 风险程度：中

三、证件信息认证（实名、护照、学生证等）
证件作为国家采信的身份校验信息，符合校验信息的基本要求。然而有两个特点限制了他的使用场景，一是敏感性，二是复杂性。

  • 个体识别度：高
  • 便捷程度：低
  • 技术要求：中
  • 风险程度：中

四、生物特征（指纹、虹膜、语音音频、人脸识别等
**理论上完美的实现方式。**基本上能够实现实人与网络的连接。现在的问题在于两个点，一是技术手段瓶颈；二是可识别性仍有待开发空间。找到人体上最独一无二的地方，再使用他进行便利的验证。能够实现的话就是验证方式的终极了。目前苹果的指纹登录，腾讯的声音锁，支付宝的人脸识别等功能正是向终极迈进的方向。

  • 个体识别度：高
  • 便捷程度：高
  • 技术要求：高
  • 风险程度：低

设计分析

对于不同的产品，应该有不同的设计方案。

如今对于营销类应用，仅需要用户提供微信登录，并在后续吸引用户填写手机号。

对于有独立内容的应用，比如公司网站、政府网站等等，需要用户必须注册本站账号，若第三方登录，则必须再强制注册本站账号。
在这里插入图片描述

一、行业

不同的行业，面临的流量与风控的要求不同。如上图所示，不同的行业由于业务场景的不同需要选择不同的注册登录流程。越接近流量导向，则注册登录流程越短（甚至无需注册登录亦能享受服务），注册登录流程后置可能性加大；越靠近风控导向，则注册登录流程越长，注册登录流程后置可能性变小。当公司产生发展方向调整的时候，注册登录流程也需要进行相应调整。

二、公司发展阶段

不同的公司发展阶段，也是一样的道理。发展初期，由于面临巨大的流量压力，对更偏向于选择短平快的，快速搭建账号体系的方式；发展后期，不管是出于业务拓展，为转型准备、精确营销及增加手中数据价值等等的考虑，往往会考虑自建账号体系，使用自己的注册登录流程。

三、具体渠道业务定位

以电商行业举例，APP，PC及H5，外投渠道，这些渠道承载的使命是不同的，因此如果有必要，也应该根据他们的定位进行符合要求的注册登录流程设计，最大化的发挥渠道效果。APP及PC，由于相对来说面向用户主要以成熟用户，老用户为主，功能更加全面，也有包括资产管理等安全要求较高的模块，因此对这些渠道来说，是需要走相对长的注册及登录流程的；至于H5及外投渠道，由于更轻量化，更侧重于引导快速交易，因此更倾向于流程后置，缩短流程，减少用户损耗。

因此需要看下，自己的行业是偏流量导向还是风控导向，公司发展阶段如何，具体渠道的业务形式，才可以从上面不同的账号注册登录形式中，选出最适合自己的方式，再进行逆向流程的补充，UI交互的打磨，形成最适合自己的注册登录流程。

需要说明的一点是，注册登录流程不是一成不变的，在发展的不同阶段，有可能会适当的进行调整。甚至于，在同一阶段，针对不同渠道的需求，也需要灵活处理。如何做好规划，这是用户产品经理的工作重点。

设计实现

综合以上考虑，我认为的数据库表结构设计如下：

个人信息表
person
|id|nickname|avatar|mobile|email|sex|birthday
|1|慕容雪村|http://…/avatar.jpg|mobile|email|男|19980101
本站用户表
user
|id|username|password|department|enabled|disabled_time|password_error_number|is_deleted
|1|zhangsan|password_hash(密码)|123|1|0|0
三方授权表
user_auths
|id|user_id|identity_type|identifier|credential|
|1|1|email|123@example.com|password_hash(密码)
|2|1|phone|13888888888|password_hash(密码)
|3|1|weibo|微博UID|微博access_token

首先用户信息表和用户授权表分开，没什么疑问。
其中用户user_id就是数据库主键，通过user_id与其他表外键关联，账号名username保证全局唯一，用于识别用户。这里并不生成全局独特的user_id，比如UUID生成，或一段连续的数字。也不向前端显示user_id，仅作为系统内部关联所用。

在多企业系统中，账号名username和机构department联合构成全局唯一。

username可以是用户自定义的纯字母，也可以是手机、邮箱，只要唯一就够了，并不做验证。
而手机号，邮箱在个人信息表中由用户自行变更，并不验证真实与否。当需要绑定账号时，才不允许用户随意变更，这时锁定该字段。同理，身份证号实名认证也是如此。

本站用户表唯一用途，就是进行登录，验证和识别身份，获得用户权限。不负责其他任务。

三方授权表唯一用途，就是提供另外的方式进行登录，验证和识别身份，获得用户权限。不负责其他任务。首次注册后，必须强制注册绑定本站账号。

这也很符合项目的生命周期，从项目开发初始只有本站注册方式，当项目发展到一定程度另外提供第三方登录。

最开始只提供账号密码登录，等有资本了，另外提供手机验证码登录。

初始只有后台管理员等有限用户，只需要用户名账号密码登录，后期发展起来，开始有大量用户，只须要在前端限制只能手机或邮箱作为账号名注册即可，不需改表结构。

posted @ 2024-02-21 11:03  CharyGao  阅读(180)  评论(0)  编辑  收藏  举报