MySQL: Large VARCHAR vs. TEXT?

Mam tabelę komunikatów w MySQL, która rejestruje wiadomości między użytkownikami. Oprócz typowych identyfikatorów i typów wiadomości (wszystkie typy całkowite) muszę zapisać rzeczywisty tekst wiadomości jako VARCHAR lub TEXT. Ustawiam limit frontonu na 3000 znaków, co oznacza, że ​​wiadomości nigdy nie będą wstawiane do bazy danych tak długo.

Czy istnieje uzasadnienie dla korzystania z VARCHAR (3000) lub TEXT? W pisaniu VARCHAR (3000) jest coś, co wydaje się nieco sprzeczne z intuicją. Przejrzałem inne podobne posty na temat Przepełnienia stosu, ale dobrze byłoby uzyskać widoki specyficzne dla tego rodzaju przechowywania typowych wiadomości.

• TEXTi BLOB może być przechowywany poza stołem, przy czym stół ma tylko wskaźnik do lokalizacji rzeczywistej pamięci. Miejsce przechowywania zależy od wielu rzeczy, takich jak rozmiar danych, rozmiar kolumn, format_wierszy i wersja MySQL.

• VARCHARjest przechowywany w jednej linii z tabelą. VARCHARjest szybszy, gdy rozmiar jest rozsądny, którego kompromis byłby szybszy, zależy od twoich danych i sprzętu, chciałbyś przetestować rzeczywisty scenariusz z twoimi danymi.

Czy potrafisz przewidzieć, jak długo potrwa wkład użytkownika?

VARCHAR (X)

Przypadek: nazwa użytkownika, adres e-mail, kraj, temat, hasło

---

TEKST

Case: wiadomości, e-maile, komentarze, sformatowany tekst, HTML, kod, obrazy, linki

---

MEDIUMTEXT

Obudowa: duże ciała Jsona, książki od krótkiej do średniej długości, ciągi CSV

---

LONGTEXT

Przypadek: podręczniki, programy, lata plików z logami, Harry Potter i Czara Ognia, rejestracja badań naukowych

Aby wyjaśnić najlepsze praktyki:

1. Wiadomości w formacie tekstowym powinny prawie zawsze być przechowywane jako TEKST (kończą się dowolnie długimi)

2. Atrybuty ciągów powinny być przechowywane jako VARCHAR (docelowa nazwa użytkownika, temat itp.).

Rozumiem, że masz limit frontonu, który jest świetny, dopóki go nie ma. * grin * Sztuczka polega na tym, aby myśleć o DB jako oddzielnym od aplikacji, które się z nią łączą. To, że jedna aplikacja ogranicza dane, nie oznacza, że ​​dane są wewnętrznie ograniczone.

Co takiego jest w samych wiadomościach, że zmusza je, aby nigdy nie miały więcej niż 3000 znaków? Jeśli jest to tylko arbitralne ograniczenie aplikacji (np. W przypadku pola tekstowego lub czegoś innego), użyj TEXTpola na warstwie danych.

Oświadczenie: Nie jestem ekspertem od MySQL ... ale to moje rozumienie problemów.

Myślę, że TEKST jest przechowywany poza wierszem mysql, podczas gdy myślę, że VARCHAR jest przechowywany jako część tego wiersza. Istnieje maksymalna długość wiersza dla wierszy mysql .. więc możesz ograniczyć ilość innych danych, które możesz przechowywać w rzędzie, używając VARCHAR.

Również ze względu na to, że VARCHAR stanowi część wiersza, podejrzewam, że zapytania dotyczące tego pola będą nieco szybsze niż te, które używają fragmentu TEXT.

Krótka odpowiedź: brak praktycznej, wydajności lub przechowywania, różnica.

Długa odpowiedź:

Zasadniczo nie ma różnicy (w MySQL) między VARCHAR(3000)(lub innym dużym limitem) a TEXT. Pierwszy z nich obetnie 3000 znaków ; ten ostatni zostanie obcięty przy 65535 bajtach . (I rozróżnia bajtów i znaków , ponieważ postać może wziąć kilka bajtów).

Dla mniejszych limitów w VARCHAR istnieją pewne zalety TEXT.

• „mniejszy” oznacza 191, 255, 512, 767 lub 3072 itd., w zależności od wersji, kontekstu i CHARACTER SET.
• INDEXessą ograniczone w tym, jak duża może być indeksowana kolumna. (767 lub 3072 bajtów ; jest to zależne od wersji i ustawień)
• Tabele pośrednie utworzone przez kompleks SELECTssą obsługiwane na dwa różne sposoby - MEMORY (szybciej) lub MyISAM (wolniej). Gdy w grę wchodzą „duże” kolumny, wolniejsza technika jest wybierana automatycznie. (Znaczące zmiany w wersji 8.0; więc ten punktor może ulec zmianie).
• W odniesieniu do poprzedniego elementu wszystkie TEXTtypy danych (w przeciwieństwie do VARCHAR) przechodzą bezpośrednio do MyISAM. Oznacza to, że TINYTEXTautomatycznie jest gorszy dla wygenerowanych tabel temperatur niż ich odpowiednik VARCHAR. (Ale to prowadzi dyskusję w trzecim kierunku!)
• VARBINARYjest jak VARCHAR; BLOBjest jak TEXT.

