写点什么

位运算在数据库中的实际应用

  • 2023-01-17
    北京
  • 本文字数:5301 字

    阅读完需:约 17 分钟

位运算在数据库中的实际应用

前言

位运算应该是我们接触计算机课程最早学习的知识点,但是很快也被我们忘之脑后了,即使是参加了多年与计算机相关的工作之后,我们也几乎很少了解和使用位运算,仅是知道它的运算性能很高。而在之前的内部分享中我有幸给大家在理论层面分享过位运算的一些运算规则和使用技巧,之后一直想找机会能给大家分享一下位计算在实际的业务中能够如何地帮助到我们。恰逢近期我一直在做监控报警平台的设计和开发工作,其中有一些场景很适合拿出来和大家一起交流。



位运算概念


因为今天的重点在于讲应用,再加上篇幅有限,理论这块我就不做过多介绍,网上也能找到很多介绍详实的好文。下面我带大家回顾一下位运算的基础知识。

二进制位简称位(bit),其值为 0 或 1。计算机真正执行的正是由 0 和 1 构成的机器指令,计算机系统内数据也是由二进制表示的。位运算就是这些数据逐位进行计算的过程。主要的运算逻辑有以下几种(以 Java 为例):

提醒一下,计算机系统中的数值都是以补码的形式存储的。因此在数值计算的时候都是对应的补码参与计算。


数据库中的应用

2.1 场景描述

在最近开发的监控报警平台中,有一个联系人组的模块,需要展示联系人组中的联系人可覆盖的联系方式的组合,如下:



2.2 数据库设计方案

2.2.1 常见方案 1

建表方案 1

CREATE TABLE `t_alert_contact_group` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',  `contact_group_name` varchar(100) NOT NULL COMMENT '联系人组名称',  `status` tinyint(4) NOT NULL COMMENT '状态,-1 - 已删除, 0 - 停用, 1 - 启用',  `contact_number` int(11) DEFAULT NULL COMMENT '联系人数',  `include_email` tinyint(4) DEFAULT NULL COMMENT '是否包含email方式',  `include_phone` tinyint(4) DEFAULT NULL COMMENT '是否包含电话/短信方式',  `include_webhook` tinyint(4) DEFAULT NULL COMMENT '是否包含webhook方式',  `team_id` bigint(20) NOT NULL COMMENT '团队ID',  `create_user` varchar(100) NOT NULL COMMENT '创建人',  `update_user` varchar(100) NOT NULL COMMENT '更新的人',  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`id`)) COMMENT='联系人组信息';
复制代码



上面的做法是我们最常想到的一种方案,每种联系方式都有一个字段以 0 或 1 来记录是否支持。如果我们想要查询支持某种联系方式(比如 Email)的联系人组时,SQL 是这样的:

SELECT * FROM t_alert_contact_group WHERE include_email = 1;

如果前端需要支持多选查询的情况,比如要查询支持 Email 或者支持电话的联系人组时,SQL 是这样的:

SELECT * FROM t_alert_contact_group WHERE include_email = 1 OR include_phone = 1;

2.2.2 常见方案 2

建表方案 2

CREATE TABLE `t_alert_contact_group` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',  `contact_group_name` varchar(100) NOT NULL COMMENT '联系人组名称',  `status` tinyint(4) NOT NULL COMMENT '状态,-1 - 已删除, 0 - 停用, 1 - 启用',  `contact_number` int(11) DEFAULT NULL COMMENT '联系人数',  `include_contact_type` varchar(20) DEFAULT NULL COMMENT '包含的联系方式',  `team_id` bigint(20) NOT NULL COMMENT '团队ID',  `create_user` varchar(100) NOT NULL COMMENT '创建人',  `update_user` varchar(100) NOT NULL COMMENT '更新的人',  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`id`)) COMMENT='联系人组信息';
复制代码

在本方案中,假如我们分别用 1,2,3 表示支持 Email、Tel/SMS、Webhook,那我们在 include_contact_type 字段上存储值可能是类似 1,2,3 或者[1,2,3]这样的形式。但是如果我们想要使用 SQL 实现方案 1 中的两种查询场景,似乎并不友好。

2.2.3 常见方案 3

建表方案 3

