mysql - 使用 Filesort - 无法避免，即使使用索引

Question

我有一个包含 40,000,000 行的表，我正在尝试优化我的查询，因为花费的时间太长。
首先，这是我的 table :
创建表 恢复 (
yearMonth char(6) DEFAULT NULL,
类型 char(1) DEFAULT NULL,
agen_o char(5) DEFAULT NULL,
tar char(2) 默认 NULL,
cve_ent char(1) 默认为空，
cve_mun char(3) DEFAULT NULL,
cve_reg int(1) 默认为空，
id_ope char(1) DEFAULT NULL,
ope_tip char(2) DEFAULT NULL,
ope_cve char(3) DEFAULT NULL,
cob_u int(9) 默认为空，
tot_imp bigint(15) 默认为空，
) ENGINE=MyISAM 默认字符集=latin1;


这是我的查询:
选择 m.name_ope 作为 cve，
SUBSTRING(r.yearMonth,5,2) 作为句点，
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 简历 r, 媒体 m
WHERE CONCAT(r.id_ope,SUBSTRING(r.ope_cve,3,1))=m.ope_cve AND
r.type IN ('C','D','E') AND
SUBSTRING(r.yearMonth,1,4)='2012' AND
r.id_ope='X' 和
SUBSTRING(r.ope_cve,1,2) IN (SELECT cve_med FROM catNac WHERE numero='0')
GROUP BY SUBSTRING(r.yearMonth,5,2),SUBSTRING(r.ope_cve,3,1)
按子字符串排序(r.yearMonth,5,2),子字符串(r.ope_cve,3,1)


所以，我添加了一个包含这些字段的索引:id_ope、yearMonth、agen_o，因为我有其他人的查询在 WHERE 中有这个字段，顺序
现在我的解释输出:
1 PRIMARY r ref indice indice 2 const 14774607 Using where;使用文件排序
所以我用 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