Параметры сортировки и чувствительность к регистру

Обработка текста в базах данных может быть сложной и требует большего внимания пользователя, чем можно было бы подозревать. С одной стороны, базы данных значительно различаются по способу обработки текста; Например, в то время как в некоторых базах данных по умолчанию учитывается регистр (например, Sqlite, PostgreSQL), в других базах данных регистр не учитывается (SQL Server, MySQL). Кроме того, из-за использования индекса чувствительность к регистру и аналогичные аспекты могут иметь далеко идущие последствия для производительности запросов: хотя может быть заманчиво использовать string.ToLower для принудительного сравнения без учета регистра в базе данных с учетом регистра, это может помешать приложению использовать индексы. На этой странице описано, как настроить учет регистра или, в более общем смысле, параметры сортировки и как это сделать эффективно без ущерба для производительности запросов.

Общие сведения о параметрах сортировки

Основное понятие в обработке текста — это параметры сортировки, которые представляют собой набор правил, определяющих порядок упорядочения и сравнения текстовых значений на равенство. Например, в то время как параметры сортировки без учета регистра игнорируют различия между буквами верхнего и нижнего регистра в целях сравнения на равенство, параметры сортировки с учетом регистра не учитывают. Однако, так как учет регистра учитывает язык и региональные параметры (например i , и I представляют разные буквы на турецком языке), существует несколько параметров сортировки без учета регистра, каждый из которых имеет собственный набор правил. Область параметров сортировки также выходит за рамки учета регистра и распространяется на другие аспекты символьных данных; в немецком языке, например, иногда (но не всегда) желательно рассматривать ä и ae как идентичные. Наконец, параметры сортировки также определяют порядок упорядочения текстовых значений: в то время как немецкий язык помещает ä его в aконец алфавита.

Все текстовые операции в базе данных используют параметры сортировки (явным или неявным образом), чтобы определить, как операция сравнивает и упорядочивает строки. Фактический список доступных параметров сортировки и их схем именования зависит от базы данных; Ссылки на соответствующие страницы документации по различным базам данных см. в разделе ниже . К счастью, базы данных обычно позволяют определять параметры сортировки по умолчанию на уровне базы данных или столбца и явно указывать, какие параметры сортировки следует использовать для конкретных операций в запросе.

Параметры сортировки баз данных

В большинстве систем баз данных параметры сортировки по умолчанию определяются на уровне базы данных; Если значение не переопределено, эти параметры сортировки неявно применяются ко всем текстовым операциям, выполняемым в этой базе данных. Параметры сортировки базы данных обычно задаются во время создания базы данных (с помощью CREATE DATABASE инструкции DDL), и если они не указаны, по умолчанию используется некоторое значение уровня сервера, определенное во время установки. Например, параметры сортировки на уровне сервера по умолчанию в SQL Server для языкового стандарта компьютера "Английский (США)" — это SQL_Latin1_General_CP1_CI_ASпараметры сортировки без учета регистра с учетом диакритических знаков. Хотя системы баз данных обычно позволяют изменять параметры сортировки существующей базы данных, это может привести к усложнению; Рекомендуется выбрать параметры сортировки перед созданием базы данных.

При использовании миграций EF Core для управления схемой базы данных следующий метод модели настраивает SQL Server базу данных для использования параметров сортировки с учетом регистраOnModelCreating:

modelBuilder.UseCollation("SQL_Latin1_General_CP1_CS_AS");

Параметры сортировки столбца

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

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

modelBuilder.Entity<Customer>().Property(c => c.Name)
    .UseCollation("SQL_Latin1_General_CP1_CI_AS");

Явные параметры сортировки в запросе

В некоторых случаях один и тот же столбец необходимо запрашивать с использованием разных параметров сортировки в разных запросах. Например, одному запросу может потребоваться выполнить сравнение столбца с учетом регистра, а другому — сравнение с тем же столбцом без учета регистра. Это можно сделать, явно указав параметры сортировки в самом запросе:

var customers = context.Customers
    .Where(c => EF.Functions.Collate(c.Name, "SQL_Latin1_General_CP1_CS_AS") == "John")
    .ToList();

При этом в SQL-запросе COLLATE создается предложение , которое применяет параметры сортировки с учетом регистра независимо от параметров сортировки, определенных на уровне столбца или базы данных:

SELECT [c].[Id], [c].[Name]
FROM [Customers] AS [c]
WHERE [c].[Name] COLLATE SQL_Latin1_General_CP1_CS_AS = N'John'

Явные параметры сортировки и индексы

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

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

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

Всегда проверяйте планы запросов и убедитесь, что правильные индексы используются в критически важных для производительности запросах, выполняемых с большими объемами данных. Переопределение чувствительности к регистру в запросе с помощью EF.Functions.Collate (или путем вызова string.ToLower) может оказать очень значительное влияние на производительность приложения.

Преобразование встроенных строковых операций .NET

В .NET равенство строк по умолчанию учитывает регистр: s1 == s2 выполняет порядковое сравнение, которое требует, чтобы строки были идентичными. Так как параметры сортировки баз данных по умолчанию различаются и для простого равенства желательно использовать индексы, EF Core не пытается преобразовать простое равенство в операцию базы данных с учетом регистра: равенство C# преобразуется непосредственно в равенство SQL, которое может быть или не учитывать регистр, в зависимости от конкретной используемой базы данных и ее конфигурации параметров сортировки.

Кроме того, .NET предоставляет перегрузки приема перечисления string.EqualsStringComparison , что позволяет указать учет регистра и язык и региональные параметры для сравнения. По умолчанию EF Core воздерживается от преобразования этих перегрузок в SQL, и попытка их использования приведет к исключению. Во-первых, EF Core не знает, какие параметры сортировки следует использовать с учетом регистра или без учета регистра. Что еще более важно, применение параметров сортировки в большинстве случаев приведет к предотвращению использования индекса, что значительно повлияет на производительность очень простой и часто используемой конструкции .NET. Чтобы заставить запрос использовать сравнение с учетом регистра или без учета регистра, укажите параметры сортировки явным образом, как EF.Functions.Collateописано выше.

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

Сведения, относящиеся к базе данных

Другие ресурсы