Nano Hash - криптовалюты, майнинг, программирование

Фильтр запросов SQL Server с порядком работает медленно

Я боролся с этим некоторое время.

У меня есть база данных с тремя таблицами (каждая из которых содержит миллионы записей) следующим образом (для простоты удалены некоторые столбцы):

1.[Entity]
    [Id]                UNIQUEIDENTIFIER PK,
    [EntityLevel_Id]    UNIQUEIDENTIFIER NOT NULL FK [EntityLevel] ([Id])
2.[EntityData]
    [Id]                UNIQUEIDENTIFIER PK,
    [Entity_Id]         UNIQUEIDENTIFIER NOT NULL FK [Entity] ([Id]),
    [DataLanguage_Id]   UNIQUEIDENTIFIER NOT NULL FK [Language] ([Id]),
    [Code]              NVARCHAR (250) NOT NULL
3.[EntityLevel]
    [Id]                UNIQUEIDENTIFIER PK,
    [Sort]              INT NOT NULL

Существуют индексы следующим образом

[IX_Entity_EntityLevelId] ON [Entity] ([EntityLevel_Id])
[IX_EntityData_EntityId] ON [EntityData] ([Entity_Id])
[IX_EntityData_DataLanguageId_Code] ON [EntityData] ([DataLanguage_Id], [Code])
[IX_EntityLevel_Sort] ON [EntityLevel] ([Sort])

Чтобы исключить вероятность того, что медлительность связана с выбранными столбцами, я выбираю только фиксированное значение.

Следующий запрос выполняется очень быстро (менее 1 секунды):

SELECT TOP 20
    1
FROM
    [Entity]
    INNER JOIN [EntityData] ON [Entity].[Id] = [EntityData].[Entity_Id]
    INNER JOIN [EntityLevel] ON [Entity].[EntityLevel_Id] = [EntityLevel].[Id]
WHERE
    [EntityData].[DataLanguage_Id] = 'B6930015-F177-4ED3-97B0-AAEF401F9265'

Следующий запрос также выполняется очень быстро:

SELECT TOP 20
    1
FROM
    [Entity]
    INNER JOIN [EntityData] ON [Entity].[Id] = [EntityData].[Entity_Id]
    INNER JOIN [EntityLevel] ON [Entity].[EntityLevel_Id] = [EntityLevel].[Id]
WHERE
    [EntityData].[DataLanguage_Id] = 'B6930015-F177-4ED3-97B0-AAEF401F9265'
ORDER BY
    [EntityData].[Code] ASC

И следующее также работает быстро:

SELECT TOP 20
    1
FROM
    [Entity]
    INNER JOIN [EntityData] ON [Entity].[Id] = [EntityData].[Entity_Id]
    INNER JOIN [EntityLevel] ON [Entity].[EntityLevel_Id] = [EntityLevel].[Id]
WHERE
    [EntityLevel].[Sort] = 1

НО, следующий запрос выполняется ОЧЕНЬ МЕДЛЕННО (примерно 10 секунд):

SELECT TOP 20
    1
FROM
    [Entity]
    INNER JOIN [EntityData] ON [Entity].[Id] = [EntityData].[Entity_Id]
    INNER JOIN [EntityLevel] ON [Entity].[EntityLevel_Id] = [EntityLevel].[Id]
WHERE
    [EntityData].[DataLanguage_Id] = 'B6930015-F177-4ED3-97B0-AAEF401F9265'
    AND
    [EntityLevel].[Sort] = 1
ORDER BY
    [EntityData].[Code]

Я не знаю причину, и я не нахожу способа применить дополнительные индексы, чтобы запрос выполнялся быстрее.

Любая помощь приветствуется!

Изменить: следующие запросы также выполняются быстро:

SELECT TOP 20
    1
FROM
    [Entity]
    INNER JOIN [EntityData] ON [Entity].[Id] = [EntityData].[Entity_Id]
    INNER JOIN [EntityLevel] ON [Entity].[EntityLevel_Id] = [EntityLevel].[Id]
