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

Статья
08/24/2023

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

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

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

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

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

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

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

Нужны только свойства проекта

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Разбивка на страницы ссылается на получение результатов в страницах, а не все одновременно; Обычно это делается для больших наборов результатов, где отображается пользовательский интерфейс, позволяющий пользователю перейти к следующей или предыдущей странице результатов. Распространенный способ реализации разбиения на страницы с базами данных заключается в использовании 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 = context.Blogs
        .Include(
            blog => blog.Posts
                .Where(post => post.BlogId == 1)
                .OrderByDescending(post => post.Title)
                .Take(5))
        .ToList();
}

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

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

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

В частности, необходимо принимать во внимание следующее:

foreach (var blog in context.Blogs.ToList())
{
    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

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

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

foreach (var blog in context.Blogs.Select(b => new { b.Url, b.Posts }).ToList())
{
    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 = context.Posts.Where(p => p.Title.StartsWith("A")).ToList();
var blogsArray = context.Posts.Where(p => p.Title.StartsWith("A")).ToArray();

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

// AsEnumerable 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
    .AsEnumerable()
    .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 записей ссылаются на один блог, запрос отслеживания обнаруживает это с помощью разрешения удостоверений, а все экземпляры Post будут ссылаться на один и тот же экземпляр блога, который будет повторяться. Запрос без отслеживания, напротив, дублирует один и тот же блог 100 раз, и код приложения должен быть написан соответствующим образом.

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

Метод	NumBlogs	NumPostsPerBlog	Среднее значение	Ошибка	StdDev	Медиана	Коэффициент	Коэффициенты	0-го поколения	Поколение 1	Поколение 2	Распределено
AsTracking	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 даже позволяет создавать sql с обычными запросами LINQ, что позволяет выразить только часть запроса в SQL. Это хороший способ, когда SQL необходимо использовать только в одном запросе в базе кода.
Определите определяемую пользователем функцию (UDF), а затем вызовите ее из запросов. Обратите внимание, что EF позволяет определяемым пользователям возвращать полные результаты — они называются табличными функциями (TVFs), а также позволяют сопоставлять DbSet функцию с функцией, что делает ее похожей на другую таблицу.
Определите представление базы данных и запрос из него в запросах. Обратите внимание, что в отличие от функций представления не могут принимать параметры.

Примечание.

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

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

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

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

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

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

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

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

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

Дополнительные разделы, связанные с эффективным запросом, см. на странице расширенных разделов о производительности.
См. раздел "Производительность" на странице документации по сравнению значений NULL для некоторых рекомендаций при сравнении значений, допускающих значение NULL.

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

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

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

Нужны только свойства проекта

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

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

Избегайте декартового взрыва при загрузке связанных сущностей

Загрузка связанных сущностей по возможности

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

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

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

Отслеживание, разрешение удостоверений и без отслеживания

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

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

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

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