WebGame行业案例:in子查询group by引发的“血案”
【导读】
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处理,直接上代码,伪代码如下:
第二版本的清小号一直在生产环境使用没发现有大的问题,直到某天的清理小号发生“血案”,也推动的第三版本的清小号尽快到来
“血案”是这样制造的(此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(
r_id`,<exists>(select `d1`.`pay_detail_record`.`player_id` from `d1`.`pay_detail_record` group by `d1`.`pay_detail_record`.`player_id` having (
=
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(
dex_lookup>(
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(
dex_lookup>(
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行业的朋友应该也有这样的类似需求,你们是怎么样优雅处理的,请赐教,我只是在此抛砖引玉一下,希望大拿们提供更好的优雅处理方法,谢谢。
建议继续学习:
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:ruochen 来源: MySQLOPS 数据库与运维自动化技术分享
- 标签: 子查询
- 发布时间:2012-03-31 23:27:11
- [54] IOS安全–浅谈关于IOS加固的几种方法
- [52] android 开发入门
- [52] 如何拿下简短的域名
- [51] 图书馆的世界纪录
- [49] Go Reflect 性能
- [49] Oracle MTS模式下 进程地址与会话信
- [47] 【社会化设计】自我(self)部分――欢迎区
- [46] 读书笔记-壹百度:百度十年千倍的29条法则
- [35] 程序员技术练级攻略
- [27] 视觉调整-设计师 vs. 逻辑