Поделиться через

Эффективное выполнение запросов

Эффективное выполнение запросов — это обширная тема, которая охватывает такие разнообразные аспекты, как индексы, стратегии загрузки связанных сущностей и многое другое. В этом разделе рассматриваются некоторые распространенные подходы для ускорения выполнения запросов и типичные подводные камни, с которыми сталкиваются пользователи.

Правильное использование индексов

Основной фактор, определяющий быстроту выполнения запроса, — это правильное использование индексов, где это необходимо. Базы данных обычно используются для хранения больших объемов данных, и запросы, которые проходят по всей таблице, часто являются источниками серьезных проблем с производительностью. Проблемы с индексированием нелегко обнаружить, так как не сразу понятно, будет ли данный запрос использовать индекс. Рассмотрим пример.

// Matches on start, so uses an index (on SQL Server)
var posts1 = await context.Posts.Where(p => p.Title.StartsWith("A")).ToListAsync();
// Matches on end, so does not use the index
var posts2 = await context.Posts.Where(p => p.Title.EndsWith("A")).ToListAsync();

Хороший способ обнаружить проблемы с индексированием — сначала определить медленный запрос, а затем изучить план запроса с помощью избранного средства базы данных; Дополнительные сведения о том, как это сделать, см. на странице диагностики производительности . План запроса показывает, проходит ли запрос всю таблицу или использует индекс.

Как правило, нет никаких специальных знаний EF для использования индексов или диагностики проблем с производительностью, связанных с ними; Общие знания базы данных, связанные с индексами, относятся к приложениям EF так же, как к приложениям, не использующим EF. Ниже перечислены некоторые общие рекомендации, которые следует учитывать при использовании индексов:

  • Хотя индексы ускоряют запросы, они также замедляют обновления, так как они должны храниться up-to-date. Избегайте определения индексов, которые не нужны, и рекомендуется использовать фильтры индексов , чтобы ограничить индекс подмножеством строк, тем самым уменьшая эти затраты.
  • Составные индексы могут ускорить запросы, которые фильтруют по нескольким столбцам, но они также могут ускорить запросы, которые не фильтруют по всем столбцам индекса в зависимости от порядка. Например, индекс столбцов A и B ускоряет фильтрацию запросов по A и B, а также запросы фильтрации только по A, но не ускоряет фильтрацию только по B.
  • Если запрос фильтруется по выражению по столбцу (например price / 2, не может использоваться простой индекс). Однако вы можете определить хранимый рассчитанный столбец для выражения и создать индекс для него. Некоторые базы данных также поддерживают индексы выражений, которые можно использовать непосредственно для ускорения фильтрации запросов по любому выражению.
  • Различные базы данных позволяют настраивать индексы различными способами, и во многих случаях поставщики EF Core предоставляют их через API Fluent. Например, поставщик SQL Server позволяет настроить кластеризованный индекс или задать его коэффициент заполнения. Дополнительные сведения см. в документации поставщика.

Проецируйте только необходимые свойства

EF Core позволяет легко запрашивать экземпляры сущностей, а затем использовать эти экземпляры в коде. Однако запросы экземпляров сущностей часто могут извлекать больше данных, чем необходимо из базы данных. Рассмотрим следующее:

await foreach (var blog in context.Blogs.AsAsyncEnumerable())
{
    Console.WriteLine("Blog: " + blog.Url);
}

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

SELECT [b].[BlogId], [b].[CreationDate], [b].[Name], [b].[Rating], [b].[Url]
FROM [Blogs] AS [b]

Это можно оптимизировать с помощью Select, чтобы указать EF, какие именно столбцы следует проецировать.

await foreach (var blogName in context.Blogs.Select(b => b.Url).AsAsyncEnumerable())
{
    Console.WriteLine("Blog: " + blogName);
}

Результирующий SQL извлекает только необходимые столбцы:

SELECT [b].[Url]
FROM [Blogs] AS [b]

Если вам нужно проецировать несколько столбцов, выполните проект в анонимный тип C# с нужными свойствами.

Обратите внимание, что этот метод очень полезен для запросов, доступных только для чтения, но все сложнее, если вам нужно обновить извлекаемые блоги, так как отслеживание изменений EF работает только с экземплярами сущностей. Можно выполнять обновления без загрузки целых сущностей, подключив измененный экземпляр "Blog" и указав EF, какие свойства изменились, но это более сложный метод, который может не оправдать усилий.

Ограничение размера набора результатов

По умолчанию запрос возвращает все строки, соответствующие его фильтрам:

