最难不过二叉树

首页归档分类关于

数据库使用经验之谈

2024-07-10

数据库在业务系统中处于核心地位，如何使用好数据库是一位架构师必须掌握的技能。但是数据库在不同场景下有不同的用法，另外数据库在不断地升级版本，不同版本下的数据库也有不同的玩法，因此“如何在业务系统中用好数据库”本身就是一个极容易撕逼的话题，每个人都在自己的业务场景中有自己使用数据库的心得经验。本文总结了本人这么多年实际处理业务数据时使用数据库所遇到的一些问题、思考和处理方案，抛砖引玉，让大家在处理自己业务系统数据库问题时有更多的思路。

本人经手的项目用的关系型数据库均为MySQL，版本横跨MySQL5到MySQL8，隔离级别为可重复读。因此以下的讨论都是围绕MySQL来进行。

该不该使用多表联查？

关系型数据库的关联查询是其一大特色，但很多高性能应用会对关联查询进行分解以提升可扩展性和处理能力。有些激进的团队会直接禁用关联查询。那这里首先要看下多表关联存在什么问题。

首先是性能问题，假设我们3个表关联查询，每个表都是1000万的数据，此时查询的复杂度会比较高，复杂查询可能会导致长时间锁定，影响其他事务。我们尽量少在数据库层面执行一条SQL时吃掉太多资源，我们更倾向于在应用程序层面做这些活，因为数据库水平扩展很难很难，但应用程序水平扩展却简单。

其次是关联查询不支持跨库，这导致很多的数据聚合需求无法实现。如果是所有表都在一个库里，一切都好办，但是现在的后台架构大多数据微服务架构，一个开发小组一个微服务，在自己的的微服务中管理自己的数据库，但是需求侧可不会管你后台是单体程序还是微服务架构，直接就是要聚合这些数据，那么这就是多表关联查询所做不到的。

还有就是扩展性问题，假设数据库A需要把其中一张大表移到另一个库，通过分库来减轻查库的压力。如果我们的业务程序用的是关联查询，那么此次分库的影响就是用到关联查询该表的代码都需要改动，因为这些表都不在一个库里了，无法实现关联查询。所以，在关联查询的写法下，要做表的迁移成本是非常高的。

所以在互联网这种高并发ToC场景下，一般都避开使用多表关联查询。具体做法就是将多表关联分解为单个查询，把聚合查询通过多个单个查询数据后在应用层组合，而不要放在数据库上通过JOIN来组合。

比如这个订单场景，我们有以下表：

  • 订单表，该表存有订单ID、用户ID，商品ID
  • 用户表，该表有用户ID、用户名
  • 商品表，该表有商品ID，商品名

此时有一个聚合查询的需求：分页查出订单列表，行数据包括：订单号，用户名，商品名

如果用多表JOIN很简单，一个SQL就能完成，如果将其拆分成多个查询，可以这么做：

  1. 先查分页查订单列表，返回订单列表[订单ID、用户ID，商品ID]
  2. 将用户ID组合成列表（先去重），对用户表进行查询SELECT uid,username from user WHERE uid IN (用户ID列表)
  3. 将商品ID组合成列表（先去重），对用户表进行查询SELECT product_id,product_name from product WHERE uid IN (商品ID列表)
  4. 对前三步得到的数据进行组合，得到最终查询结果

如果用户表位于用户微服务，商品表位于商品微服务，那么2,3步执行SQL的操作就改为执行微服务的接口调用，还可以使用多线程或者异步来并发请求，提高效率。

多表关联分解为单个查询优点有这些：

  • 查询分解后，执行单个查询可以减少锁的竞争。
  • 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
  • 可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，然后缓存在应用程序内存里，而在数据库中做关联查询，则可能需要重复地访问一部分数据。

而对于内部场景，其实多表关联查询反而是最优解，很多业务并不需要高并发查询，更多的业务需求是离线聚合查询，查询频率很低，关联查询一条SQL就能把所需数据聚合返回，而且省去了多次网络请求的时延，从数据准确性和开发效率来说是个更优方案。

另外如果可以预见自己要联的表未来也不会增加到几千万，那就别折腾了，直接JOIN吧，没有任何压力。比如你有A表有1千万数据，B表有100行数据，C表1000行数据，ABC三表JOIN并不会有任何问题，所以也别想着拆分查询的方案，属于吃力不讨好。

建表时冗余建个ext字段有无必要？

用MySQL5的开发者看到要加字段的需求肯定会很烦躁，因为在千万级上的表ALTER TABLE预示着不可避免的风险，搞不好因为这个变更语句导致线上数据库卡端几分钟，这就是个生产事故了，因此大家其实都不太愿意改动线上数据库表的结构。

