php - MySQL ("Sending Data"查询非常慢)

Question

我目前正在使用 CodeIgniter 框架开发 PHP/MySQL 应用程序。

我有一个相当长的查询，它导致了一些问题。当将日期范围更改为更长的时间(例如 30 天)而不是默认的 7 天时，就会出现问题。查询时间大幅增加:1/2 秒到 90 秒，但我只能假设这是因为数据量增加所致。

在粘贴查询之前，以下是对表格的快速解释:

• flagged_cases 独特案例列表(主表)- 352 行
• data_sources:数据源列表，每个案例使用外键引用此表 - 20 行
• 匹配:一个案例的文本行匹配(一对多关系，即一个案例，多个匹配)- 22000 行
• flagged_cases_keywords_hits:案例 ID 到关键字(和命中数)的映射 - 2500 行
• 关键字:关键字列表 - 121 行
• reviewed_state: 3 个州的 id/description，只检查此查询的 reviewed_state = 1 - 3 行

以下是查询，我意识到它相当大，但我认为索引一定存在潜在问题，不幸的是我只是不具备全面解决问题的知识，因此我们感谢您的帮助。

SELECT    flagged_cases.id, 
          data_source_id, 
          title, 
          fetch_date, 
          publish_date, 
          case_id, 
          case_title, 
          case_link, 
          relevance_score, 
          ( 
                   SELECT   group_concat(match_string_highlighted ORDER BY matches.id SEPARATOR "")
                   FROM     matches 
                   WHERE    flagged_case_id=flagged_cases.id) AS all_matches, 
          reviewed_state_id, 
          ( 
                   SELECT   group_concat(concat(k.keyword, " ", "x", cast(kh.hits AS CHAR), "") SEPARATOR "")
                   FROM     flagged_cases_keywords_hits kh 
                   JOIN     keywords k 
                   ON       kh.keyword_id = k.id 
                   WHERE    kh.flagged_case_id = flagged_cases.id 
                   ORDER BY k.weighting DESC) AS hitcount 
FROM      flagged_cases 
JOIN      data_sources 
ON        flagged_cases.data_source_id = data_sources.id 
JOIN      reviewed_state 
ON        flagged_cases.reviewed_state_id = reviewed_state.id 
LEFT JOIN matches 
ON        flagged_cases.id = matches.flagged_case_id 
WHERE     reviewed_state_id = 1 
AND       data_source_id IN('1', 
                            '3', 
                            '4', 
                            '5', 
                            '6', 
                            '7', 
                            '8', 
                            '9', 
                            '10', 
                            '11', 
                            '12', 
                            '13', 
                            '14', 
                            '15', 
                            '16', 
                            '17', 
                            '18', 
                            '19', 
                            '20') 
AND       fetch_date >= '2015-05-10 00:00:00' 
AND       fetch_date <= '2015-05-17 23:59:59' 
GROUP BY  flagged_cases.id 
ORDER BY  title DESC 
LIMIT     10;

作为 SHOW FULL PROCESSLIST 的结果，我可以看到查询保持在“发送数据”状态，从一些研究中我可以看到基本上是 MySQL 获取和选择数据，所以我只能假设必须有一个丢失的索引或导致速度减慢的原因。

我也得到了查询的EXPLAIN，如下:

+----+--------------------+----------------+--------+----------------------------------+-----------------+---------+----------------------------+------+----------------------------------------------+
| id | select_type        | table          | type   | possible_keys                    | key             | key_len | ref                        | rows | Extra                                        |
+----+--------------------+----------------+--------+----------------------------------+-----------------+---------+----------------------------+------+----------------------------------------------+
|  1 | PRIMARY            | reviewed_state | const  | PRIMARY                          | PRIMARY         | 4       | const                      |    1 | Using index; Using temporary; Using filesort |
|  1 | PRIMARY            | data_sources   | range  | PRIMARY                          | PRIMARY         | 4       | NULL                       |   19 | Using where                                  |
|  1 | PRIMARY            | flagged_cases  | ref    | data_source_id,reviewed_state_id | data_source_id  | 4       | proactive.data_sources.id  |   14 | Using where                                  |
|  1 | PRIMARY            | matches        | ref    | flagged_case_id                  | flagged_case_id | 4       | proactive.flagged_cases.id |   32 | Using index                                  |
|  3 | DEPENDENT SUBQUERY | kh             | ref    | flagged_case_id,keyword_id       | flagged_case_id | 5       | func                       |    3 | Using where; Using temporary                 |
|  3 | DEPENDENT SUBQUERY | k              | eq_ref | PRIMARY                          | PRIMARY         | 4       | proactive.kh.keyword_id    |    1 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | matches        | ref    | flagged_case_id                  | flagged_case_id | 4       | func                       |   32 |                                              |
+----+--------------------+----------------+--------+----------------------------------+-----------------+---------+----------------------------+------+----------------------------------------------+

非常感谢任何帮助/建议/提示! :)

Answer 1

你能试试看它是否有任何好处吗？

您的选择列表中的子查询被替换为内联 View ，这些 View 按连接到您的其他表的值分组。

select      flagged_cases.id, 
            data_source_id, 
            title, 
            fetch_date, 
            publish_date, 
            case_id, 
            case_title, 
            case_link, 
            relevance_score, 
            v1.all_matches, 
            reviewed_state_id, 
            v2.hitcount
from        flagged_cases 
       join data_sources 
         on flagged_cases.data_source_id = data_sources.id 
       join reviewed_state 
         on flagged_cases.reviewed_state_id = reviewed_state.id
       join (
                select      group_concat(match_string_highlighted order by matches.id separator "") as all_matches
                from        matches
                group by    flagged_case_id
            ) v1
         on v1.flagged_case_id = flagged_cases.id
       join (
                select      group_concat(concat(k.keyword, " ", "x", cast(kh.hits as char), "") order by k.weighting desc separator "")
                from        flagged_cases_keywords_hits kh 
                       join keywords k 
                         on kh.keyword_id = k.id 
                group by    kh.flagged_case_id
            ) v2
         on v2.flagged_case_id = flagged_cases.id 
  left join matches 
         on flagged_cases.id = matches.flagged_case_id 
where       reviewed_state_id = 1 
        and data_source_id in('1','3','4','5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20') 
        and fetch_date >= '2015-05-10 00:00:00' 
        and fetch_date <= '2015-05-17 23:59:59' 
group by    flagged_cases.id 
order by    title desc
limit       10;