WHERE
    [EntityData].[DataLanguage_Id] = 'B6930015-F177-4ED3-97B0-AAEF401F9265'
    AND
    [EntityLevel].[Sort] = 1

А также

SELECT TOP 20
    1
FROM
    [Entity]
    INNER JOIN [EntityData] ON [Entity].[Id] = [EntityData].[Entity_Id]
    INNER JOIN [EntityLevel] ON [Entity].[EntityLevel_Id] = [EntityLevel].[Id]
WHERE
    [EntityLevel].[Sort] = 1
ORDER BY
    [EntityData].[Code]

Проблема появляется только с заказом и двумя фильтрами

sql sql-server indexing query-performance
29.03.2020

  • sort=1 это обычный фильтр? для этого будет полезен отфильтрованный индекс. Просто обратите внимание, что вы работаете с базой данных EAV, которая хорошо известна своими проблемами производительности и масштабируемости. 29.03.2020
  • sort = 1 не является распространенным фильтром, обычно фильтруется по сортировке, но значение не является распространенным, чего мне не хватает, чтобы понять, что это просто обычное соединение, и запрос выполняется очень быстро с несколькими условиями, но замедляется с порядком и два фильтра 29.03.2020
  • Он использует другой план запроса. Когда вы наблюдаете за ними и пытаетесь понять, почему они это делают, вы часто можете это исправить. Довольно часто простое обновление статистики решает проблему. Иногда проблема заключается в прослушивании параметров 29.03.2020
  • ОК, просто перечитываю, все сузилось до order by code? Код не является ведущим столбцом в любом индексе, поэтому я предполагаю, что проблема заключается в порядке (всегда дорогостоящая операция). Кластерный индекс обычно исправляет проблемы с порядком по производительности, но вы получаете только одну. 29.03.2020
  • Я снова должен прокомментировать дизайн EAV и guid pk. Это кажется фантастической идеей для разработчика, но не для администратора баз данных. 29.03.2020
  • Я обновил статистику, и результат тот же, отредактирую вопрос и включу фактический план выполнения. 29.03.2020
  • Я понимаю проблему EAV, однако этот конкретный запрос является обычным соединением, точно так же, если таблица [EntityData] является чем-то другим (например, [Person]), и я могу попытаться добавить индекс только по коду и посмотреть результат 29.03.2020

Ответы:


1

Попробуйте индексы на

  1. [Entity] ([Id], [EntityLevel_Id]),
  2. [EntityData] ([DataLanguage_Id], [Entity_id], [Code]) и
  3. [EntityLevel] ([Sort], [Id]).

Поэкспериментируйте с порядком столбцов. Для 2. и 3. Я предположил, что [EntityData].[DataLanguage_Id] = 'B6930015-F177-4ED3-97B0-AAEF401F9265' и [EntityLevel].[Sort] = 1 в предложении WHERE фильтруют больше, чем объединение. Но я не знаю данных, и это предположение может быть неверным.

29.03.2020
  • Я могу ошибаться, но не могли бы вы объяснить, почему мне нужен индекс № 1, поскольку он уже должен существовать, потому что [Id] — это PK и кластеризованный индекс?, а также индекс № 3, зачем мне включать столбец PK в индекс? разве он уже не включен во все индексы? Предположим, запрос выполняется быстро без порядка 29.03.2020
  • @AhmedMSedeek: я не мог (и до сих пор не могу) найти эту информацию в вашем посте. Но в любом случае, я не знаю нужно ли вам это, вы должны проверить это сами. Я не могу этого сделать, потому что у меня нет данных и всего остального. Однако он думает, что это может помочь объединению лучше, чем кластерный индекс, поскольку он также сортируется по [EntityLevel_Id]. И тогда есть вероятность, что при изменении порядка, как я уже упоминал, ([EntityLevel_Id], [Id]) будет лучшим вариантом. Кластерный индекс вообще не покрывает этого. 29.03.2020

    • 2

    Ваш вопрос касается этого запроса:

    SELECT TOP 20 1
