主页 / dba / 问题 / 74693
Accepted
Gili
Asked: 2014-08-23 09:17:05 +0800 CST

如何在不损失性能的情况下将表分成两部分？

  • 772

根据https://stackoverflow.com/a/174047/14731，拆分不常用的列可以释放缓存，从而可以更快地检索常用列。

我有一个表，其列总是一起检索，但出于设计原因，我仍然想将它们拆分（减少跨多个表的重复，提高代码重用）。例如，我有不同的表使用相同的权限方案。我不想向每个表添加权限列，而是使用外键来引用单独的权限方案表。

我已经用 100 万行填充了 MySQL，对两个版本都运行了查询，发现带有 JOIN 的版本慢了大约 3 倍（0.9 秒对 2.9 秒）。

这是我的表：

original
(
    id BIGINT NOT NULL,
    first BIGINT NOT NULL,
    second BIGINT NOT NULL,
    third BIGINT NOT NULL
);
part1
(
    id BIGINT NOT NULL,
    first BIGINT NOT NULL,
    second BIGINT NOT NULL,
    PRIMARY KEY(id)
);
part2
(
    link BIGINT NOT NULL,
    third BIGINT NOT NULL,
    FOREIGN KEY (link) REFERENCES part1(id)
);

这是我的查询：

SELECT first, second, third FROM original;
SELECT part1.first, part1.second, part2.third FROM part1, part2 WHERE part2.link = part1.id;

有什么办法可以降低拆分设计的性能开销？

如果您想在您身边重现此测试，您可以使用以下 Java 应用程序生成 SQL 脚本来填充数据库：

import java.io.FileNotFoundException;
import java.io.PrintWriter;

public class Main
{
    public static void main(String[] args) throws FileNotFoundException
    {
        final int COUNT = 1_000_000;
        try (PrintWriter out = new PrintWriter("/import.sql"))
        {
            for (int i = 0; i < COUNT; ++i)
                out.println("INSERT INTO original VALUES (" + i + ", " + i + ", 0);");
            out.println("INSERT INTO original VALUES (" + (COUNT - 2) + ", " + (COUNT - 1) +
                ", 1);");
            out.println();
            for (int i = 0; i < COUNT; ++i)
            {
                out.println("INSERT INTO part1 (first, second) VALUES (" + i + ", " + i + ");");
                out.println("INSERT INTO part2 VALUES (LAST_INSERT_ID(), 0);");
            }
            out.println("INSERT INTO part1 (first, second) VALUES (" + (COUNT - 2) + ", " +
                (COUNT - 1) + ");");
            out.println("INSERT INTO part2 VALUES (LAST_INSERT_ID(), 1);");
            out.println();
        }
    }
}
mysql database-design

2 个回答

  1. 选项 #1：使用 INT UNSIGNED 而不是 BIGINT

    如果字段不会超过4,294,967,295，请将它们更改为INT UNSIGNED

    ALTER TABLE part1
    MODIFY COLUMN id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
    MODIFY COLUMN first  INT UNSIGNED NOT NULL,
    MODIFY COLUMN second INT UNSIGNED NOT NULL;
ALTER TABLE part2
    MODIFY COLUMN link  INT UNSIGNED NOT NULL,
    MODIFY COLUMN third INT UNSIGNED NOT NULL;

    较小的数据类型，特别是对于 JOIN 键，将使相同的查询运行得更快。

    如果字段不会超过16,777,215，请使用MEDIUMINT UNSIGNED更小的列。

    选项#2：使用更大的连接缓冲区

    将此添加到my.cnf

    [mysqld]
join_buffer_size = 16M

    然后，登录 MySQL 并运行

    mysql> SET GLOBAL join_buffer_size = 1024 * 1024 * 16;

    不需要重启 MySQL。

    请参阅有关join_buffer_size的 MySQL 文档

    选项#3：确保link已编入索引

    既然你有FOREIGN KEY参考，这是一个相当有争议的问题。如果您没有FOREIGN KEY，请确保链接已编入索引：

    ALTER TABLE part2 ADD UNIQUE KEY (link);

    试试看 ！！！

    更新 2014-08-22 17:13 EDT

    我使用以下方法创建了我自己的示例数据版本：

    DROP DATABASE IF EXISTS GILI; CREATE DATABASE GILI;
