Q&A：MySQL更致命的错误_MySQL

周三，我在 Percona MySQL 网络研讨会上做了关于“如何避免更常见（但致命）的 MySQL 开发错误”的演讲。  如果您错过了，您仍然可以注册以查看录音和我的幻灯片。

感谢所有参加的人，特别是那些提出了很好问题的人。  我在会议期间回答了尽可能多的问题，但以下是所有问题及其完整答案：

问：磁盘带宽也不是无限的

确实，您是对的！

我们在网络研讨会中讨论了在 SELECT * 等查询中使用列通配符对网络带宽的影响，但使用 SELECT * 也可能会影响磁盘操作。 Varchar、Blob 或 Text 列可以存储在数据库中的额外页面上，如果您在查询中不必要地包含这些列，则可能会导致存储引擎进行大量不必要的查找和页面读取。

有关 InnoDB 中字符串存储的更多详细信息，请参阅 Peter Zaitsev 关于 Innodb 中的 Blob 存储的博客。

问：单个查询中可以连接多少个表？最佳连接数是多少？ ​​

MySQL 在给定查询中限制 63 个表引用。这限制了您可以执行的 JOIN 操作的数量，也限制了 UNION 的数量。实际上，如果您的 JOIN 或 UNION 不引用任何表，即创建一行表达式的派生表，您可以超过此限制。

如果您确实联接了很多表（甚至是自己） -多次加入同一个表），在达到 63 个表引用之前，您很可能会达到实际的扩展限制。您的情况的实际限制取决于许多因素，包括表的长度、数据类型、查询中连接表达式的类型以及物理服务器的功能。这不是我可以为您引用的固定限制。

如果您认为在单个查询中需要数十个表引用，您可能应该退一步并重新考虑您的数据库设计或查询设计。

当人们尝试使用键/值表（也称为实体属性值）并且他们试图从将行转换为列，就好像表以传统方式存储，每个属性一列。出于多种原因，这是一种错误的设计，多路联接的可扩展性只是其中的一个问题。

问：单个表中可以创建多少个索引？有什么限制吗？最佳索引数量是多少？

所有 MySQL 存储引擎都支持每个表至少 16 个索引。

至于最佳索引数量，我不关心索引数量（只要保持不变即可）低于最大值 16)。我尝试确保我的查询有正确的索引。如果您在给定表上设置任意上限，例如 8 或 10 个索引，那么您可能正在运行缺少所需索引的查询，并且运行该查询的不必要的额外成本可能大于维护一个额外索引的成本

也就是说，在某些情况下，查询类型存在很大差异，因此无法拥有最佳索引来涵盖所有可能的情况。鉴于您可以拥有多列索引，以及具有不同顺序的列的多列索引，因此具有 n 列的表上可能有 n 阶乘 个可能的索引。

问：有一个表有 3 列：id(int)、user_id(int)、day(date)。每天“存在”相同的 user_id 的可能性很高。我通过“where user_id = some_id”（非常高的吞吐量）读取数据，并通过 cron 使用“where sent_date = '2014-01-01'”每天删除一次所有条目。每天删除大约 600 万行是相当痛苦的。按“天”列分区是否可以帮助我更快地删除这些数据？如果是的话——快多少？它会使 SELECT 减慢多少？ – 并非所有条目都被删除，而是仅删除某些特定旧日的条目，例如' WHERE day = '1 week ago'

按日期进行范围分区将使您有机会更改表…删除分区，因此您可以非常快速地删除给定日期的所有数据，比删除数百万个数据要快得多行。 DROP PARTITION 的性能类似于 DROP TABLE，因为每个分区在物理上都像单独的表一样存储。

搜索“where user_id = ?”将无法利用分区修剪，但仍然可以使用 user_id 上的索引。如果您删除旧分区，搜索较小表的好处可能是一个很好的权衡。

问：将 20% 的选择性作为优化器首选表扫描而不是索引查找的阈值 – 这是一个可调参数吗？

不，它是不可调整的，它是查询优化器的固定行为。如果您搜索一个值并且优化器估计> > 20% 的行包含您搜索的值，它将绕过索引，只进行表扫描。

