一些自己使用 mysql 开发应用的经验总结和建议。

编码一律使用 utf8mb4

mysql 中的 utf8 是三个字节的，能够支持 UTF-8 规范中的大部分字符，对于一些特殊的汉字或 emiji 表情字符是不支持的。MySQL在5.5.3版本中引入了utf8mb4字符集，支持全Unicode字符集，允许使用4个字节来表示一个字符，可以存储和处理任意的Unicode字符。

所以，建议在建表或建库的时候指定编码为 utf8mb4。在这方便我是有过教训的，虽然自己的系统使用没有出现问题，但是和别的系统做了对接，每次一同步就报错，后来发现是编码问题。如果你使用的是 utf8 编码，凡是有输入的地方，如果用户输入了 emoji 表情（比如 ），一保存就会报错。

CREATE DATABASE dbName CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE tbName (
    colName1 dataType1,
    colName2 dataType2,
    ...
) CHARSET=utf8mb4;

枚举

枚举语法示例：

CREATE TABLE example_table (
    example_enum_column ENUM('option1', 'option2', 'option3')
);

对于数据库自带的枚举我是不推荐使用的，很早之前也有使用过。但是对于互联网行业来说，有时候业务变化比较快，每次有调整，如果增加了新的枚举值都需要修改表结构，太过于繁琐，而且风险也大，数据库能不动尽量别去动。

enum Status{
  ACTIVE = 1,
  DISABLED,
  DELETED
}
enum Status{
  ACTIVE = "ACTIVE ",
  DISABLED = "DISABLED",
  DELETED = "DELETED"
}

所以，程序中控制字段的值是比较合理的，那么程序中应该使用数字值枚举还是字符串呢？我个人建议使用字符串，理由是维护更方面些，数字可读性差，虽然程序中我们不需要操作这些，但是偶尔会遇到需要给客户导数据的情况，如果很多字段的值都是 1、2、3 之类的，就太头大了。并且，这些枚举值返回给前端或客户端的时候，前端或客户端开发的同事也很头大，尤其是程序调试的时候看请求数据，想一下子看懂还真不容易，每个枚举字段都要找文档对着看。

但是，使用数字类型，在数据库中占用的空间比较小，查询性能也会更好一些。如果不是极致的追求性能，还是更推荐使用字符串枚举。

日期

现在日期直接在数据库中存储时间戮挺流行的，我认识的程序员就有很多这样做的，并且我自己也有几个项目是这样的。不过，我还是建议尽可能使用 datetime 类型，可读性更好，更方便维护。

select * from user where create_at>'2023-01-01'

如果你使用的是日期类型，就可以像上面这样查询，并且在数据库返回的结果中可以清晰年月日信息。如果使用的是整型来存储，就比较麻烦了，首先要将自己查询的日期转换为整数再进行查询，返回的结果也一串数字很难看懂。也可以使用 mysql 内置的函数来转换，只是麻烦些。

select * from user where create_at>UNIX_TIMESTAMP('2023-01-01')

想让查询结果也显示成可读的日期信息，可使用 FROM_UNIXTIME 函数。需要注意的是这些内置函数是精确到秒的，如果你存储的是毫秒，还要再做一点处理。总之，这么麻烦肯定是不划算的。

那么，为什么还有些项目会选择使用整数呢？我觉得可能有以下的原因：

1. 只存储到秒，这样无符号 int 就够了，int 只使用 4 字节，datetime 需要 8字节，节省空间，加载数据时性能也更好。
2. 时区问题，对于需要国际化的项目，一般都是后端返回时间戮，然后客户端根据所在的时区序列化成具体的日期。这样的话，直接使用整型更加方便，也减少了转换过程。
3. json 存储，json 如果序列化日期，再反序列化回来就是字符串，比较麻烦，像 mongodb 这样的数据库就是 json 存储的。我在一些使用 mongodb 的项目中也使用过整形来存储日期。

到底要怎么选，我觉得还是得从人和时间来考虑，对于能节省人的时间的方案优先考虑。

自增主键还是 UUID

