我有一个大型的标准化订单数据数据库，查询报表的速度变得非常慢。我在报表中使用的许多查询联接了五个或者六个表，并且不得不检查成千上万的行。

查询很多，并且已对大多数查询进行了优化，以减少服务器负载并提高速度。我认为是时候开始以非规范化格式保留数据副本了。

关于方法有什么想法吗？我应该从几个最糟糕的查询开始，然后再去那里吗？

解决方案

回答

我知道这有点切线，但是我们是否尝试过查看是否可以添加更多索引？

我没有太多的数据库背景，但是最近我正在使用很多数据库，并且我发现仅通过添加索引就可以改善很多查询。

我们正在使用DB2，有一个名为db2expln和db2advis的命令，第一个将指示是否正在使用表扫描还是索引扫描，第二个将建议我们添加索引以提高性能。我确定MySQL有类似的工具...

无论如何，如果这是我们尚未考虑的事情，它对我有很大帮助...但是，如果我们已经走了这条路线，那么我想这不是我们想要的。

另一种可能性是"物化视图"(或者在DB2中称为"物化视图")，它使我们可以指定一个表，该表实际上是由多个表中的零件组成的。因此，我们可以提供此视图来访问数据，而不是对实际的列进行规范化……但是我不知道这是否会对插入/更新/删除操作产生严重的性能影响(但是如果它是"物化的"，那么应该会帮助选择，因为这些值在物理上是分开存储的)。

回答

MySQL 5确实支持视图，这在这种情况下可能会有所帮助。听起来我们已经做了很多优化，但是如果没有，我们可以使用MySQL的EXPLAIN语法来查看实际正在使用的索引以及使查询变慢的内容。

至于规范数据(无论我们是使用视图还是只是以更有效的方式复制数据)，我认为从最慢的查询开始并逐步进行是一个很好的方法。

回答

我们可能还需要考虑选择一个临时表，然后对该临时表执行查询。这样就不必为发出的每个查询都重新连接表(当然，假设我们可以将临时表用于多个查询)。这基本上为我们提供了非规范化的数据，但是如果我们仅执行选择调用，则无需担心数据的一致性。

回答

我对有关mysql的mssql的了解更多，但我不认为我们正在谈论的连接数或者行数不会导致使用正确索引的太多问题。我们是否分析了查询计划以查看是否缺少任何计划？

http://dev.mysql.com/doc/refman/5.0/en/explain.html

话虽如此，一旦我们对索引感到满意并用尽了所有其他途径，反规范化可能是正确的答案。如果仅存在一个或者两个查询问题，则手动方法可能比较合适，而某种数据仓库工具可能更适合创建用于开发数据立方体的平台。

我发现这是一个涉及该主题的网站：

http://www.meansandends.com/mysql-data-warehouse/?link_body%2Fbody=%7Bincl%3AAggregation%7D

这是一种简单的技术，如果我们一次只执行几个操作(并且我不是要替换OLTP表，而只是创建一个用于报告目的的新表)，则可以使用它使非规范化查询保持简单。假设我们在应用程序中有以下查询：

select a.name, b.address from tbla a 
join tblb b on b.fk_a_id = a.id where a.id=1

我们可以创建一个非规范化表，并使用几乎相同的查询进行填充：

create table tbl_ab (a_id, a_name, b_address); 
-- (types elided)

注意下划线与我们使用的表别名匹配

insert tbl_ab select a.id, a.name, b.address from tbla a
join tblb b on b.fk_a_id = a.id 
-- no where clause because you want everything

然后，要修复应用程序以使用新的非规范化表格，请在下划线处切换点。

select a_name as name, b_address as address 
from tbl_ab where a_id = 1;

对于庞大的查询，这可以节省大量时间，并清楚说明数据的来源，并且我们可以重复使用已有的查询。

记住，我只是提倡将此作为最后的手段。我敢打赌，有一些索引可以为我们提供帮助。而且，当我们进行非规范化时，请不要忘记考虑磁盘上的额外空间，并确定何时运行查询以填充新表。这可能应该在晚上或者活动量少的时候。当然，该表中的数据永远不会完全是最新的。

[另一个编辑]不要忘记，我们创建的新表也需要建立索引！好处是我们可以索引自己的内容，而不必担心更新锁争用，因为除了批量插入之外，该表仅会显示选择内容。

回答

与其他一些评论一致，我一定会看看索引编制。

我今年早些时候在我们的MySQL数据库上发现的一件事是复合索引的强大功能。例如，如果要报告日期范围内的订单号，则在订单号和订单日期列上的复合索引可能会有所帮助。我相信MySQL只能对查询使​​用一个索引，因此，如果我们在订单号和订单日期上只有单独的索引，则它只能决定使用其中一个索引。使用EXPLAIN命令可以帮助确定这一点。

为了显示具有良好索引(包括大量复合索引)的性能，我可以在数据库中运行将3个表连接起来的查询，并且在大多数情况下几乎可以立即获得结果。对于更复杂的报告，大多数查询会在10秒内运行。这3个表分别具有3300万，1.1亿和1.4亿行。请注意，我们也已经对它们进行了标准化，以加快对数据库的最常见查询。

有关表和报告查询类型的更多信息可能会提出进一步的建议。

回答

除了我以前的回答，我们在某些情况下采取的另一种方法是将关键报告数据存储在单独的摘要表中。即使经过非规范化和优化后，某些报表查询也会变得很慢，我们发现创建表并存储整个月的运行总计或者摘要信息使月末报表也变得更快。

我们发现这种方法易于实现，因为它不会破坏任何已经在起作用的东西，只是在某些时候插入了额外的数据库。

回答

我一直在研究复合索引，并且看到了一些真正的好处...也许我将设置一些测试，看看是否可以在这里节省我..至少要花更长的时间。

回答

对于MySQL，我喜欢这个话题："真实世界的网络：性能和可伸缩性，MySQL版"。这包含许多不同的建议，以提高MySQL的速度。