【导读】

    webgame行业在游戏成熟了以后，合服、混服操作是常态，随着合服、混服的次数越来越多，单个服包含之前的合服或者混服数据的db越来越大，而分析数据发现，很多玩家在游戏内注册后，没玩几个等级提升就流失了，特别是利用cpm导入那种大量用户的服，然后为了最大化利用现有资源，保护硬件上的投资，就需要清理小号的操作，清理小号的操作经历了一波三折,期间还经历过一起sql性能的“血案”，本文记录清理小号演变的3个版本以及其中发生的一起“血案”

    

    1)一般清理小号的策略大致如下：

多少等级以下的角色

多少天没登陆的角色

从来没有充值的角色

……

    2)清小号的大致流程演变历程如下：

    2.1)第一版本的清小号

    研发的一个实习生写的用sp来处理清理小号，清理小号的思想是从player表和pay_detail_record表联合查询取一条player_id，然后去其他的表中按player_id删除相关记录(一般webgame的表数量在50~200之间)

    第一次看研发兄弟提过来的sp做review，猜测此sp处理性能应该很差，时间应该消耗比较长，顺便让DBA同事测试此sp的性能，大致给同事说了按批player_id处理的思想来修改，也即第二版本的清小号处理，然后做下性能对比，结果发现：sp处理需要30分钟处理完，第二版本的清小号3分钟就完成，性能有很大提升

    2.2)第二版本的清小号(当时看到测试报告的性能很好，就没有详细review这里，失职啊)

    先建立需要删除的player_id中间表，然后和需要删除数据的其他表做delete left join处理，直接上代码，伪代码如下：

create table tmp_pid as SELECT player_id FROM player WHERE player_level <=30 AND PLAYER_ID NOT IN (SELECT player_id FROM pay_detail_record ) ;——注意这里的子查询

alter table tmp_pid add index idx_playerid_tp(player_id);

DELETE lt.* FROM tb1 lt left join tmp_pid on lt.player_id=tmp_pid.player_id WHERE tmp_pid.player_id IS NOT NULL;

DELETE lt.* FROM tb2 lt left join tmp_pid on lt.playerid=tmp_pid.player_id WHERE tmp_pid.player_id IS NOT NULL;

