Hi 我是Joney 各位朋友 好久不见哈，祝愿大家2024新年大吉！ 那么在2024年 本系列我们将迎来一大波更新，欢迎各位道友关注和订阅。为了成体系的收集这些知识，我把他们整理成了一本册子 欢迎大家阅读 iamjoney.com/ 👏

本期摘要

在前面的RBAC系统中，我们对DB的使用 多是用 各种关联键来进行操作，但是这种做法我认为只是适合小规模的应用，在实际的场景中 业务往往都是变来变去，如果使用关联键会让整个维护变得异常的辛苦，本章我们主要是想办法去优化这个问题，我们采取与关联键不一样的做法去处理表之间的关联关系 ——> 冗余键（Redundant Keys）

什么是冗余键设计

冗余键是指在一个表中使用字段而不是外键包含另一个表的关键信息(比如ID)，以便加快查询速度或简化数据检索。以下是一些常见的冗余键设计方法：

1. 冗余键（Redundant Keys） ：将另一个表的主键或唯一键存储在当前表中作为冗余键，以避免频繁的表连接操作。这样可以提高查询速度，但会增加数据冗余和维护成本。
2. 派生键（Derived Keys） ：在当前表中计算生成另一个表的键值，并将其存储为派生键。这种方法适用于需要频繁查询的情况，可以提高查询效率，但需要确保派生键的准确性和一致性。
3. 索引键（Indexed Keys） ：在当前表中创建索引，包括非唯一索引和唯一索引，以加速查询操作。索引键可以针对经常被查询的列或列组合创建索引，提高检索效率。
4. 摘要键（Summary Keys） ：将另一个表的汇总信息存储在当前表中，以便快速获取汇总结果而不必进行复杂的表连接和聚合操作。这种设计方法适用于需要频繁进行汇总查询的场景。
5. 聚集键（Aggregated Keys） ：将另一个表的聚合结果存储在当前表中，以加速数据访问和分析。聚集键通常用于数据仓库或数据分析系统中，可以提高数据查询和报告生成的效率。

这些设计方法可以根据具体的业务需求和数据库性能优化的目标进行选择和应用，需要权衡查询性能、数据一致性和存储效率等方面的因素。在Newegg 我们使用SQLServer 其中大部分设计都是基于 冗余键 的。我个人的观点是：在业务初期发展阶段，总是有许多的变数，非常的不稳定，我们可以使用 冗余键 进行 小步快跑的 验证新业务，当业务趋于稳定之后我们再考虑使用关联关系，这是一个逐步的“精益求精”的过程。

一个简单的例子

举例说明，使用冗余键 设计的 Student 和 Course 以及Book 表之间的关系：“一个课程可以被多个学生选择，一个学生只能选一个课程，一个课程中包含多本书籍，一本书籍可以被多个课程包含”

-- 学生表
CREATE TABLE Student (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(255),
    selected_course_id INT DEFAULT NULL
    -- 冗余键，存储学生所选课程的课程编号
);

-- 课程表
CREATE TABLE Course (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(255),
    book_ids VARCHAR(255) DEFAULT NULL
    -- 冗余键，存储该课程所需的所有书籍的书籍编号列表 (‘book1_id,book2_id’)
);

-- 书籍表
CREATE TABLE Book (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    book_title VARCHAR(255),
    included_in_courses VARCHAR(255) DEFAULT NULL 
    -- 冗余键，存储包含该书籍的所有课程的课程编号列表，默认为 NULL
);

下面我们对这些table进行CRUD

-- 学生表
CREATE TABLE Student (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(255),
    selected_course_id INT DEFAULT NULL
    -- 冗余键，存储学生所选课程的课程编号
);

-- Design DB
-- 课程表
CREATE TABLE Course (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(255),
    book_ids VARCHAR(255) DEFAULT NULL
    -- 冗余键，存储该课程所需的所有书籍的书籍编号列表 (‘book1_id,book2_id’)
);

-- 书籍表
CREATE TABLE Book (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    book_title VARCHAR(255),
    included_in_courses VARCHAR(255) DEFAULT NULL 
    -- 冗余键，存储包含该书籍的所有课程的课程编号列表，默认为 NULL
);

-- INSERT DATA
INSERT INTO Student (student_name) VALUES
('Joney.s.li');

-- 插入课程数据
INSERT INTO Course (course_name) VALUES
( '计算机科学基础');

-- 插入书籍数据
INSERT INTO Book (book_title) VALUES
('计算机科学导论1'),
('计算机科学导论2'),
('计算机科学导论3'),
('计算机科学导论4'),
('SQL必知必会');

这种设计方案中，冗余键的作用是为了简化查询操作，减少表连接的次数，提高查询效率。

• 在 Student 表中，course_id 是冗余键，它存储了每个学生所选课程的课程编号。这样可以直接通过学生表就可以获取到学生所选课程的信息，而不必每次都进行 Course 表的连接操作。
• 在 Course 表中，book_ids 是冗余键，它存储了每门课程所需的所有书籍的书籍编号列表。这样可以直接通过课程表就可以获取到课程所需书籍的信息，而不必每次都进行 Book 表的连接操作。
• 在 Book 表中，course_ids 是冗余键，它存储了每本书籍所包含的所有课程的课程编号列表。这样可以直接通过书籍表就可以获取到包含该书籍的所有课程的信息，而不必每次都进行 Course 表的连接操作。

下面我们来操作这个DB

-- Build relations

SET @student_id := ( SELECT student_id FROM Student WHERE student_name = 'Joney.s.li' );

UPDATE Student SET selected_course_id = (
    SELECT course_id FROM Course WHERE course_name = '计算机科学基础'
) WHERE student_id = @student_id ;

-- 查询所有的书籍并关联到课程名为“计算机科学基础”的课程中
-- 查询课程名为“计算机科学基础”的课程ID
SET @course_id := (SELECT course_id FROM Course WHERE course_name = '计算机科学基础');

-- 更新所有书籍并关联到课程名为“计算机科学基础”的课程中
UPDATE Book 
SET included_in_courses = CONCAT_WS(',', included_in_courses, @course_id)
WHERE included_in_courses IS NOT NULL;

-- 更新Course
-- 获取所有书籍的 book_id 列表
SET @all_books := (SELECT GROUP_CONCAT(book_id) FROM Book);

-- 更新课程名为 '计算机科学' 的课程的 book_ids
UPDATE Course SET book_ids = @all_books WHERE course_id = @course_id;

-- 查询
-- 获取小明的学生ID
SET @student_id := (SELECT student_id FROM Student WHERE student_name = 'Joney.s.li');

-- 查询小明选的课程以及每门课程包含的书籍
SELECT s.student_name, c.course_name, b.book_title
    FROM Student s
        JOIN Course c ON s.selected_course_id = c.course_id
        JOIN Book b ON FIND_IN_SET(b.book_id, c.book_ids)
    WHERE s.student_id = @student_id;

回来我们修改原来的RBAC系统

实际上我们并不需要大刀阔斧的去改造，我们只需要把需要的DB操作填入其中就可以了 关于其中的sql注入等问题，我们需要借助 ORM的能力 TypeOrm提供的一个 允许运行原始 SQL的方法 QueryBuilder 可以有效的解决一部分的 SQL安全注入问题

1. 我们在原来的工程上 修改，打开 test_module/ 中的controller 加入route

  @Get('/t6')
  @NotAuth()
  async t6() {
    return this.tsService.queryBuilderTest();
  }

2.更新service

  // queryBuilder
  async queryBuilderTest() {
    const query = `
      SELECT * FROM user_info 
        WHERE username = ?;
    `;
    const inset = `
    INSERT INTO user_info (isDeleted, username, email, \`password\`, state) VALUE 
    ( 1,'laoli1','a@emaul.com','123',0 );
    `;
    // const value = await this.userInfoRepository.query(query, ['joeny']);
    const value = await this.userInfoRepository.query(inset, ['joeny']);
    console.log('value -->', value);
    return value;
  }

