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

最新推荐文章于 2024-07-10 22:00:00 发布

Eleven-十一

最新推荐文章于 2024-07-10 22:00:00 发布

阅读量847

点赞数

分类专栏： JAVA学习文章标签：数据库 java mysql

原文链接：https://xie.infoq.cn/article/c31529ad245658f2ce3ca4ebd

版权

JAVA学习专栏收录该内容

8 篇文章 0 订阅

订阅专栏

文章介绍了如何利用位运算优化数据库设计方案，特别是在处理联系人组的联系方式组合时，通过位运算提高查询效率，包括存储和查询支持特定联系方式的联系人组。文章列举了不同设计方案的优缺点，推荐使用位运算来表示和查询多种联系方式的支持情况。

摘要由CSDN通过智能技术生成

文章目录

注：本文章为转载文章，位运算在数据库中的实际应用

0 前言

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

1 位运算概念

二进制位简称位（bit），其值为 0 或 1。计算机真正执行的正是由 0 和 1 构成的机器指令，计算机系统内数据也是由二进制表示的。位运算就是这些数据逐位进行计算的过程。主要的运算逻辑有以下几种（以 Java 为例）：
在这里插入图片描述
提醒一下，计算机系统中的数值都是以补码的形式存储的。因此在数值计算的时候都是对应的补码参与计算。

2 数据库中的应用

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 b
WHERE
	a.id = b.contact_group_id
	AND b.contact_type = 1;

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

SELECT
	a.*
FROM
	t_alert_contact_group a
WHERE
	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 方案总结

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