Big data, оптимизация запросов

#sql_server #big_data

Есть следующая простая структура данных

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. Буду рад любому совету.
    

Ответы

Ответ 1

Пара мыслей (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, раз уж инструментов
всего около десятка.