互联网产品mysql数据库设计总结

2023-01-09 17:31:56

mysql数据库性能不比oracle数据库，所以设计上，和oracle有一些不同。下面总结一些互联网产品的数据库设计。

1.主键

主键可以使用bigint(20) unsigned也可以使用varchar，使用bigint，可以设置为自增主键auto_increment。使用varchar，要生成主键。

2.gmt_create、gmt_modified

在TB所有表中都添加gmt_create、gmt_modified字段，都是datetime类型。gmt_create表示记录创建时间，gmt_modified表示最近修改时间，如果记录没有修改，gmt_create和gmt_modified一致。

那么，这两个字段可以做什么用呢？这两个字段可以方便统计每天对某个表做了多少次的DML。另一种统计方式可以通过binlog。

查看昨天 insert：

select * FROM  test_table  WHERE

gmt_create < date_format(DATE(now()), '%Y-%m-%e %H-%i-%s') AND

gmt_create >= date_format(DATE(date_add(now(), INTERVAL - 1 DAY)),'%Y-%m-%e %H-%i-%s');

查看昨天update：

select * FROM test_table WHERE gmt_create < date_format(DATE(now()), '%Y-%m-%e %H-%i-%s')

AND gmt_create >= date_format(DATE(date_add(now(), INTERVAL - 1 DAY)),'%Y-%m-%e %H-%i-%s')

AND date_add(gmt_create, INTERVAL - 1 DAY) != date_add(gmt_modified, INTERVAL - 1 DAY)

3.逻辑删除 is_deleted

数据库不做物理删除，只做逻辑删除，用is_deleted做逻辑删除，如果删除，则为1，不删除则为0.is_deleted字段可以使用tinyint型。

4.禁止使用物理外键，使用逻辑外键

由于mysql性能不如oracle，外键还是一个比较消耗性能的东西，所以我们不要使用物理外键，就是我们在建表的时候，禁止使用foreign key。

例如表b记录了表a的id，我们只需在表b中添加一列：aid就可以了，然后通过程序来控制外键关系。

5.命名规范

1.库名，表名，字段名必须都用小写字母，并用下划线_分隔，并且见名知意，使用名词。

原因如下：

a) MySQL有配置参数lower_case_table_names，不可动态更改，linux系统默认为0，即库表名以实际情况存储，大小写敏感。如果是1，以小写存储，大小写不敏感。如果是2，以实际情况存储，但以小写比较。
b) 如果大小写混合用，可能存在abc,Abc,ABC等多个表共存，容易导致混乱。
c) 字段名显示区分大小写，但实际使用不区分，即不可以建立两个名字一样但大小写不一样的字段。
d) 为了统一规范，库名、表名、字段名使用小写字母。

6.使用innoDB存储引擎

5.5以后的默认引擘，支持事务，行级锁，更好的恢复性，高并发下性能更好，对多核，大内存，ssd等硬件支持更好。

7.关于数据类型

a.存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。

例如：对于金钱的存储，使用decimal，或者以分为单位，用bigint(20) unsigned。
b.使用UNSIGNED存储非负数值。同样的字节数，存储的数值范围更大。如tinyint 有符号为 -128-127，无符号为0-255
c.建议使用INT UNSIGNED存储IPV4。
使用INT UNSIGNED而不是char(15)来存储ipv4地址，通过MySQL函数inet_ntoa和inet_aton来进行转化。Ipv6地址目前没有转化函数，需要使用DECIMAL或者两个bigINT来存储。例如：
SELECT INET_ATON('209.207.224.40');
3520061480
SELECT INET_NTOA(3520061480);
209.207.224.40

d.整形定义中不添加（4），比如使用INT，而不是INT(4)

注意数值类型括号后面的数字只是表示宽度而跟存储范围没有关系，比如INT(3)默认显示3位，空格补齐，超出时正常显示，python、java客户端等不具备这个功能。

e.使用短数据类型，比如取值范围为0-80时，使用TINYINT UNSIGNED。

f.不建议使用ENUM类型，使用TINYINT来代替。

ENUM，有三个问题：添加新的值要做DDL，默认值问题(将一个非法值插入ENUM(也就是说，允许的值列之外的字符串)，将插入空字符串以作为特殊错误值)，索引值问题（插入数字实际是插入索引对应的值）
实例：

drop table if exists t;

create table t(sex enum('',''));

insert into t values(1);

insert into t values('');

select * from t;

+------+

| sex  |

+------+

| 0    |

|      |

+------+

2 rows in set (0.00 sec)

g.尽可能不使用TEXT、BLOB类型。
h.VARCHAR(N)，N表示存的个数，比如：VARCHAR（10）下面语句都可以成功：

update test set testvarchar='一二三四五六七八九十' where id=1

update test set testvarchar='' where id=1

update test set testvarchar='abcdefghij' where id=1

i.存储年使用YEAR类型。
j.存储日期使用DATE类型。
k.存储时间（精确到秒）建议使用TIMESTAMP类型，因为TIMESTAMP使用4字节，DATETIME使用8个字节。
l.将过大字段拆分到其他表中。
m.禁止在数据库中使用VARBINARY、BLOB存储图片、文件等

8.适当建立索引

非唯一索引必须按照“idx_字段名称_字段名称[_字段名]”进行命名。
唯一索引必须按照“uniq_字段名称_字段名称[_字段名]”进行命名。
索引名称必须使用小写。
索引中的字段数建议不超过5个。
单张表的索引数量控制在5个以内。
不建议使用%前缀模糊查询，例如LIKE “%weibo”。
合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。
唯一键由3个以下字段组成，并且字段都是整形时，使用唯一键作为主键。
没有唯一键或者唯一键不符合5中的条件时，使用自增id作为主键。
唯一键不和主键重复。
索引字段的顺序需要考虑字段值去重之后的个数，个数多的放在前面。
ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面。

码农公寓