EXPLAIN看执行计划;
EXPLAIN
SELECT
fydm,
problem_content_type AS problemContentType,
warning_time AS warningTime,
count( id ) AS problemCount,
warning_type AS warningType
FROM
ssfw_problem_warning
WHERE
warning_time BETWEEN '2020-01-01 00:00:00'
AND '2021-12-14 23:59:59'
GROUP BY
warning_time,
fydm,
problem_content_type,
warning_type
ORDER BY
warning_time DESC,
fydm ASC
原查询的索引为:
CREATE INDEX index_name ON ssfw_problem_warning (fydm, problem_content_type, warning_time, warning_type)
由于组合索引的开头时“fydm”,而where条件是“warning_time ”;所以where用不上组合索引,只有group by用上的组合索引,造成rows的数量较多,type为index,filtered的比例只有11.11;key_len也有789;
所以调整组合索引的位置顺序:
CREATE INDEX index_name ON ssfw_problem_warning (warning_time, fydm, problem_content_type, warning_type)
索引修改后,type为range,filtered的比例达到100.00,key_len为6;性能进一步提升。