У меня есть база данных SQLite размером ~280 ГБ с финансовой информацией компании, к которой я выполняю различные запросы. Да, это SQLite, потому что он отлично работает и с ним проще работать. У меня тоже есть версия этой БД для MySQL, но я бы предпочел не использовать ее, если смогу.
Есть таблица ItemValues с миллиардом строк. Каждый столбец, показанный в двух таблицах ниже, имеет индекс, который создается во время задания сборки БД (ночью).
ItemTypeId — это внешний ключ, и таблица, на которую он ссылается, называется ItemTypes и выглядит следующим образом.
Я могу довольно легко найти компании с определенным Cost ИЛИ Revenue, и поиск занимает миллисекунды, чтобы вернуть результаты.
Вот SQL-скрипт
Проблема возникает, когда мне приходится искать компании, соответствующие определенным критериям Cost И Revenue. Хотя технически я могу найти все компании с определенным Cost, затем найти все компании с определенным Revenue, а затем объединить их, выбрав только подмножество компаний, которые соответствуют обоим моим критериям, этот подход возвращает массу данных, поскольку их миллионы и миллионы. строк, соответствующих Cost (Revenue не встречается в таком количестве строк ItemValues, в относительном выражении).
Какие варианты решения этой проблемы у меня могут быть? В настоящее время я ищу только по затратам и доходам и полагаю, что мог бы добавить эти два значения к каждому ItemValues, но поля, по которым мне нужно искать, могут вырасти. Мне интересно, нужно ли переосмыслить дизайн моей таблицы, или, возможно, мне следует изучить БД, ориентированные на столбцы, но я считаю, что сначала мне следует попытаться оптимизировать запросы к существующей структуре БД.
Содержимое SQL-скрипта:
CREATE TABLE ItemTypes (
Id INTEGER PRIMARY KEY,
ItemShortDescription TEXT
);
CREATE INDEX idx_ItemTypes_ItemShortDescription ON ItemTypes (ItemShortDescription);
INSERT INTO ItemTypes (Id, ItemShortDescription) VALUES (1, 'Cost');
INSERT INTO ItemTypes (Id, ItemShortDescription) VALUES (2, 'Revenue');
INSERT INTO ItemTypes (Id, ItemShortDescription) VALUES (3, 'SomeOtherFinancialMetric1');
INSERT INTO ItemTypes (Id, ItemShortDescription) VALUES (4, 'SomeOtherFinancialMetric2');
CREATE TABLE ItemValues (
CompanyId TEXT,
ItemTypeId INTEGER,
NumericValue INTEGER,
DateEpoch INTEGER
);
CREATE INDEX idx_ItemValues_CompanyId ON ItemValues (CompanyId);
CREATE INDEX idx_ItemValues_ItemTypeId ON ItemValues (ItemTypeId);
CREATE INDEX idx_ItemValues_NumericValue ON ItemValues (NumericValue);
CREATE INDEX idx_ItemValues_DateEpoch ON ItemValues (DateEpoch);
INSERT INTO ItemValues (CompanyId, ItemTypeId, NumericValue, DateEpoch) VALUES ('AB1234', 1, 100, 1569884400);
INSERT INTO ItemValues (CompanyId, ItemTypeId, NumericValue, DateEpoch) VALUES ('AB1234', 2, 200, 1569884400);
INSERT INTO ItemValues (CompanyId, ItemTypeId, NumericValue, DateEpoch) VALUES ('G17895', 7, 50, 1632956400);
WITH salesIdsCTE AS (
SELECT Id
FROM ItemTypes
WHERE ItemShortDescription = 'Cost' Or ItemShortDescription = 'Revenue'
),
filteredReportItems AS (
SELECT *
FROM ItemValues
WHERE ItemTypeId IN (SELECT Id FROM salesIdsCTE)
AND NumericValue > 5
)
SELECT *
FROM filteredReportItems
LIMIT 5;
🤔 А знаете ли вы, что...
SQL может выполнять рекурсивные запросы для работы с иерархическими данными.
Конечно, гораздо быстрее найти отдельные строки в таблице (например, все строки, соответствующие ItemTypeId = 1 and NumericValue > 5), чем выбрать два таких (возможно, огромных) набора данных и затем объединить их.
Вы говорите, что вам нужны строки, соответствующие критериям затрат и доходов, но ваш запрос ищет строки, соответствующие критериям затрат или доходов.
Одним из правильных запросов будет:
SELECT companyid FROM itemvalues WHERE itemtypeid = 1 AND numericvalue > 5
INTERSECT
SELECT companyid FROM itemvalues WHERE itemtypeid = 2 AND numericvalue > 5;
Наиболее подходящим индексом для этого будет
CREATE INDEX idx ON itemvalues(numericvalue, itemtypeid, companyid);
Этот индекс особенно полезен, если запрошенные значения очень велики и применимы только к небольшому количеству строк таблицы. Таким образом, СУБД могла сразу исключить большую часть строк и просмотреть только небольшую часть индекса. Саму таблицу читать не придется, поскольку вся информация доступна в индексе. (Это называется индексом покрытия).
Другой способ написать запрос:
SELECT companyid
FROM itemvalues
WHERE itemtypeid IN (1,2)
GROUP BY companyid
HAVING MIN(numericvalue) > 5;
где вам, возможно, придется изменить пункт HAVING. ЕСЛИ у компании может отсутствовать тип 1 или 2, вам придется добавить AND COUNT(*) = 2 или что-то подобное, чтобы гарантировать, что оба значения существуют и превышают 5. Если вы ищете разные значения, вам может потребоваться условное агрегирование, например. HAVING MIN(numericvalue) FILTER (WHERE itemtypeid = 1) > 5 AND MIN(numericvalue) FILTER (WHERE itemtypeid = 2) > 10.
Для этого запроса индекс должен начинаться с ItemTypeId, поскольку именно по нему мы сначала фильтруем строки. Однако, поскольку существует всего несколько типов, мы можем предположить, что на большую часть строк влияет itemtypeid IN (1,2). (Может быть, даже половина таблицы.) По этой причине этот индекс имеет смысл только тогда, когда он является покрывающим индексом, поэтому таблицу нельзя читать, как уже упоминалось:
CREATE INDEX idx ON itemvalues(itemtypeid, numericvalue, companyid);
Наконец: да, другой дизайн таблицы был бы более подходящим для такого рода запросов. При использовании отдельных столбцов для разных типов вам снова придется искать отдельные строки, соответствующие критериям:
CREATE TABLE company (
companyid INTEGER PRIMARY KEY,
companyname TEXT,
cost INTEGER,
cost_dateepoch INTEGER
revenue INTEGER,
revenue_dateepoch INTEGER
);
SELECT * FROM company WHERE cost > 5 AND revenue > 5;
Никакого индекса не требуется, так как последовательное чтение всей таблицы в любом случае является самым быстрым подходом. Но у вас не будет возможности добавлять новые показатели в любое время или даже позволять пользователям добавлять их для своих целей.