对于主键，我个人是倾向于使用 uuid 的，并且所有的项目都使用 uuid。我参与过一个项目，新版本上线后，需要将用户从旧版本迁移过来，旧版本使用的是 postgresql ，新版本使用的 mongodb ，还好使用的是 uuid ，不然就难搞了。自增主键除了跨库迁移的分布式方案中不适合外，性能也会更差一些，每次插入数据都需要数据库来生成主键，uuid 则可以把生成的工作交给服务器。自增主键的好处就是占用空间小，查询速度更快。

由于 uuid 是无规律的，有的时候可以提高安全性，比如用户通过系统中的 id 可以推算系统中的数据量。在使用自增主键的情况下，假如我刚发了个帖子 id 是 9988，那么我就知道系统中有差不多1万个帖子。如果是电商项目，还可以早晚各下一单，然后比较下订单id，大概知道网站一天有多少单。如果业务有这方面的顾忌， 自增 id 要慎重使用。

使用 uuid 时建议将连接符短横杠去掉，这样可以节省一点空间，36位字符变32位。还可以使用基于时间的 uuid ，使生成的主键有顺序，或者自己做一个时间戮加随机字符也可以。

UUID 能够适用更多的情况，使用 UUID 肯定更稳重一些，但是空间和性能都有劣势。

外键

我曾经也对于要不要使用外键纠结过，大约几年前，网上讨论有挺多讨论这个问题的。我现在的建议是不要使用，不使用最大的好处就是提升性能。如果没有外键你可能会担心数据完整性不能保证，这就要靠应用程序来保证了，在数据修改时做好判定，如果数据不完整要能容错。想想看，一般情况下，即使有外键我们也是会做验证的，这种情况下，应用程序校验了一遍，数据库存储数据时又会校验一次，校验两次不是浪费吗？

其实也不用那么担心，即使真的出了问题，数据不完整也没关系，很多时候程序做容错处理也不麻烦。分布式系统，不同的服务使用不同的物理库，本来就是没有外键的，如果用户注销了数据被删除，在文章服务中可以将查找不到信息的用户显示为已注销。如果由于程序 bug 导致数据不完整，发生事故怎么办？这种事情其实很少遇到的，但是我也处理过类似的，好在数据库有归档日志的，修正程序，然后再用归档日志恢复丢失的数据解决的。严格测试是关键，不能依赖于数据库的约束。像 postgresql 这样的数据库还可以支持检查约束来避免垃圾数据，比如可以设置表 user 的字段 age 的值不能低于 18，这样的操作都是不建议的。

json

mysql 从 5.7 版本开始支持了 JSON 类型，并且还可以对 JSON 数据中的指定属性创建索引。实际开发中，并不是很推荐去使用，因为开发需要使用 orm 框架，框架可能无法支持该类型，甚至于相关的语言驱动程序也没有做支持，这会给开发带来麻烦。虽然 mysql 支持 JSON 类型，但是并不能当成文档数据库一样去使用，更不可能替代 mongodb 这样的文档数据库。

为了兼容考虑，我建议不要使用 json，如果需要存一些不是很重要的数据，但是又不想重新创建一张表，可以使用 varchar 字段来存储 json 字符串。我在一些对接第三方平台的程序中，有这么做过，有些第三方平台响应的 json 数据比较复杂层级也深，存的话可能要建好几张表，不存的话，万一有需求，信息没有记录就麻烦了，将 json 字符串存到数据库中就是比较好的选择。当然，需要做查询条件的字段，还是老老实实建表，使用 varchar 存储 json 字符串是不能建索引的。

存储 ip

对于 ip 我相信如果业务没有特殊需要，大多数人都是存储的字符串。mysql 提供了 NET_ATON() 和 INET_NTOA() 函数用做数字与 ipv4 的转换，那么是不是可以以整数的形式来存储 ip 呢，毕竟这样更节省空间？使用整形确实是可以的，使用 mysql 提供的函数，可以在查询时转换一下让结果显示 ip 地址，存储时也可以转成整数来存储。存储成数字，还可以创建索引，利用数字比较来做网络地址的匹配。

SELECT * FROM ip_address WHERE integer_ip >= INET_ATON('192.168.1.0/24') AND integer_ip <= INET_ATON('192.168.1.255/24');

上面是一个查询 192.168.1 网段 ip 的例子，也可以应用程序中做好数字的计算，不使用函数 INET_ATON，这样在使用 orm 框架时更方便些。

如果没有查询的需要，我建议还是使用字符串来存储，简单，可读性好。如果追求性能，有查询的需要，可以考虑存储为数字。

