优化MySql查询

Question

我想知道是否有办法优化此查询：

SELECT
    jdc_organizations_activities.*,
    jdc_organizations.orgName, 
    CONCAT(jos_hpj_users.firstName, ' ', jos_hpj_users.lastName) AS nameContact  
FROM jdc_organizations_activities
LEFT JOIN jdc_organizations ON jdc_organizations_activities.organizationId =jdc_organizations.id
LEFT JOIN jos_hpj_users ON jdc_organizations_activities.contact = jos_hpj_users.userId
WHERE jdc_organizations_activities.status LIKE 'proposed'  
ORDER BY jdc_organizations_activities.creationDate DESC LIMIT 0 , 100 ;

现在当我看到查询时log：

 Query_time: 2  
 Lock_time: 0  
 Rows_sent: 100  
 Rows_examined: **1028330**

查询配置文件：

2) 我应该放置索引吗在桌子上考虑到这些表上将会有大量的插入和更新。

来自 Tizag 教程：

索引是你所不需要的额外东西 可以在您的 MySQL 表上启用 提高性能，但他们确实有 一些缺点。当你创建一个新的 MySQL 建立一个单独的索引块 需要更新的信息 每次有更改时 桌子。 这意味着如果您 不断更新、插入和 删除表中的条目 可能会产生负面影响 性能。

添加索引并删除 lower() 、 group by 和通配符后更新

时间： 0.855ms

Answer 1

添加索引（如果尚未添加）：

Table: jdc_organizations_activities

• creationDate 的简单索引
• status
• organizationId 的简单索引
• contact 上的简单索引

并通过删除对函数 LOWER() 的调用并使用 = 或 LIKE< 来重写查询/代码>。这取决于您为此表定义的排序规则，但如果它是不区分大小写的排序规则（例如 latin1），它仍然会显示相同的结果。详细信息可以在 MySQL 文档：case-sensitivity< /a>

SELECT a.*
     , o.orgName
     , CONCAT(u.firstName,' ',u.lastName) AS nameContact  

FROM jdc_organizations_activities AS a
  LEFT JOIN jdc_organizations AS o
    ON a.organizationId = o.id 
  LEFT JOIN jos_hpj_users AS u
    ON a.contact = u.userId

WHERE a.status LIKE 'proposed'     --- or (a.status = 'proposed')

ORDER BY a.creationDate DESC 
LIMIT 0 , 100 ;

如果您发布了执行计划（就像现在一样）以及这些更改之后的结果，那就太好了。

---

更新

(status,creationDate) 上的复合索引可能更适合此查询（如 Darhazer 建议），而不是简单的 (status)。但这更多是猜测工作。发布计划（运行EXPLAIN 查询后）将提供更多信息。

我还假设您已经在以下位置拥有（主键）索引：

• jdc_organizations.id
• jos_hpj_users.userId

Answer 2

发布 EXPLAIN 的结果
通常，您需要对 jdc_organizations_activities.organizationId、jdc_organizations_activities.contact 建立索引，对 jdc_organizations_activities.status 和 jdc_organizations_activities.creationDate 建立复合索引>

为什么您使用 LIKE 查询进行持续查找（您没有通配符，或者您可能已经编辑了查询）
状态索引可用于 LIKE 'propose%'，但不能用于 LIKE '%propose%' - 在后一种情况下，最好只保留creationDate 上的索引

Answer 3

这些表上有哪些索引？具体来说，您是否已将 jdc_organizations_activities.creationDate 编入索引？

另外，为什么需要按 jdc_organizations_activities.id 分组？这不是每行都是唯一的吗？或者一个组织可以有多个联系人吗？

Answer 4

速度慢是因为 mysql 必须将 lower() 应用于每一行。解决方案是创建一个新列来存储 lower 的结果，然后在该列上放置索引。我们还可以使用触发器来使解决方案更加豪华。好的，我们开始：

a) 添加一个新列来保存较低版本的状态（使该 varchar 与状态一样宽）：

ALTER TABLE jdc_organizations_activities ADD COLUMN status_lower varchar(20);

b) 填充新列：

UPDATE jdc_organizations_activities SET status_lower = lower(status);

c) 在新列上创建索引

CREATE INDEX jdc_organizations_activities_status_lower_index
    ON jdc_organizations_activities(status_lower);

d) 定义触发器保持新列值正确：

DELIMITER ~;
CREATE TRIGGER jdc_organizations_activities_status_insert_trig
BEFORE INSERT ON jdc_organizations_activities
FOR EACH ROW
BEGIN
    NEW.status_lower = lower(NEW.status);
END;

CREATE TRIGGER jdc_organizations_activities_status_update_trig
BEFORE UPDATE ON jdc_organizations_activities
FOR EACH ROW
BEGIN
    NEW.status_lower = lower(NEW.status);
END;~
DELIMITER ;

您的查询现在应该可以运行。