FROM [Entity] JOIN
     [EntityData]
     ON [Entity].[Id] = [EntityData].[Entity_Id] JOIN
     [EntityLevel]
     ON [Entity].[EntityLevel_Id] = [EntityLevel].[Id]
WHERE [EntityData].[DataLanguage_Id] = 'B6930015-F177-4ED3-97B0-AAEF401F9265' AND
      [EntityLevel].[Sort] = 1
ORDER BY [EntityData].[Code];

    Я думаю, проблема в том, что SQL Server не может использовать индекс для сортировки. Вы можете обойти это, используя EXISTS:

    SELECT TOP 20 1
FROM Entity e JOIN
     EntityData ed
     ON e.Id = ed.Entity_Id 
WHERE ed.DataLanguage_Id = 'B6930015-F177-4ED3-97B0-AAEF401F9265' AND          
      EXISTS (SELECT 1
              FROM EntityLevel el
              WHERE e.EntityLevel_Id = el.Id AND
                    el.Sort = 1
             )
ORDER BY ed.Code;

    Для этой версии вам нужен индекс EntityLevel(ID, Sort).

    Это может позволить механизму SQL использовать индекс для сортировки (как это происходит в других случаях). Конечно, вы не можете выбрать какие-либо столбцы из EntityLevel, но ваши примеры запросов в любом случае этого не делают.

    29.03.2020

    3

    Кому интересно, вот решение проблемы:

    Оказалось, что при таком большом количестве строк на поиск ключей и сопоставление хэшей из-за разных индексов уходит время, поэтому решением было добавить следующий индекс, чтобы разрешить использование одного индекса в таблице для всех фильтров (и упорядочить по) значениям:

    CREATE NONCLUSTERED INDEX [IX_EntityData_EntityId_DataLanguageId_IncCode] ON [EntityData] ([Entity_Id], [DataLanguage_Id]) INCLUDE ([Code])

    Но поскольку в некоторых ситуациях я также могу фильтровать по коду, а не только использовать порядок, поэтому я изменил индекс, чтобы он выглядел следующим образом:

    CREATE NONCLUSTERED INDEX [IX_EntityData_EntityId_DataLanguageId_Code] ON [EntityData] ([Entity_Id], [DataLanguage_Id], [Code])
    31.03.2020
    Новые материалы
    Кластеризация: более глубокий взгляд
    Кластеризация — это метод обучения без учителя, в котором мы пытаемся найти группы в наборе данных на основе некоторых известных или неизвестных свойств, которые могут существовать. Независимо от..
    Как написать эффективное резюме
    Предложения по дизайну и макету, чтобы представить себя профессионально Вам не позвонили на собеседование после того, как вы несколько раз подали заявку на работу своей мечты? У вас может..
    Частный метод Python: улучшение инкапсуляции и безопасности
    Введение Python — универсальный и мощный язык программирования, известный своей простотой и удобством использования. Одной из ключевых особенностей, отличающих Python от других языков, является..
    Как я автоматизирую тестирование с помощью Jest
    Шутка для победы, когда дело касается автоматизации тестирования Одной очень важной частью разработки программного обеспечения является автоматизация тестирования, поскольку она создает..
    Работа с векторными символическими архитектурами, часть 4 (искусственный интеллект)
    Hyperseed: неконтролируемое обучение с векторными символическими архитектурами (arXiv) Автор: Евгений Осипов , Сачин Кахавала , Диланта Хапутантри , Тимал Кемпития , Дасвин Де Сильва ,..
    Понимание расстояния Вассерштейна: мощная метрика в машинном обучении
    В обширной области машинного обучения часто возникает необходимость сравнивать и измерять различия между распределениями вероятностей. Традиционные метрики расстояния, такие как евклидово..
    Обеспечение масштабируемости LLM: облачный анализ с помощью AWS Fargate и Copilot
    В динамичной области искусственного интеллекта все большее распространение получают модели больших языков (LLM). Они жизненно важны для различных приложений, таких как интеллектуальные..