SQL插入行的顺序是否重要？

Question

SQL插入行的顺序是否重要？

3

我是一名数据库初学者，第一次探索数据仓库。我已经完成了从我们的生产系统（MS SQL Server 2012）复制大量数据到我们的数据仓库（MySQL）的过程。

我遇到的问题是，ETL过程可用的软件/硬件资源不足以使用单个查询复制我的较大表中的所有数据（程序会耗尽内存并崩溃）。为了解决这个问题，我通过在表ID上使用取模运算符添加where子句，将这些表分成了12个块，因为这种方法快速且易于编写。

SELECT * FROM table WHERE table.tableID % 12 = 0;
SELECT * FROM table WHERE table.tableID % 12 = 1;
SELECT * FROM table WHERE table.tableID % 12 = 2;
etc.

我现在想知道的是，这是否会影响我的数据仓库相对于原始数据库的性能。在旧的数据库中，数据大致按照时间顺序插入，但新的数据仓库情况不同。我对数据库引擎实际存储数据的方式了解不够，不知道这是否是一个问题。数据仓库上有与原始表格上一样的所有索引，但我不知道数据库引擎是否实际上会根据索引重新排列内存中的数据以使读取更快。我通过这种方式复制并插入数据，这样做是否有问题？

- busoni34

为什么在可以使用“orderby”时还要在意呢？ - emsimpson92

@emsimpson92 这是关于性能的问题，我想不是与 SQL 本身有关。 - connorg98

@connorg98 你是正确的。如果插入的顺序直接关系到数据在磁盘上的位置，那么按日期排序/筛选可能需要触及更多的内存块。再次强调，我只粗略地了解这些事情，不太了解DB底层的工作原理。 - busoni34

如果表上有一个聚集索引，它将按照该索引进行排列，正如Gordon在下面的答案中指出的那样。因此，像这样分割插入可能会导致您由于页面拆分而拥有更多的总数据页，但查询性能几乎不会受到影响。 - Jacob H

2个回答

1

如果您有相同的索引，数据将以更或少相同的方式存储，假设您在某列上有一个哈希索引，则该结构的实现在MySql DB和MySql服务器中将类似。问题是OLTP工作负载与OLAP不同，因此对于OLTP而言良好的索引并不一定适用于数据仓库，但这取决于您的数据。请参阅本文以更好地了解OLTP和OLAP之间的区别：oltp vs olap。尝试考虑如何减少表的基数，比如在您的OLTP系统中存储有关销售的信息，并且您有类似于以下内容：

|  DateTime        | Product | QTY |
| ---------------- | --------|-----|
| 2018-03-05 10:50 |  prod1  |  5  |

有一个拥有1亿条记录的表格。也许你只想存储某个日期内产品的数量，可以使用以下方式：

|    Date    |     Qty     |
|------------|-------------|
| 2018-03-05 |    10000    |

这将减少您的表的基数，提高应用程序的效率。

- GJCode

感谢您的反馈和有用的文章！ - busoni34

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

这可能不会有什么影响。通常情况下，只有在声明聚集索引（某种类型）时，数据库才能利用表内排序。如果已经声明了一个聚集索引，则数据将按照数据页上的顺序进行排序，而不管插入顺序如何。如果没有声明，则优化器无法利用排序。

有一些类型的查询（特别是exists），当读取数据时，其性能可能会受到实际顺序的影响。但这并不常见。如果表格不能放入内存，并且您依赖于类似的数据以提高性能，则性能可能会变差。这通常是一个错误的假设，但它可能是一些查询的潜在基础。

有一些情况下，数据排序可能会产生看起来正确的结果，但这些是“坏”的SQL：

- 查询没有ORDER BY子句，但希望按特定顺序返回结果。 - 查询使用MySQL中的误（特性），允许在聚合查询的SELECT中使用非聚合、非键列。 - 查询依赖于GROUP_CONCAT()中值的排序，但没有ORDER BY子句。

这些都是“坏”的，因为它们依赖于系统的观察行为，而不是文档化的行为（毫无疑问，我可能错过了一些）。

当然，您可以测试您的新系统，以查看是否存在这种情况。但是，插入顺序并不是我的首要关注点。