我碰到了数据库性能瓶颈，现在呢？ [英] I've hit the DB performance bottleneck, where now?
问题描述
我有一些查询需要太长时间（300毫秒），现在数据库已经增长到几百万条记录。幸运的是，查询不需要查看大多数这些数据，最新的100,000记录将是足够的，所以我的计划是保持一个单独的表与最近的100,000记录，并对此运行查询。如果任何人有任何建议，一个更好的方式这样做是伟大的。我真正的问题是什么是选项，如果查询确实需要运行历史数据，下一步是什么？我已经考虑过的事情：
I have some queries that are taking too long (300ms) now that the DB has grown to a few million records. Luckily for me the queries don't need to look at the majority of this data, that latest 100,000 records will be sufficient so my plan is to maintain a separate table with the most recent 100,000 records and run the queries against this. If anyone has any suggestions for a better way of doing this that would be great. My real question is what are the options if the queries did need to run against the historic data, what is the next step? Things I've thought of:
- 升级硬件
- 使用内存数据库
- 在您自己的数据结构中手动缓存对象
这些东西是否正确， ？一些DB提供者具有比其他DB提供者更多的功能来处理这些问题，例如。指定一个特定的表/索引完全在内存中？
Are these things correct and are there any other options? Do some DB providers have more functionality than others to deal with these problems, e.g. specifying a particular table/index to be entirely in memory?
对不起，我应该提到这一点，我使用mysql。
Sorry, I should've mentioned this, I'm using mysql.
我忘了在上面提到索引。索引是迄今为止我唯一的改进来源是相当诚实的。为了识别瓶颈我一直使用maatkit查询显示是否正在使用索引。
I forgot to mention indexing in the above. Indexing have been my only source of improvement thus far to be quite honest. In order to identify bottlenecks I've been using maatkit for the queries to show whether or not indexes are being utilised.
我明白我现在正在摆脱这个问题的目的，所以也许我应该再做一个。我的问题是 EXPLAIN 表示查询需要10毫秒，而不是300毫秒jprofiler报告。如果任何人有任何建议，我真的很感激。查询是：
I understand I'm now getting away from what the question was intended for so maybe I should make another one. My problem is that EXPLAIN is saying the query takes 10ms rather than 300ms which jprofiler is reporting. If anyone has any suggestions I'd really appreciate it. The query is:
select bv.*
from BerthVisit bv
inner join BerthVisitChainLinks on bv.berthVisitID = BerthVisitChainLinks.berthVisitID
inner join BerthVisitChain on BerthVisitChainLinks.berthVisitChainID = BerthVisitChain.berthVisitChainID
inner join BerthJourneyChains on BerthVisitChain.berthVisitChainID = BerthJourneyChains.berthVisitChainID
inner join BerthJourney on BerthJourneyChains.berthJourneyID = BerthJourney.berthJourneyID
inner join TDObjectBerthJourneyMap on BerthJourney.berthJourneyID = TDObjectBerthJourneyMap.berthJourneyID
inner join TDObject on TDObjectBerthJourneyMap.tdObjectID = TDObject.tdObjectID
where
BerthJourney.journeyType='A' and
bv.berthID=251860 and
TDObject.headcode='2L32' and
bv.depTime is null and
bv.arrTime > '2011-07-28 16:00:00'
和输出 EXPLAIN 是：
+----+-------------+-------------------------+-------------+---------------------------------------------+-------------------------+---------+------------------------------------------------+------+-------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------+-------------+---------------------------------------------+-------------------------+---------+------------------------------------------------+------+-------------------------------------------------------+
| 1 | SIMPLE | bv | index_merge | PRIMARY,idx_berthID,idx_arrTime,idx_depTime | idx_berthID,idx_depTime | 9,9 | NULL | 117 | Using intersect(idx_berthID,idx_depTime); Using where |
| 1 | SIMPLE | BerthVisitChainLinks | ref | idx_berthVisitChainID,idx_berthVisitID | idx_berthVisitID | 8 | Network.bv.berthVisitID | 1 | Using where |
| 1 | SIMPLE | BerthVisitChain | eq_ref | PRIMARY | PRIMARY | 8 | Network.BerthVisitChainLinks.berthVisitChainID | 1 | Using where; Using index |
| 1 | SIMPLE | BerthJourneyChains | ref | idx_berthJourneyID,idx_berthVisitChainID | idx_berthVisitChainID | 8 | Network.BerthVisitChain.berthVisitChainID | 1 | Using where |
| 1 | SIMPLE | BerthJourney | eq_ref | PRIMARY,idx_journeyType | PRIMARY | 8 | Network.BerthJourneyChains.berthJourneyID | 1 | Using where |
| 1 | SIMPLE | TDObjectBerthJourneyMap | ref | idx_tdObjectID,idx_berthJourneyID | idx_berthJourneyID | 8 | Network.BerthJourney.berthJourneyID | 1 | Using where |
| 1 | SIMPLE | TDObject | eq_ref | PRIMARY,idx_headcode | PRIMARY | 8 | Network.TDObjectBerthJourneyMap.tdObjectID | 1 | Using where |
+----+-------------+-------------------------+-------------+---------------------------------------------+-------------------------+---------+------------------------------------------------+------+---------------------------------------
7 rows in set (0.01 sec)
推荐答案
像这样不是一个好的迹象 - 我敢打赌，你仍然有很多性能挤出使用EXPLAIN，调整db变量和改进索引和查询。但你可能已经超过了尝试的东西工作得很好的点。这是一个学习如何解释分析和日志，以及使用您对具体改进索引和查询所学习的机会。
Considering a design change like this is not a good sign - I bet you still have plenty of performance to squeeze out using EXPLAIN, adjusting db variables and improving the indexes and queries. But you're probably past the point where "trying stuff" works very well. It's an opportunity to learn how to interpret the analyses and logs, and use what you learn for specific improvements to indexes and queries.
如果你的建议是一个好的，应该能够告诉我们为什么已经。请注意，这是一个流行的pessimization -
If your suggestion were a good one, you should be able to tell us why already. And note that this is a popular pessimization--
这篇关于我碰到了数据库性能瓶颈，现在呢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
