ElasticSearch 使用不同表结构存储时间序列数据的查询效率分析

这里我们使用和之前完全相同的测试数据，来测试 elasticsearch 存储时间序列的表结构选择问题。

一个点一个doc的表结构

同样我们以最简单的表结构开始。在elasticsearch中，先要创建index，然后index下有mapping。所谓的mapping就是表结构的概念。建表的配置如下：

1settings = {    'number_of_shards': 1,    'number_of_replicas': 0,    'index.query.default_field': 'timestamp',    'index.mapping.ignore_malformed': False,    'index.mapping.coerce': False,    'index.query.parse.allow_unmapped_fields': False,
2}
3mappings = {    'testdata': {        '_source': {'enabled': False},        '_all': {'enabled': False},        'properties': {            'timestamp': {                'type': 'date',                'index': 'no',                'store': False,                'dynamic': 'strict',                'doc_values': True,                'fielddata': {                    'format': 'doc_values'
4                }
5            },            'vAppid': {                'type': 'string',                'index': 'no',                'store': False,                'dynamic': 'strict',                'doc_values': True,                'fielddata': {                    'format': 'doc_values'
6                }
7            },            'iResult': {                'type': 'string',                'index': 'no',                'store': False,                'dynamic': 'strict',                'doc_values': True,                'fielddata': {                    'format': 'doc_values'
8                }
9            },            'vCmdid': {                'type': 'string',                'index': 'no',                'store': False,                'dynamic': 'strict',                'doc_values': True,                'fielddata': {                    'format': 'doc_values'
10                }
11            },            'dProcessTime': {                'type': 'integer',                'index': 'no',                'store': False,                'dynamic': 'strict',                'doc_values': True,                'fielddata': {                    'format': 'doc_values'
12                }
13            },            'totalCount': {                'type': 'integer',                'index': 'no',                'store': False,                'dynamic': 'strict',                'doc_values': True,                'fielddata': {                    'format': 'doc_values'
14                }
15            }
16        }
17    }
18}

表结构虽然没有做按时间段打包的高级优化，但是一些es相关的设置是特别值得注意的。首先_source被关闭了，这样原始的json文档不会被重复存储一遍。其次_all也被关闭了。而且每个字段的store都是False，也就是不会单独被存储。之前测试mongodb的时候，所有字段都没有建索引的，所以为了公平起见，这里把索引都关了。这些都关掉了，那么数据存哪里了？存在doc_values里。doc_values用于在做聚合运算的时候，根据一批文档id快速找到对应的列的值。doc_values在磁盘上一个按列压缩存储的文件，非常高效。

那么800多万行数据导入之后，磁盘占用情况如何？

size: 198Mi (198Mi)docs: 8,385,335 (8,385,335)

非常惊人，838万行在mongodb里占了3G的磁盘空间，导入es居然只占用了198M。即便把所有维度字段的索引加上膨胀也非常小。

size: 233Mi (233Mi)docs: 8,385,335 (8,385,335)

那么查询效率呢？

1q = {    'aggs': {        'timestamp': {            'terms': { 
2                'field': 'timestamp'
3            },            'aggs': {                'totalCount': {'sum': {'field': 'totalCount'}}
4            }
5        }
6    },
7}
8res = es.search(index="wentao-test1", doc_type='testdata', body=q, search_type='count')

同样是按时间聚合，取得同周期的totalCount之和。查询结果为：

1{u'_shards': {u'failed': 0, u'successful': 1, u'total': 1}, u'aggregations': {u'timestamp': {u'buckets': [{u'doc_count': 38304,     u'key': 1428789900000,     u'key_as_string': u'2015-04-11T22:05:00.000Z',     u'totalCount': {u'value': 978299.0}},
2    {u'doc_count': 38020,     u'key': 1428789960000,     u'key_as_string': u'2015-04-11T22:06:00.000Z',     u'totalCount': {u'value': 970089.0}},
3    {u'doc_count': 37865,     u'key': 1428789660000,     u'key_as_string': u'2015-04-11T22:01:00.000Z',     u'totalCount': {u'value': 917908.0}},
4    {u'doc_count': 37834,     u'key': 1428789840000,     u'key_as_string': u'2015-04-11T22:04:00.000Z',     u'totalCount': {u'value': 931039.0}},
5    {u'doc_count': 37780,     u'key': 1428790140000,     u'key_as_string': u'2015-04-11T22:09:00.000Z',     u'totalCount': {u'value': 972810.0}},
6    {u'doc_count': 37761,     u'key': 1428790020000,     u'key_as_string': u'2015-04-11T22:07:00.000Z',     u'totalCount': {u'value': 953866.0}},
7    {u'doc_count': 37738,     u'key': 1428790080000,     u'key_as_string': u'2015-04-11T22:08:00.000Z',     u'totalCount': {u'value': 969901.0}},
8    {u'doc_count': 37598,     u'key': 1428789600000,     u'key_as_string': u'2015-04-11T22:00:00.000Z',     u'totalCount': {u'value': 919538.0}},
9    {u'doc_count': 37541,     u'key': 1428789720000,     u'key_as_string': u'2015-04-11T22:02:00.000Z',     u'totalCount': {u'value': 920581.0}},
10    {u'doc_count': 37518,     u'key': 1428789780000,     u'key_as_string': u'2015-04-11T22:03:00.000Z',     u'totalCount': {u'value': 924791.0}}],   u'doc_count_error_upper_bound': 0,   u'sum_other_doc_count': 8007376}}, u'hits': {u'hits': [], u'max_score': 0.0, u'total': 8385335}, u'timed_out': False, u'took': 1033}

