Rumah > pangkalan data > tutorial mysql > Mysql-SQL优化-统计某种类型的个数_MySQL

Mysql-SQL优化-统计某种类型的个数_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Lepaskan: 2016-06-01 13:04:11
asal
1152 orang telah melayarinya

有时我们想统计某种类型有多少个,会用这个SQL。全表扫描之余,还要filesort,耗时1.34秒。
Salin selepas log masuk
 
Salin selepas log masuk
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)

Salin selepas log masuk
以下是两种优化方法,都是全表扫描,但估计count()要比sum()耗的CPU少点,方法更佳。
Salin selepas log masuk
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)

Salin selepas log masuk
版本mysql5.5.30
Salin selepas log masuk

Label berkaitan:
Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Isu terkini
Mengapa ia masih melompat selepas mengembalikan palsu?
daripada 1970-01-01 08:00:00
0
0
0
Optimumkan indeks jadual dalam MySQL
daripada 1970-01-01 08:00:00
0
0
0
Pasang XML-RPC dalam PHP8 Dockerfile
daripada 1970-01-01 08:00:00
0
0
0
Tutorial Popular
Lagi>
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan