使用文件排序 - 即使使用索引也无法避免

Question

我有一个包含 40,000,000 行的表，我正在尝试优化我的查询，因为时间太长了。
首先，这是我的表：
创建表resume(yearMonth字符 (6) 默认空，type字符 (1) 默认空，agen_o字符 (5) 默认空，tar字符 (2) 默认空，cve_ent字符 (1) 默认空，cve_mun字符 (3) 默认空，cve_regint(1) 默认空，id_ope字符 (
1) 默认空，







ope_tip字符(2) 默认空，
ope_cvechar(3) 默认空，cob_uint(9) 默认空，
tot_impbigint(15) 默认空
，) 引擎=MyISAM 默认字符集=拉丁1;



这是我的查询：
选择 m.name_ope 作为 cve，子字符串(r.yearMonth，5,2) 作为句点，COUNT(DISTINCT(CONCAT(r.agen_ope，r.cve_ope))) 作为 NUM，SUM(当 r.type='A' 然后r.cob_u否则 0 结束)作为tot_u，从恢复 r，媒体 M
其中 CONCAT(r.id_ope，子字符串(r.ope_cve，3,1))=m.ope_cve 和
r.type IN ('C'，



'D'，'E') 和子字符串(r.yearMonth，1,4)='2012' 和 r.id_ope='X' 和

子字符串 (r.ope_cve，1,2) 在
(从 catNac 中选择cve_med其中 numero='0')分组 SUBSTRING(r.yearMonth，5,2)，子字符串(r.ope_cve，3,1) 按子字符串(
r.yearMonth，5,2)，子字符串(r.ope_cve，3,1)



排序因此，我添加了一个包含以下字段的索引： id_ope， 年月， agen_o， 因为我有其他人的查询在 WHERE 中有此字段，按此顺序
排列 现在我的解释输出：
1 主要 r 参考印度 2 常量 14774607 使用哪里;使用文件排序
所以我添加了另一个带有 yearMonth 的索引，ope_cve，但我仍然有"使用文件排序"。如何优化？
谢谢

Answer 1

在不修改表结构的情况下，如果你在 yearMonth 上有索引，你可以试试这个：

SELECT m.name_ope AS cve,
SUBSTRING(r.yearMonth,5,2) AS period,
COUNT(DISTINCT(CONCAT(r.agen_ope,r.cve_ope))) AS num,
SUM(CASE WHEN r.type='A' THEN r.cob_u ELSE 0 END) AS tot_u,
FROM resume r, media m
WHERE CONCAT(r.id_ope,SUBSTRING(r.ope_cve,3,1))=m.ope_cve AND
r.type IN ('C','D','E') AND
r.yearMonth LIKE '2012%' AND
r.id_ope='X' AND
SUBSTRING(r.ope_cve,1,2) IN (SELECT cve_med FROM catNac WHERE numero='0')
GROUP BY r.yearMonth,SUBSTRING(r.ope_cve,3,1)

更改：

• 使用r.yearMonth LIKE '2012%'应该允许将索引用于 where 子句的该部分。
• 由于您已经过滤掉了除 2012 年以外的每年，因此您可以单独按GROUP BY r.yearMonth分组。
• 不需要ORDER BY子句，因为MySQL对GROUP BY进行排序，除非你包含ORDER BY NULL