var blogsAll = await context.Posts
    .Where(p => p.Title.StartsWith("A"))
    .ToListAsync();

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

В результате обычно стоит думать об ограничении количества результатов:

var blogs25 = await context.Posts
    .Where(p => p.Title.StartsWith("A"))
    .Take(25)
    .ToListAsync();

Как минимум, в пользовательском интерфейсе может отображаться сообщение о том, что в базе данных могут существовать больше строк (и разрешить получение их другим способом). Полное решение реализует разбиение на страницы, где пользовательский интерфейс отображает только определенное количество строк за раз и позволяет пользователям переходить на следующую страницу по мере необходимости; Дополнительные сведения о том, как эффективно реализовать эту возможность, см. в следующем разделе.

Эффективное разбиение на страницы

Пагинация означает получение результатов по страницам, а не все одновременно, и обычно это делается для больших наборов данных, где отображается интерфейс, позволяющий пользователю переходить к следующей или предыдущей странице результатов. Распространенный способ реализации разбиения на страницы с базами данных заключается в использовании Skip операторов и Take операторов (OFFSET и LIMIT в SQL), в то время как это интуитивно понятная реализация, это также довольно неэффективно. Для разбиения на страницы, которая позволяет перемещать одну страницу за раз (в отличие от перехода на произвольные страницы), рекомендуется использовать вместо этого использование разбиения на страницы набора ключей .

Дополнительные сведения см. на странице документации по разбиению на страницы.

В реляционных базах данных все связанные сущности загружаются путем внедрения JOIN в один запрос.

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url], [p].[PostId], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [Blogs] AS [b]
LEFT JOIN [Post] AS [p] ON [b].[BlogId] = [p].[BlogId]
ORDER BY [b].[BlogId], [p].[PostId]

Если в типичном блоге несколько связанных записей, строки для этих записей будут дублировать сведения блога. Это дублирование приводит к так называемому «декартовскому взрыву». По мере загрузки нескольких связей "один ко многим" объем повторяющихся данных может увеличиваться и отрицательно влиять на производительность приложения.

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

Замечание

Текущая реализация разделенных запросов выполняет круговой обмен данными для каждого запроса. Мы планируем улучшить это в будущем и выполнить все запросы в одном цикле.

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

При работе с связанными сущностями мы обычно заранее знаем, что нужно загрузить: типичный пример будет загружать определенный набор блогов вместе со всеми своими записями. В этих сценариях всегда лучше использовать жадную загрузку, так, чтобы EF мог получить все необходимые данные за один запрос. Функция фильтрации включений также позволяет ограничить загрузку связанных сущностей, которые вы хотите загрузить, сохраняя оперативность процесса и обеспечивая выполнение в одной операции.

using (var context = new BloggingContext())
{
    var filteredBlogs = await context.Blogs
        .Include(
            blog => blog.Posts
                .Where(post => post.BlogId == 1)
                .OrderByDescending(post => post.Title)
                .Take(5))
        .ToListAsync();
}

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

Остерегайтесь отложенной загрузки

Отложенная загрузка часто представляется очень полезным способом записи логики базы данных, так как EF Core автоматически загружает связанные сущности из базы данных по мере доступа к коду. Это позволяет избежать загрузки связанных сущностей, которые не нужны (например, явная загрузка), и, казалось бы, освобождает программиста от необходимости иметь дело с связанными сущностями в целом. Однако отложенная загрузка особенно подвержена созданию ненужных дополнительных циклов, которые могут замедлить приложение.

Рассмотрим следующее:

foreach (var blog in await context.Blogs.ToListAsync())
{
    foreach (var post in blog.Posts)
    {
        Console.WriteLine($"Blog {blog.Url}, Post: {post.Title}");
    }
}

Это, казалось бы, невинный фрагмент кода итерирует все блоги и их записи, распечатав их. Включение ведения журнала инструкций EF Core показывает следующее:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [b].[BlogId], [b].[Rating], [b].[Url]
      FROM [Blogs] AS [b]
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (5ms) [Parameters=[@__p_0='1'], CommandType='Text', CommandTimeout='30']
      SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
      FROM [Post] AS [p]
      WHERE [p].[BlogId] = @__p_0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__p_0='2'], CommandType='Text', CommandTimeout='30']
      SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
      FROM [Post] AS [p]
      WHERE [p].[BlogId] = @__p_0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__p_0='3'], CommandType='Text', CommandTimeout='30']
      SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
      FROM [Post] AS [p]
      WHERE [p].[BlogId] = @__p_0

