Есть следующая простая структура данных
Id; fk_Security_Id; DateTime; Price
Строка хранит данные по инструменту(активу), дату и время, цену(котировку). Строк в БД на данный момент ~ 1 млрд. 200 млн. (10 инструментов с историей за прошлые 10 лет)
Задача - выборка данных по указанному fk_Security_Id и промежутку DateTime(например, июль 2000г.) за адекватный промежуток времени (в идеале меньше минуты).
Сначала, я использовал знакомый мне MSSQL и навесил в лоб clustered index на эти 2 поля. В результате поиск по этим 2 полям занимает в районе 35 минут и сожранные 6.5Gb RAM. Не совсем то, что конечно хотелось бы.
Какие варианты решения вижу пока я:
Не менять выбранную бд, а изменить саму структуру хранения
данных. Например разнести в разные таблицы данные по разным инструментам. В
этом случае конечно будут абсолютно идентичные таблицы с точки
зрения структуры, но можно будет выиграть некоторое время на поиске и
дальнейшее добавление новых инструментов не будет влиять на то самое время поиска. И тогда вместо композитного кластерного индекса, индекс
будет состоять из одного поля - datetime. Также возможно здесь
имеет смысл вместо поля datetime в качестве индекса брать некий
timestamp или преобразованный Id. Но не уверен что это даст
существенный прирост в поиске, хотя стоит попробовать думаю.
Использовать какую-нибудь более легковесную бд, например postgres (дружит с необходимым мне EF, что очень хотелось бы) + есть нативная поддержка Sphinx-а например.
Использовать какое-нибудь NoSql решение. С данными бд дел не имел, но допускаю,что в моем случае данные укладываются в простую структуру key-value. Правда, наверное те NoSql которые держат данные в RAM мне не подойдут потому что у меня просто столько памяти нету. Хотя, если я не ошибась есть и достаточно шустрые дисковые NoSql , Aerospike например. Но опять же поскольку я с ними не работал я не могу оценить насколько они дадут выигрыш по времени по сравнению с обыными реляционными бд.
База не распределенная, ресурсы машины - 8 потоков и 8Gb RAM. Буду рад любому совету.
Ответ
Пара мыслей (eсли вы всё же остановитесь на MSSQL).
На мой взгляд big-data подразумевает щепетильное отношение к структурам хранения данных и типам хранимых данных.
Сравните, к примеру, размеры различных типов данных для хранения дат и чисел:
declare
@dt datetime = getdate(),
@dt2 datetime2(0) = getdate(),
@sdt smalldatetime = getdate(),
@m money = 1.0,
@f float = 1.0,
@dec_15_5 decimal(15,5) = 1.0,
@r real = 1.0
select
[datetime] = datalength(@dt),
[datetime2(0)] = datalength(@dt2),
[smalldatetime] = datalength(@sdt),
[money] = datalength(@m),
[float] = datalength(@f),
[decimal(15,5)] = datalength(@dec_15_5),
[real] = datalength(@r)
datetime datetime2(0) smalldatetime money float decimal(15,5) real
--------- ------------- -------------- ------ ------ -------------- -----
8 6 4 8 8 5 4
Если тип столбца DateTime у вас datetime, рассмотрите возможность использования, например, типа smalldatetime (диапазон значений от 1900-01-01 до 2079-06-06 с точностью 1 минута). Если, тип стоблца Price, к примеру, float - рассмотрите возможность использования типов decimal (numeric) или real. Чем меньше размер строки данных, тем больше строк помещается в одну страницу памяти, соответственно легче оперировать ими в запросах. В таблицах с большим числом строк нелишним будет избегать NULL-able столбцов (это также сэкономит немного места). Правда следствием компактного хранения может быть некоторое неудобство в написании запросов, когда, например, при вычислении среднего для сохранения точности приходится делать кастинг в тип с большей точностью, а потом обратно. Да и сам кастинг несколько повысит стоимость запроса.
Ваша идея разнести инструменты по таблицам имеет рациональное зерно. Нужно ли их держать в одной таблице, и в самом ли деле нужен Id в таблице, если, к примеру, на неё нет ссылок - решать вам. Однако если разнести данные по таблицам вида
create table SomeInstrument
(
DateTime smalldatetime not NULL primary key,
Rate real not NULL
)
то общий объём хранимых данных явно уменьшится, т.к. не будет столбцов Id и fk_Security_Id.
Если всё же оставите всё в одной таблице, то fk_Security_Id (вместе с primary key таблицы, на которую он ссылается) имеет смысл перевести на тип tinyint, раз уж инструментов всего около десятка.
Комментариев нет:
Отправить комментарий