有时我们想统计某种类型有多少个，会用这个SQL。全表扫描之余，还要filesort，耗时1.34秒。
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 | |+----+-------------+-------+-<em style="color:transparent">本文来源[email protected]搞@^&代*@码网(</em>-----+---------------+------+---------+------+---------+-------+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
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：Mysql-SQL优化-统计某种类型的个数_MySQL
转载请注明原文链接：Mysql-SQL优化-统计某种类型的个数_MySQL
