MySQL数据库优化最佳实践

Question

什么是优化MySQL安装以实现最佳性能的最佳实践方法，尤其是在处理较大表（> 5万条记录，每个表总共约100MB）时？我们目前正在研究重写DelphiFeeds.com（Delphi编程社区的新闻网站），并注意到简单的Update语句可能需要多达50毫秒。这似乎很多。是否有推荐的配置设置应该启用/设置，通常在标准MySQL安装上禁用（例如利用更多RAM缓存查询和数据等）？

此外，选择存储引擎会产生哪些性能影响？我们计划使用InnoDB，但如果基于性能原因建议使用MyISAM，则可能使用MyISAM。

- Dennis G.

---

选择INNODB引擎的一个重要原因是它支持事务处理，而MyIsam则不支持。如果您关心数据完整性（应该的 :))，那么没有其他可靠的方法。如果您不使用事务且出现像断电这样的意外情况，就无法可靠地回滚SQL序列。 - Stann

Answer 1

“最佳实践”是：

1. 测量性能，尽可能地隔离相关子系统。
2. 确定瓶颈的根本原因。是I/O受限？CPU受限？内存受限？等待锁定？
3. 进行更改以减轻您发现的根本原因。
4. 再次测量以证明您已经解决了瓶颈并且提高了多少效率。
5. 回到步骤2并重复执行，直到系统运行足够快为止。

订阅http://www.mysqlperformanceblog.com上的RSS订阅，并阅读历史文章。那是一个非常有用的性能相关智慧资源。例如，您问InnoDB vs. MyISAM的问题。他们的结论是：InnoDB的平均性能比MyISAM高约30％。尽管还有一些使用场景，MyISAM超过InnoDB。

• InnoDB vs. MyISAM vs. Falcon基准测试-第1部分

该博客的作者也是@Andrew Barnett提到的《高性能MySQL》一书的合作者。

---

关于@ʞɔıu的评论：如何判断您是I / O受限还是CPU受限还是内存受限取决于平台。操作系统可能提供诸如ps、iostat、vmstat或top等工具。如果您的操作系统没有提供，则可能需要获取第三方工具。

基本上，任何资源达到100％利用率/饱和就很可能成为瓶颈。如果您的CPU负载很低，但硬件的I/O负载已达最大值，则表示您受到I / O限制。

不过这只是一个数据点，解决方法可能还取决于其他因素。例如，复杂的SQL查询可能会执行文件排序，这会使I/O繁忙。您应该投入更多/更快的硬件，还是重新设计查询以避免文件排序？

在StackOverflow帖子中总结太多因素了，许多书籍都支持这一观点。使数据库高效运行并充分利用资源是需要专业技能和不断学习的全职工作。

---

Jeff Atwood刚刚写了一篇关于如何找到系统瓶颈的好博客文章:

• 《计算机性能壳游戏》

- Bill Karwin

---

你如何判断自己是IO限制、CPU限制还是内存限制？ - ʞɔıu

Answer 2

建议购买O'Reilly出版的《高性能MySQL》一书。该书有近700页内容，因此我认为你在SO上很难找到简洁的答案。

- Andrew Barnett

Answer 3

总体而言，虽然有些细节需要注意，但是我们可以提供一个中高级别的建议。

• 您需要评估读写比率。对于读写比率低于5:1的表格，使用InnoDB可能会更好，因为插入操作不会阻塞选择操作。但是，如果您没有使用事务，则应将innodb_flush_log_at_trx_commit更改为1，以使性能超过MyISAM。
• 查看内存参数。MySQL的默认值非常保守，即使在普通硬件上，一些内存限制也可以提高10倍以上。这将有利于您的SELECT操作，而不是INSERT操作。
• MySQL可以记录诸如未使用索引的查询以及查询时间过长的查询（用户可定义）等内容。
• 查询缓存可能很有用，但您需要对其进行测试（即查看它的使用情况）。Cacti和Munin都可以做到这一点。
• 应用程序设计也很重要：
  • 轻松缓存经常获取但较小的数据集将产生很大的差异（即缓存寿命为几秒钟）。
  • 不要重新获取已经存在的数据。
  • 多步存储可以帮助处理高频插入并且频繁读取的表格。基本思想是您可以为临时插入创建一个表格（INSERT DELAYED也可能很有用），但是需要批量处理从该表格到所有读取操作发生的位置的更新。这有一些变化。
• 不要忘记透视和上下文也很重要：如果“长”更新仅每天发生一次，那么您认为UPDATE需要很长时间的操作可能实际上相当微不足道。

- staticsan

Answer 4

之前已经讨论了大量关于编程方面的最佳实践，因此没有必要重复它们。实际上，如果您需要具体的建议，我建议运行 MySQL调整器。这是一个perl脚本，您可以下载并在您的数据库服务器上运行，它将为您提供有关数据库性能（例如缓存命中）的一堆统计数据，以及一些具体的建议，以改善性能所需进行的问题或配置参数。

虽然所有这些统计数据都可以在MySQL本身中找到，但我发现这个工具以更易于理解的方式提供了它们。虽然需要注意的是，建议可能因人而异，但我发现它们通常相当准确。只需确保您已经通过真实流量对数据库进行了充分的测试。

- jkupferman