... and so on

В чем причина? Почему все эти запросы отправляются для простых циклов выше? При отложенной загрузке публикации блога загружаются (лениво) только при доступе к свойству Posts; в результате каждая итерация во внутреннем цикле foreach активирует дополнительный запрос к базе данных в рамках своего сеанса. В результате после загрузки всех блогов после первоначального запроса у нас будет еще один запрос для каждого блога, загрузка всех его записей; иногда это называется проблемой N+1 , и это может привести к очень значительным проблемам с производительностью.

Предположим, если нам понадобятся все записи блогов, логично использовать жадную загрузку. Мы можем использовать оператор Include для выполнения загрузки, но так как нам нужны только URL-адреса блогов (и мы должны загружать только необходимые данные). Поэтому вместо этого мы будем использовать проекцию:

await foreach (var blog in context.Blogs.Select(b => new { b.Url, b.Posts }).AsAsyncEnumerable())
{
    foreach (var post in blog.Posts)
    {
        Console.WriteLine($"Blog {blog.Url}, Post: {post.Title}");
    }
}

Это позволит EF Core получить все блоги , а также их записи в одном запросе. В некоторых случаях это также может быть полезно, чтобы избежать декартовых последствий взрыва с помощью разделенных запросов.

Предупреждение

Поскольку отложенная загрузка чрезвычайно упрощает непреднамеренное возникновение проблемы N+1, рекомендуется избегать ее использования. Охотная или явная загрузка четко указывает в исходном коде момент обращения к базе данных.

Буферизация и потоковая передача

Буферизация относится к загрузке всех результатов запроса в память, в то время как потоковая передача означает, что EF передает приложению один результат каждый раз, никогда не содержащий весь набор результатов в памяти. В принципе требования к памяти потокового запроса фиксируются. Они совпадают с тем, возвращает ли запрос 1 строку или 1000; С другой стороны, для буферизации требуется больше памяти, чем больше строк возвращаются. Для запросов, которые приводят к большим результатам, это может быть важным фактором производительности.

Зависит ли буфер запроса или потоки от способа его вычисления:

// ToList and ToArray cause the entire resultset to be buffered:
var blogsList = await context.Posts.Where(p => p.Title.StartsWith("A")).ToListAsync();
var blogsArray = await context.Posts.Where(p => p.Title.StartsWith("A")).ToArrayAsync();

// Foreach streams, processing one row at a time:
await foreach (var blog in context.Posts.Where(p => p.Title.StartsWith("A")).AsAsyncEnumerable())
{
    // ...
}

// AsAsyncEnumerable also streams, allowing you to execute LINQ operators on the client-side:
var doubleFilteredBlogs = context.Posts
    .Where(p => p.Title.StartsWith("A")) // Translated to SQL and executed in the database
    .AsAsyncEnumerable()
    .Where(p => SomeDotNetMethod(p)); // Executed at the client on all database results

Если ваши запросы возвращают всего несколько результатов, вероятно, вам не нужно беспокоиться об этом. Однако если запрос может возвращать большое количество строк, стоит думать о потоковой передаче вместо буферизации.

Замечание

Избегайте использования ToList или ToArray, если вы намерены применить другой оператор LINQ к результату, это будет без необходимости буферизовать все результаты в память. Вместо этого используйте AsEnumerable.

Внутренняя буферизация по EF

В некоторых ситуациях EF самостоятельно буферизирует набор результатов независимо от того, как вы оцениваете запрос. Два случая, когда это происходит:

  • Когда используется стратегия повторного выполнения. Это делается, чтобы убедиться, что те же результаты возвращаются, если запрос будет получен позже.
  • При использовании разделенного запроса результаты всех, кроме последнего запроса, буфериируются, если в SQL Server не включена функция MARS (несколько активных результирующих наборов). Это связано с тем, что обычно невозможно одновременно использовать несколько наборов результатов запросов.

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

Отслеживание, безотслеживание и разрешение идентификации

Рекомендуется прочитать посвященную страницу об отслеживании и его отсутствии прежде чем продолжить этот раздел.

