mysql query count sql 效能调优记录(-)

count字段带来的低效sql

 

一、大概过程：

1.  set profiling=1;
2. 
   

mysql> select  com_id ,company.name,count(company_albums.id) as albums_count  from company_albums ,company  where company_albums.com_id=company.id group by company.name order by albums_count desc limit 30;

+--------+-----------------------+--------------+
| com_id | name                  | albums_count |
+--------+-----------------------+--------------+
.........

30 rows in set (5.44 sec)

 

3.mysql> explain select  com_id ,company.name,count(company_albums.id) as albums_count  from company_albums ,company  where company_albums.com_id=company.id group by company.name order by albums_count desc limit 30;
+----+-------------+----------------+--------+---------------+---------+---------+------------------------------------+-------+---------------------------------+
| id | select_type | table          | type   | possible_keys | key     | key_len | ref                                | rows  | Extra                           |
+----+-------------+----------------+--------+---------------+---------+---------+------------------------------------+-------+---------------------------------+
|  1 | SIMPLE      | company_albums | ALL    | com_idx       | NULL    | NULL    | NULL                               | 72441 | Using temporary; Using filesort |
|  1 | SIMPLE      | company        | eq_ref | PRIMARY       | PRIMARY | 4       | test_01.company_albums.com_id |     1 |                                 |
+----+-------------+----------------+--------+---------------+---------+---------+------------------------------------+-------+---------------------------------+
2 rows in set (0.00 sec)

 

 

4。

mysql> show profiles;

 

mysql> show profiles;
+----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                                                                                                                            |
+----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+                                                                                                                                                                                                               |
|       63 | 5.43254700 | select  com_id ,company.name,count(company_albums.id) as albums_count  from company_albums ,company  where company_albums.com_id=company.id group by company.name order by albums_count desc limit 30                            |
|       65 | 0.00039400 | explain select  com_id ,company.name,count(company_albums.id) as albums_count  from company_albums ,company  where company_albums.com_id=company.id group by company.name order by albums_count desc limit 30                    |
+----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

 

mysql> show profile cpu ,block io for query 63;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000036 | 0.000000 |   0.000000 |            0 |             0 |
| checking query cache for query | 0.000104 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables                 | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |
| System lock                    | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |
| Table lock                     | 0.000039 | 0.000000 |   0.000000 |            0 |             0 |
| init                           | 0.000034 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing                     | 0.000013 | 0.000000 |   0.000000 |            0 |             0 |
| statistics                     | 0.000039 | 0.000000 |   0.000000 |            0 |             0 |
| preparing                      | 0.000018 | 0.000000 |   0.000000 |            0 |             0 |
| Creating tmp table             | 0.001009 | 0.000000 |   0.000000 |            0 |             0 |
| executing                      | 0.000017 | 0.000000 |   0.000000 |            0 |             0 |
| Copying to tmp table           | 5.428130 | 1.916120 |   5.116320 |              0 |             0 |
| Sorting result                 | 0.001783 | 0.012001 |   0.000000 |            0 |             0 |
| Sending data                   | 0.000074 | 0.004000 |   0.000000 |            0 |             0 |
| end                            | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table             | 0.000727 | 0.000000 |   0.000000 |            0 |             0 |
| end                            | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |
| query end                      | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000050 | 0.000000 |   0.000000 |            0 |             0 |
| storing result in query cache  | 0.000403 | 0.000000 |   0.000000 |            0 |             0 |
| logging slow query             | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up                    | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
22 rows in set (0.00 sec)

 

二、问题总结：

通过以上company_albums | ALL    | com_idx  综合得知全表扫描了，试着分析和调整

 

mysql> explain select  com_id ,company.name,count(company_albums.name) as albums_count  from company_albums ,company  where company_albums.com_id=company.id group by company.name order by albums_count desc limit 30  ;
+----+-------------+----------------+--------+---------------+---------+---------+------------------------------------+-------+---------------------------------+
| id | select_type | table          | type   | possible_keys | key     | key_len | ref                                | rows  | Extra                           |
+----+-------------+----------------+--------+---------------+---------+---------+------------------------------------+-------+---------------------------------+
|  1 | SIMPLE      | company_albums | ALL     | com_idx       | NULL    | NULL    | NULL                               | 72441 | Using temporary; Using filesort |
|  1 | SIMPLE      | company        | eq_ref | PRIMARY        | PRIMARY | 4       | test_01.company_albums.com_id |     1 |                                 |
+----+-------------+----------------+--------+---------------+---------+---------+------------------------------------+-------+---------------------------------+
2 rows in set (0.00 sec)

mysql> explain select  com_id ,company.name,count(*) as albums_count   from company_albums ,company  where company_albums.com_id=company.id group by company.name order by albums_count desc limit 30  ;
+----+-------------+----------------+-------+---------------+---------+---------+-------------------------+------+---------------------------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref                     | rows | Extra                           |
+----+-------------+----------------+-------+---------------+---------+---------+-------------------------+------+---------------------------------+
|  1 | SIMPLE      | company        | index | PRIMARY        | name    | 767     | NULL                    |    1 | Using temporary; Using filesort |
|  1 | SIMPLE      | company_albums | ref   | com_idx        | com_idx | 4        | test_01.company.id |  108 | Using index                     |
+----+-------------+----------------+-------+---------------+---------+---------+-------------------------+------+---------------------------------+
2 rows in set (0.00 sec)

 

mysql> select  com_id ,company.name,count(*) as albums_count  from company_albums ,company  where company_albums.com_id=company.id group by company.name order by albums_count desc limit 31;
+--------+-----------------------+--------------+
| com_id | name                  | albums_count |
+--------+-----------------------+--------------+
--------------------

--------------------

31 rows in set (0.09 sec)

 

三、结论

现在优化后只需要(0.09 sec)了，写sql的时候尽量多思考和谨慎，频繁查询的更要仔细