1、schema

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
{
    "mappings": {
        "properties": {
            "remindRuleId": {
                "type": "keyword"
            },
            "remindDate": {
                "type": "date",
                "ignore_malformed": true,
                "format": "yyyy-MM-dd"
            },
            "carIdOwnOrg": {
                "type": "keyword"
            },
            "wechatFollower": {
                "type": "keyword"
            },
            "wechatSendState": {
                "type": "keyword"
            },
            "wechatSendDate": {
                "type": "date",
                "ignore_malformed": true,
                "format": "yyyy-MM-dd"
            },
            "phoneSendState": {
                "type": "keyword"
            },
            "phoneSendDate": {
                "type": "date",
                "ignore_malformed": true,
                "format": "yyyy-MM-dd"
            },
            "smsSendState": {
                "type": "keyword"
            },
            "smsSendDate": {
                "type": "date",
                "ignore_malformed": true,
                "format": "yyyy-MM-dd"
            },
            "couponSendState": {
                "type": "keyword"
            },
            "picSendState": {
                "type": "keyword"
            },
            "operatorId": {
                "type": "keyword"
            },
            "state": {
                "type": "keyword"
            },
            "isDeleted": {
                "type": "keyword"
            },
            "operatorProcessState": {
                "type": "keyword"
            },
            "consumeAgainState": {
                "type": "keyword"
            },
            "consumeAgainDate": {
                "type": "date",
                "ignore_malformed": true,
                "format": "yyyy-MM-dd"
            },
            "finishTime": {
                "type": "date",
                "ignore_malformed": true,
                "format": "yyyy-MM-dd"
            },
            "customerName": {
                "type": "text",
                "fields": {
                    "raw": {
                        "type": "keyword"
                    }
                },
                "analyzer": "ngram_10_analyzer"
            },
            "customerCellPhone": {
                "type": "text",
                "analyzer": "ngram_11_analyzer"
            },
            "fullCarNo": {
                "type": "text",
                "fields": {
                    "raw": {
                        "type": "keyword"
                    }
                },
                "analyzer": "ngram_8_analyzer"
            },
            "remindType": {
                "type": "keyword"
            },
            "idOwnOrg": {
                "type": "keyword"
            },
            "creationtime": {
                "type": "date",
                "ignore_malformed": true,
                "format": "yyyy-MM-dd"
            },
            "customerId": {
                "type": "keyword"
            },
            "carId": {
                "type": "keyword"
            },
            "groupId": {
                "type": "keyword"
            },
            "lastServiceDate": {
                "type": "date",
                "ignore_malformed": true,
                "format": "yyyy-MM-dd"
            },
            "lastServiceOrg": {
                "type": "keyword"
            },
            "operatorSendState": {
                "type": "keyword"
            },
            "pkId": {
                "type": "keyword"
            }
        }
    }
}

2、数据demo

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
{
  "_index": "customer_remind",
  "_type": "_doc",
  "_id": "4",
  "_version": 1,
  "_seq_no": 5,
  "_primary_term": 1,
  "found": true,
  "_source": {
    "wechatSendState": "1",
    "phoneSendDate": "2021-07-13",
    "groupId": "10907434497393152194",
    "remindDate": "2021-07-13",
    "lastServiceOrg": "10907434497393152194",
    "couponSendState": "1",
    "customerId": "10907434497393152194",
    "docUpdateTime": "2021-07-13",
    "smsSendState": "1",
    "operatorId": "10907434497393152194",
    "consumeAgainDate": "2021-07-13",
    "fullCarNo": "苏A85214",
    "carIdOwnOrg": "10907434497393152194",
    "idOwnOrg": "10907434497393152194",
    "finishTime": "2021-07-13",
    "smsSendDate": "2021-07-13",
    "consumeAgainState": "1",
    "lastServiceDate": "2021-07-13",
    "customerName": "张三",
    "carId": "10907434497393152194",
    "remindRuleId": "10907434497393152194",
    "picSendState": "1",
    "phoneSendState": "1",
    "wechatSendDate": "2021-07-13",
    "wechatFollower": "1",
    "operatorProcessState": "1",
    "creationtime": "2021-07-13",
    "customerCellPhone": "1888888888",
    "remindType": "0",
    "state":"0",
    "isDeleted":"0",
    "operatorSendState":"1",
    "pkId": "1"
  }
}

3、query dsl样例

3.1、查询提醒单数量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
POST  /customer_remind/_search?track_total_hits=true
{
  "query": {
    "bool": {
      "should": [
        {
          "terms": {
            "carIdOwnOrg": ["10907434497393152194","10907434497393152195"]
          }
        },
        {
          "terms": {
            "idOwnOrg": ["10907434497393152194"]
          }
        }
      ],
      "filter": [
        {
          "terms": {
            "state": [
              "0",
              "1"
            ]
          }
        },
        {
          "term": {
            "groupId": "10907434497393152194"
          }
        },
        {
          "term": {
            "remindType": "0"
          }
        },
        {
          "term": {
            "operatorProcessState": "1"
          }
        },
        {
          "term": {
            "operatorId": "10907434497393152194"
          }
        },
        {
          "term": {
            "consumeAgainState": "1"
          }
        },
        {
          "term": {
            "carId": "10907434497393152194"
          }
        }
      ]
    }
  },
  "size": 0
}