EF отслеживает экземпляры сущностей по умолчанию, так что изменения в них обнаруживаются и сохраняются при вызове SaveChanges. Другой эффект отслеживания запросов заключается в том, что EF обнаруживает, был ли экземпляр уже загружен для ваших данных, и автоматически возвращает уже отслеживаемый экземпляр, вместо создания нового; это называется разрешение идентичности. С точки зрения производительности отслеживание изменений означает следующее:

  • EF внутренне поддерживает словарь отслеживаемых экземпляров. При загрузке новых данных EF проверяет словарь, чтобы узнать, отслеживается ли экземпляр с таким ключом сущности (разрешение идентичности). Поддержка и поиск в словаре занимают некоторое время при загрузке результатов запроса.
  • Прежде чем передавать загруженный экземпляр приложению, EF создает моментальный снимок этого экземпляра и сохраняет его внутренне. Когда вызывается SaveChanges, экземпляр приложения сравнивается с моментальным снимком, чтобы выявить изменения, которые должны быть сохранены. Моментальный снимок занимает больше памяти, и сам процесс создания моментальных снимков занимает время; Иногда можно указать другое, возможно, более эффективное поведение моментального снимка с помощью сравнения значений или использовать прокси-серверы отслеживания изменений для обхода процесса создания моментальных снимков в целом (хотя это поставляется с собственным набором недостатков).

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

Чтобы проиллюстрировать, предположим, что мы загружаем большое количество записей из базы данных, а также блог, на который ссылается каждая запись. Если 100 записей ссылаются на один блог, запрос отслеживания обнаруживает это через разрешение идентификаторов, и все экземпляры поста будут ссылаться на единый, избавленный от дубликатов экземпляр блога. Запрос без отслеживания, напротив, дублирует один и тот же блог 100 раз, и код приложения должен быть написан соответствующим образом.

Ниже приведены результаты для сравнения отслеживания и поведения без отслеживания запросов для загрузки 10 блогов с 20 записей каждый. Исходный код доступен здесь, вы можете использовать его в качестве основы для собственных измерений.

Метод NumBlogs Количество постов на блог Среднее Ошибка StdDev Медиана Коэффициент Коэффициенты 0-го поколения Поколение 1 Поколение 2 Выделено
АсТрекинг 10 20 1,414.7 нас 27.20 мы 45.44 мы 1405.5 нас 1.00 0.00 60,5469 13.6719 - 380.11 КБ
AsNoTracking 10 20 993.3 нас 24.04 мы 65.40 мы 966.2 нас 0.71 0.05 37.1094 6.8359 - 232.89 КБ

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

Использование запросов SQL

В некоторых случаях для запроса существует более оптимизированный SQL, который EF не создает. Это может произойти, когда конструкция SQL является расширением, характерным для вашей базы данных, которая не поддерживается, или просто потому, что EF еще не преобразуется в нее. В таких случаях написание SQL вручную может обеспечить существенное повышение производительности, и EF поддерживает несколько способов этого.

  • Используйте SQL-запросы непосредственно в запросе, например с помощью FromSqlRaw. EF даже позволяет создавать запросы LINQ поверх SQL, что позволяет выразить часть запроса с помощью SQL. Это хороший способ, когда SQL необходимо использовать только в одном запросе в базе кода.
  • Определите определяемую пользователем функцию (UDF), а затем вызовите ее из запросов. Обратите внимание, что EF позволяет функциям, определяемым пользователем (UDFs), возвращать полные наборы результатов — они называются табличными функциями (TVFs), а также позволяет сопоставлять DbSet с функцией, делая ее похожей на еще одну таблицу.
  • Определите представление базы данных и запрос из него в запросах. Обратите внимание, что в отличие от функций представления не могут принимать параметры.

Замечание

Необработанный SQL, как правило, следует использовать в крайнем случае, после того как EF не может создать нужный SQL, и когда производительность достаточно важна для данного запроса, чтобы оправдать его использование. Использование необработанного SQL приводит к значительным недостаткам обслуживания.

Асинхронное программирование

В качестве общего правила, чтобы приложение было масштабируемым, важно всегда использовать асинхронные API, а не синхронный (например SaveChangesAsync , а не SaveChanges). Синхронные API блокируют поток на время выполнения операций ввода-вывода с базой данных, увеличивая потребность в потоках и количестве переключений контекста потоков, которые должны происходить.

Дополнительные сведения см. на странице асинхронного программирования.

Предупреждение

Избегайте смешивания синхронного и асинхронного кода в одном приложении — очень легко непреднамеренно вызвать тонкие проблемы истощения пула потоков.

Предупреждение

Асинхронная реализация Microsoft.Data.SqlClient, к сожалению, имеет некоторые известные проблемы (например, #593, #601и другие). Если возникают непредвиденные проблемы с производительностью, попробуйте использовать выполнение команды синхронизации, особенно при работе с большим текстом или двоичными значениями.

Дополнительные ресурсы