Mysql表设计的经验准则

1.命名规范

• 表名、字段名必须使用小写字母或者数字，禁止使用数字开头、禁止使用拼音，并且一般不适用英文缩写

• 主键索引名pk字段名；唯一索引名为uk字段名；普通索引则为idx_字段名。

2.选择合适的字段类型

设计表，我们需要选择合适的字段类型，比如:

• 尽可能存储空间小的字段类型，就好像数字类型的，从tinyint、smallint、int、bigint、从左往右开始选择

• 小数类型如金额，则选择decimal，禁止使用float和double。

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

• varchar是可变长字符串，不预先分配存储空间，长度不要超过5000

• 如果存储的值太大，建议字段类型修改为text，同时抽出单独一张表，用主键与之对应。

• 同一表中，所有varchar字段的长度加起来，不能大于65535，如果有这样的需求，请使用TEXT/LONGTEXT类型。

3.主键设计要合理

主键设计的话，最好不要与业务逻辑有所关联。有些业务上的字段，比如身份证，虽然是唯一的，一些开发者喜欢用它来做主键，但是不是很建议哈。主键最好是毫无意义的一串不独立不重复的数字，比如UUID，又或者Auto_increment自增的主键，或者是雪花算法生成的主键等等；

4.选择合适的字段长度

先问大家一个问题，大家字段数据库字段长度表示字符长度还是字节长度？

其实在MySQL中，varchar和char类型表示字符长度，而其他类型表示的长度都表示字节长度。比如char(10)而bigint(4)表示显示长度是4个字节，但是bigint实际长度是8个字节，所以bigint'(4)的实际长度就是8个字节。

我们在实际表的时候，需要充分考虑一个字段的长度，比如一个用户名长度字段(他的长度5-20个字符)，你觉得应该设计多长呢？可以考虑设置为username varchar(32)。字段长度一般设置为2的幂也就是2 的n次方

5.优先考虑逻辑删除，而不是物理删除

什么是物理删除？什么是逻辑删除？

• 物理删除:把数据从硬板中删除，可释放存储空间

• 逻辑删除:给数据添加一个字符。比如is_deleted，以标记该数据已经逻辑删除。

物理删除就是执行delete语句，如删除account_no=‘666’ 的账户信息sql如下:

 delete from account_info_tab where account_no='666';

逻辑删除就是

 update account_info_tab set is_deleted=1 where account_on='666';

为什么推荐使用逻辑删除，不推荐物理删除呢

• 为什么不推荐使用物理删除，因为恢复数据很困难

• 物理删除会使自增主键不在连续

• 核心业务表的数据不建议物理删除，只适合做状态更改。

6.每个表都需要添加create_time等通用字段

表必备一般来说，或具备这几个字段

• id：主键，一个表必须得有主键，必须

• create_time：创建时间，必须

• modifed_time/update_time：修改时间，必须，更新记录时，需要更新它

• version：数据记录的版本号，用于乐观锁，非必须

• remark：数据记录备注，非必须

• modified_by：修改人，非必须

• creator：创建人，非必须

7.一张表的字段不宜过多

我们建表的时候，要牢记，一张表的字段不宜过多哈，一般尽量不要超过20个字段哈。

如果一张表的字段过多，表中的数据可能就会很大，查询效率会很低。因此，一张表不要设计太多字段，如果业务需求，实在需要很多字段，可以把一张很大的表，拆成多张小的表，他们主键相同即可。

当表的字段数非常多时，可以将表分成两张表，一张作为条件查询表，一张作为详细内容表。为性能考虑

8.尽可能使用not null字段定义

如果没有特殊 的理由，一般建议将字段定义为not null。

为什么呢？

• 首先，not null可以防止出现空指针问题。

• 其次，null值存储也需要额外的空间的，它也会导致比较运算更为复杂，使优化器难以优化sql。

• null值有可能导致索引失效

• 如果字段默认设置成一个空字符串或常量值并没有什么不同，且不会影响到应用逻辑，那就可以将这个字段设置为not null。

9.设计表时，评估哪些字段需要加索引

首先，评估你的表数据量。如果你的表数据量只有一百几十行，就没有必要加索引。否则设计表的时候，如果有查询条件，一般就需要建立索引。但索引也不能滥用。

• 索引也不要建太多，一般单表索引个数不要超过5个。因为创建过多的索引，会降低写得速度

• 区分度不高的字段，不能加索引，如性别等

• 索引创建完后，还是要注意避免索引失效的情况，如使用mysql的内置函数，会导致索引失效

• 索引过多的话，可以通过联合索引的方式来优化。然后的话，索引还有一些规则，如覆盖索引，最左匹配原则等等。

10.避免使用MySQL保留字

如果库名、表名、字段名等属性含有保留字，sql语句必须用反引号来引用属性名称，这将使得sql语句书写、shell脚本中变量的转移等变得非常复杂。

因此我们一般避免使用mysql保留字，如select、interval、desc等等。