3.2、分页查询提醒单列表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
POST  /customer_remind/_search/
{
    "query": {
        "bool": {
            "must": [
                {
                    "match": {
                        "customerName": {
                            "analyzer": "ik_smart",
                            "query": "AAA",
                            "operator": "and"
                        }
                    }
                },
                {
                    "match": {
                        "customerCellPhone": {
                            "analyzer": "ik_smart",
                            "query": "A",
                            "operator": "and"
                        }
                    }
                },
                {
                    "match": {
                        "fullCarNo": {
                            "analyzer": "ik_smart",
                            "query": "A",
                            "operator": "and"
                        }
                    }
                }
            ],
            "should": [
                {
                    "terms": {
                        "carIdOwnOrg": [
                            "10907434497393152194",
                            "10907434497393152195"
                        ]
                    }
                },
                {
                    "terms": {
                        "idOwnOrg": [
                            "10907434497393152195"
                        ]
                    }
                }
            ],
            "filter": [
                {
                    "range": {
                        "lastServiceDate": {
                            "gte": "2021-07-12",
                            "lte": "2021-07-14"
                        }
                    }
                },
                {
                    "range": {
                        "lastServiceOrg": {
                            "gte": "0"
                        }
                    }
                },
                {
                    "range": {
                        "consumeAgainDate": {
                            "gte": "2021-07-12",
                            "lte": "2021-07-14"
                        }
                    }
                },
                {
                    "range": {
                        "finishTime": {
                            "gte": "2021-07-12",
                            "lte": "2021-07-14"
                        }
                    }
                },
                {
                    "range": {
                        "remindDate": {
                            "gte": "2021-07-12",
                            "lte": "2021-07-14"
                        }
                    }
                },
                {
                    "range": {
                        "creationtime": {
                            "gte": "2021-07-12",
                            "lte": "2021-07-14"
                        }
                    }
                },
                {
                    "terms": {
                        "carIdOwnOrg": [
                            "10907434497393152194",
                            "10907434497393152195"
                        ]
                    }
                },
                {
                    "term": {
                        "isDeleted": "0"
                    }
                },
                {
                    "term": {
                        "groupId": "10907434497393152194"
                    }
                },
                {
                    "term": {
                        "remindType": "0"
                    }
                },
                {
                    "term": {
                        "remindRuleId": "10907434497393152194"
                    }
                },
                {
                    "term": {
                        "wechatFollower": "1"
                    }
                },
                {
                    "term": {
                        "wechatSendState": "1"
                    }
                },
                {
                    "term": {
                        "phoneSendState": "1"
                    }
                },
                {
                    "term": {
                        "couponSendState": "1"
                    }
                },
                {
                    "term": {
                        "operatorId": "10907434497393152194"
                    }
                },
                {
                    "term": {
                        "operatorSendState": "1"
                    }
                },
                {
                    "term": {
                        "operatorProcessState": "1"
                    }
                },
                {
                    "term": {
                        "consumeAgainState": "1"
                    }
                },
                {
                    "term": {
                        "picSendState": "1"
                    }
                },
                {
                    "terms": {
                        "state": [
                            "0",
                            "1"
                        ]
                    }
                },
                {
                    "term": {
                        "carId": "10907434497393152194"
                    }
                },
                {
                    "term": {
                        "customerId": "10907434497393152194"
                    }
                },
                {
                    "terms": {
                        "lastServiceOrg": [
                            "10907434497393152194",
                            "10907434497393152195",
                            "0"
                        ]
                    }
                }
            ]
        }
    },
    "from": 0,
    "size": 10,
    "sort": {
        "creationtime": {
            "order": "desc"
        }
    }
}

4、数据

4.1、准备接搜索的服务提醒单数据量

  • 服务提醒单现有数量：2464000条（百万级别）
  • 服务提醒单每日新增数量：5096条（千级别）

4.2、年内准备迁移的保险提醒单及特殊人群提醒单数据数据量

  • 保险提醒现有数据：2678857条（百万级别），每日增量：10246条（万级别）
  • 特殊人群提醒现有数据：9695180条（近千万级别），每日增量：100354条（十万级别）

其中特殊人群提醒迁移之前会做归档，数据量应该会砍半。

4.3、接口请求情况

目前对接搜索的接口有两个：查询提醒单数量及分页查询提醒单列表。

每月请求总数：468.05K，每日请求数：15.6k

4.4、接口请求及数据预测

4.4.1、接口请求

从arms上看接口请求趋势，从上线到现在2个月，所有接口每日请求在18k左右，成正态分布趋势，未来一段时间内，接口请求应该不会激增。

4.4.2、提醒单数据量

现有250万左右服务提醒单数据，每月增30万左右数据。因业务需求，数据归档方案未定，今年年底，数据量可能会达到430万左右数据。

今年年底保险提醒及特殊人群提醒数据迁移后，提醒单总数据量在1500万左右。

4.5、附件

  • 根据条件查询提醒单数量接口请求趋势

img

  • 查询未完成的提醒单数量接口请求趋势

img

  • 查询服务提醒单列表接口请求趋势

img