USE GILI
create table original 
(
    id mediumint unsigned not null auto_increment,
    first mediumint unsigned not null,
    second mediumint unsigned not null,
    third mediumint unsigned not null,
    primary key (id)
);
create table part1 like original;
alter table part1 drop column third;
create table part2 select third from original;
alter table part2 add link mediumint unsigned not null first;
alter table part2 add primary key (link);
insert into original (first,second,third) values (1,2,3);
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into original (first,second,third) select first,second,third from original;
insert into part1 (id,first,second) select id,first,second from original;
insert into part2 (link,third) select id,third from original;
select count(1) from (select * from original) A;
select count(1) from (select * from part1 inner join part2 on part1.id = part2.link) A;

    结果与您的大致相同：大约慢 3 倍。我读了你的stackoverflow链接。然后我开始思考：看看我们使用的样本。我们基本上将 MySQL 推到了极限，将 1M 行与 1M 行连接起来。这是最坏的等值连接方案。综合考虑，性能还是不错的。

    我记得我的大学时代，我必须使用链表创建一个二维数组来构造一个稀疏数组。如果特定坐标中不存在节点，则数组的默认值在应用程序中定义为零。然后，想象一下。创建一个 1000x1000 稀疏数组，其中所有 1000000（100 万）个坐标都有一个非零值表示。现在，您至少有 200.2 万个指针映射所有相邻节点。这是对数据的 100 万个 4 字节整数的补充。从其中提取单个值需要更多的 CPU 用于导航而不是检索实际数据。

    执行从 part1 到 part2 的 1M 行的 INNER JOIN，其中 part2 绝对具有每个键，需要更多资源用于导航（临时表创建、键比较、值填充）。如果 LEFT JOIN 的右侧不是很稀疏或 LEFT JOIN 的左侧很大，则非规范化有时会令人沮丧。在您的情况下，如果必须经常一起大量引用它们，那么将原始文件分成第 1 部分和第 2 部分对您没有任何好处。换句话说，分离不形成重复组的列并不是真正的规范化。

    我给出的 3 个选项对 part1 和逐行获取 part2 有好处。

    考虑以下情况：

    • 您只需要 part1 中的一些行
      • 你信任 MySQL 查询优化器吗
      • 在加入 part2 之前，您是否重构查询以获取 part1 的横截面？
      • 您一次只从第 2 部分检索一行吗？
    • 使用 LEFT JOIN 并了解给定 part1 没有 part2
      • 在应用程序中为第 2 部分设置默认值？
      • 为那个 part1 填充 part2 ？

    更多地考虑您计划如何检索数据、单个查询中需要多少数据以及如何构建查询将成为您寻求良好性能的决定因素。

    结语

    由于进行 JOIN 的成本，您不会获得更好的性能。这就像试图将铅变成黄金（理论上可能，实际上和经济上都不可能）。

    你最好的选择是让桌子保持原来的状态。

    • 1
  2. Best Answer

    这正是在有限范围内使用归一化并在性能测试之后的原因。规范化是以连接（排序）为代价的。5NF 上 DWH 的主要目的是安全地存储数据，而不是快速检索数据。

    备选方案 1 有一个物化视图的概念：保存在硬盘驱动器上的视图。MySQL 没有提供开箱即用的功能，但这篇文章 - MySQL 的物化视图- 解释了如何通过 SP 更新/刷新表来重新创建此功能。

    物化视图 (MV) 是查询的预计算（物化）结果。与简单的 VIEW 不同，物化视图的结果存储在某处，通常在表中。当需要立即响应并且物化视图所基于的查询需要很长时间才能产生结果时，使用物化视图。物化视图必须不时刷新。这取决于物化视图的刷新频率及其内容的实际程度。基本上，物化视图可以立即或延迟刷新，它可以完全刷新或到某个时间点。MySQL 本身不提供物化视图。

    备选方案 2 您可以尝试通过其他方式来实现您的设计。与其拆分主表，不如从主表创建 2-3 个视图或表。这样，您将拥有具有不同值的星型模式的标准化表，并且您将保留主快速表。

    性能调优总是关于 CPU（时间）、RAM 和 IO（吞吐量或空间）之间的权衡。在这种情况下，它位于 CPU 和 IO 之间。

    • 1

相关问题