实现更好的数据库性能
我有一个由关系数据库支持的网站，该数据库由常见的电子商务相关表（订单、订单项、购物车、信用卡、付款、客户、地址等）组成。
存储过程。由于数据量+必须发生的大量连接，返回订单历史记录的速度非常慢，并且根据搜索参数，它有时会超时（尽管有索引）。
数据库模式已经很好地规范化了，我相信通过转向数据仓库之类的东西可以实现更好的性能。 DW 项目并不简单，而且存在保持数据同步的问题，所以我想知道是否有人知道捷径。也许是一个开箱即用的解决方案，将创建 DW 模式并保持数据同步（可能通过触发器）。我听说过 Lucene，但它似乎更适合文本搜索和文档管理。还有人有其他建议吗？
I have a website backed by a relational database comprised of the usual e-commerce related tables (Order, OrderItem, ShoppingCart, CreditCard, Payment, Customer, Address, etc...).
The stored proc. which returns order history is painfully slow due to the amount of data + the numerous joins which must occur, and depending on the search parameters it sometimes times out (despite the indexing that is in place).
The DB schema is pretty well normalized and I believe I can achieve better performance by moving toward something like a data warehouse. DW projects aren't trivial and then there's the issue of keeping the data in sync so I was wondering if anyone knows of a shortcut. Perhaps an out-of the box solution that will create the DW schema and keep the data in sync (via triggers perhaps). I've heard of Lucene but it seems geared more toward text searches and document management. Does anyone have other suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
你的数据库有多大？
确实没有任何捷径，但维度建模实际上并不那么难。您首先确定一个粒度，然后需要识别您的事实以及与事实相关的维度。然后将维度划分为表，这样维度就可以随着时间的推移缓慢增长。维度的选择是完全实用的并且基于数据行为。
我建议你看看金博尔的书。
对于几 GB 的数据库，当然可以每天多次从头开始更新报告数据库（没有历史记录，只是从 3NF 重新填充相同数据的不同模型）。有些实时数据仓库技术只是全天连续应用更改。
因此，虽然 DW 项目可能并不简单，但非规范化技术非常平易近人且可用，而无需构建完整的时不变数据仓库。
How big is your database?
There's not really any shortcuts, but dimensional modelling is really NOT that hard. You first determine a grain and then need to identify your facts and the dimensions associated with the facts. Then you divide the dimensions into tables which allow you to have the dimensions only grow slowly over time. The choice of dimensions is completely practical and based on the data behavior.
I recommend you have a look at Kimball's books.
For a database of a few GB, it's certainly possible to update a reporting database from scratch several times a day (no history, just repopulating from a 3NF for a different model of the same data). There are certain realtime data warehousing techniques which just apply changes continuously throughout the day.
So while DW projects might not be trivial, the denormalization techniques are very approachable and usable without necessarily building a complete time-invariant data warehouse.
物化视图是您可能在 Oracle 中使用的视图。它们为您提供了您正在寻找的“保持数据同步”功能以及聚合数据的快速访问。由于您没有提及平台的任何细节（平台、服务器规格、行数、每秒点击次数等），因此我无法提供更多帮助。
当然，我们假设您已经检查过您的所有 SQL 是否都已正确且最佳地编写，您的索引是否正确，您在应用程序的所有级别中正确使用了缓存，您的数据库服务器有足够的 RAM，速度快另外
，您是否考虑过对架构进行非规范化，使其足以更快地提供最常见的查询？这比实现整个数据仓库要好，而这可能不是您想要的。通常，数据仓库用于报告目的，而不是用于服务交互式应用程序。
Materialized Views are what you might use in Oracle. They give you the "keeping the data in sync" feature you are looking for combined with fast access of aggregate data. Since you didn't mention any specifics (platform, server specs, number of rows, number of hits/second, etc) of your platform, I can't really help much more than that.
Of course, we are assuming you've already checked that all your SQL is written properly and optimally, that your indexing is correct, that you are properly using caching in all levels of your app, that your DB server has enough RAM, fast hard drives, etc.
Also, have you considered denormalizing your schema, just enough to serve up your most common queries faster? that's better than implementing an entire data warehouse, which might not even be what you want anyway. Usually a data warehouse is for reporting purposes, not for serving interactive apps.