所以有经验的开发者会在建表时先预留一些冗余字段，比如c1,c2,c3等等，先冗余建立字段，后续线上要添加新字段时，直接复用这些预留字段即可，少了修改线上数据库表结构的风险。如果不希望预留这么多字段，可以建一个text类型的ext字段，存储json结构的数据，不管什么结构的数据直接dump成json往里面塞，做成动态的数据结构了，扩展性更强。如果ext中有某些字段需要作为查询条件进行检索，我们也可以通过建立虚拟列来建立索引，加快检索速度。比如我们ext存储的json格式如下：

{"status":1, "name":lijunshi}

status作为常用的检索条件，因此我们要为他建立索引，直接对TEXT类型的字段中的某个JSON属性（如status）建立索引是不支持的，但可以通过虚拟列（Virtual Columns）和索引的方式来实现对JSON字段中特定属性的索引。

ALTER TABLE my_table
ADD COLUMN status INT AS (JSON_UNQUOTE(JSON_EXTRACT(ext, '$.status'))) VIRTUAL,
ADD INDEX (status);

我们建立了虚拟列status并为其加了索引。这个模式下我们既等保证字段的可扩展性，又能保证字段的检索速度。

其实mysql8时，这个线上数据库表加字段的问题已经不再是个风险点了，因为mysql8已经支持秒加字段了。

ALTER TABLE `user` ADD `status` (1) DEFAULT 0, ALGORITHM=instant

SELECT FOR UPDATE并不是那么好用

当多个查询需要再同一个时间修改数据，都会产生并发控制的问题。如果要在数据库层面做并发控制，那需要用到数据库的锁。锁有读锁（共享锁）和写锁（排他锁）；锁从粒度分则有行锁和表锁，实际操作中尽量只锁定需要修改的部分数据而不是所有数据，这样系统的并发性会更高。

举一个场景加锁的必要：在仓库库存管理中，如果在查询库存表T1时库存是30 ，但在查询完成后，其他业务操作改变了库存，导致库存变为25 。然而，业务处理时依旧基于查询时的库存 30 进行操作，在发货表T2上插入了新行，这样导致了超发。

这个是典型的“先读后写”的场景：先查状态，再处理逻辑，即使将其作为一个事务处理，也有问题，本质问题在于在使用库存这个数据时，有可能在其他事务中已经被修改。

一个简单的处理方式是加悲观锁，为了保证业务的强一致性，直接用数据库的SELECT FOR UPDATE来锁住SELECT到的数据行，保证在一个处理逻辑过程中，查询的数据不会被修改。上面的例子中，我们的流程就改为：

  1. 开始事务
  2. SELECT 库存 FROM T1 FOR UPDATE
  3. 库存>0 则在发货表T2上插入了新行
  4. 库存 <=0 则返回操作中止，返回库存不足
  5. 提交事务

有人会担心SELECT FOR UPDATE是不是性能不太行，有一说一这种悲观锁很重，对数据库的性能影响确实很大，但是引擎用的是Innodb，锁的是行数据，影响的范围比较少，只要你SELECT FOR UPDATE选中的数据不多，而其他操作不访问这些数据，那么并不太影响并发。

如果你的场景简单，并发数不多，但要求处理逻辑的强一致性，那么数据库的悲观锁就是你的最佳方案。但是一旦你的业务或者架构并不简单，那么这个数据库悲观锁的方案，可能就不那么合适了。比如还是上面的例子，但我们是微服务架构，步骤1查T1表的库存是微服务1的逻辑，步骤2插入发货表T2是微服务2的逻辑，因此整个处理流程其实是：

  1. 微服务2先请求微服务1查询库存
  2. 微服务2在根据查询结果选择是否插入新数据

微服务1不可能做查询库存时做SELECT FOR UPDATE，因为微服务1不知道微服务2什么时候才处理完逻辑，即使微服务2处理完逻辑后通知微服务1，但是这个过程也是不可控的，数据库死锁是个严重的问题，这就影响到其他业务了，微服务1团队就不愿意采用这个方案，风险太大了。即使最终采取这个方案，就得在微服务1上做锁的超时释放逻辑了，大概就是在一个事务里进行sleep，等待请求来结束sleep。总的来说，微服务架构下开一个长事务来进行这些阻塞操作多少有些不合适。

互联网业务更喜欢用分布式锁来完成上面场景的业务需求，分布式锁用得比较多的是redis和etcd，这些组件的并发处理能力远比数据库要强，同时这些分布式锁也带锁超时释放功能，对微服务1团队而言更为友好，开箱即用省了很多资源成本，另外也将数据库加锁这种偏业务的功能解耦出来，让数据库做一个更为纯粹的稳定的数据存储组件。但缺点就是引入了新的组件和处理逻辑，架构上会复杂一些。

另外使用数据库锁一定要注意死锁问题，既然加锁了就必须记得解锁，因为SELECT FOR UPDATE实现的锁不像redis那种分布锁还可以设置超时时间，一旦忘记释放锁就是严重的死锁问题，所以一定要做好锁的管理。

触发器慎用

面对这样的业务场景，你会怎么做？

我们有两个表，一个叫订单表，一个叫用户表，订单表有字段订单ID、商品ID、商品名、用户ID、用户名；用户表有字段用户ID、用户名。可以看出订单表冗余存储了用户名来避免联表，来提示查询效率，但是引入立刻一个问题：用户名是可以修改的，因此要考虑用户名一致性的问题，比如用户修改了用户名，如何将该修改同步到订单表呢？

有人提出数据库的触发器可以解决这个问题：在数据库建立AFTER UPDATE触发器，当用户表的用户名发生修改时，UPDATE订单表的用户名，保证两个表的数据一致性。

触发器的优点是业务代码逻辑简单一些，因为逻辑数据库帮你做了；缺点就是维护成本高，接手的哥们恐怕不好处理了。很多用了触发器的项目在做项目迁移和扩展时遇到非常严重的维护问题，相当头疼。

个人观点，从项目管理来说，慎用触发器，除非你们有专门的 DBA 去维护，要不然不要用触发器等数据库高级功能。逻辑请直接写到代码里面，数据库就只管存数据。

外键带来的维护难度

外键限制会将约束放到MySQL中，这对于必须维护外键的场景，性能会更高。不过这也会带来额外的复杂性和额外的索引消耗，还会增加多表之间的交互，会导致系统中更多的锁和竞争。外键可以被看作是一个确保系统完整性的额外的特性，但是如果设计的是一个高性能的系统，那么外键就显得很臃肿了。很多人在更在意系统的性能的时候都不会使用外键，而是通过应用程序来维护，所以现在基本都不会再使用外键约束了，外键维护起来太难受了。

举个简单的例子，你有两个表，一个用户表，一个帖子表，帖子外键连到用户。

现在你把一个用户删了，那么这个用户对应的帖子就需要做适当的处理。比如你可以在用户的删除钩子上，把他发的所有帖子的用户 ID 都改成一个特殊用户的 ID 。这样那些帖子就不会被删除了。

而用数据库外键的话，你一旦删除了一个用户，这些帖子就消失不见了，换句话说，数据库背着程序做了一些修改，而程序没有感知，如果修改的数据较多的话还可能造成数据库卡顿，想想都觉得恐怖，也就说当开发者在删除一个数据时，还得去考量外键约束会自动删除哪些表的数据，这样开发压力实在是太大了，本质就是埋坑，而且是大坑。其实数据库只需负责自己数据存储的责任就好了，至于一些约束还是交给程序来负责为好。

小心OR语句导致的全表扫描

下面这条语句，即使查询条件的列都有单独的索引，但数据库在执行查询时无法利用这些索引，导致本次查询执行全表扫描，当表很大时导致慢查询。

SELECT * FROM table WHERE column1 = 'value1' OR column2 = 'value2';

这个问题在实际编程中很容易发生，因为大多数开发者只注重在查询条件上添加索引，而不会很关注具体SQL的执行计划。比如原本的查询语句只有

SELECT * FROM table WHERE column1 = 'value1'

但后面加了个需求，需要额外加一个筛选条件column2，此时开发者很自然的使用OR 来组合这次查询，当然他也知道要在column2上加索引来加速查询，但实际上这个语句根本没利用到索引。

如果要优化这条SQL，那请使用UNION来实现：将查询拆分为多个子查询，每个子查询都可以利用索引。

SELECT * FROM table WHERE column1 = 'value1'
UNION ALL
SELECT * FROM table WHERE column2 = 'value2'

另一个优化思路是建复合索引，比如针对上面的OR查询，我们建立复合索引(column1, column2)，查询条件为 WHERE column1 = 'value1' OR column1 = 'value2'，那么复合索引会被利用上，因为查询条件中包含了索引的最左前缀列 column1。不过记得复合索引生效的前提：只有在查询条件中包含索引的最左前缀列时才能被有效利用。比如我们的OR查询顺序调整为:

SELECT * FROM table WHERE column2 = 'value2' OR column1 = 'value1';

这样的SQL是利用不上复合索引(column1, column2)，因为查询条件中没有包含索引的最左前缀列 column1，这要特别注意。