WebGame行业案例：in子查询group by引发的“血案” -- MySQL -- IT技术博客大学习 -- 共学习共进步！

您现在的位置：首页 --> MySQL --> WebGame行业案例：in子查询group by引发的“血案”

WebGame行业案例：in子查询group by引发的“血案”

浏览:2011次出处信息

【导读】

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) ;

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

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

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

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;

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

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

| 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) ;

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

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

| 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) ;

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

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

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

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

建议继续学习：

QQ技术交流群：445447336，欢迎加入！
扫一扫订阅我的微信号：IT技术博客大学习

<< 前一篇：MySQL数据库开源软件版本生产环境GA版本如何选择

后一篇：MySQL5.5数据库innodb_change_buffering怪异问题分析 >>

文章信息

作者：ruochen 来源： MySQLOPS 数据库与运维自动化技术分享
标签：子查询
发布时间：2012-03-31 23:27:11

建议继续学习

近3天十大热文