【MySql】Mysql-SQL优化-统计某种类型的个数

大数据

有时我们想统计某种类型有多少个，会用这个SQL。全表扫描之余，还要filesort，耗时1.34秒。

1. mysql>  select country,count(*) from t1 group by country;
   
2. +---------+----------+
   
3. | country | count(*) |
   
4. +---------+----------+
   
5. | NULL    |       32 |
   
6. | africa  |   524288 |
   
7. | america |   524288 |
   
8. | china   |   524288 |
   
9. +---------+----------+
   
10. 4 rows in set (1.34 sec)
    
11. 
    
12. mysql> desc select country,count(*) from t1 group by country;
    
13. +----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
    
14. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                           |
    
15. +----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
    
16. |  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1573382 | Using temporary; Using filesort |
    
17. +----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
    
18. 1 row in set (0.00 sec)

复制代码

1. 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;
   
2. +--------+---------+--------+
   
3. | africa | america | china  |
   
4. +--------+---------+--------+
   
5. | 524288 |  524288 | 524288 |
   
6. +--------+---------+--------+
   
7. 1 row in set (0.78 sec)
   
8. 
   
9. 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;
   
10. +----+-------------+-------+------+---------------+------+---------+------+---------+-------+
    
11. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra |
    
12. +----+-------------+-------+------+---------------+------+---------+------+---------+-------+
    
13. |  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1573382 |       |
    
14. +----+-------------+-------+------+---------------+------+---------+------+---------+-------+
    
15. 1 row in set (0.00 sec)
    
16. 
    
17. 
    
18. 
    
19. mysql> select sum(country='africa') as africa ,sum(country='america') as america,sum(country='china') from t1;
    
20. +--------+---------+----------------------+
    
21. | africa | america | sum(country='china') |
    
22. +--------+---------+----------------------+
    
23. | 524288 |  524288 |               524288 |
    
24. +--------+---------+----------------------+
    
25. 1 row in set (0.86 sec)
    
26. 
    
27. mysql> desc select sum(country='africa') as africa ,sum(country='america') as america,sum(country='china') from t1;
    
28. +----+-------------+-------+------+---------------+------+---------+------+---------+-------+
    
29. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra |
    
30. +----+-------------+-------+------+---------------+------+---------+------+---------+-------+
    
31. |  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1573382 |       |
    
32. +----+-------------+-------+------+---------------+------+---------+------+---------+-------+
    
33. 1 row in set (0.00 sec)

复制代码

1. 版本mysql5.5.30

复制代码