mysql> select country,count(*) from t1 group by country;+---------+----------+| country | count(*) |+---------+----------+| null | 32 || africa | 524288 || america | 524288 || china | 524288 |+---------+----------+4 rows in set (1.34 sec)mysql> desc select country,count(*) from t1 group by country;+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+| 1 | simple | t1 | all | null | null | null | null | 1573382 | using temporary; using filesort |+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+1 row in set (0.00 sec)
以下是两种优化方法，都是全表扫描，但估计count()要比sum()耗的cpu少点，方法更佳。
mysql> select count(country='africa' or null) as africa,count(country='america' or null) as america, count(country='china' or null) as china from t1;+--------+---------+--------+| africa | america | china |+--------+---------+--------+| 524288 | 524288 | 524288 |+--------+---------+--------+1 row in set (0.78 sec)mysql> desc select count(country='africa' or null) as africa,count(country='america' or null) as america, count(country='china' or null) as china from t1;+----+-------------+-------+------+---------------+------+---------+------+---------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |+----+-------------+-------+------+---------------+------+---------+------+---------+-------+| 1 | simple | t1 | all | null | null | null | null | 1573382 | |+----+-------------+-------+------+---------------+------+---------+------+---------+-------+1 row in set (0.00 sec)mysql> select sum(country='africa') as africa ,sum(country='america') as america,sum(country='china') from t1;+--------+---------+----------------------+| africa | america | sum(country='china') |+--------+---------+----------------------+| 524288 | 524288 | 524288 |+--------+---------+----------------------+1 row in set (0.86 sec)mysql> desc select sum(country='africa') as africa ,sum(country='america') as america,sum(country='china') from t1;+----+-------------+-------+------+---------------+------+---------+------+---------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |+----+-------------+-------+------+---------------+------+---------+------+---------+-------+| 1 | simple | t1 | all | null | null | null | null | 1573382 | |+----+-------------+-------+------+---------------+------+---------+------+---------+-------+1 row in set (0.00 sec)
版本mysql5.5.30