CREATE TABLE `t_alert_contact_group` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',  `contact_group_name` varchar(100) NOT NULL COMMENT '联系人组名称',  `status` tinyint(4) NOT NULL COMMENT '状态,-1 - 已删除, 0 - 停用, 1 - 启用',  `contact_number` int(11) DEFAULT NULL COMMENT '联系人数',  `team_id` bigint(20) NOT NULL COMMENT '团队ID',  `create_user` varchar(100) NOT NULL COMMENT '创建人',  `update_user` varchar(100) NOT NULL COMMENT '更新的人',  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`id`)) COMMENT='联系人组信息';
CREATE TABLE `t_alert_contact_group_contact_type` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `contact_group_id` bigint(20) NOT NULL, `contact_type` tinyint(4) NOT NULL COMMENT '联系方式,1 - Email; 2 - 电话/短信;3 - Webhook', `team_id` bigint(20) NOT NULL COMMENT '团队ID', `create_user` varchar(100) NOT NULL COMMENT '创建人', `update_user` varchar(100) NOT NULL COMMENT '更新的人', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) COMMENT='联系人组包含的联系方式关联表';
复制代码

上述方案与前两种不同的地方是,我们使用一张关联表来表示每个联系人组支持的联系方式组合。同样的我们也来使用 SQL 实现前面提到的两个场景需求。

查询支持某种联系方式(比如 Email)的联系人组时,SQL 是这样的:

SELECT	a.*FROM	t_alert_contact_group a,	t_alert_contact_group_contact_type bWHERE	a.id = b.contact_group_id	AND b.contact_type = 1;
复制代码

查询支持 Email 或者支持电话的联系人组时,SQL 是这样的:

SELECT	a.*FROM	t_alert_contact_group aWHERE	a.id IN (	SELECT		b.contact_group_id	FROM		t_alert_contact_group_contact_type b	WHERE		b.contact_type = 1		OR b.contact_type = 2);
复制代码

2.2.4 推荐方案

建表方案 4

CREATE TABLE `t_alert_contact_group` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',  `contact_group_name` varchar(100) NOT NULL COMMENT '联系人组名称',  `status` tinyint(4) NOT NULL COMMENT '状态,-1 - 已删除, 0 - 停用, 1 - 启用',  `contact_number` int(11) DEFAULT NULL COMMENT '联系人数',  `included_contact_type` int(11) DEFAULT NULL COMMENT '涵盖的联系方式,TEL/EMAIL/WEBHOOK',  `team_id` bigint(20) NOT NULL COMMENT '团队ID',  `create_user` varchar(100) NOT NULL COMMENT '创建人',  `update_user` varchar(100) NOT NULL COMMENT '更新的人',  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`id`)) COMMENT='联系人组信息';
复制代码

当前这种方案正是我本次分享的重点,也是我在开发中实际使用的方案。接下来让我带大家了解一下我是怎样利用位运算的特性去实现产品需求的。

首先,我定义了一个枚举类来标识各种联系方式的类型。

public enum ContactType  {
EMAIL(1), WEBHOOK(1 << 1), SMS(1 << 2), TEL(1 << 3);
public static final Integer FULL_TYPE = (1 << ContactType.values().length) - 1;
@Getter private final Integer code;
ContactType(Integer code) { this.code = code; }}
复制代码

从定义中我们看到,code 的起始值是 1,其后枚举实例的 code 依次较前一个实例左移 1 位,这是当前这个方案必须遵循的规则,目的是为了让我们每个枚举实例对应的 code 值的所有 bit 位有且仅有一个 1,并各自分散在不同的位上。其实我们在很多开源代码上也会经常看到类似的定义。

接下来我们需要考虑数据库表中 included_contact_type 该怎么储存数据。我们先直接看代码。

private Integer calcIncludedContactType(List<AlertContactDTO> contacts) {    if (CollectionUtils.isEmpty(contacts)) {        return 0;    }
Integer contactType = 0; for (AlertContactDTO contact : contacts) { if (StringUtils.isNotBlank(contact.getContactEmail())) { contactType |= ContactType.EMAIL.getCode(); }
if (StringUtils.isNotBlank(contact.getContactTelephone())) { contactType |= ContactType.TEL.getCode(); contactType |= ContactType.SMS.getCode(); }
if (StringUtils.isNotBlank(contact.getContactWebhook())) { contactType |= ContactType.WEBHOOK.getCode(); }
if (contactType == ContactType.FULL_TYPE) { break; } }
return contactType;}
复制代码

