Tidb查询被阻塞,无法正常查询

为提高效率,提问时请尽量提供详细背景信息,问题描述清晰可优先响应。以下信息点请尽量提供:

  • 系统版本 & kernel 版本】 centos 7 & 3.10.0-957.5.1.el7.x86_64
  • TiDB 版本】 v3.0.0 GA
  • 磁盘型号】 阿里云本地SSD
  • 集群节点分布】 tidb:4,pd:3,tikv:9
  • 数据量 & region 数量 & 副本数】 4.8T& 67420& 3
  • 问题描述(我做了什么)】 恢复一个mysql的dump文件(压缩177G),总数据量应该有2T左右
  • 关键词

其中一个tidb在执行导入操作,即恢复177G备份文件。同时有大量的统计sql执行和insert操作。 后面发现这个tidb所有sql都被阻塞,无法正常返回结构。就连简单的select * from xxx limit 1都被阻塞。只能通过kill tidb xxx才能终止。正在恢复的操作,提示超时:

tidb日志一直刷如下日志:

[2019/10/15 11:23:03.627 +08:00] [WARN] [backoff.go:313] ["tikvRPC backoffer.maxSleep 40000ms is exceeded, errors:
send tikv request error: context deadline exceeded, ctx: region ID: 1114193, meta: id:1114193 start_key:"t\200\000\000\000\000\000\007\251_i\200\000\000\000\000\000\000\001\003\200\000\000\000\0014\027 \003\200\000\000\000\016kh\001" end_key:"t\200\000\000\000\000\000\007\251_i\200\000\000\000\000\000\000\001\003\200\000\000\000\0014\027 \003\200\000\000\000\016z\016\001" region_epoch:<conf_ver:89 version:1436 > peers:<id:1114194 store_id:1 > peers:<id:1114195 store_id:5 > peers:<id:1114196 store_id:1005501 > , peer: id:1114195 store_id:5 , addr: 10.43.101.90:20160, idx: 1, try next peer later at 2019-10-15T11:22:02.113754233+08:00
not leader: region_id:1114193 leader:<id:1114195 store_id:5 > , ctx: region ID: 1114193, meta: id:1114193 start_key:"t\200\000\000\000\000\000\007\251_i\200\000\000\000\000\000\000\001\003\200\000\000\000\0014\027 \003\200\000\000\000\016kh\001" end_key:"t\200\000\000\000\000\000\007\251_i\200\000\000\000\000\000\000\001\003\200\000\000\000\0014\027 \003\200\000\000\000\016z\016\001" region_epoch:<conf_ver:89 version:1436 > peers:<id:1114194 store_id:1 > peers:<id:1114195 store_id:5 > peers:<id:1114196 store_id:1005501 > , peer: id:1114196 store_id:1005501 , addr: 10.43.208.210:20160, idx: 2 at 2019-10-15T11:22:02.125162749+08:00
send tikv request error: context deadline exceeded, ctx: region ID: 1114193, meta: id:1114193 start_key:"t\200\000\000\000\000\000\007\251_i\200\000\000\000\000\000\000\001\003\200\000\000\000\0014\027 \003\200\000\000\000\016kh\001" end_key:"t\200\000\000\000\000\000\007\251_i\200\000\000\000\000\000\000\001\003\200\000\000\000\0014\027 \003\200\000\000\000\016z\016\001" region_epoch:<conf_ver:89 version:1436 > peers:<id:1114194 store_id:1 > peers:<id:1114195 store_id:5 > peers:<id:1114196 store_id:1005501 > , peer: id:1114195 store_id:5 , addr: 10.43.101.90:20160, idx: 1, try next peer later at 2019-10-15T11:23:03.627878809+08:00"]
[2019/10/15 11:23:03.636 +08:00] [WARN] [client.go:619] ["send request is cancelled"] [to=10.43.101.90:20160] [cause="context deadline exceeded"]
[2019/10/15 11:23:03.636 +08:00] [INFO] [region_cache.go:372] ["switch region peer to next due to send request fail"] [conn=727] [current="region ID: 1224712, meta: id:1224712 start_key:"t\200\000\000\000\000\000\010\313_i\200\000\000\000\000\000\000\002\00120180222\37700040023\37715017039\377\000\000\000\000\000\000\000\000\367\003\200\000\000\000\003\243\315^" end_key:"t\200\000\000\000\000\000\010\313_i\200\000\000\000\000\000\000\002\00120180225\37720065569\37745144879\377\000\000\000\000\000\000\000\000\367\003\200\000\000\000\003\207\326\252" region_epoch:<conf_ver:89 version:5728 > peers:<id:1224713 store_id:1 > peers:<id:1224714 store_id:5 > peers:<id:1224715 store_id:1005501 > , peer: id:1224714 store_id:5 , addr: 10.43.101.90:20160, idx: 1"] [needReload=false] [error="context deadline exceeded"] [errorVerbose="context deadline exceeded
github.com/pingcap/errors.AddStack
	/home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/errors.go:174
github.com/pingcap/errors.Trace
	/home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/errors@v0.11.4/juju_adaptor.go:15
github.com/pingcap/tidb/store/tikv.sendBatchRequest
	/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/store/tikv/client.go:621
github.com/pingcap/tidb/store/tikv.(*rpcClient).SendRequest
	/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/store/tikv/client.go:658
github.com/pingcap/tidb/store/tikv.(*RegionRequestSender).sendReqToRegion
	/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/store/tikv/region_request.go:145
github.com/pingcap/tidb/store/tikv.(*RegionRequestSender).SendReqCtx
	/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/store/tikv/region_request.go:116
github.com/pingcap/tidb/store/tikv.(*RegionRequestSender).SendReq
	/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/store/tikv/region_request.go:72
github.com/pingcap/tidb/store/tikv.(*tikvStore).SendReq
	/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/store/tikv/kv.go:367
github.com/pingcap/tidb/store/tikv.(*twoPhaseCommitter).prewriteSingleBatch
	/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/store/tikv/2pc.go:497
github.com/pingcap/tidb/store/tikv.(*twoPhaseCommitter).doActionOnBatches.func1
	/home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb/store/tikv/2pc.go:423
runtime.goexit
	/usr/local/go/src/runtime/asm_amd64.s:1337"]

在这之前,对tikv进行了扩容,由于均衡速度较慢,将max-snapshot-count从3调整到8。 这个tidb无法正常使用时,其它3个tidb能正常使用,相同的sql无阻塞情况。

[tidb@iZ2ze2d8u10y3fuv5c6w0yZ bin]$ ./tidb-server -V
Release Version: v3.0.0
Git Commit Hash: 60965b006877ca7234adaced7890d7b029ed1306
Git Branch: HEAD
UTC Build Time: 2019-06-28 12:14:07
GoVersion: go version go1.12 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false

出现问题后,将max-snapshot-count从8调整到3。并强制kill -9 tidb(stop脚本无法正常停掉),再重启后恢复正常。

当时TiKV节点有什么报错信息

[2019/10/15 11:30:59.938 +08:00] [ERROR] [process.rs:179] ["get snapshot failed"] [err="Request(message: "region epoch is not match" epoch_not_match { current_regions { id: 1037546 start_key: 7480000000000003FF8D5F698000000000FF0000020100000000FF00000000F7038000FF00001270676F0000FD end_key: 7480000000000003FF8D5F698000000000FF0000020131383033FF32313039FF353939FF3735353132FF3537FF353831360000FD03FF8000000005F334F7FF0000000000000000F7 region_epoch { conf_ver: 77 version: 515 } peers { id: 1037547 store_id: 5 } peers { id: 1037548 store_id: 1 } peers { id: 1037549 store_id: 1005501 } } current_regions { id: 1228661 start_key: 7480000000000003FF8D5F698000000000FF0000020100000000FF00000000F7038000FF0000124D579A0000FD end_key: 7480000000000003FF8D5F698000000000FF0000020100000000FF00000000F7038000FF00001270676F0000FD region_epoch { conf_ver: 77 version: 515 } peers { id: 1228662 store_id: 5 } peers { id: 1228663 store_id: 1 } peers { id: 1228664 store_id: 1005501 } } })"] [cid=123049243]
[2019/10/15 11:30:59.943 +08:00] [ERROR] [process.rs:179] ["get snapshot failed"] [err="Request(message: "region epoch is not match" epoch_not_match { current_regions { id: 1037546 start_key: 7480000000000003FF8D5F698000000000FF0000020100000000FF00000000F7038000FF00001270676F0000FD end_key: 7480000000000003FF8D5F698000000000FF0000020131383033FF32313039FF353939FF3735353132FF3537FF353831360000FD03FF8000000005F334F7FF0000000000000000F7 region_epoch { conf_ver: 77 version: 515 } peers { id: 1037547 store_id: 5 } peers { id: 1037548 store_id: 1 } peers { id: 1037549 store_id: 1005501 } } current_regions { id: 1228661 start_key: 7480000000000003FF8D5F698000000000FF0000020100000000FF00000000F7038000FF0000124D579A0000FD end_key: 7480000000000003FF8D5F698000000000FF0000020100000000FF00000000F7038000FF00001270676F0000FD region_epoch { conf_ver: 77 version: 515 } peers { id: 1228662 store_id: 5 } peers { id: 1228663 store_id: 1 } peers { id: 1228664 store_id: 1005501 } } })"] [cid=123049249]
[2019/10/15 11:31:00.002 +08:00] [ERROR] [process.rs:179] ["get snapshot failed"] [err="Request(message: "region epoch is not match" epoch_not_match { current_regions { id: 1037546 start_key: 7480000000000003FF8D5F698000000000FF0000020100000000FF00000000F7038000FF00001270676F0000FD end_key: 7480000000000003FF8D5F698000000000FF0000020131383033FF32313039FF353939FF3735353132FF3537FF353831360000FD03FF8000000005F334F7FF0000000000000000F7 region_epoch { conf_ver: 77 version: 515 } peers { id: 1037547 store_id: 5 } peers { id: 1037548 store_id: 1 } peers { id: 1037549 store_id: 1005501 } } current_regions { id: 1228661 start_key: 7480000000000003FF8D5F698000000000FF0000020100000000FF00000000F7038000FF0000124D579A0000FD end_key: 7480000000000003FF8D5F698000000000FF0000020100000000FF00000000F7038000FF00001270676F0000FD region_epoch { conf_ver: 77 version: 515 } peers { id: 1228662 store_id: 5 } peers { id: 1228663 store_id: 1 } peers { id: 1228664 store_id: 1005501 } } })"] [cid=123049276]
[2019/10/15 11:31:18.809 +08:00] [INFO] [size.rs:167] ["approximate size over threshold, need to do split check"] [threshold=150994944] [size=155173163] [region_id=115336]
[2019/10/15 11:31:18.809 +08:00] [INFO] [keys.rs:149] ["approximate keys over threshold, need to do split check"] [threshold=1440000] [keys=1580100] [region_id=115336]
[2019/10/15 11:31:28.811 +08:00] [INFO] [size.rs:167] ["approximate size over threshold, need to do split check"] [threshold=150994944] [size=159249775] [region_id=115336]
[2019/10/15 11:31:28.811 +08:00] [INFO] [keys.rs:149] ["approximate keys over threshold, need to do split check"] [threshold=1440000] [keys=1624411] [region_id=115336]

补充个信息,出问题的tidb配置不一样要比其它tidb要好些,是16core 128G,配置增加了max-memory = 0

看一下当时监控,这台tidb节点的内存使用情况,cpu使用情况是怎么样的

可以看下TiKV Server当时的监控是有有异常,并检查一下TiDB节点与TiKV节点之间的网络是否正常

网络是正常的,有一个tikv的kv_batch_get突了下。

您好: 从返回的日志查看,与此bug匹配:

修复 TiKV Client Batch gRPC 的后台线程 panic 后导致 TiDB 无法正常连接 TiKV 进而无法提供服务的问 题 #11101

    建议可以考虑升级到新版本,谢谢.

已经升级到最新版本v3.0.5 release,目前还在观察。

麻烦了,有问题随时联系:handshake:

目前没有出现tidb卡死的情况了,不过已经发生了两次tidb被oom了,之前没有发生过。

麻烦提供一下发生oom时tidb的相关日志

goroutine 1154811 [select]:
github.com/pingcap/tidb/store/tikv.(*copIteratorWorker).sendToRespCh(0xce07666dc0, 0xc48b0d8b80, 0xc005ca2ea0, 0x1, 0xdc14dcf320)
        github.com/pingcap/tidb@/store/tikv/coprocessor.go:573 +0xd2
github.com/pingcap/tidb/store/tikv.(*copIteratorWorker).handleCopResponse(0xce07666dc0, 0xdc448809a0, 0xdc1ee3fef0, 0xc48b0d8b80, 0xc62a08aa80, 0xc005ca2ea0, 0x0, 0xdc2c236600, 0xdc1ee3fef0, 0x0, ...)
        github.com/pingcap/tidb@/store/tikv/coprocessor.go:846 +0x193
github.com/pingcap/tidb/store/tikv.(*copIteratorWorker).handleTaskOnce(0xce07666dc0, 0xdc448809a0, 0xc62a08aa80, 0xc005ca2ea0, 0x70, 0x68, 0x1f7e0a0, 0xc00fef9fb0, 0xc00fef9ef0)
        github.com/pingcap/tidb@/store/tikv/coprocessor.go:702 +0x406
github.com/pingcap/tidb/store/tikv.(*copIteratorWorker).handleTask(0xce07666dc0, 0xdc448809a0, 0xc62a08aa80, 0xc005ca2ea0)
        github.com/pingcap/tidb@/store/tikv/coprocessor.go:645 +0x103
github.com/pingcap/tidb/store/tikv.(*copIteratorWorker).run(0xce07666dc0, 0x230d2a0, 0xc314e67170)
        github.com/pingcap/tidb@/store/tikv/coprocessor.go:475 +0x1a4
created by github.com/pingcap/tidb/store/tikv.(*copIterator).open
        github.com/pingcap/tidb@/store/tikv/coprocessor.go:502 +0x99

goroutine 1154810 [select]:
github.com/pingcap/tidb/store/tikv.(*copIteratorWorker).sendToRespCh(0xce07666d80, 0xc035f1f180, 0xc005ca2ea0, 0x1, 0xdc708fed80)
        github.com/pingcap/tidb@/store/tikv/coprocessor.go:573 +0xd2
github.com/pingcap/tidb/store/tikv.(*copIteratorWorker).handleCopResponse(0xce07666d80, 0xdc6e8322a0, 0xdc6be465f0, 0xc035f1f180, 0xc62a08abc0, 0xc005ca2ea0, 0x0, 0xdc6d931b00, 0xdc6be465f0, 0x0, ...)
        github.com/pingcap/tidb@/store/tikv/coprocessor.go:846 +0x193
github.com/pingcap/tidb/store/tikv.(*copIteratorWorker).handleTaskOnce(0xce07666d80, 0xdc6e8322a0, 0xc62a08abc0, 0xc005ca2ea0, 0x70, 0x68, 0x1f7e0a0, 0xc003ce7fb0, 0xc003ce7ef0)
        github.com/pingcap/tidb@/store/tikv/coprocessor.go:702 +0x406
github.com/pingcap/tidb/store/tikv.(*copIteratorWorker).handleTask(0xce07666d80, 0xdc6e8322a0, 0xc62a08abc0, 0xc005ca2ea0)
        github.com/pingcap/tidb@/store/tikv/coprocessor.go:645 +0x103
github.com/pingcap/tidb/store/tikv.(*copIteratorWorker).run(0xce07666d80, 0x230d2a0, 0xc314e67170)
        github.com/pingcap/tidb@/store/tikv/coprocessor.go:475 +0x1a4
created by github.com/pingcap/tidb/store/tikv.(*copIterator).open
        github.com/pingcap/tidb@/store/tikv/coprocessor.go:502 +0x99

goroutine 1154813 [select]:
github.com/pingcap/tidb/distsql.(*selectResult).fetch(0xc465220180, 0x230d2a0, 0xc49f954570)
        github.com/pingcap/tidb@/distsql/select_result.go:114 +0x234
created by github.com/pingcap/tidb/distsql.(*selectResult).Fetch
        github.com/pingcap/tidb@/distsql/select_result.go:85 +0x53

dmesg信息

[2316965.223876] [ 9893]  1000  9893 33228665 32485234   63601        0             0 tidb-server
[2316965.223879] Out of memory: Kill process 9893 (tidb-server) score 987 or sacrifice child
[2316965.224794] Killed process 9893 (tidb-server) total-vm:132914660kB, anon-rss:129939524kB, file-rss:1412kB, shmem-rss:0kB

tidb配置:16core 128G

你好: 1. 请反馈完整的tidb日志和tikv日志 2. 问题发生的时间 3. 知道当时在执行哪条sql吗?

时间:10:39:23 左右 tidb_10.log.gz (1.3 MB)

执行完一个sql,内存使用会达到70G,再执行一个时,就会被oom。

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

tidb配置,改了 oom-action = “cancel”
mem-quota-query = 64359738368

TiKV_coprocessor_request_wait_seconds 执行期间有这个报警。 报警这台tikv的日志: tikv_10.log.gz (356.0 KB)

您好: 请反馈以下信息: 1. explain select v.user_uuid … 这条sql的执行计划 2. show stats_meta where table_name in(‘vip_service’,‘product’,‘vip_growth’,‘order’); 3. 反馈下这4个表的表结构信息包含索引

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='会员成长值'