网页数据库查询优化
我正在整理一个网页，就数据库点击率而言，该网页相当“昂贵”。我不想在这个阶段开始优化 - 尽管我试图在截止日期前完成，但我最终可能根本不进行优化。
目前该页面需要对数据库进行 18 次（没错，是 18 次）点击。我已经在使用联接，并且一些查询是 UNIONed 的，以最大限度地减少对数据库的访问。我的本地开发机器可以处理这个（页面并不慢），但是，我觉得如果我将其发布到野外，查询的数量将很快淹没我的数据库（MySQL）。
我总是可以使用内存缓存或类似的东西，但我更愿意继续需要在截止日期之前完成的其他开发工作 - 至少检索页面工作 - 现在这只是一个优化问题（如果需要）。
因此，我的问题是 - 单页检索的 18 db 查询是否完全令人无法容忍 - （即我应该搁置所有内容并优化检索逻辑的地狱），或者我应该像往常一样继续，在截止日期前按时发布并发布看看会发生什么？
[编辑]
只是为了澄清一下，我已经完成了“明显”的事情，例如对查询中使用的字段使用（单个和复合）索引。我还没有做的是运行查询分析器来查看我的索引等是否是最佳的。
I am putting together a web page which is quite 'expensive' in terms of database hits. I don't want to start optimizing at this stage - though with me trying to hit a deadline, I may end up not optimizing at all.
Currently the page requires 18 (that's right eighteen) hits to the db. I am already using joins, and some of the queries are UNIONed to minimize the trips to the db. My local dev machine can handle this (page is not slow) however, I feel if I release this into the wild, the number of queries will quickly overwhelm my database (MySQL).
I could always use memcache or something similar, but I would much rather continue with my other dev work that needs to be completed before the deadline - at least retrieving the page works - its simply a matter of optimization now (if required).
My question therefore is - is 18 db queries for a single page retrieval completely outrageous - (i.e. I should put everything on hold and optimize the hell of the retrieval logic), or shall I continue as normal, meet the deadline and release on schedule and see what happens?
[Edit]
Just to clarify, I have already done the 'obvious' things like using (single and composite) indexes for fields used in the queries. What I haven't yet done is to run a query analyzer to see if my indexes etc are optimal.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
18 个数据库查询可能有点过大，除非它是某种复杂的门户；尽管无法 100% 知道页面是什么以及服务器后端代码，但很难判断。
额外查询的主要成本通常是为其建立数据库连接以及查询往返的成本。
对于前者，确保你的后端支持共享数据库连接池（我假设你使用 PHP，所以我没有任何实用的建议，但 Java 和 Perl 都有实现这一点的方法）；当然，请确保一个页面加载对整个页面重复使用相同的数据库连接。
对于后者（较少的查询），请查看：
将所有查询捆绑到具有多个结果集的单个大型查询中
对结果进行反规范化像您已经做的那样通过 JOIN 和 UNION 进行设置
另外，请考虑在您的 web 应用程序和数据库（memcache 或缓存数据的应用程序服务器）之间设置一个中间层）。
然而，我必须说，实际上，我建议不要执行上述任何操作，直到您针对产品服务器和基准测试应用程序并使用基准测试和分析找到慢点。
更新：为了回答评论中的怀疑论者，这里有一些有关连接成本的信息，特别是与 mysql
http://mysql-dox.net/Sams-MySQL.Database.Design.and/0672327651/ch14lev1sec3.html
(Google 缓存)
18 db queries is probably a bit of overkill unless it's some kind of complicated portal; although without knowing 100% what the page is and the server back-end code it's hard to judge.
The main cost of extra query is usually the cost of establishing the database connection for it as well as query round-trip.
For the former, make sure that your back-end supports shared pool of DB connections (I assume you use PHP so I don't have any practical advice, but both Java and Perl have ways of achieving that); and of course make sure one page load reuses the same DB connection for the entire page.
For the latter (less queries), look into:
Bundling all queries into a single large query with multiple result sets
De-normalizing your result sets via JOIN and UNION like you already do
Also, consider having a middle tier between your webapp and the DB (memcache, or an app server that caches data).
However, I must say that practically, I'd advise against doing any of the above until you test the app against prod server and benchmark and find the slow points using benchmarks and profiling.
UPDATE: To answer the skeptic in the comment, here's some info on the cost of connections, specifically as related ot mysql
http://mysql-dox.net/Sams-MySQL.Database.Design.and/0672327651/ch14lev1sec3.html
(Google cache)
你的做法是完全错误的。
这些“数据库之旅”并没有什么不好的。
不惜一切代价尽量减少查询数量的尝试可能会导致查询缓慢和性能灾难
Your approach is totally wrong.
There is noting bad in these "trips to the db".
And your attemts to minimize query number at any cost may lead you to slow queries and performance disaster
您是否在多个页面上检索相同的信息？如果您是，则可以将该信息从一个页面传递到另一个页面，而不是每次都查询数据库。
例如，假设您在每个页面的顶部显示用户名（就像这样）。将这些信息从一个页面传递到另一个页面可能比每次都查询数据库更有意义。我知道这是一个明显的例子，但我希望它能证明我想说的内容。
Are you retrieving the same information across multiple pages at all? If you are it may be possible to pass that information from page to page rather than querying the DB every time.
For instance, say you are displaying a users name at the top of every page (like SO does). It may make more sense to pass this information from page to page rather than query the DB for it every time. Kind of an obvious example i know, but I hope it demonstrates what i'm trying to say.
18 个查询不是问题，只要它们快速且高效。
但是，如果您觉得太多，也许您应该看一下更大的图片并确定该页面是否试图做太多事情。
18 queries aren't a problem, provided they are fast and efficient.
However, if you feel that is too many, maybe you should take a look at the larger picture and determine if that page is trying to do too much.