一条慢查询sql的的分析(二)
id ?FROM (select max(id) as id from tt ? group by name, rule_id) as a left join tt b on a.id = b.id |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)
?
看一看执行 过程
mysql> show profile for query 6; +------------------------------+----------+ | Status | Duration | +------------------------------+----------+ | Sending data | 0.000596 | | Waiting for query cache lock | 0.000002 | | Sending data | 0.000579 | | Waiting for query cache lock | 0.000002 | | Sending data | 0.000534 | | Waiting for query cache lock | 0.000002 | | Sending data | 1.101490 | | end | 0.000010 | | query end | 0.000004 | | closing tables | 0.000003 | | removing tmp table | 0.001369 | | closing tables | 0.000010 | | freeing items | 0.000024 | | logging slow query | 0.000002 | | logging slow query | 0.000035 | | cleaning up | 0.000003 | +------------------------------+----------+ 100 rows in set (0.00 sec)
?
?
太多的查询缓存lock，关掉它，再看一下查询结果
mysql> show profile for query 10; +---------------------------+----------+ | Status | Duration | +---------------------------+----------+ | starting | 0.000154 | | checking permissions | 0.000006 | | checking permissions | 0.000005 | | Opening tables | 0.000124 | | System lock | 0.000122 | | optimizing | 0.000007 | | statistics | 0.000035 | | preparing | 0.000018 | | executing | 0.000012 | | Sorting result | 0.000006 | | Sending data | 0.518897 | | converting HEAP to MyISAM | 0.070147 | | Sending data | 0.067123 | | init | 0.000033 | | optimizing | 0.000003 | | statistics | 0.000020 | | preparing | 0.000009 | | executing | 0.000001 | | Sending data | 1.193679 | | end | 0.000011 | | query end | 0.000010 | | closing tables | 0.000002 | | removing tmp table | 0.001491 | | closing tables | 0.000011 | | freeing items | 0.000020 | | logging slow query | 0.000002 | | logging slow query | 0.000050 | | cleaning up | 0.000003 | +---------------------------+----------+
?
?
再次分析，我们发现 converting HEAP to MyISAM 这个很耗时
?
mysql> select @@max_heap_table_size/1024/1024; +---------------------------------+ | @@max_heap_table_size/1024/1024 | +---------------------------------+ | 16.00000000 | +---------------------------------+ mysql> set max_heap_table_size = 16777216*4; Query OK, 0 rows affected (0.00 sec) mysql> select @@max_heap_table_size/1024/1024; +---------------------------------+ | @@max_heap_table_size/1024/1024 | +---------------------------------+ | 64.00000000 | +---------------------------------+
?
?
再看看执行结果 ?1.77579775 > 1.68962725 ，还是有效果的
?
mysql> show profiles;
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration ? | Query ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------+
| ? ? ? 17 | 1.68962725 | SELECT a.id,b.name,b.rule_id ?FROM (select max(id) as id from tt ? group by name, rule_id) as a left join tt b on a.id = b.id |
+----------+------------+-----------------------