Mysql——SQL最佳化-統計某種類型的個數，mysqlsql最佳化個數

Mysql——SQL最佳化-統計某種類型的個數，mysqlsql最佳化個數

有時我們想統計某種類型有多少個，會用這個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

mysql資料庫，sql語句，把一個表中的統計數量，更新到兩一個表中

update b set b.cnum = (select c.counts from
(SELECT cid,COUNT(*) counts FROM comment GROUP BY cid) c
where a.cid=c.cid);

哎，禁不住說一聲，苦逼程式員啊，都這麼晚不睡

你試試吧，按照你描述的，我這麼寫應該就通過了，有問題直接hi
 
mysql SQL語句最佳化

select 指定的列看看, 還有盡量用索引來搜尋吧