按照上面的代码逻辑,如果我们想表示某一个联系人组既支持 Email 也支持 Webhook 的话,我们位计算方式应该是这样的:

// 因为我们目前的 type 只用四种,我们 code 值以 4 位 bit 来显示,方便展示。另外补充一句:正数的原码和补码相同 EAMIL.code (0001) | WEBHOOK.code (0010) = 0011 => 3

因此我们存入 included_contact_type 字段的值将会是 3。

说完了储存,我们接着说查询,同样的以前面的两个查询场景为例。在 MySQL 的中,SQL 语句是支持位计算的。我们来看看它们是怎么做的。

查询支持某种联系方式(比如 Email)的联系人组

// 我们需要查询支持什么方式,included_contact_type 就与谁的 code 值按位与计算即可 SELECT * FROM t_alert_contact_group WHERE included_contact_type & 1;

查询支持 Email 或者支持电话的联系人组

SELECT * FROM t_alert_contact_group WHERE included_contact_type & 1 OR included_contact_type & 8;

如果我们真的使用上述 SQL 的方式去实现查询场景,那在查询性能上来说和前面的方案相比较没有什么优势,而其实我们需要借助 Java 代码的辅助,可以不让我们的 SQL 语句中出现 OR、LIKE 这些不怎么友好的关键字。直接上代码:

/** * 获取包含目标类型的可能的code值. * * @param targetType * @return */List<Integer> getPossibleCodes(ContactType targetType) {    List<Integer> possibleCodes = new ArrayList<>();    for (int i = 0; i < FULL_TYPE; i++) {        if ((i & targetType.getCode()) == targetType.getCode()) {            possibleCodes.add(i);        }    }
return possibleCodes;}
复制代码

通过上面代码我们可以获取到包含目标联系方式的 code 值的所有状态值,以此集合带入到 SQL 的 IN 中就可以想要的结果了。

SELECT * FROM t_alert_contact_group WHERE included_contact_type IN (xx, xx, xx);

最后,我补充一下联系方式的回显。

/** * 回显支持的联系方式. *  * @param includeContactType * @return */List<ContactType> getAllIncludeContactType(Integer includeContactType) {    if (includeContactType <= 0) {        return Collections.emptyList();    }
List<ContactType> includeContactTypes = new ArrayList<>(); for (ContactType contactType : ContactType.values()) { if ((contactType.getCode() & includeContactType) == contactType.getCode()) { includeContactTypes.add(contactType); } }
return includeContactTypes;}
复制代码

2.3 方案总结

总体比较下来,要实现类似上述的需求场景,引入位计算的种种特性是比较值得推荐的方案。至于缺点中提到的代码不易理解,在大家阅读了本篇文章之后应该不成问题。以上就是我借由工作中实际遇到的场景给大家分享的所有内容,其实除了文章使用到的位计算技巧,还有很多有意思的用法,这次分享另一个目的就是希望能引发大家对位计算研究的兴趣,希望大家可以在之后的时间里去扩展学习位计算并应用。



关于领创集团

(Advance Intelligence Group)

领创集团成立于 2016 年,致力于通过科技创新的本地化应用,改造和重塑金融和零售行业,以多元化的业务布局打造一个服务于消费者、企业和商户的生态圈。集团旗下包含企业业务和消费者业务两大板块,企业业务包含 ADVANCE.AI 和 Ginee,分别为银行、金融、金融科技、零售和电商行业客户提供基于 AI 技术的数字身份验证、风险管理产品和全渠道电商服务解决方案;消费者业务 Atome Financial 包括亚洲领先的先享后付平台 Atome 和数字金融服务。2021 年 9 月,领创集团完成超 4 亿美元 D 轮融资,融资完成后领创集团估值已超 20 亿美元,成为新加坡最大的独立科技创业公司之一。


发布于: 刚刚阅读数: 3
用户头像

智慧领创美好生活 2021-08-12 加入

AI技术驱动的科技集团,致力于以技术赋能为核心,通过科技创新的本地化应用,改造和重塑金融和零售行业,以多元化的业务布局打造一个服务于消费者、企业和商户的生态圈,带来个性化、陪伴式的产品服务和优质体验。

评论

发布
暂无评论
位运算在数据库中的实际应用_数据库_领创集团Advance Intelligence Group_InfoQ写作社区