Mysql表设计需要注意的问题

下面探讨的数据库为MySQL 存储引擎为innodb因为这是最常见的，使用最多的数据库和引擎

什么是页分裂？

这是因为聚簇索引采用的是平衡二叉树算法，而且每个节点都保存了该主键所对应行的数据，假设插入数据的主键是自增长的，那么根据二叉树算法会很快的把该数据添加到某个节点下，而其他的节点不用动；但是如果插入的是不规则的数据，那么每次插入都会改变二叉树之前的数据状态。从而导致了页分裂。

为什么一定要设置一个主键

因为就算你不主动设置一个主键，innodb也会帮你生成一个隐藏列，作为自增主键来使用。所以，不管怎么样都会有自增主键，还不如自己指定一个，主键索引可以提高查询效率。

主键使用自增还是UUID

主键肯定是用自增。innodb种的主键是聚族索引，如果主键是自增的，那么每次插入的新数据都会顺序添加到当前索引节点的后续位置，当一页写满时，就会自动开启第二页，如果不是自增主键，那么就可能在中间插入，就会引发页的分裂，产生很多碎片，总之用自增主键性能更好。

UUID产生的索引文件更大，当数据量超过一百万行时，主键查询性能和索引文件大小都比UUID要更有效率和更小。

主键为什么不推荐有业务含义

1）因为任何含有业务的列都有改变的可能性，主键一旦带上业务含义，那么主键就有可能发生改变。主键发生改变，该数据在磁盘上的存储位置就会发生更改，有可能引发页分裂，产生空间碎片。

2）带有业务含义的主键不一定是顺序自增的，就有可能导致后边插入的数据主键一定比前面的大，如果出现后边插入的数据主键比前面插入的小，就肯能引发页分裂，产生空间碎片。

表示枚举的字段为什么不用enum类型

首先，枚举在MySQL中一般用tinyint类型，为什么不使用enum呢

1）ENUM类型，order by操作效率低，需要额外的操作

2）如果枚举是数值，有陷阱

例如：表结构如下create table test(sex enum('0','1'))此时执行插入语句insert into test values(1) 查询的结果为0

只有这样插入insert into test values('1')此时结果才是1

货币字段用什么类型

货币：如果货币单位是分，可以使用int类型，如果坚持用元的话，可以使用Decimal类型，不能使用float和double类型，因为这两个类型是以二进制存储的，所有有一定的误差。

例如：create table 't'('price' float(10,2) default null) engine=innodb default charset=utf8然后插入一条数据price=120.23,然后你会发现数据变为120.25，精度失准。

时间字段用什么类型

1）如果使用varchar，优点是显示直观，缺点是做时间比较运算的时候，需要使用str_to_date等函数把它转化为事件类型，你会发现这是无法命中索引的，数据量一大，这就是一个大坑。

2）如果使用timestamp类型，该类型为4个字节，表示的时间范围为1970-01-01 到2038-01-19 也就是说2038年以后的时间不能使用timestamp存储。该类型有一个优势，就是自带时区，如果修改了时区，该字段值会跟着改变。

3）dtatime类型，该类型为8个字节，自己维护一个时间戳，表示范围比timestamp大，因为需要自己维护，所以不太方便。

为什么不直接存储图片、音频、视频等大容量内容

在实际生产中，我们都是在文件服务器上存真实文件，数据库中保存对应的文件路劲即可，MySQL有存放大文件的类型，text和blob类型，但是基本不使用

1）MySQL内存临时表不支持text、blob这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表，导致效率低下。

2）binlog内容太多，因为数据内容过大，会导致binlog内容较多，而MySQL主从同步靠binlog进行同步，binlog太大就会导致主从同步效率问题。

字段为什么要定义为NOT NULL

1）索引性能不好

2）查询会出现一些不可预料的结果

我们都只是茫茫星辰中的一粒沙。

互联网产品mysql数据库设计注意事项

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('0','1'));
insert into t values(1);
insert into t values('3');
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='1234567890' 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的字段需要添加在索引的后面。

原文： https://www.cnblogs.com/yjp372928571/p/11221667.html

文章来源: blog.csdn.net，作者：隔壁老瓦，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/wxb880114/article/details/106685471