只花了1秒钟的时间，之前这个查询在mongodb里需要花9秒。那么是不是因为elasticsearch是并行数据库所以快呢？我们之前在创建index的时候故意指定了shard数量为1，所以这个查询只有一个机器参与的。为了好奇，我又试验了以下6个分片的。在分片为6的时候，总尺寸为259M（含索引），而上面那个查询只需要200ms。当然这里测试的时候使用的mongodb和es的机器不完全一样，也许是因为硬件原因呢？

第二个查询要复杂一些，按vAppid过滤，然后按timestamp和vCmdid两个维度聚合。查询如下：

1q = {    'query': {        'constant_score': {            'filter': {                'bool': {                    'must_not': {                        'term': {                            'vAppid': ''
2                        }
3                    }
4                }
5            }
6        },
7    },    'aggs': {        'timestamp': {            'terms': { 
8                'field': 'timestamp'
9            },            'aggs': {                'vCmdid': {                    'terms': { 
10                        'field': 'vCmdid'
11                    },                    'aggs': {                        'totalCount': {'sum': {'field': 'totalCount'}}
12                    }
13                }
14            }
15        }
16    },
17}
18res = es.search(index="wentao-test3", doc_type='testdata', body=q, search_type='count')

constant_score跳过了score阶段。查询结果如下：

1{u'_shards': {u'failed': 0, u'successful': 1, u'total': 1}, u'aggregations': {u'timestamp': {u'buckets': [{u'doc_count': 38304,     u'key': 1428789900000,     u'key_as_string': u'2015-04-11T22:05:00.000Z',     u'vCmdid': {u'buckets': [{u'doc_count': 7583,        u'key': u'10000',        u'totalCount': {u'value': 241108.0}},
2       {u'doc_count': 4122, u'key': u'19', u'totalCount': {u'value': 41463.0}},
3       {u'doc_count': 2312, u'key': u'14', u'totalCount': {u'value': 41289.0}},
4       {u'doc_count': 2257, u'key': u'18', u'totalCount': {u'value': 57845.0}},
5       {u'doc_count': 1723,        u'key': u'1002',        u'totalCount': {u'value': 33844.0}},
6       {u'doc_count': 1714,        u'key': u'2006',        u'totalCount': {u'value': 33681.0}},
7       {u'doc_count': 1646,        u'key': u'2004',        u'totalCount': {u'value': 28374.0}},
8       {u'doc_count': 1448, u'key': u'13', u'totalCount': {u'value': 32187.0}},
9       {u'doc_count': 1375, u'key': u'3', u'totalCount': {u'value': 32976.0}},
10       {u'doc_count': 1346,        u'key': u'2008',        u'totalCount': {u'value': 45932.0}}],      u'doc_count_error_upper_bound': 0,      u'sum_other_doc_count': 12778}},
11    ... // ignore
12    {u'doc_count': 37518,     u'key': 1428789780000,     u'key_as_string': u'2015-04-11T22:03:00.000Z',     u'vCmdid': {u'buckets': [{u'doc_count': 7456,        u'key': u'10000',        u'totalCount': {u'value': 234565.0}},
13       {u'doc_count': 4049, u'key': u'19', u'totalCount': {u'value': 39884.0}},
14       {u'doc_count': 2308, u'key': u'14', u'totalCount': {u'value': 39939.0}},
15       {u'doc_count': 2263, u'key': u'18', u'totalCount': {u'value': 57121.0}},
16       {u'doc_count': 1731,        u'key': u'1002',        u'totalCount': {u'value': 32309.0}},
17       {u'doc_count': 1695,        u'key': u'2006',        u'totalCount': {u'value': 33299.0}},
18       {u'doc_count': 1649,        u'key': u'2004',        u'totalCount': {u'value': 28429.0}},
19       {u'doc_count': 1423, u'key': u'13', u'totalCount': {u'value': 30672.0}},
20       {u'doc_count': 1340,        u'key': u'2008',        u'totalCount': {u'value': 45051.0}},
21       {u'doc_count': 1308, u'key': u'3', u'totalCount': {u'value': 32076.0}}],      u'doc_count_error_upper_bound': 0,      u'sum_other_doc_count': 12296}}],   u'doc_count_error_upper_bound': 0,   u'sum_other_doc_count': 8007376}}, u'hits': {u'hits': [], u'max_score': 0.0, u'total': 8385335}, u'timed_out': False, u'took': 2235}

