Pracuję w bazie danych MySql z tabelą podobną do tej:

+--------------+
|  table_name  |
+--------------+
|    myField   |
+--------------+

... i muszę zrobić wiele takich zapytań (z 5-10 ciągami na liście) :

SELECT myField FROM table_name
WHERE myField IN ('something', 'other stuff', 'some other a bit longer'...)

Będzie około 24 000 000 unikalnych wierszy

1) Czy powinienem użyć klucza FULLTEXTlub i INDEXdo mojego VARCHAR(150)?
2) Jeśli zwiększę znaki ze 150 do 220 lub 250 ... czy miałoby to wielką różnicę? (Czy jest jakiś sposób, aby to obliczyć?)
3) Jak powiedziałem, będą wyjątkowe, więc myField powinien być KLUCZEM PODSTAWOWYM . Czy nie jest rzadko dodawanie KLUCZA PODSTAWOWEGO do pola, które jest już INDEKSEM VARCHAR / PEŁNYM TEKSTEM?

SUGGESTION # 1: Standardowe indeksowanie

CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    primary key (id),
    key (myfield)
);

Jeśli indeksujesz w ten sposób, możesz albo wyszukać cały ciąg, albo wykonać wyszukiwania LIKE zorientowane na lewo

SUGGESTION # 2: FULLTEXT Indeksowanie

CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    primary key (id),
    fulltext (myfield)
);

Możesz skutecznie wyszukiwać pojedyncze słowa kluczowe oraz całe frazy. Konieczne będzie zdefiniowanie niestandardowej listy słów kluczowych, ponieważ MySQL nie będzie indeksował 543 słów .

Oto moje inne posty z ostatnich dwóch lat na temat indeksów FULLTEXT

• May 23, 2011: Optymalizacja wyszukiwania pełnotekstowego mysql (StackOverflow)
• Oct 25, 2011: Indeks FULLTEXT jest ignorowany w TRYBIE BOOLEAN z warunkową liczbą słów
• Jan 26, 2012: Przeszukiwanie pełnego tekstu MySQL optymalizacja my.cnf
• May 07, 2012: MySQL EXPLAIN nie pokazuje „użyj indeksu” dla FULLTEXT

SUGGESTION # 3: Indeksowanie skrótów

CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    hashmyfield char(32) not null,
    primary key (id),
    key (hashmyfield)
);

Jeśli szukasz jednej konkretnej wartości, której wartości mogą być dłuższe niż 32 znaki, możesz zapisać wartość skrótu:

INSERT INTO mytable (myfield,hashmyfield)
VALUES ('whatever',MD5('whatever'));

W ten sposób po prostu szukasz wartości skrótu, aby pobrać wyniki

SELECT * FROM mytable WHERE hashmyfield = MD5('whatever');

Spróbuj !!!

MySQL umożliwia zdefiniowanie indeksu z prefiksem, co oznacza, że ​​należy zdefiniować pierwsze N ​​znaków z oryginalnego ciągu, który ma być indeksowany, a sztuczką jest wybranie liczby N, która jest wystarczająco długa, aby zapewnić dobrą selektywność, ale wystarczająco krótką, aby zaoszczędzić miejsce. Prefiks powinien być wystarczająco długi, aby indeks był tak samo przydatny, jak w przypadku indeksowania całej kolumny.

Zanim przejdziemy dalej, zdefiniujmy kilka ważnych terminów. Selektywność indeksu to stosunek całkowitych odrębnych indeksowanych wartości i całkowitej liczby wierszy . Oto jeden przykład tabeli testowej:

+-----+-----------+
| id  | value     |
+-----+-----------+
| 1   | abc       |
| 2   | abd       |
| 3   | adg       |
+-----+-----------+

Jeśli indeksujemy tylko pierwszy znak (N = 1), tabela indeksów będzie wyglądać następująco:

+---------------+-----------+
| indexedValue  | rows      |
+---------------+-----------+
| a             | 1,2,3     |
+---------------+-----------+

W tym przypadku selektywność indeksu jest równa IS = 1/3 = 0,33.

Zobaczmy teraz, co się stanie, jeśli zwiększymy liczbę indeksowanych znaków do dwóch (N = 2).

+---------------+-----------+
| indexedValue  | rows      |
+---------------+-----------+
| ab             | 1,2      |
| ad             | 3        |
+---------------+-----------+

W tym scenariuszu IS = 2/3 = 0,66, co oznacza, że ​​zwiększyliśmy selektywność indeksu, ale zwiększyliśmy również rozmiar indeksu. Sztuczka polega na znalezieniu minimalnej liczby N, która doprowadzi do maksymalnej selektywności indeksu .