……

    第二版本的清小号一直在生产环境使用没发现有大的问题，直到某天的清理小号发生“血案”，也推动的第三版本的清小号尽快到来

    “血案”是这样制造的(此db是超过10个db通过多次的合服和混服合并到一起的)：

    表结构如下：

    root@localhost:d1 14:06:23>show create table player\\G

     *************************** 1. row ***************************

     Table: player

     Create Table: CREATE TABLE `player` (

     `player_id` int(11) NOT NULL DEFAULT ’0′,

     `copper` int(11) DEFAULT ’0′,

     `food` int(11) DEFAULT ’0′,

     `prestige` int(11) DEFAULT ’0′,

     `forces` int(11) DEFAULT ’0′,

     `player_level` int(4) NOT NULL DEFAULT ’1′,

     `user_id` bigint(19) DEFAULT NULL,

     `officerId` int(11) DEFAULT ’0′,

     `src` int(11) DEFAULT NULL,

     `player_type` int(1) NOT NULL DEFAULT ’0′,

     `loyalty` int(11) DEFAULT ’0′,

     `innewarea` tinyint(3) DEFAULT ’0′ COMMENT ‘

     PRIMARY KEY (`player_id`)

     ) ENGINE=InnoDB DEFAULT CHARSET=utf8

     1 row in set (0.00 sec)

    root@localhost:d1 14:06:31>show create table pay_detail_record\\G

     *************************** 1. row ***************************

     Table: pay_detail_record

     Create Table: CREATE TABLE `pay_detail_record` (

     `id` int(11) NOT NULL DEFAULT ’0′,

     `player_id` int(11) DEFAULT NULL,

     `user_id` bigint(19) DEFAULT NULL,

     PRIMARY KEY (`id`),

     KEY `idx_playerid_pdr` (`player_id`)

     ) ENGINE=InnoDB DEFAULT CHARSET=utf8

     1 row in set (0.00 sec)

    表相关的记录数：

    root@localhost:d1 14:06:45>select count(*) from player;

     +———-+

     | count(*) |

     +———-+

     | 546109 |

     +———-+

     1 row in set (0.10 sec)

     root@localhost:d1 15:18:05>select count(id),count(distinct player_id) from pay_detail_record;

     +———-+—————————+

     | count(id) | count(distinct player_id) |

     +———-+—————————+

     | 25650 | 1690 |

     +———-+—————————+

     1 row in set (0.02 sec)

    root@localhost:d1 14:08:20>create table tmp_pid as SELECT player_id FROM player WHERE player_level <=30 AND PLAYER_ID NOT IN (SELECT player_id FROM pay_detail_record GROUP BY player_id) ;

     Query OK, 539728 rows affected (1 hour 5 min 33.31 sec)——-注意这里的记录数和执行时间

     Records: 539728 Duplicates: 0 Warnings: 0

    root@localhost:d1 15:16:21>select 539728/546109*100;

     +——————-+

     | 539728/546109*100 |

     +——————-+

     | 98.8316 |————————————————————清除的比例很大

     +——————-+

     1 row in set (0.00 sec)

    root@localhost:d1 15:19:32>select 1690/25650*100;

     +—————-+

     | 1690/25650*100 |

     +—————-+

     | 6.5887 |————————————————————-唯一性的比例也很大

     +—————-+

     1 row in set (0.00 sec)

    大家可以将上面两处红色标记的地方对比下，这个执行1Hour 5min 33.31sec的sql和第二版本的清小号脚本多了个group by，显然问题是出在这个group by上,原来同事测试第二版本的清小号，一般都是几分钟之内就解决的，这次的清理小号，建立中间表就话了65分钟,大大拖延了处理时间，但是group by性能为什么这么差呢？留作后话，暂不表…

    “血案”就是在某个游戏的大版本升级中，研发同学在升级清小号脚本的时候，在子查询内加了个group by(如上面红色标记，和第二版本的清小号红色标记对比下)

    然后我们尝试修改这个建立中间表的sql语句，下面是测试

    root@localhost:d1 15:23:45>create table tmp_pid1 SELECT sql_no_cache player.player_id FROM player left join pay_detail_record on player.player_id=pay_detail_record.player_id where player.player_level <=30 and pay_detail_record.player_id is null;

     Query OK, 539728 rows affected (5.35 sec)

     Records: 539728 Duplicates: 0 Warnings: 0

    root@localhost:d1 15:23:45>create table tmp_pid2 SELECT sql_no_cache player.player_id FROM player WHERE player_level <=30 AND NOT exists (SELECT 1 FROM pay_detail_record M where M.player_id=player.player_id) ;

     Query OK, 539728 rows affected (6.16 sec)

     Records: 539728 Duplicates: 0 Warnings: 0

    root@localhost:d1 15:23:58>create table tmp_pid3 SELECT sql_no_cache player.player_id FROM player WHERE player_level <=30 AND PLAYER_ID NOT IN (SELECT distinct player_id FROM pay_detail_record) ;

     Query OK, 539728 rows affected (6.12 sec)

     Records: 539728 Duplicates: 0 Warnings: 0

    root@localhost:d1 15:24:08>create table tmp_pid4 SELECT sql_no_cache player.player_id FROM player WHERE player_level <=30 AND PLAYER_ID NOT IN (SELECT player_id FROM pay_detail_record) ;

     Query OK, 539728 rows affected (6.22 sec)

     Records: 539728 Duplicates: 0 Warnings: 0

    然后，我们分别看下执行计划

    root@localhost:d1 15:21:23>explain extended SELECT sql_no_cache player_id FROM player WHERE player_level <=30 AND PLAYER_ID NOT IN (SELECT player_id FROM pay_detail_record GROUP BY player_id) ;

     +—-+——————-+——————-+——-+—————+——————+———+——+——-+———-+————-+

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

     +—-+——————-+——————-+——-+—————+——————+———+——+——-+———-+————-+

     | 1 | PRIMARY | player | ALL | NULL | NULL | NULL | NULL | 555743 | 100.00 | Using where |

     | 2 | DEPENDENT SUBQUERY | pay_detail_record | index | NULL | idx_playerid_pdr | 5 | NULL | 63 | 5142.86 | Using index |

     +—-+——————-+——————-+——-+—————+——————+———+——+——-+———-+————-+

     2 rows in set, 1 warning (0.00 sec)

    5142.86———-？？？

    root@localhost:d1 15:21:36>show warnings\\G

     *************************** 1. row ***************************

     Level: Note

     Code: 1003

     Message: select sql_no_cache `d1`.`player`.`player_id` AS `player_id` from `d1`.`player` where ((`d1`.`player`.`player_level` <= 30) and (not((`d1`.`player`.`playe

     r_id`,<exists>(select `d1`.`pay_detail_record`.`player_id` from `d1`.`pay_detail_record` group by `d1`.`pay_detail_record`.`player_id` having ((`d1`.`player`.`player_id`)

     = (`d1`.`pay_detail_record`.`player_id`)))))))

     1 row in set (0.00 sec)

    root@localhost:d1 15:26:49>explain extended SELECT sql_no_cache count(*) FROM player left join pay_detail_record on player.player_id=pay_detail_record.player_id where player.player_level <=30 and pay_detail_record.player_id is null;

     +—-+————-+——————-+——+——————+——————+———+———————+——-+———-+————————-+

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

     +—-+————-+——————-+——+——————+——————+———+———————+——-+———-+————————-+

     | 1 | SIMPLE | player | ALL | NULL | NULL | NULL | NULL | 555743 | 100.00 | Using where |

     | 1 | SIMPLE | pay_detail_record | ref | idx_playerid_pdr | idx_playerid_pdr | 5 | d1.player.player_id | 8 | 100.00 | Using where; Using index |

     +—-+————-+——————-+——+——————+——————+———+———————+——-+———-+————————-+

     2 rows in set, 1 warning (0.00 sec)

    root@localhost:d1 15:28:16>show warnings\\G

     *************************** 1. row ***************************

     Level: Note

     Code: 1003

     Message: select sql_no_cache count(0) AS `count(*)` from `d1`.`player` left join `d1`.`pay_detail_record` on((`d1`.`pay_detail_record`.`player_id` = `d1`.`player`.`player_id`))

     where ((`d1`.`player`.`player_level` <= 30) and isnull(`d1`.`pay_detail_record`.`player_id`))

     1 row in set (0.00 sec)

     root@localhost:d1 15:29:42>explain extended SELECT sql_no_cache count(*) FROM player WHERE player_level <=30 AND NOT exists (SELECT 1 FROM pay_detail_record M where M.player_id=player.player_id) ;

     +—-+——————-+——-+——+——————+——————+———+———————+——-+———-+————————-+

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

     +—-+——————-+——-+——+——————+——————+———+———————+——-+———-+————————-+

     | 1 | PRIMARY | player | ALL | NULL | NULL | NULL | NULL | 555743 | 100.00 | Using where |

     | 2 | DEPENDENT SUBQUERY | M | ref | idx_playerid_pdr | idx_playerid_pdr | 5 | d1.player.player_id | 8 | 100.00 | Using where; Using index |

     +—-+——————-+——-+——+——————+——————+———+———————+——-+———-+————————-+

     2 rows in set, 2 warnings (0.00 sec)

    root@localhost:d1 15:29:47>show warnings\\G *************************** 1. row ***************************

     Level: Note

     Code: 1276

     Message: Field or reference ‘d1.player.player_id’ of SELECT #2 was resolved in SELECT #1

     *************************** 2. row ***************************

     Level: Note

     Code: 1003

     Message: select sql_no_cache count(0) AS `count(*)` from `d1`.`player` where ((`d1`.`player`.`player_level` <= 30) and (not(exists(select 1 from `d1`.`pay_detail_record` `m` whe

     re (`d1`.`m`.`player_id` = `d1`.`player`.`player_id`)))))

     2 rows in set (0.00 sec)

    root@localhost:d1 15:30:17>explain extended SELECT sql_no_cache count(*) FROM player WHERE player_level <=30 AND PLAYER_ID NOT IN (SELECT distinct player_id FROM pay_detail_record) ;

     +—-+——————-+——————-+—————-+——————+——————+———+——+——-+———-+————-+

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

     +—-+——————-+——————-+—————-+——————+——————+———+——+——-+———-+————-+

     | 1 | PRIMARY | player | ALL | NULL | NULL | NULL | NULL | 555743 | 100.00 | Using where |

     | 2 | DEPENDENT SUBQUERY | pay_detail_record | index_subquery | idx_playerid_pdr | idx_playerid_pdr | 5 | func | 16 | 100.00 | Using index |

     +—-+——————-+——————-+—————-+——————+——————+———+——+——-+———-+————-+

     2 rows in set, 1 warning (0.00 sec)

    root@localhost:d1 15:30:26>show warnings\\G *************************** 1. row ***************************

     Level: Note

     Code: 1003

     Message: select sql_no_cache count(0) AS `count(*)` from `d1`.`player` where ((`d1`.`player`.`player_level` <= 30) and (not((`d1`.`player`.`player_id`,(

     dex_lookup>((`d1`.`player`.`player_id`) in pay_detail_record on idx_playerid_pdr checking NULL having (`d1`.`pay_detail_record`.`player_id`)))))))

     1 row in set (0.00 sec)

     root@localhost:d1 15:31:03>explain extended SELECT sql_no_cache count(*) FROM player WHERE player_level <=30 AND PLAYER_ID NOT IN (SELECT player_id FROM pay_detail_record) ;

     +—-+——————-+——————-+—————-+——————+——————+———+——+——-+———-+————-+

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

     +—-+——————-+——————-+—————-+——————+——————+———+——+——-+———-+————-+

     | 1 | PRIMARY | player | ALL | NULL | NULL | NULL | NULL | 555743 | 100.00 | Using where |

     | 2 | DEPENDENT SUBQUERY | pay_detail_record | index_subquery | idx_playerid_pdr | idx_playerid_pdr | 5 | func | 16 | 100.00 | Using index |

     +—-+——————-+——————-+—————-+——————+——————+———+——+——-+———-+————-+

     2 rows in set, 1 warning (0.00 sec)

    root@localhost:d1 15:31:10>show warnings\\G

     *************************** 1. row ***************************

     Level: Note

     Code: 1003

     Message: select sql_no_cache count(0) AS `count(*)` from `d1`.`player` where ((`d1`.`player`.`player_level` <= 30) and (not(>(`d1`.`player`.`player_id`,(<in

     dex_lookup>((`d1`.`player`.`player_id`) in pay_detail_record on idx_playerid_pdr checking NULL having (`d1`.`pay_detail_record`.`player_id`)))))))

     1 row in set (0.00 sec)

    留心上面几处蓝色和红色的标记处,特别是蓝色标记处

    我曾经在weibo上咨询过专家，链接参考这里：http://weibo.com/1856362104/y3KD93ufr

    还有这里何登成的测试和分析及源码跟踪：http://weibo.com/2216172320/y4gpg28K3

    2.3)第三版本的清小号

    当然是修改了第二版本的group by语句，并且去掉in(其实我们的规范中是不允许写in的子查询的，除非很少的常量并能利用索引的)

    并且在第二版本的时候，我们就发现在删除大量数据的时候，ibdata文件会膨胀很大(我们是per-table)，因为删除大量的数据时候没有及时的commit；导致undo消耗了大量的空间，而mysql的ibdata目前还是不能自动shrink的，我们在很多的delete语句之间适当的时候commit掉，但此方法处理只能针对单个表来commit，还是不够优雅，期望按单表的几k条记录来commit，否者遇到删除一个特别大的表的时候，ibdata还是会膨胀很大；另外，另外就是set sql_log_bin=0咯

    webgame行业的朋友应该也有这样的类似需求，你们是怎么样优雅处理的，请赐教，我只是在此抛砖引玉一下，希望大拿们提供更好的优雅处理方法，谢谢。

建议继续学习：

1. 改变了对Mysql子查询的看法    (阅读：3012)
2. oracle 子查询写法    (阅读：2365)