查询只花了2.2秒，而之前在mongodb上花了21.4秒。在6个shard的index上跑同样的查询，只需花0.6秒。

一个时间段打包成一个doc

和之前 MongoDB 的 _._._._.v 的结构一样，数据按照维度嵌套存放在内部的子文档里。

表结构如下

1mappings = {    'testdata': {        '_source': {'enabled': False},        '_all': {'enabled': False},        'properties': {            'max_timestamp': {                'type': 'date',                'index': 'not_analyzed',                'store': False,                'dynamic': 'strict',                'doc_values': False,                'fielddata': {                    'format': 'disabled'
2                }
3            },            'min_timestamp': {                'type': 'date',                'index': 'not_analyzed',                'store': False,                'dynamic': 'strict',                'doc_values': False,                'fielddata': {                    'format': 'disabled'
4                }
5            },            'count': {                'type': 'integer',                'index': 'no',                'store': False,                'dynamic': 'strict',                'doc_values': False,                'fielddata': {                    'format': 'disabled'
6                }
7            },            'sum_totalCount': {                'type': 'integer',                'index': 'no',                'store': False,                'dynamic': 'strict',                'doc_values': False,                'fielddata': {                    'format': 'disabled'
8                }
9            },            'sum_dProcessTime': {                'type': 'integer',                'index': 'no',                'store': False,                'dynamic': 'strict',                'doc_values': False,                'fielddata': {                    'format': 'disabled'
10                }
11            },            '_': { # timestamp
12                'type': 'nested',                'properties': {                    'd': {'type': 'date', 'index': 'not_analyzed', 'store': False, 'fielddata': {'format': 'fst'}},                    'c': {'type': 'integer', 'index': 'not_analyzed', 'store': False, 'fielddata': {'format': 'fst'}},                    '0': {'type': 'integer', 'index': 'not_analyzed', 'store': False, 'fielddata': {'format': 'fst'}},                    '1': {'type': 'integer', 'index': 'not_analyzed', 'store': False, 'fielddata': {'format': 'fst'}},                    '_': { # vAppid
13                        'type': 'nested',                        'properties': {                            'd': {'type': 'string', 'index': 'not_analyzed', 'store': False, 'fielddata': {'format': 'fst'}},                            '_': { # iResult
14                                'type': 'nested',                                'properties': {                                    'd': {'type': 'string', 'index': 'not_analyzed', 'store': False, 'fielddata': {'format': 'fst'}},                                    '_': { # vCmdid
15                                        'type': 'nested',                                        'properties': {                                            'd': {'type': 'string', 'index': 'not_analyzed', 'store': False, 'fielddata': {'format': 'fst'}},                                            'v': { # values
16                                                'type': 'nested',                                                'properties': {                                                    '0': {'type': 'integer', 'index': 'not_analyzed', 'store': False, 'fielddata': {'format': 'fst'}},                                                    '1': {'type': 'integer', 'index': 'not_analyzed', 'store': False, 'fielddata': {'format': 'fst'}}
17                                                }
18                                            }
19                                        }
20                                    }
21                                }
22                            }
23                        }
24                    }
25                }
26            }
27        }
28    }
29}

表结构的要点是一对nested的嵌套文档。nested的成员必须打开doc_values或者index中的一项，否则数据不会被保存。因为doc_values更占空间，所以我们选择了不存doc values。

在 MongoDB 里的数据

1{    "sharded" : false,    "primary" : "shard2_RS",    "ns" : "wentao_test.sparse_precomputed_no_appid",    "count" : 39,    "size" : 2.68435e+08,    "avgObjSize" : 6.88294e+06,    "storageSize" : 2.75997e+08,    "numExtents" : 3,    "nindexes" : 1,    "lastExtentSize" : 1.58548e+08,    "paddingFactor" : 1.0000000000000000,    "systemFlags" : 1,    "userFlags" : 1,    "totalIndexSize" : 8176,    "indexSizes" : {        "_id_" : 8176
2    },    "ok" : 1.0000000000000000,    "$gleStats" : {        "lastOpTime" : Timestamp(1429187664, 3),        "electionId" : ObjectId("54c9f324adaa0bd054140fda")
3    }
4}

只有39个文档，尺寸是270M。数据导入到es之后

size: 74.6Mi (74.6Mi)docs: 9,355,029 (9,355,029)

文档数变成了935万个，因为子文档在es里也算成文档的，尺寸只有74M。查询条件如下

