Mysql-SQL優化-統計某種類型的個數
有時我們想統計某種類型有多少個，會用這個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 | |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
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
