Mysql是我们开发中存储数据的常用工具之一，好的数据表设计不仅让业务更加清晰，而且也会让后面继续维护这套业务的人更易上手，今天我们从表的字段、索引等方面来聊聊一些好的Mysql表设计经验。

1、表、字段、索引的命名规范

数据库表名、字段名、索引名等都需要规范的命名，命名尽量使用英文并要可读性高，采用驼峰或者下划线分割的方式，让人见名知意。例如订单的id可以命名为order_id，用户的id可命名为user_id，这样我们见到字段就可以知道其含义。

一般表名、字段名使用小写字母，不使用数字开头、不使用拼音。对于索引的命名来讲，我们遵循主键索引名（pk_字段名）；唯一索引名（uk_字段名）；普通索引名（ idx_字段名）。

2、字段

2.1 选择合适的字段类型

设计字段的时候往往要考虑到自己的实际业务场景，然后选择合适的字段类型。

（1）如某个字段的数据长度不会超过10个字符，则可以使用CHAR类型，如果字段的长度是定长（如身份证号、门牌号等）也可以CHAR类型，如果某个字段的长度不确定可以使用VARCHAR类型，如果字段长度可能很长，建议使用text类型。

（2）对于需要精确数值计算的字段(如货币和百分比)，应该选择带有精度和小数位数的字段类型(如DECIMAL)。

（3）Mysql表示时间类型的字段主要如下的几种：

date：表示的日期值，格式yyyy-mm-dd，范围1000-01-01到9999-12-31

datetime：表示的日期时间值，它与时区无关，格式yyyy-mm-dd hh:mm:ss，范围1000-01-0100:00:00到9999-12-31 23:59:59

time：表示的时间值，格式hh:mm:ss，范围-838:59:59到838:59:59

timestamp： 表示的时间戳值，它跟时区有关，格式为yyyymmdd hhmmss，范围1970-01-01 00:00:01到2038-01-19 03:14:07

year：年份值，格式为yyyy。范围1901到2155

在阿里开发规范中推荐使用datetime类型来保存日期和时间，这是因为存储范围更大，且跟时区无关。

2.2 选择适合字段长度

首先我们要明确一点。在Mysql中的 VARCHAR 和 CHAR 类型表示字符长度，而其他类型表示的长度都表示字节长度。如 CHAR (10)表示字符长度是10；bigint(8)表示长度是8个字节长度。

我们需要根据实际的业务场景选择合适的长度可以节省存储空间，提升存储、查询的效率。

2.3 设计合理的主键

主键的设计在数据库中非常重要，它用于唯一标识表中的每一行数据，并且在数据操作和查询中起到关键作用。通常主键使用自增的id，这样可以保持主键的连续性。在分布式的环境下，我们无法使用自增主键，一般推荐使用id生成器生成唯一的主键（常见的有雪花算法）。

2.4 添加一些通用的字段

在阿里的表设计规范中也提到了设计数据表的时候添加一些通用的字段，如id、创建人字段、创建时间字段、修改人字段、修改时间字段等等，通过这些通用字段可以帮助我们了解这个数据谁创建的、最后的是谁在什么时间做了修改等信息。

2.5 表字段的数量不宜过多

我们设计数据表的时候，表的字段尽量不超过20个。如果超出的话考虑做拆分，拆分为基本表和详情表。这个拆分可以提高查询效率，优化了磁盘的存储空间（表的字段数越多，每一行数据占用的存储空间也就越大。这样可能会导致磁盘空间的浪费），同时方便后续的维护工作。

2.6 字段添加注释

设计数据表的字段的时候我们也要些字段的注释，这样自己也包括其他的人在看到这个字段的时候知道这个字段代表的含义，例如type字段

type tinyint not null default 0 comment '类型 1-上传文件 2-下载文件'

通过添加注释我们在后续维护的时候无需去看业务代码就知道字段type所代表的含义。

2.7 尽可能设置字段不为空

一般都推荐将字段定义为NOT NULL，因为一方面NOT NULL可以有效的防止代码中出现空指针问题，另一方面由于NULL值存储也需要额外的空间的，同时NULL值也会导致比较运算更为复杂，使优化器难以优化sql。

如果在实际业务中将字段默认设置成一个空字符串或常量值并没有什么影响，那可以将这个字段设置为NOT NULL。

2.8 字段的删除优先考虑逻辑删除

常见的删除有两种方式，一种是逻辑删除（数据表中添加一个字段is_deleted，用来标记该数据已经逻辑删除）；一种是物理删除（把数据从硬盘中删除，可释放存储空间）。

由于使用物理删除一方面数据恢复困难，另一方面物理删除也会导致索引树重构，所以推荐使用逻辑删除。

2.9 合理的添加表的冗余字段

添加表的冗余字段是违反了Mysql的三大范式设计要求，但是在实际的业务中我们增加了冗余字段可以减少表的关联提升了性能。如在设计订单的表的时候，我们在订单表中的添加用户下单的商品的冗余字段，这样设计的目的就在查询的订单的时候就不用再做一次查询来获取下单商品的信息。

2.10 核心业务表添加扩展字段

我们一些关键的表设计的时候建议加上扩展字段（extra），因为随着业务的发展，我们需要存储更多业务信息，有些业务信息只做展示和代码中查询使用（如下单商品的规格信息），这些字段可以考虑放在扩展字段中保存。

3、索引

3.1 合理设置索引

在设计表时，我么需要充分考虑哪些字段需要加索引，可以参考如以下几个原则：

（1）高频的查询条件：如果在查询中使用了某个字段作为查询条件，那么这个字段考虑建立索引。如在订单表中将订单的id设置为索引。

（2）区分度高的字段设置索引：如果一个字段的取值范围非常小，典型的是性别字典，它只有男女两种可能，那么这个字段就不适合建立索引，因为其区分度低。

（3）不要建立过多的索引：每个表所建立的索引数量应该控制在一个合理的范围内，一般不要超过5个。因为过多的索引会导致写入速度变慢，并占用更多的存储空间。

（4）善于使用联合索引：在某些情况下可以通过联合索引的方式来优化查询速度，减少所需的索引数量。

3.2 尽可能少使用外键

外键可以保证数据的一致性和完整性，但是它也会带来一些问题，如性能问题、限制数据库的扩展性和灵活性、增加了维护成本等等问题，所以在一些情况下我们可以通过代码维护数据的一致性和完整性来替代外键。

总结：

（1）设计表的时候，对于表名、字段名、索引名都要规范命名

（2）设计数据表字段不宜过多，字段要选择合适的类型和长度，字段尽可能设置不为空，字段的注释也清晰，字段在一些业务场景中可以设计冗余字段。

（3）合理设置索引、尽量少使用外键。