记自己一次sql优化（使用伪列）

起因

未优化之前的s q l,大表内连接次数太多，严重影响效率。因此需要优化。

sql 要满足的逻辑

以status_id分组，weight_id最大的行中（同一组中可能有好几个weight_id相同的行），mid最大的那一行

表结构

CREATE TABLE `at_x_0` (
  `uixd` ,
  `mixd` ,
  `status_ixd` ,
  `message_type` ,
  `time_ixd` ,
  `time` ,
  `weight_id` ,
  `extension` ,
  UNIQUE KEY `uid` (`mid`,`uid`),
  KEY `uid_2` (`uid`,`time_id`,`message_type`,`status_id`,`weight_id`),
  KEY `sw` (`status_id`,`weight_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

优化前的表结构（两次内连接）

“select e.* from $db$.$table$ e inner join
(
select max(c.mid) mid from
(select a.mid,a.status_id,a.weight_id
from $db$.$table$ a inner join
(select f.status_id,max(f.weight_id) weight_id
from at_message_0.at_message_0 f where f.uid=? and f.time_id=? and f.message_type=? and f.weight_id<=? group by f.status_id
) b on a.status_id=b.status_id and a.uid=? and a.message_type=? and a.weight_id=b.weight_id order by a.weight_id desc
) c group by c.status_id
) d where e.mid=d.mid and e.uid=? order by e.weight_id desc limit ?,?”;

优化前的执行计划

+------+-------------+------------+-------+---------------+-------+---------+------+-------+-------------------------------------------------+
| id   | select_type | table      | type  | possible_keys | key   | key_len | ref  | rows  | Extra                                           |
+------+-------------+------------+-------+---------------+-------+---------+------+-------+-------------------------------------------------+
|    1 | SIMPLE      | <derived2> | ALL   | NULL          | NULL  | NULL    | NULL | 50176 | Using temporary; Using filesort                 |
|    2 | DERIVED     | <derived4> | ALL   | NULL          | NULL  | NULL    | NULL |   224 | Using temporary; Using filesort                 |
|    2 | DERIVED     | b          | ALL   | NULL          | NULL  | NULL    | NULL |   224 | Using where; Using join buffer (flat, BNL join) |
|    4 | DERIVED     | a          | index | NULL          | uid_2 | 29      | NULL |   224 | Using index; Using temporary; Using filesort    |
+------+-------------+------------+-------+---------------+-------+---------+------+-------+-------------------------------------------------+

优化后的sql(一次右连接，加伪列)
set @num :=0, @group := ”;

select x.uid,x.mid,x.status_id,x.message_type,x.time_id,x.time,x.weight_id,x.extension from
(
select d.*,
@num := if(@group = d.status_id, @num:=@num-1, 0) as row_number,
@group := d.status_id as dummy from
(select b.* from at_message_0.at_message_0 b
right join
(select a.status_id,max(a.weight_id) as max_weight_id from at_message_0.at_message_0 a group by a.status_id) c
on b.status_id = c.status_id and b.weight_id= c.max_weight_id
) d order by d.status_id,d.mid desc
) as x group by x.status_id order by x.row_number desc;

优化后的执行计划

+------+-------------+------------+-------+---------------+------+---------+-----------------------------+------+---------------------------------+
| id   | select_type | table      | type  | possible_keys | key  | key_len | ref                         | rows | Extra                           |
+------+-------------+------------+-------+---------------+------+---------+-----------------------------+------+---------------------------------+
|    1 | SIMPLE      | <derived2> | ALL   | NULL          | NULL | NULL    | NULL                        |  201 | Using temporary; Using filesort |
|    2 | DERIVED     | <derived4> | ALL   | NULL          | NULL | NULL    | NULL                        |  201 | Using temporary; Using filesort |
|    2 | DERIVED     | b          | ref   | sw,sm         | sw   | 16      | c.status_id,c.max_weight_id |    1 | Using where                     |
|    4 | DERIVED     | a          | range | NULL          | sw   | 8       | NULL                        |  202 | Using index for group-by        |
+------+-------------+------------+-------+---------------+------+---------+-----------------------------+------+---------------------------------+