你发现他们执行出来结果不能作为 实体 直接使用 , 如何处理这个问题 只需要

  // queryBuilder
  async queryBuilderTest() {
    const query = `
      SELECT * FROM user_info 
        WHERE username = ?;
    `;
    // const inset = `
    // INSERT INTO user_info (isDeleted, username, email, \`password\`, state) VALUE
    // ( 1,'laoli1','a@emaul.com','123',0 );
    // `;
    const value = await this.userInfoRepository.query(query, ['joeny']);
    // const value = await this.userInfoRepository.query(inset, ['joeny']);

    // 如果你需要设置成 Entity 请自行转化, 当然啦 这一代点哇我们完全可以从 SQL review中
    // 提前预防和设计
    const EntityValue = this.userInfoRepository.create(value[0]); // [!code focus]
    console.log('EntityValue -->', EntityValue); // [!code focus]

    console.log('value -->', value);
    return value;
  }

这种原始的SQL，比起我们之前 费尽功夫的组装数据 要省事太多太多了！，比如下面的代码可以直接用简单的sql代替

原来的代码(吐槽一下 又长又臭 又不优雅！)

  async getRoleDetail(id: number) {
    const sqlRes = await this.entityManager.findOne(RoleInfo, {
      where: { id: id },
      relations: {
        rolePermissions: {
          permission: {
            permissionMenus: {
              menu: true,
            },
            permissionABs: {
              actionButton: true,
            },
          },
        },
      },
    });

    const menusMap = new Map();
    const menus = sqlRes.rolePermissions.reduce((prev, rolePermissions) => {
      const permission = rolePermissions.permission;
      const permissionMenus = permission.permissionMenus;
      permissionMenus.forEach((permissionMenu) => {
        const menu = permissionMenu.menu;
        if (!menusMap.has(menu.id)) {
          menusMap.set(menu.id, true);
          prev.push(menu);
        }
      });
      return prev;
    }, []);

    const actionButtonMap = new Map();
    const actionButtons = sqlRes.rolePermissions.reduce(
      (prev, rolePermissions) => {
        const permission = rolePermissions.permission;
        const permissionABs = permission.permissionABs;
        permissionABs?.forEach((permissionMenu) => {
          const actionButton = permissionMenu.actionButton;
          if (actionButton && !actionButtonMap.has(actionButton.id)) {
            actionButtonMap.set(actionButton.id, true);
            prev.push(actionButton);
          }
        });
        return prev;
      },
      [],
    );

    return {
      ...sqlRes,
      menus,
      actionButtons,
    };
  }

直接使用sql（ 🥳 看起来优雅多了 对吗？）

-- 查询角色详细信息、菜单信息和动作按钮信息
SELECT
    ri.*,
    m.* AS menu,
    ab.* AS actionButton
FROM
    RoleInfo ri
LEFT JOIN
    RolePermission rp ON ri.id = rp.roleId
LEFT JOIN
    Permission p ON rp.permissionId = p.id
LEFT JOIN
    PermissionMenu pm ON p.id = pm.permissionId
LEFT JOIN
    Menu m ON pm.menuId = m.id
LEFT JOIN
    PermissionActionButton pab ON p.id = pab.permissionId
LEFT JOIN
    ActionButton ab ON pab.actionButtonId = ab.id
WHERE
    ri.id = :roleId;

总结

以上就是本期文章了，内容不是很多，但是还是希望对大家有帮助，按照我的工作经验来说，在一些公司中 我们并不会直接使用各种各样的 orm ,更多倾向于 使用原始的SQL ，因为他的课阅读性 和 可维护性，以及 跨团队，都是比orm 来的方便的。