[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