group by 优化问题

Question

mysql> select count(*) from phpcms_ads_stat;
+----------+
| count(*) |
+----------+
|  9345149 | 
+----------+
1 row in set (0.00 sec)mysql> show index from phpcms_ads_stat;
+-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table           | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| phpcms_ads_stat |          0 | PRIMARY   |            1 | id          | A         |     9345149 |     NULL | NULL   |      | BTREE      |         | 
| phpcms_ads_stat |          1 | adsid     |            1 | adsid       | A         |         588 |     NULL | NULL   |      | BTREE      |         | 
| phpcms_ads_stat |          1 | adsid     |            2 | type        | A         |         588 |     NULL | NULL   |      | BTREE      |         | 
| phpcms_ads_stat |          1 | adsid     |            3 | ip          | A         |     4672574 |     NULL | NULL   |      | BTREE      |         | 
| phpcms_ads_stat |          1 | clicktime |            1 | clicktime   | A         |     4672574 |     NULL | NULL   |      | BTREE      |         | 
+-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)mysql> explain SELECT *, COUNT(*) AS num FROM phpcms_ads_stat WHERE adsid=114   AND type=1 GROUP BY area ORDER BY num DESC;
+----+-------------+-----------------+------+---------------+-------+---------+-------------+------+----------------------------------------------+
| id | select_type | table           | type | possible_keys | key   | key_len | ref         | rows | Extra                                        |
+----+-------------+-----------------+------+---------------+-------+---------+-------------+------+----------------------------------------------+
|  1 | SIMPLE      | phpcms_ads_stat | ref  | adsid         | adsid | 4       | const,const | 7696 | Using where; Using temporary; Using filesort | 
+----+-------------+-----------------+------+---------------+-------+---------+-------------+------+----------------------------------------------+
1 row in set (0.00 sec)mysql> 想知道这个查询语句还需要怎么优化？这个查询要20几秒

Answer 1

创建(adsid,type,area)的复合索引。

Answer 2

贴出来看一下 show index from phpcms_ads_stat;
explain SELECT *, COUNT(*) AS num FROM phpcms_ads_stat WHERE adsid=114   AND type=1 GROUP BY area ORDER BY num DESC;

Answer 3

mysql> show index from phpcms_ads_stat;
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| phpcms_ads_stat |          0 | PRIMARY  |            1 | id          | A         |     3588426 |     NULL | NULL   |      | BTREE      |         | 
| phpcms_ads_stat |          1 | adsid    |            1 | adsid       | A         |         271 |     NULL | NULL   |      | BTREE      |         | 
| phpcms_ads_stat |          1 | adsid    |            2 | type        | A         |         271 |     NULL | NULL   |      | BTREE      |         | 
| phpcms_ads_stat |          1 | adsid    |            3 | ip          | A         |     1794213 |     NULL | NULL   |      | BTREE      |         | 
| phpcms_ads_stat |          1 | area     |            1 | area        | A         |         474 |     NULL | NULL   |      | BTREE      |         | 
| phpcms_ads_stat |          1 | ata      |            1 | adsid       | A         |         271 |     NULL | NULL   |      | BTREE      |         | 
| phpcms_ads_stat |          1 | ata      |            2 | type        | A         |         271 |     NULL | NULL   |      | BTREE      |         | 
| phpcms_ads_stat |          1 | ata      |            3 | area        | A         |        3156 |     NULL | NULL   |      | BTREE      |         | 
+-----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (0.02 sec)mysql> explain SELECT *, COUNT(*) AS num FROM phpcms_ads_stat WHERE adsid=114 AND type=1 GROUP BY area ORDER BY num DESC;
+----+-------------+-----------------+------+---------------+------+---------+-------------+-------+----------------------------------------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref         | rows  | Extra                                        |
+----+-------------+-----------------+------+---------------+------+---------+-------------+-------+----------------------------------------------+
|  1 | SIMPLE      | phpcms_ads_stat | ref  | adsid,ata     | ata  | 4       | const,const | 14868 | Using where; Using temporary; Using filesort | 
+----+-------------+-----------------+------+---------------+------+---------+-------------+-------+----------------------------------------------+
1 row in set (0.00 sec)

Answer 4

已经使用了ATA索引，这样已经是最优化了。
在3588426 条记录中，有14868 多条符合要求。然后你的排序是以COUNT（*）排的，只能产生结果集后再进行排序Using filesort。