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

  • Статья

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

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

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

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

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

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

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

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();

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

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 предоставляет перегрузки принятия StringComparison перечисления, что позволяет указывать конфиденциальность регистра string.Equals и язык и региональные параметры для сравнения. По проектированию EF Core воздерживается от перевода этих перегрузк в SQL, и попытка их использования приведет к исключению. Для одной вещи EF Core не знает, какой регистр учитывает регистр или не учитывает регистр, следует использовать параметры сортировки. Более важно, что применение сортировки в большинстве случаев предотвращает использование индекса, что значительно влияет на производительность для очень простой и часто используемой конструкции .NET. Чтобы принудительно использовать сравнение с учетом регистра или без учета регистра, укажите параметры сортировки явным образом, EF.Functions.Collate как описано выше.

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

Сведения о конкретной базе данных

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

  • Сеанс стенда сообщества данных .NET, введение в параметры сортировки и изучение аспектов perf и индексирования.