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 |
+----------+------------+-----------------------