[TOC] # 原始SQL ``` SELECT `a`.`province_id`,count(province_id) as vote_num,`b`.`name`,`b`.`people_num`,`b`.`img`,count(a.uid) as known FROM `cmf_region_relevance` `a` LEFT JOIN `cmf_region` `b` ON `b`.`id`=`a`.`province_id` WHERE `b`.`level` = 1 GROUP BY `a`.`province_id` ORDER BY `vote_num` DESC ``` 返回行數：31 更新行數：0 執行耗時：138ms >注：高版本的這個group by 可能不解析，sql server肯定會報錯，mysql8.0可能會報錯！ # 建議優化后： ``` SELECT `a`.`province_id`, COUNT(province_id) AS vote_num, `b`.`name`, `b`.`people_num`, `b`.`img` , COUNT(a.uid) AS known FROM `cmf_region_relevance` `a` LEFT JOIN `cmf_region` `b` ON `b`.`id` = `a`.`province_id` WHERE `b`.`level` = 1 GROUP BY `a`.`province_id`,`b`.`name`, `b`.`people_num`, `b`.`img` ORDER BY `vote_num` DESC ``` 用時： 返回行數：31 更新行數：0 執行耗時：2179ms >很明顯，這里不問題，耗時更長！ # 重寫建議： ``` SELECT `cmf_region_relevance`.`province_id`, COUNT(*) AS `vote_num`, CAST(`t`.`name` AS CHAR(32)) AS `name`, CAST(`t`.`people_num` AS UNSIGNED) AS `people_num`, CAST(`t`.`img` AS CHAR(255)) AS `img`, COUNT(*) AS `known` FROM (SELECT * FROM `public_health`.`cmf_region` WHERE CAST(`level` AS UNSIGNED) = 1) AS `t` INNER JOIN `public_health`.`cmf_region_relevance` ON `t`.`id` = `cmf_region_relevance`.`province_id` GROUP BY `cmf_region_relevance`.`province_id`, CAST(`t`.`name` AS CHAR(32)), CAST(`t`.`people_num` AS UNSIGNED), CAST(`t`.`img` AS CHAR(255)) ORDER BY ISNULL(COUNT(*)), COUNT(*) DESC ``` 返回行數：31 更新行數：0 執行耗時：128ms >很明顯更清晰 # 建議增加索引 索引1： DDL語句： ALTER TABLE `public_health`.`cmf_region` ADD INDEX rds_idx_0 (`level`); 索引2： DDL語句： ALTER TABLE `public_health`.`cmf_region_relevance` ADD INDEX rds_idx_1 (`province_id`); 現存索引： public_health.cmf_region_relevance: `idx_provinceid_uid` (`province_id`, `uid`) ============== 增加索引后 返回行數：31 更新行數：0 執行耗時：123ms