root 11:23: [xx]> explain select v.user_uuid,
-> CONCAT(v.vip_id,'_^',
-> if(v.product_id =0,0,p.renew),'_^',v.product_id,'_^',v.channel_uuid,'_^',
-> if(v.price =0,0,1),'_^',if(p.days is null,0,p.days),'_^',if(g.level is null,0,g.level),'_^',if( o.platform_id is null,0, o.platform_id)
-> ,'_^',if( o.version is null,'', o.version),'_^',if(p.cate is null,0,p.cate) ) as vip_renew,
-> CAST(1 AS signed) as cnt from vip_service v left join product p on p.id = v.product_id left join vip_growth g on v.user_uuid = g.user_uuid
-> left join `order` o on o.uuid = v.order_uuid
-> where v.vip_end_time > '2019-11-17' and v.vip_begin_time < '2019-11-18' and v.created_at < '2019-11-18' and v.channel_uuid != 'CCCCCCCCCCCC' and v.status = 0
-> and 1=1 GROUP BY v.user_uuid;
+--------------------------------------+--------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | count | task | operator info |
+--------------------------------------+--------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_11 | 66268316.00 | root | boss.v.user_uuid, concat(cast(boss.v.vip_id), "_^", cast(if(eq(boss.v.product_id, 0), 0, boss.p.renew)), "_^", cast(boss.v.product_id), "_^", boss.v.channel_uuid, "_^", cast(if(eq(boss.v.price, 0), 0, 1)), "_^", cast(if(isnull(boss.p.days), 0, boss.p.days)), "_^", cast(if(isnull(boss.g.level), 0, boss.g.level)), "_^", cast(if(isnull(boss.o.platform_id), 0, boss.o.platform_id)), "_^", if(isnull(boss.o.version), "", boss.o.version), "_^", cast(if(isnull(boss.p.cate), 0, boss.p.cate))), 1 |
| └─HashAgg_14 | 66268316.00 | root | group by:boss.v.user_uuid, funcs:firstrow(boss.v.user_uuid), firstrow(boss.v.product_id), firstrow(boss.v.vip_id), firstrow(boss.v.price), firstrow(boss.v.channel_uuid), firstrow(boss.p.days), firstrow(boss.p.cate), firstrow(boss.p.renew), firstrow(boss.g.level), firstrow(boss.o.platform_id), firstrow(boss.o.version) |
| └─HashLeftJoin_22 | 282468018.91 | root | left outer join, inner:TableReader_81, equal:[eq(boss.v.order_uuid, boss.o.uuid)] |
| ├─HashLeftJoin_45 | 281904068.33 | root | left outer join, inner:TableReader_79, equal:[eq(boss.v.user_uuid, boss.g.user_uuid)] |
| │ ├─HashLeftJoin_62 | 281904068.33 | root | left outer join, inner:TableReader_77, equal:[eq(boss.v.product_id, boss.p.id)] |
| │ │ ├─TableReader_65 | 225523254.66 | root | data:Selection_64 |
| │ │ │ └─Selection_64 | 225523254.66 | cop | eq(boss.v.status, 0), gt(boss.v.vip_end_time, 2019-11-17 00:00:00.000000), lt(boss.v.created_at, 2019-11-18 00:00:00.000000), lt(boss.v.vip_begin_time, 2019-11-18 00:00:00.000000), ne(boss.v.channel_uuid, "CCCCCCCCCCCC") |
| │ │ │ └─TableScan_63 | 291659675.00 | cop | table:v, range:[0,+inf], keep order:false |
| │ │ └─TableReader_77 | 9752.00 | root | data:TableScan_76 |
| │ │ └─TableScan_76 | 9752.00 | cop | table:p, range:[-inf,+inf], keep order:false |
| │ └─TableReader_79 | 23904583.00 | root | data:TableScan_78 |
| │ └─TableScan_78 | 23904583.00 | cop | table:g, range:[-inf,+inf], keep order:false |
| └─TableReader_81 | 490041900.00 | root | data:TableScan_80 |
| └─TableScan_80 | 490041900.00 | cop | table:o, range:[-inf,+inf], keep order:false |
+--------------------------------------+--------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
14 rows in set (0.01 sec)
root 11:27: [xx]> show stats_meta where table_name in('vip_service','product','vip_growth','order');
+---------+-------------+----------------+---------------------+--------------+-----------+
| Db_name | Table_name | Partition_name | Update_time | Modify_count | Row_count |
+---------+-------------+----------------+---------------------+--------------+-----------+
| payment | order | | 2019-09-09 18:25:01 | 46883 | 8485394 |
| boss | product | | 2019-11-19 11:26:15 | 4483 | 9752 |
| boss | vip_growth | | 2019-11-19 11:28:14 | 8015466 | 23904633 |
| boss | order | | 2019-11-19 10:48:14 | 1258477 | 490041900 |
| boss | vip_service | | 2019-11-19 10:49:15 | 10390062 | 291659675 |
+---------+-------------+----------------+---------------------+--------------+-----------+
5 rows in set (0.01 sec)
CREATE TABLE `vip_service` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`user_uuid` char(32) NOT NULL DEFAULT '' COMMENT '用户uuid',
`order_uuid` char(32) NOT NULL DEFAULT '' COMMENT '订单号',
`product_id` int(11) NOT NULL COMMENT '产品id',
`vip_begin_time` datetime NOT NULL COMMENT '开始时间',
`vip_end_time` datetime NOT NULL COMMENT '结束时间',
`vip_id` tinyint(1) NOT NULL COMMENT 'vip等级',
`days` int(8) NOT NULL COMMENT '天数',
`price` int(11) NOT NULL COMMENT '每天价格',
`channel_uuid` char(32) NOT NULL DEFAULT '' COMMENT '渠道uuid',
`batch_id` int(5) NOT NULL COMMENT '厂商',
`device_id` varchar(128) NOT NULL COMMENT '设备id',
`voucher_number` tinyint(4) NOT NULL COMMENT '赠送的代金券数量',
`created_at` datetime NOT NULL COMMENT '创建时间',
`updated_at` datetime NOT NULL COMMENT '更新时间',
`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0:正常;1禁用;2:已删除;3:超级',
`renew` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否连续包月1表示连续包月',
`priority` int(5) NOT NULL DEFAULT '0' COMMENT '优先级',
`cate` int(5) NOT NULL DEFAULT '0' COMMENT '产品种类 1:VIP产品;-2:单点包产品;2:集合包产品;3:优惠券类;4:芒果币;5:频道源单点;6:频道源集合',
PRIMARY KEY (`id`),
KEY `device_id` (`device_id`),
KEY `idx_user_uuid` (`user_uuid`,`vip_end_time`),
KEY `idx_order_uuid` (`order_uuid`),
KEY `union_vip_time_vip_id_created_index_101` (`vip_begin_time`,`vip_end_time`,`vip_id`,`created_at`),
KEY `union_vip_time_cate_user_uuid_index_101` (`created_at`,`vip_end_time`,`vip_begin_time`,`cate`,`user_uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=292657999
CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`name` varchar(128) NOT NULL,
`days` int(11) NOT NULL,
`kind` int(11) NOT NULL,
`price` int(11) NOT NULL,
`cate` int(11) NOT NULL,
`status` int(11) NOT NULL,
`show` int(11) NOT NULL,
`vip_id` int(11) DEFAULT NULL,
`pid_alias` varchar(64) DEFAULT '' COMMENT '产品id别名',
`mark` varchar(128) DEFAULT NULL,
`mark_weight` int(11) NOT NULL,
`operator_type` int(11) NOT NULL,
`sync_sid` varchar(128) DEFAULT NULL COMMENT '同步给银沙的产品id',
`join_at` datetime NOT NULL,
`show_price` int(11) NOT NULL,
`promotion` varchar(128) DEFAULT NULL,
`ct_json_stored` varchar(512) DEFAULT NULL,
`renew` tinyint(3) unsigned DEFAULT '0' COMMENT '0 非续订产品 1 续订产品',
`single_sale` int(11) DEFAULT NULL COMMENT '单点折扣,单位：折',
`pay_channel` varchar(256) DEFAULT '' COMMENT '只允许购买的支付渠道',
`client_type` tinyint(1) unsigned DEFAULT '1' COMMENT '1mpp产品2ott产品',
`location` tinyint(1) unsigned DEFAULT NULL COMMENT '显示位置',
`invoker` varchar(255) DEFAULT NULL COMMENT '只允许购买的终端，多个逗号分隔',
`pay_channel_black` varchar(255) DEFAULT NULL COMMENT '不允许购买的支付渠道',
`ott_product_id` int(11) unsigned DEFAULT NULL COMMENT 'ott端对应的产品id',
`vip_discount` tinyint(1) unsigned DEFAULT '0' COMMENT 'vip用户订购时享受的折扣',
`settle_price` int(11) NOT NULL DEFAULT '0' COMMENT '计算价格(0:根据渠道结算,>0:结算价格,<0:结算价格0)',
`black_version` varchar(1024) NOT NULL DEFAULT '' COMMENT '产品针对终端的黑名单',
`up_uuid` varchar(64) NOT NULL DEFAULT '' COMMENT '产品所属up主',
`renew_pid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '下次续费的产品id',
`prod_set_id` int(11) NOT NULL DEFAULT '0' COMMENT '体验产品所属集合ID(trial_prod_set.id)',
`renew_before_days` int(11) NOT NULL DEFAULT '0' COMMENT '最多提前几天续费(单位:天)',
`first_level` varchar(64) NOT NULL DEFAULT '' COMMENT '一级会员产品名称: 数字表示月数. 1 表示1个月,3表示3个月',
`limit_cnt` int(11) NOT NULL DEFAULT '0' COMMENT '限制数量,0表示不限制',
`phone_price` int(11) NOT NULL DEFAULT '0' COMMENT '话费支付价格',
`service_type_id` int(11) NOT NULL DEFAULT '0' COMMENT '产品所属服务类ID,1为mgtv会员',
`pay_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '支付方式(0:在线支付;1:接口开通)',
`service_id` int(11) NOT NULL DEFAULT '0' COMMENT '服务ID',
PRIMARY KEY (`id`),
KEY `product_4da47e07` (`name`),
KEY `product_21ec032f` (`kind`),
KEY `product_063d78c2` (`vip_id`),
KEY `client_type` (`client_type`),
KEY `idx_service_type` (`service_type_id`),
KEY `idx_service` (`service_id`),
KEY `idx_alias` (`pid_alias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=284417
CREATE TABLE `order` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`uuid` varchar(255) NOT NULL,
`channel_id` int(11) NOT NULL,
`business_id` int(11) NOT NULL,
`platform_id` int(11) NOT NULL,
`status` int(11) NOT NULL,
`paid_at` datetime DEFAULT NULL,
`amount` int(11) NOT NULL,
`account_id` int(11) NOT NULL,
`relation_order_id` int(11) DEFAULT NULL,
`version` varchar(255) DEFAULT NULL,
`mac` varchar(255) DEFAULT NULL,
`uip` char(56) DEFAULT NULL,
`voucher_id` int(11) NOT NULL DEFAULT '0' COMMENT '代金券ID',
`voucher_amount` int(11) NOT NULL DEFAULT '0' COMMENT '代金券抵扣金额',
`cxid` char(64) DEFAULT NULL COMMENT '推广渠道',
`price` int(11) unsigned DEFAULT '0' COMMENT '用户价格',
`is_test` tinyint(1) unsigned DEFAULT '0' COMMENT '是否测试订单1是0否',
`source_uuid` char(32) NOT NULL DEFAULT '' COMMENT '业务来源订单号',
PRIMARY KEY (`id`),
UNIQUE KEY `uuid` (`uuid`),
KEY `order_9e85bf2d` (`channel_id`),
KEY `order_e3d75fef` (`platform_id`),
KEY `order_93025c2f` (`account_id`),
KEY `union_paid_at_updated_at_channel_id_platform_101_index` (`paid_at`,`updated_at`,`channel_id`,`platform_id`),
KEY `idx_created_at` (`created_at`),
KEY `union_updated_at_channel_id_platform_101_index` (`updated_at`,`channel_id`,`platform_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=678431263
CREATE TABLE `vip_growth` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`user_uuid` char(32) NOT NULL DEFAULT '' COMMENT '用户uuid',
`score` int(11) NOT NULL DEFAULT '0' COMMENT '成长值',
`level` int(4) NOT NULL DEFAULT '0' COMMENT '会员成长等级',
`update_time` datetime NOT NULL DEFAULT '1970-01-01 08:00:00' COMMENT '更新时间',
`upgrade_time` datetime NOT NULL DEFAULT '1970-01-01 08:00:00' COMMENT '最近升级日期',
`degrade_time` datetime NOT NULL DEFAULT '1970-01-01 08:00:00' COMMENT '最近降级日期',
`daily_update_time` datetime NOT NULL DEFAULT '1970-01-01 08:00:00' COMMENT '日常更新时间',
`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态(0:有效;1:删除)',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_user_uuid` (`user_uuid`),
KEY `idx_upgrade_degrade` (`upgrade_time`,`degrade_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=90931620 COMMENT='会员成长值'
