我的网站项目大约投入了 10%，我正在尝试弄清楚我给数据库带来了什么样的负载。 当用户登录时，我有许多每分钟都会触发的功能，每次有人访问该页面时，都会提取许多元素（例如区号列表、州和国家/地区）来构建注册页面。 我确信我可以将其中一些转移到 PHP，这样就不会涉及数据库。
在 6 天 14 小时 57 分 58 秒内，我显示了 120,998,563 个查询，平均每分钟 12.69 k，每秒 211.43 个。 我列出的最大并发连接数为 79。最后两个没有意义。 接收平均每小时 133 MiB，发送平均每分钟 1,997 MiB。
I'm roughly 10% into my website project and I'm trying to figure out what sort of load I'm putting on the database. When a user is logged in, I have many functions that trigger every minute, and every time someone visits the page, a number of elements, such as area code lists, states and countries are pulled to build a registration page. I'm sure I can move some of that to PHP so the database isn't involved.
In 6 days, 14 hours, 57 minutes and 58 seconds, I show 120,998,563 queries, averaging 12.69 k per minute and 211.43 per second. I'm listing maximum concurrent connections of 79. These last two don't make sense. Received averages 133 MiB per hour and Sent averages 1,997 MiB per minute.
发布评论
评论（6）
除了查询数量之外，重要的可能是它们做什么以及如何做：如果您的表中有数百万行，并且没有使用正确的索引，那么您的服务器将会崩溃......如果您的查询经过超优化，有了正确的索引，和/或您没有太多数据，您的服务器就会运行。
您可能需要对以下查询使用 EXPLAIN最常用的，看看至少那些是优化/使用索引;-)
然后，你可能会想添加这样一种缓存机制，比如 APC 或 memcached ; 至少如果你能...
例如，州和国家列表可能永远不会改变：它可以被缓存，不会访问数据库数千次，而只是每天一次或每小时一次。
More than the number of queries, what probably matters is what they do, and how : if you have millions of rows in your tables, and are not using the right indexes, you server will fall... If your queries are ultra-optimized, with the right indexes, and/or you don't have much data, you server will live.
You might want to use EXPLAIN on the queries that are the most used, to see if at least those are optimized / using indexes ;-)
Then, you will probably want to add so kind of caching mecanism, like APC or memcached ; at least if you can...
For instance, the lists states and countries probably never change : it could be cached, to not hit the database thousands of times, but just, say, once a day or once an hour.
更多提示：
A - 确保您不运行 nXn 运行时查询（或者至少尝试尽可能避免它）。 我的意思是不要去：
-query
-while(查询)
- 查询2
- while (查询2)
- 查询2时结束
- 结束 while 查询
，绝对不要进入第三级 (n^3)...
B - 关于速度的另一件事：当你不需要时 - 不要去选择 * from . 如果您只需要名字和姓氏，请仅选择这些...数据会更快返回。 你将能够更快地完成它。
some more tips:
A - make sure you don't run nXn runtime queries (or at least try to avoid it as much as you can). What i mean by that is don't go:
-query
-while(query)
- query2
- while (query2)
- end while query2
-end while query
and definitely don't go into third level (n^3)...
B - another thing about the speed: when you don't have to - don't go and select * from . if you need only first name and last name, select only these... the data will come back faster. and you'll be able to go through it faster.
您可能想要启用并开始监视慢查询日志（ http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html）。 查询数量不一定等于高负载 - 只要它们命中索引，甚至更好的查询缓存即可。 不过，一些设计不良的查询可能会杀死您的服务器 - 慢速查询日志会指出这些查询，以便您可以花时间优化它们。
You might want to enable and start monitoring the slow query log ( http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html ). The number of queries doesn't necessarily equate to high load - as long as they are hitting indexes, or even better the query cache. A few poorly designed queries can kill your server though - and the slow query log will point those out so you can spend your time optimizing them.
正如其他人所说，查询的数量并不重要，更重要的是查询的类型以及数据库的索引程度。 另外，正如其他人提到的，看看像 APC 或 memcache 这样的缓存机制，但我也会推荐 php 类缓存系统。 我知道 zend 框架有一个，我个人使用 Cache_Lite 来自 PEAR 库。 您可以缓存数据库查询，这些查询不需要绝对是 php 级别的最新更新信息。 因此，如果您的页面运行，假设有 10 个查询，但实际上只有 2 或 3 个查询需要是最新信息，您可以将其他查询缓存 5 或 10 分钟。 即使是一分钟的缓存也会为您在高流量站点上节省大量事务。
As others have said, the number of queries doesn't really matter, it is more the type of query and how well your database is indexed. Also, as someone else mentioned take a look at a caching mechanism like APC or memcache, but I would also reccommend a php class caching system. I know the zend framework has one and I personally use Cache_Lite from the PEAR library. You can cache db queries that don't need to be absolutely the latest updated information at the php level of things. So if your page runs, say 10 queries but really only 2 or 3 of them need to be fresh information you can cache the other queries for 5 or ten minutes. Even a one minute cache will save you lots of transaction on a high volume site.
另一个因素是您的托管环境。 在许多共享主机上，允许的并发数据库连接数存在限制，并且该限制可能低得惊人。 从统计数据来看，假设访问者在一天中均匀分布，通常情况下是没问题的，但如果您有足够多的受众，他们希望内容在某个时间发布，那么如果他们都在请求，您最终可能会耗尽可用连接在很短的时间内编写脚本（并打开数据库连接）。
Another factor is your hosting environment. On a lot of shared hosts, there's a limit to the number of permitted simultaneous database connection, and this limit can be surprisingly low. Statistically it's usually fine, assuming that visitors are spread out evenly through the day, but if you have a large enough audience who expect content to come out at a certain time, you could end up maxing out your available connections if they're all requesting scripts (and opening database connections) in a narrow time frame.
最好查看的是每页的查询数量，然后还要调查它们是什么类型的查询。 例如，简单的 SELECT 语句非常快，但如果将 3 个表连接在一起，则非常慢。 它还取决于数据库表中的索引和限制。
本质上，我们确实没有足够的信息可以告诉您。 即使你提供了什么，如果不知道一次有多少用户，它也是毫无用处的。
The best thing to look at is the number of queries per page, then also investigate what types of queries they are. Simple SELECT statements for example are very fast, but if you are joining 3 tables together, are very slow. It also depends on the indexes and limits in your database tables.
Essentially, we really don't have enough information to tell you. Even with what you gave, without any idea of the number of users at a time it's pretty useless.