Sortowania i czułość wielkości liter

  • Artykuł

Przetwarzanie tekstu w bazach danych może być złożone i wymaga większej uwagi użytkownika niż podejrzewa się. W pierwszej kolejności bazy danych różnią się znacznie w sposobie obsługi tekstu; na przykład w niektórych bazach danych jest domyślnie rozróżniana wielkość liter (np. Sqlite, PostgreSQL), a inne są bez uwzględniania wielkości liter (SQL Server, MySQL). Ponadto ze względu na użycie indeksu wielkość liter i podobne aspekty mogą mieć daleko idący wpływ na wydajność zapytań: chociaż może być kuszące, aby wymusić string.ToLower porównanie bez uwzględniania wielkości liter w bazie danych z uwzględnieniem wielkości liter, może to uniemożliwić aplikacji korzystanie z indeksów. Na tej stronie szczegółowo opisano sposób konfigurowania poufności wielkości liter lub ogólniej sortowania oraz sposobu ich wykonywania w wydajny sposób bez naruszania wydajności zapytań.

Wprowadzenie do sortowania

Podstawową koncepcją przetwarzania tekstu jest sortowanie, czyli zestaw reguł określający sposób porządkowania i porównywania wartości tekstowych pod kątem równości. Na przykład podczas gdy sortowanie bez uwzględniania wielkości liter pomija różnice między wielkimi i małymi literami na potrzeby porównania równości, sortowanie z uwzględnieniem wielkości liter nie jest. Jednak ponieważ czułość wielkości liter jest wrażliwa na kulturę (np. i i I reprezentuje różne litery w języku tureckim), istnieje wiele sortowania bez uwzględniania wielkości liter, z których każdy ma własny zestaw reguł. Zakres sortowania wykracza również poza wielkość liter, na inne aspekty danych znaków; w języku niemieckim, na przykład, czasami (ale nie zawsze) pożądane jest traktowanie ä i ae tak samo. Na koniec sortowania definiują również sposób porządkowania wartości tekstowych: podczas gdy niemieckie miejsca ä po a, szwedzkie umieszcza je na końcu alfabetu.

Wszystkie operacje tekstowe w bazie danych używają sortowania — jawnie lub niejawnie — w celu określenia, jak operacja porównuje ciągi i porządkuje je. Rzeczywista lista dostępnych sortowania i ich schematów nazewnictwa jest specyficzna dla bazy danych; Zapoznaj się z poniższą sekcją, aby uzyskać linki do odpowiednich stron dokumentacji różnych baz danych. Na szczęście bazy danych zwykle zezwalają na zdefiniowanie domyślnego sortowania na poziomie bazy danych lub kolumny oraz jawne określenie sortowania, które ma być używane dla określonych operacji w zapytaniu.

Sortowanie bazy danych

W większości systemów baz danych domyślne sortowanie jest definiowane na poziomie bazy danych; chyba że zostanie zastąpione, sortowanie niejawnie ma zastosowanie do wszystkich operacji tekstowych występujących w tej bazie danych. Sortowanie bazy danych jest zwykle ustawiane w czasie tworzenia bazy danych (za pośrednictwem CREATE DATABASE instrukcji DDL), a jeśli nie zostanie określone, domyślnie zostanie określona określona wartość na poziomie serwera określona w czasie instalacji. Na przykład domyślne sortowanie na poziomie serwera w programie SQL Server dla ustawień regionalnych "angielski (Stany Zjednoczone)" to SQL_Latin1_General_CP1_CI_AS, czyli sortowanie z uwzględnieniem wielkości liter. Mimo że systemy baz danych zwykle zezwalają na zmianę sortowania istniejącej bazy danych, może to prowadzić do komplikacji; Zaleca się wybranie sortowania przed utworzeniem bazy danych.

W przypadku korzystania z migracji programu EF Core do zarządzania schematem bazy danych w metodzie modelu OnModelCreating konfiguruje bazę danych programu SQL Server tak, aby korzystała z sortowania uwzględniającego wielkość liter:

modelBuilder.UseCollation("SQL_Latin1_General_CP1_CS_AS");

Sortowanie kolumn

Sortowania można również zdefiniować w kolumnach tekstowych, przesłaniając wartość domyślną bazy danych. Może to być przydatne, jeśli niektóre kolumny muszą być niewrażliwe na wielkość liter, podczas gdy pozostała część bazy danych musi mieć wielkość liter.

W przypadku używania migracji programu EF Core do zarządzania schematem bazy danych następujące elementy konfigurują kolumnę dla Name właściwości tak, aby była niewrażliwa na wielkość liter w bazie danych, która w przeciwnym razie jest wrażliwa na wielkość liter:

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