索引覆盖避免回表

很多数据库相关的书藉里都会提到一个技巧：覆盖索引（Covering Index）。如果一个查询语句中，所有需要查询的字段都可以从索引中直接获取，而无需回到主表中查找。二级索引存储了主键信息，如果数据不在索引上，那么就会使用主键去聚簇索引中查询信息，这样就需要回表，如果二级索引上已经有所有需要的信息了，那么就可以省去这一步，提升效率。

select name,age from user where group='g1';

比如上传的查询，如果有个 group 加 name 加 age 的联合索引，就可以避免回表，提升性能。

但是，现实中很少有这样的好事，很多业务的查询都是比较变态的，条件非常的动态，能利用上覆盖索引的机会很少。即使能用上，也要为此，创建很多条索引，索引并不是越多越好，多了就会耗费非常大的存储空间，使得数据的写入速度变慢。而且，开发和维护也会变得非常复杂，专门去写对应的 sql 本来就增加了开发成本，封装好的这类方法，往往不通用，比如上面的例子，返回的信息中只有用户的姓名和年龄以致于别的地方很可能用不上。

我觉得不要刻意去追求索引覆盖，甚至于不用去考虑这个事件，绝大多数情况下是不划算的。

排序字段一定要有索引

大部分情况下，列表查询都是需要有排序的，否则程序返回的结果是不确定的，除非是一次读取所有符合条件的记录。有的时候，删除也必须要带排序的，否则可能会造成事故。

delecte from log limit 10;

像上面的这个例子，具体哪些条目被删除是不确定的，如果是主备架构，那么备机一同步，也执行了同样的操作，最后删除的数据和主机可能是不一样的，导致了数据不一致。如果不加 limit 也是不好的习惯，除非能通过条件限定在一个比较小的范围内，才能一次删除所有符合条件的记录，否则可能会带来线上宕机电话被打爆的后果，别问我怎么知道的。对于 mongodb 这种支持数据有效期的数据库也同样小心，当大批量数据同时过期时，数据库也会出现高负载，我在我的项目里都是尽可能让数据清理发生在负载低的时候，或者让程序程序来负责删除。总之，大量删除建议排序（排序字段必须要有索引）并限制数量（可以多执行几次，但是每次必须控制在较少的量），或者能控制在较小范围一次删除所有符合条件的记录。

-- 这里必须要对 create_at 创建索引，对于动态条件很多的情况下，酌情创建联合索引
select * from user where `group`='救火队' or department='开发部' order create_at asc,id asc limit 20;

对于查询来说排序就更重要了，记住一点：排序字段必须有索引。因为，排序字段有索引的情况下，数据库不需要即时排序，只需要从索引上逐个读取数据就可以了。这个很重要，如果没有排序索引，数据库很可能为了排序而全表扫描，而且这个排序是非常占资源的，如果内存不够用，mysql 还会使用临时文件来排序，不仅负载高而且非常慢。如果是并发较高的查询接口，每次查询数据库都要实时排序，后果很严重。

分区

分区可以快速删除不需要的数据，还可以让查询提速，如果底层使用的是分布式存储系统，通过分区可以将文件散列在不同的服务器节点上，分摊压力。但是分区也比较的麻烦，如果做 list 分区，按时间来分区存储数据，对于日志型的数据确实是不错的方案，删除过期数据也非常快捷，但是这个分区需要定期修改，就有点麻烦了。我之前做的项目都是靠程序中的周期任务定期调整。如果可以的话，尽可能使用 hash 和 key 分区，一开始指定分区数量，将数据分散放入，后期也不需要再费太多精力，很适合于 saas 平台按企业 id 来分区。

如果要做分区，最好是每次查询只从某一个区分中获取数据，如果出现跨分区，查询的速度可能比不分区还慢。假如经常需要跨分区查询，说明是分区是失败的，不仅没有提升还可能给系统带来性能隐患。数据规模不大的话，建议不要分区，多一事不如少一事。总之，非必要不使用。

后记

追求性能都是有代价的，代价就是我们开发人员的时间，我建议要从项目的管理和开发效率来综合考虑的。有时候牺牲一点性能来换取时间是非常值得的，随着硬件的成本降低，相比之下人力成本十分的高昂。