记公司邮件组里的一次sql优化讨论

Hi ALL

无线有一张表，需要对其中一个token字段按照一个规则除重

表结构如下：

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

| Field         | Type         | Null | Key | Default | Extra          |

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

| sysid         | bigint(20)   | NO   | PRI | NULL    | auto_increment |

| username      | varchar(50)  | YES  |     | NULL    |                |

| uid           | varchar(50)  | NO   |     | NULL    |                |

| vid           | varchar(50)  | NO   |     | NULL    |                |

| token         | varchar(170) | NO   | MUL | NULL    |                |

| pid           | char(5)      | NO   | MUL | 10010   |                |

| tokentype     | tinyint(2)   | NO   |     | NULL    |                |

| lastlogintime | datetime     | YES  |     | NULL    |                |

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

除重的sql语句如下：

update pushtoken inner join (select max(lastlogintime) maxtime,count(1),token from pushtoken where pid = "10010" group by token having count(1) >1 ) as t2 on pushtoken.token = t2.token set pushtoken.token = null where pushtoken.lastlogintime <> t2.maxtime ;

翻译成人类语言就是，如果token有重复，就保留lastlogintime最新的，其他的置成空

问题在于：
该表一共390w+行数据，mysql执行此语句，需要好几个小时，求优化方案。

回复一：
[quote]
参考方案：
生成个临时表，结构一样，把token字段设置成不能重复，
然后insert into 临时表 select * from 表 order by lastlogintime desc，
再rename 临时表。
注意备份。
Insert的时候加下ignore。
[/quote]

回复二：
[quote]

临时想的解决思路，不知道是否符合你得需求：

创建个临时表，保存不置空token的信息

CREATE TABLE `tmp_pushtoken` (
  `sysid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '唯一id',
  `token` varchar(170) NOT NULL COMMENT '实际的token或者requestid',
  PRIMARY KEY (`sysid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

从原表导出不置空token的sysid,token信息保存到临时表

insert into tmp_pushtoken SELECT sysid,token FROM pushtoken WHERE pid = "10010" GROUP BY token HAVING count(1) >1 AND MAX(lastlogintime);

原表指定条件所有token置空

update pushtoken SET token = null WHERE AND pid = "10010";

将临时表token信息恢复到原表中

update pushtoken INNER JOIN tmp_pushtoken SET pushtoken.token=tmp_pushtoken.token ON USING(sysid);

[/quote]

回复三：
[quote]
呃，我觉得这主要是查询慢吧，token字段不适合建索引么？
[/quote]

回复四：
[quote]
1，

select max(lastlogintime) maxtime,count(1),token from pushtoken where pid = "10010" group by token having count(1) >1

这个单独做个临时表，然后 maxtime上头建立个索引、token上头建立个索引。

2，把上面的表代换回原来的查询里头(原表的token字段、lastlogin字段也要索引
一个）。估计能快个十几倍，但应该也是分钟级别 的，还好是清洗数据。

3，drop掉步骤1里头的表。

4，原该弄not null约束的，就得加上了

p.s. 原著要求不能rename table，所以下头这个子查询的方案是我建议的，起码
不用编码能完活儿。不过预计到会慢了，在dml里头的子查询不是mysql的强项。
[/quote]