Sortowanie jawne w zapytaniu

W niektórych przypadkach ta sama kolumna musi być odpytywane przy użyciu różnych sortowania według różnych zapytań. Na przykład jedno zapytanie może wymagać przeprowadzenia porównania z uwzględnieniem wielkości liter w kolumnie, podczas gdy inne może wymagać porównania bez uwzględniania wielkości liter w tej samej kolumnie. Można to zrobić, jawnie określając sortowanie w samym zapytaniu:

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

Spowoduje to wygenerowanie COLLATE klauzuli w zapytaniu SQL, która stosuje sortowanie uwzględniające wielkość liter niezależnie od sortowania zdefiniowanego na poziomie kolumny lub bazy danych:

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

Jawne sortowania i indeksy

Indeksy są jednym z najważniejszych czynników wydajności bazy danych — zapytanie, które działa wydajnie z indeksem, może być zatrzymywane bez tego indeksu. Indeksy niejawnie dziedziczą sortowanie kolumny; Oznacza to, że wszystkie zapytania w kolumnie są automatycznie uprawnione do używania indeksów zdefiniowanych w tej kolumnie — pod warunkiem, że zapytanie nie określa innego sortowania. Określenie jawnego sortowania w zapytaniu zwykle uniemożliwi użycie indeksu zdefiniowanego w tej kolumnie, ponieważ sortowania nie będą już zgodne; Dlatego zaleca się zachowanie ostrożności podczas korzystania z tej funkcji. Zawsze zaleca się zdefiniowanie sortowania na poziomie kolumny (lub bazy danych), dzięki czemu wszystkie zapytania mogą niejawnie używać tego sortowania i korzystać z dowolnego indeksu.

Należy pamiętać, że niektóre bazy danych umożliwiają definiowanie sortowania podczas tworzenia indeksu (np. PostgreSQL, Sqlite). Dzięki temu można zdefiniować wiele indeksów w tej samej kolumnie, przyspieszając operacje z różnymi sortowaniami (np. w porównaniach uwzględniających wielkość liter i bez uwzględniania wielkości liter). Aby uzyskać więcej informacji, zapoznaj się z dokumentacją dostawcy bazy danych.

Ostrzeżenie

Zawsze sprawdzaj plany zapytań zapytań i upewnij się, że odpowiednie indeksy są używane w zapytaniach o krytycznym znaczeniu dla wydajności wykonywanych na dużych ilościach danych. Zastępowanie poufności wielkości liter w zapytaniu za pośrednictwem EF.Functions.Collate metody (lub przez wywołanie metody string.ToLower) może mieć bardzo znaczący wpływ na wydajność aplikacji.

Tłumaczenie wbudowanych operacji ciągów platformy .NET

W przypadku platformy .NET równość ciągów jest domyślnie uwzględniana wielkość liter: s1 == s2 wykonuje porównanie porządkowe, które wymaga, aby ciągi są identyczne. Ponieważ domyślne sortowanie baz danych różni się i dlatego, że pożądane jest proste równości w przypadku używania indeksów, program EF Core nie próbuje przetłumaczyć prostej równości na operację uwzględniającą wielkość liter bazy danych: równość języka C# jest tłumaczona bezpośrednio na równość JĘZYKA SQL, która może lub nie może być uwzględniana wielkości liter, w zależności od używanej bazy danych i konfiguracji sortowania.

Ponadto platforma .NET udostępnia przeciążenia akceptowania string.EqualsStringComparison wyliczenia, co umożliwia określenie poufności liter i kultury dla porównania. Zgodnie z projektem program EF Core powstrzymuje się od tłumaczenia tych przeciążeń na język SQL, a próba ich użycia spowoduje wyjątek. W pierwszej kolejności program EF Core nie wie, w którym przypadku należy używać sortowania bez uwzględniania wielkości liter. Co ważniejsze, zastosowanie sortowania w większości przypadków uniemożliwia użycie indeksu, co znacząco wpływa na wydajność bardzo podstawowej i powszechnie używanej konstrukcji platformy .NET. Aby wymusić użycie porównania uwzględniającego wielkość liter lub wielkość liter, określ sortowanie jawnie zgodnie z EF.Functions.Collatepowyższym opisem.

Dodatkowe zasoby

Informacje specyficzne dla bazy danych

Inne zasoby

  • Sesja standup społeczności danych platformy .NET, wprowadzając sortowania i eksplorując aspekty wydajności i indeksowania.