mysql - Active Record Join 语句优化

Question

我通过单击问题侧边栏上的摘要找到了此查询(由 Redmine 3.4 和 Rails 4.2.5.1 - Active Record 生成)，消耗 100% CPU 超过 3 小时...

在 mysql 上运行 SHOW FULL PROCESSLIST 后，我得到了这个:

SELECT COUNT(*) FROM `custom_values` INNER JOIN issues 
ON issues.id = custom_values.customized_id INNER JOIN issue_statuses 
ON issues.status_id=issue_statuses.id WHERE `custom_values`.`custom_field_id` 
= 16 AND ((issues.id=29951 OR issues.id=29950 OR ........................

现在有 29940 个 OR!!! 剩下的 SQL 查询

.............OR issues.id=6)) AND `custom_values`.`value` = 'Some Value' 
AND `issue_statuses`.`is_closed` = 1

我想知道是否有一种方法可以优化上述查询，以便我可以建议改进引起此问题的Redmine或Active Record。

Answer 1

使用IN怎么样？

SELECT COUNT(*) FROM `custom_values` INNER JOIN issues 
ON issues.id = custom_values.customized_id INNER JOIN issue_statuses 
ON issues.status_id=issue_statuses.id WHERE `custom_values`.`custom_field_id` 
= 16 AND ((issues.id in (29951, 29950, 29440,.. ........................

或

SELECT COUNT(*) FROM `custom_values` INNER JOIN issues 
ON issues.id = custom_values.customized_id INNER JOIN issue_statuses 
ON issues.status_id=issue_statuses.id WHERE `custom_values`.`custom_field_id` 
= 16 AND ((issues.id between 29951 and 29940...

或

通过使用 cte。

with cte as (
   select 29940 as ct, 0 as cnt
   union all
   select cte.ct + 1, 1 as cnt 
    from `custom_values`
    inner join issues ON issues.id = custom_values.customized_id 
    inner join issue_statuses ON issues.status_id=issue_statuses.id 
     cte on cte.ct = t.order_sp
    where cte.ct <= 29951  and `custom_values`.`custom_field_id` and issues.id = cte.ct
)select sum(cnt) from cte;