就像一本书的索引不包含非常常见的单词一样，因为单词出现的页面列表太长，从书的背面来回翻到每个列出的页面实际上比阅读这本书要费力。

还要记住我的数字20% 是近似值。您的结果可能会有所不同。这并不是源代码中的神奇阈值，这只是我观察到的一种趋势。

问：关于生成综合测试数据，这听起来像是一个非常容易编写的 Perl 脚本。

是的，对于一张给定的表来说，做到这一点可能很容易。但每个表都是不同的，您可能在数十个应用程序中有数百个表来生成测试数据。您可能还想改变一个测试到另一个测试的数据值分布。

为一种特定情况编写测试数据生成器很容易，因此您可以合理地将其作为一项一次性任务来完成。编写一个可用于多种情况的通用测试数据生成器需要更多工作。

问：希望在演示文稿中引用一组 URL，而不必返回并挖掘它们。演示文稿。

开源消息队列：

• Redis (Resque)
• OpenMQ
• ActiveMQ
• RabbitMQ
• Gearman
• Beanstalk
• Kafka
• Kestrel

MySQL 性能博客文章：

• 探索消息代理
• Kiss Kiss Kiss （保持简单）
• 为什么你不想分片

开源测试数据生成器：

• Databene Benerator

Web 应用程序的负载测试工具：

• JMeter
• Siege

负载测试重放查询日志的工具：

• Percona Playback
• pt-log-player

实施业务规则的进一步阅读：

• 真实世界规则引擎
• 企业规则引擎（警告）
• Drools，业务逻辑集成平台

问：如何要最好地使用 mysql 查询缓存？ ​​

如果每次写入时都多次读取，则最好使用任何缓存。因此，我们想要估计查询缓存读取与写入的平均比率，以估计它为我们提供了多少杠杆。

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';<br>

检查 QCache_hits 的值（这是读取查询结果时的情况）来自查询缓存）通过 QCache_inserts （这是所需查询结果不在缓存中，必须运行然后将结果存储在缓存中的情况）。我希望看到 1000% 或更高的比率（即 10:1 的插入命中率）。

如果比率较差，例如小于 1:1 或小于 100%，请考虑禁用查询缓存，因为维护它的成本可能高于它为您带来的性能优势。

请记住，这只是一个指导原则，因为我描述的计算只是平均值。查询缓存提供的查询可能非常昂贵，因此使用缓存结果会带来很大的好处，即使它只占少量命中。唯一确定的方法是在您的负载下对应用程序进行负载测试，并比较启用或禁用查询缓存以及不同大小的总体性能结果。

问：如何检测何时太多的索引开始影响性能？ ​​

有些人不愿意创建索引，因为他们被警告说，当您插入、更新或删除行时，索引需要同步更新。有些人还概括为索引损害写入但有益于读取。这些机器人都不是真的。

您的 DML 操作并没有真正实时更新索引。 InnoDB 包含一个称为更改缓冲的功能，它可以推迟索引更新。随着时间的推移，更改缓冲区会逐渐合并到索引中。这样，InnoDB 就可以处理流量的大幅增长，而不会影响吞吐量。您可以监控更改缓冲区中还有多少内容需要合并：

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_ibuf_size';<br>

索引会损害写入的说法也是不准确的。 UPDATE 和 DELETE 语句通常有一个 WHERE 子句，用于将更改应用于特定行。这些条件使用索引来减少检查的行，就像在 SELECT 语句中一样。但在 UPDATE 和 DELETE 语句中，使用索引更为重要，因为否则该语句必须锁定大量行以确保锁定要更改的行。

所以我通常说，不要'不要仅根据您拥有的索引数量来避免索引，只需确保您运行的查询正在使用您的索引，并删除未使用的索引即可。以下是过去的几篇博客文章，展示了如何执行此操作：

• 查找未使用的索引
• 快速查找未使用的索引（并估计其大小）

再次感谢您参加我的网络研讨会！  以下是更多提示：

• 查看即将在北美和欧洲举办的 Percona 培训课程。
• 通过我们的 Percona Live 加入 Percona 和 MySQL 社区。
• 未来观看更多来自 Percona 的网络研讨会！