1q = {'aggs': {    'expanded_timestamp': {        'nested' : {            'path': '_'
2        },        'aggs': {            'grouped_timestamp': {                'terms': {                    'field':  '_.d',                    'size': 0
3                },                'aggs': {                    'totalCount': {                        'sum': {                            'field': '_.0'
4                        }
5                    }
6                }
7            }
8        }
9    }
10}
11}
12res = es.search(index="wentao-test4", doc_type='testdata', body=q, search_type='count')

注意 _.0 是预先计算好的同周期的 totalCount sum。嵌套的维度字段排序是 timestmap => vAppid => iResult => vCmdid => values (0 as toalCount, 1 as dProcessTime)。

1{u'_shards': {u'failed': 0, u'successful': 1, u'total': 1}, u'aggregations': {u'expanded_timestamp': {u'doc_count': 743,   u'grouped_timestamp': {u'buckets': [{u'doc_count': 8,      u'key': 1428790140000,      u'key_as_string': u'2015-04-11T22:09:00.000Z',      u'totalCount': {u'value': 972810.0}},
2     ... // ignore
3     {u'doc_count': 1,      u'key': 1428793140000,      u'key_as_string': u'2015-04-11T22:59:00.000Z',      u'totalCount': {u'value': 83009.0}}],    u'doc_count_error_upper_bound': 0,    u'sum_other_doc_count': 0}}}, u'hits': {u'hits': [], u'max_score': 0.0, u'total': 39}, u'timed_out': False, u'took': 56}

查询只花了0.056秒。使用预先计算的值并不公平。使用原始的值计算也是可以做到的：

1q = {    'aggs': {        'per_id': {            'terms': {                'field': '_uid'
2            },            'aggs': {                'expanded_timestamp': {                    'nested' : {                        'path': '_'
3                    },                    'aggs': {                        'grouped_timestamp': {                            'terms': {                                'field':  '_.d'
4                            },                            'aggs': {                                'expanded_vAppid': {                                    'nested' : {                                        'path': '_._._._.v'
5                                    },                                    'aggs': {                                        'totalCount': {                                            'sum' : {                                                'field': '_._._._.v.0'
6                                            },
7                                        }
8                                    }
9                                }
10                            }
11                        }
12                    }
13                }
14            }
15        }
16    },
17}

这里使用了多级展开，最后对 _._._._.v.0 求和。计算的结果和 _.0 求和是一样的。花的时间是0.548秒。

然后再来测一下按vAppid过滤，同时按时间和vCmdid两个维度聚合的查询。这个写起来有一些变态：

1q = {'aggs': {    'expanded_timestamp': {        'nested' : {            'path': '_'
2        },        'aggs': {            'grouped_timestamp': {                'terms': {                    'field':  '_.d',                    'size': 0
3                },                'aggs': {                    'expanded_to_vAppid': {                        'nested' : {                            'path': '_._'
4                        },                        'aggs': {                            'vAppid_not_empty': {                                'filter': {                                    'bool': {                                        'must_not': {                                            'term': {                                                '_._.d': ''
5                                            }
6                                        }
7                                    }
8                                },                                'aggs': {                                    'expanded_to_vCmdid': {                                        'nested' : {                                            'path': '_._._._'
9                                        },                                        'aggs': {                                            'ts_and_vCmdid': {                                                'terms': {'field': '_._._._.d', 'size': 0}, # _._._._.d is vCmdid
10                                                'aggs': {                                                    'expanded_to_values': {                                                        'nested' : {                                                            'path': '_._._._.v'
11                                                        },                                                        'aggs': {                                                            'totalCount': {                                                                'sum' : {                                                                    'field': '_._._._.v.0'
12                                                                },
13                                                            }
14                                                        }
15                                                    }
16                                                }
17                                            }
18                                        }
19                                    }
20                                }
21                            }
22                        }
23                    }
24                }
25            }
26        }
27    }
28}
29}

查询的速度是3.2秒。比原始格式保存的方式查起来要慢。但是实际情况下，预先计算的值是更可能被使用的，这种需要拆开原始的value的情况很少。

总结

ElasticSearch 就像闪电一样快。

1. 原始格式保存，占用 198M（mongodb是3G），查询1秒（mongodb是9秒）

2. 打包格式保存，占用 74M（mongodb是270M），查询0.54秒（mongodb是7.1秒）

3. 打包格式在原始值要完全展开的时候稍微比原始格式要慢，但是打包可以很方便的存储预聚合的值，那么大部分时候读取甚至是0.05秒这个级别的。

如果我们可以用74M，存储880万个点。那么有2T硬盘，可以存多少数据呢？很多很多……不但可以存进去读出来，更重要的是es还可以帮我们在服务器端完成按需聚合，从不同维度快速展示数据。