在CakePHP中确定要在MySQL中索引哪些列

Question

我有一个Web应用程序，其中每个页面都会发出相当多的查询。随着将更多数据添加到数据库中，我们发现页面加载时间越来越长。

在检查PhpMyAdmin -> 状态 -> 连接时，我们注意到了这一点（红色数字）：

Select_full_join 348.6 k 没有使用索引的连接数量。如果此值不为0，则应仔细检查表的索引。

1. 我如何确定哪些连接导致问题？所有连接都同样受到谴责吗？ 2. 我如何确定应该对哪些列进行索引，以使性能达到最佳？

我们正在使用CakePHP + MySQL，并且所有查询都是自动生成的。

- Samudra

Answer 1

我一直遵循的经验法则是，如果我使用了join操作，那么我要连接的字段必须被索引。

例如，如果您有一个像以下这样的查询：

SELECT t1.name, t2.salary
    FROM employee AS t1 
    INNER JOIN info AS t2 ON t1.name = t2.name;

t1.name和t2.name都应该建立索引。

以下也是一些不错的阅读材料：

优化MySQL：连接顺序的重要性

如何通过索引优化MySQL JOIN查询

总的来说，这个网站上有一些好的信息。

MySQL优化团队

编辑：这个总是有帮助的。

如果您可以访问服务器设置，请查看：

MySQL慢查询日志

一旦您有缓慢查询的日志，您就可以对它们使用explain来查看需要建立索引的内容。

- SuperMykEl

---

如果我有一个HABTM / Join表情况，或者一个简单的CakePHP模型关联情况（比如Post属于User，Post有user_id），那么我应该索引哪些字段呢？这不是一种“隐式”的连接吗？抱歉，我对此完全不了解。 - Angad

就性能而言，显式/隐式连接之间几乎没有区别。关于在HABTM / Join中索引哪些字段，我会确保任何我在连接中使用的字段（在您先前的评论中是Posts表中的user_id字段或任何您实际上用作外键的内容）都被索引。 - SuperMykEl

+1，谢谢。正是我想要的。我的应用程序变得非常非常慢 :) - Angad

Answer 2

如果您不知道哪些查询效率低下，您有几个选择。

您可以尝试以下方法：

在网站处于活动状态时，从phpmyadmin发出命令SHOW FULL PROCESSLIST。它将向您显示一堆慢查询，FULL processlist应该会给您整个查询。然后，您可以使用EXPLAIN命令来弄清楚它在做什么。

您还应该尝试以下方法：

考虑应用程序代表用户执行的工作。思考一下哪些查询需要浏览大量数据才能为用户提供价值。思考一下随着应用程序的使用越来越多，哪些表正在增长。

然后，找到传递该值并访问增长表的查询。同样，使用EXPLAIN命令查看MySQL如何处理它们，并根据需要添加索引。

我认为很明显应该添加哪些索引。添加明显的索引，然后让系统稳定一段时间，再重新测量。

请注意，这是将新应用程序投入生产的正常部分。

- O. Jones

---

SHOW FULL PROCESSLIST 是一种令人难以置信的工具！非常感谢 :) +! - Angad