分析

mysql> show profile for query  17; 
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000120 |
| checking permissions | 0.000004 |
| checking permissions | 0.000003 |
| Opening tables       | 0.000106 |
| System lock          | 0.000113 |
| optimizing           | 0.000007 |
| statistics           | 0.000044 |
| preparing            | 0.000011 |
| executing            | 0.000006 |
| Sorting result       | 0.000002 |
| Sending data         | 0.567858 |
| init                 | 0.000032 |
| optimizing           | 0.000004 |
| statistics           | 0.000017 |
| preparing            | 0.000015 |
| executing            | 0.000002 |
| Sending data         | 1.120159 |
| end                  | 0.000011 |
| query end            | 0.000005 |
| closing tables       | 0.000002 |
| removing tmp table   | 0.001020 |
| closing tables       | 0.000011 |
| freeing items        | 0.000018 |
| logging slow query   | 0.000002 |
| logging slow query   | 0.000056 |
| cleaning up          | 0.000004 |
+----------------------+----------+

?

?

好看多了，耗时的地方都在 Sending data 上了，如果硬盘换成PCI-SSD 估计又能提高不少

?

还有没有其他方法呢，当然有，那么换一种写法

?

先看查询计划

?

mysql> explain select max(concat_ws(' ',lpad(id,5,' '),status)) as res, name, rule_id from tt group by name, rule_id ;

+----+-------------+-------+-------+---------------+------+---------+------+---------+-------+

| id | select_type | table | type ?| possible_keys | key ?| key_len | ref ?| rows ? ?| Extra |

+----+-------------+-------+-------+---------------+------+---------+------+---------+-------+

| ?1 | SIMPLE ? ? ?| tt ? ?| index | NULL ? ? ? ? ?| ttx ?| 52 ? ? ?| NULL | 1176818 | ? ? ? |

+----+-------------+-------+-------+---------------+------+---------+------+---------+-------+

再看看执行结果

?

mysql> show profiles;

+----------+------------+-------------------------------------------------------------------------------------------------------------------------------+

| Query_ID | Duration ? | Query ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |

+----------+------------+-------------------------------------------------------------------------------------------------------------------------------+

| ? ? ? 22 | 1.82505025 | select max(concat_ws(' ',lpad(id,5,' '),status)) as res, name, rule_id from tt group by name, rule_id ? ? ? ? ? ? ? ? ? ? ? ? |

+----------+------------+-------------------------------------------------------------------------------------------------------------------------------+

好像更慢了 1.82505025 > 1.68962725

?

分析一下

?

mysql> show profile for query  22;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000157 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000019 |
| System lock          | 0.000020 |
| init                 | 0.000032 |
| optimizing           | 0.000005 |
| statistics           | 0.000016 |
| preparing            | 0.000012 |
| executing            | 0.000008 |
| Sorting result       | 0.000003 |
| Sending data         | 1.824677 |
| end                  | 0.000012 |
| query end            | 0.000005 |
| closing tables       | 0.000009 |
| freeing items        | 0.000016 |
| logging slow query   | 0.000002 |
| logging slow query   | 0.000049 |
| cleaning up          | 0.000004 |
+----------------------+----------+

?

?

时间基本上都花费在send data了

?

这里说一下，max + group by 完全就是一个坑，如果有多个字段要返回数据不能这样写

?