Istnieją dwa podejścia do wykonywania obliczeń dla tabeli bazy danych. Zrobię demonstrację na tym zrzutu bazy danych .

Powiedzmy, że chcemy dodać do indeksu kolumnę nazwisko w pracownikach tabeli i chcemy zdefiniować najmniejszą liczbę N, która zapewni najlepszą selektywność indeksu.

Najpierw określmy najczęstsze nazwiska:

select count(*) as cnt, last_name from employees group by employees.last_name order by cnt

+-----+-------------+
| cnt | last_name   |
+-----+-------------+
| 226 | Baba        |
| 223 | Coorg       |
| 223 | Gelosh      |
| 222 | Farris      |
| 222 | Sudbeck     |
| 221 | Adachi      |
| 220 | Osgood      |
| 218 | Neiman      |
| 218 | Mandell     |
| 218 | Masada      |
| 217 | Boudaillier |
| 217 | Wendorf     |
| 216 | Pettis      |
| 216 | Solares     |
| 216 | Mahnke      |
+-----+-------------+
15 rows in set (0.64 sec)

Jak widać, nazwisko Baba jest najczęstsze. Teraz znajdziemy najczęściej występujące prefiksy nazwisk , zaczynając od pięcioliterowych prefiksów.

+-----+--------+
| cnt | prefix |
+-----+--------+
| 794 | Schaa  |
| 758 | Mande  |
| 711 | Schwa  |
| 562 | Angel  |
| 561 | Gecse  |
| 555 | Delgr  |
| 550 | Berna  |
| 547 | Peter  |
| 543 | Cappe  |
| 539 | Stran  |
| 534 | Canna  |
| 485 | Georg  |
| 417 | Neima  |
| 398 | Petti  |
| 398 | Duclo  |
+-----+--------+
15 rows in set (0.55 sec)

Występuje znacznie więcej wystąpień każdego prefiksu, co oznacza, że ​​musimy zwiększać liczbę N, aż wartości będą prawie takie same jak w poprzednim przykładzie.

Oto wyniki dla N = 9

select count(*) as cnt, left(last_name,9) as prefix from employees group by prefix order by cnt desc limit 0,15;

+-----+-----------+
| cnt | prefix    |
+-----+-----------+
| 336 | Schwartzb |
| 226 | Baba      |
| 223 | Coorg     |
| 223 | Gelosh    |
| 222 | Sudbeck   |
| 222 | Farris    |
| 221 | Adachi    |
| 220 | Osgood    |
| 218 | Mandell   |
| 218 | Neiman    |
| 218 | Masada    |
| 217 | Wendorf   |
| 217 | Boudailli |
| 216 | Cummings  |
| 216 | Pettis    |
+-----+-----------+

Oto wyniki dla N = 10.

+-----+------------+
| cnt | prefix     |
+-----+------------+
| 226 | Baba       |
| 223 | Coorg      |
| 223 | Gelosh     |
| 222 | Sudbeck    |
| 222 | Farris     |
| 221 | Adachi     |
| 220 | Osgood     |
| 218 | Mandell    |
| 218 | Neiman     |
| 218 | Masada     |
| 217 | Wendorf    |
| 217 | Boudaillie |
| 216 | Cummings   |
| 216 | Pettis     |
| 216 | Solares    |
+-----+------------+
15 rows in set (0.56 sec)

To bardzo dobre wyniki. Oznacza to, że możemy wykonać indeks na kolumnie last_name z indeksowaniem tylko pierwszych 10 znaków. W definicji tabeli kolumna last_name jest zdefiniowana jako VARCHAR(16), a to oznacza, że ​​zapisaliśmy 6 bajtów (lub więcej, jeśli w nazwisku znajdują się znaki UTF8) na pozycję. W tej tabeli znajduje się 1637 różnych wartości pomnożonych przez 6 bajtów, co stanowi około 9 KB, i wyobraź sobie, jak ta liczba by wzrosła, gdyby nasza tabela zawierała miliony wierszy.

Możesz przeczytać inne sposoby obliczania liczby N w moim poście Prefiksy indeksów w MySQL .

Użycie funkcji MD5 i SHA1 w celu wygenerowania wartości, które powinny być indeksowane, również nie jest dobrym podejściem . Czemu? Przeczytaj w poście Jak wybrać odpowiedni typ danych dla klucza podstawowego w bazie danych MySQL