MySQL:每页多少个查询太多? [英] MySQL: How many queries per page is too many?

问题描述

我是MySQL的新手，对我来说，很快变得很明显的是，相对于其中的一些查询，每页创建多个数据库查询要容易得多.多少查询可能太多，或者在什么时候我应该花更多的宝贵时间来合并查询，花时间找出聪明的联接，等等.

I'm new to MySQL and something that's quickly becoming obvious to me is that it feels considerably easier to create several database queries per page as opposed to a few of them.... but I don't really have a feel for how many queries might be too many, or at what point I should invest more precious time to combining queries, spending time figuring out clever joins, etc.

因此，我想知道是否有经验的人在这里使用每页查询数方面的心理基准"，如果是，那么多少可能太多?

I'm therefore wondering if there are some kind of "mental benchmarks" experienced folks here use with regard to number of queries per page, and if so, how many might be too many?

我知道，在任何情况下正确的答案都与满足应用程序功能要求所需的条件有关.但是，在客户需求可能是灵活的或设置不当的项目上，或者在您作为开发人员具有完全控制权的项目上(例如，您自己开发的网站)，您基本上可以在功能和性能之间进行协商……如果编码要求影响性能而您又无法对其进行进一步优化，则只能削减一些琐碎的功能.

I understand that the correct answer in any context is related to what's needed to satisfy an application's functional requirements. However, on projects where client requirements may be flexible or not properly set, or on projects where you as the developer have full control (e.g. sites you develop for yourself), you may be able to negotiate between functionality and performance... basically, to just cut trivial features if coding requirements impact performance and you're unable to optimise it any further.

我对此表示感谢.

谢谢

推荐答案

这取决于页面的使用频率，应用服务器与数据库服务器之间的延迟以及许多其他因素.

It depends how often the page is used, the latency between the app server and database server, and a lot of other factors.

对于仅显示数据的页面，我的直觉是100太多了.但是，在某些情况下这是可以接受的.

For a page which only displays data, my gut feeling is that 100 is too many. However, there are some cases where that may be acceptable.

实际上，您仅应在必要时进行优化，这意味着您可以优化人们最常使用的页面，而忽略次要页面.

In practice you should only optimise where necessary, which means you optimise the pages that people use the most, and ignore the minor ones.

尤其是，这些页面对公众不可用，并且(很少)授权用户几乎从未使用过它们，因此没有动力使它们更快.

In particular, the pages are not available to the public and the (few) authorised users hardly ever use them, there is no incentive to make them faster.

如果您认为真正的性能问题是由于查询过多而引起的，请启用常规查询日志(恐怕会使性能更糟)并分析最常见的查询以消除它们.

If there is a real performance problem which you believe comes from having too many queries, enable the general query log (which may make performance worse, I'm afraid) and analyse the most common queries with a view to eliminating them.

您可能会发现有些垂头丧气的结果"-对很少更改的数据进行简单查询，这些查询在最流行的页面上被调用，您可以轻松消除(例如，让您的应用服务器在cron作业中获取数据放入本地文件并从那里读取).甚至是垂头丧气"之类的查询，这些都是完全不必要的.

You might find that there are some "low hanging fruits" - simple queries on rarely changing data which are called on most popular pages, which you can easily eliminate (for example, have your app server fetch the data on a cron job into a local file and read it from there). Or even "lower hanging fruits" like queries which are completely unnecessary.

尝试组合多个查询的困难在于，它倾向于与代码重用和代码可维护性背道而驰，因此，仅在绝对必要时才应这样做.听起来您还没有足够的数据来做出决定.

The difficulty with trying to combine multiple queries is that it tends to go against code-reuse and code maintainability, so you should only do it if it is ABSOLUTELY necessary; it doesn't sound like you have enough data yet to make that determination.

这篇关于MySQL:每页多少个查询太多?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！