Obalenie innych odpowiedzi

Pierwotne pytanie zawierało jedno pytanie (jakiego typu danych użyć); zaakceptowana odpowiedź odpowiedziała na coś innego (zapisywanie poza rekordem). Ta odpowiedź jest już nieaktualna.

Kiedy ten wątek został uruchomiony i odpowiedział, w InnoDB istniały tylko dwa „formaty wierszy”. Niedługo potem dwa kolejne formaty ( DYNAMICiCOMPRESSED ).

Miejsce przechowywania TEXTi VARCHAR()zależy od rozmiaru , a nie od nazwy typu danych . Aby uzyskać zaktualizowaną dyskusję na temat przechowywania / zapisywania w trybie zapisu dużych kolumn tekstu / obiektów blob, zobacz to .

W poprzednich odpowiedziach nie nalegano wystarczająco na główny problem: nawet w bardzo prostych zapytaniach, takich jak

(SELECT t2.* FROM t1, t2 WHERE t2.id = t1.id ORDER BY t1.id) 

może być wymagana tabela tymczasowa, a jeśli VARCHARpole jest zaangażowane, jest konwertowane na CHARpole w tabeli tymczasowej. Więc jeśli masz w tabeli powiedz 500 000 linii z VARCHAR(65000)polem, sama kolumna użyje 6,5 * 5 * 10 ^ 9 bajtów. Takie tabele tymczasowe nie mogą być obsługiwane w pamięci i są zapisywane na dysku. Można oczekiwać, że wpływ będzie katastrofalny.

Źródło (z metrykami): https://nicj.net/mysql-text-vs-varchar-performance/ (Odnosi się to do obsługi TEXTvs VARCHARw „standardowym” (?) Silniku pamięci MyISAM. W innych może być inaczej, np. InnoDB.)

Istnieje OGROMNA różnica między VARCHAR a TEKSTEM. Podczas gdy pola VARCHAR mogą być indeksowane, pola TEXT nie. Pola typu VARCHAR są przechowywane w linii, podczas gdy TEKST jest przechowywany w trybie offline, w rzeczywistości zapisywane są tylko wskaźniki do danych TEKSTU.

Jeśli musisz zaindeksować swoje pole w celu szybszego wyszukiwania, zaktualizuj lub usuń niż przejdź do VARCHAR, bez względu na to, jak duże. VARCHAR (10000000) nigdy nie będzie taki sam jak pole TEXT, ponieważ te dwa typy danych mają różny charakter.

• Jeśli korzystasz z pola tylko do archiwizacji
• nie zależy ci na wycofywaniu prędkości danych
• zależy Ci na szybkości, ale w zapytaniu użyjesz operatora „% LIKE%”, więc indeksowanie niewiele pomoże
• nie można przewidzieć limitu długości danych

niż iść do TEKSTU.

Varchar jest przeznaczony do małych danych, takich jak adresy e-mail, natomiast Text do znacznie większych danych, takich jak artykuły prasowe, Blob do danych binarnych, takich jak obrazy.

Wydajność Varchar jest większa, ponieważ działa całkowicie z pamięci, ale nie będzie tak, jeśli dane są zbyt duże, jak varchar(4000)na przykład.

Z drugiej strony, tekst nie przylega do pamięci i ma wpływ na wydajność dysku, ale można tego uniknąć, oddzielając dane tekstowe w osobnej tabeli i stosując zapytanie o lewe połączenie w celu pobrania danych tekstowych.

Kropelka jest znacznie wolniejsza, więc używaj jej tylko wtedy, gdy nie masz zbyt wielu danych, takich jak 10000 obrazów, które kosztują 10000 rekordów.

Postępuj zgodnie z tymi wskazówkami, aby uzyskać maksymalną prędkość i wydajność:

1. Użyj varchar do imienia, tytułów, e-maili

2. Użyj tekstu dla dużych danych

3. Oddziel tekst w różnych tabelach

4. Użyj zapytań Left Join o identyfikatorze, takim jak numer telefonu

5. Jeśli zamierzasz użyć obiektu Blob, zastosuj te same wskazówki, co w tekście

Spowoduje to, że zapytania będą kosztować milisekundy w tabelach z danymi> 10 M i rozmiarem do 10 GB.